· 6 years ago · Aug 05, 2019, 05:44 PM
1DROP FUNCTION IF EXISTS campaigns.get_campaign_records_latest_results;
2DROP FUNCTION IF EXISTS campaigns.get_campaign_calls_report;
3DROP FUNCTION IF EXISTS plugins.get_campaign_business_results_report;
4ALTER TABLE "campaigns"."imports" ADD "type" integer NOT NULL DEFAULT 0;
5comment on column campaigns.imports.type is '0 - add, 1 - remove';
6ALTER TABLE "campaigns"."imports" ADD "removed" integer NOT NULL DEFAULT 0;
7
8-- Remove is_closed
9
10ALTER TABLE campaigns.campaign_records
11ADD COLUMN state int;
12
13UPDATE campaigns.campaign_records
14SET state = is_closed::int;
15
16ALTER TABLE campaigns.campaign_records
17DROP COLUMN is_closed CASCADE;
18
19ALTER TABLE campaigns.campaign_records
20ALTER COLUMN state SET NOT NULL;
21
22comment on column campaigns.campaign_records.state is '0 - active, 1 - inactive, 2 - disabled';
23
24-- Diagnostics
25
26CREATE OR REPLACE VIEW campaigns.calls_diagnostic_stats
27AS SELECT c.call_id,
28 i.campaign_id,
29 c.campaign_record_id,
30 cr.phone_number,
31 c.agent_id,
32 c.transferee_agent_id,
33 c.ring_result,
34 c.transfer_result,
35 r.business_result_id AS business_result,
36 c.end_cause,
37 c.gate_id,
38 c.bot_hangup_reason,
39 c.predicted_gender,
40 c.scenario_completed,
41 cr.state AS record_state,
42 c.bot_scenario_revision_id,
43 c.end_time - c.start_time AS call_duration,
44 c.answer_time - c.start_time AS record_answer_duration,
45 c.agent_answer_time - c.answer_time AS agent_answer_duration,
46 c.sent_call_context_time - c.agent_answer_time AS sent_call_context_duration,
47 c.sent_call_context_time - c.start_time AS call_init_duration,
48 COALESCE(c.transfer_time, c.end_time) - c.agent_answer_time AS call_agent_duration,
49 c.end_time - c.transfer_time AS call_transferee_duration
50 FROM campaigns.calls c
51 LEFT JOIN campaigns.calls_results r ON r.call_result_id = c.call_id
52 JOIN campaigns.campaign_records cr ON cr.campaign_record_id = c.campaign_record_id
53 JOIN campaigns.imports i ON i.import_id = cr.import_id;
54
55
56-- Permissions
57
58ALTER TABLE campaigns.calls_diagnostic_stats OWNER TO postgres;
59GRANT ALL ON TABLE campaigns.calls_diagnostic_stats TO postgres;
60
61-- Functions: change is_closed for state
62
63CREATE OR REPLACE FUNCTION campaigns.get_campaign_records_latest_results(campaignid bigint)
64 RETURNS TABLE(camp_record bigint, call_id bigint, ring_res integer, bot_reason text, business_id bigint, business_name text, state integer, call_state integer, scenario_completed boolean, transfer_result integer, end_time timestamp without time zone, reopen_count integer, form_result jsonb, transferee_agent_id bigint, start_time timestamp without time zone, transfer_time timestamp without time zone, agent_answer_time timestamp without time zone)
65 LANGUAGE sql
66 STABLE
67AS $function$
68 SELECT DISTINCT ON (c.campaign_record_id)
69 cr.campaign_record_id, c.call_id, c.ring_result, bot_hangup_reason, bres.business_result_id, bres."name", cr.state,
70 CASE WHEN c.agent_id IS NULL
71 AND c.transferee_agent_id IS NULL THEN 2
72 WHEN c.agent_id IS NOT NULL
73 AND c.transferee_agent_id IS NULL THEN 1
74 ELSE 0
75 END AS call_state, c.scenario_completed, c.transfer_result, c.end_time, cr.reopen_count,
76 cres.form_result, c.transferee_agent_id, c.start_time, c.transfer_time, c.agent_answer_time
77 FROM
78 campaigns.calls C
79 JOIN campaigns.campaign_records cr ON C.campaign_record_id = cr.campaign_record_id
80 LEFT JOIN campaigns.calls_results cres ON cres.call_result_id = c.call_id
81 JOIN campaigns.imports imp ON imp.import_id = cr.import_id
82 LEFT JOIN campaigns.business_results bres ON bres.business_result_id = cres.business_result_id
83 WHERE
84 campaign_id = campaignid
85 ORDER BY
86 c.campaign_record_id, c.start_time DESC;
87$function$
88;
89
90
91CREATE OR REPLACE FUNCTION campaigns.get_campaign_calls_report(campaignid bigint, call_result_type integer, businessresultid bigint, hangupreason text, ringresultid integer, isclosed boolean)
92 RETURNS TABLE(phone_number text, business_result text, hangup_reason text, ring_res integer, form_res text, user_name text, full_name text, start_time timestamp without time zone, queue_id bigint, bot_duration integer, agent_duration integer, records_count integer, update_date timestamp without time zone, state integer)
93 LANGUAGE sql
94 STABLE
95AS $function$
96 WITH
97latest_calls AS (
98 SELECT * FROM campaigns.get_campaign_records_latest_results(campaignid)
99),
100first_calls AS (
101 SELECT
102 distinct on (camp_record.campaign_record_id)
103 CALL.start_time, camp_record.campaign_record_id, camp_record.phone_number, camp_record.reopen_count, camp_record.state
104 FROM campaigns.campaign_records camp_record
105 LEFT JOIN campaigns.calls call ON call.campaign_record_id =camp_record.campaign_record_id
106 JOIN campaigns.imports import ON camp_record.import_id = IMPORT.import_id
107 WHERE IMPORT.campaign_id = campaignid
108 order by camp_record.campaign_record_id, COALESCE(call.start_time)
109),
110user_names AS (
111 SELECT user_id, string_agg(claim_value, ' ') full_name
112 FROM "identity".user_claims
113 WHERE claim_type ~ '.*/givenname|.*/surname'
114 GROUP BY user_id
115),
116user_logins AS (
117 SELECT user_id, username FROM "identity".users
118)
119SELECT
120 fc.phone_number,
121 lc.business_name AS business_results,
122 lc.bot_reason AS hangup_reason,
123 lc.ring_res AS ring_res,
124 lc.form_result::TEXT,
125 ul.username AS user_name,
126 ud.full_name,
127 fc.start_time AS start_time,
128 lc.transferee_agent_id AS queue_id,
129 CASE
130 WHEN lc.transfer_time IS NOT NULL THEN ROUND(extract(EPOCH FROM (lc.transfer_time - lc.agent_answer_time)))::INT
131 ELSE ROUND(extract(EPOCH FROM (lc.end_time - lc.agent_answer_time)))::INT END AS bot_duration,
132 CASE
133 WHEN transfer_time IS NOT NULL THEN ROUND(extract(EPOCH FROM (lc.end_time - lc.transfer_time)))::INT
134 ELSE NULL END AS agent_duration,
135 CASE
136 WHEN lc.state <> 0 THEN lc.reopen_count + 1
137 ELSE COALESCE(lc.reopen_count, 0) END AS records_count,
138 coalesce(lc.end_time, lc.start_time) AS update_date,
139 COALESCE(lc.state, 0) AS state
140FROM first_calls fc
141 LEFT JOIN latest_calls lc ON fc.campaign_record_id = lc.camp_record
142 LEFT JOIN user_names ud ON ud.user_id = lc.transferee_agent_id
143 LEFT JOIN user_logins ul ON ul.user_id = lc.transferee_agent_id
144WHERE
145 (isclosed IS NULL OR
146 (isclosed = false AND fc.state = 0)
147 OR (isclosed = TRUE AND fc.state <> 0)
148 )
149 AND (call_result_type = 2 OR call_state = call_result_type)
150 AND (businessresultid IS NULL OR business_id = businessresultid)
151 AND (hangupreason IS NULL OR bot_reason = hangupreason)
152 AND (ringresultid IS NULL OR ring_res = ringresultid)
153$function$
154;
155
156
157CREATE OR REPLACE FUNCTION plugins.get_campaign_business_results_report(campaignid bigint)
158 RETURNS TABLE(business_result_id bigint, current_count integer, limit_count integer, business_name text, business_position integer)
159 LANGUAGE sql
160 STABLE
161AS $function$
162 WITH limits AS (
163 SELECT
164 cbr.campaign_id,
165 cbr.business_result_id,
166 lim.limit_count
167 FROM
168 campaigns.campaigns_business_results cbr
169 LEFT JOIN plugins.campaigns_business_results_limits lim ON lim.campaign_id = cbr.campaign_id
170 AND lim.business_result_id = cbr.business_result_id
171 WHERE
172 cbr.campaign_id = campaignId
173),
174counts AS (
175 SELECT
176 business_id, count(*) AS count
177 FROM
178 campaigns.get_campaign_records_latest_results (campaignId)
179 WHERE
180 state <> 0
181 AND business_id IS NOT NULL
182 GROUP BY
183 business_Id
184)
185SELECT
186 br.business_result_id,
187 COALESCE(counts.count, 0)::integer,
188 limits.limit_count,
189 br."name",
190 br."position"
191FROM
192 campaigns.business_results br
193 LEFT JOIN limits ON limits.business_result_id = br.business_result_id
194 LEFT JOIN counts ON counts.business_id = br.business_result_id;
195$function$
196;
197
198The error was Exception while reading from stream
199
200System.Exception: An error occurred executing the following sql:
201-- Remove is_closed
202
203ALTER TABLE campaigns.campaign_records
204ADD COLUMN state int;
205
206UPDATE campaigns.campaign_records
207SET state = is_closed::int;
208
209ALTER TABLE campaigns.campaign_records
210DROP COLUMN is_closed CASCADE;
211
212ALTER TABLE campaigns.campaign_records
213ALTER COLUMN state SET NOT NULL;
214
215comment on column campaigns.campaign_records.state is '0 - active, 1 - inactive, 2 - disabled';
216
217-- Diagnostics
218
219CREATE OR REPLACE VIEW campaigns.calls_diagnostic_stats
220AS SELECT c.call_id,
221 i.campaign_id,
222 c.campaign_record_id,
223 cr.phone_number,
224 c.agent_id,
225 c.transferee_agent_id,
226 c.ring_result,
227 c.transfer_result,
228 r.business_result_id AS business_result,
229 c.end_cause,
230 c.gate_id,
231 c.bot_hangup_reason,
232 c.predicted_gender,
233 c.scenario_completed,
234 cr.state AS record_state,
235 c.bot_scenario_revision_id,
236 c.end_time - c.start_time AS call_duration,
237 c.answer_time - c.start_time AS record_answer_duration,
238 c.agent_answer_time - c.answer_time AS agent_answer_duration,
239 c.sent_call_context_time - c.agent_answer_time AS sent_call_context_duration,
240 c.sent_call_context_time - c.start_time AS call_init_duration,
241 COALESCE(c.transfer_time, c.end_time) - c.agent_answer_time AS call_agent_duration,
242 c.end_time - c.transfer_time AS call_transferee_duration
243 FROM campaigns.calls c
244 LEFT JOIN campaigns.calls_results r ON r.call_result_id = c.call_id
245 JOIN campaigns.campaign_records cr ON cr.campaign_record_id = c.campaign_record_id
246 JOIN campaigns.imports i ON i.import_id = cr.import_id;
247
248
249-- Permissions
250
251ALTER TABLE campaigns.calls_diagnostic_stats OWNER TO postgres;
252GRANT ALL ON TABLE campaigns.calls_diagnostic_stats TO postgres;
253
254-- Functions: change is_closed for state
255
256CREATE OR REPLACE FUNCTION campaigns.get_campaign_records_latest_results(campaignid bigint)
257 RETURNS TABLE(camp_record bigint, call_id bigint, ring_res integer, bot_reason text, business_id bigint, business_name text, state integer, call_state integer, scenario_completed boolean, transfer_result integer, end_time timestamp without time zone, reopen_count integer, form_result jsonb, transferee_agent_id bigint, start_time timestamp without time zone, transfer_time timestamp without time zone, agent_answer_time timestamp without time zone)
258 LANGUAGE sql
259 STABLE
260AS $function$
261 SELECT DISTINCT ON (c.campaign_record_id)
262 cr.campaign_record_id, c.call_id, c.ring_result, bot_hangup_reason, bres.business_result_id, bres."name", cr.state,
263 CASE WHEN c.agent_id IS NULL
264 AND c.transferee_agent_id IS NULL THEN 2
265 WHEN c.agent_id IS NOT NULL
266 AND c.transferee_agent_id IS NULL THEN 1
267 ELSE 0
268 END AS call_state, c.scenario_completed, c.transfer_result, c.end_time, cr.reopen_count,
269 cres.form_result, c.transferee_agent_id, c.start_time, c.transfer_time, c.agent_answer_time
270 FROM
271 campaigns.calls C
272 JOIN campaigns.campaign_records cr ON C.campaign_record_id = cr.campaign_record_id
273 LEFT JOIN campaigns.calls_results cres ON cres.call_result_id = c.call_id
274 JOIN campaigns.imports imp ON imp.import_id = cr.import_id
275 LEFT JOIN campaigns.business_results bres ON bres.business_result_id = cres.business_result_id
276 WHERE
277 campaign_id = campaignid
278 ORDER BY
279 c.campaign_record_id, c.start_time DESC;
280$function$
281;
282
283
284CREATE OR REPLACE FUNCTION campaigns.get_campaign_calls_report(campaignid bigint, call_result_type integer, businessresultid bigint, hangupreason text, ringresultid integer, isclosed boolean)
285 RETURNS TABLE(phone_number text, business_result text, hangup_reason text, ring_res integer, form_res text, user_name text, full_name text, start_time timestamp without time zone, queue_id bigint, bot_duration integer, agent_duration integer, records_count integer, update_date timestamp without time zone, state integer)
286 LANGUAGE sql
287 STABLE
288AS $function$
289 WITH
290latest_calls AS (
291 SELECT * FROM campaigns.get_campaign_records_latest_results(campaignid)
292),
293first_calls AS (
294 SELECT
295 distinct on (camp_record.campaign_record_id)
296 CALL.start_time, camp_record.campaign_record_id, camp_record.phone_number, camp_record.reopen_count, camp_record.state
297 FROM campaigns.campaign_records camp_record
298 LEFT JOIN campaigns.calls call ON call.campaign_record_id =camp_record.campaign_record_id
299 JOIN campaigns.imports import ON camp_record.import_id = IMPORT.import_id
300 WHERE IMPORT.campaign_id = campaignid
301 order by camp_record.campaign_record_id, COALESCE(call.start_time)
302),
303user_names AS (
304 SELECT user_id, string_agg(claim_value, ' ') full_name
305 FROM "identity".user_claims
306 WHERE claim_type ~ '.*/givenname|.*/surname'
307 GROUP BY user_id
308),
309user_logins AS (
310 SELECT user_id, username FROM "identity".users
311)
312SELECT
313 fc.phone_number,
314 lc.business_name AS business_results,
315 lc.bot_reason AS hangup_reason,
316 lc.ring_res AS ring_res,
317 lc.form_result::TEXT,
318 ul.username AS user_name,
319 ud.full_name,
320 fc.start_time AS start_time,
321 lc.transferee_agent_id AS queue_id,
322 CASE
323 WHEN lc.transfer_time IS NOT NULL THEN ROUND(extract(EPOCH FROM (lc.transfer_time - lc.agent_answer_time)))::INT
324 ELSE ROUND(extract(EPOCH FROM (lc.end_time - lc.agent_answer_time)))::INT END AS bot_duration,
325 CASE
326 WHEN transfer_time IS NOT NULL THEN ROUND(extract(EPOCH FROM (lc.end_time - lc.transfer_time)))::INT
327 ELSE NULL END AS agent_duration,
328 CASE
329 WHEN lc.state <> 0 THEN lc.reopen_count + 1
330 ELSE COALESCE(lc.reopen_count, 0) END AS records_count,
331 coalesce(lc.end_time, lc.start_time) AS update_date,
332 COALESCE(lc.state, 0) AS state
333FROM first_calls fc
334 LEFT JOIN latest_calls lc ON fc.campaign_record_id = lc.camp_record
335 LEFT JOIN user_names ud ON ud.user_id = lc.transferee_agent_id
336 LEFT JOIN user_logins ul ON ul.user_id = lc.transferee_agent_id
337WHERE
338 (isclosed IS NULL OR
339 (isclosed = false AND fc.state = 0)
340 OR (isclosed = TRUE AND fc.state <> 0)
341 )
342 AND (call_result_type = 2 OR call_state = call_result_type)
343 AND (businessresultid IS NULL OR business_id = businessresultid)
344 AND (hangupreason IS NULL OR bot_reason = hangupreason)
345 AND (ringresultid IS NULL OR ring_res = ringresultid)
346$function$
347;
348
349
350CREATE OR REPLACE FUNCTION plugins.get_campaign_business_results_report(campaignid bigint)
351 RETURNS TABLE(business_result_id bigint, current_count integer, limit_count integer, business_name text, business_position integer)
352 LANGUAGE sql
353 STABLE
354AS $function$
355 WITH limits AS (
356 SELECT
357 cbr.campaign_id,
358 cbr.business_result_id,
359 lim.limit_count
360 FROM
361 campaigns.campaigns_business_results cbr
362 LEFT JOIN plugins.campaigns_business_results_limits lim ON lim.campaign_id = cbr.campaign_id
363 AND lim.business_result_id = cbr.business_result_id
364 WHERE
365 cbr.campaign_id = campaignId
366),
367counts AS (
368 SELECT
369 business_id, count(*) AS count
370 FROM
371 campaigns.get_campaign_records_latest_results (campaignId)
372 WHERE
373 state <> 0
374 AND business_id IS NOT NULL
375 GROUP BY
376 business_Id
377)
378SELECT
379 br.business_result_id,
380 COALESCE(counts.count, 0)::integer,
381 limits.limit_count,
382 br."name",
383 br."position"
384FROM
385 campaigns.business_results br
386 LEFT JOIN limits ON limits.business_result_id = br.business_result_id
387 LEFT JOIN counts ON counts.business_id = br.business_result_id;
388$function$
389;