-- {"TITLES" : ["Order Date", "Domain Name", "Order No.", "Agent", "Organization", "Billing", "Item", "Paid"] }
-- {"COLS" : ["f_invchead_invcdate", "invcitem_custpn", "invchead_invcnumber", "cust_name", "invchead_billto_registrar_organization", "contacts", "product", "is_paid"]}
-- JSON END
+--
+-- THIS IS THE OLD VERSION
-SELECT
- invcitem_custpn,
- invcitem_invchead_id,
- invchead.invchead_invcdate AS invchead_invcdate,
- to_char(invchead.invchead_invcdate,'DD/Mon/YYYY') AS f_invchead_invcdate,
- invchead.invchead_invcnumber AS invchead_invcnumber,
- invchead.invchead_posted AS invchead_posted,
- invchead.invchead_void AS invchead_void,
- invchead.invchead_billto_registrar_organization AS invchead_billto_registrar_organization,
- invchead.invchead_billto_name AS invchead_billto_name,
- invchead.invchead_billto_address1 AS invchead_billto_address1,
- invchead.invchead_billto_address2 AS invchead_billto_address2,
- invchead.invchead_billto_address3 AS invchead_billto_address3,
- invchead.invchead_billto_city AS invchead_billto_city,
- invchead.invchead_billto_state AS invchead_billto_state,
- invchead.invchead_billto_country AS invchead_billto_country,
- custinfo.cust_name AS cust_name,
- custinfo.cust_curr_id AS cust_curr_id,
- curr_symbol.curr_name AS cust_curr_id_curr_name,
- aropen.aropen_id AS aropen_id,
- aropen.aropen_amount AS aropen_amount,
- COALESCE(currtocurr(
- aropen.aropen_curr_id,
- custinfo.cust_curr_id,
- aropen.aropen_amount,
- aropen.aropen_distdate
- ), 0) AS cust_aropen_amount,
- aropen.aropen_paid AS aropen_paid,
- COALESCE(currtocurr(
- aropen.aropen_curr_id,
- custinfo.cust_curr_id,
- aropen.aropen_paid,
- aropen.aropen_distdate
- ), 0) AS cust_aropen_paid,
- item.item_number AS item_number,
- item.item_descrip1 AS item_descrip1,
-
- CONCAT(
- invchead.invchead_billto_name,
- CASE WHEN LENGTH(invchead.invchead_billto_address1) > 0 THEN E'\n' || invchead.invchead_billto_address1 END,
- CASE WHEN LENGTH(invchead.invchead_billto_address2) > 0 THEN E'\n' || invchead.invchead_billto_address2 END,
- CASE WHEN LENGTH(invchead.invchead_billto_address3) > 0 THEN E'\n' || invchead.invchead_billto_address3 END,
- CASE WHEN LENGTH(invchead.invchead_billto_city) > 0 THEN E'\n' || invchead.invchead_billto_city END,
- CASE WHEN LENGTH(invchead.invchead_billto_state) > 0 THEN E'\n' || invchead.invchead_billto_state END,
- CASE WHEN LENGTH(invchead.invchead_billto_country) > 0 THEN E'\n' || invchead.invchead_billto_country END
- ) AS contacts,
- CONCAT(
- item.item_number,
- CASE WHEN LENGTH(item.item_descrip1) > 0 THEN E'\n' || item.item_descrip1 END
- ) AS product,
- CASE WHEN aropen.aropen_paid < aropen.aropen_amount THEN
- 'NO'
- ELSE
- 'YES'
- END AS is_paid
-FROM
- invcitem
-LEFT JOIN
- item
-ON
- item.item_id = invcitem_item_id
-LEFT JOIN
- invchead
-ON
- invchead.invchead_id = invcitem_invchead_id
-LEFT JOIN
- aropen AS aropen
-ON
- aropen.aropen_doctype = 'I'
- AND
- aropen.aropen_docnumber = invchead.invchead_invcnumber
-LEFT JOIN
- custinfo AS custinfo
-ON
- custinfo.cust_id = invchead.invchead_cust_id
-LEFT JOIN
- curr_symbol
-ON
- curr_symbol.curr_id = custinfo.cust_curr_id
-WHERE
- (
- (TRUE)
- <? if exists("cust_id") ?>
- AND
- (invchead.invchead_cust_id = <? value("cust_id") ?>)
- <? endif ?>
- <? if exists("status") ?>
- AND
- CASE WHEN <? value("status") ?> = 'paid' THEN
- (aropen.aropen_amount <= aropen.aropen_paid)
- ELSE
- (aropen.aropen_amount > aropen.aropen_paid)
- END
- <? endif ?>
- <? if exists("search") ?>
- AND
- (
- (SELECT invcitem_custpn FROM invcitem WHERE invcitem_invchead_id = invchead_id LIMIT 1) ILIKE <? value("search") ?>
- OR
- invchead_billto_registrar_organization ILIKE <? value("search") ?>
- )
- <? endif ?>
- <? if exists("start_dt") ?>
- AND
- (
- invchead.invchead_invcdate >= <? value("start_dt") ?>
- )
- <? endif ?>
- <? if exists("end_dt") ?>
- AND
- (
- invchead.invchead_invcdate <= <? value("end_dt") ?>
- )
- <? endif ?>
- )
-ORDER BY <? value("sort") ?> <? value("dir") ?>, invchead.invchead_id ASC, invcitem_linenumber ASC
+-- SELECT
+-- invcitem_custpn,
+-- invcitem_invchead_id,
+-- invchead.invchead_invcdate AS invchead_invcdate,
+-- to_char(invchead.invchead_invcdate,'DD/Mon/YYYY') AS f_invchead_invcdate,
+-- invchead.invchead_invcnumber AS invchead_invcnumber,
+-- invchead.invchead_posted AS invchead_posted,
+-- invchead.invchead_void AS invchead_void,
+-- invchead.invchead_billto_registrar_organization AS invchead_billto_registrar_organization,
+-- invchead.invchead_billto_name AS invchead_billto_name,
+-- invchead.invchead_billto_address1 AS invchead_billto_address1,
+-- invchead.invchead_billto_address2 AS invchead_billto_address2,
+-- invchead.invchead_billto_address3 AS invchead_billto_address3,
+-- invchead.invchead_billto_city AS invchead_billto_city,
+-- invchead.invchead_billto_state AS invchead_billto_state,
+-- invchead.invchead_billto_country AS invchead_billto_country,
+-- custinfo.cust_name AS cust_name,
+-- custinfo.cust_curr_id AS cust_curr_id,
+-- curr_symbol.curr_name AS cust_curr_id_curr_name,
+-- aropen.aropen_id AS aropen_id,
+-- aropen.aropen_amount AS aropen_amount,
+-- COALESCE(currtocurr(
+-- aropen.aropen_curr_id,
+-- custinfo.cust_curr_id,
+-- aropen.aropen_amount,
+-- aropen.aropen_distdate
+-- ), 0) AS cust_aropen_amount,
+-- aropen.aropen_paid AS aropen_paid,
+-- COALESCE(currtocurr(
+-- aropen.aropen_curr_id,
+-- custinfo.cust_curr_id,
+-- aropen.aropen_paid,
+-- aropen.aropen_distdate
+-- ), 0) AS cust_aropen_paid,
+-- item.item_number AS item_number,
+-- item.item_descrip1 AS item_descrip1,
+--
+-- CONCAT(
+-- invchead.invchead_billto_name,
+-- CASE WHEN LENGTH(invchead.invchead_billto_address1) > 0 THEN E'\n' || invchead.invchead_billto_address1 END,
+-- CASE WHEN LENGTH(invchead.invchead_billto_address2) > 0 THEN E'\n' || invchead.invchead_billto_address2 END,
+-- CASE WHEN LENGTH(invchead.invchead_billto_address3) > 0 THEN E'\n' || invchead.invchead_billto_address3 END,
+-- CASE WHEN LENGTH(invchead.invchead_billto_city) > 0 THEN E'\n' || invchead.invchead_billto_city END,
+-- CASE WHEN LENGTH(invchead.invchead_billto_state) > 0 THEN E'\n' || invchead.invchead_billto_state END,
+-- CASE WHEN LENGTH(invchead.invchead_billto_country) > 0 THEN E'\n' || invchead.invchead_billto_country END
+-- ) AS contacts,
+-- CONCAT(
+-- item.item_number,
+-- CASE WHEN LENGTH(item.item_descrip1) > 0 THEN E'\n' || item.item_descrip1 END
+-- ) AS product,
+-- CASE WHEN aropen.aropen_paid < aropen.aropen_amount THEN
+-- 'NO'
+-- ELSE
+-- 'YES'
+-- END AS is_paid
+-- FROM
+-- invcitem
+-- LEFT JOIN
+-- item
+-- ON
+-- item.item_id = invcitem_item_id
+-- LEFT JOIN
+-- invchead
+-- ON
+-- invchead.invchead_id = invcitem_invchead_id
+-- LEFT JOIN
+-- aropen AS aropen
+-- ON
+-- aropen.aropen_doctype = 'I'
+-- AND
+-- aropen.aropen_docnumber = invchead.invchead_invcnumber
+-- LEFT JOIN
+-- custinfo AS custinfo
+-- ON
+-- custinfo.cust_id = invchead.invchead_cust_id
+-- LEFT JOIN
+-- curr_symbol
+-- ON
+-- curr_symbol.curr_id = custinfo.cust_curr_id
+-- WHERE
+-- (
+-- (TRUE)
+-- <? if exists("cust_id") ?>
+-- AND
+-- (invchead.invchead_cust_id = <? value("cust_id") ?>)
+-- <? endif ?>
+-- <? if exists("status") ?>
+-- AND
+-- CASE WHEN <? value("status") ?> = 'paid' THEN
+-- (aropen.aropen_amount <= aropen.aropen_paid)
+-- ELSE
+-- (aropen.aropen_amount > aropen.aropen_paid)
+-- END
+-- <? endif ?>
+-- <? if exists("search") ?>
+-- AND
+-- (
+-- (SELECT invcitem_custpn FROM invcitem WHERE invcitem_invchead_id = invchead_id LIMIT 1) ILIKE <? value("search") ?>
+-- OR
+-- invchead_billto_registrar_organization ILIKE <? value("search") ?>
+-- )
+-- <? endif ?>
+-- <? if exists("start_dt") ?>
+-- AND
+-- (
+-- invchead.invchead_invcdate >= <? value("start_dt") ?>
+-- )
+-- <? endif ?>
+-- <? if exists("end_dt") ?>
+-- AND
+-- (
+-- invchead.invchead_invcdate <= <? value("end_dt") ?>
+-- )
+-- <? endif ?>
+-- )
+-- ORDER BY <? value("sort") ?> <? value("dir") ?>, invchead.invchead_id ASC, invcitem_linenumber ASC
projects / Pman.Xtuple / commitdiff