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