Index: openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,19 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ insert into $audit_table_name
+ ($id_column_join last_modified,
+ last_modifying_user, modified_ip_address, delete_p)
+ values ($id_column_bind_join sysdate,
+ :last_modifying_user, :modified_ip_address, :delete_p)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,19 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ insert into $audit_table_name
+ ($id_column_join last_modified,
+ last_modifying_user, modified_ip_address, delete_p)
+ values ($id_column_bind_join current_timestamp,
+ :last_modifying_user, :modified_ip_address, :delete_p)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ec-audit-trail-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,26 @@
+
+
+
+
+
+
+ select distinct $id_column
+ from $main_table_name
+ where [join $date_clause_list "\nand "]
+
+
+
+
+
+
+
+
+ select distinct $id_column
+ from $main_table_name
+ where [join $date_clause_list "\nand "]
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ec-style-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/Attic/ec-style-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ec-style-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,15 @@
+
+
+
+
+
+
+ select prefer_text_only_p, language_preference
+ from user_preferences
+ where user_id = :user_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,51 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select ec_order_cost(:order_id) as total_amount,
+ creditcard_id,
+ case when sign(sysdate - confirmed_date - .95) = -1 then 1 else 0 end as youth
+ from ec_orders
+ where order_id = :order_id
+
+
+
+
+
+
+
+
+ select transaction_amount as total_amount,
+ creditcard_id,
+ case when sign(sysdate-inserted_date-.95) = -1 then 1 else 0 end as youth
+ from ec_financial_transactions
+ where transaction_id = :transaction_id
+
+
+
+
+
+
+
+
+ insert into ec_cybercash_log ([join [ad_ns_set_keys -exclude "cc_time" $bind_vars] ", "], cc_time, txn_attempted_time)
+ values ([join [ad_ns_set_keys -exclude "cc_time" -colon $bind_vars] ", "], to_date(:cc_time, 'YYYYMMDDHH24MISS'), sysdate)
+
+
+
+
+
+
+
+
+ select to_char(:n_hours_to_add / 24 + to_date(:the_date, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDDHH24MISS') from dual
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,51 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select ec_order_cost(:order_id) as total_amount,
+ creditcard_id,
+ case when sign(current_timestamp - confirmed_date - .95) = -1 then 1 else 0 end as youth
+ from ec_orders
+ where order_id = :order_id
+
+
+
+
+
+
+
+
+ select transaction_amount as total_amount,
+ creditcard_id,
+ case when sign(current_timestamp-inserted_date-.95) = -1 then 1 else 0 end as youth
+ from ec_financial_transactions
+ where transaction_id = :transaction_id
+
+
+
+
+
+
+
+
+ insert into ec_cybercash_log ([join [ad_ns_set_keys -exclude "cc_time" $bind_vars] ", "], cc_time, txn_attempted_time)
+ values ([join [ad_ns_set_keys -exclude "cc_time" -colon $bind_vars] ", "], to_date(:cc_time, 'YYYYMMDDHH24MISS'), current_timestamp)
+
+
+
+
+
+
+
+
+ select to_char(:n_hours_to_add / 24 + to_date(:the_date, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDDHH24MISS')
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-credit-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,50 @@
+
+
+
+
+
+
+ select creditcard_number, creditcard_expire,
+ billing_zip_code
+ from ec_creditcards
+ where creditcard_id = :creditcard_id
+
+
+
+
+
+
+
+
+ select max(transaction_id) from ec_financial_transactions where order_id = :order_id
+
+
+
+
+
+
+
+
+ select transaction_amount from ec_financial_transactions where transaction_id = :transaction_id
+
+
+
+
+
+
+
+
+ select t.transaction_amount,
+ c.creditcard_number,
+ c.creditcard_expire,
+ c.billing_zip_code
+ from ec_financial_transactions t,
+ ec_creditcards c
+ where t.transaction_id = :transaction_id
+ and c.creditcard_id = t.creditcard_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,49 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_issue_id_sequence.NEXTVAL from dual
+
+
+
+
+
+
+
+ insert into ec_customer_serv_interactions
+ (interaction_id, customer_service_rep, user_identification_id, interaction_date, interaction_originator, interaction_type, interaction_headers)
+ values
+ (:interaction_id, :customer_service_rep, :user_identification_id, sysdate, :interaction_originator, :interaction_type, :interaction_headers)
+
+
+
+
+
+
+
+
+ insert into ec_customer_service_issues
+ (issue_id, user_identification_id, order_id, open_date, close_date, closed_by, gift_certificate_id)
+ values
+ (:issue_id, :user_identification_id, :order_id, sysdate, sysdate, :customer_service_rep, :gift_certificate_id)
+
+
+
+
+
+
+
+
+ insert into ec_customer_service_issues
+ (issue_id, user_identification_id, order_id, open_date, close_date, closed_by, gift_certificate_id)
+ values
+ (:issue_id, :user_identification_id, :order_id, sysdate, sysdate, :customer_service_rep, :gift_certificate_id)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,49 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_issue_id_sequence.NEXTVAL
+
+
+
+
+
+
+
+ insert into ec_customer_serv_interactions
+ (interaction_id, customer_service_rep, user_identification_id, interaction_date, interaction_originator, interaction_type, interaction_headers)
+ values
+ (:interaction_id, :customer_service_rep, :user_identification_id, current_timestamp, :interaction_originator, :interaction_type, :interaction_headers)
+
+
+
+
+
+
+
+
+ insert into ec_customer_service_issues
+ (issue_id, user_identification_id, order_id, open_date, close_date, closed_by, gift_certificate_id)
+ values
+ (:issue_id, :user_identification_id, :order_id, current_timestamp, current_timestamp, :customer_service_rep, :gift_certificate_id)
+
+
+
+
+
+
+
+
+ insert into ec_customer_service_issues
+ (issue_id, user_identification_id, order_id, open_date, close_date, closed_by, gift_certificate_id)
+ values
+ (:issue_id, :user_identification_id, :order_id, current_timestamp, current_timestamp, :customer_service_rep, :gift_certificate_id)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-customer-service-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,37 @@
+
+
+
+
+
+
+ select user_identification_id from ec_user_identification where user_id = :user_id
+
+
+
+
+
+
+
+
+ insert into ec_user_identification
+ (user_identification_id, user_id)
+ values
+ (:user_identification_id, :user_id)
+
+
+
+
+
+
+
+
+ insert into ec_cs_issue_type_map
+ (issue_id, issue_type)
+ values
+ (:issue_id, :issue_type)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,92 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select u.email,
+ to_char(confirmed_date,'MM/DD/YY') as confirmed_date,
+ shipping_address, u.user_id
+ from ec_orders, cc_users u
+ where ec_orders.user_id = u.user_id
+ and order_id = :order_id
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 1, :order_id, sysdate)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, sysdate)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, sysdate)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, sysdate)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, sysdate)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, sysdate)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-email-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,92 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select u.email,
+ to_char(confirmed_date,'MM/DD/YY') as confirmed_date,
+ shipping_address, u.user_id
+ from ec_orders, cc_users u
+ where ec_orders.user_id = u.user_id
+ and order_id = :order_id
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 1, :order_id, current_timestamp)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, current_timestamp)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, current_timestamp)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, current_timestamp)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, current_timestamp)
+
+
+
+
+
+
+
+
+ insert into ec_automatic_email_log
+ (user_identification_id, email_template_id, order_id, date_sent)
+ values
+ (:user_identification_id, 3, :order_id, current_timestamp)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-email-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-email-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-email-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,190 @@
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list
+ from ec_email_templates
+ where email_template_id = 1
+
+
+
+
+
+
+
+
+ select ep.email_on_purchase_list, ep.product_name
+ from ec_items ei, ec_products ep
+ where ei.product_id = ep.product_id
+ and ei.order_id = :order_id
+ group by ep.email_on_purchase_list, ep.product_name
+
+
+
+
+
+
+
+
+ select u.email, u.user_id
+ from ec_orders, cc_users u
+ where ec_orders.user_id = u.user_id
+ and order_id = :order_id
+
+
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list
+ from ec_email_templates
+ where email_template_id = 1
+
+
+
+
+
+
+
+
+ select u.email, u.user_id, s.shipment_date, s.address_id, o.order_state, o.order_id
+ from ec_orders o, cc_users u, ec_shipments s
+ where o.user_id = u.user_id
+ and o.order_id = s.order_id
+ and s.shipment_id = :shipment_id
+
+
+
+
+
+
+
+
+ select p.product_name, p.one_line_description, p.product_id, i.price_charged, i.price_name, count(*) as quantity
+ from ec_items i, ec_products p
+ where i.product_id=p.product_id
+ and i.shipment_id=:shipment_id
+ group by p.product_name, p.one_line_description, p.product_id, i.price_charged, i.price_name
+
+
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list
+ from ec_email_templates
+ where email_template_id = 1
+
+
+
+
+
+
+
+
+ select g.purchased_by as user_id, u.email, g.recipient_email, g.amount
+ from ec_gift_certificates g, cc_users u
+ where g.purchased_by=u.user_id
+ and g.gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list
+ from ec_email_templates
+ where email_template_id = 1
+
+
+
+
+
+
+
+
+ select g.purchased_by as user_id, u.email, g.recipient_email, g.amount, g.certificate_to, g.certificate_from, g.certificate_message
+ from ec_gift_certificates g, cc_users u
+ where g.purchased_by=u.user_id
+ and g.gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list
+ from ec_email_templates
+ where email_template_id = 1
+
+
+
+
+
+
+
+
+ select g.purchased_by as user_id, u.email, g.recipient_email, g.amount, g.certificate_to, g.certificate_from, g.certificate_message
+ from ec_gift_certificates g, cc_users u
+ where g.purchased_by=u.user_id
+ and g.gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+
+ select subject as email_subject, message as email_body, issue_type_list from ec_email_templates where email_template_id=5
+
+
+
+
+
+
+
+ select user_id
+ from cc_users
+ where upper(email)=upper(:email)
+
+
+
+
+
+
+
+ select user_identification_id from ec_user_identification where upper(email)=upper(:email)
+
+
+
+
+
+
+ select user_identification_id from ec_user_identification where upper(email)=upper(:email)
+
+
+
+
+
+
+
+ insert into ec_user_identification
+ (user_identification_id, email)
+ values
+ (:user_identification_id, :trimmed_email)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
+
+ select ec_order_gift_cert_amount(:order_id) from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
+
+ select ec_order_gift_cert_amount(:order_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-money-computations-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,120 @@
+
+
+
+
+
+ select price from ec_products where product_id=:product_id
+
+
+
+
+
+
+
+ select p.price, c.user_class_name
+ from ec_product_user_class_prices p, ec_user_classes c
+ where p.product_id=:product_id
+ and p.user_class_id=c.user_class_id
+ and p.user_class_id in (select m.user_class_id from ec_user_class_user_map m where m.user_id=:user_id $additional_user_class_restriction)
+
+
+
+
+
+
+
+ select sale_price, sale_name
+ from ec_sale_prices_current
+ where product_id=:product_id
+ and (offer_code is null $or_part_of_query)
+
+
+
+
+
+
+
+ select shipping, shipping_additional, weight from ec_products where product_id=:product_id
+
+
+
+
+
+
+ select default_shipping_per_item, weight_shipping_cost from ec_admin_settings
+
+
+
+
+
+
+ select min(item_id) from ec_items where product_id=:product_id and order_id:order_id
+
+
+
+
+
+
+ select add_exp_amount_per_item, add_exp_amount_by_weight from ec_admin_settings
+
+
+
+
+
+
+ select price from ec_products where product_id=:product_id
+
+
+
+
+
+
+ select p.price, c.user_class_name
+ from ec_product_user_class_prices p, ec_user_classes c
+ where p.product_id=:product_id
+ and p.user_class_id=c.user_class_id
+ and p.user_class_id in ([join $user_class_id_list ", "])
+
+
+
+
+
+
+ select sale_price, sale_name
+ from ec_sale_prices_current
+ where product_id=:product_id
+ and (offer_code is null $or_part_of_query)
+
+
+
+
+
+
+
+ select shipping, shipping_additional, weight from ec_products where product_id=:product_id
+
+
+
+
+
+
+ select min(item_id) from ec_items where product_id=:product_id and order_id=:order_id
+
+
+
+
+
+
+
+ select confirmed_date, user_id,
+ ec_total_price(:order_id) as total_price,
+ ec_total_shipping(:order_id) as total_shipping,
+ ec_total_tax(:order_id) as total_tax
+ from ec_orders
+ where order_id = :order_id
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,102 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select site_node.url(s.node_id)
+ from site_nodes s, apm_packages a
+ where s.object_id = a.package_id
+ and a.package_key = 'ecommerce'
+
+
+
+
+
+
+
+
+ select site_node.url(s.node_id)
+ from site_nodes s, apm_packages a
+ where s.object_id = a.package_id
+ and a.package_key = '$package_key'
+
+
+
+
+
+
+
+
+ select c.one_line_summary,
+ c.rating,
+ c.user_comment,
+ to_char(c.last_modified,'Day Month DD, YYYY') last_modified_pretty,
+ u.email,
+ u.user_id
+ from ec_product_comments c,
+ cc_users u
+ where c.user_id = u.user_id
+ and c.product_id = :product_id
+ $end_of_comment_query
+
+
+
+
+
+
+
+
+ select decode(sign(sysdate-available_date),1,1,null,1,0) as available_p,
+ color_list, size_list, style_list, no_shipping_avail_p
+ from ec_products
+ where product_id = :product_id
+
+
+
+
+
+
+
+
+ select to_char(available_date,'Month DD, YYYY') available_date
+ from ec_products
+ where product_id = :product_id
+
+
+
+
+
+
+
+
+ select eco.confirmed_date, eco.creditcard_id, eco.shipping_method,
+ u.email,
+ eca.line1, eca.line2, eca.city, eca.usps_abbrev, eca.zip_code, eca.country_code,
+ eca.full_state_name, eca.attn, eca.phone, eca.phone_time
+ from ec_orders eco,
+ cc_users u,
+ ec_addresses eca
+ where eco.order_id = :order_id
+ and eco.user_id = u.user_id(+)
+ and eco.shipping_address = eca.address_id(+)
+
+
+
+
+
+
+
+
+ insert into ec_user_sessions
+ (user_session_id, ip_address, start_time, http_user_agent)
+ values
+ (:user_session_id, :ip_address , sysdate, :http_user_agent)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,85 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select site_node__url(s.node_id)
+ from site_nodes s, apm_packages a
+ where s.object_id = a.package_id
+ and a.package_key = 'ecommerce'
+
+
+
+
+
+
+
+
+ select site_node__url(s.node_id)
+ from site_nodes s, apm_packages a
+ where s.object_id = a.package_id
+ and a.package_key = '$package_key'
+
+
+
+
+
+
+
+
+ select c.one_line_summary,
+ c.rating,
+ c.user_comment,
+ to_char(c.last_modified,'Day Month DD, YYYY') last_modified_pretty,
+ u.email,
+ u.user_id
+ from ec_product_comments c,
+ cc_users u
+ where c.user_id = u.user_id
+ and c.product_id = :product_id
+ $end_of_comment_query
+
+
+
+
+
+
+
+ FIX ME DECODE
+
+ select decode(sign(current_timestamp-available_date),1,1,null,1,0) as available_p,
+ color_list, size_list, style_list, no_shipping_avail_p
+ from ec_products
+ where product_id = :product_id
+
+
+
+
+
+
+
+
+ select to_char(available_date,'Month DD, YYYY') available_date
+ from ec_products
+ where product_id = :product_id
+
+
+
+
+
+
+
+
+ insert into ec_user_sessions
+ (user_session_id, ip_address, start_time, http_user_agent)
+ values
+ (:user_session_id, :ip_address , current_timestamp, :http_user_agent)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,417 @@
+
+
+
+
+
+
+ select package_id from apm_packages
+ where package_key = 'ecommerce'
+
+
+
+
+
+
+
+
+ select subcategory_id from ec_subcategories where category_id = :category_id and subcategory_id in ([join $subcategory_list ", "]) order by subcategory_name
+
+
+
+
+
+
+
+
+ select category_name from ec_categories where category_id = :category_id
+
+
+
+
+
+
+
+
+ select category_name from ec_categories where category_id = :category_id
+
+
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id = :subcategory_id
+
+
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subcategory_id = :subcategory_id and subsubcategory_id in ([join $subsubcategory_list ","]) order by subsubcategory_name
+
+
+
+
+
+
+
+
+ select product_name from ec_products where product_id = :product_id
+
+
+
+
+
+
+
+
+ select subcategory_id from ec_subsubcategories where subsubcategory_id = :subsubcategory_id
+
+
+
+
+
+
+
+
+ select category_id from ec_subcategories where subcategory_id = :subcategory_id
+
+
+
+
+
+
+
+ select category_name from ec_categories where category_id = :category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id = :subcategory_id
+
+
+
+
+
+
+ select subsubcategory_name from ec_subsubcategories where subsubcategory_id = :subsubcategory_id
+
+
+
+
+
+
+
+ select category_id from ec_subcategories where subcategory_id = :subcategory_id
+
+
+
+
+
+
+
+ select category_name from ec_categories where category_id = :category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id = :subcategory_id
+
+
+
+
+
+
+ select category_name from ec_categories where category_id = :category_id
+
+
+
+
+
+
+
+ select category_id from ec_category_product_map where product_id = :product_id
+
+
+
+
+
+
+
+
+ select s.subcategory_id
+ from ec_subcategory_product_map m,
+ ec_subcategories s
+ where m.subcategory_id = s.subcategory_id
+ and s.category_id = :category_id
+ and m.product_id = :product_id
+
+
+
+
+
+
+
+
+ select ss.subsubcategory_id
+ from ec_subsubcategory_product_map m,
+ ec_subsubcategories ss
+ where m.subsubcategory_id = ss.subsubcategory_id
+ and ss.subcategory_id = :subcategory_id
+ and m.product_id = :product_id
+
+
+
+
+
+
+
+
+ select p.product_id, p.product_name from ec_products_displayable p, ec_product_links l where l.product_a = :product_id and l.product_b = p.product_id
+
+
+
+
+
+
+
+
+ select publication, author_name, review_date, review from ec_product_reviews where product_id = :product_id and display_p = 't'
+
+
+
+
+
+
+
+
+ select avg(rating) from ec_product_comments where product_id = :product_id and approved_p = 't'
+
+
+
+
+
+
+
+
+ select count(*) from ec_product_comments where product_id = :product_id and (approved_p='t' [ec_decode [util_memoize {ad_parameter -package_id [ec_id] ProductCommentsNeedApprovalP ecommerce} [ec_cache_refresh]] "0" "or approved_p is null" ""])
+
+
+
+
+
+
+
+
+ select category_id, category_name from ec_categories order by sort_key
+
+
+
+
+
+
+
+
+ select user_id as correct_user_id from ec_orders where order_id = :order_id
+
+
+
+
+
+
+
+ FIX ME OUTER JOIN
+
+ select eco.confirmed_date, eco.creditcard_id, eco.shipping_method,
+ u.email,
+ eca.line1, eca.line2, eca.city, eca.usps_abbrev, eca.zip_code, eca.country_code,
+ eca.full_state_name, eca.attn, eca.phone, eca.phone_time
+ from ec_orders eco,
+ cc_users u,
+ ec_addresses eca
+ where eco.order_id = :order_id
+ and eco.user_id = u.user_id(+)
+ and eco.shipping_address = eca.address_id(+)
+
+
+
+
+
+
+
+
+ select i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice,
+ p.product_name, p.one_line_description, p.product_id,
+ count(*) as quantity
+ from ec_items i,
+ ec_products p
+ where i.order_id = :order_id
+ and i.product_id = p.product_id
+ group by p.product_name, p.one_line_description, p.product_id,
+ i.price_name, i.price_charged, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
+
+
+
+ select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice,
+ p.product_name, p.one_line_description, p.product_id,
+ count(*) as quantity
+ from ec_items i,
+ ec_products p
+ where i.order_id = :order_id
+ and i.product_id = p.product_id
+ group by p.product_name, p.one_line_description, p.product_id,
+ i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
+
+
+
+ select i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice,
+ p.product_name, p.one_line_description, p.product_id,
+ count(*) as quantity
+ from ec_items i,
+ ec_products p
+ where i.order_id = :order_id
+ and i.product_id = p.product_id
+ group by p.product_name, p.one_line_description, p.product_id,
+ i.price_charged, i.price_name, i.color_choice, i.size_choice, i.style_choice
+
+
+
+
+
+
+
+ select count(*) from $item_view where order_id = :order_id
+
+
+
+
+
+
+
+ select o.order_id, o.confirmed_date, o.order_state
+ from ec_orders o
+ where o.user_id = :user_id
+ order by o.order_id
+
+
+
+
+
+
+
+
+ select * from ec_product_purchase_comb where product_id = :product_id
+
+
+
+
+
+
+
+
+ select product_name from ec_products where product_id = :product_id
+
+
+
+
+
+
+
+
+ select product_name from ec_products where product_id = :product_id
+
+
+
+
+
+
+
+
+ select s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p, count(*) as n_items
+ from ec_items i,
+ ec_shipments s
+ where i.order_id = :order_id
+ and i.shipment_id = s.shipment_id
+ and i.product_id = :product_id
+ and i.color_choice [ec_decode $color_choice "" "is null" "= :color_choice"]
+ and i.size_choice [ec_decode $size_choice "" "is null" "= :size_choice"]
+ and i.style_choice [ec_decode $style_choice "" "is null" "= :style_choice"]
+ and i.price_charged [ec_decode $price_charged "" "is null" "= :price_charged"]
+ and i.price_name [ec_decode $price_name "" "is null" "= :price_name"]
+ group by s.shipment_date, s.carrier, s.tracking_number, s.shipment_id, s.shippable_p
+
+
+
+
+
+
+
+
+ select response_id, one_line, response_text
+ from ec_canned_responses
+ order by one_line
+
+
+
+
+
+
+
+
+ select c.user_class_name, m.user_class_approved_p, c.user_class_id
+ from ec_user_classes c, ec_user_class_user_map m
+ where m.user_id = :user_id
+ and m.user_class_id = c.user_class_id
+ order by c.user_class_id
+
+
+
+
+
+
+
+
+ update ec_user_sessions
+ set user_id = :user_id
+ where user_session_id = :user_session_id
+
+
+
+
+
+
+
+
+ select state_name from states where usps_abbrev =:usps_abbrev
+
+
+
+
+
+
+
+
+ select country_name from country_codes where iso=:country_code
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,246 @@
+
+
+
+ oracle8.1.6
+
+
+
+
+ select order_id, ec_order_cost(order_id) as total_order_price
+ from ec_orders
+ where order_state = 'confirmed'
+ and (sysdate - confirmed_date) > 1/96
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ select g.gift_certificate_id, t.transaction_id
+ from ec_gift_certificates g, ec_financial_transactions t
+ where g.gift_certificate_id=t.gift_certificate_id
+ and g.gift_certificate_state = 'confirmed'
+ and (sysdate - g.issue_date) > 1/96
+
+
+
+
+
+
+
+
+ update ec_financial_transactions
+ set authorized_date=sysdate,
+ to_be_captured_p='t'
+ where transaction_id = :transaction_id
+
+
+
+
+
+
+
+
+ update ec_gift_certificates
+ set authorized_date = sysdate,
+ gift_certificate_state = :cc_result
+ where gift_certificate_id = :gift_certificate_id
+
+
+
+
+
+
+
+ update ec_orders set order_state='expired', expired_date=sysdate where order_state='in_basket' and sysdate-in_basket_date > [util_memoize {ad_parameter -package_id [ec_id] CartDuration ecommerce} [ec_cache_refresh]]
+
+
+
+
+
+
+ update ec_items set item_state='expired', expired_date=sysdate where item_state='in_basket' and order_id in (select order_id from ec_orders where order_state='expired')
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where to_be_captured_p='t'
+ and sysdate-to_be_captured_date > 1/48
+ and authorized_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set marked_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set settled_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+ select case when sign(1 - ((sysdate-marked_date)/2)) = -1 then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where transaction_type='refund'
+ and sysdate - inserted_date > 1/48
+ and refunded_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+ update ec_financial_transactions set refunded_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set refunded_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_financial_transactions set refund_settled_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+ select case when sign(1 - ((sysdate-refunded_date)/2)) = -1 then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, sysdate, :problem_details, :order_id)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,246 @@
+
+
+
+ postgresql7.1
+
+
+
+
+ select order_id, ec_order_cost(order_id) as total_order_price
+ from ec_orders
+ where order_state = 'confirmed'
+ and (current_timestamp - confirmed_date) > 1/96
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ select g.gift_certificate_id, t.transaction_id
+ from ec_gift_certificates g, ec_financial_transactions t
+ where g.gift_certificate_id=t.gift_certificate_id
+ and g.gift_certificate_state = 'confirmed'
+ and (current_timestamp - g.issue_date) > 1/96
+
+
+
+
+
+
+
+
+ update ec_financial_transactions
+ set authorized_date=current_timestamp,
+ to_be_captured_p='t'
+ where transaction_id = :transaction_id
+
+
+
+
+
+
+
+
+ update ec_gift_certificates
+ set authorized_date = current_timestamp,
+ gift_certificate_state = :cc_result
+ where gift_certificate_id = :gift_certificate_id
+
+
+
+
+
+
+
+ update ec_orders set order_state='expired', expired_date=current_timestamp where order_state='in_basket' and current_timestamp-in_basket_date > [util_memoize {ad_parameter -package_id [ec_id] CartDuration ecommerce} [ec_cache_refresh]]
+
+
+
+
+
+
+ update ec_items set item_state='expired', expired_date=current_timestamp where item_state='in_basket' and order_id in (select order_id from ec_orders where order_state='expired')
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where to_be_captured_p='t'
+ and current_timestamp-to_be_captured_date > 1/48
+ and authorized_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set marked_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set settled_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+ select case when sign(1 - ((current_timestamp-marked_date)/2)) = -1 then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where transaction_type='refund'
+ and current_timestamp - inserted_date > 1/48
+ and refunded_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+ update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
+
+ update ec_financial_transactions set refunded_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_financial_transactions set refund_settled_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+ select case when sign(1 - ((current_timestamp-refunded_date)/2)) = -1 then 1 else 0 end from ec_financial_transactions where transaction_id=:transaction_id
+
+
+
+
+
+
+ insert into ec_problems_log
+ (problem_id, problem_date, problem_details, order_id)
+ values
+ (ec_problem_id_sequence.nextval, current_timestamp, :problem_details, :order_id)
+
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-scheduled-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,260 @@
+
+
+
+
+
+ select product_id from ec_products
+
+
+
+
+
+
+
+ select i2.product_id as correlated_product_id,
+ count(*) as n_product_occurrences
+ from ec_items i2
+ where i2.order_id in (select o2.order_id
+ from ec_orders o2
+ where o2.user_id in (select user_id
+ from ec_orders o
+ where o.order_id in (select i.order_id
+ from ec_items i
+ where product_id = :product_id)))
+ and i2.product_id <> :product_id
+ group by i2.product_id
+ order by n_product_occurrences desc
+
+
+
+
+
+
+
+ select count(*) from ec_product_purchase_comb where product_id=:product_id
+
+
+
+
+
+
+
+ insert into ec_product_purchase_comb
+ (product_id, [join $insert_cols ", "])
+ values
+ (:product_id, [join $insert_vals ", "])
+
+
+
+
+
+
+
+
+ update ec_product_purchase_comb
+ set [join $update_items ", "]
+ where product_id=:product_id
+
+
+
+
+
+
+
+
+ select max(transaction_id)
+ from ec_financial_transactions
+ where order_id = :order_id
+
+
+
+
+
+
+
+
+ select avs_code
+ from ec_cybercash_log
+ where transaction_id = :transaction_id
+ and txn_attempted_time = (select MAX(txn_attempted_time)
+ from ec_cybercash_log log2
+ where log2.transaction_id = :transaction_id)
+
+
+
+
+
+
+
+ update ec_orders set order_state=:new_order_state where order_id=:order_id
+
+
+
+
+
+
+ select transaction_id from ec_financial_transactions where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t', to_be_captured_p='f' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_gift_certificates set gift_certificate_state='failed_authorization' where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
+
+
+ select order_id
+ from ec_orders o
+ where (order_state='authorized_plus_avs' or order_state='authorized_minus_avs')
+ and (0=(select count(*) from ec_automatic_email_log log where log.order_id=o.order_id and email_template_id=1))
+
+
+
+
+
+
+
+
+ select gift_certificate_id
+ from ec_gift_certificates g
+ where (gift_certificate_state='authorized_plus_avs' or gift_certificate_state='authorized_minus_avs')
+ and (0=(select count(*) from ec_automatic_email_log log where log.gift_certificate_id=g.gift_certificate_id and email_template_id=4))
+
+
+
+
+
+
+
+
+ select gift_certificate_id
+ from ec_gift_certificates g
+ where (gift_certificate_state='authorized_plus_avs' or gift_certificate_state='authorized_minus_avs')
+ and (0=(select count(*) from ec_automatic_email_log log where log.gift_certificate_id=g.gift_certificate_id and email_template_id=5))
+
+
+
+
+
+
+
+ select order_id from ec_orders where order_state='failed_authorization'
+
+
+
+
+
+
+ update ec_orders set order_state='in_basket', saved_p='t' where order_id=:order_id
+
+
+
+
+
+
+
+ update ec_creditcards
+ set creditcard_number=null
+ where creditcard_id in (select unique c.creditcard_id
+ from ec_creditcards c, ec_orders o
+ where c.creditcard_id = o.creditcard_id
+ and c.creditcard_number is not null
+ and 0=(select count(*)
+ from ec_orders o2
+ where o2.creditcard_id=c.creditcard_id
+ and o2.order_state not in ('fulfilled','returned','void','expired')))
+
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where to_be_captured_p='t'
+ and authorized_date is not null
+ and marked_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t', to_be_captured_p='f' where transaction_id=:transaction_id
+
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where marked_date is not null
+ and settled_date is null
+ and failed_p='f'
+
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id
+
+
+
+
+
+
+ select transaction_id, order_id from ec_financial_transactions
+ where refunded_date is not null
+ and refund_settled_date is null
+ and failed_p='f'
+
+
+
+
+
+
+ update ec_financial_transactions set failed_p='t' where transaction_id=:transaction_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,112 @@
+
+
+
+ oracle8.1.6
+
+
+
+ declare begin ec_reinst_gift_cert_on_order (:order_id); end;
+
+
+
+
+
+
+ select ec_order_cost(:order_id) from dual
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=sysdate where transaction_id=:transaction_id
+
+
+
+
+
+
+ select ec_order_cost(:order_id) from dual
+
+
+
+
+
+
+ select ec_transaction_id_sequence.nextval from dual
+
+
+
+
+
+
+ insert into ec_financial_transactions
+ (transaction_id, order_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:transaction_id, :order_id, :total_amount, 'charge', sysdate)
+
+
+
+
+
+
+
+ select gift_certificate_id
+ from ec_gift_certificates_approved
+ where user_id=:user_id
+ and sysdate - expires < 0
+ and amount_remaining_p = 't'
+ order by expires
+
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
+
+ select ec_order_amount_owed(:order_id) from dual
+
+
+
+
+
+
+ select gift_certificate_amount_left(:gift_certificate_id) from dual
+
+
+
+
+
+
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_used, used_date)
+ VALUES
+ (:gift_certificate_id, :order_id, least(to_number(:gift_certificate_amount_left),
+ to_number(:amount_owed)), sysdate)
+
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
+
+ select ec_order_amount_owed(:order_id) from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,113 @@
+
+
+
+ postgresql7.1
+
+
+
+ FIX ME PLSQL
+declare begin ec_reinst_gift_cert_on_order (:order_id); end;
+
+
+
+
+
+
+ select ec_order_cost(:order_id)
+
+
+
+
+
+
+ update ec_financial_transactions set authorized_date=current_timestamp where transaction_id=:transaction_id
+
+
+
+
+
+
+ select ec_order_cost(:order_id)
+
+
+
+
+
+
+ select ec_transaction_id_sequence.nextval
+
+
+
+
+
+
+ insert into ec_financial_transactions
+ (transaction_id, order_id, transaction_amount, transaction_type, inserted_date)
+ values
+ (:transaction_id, :order_id, :total_amount, 'charge', current_timestamp)
+
+
+
+
+
+
+
+ select gift_certificate_id
+ from ec_gift_certificates_approved
+ where user_id=:user_id
+ and current_timestamp - expires < 0
+ and amount_remaining_p = 't'
+ order by expires
+
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
+
+ select ec_order_amount_owed(:order_id)
+
+
+
+
+
+
+ select gift_certificate_amount_left(:gift_certificate_id)
+
+
+
+
+
+
+ insert into ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_used, used_date)
+ VALUES
+ (:gift_certificate_id, :order_id, least(to_number(:gift_certificate_amount_left),
+ to_number(:amount_owed)), current_timestamp)
+
+
+
+
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
+
+ select ec_order_amount_owed(:order_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-state-changes-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,60 @@
+
+
+
+
+
+ select creditcard_id, user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ update ec_orders set order_state='in_basket', confirmed_date=null where order_id=:order_id
+
+
+
+
+
+
+ update ec_creditcards set failed_p='t' where creditcard_id=:creditcard_id
+
+
+
+
+
+
+ select max(transaction_id) from ec_financial_transactions where order_id=:order_id
+
+
+
+
+
+
+ update ec_orders set order_state='in_basket', confirmed_date=null where order_id=:order_id
+
+
+
+
+
+
+ update ec_items set item_state='to_be_shipped' where order_id=:order_id
+
+
+
+
+
+
+ select user_id from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ update ec_orders set order_state='in_basket', confirmed_date=null where order_id=:order_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-styles-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/Attic/ecommerce-styles-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-styles-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,18 @@
+
+
+
+
+
+ select category_id from ec_categories where category_name = :category
+
+
+
+
+
+
+ select category_id from ec_categories where category_name = :category
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select ec_gift_certificate_balance(:user_id) from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,13 @@
+
+
+
+ postgresql7.1
+
+
+
+ select ec_gift_certificate_balance(:user_id)
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-user-contributions-summary-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,22 @@
+
+
+
+
+
+ select address_id
+from ec_addresses where user_id = :user_id
+
+
+
+
+
+
+ select c.comment_id, p.product_name, comment_date
+ from ec_product_comments c, ec_products p
+ where c.product_id = p.product_id
+ and user_id = :user_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-utilities-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-utilities-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-utilities-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,70 @@
+
+
+
+
+
+ select min(price) from ec_offers where product_id=:product_id
+
+
+
+
+
+
+ select retailprice from ec_custom_product_field_values where product_id=:product_id
+
+
+
+
+
+
+ select min(price) from ec_offers where product_id=:product_id
+
+
+
+
+
+
+ select user_id,first_names,last_name,email,other_id_info,postal_code from ec_user_identification where user_identification_id=:user_identification_id
+
+
+
+
+
+
+ select first_names || ' ' || last_name from cc_users where user_id=:user_id
+
+
+
+
+
+
+ select line1, line2, city, usps_abbrev, zip_code, country_code, full_state_name, attn, phone, phone_time from ec_addresses where address_id=:address_id
+
+
+
+
+
+
+ select creditcard_type, creditcard_last_four, creditcard_expire, billing_zip_code from ec_creditcards where creditcard_id=:creditcard_id
+
+
+
+
+
+
+ select case when shipping_method = 'no shipping' then 0 else 1 end as shippable_p from ec_orders where order_id=:order_id
+
+
+
+
+
+
+ select
+ gift_certificate_state, user_id
+ from ec_gift_certificates
+ where gift_certificate_id=:gift_certificate_id
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-oracle.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select to_char(sysdate, 'YYYY-MM-DD') from dual
+
+
+
+
+
+
+ select to_char(sysdate, 'HH24:MI:SS') from dual
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs-postgresql.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql7.1
+
+
+
+ select to_char(current_timestamp, 'YYYY-MM-DD')
+
+
+
+
+
+
+ select to_char(current_timestamp, 'HH24:MI:SS')
+
+
+
+
+
Index: openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/ecommerce/tcl/ecommerce-widgets-procs.xql 10 Jul 2001 20:38:43 -0000 1.1
@@ -0,0 +1,72 @@
+
+
+
+
+
+ select category_id, category_name from ec_categories order by category_name
+
+
+
+
+
+
+ select subcategory_id, subcategory_name from ec_subcategories where category_id=:category_id order by subcategory_name
+
+
+
+
+
+
+ select subcategory_id from ec_subcategories where category_id=:category_id
+
+
+
+
+
+
+ select subcategory_name from ec_subcategories where subcategory_id=:subcategory_id
+
+
+
+
+
+
+ select subcategory_id from ec_subcategories where category_id=:category_id and subcategory_id in ([join $subcategory_list ","]) order by subcategory_name
+
+
+
+
+
+
+ select subsubcategory_id from ec_subsubcategories where subcategory_id=:subcategory_id and subsubcategory_id in ([join $subsubcategory_list ","]) order by subsubcategory_name
+
+
+
+
+
+
+ select picklist_item from ec_picklist_items where picklist_name='issue_type' order by sort_key
+
+
+
+
+
+
+ select picklist_item from ec_picklist_items where picklist_name='info_used' order by sort_key
+
+
+
+
+
+
+
+ select picklist_item
+ from ec_picklist_items
+ where picklist_name='interaction_type'
+ order by sort_key
+
+
+
+
+
+