DELIMITER $$
CREATE PROCEDURE `getWarehouseWiseLedgerBalance`(IN wid VARCHAR(20),IN sd date,IN ed date)
BEGIN
DECLARE ware_cond text DEFAULT '';
IF wid != NULL THEN
     SET @ware_cond = CONCAT(' warehouse_id IN(',wid,')');
END IF ;
    (SELECT t.* ,ah.* from accounts_heads as ah left join (
            SELECT ledger_id,SUM(op_debit_total) as op_debit_total,SUM(op_credit_total) as op_credit_total,
            SUM(debit_total) as debit_total,SUM(credit_total) as credit_total,sum((op_debit_total+debit_total)-(op_credit_total+credit_total)) as closing from (
            SELECT ledger_id,SUM(amount) as op_debit_total,0 op_credit_total,0 debit_total,0 credit_total
                    FROM `journal_transactions` WHERE @ware_cond status=1 and type=0 and date < sd and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,SUM(amount) as op_credit_total,0 debit_total,0 credit_total
                    FROM `journal_transactions` WHERE @ware_cond status=1 and type=1 and date < sd and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,0 op_credit_total,SUM(amount) as debit_total,0 credit_total
                    FROM `journal_transactions` WHERE @ware_cond status=1 and type=0 and date >= sd
                and date <= '2022-03-09' and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,0 op_credit_total,0 debit_total,SUM(amount) as credit_total
                    FROM `journal_transactions` WHERE @ware_cond status=1 and type=1 and date >= sd and date <= ed
                and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                ) as t group by ledger_id
              ) as t on ah.id=t.ledger_id WHERE ah.type=0);
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `getWarehouseWiseLedgerBalance`(IN wid VARCHAR(20),IN sd date,IN ed date)
BEGIN
    (SELECT t.* ,ah.* from `accounts_heads` as ah left join (
            SELECT ledger_id,SUM(op_debit_total) as op_debit_total,SUM(op_credit_total) as op_credit_total,
            SUM(debit_total) as debit_total,SUM(credit_total) as credit_total,sum((op_debit_total+debit_total)-(op_credit_total+credit_total)) as closing from (
            SELECT ledger_id,SUM(amount) as op_debit_total,0 op_credit_total,0 debit_total,0 credit_total
                    FROM `journal_transactions` WHERE warehouse_id IN(wid) and status=1 and type=0 and date < sd and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,SUM(amount) as op_credit_total,0 debit_total,0 credit_total
                    FROM `journal_transactions` WHERE warehouse_id IN(wid) and status=1 and type=1 and date < sd and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,0 op_credit_total,SUM(amount) as debit_total,0 credit_total
                    FROM `journal_transactions` WHERE warehouse_id IN(wid) and status=1 and type=0 and date >= sd
                and date <= ed and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                    UNION
            SELECT ledger_id,0 op_debit_total,0 op_credit_total,0 debit_total,SUM(amount) as credit_total
                    FROM `journal_transactions` WHERE warehouse_id IN(wid) and status=1 and type=1 and date >= sd and date <= ed
                and ledger_id is not null and ledger_id > 0 GROUP by ledger_id
                ) as t group by ledger_id
              ) as t on ah.id=t.ledger_id WHERE ah.type=0);
END$$
DELIMITER ;