im_delinquent_employees db { coverage " " } { report_date " " } { purpose " " }What it does:
Returns a string that gives a list of recent employeesDefined in: /web/philip/tcl/intranet-status-report-defs.tcl
Source code:
# set user_class_selection [ns_set create selection] # # We are forced to hard code the number because the user_class # # table doesn't have a non-integer key. This is not good. # # 57 # set user_class_id [ad_parameter UserClassStatusReportID intranet] # if { [empty_string_p $user_class_id] } { # return "" # } # ns_set put $user_class_selection user_class_id $user_class_id # set user_class_sql_query [ad_user_class_query $user_class_selection] # set selection [ns_db select $db $user_class_sql_query] set selection [ns_db select $db "select u.user_id, u.first_names || ' ' || u.last_name as name from (select distinct users_active.user_id, users_active.first_names, users_active.last_name from users_active, user_group_map where users_active.user_id = user_group_map.user_id and user_group_map.group_id = [im_employee_group_id $db]) u, im_employee_info info where u.user_id = info.user_id and sysdate > info.start_date and exists (select 1 from im_employee_percentage_time where im_employee_percentage_time.user_id=u.user_id and start_block between to_date('$report_date')-7 and to_date('$report_date') and im_employee_percentage_time.percentage_time > 0) and not exists (select 1 from im_hours h where h.user_id=u.user_id and h.day between to_date('$report_date')-7 and to_date('$report_date') and h.note is not null)"] set count 0 set return_string [list] set return_list "" while {[ns_db getrow $db $selection]} { set_variables_after_query if {$purpose == "web_display"} { lappend return_list "<a href=[im_url_stub]/users/view.tcl?[export_url_vars user_id]>$name</a>" } else { lappend return_list $name } } if {[llength $return_list] > 0} { if {$purpose == "web_display"} { append return_string "<blockquote><b>The following employees have not logged their work in over 7 days:</b> <br> [join $return_list " | "] </blockquote>" } else { append return_string "The following employees have not logged their work in over 7 days: [join $return_list " | "]" } } set return_list [list] # to be on time with a status report # you have to fill out the following every 7 dates # a) If a survey exists for your project_type, fill it out # b) If not, the project report is a general comment set project_report_as_survey_list "" foreach type_survey_pair [ad_parameter_all_values_as_list ProjectReportTypeSurveyNamePair intranet] { set type_survey_list [split $type_survey_pair ","] set type [lindex $type_survey_list 0] set survey [lindex $type_survey_list 1] # we found a project type done with a survey lappend project_report_type_as_survey_list [string tolower $type] } set selection [ns_db select $db "select u.first_names || ' ' || u.last_name as name, u.user_id, g.group_id, g.group_name, im_project_types.project_type from im_projects p, user_groups g, users_active u, im_project_types where p.parent_id is null and p.project_type_id = im_project_types.project_type_id and p.group_id = g.group_id and p.project_lead_id = u.user_id and exists (select 1 from user_group_map where group_id=[im_employee_group_id $db] and user_id=u.user_id) and p.project_status_id = (select project_status_id from im_project_status where project_status='Open') and ((lower(project_type) not in ('[join $project_report_type_as_survey_list "','"]') and p.group_id not in (select on_what_id from general_comments where on_which_table = 'user_groups' and comment_date between to_date('$report_date')-7 and to_date('$report_date')+1)) or (lower(project_type) in ('[join $project_report_type_as_survey_list "','"]') and not exists (select 1 from survsimp_responses where submission_date between to_date('$report_date')-7 and to_date('$report_date')+1 and survsimp_responses.group_id=p.group_id))) order by lower(im_project_types.project_type), lower(g.group_name)"] # note: report_date + 1 about is to catch the case # where the user submitted it on that day while {[ns_db getrow $db $selection]} { set_variables_after_query if {$purpose == "web_display"} { lappend return_list "<a href=[im_url_stub]/users/view.tcl?[export_url_vars user_id]>$name</a> on $project_type project: <a href=[im_url_stub]/projects/view.tcl?[export_url_vars group_id]>$group_name</a>" } else { lappend return_list "$name on $project_type project: $group_name" } } if {[llength $return_list] > 0} { if {$purpose == "web_display"} { append return_string " <p> <blockquote> <b>The following employees are late with a progress report:</b> <br> [join $return_list " | "] </blockquote>" } else { append return_string " \n\n The following employees are late with a progress report: [join $return_list " | "]" } return $return_string }