galovics commented on code in PR #2291: URL: https://github.com/apache/fineract/pull/2291#discussion_r864716282
########## fineract-provider/src/main/resources/db/changelog/tenant/parts/0012_pentaho_reports_to_table.xml: ########## @@ -0,0 +1,247 @@ +<?xml version="1.1" encoding="UTF-8" standalone="no"?> +<!-- + + Licensed to the Apache Software Foundation (ASF) under one + or more contributor license agreements. See the NOTICE file + distributed with this work for additional information + regarding copyright ownership. The ASF licenses this file + to you under the Apache License, Version 2.0 (the + "License"); you may not use this file except in compliance + with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, + software distributed under the License is distributed on an + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + KIND, either express or implied. See the License for the + specific language governing permissions and limitations + under the License. + +--> + +<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"; xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"; xmlns:pro="http://www.liquibase.org/xml/ns/pro"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd";> + <changeSet author="fineract" id="1-mysql" context="mysql"> + <insert tableName="stretchy_report"> + <column name="report_name" value="Trial Balance Table"/> + <column name="report_type" value="Table"/> + <column name="report_subtype"/> + <column name="report_category" value="Accounting"/> + <column name="report_sql" value="select * from (select debits.glcode as 'glcode', debits.name as 'name', IF(debits.type = 1 or debits.type = 5, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),null) as 'debit', IF(debits.type = 4 or debits.type = 3 or debits.type = 2, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),null) as 'credit' from (select acc_gl_account.gl_code as 'glcode', name,sum(amount) as 'debitamount', acc_gl_account.classification_enum as 'type' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glcode', name as 'name', sum(amount) as 'creditamount', acc_gl_account.classification_enum as 'type' from acc_gl _journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', IF(credits.type = 1 or credits.type = 5, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),null) as 'debit', IF(credits.type = 4 or credits.type = 3 or credits.type = 2, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),null) as 'credit' from (select acc_gl_account.gl_code as 'glcode', name, sum(amount) as 'debitamount', acc_gl_account.classification_enum as 'type' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_journal_entry.entry_date between date('$ {startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name as 'name', sum(amount) as 'creditamount', acc_gl_account.classification_enum as 'type' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode) as fullouterjoinresult order by glcode "/> + <column name="description" value="Trial Balance Report"/> + <column name="core_report" valueBoolean="true"/> + <column name="use_report" valueBoolean="true"/> + <column name="self_service_user_report" valueBoolean="false"/> + </insert> + <insert tableName="stretchy_report"> + <column name="report_name" value="GeneralLedgerReport Table"/> + <column name="report_type" value="Table"/> + <column name="report_subtype"/> + <column name="report_category" value="Accounting"/> + <column name="report_sql" value="select details.edate entry_date, sum(details.debit_amount) debit_amount, sum(details.credit_amount) credit_amount, details.description, ifnull(opb.openingbalance,0) openingbalance, if (details.manual_entry=1,details.id,'0system') transtype, if (actype in (1,5), (sum(details.debit_amount) - sum(details.credit_amount)), (sum(details.credit_amount) - sum(details.debit_amount))) as cumulative_sum from ( select a.account_id acid1 ,concat(gl.gl_code,'-',gl.name) as report_header ,gl.classification_enum actype ,gl.gl_code as reportid ,j1.entry_date edate ,concat(gl1.gl_code,'-',gl1.name) as account_name ,if (j1.type_enum=1, j1.amount, 0) as debit_amount ,if (j1.type_enum=2, j1.amount , 0) as credit_amount ,j1.id ,j1.office_id ,j1.transaction_id , j1.type_enum ,j1.office_running_balance as aftertxn ,j1.description as description ,j1.transaction_id as transactionid ,a.manual_entry from acc_gl_journal_entry j1 inner join (select distinct je.transaction_id tid,je.account_id,je.manual_entry from m_office o left join m_office ounder on ounder.hierarchy like concat(o.hierarchy,'%') inner join acc_gl_journal_entry je on je.office_id = ounder.id where je.account_id =${GLAccountNO} and o.id = ${officeId} and je.entry_date between ${startDate} and ${endDate})a on a.tid = j1.transaction_id and j1.account_id <> ${GLAccountNO} left join acc_gl_account gl on gl.id = a.account_id left join acc_gl_account gl1 on gl1.id = j1.account_id order by j1.entry_date, j1.id) details left join ( select je.account_id acid2, if(aga1.classification_enum in (1,5), (sum(if(je.type_enum=2,ifnull(je.amount,0),0))- sum(if(je.type_enum=1,ifnull(je.amount,0),0))), (sum(if(je.type_enum=1,ifnull(je.amount,0),0))- sum(if(je.type_enum=2,ifnull(je.amount,0),0)))) openingbalance from m_office o left join m_office ounder on ounder.hierarchy like concat(o.hierarchy,'%') left join acc_gl_journal_entry je on je.office_id = ounder.id left join acc _gl_account aga1 on aga1.id=je.account_id where je.entry_date <= DATE_SUB(${startDate},INTERVAL 1 day) and je.office_running_balance is not null and (o.id=${officeId}) and je.account_id = ${GLAccountNO} group by je.account_id )opb on opb.acid2=details.acid1 left join ( select name branchname from m_office mo where mo.id=1 )branch on details.office_id=${officeId} group by details.edate, details.acid1, details.report_header, details.reportid, details.account_name, branch.branchname ,transtype, details.description, openingbalance "/> + <column name="description"/> + <column name="core_report" valueBoolean="false"/> + <column name="use_report" valueBoolean="true"/> + <column name="self_service_user_report" valueBoolean="false"/> + </insert> + <insert tableName="stretchy_report"> + <column name="report_name" value="Income Statement Table"/> + <column name="report_type" value="Table"/> + <column name="report_subtype"/> + <column name="report_category" value="Accounting"/> + <column name="report_sql" value="(select * from ( select debits.glcode as 'glcode', debits.name as 'name', 'Expense'as IncomeOrExpense, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (5) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by gl_code order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (5) and acc_gl_journal_entry.entry_date b etween date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by gl_code) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', 'Expense'as IncomeOrExpense, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (5) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by gl_code order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name as 'name',sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entr y.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (5) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by gl_code order by glcode) credits on debits.glcode=credits.glcode)as fullouterjoinresult order by glcode) UNION (select * from ( select debits.glcode as 'glcode', debits.name as 'name', 'Income' as IncomeOrExpense, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode', name, sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (4) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (4) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', 'Income' as IncomeOrExpense, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (4) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name as 'name',sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (4) and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode)as fullouterjoinresult order by glcode)"/> + <column name="description" value="Profit and Loss Statement"/> + <column name="core_report" valueBoolean="true"/> + <column name="use_report" valueBoolean="true"/> + <column name="self_service_user_report" valueBoolean="false"/> + </insert> + <insert tableName="stretchy_report"> + <column name="report_name" value="Balance Sheet Table"/> + <column name="report_type" value="Table"/> + <column name="report_subtype"/> + <column name="report_category" value="Accounting"/> + <column name="report_sql" value="(select debits.glcode as 'glcode', debits.name as 'name', 'Assets' as BalanceType, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (1) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (1) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry .office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', 'Assets' as BalanceType, (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (1) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (1) and acc_gl _journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode UNION select debits.glcode as 'glcode', debits.name as 'name', 'Liability' as BalanceType, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (2) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_jour nal_entry.type_enum=1 and acc_gl_account.classification_enum in (2) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', 'Liability' as BalanceType, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (2) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_ac count where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_account.classification_enum in (2) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode UNION select debits.glcode as 'glcode', debits.name as 'name', 'Equity' as BalanceType, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_account.classification_enum in (3) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as 'glco de',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum= 1 and acc_gl_account.classification_enum in (3) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as 'glcode', credits.name as 'name', 'Equity' as BalanceType, (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance' from (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'debitamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum= 2 and acc_gl_account.classification_enum in (3) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode or der by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as 'glcode',name,sum(amount) as 'creditamount' from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum= 1 and acc_gl_account.classification_enum in (3) and acc_gl_journal_entry.entry_date <= date('${endDate}') and (acc_gl_journal_entry.office_id=${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode) "/> + <column name="description" value="Balance Sheet"/> + <column name="core_report" valueBoolean="true"/> + <column name="use_report" valueBoolean="true"/> + <column name="self_service_user_report" valueBoolean="false"/> + </insert> + + + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Trial Balance Table')"/> + <column name="parameter_id" valueNumeric="1"/> + <column name="report_parameter_name" value="fromDate"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Trial Balance Table')"/> + <column name="parameter_id" valueNumeric="5"/> + <column name="report_parameter_name" value="branch"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Trial Balance Table')"/> + <column name="parameter_id" valueNumeric="2"/> + <column name="report_parameter_name" value="toDate"/> + </insert> + + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/> + <column name="parameter_id" valueNumeric="1008"/> + <column name="report_parameter_name" value="GLAccountNO"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/> + <column name="parameter_id" valueNumeric="5"/> + <column name="report_parameter_name" value="officeId"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/> + <column name="parameter_id" valueNumeric="2"/> + <column name="report_parameter_name" value="endDate"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='GeneralLedgerReport Table')"/> + <column name="parameter_id" valueNumeric="1"/> + <column name="report_parameter_name" value="startDate"/> + </insert> + + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Balance Sheet Table')"/> + <column name="parameter_id" valueNumeric="5"/> + <column name="report_parameter_name" value="branch"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Balance Sheet Table')"/> + <column name="parameter_id" valueNumeric="2"/> + <column name="report_parameter_name" value="date"/> + </insert> + + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Income Statement Table')"/> + <column name="parameter_id" valueNumeric="5"/> + <column name="report_parameter_name" value="branch"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Income Statement Table')"/> + <column name="parameter_id" valueNumeric="1"/> + <column name="report_parameter_name" value="fromDate"/> + </insert> + <insert tableName="stretchy_report_parameter"> + <column name="report_id" valueComputed="(select sr.id from stretchy_report sr where sr.report_name ='Income Statement Table')"/> + <column name="parameter_id" valueNumeric="2"/> + <column name="report_parameter_name" value="toDate"/> + </insert> + </changeSet> + + <changeSet author="fineract" id="1-postgresql" context="postgresql"> + <insert tableName="stretchy_report"> + <column name="report_name" value="Trial Balance Table"/> + <column name="report_type" value="Table"/> + <column name="report_subtype"/> + <column name="report_category" value="Accounting"/> + <column name="report_sql" value="select * from (select debits.glcode as "glcode", debits.name as "name", IF(debits.type = 1 or debits.type = 5, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),null) as "debit", IF(debits.type = 4 or debits.type = 3 or debits.type = 2, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),null) as "credit" from (select acc_gl_account.gl_code as "glcode", name,sum(amount) as "debitamount", acc_gl_account.classification_enum as "type" from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits LEFT OUTER JOIN (select acc_gl_account.gl_code as "glcode", name as "name" ;, sum(amount) as "creditamount", acc_gl_account.classification_enum as "type" from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode union select credits.glcode as "glcode", credits.name as "name", IF(credits.type = 1 or credits.type = 5, ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),null) as "debit", IF(credits.type = 4 or credits.type = 3 or credits.type = 2, ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),null) as "credit" from (select acc_gl_account.gl_code as "glcode", name, sum(amount) as "debitamount", acc_gl_account.classification_enum as "type" from ac c_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=2 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) debits RIGHT OUTER JOIN (select acc_gl_account.gl_code as "glcode",name as "name", sum(amount) as "creditamount", acc_gl_account.classification_enum as "type" from acc_gl_journal_entry,acc_gl_account where acc_gl_account.id = acc_gl_journal_entry.account_id and acc_gl_journal_entry.type_enum=1 and acc_gl_journal_entry.entry_date between date('${startDate}') and date('${endDate}') and (acc_gl_journal_entry.office_id= ${officeId} or ${officeId}=1) group by glcode order by glcode) credits on debits.glcode=credits.glcode) as fullouterjoinresult order by glcode "/> Review Comment: The query clearly won't work on PostgreSQL since it's using the IFNULL function which is not supported by PostgreSQL. Instead we should use COALESCE but I assume there are other problems as well with the query. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
