details: https://code.openbravo.com/erp/devel/pi/rev/1b42e944e4a7
changeset: 21249:1b42e944e4a7
user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
date: Wed Oct 09 13:11:37 2013 +0200
summary: Fixes bug 24891
diffstat:
src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java | 51
++++++-
src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql | 62
+++++++++-
2 files changed, 100 insertions(+), 13 deletions(-)
diffs (205 lines):
diff -r 72561635f02f -r 1b42e944e4a7
src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java
--- a/src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java
Wed Oct 09 12:56:31 2013 +0200
+++ b/src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java
Wed Oct 09 13:11:37 2013 +0200
@@ -36,6 +36,7 @@
import org.openbravo.base.secureApp.HttpSecureAppServlet;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.security.OrganizationStructureProvider;
import org.openbravo.dal.service.OBCriteria;
import org.openbravo.dal.service.OBDal;
import org.openbravo.erpCommon.businessUtility.AccountTree;
@@ -60,6 +61,8 @@
public class GeneralAccountingReports extends HttpSecureAppServlet {
private static final long serialVersionUID = 1L;
+ String openingEntryOwner;
+ String openingEntryOwnerRef;
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws IOException,
ServletException {
@@ -170,6 +173,8 @@
TreeID = dataTree[0].id;
OBContext.setAdminMode(false);
try {
+ openingEntryOwner = "";
+ openingEntryOwnerRef = "";
// For each year, the initial and closing date is obtained
Year year = OBDal.getInstance().get(Year.class, strYearId);
Year yearRef = OBDal.getInstance().get(Year.class, strYearRefId);
@@ -178,7 +183,6 @@
// Years to be included as no closing is present
String strYearsToClose = "";
String strYearsToCloseRef = "";
-
if (strCalculateOpening.equals("Y")) {
strCalculateOpening = "N";
strDateTo = strAsDateTo;
@@ -186,13 +190,13 @@
strDateFrom = "";
strDateFromRef = "";
strYearsToClose =
getYearsToClose(startingEndingDate.get("startingDate"), strOrg,
- year.getCalendar());
+ year.getCalendar(), strcAcctSchemaId, false);
if (strYearsToClose.length() > 0) {
strCalculateOpening = "Y";
strYearsToClose = "," + strYearsToClose;
}
strYearsToCloseRef =
getYearsToClose(startingEndingDateRef.get("startingDate"), strOrg,
- yearRef.getCalendar());
+ yearRef.getCalendar(), strcAcctSchemaId, true);
if (strYearsToCloseRef.length() > 0) {
strCalculateOpening = "Y";
strYearsToCloseRef = "," + strYearsToCloseRef;
@@ -211,14 +215,14 @@
}
// For each account with movements in the year, debit and credit
total amounts are
// calculated according to fact_acct movements.
- AccountTreeData[] accounts = AccountTreeData.selectAcct(this,
+ AccountTreeData[] accounts = AccountTreeData.selectFactAcct(this,
Utility.getContext(this, vars, "#AccessibleOrgTree",
"GeneralAccountingReports"),
Utility.getContext(this, vars, "#User_Client",
"GeneralAccountingReports"),
strDateFrom, DateTimeData.nDaysAfter(this, strDateTo, "1"),
strcAcctSchemaId,
Tree.getMembers(this, strTreeOrg, strOrg), "'" +
year.getFiscalYear() + "'"
- + strYearsToClose, strDateFromRef,
+ + strYearsToClose, openingEntryOwner, strDateFromRef,
DateTimeData.nDaysAfter(this, strDateToRef, "1"), "'" +
yearRef.getFiscalYear() + "'"
- + strYearsToCloseRef);
+ + strYearsToCloseRef, openingEntryOwnerRef);
{
if (log4j.isDebugEnabled())
log4j.debug("*********** strIncomeSummaryAccount: " +
strIncomeSummaryAccount);
@@ -298,25 +302,48 @@
}
}
- private String getYearsToClose(Date startingDate, String strOrg, Calendar
calendar) {
+ private String getYearsToClose(Date startingDate, String strOrg, Calendar
calendar,
+ String strcAcctSchemaId, boolean isYearRef) {
Set<Year> previousYears = getOrderedPreviousYears(startingDate, calendar);
Set<String> notClosedYears = new HashSet<String>();
for (Year previousYear : previousYears) {
- if (isNotClosed(previousYear, strOrg)) {
- notClosedYears.add(previousYear.getFiscalYear());
+ for (Organization org : getCalendarOwnerOrgs(strOrg)) {
+ if (isNotClosed(previousYear, org, strcAcctSchemaId)) {
+ notClosedYears.add(previousYear.getFiscalYear());
+ } else {
+ if (isYearRef) {
+ openingEntryOwnerRef = previousYear.getFiscalYear();
+ } else {
+ openingEntryOwner = previousYear.getFiscalYear();
+ }
+ }
}
}
return Utility.getInStrSet(notClosedYears);
}
- private boolean isNotClosed(Year year, String strOrg) {
+ private Set<Organization> getCalendarOwnerOrgs(String strOrg) {
+ Set<Organization> calendarOwnerOrgs = new HashSet<Organization>();
+ Organization organization = OBDal.getInstance().get(Organization.class,
strOrg);
+ if (organization.isAllowPeriodControl()) {
+ calendarOwnerOrgs.add(organization);
+ }
+ for (String child : new
OrganizationStructureProvider().getChildTree(strOrg, false)) {
+ calendarOwnerOrgs.addAll(getCalendarOwnerOrgs(child));
+ }
+ return calendarOwnerOrgs;
+ }
+
+ private boolean isNotClosed(Year year, Organization org, String
strcAcctSchemaId) {
OBContext.setAdminMode(false);
try {
OBCriteria<OrganizationClosing> obc = OBDal.getInstance().createCriteria(
OrganizationClosing.class);
- obc.add(Restrictions.eq(OrganizationClosing.PROPERTY_ORGANIZATION,
- OBDal.getInstance().get(Organization.class, strOrg)));
+ obc.createAlias(OrganizationClosing.PROPERTY_ORGACCTSCHEMA, "oa");
+ obc.add(Restrictions.eq("organization", org));
obc.add(Restrictions.eq(OrganizationClosing.PROPERTY_YEAR, year));
+ obc.add(Restrictions.eq("oa.accountingSchema.id", strcAcctSchemaId));
+
obc.add(Restrictions.isNotNull(OrganizationClosing.PROPERTY_CLOSINGFACTACCTGROUP));
obc.setMaxResults(1);
return obc.uniqueResult() == null ? true : false;
} finally {
diff -r 72561635f02f -r 1b42e944e4a7
src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql
--- a/src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql Wed Oct
09 12:56:31 2013 +0200
+++ b/src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql Wed Oct
09 13:11:37 2013 +0200
@@ -66,7 +66,7 @@
(COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit, 0
as qty_ref, 0 as qtyCredit_ref
FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y
WHERE m.AD_Org_ID IN ('1')
- AND m.AD_Client_ID IN ('1')
+ AND m.AD_Client_ID IN ('1')
AND 1=1
AND f.FACTACCTTYPE <> 'R'
AND f.FACTACCTTYPE <> 'C'
@@ -107,6 +107,66 @@
<Parameter name="org" optional="true" type="replace" after="AND 1=1
AND f.AD_ORG_ID IN (" text="'1'"/>
<Parameter name="agnoRef" optional="true" type="replace" after="AND
y.YEAR IN (" text="'1'"/>
</SqlMethod>
+ <SqlMethod name="selectFactAcct" type="preparedStatement" return="multiple">
+ <SqlMethodComment></SqlMethodComment>
+ <Sql><![CDATA[
+ SELECT ID, SUM(QTY) AS QTY, SUM(QTYCREDIT) AS QTYCREDIT, SUM(QTY_REF)
AS QTY_REF, SUM(QTYCREDIT_REF) AS QTYCREDIT_REF
+ FROM (
+ SELECT m.C_ElementValue_ID as id, (COALESCE(f.AMTACCTDR,0) -
COALESCE(f.AMTACCTCR, 0)) AS qty,
+ (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS qtyCredit, 0
as qty_ref, 0 as qtyCredit_ref
+ FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y
+ WHERE m.AD_Org_ID IN ('1')
+ AND m.AD_Client_ID IN ('1')
+ AND 1=1
+ AND f.FACTACCTTYPE <> 'R'
+ AND f.FACTACCTTYPE <> 'C'
+ AND m.C_ElementValue_ID = f.Account_ID
+ AND 0=0 AND f.AD_ORG_ID IN ('1')
+ AND f.C_PERIOD_ID = p.C_PERIOD_ID
+ AND p.C_YEAR_ID = y.C_YEAR_ID
+ AND Y.YEAR IN ('1')
+ and not exists (select 1 from ad_org_closing, c_year year
+ where open_fact_acct_group_id = f.fact_acct_group_id
+ and ad_org_closing.c_year_id = year.c_year_id
+ and year.year <> ?)
+ UNION ALL
+ SELECT m.C_ElementValue_ID as id, 0 AS QTY, 0 as qtyCredit,
(COALESCE(f.AMTACCTDR,0) - COALESCE(f.AMTACCTCR, 0)) AS qty_ref,
+ (COALESCE(f.AMTACCTCR,0) - COALESCE(f.AMTACCTDR, 0)) AS
qtyCredit_ref
+ FROM C_ElementValue m, Fact_Acct f, C_Period p, C_Year y
+ WHERE m.AD_Org_ID IN('2')
+ AND m.AD_Client_ID IN('2')
+ AND 2=2
+ AND f.FACTACCTTYPE <> 'R'
+ AND f.FACTACCTTYPE <> 'C'
+ AND m.C_ElementValue_ID = f.Account_ID
+ AND 1=1 AND f.AD_ORG_ID IN ('1')
+ AND f.C_PERIOD_ID = p.C_PERIOD_ID
+ AND p.C_YEAR_ID = y.C_YEAR_ID
+ AND y.YEAR IN ('1')
+ and not exists (select 1 from ad_org_closing, c_year year
+ where open_fact_acct_group_id = f.fact_acct_group_id
+ and ad_org_closing.c_year_id = year.c_year_id
+ and year.year <> ?)
+ ) AA
+ GROUP BY ID
+ ]]></Sql>
+ <Parameter name="adOrgClient" type="replace" optional="true"
after="m.AD_Org_ID IN (" text="'1'"/>
+ <Parameter name="adUserClient" type="replace" optional="true"
after="m.AD_Client_ID IN (" text="'1'"/>
+ <Parameter name="dateFrom" optional="true" after="AND 1=1 "><![CDATA[
AND f.DATEACCT >= to_date(?)]]></Parameter>
+ <Parameter name="dateTo" optional="true" after="AND 1=1 "><![CDATA[
AND f.DATEACCT < to_date(?)]]></Parameter>
+ <Parameter name="acctschema" optional="true" after="AND 1=1
"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
+ <Parameter name="org" optional="true" type="replace" after="AND 0=0
AND f.AD_ORG_ID IN (" text="'1'"/>
+ <Parameter name="agno" optional="true" type="replace" after="AND
Y.YEAR IN (" text="'1'"/>
+ <Parameter name="openingEntryOwner"/>
+ <Parameter name="adOrgClient" type="replace" optional="true"
after="m.AD_Org_ID IN(" text="'2'"/>
+ <Parameter name="adUserClient" type="replace" optional="true"
after="m.AD_Client_ID IN(" text="'2'"/>
+ <Parameter name="dateFromRef" optional="true" after="AND 2=2
"><![CDATA[ AND f.DATEACCT >= to_date(?)]]></Parameter>
+ <Parameter name="dateToRef" optional="true" after="AND 2=2 "><![CDATA[
AND f.DATEACCT < to_date(?)]]></Parameter>
+ <Parameter name="acctschema" optional="true" after="AND 2=2
"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
+ <Parameter name="org" optional="true" type="replace" after="AND 1=1
AND f.AD_ORG_ID IN (" text="'1'"/>
+ <Parameter name="agnoRef" optional="true" type="replace" after="AND
y.YEAR IN (" text="'1'"/>
+ <Parameter name="openingEntryOwnerRef"/>
+ </SqlMethod>
<SqlMethod name="selectForms" type="preparedStatement" return="multiple">
<SqlMethodComment></SqlMethodComment>
<Sql><![CDATA[
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
