Index: openacs-4/packages/ecommerce/www/admin/customer-service/issues-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issues-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/admin/customer-service/issues-oracle.xql 15 Jul 2001 20:30:12 -0000 1.1
@@ -0,0 +1,83 @@
+
+
+ oracle8.1.6
+
+
+
+
+ and sysdate-i.open_date <= 1
+
+
+
+
+
+
+ and sysdate-i.open_date <= 7
+
+
+
+
+
+ and months_between(sysdate,i.open_date) <= 1
+
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date
+ from ec_customer_service_issues i, cc_users u, ec_user_identification id
+ where i.user_identification_id = id.user_identification_id
+ and id.user_id = u.user_id(+)
+ and 0 = (select count(*) from ec_cs_issue_type_map m where m.issue_id=i.issue_id)
+ $open_date_query_bit $status_query_bit
+ order by i.issue_id desc
+
+
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
+ m.issue_type
+ from ec_customer_service_issues i, cc_users u, ec_user_identification id, ec_cs_issue_type_map m
+ where i.user_identification_id = id.user_identification_id
+ and id.user_id = u.user_id (+)
+ and i.issue_id = m.issue_id
+ $open_date_query_bit $status_query_bit
+ $issue_type_query_bit
+ order by $order_by_sql
+
+
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
+ m.issue_type
+ from ec_customer_service_issues i, cc_users u, ec_user_identification id, ec_cs_issue_type_map m
+ where i.user_identification_id = id.user_identification_id
+ and id.user_id = u.user_id (+)
+ and i.issue_id = m.issue_id
+ and m.issue_type=:view_issue_type
+ $open_date_query_bit $status_query_bit
+ order by $order_by_sql
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/admin/customer-service/issues-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issues-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/www/admin/customer-service/issues-postgresql.xql 15 Jul 2001 20:30:12 -0000 1.1
@@ -0,0 +1,86 @@
+
+
+ postgresql7.1
+
+
+
+
+ and now()-i.open_date <= 1
+
+
+
+
+
+
+ and now()-i.open_date <= 7
+
+
+
+
+
+ and extract(month from now()) - extract(month from i.open_date) <= 1
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date
+ from ec_customer_service_issues i
+ JOIN ec_user_identification id using (user_identification_id)
+ LEFT JOIN cc_users u on (id.user_id = u.user_id)
+ where 0 = (select count(*) from ec_cs_issue_type_map m where m.issue_id=i.issue_id)
+ $open_date_query_bit $status_query_bit
+ order by i.issue_id desc
+
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
+ m.issue_type
+ from ec_cs_issue_type_map m, ec_customer_service_issues i
+ JOIN ec_user_identification id using (user_identification_id)
+ LEFT JOIN cc_users u on (id.user_id = u.user_id)
+ -- gilbertw
+ -- removed this and used a normal where contraint so I dont have to
+ -- worry about setting up the correct and/where statement for the
+ -- dynamic queries
+ -- JOIN ec_cs_issue_type_map m on (i.issue_id = m.issue_id)
+ where i.issue_id = m.issue_id
+ $open_date_query_bit $status_query_bit
+ $issue_type_query_bit
+ order by $order_by_sql
+
+
+
+
+
+
+
+ select i.issue_id, u.user_id, u.first_names as users_first_names,
+ u.last_name as users_last_name, id.user_identification_id, i.order_id,
+ to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
+ to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
+ m.issue_type
+ from ec_customer_service_issues i
+ JOIN ec_user_identification id using (user_identification_id)
+ LEFT JOIN cc_users u on (id.user_id = u.user_id)
+ JOIN ec_cs_issue_type_map m on (i.issue_id = m.issue_id)
+ where m.issue_type=:view_issue_type
+ $open_date_query_bit $status_query_bit
+ order by $order_by_sql
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/www/admin/customer-service/issues.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/issues.tcl,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/ecommerce/www/admin/customer-service/issues.tcl 20 Apr 2001 20:51:14 -0000 1.1
+++ openacs-4/packages/ecommerce/www/admin/customer-service/issues.tcl 15 Jul 2001 20:30:12 -0000 1.2
@@ -24,11 +24,34 @@
set view_open_date "all"
}
if { ![info exists order_by] } {
- set order_by "i.issue_id"
+ set order_by "issue_id"
+ set order_by_sql "i.issue_id"
+} else {
+ switch $order_by {
+ "issue_id" {
+ set order_by_sql "i.issue_id"
+ }
+ "open_date" {
+ set order_by_sql "i.open_date"
+ }
+ "close_date" {
+ set order_by_sql "i.close_date"
+ }
+ "customer" {
+ set order_by_sql "u.last_name, u.first_names"
+ }
+ "order_id" {
+ set order_by_sql "i.order_id"
+ }
+ "issue_type" {
+ set order_by_sql "m.issue_type"
+ }
+ default {
+ set order_by_sql "i.issue_id"
+ }
+ }
}
-
-
append doc_body "[ad_admin_header "Customer Service Issues"]
Customer Service Issues
@@ -113,28 +136,18 @@
}
if { $view_open_date == "last_24" } {
- set open_date_query_bit "and sysdate-i.open_date <= 1"
+ set open_date_query_bit [db_map last_24]
} elseif { $view_open_date == "last_week" } {
- set open_date_query_bit "and sysdate-i.open_date <= 7"
+ set open_date_query_bit [db_map last_week]
} elseif { $view_open_date == "last_month" } {
- set open_date_query_bit "and months_between(sysdate,i.open_date) <= 1"
+ set open_date_query_bit [db_map last_month]
} else {
set open_date_query_bit ""
}
if { $view_issue_type == "uncategorized" } {
- set sql_query "select i.issue_id, u.user_id, u.first_names as users_first_names,
- u.last_name as users_last_name, id.user_identification_id, i.order_id,
- to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
- to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date
- from ec_customer_service_issues i, cc_users u, ec_user_identification id
- where i.user_identification_id = id.user_identification_id
- and id.user_id = u.user_id(+)
- and 0 = (select count(*) from ec_cs_issue_type_map m where m.issue_id=i.issue_id)
- $open_date_query_bit $status_query_bit
- order by i.issue_id desc
- "
+ set sql_query [db_map uncategorized]
} elseif { $view_issue_type == "all others" } {
@@ -145,48 +158,23 @@
} else {
set issue_type_query_bit ""
}
+ set sql_query [db_map all_others]
- set sql_query "select i.issue_id, u.user_id, u.first_names as users_first_names,
- u.last_name as users_last_name, id.user_identification_id, i.order_id,
- to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
- to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
- m.issue_type
- from ec_customer_service_issues i, cc_users u, ec_user_identification id, ec_cs_issue_type_map m
- where i.user_identification_id = id.user_identification_id
- and id.user_id = u.user_id (+)
- and i.issue_id = m.issue_id
- $open_date_query_bit $status_query_bit
- $issue_type_query_bit
- order by :order_by
- "
-
} else {
- set sql_query "select i.issue_id, u.user_id, u.first_names as users_first_names,
- u.last_name as users_last_name, id.user_identification_id, i.order_id,
- to_char(open_date,'YYYY-MM-DD HH24:MI:SS') as full_open_date,
- to_char(close_date,'YYYY-MM-DD HH24:MI:SS') as full_close_date,
- m.issue_type
- from ec_customer_service_issues i, cc_users u, ec_user_identification id, ec_cs_issue_type_map m
- where i.user_identification_id = id.user_identification_id
- and id.user_id = u.user_id (+)
- and i.issue_id = m.issue_id
- and m.issue_type=:view_issue_type
- $open_date_query_bit $status_query_bit
- order by :order_by
- "
+ set sql_query [db_map default_query]
}
set link_beginning "issues.tcl?[export_url_vars view_issue_type view_status view_open_date]"
set table_header "