Untitled
unknown
python
a year ago
24 kB
6
Indexable
"\n \n select \n dummy.account_id,\n dummy.partner_id,\n --dummy.transaction_currency_id\n \n sum(period_dr) period_dr,\n sum(period_cr) period_cr,\n \n sum(begin_dr) begin_dr,\n sum(begin_cr) begin_cr,\n sum(end_dr) end_dr,\n sum(end_cr) end_cr,\n \n\n sum(transaction_begin_dr) transaction_begin_dr,\n sum(transaction_begin_cr) transaction_begin_cr,\n sum(transaction_period_dr) transaction_period_dr,\n sum(transaction_period_cr) transaction_period_cr,\n sum(transaction_end_dr) transaction_end_dr,\n sum(transaction_end_cr) transaction_end_cr\n \n \n from (\n \n \n select acc.internal_type, acc.currency_id, \n balance.account_id,\n balance.partner_id as partner_id,\n NULL::INTEGER as analytic_account_id,\n NULL::INTEGER as transaction_currency_id\n ,\n \n sum(begin_dr) begin_dr,\n sum(begin_cr) begin_cr,\n \n sum(period_dr) period_dr,\n sum(period_cr) period_cr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_dr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_cr,\n\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_dr) \n end transaction_period_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_cr) \n end transaction_period_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) < 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_cr\n \n from (\n \n /* Lay so du dau ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n aml.debit as begin_dr, aml.credit as begin_cr,\n 0.0 as period_dr, 0.0 as period_cr,\n \n case when (aml.amount_currency >= 0.0) then aml.amount_currency\n else 0.0\n end transaction_begin_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_begin_cr,\n 0.0 as transaction_period_dr, 0.0 as transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date < '2024-01-01'::date\n \n and acc.internal_type not in ('receivable', 'payable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n \n \n \n \n union all\n \n \n /* Lay phat sinh trong ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n 0 as begin_dr, 0 as begin_cr,\n aml.debit as period_dr, aml.credit as period_cr,\n \n 0 as transaction_begin_dr, 0 as transaction_begin_cr,\n case when (aml.amount_currency >= 0) then aml.amount_currency\n else 0.0\n end transaction_period_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date between '2024-01-01' and '2024-12-31'\n \n and acc.internal_type not in ('receivable', 'payable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n ) balance\n -- 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)\n left join account_account acc on acc.id=balance.account_id\n group by acc.internal_type, acc.currency_id, balance.account_id,balance.partner_id\n having (sum(balance.begin_dr) + sum(balance.begin_cr)) \n != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0\n union all \n \n select acc.internal_type, acc.currency_id, \n balance.account_id,\n balance.partner_id,\n NULL::INTEGER as analytic_account_id,\n NULL::INTEGER as transaction_currency_id\n ,\n \n sum(begin_dr) begin_dr,\n sum(begin_cr) begin_cr,\n \n sum(period_dr) period_dr,\n sum(period_cr) period_cr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_dr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_cr,\n\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_dr) \n end transaction_period_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_cr) \n end transaction_period_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) < 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_cr\n \n from (\n \n /* Lay so du dau ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n aml.debit as begin_dr, aml.credit as begin_cr,\n 0.0 as period_dr, 0.0 as period_cr,\n \n case when (aml.amount_currency >= 0.0) then aml.amount_currency\n else 0.0\n end transaction_begin_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_begin_cr,\n 0.0 as transaction_period_dr, 0.0 as transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date < '2024-01-01'::date\n \n and acc.internal_type in ('receivable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n \n \n \n \n union all\n \n \n /* Lay phat sinh trong ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n 0 as begin_dr, 0 as begin_cr,\n aml.debit as period_dr, aml.credit as period_cr,\n \n 0 as transaction_begin_dr, 0 as transaction_begin_cr,\n case when (aml.amount_currency >= 0) then aml.amount_currency\n else 0.0\n end transaction_period_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date between '2024-01-01' and '2024-12-31'\n \n and acc.internal_type in ('receivable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n ) balance\n -- 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)\n left join account_account acc on acc.id=balance.account_id\n group by acc.internal_type, acc.currency_id, \n balance.account_id, \n balance.partner_id\n --balance.transaction_currency_id\n \n having (sum(balance.begin_dr) + sum(balance.begin_cr)) \n != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0\n union all \n \n select acc.internal_type, acc.currency_id, \n balance.account_id,\n balance.partner_id,\n NULL::INTEGER as analytic_account_id,\n NULL::INTEGER as transaction_currency_id\n ,\n \n sum(begin_dr) begin_dr,\n sum(begin_cr) begin_cr,\n \n sum(period_dr) period_dr,\n sum(period_cr) period_cr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) >= 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_dr,\n case when (sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr)) < 0\n then abs(sum(begin_dr) - sum(begin_cr) + sum(period_dr) - sum(period_cr))\n else 0 end end_cr,\n\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) > sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when sum(transaction_begin_dr) < sum(transaction_begin_cr) \n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr))\n else 0 end \n end transaction_begin_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_dr) \n end transaction_period_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else sum(transaction_period_cr) \n end transaction_period_cr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) >= 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_dr,\n case when (acc.internal_type = 'liquidity' and acc.currency_id is null) then 0.0\n else case when (sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr)) < 0\n then abs(sum(transaction_begin_dr) - sum(transaction_begin_cr) \n + sum(transaction_period_dr) - sum(transaction_period_cr))\n else 0 end \n end transaction_end_cr\n \n from (\n \n /* Lay so du dau ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n aml.debit as begin_dr, aml.credit as begin_cr,\n 0.0 as period_dr, 0.0 as period_cr,\n \n case when (aml.amount_currency >= 0.0) then aml.amount_currency\n else 0.0\n end transaction_begin_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_begin_cr,\n 0.0 as transaction_period_dr, 0.0 as transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date < '2024-01-01'::date\n \n and acc.internal_type in ('payable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n \n \n \n \n union all\n \n \n /* Lay phat sinh trong ky */\n select aml.id,\n aml.account_id, aml.partner_id, aml.analytic_account_id,\n aml.currency_id as transaction_currency_id,\n aml.company_currency_id as company_currency_id,\n \n 0 as begin_dr, 0 as begin_cr,\n aml.debit as period_dr, aml.credit as period_cr,\n \n 0 as transaction_begin_dr, 0 as transaction_begin_cr,\n case when (aml.amount_currency >= 0) then aml.amount_currency\n else 0.0\n end transaction_period_dr,\n case when (aml.amount_currency <= 0.0) then abs(aml.amount_currency)\n else 0.0\n end transaction_period_cr\n from account_move_line aml\n join account_account acc on aml.account_id = acc.id\n join account_move am on aml.move_id = am.id\n join account_journal ajn on am.journal_id = ajn.id\n join res_partner rpt on aml.partner_id = rpt.id\n where aml.date between '2024-01-01' and '2024-12-31'\n \n and acc.internal_type in ('payable')\n \n and am.state = 'posted'\n and aml.account_id IN (9)\n \n \n and aml.partner_id in (817)\n \n 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)\n \n \n \n ) balance\n -- 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)\n left join account_account acc on acc.id=balance.account_id\n group by acc.internal_type, acc.currency_id, \n balance.account_id, \n balance.partner_id\n \n having (sum(balance.begin_dr) + sum(balance.begin_cr)) \n != 0.0 or (sum(balance.period_dr) + sum(balance.period_cr)) != 0.0\n \n ) dummy\n \n group by \n dummy.account_id,\n dummy.partner_id\n --dummy.transaction_currency_id\n \n "Editor is loading...
Leave a Comment