



-- the account transaction window.


-- JSON START
-- {
 
-- 'cols' : [
--  { 'header' : 'Date' ,  'dataIndex': 'gltrans_date' },
--  { 'header' : 'Created' ,  'dataIndex': 'gltrans_created' },
--  { 'header' : 'By' ,  'dataIndex': 'gltrans_username' },
--  { 'header' : 'Source' ,  'dataIndex'  : 'gltrans_source'  },
--  { 'header' : 'Doc type' ,  'dataIndex'  : 'gltrans_doctype'  },
--  { 'header' : 'Doc No#' ,   'dataIndex'  : 'gltrans_docnumber'  },
--  { 'header' : 'Rel. Accounts' ,  'dataIndex'  : 'related_accounts'  },
--  { 'header' : 'Notes' ,  'dataIndex'  : 'gltrans_notes'  },
--  { 'header' : 'Currency' ,  'dataIndex'  : 'gltrans_base_curr'  },
--  { 'header' : 'Credit' ,  'dataIndex'  : 'gltrans_credit_amount'  },
--  { 'header' : 'Debit' ,  'dataIndex'  : 'gltrans_debit_amount'  },
--  { 'header' : 'Amount' ,  'dataIndex'  : 'gltrans_balance'  },

-- ]
 
-- }
-- JSON END


 
SELECT 
        rank,
        row_number,
        gltrans_id,
        to_char(gltrans_date,'DD/Mon/YYYY') AS gltrans_date,
        to_char(gltrans_created,'DD/Mon/YYYY') AS gltrans_created,

        gltrans_source,
        gltrans_doctype,
        gltrans_username,
        gltrans_docnumber,
        gltrans_notes,
        gltrans_sequence,
        gltrans_credit_amount,
        gltrans_debit_amount,
        <? if exists("search") ?>
            '-'
        <? else ?>
            gltrans_balance
        <? endif ?> AS gltrans_balance,
        
        (
            SELECT 
                    curr_name
            FROM
                    curr_symbol
            WHERE
                    curr_base
            LIMIT 1
        ) AS gltrans_base_curr,
        
        
        <? if exists("accnt_id") ?>
            array_to_string(array (
                SELECT
                        accnt_descrip
                    FROM
                        gltrans glt
                    LEFT JOIN
                        accnt
                    ON
                        accnt_id = glt.gltrans_accnt_id
                    WHERE
                        glt.gltrans_accnt_id != <? value("accnt_id") ?>
                    AND
                        glt.gltrans_sequence = zz.gltrans_sequence
                    ORDER BY
                        accnt_descrip ASC
                        
            ), ', ') as related_accounts,
            array_to_string(array (
                SELECT
                        accnt_id
                    FROM
                        gltrans glt
                    LEFT JOIN
                        accnt
                    ON
                        accnt_id = glt.gltrans_accnt_id
                    WHERE
                        glt.gltrans_accnt_id != <? value("accnt_id") ?>
                    AND
                        glt.gltrans_sequence = zz.gltrans_sequence
                    ORDER BY
                        accnt_descrip ASC
                        
            ), ',') as related_account_ids
            
        <? else ?>                            
                '' as related_accounts,
                '' as related_account_ids,
        <? endif ?>
        
        -- related accounts.
       
        
        

FROM
(
    SELECT 
            rank,
            row_number,
            gltrans_id,
            gltrans_date,
            gltrans_created,
            gltrans_source,
            gltrans_doctype,
            gltrans_username,
            gltrans_docnumber,
            gltrans_notes,
            gltrans_sequence,
            gltrans_credit_amount,
            gltrans_debit_amount,
            SUM(ROUND(gltrans_balance,2)) OVER (ORDER BY gltrans_date ASC, gltrans_id ASC) AS gltrans_balance
            
    FROM
    (
        SELECT  
                rank,
                row_number,
                gltrans_id,
                gltrans_date,
                gltrans_created,
                gltrans_source,
                gltrans_doctype,
                gltrans_username,
                gltrans_docnumber,
                gltrans_notes,
                gltrans_sequence,
                gltrans_credit_amount,
                gltrans_debit_amount,
                <? if exists("search") ?>
                    0
                <? else ?>
                    CASE WHEN row_number = 1 THEN
                    (
                        trialbal_byperiod(gltrans_accnt_id, date(gltrans_date - INTERVAL '1 MONTH'))
                        +
                        (
                            SELECT 
                                    COALESCE(SUM(
                                        CASE WHEN accnt_type IN ('A','E') THEN
                                            gltrans_amount * -1
                                        ELSE
                                            gltrans_amount
                                        END
                                    ),0)
                            FROM

                                    gltrans
                            WHERE
                                    gltrans_date >= date_trunc('MONTH',x.gltrans_date)::date 
                                    AND 
                                    gltrans_date <= date(x.gltrans_date - INTERVAL '1 DAY')
                                    AND
                                    gltrans_accnt_id = x.gltrans_accnt_id
                                    AND
                                    NOT gltrans_deleted
                        )
                        +
                        (
                            SELECT 
                                    COALESCE(SUM(
                                        CASE WHEN accnt_type IN ('A','E') THEN
                                            gltrans_amount * -1
                                        ELSE
                                            gltrans_amount
                                        END
                                    ),0)
                            FROM
                                (
                                    SELECT 
                                            gltrans_amount,
                                            gltrans_date,
                                            gltrans_id,
                                            rank() OVER (PARTITION BY gltrans_date ORDER BY gltrans_id ASC) AS rank
                                    FROM
                                            gltrans
                                    WHERE
                                            gltrans_date = x.gltrans_date
                                            AND
                                            gltrans_accnt_id = x.gltrans_accnt_id
                                            AND
                                            NOT gltrans_deleted

                                    ORDER BY gltrans_date ASC, gltrans_id ASC


                                ) as y
                            WHERE
                                y.rank <= x.rank
                        )
                    )
                    ELSE
                        gltrans_amount
                    END
                <? endif ?>
                 AS gltrans_balance

        FROM
        (

            SELECT 
                    gltrans_id,
                    gltrans_date,
                    gltrans_created,
                    gltrans_source,
                    gltrans_doctype,
                    gltrans_username,
                    gltrans_docnumber,
                    gltrans_notes,
                    gltrans_sequence,
                    gltrans_accnt_id,
                    accnt_type,

                    CASE WHEN accnt_type IN ('A','E') THEN
                        gltrans_amount * -1
                    ELSE
                        gltrans_amount
                    END AS gltrans_amount,

                    CASE WHEN (gltrans_amount < 0) THEN 
                        ABS(gltrans_amount)
                    END AS gltrans_debit_amount,

                    CASE WHEN (gltrans_amount > 0) THEN 
                        gltrans_amount
                    END AS gltrans_credit_amount,

                    rank() OVER (PARTITION BY gltrans_date ORDER BY gltrans_id ASC) AS rank,
                    row_number() OVER (ORDER BY gltrans_date ASC, gltrans_id ASC) AS row_number

            FROM
                    gltrans

            LEFT JOIN 
                    accnt
            ON
                    accnt_id = gltrans_accnt_id

            WHERE
                    (
                        (NOT gltrans_deleted)
                        <? if exists("endDate") ?>
                            AND (gltrans_date <= <? value("endDate") ?>)
                        <? endif ?>
                        <? if exists("startDate") ?>
                            AND (gltrans_date >= <? value("startDate") ?>)
                        <? else ?>
                            AND (gltrans_date >= startOfTime()::date)
                        <? endif ?>
                        <? if exists("accnt_id") ?>
                            AND (gltrans_accnt_id = <? value("accnt_id") ?>)
                        <? endif ?>
                        <? if exists("search") ?>
                            AND (
                                    (gltrans_docnumber LIKE <? value("search") ?> || '%')
                                    OR
                                    (gltrans_source ILIKE '%' || <? value("search") ?> || '%')
                                    OR
                                    (gltrans_notes ILIKE '%' || <? value("search") ?> || '%')  
                                )
                        <? endif ?>

                    )


            ORDER BY gltrans_date ASC, gltrans_id ASC
        ) AS x  
    ) AS z
ORDER BY gltrans_date DESC, gltrans_id DESC    
) AS zz
