This function was easy to write after writing the Transaction Open Batches Stored Procedure.
<?php
--
-- Transaction Open Units Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_OpenUnits;
DELIMITER |
CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)
BEGIN
SELECT
`transaction`.`unit_number` AS unit_no,
`category`.`category_id` AS category_id,
`category`.`calc_name` AS category,
IFNULL(
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `unit_number` = `unit_no` AND `credit_account_id` = unit_account_id
),0)
-
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `unit_number` = `unit_no` AND `debit_account_id` = unit_account_id
),0)
,0) AS unit_balance
FROM
`transaction`
LEFT JOIN `category` ON `transaction`.`category_id` = `category`.`category_id`
WHERE
`transaction`.`unit_number` > 0
GROUP BY
`transaction`.`unit_number`
HAVING
unit_balance > 0;
END
|
DELIMITER ;
?>
Post new comment