· 6 years ago · Apr 17, 2019, 02:22 PM
1CREATE OR REPLACE FUNCTION event_templates_with_max_quota()
2 RETURNS TABLE(
3 "id" bigint
4, "created_at" timestamp without time zone
5, "updated_at" timestamp without time zone
6, "tenant_id" bigint
7, "duration" integer
8, "reservation_duration" integer
9, "reservation_expiration_offset" integer
10, "min_participants" integer
11, "exclusivity" exclusivity_enum
12, "personalization_required" boolean
13, "tag_ids" integer[]
14, "available_locales" locale_enum[]
15, "personalization_attrs" text[]
16, "name" text
17, "desc" text
18, "name_i18n_translations" jsonb
19, "desc_i18n_translations" jsonb
20
21 , span tsrange
22 , max_quota integer
23 ) AS
24 $$
25 -- calculate potential events for the event templates because templates do not have a start time.
26 -- potential start times are stored in gcal format. we calculate these times with the event_instances function from rrule. see rrule_functions.sql
27 WITH events AS (
28 -- TODO: replace 'Europe/Berlin' with event time zone
29 SELECT event_templates.*, tsrange(
30 ts::timestamp,
31 ts::timestamp + duration * INTERVAL '1 second'
32 ) AS span
33 FROM event_templates
34 INNER JOIN (
35 -- calculate event instances in local time and then convert back to utc
36 SELECT event_template_times.event_template_id, event_instances(event_template_times.start AT TIME ZONE 'Europe/Berlin', event_template_times.rrule)::timestamp AT TIME ZONE 'Europe/Berlin' AT TIME ZONE 'UTC' ts
37 FROM event_template_times
38 ) event_instances
39 ON event_instances.event_template_id = event_templates.id
40 )
41 -- then select all bookable events
42 SELECT events.*, LEAST(events.max_capacity, (
43 SELECT max_quantity
44 FROM quotas
45 INNER JOIN pos_quotas
46 ON pos_quotas.quota_id = quotas.id
47 AND pos_quotas.pos_id = pos_id
48 WHERE quotas.event_id = events.id
49 AND NOW()
50 -- if sales_start_offset is NULL then we go back 100_000 days of time
51 BETWEEN date_trunc('day', lower(events.span)) - COALESCE(sales_start_offset, 100000) * INTERVAL '1 day' + sales_start_time
52 -- if sales_end_offset is NULL then we go back 0 days of time
53 AND date_trunc('day', lower(events.span)) - COALESCE(sales_end_offset, 0) * INTERVAL '1 day' + sales_end_time
54 -- we assume that only one quota matches
55 LIMIT 1
56 )) AS max_quota
57 FROM (
58 SELECT events.*
59 -- TODO: we could rewrite this LEAST with a chain of IF's that shortcircuit
60 , LEAST(
61 (SELECT MIN(CASE WHEN
62
63
64 (EXISTS (SELECT 1
65 FROM materials
66 INNER JOIN material_capacities
67 ON material_capacities.resource_id = materials.id
68 AND material_capacities.configuration_id = material_requirements.configuration_id
69 -- the requirement specifies specific properties or ids a resource needs to fulfill
70 WHERE -- (materials.id = ANY (material_requirements.resource_ids)
71 -- OR
72 materials.property_ids @> material_requirements.property_ids
73 -- )
74 -- check all required time spans against availability and reservations
75 AND true = ALL (
76 -- capacity of resource needs to be infinity (null) or greater than the already reserved quantity
77 SELECT (material_capacities.capacity IS NULL
78 OR material_capacities.capacity - COALESCE(MAX(material_reservation_sums.quantity), 0) >= material_requirements.min_capacity)
79 -- check availability of resource
80 AND EXISTS (
81 SELECT 1
82 FROM material_availabilities
83 WHERE material_availabilities.resource_id = materials.id
84 AND material_availabilities.span @> material_requirement_spans.span
85 ) AS available
86 -- calculate the required time spans with event span and requirement offsets
87 FROM (
88 SELECT requirement_id, tsrange(lower(events.span) + start_offset * INTERVAL '1 second', upper(events.span) + end_offset * INTERVAL '1 second') AS span
89 FROM material_requirement_spans
90 ) material_requirement_spans
91 LEFT JOIN material_reservation_sums
92 ON material_reservation_sums.resource_id = materials.id
93 AND material_reservation_sums.span && material_requirement_spans.span
94 WHERE material_requirement_spans.requirement_id = material_requirements.id
95 -- group by span so that we check each required time span
96 GROUP BY material_requirement_spans.span
97 )
98 ))
99
100
101 THEN material_requirements.min_capacity ELSE 0 END)
102 -- count all resources that are available at the required times and still have capacity
103 -- a resource specifies when it is available, this needs to match the required time spans
104 -- a resource has a specific capacity for each configuration it can be used in
105 -- a resource may already have reservation blocking some or all of the capacity
106
107 FROM material_requirements
108 WHERE material_requirements.event_id = events.id
109 -- only look at required requirements. there are also optional ones
110 -- AND material_requirements.requirement = 'required'
111 GROUP BY material_requirements.event_id
112 )
113 , (SELECT MIN(CASE WHEN
114
115
116 (EXISTS (SELECT 1
117 FROM places
118 INNER JOIN place_capacities
119 ON place_capacities.resource_id = places.id
120 AND place_capacities.configuration_id = place_requirements.configuration_id
121 -- the requirement specifies specific properties or ids a resource needs to fulfill
122 WHERE -- (places.id = ANY (place_requirements.resource_ids)
123 -- OR
124 places.property_ids @> place_requirements.property_ids
125 -- )
126 -- check all required time spans against availability and reservations
127 AND true = ALL (
128 -- capacity of resource needs to be infinity (null) or greater than the already reserved quantity
129 SELECT (place_capacities.capacity IS NULL
130 OR place_capacities.capacity - COALESCE(MAX(place_reservation_sums.quantity), 0) >= place_requirements.min_capacity)
131 -- check availability of resource
132 AND EXISTS (
133 SELECT 1
134 FROM place_availabilities
135 WHERE place_availabilities.resource_id = places.id
136 AND place_availabilities.span @> place_requirement_spans.span
137 ) AS available
138 -- calculate the required time spans with event span and requirement offsets
139 FROM (
140 SELECT requirement_id, tsrange(lower(events.span) + start_offset * INTERVAL '1 second', upper(events.span) + end_offset * INTERVAL '1 second') AS span
141 FROM place_requirement_spans
142 ) place_requirement_spans
143 LEFT JOIN place_reservation_sums
144 ON place_reservation_sums.resource_id = places.id
145 AND place_reservation_sums.span && place_requirement_spans.span
146 WHERE place_requirement_spans.requirement_id = place_requirements.id
147 -- group by span so that we check each required time span
148 GROUP BY place_requirement_spans.span
149 )
150 ))
151
152
153 THEN place_requirements.min_capacity ELSE 0 END)
154 -- count all resources that are available at the required times and still have capacity
155 -- a resource specifies when it is available, this needs to match the required time spans
156 -- a resource has a specific capacity for each configuration it can be used in
157 -- a resource may already have reservation blocking some or all of the capacity
158
159 FROM place_requirements
160 WHERE place_requirements.event_id = events.id
161 -- only look at required requirements. there are also optional ones
162 -- AND place_requirements.requirement = 'required'
163 GROUP BY place_requirements.event_id
164 )
165 , (SELECT MIN(CASE WHEN
166
167
168 (EXISTS (SELECT 1
169 FROM agents
170 INNER JOIN agent_capacities
171 ON agent_capacities.resource_id = agents.id
172 AND agent_capacities.configuration_id = agent_requirements.configuration_id
173 -- the requirement specifies specific properties or ids a resource needs to fulfill
174 WHERE -- (agents.id = ANY (agent_requirements.resource_ids)
175 -- OR
176 agents.property_ids @> agent_requirements.property_ids
177 -- )
178 -- check all required time spans against availability and reservations
179 AND true = ALL (
180 -- capacity of resource needs to be infinity (null) or greater than the already reserved quantity
181 SELECT (agent_capacities.capacity IS NULL
182 OR agent_capacities.capacity - COALESCE(MAX(agent_reservation_sums.quantity), 0) >= agent_requirements.min_capacity)
183 -- check availability of resource
184 AND EXISTS (
185 SELECT 1
186 FROM agent_availabilities
187 WHERE agent_availabilities.resource_id = agents.id
188 AND agent_availabilities.span @> agent_requirement_spans.span
189 ) AS available
190 -- calculate the required time spans with event span and requirement offsets
191 FROM (
192 SELECT requirement_id, tsrange(lower(events.span) + start_offset * INTERVAL '1 second', upper(events.span) + end_offset * INTERVAL '1 second') AS span
193 FROM agent_requirement_spans
194 ) agent_requirement_spans
195 LEFT JOIN agent_reservation_sums
196 ON agent_reservation_sums.resource_id = agents.id
197 AND agent_reservation_sums.span && agent_requirement_spans.span
198 WHERE agent_requirement_spans.requirement_id = agent_requirements.id
199 -- group by span so that we check each required time span
200 GROUP BY agent_requirement_spans.span
201 )
202 ))
203
204
205 THEN agent_requirements.min_capacity ELSE 0 END)
206 -- count all resources that are available at the required times and still have capacity
207 -- a resource specifies when it is available, this needs to match the required time spans
208 -- a resource has a specific capacity for each configuration it can be used in
209 -- a resource may already have reservation blocking some or all of the capacity
210
211 FROM agent_requirements
212 WHERE agent_requirements.event_id = events.id
213 -- only look at required requirements. there are also optional ones
214 -- AND agent_requirements.requirement = 'required'
215 GROUP BY agent_requirements.event_id
216 )
217
218 ) AS max_capacity
219 FROM events
220 ) events
221 $$
222 LANGUAGE sql STABLE;