im_list_late_project_report_groups_for_user

im_list_late_project_report_groups_for_user   db   user_id   { number_days "7" }

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 query

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