im_customers_status_change

im_customers_status_change   db   { coverage "" }   { report_date "" }   { purpose "" }

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.

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"] "
    }