git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/briefEarnedCommission-detail.mql

   1 -- Group: briefEarnedCommission
   2 -- Name: detail
   3 -- Notes: used by dspBriefEarnedCommissions
   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.
   6
   7 SELECT cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
   8        cohist_ordernumber, cohist_orderdate, cohist_invcnumber, cohist_invcdate, currAbbr,
   9        SUM(extprice) AS sumextprice,
  10        SUM(cohist_commission) AS sumcommission,
  11        SUM(baseextprice) AS sumbaseextprice,
  12        SUM(basecommission) AS sumbasecommission,
  13 <? if exists("isReport") ?>
  14        formatDate(cohist_orderdate) AS f_orderdate,
  15        formatDate(cohist_invcdate) AS f_invcdate,
  16        formatMoney(SUM(extprice)) AS f_sumextprice,
  17        formatMoney(SUM(baseextprice)) AS f_sumbaseextprice,
  18        formatMoney(SUM(cohist_commission)) AS f_sumcommission,
  19        formatMoney(SUM(basecommission)) AS f_sumbasecommission,
  20 <? endif ?>
  21        <? value("return") ?> AS cohist_invcdate_xtnullrole,
  22        'curr' AS sumextprice_xtnumericrole,
  23        'curr' AS sumcommission_xtnumericrole,
  24        'curr' AS sumbaseextprice_xtnumericrole,
  25        'curr' AS sumbasecommission_xtnumericrole,
  26        0 AS sumbaseextprice_xttotalrole,
  27        0 AS sumbasecommission_xttotalrole
  28 <? if exists("includeMisc") ?>
  29 FROM saleshistorymisc
  30 <? else ?>
  31 FROM saleshistory
  32 <? endif ?>
  33 WHERE ((cohist_commission <> 0)
  34     AND(cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
  35     <? if exists("salesrep_id") ?>
  36     AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
  37     <? endif ?>
  38       )
  39 GROUP BY cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
  40          cohist_ordernumber, cohist_orderdate, cohist_invcnumber, cohist_invcdate, currAbbr
  41 ORDER BY salesrep_number, cust_number, cohist_invcdate;