Index: openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql 7 Dec 2006 05:27:06 -0000 1.2 +++ openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql 7 Mar 2007 16:26:28 -0000 1.3 @@ -37,7 +37,35 @@ and m.session_id in (select max(session_id) from dotlrn_ecommerce_application_assessment_map group by rel_id)) m - on (r.rel_id = m.rel_id), + on (r.rel_id = m.rel_id) + left join + + (select count(*) as attendance, dca.community_id, a.user_id + from + calendars c, + cal_items ci, + cal_item_types cit, + attendance_cal_item_map a, + dotlrn_communities_all dca, + portal_pages pp, + portal_element_map pem, + portal_element_parameters pep, + portal_datasources pd + where + a.cal_item_id = ci.cal_item_id + and cit.calendar_id = c.calendar_id + and cit.type='Session' + and ci.on_which_calendar= c.calendar_id + and pem.datasource_id = pd.datasource_id + and pep.key = 'calendar_id' + and pp.page_id = pem.page_id + and pep.element_id = pem.element_id + and pd.name='calendar_portlet' + and pp.portal_id = dca.portal_id + and pep.value = c.calendar_id + group by a.user_id, dca.community_id + ) a on (a.user_id = r.user_id and r.community_id = a.community_id), + dotlrn_ecommerce_section s left join ec_products p on (s.product_id = p.product_id), @@ -49,7 +77,6 @@ and s.course_id = i.item_id and t.course_id = i.live_revision and r.rel_id = o.object_id - $member_state_clause $user_clause $section_clause @@ -60,7 +87,7 @@ - select person__name(r.user_id) as person_name, member_state, r.community_id, r.user_id as applicant_user_id, s.section_name, t.course_name, s.section_id, r.rel_id, e.phone, o.creation_user as patron_id, m.completed_datetime, + select person__name(r.user_id) as person_name, member_state, r.community_id, r.user_id as applicant_user_id, s.section_name, t.course_name, s.section_id, r.rel_id, e.phone, o.creation_user as patron_id, m.completed_datetime, coalesce(a.attendance,0) as attendance, (select count(*) from (select * from dotlrn_member_rels_full rr, @@ -69,9 +96,9 @@ and rr.rel_id <= r.rel_id and rr.community_id = r.community_id and rr.member_state = r.member_state - order by o.creation_date) r) as number, s.product_id, m.session_id, m.completed_datetime + order by o.creation_date) r) as number, s.product_id, m.session_id, m.completed_datetime, a.calendar_id - from dotlrn_member_rels_full r + from dotlrn_member_rels_full r left join (select * from ec_addresses where address_id in (select max(address_id) @@ -84,19 +111,45 @@ and m.session_id in (select max(session_id) from dotlrn_ecommerce_application_assessment_map group by rel_id)) m - on (r.rel_id = m.rel_id), + on (r.rel_id = m.rel_id) +left join + (select count(*) as attendance, dca.community_id, a.user_id, c.calendar_id + from + attendance_cal_item_map a, + calendars c, + cal_items ci, + cal_item_types cit, + dotlrn_communities_all dca, + portal_pages pp, + portal_element_map pem, + portal_element_parameters pep, + portal_datasources pd + where + a.cal_item_id = ci.cal_item_id + and cit.calendar_id = c.calendar_id + and cit.type='Session' + and ci.on_which_calendar= c.calendar_id + and pem.datasource_id = pd.datasource_id + and pep.key = 'calendar_id' + and pp.page_id = pem.page_id + and pep.element_id = pem.element_id + and pp.portal_id = dca.portal_id + and pd.name='calendar_portlet' + and pep.value = c.calendar_id + group by a.user_id, dca.community_id, c.calendar_id + ) a + + on (a.user_id = r.user_id and a.community_id = r.community_id), dotlrn_ecommerce_section s left join ec_products p on (s.product_id = p.product_id), dotlrn_catalogi t, cr_items i, acs_objects o - where r.community_id = s.community_id and s.course_id = i.item_id and t.course_id = i.live_revision and r.rel_id = o.object_id - $member_state_clause $user_clause $section_clause Index: openacs-4/packages/dotlrn-ecommerce/www/applications.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/www/applications.tcl,v diff -u -r1.53 -r1.54 --- openacs-4/packages/dotlrn-ecommerce/www/applications.tcl 7 Dec 2006 05:27:06 -0000 1.53 +++ openacs-4/packages/dotlrn-ecommerce/www/applications.tcl 7 Mar 2007 16:26:28 -0000 1.54 @@ -12,6 +12,7 @@ [list \ type:optional \ orderby:optional \ + groupby:optional \ section_id:optional \ {csv_p 0} \ {as_item_id ""} \ @@ -130,7 +131,9 @@ } }] +lappend list_filters attendance_filter [list label "Attendance" where_clause { a.attendance >= :attendance_filter }] set actions "" + set bulk_actions [list [_ dotlrn-ecommerce.Approve] application-bulk-approve [_ dotlrn-ecommerce.Approve] "[_ dotlrn-ecommerce.Reject] / [_ dotlrn-ecommerce.Cancel]" application-bulk-reject "[_ dotlrn-ecommerce.Reject] / [_ dotlrn-ecommerce.Cancel]"] if {[parameter::get -parameter AllowApplicationBulkEmail -default 0]} { @@ -142,17 +145,18 @@ lappend bulk_actions "[_ dotlrn-ecommerce.Mark_as_Paid]" application-bulk-payments "[_ dotlrn-ecommerce.Mark_as_Paid]" } -set elements {section_name { - label "[_ dotlrn-ecommerce.Section]" +set elements [list section_name [list \ + label "[_ dotlrn-ecommerce.Section]" \ display_template { @applications.course_name@: @applications.section_name@ @applications.course_name@: @applications.section_name@ - - } - } + @applications.community_id@ + } \ + hide_p [info exists groupby] \ + ] \ number { label "[_ dotlrn-ecommerce.lt_Number_in_Waiting_Lis]" html { align center } @@ -168,7 +172,7 @@ Approved } - } + } \ person_name { label "[_ dotlrn-ecommerce.Participant]" display_template { @@ -179,7 +183,9 @@ @applications.person_name@ } - } + aggregate "count" + aggregate_group_label "Num. Participants" + } \ member_state { label "[_ dotlrn-ecommerce.Member_Request]" display_template { @@ -202,7 +208,7 @@ [_ dotlrn-ecommerce.lt_User_has_submitted_an] } - } + } \ assessment_result { label "[_ dotlrn-ecommerce.Application]" display_template { @@ -218,12 +224,12 @@ } html { align center } - } + } \ phone { label "[_ dotlrn-ecommerce.Phone_Number]" hide_p {[ad_decode $_type "waitinglist approved" 0 "request approved" 0 "application approved" 0 "all" 0 1]} - } -} + } \ + ] if {[parameter::get -parameter AllowApplicationNotes -default 1]} { lappend elements comments { @@ -238,7 +244,12 @@ comments_text_plain { label "[_ dotlrn-ecommerce.Notes]" hide_p {[ad_decode $csv_p 1 0 1]} - } + } \ + attendance {label "Attendance" + aggregate "count" + aggregate_group_label "Num. Attendees" + } + } lappend elements \ @@ -347,6 +358,13 @@ # has_default_p 1 #} + +if {[info exists groupby]} { + lappend actions "Ungroup" applications "Stop grouping applications by section name" +} else { + lappend actions "Group by Section Name" [export_vars -base applications {{groupby section_name}}] "Group applications by section name" +} + # HAM : # this exports the current page lappend actions \ @@ -454,14 +472,17 @@ member_state { label "[_ dotlrn-ecommerce.Member_Request]" } - } + } -groupby { + label "Group By" + values {section_name {{groupby section_id } {orderby section_id}}} + } set page_clause [template::list::page_where_clause -and -key r.rel_id -name applications] } set csv_session_ids [list] -db_multirow -extend { unique_id approve_url reject_url asm_url section_edit_url person_url register_url comments comments_text_plain comments_truncate add_comment_url target calendar_id item_type_id num_sessions } applications applications [subst { }] { +db_multirow -extend { unique_id approve_url reject_url asm_url section_edit_url person_url register_url comments comments_text_plain comments_truncate add_comment_url target calendar_id item_type_id num_sessions attendance_rate} applications applications [subst { }] { set unique_id "${applicant_user_id}-${section_id}" set list_type [ad_decode $member_state "needs approval" full "request approval" prereq "application sent" payment full] @@ -511,6 +532,11 @@ if {$session_id ne ""} { lappend csv_session_ids $session_id } + + # attendance data + if { $num_sessions == 0 } { set attendance_rate "0" } else { set attendance_rate [format "% .0f" [expr (${attendance}.0/$num_sessions)*100]] } + + } # HAM : # unset section id because it seems the last section_id @@ -546,7 +572,7 @@ lappend csv_cols "attendance" "num_sessions" set csv_cols_labels(attendance) attendance set csv_cols_labels(num_sessions) "number of sessions" - template::multirow extend applications attendance num_sessions +# template::multirow extend applications attendance num_sessions set csv_as_item_list [list] set assessment_rev_id_list [list] set item_list [list] @@ -625,10 +651,7 @@ } } # attendance data - set attendance [db_string "count" "select count(user_id) from attendance_cal_item_map where user_id = :applicant_user_id and cal_item_id in (select cal_item_id from cal_items where on_which_calendar = :calendar_id and item_type_id = :item_type_id )" ] if { $num_sessions == 0 } { set attendance_rate "0" } else { set attendance_rate [format "% .0f" [expr (${attendance}.0/$num_sessions)*100]] } - set attendance "${attendance}" - } @@ -663,4 +686,4 @@ ns_return 200 "text/x-csv" $__output ad_script_abort -} \ No newline at end of file +}