details: https://code.openbravo.com/erp/devel/main/rev/865e35797a3b changeset: 21244:865e35797a3b user: Javier Etxarri <javier.echarri <at> openbravo.com> date: Mon Oct 07 10:19:00 2013 +0200 summary: Fixes issue 24890: Cannot delete shipments
details: https://code.openbravo.com/erp/devel/main/rev/eda54c434a3e changeset: 21245:eda54c434a3e user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com> date: Wed Oct 09 13:11:37 2013 +0200 summary: Fixes bug 24891 details: https://code.openbravo.com/erp/devel/main/rev/140b1f50768f changeset: 21246:140b1f50768f user: David Miguelez <david.miguelez <at> openbravo.com> date: Wed Oct 09 17:04:19 2013 +0200 summary: Fixes Issue 24912. Prevents from not Balanced error. details: https://code.openbravo.com/erp/devel/main/rev/eec0f437deb7 changeset: 21247:eec0f437deb7 user: David Miguelez <david.miguelez <at> openbravo.com> date: Wed Oct 09 17:39:29 2013 +0200 summary: Related to Issue 24912. details: https://code.openbravo.com/erp/devel/main/rev/165e2aa984c3 changeset: 21248:165e2aa984c3 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Tue Oct 08 19:14:12 2013 +0200 summary: Fixed issue 24913.Return empty result when no variants are found. details: https://code.openbravo.com/erp/devel/main/rev/d107af5fc116 changeset: 21249:d107af5fc116 user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Wed Oct 09 19:43:59 2013 +0200 summary: Fixed 24919: Speedup AcctServer.checkDocuments Avoid checking same docbasetype several times by only doing check for distinct docbasetype values thus skipping duplicates which happen as docbasetypes are defined per organization. details: https://code.openbravo.com/erp/devel/main/rev/54d50808f4ef changeset: 21250:54d50808f4ef user: Stefan Hühner <stefan.huehner <at> openbravo.com> date: Wed Oct 09 19:45:30 2013 +0200 summary: Issue 24919: Small code-cleanup Delete unused (non-public) selectDocuments methods in xsql file to make it obvious that the method is not used. diffstat: src/org/openbravo/erpCommon/ad_forms/AcctServer_data.xsql | 35 +----- src/org/openbravo/erpCommon/ad_forms/DocFINFinAccTransaction.java | 15 +- src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java | 51 ++++++- src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql | 62 +++++++++- src/org/openbravo/event/MInOutLineEventHandler.java | 9 +- src/org/openbravo/materialmgmt/ManageVariantsDS.java | 3 + 6 files changed, 117 insertions(+), 58 deletions(-) diffs (truncated from 322 to 300 lines): diff -r e636887f6f83 -r 54d50808f4ef src/org/openbravo/erpCommon/ad_forms/AcctServer_data.xsql --- a/src/org/openbravo/erpCommon/ad_forms/AcctServer_data.xsql Thu Oct 10 11:34:10 2013 +0000 +++ b/src/org/openbravo/erpCommon/ad_forms/AcctServer_data.xsql Wed Oct 09 19:45:30 2013 +0200 @@ -523,39 +523,6 @@ </Sql> <Parameter name="adUserClient" type="replace" optional="true" after="AD_Org.AD_Client_ID IN (" text="'1'"/> </SqlMethod> - <SqlMethod name="selectDocuments" type="preparedStatement" return="single"> - <SqlMethodComment></SqlMethodComment> - <Sql> - <![CDATA[ - select 1 as id from dual where exists ( - select C_INVOICE_ID as id - from c_invoice a, ad_org - where posted = 'N' - and processed = 'Y' - and (processing = 'N' or processing is null) - AND ad_org.ad_org_id=a.ad_org_id - AND a.ad_client_id = ? - AND AD_ISORGINCLUDED(ad_org.ad_org_id, ?, ad_org.ad_client_id)<>-1 - AND EXISTS (select 1 - from c_year, c_period, c_periodcontrol, ad_org - where c_year.c_year_id = c_period.c_year_id - and c_period.c_period_id = c_periodcontrol.c_period_id - and c_year.c_calendar_id = ad_org.c_calendar_id - and ad_org.ad_client_id = a.ad_client_id - and c_periodcontrol.docbasetype = ? - and c_periodcontrol.periodstatus = 'O' - and a.dateacct >= c_period.StartDate - and a.dateacct < c_period.EndDate + 1)) - ]]> - </Sql> - <Parameter name="tableName" type="replace" optional="true" after="select " text="C_INVOICE"/> - <Parameter name="tableName" type="replace" optional="true" after="from " text="c_invoice"/> - <Parameter name="adClientId" /> - <Parameter name="adOrgId"/> - <Parameter name="docbasetype"/> - <Parameter name="dateColumn" type="replace" optional="true" after="and a." text="dateacct"/> - <Parameter name="dateColumn" type="replace" optional="true" after="and a." text="dateacct"/> - </SqlMethod> <SqlMethod name="selectDocumentsDates" type="preparedStatement" return="single"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -639,7 +606,7 @@ <SqlMethod name="selectDocTypes" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> - select docbasetype as name from c_doctype + select distinct docbasetype as name from c_doctype where ad_table_id = ? and ad_client_id = ? </Sql> diff -r e636887f6f83 -r 54d50808f4ef src/org/openbravo/erpCommon/ad_forms/DocFINFinAccTransaction.java --- a/src/org/openbravo/erpCommon/ad_forms/DocFINFinAccTransaction.java Thu Oct 10 11:34:10 2013 +0000 +++ b/src/org/openbravo/erpCommon/ad_forms/DocFINFinAccTransaction.java Wed Oct 09 19:45:30 2013 +0200 @@ -713,7 +713,7 @@ // Total retValue = retValue.add(new BigDecimal(getAmount(AcctServer.AMTTYPE_Gross))); if (usedCredit.compareTo(ZERO) != 0 && generatedCredit.compareTo(ZERO) == 0) - retValue.add(usedCredit); + retValue = retValue.add(usedCredit); sb.append(retValue); FIN_Payment payment = OBDal.getInstance().get(FIN_FinaccTransaction.class, Record_ID) .getFinPayment(); @@ -732,12 +732,13 @@ ((DocLine_FINFinAccTransaction) p_lines[i]).PaymentAmount)); retValue = retValue.subtract(lineBalance); } else { - BigDecimal lineBalance = BigDecimal.ZERO; - for (FIN_PaymentDetail pd : payment.getFINPaymentDetailList()) { - lineBalance = lineBalance.add(payment.isReceipt() ? pd.getAmount().add( - pd.getWriteoffAmount()) : pd.getAmount().add(pd.getWriteoffAmount()).negate()); - } - retValue = retValue.subtract(lineBalance); + BigDecimal lineBalance = payment.isReceipt() ? new BigDecimal( + ((DocLine_FINFinAccTransaction) p_lines[i]).getAmount()) : new BigDecimal( + ((DocLine_FINFinAccTransaction) p_lines[i]).getAmount()).negate(); + BigDecimal lineWriteoff = payment.isReceipt() ? new BigDecimal( + ((DocLine_FINFinAccTransaction) p_lines[i]).getWriteOffAmt()) : new BigDecimal( + ((DocLine_FINFinAccTransaction) p_lines[i]).getWriteOffAmt()).negate(); + retValue = retValue.subtract(lineBalance).subtract(lineWriteoff); } } } finally { diff -r e636887f6f83 -r 54d50808f4ef src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java --- a/src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java Thu Oct 10 11:34:10 2013 +0000 +++ b/src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports.java Wed Oct 09 19:45:30 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 e636887f6f83 -r 54d50808f4ef src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql --- a/src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql Thu Oct 10 11:34:10 2013 +0000 +++ b/src/org/openbravo/erpCommon/businessUtility/AccountTree_data.xsql Wed Oct 09 19:45:30 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><