im_customers_status_change db { coverage " " } { report_date " " } { purpose " " }What it does:
Returns a string that gives a list of customers that have had a status change with in the coverage date from the report date. It also displays what status change they have undergone. Note that the most recent status change is listed for the given period.Defined in: /web/philip/tcl/intranet-status-report-defs.tcl
Source code:
set selection [ns_db select $db "select g.group_name, g.group_id, to_char(status_modification_date, 'Mon DD, YYYY') as status_modification_date, im_cust_status_from_id(customer_status_id) as status, im_cust_status_from_id(old_customer_status_id) as old_status from im_customers c, user_groups g where status_modification_date > to_date([util_decode $report_date "" sysdate "'$report_date'"], 'YYYY-MM-DD')-[util_decode $coverage "" 1 $coverage] and old_customer_status_id is not null and old_customer_status_id <> customer_status_id and c.group_id=g.group_id order by lower(group_name)"] set return_list [list] while {[ns_db getrow $db $selection]} { set_variables_after_query if {$purpose == "web_display"} { lappend return_list "<a href=[im_url_stub]/customers/view.tcl?[export_url_vars group_id]>$group_name</a> went from <b>$old_status</b> to <b>$status</b> on $status_modification_date." } else { lappend return_list "$group_name went from $old_status to $status on $status_modification_date." } } if {[llength $return_list] == 0} { set end_date [database_to_tcl_string $db "select sysdate-[util_decode $coverage "" 1 $coverage] from dual"] if { [empty_string_p $report_date] } { set report_date [database_to_tcl_string $db "select sysdate from dual"] } return "No status changes in period [util_IllustraDatetoPrettyDate $end_date] - [util_IllustraDatetoPrettyDate $report_date].\n" } if {$purpose == "web_display"} { return "<ul><li>[join $return_list "<li>"]</ul>" } else { return "\n[join $return_list "\n"] " }