details: https://code.openbravo.com/erp/devel/pi/rev/4bf53115b637 changeset: 33959:4bf53115b637 user: Armaignac <collazoandy4 <at> gmail.com> date: Tue May 01 18:01:20 2018 -0400 summary: Fixes issue 38386: Same search key can be used twice in warehouse definition
The same Search Key in warehouse and storage bin can be used twice because a missing constraint An unique constraints was added to warehouse definition. details: https://code.openbravo.com/erp/devel/pi/rev/78f6e305eb5e changeset: 33960:78f6e305eb5e user: Atul Gaware <atul.gaware <at> openbravo.com> date: Tue May 15 15:33:25 2018 +0530 summary: Related to issue 38386: Same search key can be used twice in warehouse definition A build validation to check whether there exists a warehouse with same value within client to avoid failure of build when adding a unique constraint for client and value in m_warehouse table. diffstat: src-db/database/model/tables/M_WAREHOUSE.xml | 6 +- src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.class | Bin src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValueData.class | Bin src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.java | 62 ++++++++++ src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue_data.xsql | 49 +++++++ 5 files changed, 116 insertions(+), 1 deletions(-) diffs (141 lines): diff -r 844e420a74a6 -r 78f6e305eb5e src-db/database/model/tables/M_WAREHOUSE.xml --- a/src-db/database/model/tables/M_WAREHOUSE.xml Mon May 14 19:29:02 2018 +0000 +++ b/src-db/database/model/tables/M_WAREHOUSE.xml Tue May 15 15:33:25 2018 +0530 @@ -97,8 +97,12 @@ <reference local="M_WAREHOUSE_RULE_ID" foreign="M_WAREHOUSE_RULE_ID"/> </foreign-key> <unique name="M_WAREHOUSE_NAME"> + <unique-column name="NAME"/> <unique-column name="AD_CLIENT_ID"/> - <unique-column name="NAME"/> + </unique> + <unique name="M_WAREHOUSE_VALUE"> + <unique-column name="VALUE"/> + <unique-column name="AD_CLIENT_ID"/> </unique> <check name="M_WAREHOUSE_ALLOCATED"><![CDATA[ISALLOCATED IN ('Y', 'N')]]></check> <check name="M_WAREHOUSE_ISACTIVE_CHK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> diff -r 844e420a74a6 -r 78f6e305eb5e src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.class has changed diff -r 844e420a74a6 -r 78f6e305eb5e src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValueData.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/DuplicatedClientWarehouseValueData.class has changed diff -r 844e420a74a6 -r 78f6e305eb5e src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue.java Tue May 15 15:33:25 2018 +0530 @@ -0,0 +1,62 @@ +/* + ************************************************************************* + * 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) 2018 Openbravo SLU + * All Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ + */ +package org.openbravo.buildvalidation; + +import java.util.ArrayList; +import java.util.List; + +import org.openbravo.base.ExecutionLimits; +import org.openbravo.buildvalidation.BuildValidation; +import org.openbravo.database.ConnectionProvider; +import org.openbravo.modulescript.OpenbravoVersion; + +/** + * This validation is related to Issue 0038386: Same search key can be used twice in warehouse + * definition + */ + +public class DuplicatedClientWarehouseValue extends BuildValidation { + @Override + public List<String> execute() { + ConnectionProvider cp = getConnectionProvider(); + ArrayList<String> errors = new ArrayList<String>(); + try { + if (DuplicatedClientWarehouseValueData.existsDuplicatedClientWarehouseValue(cp)) { + DuplicatedClientWarehouseValueData[] clientWarehouse = DuplicatedClientWarehouseValueData + .duplicatedClientWarehouseValue(cp); + errors + .add("Due to a database constraint modification, is no longer allowed to " + + "have the same Warehouse search key more than once within client. " + + "There exists data in your database that do not fit this new constraint. Please review following:- "); + for (int i = 0; i < clientWarehouse.length; i++) { + errors.add(" Client: " + clientWarehouse[i].client + ", Search Key: " + + clientWarehouse[i].searchkey + ", Warehouse: " + clientWarehouse[i].warehouse); + } + } + } catch (Exception e) { + return handleError(e); + } + return errors; + } + + @Override + protected ExecutionLimits getBuildValidationLimits() { + return new ExecutionLimits("0", null, new OpenbravoVersion(3, 0, 33955)); + } +} \ No newline at end of file diff -r 844e420a74a6 -r 78f6e305eb5e src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue_data.xsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/DuplicatedClientWarehouseValue_data.xsql Tue May 15 15:33:25 2018 +0530 @@ -0,0 +1,49 @@ +<?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) 2018 Openbravo SLU + * All Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ +--> + +<SqlClass name="DuplicatedClientWarehouseValueData" package="org.openbravo.buildvalidation"> + <SqlMethod name="duplicatedClientWarehouseValue" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + SELECT c.name AS client, + w.value AS searchkey, + w.name AS warehouse + FROM m_warehouse w + JOIN ad_client c ON w.ad_client_id = c.ad_client_id + WHERE EXISTS (SELECT 1 + FROM m_warehouse w2 + WHERE w2.m_warehouse_id <> w.m_warehouse_id + AND w2.ad_client_id = w.ad_client_id + AND w2.value = w.value) + ORDER BY c.name, + w.value, + w.name + ]]></Sql> + </SqlMethod> + <SqlMethod name="existsDuplicatedClientWarehouseValue" type="preparedStatement" return="boolean"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + SELECT count(*) AS EXISTING + FROM m_warehouse + GROUP BY ad_client_id, value + HAVING count(*)>1 + ]]></Sql> + </SqlMethod> +</SqlClass> ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
