1 -- Group: summarizedSalesHistory
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- See www.xtuple.com/CPAL for the full text of the software license.
8 -- Columns added to match columns of Printed Report
9 <? foreach("groupLitList") ?>
10 <? literal("groupLitList") ?>
11 <? if not isLast("groupLitList") ?>
14 <? endforeach ?> AS groupsLit,
15 <? foreach("groupList") ?>
16 substring(<? literal("groupList") ?> from 1 for 15)
17 <? if not isLast("groupList") ?>
20 <? endforeach ?> AS groups,
21 <? foreach("groupDescripList") ?>
22 substring(<? literal("groupDescripList") ?> from 1 for 30)
23 <? if not isLast("groupDescripList") ?>
26 <? endforeach ?> AS groupsDescrip,
27 -- End of columns for printed report
28 <? if exists("byCustomer") ?>
29 cust_number, cohist_cust_id AS cust_number_xtidrole, cust_name,
31 <? if exists("byCustomerType") ?>
32 custtype_code, custtype_id AS custtype_code_xtidrole,
34 <? if exists("byItem") ?>
35 item_number, item_id AS item_number_xtidrole, itemdescription,
37 <? if exists("bySalesRep") ?>
38 salesrep_number, salesrep_id AS salesrep_number_xtidrole, salesrep_name,
40 <? if exists("byShippingZone") ?>
41 shipzone_name, shipzone_id AS shipzone_name_xtidrole,
43 <? if exists("bySite") ?>
44 warehous_code, warehous_id AS warehous_code_xtidrole,
46 <? if exists("byCurrency") ?>
48 MIN(custunitprice) AS minprice, MAX(custunitprice) AS maxprice,
49 AVG(custunitprice) AS avgprice,
50 SUM(custextprice) AS totalsales,
51 CASE WHEN (SUM(cohist_qtyshipped) = 0) THEN 0
52 ELSE SUM(custextprice) / SUM(cohist_qtyshipped)
55 currConcat(baseCurrId()) AS currAbbr,
56 MIN(baseunitprice) AS minprice, MAX(baseunitprice) AS maxprice,
57 AVG(baseunitprice) AS avgprice,
58 SUM(baseextprice) AS totalsales,
59 CASE WHEN (SUM(cohist_qtyshipped) = 0) THEN 0
60 ELSE SUM(baseextprice) / SUM(cohist_qtyshipped)
63 SUM(cohist_qtyshipped) AS totalunits,
64 MIN(cohist_invcdate) AS firstdate,
65 MAX(cohist_invcdate) AS lastdate,
66 SUM(cohist_qtyshipped) AS qtyshipped,
67 'salesprice' AS minprice_xtnumericrole,
68 'salesprice' AS maxprice_xtnumericrole,
69 'salesprice' AS avgprice_xtnumericrole,
70 'salesprice' AS wtavgprice_xtnumericrole,
71 'qty' AS qtyshipped_xtnumericrole,
72 'curr' AS extprice_xtnumericrole,
73 'qty' AS totalunits_xtnumericrole,
74 'curr' AS totalsales_xtnumericrole,
75 'qty' AS totalunits_xtnumericrole,
76 'curr' AS totalsales_xtnumericrole,
77 0 AS totalunits_xttotalrole,
78 0 AS totalsales_xttotalrole
81 <? if exists("startDate") ?>
82 AND (cohist_invcdate >= <? value("startDate") ?>)
85 <? if exists("endDate") ?>
86 AND (cohist_invcdate <= <? value("endDate") ?>)
89 <? if exists("shipStartDate") ?>
90 AND (cohist_shipdate >= <? value("shipStartDate") ?>)
93 <? if exists("shipEndDate") ?>
94 AND (cohist_shipdate <= <? value("shipEndDate") ?>)
97 <? if exists("warehous_id") ?>
98 AND (warehous_id=<? value("warehous_id") ?>)
101 <? if exists("item_id") ?>
102 AND (item_id=<? value("item_id") ?>)
105 <? if exists("cust_id") ?>
106 AND (cohist_cust_id=<? value("cust_id") ?>)
109 <? if exists("shipto_id") ?>
110 AND (cohist_shipto_id=<? value("shipto_id") ?>)
113 <? if exists("salesrep_id") ?>
114 AND (salesrep_id=<? value("salesrep_id") ?>)
117 <? if exists("prodcat_id") ?>
118 AND (prodcat_id=<? value("prodcat_id") ?>)
121 <? if exists("prodcat_pattern") ?>
122 AND (prodcat_code ~ <? value("prodcat_pattern") ?>)
125 <? if exists("custtype_id") ?>
126 AND (custtype_id=<? value("custtype_id") ?>)
129 <? if exists("custtype_pattern") ?>
130 AND (custtype_code ~ <? value("custtype_pattern") ?>)
133 <? if exists("custgrp_id") ?>
134 AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
136 WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
139 <? if exists("custgrp_pattern") ?>
140 AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
141 FROM custgrp, custgrpitem
142 WHERE ( (custgrpitem_custgrp_id=custgrp_id)
143 AND (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
146 <? if exists("shipzone_id") ?>
147 AND (shipzone_id=<? value("shipzone_id") ?>)
150 <? if exists("curr_id") ?>
151 AND cust_curr_id = <? value("curr_id") ?>
154 <? if exists("currConcat_pattern") ?>
155 AND (currAbbr ~ <? value("currConcat_pattern") ?>)
159 <? if exists("bySalesRep") ?>
160 , salesrep_id, salesrep_number, salesrep_name
162 <? if exists("byShippingZone") ?>
163 , shipzone_id, shipzone_name, shipzone_descrip
165 <? if exists("byCustomer") ?>
166 , cohist_cust_id, cust_number, cust_name
168 <? if exists("byCustomerType") ?>
169 , custtype_id, custtype_code, custtype_descrip
171 <? if exists("byItem") ?>
172 , item_id, item_number, itemdescription
174 <? if exists("bySite") ?>
175 , warehous_id, warehous_code, warehous_descrip
177 <? if exists("byCurrency") ?>
178 , cust_curr_id, currAbbr
181 <? if exists("bySalesRep") ?>
184 <? if exists("byShippingZone") ?>
187 <? if exists("byCustomer") ?>
190 <? if exists("byCustomerType") ?>
193 <? if exists("byItem") ?>
196 <? if exists("bySite") ?>