Cash Flow

Functions added:
=============

CREATE FUNCTION `getAllCashAccounts`() RETURNS varchar(200) CHARSET latin1
BEGIN

DECLARE allCashAccounts varchar(500);

SET @allCashAccounts = (select GROUP_CONCAT(id) from acc_gl_account where id in (10) or parent_id in (10));

RETURN (select @allCashAccounts);

END


CREATE FUNCTION `getAllCashTransactionIds`(`officeId` INT,`startDate` DATE , `endDate` DATE) RETURNS varchar(2000) CHARSET latin1
BEGIN

DECLARE allCashAccounts varchar(500);
DECLARE allTxnIds varchar(2000);

SET @allCashAccounts = (select getAllCashAccounts());

SET @txnIds := (select GROUP_CONCAT(transaction_id) from (SELECT transaction_id, count(transaction_id) AS cnt FROM `acc_gl_journal_entry` WHERE `entry_date` BETWEEN startDate AND endDate and office_id = officeId and FIND_IN_SET(account_id,@allCashAccounts ) GROUP BY transaction_id HAVING cnt <=3) x);

RETURN (select @txnIds);

END



Query:
=====
select y.AccName, y.Payments, y.Receipts from (select @txnIds := getAllCashTransactionIds(5,'2018-10-11', '2018-10-12'), a.name AccName,  sum(amount) Payments, 0.0 Receipts from acc_gl_account a join  acc_gl_journal_entry e on a.id = e.account_id and a.id <>10  where FIND_IN_SET(transaction_id, @txnIds) and  type_enum = 1 group by e.account_id ) y