git.roojs.org Git - Pman.Xtuple/blob - metasql/registrar.invoices.sql

   1
   2
   3 -- JSON START
   4 -- {
   5
   6 -- 'cols' : [
   7
   8 --  { 'header' : 'Invoice #' ,  'dataIndex': 'invchead_invcnumber', 'autoHeight' : 1 },
   9 --  { 'header' : 'Created' ,  'dataIndex': 'f_invchead_invcdate', 'autoHeight' : 1 },
  10 --  { 'header' : 'Domain' ,  'dataIndex': 'domain', 'autoHeight' : 1 },
  11 --  { 'header' : 'Product' ,  'dataIndex': 'product', 'autoHeight' : 1 },
  12 --  { 'header' : 'Contacts' ,  'dataIndex': 'contacts', 'autoHeight' : 1 },
  13 --  { 'header' : 'Value' ,  'dataIndex': 'aropen_amount', 'autoHeight' : 1 },
  14 --  { 'header' : 'Balance' ,  'dataIndex': 'balance', 'autoHeight' : 1 }
  15 -- ]
  16 -- }
  17 -- JSON END
  18
  19
  20 SELECT
  21         invchead_id,
  22         invchead_invcdate,
  23         invchead_invcnumber,
  24         f_invchead_invcdate,
  25         domain,
  26         admin_cntct_registrar_organization,
  27         cust_name,
  28         contacts,
  29         product,
  30         aropen_amount,
  31         aropen_paid,
  32         cust_curr_id,
  33         cust_curr_id_curr_name,
  34         aropen_id,
  35         invchead_void,
  36         <? if exists("status") || exists("search") ?>
  37             0 AS balance
  38         <? else ?>
  39             SUM(aropen_amount - aropen_paid) OVER (ORDER BY x.invchead_invcdate ASC, x.invchead_id ASC) AS balance
  40         <? endif ?>
  41
  42 FROM
  43 (
  44     SELECT
  45             invchead_id,
  46             invchead_invcdate,
  47             invchead_invcnumber,
  48             to_char(invchead_invcdate,'DD/Mon/YYYY') AS f_invchead_invcdate,
  49             custinfo.cust_curr_id AS cust_curr_id,
  50             curr_symbol.curr_name AS cust_curr_id_curr_name,
  51             custinfo.cust_name AS cust_name,
  52             aropen.aropen_id AS aropen_id,
  53             admin_cntct.cntct_registrar_organization AS admin_cntct_registrar_organization,
  54             CONCAT(
  55                 admin_cntct.cntct_registrar_organization, E'\n', admin_cntct.cntct_name,
  56                 CASE WHEN LENGTH(admin_cntct_addr.addr_line1) > 0 THEN E'\n' || admin_cntct_addr.addr_line1 END,
  57                 CASE WHEN LENGTH(admin_cntct_addr.addr_line2) > 0 THEN E'\n' || admin_cntct_addr.addr_line2 END,
  58                 CASE WHEN LENGTH(admin_cntct_addr.addr_line3) > 0 THEN E'\n' || admin_cntct_addr.addr_line3 END,
  59                 CASE WHEN LENGTH(admin_cntct_addr.addr_city) > 0 THEN E'\n' || admin_cntct_addr.addr_city END,
  60                 CASE WHEN LENGTH(admin_cntct_addr.addr_state) > 0 THEN E'\n' || admin_cntct_addr.addr_state END,
  61                 CASE WHEN LENGTH(admin_cntct_addr.addr_country) > 0 THEN E'\n' || admin_cntct_addr.addr_country END
  62             ) AS contacts,
  63             COALESCE(currtocurr(
  64                 aropen.aropen_curr_id,
  65                 custinfo.cust_curr_id,
  66                 aropen.aropen_amount,
  67                 aropen.aropen_distdate
  68             ), 0) AS aropen_amount,
  69             COALESCE(currtocurr(
  70                 aropen.aropen_curr_id,
  71                 custinfo.cust_curr_id,
  72                 aropen.aropen_paid,
  73                 aropen.aropen_distdate
  74             ), 0) AS aropen_paid,
  75 --             COALESCE(currtocurr(
  76 --                 aropen.aropen_curr_id,
  77 --                 custinfo.cust_curr_id,
  78 --                 (aropen.aropen_amount - aropen.aropen_paid),
  79 --                 aropen.aropen_distdate
  80 --             ), 0) AS amount,
  81             (SELECT invcitem_custpn FROM invcitem WHERE invcitem_invchead_id = invchead_id LIMIT 1) AS domain,
  82             (
  83                 SELECT
  84                         array_to_string(array_agg(i.item_number || ' - ' || i.item_descrip1), E'\n')
  85                 FROM
  86                     (
  87                         SELECT
  88                                 item.item_number,
  89                                 item.item_descrip1
  90                         FROM
  91                                 invcitem
  92                         LEFT JOIN
  93                                 item AS item
  94                         ON
  95                                 item.item_id = invcitem_item_id
  96                         WHERE
  97                                 invcitem_invchead_id = invchead_id
  98                         ORDER BY item.item_type
  99                     ) AS i
 100             ) AS product,
 101             invchead_void
 102     FROM
 103             invchead
 104     LEFT JOIN
 105             aropen AS aropen
 106     ON
 107             aropen.aropen_doctype = 'I'
 108             AND
 109             aropen.aropen_docnumber =  invchead_invcnumber
 110     LEFT JOIN
 111             custinfo AS custinfo
 112     ON
 113             custinfo.cust_id = invchead_cust_id
 114     LEFT JOIN
 115             curr_symbol
 116     ON
 117             curr_symbol.curr_id = custinfo.cust_curr_id
 118     LEFT JOIN
 119             cntct AS admin_cntct
 120     ON
 121             admin_cntct.cntct_id = invchead_registrar_admin_cntct_id
 122     LEFT JOIN
 123             addr AS admin_cntct_addr
 124     ON
 125             admin_cntct_addr.addr_id = admin_cntct.cntct_addr_id
 126     WHERE
 127         (
 128                 (TRUE)
 129                 <? if exists("cust_id") ?>
 130                 AND
 131                     (invchead.invchead_cust_id = <? value("cust_id") ?>)
 132                 <? endif ?>
 133                 <? if exists("status") ?>
 134                 AND
 135                     CASE WHEN <? value("status") ?> = 'paid' THEN
 136                         (aropen.aropen_amount <= aropen.aropen_paid)
 137                     ELSE
 138                         (aropen.aropen_amount > aropen.aropen_paid)
 139                     END
 140                 <? endif ?>
 141                 <? if exists("search") ?>
 142                 AND
 143                     (
 144                         (SELECT invcitem_custpn FROM invcitem WHERE invcitem_invchead_id = invchead_id LIMIT 1) ILIKE <? value("search") ?>
 145                         OR
 146                         admin_cntct.cntct_registrar_organization ILIKE <? value("search") ?>
 147                         OR
 148                         admin_cntct.cntct_name ILIKE <? value("search") ?>
 149                     )
 150                 <? endif ?>
 151         )
 152     ORDER BY invchead_invcdate ASC, invchead_id ASC
 153 ) AS x
 154
 155 ORDER BY <? value("sort") ?> <? value("dir") ?>, invchead_id <? value("dir") ?>