details: https://code.openbravo.com/erp/devel/pi/rev/7bd897420af5 changeset: 16549:7bd897420af5 user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> date: Wed May 09 09:42:55 2012 +0200 summary: Fixed bug 20350: Included new AccountingTabs build validation It validates client data and introduce alerts in case any of the new database contraints introduced in #13691 affect client data.
The Cbpvendoracct build validation has been removed because it has no sense anymore since #13691 details: https://code.openbravo.com/erp/devel/pi/rev/60b35ed88b24 changeset: 16550:60b35ed88b24 user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com> date: Mon Apr 23 13:30:54 2012 +0200 summary: Fixed bug 13691: Acct. schema unique constraint in several tables Created new unique constraint in C_BP_GROUP_ACCT (C_ACCTSCHEMA_ID, C_BP_GROUP_ID) and added new error message. Removed status from unique constraints in C_BP_CUSTOMER_ACCT and C_BP_VENDOR_ACCT tables Updated error messages beloging to these constraints diffstat: src-db/database/model/tables/C_BP_CUSTOMER_ACCT.xml | 1 - src-db/database/model/tables/C_BP_GROUP_ACCT.xml | 4 + src-db/database/model/tables/C_BP_VENDOR_ACCT.xml | 1 - src-db/database/sourcedata/AD_MESSAGE.xml | 15 +- src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabs.class | 0 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabsData.class | 0 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/Cbpvendoracct.class | 0 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/CbpvendoracctData.class | 0 src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs.java | 117 +++++++ src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs_data.xsql | 151 ++++++++++ src-util/buildvalidation/src/org/openbravo/buildvalidation/Cbpvendoracct.java | 85 ----- src-util/buildvalidation/src/org/openbravo/buildvalidation/Cbpvendoracct_data.xsql | 136 --------- 12 files changed, 285 insertions(+), 225 deletions(-) diffs (truncated from 589 to 300 lines): diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-db/database/model/tables/C_BP_CUSTOMER_ACCT.xml --- a/src-db/database/model/tables/C_BP_CUSTOMER_ACCT.xml Tue May 15 18:21:40 2012 +0200 +++ b/src-db/database/model/tables/C_BP_CUSTOMER_ACCT.xml Mon Apr 23 13:30:54 2012 +0200 @@ -73,7 +73,6 @@ </foreign-key> <unique name="C_BP_CUSTOMER_ACCT_BPARTNER_UN"> <unique-column name="C_BPARTNER_ID"/> - <unique-column name="STATUS"/> <unique-column name="C_ACCTSCHEMA_ID"/> </unique> <check name="C_BP_CUSTOMER_A_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-db/database/model/tables/C_BP_GROUP_ACCT.xml --- a/src-db/database/model/tables/C_BP_GROUP_ACCT.xml Tue May 15 18:21:40 2012 +0200 +++ b/src-db/database/model/tables/C_BP_GROUP_ACCT.xml Mon Apr 23 13:30:54 2012 +0200 @@ -180,6 +180,10 @@ <foreign-key foreignTable="C_VALIDCOMBINATION" name="C_BP_GROUP_ACCT_WRITEOFF_REV"> <reference local="WRITEOFF_REV_ACCT" foreign="C_VALIDCOMBINATION_ID"/> </foreign-key> + <unique name="C_BP_GROUP_ACCT_SCHEM_GROUP_UN"> + <unique-column name="C_ACCTSCHEMA_ID"/> + <unique-column name="C_BP_GROUP_ID"/> + </unique> <check name="C_BP_GROUP_ACCT_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> </table> </database> diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-db/database/model/tables/C_BP_VENDOR_ACCT.xml --- a/src-db/database/model/tables/C_BP_VENDOR_ACCT.xml Tue May 15 18:21:40 2012 +0200 +++ b/src-db/database/model/tables/C_BP_VENDOR_ACCT.xml Mon Apr 23 13:30:54 2012 +0200 @@ -84,7 +84,6 @@ <unique name="C_BP_VENDOR_ACCT_ACCTSCHEMA_UN"> <unique-column name="C_ACCTSCHEMA_ID"/> <unique-column name="C_BPARTNER_ID"/> - <unique-column name="STATUS"/> </unique> <check name="C_BP_VENDOR_ACC_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> </table> diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-db/database/sourcedata/AD_MESSAGE.xml --- a/src-db/database/sourcedata/AD_MESSAGE.xml Tue May 15 18:21:40 2012 +0200 +++ b/src-db/database/sourcedata/AD_MESSAGE.xml Mon Apr 23 13:30:54 2012 +0200 @@ -8720,7 +8720,7 @@ <!--800202--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> <!--800202--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> <!--800202--> <VALUE><![CDATA[C_BP_CUSTOMER_ACCT_BPARTNER_UN]]></VALUE> -<!--800202--> <MSGTEXT><![CDATA[The customer already has a record with the same accounting schema, with the same organization and with the same status. Please change at least one of the three]]></MSGTEXT> +<!--800202--> <MSGTEXT><![CDATA[The customer already has a record for the same accounting schema. Only one record per accounting schema is allowed.]]></MSGTEXT> <!--800202--> <MSGTYPE><![CDATA[E]]></MSGTYPE> <!--800202--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> <!--800202--></AD_MESSAGE> @@ -8731,7 +8731,7 @@ <!--800203--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> <!--800203--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> <!--800203--> <VALUE><![CDATA[C_BP_VENDOR_ACCT_ACCTSCHEMA_UN]]></VALUE> -<!--800203--> <MSGTEXT><![CDATA[The vendor already has a record with the same accounting schema and with the same status. Please change at least one of them]]></MSGTEXT> +<!--800203--> <MSGTEXT><![CDATA[The vendor already has a record for the same accounting schema. Only one record per accounting schema is allowed.]]></MSGTEXT> <!--800203--> <MSGTYPE><![CDATA[E]]></MSGTYPE> <!--800203--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> <!--800203--></AD_MESSAGE> @@ -17819,6 +17819,17 @@ <!--8927B08F10B745E9883A329F1FCBC63A--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> <!--8927B08F10B745E9883A329F1FCBC63A--></AD_MESSAGE> +<!--89882D07B9804B3ABB93C55FDBC52284--><AD_MESSAGE> +<!--89882D07B9804B3ABB93C55FDBC52284--> <AD_MESSAGE_ID><![CDATA[89882D07B9804B3ABB93C55FDBC52284]]></AD_MESSAGE_ID> +<!--89882D07B9804B3ABB93C55FDBC52284--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> +<!--89882D07B9804B3ABB93C55FDBC52284--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> +<!--89882D07B9804B3ABB93C55FDBC52284--> <ISACTIVE><![CDATA[Y]]></ISACTIVE> +<!--89882D07B9804B3ABB93C55FDBC52284--> <VALUE><![CDATA[C_BP_GROUP_ACCT_SCHEM_GROUP_UN]]></VALUE> +<!--89882D07B9804B3ABB93C55FDBC52284--> <MSGTEXT><![CDATA[The business partner category already has a record for the same accounting schema. Only one record per accounting schema is allowed.]]></MSGTEXT> +<!--89882D07B9804B3ABB93C55FDBC52284--> <MSGTYPE><![CDATA[E]]></MSGTYPE> +<!--89882D07B9804B3ABB93C55FDBC52284--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +<!--89882D07B9804B3ABB93C55FDBC52284--></AD_MESSAGE> + <!--89FDE3CB1891401A8CEDB277C95D6A90--><AD_MESSAGE> <!--89FDE3CB1891401A8CEDB277C95D6A90--> <AD_MESSAGE_ID><![CDATA[89FDE3CB1891401A8CEDB277C95D6A90]]></AD_MESSAGE_ID> <!--89FDE3CB1891401A8CEDB277C95D6A90--> <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabs.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabs.class has changed diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabsData.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabsData.class has changed diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/Cbpvendoracct.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/Cbpvendoracct.class has changed diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/CbpvendoracctData.class Binary file src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/CbpvendoracctData.class has changed diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs.java Mon Apr 23 13:30:54 2012 +0200 @@ -0,0 +1,117 @@ +/* + ************************************************************************* + * 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) 2012 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 this issue: https://issues.openbravo.com/view.php?id=13691 + * C_BP_CUSTOMER_ACCT.C_BP_CUSTOMER_ACCT_BPARTNER_UN constraint, removed status column + * C_BP_VENDOR_ACCT.C_BP_VENDOR_ACCT_ACCTSCHEMA_UN constraint, removed status column + * C_BP_GROUP_ACCT.C_BP_GROUP_ACCT_SCHEM_GROUP_UN constraint added + */ +public class AccountingTabs extends BuildValidation { + private static final String alertGroupName = "Business Partner Category Duplicated Accounts"; + private static final String alertCustomerName = "Customer Business Partner Duplicated Accounts"; + private static final String alertVendorName = "Vendor Business Partner Duplicated Accounts"; + + @Override + public List<String> execute() { + ConnectionProvider cp = getConnectionProvider(); + ArrayList<String> errors = new ArrayList<String>(); + try { + final int wrongGroupAcct = Integer.parseInt(AccountingTabsData.countWrongBPGroupAcct(cp)); + final int wrongCustomerAcct = Integer.parseInt(AccountingTabsData.countWrongCustomerAcct(cp)); + final int wrongVendorAcct = Integer.parseInt(AccountingTabsData.countWrongVendorAcct(cp)); + + if (wrongGroupAcct > 0 || wrongCustomerAcct > 0 || wrongVendorAcct > 0 ) { + errors.add("You can not apply this MP because your instance fails in the pre-validation phase: "); + + if (wrongGroupAcct > 0) { + errors + .add("It is not allowed to have more than one entry in Business Partner Category -> Accounting tab for the same accounting schema. Until 3.0MP11 it was allowed although it was wrong since the behaviour was unpredictable: any of the duplicated accounts could be used for the accounting of that business partner category. To fix this problem in your instance, you can know the duplicated entries by reviewing Alerts in your system (Alert Rule: "+ alertGroupName + "). Once you find the duplicated entries you should remove the wrong ones. After fixing all these entries you should be able to apply this MP."); + + if (AccountingTabsData.existsAlertRule(cp, alertGroupName).equals("0")) { + final String alertRuleId = AccountingTabsData.getUUID(cp); + AccountingTabsData.insertAlertRule(cp, alertRuleId, alertGroupName, "323", "select max(C_BP_Group_Acct_ID) as referencekey_id, ad_column_identifier('C_BP_Group_Acct',max(C_BP_Group_Acct_ID),'en_US') as record_id, 0 as ad_role_id, null as ad_user_id, 'Duplicated accounting Configuration entry. Please ensure just one entry exists per accounting schema for this business partner category' as description, 'Y' as isActive, max(ad_org_id) as ad_org_id, max(ad_client_id) as ad_client_id, now() as created, 0 as createdBy, now() as updated, 0 as updatedBy from C_BP_Group_Acct p group by c_acctschema_id, c_bp_group_id having count(*)>1"); + processAlert(alertRuleId, cp); + } + } + + if (wrongCustomerAcct > 0) { + errors + .add("It is not allowed to have more than one entry in Business Partner -> Customer -> Customer Accounting tab for the same accounting schema. Until 3.0MP11 it was allowed although it was wrong since the behaviour was unpredictable: any of the duplicated accounts could be used for the accounting of that business partner. To fix this problem in your instance, you can know the duplicated entries by reviewing Alerts in your system (Alert Rule: "+ alertCustomerName + "). Once you find the duplicated entries you should remove the wrong ones. After fixing all these entries you should be able to apply this MP."); + + if (AccountingTabsData.existsAlertRule(cp, alertCustomerName).equals("0")) { + final String alertRuleId = AccountingTabsData.getUUID(cp); + AccountingTabsData.insertAlertRule(cp, alertRuleId, alertCustomerName, "212", "select max(C_BP_CUSTOMER_ACCT_ID) as referencekey_id, ad_column_identifier('C_BP_CUSTOMER_ACCT',max(C_BP_CUSTOMER_ACCT_ID),'en_US') as record_id, 0 as ad_role_id, null as ad_user_id, 'Duplicated accounting Configuration entry. Please ensure just one entry exists per accounting schema for this business partner (customer)' as description, 'Y' as isActive, max(ad_org_id) as ad_org_id, max(ad_client_id) as ad_client_id, now() as created, 0 as createdBy, now() as updated, 0 as updatedBy from C_BP_CUSTOMER_ACCT p group by c_acctschema_id, c_bpartner_id having count(*)>1"); + processAlert(alertRuleId, cp); + } + } + + if (wrongVendorAcct > 0) { + errors + .add("It is not allowed to have more than one entry in Business Partner -> Vendor -> Vendor Accounting tab for the same accounting schema. Until 3.0MP11 it was allowed although it was wrong since the behaviour was unpredictable: any of the duplicated accounts could be used for the accounting of that business partner. To fix this problem in your instance, you can know the duplicated entries by reviewing Alerts in your system (Alert Rule: "+ alertVendorName + "). Once you find the duplicated entries you should remove the wrong ones. After fixing all these entries you should be able to apply this MP."); + + if (AccountingTabsData.existsAlertRule(cp, alertVendorName).equals("0")) { + final String alertRuleId = AccountingTabsData.getUUID(cp); + AccountingTabsData.insertAlertRule(cp, alertRuleId, alertVendorName, "213", "select max(C_BP_VENDOR_ACCT_ID) as referencekey_id, ad_column_identifier('C_BP_VENDOR_ACCT',max(C_BP_VENDOR_ACCT_ID),'en_US') as record_id, 0 as ad_role_id, null as ad_user_id, 'Duplicated accounting Configuration entry. Please ensure just one entry exists per accounting schema for this business partner (vendor)' as description, 'Y' as isActive, max(ad_org_id) as ad_org_id, max(ad_client_id) as ad_client_id, now() as created, 0 as createdBy, now() as updated, 0 as updatedBy from C_BP_VENDOR_ACCT p group by c_acctschema_id, c_bpartner_id having count(*)>1"); + processAlert(alertRuleId, cp); + } + } + } + } catch (Exception e) { + return handleError(e); + } + return errors; + } + + /** + * @param alertRule + * @param conn + * @throws Exception + */ + private void processAlert(String adAlertruleId, ConnectionProvider cp) throws Exception { + AccountingTabsData[] alertRule = AccountingTabsData.select(cp, adAlertruleId); + AccountingTabsData[] alert = null; + if (!alertRule[0].sql.equals("")) { + try { + alert = AccountingTabsData.selectAlert(cp, alertRule[0].sql); + } catch (Exception ex) { + return; + } + } + // Insert + if (alert != null && alert.length != 0) { + StringBuilder msg = new StringBuilder(); + + for (int i = 0; i < alert.length; i++) { + if (AccountingTabsData.existsReference(cp, adAlertruleId, alert[i].referencekeyId).equals( + "0")) { + AccountingTabsData.insertAlert(cp, alert[i].description, alertRule[0].adAlertruleId, + alert[i].recordId, alert[i].referencekeyId); + } + } + } + } + +} diff -r aa69cbc6c4c7 -r 60b35ed88b24 src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs_data.xsql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs_data.xsql Mon Apr 23 13:30:54 2012 +0200 @@ -0,0 +1,151 @@ +<?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) 2012 Openbravo SLU + * All Rights Reserved. + * Contributor(s): ______________________________________. + ************************************************************************ +--> + + +<SqlClass name="AccountingTabsData" package="org.openbravo.buildvalidation"> + <SqlMethod name="select" type="preparedStatement" return="multiple"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + SELECT AD_CLIENT_ID, + AD_ORG_ID, + CREATED, + CREATEDBY, + UPDATED, + UPDATEDBY, + ISACTIVE, + '' as RECORD_ID, + '' as DESCRIPTION, + '' as REFERENCEKEY_ID, + '' as AD_ROLE_ID, + '' as RECORDS, + '' as UUID, + AD_ALERTRULE_ID, + SQL, + NAME + FROM AD_ALERTRULE + WHERE AD_ALERTRULE_ID = ? + ]]></Sql> + <Parameter name="alertRule"/> + </SqlMethod> + + <SqlMethod name="countWrongBPGroupAcct" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + select count(s.srows) as RECORDS + from ( select 1 as srows + FROM C_BP_GROUP_ACCT + group by C_ACCTSCHEMA_ID, c_bp_group_id + having count(*) > 1 + ) s + ]]></Sql> + </SqlMethod> + <SqlMethod name="countWrongCustomerAcct" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + select count(s.srows) as RECORDS + from ( select 1 as srows + from C_BP_CUSTOMER_ACCT + group by C_ACCTSCHEMA_ID, c_bpartner_id + having count(*) > 1 + ) s + ]]></Sql> + </SqlMethod> + <SqlMethod name="countWrongVendorAcct" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + select count(s.srows) as RECORDS + from ( select 1 as srows + from C_BP_VENDOR_ACCT + group by C_ACCTSCHEMA_ID, c_bpartner_id + having count(*) > 1 + ) s + ]]></Sql> + </SqlMethod> + <SqlMethod name="existsAlertRule" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql><![CDATA[ + SELECT COUNT(*) AS EXISTING + FROM AD_ALERTRULE + WHERE NAME = ? + AND ISACTIVE = 'Y' + ]]></Sql> + <Parameter name="alertRuleName"/> + </SqlMethod> + <SqlMethod name="getUUID" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
