Index: openacs-4/packages/ecommerce/www/admin/customer-service/actions-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/actions-oracle.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/admin/customer-service/actions-oracle.xql	30 Aug 2001 06:50:22 -0000	1.1
@@ -0,0 +1,105 @@
+<?xml version="1.0"?>
+
+<queryset>
+   <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
+
+<fullquery name="none_sql">
+      <querytext>
+
+select a.action_id, a.interaction_id, a.issue_id,
+           i.interaction_date, i.customer_service_rep, i.interaction_originator, i.interaction_type,
+           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+           i.user_identification_id, customer_info.user_id as customer_user_id,
+           customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+      from ec_customer_service_actions a, ec_customer_serv_interactions i, cc_users reps,
+           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+              from ec_user_identification id, cc_users u2
+             where id.user_id=u2.user_id(+)) customer_info
+     where a.interaction_id = i.interaction_id
+       and i.user_identification_id=customer_info.user_identification_id
+       and i.customer_service_rep=reps.user_id(+)
+       and 0 = (select count(*) from ec_cs_action_info_used_map map where map.action_id=a.action_id)
+           $interaction_date_query_bit $rep_query_bit
+  order by $order_by
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="all_others_sql">
+      <querytext>
+
+      select a.action_id, a.interaction_id, a.issue_id,
+           i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+           customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+      from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
+           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+              from ec_user_identification id, cc_users u2
+             where id.user_id=u2.user_id(+)) customer_info
+     where a.interaction_id = i.interaction_id
+       and i.user_identification_id=customer_info.user_identification_id
+       and a.action_id=map.action_id
+       and i.customer_service_rep=reps.user_id(+) $info_used_query_bit
+           $interaction_date_query_bit $rep_query_bit
+  order by $order_by
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="default_sql">
+      <querytext>
+
+      select a.action_id, a.interaction_id, a.issue_id,
+             i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+             to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+             reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+             customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+       from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
+            (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+               from ec_user_identification id, cc_users u2
+              where id.user_id=u2.user_id(+)) customer_info
+      where a.interaction_id = i.interaction_id
+        and i.user_identification_id=customer_info.user_identification_id
+        and reps.user_id=i.customer_service_rep
+        and a.action_id=map.action_id
+        and map.info_used=:view_info_used
+           $interaction_date_query_bit $rep_query_bit
+  order by $order_by
+
+
+      </querytext>
+</fullquery>
+
+
+<partialquery name="last_24">
+      <querytext>
+
+      and sysdate-i.interaction_date <= 1
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_week">
+      <querytext>
+
+      and sysdate-i.interaction_date <= 7
+
+      </querytext>
+</partialquery>
+      
+
+<partialquery name="last_month">
+      <querytext>
+
+      and months_between(sysdate,i.interaction_date) <= 1
+
+      </querytext>
+</partialquery>
+
+ 
+</queryset>
Index: openacs-4/packages/ecommerce/www/admin/customer-service/actions-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/actions-postgresql.xql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/admin/customer-service/actions-postgresql.xql	30 Aug 2001 06:50:22 -0000	1.1
@@ -0,0 +1,106 @@
+<?xml version="1.0"?>
+
+<queryset>
+   <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+
+<fullquery name="none_sql">
+      <querytext>
+
+      select a.action_id, a.interaction_id, a.issue_id,
+      i.interaction_date, i.customer_service_rep, i.interaction_originator, i.interaction_type,
+      to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+      reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+      i.user_identification_id, customer_info.user_id as customer_user_id,
+      customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+      from ec_customer_service_actions a, ec_customer_serv_interactions i
+          LEFT JOIN cc_users reps on (i.customer_service_rep=reps.user_id),
+      (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+              from ec_user_identification id LEFT JOIN cc_users u2
+              on (id.user_id=u2.user_id)) customer_info
+      where a.interaction_id = i.interaction_id
+      and i.user_identification_id=customer_info.user_identification_id
+      and 0 = (select count(*) from ec_cs_action_info_used_map map where map.action_id=a.action_id)
+      $interaction_date_query_bit $rep_query_bit
+      order by $order_by
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="all_others_sql">
+      <querytext>
+
+      select a.action_id, a.interaction_id, a.issue_id,
+      i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+      to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+      reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+      customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+      from ec_customer_service_actions a, ec_customer_serv_interactions i
+          LEFT JOIN cc_users reps
+             on (i.customer_service_rep=reps.user_id), 
+      ec_cs_action_info_used_map map,
+      (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+          from ec_user_identification id LEFT JOIN cc_users u2
+          on (id.user_id=u2.user_id)) customer_info
+     where a.interaction_id = i.interaction_id
+     and i.user_identification_id=customer_info.user_identification_id
+     and a.action_id=map.action_id
+     $info_used_query_bit
+     $interaction_date_query_bit $rep_query_bit
+     order by $order_by
+
+      </querytext>
+</fullquery>
+
+
+<fullquery name="default_sql">
+      <querytext>
+
+      select a.action_id, a.interaction_id, a.issue_id,
+      i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+      to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+      reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+      customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+      from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
+      (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+          from ec_user_identification id LEFT JOIN cc_users u2
+          on (id.user_id=u2.user_id)) customer_info
+      where a.interaction_id = i.interaction_id
+      and i.user_identification_id=customer_info.user_identification_id
+      and reps.user_id=i.customer_service_rep
+      and a.action_id=map.action_id
+      and map.info_used=:view_info_used
+      $interaction_date_query_bit $rep_query_bit
+      order by $order_by
+
+      </querytext>
+</fullquery>
+
+
+<partialquery name="last_24">
+      <querytext>
+
+      and now()-i.interaction_date <= timespan_days(1)
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_week">
+      <querytext>
+
+      and now()-i.interaction_date <= timespan_days(7)
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_month">
+      <querytext>
+
+      and now()-i.interaction_date <= '1 month'::interval
+
+      </querytext>
+</partialquery>
+
+</queryset>
Index: openacs-4/packages/ecommerce/www/admin/customer-service/actions.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/actions.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/ecommerce/www/admin/customer-service/actions.tcl	20 Apr 2001 20:51:14 -0000	1.1
+++ openacs-4/packages/ecommerce/www/admin/customer-service/actions.tcl	30 Aug 2001 06:50:22 -0000	1.2
@@ -80,13 +80,14 @@
 <option value=\"all\">All
 "
 
-set sql "
-  select i.customer_service_rep as rep, u.first_names as rep_first_names, u.last_name as rep_last_name
-    from ec_customer_serv_interactions i, cc_users u
-   where i.customer_service_rep=u.user_id
-group by i.customer_service_rep, u.first_names, u.last_name
-order by u.last_name, u.first_names
-"
+set sql [db_map get_customer_service_data_sql]
+#set sql "
+#  select i.customer_service_rep as rep, u.first_names as rep_first_names, u.last_name as rep_last_name
+#    from ec_customer_serv_interactions i, cc_users u
+#   where i.customer_service_rep=u.user_id
+#group by i.customer_service_rep, u.first_names, u.last_name
+#order by u.last_name, u.first_names
+#"
 
 db_foreach get_customer_service_data $sql {
     
@@ -129,34 +130,38 @@
 }
 
 if { $view_interaction_date == "last_24" } {
-    set interaction_date_query_bit "and sysdate-i.interaction_date <= 1"
+    #set interaction_date_query_bit "and sysdate-i.interaction_date <= 1"
+    set interaction_date_query_bit [db_map last_24]
 } elseif { $view_interaction_date == "last_week" } {
-    set interaction_date_query_bit "and sysdate-i.interaction_date <= 7"
+    #set interaction_date_query_bit "and sysdate-i.interaction_date <= 7"
+    set interaction_date_query_bit [db_map last_week]
 } elseif { $view_interaction_date == "last_month" } {
-    set interaction_date_query_bit "and months_between(sysdate,i.interaction_date) <= 1"
+    #set interaction_date_query_bit "and months_between(sysdate,i.interaction_date) <= 1"
+    set interaction_date_query_bit [db_map last_month]
 } else {
     set interaction_date_query_bit ""
 }
 
 if { $view_info_used == "none" } {
-    set sql_query "
-    select a.action_id, a.interaction_id, a.issue_id,
-           i.interaction_date, i.customer_service_rep, i.interaction_originator, i.interaction_type,
-           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
-           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
-           i.user_identification_id, customer_info.user_id as customer_user_id,
-           customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
-      from ec_customer_service_actions a, ec_customer_serv_interactions i, cc_users reps,
-           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
-              from ec_user_identification id, cc_users u2
-             where id.user_id=u2.user_id(+)) customer_info
-     where a.interaction_id = i.interaction_id
-       and i.user_identification_id=customer_info.user_identification_id
-       and i.customer_service_rep=reps.user_id(+)
-       and 0 = (select count(*) from ec_cs_action_info_used_map map where map.action_id=a.action_id)
-           $interaction_date_query_bit $rep_query_bit
-  order by $order_by
-    "
+     set sql_query [db_map none_sql]
+#    set sql_query "
+#    select a.action_id, a.interaction_id, a.issue_id,
+#           i.interaction_date, i.customer_service_rep, i.interaction_originator, i.interaction_type,
+#           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+#           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+#           i.user_identification_id, customer_info.user_id as customer_user_id,
+#           customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+#      from ec_customer_service_actions a, ec_customer_serv_interactions i, cc_users reps,
+#           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+#              from ec_user_identification id, cc_users u2
+#             where id.user_id=u2.user_id(+)) customer_info
+#     where a.interaction_id = i.interaction_id
+#       and i.user_identification_id=customer_info.user_identification_id
+#       and i.customer_service_rep=reps.user_id(+)
+#       and 0 = (select count(*) from ec_cs_action_info_used_map map where map.action_id=a.action_id)
+#           $interaction_date_query_bit $rep_query_bit
+#  order by $order_by
+#    "
 } elseif { $view_info_used == "all others" } {
     if { [llength $important_info_used_list] > 0 } {
 	set safe_important_info_used_list [DoubleApos $important_info_used_list]
@@ -165,43 +170,45 @@
 	set info_used_query_bit ""
     }
 
-    set sql_query "
-    select a.action_id, a.interaction_id, a.issue_id,
-           i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
-           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
-           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
-           customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
-      from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
-           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
-              from ec_user_identification id, cc_users u2
-             where id.user_id=u2.user_id(+)) customer_info
-     where a.interaction_id = i.interaction_id
-       and i.user_identification_id=customer_info.user_identification_id
-       and a.action_id=map.action_id
-       and i.customer_service_rep=reps.user_id(+) $info_used_query_bit
-           $interaction_date_query_bit $rep_query_bit
-  order by $order_by
-    "
+    set sql_query [db_map all_others_sql]
+#    set sql_query "
+#    select a.action_id, a.interaction_id, a.issue_id,
+#           i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+#           to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+#           reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+#           customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+#      from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
+#           (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+#              from ec_user_identification id, cc_users u2
+#             where id.user_id=u2.user_id(+)) customer_info
+#     where a.interaction_id = i.interaction_id
+#       and i.user_identification_id=customer_info.user_identification_id
+#       and a.action_id=map.action_id
+#       and i.customer_service_rep=reps.user_id(+) $info_used_query_bit
+#           $interaction_date_query_bit $rep_query_bit
+#  order by $order_by
+#    "
 } else {
 
-    set sql_query "
-      select a.action_id, a.interaction_id, a.issue_id,
-             i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
-             to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
-             reps.first_names as rep_first_names, reps.last_name as rep_last_name,
-             customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
-       from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
-            (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
-               from ec_user_identification id, cc_users u2
-              where id.user_id=u2.user_id(+)) customer_info
-      where a.interaction_id = i.interaction_id
-        and i.user_identification_id=customer_info.user_identification_id
-        and reps.user_id=i.customer_service_rep
-        and a.action_id=map.action_id
-        and map.info_used=:view_info_used
-           $interaction_date_query_bit $rep_query_bit
-  order by $order_by
-    "
+    set sql_query [db_map default_sql]
+#    set sql_query "
+#      select a.action_id, a.interaction_id, a.issue_id,
+#             i.interaction_date, i.customer_service_rep, i.user_identification_id, i.interaction_originator, i.interaction_type,
+#             to_char(i.interaction_date,'YYYY-MM-DD HH24:MI:SS') as full_interaction_date,
+#             reps.first_names as rep_first_names, reps.last_name as rep_last_name,
+#             customer_info.user_id as customer_user_id, customer_info.first_names as customer_first_names, customer_info.last_name as customer_last_name
+#       from ec_customer_service_actions a, ec_customer_serv_interactions i, ec_cs_action_info_used_map map, cc_users reps,
+#            (select id.user_identification_id, id.user_id, u2.first_names, u2.last_name
+#               from ec_user_identification id, cc_users u2
+#              where id.user_id=u2.user_id(+)) customer_info
+#      where a.interaction_id = i.interaction_id
+#        and i.user_identification_id=customer_info.user_identification_id
+#        and reps.user_id=i.customer_service_rep
+#        and a.action_id=map.action_id
+#        and map.info_used=:view_info_used
+#           $interaction_date_query_bit $rep_query_bit
+#  order by $order_by
+#    "
 }
 
 # set link_beginning "actions.tcl?[export_url_vars view_issue_type view_status view_open_date]"
Index: openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql	10 Jul 2001 20:33:53 -0000	1.1
+++ openacs-4/packages/ecommerce/www/admin/customer-service/actions.xql	30 Aug 2001 06:50:22 -0000	1.2
@@ -12,5 +12,20 @@
       </querytext>
 </fullquery>
 
+
+<fullquery name="get_customer_service_data_sql">
+      <querytext>
+
+select i.customer_service_rep as rep, u.first_names as rep_first_names, u.last_name as rep_last_name
+    from ec_customer_serv_interactions i, cc_users u
+   where i.customer_service_rep=u.user_id
+group by i.customer_service_rep, u.first_names, u.last_name
+order by u.last_name, u.first_names
+
+      </querytext>
+</fullquery>
+
+
+
  
 </queryset>
Index: openacs-4/packages/ecommerce/www/admin/customer-service/interactions-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interactions-oracle.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/ecommerce/www/admin/customer-service/interactions-oracle.xql	30 Aug 2001 06:21:40 -0000	1.1
+++ openacs-4/packages/ecommerce/www/admin/customer-service/interactions-oracle.xql	30 Aug 2001 06:50:22 -0000	1.2
@@ -1,8 +1,9 @@
 <?xml version="1.0"?>
 
 <queryset>
-   <rdbms><type>postgresql</type><version>7.1</version></rdbms>
+   <rdbms><type>oracle</type><version>8.1.6</version></rdbms>
 
+
 <fullquery name="get_interation_originator_list">
       <querytext>
       
@@ -25,5 +26,32 @@
       </querytext>
 </fullquery>
 
+
+<partialquery name="last_24">
+      <querytext>
+
+      and sysdate-i.interaction_date <= 1
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_week">
+      <querytext>
+
+      and sysdate-i.interaction_date <= 7
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_month">
+      <querytext>
+
+      and months_between(sysdate,i.interaction_date) <= 1
+
+      </querytext>
+</partialquery>
+
  
 </queryset>
Index: openacs-4/packages/ecommerce/www/admin/customer-service/interactions-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interactions-postgresql.xql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/ecommerce/www/admin/customer-service/interactions-postgresql.xql	30 Aug 2001 06:21:40 -0000	1.1
+++ openacs-4/packages/ecommerce/www/admin/customer-service/interactions-postgresql.xql	30 Aug 2001 06:50:22 -0000	1.2
@@ -36,4 +36,31 @@
 </fullquery>
 
 
+<partialquery name="last_24">
+      <querytext>
+
+      and now()-i.interaction_date <= timespan_days(1)
+
+      </querytext>
+</partialquery>
+
+
+<partialquery name="last_week">
+      <querytext>
+
+      and now()-i.interaction_date <= timespan_days(7)
+
+      </querytext>
+</partialquery>
+      
+
+<partialquery name="last_month">
+      <querytext>
+
+      and now()-i.interaction_date <= '1 month'::interval
+
+      </querytext>
+</partialquery>
+
+
 </queryset>
Index: openacs-4/packages/ecommerce/www/admin/customer-service/interactions.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/interactions.tcl,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/ecommerce/www/admin/customer-service/interactions.tcl	30 Aug 2001 06:21:40 -0000	1.2
+++ openacs-4/packages/ecommerce/www/admin/customer-service/interactions.tcl	30 Aug 2001 06:50:22 -0000	1.3
@@ -152,11 +152,14 @@
 }
 
 if { $view_interaction_date == "last_24" } {
-    set interaction_date_query_bit "and sysdate-i.interaction_date <= 1"
+    #set interaction_date_query_bit "and sysdate-i.interaction_date <= 1"
+    set interaction_date_query_bit [db_map last_24]
 } elseif { $view_interaction_date == "last_week" } {
-    set interaction_date_query_bit "and sysdate-i.interaction_date <= 7"
+    #set interaction_date_query_bit "and sysdate-i.interaction_date <= 7"
+    set interaction_date_query_bit [db_map last_week]
 } elseif { $view_interaction_date == "last_month" } {
-    set interaction_date_query_bit "and months_between(sysdate,i.interaction_date) <= 1"
+    #set interaction_date_query_bit "and months_between(sysdate,i.interaction_date) <= 1"
+    set interaction_date_query_bit [db_map last_month]
 } else {
     set interaction_date_query_bit ""
 }