-- Group: desktop
-- Name:  salesHist
-- Notes: Summarized Sales History for Desktop
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

--------- BOOKINGS -----------
<? if exists("bookings") ?>
SELECT <? literal("group_id") ?>, <? literal("group_number") ?>, <? literal("group_name") ?>,
       sum(round((coitem_qtyord * coitem_qty_invuomratio) *
                     (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2)) AS amount,
       sum(coitem_qtyord) AS qty,
       'curr' AS amount_xtnumericrole,
       'qty' AS qty_xtnumericrole,
       0 AS amount_xttotalrole,
       0 AS qty_xttotalrole
FROM coitem
  JOIN cohead ON (coitem_cohead_id=cohead_id)
  JOIN custinfo ON (cust_id=cohead_cust_id)
  JOIN salesrep ON (salesrep_id=cohead_salesrep_id)
  JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  JOIN item ON (item_id=itemsite_item_id)
  JOIN prodcat ON (item_prodcat_id=prodcat_id)
WHERE (cohead_orderdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
AND (coitem_status != 'X')
GROUP BY <? literal("group_id") ?>, <? literal("group_number") ?>, <? literal("group_name") ?>
ORDER BY <? literal("group_number") ?>;

--------- SALES HISTORY -------
<? elseif exists("sales") ?> 
SELECT <? literal("group_id") ?>, <? literal("group_number") ?>, <? literal("group_name") ?>,
       sum(round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2)) AS amount,
       sum(cohist_qtyshipped) AS qty,
       'curr' AS amount_xtnumericrole,
       'qty' AS qty_xtnumericrole,
       0 AS amount_xttotalrole,
       0 AS qty_xttotalrole
FROM cohist 
  JOIN custinfo ON (cust_id=cohist_cust_id)
  JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
  JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
  JOIN item ON (item_id=itemsite_item_id)
  JOIN prodcat ON (item_prodcat_id=prodcat_id)
WHERE (cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
GROUP BY <? literal("group_id") ?>, <? literal("group_number") ?>, <? literal("group_name") ?>
ORDER BY <? literal("group_number") ?>;
<? endif ?>
