-- Group: summarizedSalesHistory
-- Name: detail
-- Notes:
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT 1 AS dummy,
-- Columns added to match columns of Printed Report
<? foreach("groupLitList") ?>
  <? literal("groupLitList") ?>
  <? if not isLast("groupLitList") ?>
  || E'\n' ||
  <? endif ?>
<? endforeach ?> AS groupsLit,
<? foreach("groupList") ?>
  substring(<? literal("groupList") ?> from 1 for 15)
  <? if not isLast("groupList") ?>
  || E'\n' ||
  <? endif ?>
<? endforeach ?> AS groups,
<? foreach("groupDescripList") ?>
  substring(<? literal("groupDescripList") ?> from 1 for 30)
  <? if not isLast("groupDescripList") ?>
  || E'\n' ||
  <? endif ?>
<? endforeach ?> AS groupsDescrip,
-- End of columns for printed report
       <? if exists("byCustomer") ?>
         cust_number, cohist_cust_id AS cust_number_xtidrole, cust_name,
       <? endif ?>
       <? if exists("byCustomerType") ?>
         custtype_code, custtype_id AS custtype_code_xtidrole,
       <? endif ?>
       <? if exists("byItem") ?>
         item_number, item_id AS item_number_xtidrole, itemdescription,
       <? endif ?>
       <? if exists("bySalesRep") ?>
         salesrep_number, salesrep_id AS salesrep_number_xtidrole, salesrep_name,
       <? endif ?>
       <? if exists("byShippingZone") ?>
         shipzone_name, shipzone_id AS shipzone_name_xtidrole,
       <? endif ?>
       <? if exists("bySite") ?>
         warehous_code, warehous_id AS warehous_code_xtidrole,
       <? endif ?>
       <? if exists("byCurrency") ?>
         currAbbr,
         MIN(custunitprice) AS minprice, MAX(custunitprice) AS maxprice,
         AVG(custunitprice) AS avgprice,
         SUM(custextprice) AS totalsales,
         CASE WHEN (SUM(cohist_qtyshipped) = 0) THEN 0
         ELSE SUM(custextprice) / SUM(cohist_qtyshipped)
         END AS wtavgprice,
       <? else ?>
         currConcat(baseCurrId()) AS currAbbr,
         MIN(baseunitprice) AS minprice, MAX(baseunitprice) AS maxprice,
         AVG(baseunitprice) AS avgprice,
         SUM(baseextprice) AS totalsales,
         CASE WHEN (SUM(cohist_qtyshipped) = 0) THEN 0
         ELSE SUM(baseextprice) / SUM(cohist_qtyshipped)
         END AS wtavgprice,
       <? endif ?>
         SUM(cohist_qtyshipped) AS totalunits,
         MIN(cohist_invcdate) AS firstdate,
         MAX(cohist_invcdate) AS lastdate,
         SUM(cohist_qtyshipped) AS qtyshipped,
         'salesprice' AS minprice_xtnumericrole,
         'salesprice' AS maxprice_xtnumericrole,
         'salesprice' AS avgprice_xtnumericrole,
         'salesprice' AS wtavgprice_xtnumericrole,
         'qty' AS qtyshipped_xtnumericrole,
         'curr' AS extprice_xtnumericrole,
         'qty' AS totalunits_xtnumericrole,
         'curr' AS totalsales_xtnumericrole,
         'qty' AS totalunits_xtnumericrole,
         'curr' AS totalsales_xtnumericrole,
         0 AS totalunits_xttotalrole,
         0 AS totalsales_xttotalrole
<? if exists("includeMisc") ?>
  FROM saleshistorymisc
<? else ?>
  FROM saleshistory
<? endif ?>
WHERE ( (TRUE)
<? if exists("startDate") ?>
 AND (cohist_invcdate >= <? value("startDate") ?>)
<? endif ?>

<? if exists("endDate") ?>
 AND (cohist_invcdate <= <? value("endDate") ?>)
<? endif ?>

<? if exists("shipStartDate") ?>
 AND (cohist_shipdate >= <? value("shipStartDate") ?>)
<? endif ?>

<? if exists("shipEndDate") ?>
 AND (cohist_shipdate <= <? value("shipEndDate") ?>)
<? endif ?>

<? if exists("warehous_id") ?>
 AND (warehous_id=<? value("warehous_id") ?>)
<? endif ?>

<? if exists("item_id") ?>
 AND (item_id=<? value("item_id") ?>)
<? endif ?>

<? if exists("cust_id") ?>
 AND (cohist_cust_id=<? value("cust_id") ?>)
<? endif ?>

<? if exists("shipto_id") ?>
 AND (cohist_shipto_id=<? value("shipto_id") ?>)
<? endif ?>

<? if exists("salesrep_id") ?>
 AND (salesrep_id=<? value("salesrep_id") ?>)
<? endif ?>

<? if exists("prodcat_id") ?>
 AND (prodcat_id=<? value("prodcat_id") ?>)
<? endif ?>

<? if exists("prodcat_pattern") ?>
 AND (prodcat_code ~ <? value("prodcat_pattern") ?>)
<? endif ?>

<? if exists("custtype_id") ?>
 AND (custtype_id=<? value("custtype_id") ?>)
<? endif ?>

<? if exists("custtype_pattern") ?>
   AND (custtype_code ~ <? value("custtype_pattern") ?>)
<? endif ?>

<? if exists("custgrp_id") ?>
  AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                   FROM custgrpitem
                   WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
<? endif ?>

<? if exists("custgrp_pattern") ?>
  AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
                   FROM custgrp, custgrpitem
                   WHERE ( (custgrpitem_custgrp_id=custgrp_id)
                     AND   (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
<? endif ?>

<? if exists("shipzone_id") ?>
 AND (shipzone_id=<? value("shipzone_id") ?>)
<? endif ?>

<? if exists("curr_id") ?>
 AND cust_curr_id = <? value("curr_id") ?>
<? endif ?>

<? if exists("currConcat_pattern") ?>
 AND (currAbbr ~ <? value("currConcat_pattern") ?>)
<? endif ?>
)
GROUP BY dummy
<? if exists("bySalesRep") ?>
 , salesrep_id, salesrep_number, salesrep_name
<? endif ?>
<? if exists("byShippingZone") ?>
 , shipzone_id, shipzone_name, shipzone_descrip
<? endif ?>
<? if exists("byCustomer") ?>
 , cohist_cust_id, cust_number, cust_name
<? endif ?>
<? if exists("byCustomerType") ?>
 , custtype_id, custtype_code, custtype_descrip
<? endif ?>
<? if exists("byItem") ?>
 , item_id, item_number, itemdescription
<? endif ?>
<? if exists("bySite") ?>
 , warehous_id, warehous_code, warehous_descrip
<? endif ?>
<? if exists("byCurrency") ?>
 , cust_curr_id, currAbbr
<? endif ?>
 ORDER BY dummy
<? if exists("bySalesRep") ?>
 , salesrep_number
<? endif ?>
<? if exists("byShippingZone") ?>
 , shipzone_name
<? endif ?>
<? if exists("byCustomer") ?>
 , cust_number
<? endif ?>
<? if exists("byCustomerType") ?>
 , custtype_code
<? endif ?>
<? if exists("byItem") ?>
 , item_number
<? endif ?>
<? if exists("bySite") ?>
 , warehous_code
<? endif ?>
;