Index: openacs-4/packages/assessment/www/asm-admin/send-mail-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/www/asm-admin/send-mail-oracle.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/assessment/www/asm-admin/send-mail-oracle.xql	1 Feb 2005 22:00:10 -0000	1.1
+++ openacs-4/packages/assessment/www/asm-admin/send-mail-oracle.xql	7 Oct 2007 22:36:54 -0000	1.2
@@ -2,45 +2,51 @@
 <queryset>
     <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
 
-    <partialquery name="dotlrn_all">
+
+    <partialquery name="all">
 	<querytext>
             select '[db_quote $sender_email]' as from_addr,
                '[db_quote $sender_first_names]' as sender_first_names,
                '[db_quote $sender_last_name]' as sender_last_name,
                parties.email,
-               decode(acs_objects.object_type,
-                      'user',
-                      (select first_names
-                       from persons
-                       where person_id = parties.party_id),
-                      'group',
-                      (select group_name
-                       from groups
-                       where group_id = parties.party_id),
-                      'rel_segment',
-                      (select segment_name
-                       from rel_segments
-                       where segment_id = parties.party_id),
-                      '') as first_names,
-               decode(acs_objects.object_type,
-                      'user',
-                      (select last_name
-                       from persons
-                       where person_id = parties.party_id),
-                      '') as last_name,
-               '[db_quote $community_name]' as community_name,
-               '[db_quote $community_url]' as community_url
-            from party_approved_member_map,
+            decode(acs_objects.object_type,
+            'user',
+            (select first_names
+            from persons
+            where person_id = parties.party_id),
+            'group',
+            (select group_name
+            from groups
+            where group_id = parties.party_id),
+            'rel_segment',
+            (select segment_name
+            from rel_segments
+            where segment_id = parties.party_id),
+            '') as first_names,
+            decode(acs_objects.object_type,
+            'user',
+            (select last_name
+            from persons
+            where person_id = parties.party_id),
+            '') as last_name
+            from
                  parties,
-                 acs_objects
-            where party_approved_member_map.party_id = $segment_id
-            and party_approved_member_map.member_id <> $segment_id
-            and party_approved_member_map.member_id = parties.party_id
+                 acs_objects,
+                 users
+            where
+            parties.party_id <> 0
+            and parties.party_id = users.user_id        
             and parties.party_id = acs_objects.object_id
+            and exists (select 1 from acs_object_party_privilege_map m
+                        where m.object_id = $assessment_id
+                        and m.party_id = parties.party_id
+                        and m.privilege = 'read')
+        
+               
 	</querytext>
     </partialquery>
 
-    <partialquery name="dotlrn_responded">
+    <partialquery name="responded">
 	<querytext>
  		select '[db_quote $sender_email]' as from_addr,
                '[db_quote $sender_first_names]' as sender_first_names,
@@ -65,15 +71,13 @@
                       (select last_name
                        from persons
                        where person_id = parties.party_id),
-                      '') as last_name,
-               '[db_quote $community_name]' as community_name,
-               '[db-quote $community_url]' as community_url
-            from party_approved_member_map,
+                      '') as last_name
+            from 
                  parties,
-                 acs_objects
-            where party_approved_member_map.party_id = $segment_id
-            and party_approved_member_map.member_id <> $segment_id
-            and party_approved_member_map.member_id = parties.party_id
+                 acs_objects,
+                 users
+            where
+            parties.party_id = users.user_id                 
             and parties.party_id = acs_objects.object_id
 	    and parties.party_id in (
 		select s.subject_id
@@ -84,7 +88,7 @@
 	</querytext>
     </partialquery>
 
-    <partialquery name="dotlrn_not_responded">
+    <partialquery name="not_responded">
 	<querytext>
 		select '[db_quote $sender_email]' as from_addr,
                '[db_quote $sender_first_names]' as sender_first_names,
@@ -109,22 +113,25 @@
                       (select last_name
                        from persons
                        where person_id = parties.party_id),
-                      '') as last_name,
-               '[db_quote $community_name]' as community_name,
-               '[db_quote $community_url]' as community_url
-            from party_approved_member_map,
+                      '') as last_name
+            from 
                  parties,
-                 acs_objects
-            where party_approved_member_map.party_id = $segment_id
-            and party_approved_member_map.member_id <> $segment_id
-            and party_approved_member_map.member_id = parties.party_id
+                 acs_objects,
+                 users 
+            where
+            parties.party_id <> 0
+            and parties.party_id = users.user_id
             and parties.party_id = acs_objects.object_id
 	    and parties.party_id not in (
 		select s.subject_id
 		from as_sessions s, cr_revisions r
 		where s.assessment_id = r.revision_id
 		and s.completed_datetime is not null
 		and r.item_id = $assessment_id)
+                and exists (select 1 from acs_object_party_privilege_map m
+                        where m.object_id = $assessment_id
+                        and m.party_id = parties.party_id
+                        and m.privilege = 'read')
 	</querytext>
     </partialquery>
 
@@ -133,10 +140,10 @@
  		select '[db_quote $sender_email]' as from_addr,
                '[db_quote $sender_first_names]' as sender_first_names,
                '[db_quote $sender_last_name]' as sender_last_name,
-               parties.email,
+               parties.email
             from parties
-            where parties.party_id = acs_objects.object_id
-	    and parties.party_id in (
+            where
+                parties.party_id in (
 		select s.subject_id
 		from as_sessions s, cr_revisions r
 		where s.assessment_id = r.revision_id
@@ -145,4 +152,15 @@
 	</querytext>
     </partialquery>
 
-</queryset>
\ No newline at end of file
+    <partialquery name="list_of_user_ids">
+	<querytext>
+ 		select '[db_quote $sender_email]' as from_addr,
+               '[db_quote $sender_first_names]' as sender_first_names,
+               '[db_quote $sender_last_name]' as sender_last_name,
+               parties.email
+            from parties
+            where
+                parties.party_id in ([template::util::tcl_to_sql_list $user_ids])
+                </querytext>
+        </partialquery>
+</queryset>