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 -N -r1.3 -r1.4
--- openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql	7 Mar 2007 16:26:28 -0000	1.3
+++ openacs-4/packages/dotlrn-ecommerce/www/applications-postgresql.xql	18 Jun 2007 14:23:46 -0000	1.4
@@ -40,7 +40,7 @@
 		    on (r.rel_id = m.rel_id)
                     left join 
          
-        (select count(*) as attendance, dca.community_id, a.user_id
+        (select count(*) as attendance, dca.community_id, a.user_id, dca.community_key as section_key
          from
          calendars c,
          cal_items ci,
@@ -63,20 +63,22 @@
          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
+         group by a.user_id, dca.community_id, dca.community_key 
          ) 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),
 		    dotlrn_catalog t,
 		    cr_items i,
-		    acs_objects o
+		    acs_objects o,
+                    dotlrn_communities_all dca
 		
 		    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
+		    and r.community_id = dca.community_id
 		    $member_state_clause
 		    $user_clause
 		    $section_clause
@@ -87,17 +89,7 @@
             
     <fullquery name="applications">
         <querytext>    
-		    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,
-			   acs_objects o
-			   where rr.rel_id = o.object_id
-			   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, a.calendar_id
-		
+		    select $select_columns 		
 		    from  dotlrn_member_rels_full r
 		    left join (select *
 			       from ec_addresses
@@ -113,7 +105,7 @@
 						    group by rel_id)) m
 		    on (r.rel_id = m.rel_id)
 left join
-        (select count(*) as attendance, dca.community_id, a.user_id, c.calendar_id
+        (select count(*) as attendance, dca.community_id, a.user_id, c.calendar_id, dca.community_key as section_key
          from
          attendance_cal_item_map a,
          calendars c,
@@ -136,7 +128,7 @@
          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
+         group by a.user_id, dca.community_id, c.calendar_id, dca.community_key
          ) a
 		
  on (a.user_id = r.user_id and a.community_id  = r.community_id),
@@ -145,17 +137,20 @@
 		    on (s.product_id = p.product_id),
 		    dotlrn_catalogi t,
 		    cr_items i,
-		    acs_objects o
+		    acs_objects o,
+                    dotlrn_communities_all dca
 		    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
+		    and r.community_id = dca.community_id
 		    $member_state_clause
 		    $user_clause
 		    $section_clause
 			$page_clause
 		    [template::list::filter_where_clauses -and -name applications]
 		    [template::list::orderby_clause -name applications -orderby]    
+	$groupby_clause
     	</querytext>
     </fullquery>
     
@@ -183,5 +178,45 @@
             order by o.creation_date
     	</querytext>
     </fullquery>
-            
+           
+<fullquery name="applications_session_ids">
+<querytext>
+		    select m.session_id
+		
+		    from dotlrn_member_rels_full r
+		    left join (select *
+			       from ec_addresses
+			       where address_id in (select max(address_id)
+						    from ec_addresses
+						    group by user_id)) e
+		    on (r.user_id = e.user_id)
+		    left join (select m.*, s.completed_datetime
+			       from dotlrn_ecommerce_application_assessment_map m, as_sessions s
+			       where m.session_id = s.session_id
+			       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), 
+		    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,
+                    dotlrn_communities_all dca
+		
+		    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
+		    and r.community_id = dca.community_id
+	            and m.session_id is not null
+--		    $member_state_clause
+		    $user_clause
+		    $section_clause
+		    [template::list::filter_where_clauses -and -name applications]
+		    [template::list::orderby_clause -name applications -orderby]    
+
+</querytext>
+</fullquery> 
 </queryset>
\ No newline at end of file