git.roojs.org Git - Pman.Xtuple/blob - metasql/salesPlanning.summary.sql

   1
   2
   3 SELECT
   4         invcitem_item_id,
   5         invchead_cust_id
   6 --         SUM(COALESCE(invcitem_billed, 0)) AS billed
   7
   8 --         substr(current_database(),LENGTH(current_database())-1,2) AS system_country,
   9 --         invcitem_item_id,
  10 --         item_number,
  11 --         item_descrip1,
  12 --         charass_getvalue('I',invcitem_item_id, 'BRAND') as brand_name,
  13 --         charass_getvalue('I',invcitem_item_id, 'PRODUCTGROUP') as group_name,
  14 --         CASE WHEN itemsrc_active THEN
  15 --             'Yes'
  16 --         ELSE
  17 --             'No'
  18 --         END AS active,
  19 --         cust_name,
  20 --         invchead_cust_id
  21 --         addr_country,
  22 --         cust_name AS cust_group,
  23 --         invchead_invcdate,
  24 --         invcitem_billed
  25
  26 FROM
  27         invcitem
  28 LEFT JOIN
  29         invchead
  30     ON
  31         invchead_id = invcitem_invchead_id
  32 LEFT JOIN
  33         itemsite
  34     ON
  35         itemsite_item_id = invcitem_item_id
  36 LEFT JOIN
  37         item
  38     ON
  39         item_id = invcitem_item_id
  40
  41 WHERE
  42         item_active = TRUE
  43     AND
  44         item_sold = TRUE
  45     AND
  46         itemsite_stocked = TRUE
  47     AND
  48         invcitem_item_id IN (
  49                                 SELECT
  50                                         charass_target_id -- select all the item id belongs to the brand or productgroup
  51                                 FROM
  52                                         charass
  53                                 LEFT JOIN
  54                                         char
  55                                     ON
  56                                         char_id = charass_char_id
  57                                 WHERE
  58
  59                                         charass_target_type = 'I'
  60                                     AND
  61                                         (
  62                                             (
  63                                                 <? if exists("brand") ?>
  64
  65                                                     char_name = 'BRAND'
  66                                                     AND
  67                                                     charass_value = <? value("brand") ?>
  68
  69                                                 <? else ?>
  70                                                     false
  71                                                 <? endif ?>
  72                                             )
  73                                             OR
  74                                             (
  75                                                 <? if exists("group") ?>
  76
  77                                                     char_name = 'PRODUCTGROUP'
  78                                                     AND
  79                                                     charass_value = <? value("group") ?>
  80
  81                                                 <? else ?>
  82                                                     false
  83                                                 <? endif ?>
  84                                             )
  85
  86                                         )
  87                             )
  88     AND
  89         EXTRACT(YEAR FROM invchead_invcdate) >= EXTRACT(YEAR FROM NOW() - INTERVAL '2 YEAR')
  90     AND
  91         EXTRACT(YEAR FROM invchead_invcdate) <= EXTRACT(YEAR FROM NOW())
  92
  93 GROUP BY invcitem_item_id, invchead_cust_id