git.roojs.org Git - Pman.Xtuple/blob - sqlreports/salescompare.sql

   1
   2
   3
   4
   5
   6
   7
   8
   9 -- there must be a better way to do this.......
  10
  11
  12
  13     SELECT
  14             itemcharvalue(item_id, 'BRAND') as item_brand,
  15             itemcharvalue(item_id, 'PRODUCTCATEGORY') as item_productcategory,
  16             item_number,
  17             item_descrip1,
  18             CASE WHEN (addr_country IS NULL OR addr_country = '') THEN
  19                 'UNKNOWN'
  20             ELSE
  21                 addr_country
  22             END AS  cohist_country,
  23
  24             ROUND( COALESCE(SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitprice, cohist_shipdate)),0),2) as ship_value,
  25             ROUND(COALESCE(SUM(cohist_qtyshipped),0),0) as ship_qty
  26
  27         FROM
  28             cohist
  29         LEFT JOIN
  30             custinfo
  31         ON
  32             cust_id = cohist_cust_id
  33
  34         LEFT JOIN
  35             cntct
  36         ON
  37             cntct_id = cust_cntct_id
  38
  39         LEFT JOIN
  40             addr
  41         ON
  42             addr_id = cntct_addr_id
  43         LEFT JOIN
  44             itemsite
  45         ON
  46             itemsite_id = cohist_itemsite_id
  47         LEFT JOIN
  48             item
  49         ON
  50             itemsite_item_id = item_id
  51         WHERE
  52             item_type = 'P'
  53         AND
  54  --           (cohist_invcdate BETWEEN '$cStartDate' AND '$cEndDate')
  55            (cohist_invcdate BETWEEN '2011-01-01' AND '2012-01-01')
  56
  57         GROUP BY
  58             item_id,item_number,item_descrip1,addr_country
  59           ORDER BY
  60             item_number ASC;
  61
  62
  63 SELECT
  64
  65
  66     itemcharvalue(item_id, 'BRAND') as item_brand,
  67     itemcharvalue(item_id, 'PRODUCTCATEGORY') as item_productcategory,
  68     item_number,
  69     item_descrip1,
  70
  71     -- previous year qty sales
  72     (SELECT
  73             COALESCE(SUM(cohist_qtyshipped),0)
  74         FROM
  75             cohist
  76         WHERE
  77              (cohist_itemsite_id = itemsite__id)
  78         AND
  79             (cohist_invcdate BETWEEN '$pStartDate' AND '$pEndDate')
  80     )    as prev_totalship
  81
  82     -- value
  83     (SELECT
  84         SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitprice, cohist_shipdate))
  85
  86         FROM
  87             cohist
  88         WHERE
  89              (cohist_itemsite_id = itemsite__id)
  90         AND
  91             (cohist_invcdate BETWEEN '$pStartDate' AND '$pEndDate')
  92     )    as prev_totalsale
  93
  94     -- countries
  95     (SELECT
  96             COALESCE(SUM(cohist_qtyshipped),0)
  97         FROM
  98             cohist
  99         LEFT JOIN
 100             custinfo
 101         ON
 102             cust_id = cohist_cust_id
 103
 104         LEFT JOIN
 105             cntct
 106         ON
 107             cntct_id = cust_cntct_id
 108
 109         LEFT JOIN
 110             addr
 111         ON
 112             addr_id = cntct_addr_id
 113
 114
 115
 116
 117         WHERE
 118             (cohist_itemsite_id = itemsite__id)
 119         AND
 120             (cohist_invcdate BETWEEN '$cStartDate' AND '$cEndDate')
 121         AND
 122             CASE WHEN (addr_country IS NULL OR addr_country = '') THEN
 123                 'UNKNOWN'
 124             ELSE
 125                 addr_country
 126             END = '$country'
 127
 128     )    as cur_ship_{$country}
 129
 130    (SELECT
 131             SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitprice, cohist_shipdate))
 132         FROM
 133             cohist
 134         LEFT JOIN
 135             custinfo
 136         ON
 137             cust_id = cohist_cust_id
 138
 139         LEFT JOIN
 140             cntct
 141         ON
 142             cntct_id = cust_cntct_id
 143
 144         LEFT JOIN
 145             addr
 146         ON
 147             addr_id = cntct_addr_id
 148
 149
 150
 151         WHERE
 152             (cohist_itemsite_id = itemsite__id)
 153         AND
 154             (cohist_invcdate BETWEEN '$cStartDate' AND '$cEndDate')
 155         AND
 156             CASE WHEN (addr_country IS NULL OR addr_country = '') THEN
 157                 'UNKNOWN'
 158             ELSE
 159                 addr_country
 160             END = '$country'
 161
 162     )    as cur_total_{$country}
 163
 164
 165
 166
 167     FROM
 168         item
 169     LEFT JOIN
 170         itemsite
 171     ON
 172         itemsite_item_id = item_id
 173     WHERE
 174         item_type = 'P';
 175      ORDER BY
 176         item_number
 177
 178
 179
 180
 181
 182