im_delinquent_employees

im_delinquent_employees   db   { coverage "" }   { report_date "" }   { purpose "" }

Returns a string that gives a list of recent employees

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
    }