source: trunk/prototype/modules/calendar/schedules.php @ 7702

Revision 7702, 8.5 KB checked in by acoutinho, 8 years ago (diff)

Ticket #3259 - Otimizar carregamento de eventos no modulo ExpressoCalendar?

Line 
1<?php
2/**
3 * Created by JetBrains PhpStorm.
4 * User: Adriano
5 * Date: 28/12/12
6 * Time: 18:36
7 * To change this template use File | Settings | File Templates.
8 */
9if(!defined('ROOTPATH'))
10    define('ROOTPATH', dirname(__FILE__).'/../..');
11
12require_once ROOTPATH.'/api/controller.php';
13require_once ROOTPATH.'/modules/calendar/constants.php';
14require_once ROOTPATH.'/modules/calendar/interceptors/DBMapping.php';
15
16use prototype\api\Config as Config;
17
18class Schedule{
19
20    function findEventsRange( $start, $end, $calendars, $timezones ){
21
22        $sql =
23            ' SELECT calendar_object.id as id ,calendar_object.cal_uid as "uid", calendar_object.type_id as "type", '
24                .'calendar_object.dtstart as "startTime", calendar_object.summary as "summary", '
25                .'calendar_object.description as "description", calendar_object.dtend as "endTime" , '
26                .'calendar_object.priority as "priority", calendar_object.due as "due", '
27                .'calendar_object.percentage as "percentage", calendar_object.status as "status", '
28                .'calendar_object.location as "location", calendar_object.allday as "allDay", '
29                .'calendar_object.transp as "transparent", calendar_object.class_id as "class", '
30                .'calendar_object.repeat as "repeat", calendar_object.range_start as "rangeStart", '
31                .'calendar_object.range_end as "rangeEnd", calendar_object.last_update as "lastUpdate", '
32                .'calendar_object.dtstamp as "dtstamp", calendar_object.sequence as "sequence", '
33                .'calendar_object.tzid as "timezone" ,calendar_to_calendar_object.calendar_id as '
34                .'calendar FROM calendar_to_calendar_object , calendar_object '
35                .'WHERE ( calendar_to_calendar_object.calendar_id IN (\'' . implode('\',\'', $calendars) . '\')) '
36                .'AND calendar_to_calendar_object.calendar_object_id = calendar_object.id '
37                .'AND calendar_object.id NOT IN(select calendar_object_task_id from calendar_task_to_activity_object where owner = \'' . Config::me('uidNumber') . '\') ';
38
39        $ids = array();
40        $occ = array();
41
42        if ($occurrences = DBMapping::checkOccurrences($start, $end, $calendars))
43            foreach ($occurrences as $id => $occurrence) {
44                $ids[] = $id;
45                $occ[] = $occurrence;
46            }
47
48        $where =
49            ' AND ((range_end >= \'' . $start . '\' AND range_end <= \'' . $end . '\') OR '
50                .'(range_start >= \'' . $start . '\' AND range_start <= \'' . $end . '\') OR '
51                .'(range_start <= \'' . $start . '\' AND range_end >= \'' . $end . '\')) '
52                .(!empty($ids) ? ' ' .'AND calendar_object.id NOT IN (\'' . implode('\',\'', $ids) . '\') ' : ' ')
53                .'AND calendar_object.dtstart NOT IN (SELECT calendar_repeat_occurrence.occurrence from calendar_repeat_occurrence, '
54                .'calendar_repeat where (calendar_repeat_occurrence.repeat_id = calendar_repeat.id) '
55                .'AND (calendar_repeat.object_id = calendar_object.id))';
56
57        $params = Controller::service('PostgreSQL')->execResultSql($sql.$where);
58        $params = array_merge($params, $occ);
59
60        return $this->normalizeEvents( $params, $timezones );
61    }
62
63    function findEventsSearch( $summary, $description, $calendars, $timezones, $limit, $offset ){
64
65        $sql = ' SELECT calendar_object.id as id ,calendar_object.cal_uid as "uid", calendar_object.type_id as "type", '
66            .'calendar_object.dtstart as "startTime", calendar_object.summary as "summary", '
67            .'calendar_object.description as "description", calendar_object.dtend as "endTime" , '
68            .'calendar_object.priority as "priority", calendar_object.due as "due", '
69            .'calendar_object.percentage as "percentage", calendar_object.status as "status", '
70            .'calendar_object.location as "location", calendar_object.allday as "allDay", '
71            .'calendar_object.transp as "transparent", calendar_object.class_id as "class", '
72            .'calendar_object.repeat as "repeat", calendar_object.range_start as "rangeStart", '
73            .'calendar_object.range_end as "rangeEnd", calendar_object.last_update as "lastUpdate", '
74            .'calendar_object.dtstamp as "dtstamp", calendar_object.sequence as "sequence", '
75            .'calendar_object.tzid as "timezone" ,calendar_to_calendar_object.calendar_id as '
76            .'calendar FROM calendar_to_calendar_object , calendar_object '
77            .'WHERE ( calendar_to_calendar_object.calendar_id IN (\'' . implode('\',\'', $calendars) . '\')) '
78            .'AND calendar_to_calendar_object.calendar_object_id = calendar_object.id '
79            .'AND calendar_object.id NOT IN(select calendar_object_task_id from calendar_task_to_activity_object where owner = \'' . Config::me('uidNumber') . '\') ';
80
81
82            $where = 'AND (((upper("summary") like upper(\'%'.$summary.'%\') OR upper("description") like upper(\'%'.$description.'%\')))) ORDER BY dtstart LIMIT '.$limit.'  OFFSET '.$offset.' ';
83            $params = Controller::service('PostgreSQL')->execResultSql($sql.$where);
84
85
86
87        return $this->normalizeEvents( $params, $timezones );
88    }
89
90    function normalizeEvents( &$result, $timezones ){
91
92        $date = new DateTime('now', new DateTimeZone('UTC'));
93        $DayLigth = array();
94
95        foreach ($result as $i => $v) {
96
97            $currentTimezone = (isset($v['calendar']) && isset($timezones[$v['calendar']])) ? $timezones[$v['calendar']] : $v['timezone'];
98
99            $date->setTimestamp((int) ($v['startTime'] / 1000));
100            $date->setTimezone( new DateTimeZone( $v['timezone'] ));
101            $DayLigth['event']['startTime'] = ($date->getTimestamp() + $date->getOffset()).'000';
102
103            $date->setTimezone( new DateTimeZone($currentTimezone));
104            $DayLigth['calendar']['startTime'] = ($date->getTimestamp() + $date->getOffset()).'000';
105
106            $date->setTimestamp((int) ($v['endTime'] / 1000));
107            $date->setTimezone( new DateTimeZone($currentTimezone));
108            $DayLigth['event']['endTime'] = ($date->getTimestamp() + $date->getOffset()).'000';
109
110            if(isset($v['due']) && $v['due'] != '0'){
111                $date->setTimestamp((int) ($v['due'] / 1000));
112                $DayLigth['event']['due'] = ($date->getTimestamp() + $date->getOffset()).'000';
113            }else{
114                $DayLigth['event']['due'] = $v['due'];
115            }
116
117            $date->setTimezone( new DateTimeZone($currentTimezone));
118            $DayLigth['calendar']['endTime'] = ($date->getTimestamp() + $date->getOffset()).'000';
119
120            $result[$i]['DayLigth'] = $DayLigth;
121
122            if(isset( $v['occurrences'] ) && count( $v['occurrences'] ) > 0){
123
124                foreach( $result[$i]['occurrences'] as &$o){
125
126                    $date->setTimestamp((int) ($o / 1000));
127                    $o = ($date->getTimestamp() + $date->getOffset()).'000';
128
129                }
130            }
131
132            $attend = Controller::read(array('concept' => 'participant'), null, array('filter' => array('AND', array('=','schedulable',$v['id']), array('=','user', Config::me('uidNumber'))  )));
133
134            if(count($attend) > 0 && !empty($attend)){
135
136                if(array_key_exists(0, $attend))
137                    $attend = $attend[0];
138
139                $result[$i]['editable'] = (strstr($attend['acl'],"w") || strstr($attend['acl'],"o")) ? 1 : 0;
140
141            }else{
142
143               $result[$i]['editable'] = $v['type'] == '2' ? 0 : 2;
144
145            }
146        }
147
148        return $this->toUtf8( $result );
149    }
150
151    function srtToUtf8($data) {
152        return mb_convert_encoding($data, 'UTF-8', 'UTF-8 , ISO-8859-1');
153    }
154
155    function toUtf8($data) {
156        if (is_array($data)) {
157            $return = array();
158            foreach ($data as $i => $v)
159                $return[$this->srtToUtf8($i)] = (is_array($v)) ? $this->toUtf8($v) : $this->srtToUtf8($v);
160
161            return $return;
162        }else
163            return $this->srtToUtf8($data);
164    }
165
166}
167
168$params = $_GET;
169$schedule = new Schedule();
170
171if(isset( $params['rangeStart'] ))
172    $events = $schedule->findEventsRange( $params['rangeStart'], $params['rangeEnd'], $params['calendar'], $params['timezones'] );
173else
174    $events = $schedule->findEventsSearch( $params['summary'], $params['description'], $params['calendar'], $params['timezones'], $params['limit'], $params['offset'] );
175
176echo json_encode( $events );
177?>
Note: See TracBrowser for help on using the repository browser.