Code example to generate custom Project Nr.

ad_proc -public im_custom_next_project_nr {
    {-customer_id 0 }
    {-nr_digits {}}
    {-date_format {}}
} {

    Project_nr should look like: cccc-xxxxx with the first 4 digits being
    the customer code and the second 4 digits is the current project_nr

    Returns "" if there was an error calculating the number.

} {

    if {"none" == $date_format} { set date_format "" }
    set customer_customer_code ""
    set customer_name "Error retreiving Customer Information"
    catch {
            db_1row cust_code "
                select  company_id,
                        borg_customer_code as customer_customer_code,
                        company_name as customer_name
                from    im_companies
                where   company_id = :customer_id
            "
    } errmsg
    ns_log Notice "im_customer_next_project_nr: customer_customer_code=$customer_code"

    if {[string length $customer_code] != 4} {
        ad_return_complaint 1 "<b>Unable to find 'Spanish Customer Code'</b>:
        <p>
        The customer <a href=/intranet/companies/view?company_id=$customer_id>$customer_name</a>
        does not have a valid 4 digit 'Customer Code' field. <br>
        Please follow the link and setup a customer code with four digits.<br>
        </p>
        <pre>$errmsg</pre>
        "
        ad_script_abort
    }

    # ----------------------------------------------------
    # Calculate the next project nr by finding out the last
    # one +1

    # code + year code
    set today [db_string today "select to_char(now(), :date_format)"]
    set today "$customer_code$today"

    # Adjust the position of the start of date and nr in the invoice_nr
    set customer_customer_code_len [string length $customer_code]
    set date_format_len [string length $date_format]
    set date_format_len [expr $date_format_len + $customer_code_len]
    set nr_start_idx [expr 2 + $date_format_len]
    set date_start_idx 1

    set num_check_sql ""
    set zeros ""
    for {set i 0} {$i < $nr_digits} {incr i} {
        set digit_idx [expr 1 + $i]
        append num_check_sql "
                and ascii(substr(p.nr,$digit_idx,1)) > 47
                and ascii(substr(p.nr,$digit_idx,1)) < 58
        "
        append zeros "0"
    }


    set sql "
        select
                trim(max(p.nr)) as last_project_nr
        from (
                 select substr(project_nr, :nr_start_idx, :nr_digits) as nr
                 from   im_projects
                 where  lower(substr(project_nr, :date_start_idx, :date_format_len)) = lower('$today')
             ) p
        where   1=1
                $num_check_sql
    "

    set last_project_nr [db_string max_project_nr $sql -default $zeros]
    set last_project_nr [string trimleft $last_project_nr "0"]
    if {[empty_string_p $last_project_nr]} { set last_project_nr 0 }
    set next_number [expr $last_project_nr + 1]
    ns_log Notice "im_customer_next_project_nr: last_project_nr=$last_project_nr, next_number=$next_number"

    # ----------------------------------------------------
    # Put together the new project_nr
    set nr_sql "select '$today' || '_' || trim(to_char($next_number,:zeros)) as project_nr"
    set project_nr [db_string next_project_nr $nr_sql -default ""]
    return $project_nr
}