im_project_reports

one of the documented procedures in this installation of the ACS
Usage:
im_project_reports   db   { coverage "" }   { report_date "" }   { purpose "" }   { user_id "" }
What it does:
Returns a string that gives a list of recent employees
Defined in: /web/philip/tcl/intranet-status-report-defs.tcl

Source code:



    if { [empty_string_p $coverage] } {
	set coverage 1
    } 
    if { [empty_string_p $report_date] } {
	set report_date sysdate
    } else {
	set report_date "'$report_date'"
    }
    if { [empty_string_p $user_id] } {
	set user_id_sql ""
    } else {
	validate_integer "user id" $user_id
	set user_id_sql " and u.user_id='$user_id' "
    }
    set selection [ns_db select $db  "select u.user_id, first_names, last_name, general_comments.content, general_comments.html_p, one_line, comment_id,
                    user_groups.group_name, user_groups.group_id, to_char(comment_date,'Mon DD, YYYY') as pretty_comment_date
              from users_active u, general_comments, user_groups, im_projects
             where u.user_id =general_comments.user_id $user_id_sql
               and im_projects.group_id = user_groups.group_id
               and user_groups.group_id = general_comments.on_what_id
               and on_which_table = 'user_groups'
               and comment_date > to_date($report_date, 'YYYY-MM-DD') - $coverage
             order by lower(group_name), comment_date"]

    set return_list [list]
    set return_url "[im_url]/projects/view.tcl?[export_url_vars group_id]"
    while {[ns_db getrow $db $selection]} {
	set_variables_after_query

	if {$purpose == "web_display"} {
	    set info "<a href=[ad_url]/general-comments/view-one.tcl?[export_url_vars comment_id return_url]&item=[ns_urlencode $group_name]>$one_line</a> - <a href=[im_url]/projects/view.tcl?[export_url_vars group_id]>$group_name</a> posted on $pretty_comment_date"
	    if { ![empty_string_p $user_id] } {
		append info " by <a href=[im_url_stub]/users/view.tcl?[export_url_vars user_id]>$first_names $last_name</a>"
	    }
	    lappend return_list "$info

<blockquote>[util_maybe_convert_to_html $content $html_p]</blockquote>
"
	} else {

	    lappend return_list "$one_line - $group_name posted on $pretty_comment_date  by $first_names $last_name
	    
[util_striphtml $content]

  -- [im_url]/projects/view.tcl?[export_url_vars group_id]
"
	}
    }

    # get all the project surveys linked projects
    set selection [ns_db select $db  "select u.user_id, first_names, last_name,  response_id,
                    user_groups.group_name, user_groups.group_id, to_char(submission_date,'Mon DD, YYYY') as pretty_date
              from users_active u, survsimp_responses, user_groups, im_projects
             where u.user_id =survsimp_responses.user_id $user_id_sql
               and im_projects.group_id = user_groups.group_id
               and user_groups.group_id = survsimp_responses.group_id
               and submission_date > to_date($report_date, 'YYYY-MM-DD') - $coverage
             order by lower(group_name), submission_date"]
    set db2 [ns_db gethandle log]
    while {[ns_db getrow $db $selection]} {
	set_variables_after_query
	set text ""
	if {$purpose == "web_display"} {
	    set survey_html_p 1
	    append text " <b><a href=[im_url_stub]/projects/view.tcl?group_id=$group_id>$group_name</a> report posted on $pretty_date by <a href=/shared/community-member.tcl?user_id=$user_id>$first_names $last_name</a></b><blockquote>\n"
	} else {
	    set survey_html_p 0
	    append text "$group_name report posted on $pretty_date by $first_names $last_name\n"
	}
	append text [survsimp_answer_summary_display $db2 $response_id $survey_html_p]
	if { $survey_html_p } {
	    append text "</blockquote>\n"
	}
	lappend return_list $text
    }

    if {[llength $return_list] == 0} {
	return "None. \n"
    }
    ns_db releasehandle $db2    
    if {$purpose == "web_display"} {
	return "<ul><li>[join $return_list "<li>"]</ul>"
    } else {
	return "\n [join $return_list "\n"] "
    }



philg@mit.edu