details: https://code.openbravo.com/erp/devel/pi/rev/dcaf5195237a changeset: 30293:dcaf5195237a user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Sep 14 13:39:12 2016 +0200 summary: Fixed issue 33989. Removed slow query getting Organization Ledger.
The method to get the default General Ledget was using a query that filtered and sorted the results using ad_isorgincluded procedure. In case of having a large number of organizations this query was slow. The method is now using the OrganizationStructureProvider to get the parent organization List of the required org to search the default General Ledger. details: https://code.openbravo.com/erp/devel/pi/rev/018192ddfcb3 changeset: 30294:018192ddfcb3 user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Mon Sep 19 11:14:46 2016 +0200 summary: Related to issue 33989: Code review improvements - Avoid setting admin mode in getOrgLedgerRecursive() and getClientLedger() methods as it is already done in getOrgLedger() method in OBLedgerUtils class. - Retrieve first, general ledger of login organization in LoginUtils class. If no result is retrieved, we will retrieve general ledger of rest of context organizations. diffstat: src/org/openbravo/base/secureApp/LoginUtils.java | 29 ++++- src/org/openbravo/erpCommon/utility/OBLedgerUtils.java | 92 ++++++++++------- 2 files changed, 75 insertions(+), 46 deletions(-) diffs (173 lines): diff -r 676e26599466 -r 018192ddfcb3 src/org/openbravo/base/secureApp/LoginUtils.java --- a/src/org/openbravo/base/secureApp/LoginUtils.java Thu Sep 15 12:14:10 2016 -0400 +++ b/src/org/openbravo/base/secureApp/LoginUtils.java Mon Sep 19 11:14:46 2016 +0200 @@ -276,15 +276,28 @@ vars.setSessionValue("#Client_SMTP", data[0].smtphost); data = null; + // Get General Ledger of login organization AttributeData[] attr = null; - String[] orgList = Utility.getContext(conn, vars, "#User_Org", "LoginHandler").split(","); - for (String orgId : orgList) { - String acctSchemaId = OBLedgerUtils.getOrgLedger(orgId.replace("'", "")); - if (StringUtils.isNotEmpty(acctSchemaId)) { - attr = AttributeData.selectAcctSchema(conn, acctSchemaId, - Utility.getContext(conn, vars, "#User_Client", "LoginHandler")); - if (ArrayUtils.isNotEmpty(attr)) { - break; + String acctSchemaId = OBLedgerUtils.getOrgLedger(strOrg); + if (StringUtils.isNotEmpty(acctSchemaId)) { + attr = AttributeData.selectAcctSchema(conn, acctSchemaId, + Utility.getContext(conn, vars, "#User_Client", "LoginHandler")); + } + + // Get General Ledger of context organizations + if (ArrayUtils.isEmpty(attr)) { + String[] orgList = Utility.getContext(conn, vars, "#User_Org", "LoginHandler") + .replace("'", "").split(","); + for (String orgId : orgList) { + if (!StringUtils.equals(orgId, strOrg)) { + acctSchemaId = OBLedgerUtils.getOrgLedger(orgId); + if (StringUtils.isNotEmpty(acctSchemaId)) { + attr = AttributeData.selectAcctSchema(conn, acctSchemaId, + Utility.getContext(conn, vars, "#User_Client", "LoginHandler")); + if (ArrayUtils.isNotEmpty(attr)) { + break; + } + } } } } diff -r 676e26599466 -r 018192ddfcb3 src/org/openbravo/erpCommon/utility/OBLedgerUtils.java --- a/src/org/openbravo/erpCommon/utility/OBLedgerUtils.java Thu Sep 15 12:14:10 2016 -0400 +++ b/src/org/openbravo/erpCommon/utility/OBLedgerUtils.java Mon Sep 19 11:14:46 2016 +0200 @@ -18,10 +18,13 @@ */ package org.openbravo.erpCommon.utility; +import java.util.List; + import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.hibernate.Query; import org.openbravo.dal.core.OBContext; +import org.openbravo.dal.security.OrganizationStructureProvider; import org.openbravo.dal.service.OBDal; import org.openbravo.model.common.enterprise.Organization; import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema; @@ -51,64 +54,77 @@ // No organization return null; } + final Organization org = OBDal.getInstance().get(Organization.class, orgId); if (org == null) { // No organization return null; } - String acctSchemaId = getOrgLedgerRecursive(orgId); - if (!StringUtils.isEmpty(acctSchemaId)) { - // Get ledger of organization tree + + String acctSchemaId = getOrgLedgerRecursive(org); + if (StringUtils.isNotEmpty(acctSchemaId)) { + // Get general ledger of organization tree return acctSchemaId; } + String clientId = StringUtils.equals(orgId, "0") ? OBContext.getOBContext() .getCurrentClient().getId() : org.getClient().getId(); - // Get client base Ledger + // Get client base general ledger return getClientLedger(clientId); } catch (Exception e) { log4j.error("Impossible to get ledger for organization id " + orgId, e); + return null; } finally { OBContext.restorePreviousMode(); } + } + + /** + * If the Organization has a General Ledger defined return its id. If not get the parent + * organization list and loop through it until a organization with a GeneralLedger defined is + * found. In case none has it defined return null. + * + * @param org + * the Organization whose General Ledger is required. + * @return the General Ledger Id of the organization in case the organization or one of its parent + * has a General Ledger defined. + */ + private static String getOrgLedgerRecursive(Organization org) { + + if (org.getGeneralLedger() != null) { + // Get general ledger of organization + return org.getGeneralLedger().getId(); + } + + if (StringUtils.equals(org.getId(), "0")) { + // * organization doesn't have parents + return null; + } + + // Loop through parent organization list + OrganizationStructureProvider osp = OBContext.getOBContext().getOrganizationStructureProvider( + org.getClient().getId()); + List<String> parentOrgIds = osp.getParentList(org.getId(), false); + for (String orgId : parentOrgIds) { + Organization parentOrg = OBDal.getInstance().get(Organization.class, orgId); + if (parentOrg.getGeneralLedger() != null) { + return parentOrg.getGeneralLedger().getId(); + } + } return null; } - private static String getOrgLedgerRecursive(String orgId) { - try { - OBContext.setAdminMode(true); - StringBuffer where = new StringBuffer(); - where.append(" select " + Organization.PROPERTY_GENERALLEDGER + ".id"); - where.append(" from " + Organization.ENTITY_NAME); - where.append(" where ad_isorgincluded(:orgId, " + Organization.PROPERTY_ID + ", " - + Organization.PROPERTY_CLIENT + ".id) <> -1"); - where.append(" and " + Organization.PROPERTY_GENERALLEDGER + " is not null"); - where.append(" order by ad_isorgincluded(:orgId, " + Organization.PROPERTY_ID + ", " - + Organization.PROPERTY_CLIENT + ".id)"); - Query qry = OBDal.getInstance().getSession().createQuery(where.toString()); - qry.setParameter("orgId", orgId); - qry.setMaxResults(1); - return (String) qry.uniqueResult(); - } finally { - OBContext.restorePreviousMode(); - } - } - private static String getClientLedger(String clientId) { - try { - OBContext.setAdminMode(true); - StringBuffer where = new StringBuffer(); - where.append(" select " + AcctSchema.PROPERTY_ID); - where.append(" from " + AcctSchema.ENTITY_NAME); - where.append(" where " + AcctSchema.PROPERTY_CLIENT + ".id = :clientId"); - where.append(" order by " + AcctSchema.PROPERTY_NAME); - Query qry = OBDal.getInstance().getSession().createQuery(where.toString()); - qry.setParameter("clientId", clientId); - qry.setMaxResults(1); - return (String) qry.uniqueResult(); - } finally { - OBContext.restorePreviousMode(); - } + StringBuffer where = new StringBuffer(); + where.append(" select " + AcctSchema.PROPERTY_ID); + where.append(" from " + AcctSchema.ENTITY_NAME); + where.append(" where " + AcctSchema.PROPERTY_CLIENT + ".id = :clientId"); + where.append(" order by " + AcctSchema.PROPERTY_NAME); + Query qry = OBDal.getInstance().getSession().createQuery(where.toString()); + qry.setParameter("clientId", clientId); + qry.setMaxResults(1); + return (String) qry.uniqueResult(); } } ------------------------------------------------------------------------------ _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
