Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql,v diff -u -r1.1 -r1.1.10.1 --- openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql 10 Jul 2001 20:33:53 -0000 1.1 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-oracle.xql 13 Aug 2005 09:35:02 -0000 1.1.10.1 @@ -9,5 +9,26 @@ - + + + select c.*, + i.first_names || ' ' || i.last_name as issuer, + i.user_id as issuer_user_id, + p.first_names || ' ' || p.last_name as purchaser, + p.user_id as purchaser_user_id, + gift_certificate_amount_left(c.gift_certificate_id) as amount_left, + decode(sign(sysdate-expires),1,'t',0,'t','f') as expired_p, + v.first_names as voided_by_first_names, v.last_name as voided_by_last_name + decode(amount_left,0,'t','f') as amount_left_is_zero, + decode(gift_certificate_state,'void','t','f') as state_is_void + from ec_gift_certificates c, + cc_users i, + cc_users p, + cc_users v + where c.issued_by=i.user_id(+) + and c.purchased_by=p.user_id(+) + and c.voided_by=v.user_id(+) + and c.user_id=:user_id + order by expired_p, amount_left_is_zero, state_is_void, gift_certificate_id + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql,v diff -u -r1.1 -r1.1.10.1 --- openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql 10 Jul 2001 20:33:53 -0000 1.1 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates-postgresql.xql 13 Aug 2005 09:35:02 -0000 1.1.10.1 @@ -3,11 +3,35 @@ postgresql7.1 + select ec_gift_certificate_balance(:user_id) + + + select c.*, + i.first_names || ' ' || i.last_name as issuer, + i.user_id as issuer_user_id, + p.first_names || ' ' || p.last_name as purchaser, + p.user_id as purchaser_user_id, + gift_certificate_amount_left(c.gift_certificate_id) as amount_left, + (now() - expires) <= 0 as expired_p, + v.first_names as voided_by_first_names, v.last_name as voided_by_last_name + gift_certificate_amount_left(gift_certificate_id) = 0 as amount_left_is_zero, + gift_certificate_state = 'void' as state_is_void + from ec_gift_certificates c, + cc_users i, + cc_users p, + cc_users v + where c.issued_by=i.user_id + and c.purchased_by=p.user_id + and c.voided_by=v.user_id + and c.user_id=:user_id + order by expired_p, amount_left_is_zero, state_is_void, gift_certificate_id + + Index: openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.tcl,v diff -u -r1.4 -r1.4.2.1 --- openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.tcl 9 Mar 2004 00:59:47 -0000 1.4 +++ openacs-4/packages/ecommerce/www/admin/customer-service/gift-certificates.tcl 13 Aug 2005 09:35:02 -0000 1.4.2.1 @@ -50,13 +50,22 @@ " -set sql "select c.*, i.first_names || ' ' || i.last_name as issuer, i.user_id as issuer_user_id, p.first_names || ' ' || p.last_name as purchaser, p.user_id as purchaser_user_id, gift_certificate_amount_left(c.gift_certificate_id) as amount_left, decode(sign(sysdate-expires),1,'t',0,'t','f') as expired_p, v.first_names as voided_by_first_names, v.last_name as voided_by_last_name -from ec_gift_certificates c, cc_users i, cc_users p, cc_users v -where c.issued_by=i.user_id(+) -and c.purchased_by=p.user_id(+) -and c.voided_by=v.user_id(+) -and c.user_id=:user_id -order by expired_p, decode(amount_left,0,1,0), decode(gift_certificate_state,'void',1,0), gift_certificate_id" +set sql "select c.*, + i.first_names || ' ' || i.last_name as issuer, + i.user_id as issuer_user_id, + p.first_names || ' ' || p.last_name as purchaser, + p.user_id as purchaser_user_id, + gift_certificate_amount_left(c.gift_certificate_id) as amount_left, + decode(sign(sysdate-expires),1,'t',0,'t','f') as expired_p, + v.first_names as voided_by_first_names, v.last_name as voided_by_last_name + decode(amount_left,0,'t','f') as amount_left_is_zero, + decode(gift_certificate_state,'void','t','f') as state_is_void + from ec_gift_certificates c, cc_users i, cc_users p, cc_users v + where c.issued_by=i.user_id(+) + and c.purchased_by=p.user_id(+) + and c.voided_by=v.user_id(+) + and c.user_id=:user_id + order by expired_p, amount_left_is_zero, state_is_void, gift_certificate_id" set gift_certificate_counter 0 set current_printed 0