Changeset 6151 for trunk/prototype/modules/calendar/interceptors

Timestamp:

05/09/12 17:50:59 (12 years ago)

Author:

acoutinho

Message:

Ticket #2709 - Inconsistencias na query de pesquisas de eventos

File:

• trunk/prototype/modules/calendar/interceptors/DBMapping.php (modified) (6 diffs)

trunk/prototype/modules/calendar/interceptors/DBMapping.php

@@ -111 +111 @@
 
     public function findSchedulable(&$uri, &$params, &$criteria, $original) {
-        if (isset($criteria['filter'][3][1]) && $criteria['filter'][3][1] == 'calendar') {
-            $start = $criteria['filter'][1][2];
-            $end = $criteria['filter'][2][2];
+        if (isset($criteria['filter'][2][1]) && $criteria['filter'][2][1] == 'calendar') {
+            $start = array( $criteria['filter'][1][1][1][2] , $criteria['filter'][1][2][1][2], $criteria['filter'][1][3][1][2] );
+            $end = array( $criteria['filter'][1][1][2][2] , $criteria['filter'][1][2][2][2], $criteria['filter'][1][3][2][2] );
 
             $ids = array();
             $occ = array();
 
-            if ($occurrences = self::checkOccurrences($start, $end))
+            if ($occurrences = self::checkOccurrences($start[0], $end[0]))
                 foreach ($occurrences as $id => $occurrence) {
                     $ids[] = $id;
                     $occ[] = $occurrence;
-                }
-
-            $sql = ' SELECT calendar_object.id as id ,calendar_object.cal_uid as "uid", calendar_object.type_id as "type", calendar_object.dtstart as "startTime", calendar_object.summary as "summary", calendar_object.description as "description", calendar_object.dtend as "endTime" , calendar_object.location as "location", calendar_object.allday as "allDay", calendar_object.transp as "transparent", calendar_object.class_id as "class", calendar_object.repeat as "repeat", calendar_object.range_start as "rangeStart",calendar_object.range_end as "rangeEnd", calendar_object.last_update as "lastUpdate", calendar_object.dtstamp as "dtstamp", calendar_object.sequence as "sequence",  calendar_object.tzid as "timezone" ,calendar_to_calendar_object.calendar_id as calendar FROM calendar_to_calendar_object , calendar_object WHERE (range_start >= \'' . $start . '\' AND range_end <= \'' . $end . '\' AND calendar_to_calendar_object.calendar_id IN (\'' . implode('\',\'', $criteria['filter'][3][2]) . '\')) AND calendar_to_calendar_object.calendar_object_id = calendar_object.id' . (!empty($ids) ? ' AND calendar_object.id NOT IN (\'' . implode('\',\'', $ids) . '\')' : '');
+                }       
+        
+            $queryRange = '((range_end >= \'' . $start[0] . '\' AND range_end <= \'' . $end[0] . '\') OR '
+                    .'(range_start >= \'' . $start[1] . '\' AND range_start <= \'' . $end[1] . '\') OR '
+                    .'(range_start <= \'' . $start[2] . '\' AND range_end >= \'' . $end[2] . '\')) ';
+                
+            $sql = ' SELECT calendar_object.id as id ,calendar_object.cal_uid as "uid", calendar_object.type_id as "type", '
+                .'calendar_object.dtstart as "startTime", calendar_object.summary as "summary", '
+                .'calendar_object.description as "description", calendar_object.dtend as "endTime" , '
+                .'calendar_object.location as "location", calendar_object.allday as "allDay", '
+                .'calendar_object.transp as "transparent", calendar_object.class_id as "class", '
+                .'calendar_object.repeat as "repeat", calendar_object.range_start as "rangeStart", '
+                .'calendar_object.range_end as "rangeEnd", calendar_object.last_update as "lastUpdate", '
+                .'calendar_object.dtstamp as "dtstamp", calendar_object.sequence as "sequence", '
+                .'calendar_object.tzid as "timezone" ,calendar_to_calendar_object.calendar_id as '
+                .'calendar FROM calendar_to_calendar_object , calendar_object '
+                .'WHERE ( '. $queryRange .' '
+                .'AND calendar_to_calendar_object.calendar_id IN (\'' . implode('\',\'', $criteria['filter'][2][2]) . '\')) '
+                .'AND calendar_to_calendar_object.calendar_object_id = calendar_object.id' . (!empty($ids) ? ' '
+                .'AND calendar_object.id NOT IN (\'' . implode('\',\'', $ids) . '\')' : '');
 
             $params = Controller::service('PostgreSQL')->execResultSql($sql);
@@ -479 +496 @@
     public function deepnessFindEvent(&$uri, &$result, &$criteria, $original) {
         if (isset($original['criteria']['deepness']) && $original['criteria']['deepness'] != 0) {
-
             $Time = new DateTime('now', new DateTimeZone('UTC'));
             $DayLigth = array();
@@ -511 +527 @@
 
                 if (isset($v['id'])) {
-                    $result[$i]['participants'] = Controller::find( array( 'concept' => 'participant' ) , false ,array( 'filter' => array('=', 'schedulable'  ,  $v['id']), 'criteria' => array( 'deepness' => $original['criteria']['deepness'] - 1 ))); 
+                    $result[$i]['participants'] = Controller::find( array( 'concept' => 'participant' ) , false ,array( 'filter' => array('=', 'schedulable'  ,  $v['id']),'deepness' => $original['criteria']['deepness'] - 1 )); 
 
                     $attachmentRelation = Controller::find(array('concept' => 'schedulableToAttachment'), false, array('filter' => array('=', 'schedulable', $v['id'])));
@@ -537 +553 @@
 
 //TODO: Remover apos suporte a deepness na api
-    public function deepnessReadParticipant( &$uri , &$result , &$criteria , $original ){               
-       
+    public function deepnessReadParticipant( &$uri , &$result , &$criteria , $original ){
+
        if(isset($original['criteria']['deepness']) && $original['criteria']['deepness'] != 0)
        {
@@ -570 +586 @@
    
     //TODO: Remover apos suporte a deepness na api 
-    public function deepnessFindParticipant( &$uri , &$result , &$criteria , $original ){               
-       
+    public function deepnessFindParticipant( &$uri , &$result , &$criteria , $original ){
+
        if(isset($original['criteria']['deepness']) && $original['criteria']['deepness'] != 0)
        {
@@ -665 +681 @@
 
             $params = array();
-
-            $al = Controller::service('PostgreSQL')->execSql( "SELECT distinct co.id as \"id\", co.cal_uid as \"uid\", co.type_id as \"type\", co.dtstart as \"startTime\", co.summary as \"summary\", co.description as \"description\", co.dtend as \"endTime\", co.location as \"location\", co.allday as \"allDay\", co.transp as transparent, co.class_id as class, ".
-                                                                "co.range_start as \"rangeStart\", co.range_end as \"rangeEnd\", co.last_update as \"lastUpdate\", co.dtstamp as \"dtstamp\", co.sequence as \"sequence\", co.tzid as \"timezone\", CASE WHEN rep.object_id = co.id THEN occ.occurrence - al.alarm_offset ELSE co.dtstart - al.alarm_offset END as \"sendTime\" ".
-                                                                "FROM calendar_alarm as al, calendar_object as co, calendar_repeat as rep, calendar_repeat_occurrence as occ, calendar_participant as part WHERE ".
-                                                                "al.action_id = '".ALARM_ALERT."' AND al.sent = '0' AND al.participant_id = part.id AND part.object_id = co.id AND ".
-                                                                "CASE WHEN rep.object_id = co.id ".
-                                                                "THEN rep.id = occ.repeat_id AND occ.occurrence - al.alarm_offset >= '$target' AND occ.occurrence - al.alarm_offset <= '".( $target + 86400000)."' ".
-                                                                "ELSE co.range_start - al.alarm_offset >= '$target' AND co.range_start - al.alarm_offset <= '".( $target + 86400000)."' END");
-
+            $notC = array();
+
+            $al = Controller::service('PostgreSQL')->execSql("SELECT distinct co.id as \"id\", co.cal_uid as \"uid\", co.type_id as \"type\", co.dtstart as \"startTime\", co.summary as \"summary\", co.description as \"description\", co.dtend as \"endTime\", co.location as \"location\", co.allday as \"allDay\", co.transp as transparent, co.class_id as class, ".
+                "co.range_start as \"rangeStart\", co.range_end as \"rangeEnd\", co.last_update as \"lastUpdate\", co.dtstamp as \"dtstamp\", co.sequence as \"sequence\", co.tzid as \"timezone\", CASE WHEN rep.object_id = co.id THEN occ.occurrence - al.alarm_offset ELSE co.dtstart - al.alarm_offset END as \"sendTime\", ".
+                "al.unit as \"unit\", al.time as \"time\" FROM calendar_alarm as al, calendar_object as co, calendar_repeat as rep, calendar_repeat_occurrence as occ, calendar_participant as part WHERE ".
+                "al.action_id = '".ALARM_ALERT."' AND al.sent = '0' AND ( (al.participant_id = part.id) AND (part.user_info_id = '". Config::me('uidNumber') ."') ) AND part.object_id = co.id AND ".
+                "CASE WHEN rep.object_id = co.id ".
+                "THEN rep.id = occ.repeat_id AND occ.occurrence - al.alarm_offset >= '$target' AND occ.occurrence - al.alarm_offset <= '".( $target + 86400000)."' ".
+                "ELSE (co.range_start - al.alarm_offset) >= '$target' AND (co.range_start - al.alarm_offset) <= '".( $target + 86400000)."' END");
+            
             if(is_array($al))
-              foreach( $al as $v )
-              {
-                    $params['sendTime'] = $v['sendTime'];
-
-                    unset( $v['sendTime'] );
-
-                    $params['schedulable'] = $v;
+              foreach( $al as $v ){
+                if($notC[$v['id']])
+                    continue;
+
+                $notC[$v['id']] = true;
+                $params[] = array('schedulable' =>  $v);
               }
             else
               $params = false;
-
+ 
             return false;
         }