27/09/2024--Karthik

DELIMITER $$
CREATE  PROCEDURE `GetJournalEntrySummary`()
BEGIN
    SELECT 
        a.journal_gid, 
        a.transaction_type, 
        a.journal_refno, 
        DATE_FORMAT(a.transaction_date, '%d-%m-%Y') AS transaction_date, 
        a.remarks AS entry_remarks,
        a.document_path,
        e.account_name AS voucher_type,
        e.account_gid,
        e.accountgroup_gid,
        b.remarks AS detail_remarks, 
        b.journaldtl_gid, 
        (CASE WHEN b.journal_type = 'dr' THEN '0.00' 
              WHEN b.journal_type = 'cr' THEN FORMAT(b.transaction_amount, 2) 
         END) AS credit_amount, 
        (CASE WHEN b.journal_type = 'cr' THEN '0.00' 
              WHEN b.journal_type = 'dr' THEN FORMAT(b.transaction_amount, 2) 
         END) AS debit_amount, 
        b.journal_gid AS detail_journal_gid, 
        b.account_gid, 
        COALESCE(FORMAT((SELECT SUM(transaction_amount) 
                         FROM acc_trn_journalentrydtl 
                         WHERE journal_gid = a.journal_gid AND journal_type = 'cr'), 2), '0.00') AS total_credit_amount, 
        COALESCE(FORMAT((SELECT SUM(transaction_amount) 
                         FROM acc_trn_journalentrydtl 
                         WHERE journal_gid = a.journal_gid AND journal_type = 'dr'), 2), '0.00') AS total_debit_amount 
    FROM 
        acc_trn_journalentry a
    LEFT JOIN 
        acc_trn_journalentrydtl b ON a.journal_gid = b.journal_gid 
    LEFT JOIN 
        acc_mst_tchartofaccount e ON e.account_gid = b.account_gid 
    WHERE 
        a.invoice_flag = 'Y' 
    ORDER BY 
        a.transaction_date DESC, 
        a.journal_gid DESC;
END$$
DELIMITER ;