You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by GitBox <gi...@apache.org> on 2022/04/22 13:07:36 UTC
[GitHub] [fineract] fynmanoj opened a new pull request, #2291: FINERACT-1541: Pentaho accounting reports converted to table format
fynmanoj opened a new pull request, #2291:
URL: https://github.com/apache/fineract/pull/2291
This is the rebased version of : https://github.com/apache/fineract/pull/2176
## Description
Describe the changes made and why they were made.
Ignore if these details are present on the associated [Apache Fineract JIRA ticket](https://github.com/apache/fineract/pull/1284).
## Checklist
Please make sure these boxes are checked before submitting your pull request - thanks!
- [ ] Write the commit message as per https://github.com/apache/fineract/#pull-requests
- [ ] Acknowledge that we will not review PRs that are not passing the build _("green")_ - it is your responsibility to get a proposed PR to pass the build, not primarily the project's maintainers.
- [ ] Create/update unit or integration tests for verifying the changes made.
- [ ] Follow coding conventions at https://cwiki.apache.org/confluence/display/FINERACT/Coding+Conventions.
- [ ] Add required Swagger annotation and update API documentation at fineract-provider/src/main/resources/static/api-docs/apiLive.htm with details of any API changes
- [ ] Submission is not a "code dump". (Large changes can be made "in repository" via a branch. Ask on the developer mailing list for guidance, if required.)
FYI our guidelines for code reviews are at https://cwiki.apache.org/confluence/display/FINERACT/Code+Review+Guide.
--
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: commits-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] galovics commented on a diff in pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
Posted by GitBox <gi...@apache.org>.
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: commits-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] galovics commented on pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
Posted by GitBox <gi...@apache.org>.
galovics commented on PR #2291:
URL: https://github.com/apache/fineract/pull/2291#issuecomment-1154143006
I guess #2347 is the updated version of this. I'm closing it but let me know if that's not true and we need both.
--
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: commits-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] fynmanoj closed pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
Posted by GitBox <gi...@apache.org>.
fynmanoj closed pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
URL: https://github.com/apache/fineract/pull/2291
--
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: commits-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] galovics closed pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
Posted by GitBox <gi...@apache.org>.
galovics closed pull request #2291: FINERACT-1541: Pentaho accounting reports converted to table format
URL: https://github.com/apache/fineract/pull/2291
--
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: commits-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org