Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
Total | n/a |
0 / 0 |
n/a |
0 / 0 |
CRAP | n/a |
0 / 0 |
|||
OverallCalendar | n/a |
0 / 0 |
n/a |
0 / 0 |
0 | n/a |
0 / 0 |
1 | <?php |
2 | |
3 | namespace BO\Zmsdb\Query; |
4 | |
5 | class OverallCalendar extends Base |
6 | { |
7 | const TABLE = 'gesamtkalender'; |
8 | |
9 | const UPSERT_MULTI = ' |
10 | INSERT INTO gesamtkalender |
11 | (scope_id, availability_id, time, seat, status) |
12 | VALUES %s |
13 | ON DUPLICATE KEY UPDATE |
14 | status = CASE |
15 | WHEN status = "termin" THEN "termin" |
16 | ELSE VALUES(status) |
17 | END, |
18 | |
19 | availability_id = CASE |
20 | WHEN status = "termin" |
21 | THEN availability_id |
22 | ELSE VALUES(availability_id) |
23 | END, |
24 | |
25 | updated_at = CURRENT_TIMESTAMP |
26 | '; |
27 | |
28 | const CANCEL_AVAILABILITY = ' |
29 | UPDATE gesamtkalender |
30 | SET status = "cancelled", |
31 | availability_id= NULL, |
32 | updated_at = CURRENT_TIMESTAMP |
33 | WHERE scope_id = :scope_id |
34 | AND availability_id= :availability_id |
35 | AND status = "free" |
36 | '; |
37 | |
38 | const PURGE_MISSING_AVAIL_BY_SCOPE = ' |
39 | UPDATE gesamtkalender g |
40 | LEFT JOIN oeffnungszeit a |
41 | ON g.availability_id = a.OeffnungszeitID |
42 | SET g.status = "cancelled", |
43 | g.availability_id= NULL, |
44 | g.updated_at = CURRENT_TIMESTAMP |
45 | WHERE ( a.OeffnungszeitID IS NULL |
46 | OR a.Endedatum < :dateString ) |
47 | AND g.scope_id = :scopeID |
48 | AND g.status <> "termin" |
49 | '; |
50 | |
51 | |
52 | const DELETE_ALL_BEFORE = ' |
53 | DELETE FROM gesamtkalender |
54 | WHERE time < :threshold |
55 | '; |
56 | |
57 | const FIND_FREE_SEAT = ' |
58 | SELECT seat |
59 | FROM gesamtkalender |
60 | WHERE scope_id = :scope |
61 | AND time >= :start |
62 | AND time < :end |
63 | AND status = "free" |
64 | GROUP BY seat |
65 | HAVING COUNT(*) = :units |
66 | ORDER BY seat |
67 | LIMIT 1 |
68 | '; |
69 | |
70 | const BLOCK_SEAT_RANGE = ' |
71 | UPDATE gesamtkalender |
72 | SET process_id = :pid, |
73 | slots = CASE |
74 | WHEN time = :start THEN :units |
75 | ELSE NULL |
76 | END, |
77 | status = "termin" |
78 | WHERE scope_id = :scope |
79 | AND seat = :seat |
80 | AND time >= :start |
81 | AND time < :end |
82 | '; |
83 | |
84 | const UNBOOK_PROCESS = ' |
85 | UPDATE gesamtkalender g |
86 | LEFT JOIN oeffnungszeit a |
87 | ON g.availability_id = a.OeffnungszeitID |
88 | SET g.process_id = NULL, |
89 | g.slots = NULL, |
90 | g.status = CASE |
91 | WHEN a.OeffnungszeitID IS NULL |
92 | OR a.Endedatum < CURDATE() |
93 | THEN "cancelled" |
94 | WHEN g.seat > IFNULL(a.Anzahlterminarbeitsplaetze,1) |
95 | THEN "cancelled" |
96 | ELSE "free" |
97 | END, |
98 | g.updated_at = CURRENT_TIMESTAMP |
99 | WHERE g.scope_id = :scope_id |
100 | AND g.process_id = :process_id |
101 | '; |
102 | |
103 | const SELECT_RANGE = ' |
104 | SELECT g.scope_id, g.time, g.availability_id, g.seat, g.status, g.process_id, g.slots, g.updated_at, |
105 | s.Bezeichnung as scope_name, s.standortkuerzel as scope_short |
106 | FROM gesamtkalender g |
107 | JOIN standort s ON g.scope_id = s.StandortID |
108 | WHERE g.scope_id IN (%s) |
109 | AND g.time BETWEEN :from AND :until |
110 | ORDER BY g.scope_id, g.time, g.seat |
111 | '; |
112 | |
113 | const SELECT_RANGE_UPDATED = ' |
114 | SELECT g.scope_id, g.time, g.availability_id, g.seat, g.status, g.process_id, g.slots, g.updated_at, |
115 | s.Bezeichnung as scope_name, s.standortkuerzel as scope_short |
116 | FROM gesamtkalender g |
117 | JOIN standort s ON g.scope_id = s.StandortID |
118 | WHERE g.scope_id IN (%s) |
119 | AND g.time BETWEEN :from AND :until |
120 | AND g.updated_at > :updatedAfter |
121 | ORDER BY g.scope_id, g.time, g.seat |
122 | '; |
123 | } |