-- JSON START
-- {"TITLES" : ["Customer Name", "Document Type", "Document No.", "Document Date", "Due Date", "Currency", "Amount", "Applied", "Balance Due"]}
-- {"COLS" : ["cust_name", "doctype", "f_docnumber", "f_docdate", "f_duedate", "f_currency", "f_amount", "f_applied", "f_balance"]}
-- JSON END

SELECT CASE WHEN (araging_doctype = 'I') THEN 'Invoice'
            WHEN (araging_doctype = 'D') THEN 'DebitMemo'
            WHEN (araging_doctype = 'C') THEN 'CreditMemo'
            WHEN (araging_doctype = 'R') THEN 'Deposit'
            ELSE 'Misc.'
       END AS doctype,
       araging_docnumber AS f_docnumber,
       formatDate(CAST(araging_docdate AS DATE)) AS f_docdate,
       CASE WHEN (araging_doctype IN ('I','C','D')) THEN formatDate(araging_duedate)
            ELSE ''
       END AS f_duedate,
       formatMoney(araging_aropen_amount) AS f_amount,
       formatMoney(araging_aropen_amount - araging_total_val) AS f_applied,
       formatMoney(araging_total_val) AS f_balance,
       curr_symbol AS f_currency,
       cust_name
FROM araging(<? value("asofdate") ?>, true, false)
LEFT JOIN invchead ON (invchead_invcnumber = araging_docnumber)
LEFT JOIN curr_symbol ON (curr_id = invchead_curr_id)
LEFT JOIN custinfo ON (cust_id = invchead_cust_id)

WHERE ((araging_cust_id = <? value("cust_id") ?>)
   AND (abs(araging_aropen_amount) > 0)
   AND (NOT invchead_void)
   <? if exists("startDate") ?>
        AND (araging_docdate BETWEEN <? value("startDate") ?> AND <? value("asofdate") ?>)
   <? endif ?>
      )
ORDER BY araging_duedate;