3 <title>Brief Sales History</title>
4 <name>BriefSalesHistory</name>
5 <description></description>
14 <topmargin>50</topmargin>
15 <bottommargin>50</bottommargin>
16 <rightmargin>50</rightmargin>
17 <leftmargin>50</leftmargin>
20 <sql>SELECT <? if exists("startDate") ?>
21 formatDate(date(<? value("startDate") ?>))
24 <? endif ?> AS startDate,
25 <? if exists("endDate") ?>
26 formatDate(date(<? value("endDate") ?>))
29 <? endif ?> AS endDate,
30 <? if exists("showPrices") ?>
31 text('Unit Price') AS lbl_unitprice,
32 text('Total') AS lbl_total,
34 text('') AS lbl_unitprice,
35 text('') AS lbl_total,
37 <? if exists("warehous_id") ?>
38 (SELECT warehous_code FROM whsinfo WHERE warehous_id=<? value("warehous_id") ?>)
43 <? if exists("cust_id") ?>
44 (SELECT cust_name FROM custinfo WHERE cust_id=<? value("cust_id") ?>)
49 <? if exists("custtype_id") ?>
50 (SELECT custtype_code FROM custtype WHERE custtype_id=<? value("custtype_id") ?>)
52 text('All Customer Types')
55 <? if exists("custtype_pattern") ?>
56 text(<? value("custtype_pattern") ?>)
58 text('Not Applicable')
61 <? if exists("custgrp_id") ?>
62 (SELECT custgrp_name FROM custgrp WHERE custgrp_id=<? value("custgrp_id") ?>)
64 text('All Customer Groups')
67 <? if exists("custgrp_pattern") ?>
68 text(<? value("custgrp_pattern") ?>)
70 text('Not Applicable')
73 <? if exists("prodcat_id") ?>
74 (SELECT prodcat_code FROM prodcat WHERE prodcat_id=<? value("prodcat_id") ?>)
76 text('All Product Categories')
79 <? if exists("prodcat_pattern") ?>
80 text(<? value("prodcat_pattern") ?>)
82 text('Not Applicable')
85 <? if exists("item_id") ?>
86 (SELECT item_number FROM item WHERE item_id=<? value("item_id") ?>)
88 text('All Item Numbers')
91 <? if exists("cohead_id") ?>
92 (SELECT cohead_number FROM cohead WHERE cohead_id=<? value("cohead_id") ?>)
100 <sql>SELECT cohist_ordernumber AS sonumber,
101 cohist_invcnumber AS invnumber,
102 formatDate(cohist_orderdate) AS orddate,
103 formatDate(cohist_invcdate, 'Return') AS shipdate,
104 SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as extended,
105 formatMoney(SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2))) as f_total,
106 SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as total
108 LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
109 WHERE ((cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
110 <? if exists("cust_id") ?>
111 AND (cohist_cust_id=<? value("cust_id") ?>)
113 <? if exists("shipto_id") ?>
114 AND (cohist_shipto_id=<? value("shipto_id") ?>)
116 <? if exists("custtype_id") ?>
117 AND (cust_custtype_id=<? value("custtype_id") ?>)
119 <? if exists("custtype_pattern") ?>
120 AND (custtype_code ~ <? value("custtype_pattern") ?>)
122 <? if exists("custgrp_id") ?>
123 AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
125 WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
127 <? if exists("custgrp_pattern") ?>
128 AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
129 FROM custgrp, custgrpitem
130 WHERE ( (custgrpitem_custgrp_id=custgrp_id)
131 AND (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
133 <? if exists("warehous_id") ?>
134 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
136 <? if exists("item_id") ?>
137 AND (itemsite_item_id=<? value("item_id") ?>)
139 <? if exists("prodcat_id") ?>
140 AND (item_prodcat_id=<? value("prodcat_id") ?>)
142 <? if exists("prodcat_pattern") ?>
143 AND (item_prodcat_id IN (SELECT prodcat_id
145 WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
147 <? if exists("salesrep_id") ?>
148 AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
151 GROUP BY cohead_id, cust_id,cust_number,cust_custtype_id,cohist_cust_id,custtype_code,
152 cust_name,cohist_ordernumber, cohist_ponumber,
153 cohist_invcnumber,cohist_orderdate, cohist_invcdate
154 ORDER BY cohist_invcdate, cohist_orderdate;</sql>
168 <weight>normal</weight>
172 <string>Doc. #</string>
184 <weight>normal</weight>
188 <string>Ship Date</string>
200 <weight>normal</weight>
204 <string>Invoice #</string>
216 <weight>bold</weight>
220 <string>Brief Sales History</string>
232 <weight>normal</weight>
236 <string>Total</string>
248 <weight>normal</weight>
252 <string>Ord. Date</string>
268 <bottompad>12</bottompad>
272 <weight>bold</weight>
277 <query>Parameter Query</query>
278 <column>filter</column>
298 <weight>normal</weight>
302 <string>Total</string>
314 <weight>normal</weight>
318 <string>Ord. Date</string>
330 <weight>normal</weight>
334 <string>Invoice #</string>
353 <weight>normal</weight>
357 <string>Ship Date</string>
369 <weight>normal</weight>
373 <string>Doc. #</string>
380 <query>detail</query>
393 <weight>bold</weight>
398 <query>detail</query>
399 <column>sonumber</column>
412 <weight>bold</weight>
417 <query>detail</query>
418 <column>invnumber</column>
431 <weight>bold</weight>
436 <query>detail</query>
437 <column>f_total</column>
450 <weight>bold</weight>
455 <query>detail</query>
456 <column>shipdate</column>
469 <weight>bold</weight>
474 <query>detail</query>
475 <column>orddate</column>
492 <weight>normal</weight>
496 <string>Report Date:</string>
508 <weight>bold</weight>
513 <query>Context Query</query>
514 <column>page_number</column>
527 <weight>bold</weight>
532 <query>Context Query</query>
533 <column>report_date</column>
546 <weight>normal</weight>
550 <string>Page:</string>
565 <weight>normal</weight>
569 <string>Total:</string>
581 <weight>bold</weight>
586 <query>detail</query>
587 <column>total</column>
589 <format builtin="true">money</format>