Index: openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d23-0.1d24.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d23-0.1d24.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d23-0.1d24.sql	19 Oct 2005 09:48:48 -0000	1.1
@@ -0,0 +1,55 @@
+-- adding credit card type to dlec_view_orders
+
+drop view dlec_view_orders cascade;
+
+create or replace view dlec_view_orders as (
+    select o.order_id, to_char(o.confirmed_date, 'Mon dd, yyyy hh:miam') 
+	as confirmed_date, to_char(o.confirmed_date, 'Mon dd, yyyy') as confirmed_date_only_date, o.order_state,   (i.price_charged + 
+	coalesce(i.shipping_charged, 0) + coalesce(i.price_tax_charged, 0)
+     	- coalesce(i.price_refunded, 0) - coalesce(i.shipping_refunded, 0) 
+	- coalesce(i.price_tax_refunded, 0)) as price_to_display,
+	o.user_id as purchasing_user_id, u.first_names, u.last_name, 
+	t.method, coalesce((select true
+		where exists (select *
+			from ec_gift_certificate_usage
+			where order_id = o.order_id
+			and exists (select *
+				from scholarship_fund_grants
+				where ec_gift_certificate_usage.gift_certificate_id 				      = gift_certificate_id))), false) as has_scholarship_p,
+				s.section_id as _section_id, 
+		    	coalesce((select course_name
+	      		from dlec_view_sections
+	      		where section_id = s.section_id)||': '||s.section_name, 
+			p.product_name) as _section_name, s.course_id,
+        case when t.method = 'cc' then
+	(select creditcard_type from ec_creditcards where 
+	ec_creditcards.creditcard_id = o.creditcard_id)
+	else null end as credit_card_type,	 
+	case when t.method = 'invoice' then
+   	ec_total_price(o.order_id) - ec_order_gift_cert_amount(o.order_id) - 
+    	(select coalesce(sum(amount), 0)
+     	from dotlrn_ecommerce_transaction_invoice_payments
+     	where order_id = o.order_id) + ec_total_refund(o.order_id)
+    	else 0 end as balance, 
+	(coalesce(i.price_refunded, 0) + coalesce(i.shipping_refunded, 0) - 
+	coalesce(i.price_tax_refunded, 0)) as refund_price,
+	(i.price_charged + coalesce(i.shipping_charged, 0) + 
+	coalesce(i.price_tax_charged, 0)) as total_price, 
+    (select to_char(refund_date, 'Mon dd, yyyy')
+     from ec_refunds
+     where order_id = o.order_id
+     order by refund_date desc
+     limit 1) as refund_date, 
+     u.first_names||' '||u.last_name as purchaser,
+     i.item_id, deo.participant_id, case when ao.object_type = 'group' then acs_group__name(deo.participant_id) else person__name(deo.participant_id) end as participant_name,
+    deo.checked_out_by, u.user_id as purchaser_id, (deo.checked_out_by != u.user_id) 
+	as checked_out_by_admin_p, o.authorized_date, o.confirmed_date as confirmed_date_date_column, ao.object_type
+    from ec_orders o
+    join ec_items i using (order_id)
+    join ec_products p using (product_id)
+    join dotlrn_ecommerce_orders deo using (item_id)
+    join acs_objects ao on (deo.participant_id = ao.object_id)
+    join dotlrn_ecommerce_transactions t using (order_id)
+    left join dotlrn_ecommerce_section s on (i.product_id = s.product_id)
+    left join cc_users u on (o.user_id=u.user_id)
+    where o.order_state in ('confirmed', 'authorized', 'fulfilled', 'returned'));