gistfile1.txt

$data = DB::connection('mysql')->select("Select '007' as po_code,'01' as company_code,'HO' as company_branch_code
,'01' as finance_code,'01' as project_code,'001' as component_code
,'01/2023' as mnyr,'CORE' as coa_id,l1_code,l2_code,l3_code,l4_code,l5_code
,0 as acctype,accgroup,SUM(Case WHen t.acc_group=4 Then cur_credit-cur_debit When t.acc_group Not In(2,4) Then cur_credit Else 0 End) as this_month_cash
,SUM(Case WHen t.acc_group=4 Then Op_credit-Op_debit+cur_credit-cur_debit WHen t.acc_group Not In(2,4) Then op_credit+cur_credit Else 0 End) as this_fy_cash
,0 as this_month_noncash,0 as last_june
,0 as bal_dr,0 as bal_cr,0 as cum_bal_cr
,0 as cum_bal_dr
From olrs_trailbalance t Inner Join (select distinct acc_chart,olrs_acc_chart from acc_map) a ON t.acc_id=a.acc_chart
Inner Join po_a_acc_head pa ON a.olrs_acc_chart=pa.Id
Where pa.accgroup='RP'
AND l1_code='005'
AND t.acc_id not in(".$cash_code."
)
AND t.acc_id not in(".$bank_code."
)
AND t.type = 'imicrof'
Group By l1_code,l2_code,l3_code,l4_code,l5_code,accgroup
 	Union All
Select '007' as po_code,'01' as company_code,'HO' as company_branch_code
,'01' as finance_code,'01' as project_code,'001' as component_code
,'01/2023' as mnyr,'CORE' as coa_id,l1_code,l2_code,l3_code,l4_code,l5_code
,0 as acctype,accgroup,SUM(Case WHen t.acc_group=2 Then cur_debit-cur_credit When t.acc_group not in (2,4) Then cur_debit Else 0 End) as this_month_cash
,SUM(Case WHen t.acc_group=2 Then Op_debit-Op_credit+cur_debit-cur_credit WHen t.acc_group Not In(2,4) Then op_debit+cur_debit Else 0 End) as this_fy_cash
,0 as this_month_noncash,0 as last_june
,0 as bal_dr,0 as bal_cr,0 as cum_bal_cr
,0 as cum_bal_dr
From olrs_trailbalance t Inner Join (select distinct acc_chart,olrs_acc_chart from acc_map) a ON t.acc_id=a.acc_chart
Inner Join po_a_acc_head pa ON a.olrs_acc_chart=pa.Id
Where pa.accgroup='RP'
AND l1_code='006'
AND t.acc_id not in(".$cash_code."
)
AND t.acc_id not in(".$bank_code."
)
AND t.type = 'imicrof'
Group By l1_code,l2_code,l3_code,l4_code,l5_code,accgroup
		Union All
Select '007' as po_code,'01' as company_code,'HO' as company_branch_code
,'01' as finance_code,'01' as project_code,'001' as component_code
,'01/2023' as mnyr,'CORE' as coa_id,l1_code,l2_code,l3_code,l4_code,l5_code
,0 as acctype,accgroup,SUM(bal_lastYear+op_debit-op_credit+ previouse_journal_debit-previouse_journal_credit) as this_month_cash
,SUM(bal_lastYear) as this_fy_cash
,0 as this_month_noncash,0 as last_june
,0 as bal_dr,0 as bal_cr,0 as cum_bal_cr
,0 as cum_bal_dr
From olrs_trailbalance t Inner Join (select distinct acc_chart,olrs_acc_chart from acc_map) a ON t.acc_id=a.acc_chart
Inner Join po_a_acc_head pa ON a.olrs_acc_chart=pa.Id
Where report_date='2023-01-31' AND  pa.accgroup='RP' AND l1_code='005'
AND (
t.acc_id in(".$cash_code.")
OR t.acc_id in(".$bank_code.")
)
AND t.type = 'imicrof'
Group By l1_code,l2_code,l3_code,l4_code,l5_code,accgroup
 		Union All
Select '007' as po_code,'01' as company_code,'HO' as company_branch_code
,'01' as finance_code,'01' as project_code,'001' as component_code
,'01/2023' as mnyr,'CORE' as coa_id,l1_code,l2_code,l3_code,l4_code,l5_code
,0 as acctype,accgroup,SUM(bal_debit-bal_credit) as this_month_cash,SUM(bal_debit-bal_credit)  as this_fy_cash
,0 as this_month_noncash,0 as last_june,0 as bal_dr,0 as bal_cr,0 as cum_bal_cr,0 as cum_bal_dr
From olrs_trailbalance t Inner Join (select distinct acc_chart,olrs_acc_chart from acc_map) a ON t.acc_id=a.acc_chart
Inner Join po_a_acc_head pa ON a.olrs_acc_chart=pa.Id
Where report_date='2023-01-31' and pa.accgroup='RP' AND l1_code='006'
AND (
t.acc_id in(".$cash_code.")
OR t.acc_id in(".$bank_code.")
)
AND t.type = 'imicrof'
Group By l1_code,l2_code,l3_code,l4_code,l5_code,accgroup");