im_list_late_project_report_groups_for_user db user_id { number_days "7" }What it does:
Returns a list of all the groups and group ids for which the user is late entering in a report. The ith element is the group name, the i+1st element is the group_id. This function simply hides the complexity of the late_project_report queryDefined in: /web/philip/tcl/intranet-defs.tcl
Source code:
set project_report_type_as_survey_list [list] set survey_report_types_list [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] lappend survey_report_types_list [string tolower $survey] } # We generate a list of the criteria out here to try to make the query more readable set criteria [list "p.requires_report_p='t'" "u.user_id='$user_id'"] # Only open projects need project reports lappend criteria "p.project_status_id = (select project_status_id from im_project_status where project_status='Open')" # We have mulitple reports - those for project types listed in the .ini file # and general comments for others. # Check reports that need general_comments reports if { [llength $project_report_type_as_survey_list] == 0 } { set general_comments_reports "not exists (select 1 from general_comments gc where gc.comment_date > sysdate - $number_days and on_which_table = 'user_groups' and on_what_id = p.group_id)" lappend criteria $general_comments_reports } else { set general_comments_reports "lower(project_type) not in ('[join $project_report_type_as_survey_list "','"]') and not exists (select 1 from general_comments gc where gc.comment_date > sysdate - $number_days and on_which_table = 'user_groups' and on_what_id = p.group_id)" # With project types that need survey reports, we check two things: # 1. that a survey actually exists for the user to fill out # 2. It's filled out if it exists. # set survey_reports "lower(project_type) in ('[join $project_report_type_as_survey_list "','"]') and exists (select 1 from survsimp_surveys where short_name in ('[join $survey_report_types_list "','"]')) and not exists (select 1 from survsimp_responses where survey_id=(select survey_id from survsimp_surveys where short_name in ('[join $survey_report_types_list "','"]')) and submission_date > sysdate - $number_days and group_id=p.group_id)" lappend criteria "( ($general_comments_reports) or ($survey_reports) )" } set where_clause [join $criteria "\n and "] set selection [ns_db select $db "select g.group_name, g.group_id from user_groups g, im_projects p, im_employees_active u, im_project_types where p.project_lead_id = u.user_id and p.project_type_id = im_project_types.project_type_id and p.group_id=g.group_id and $where_clause"] set group_list [list] while {[ns_db getrow $db $selection]} { set_variables_after_query lappend group_list $group_name $group_id } return $group_list