Fixes issue 22293: Wrong checking in c_period_trg3 trigger
authorJavier Etxarri <javier.echarri@openbravo.com>
Mon, 28 Jan 2013 11:44:47 +0100
changeset 19480 c915c82a2e1d
parent 19479 d000753ca50c
child 19481 cc72a8879a12
Fixes issue 22293: Wrong checking in c_period_trg3 trigger
src-db/database/model/functions/C_YEARPERIODS.xml
src-db/database/model/triggers/C_PERIOD_TRG3.xml
src/org/openbravo/Event/PeriodEnventHandler.java
--- a/src-db/database/model/functions/C_YEARPERIODS.xml	Mon Jan 28 10:25:08 2013 +0100
+++ b/src-db/database/model/functions/C_YEARPERIODS.xml	Mon Jan 28 11:44:47 2013 +0100
@@ -38,6 +38,7 @@
     v_MonthNo NUMBER;
     v_StartDate DATE;
     Test NUMBER;
+    V_COUNT NUMBER;
     v_ResultStr VARCHAR(300) ;
     --  C_Year Variables
     v_Client_ID VARCHAR2(32);
@@ -127,6 +128,19 @@
         -- get new v_NextNo
         AD_Sequence_Next('C_Period', v_Year_ID, v_NextNo) ;
         --          DBMS_OUTPUT.PUT_LINE('Adding Period ID: '||v_NextNo);
+
+        SELECT COUNT(*) INTO V_COUNT 
+	FROM C_PERIOD 
+	WHERE C_PERIOD_ID <> v_NextNo
+	AND AD_ORG_ID = v_Org_ID
+	AND AD_CLIENT_ID = AD_Client_ID
+	AND TO_DATE(ADD_MONTHS(v_StartDate, v_MonthNo-1)) <= ENDDATE 
+	AND ADD_MONTHS(TRUNC(TO_DATE(ADD_MONTHS(v_StartDate, v_MonthNo-1)), 'MM'), 1) -1 >= STARTDATE;
+	
+	IF (V_COUNT > 0) THEN
+		RAISE_APPLICATION_ERROR(-20000, '@DatesOverlapped@');
+	END IF;
+	
         INSERT
         INTO C_Period
           (
--- a/src-db/database/model/triggers/C_PERIOD_TRG3.xml	Mon Jan 28 10:25:08 2013 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,60 +0,0 @@
-<?xml version="1.0"?>
-  <database name="TRIGGER C_PERIOD_TRG3">
-    <trigger name="C_PERIOD_TRG3" table="C_PERIOD" fires="after" insert="true" update="true" delete="false" foreach="statement">
-      <body><![CDATA[
-    /*************************************************************************
-    * 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):  ______________________________________.
-    ************************************************************************/
-
-    V_Count NUMBER:= 0;
-    TYPE RECORD IS REF CURSOR;
-    Cur_Period RECORD;
-Begin
-
-    IF AD_isTriggerEnabled()='N' THEN RETURN;
-    END IF;
-
-    For Cur_Period In
-      (Select * 
-      From C_Period
-      Where C_Period_Id In (Select Distinct(Cp.C_Period_Id)
-      From C_Period Cp 
-            Join C_Periodcontrol Cpc On Cpc.C_Period_Id= Cp.C_Period_Id 
-      Where Periodstatus <> 'P'
-      and periodtype = 'S'
-      )
-       )
-    Loop
-       Select Count(*)
-       Into V_COUNT
-       From C_Period 
-       Where  exists (select 1 from c_year y1, c_year y2
-       								where y1.c_year_id = c_period.c_year_id
-       								and y1.c_calendar_id = y2.c_calendar_id
-       								and y2.c_year_id = Cur_Period.C_Year_Id
-       								and c_period.c_period_id <> Cur_Period.c_period_id)
-       and periodtype = 'S'
-       And Cur_Period.startdate <= Enddate
-       And Cur_Period.Enddate >= Startdate;
-       If (V_COUNT > 0) Then
-         RAISE_APPLICATION_ERROR(-20000, '@DatesOverlapped@');
-       End if;
-    End Loop;
-
-    END C_PERIOD_TRG3
-]]></body>
-    </trigger>
-  </database>
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/org/openbravo/Event/PeriodEnventHandler.java	Mon Jan 28 11:44:47 2013 +0100
@@ -0,0 +1,71 @@
+package org.openbravo.Event;
+
+import javax.enterprise.event.Observes;
+
+import org.apache.log4j.Logger;
+import org.hibernate.criterion.Restrictions;
+import org.openbravo.base.exception.OBException;
+import org.openbravo.base.model.Entity;
+import org.openbravo.base.model.ModelProvider;
+import org.openbravo.client.kernel.event.EntityNewEvent;
+import org.openbravo.client.kernel.event.EntityPersistenceEventObserver;
+import org.openbravo.client.kernel.event.EntityUpdateEvent;
+import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.service.OBCriteria;
+import org.openbravo.dal.service.OBDal;
+import org.openbravo.database.ConnectionProvider;
+import org.openbravo.erpCommon.utility.Utility;
+import org.openbravo.model.financialmgmt.calendar.Period;
+import org.openbravo.service.db.DalConnectionProvider;
+
+public class PeriodEnventHandler extends EntityPersistenceEventObserver {
+  private static Entity[] entities = { ModelProvider.getInstance().getEntity(Period.ENTITY_NAME) };
+  protected Logger logger = Logger.getLogger(this.getClass());
+
+  @Override
+  protected Entity[] getObservedEntities() {
+    return entities;
+  }
+
+  public void onUpdate(@Observes EntityUpdateEvent event) {
+    ConnectionProvider conn = new DalConnectionProvider(false);
+    String language = OBContext.getOBContext().getLanguage().getLanguage();
+    if (!isValidEvent(event)) {
+      return;
+    }
+    if (checkPeriod((Period) event.getTargetInstance())) {
+      logger.error("Period " + event.getTargetInstance().getId()
+          + " is being updated and is overlapping another period");
+      throw new OBException(Utility.messageBD(conn, "DatesOverlapped", language));
+    }
+    logger.info("Period " + event.getTargetInstance().getId() + " is being updated");
+  }
+
+  public void onSave(@Observes EntityNewEvent event) {
+    ConnectionProvider conn = new DalConnectionProvider(false);
+    String language = OBContext.getOBContext().getLanguage().getLanguage();
+    if (!isValidEvent(event)) {
+      return;
+    }
+    if (checkPeriod((Period) event.getTargetInstance())) {
+      logger.error("Period " + event.getTargetInstance().getId()
+          + " is being saved and is overlapping another period");
+      throw new OBException(Utility.messageBD(conn, "DatesOverlapped", language));
+    }
+    logger.info("Period " + event.getTargetInstance().getId() + " is being saved");
+  }
+
+  private boolean checkPeriod(Period period) {
+    OBCriteria<Period> criteria = OBDal.getInstance().createCriteria(Period.class);
+    criteria.add(Restrictions.eq(Period.PROPERTY_ORGANIZATION, period.getOrganization()));
+    criteria.add(Restrictions.eq(Period.PROPERTY_CLIENT, period.getClient()));
+    criteria.add(Restrictions.ne(Period.PROPERTY_ID, period.getId()));
+    criteria.add(Restrictions.ge(Period.PROPERTY_ENDINGDATE, period.getStartingDate()));
+    criteria.add(Restrictions.le(Period.PROPERTY_STARTINGDATE, period.getEndingDate()));
+    if (criteria.list().size() > 0) {
+      return true;
+    } else {
+      return false;
+    }
+  }
+}
\ No newline at end of file