Untitled
unknown
pgsql
a year ago
15 kB
6
Indexable
select
dummy.account_id,
dummy.partner_id,
--dummy.transaction_currency_id sum(period_dr) period_dr,
sum(period_cr) period_cr, sum(begin_dr) begin_dr, sum(begin_cr) begin_cr, sum(end_dr) end_dr, sum(end_cr) end_cr, sum(transaction_begin_dr) transaction_begin_dr,
sum(transaction_begin_cr) transaction_begin_cr,
sum(transaction_period_dr) transaction_period_dr,
sum(transaction_period_cr) transaction_period_cr,
sum(transaction_end_dr) transaction_end_dr,
sum(transaction_end_cr) transaction_end_cr
from ( select acc.internal_type, acc.currency_id, balance.account_id, balance.partner_id as partner_id, NULL::INTEGER as analytic_account_id, NULL::INTEGER as transaction_currency_id, sum(begin_dr) begin_dr, sum(begin_cr) begin_cr, sum(period_dr) period_dr,
sum(period_cr) period_cr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_dr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else 0 end
end transaction_begin_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else 0 end
end transaction_begin_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_dr)
end transaction_period_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_cr)
end transaction_period_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) < 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_cr from ( /* Lay so du dau ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,aml.debit as begin_dr, aml.credit as begin_cr,0.0 as period_dr, 0.0 as period_cr, case when (aml.amount_currency >= 0.0) then aml.amount_currencyelse 0.0end transaction_begin_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_begin_cr,0.0 as transaction_period_dr, 0.0 as transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date < '2024-01-01'::date and acc.internal_type not in ('receivable', 'payable')
and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) union all /* Lay phat sinh trong ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,0 as begin_dr, 0 as begin_cr,aml.debit as period_dr, aml.credit as period_cr,0 as transaction_begin_dr, 0 as transaction_begin_cr,case when (aml.amount_currency >= 0) then aml.amount_currencyelse 0.0end transaction_period_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date between '2024-01-01' and '2024-12-31' and acc.internal_type not in ('receivable', 'payable') and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) ) balance -- THANH 20210908 khổng hiển thị Amount Currency đối với trường hợp các tài khoản được chọn để xem báo cáo đều là tài khoản loại liquidity và trùng với company currency (field currency_id null trong tài khoản) left join account_account acc on acc.id=balance.account_idgroup by acc.internal_type, acc.currency_id, balance.account_id,balance.partner_idhaving (sum(balance.begin_dr) + sum(balance.begin_cr)) != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0 union all select acc.internal_type, acc.currency_id, balance.account_id,balance.partner_id,NULL::INTEGER as analytic_account_id,NULL::INTEGER as transaction_currency_id
, sum(begin_dr) begin_dr, sum(begin_cr) begin_cr, sum(period_dr) period_dr,
sum(period_cr) period_cr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_dr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else 0 end
end transaction_begin_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else
0 end
end transaction_begin_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_dr)
end transaction_period_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_cr)
end transaction_period_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) < 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_cr from ( /* Lay so du dau ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,aml.debit as begin_dr, aml.credit as begin_cr,0.0 as period_dr, 0.0 as period_cr, case when (aml.amount_currency >= 0.0) then aml.amount_currencyelse 0.0end transaction_begin_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_begin_cr,0.0 as transaction_period_dr, 0.0 as transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date < '2024-01-01'::date and acc.internal_type in ('receivable')
and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) union all /* Lay phat sinh trong ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,0 as begin_dr, 0 as begin_cr,aml.debit as period_dr, aml.credit as period_cr,0 as transaction_begin_dr, 0 as transaction_begin_cr,case when (aml.amount_currency >= 0) then aml.amount_currencyelse 0.0end transaction_period_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date between '2024-01-01' and '2024-12-31' and acc.internal_type in ('receivable') and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) ) balance -- THANH 20210908 khổng hiển thị Amount Currency đối với trường hợp các tài khoản được chọn để xem báo cáo đều là tài khoản loại liquidity và trùng với company currency (field currency_id null trong tài khoản) left join account_account acc on acc.id=balance.account_idgroup by acc.internal_type, acc.currency_id, balance.account_id, balance.partner_id--balance.transaction_currency_id
having (sum(balance.begin_dr) + sum(balance.begin_cr)) != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0 union all select acc.internal_type, acc.currency_id, balance.account_id,balance.partner_id,NULL::INTEGER as analytic_account_id,NULL::INTEGER as transaction_currency_id
, sum(begin_dr) begin_dr, sum(begin_cr) begin_cr, sum(period_dr) period_dr,
sum(period_cr) period_cr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_dr,
case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0 then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))else 0 end end_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else 0 end
end transaction_begin_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)) else 0 end
end transaction_begin_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_dr)
end transaction_period_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else sum(transaction_period_cr)
end transaction_period_cr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_dr,
case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0 else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) < 0 then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr)
+ sum(transaction_period_dr) - sum(transaction_period_cr)) else 0 end
end transaction_end_cr from ( /* Lay so du dau ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,aml.debit as begin_dr, aml.credit as begin_cr,0.0 as period_dr, 0.0 as period_cr, case when (aml.amount_currency >= 0.0) then aml.amount_currencyelse 0.0end transaction_begin_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_begin_cr,0.0 as transaction_period_dr, 0.0 as transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date < '2024-01-01'::date and acc.internal_type in ('payable')
and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) union all /* Lay phat sinh trong ky */
select aml.id,aml.account_id, aml.partner_id, aml.analytic_account_id,aml.currency_id as transaction_currency_id,aml.company_currency_id as company_currency_id,0 as begin_dr, 0 as begin_cr,aml.debit as period_dr, aml.credit as period_cr,0 as transaction_begin_dr, 0 as transaction_begin_cr,case when (aml.amount_currency >= 0) then aml.amount_currencyelse 0.0end transaction_period_dr,case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)else 0.0end transaction_period_cr
from account_move_line amljoin account_account acc on aml.account_id = acc.idjoin account_move am on aml.move_id = am.idjoin account_journal ajn on am.journal_id = ajn.idjoin res_partner rpt on aml.partner_id = rpt.id
where aml.date between '2024-01-01' and '2024-12-31' and acc.internal_type in ('payable') and am.state = 'posted'
and aml.account_id IN (9) and aml.partner_id in (817) and am.journal_id IN (1,2,3,6,4,70,69,67,65,66,64,68,94,95,96,8,25,10,5,26,22,23,24,21) ) balance -- THANH 20210908 khổng hiển thị Amount Currency đối với trường hợp các tài khoản được chọn để xem báo cáo đều là tài khoản loại liquidity và trùng với company currency (field currency_id null trong tài khoản) left join account_account acc on acc.id=balance.account_idgroup by acc.internal_type, acc.currency_id, balance.account_id, balance.partner_id
having (sum(balance.begin_dr) + sum(balance.begin_cr)) != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0 ) dummy group by
dummy.account_id,
dummy.partner_id
--dummy.transaction_currency_id Editor is loading...
Leave a Comment