Fixes issue 30451: Performance Problems on Costing Migration Process
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Wed, 12 Aug 2015 10:53:32 +0200
changeset 27383 a2790dd1c422
parent 27382 531f7892150a
child 27384 f8dc8ea21e39
Fixes issue 30451: Performance Problems on Costing Migration Process

Added some checks before process starts to check if exists wrong data and it is needed to correct it.
src-db/database/sourcedata/AD_MESSAGE.xml
src/org/openbravo/costing/CostingMigrationProcess.java
--- a/src-db/database/sourcedata/AD_MESSAGE.xml	Thu Aug 20 21:13:13 2015 +0000
+++ b/src-db/database/sourcedata/AD_MESSAGE.xml	Wed Aug 12 10:53:32 2015 +0200
@@ -20941,6 +20941,18 @@
 <!--798D639305F44505B065D173EB89B91C-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
 <!--798D639305F44505B065D173EB89B91C--></AD_MESSAGE>
 
+<!--79979B9127874FCC83F0B5EF3EEA9A60--><AD_MESSAGE>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <AD_MESSAGE_ID><![CDATA[79979B9127874FCC83F0B5EF3EEA9A60]]></AD_MESSAGE_ID>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <VALUE><![CDATA[NegativeStockWithoutAllowing]]></VALUE>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <MSGTEXT><![CDATA[There are products with negative stock without allowing it in %s client]]></MSGTEXT>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--79979B9127874FCC83F0B5EF3EEA9A60-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
+<!--79979B9127874FCC83F0B5EF3EEA9A60--></AD_MESSAGE>
+
 <!--79D510C3817A4174B9ECB194E47CDBEB--><AD_MESSAGE>
 <!--79D510C3817A4174B9ECB194E47CDBEB-->  <AD_MESSAGE_ID><![CDATA[79D510C3817A4174B9ECB194E47CDBEB]]></AD_MESSAGE_ID>
 <!--79D510C3817A4174B9ECB194E47CDBEB-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -22567,6 +22579,18 @@
 <!--A028CFDE75274327BF364041696938AE-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
 <!--A028CFDE75274327BF364041696938AE--></AD_MESSAGE>
 
+<!--A04AA56850A4411FB21500815DF702BB--><AD_MESSAGE>
+<!--A04AA56850A4411FB21500815DF702BB-->  <AD_MESSAGE_ID><![CDATA[A04AA56850A4411FB21500815DF702BB]]></AD_MESSAGE_ID>
+<!--A04AA56850A4411FB21500815DF702BB-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--A04AA56850A4411FB21500815DF702BB-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--A04AA56850A4411FB21500815DF702BB-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--A04AA56850A4411FB21500815DF702BB-->  <VALUE><![CDATA[InconsistenciesInStock]]></VALUE>
+<!--A04AA56850A4411FB21500815DF702BB-->  <MSGTEXT><![CDATA[There are inconsistencies between M_TRANSACTION and M_STORAGE_DETAIL tables]]></MSGTEXT>
+<!--A04AA56850A4411FB21500815DF702BB-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--A04AA56850A4411FB21500815DF702BB-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--A04AA56850A4411FB21500815DF702BB-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
+<!--A04AA56850A4411FB21500815DF702BB--></AD_MESSAGE>
+
 <!--A0844A1983A54B4AB60640B28B66C2B2--><AD_MESSAGE>
 <!--A0844A1983A54B4AB60640B28B66C2B2-->  <AD_MESSAGE_ID><![CDATA[A0844A1983A54B4AB60640B28B66C2B2]]></AD_MESSAGE_ID>
 <!--A0844A1983A54B4AB60640B28B66C2B2-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -23392,6 +23416,18 @@
 <!--B22364B632144C2FA68384163FE4D447-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
 <!--B22364B632144C2FA68384163FE4D447--></AD_MESSAGE>
 
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665--><AD_MESSAGE>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <AD_MESSAGE_ID><![CDATA[B2AC39FDE8B74E0BBC2C71366BFAE665]]></AD_MESSAGE_ID>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <VALUE><![CDATA[PeriodClosedForMMI]]></VALUE>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <MSGTEXT><![CDATA[Current Period is not opened or does not exist for %s organization and Material Physical Inventory document]]></MSGTEXT>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
+<!--B2AC39FDE8B74E0BBC2C71366BFAE665--></AD_MESSAGE>
+
 <!--B2DA469610FB457699D8B70BEDE009E4--><AD_MESSAGE>
 <!--B2DA469610FB457699D8B70BEDE009E4-->  <AD_MESSAGE_ID><![CDATA[B2DA469610FB457699D8B70BEDE009E4]]></AD_MESSAGE_ID>
 <!--B2DA469610FB457699D8B70BEDE009E4-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
@@ -23416,6 +23452,18 @@
 <!--B2E2BD179E374B09A39DCB5A3A301F0B-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
 <!--B2E2BD179E374B09A39DCB5A3A301F0B--></AD_MESSAGE>
 
+<!--B38D1A1470E54E3884E708996856C58E--><AD_MESSAGE>
+<!--B38D1A1470E54E3884E708996856C58E-->  <AD_MESSAGE_ID><![CDATA[B38D1A1470E54E3884E708996856C58E]]></AD_MESSAGE_ID>
+<!--B38D1A1470E54E3884E708996856C58E-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--B38D1A1470E54E3884E708996856C58E-->  <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--B38D1A1470E54E3884E708996856C58E-->  <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--B38D1A1470E54E3884E708996856C58E-->  <VALUE><![CDATA[ProductStockInDifferentUOM]]></VALUE>
+<!--B38D1A1470E54E3884E708996856C58E-->  <MSGTEXT><![CDATA[There are products with stock in a Unit of Measure different from the one defined in the Product window]]></MSGTEXT>
+<!--B38D1A1470E54E3884E708996856C58E-->  <MSGTYPE><![CDATA[E]]></MSGTYPE>
+<!--B38D1A1470E54E3884E708996856C58E-->  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
+<!--B38D1A1470E54E3884E708996856C58E-->  <ISINCLUDEINI18N><![CDATA[N]]></ISINCLUDEINI18N>
+<!--B38D1A1470E54E3884E708996856C58E--></AD_MESSAGE>
+
 <!--B39063570565473E81C41FC76BE18078--><AD_MESSAGE>
 <!--B39063570565473E81C41FC76BE18078-->  <AD_MESSAGE_ID><![CDATA[B39063570565473E81C41FC76BE18078]]></AD_MESSAGE_ID>
 <!--B39063570565473E81C41FC76BE18078-->  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
--- a/src/org/openbravo/costing/CostingMigrationProcess.java	Thu Aug 20 21:13:13 2015 +0000
+++ b/src/org/openbravo/costing/CostingMigrationProcess.java	Wed Aug 12 10:53:32 2015 +0200
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2012-2013 Openbravo SLU
+ * All portions are Copyright (C) 2012-2015 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -62,11 +62,14 @@
 import org.openbravo.model.common.enterprise.Organization;
 import org.openbravo.model.common.enterprise.OrganizationType;
 import org.openbravo.model.common.plm.Product;
+import org.openbravo.model.financialmgmt.calendar.Period;
+import org.openbravo.model.financialmgmt.calendar.PeriodControl;
 import org.openbravo.model.materialmgmt.cost.Costing;
 import org.openbravo.model.materialmgmt.cost.CostingAlgorithm;
 import org.openbravo.model.materialmgmt.cost.CostingRule;
 import org.openbravo.model.materialmgmt.cost.CostingRuleInit;
 import org.openbravo.model.materialmgmt.cost.TransactionCost;
+import org.openbravo.model.materialmgmt.onhandquantity.StorageDetail;
 import org.openbravo.model.materialmgmt.transaction.InventoryCount;
 import org.openbravo.model.materialmgmt.transaction.InventoryCountLine;
 import org.openbravo.model.materialmgmt.transaction.MaterialTransaction;
@@ -242,6 +245,121 @@
         throw new OBException("@TrxWithNoCost@");
       }
     }
+
+    // Check current period is opened for Material Physical Inventory document
+    OBCriteria<Organization> ohql = OBDal.getInstance().createCriteria(Organization.class);
+    ohql.add(Restrictions.eq(Organization.PROPERTY_ALLOWPERIODCONTROL, true));
+    ohql.setFilterOnReadableClients(false);
+    ohql.setFilterOnReadableOrganization(false);
+    ScrollableResults orgList = ohql.scroll(ScrollMode.FORWARD_ONLY);
+    int i = 0;
+    try {
+      while (orgList.next()) {
+        Organization organization = (Organization) orgList.get()[0];
+        StringBuffer phqlWhere = new StringBuffer();
+        phqlWhere.append(" as pc");
+        phqlWhere.append(" join pc." + PeriodControl.PROPERTY_PERIOD + " as p");
+        phqlWhere.append(" where p." + Period.PROPERTY_STARTINGDATE + " <= :date");
+        phqlWhere.append(" and p." + Period.PROPERTY_ENDINGDATE + " >= :date");
+        phqlWhere.append(" and pc." + PeriodControl.PROPERTY_ORGANIZATION + " = :org");
+        phqlWhere.append(" and pc." + PeriodControl.PROPERTY_DOCUMENTCATEGORY + " = 'MMI'");
+        phqlWhere.append(" and pc." + PeriodControl.PROPERTY_PERIODSTATUS + " = 'O'");
+        final OBQuery<PeriodControl> phql = OBDal.getInstance().createQuery(PeriodControl.class,
+            phqlWhere.toString());
+        phql.setFilterOnReadableClients(false);
+        phql.setFilterOnReadableOrganization(false);
+        phql.setNamedParameter("date", new Date());
+        phql.setNamedParameter("org", organization);
+        phql.setMaxResult(1);
+        PeriodControl period = (PeriodControl) phql.uniqueResult();
+        if (period == null) {
+          throw new OBException(String.format(OBMessageUtils.messageBD("PeriodClosedForMMI"),
+              organization.getName()));
+        }
+
+        i++;
+        if (i % 100 == 0) {
+          OBDal.getInstance().flush();
+          OBDal.getInstance().getSession().clear();
+        }
+      }
+    } finally {
+      orgList.close();
+    }
+
+    // Check there is not negative stock without allowing it
+    for (Client client : getClients()) {
+      if (!client.getClientInformationList().get(0).isAllowNegativeStock()) {
+        final OBCriteria<StorageDetail> sdhql = OBDal.getInstance().createCriteria(
+            StorageDetail.class);
+        sdhql.add(Restrictions.eq(StorageDetail.PROPERTY_CLIENT, client));
+        sdhql.add(Restrictions.or(
+            Restrictions.lt(StorageDetail.PROPERTY_QUANTITYONHAND, BigDecimal.ZERO),
+            Restrictions.lt(StorageDetail.PROPERTY_ONHANDORDERQUANITY, BigDecimal.ZERO)));
+        sdhql.setFilterOnReadableClients(false);
+        sdhql.setFilterOnReadableOrganization(false);
+        sdhql.setMaxResults(1);
+        StorageDetail storageDetail = (StorageDetail) sdhql.uniqueResult();
+        if (storageDetail != null) {
+          throw new OBException(String.format(
+              OBMessageUtils.messageBD("NegativeStockWithoutAllowing"), client.getName()));
+        }
+      }
+    }
+
+    // Check there is not stock of a product in a UOM different from the one defined for it
+    StringBuffer sdphqlWhere = new StringBuffer();
+    sdphqlWhere.append(" as sd");
+    sdphqlWhere.append(" join sd." + StorageDetail.PROPERTY_PRODUCT + " as p");
+    sdphqlWhere.append(" where p." + Product.PROPERTY_UOM + " <> sd." + StorageDetail.PROPERTY_UOM);
+    sdphqlWhere.append(" and (coalesce(sd." + StorageDetail.PROPERTY_QUANTITYONHAND + ",0) > 0");
+    sdphqlWhere.append(" or coalesce(sd." + StorageDetail.PROPERTY_ONHANDORDERQUANITY + ",0) > 0)");
+    final OBQuery<StorageDetail> sdphql = OBDal.getInstance().createQuery(StorageDetail.class,
+        sdphqlWhere.toString());
+    sdphql.setFilterOnReadableClients(false);
+    sdphql.setFilterOnReadableOrganization(false);
+    sdphql.setMaxResult(1);
+    StorageDetail storageDetailProduct = (StorageDetail) sdphql.uniqueResult();
+    if (storageDetailProduct != null) {
+      throw new OBException("@ProductStockInDifferentUOM@");
+    }
+
+    // Check there are not inconsistencies between M_TRANSACTION and M_STORAGE_DETAIL tables
+    StringBuffer tsdhqlWhere = new StringBuffer();
+    tsdhqlWhere.append(" select 1");
+    tsdhqlWhere.append(" from " + MaterialTransaction.ENTITY_NAME + " as t");
+    tsdhqlWhere.append(", " + StorageDetail.ENTITY_NAME + " as sd");
+    tsdhqlWhere.append(" where t." + MaterialTransaction.PROPERTY_PRODUCT + " = sd."
+        + StorageDetail.PROPERTY_PRODUCT);
+    tsdhqlWhere.append(" and t." + MaterialTransaction.PROPERTY_STORAGEBIN + " = sd."
+        + StorageDetail.PROPERTY_STORAGEBIN);
+    tsdhqlWhere.append(" and t." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE + " = sd."
+        + StorageDetail.PROPERTY_ATTRIBUTESETVALUE);
+    tsdhqlWhere.append(" and t." + MaterialTransaction.PROPERTY_UOM + " = sd."
+        + StorageDetail.PROPERTY_UOM);
+    tsdhqlWhere.append(" and coalesce(t." + MaterialTransaction.PROPERTY_ORDERUOM
+        + ", '0') = coalesce(sd." + StorageDetail.PROPERTY_ORDERUOM + ", '0')");
+    tsdhqlWhere.append(" group by t." + MaterialTransaction.PROPERTY_PRODUCT);
+    tsdhqlWhere.append(" , t." + MaterialTransaction.PROPERTY_STORAGEBIN);
+    tsdhqlWhere.append(" , t." + MaterialTransaction.PROPERTY_ATTRIBUTESETVALUE);
+    tsdhqlWhere.append(" , t." + MaterialTransaction.PROPERTY_UOM);
+    tsdhqlWhere.append(" , t." + MaterialTransaction.PROPERTY_ORDERUOM);
+    tsdhqlWhere.append(" , sd." + StorageDetail.PROPERTY_PRODUCT);
+    tsdhqlWhere.append(" , sd." + StorageDetail.PROPERTY_STORAGEBIN);
+    tsdhqlWhere.append(" , sd." + StorageDetail.PROPERTY_ATTRIBUTESETVALUE);
+    tsdhqlWhere.append(" , sd." + StorageDetail.PROPERTY_UOM);
+    tsdhqlWhere.append(" , sd." + StorageDetail.PROPERTY_ORDERUOM);
+    tsdhqlWhere.append(" having (coalesce(sum(t." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY
+        + "), 0) <> coalesce(max(sd." + StorageDetail.PROPERTY_QUANTITYONHAND + "), 0)");
+    tsdhqlWhere.append(" or coalesce(sum(t." + MaterialTransaction.PROPERTY_ORDERQUANTITY
+        + "), 0) <> coalesce(max(sd." + StorageDetail.PROPERTY_ONHANDORDERQUANITY + "), 0))");
+    final Query tsdhql = OBDal.getInstance().getSession().createQuery(tsdhqlWhere.toString());
+    tsdhql.setMaxResults(1);
+    Object transactionStorageDetail = tsdhql.uniqueResult();
+    if (transactionStorageDetail != null) {
+      throw new OBException("@InconsistenciesInStock@");
+    }
+
   }
 
   private void updateLegacyCosts() {