Index: openacs-4/packages/dotlrn-ecommerce/dotlrn-ecommerce.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/dotlrn-ecommerce.info,v
diff -u -r1.37 -r1.38
--- openacs-4/packages/dotlrn-ecommerce/dotlrn-ecommerce.info	14 Sep 2005 02:42:33 -0000	1.37
+++ openacs-4/packages/dotlrn-ecommerce/dotlrn-ecommerce.info	14 Sep 2005 15:47:59 -0000	1.38
@@ -7,15 +7,15 @@
     <initial-install-p>f</initial-install-p>
     <singleton-p>t</singleton-p>
     
-    <version name="0.1d17" url="http://openacs.org/repository/download/apm/dotlrn-ecommerce-0.1d17.apm">
+    <version name="0.1d18" url="http://openacs.org/repository/download/apm/dotlrn-ecommerce-0.1d18.apm">
         <owner url="mailto:roelmc@solutiongrove.com">Roel Canicula</owner>
         <summary>Package to tie the dotLRN, Ecommerce, Assessments and dotLRN Catalog packages together</summary>
         <release-date>2005-08-10</release-date>
         <vendor url="http://www.solutiongrove.com">Solution Grove</vendor>
         <description format="text/html">Package to tie the dotLRN, Ecommerce, Assessments and dotLRN Catalog packages together.  Initially intended for the MOS and MGH projects, the goal is to create a reusable module for similar projects.</description>
         <maturity>0</maturity>
 
-        <provides url="dotlrn-ecommerce" version="0.1d17"/>
+        <provides url="dotlrn-ecommerce" version="0.1d18"/>
 
         <callbacks>
             <callback type="after-install"  proc="dotlrn_ecommerce::install"/>
Index: openacs-4/packages/dotlrn-ecommerce/sql/postgresql/dotlrn-ecommerce-views-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn-ecommerce/sql/postgresql/dotlrn-ecommerce-views-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/dotlrn-ecommerce/sql/postgresql/dotlrn-ecommerce-views-create.sql	3 Sep 2005 00:56:34 -0000	1.2
+++ openacs-4/packages/dotlrn-ecommerce/sql/postgresql/dotlrn-ecommerce-views-create.sql	14 Sep 2005 15:47:59 -0000	1.3
@@ -166,6 +166,40 @@
     group by o.order_id, o.confirmed_date, o.order_state, ec_total_price(o.order_id), o.user_id, u.first_names, u.last_name, o.in_basket_date, t.method, s.section_name, s.section_id, s.course_id, o.authorized_date, balance
 );
 
+create view dlec_sections as (
+	select s.*,
+	v.maxparticipants,
+	(v.maxparticipants - s.attendees) as available_slots,
+	(s.attendees::float / v.maxparticipants * 100) as attendance_percentage
+	from (select *,
+	      (select count(*)
+	       from dotlrn_member_rels_approved
+	       where community_id = s.community_id
+	       and rel_type in ('dotlrn_member_rel', 'dc_student_rel')) as attendees
+	      from dotlrn_ecommerce_section s) s
+	left join ec_custom_product_field_values v
+	on (s.product_id = v.product_id)
+);
+
+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
+	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')))
+);
+
 -- scholarships allocated
 --create view dlec_view_scholarships_allocated as (
 --    select person__name(user_id), to_char(grant_date, 'Month dd, yyyy hh:miam') as grant_date, grant_amount, dlec_view_scholarship_funds.fund_id, dlec_view_scholarship_funds.account_code, dlec_view_scholarship_funds.object_title
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
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql	14 Sep 2005 15:47:59 -0000	1.1
@@ -0,0 +1,42 @@
+-- 
+-- packages/dotlrn-ecommerce/sql/postgresql/upgrade/upgrade-0.1d17-0.1d18.sql
+-- 
+-- @author Roel Canicula (roelmc@pldtdsl.net)
+-- @creation-date 2005-09-14
+-- @arch-tag: 7e661079-1f4b-472a-9ffd-170aca6f03c8
+-- @cvs-id $Id: upgrade-0.1d17-0.1d18.sql,v 1.1 2005/09/14 15:47:59 roelc Exp $
+--
+
+create view dlec_sections as (
+	select s.*,
+	v.maxparticipants,
+	(v.maxparticipants - s.attendees) as available_slots,
+	(s.attendees::float / v.maxparticipants * 100) as attendance_percentage
+	from (select *,
+	      (select count(*)
+	       from dotlrn_member_rels_approved
+	       where community_id = s.community_id
+	       and rel_type in ('dotlrn_member_rel', 'dc_student_rel')) as attendees
+	      from dotlrn_ecommerce_section s) s
+	left join ec_custom_product_field_values v
+	on (s.product_id = v.product_id)
+);
+
+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
+	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')))
+);
\ No newline at end of file