Fixes Issue 27056:It is possible to assign more than once same warehouse to one org
authorAtul Gaware <atul.gaware@openbravo.com>
Fri, 25 Jul 2014 16:26:07 +0530
changeset 24135 c3d258163d89
parent 24134 7ad630b1df1f
child 24136 01542ca587a5
Fixes Issue 27056:It is possible to assign more than once same warehouse to one org
src-db/database/model/tables/AD_ORG_WAREHOUSE.xml
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedOrgWarehouse.class
src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedOrgWarehouseData.class
src-util/buildvalidation/build/javasqlc/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouseData.java
src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouse.java
src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouse_data.xsql
--- a/src-db/database/model/tables/AD_ORG_WAREHOUSE.xml	Wed Jul 30 01:37:56 2014 +0200
+++ b/src-db/database/model/tables/AD_ORG_WAREHOUSE.xml	Fri Jul 25 16:26:07 2014 +0530
@@ -53,6 +53,10 @@
       <index name="AD_ORGWAREHOUSE_ORG_IDX" unique="false">
         <index-column name="AD_ORG_ID"/>
       </index>
+      <unique name="AD_ORG_WAREHOUSE_WAREHOUSE_UN">
+        <unique-column name="AD_ORG_ID"/>
+        <unique-column name="M_WAREHOUSE_ID"/>
+      </unique>
       <check name="AD_ORG_WAREHOUSE_ACTIVE_CHK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check>
     </table>
   </database>
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedOrgWarehouse.class has changed
Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedOrgWarehouseData.class has changed
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/build/javasqlc/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouseData.java	Fri Jul 25 16:26:07 2014 +0530
@@ -0,0 +1,135 @@
+//Sqlc generated V1.O00-1
+package org.openbravo.buildvalidation;
+
+import java.sql.*;
+
+import org.apache.log4j.Logger;
+
+import javax.servlet.ServletException;
+
+import org.openbravo.data.FieldProvider;
+import org.openbravo.database.ConnectionProvider;
+import org.openbravo.data.UtilSql;
+import java.util.*;
+
+class DuplicatedOrgWarehouseData implements FieldProvider {
+static Logger log4j = Logger.getLogger(DuplicatedOrgWarehouseData.class);
+  private String InitRecordNumber="0";
+  public String client;
+  public String organization;
+  public String warehouse;
+
+  public String getInitRecordNumber() {
+    return InitRecordNumber;
+  }
+
+  public String getField(String fieldName) {
+    if (fieldName.equalsIgnoreCase("client"))
+      return client;
+    else if (fieldName.equalsIgnoreCase("organization"))
+      return organization;
+    else if (fieldName.equalsIgnoreCase("warehouse"))
+      return warehouse;
+   else {
+     log4j.debug("Field does not exist: " + fieldName);
+     return null;
+   }
+ }
+
+  public static DuplicatedOrgWarehouseData[] DuplicatedOrganizationWarehouse(ConnectionProvider connectionProvider)    throws ServletException {
+    return DuplicatedOrganizationWarehouse(connectionProvider, 0, 0);
+  }
+
+  public static DuplicatedOrgWarehouseData[] DuplicatedOrganizationWarehouse(ConnectionProvider connectionProvider, int firstRegister, int numberRegisters)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "        SELECT c.name as client, o.name as organization, w.name as warehouse" +
+      "        FROM ad_org_warehouse ow" +
+      "            left join ad_org o on ow.ad_org_id=o.ad_org_id" +
+      "            left join m_warehouse w on ow.m_warehouse_id = w.m_warehouse_id" +
+      "            left join ad_client c on ow.ad_client_id = c.ad_client_id" +
+      "        GROUP BY ow.m_warehouse_id, ow.ad_org_id, c.name, o.name, w.name" +
+      "        HAVING count(*)>1";
+
+    ResultSet result;
+    Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      result = st.executeQuery();
+      long countRecord = 0;
+      long countRecordSkip = 1;
+      boolean continueResult = true;
+      while(countRecordSkip < firstRegister && continueResult) {
+        continueResult = result.next();
+        countRecordSkip++;
+      }
+      while(continueResult && result.next()) {
+        countRecord++;
+        DuplicatedOrgWarehouseData objectDuplicatedOrgWarehouseData = new DuplicatedOrgWarehouseData();
+        objectDuplicatedOrgWarehouseData.client = UtilSql.getValue(result, "client");
+        objectDuplicatedOrgWarehouseData.organization = UtilSql.getValue(result, "organization");
+        objectDuplicatedOrgWarehouseData.warehouse = UtilSql.getValue(result, "warehouse");
+        objectDuplicatedOrgWarehouseData.InitRecordNumber = Integer.toString(firstRegister);
+        vector.addElement(objectDuplicatedOrgWarehouseData);
+        if (countRecord >= numberRegisters && numberRegisters != 0) {
+          continueResult = false;
+        }
+      }
+      result.close();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    DuplicatedOrgWarehouseData objectDuplicatedOrgWarehouseData[] = new DuplicatedOrgWarehouseData[vector.size()];
+    vector.copyInto(objectDuplicatedOrgWarehouseData);
+    return(objectDuplicatedOrgWarehouseData);
+  }
+
+  public static boolean existsDuplicatedOrgWarehouse(ConnectionProvider connectionProvider)    throws ServletException {
+    String strSql = "";
+    strSql = strSql + 
+      "        SELECT count(*) AS EXISTING" +
+      "        FROM ad_org_warehouse" +
+      "        GROUP BY ad_org_id, m_warehouse_id" +
+      "        HAVING count(*)>1";
+
+    ResultSet result;
+    boolean boolReturn = false;
+    PreparedStatement st = null;
+
+    try {
+    st = connectionProvider.getPreparedStatement(strSql);
+
+      result = st.executeQuery();
+      if(result.next()) {
+        boolReturn = !UtilSql.getValue(result, "existing").equals("0");
+      }
+      result.close();
+    } catch(SQLException e){
+      log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
+      throw new ServletException("@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
+    } catch(Exception ex){
+      log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
+      throw new ServletException("@CODE=@" + ex.getMessage());
+    } finally {
+      try {
+        connectionProvider.releasePreparedStatement(st);
+      } catch(Exception ignore){
+        ignore.printStackTrace();
+      }
+    }
+    return(boolReturn);
+  }
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouse.java	Fri Jul 25 16:26:07 2014 +0530
@@ -0,0 +1,50 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License.
+ * The Original Code is Openbravo ERP.
+ * The Initial Developer of the Original Code is Openbravo SLU
+ * All portions are Copyright (C) 2014 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+ */
+package org.openbravo.buildvalidation;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.openbravo.database.ConnectionProvider;
+
+/**
+ * This validation is related to Issue 27056: It is possible to assign more than once the same warehouse to one organization
+ */
+
+public class DuplicatedOrgWarehouse extends BuildValidation{
+  @Override
+  public List<String> execute() {
+    ConnectionProvider cp = getConnectionProvider();
+    ArrayList<String> errors = new ArrayList<String>();
+    try{
+      if (DuplicatedOrgWarehouseData.existsDuplicatedOrgWarehouse(cp)) {
+    	DuplicatedOrgWarehouseData[] orgWarehouse =  DuplicatedOrgWarehouseData.DuplicatedOrganizationWarehouse(cp);
+    	errors.add("Due to a database constraint modification, is no longer allowed to "+
+    	            "assign the same Warehouse more than once in each Organization. "+
+    	            "There exists data in your database that do not fit this new constraint. Please review following:- ");
+    	for(int i = 0; i < orgWarehouse.length ;i++) {
+        errors.add(" Client: " + orgWarehouse[i].client + ", Organization: " + orgWarehouse[i].organization + ", Warehouse: " + orgWarehouse[i].warehouse);
+    	}
+      }
+    } catch (Exception e) {
+      return handleError(e);
+    }
+    return errors;
+  }
+}
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedOrgWarehouse_data.xsql	Fri Jul 25 16:26:07 2014 +0530
@@ -0,0 +1,43 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+ * Version 1.1  with a permitted attribution clause; you may not  use this
+ * file except in compliance with the License. You  may  obtain  a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * Software distributed under the License  is  distributed  on  an "AS IS"
+ * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+ * License for the specific  language  governing  rights  and  limitations
+ * under the License.
+ * The Original Code is Openbravo ERP.
+ * The Initial Developer of the Original Code is Openbravo SLU
+ * All portions are Copyright (C) 2014 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+<SqlClass name="DuplicatedOrgWarehouseData" package="org.openbravo.buildvalidation">
+  <SqlMethod name="DuplicatedOrganizationWarehouse" type="preparedStatement" return="multiple">
+      <SqlMethodComment></SqlMethodComment>
+      <Sql><![CDATA[
+        SELECT c.name as client, o.name as organization, w.name as warehouse
+        FROM ad_org_warehouse ow
+            left join ad_org o on ow.ad_org_id=o.ad_org_id
+            left join m_warehouse w on ow.m_warehouse_id = w.m_warehouse_id
+            left join ad_client c on ow.ad_client_id = c.ad_client_id
+        GROUP BY ow.m_warehouse_id, ow.ad_org_id, c.name, o.name, w.name
+        HAVING count(*)>1
+      ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="existsDuplicatedOrgWarehouse" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+        SELECT count(*) AS EXISTING
+        FROM ad_org_warehouse
+        GROUP BY ad_org_id, m_warehouse_id
+        HAVING count(*)>1
+      ]]></Sql>
+  </SqlMethod>
+</SqlClass>