Index: openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql	14 Sep 2005 15:47:59 -0000	1.1
+++ openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql	14 Sep 2005 17:45:49 -0000	1.2
@@ -23,20 +23,27 @@
 );
 
 create view dlec_members as (
-	select u.user_id, u.first_names, u.last_name, u.email, a.line1 as address1, a.line2 as address2, a.city, a.usps_abbrev as state_code, a.full_state_name, a.zip_code, phone
-	from dotlrn_users u
+ 	select to_char(o.authorized_date, 'yyyy-mm-dd hh:miam') as authorized_date, p.product_name, u.user_id, u.first_names, u.last_name, u.email, a.line1 as address1, a.line2 as address2, a.city, a.usps_abbrev as state_code, a.full_state_name, a.zip_code, a.phone
+	from ec_items i,
+	ec_orders o,
+	ec_products p,
+	dotlrn_users u
 	left join (select *
 		   from ec_addresses
 		   where address_id in (select max(address_id)
 					from ec_addresses
 					group by user_id)) a
 	on (u.user_id = a.user_id)
-	where u.user_id in (select member_id
-			  from group_member_map
-			  where group_id = (select attr_value
-			  		    from apm_parameter_values
-			  		    where parameter_id = (select parameter_id
-			      					  from apm_parameters
-			      					  where package_key = 'dotlrn-ecommerce'
-			      					  and parameter_name = 'MemberGroupId')))
+	where i.order_id = o.order_id
+	and i.product_id = p.product_id
+	and o.user_id = u.user_id
+	and o.order_state in ('authorized', 'fulfilled', 'returned')
+	and i.product_id in (select product_id
+			     from ec_category_product_map
+			     where category_id = (select attr_value
+			  		    	  from apm_parameter_values
+			  		    	  where parameter_id = (select parameter_id
+			      					  	from apm_parameters
+			      					  	where package_key = 'dotlrn-ecommerce'
+			      					  	and parameter_name = 'MembershipECCategoryId')))
 );
\ No newline at end of file
