[Home] [Help]
56: /* calls in queue */
57: Cursor getCallsWaitingForClass is
58: Select count(distinct(C.MEDIA_ITEM_ID)) calls,
59: C.classification class
60: from CCT_MEDIA_ITEMS C
61: where C.status = 1
62: and media_type = 0
63: and C.creation_date between l_date_low and l_date_high
64: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
128:
129: /* calls in queue by time ranges for graph */
130: Select count(distinct(C.MEDIA_ITEM_ID))
131: into calls_range1
132: from CCT_MEDIA_ITEMS C
133: where C.status = 1
134: and C.media_type = 0
135: and C.creation_date between l_date_low and l_date_high
136: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
139:
140: /* calls in queue by time ranges for graph */
141: Select count(distinct(C.MEDIA_ITEM_ID))
142: into calls_range2
143: from CCT_MEDIA_ITEMS C
144: where C.status = 1
145: and C.media_type = 0
146: and C.creation_date between l_date_low and l_date_high
147: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
151:
152: /* calls in queue by time ranges for graph */
153: Select count(distinct(C.MEDIA_ITEM_ID))
154: into calls_range3
155: from CCT_MEDIA_ITEMS C
156: where C.status = 1
157: and media_type = 0
158: and C.creation_date between l_date_low and l_date_high
159: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
164:
165: /* calls in queue by time ranges for graph */
166: Select count(distinct(C.MEDIA_ITEM_ID))
167: into calls_range4
168: from CCT_MEDIA_ITEMS C
169: where C.status = 1
170: and media_type = 0
171: and C.creation_date between l_date_low and l_date_high
172: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
176:
177: /* calls in queue by time ranges for graph */
178: Select count(distinct(C.MEDIA_ITEM_ID))
179: into calls_range5
180: from CCT_MEDIA_ITEMS C
181: where C.status = 1
182: and media_type = 0
183: and C.creation_date between l_date_low and l_date_high
184: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
188:
189: /* calls in queue by time ranges for graph */
190: Select count(distinct(C.MEDIA_ITEM_ID))
191: into calls_range6
192: from CCT_MEDIA_ITEMS C
193: where C.status = 1
194: and media_type = 0
195: and C.creation_date between l_date_low and l_date_high
196: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
200:
201: /* calls in queue by time ranges for graph */
202: Select count(distinct(C.MEDIA_ITEM_ID))
203: into calls_range7
204: from CCT_MEDIA_ITEMS C
205: where C.status = 1
206: and media_type = 0
207: and C.creation_date between l_date_low and l_date_high
208: and (C.server_group_id= v_site_id or (v_site_id=-999 or v_site_id is null))
212: /* get talking agents for graph */
213: select count(distinct I1.resource_id) into talk_graph
214: from IEU_SH_SESSIONS I1,
215: IEU_SH_ACTIVITIES I2,
216: CCT_media_items M,
217: JTF_RS_GROUP_MEMBERS J4,
218: CCT_AGENT_RT_STATS C1
219: where I1.session_id = I2.session_id
220: and I1.application_id = 696
239: Select count(distinct I1.resource_id) into wrap_graph
240: from IEU_SH_SESSIONS I1,
241: IEU_SH_ACTIVITIES I2,
242: CCT_AGENT_RT_STATS C1,
243: CCT_media_items M,
244: JTF_RS_GROUP_MEMBERS J4
245: where I1.session_id = I2.session_id
246: and I1.application_id = 696
247: and I2.active_flag ='T'
266:
267:
268: /* calls in queue */
269: Select count(distinct(C.MEDIA_ITEM_ID)) into callsWaiting
270: from CCT_MEDIA_ITEMS C
271: where C.status = 1
272: and media_type = 0
273: and C.creation_date between l_date_low and l_date_high
274: and (C.server_group_id= v_site_id or v_site_id=-999)
273: and C.creation_date between l_date_low and l_date_high
274: and (C.server_group_id= v_site_id or v_site_id=-999)
275: and (C.classification = l_className or v_classification_id=-999);
276:
277: /* CCT_MEDIA_ITEMS.status
278: 0 Media item received, not yet routed ( active mode only )
279: 1 Media item routed, waiting in queue
280: 2 Email reserved by an agent ( email only )
281: 3 Media item dequeued and served by an agent
285:
286:
287: /* longest call in queue */
288: Select max(l_date_high- C.last_update_date)*24*3600 into longestCallWaiting
289: from CCT_MEDIA_ITEMS C
290: where C.status = 1
291: and media_type = 0
292: and C.creation_date between l_date_low and l_date_high
293: and (C.server_group_id= v_site_id or v_site_id=-999)
294: and (C.classification = l_className or v_classification_id=-999);
295:
296: /* total queue time */
297: select sum(l_date_high- C.last_update_date)*24*3600 into totalWaitingTime
298: from CCT_MEDIA_ITEMS C
299: where C.status = 1
300: and media_type = 0
301: and C.creation_date between l_date_low and l_date_high
302: and (C.server_group_id= v_site_id or v_site_id=-999)
728: Cursor getWrapAgentList is
729: Select
730: (l_date_high - C1.last_update_date)*24*3600 duration
731: from CCT_AGENT_RT_STATS C1,
732: /* CCT_MEDIA_ITEMS C2,*/
733: IEU_SH_SESSIONS I1,
734: IEU_SH_ACTIVITIES I2
735: where C1.has_call = 'F'
736: and C1.agent_id = v_resource_id
1113: /* J3.group_name groupName,*/
1114: I1.extension extension,
1115: C2.classification class
1116: from CCT_AGENT_RT_STATS C1,
1117: CCT_MEDIA_ITEMS C2,
1118: JTF_RS_RESOURCE_EXTNS_VL J,
1119: JTF_RS_GROUPS_VL J3,
1120: JTF_RS_GROUP_MEMBERS J4,
1121: IEU_SH_SESSIONS I1,