queries

INSERT INTO `forms` (`id`, `form_name`, `module_name`, `module_id`, `path`, `menu_order`, `is_visible`) VALUES (NULL, 'expense reports', 'finance', '25', 'finance/reports/expense-reports', '35', '1'), (NULL, 'invoice reports', 'finance', '25', 'finance/reports/invoice-reports', '40', '1'), (NULL, 'income statement', 'finance', '25', 'finance/reports/income-statement', '70', '1'), (NULL, 'trial balance', 'finance', '25', 'finance/reports/trial-balance', '75', '1'), (NULL, 'balance sheet', 'finance', '25', 'finance/reports/balance-sheet', '80', '1');

DELIMITER $$
CREATE PROCEDURE `sp_trial_balance`(IN `asofdate` DATE)
BEGIN
    SELECT
        l.parent_account,
        COALESCE(SUM(l.debit),0) AS debit,
        COALESCE(SUM(l.credit),0) AS credit,
        SUM(l.debit) - SUM(l.credit) AS balance
    FROM
        ledgers l
    WHERE
        (l.date <= asofdate) AND (l.deleted_at IS NULL)
    GROUP BY
        l.parent_account
    ORDER BY
        l.parent_account;
END$$
DELIMITER ;