← Back to branch summary

~xibo-maintainers/xibo/tempel

« back to all changes in this revision

Viewing changes to lib/Factory/ScheduleFactory.php

  • Committer: Dan Garner
  • Date: 2016-02-15 17:54:45 UTC
  • mto: (454.4.130)
  • mto: This revision was merged to the branch mainline in revision 484.
  • Revision ID: git-v1:dd226a6f84464ff28758a249e1fd52ca4a35d911
Correction to Layout Duration ToolTip
xibosignage/xibo#721

Show diffs side-by-side

added added

removed removed

Lines of Context:
lib/Factory/ScheduleFactory.php
9
9
namespace Xibo\Factory;
10
10
 
11
11
 
12
 
use Jenssegers\Date\Date;
13
 
use Stash\Interfaces\PoolInterface;
14
12
use Xibo\Entity\Schedule;
15
13
use Xibo\Exception\NotFoundException;
16
 
use Xibo\Service\ConfigServiceInterface;
17
 
use Xibo\Service\DateServiceInterface;
18
 
use Xibo\Service\LogServiceInterface;
19
 
use Xibo\Service\SanitizerServiceInterface;
20
 
use Xibo\Storage\StorageServiceInterface;
 
14
use Xibo\Helper\Log;
 
15
use Xibo\Helper\Sanitize;
 
16
use Xibo\Storage\PDOConnect;
21
17
 
22
 
/**
23
 
 * Class ScheduleFactory
24
 
 * @package Xibo\Factory
25
 
 */
26
18
class ScheduleFactory extends BaseFactory
27
19
{
28
20
    /**
29
 
     * @var ConfigServiceInterface
30
 
     */
31
 
    private $config;
32
 
 
33
 
    /** @var PoolInterface  */
34
 
    private $pool;
35
 
 
36
 
    /** @var  DateServiceInterface */
37
 
    private $dateService;
38
 
 
39
 
    /**
40
 
     * @var DisplayGroupFactory
41
 
     */
42
 
    private $displayGroupFactory;
43
 
 
44
 
    /** @var DayPartFactory */
45
 
    private $dayPartFactory;
46
 
 
47
 
    /**
48
 
     * Construct a factory
49
 
     * @param StorageServiceInterface $store
50
 
     * @param LogServiceInterface $log
51
 
     * @param SanitizerServiceInterface $sanitizerService
52
 
     * @param ConfigServiceInterface $config
53
 
     * @param PoolInterface $pool
54
 
     * @param DateServiceInterface $date
55
 
     * @param DisplayGroupFactory $displayGroupFactory
56
 
     * @param DayPartFactory $dayPartFactory
57
 
     */
58
 
    public function __construct($store, $log, $sanitizerService, $config, $pool, $date, $displayGroupFactory, $dayPartFactory)
59
 
    {
60
 
        $this->setCommonDependencies($store, $log, $sanitizerService);
61
 
        $this->config = $config;
62
 
        $this->pool = $pool;
63
 
        $this->dateService = $date;
64
 
        $this->displayGroupFactory = $displayGroupFactory;
65
 
        $this->dayPartFactory = $dayPartFactory;
66
 
    }
67
 
 
68
 
    /**
69
 
     * Create Empty
70
 
     * @return Schedule
71
 
     */
72
 
    public function createEmpty()
73
 
    {
74
 
        return new Schedule(
75
 
            $this->getStore(),
76
 
            $this->getLog(),
77
 
            $this->config,
78
 
            $this->pool,
79
 
            $this->dateService,
80
 
            $this->displayGroupFactory,
81
 
            $this->dayPartFactory
82
 
        );
83
 
    }
84
 
 
85
 
    /**
86
21
     * @param int $eventId
87
22
     * @return Schedule
88
23
     * @throws NotFoundException
89
24
     */
90
 
    public function getById($eventId)
 
25
    public static function getById($eventId)
91
26
    {
92
 
        $events = $this->query(null, ['disableUserCheck' => 1, 'eventId' => $eventId]);
 
27
        $events = ScheduleFactory::query(null, ['disableUserCheck' => 1, 'eventId' => $eventId]);
93
28
 
94
29
        if (count($events) <= 0)
95
30
            throw new NotFoundException();
102
37
     * @return array[Schedule]
103
38
     * @throws NotFoundException
104
39
     */
105
 
    public function getByDisplayGroupId($displayGroupId)
 
40
    public static function getByDisplayGroupId($displayGroupId)
106
41
    {
107
 
        return $this->query(null, ['disableUserCheck' => 1, 'displayGroupIds' => [$displayGroupId]]);
 
42
        return ScheduleFactory::query(null, ['disableUserCheck' => 1, 'displayGroupId' => $displayGroupId]);
108
43
    }
109
44
 
110
45
    /**
113
48
     * @return array[Schedule]
114
49
     * @throws NotFoundException
115
50
     */
116
 
    public function getByCampaignId($campaignId)
 
51
    public static function getByCampaignId($campaignId)
117
52
    {
118
 
        return $this->query(null, ['disableUserCheck' => 1, 'campaignId' => $campaignId]);
 
53
        return ScheduleFactory::query(null, ['disableUserCheck' => 1, 'campaignId' => $campaignId]);
119
54
    }
120
55
 
121
56
    /**
122
 
     * Get by OwnerId
123
57
     * @param int $ownerId
124
58
     * @return array[Schedule]
125
59
     * @throws NotFoundException
126
60
     */
127
 
    public function getByOwnerId($ownerId)
128
 
    {
129
 
        return $this->query(null, ['disableUserCheck' => 1, 'ownerId' => $ownerId]);
130
 
    }
131
 
 
132
 
    /**
133
 
     * Get by DayPartId
134
 
     * @param int $dayPartId
135
 
     * @return Schedule[]
136
 
     * @throws NotFoundException
137
 
     */
138
 
    public function getByDayPartId($dayPartId)
139
 
    {
140
 
        return $this->query(null, ['disableUserCheck' => 1, 'dayPartId' => $dayPartId]);
141
 
    }
142
 
 
143
 
    /**
144
 
     * @param int $displayId
145
 
     * @param Date $fromDt
146
 
     * @param Date $toDt
147
 
     * @param array $options
148
 
     * @return array
149
 
     */
150
 
    public function getForXmds($displayId, $fromDt, $toDt, $options = [])
151
 
    {
152
 
        $options = array_merge(['useGroupId' => false], $options);
153
 
 
154
 
        // We dial the fromDt back to the top of the day, so that we include dayPart events that start on this
155
 
        // day
156
 
        $adjustedFromDt = clone $fromDt;
157
 
 
158
 
        $params = array(
159
 
            'fromDt' => $adjustedFromDt->startOfDay()->format('U'),
160
 
            'toDt' => $toDt->format('U')
161
 
        );
162
 
 
163
 
        $this->getLog()->debug('Get events for XMDS - with options: ' . json_encode($options));
164
 
 
165
 
        // Add file nodes to the $fileElements
166
 
        // Firstly get all the scheduled layouts
167
 
        $SQL = '
168
 
            SELECT `schedule`.eventTypeId, 
169
 
                layout.layoutId, 
170
 
                `layout`.status, 
171
 
                `command`.code, 
172
 
                schedule.fromDt, 
173
 
                schedule.toDt,
174
 
                schedule.recurrence_type AS recurrenceType,
175
 
                schedule.recurrence_detail AS recurrenceDetail,
176
 
                schedule.recurrence_range AS recurrenceRange,
177
 
                schedule.recurrenceRepeatsOn,
178
 
                schedule.lastRecurrenceWatermark,
179
 
                schedule.eventId, 
180
 
                schedule.is_priority AS isPriority,
181
 
                `schedule`.displayOrder,
182
 
                schedule.dayPartId,
183
 
                `schedule`.campaignId,
184
 
                `schedule`.commandId,
185
 
                schedule.syncTimezone,
186
 
                `campaign`.campaign,
187
 
                `command`.command,
188
 
                `lkscheduledisplaygroup`.displayGroupId,
189
 
                `daypart`.isAlways,
190
 
                `daypart`.isCustom
191
 
               FROM `schedule`
192
 
                INNER JOIN `daypart`
193
 
                ON `daypart`.dayPartId = `schedule`.dayPartId
194
 
                INNER JOIN `lkscheduledisplaygroup`
195
 
                ON `lkscheduledisplaygroup`.eventId = `schedule`.eventId
196
 
                INNER JOIN `lkdgdg`
197
 
                ON `lkdgdg`.parentId = `lkscheduledisplaygroup`.displayGroupId
198
 
        ';
199
 
 
200
 
        if (!$options['useGroupId']) {
201
 
            // Only join in the display/display group link table if we are requesting this data for a display
202
 
            // otherwise the group we are looking for might not have any displays, and this join would therefore
203
 
            // remove any records.
204
 
            $SQL .= '
205
 
                INNER JOIN `lkdisplaydg`
206
 
                ON lkdisplaydg.DisplayGroupID = `lkdgdg`.childId
207
 
            ';
208
 
        }
209
 
 
210
 
        $SQL .= '    
211
 
                LEFT OUTER JOIN `campaign`
212
 
                ON `schedule`.CampaignID = campaign.CampaignID
213
 
                LEFT OUTER JOIN `lkcampaignlayout`
214
 
                ON lkcampaignlayout.CampaignID = campaign.CampaignID
215
 
                LEFT OUTER JOIN `layout`
216
 
                ON lkcampaignlayout.LayoutID = layout.LayoutID
217
 
                  AND layout.retired = 0
218
 
                LEFT OUTER JOIN `command`
219
 
                ON `command`.commandId = `schedule`.commandId
220
 
        ';
221
 
 
222
 
        if ($options['useGroupId']) {
223
 
            $SQL .= ' WHERE `lkdgdg`.childId = :displayGroupId ';
224
 
            $params['displayGroupId'] = $options['displayGroupId'];
225
 
        } else {
226
 
            $SQL .= ' WHERE `lkdisplaydg`.DisplayID = :displayId ';
227
 
            $params['displayId'] = $displayId;
228
 
        }
229
 
 
230
 
        // Are we requesting a range or a single date/time?
231
 
        // only the inclusive range changes, but it is clearer to have the whole statement reprinted.
232
 
        // Ranged request
233
 
        $SQL .= ' 
234
 
            AND (
235
 
                  (schedule.FromDT <= :toDt AND IFNULL(`schedule`.toDt, `schedule`.fromDt) >= :fromDt) 
236
 
                  OR `schedule`.recurrence_range >= :fromDt 
237
 
                  OR (
238
 
                    IFNULL(`schedule`.recurrence_range, 0) = 0 AND IFNULL(`schedule`.recurrence_type, \'\') <> \'\' 
239
 
                  )
240
 
            )
241
 
            
242
 
            ORDER BY schedule.DisplayOrder, IFNULL(lkcampaignlayout.DisplayOrder, 0), schedule.FromDT, schedule.eventId
243
 
        ';
244
 
 
245
 
        return $this->getStore()->select($SQL, $params);
 
61
    public static function getByOwnerId($ownerId)
 
62
    {
 
63
        return ScheduleFactory::query(null, ['disableUserCheck' => 1, 'ownerId' => $ownerId]);
246
64
    }
247
65
 
248
66
    /**
250
68
     * @param array $filterBy
251
69
     * @return array[Schedule]
252
70
     */
253
 
    public function query($sortOrder = null, $filterBy = [])
 
71
    public static function query($sortOrder = null, $filterBy = null)
254
72
    {
255
73
        $entries = [];
256
74
        $params = [];
257
75
 
 
76
        $useDetail = Sanitize::getInt('useDetail', $filterBy) == 1;
 
77
 
258
78
        $sql = '
259
 
        SELECT `schedule`.eventId, 
260
 
            `schedule`.eventTypeId,
 
79
        SELECT `schedule`.eventId, `schedule`.eventTypeId, ';
 
80
 
 
81
        if ($useDetail) {
 
82
            $sql .= '
 
83
            `schedule_detail`.fromDt,
 
84
            `schedule_detail`.toDt,
 
85
            ';
 
86
        } else {
 
87
            $sql .= '
261
88
            `schedule`.fromDt,
262
89
            `schedule`.toDt,
 
90
            ';
 
91
        }
 
92
 
 
93
        $sql .= '
263
94
            `schedule`.userId,
264
95
            `schedule`.displayOrder,
265
96
            `schedule`.is_priority AS isPriority,
266
97
            `schedule`.recurrence_type AS recurrenceType,
267
98
            `schedule`.recurrence_detail AS recurrenceDetail,
268
99
            `schedule`.recurrence_range AS recurrenceRange,
269
 
            `schedule`.recurrenceRepeatsOn,
270
 
            `schedule`.lastRecurrenceWatermark,
271
100
            campaign.campaignId,
272
101
            campaign.campaign,
273
102
            `command`.commandId,
274
 
            `command`.command,
275
 
            `schedule`.dayPartId,
276
 
            `schedule`.syncTimezone,
277
 
            `daypart`.isAlways,
278
 
            `daypart`.isCustom
 
103
            `command`.command
279
104
          FROM `schedule`
280
 
            INNER JOIN `daypart`
281
 
            ON `daypart`.dayPartId = `schedule`.dayPartId
282
105
            LEFT OUTER JOIN `campaign`
283
106
            ON campaign.CampaignID = `schedule`.CampaignID
284
107
            LEFT OUTER JOIN `command`
285
108
            ON `command`.commandId = `schedule`.commandId
 
109
        ';
 
110
 
 
111
        if ($useDetail) {
 
112
            $sql .= '
 
113
            INNER JOIN `schedule_detail`
 
114
            ON schedule_detail.EventID = `schedule`.EventID
 
115
            ';
 
116
        }
 
117
 
 
118
        $sql .= '
286
119
          WHERE 1 = 1
287
120
        ';
288
121
 
289
 
        if ($this->getSanitizer()->getInt('eventId', $filterBy) !== null) {
 
122
        if (Sanitize::getInt('eventId', $filterBy) !== null) {
290
123
            $sql .= ' AND `schedule`.eventId = :eventId ';
291
 
            $params['eventId'] = $this->getSanitizer()->getInt('eventId', $filterBy);
 
124
            $params['eventId'] = Sanitize::getInt('eventId', $filterBy);
292
125
        }
293
126
 
294
 
        if ($this->getSanitizer()->getInt('campaignId', $filterBy) !== null) {
 
127
        if (Sanitize::getInt('campaignId', $filterBy) !== null) {
295
128
            $sql .= ' AND `schedule`.campaignId = :campaignId ';
296
 
            $params['campaignId'] = $this->getSanitizer()->getInt('campaignId', $filterBy);
297
 
        }
298
 
 
299
 
        if ($this->getSanitizer()->getInt('ownerId', $filterBy) !== null) {
300
 
            $sql .= ' AND `schedule`.userId = :ownerId ';
301
 
            $params['ownerId'] = $this->getSanitizer()->getInt('ownerId', $filterBy);
302
 
        }
303
 
 
304
 
        if ($this->getSanitizer()->getInt('dayPartId', $filterBy) !== null) {
305
 
            $sql .= ' AND `schedule`.dayPartId = :dayPartId ';
306
 
            $params['dayPartId'] = $this->getSanitizer()->getInt('dayPartId', $filterBy);
 
129
            $params['campaignId'] = Sanitize::getInt('campaignId', $filterBy);
307
130
        }
308
131
 
309
132
        // Only 1 date
310
 
        if ($this->getSanitizer()->getInt('fromDt', $filterBy) !== null && $this->getSanitizer()->getInt('toDt', $filterBy) === null) {
 
133
        if (!$useDetail && Sanitize::getInt('fromDt', $filterBy) !== null && Sanitize::getInt('toDt', $filterBy) === null) {
311
134
            $sql .= ' AND schedule.fromDt > :fromDt ';
312
 
            $params['fromDt'] = $this->getSanitizer()->getInt('fromDt', $filterBy);
 
135
            $params['fromDt'] = Sanitize::getInt('fromDt', $filterBy);
313
136
        }
314
137
 
315
 
        if ($this->getSanitizer()->getInt('toDt', $filterBy) !== null && $this->getSanitizer()->getInt('fromDt', $filterBy) === null) {
 
138
        if (!$useDetail && Sanitize::getInt('toDt', $filterBy) !== null && Sanitize::getInt('fromDt', $filterBy) === null) {
316
139
            $sql .= ' AND IFNULL(schedule.toDt, schedule.fromDt) <= :toDt ';
317
 
            $params['toDt'] = $this->getSanitizer()->getInt('toDt', $filterBy);
 
140
            $params['toDt'] = Sanitize::getInt('toDt', $filterBy);
 
141
        }
 
142
 
 
143
        if ($useDetail && Sanitize::getInt('fromDt', $filterBy) !== null && Sanitize::getInt('toDt', $filterBy) === null) {
 
144
            $sql .= ' AND schedule_detail.fromDt > :fromDt ';
 
145
            $params['fromDt'] = Sanitize::getInt('fromDt', $filterBy);
 
146
        }
 
147
 
 
148
        if ($useDetail && Sanitize::getInt('toDt', $filterBy) !== null && Sanitize::getInt('fromDt', $filterBy) === null) {
 
149
            $sql .= ' AND IFNULL(schedule_detail.toDt, schedule_detail.fromDt) <= :toDt ';
 
150
            $params['toDt'] = Sanitize::getInt('toDt', $filterBy);
318
151
        }
319
152
        // End only 1 date
320
153
 
321
154
        // Both dates
322
 
        if ($this->getSanitizer()->getInt('fromDt', $filterBy) !== null && $this->getSanitizer()->getInt('toDt', $filterBy) !== null) {
323
 
            $sql .= ' AND schedule.fromDt < :toDt ';
324
 
            $sql .= ' AND IFNULL(schedule.toDt, schedule.fromDt) >= :fromDt ';
325
 
            $params['fromDt'] = $this->getSanitizer()->getInt('fromDt', $filterBy);
326
 
            $params['toDt'] = $this->getSanitizer()->getInt('toDt', $filterBy);
 
155
        if (!$useDetail && Sanitize::getInt('fromDt', $filterBy) !== null && Sanitize::getInt('toDt', $filterBy) !== null) {
 
156
            $sql .= ' AND schedule.fromDt > :fromDt ';
 
157
            $sql .= ' AND IFNULL(schedule.toDt, schedule.fromDt) <= :toDt ';
 
158
            $params['fromDt'] = Sanitize::getInt('fromDt', $filterBy);
 
159
            $params['toDt'] = Sanitize::getInt('toDt', $filterBy);
 
160
        }
 
161
 
 
162
        if ($useDetail && Sanitize::getInt('fromDt', $filterBy) !== null && Sanitize::getInt('toDt', $filterBy) !== null) {
 
163
            $sql .= ' AND schedule_detail.fromDt < :toDt ';
 
164
            $sql .= ' AND IFNULL(schedule_detail.toDt, schedule_detail.fromDt) >= :fromDt ';
 
165
            $params['fromDt'] = Sanitize::getInt('fromDt', $filterBy);
 
166
            $params['toDt'] = Sanitize::getInt('toDt', $filterBy);
327
167
        }
328
168
        // End both dates
329
169
 
330
 
        if ($this->getSanitizer()->getIntArray('displayGroupIds', $filterBy) != null) {
331
 
            $sql .= ' AND `schedule`.eventId IN (SELECT `lkscheduledisplaygroup`.eventId FROM `lkscheduledisplaygroup` WHERE displayGroupId IN (' . implode(',', $this->getSanitizer()->getIntArray('displayGroupIds', $filterBy)) . ')) ';
332
 
        }
333
 
 
334
 
        // Future schedules?
335
 
        if ($this->getSanitizer()->getInt('futureSchedulesFrom', $filterBy) !== null && $this->getSanitizer()->getInt('futureSchedulesTo', $filterBy) === null) {
336
 
            // Get schedules that end after this date, or that recur after this date
337
 
            $sql .= ' AND (IFNULL(`schedule`.toDt, `schedule`.fromDt) >= :futureSchedulesFrom OR `schedule`.recurrence_range >= :futureSchedulesFrom OR (IFNULL(`schedule`.recurrence_range, 0) = 0) AND IFNULL(`schedule`.recurrence_type, \'\') <> \'\') ';
338
 
            $params['futureSchedulesFrom'] = $this->getSanitizer()->getInt('futureSchedulesFrom', $filterBy);
339
 
        }
340
 
 
341
 
        if ($this->getSanitizer()->getInt('futureSchedulesFrom', $filterBy) !== null && $this->getSanitizer()->getInt('futureSchedulesTo', $filterBy) !== null) {
342
 
            // Get schedules that end after this date, or that recur after this date
343
 
            $sql .= ' AND ((schedule.fromDt < :futureSchedulesTo AND IFNULL(`schedule`.toDt, `schedule`.fromDt) >= :futureSchedulesFrom) OR `schedule`.recurrence_range >= :futureSchedulesFrom OR (IFNULL(`schedule`.recurrence_range, 0) = 0 AND IFNULL(`schedule`.recurrence_type, \'\') <> \'\') ) ';
344
 
            $params['futureSchedulesFrom'] = $this->getSanitizer()->getInt('futureSchedulesFrom', $filterBy);
345
 
            $params['futureSchedulesTo'] = $this->getSanitizer()->getInt('futureSchedulesTo', $filterBy);
346
 
        }
347
 
 
348
 
        // Restrict to mediaId - meaning layout schedules of which the layouts contain the selected mediaId
349
 
        if ($this->getSanitizer()->getInt('mediaId', $filterBy) !== null) {
350
 
            $sql .= '
351
 
                AND schedule.campaignId IN (
352
 
                    SELECT `lkcampaignlayout`.campaignId
353
 
                      FROM `lkwidgetmedia`
354
 
                       INNER JOIN `widget`
355
 
                       ON `widget`.widgetId = `lkwidgetmedia`.widgetId
356
 
                       INNER JOIN `lkregionplaylist`
357
 
                       ON `lkregionplaylist`.playlistId = `widget`.playlistId
358
 
                       INNER JOIN `region`
359
 
                       ON `region`.regionId = `lkregionplaylist`.regionId
360
 
                       INNER JOIN layout
361
 
                       ON layout.LayoutID = region.layoutId
362
 
                       INNER JOIN `lkcampaignlayout`
363
 
                       ON lkcampaignlayout.layoutId = layout.layoutId
364
 
                     WHERE lkwidgetmedia.mediaId = :mediaId
365
 
                    UNION
366
 
                    SELECT `lkcampaignlayout`.campaignId
367
 
                      FROM `layout`
368
 
                       INNER JOIN `lkcampaignlayout`
369
 
                       ON lkcampaignlayout.layoutId = layout.layoutId
370
 
                     WHERE `layout`.backgroundImageId = :mediaId
371
 
                )
372
 
            ';
373
 
            $params['mediaId'] = $this->getSanitizer()->getInt('mediaId', $filterBy);
 
170
        if (Sanitize::getIntArray('displayGroupIds', $filterBy) != null) {
 
171
            $sql .= ' AND `schedule`.eventId IN (SELECT `lkscheduledisplaygroup`.eventId FROM `lkscheduledisplaygroup` WHERE displayGroupId IN (' . implode(',', Sanitize::getIntArray('displayGroupIds', $filterBy)) . ')) ';
374
172
        }
375
173
 
376
174
        // Sorting?
377
175
        if (is_array($sortOrder))
378
176
            $sql .= 'ORDER BY ' . implode(',', $sortOrder);
379
177
 
380
 
        foreach ($this->getStore()->select($sql, $params) as $row) {
381
 
            $entries[] = $this->createEmpty()->hydrate($row, ['intProperties' => ['isPriority', 'syncTimezone', 'isAlways', 'isCustom']]);
 
178
        Log::sql($sql, $params);
 
179
 
 
180
        foreach (PDOConnect::select($sql, $params) as $row) {
 
181
            $entries[] = (new Schedule())->hydrate($row, ['intProperties' => ['isPriority']]);
382
182
        }
383
183
 
384
184
        return $entries;