metasql/registrar.contracts.sql

diff --git a/metasql/registrar.contracts.sql b/metasql/registrar.contracts.sql
index 4d34c94..4ffa012 100644 (file)
--- a/metasql/registrar.contracts.sql
+++ b/metasql/registrar.contracts.sql
@@ -1,9 +1,4 @@
 
--- JSON START
--- {"TITLES" : ["Created", "Domain", "Product", "Contacts", "Value", "Balance"] }
--- {"COLS" : ["f_invchead_invcdate", "domain", "product", "contacts", "amount", "balance"]}
--- JSON END
-
 SELECT 
         invchead_id,
         invchead_invcdate,
@@ -43,15 +38,7 @@ FROM
             custinfo.cust_name AS cust_name,
             aropen.aropen_amount AS aropen_amount,
             aropen.aropen_paid AS aropen_paid,
-            CONCAT(
-                invchead_billto_registrar_organization, E'\n', invchead_billto_name,
-                CASE WHEN LENGTH(invchead_billto_address1) > 0 THEN E'\n' || invchead_billto_address1 END,
-                CASE WHEN LENGTH(invchead_billto_address2) > 0 THEN E'\n' || invchead_billto_address2 END,
-                CASE WHEN LENGTH(invchead_billto_address3) > 0 THEN E'\n' || invchead_billto_address3 END,
-                CASE WHEN LENGTH(invchead_billto_city) > 0 THEN E'\n' || invchead_billto_city END,
-                CASE WHEN LENGTH(invchead_billto_state) > 0 THEN E'\n' || invchead_billto_state END,
-                CASE WHEN LENGTH(invchead_billto_country) > 0 THEN E'\n' || invchead_billto_country END
-            ) AS contacts,
+            
             COALESCE(currtocurr(
                 aropen.aropen_curr_id,
                 custinfo.cust_curr_id,
@@ -79,17 +66,21 @@ FROM
                     ) AS i
             ) AS product
     FROM
+            invcitem
+    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_invcnumber
+            aropen.aropen_docnumber =  invchead.invchead_invcnumber
     LEFT JOIN
             custinfo AS custinfo
     ON
-            custinfo.cust_id = invchead_cust_id
+            custinfo.cust_id = invchead.invchead_cust_id
     WHERE
         (
                 (TRUE)
@@ -97,22 +88,14 @@ FROM
                 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") ?>
+                        (SELECT invcitem_custpn FROM invcitem WHERE invcitem_invchead_id = invchead.invchead_id LIMIT 1) ILIKE '%' | <? value("search") ?> | '%'
                         OR
-                        invchead_billto_registrar_organization ILIKE <? value("search") ?>
+                        invchead_billto_registrar_organization ILIKE '%' | <? value("search") ?> | '%'
                         OR
-                        invchead_billto_name ILIKE <? value("search") ?>
+                        invchead_billto_name ILIKE '%' | <? value("search") ?> | '%'
                     )
                 <? endif ?>
         )