遠端桌面測試資料庫賬號：
gimopr/gimap12345@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.10.17)(PORT = 1531))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = t1eim)))
坐席組/坐席/機構碼
select a.id_gim_dic_item as dicItemId,
a.dic_type as dicType,
b.dic_name as dicName,
a.item_code as itemCode,
a.item_name as itemName,
a.item_order as itemOrder,
a.parent_id as parentId,
c.item_name as parentName,
a.created_by as createdBy,
a.date_created as createdDate,
a.updated_by as updatedBy,
a.date_updated as updatedDate
from gim_dic_item a, gim_dic_type b,gim_dic_item c
where a.dic_type = b.dic_type
and a.parent_id = c.item_code(+)
and a.dic_type = c.dic_type(+)
and a.dic_type = 'CKY_DEPT'
資料庫查詢機構碼
select * from gim_dic_item t where t.dic_type = 'CKY_DEPT'
1.客戶繫結資訊
select t.client_im_no 客戶openid,
t.im_nickname 暱稱,
t.mobile_no 手機號,
t.customer_no 客戶號,
t.client_name 姓名,
(case
when t.bind_status = '0' then
'已繫結'
else
'未繫結'
end) 繫結狀態,
t.date_bind 繫結時間,
t.date_unbind 解綁時間,
t.car_no 車牌號,
(case
when y.current_status = '1' then
'關注'
else
'取消關注'
end) 關注狀態,
t.pa_im_no 微應用號
from gim_client_contact t, gim_client_subscription y
where t.client_im_no = y.client_im_no
and t.pa_im_no = y.pa_im_no
and t.mobile_no = '13001296085';
select t.client_im_no 客戶openid,
t.im_nickname 暱稱,
t.mobile_no 手機號,
t.customer_no 客戶號,
t.client_name 姓名,
(case
when t.bind_status = '0' then
'已繫結'
else
'未繫結'
end) 繫結狀態,
t.date_bind 繫結時間,
t.date_unbind 解綁時間,
t.car_no 車牌號,
(case
when y.current_status = '1' then
'關注'
else
'取消關注'
end) 關注狀態,
t.pa_im_no 微應用號,
x.id_gim_task_info 會話ID,
x.date_created 會話開始時間,
to_char(x.date_completed, 'yyyy-MM-dd hh24:mi:ss') 會話結束時間,
x.task_status 會話狀態,
z.um_id 坐席UM,
z.name 坐席姓名
from gim_client_contact t,
gim_client_subscription y,
gim_task_info x,
gim_agent_info z
where t.client_im_no = y.client_im_no
and t.pa_im_no = y.pa_im_no
and t.id_gim_client_contact = x.id_gim_client_contact(+)
and x.id_gim_agent_info = z.id_gim_agent_info
and t.mobile_no = '15663055058';
繫結記錄：
select y.client_im_no 客戶openid,
t.client_name 客戶姓名,
t.party_no 客戶號,
(case
when t.bind_status = '0' then
'繫結'
else
'解綁'
end) 繫結狀態,
t.date_created 時間
from gim_bind_record t, gim_client_contact y
where y.id_gim_client_contact = t.id_gim_client_contact
and y.client_im_no='oH-vNswOe6oWxsgjaTQBxRyQFP5A';
2.坐席渠道許可權
select distinct a.um_id as umId,b.permission as permission
from gim_agent_info a,gim_agent_permission b where a.id_gim_agent_info = b.id_gim_agent_info
and a.um_id = 'TONGWEILIN890' order by b.permission
3.好貸派工坐席資訊查詢
SELECT b.*
from gim_agent_permission a,
gim_agent_info b,
gim_agent_group_relation c,
gim_busi_agrp_relation d,
gim_business_info e
where a.id_gim_agent_info = b.id_gim_agent_info
and b.id_gim_agent_info = c.id_gim_agent_info
and c.id_gim_agent_group = d.id_gim_agent_group
and d.id_gim_business_info = e.id_gim_business_info
and e.business_type in ('HD01')
and a.permission = '01'
and b.um_id='CAOXUE244'
and rownum=1
派工查詢：
select cc.mobile_no,
cc.client_im_no,
cc.im_nickname,
cc.client_name,
t.id_gim_task_info,
t.date_created,
t.task_status,
u.um_id,
u.name
from gim_client_contact cc, gim_task_info t, gim_agent_info u
where cc.client_im_no = 'oGFrljp_3F5Ogk1p1KiVR-BZvTws'
and cc.id_gim_client_contact = t.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
查詢會話
select /* index(cc MSG_INFO_IM_NO )*/*
from gim_msg_info cc
where 1 = 1
-- and cc.date_created >= to_date('2015-08-28', 'yyyy-mm-dd')
and cc.client_im_no='oH-vNsxk3EsSW5DQMEZXbntktfFQ'
and cc.id_gim_task_info = '1E597C349FF005D6E053A31F210ADB2A'
select t.id_gim_task_info,
t.date_created,
t.task_status
from gim_client_contact c, gim_task_info t
where c.client_im_no = 'oH-vNsxk3EsSW5DQMEZXbntktfFQ'
and c.id_gim_client_contact = t.id_gim_client_contact
坐席派工：
select cc.mobile_no,
cc.client_im_no,
cc.im_nickname,
cc.client_name,
cc.customer_no,
t.customer_no,
t.id_gim_task_info,
t.date_created,
to_char(t.date_completed,'yyyy-MM-dd hh24:mi:ss'),
t.task_status,
u.um_id,
u.name
from gim_client_contact cc, gim_task_info t, gim_agent_info u
where u.um_id='LIUSHAN765'
and cc.im_nickname in ('匿名363','匿名183','匿名91','匿名613')
and cc.id_gim_client_contact = t.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
and t.date_created>=to_date('2015-10-19','yyyy-MM-dd')
order by t.date_created
會話查詢：
select cc.mobile_no 手機號,
cc.client_im_no 客戶openid,
cc.im_nickname 暱稱,
cc.client_name 客戶姓名,
t.customer_no 客戶號,
t.id_gim_task_info 會話id,
t.date_created 會話開始時間,
to_char(t.date_completed, 'yyyy-MM-dd hh24:mi:ss') 會話結束時間,
t.task_status,
u.um_id 坐席um,
u.name 坐席姓名,
cc.pa_im_no,
cc.date_bind,
a.name
from gim_client_contact cc,
gim_task_info t,
gim_agent_info u,
GIM_AGENT_GROUP A, ---坐席組表
GIM_AGENT_GROUP_RELATION B ---坐席-坐席組關係表
where cc.id_gim_client_contact = t.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
AND t.ID_GIM_AGENT_INFO = b.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_GROUP = A.ID_GIM_AGENT_GROUP
and u.um_id='ZHOURUYUAN842'
--and cc.im_nickname in ('匿名363','匿名183','匿名91','匿名613')
--and cc.client_im_no = 'oLeTpjhvfel6TzwTLNJ9w38PL1iQ'
--and cc.mobile_no='13380059921'
--and cc.pa_im_no = 'gh_213aa2f7f260'
--and t.customer_no='LOAN-3441174929'
--and t.pa_im_no = 'gh_2ade6f03a430'
--and t.date_created >= to_date('2017-08-29', 'yyyy-MM-dd')
order by t.date_created desc
select distinct j.group_name 業務名稱,
t.ID_GIM_TASK_INFO 會話ID,
DECODE(t.PA_IM_TYPE,
'01',
'微信',
'02',
'線上客服',
'03',
'簡訊平臺',
'04',
'郵件平臺',
'06',
'天下通',
'07',
'個人微信',
'08',
'支付寶錢包',
'09',
'APP線上客服',
'10',
'IVR',
'11',
'音視訊') 渠道,
u.um_id 坐席um,
u.name 坐席姓名,
u.seat_no 坐席工號,
a.name 坐席組名稱,
cc.im_nickname 暱稱,
cc.client_name 客戶姓名,
cc.mobile_no 手機號,
t.date_created 會話開始時間,
to_char(t.date_completed, 'yyyy-MM-dd hh24:mi:ss') 會話結束時間,
k.SATISFY_TAG 客戶評價,
k.EVALUATE_CONTENT 評價內容
from gim_client_contact cc, ---客戶資訊表
gim_task_info t, ---會話資訊表
gim_agent_info u, ---坐席資訊表
GIM_AGENT_GROUP A, ---坐席組表
GIM_AGENT_GROUP_RELATION B, ---坐席-坐席組關係表
GIM_EVALUATE_INFO k, ---評價表
GIM_ADMIN_GROUP j ---行政組表
where t.id_gim_client_contact = cc.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
AND t.ID_GIM_AGENT_INFO = b.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_GROUP = A.ID_GIM_AGENT_GROUP
and A.id_gim_admin_group = j.id_gim_admin_group
and t.id_gim_task_info = k.id_gim_task_info(+)
-- and u.um_id = 'SHENDONG139' --- 坐席um
-- and cc.Client_Name like '%張三%' --客戶姓名
and cc.im_nickname like '%匿名1103%' --客戶暱稱
and cc.mobile_no = '13380059921' ---手機號
and a.name like '%信用卡%' ---坐席組
and t.pa_im_type='01'---渠道
--and k.satisfy_tag='5'---會話評價
and t.date_created >= to_date('2016-10-14', 'yyyy-MM-dd') ---會話時間
--and t.date_created < to_date('2016-10-29', 'yyyy-MM-dd') ---會話時間
order by t.date_created desc
客戶關注相關：
select *
from gim_client_subscription_log t, gim_client_subscription y
where t.id_gim_client_subscription = y.id_gim_client_subscription
and y.client_im_no = 'oLeTpjqUeAej4RSdDwzpUZjfO_L0'
select count(0)
from gim_client_subscription t
where --t.pa_im_no = 'gh_1cd55f80c8fe' ---普惠
t.pa_im_no = 'gh_213aa2f7f260' ---新渠道產險
-- and t.pa_im_type='01'
and t.date_last_subscription >= trunc(sysdate - 280 / 24 / 60) ----十分鐘,關注
-- and t.date_last_cancel >= trunc(sysdate - 280 / 24 / 60) ----十分鐘,取消關注
根據坐席坐席組查詢會話
select cc.mobile_no,
cc.client_im_no,
cc.im_nickname,
cc.client_name,
cc.customer_no,
t.customer_no,
t.id_gim_task_info,
t.date_created,
to_char(t.date_completed,'yyyy-MM-dd hh24:mi:ss'),
t.task_status,
u.um_id,
u.name
from gim_client_contact cc, gim_task_info t, gim_agent_info u ,gim_agent_group_relation a,GIM_AGENT_GROUP y
where
cc.id_gim_client_contact = t.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
and u.id_gim_agent_info = a.id_gim_agent_info
and a.id_gim_agent_group=y.id_gim_agent_group
and y.id_gim_agent_group in ('E981218FC50B323AE043A01F210A870A','E981218FC50C323AE043A01F210A870A')
and t.date_created>=to_date('2015-11-18','yyyy-MM-dd')
order by t.date_created
根據指令查詢話術
SELECT P.PARAMETER_NAME paramerterName
FROM GIM_PARAMETER P
WHERE P.PARAMETER_TYPE ='221935_2' ---systemId _businessType 引數1_2
and P.PARAMETER_CODE='2_5' ---businessType_order 引數2_3
and P.SYSTEM_ID = '10000' ---系統id
and rownum=1
坐席資訊查詢：
SELECT distinct G.GROUP_NAME groupName,
Y.ID_GIM_AGENT_INFO id,
Y.UM_ID umId,
Y.NAME agentName,
A.NAME seatName,
Y.NICKNAME nickname,
Y.IP ip,
c.permission permission, ---許可權 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR
Y.VERSION version,
Y.CURRENT_STATE currentState,
Y.ONLINE_STATUS onlineStatus,
Y.EXTENSION_NO extensionNo,
Y.MAX_PROCESS_NUMBER maxProcessNumber,
Y.CURRENT_PROCESS_NUMBER currentProcessNumber,
Y.Max_Sendcount maxSendCount,
Y.max_sendemail_count maxSendEmailCount,
Y.DEPARTMENT_CODE departmentCode,
Y.DEPARTMENT_NAME departmentName
FROM GIM_ADMIN_GROUP G, ---行政組表（坐席組的上級）
GIM_AGENT_ADMIN_RELATE Z, ---坐席與行政組關聯表
GIM_AGENT_INFO Y, ---坐席資訊表
GIM_AGENT_GROUP A, ---坐席組表
GIM_AGENT_GROUP_RELATION B, ---坐席-坐席組關係表
GIM_USER_ROLE_RELATE ur,--- 使用者角色關聯表
GIM_ROLE_DATA_RELATE dr,---角色與資料關係表
gim_agent_permission c ---坐席許可權表
WHERE G.ID_GIM_ADMIN_GROUP = Z.ID_GIM_ADMIN_GROUP
AND Z.ID_GIM_AGENT_INFO = Y.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_INFO = Y.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_GROUP = A.ID_GIM_AGENT_GROUP
AND ur.ID_GIM_ROLE = dr.ID_GIM_ROLE
and Y.ID_GIM_AGENT_INFO = c.id_gim_agent_info
and dr.DATA_TYPE = '00'
and dr.DATA_ID = A.ID_GIM_AGENT_GROUP
AND Y.IS_ACTIVE = 'Y'
and y.um_id = 'XUCAN464';
坐席資料採集：
SELECT A.NAME seatName,
(sum(case when Y.ONLINE_STATUS = 0 then 1 else 0 end)) 離線人數,
(sum(case when Y.ONLINE_STATUS = 1 then 1 else 0 end)) 線上人數,
(sum(case when Y.ONLINE_STATUS = 2 then 1 else 0 end)) 示忙人數
FROM GIM_AGENT_INFO Y, ---坐席資訊表
GIM_AGENT_GROUP A, ---坐席組表
GIM_AGENT_GROUP_RELATION B ---坐席-坐席組關係表
WHERE Y.ID_GIM_AGENT_INFO = B.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_GROUP = A.ID_GIM_AGENT_GROUP
AND Y.IS_ACTIVE = 'Y'
group by A.NAME
坐席狀態查詢
select t.um_id 坐席Um,
(case
when t.state = '0' then
'離線'
when t.state = '1' then
'線上'
when t.state = '2' then
'示忙'
else
t.state
end) 坐席狀態,
t.date_created 狀態開始時間,
t.date_updated 狀態結束時間
from gim_agent_state t
where t.um_id = 'ZHANGQIAN213'
and t.date_created >= date '2016-06-14'
and t.date_created<date '2016-06-15';
已刪坐席查詢
select distinct y.id_gim_agent_info id,
y.um_id umId,
y.name agentName,
y.nickname nickname,
y.ip ip,
y.current_state currentState,
y.extension_no extensionNo,
y.max_process_number maxProcessNumber,
y.current_process_number currentProcessNumber,
g.name
from gim_agent_group g, gim_agent_admin_relate z, gim_agent_info y
where g.id_gim_admin_group = z.id_gim_admin_group
and z.id_gim_agent_info = y.id_gim_agent_info
--and g.id_gim_agent_group = #agentGroupId#
and y.um_id='WANGFEI982'
and y.id_gim_agent_info not in
(select c.id_gim_agent_info
from gim_agent_group_relation c)
and y.is_active = 'Y'
SELECT a.mobile_no 手機號,
a.client_name 客戶姓名,
a.im_nickname 客戶暱稱,
(case
when t.action_type = '1' then
'關注'
else
'取消關注'
end) 狀態,
t.date_created 時間
from GIM_CLIENT_SUBSCRIPTION_LOG t,
GIM_CLIENT_SUBSCRIPTION y,
gim_client_contact a
where t.id_gim_client_subscription = y.id_gim_client_subscription
and y.client_im_no = a.client_im_no
and y.client_im_no = 'oLeTpjl5nClMudWZ0ZvVyFb0IduY'
信用卡解綁：
select count(0)
from gim_client_contact t
where t.pa_im_no = 'gh_5505cf46c5ec'
and t.bind_status = '1'
and t.date_unbind is not null
and t.date_unbind >= to_date('2016-01-25', 'yyyy-MM-dd')
and t.date_unbind < to_date('2016-02-01', 'yyyy-MM-dd')
平安壽險線上客服會話記錄採集
SELECT distinct A.ID_GIM_TASK_INFO,
DECODE(B.PA_IM_TYPE,
'01',
'微信',
'02',
'線上客服',
'03',
'簡訊平臺',
'04',
'郵件平臺',
'06',
'天下通',
'07',
'個人微信',
'08',
'支付寶錢包',
'09',
'APP線上客服') as PARAMETER_CODE,
F.NAME as GROUPNAME,
D.UM_ID,
D.NAME as AGENTNAME,
NVL(B.CLIENT_NAME,B.IM_NICKNAME) as CLIENTNAME,
NVL(B.MOBILE_NO,'') as MOBILE_NO,
to_char(A.DATE_CREATED, 'yyyy-MM-dd hh24:mi:ss') as DATE_CREATED,
(case
when A.date_completed is null then
''
else
to_char(A.date_completed, 'yyyy-MM-dd hh24:mi:ss')
end) as DATE_UPDATED,
C.SATISFY_TAG as SATISFY_TAG,
C.EVALUATE_CONTENT,
B.CLIENT_IM_NO
FROM GIM_TASK_INFO A,
GIM_CLIENT_CONTACT B,
GIM_EVALUATE_INFO C,
GIM_AGENT_INFO D,
GIM_BUSINESS_INFO G,
gim_agent_group_relation E,
GIM_AGENT_GROUP F
WHERE a.id_gim_client_contact = b.id_gim_client_contact
and a.id_gim_task_info = c.id_gim_task_info(+)
and a.id_gim_agent_info = d.id_gim_agent_info
and a.business_type = g.business_type
and e.id_gim_agent_info = d.id_gim_agent_info
and e.id_gim_agent_group = f.id_gim_agent_group
and A.TASK_STATUS in ('03', '06')
and F.ID_GIM_ADMIN_GROUP ='100241'
and F.ID_GIM_AGENT_GROUP ='F078B93F814630B4E043A01F210A4588'
and B.PA_IM_TYPE ='09'
and A.DATE_CREATED between to_date('2015-12-02'||' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and to_date('2015-12-02'||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by to_char(A.DATE_CREATED, 'yyyy-MM-dd hh24:mi:ss')
信用卡小i
select count(0) from gim_task_info t where t.id_gim_agent_info='D870A80DFF159045E040210A541E3F96' and t.agent_type='02' and t.date_created >= to_date('2015-08-01', 'yyyy-MM-dd')
and t.date_created < to_date('2016-01-18', 'yyyy-MM-dd');
select * from gim_agent_robot t where t.id_gim_agent_robot='D74155815C3445C5E0401F0A0F0A292A';
select * from gim_portal_info;
select count(0) from gim_portal_info t where t.business_type='3' and t.created_date >= to_date('2015-08-01', 'yyyy-MM-dd')
and t.created_date < to_date('2016-01-18', 'yyyy-MM-dd');
銀行APP首次響應時間
select date_created 日期,
um_id 坐席um,
id_gim_task_info 任務ID,
(first_response_time - created_date) * 24 * 60 * 60 as 首次響應時間
from (select a.id_gim_task_info,
a.date_created created_date,
c.um_id,
to_char(a.date_created, 'yyyy-mm-dd') as date_created,
min(b.date_created) first_response_time
from gim_task_info a,
gim_msg_info b,
gim_agent_info c,
gim_agent_group_relation d
where a.id_gim_task_info = b.id_gim_task_info
and a.id_gim_agent_info = c.id_gim_agent_info
and c.id_gim_agent_info = d.id_gim_agent_info
and a.channel_type = '09'
and a.date_created between
to_date('2016-02-02' || ' 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and
to_date('2016-02-02' || ' 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
--and a.id_gim_agent_info = 'F57FB52911CF34E7E043A01F210AD4F3'
--and d.id_gim_agent_group = v_agent_group_id
and c.um_id = 'LUOLISI526'
and b.from_type = 'seat'
group by a.id_gim_task_info,
a.date_created,
c.um_id,
to_char(a.date_created, 'yyyy-mm-dd'))
待傳送簡訊統計：
select to_char(s.date_created, 'yyyymmdd'), count(*)
from gim_sms_info s
where s.base_id is null
and is_send = '0'
and date_created > to_date('20160308', 'yyyymmdd')
group by to_char(s.date_created, 'yyyymmdd')
信用卡交易提醒：
select t.char_col1 日期,
t.number_col1 天下通交易提醒次數,
t.number_col4 微信交易提醒次數
from gim_reports_info t
where t.report_id = '00000032' and t.char_col1>='2016-04-04'
線上客服會話接通量大於接入量資料：
select t.id_gim_task_info,
t.client_im_no,
t.socket_id,
y.id_portal_info,
y.id_gim_task_info,
y.sid
from gim_task_info t, gim_portal_info y
where t.id_gim_task_info = y.id_gim_task_info(+)
and t.business_type = 'SX_1'
and t.pa_im_type = '02'
and t.date_created >= to_date('2016-03-11', 'yyyy-MM-dd')
select to_char(t.date_created, 'yyyy-mm-dd') 日期,
(sum(case
when y.id_portal_info is not null then
1
else
0
end)) 接入量,
(sum(case
when t.id_gim_task_info is not null then
1
else
0
end)) 接通量
from gim_task_info t, gim_portal_info y
where t.id_gim_task_info = y.id_gim_task_info(+)
--and t.business_type = 'SX_1'
and t.pa_im_type = '02'
and t.agent_type='01'
and t.date_created >= to_date('2016-03-01', 'yyyy-MM-dd')
and t.date_created < to_date('2016-03-11', 'yyyy-MM-dd')
group by to_char(t.date_created, 'yyyy-mm-dd')
直通車險新關注粉絲：
select count(0)
from GIM_CLIENT_SUBSCRIPTION t
where t.pa_im_no = 'gh_213aa2f7f260'
and t.current_status = '1'
and t.province not in ('廣東', '上海')
and t.date_last_subscription >= to_date('2016-02-02', 'yyyy-MM-dd')
and t.date_last_subscription < to_date('2016-03-16', 'yyyy-MM-dd');
客戶聊天記錄查詢
SELECT
b.um_id 坐席um,
b.name 坐席姓名,
f.id_gim_task_info 會話ID,
to_char(f.date_created, 'yyyy-MM-dd hh24:mi:ss') 會話開始時間,
to_char(f.date_completed, 'yyyy-MM-dd hh24:mi:ss') 會話結束時間,
f.client_im_no 客戶openid,
to_char(g.date_created, 'yyyy-MM-dd hh24:mi:ss') 訊息傳送時間,
g.from_type 傳送方,
g.msg_context 聊天內容
from
gim_agent_info b, ---坐席資訊表
gim_task_info f, ---會話資訊表
gim_msg_info g ---聊天資訊表
where b.id_gim_agent_info = f.id_gim_agent_info
and f.id_gim_task_info = g.id_gim_task_info
and f.client_im_no='oLeTpjqzZRtSPWED24D1D_ooC8Z4'
人壽app機器人：
select count(0)
from gim_task_info t
where t.business_type in ('SX_1', 'SX_2')
and t.agent_type = '02'
and t.pa_im_type='09'
and t.date_created >= to_date('2016-03-18', 'yyyy-MM-dd')
and t.date_created< to_date('2016-03-22', 'yyyy-MM-dd')
select t.client_im_no 客戶openid,
(case
when y.pa_im_type = '01' then
'微信'
when y.pa_im_type = '02' then
'線上客服'
when y.pa_im_type = '03' then
'簡訊'
when y.pa_im_type = '04' then
'郵件'
when y.pa_im_type = '06' then
'天下通'
when y.pa_im_type = '07' then
'個人微信'
when y.pa_im_type = '08' then
'支付寶'
when y.pa_im_type = '09' then
'APP'
when y.pa_im_type = '10' then
'IVR'
when y.pa_im_type = '11' then
'視屏語音'
else
y.pa_im_type
end) 渠道,
t.from_type 傳送方,
t.msg_type 訊息型別,
t.msg_context 訊息內容,
t.business_type
from gim_msg_info t, gim_task_info y
where t.id_gim_task_info = y.id_gim_task_info
--and t.from_type = 'client'
--and t.msg_type in ('text/plain', 'text')
--and t.msg_type in ( 'text')
and y.business_type in ('XTAPP_BIZ_01', 'XTAPP_BIZ_02') ---信託
--and y.business_type in('SX_1','SX_1') ---壽險
--and y.business_type in('XYD_02','9') ---普惠
and t.date_created >= to_date('2016-01-01', 'yyyy-mm-dd')
繫結粉絲採集：
select t.client_im_no 客戶openid,
t.date_created 關注時間,
(case when t.bind_status = '0' then '已繫結' else '未繫結' end) 繫結狀態,
t.date_bind 繫結時間
from gim_client_contact t
where t.pa_im_no = 'gh_7a206b36308e'
and t.date_created >= sysdate - 1;
首先投訴組諮詢總量：
select *
from gim_portal_info a, gim_business_info b, gim_busi_agrp_relation c
where a.business_type = b.business_type
and b.id_gim_business_info = c.id_gim_business_info
and c.id_gim_agent_group = '1DBE0435BAD003EEE053A31F210A35E2'
and a.pa_im_type = '09'
and a.created_date between
to_date('2016-03-19 09:30:00', 'yyyy-MM-dd hh24:mi:ss') and
to_date('2016-03-19 12:20:00', 'yyyy-MM-dd hh24:mi:ss');
行用卡推送資料：
select to_char(t.createddate,'yyyy-mm-dd') 時間,count(0) 總數,
(sum(case when t.pushflag = '0' then 1 else 0 end)) 成功數,
(sum(case when t.pushflag!= '0' then 1 else 0 end)) 失敗
from gim_pns_push_sent_msg_info t
where --t.templateno = 'MP'and
t.createddate >= to_date('2016-04-01', 'yyyy-mm-dd')and
t.weappno='PAXYK95511_01' group by to_char(t.createddate,'yyyy-mm-dd')
select count(0)
from gim_pns_push_sent_msg_info t
where --t.templateno = 'MP'and
t.createddate >= to_date('2016-04-01', 'yyyy-mm-dd')
and t.pushflag='3'
and t.weappno='PAXYK95511_06'
--and t.weappno='PAXYK95511_01'
--and t.weappno='PINGAN_BANK_01'
--and t.failreason='傳送天下通訊息失敗:驗證失敗'
天下通選單點選訊息查詢：
select * from gim_msg_info t where t.client_im_no='ED2A433F9D1F46EA29D551369506BA9467B6B5BBA9ED08F2' and t.date_created>= to_date('2016-03-09', 'yyyy-MM-dd');
接入、接通量
select t.pa_im_type 渠道, --- 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR 11-音視訊
count(0) 接通數量
from gim_task_info t
where t.business_type = '7'
--and t.pa_im_type = '02'
and t.agent_type = '01'
and t.date_created >=
to_date('2016-04-07 00:00;00', 'yyyy-MM-dd hh24:mi:ss')
and t.date_created <
to_date('2016-04-08 00:00;00', 'yyyy-MM-dd hh24:mi:ss')
group by t.pa_im_type;
select t.pa_im_type 渠道, --- 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR 11-音視訊
count(0) 接入數量
from gim_portal_info t
where t.business_type = '7'
--and t.pa_im_type = '02'
--and t.agent_type = '01'
and t.created_date >=
to_date('2016-04-07 00:00;00', 'yyyy-MM-dd hh24:mi:ss')
and t.created_date <
to_date('2016-04-08 00:00;00', 'yyyy-MM-dd hh24:mi:ss')
group by t.pa_im_type;
微信推送：
select * from gim_pns_push_sending_msg t where t.clientimno='oiBF4jkEV-b1l0rFxbjWDdGM4lCU' and t.createddate>= to_date('2016-04-15', 'yyyy-MM-dd');
select * from gim_pns_push_sent_msg_info t where t.clientimno='oiBF4jkEV-b1l0rFxbjWDdGM4lCU' and t.createddate>= to_date('2016-04-15', 'yyyy-MM-dd');
select *
from gim_template_push_info a
where a.client_im_no = 'oaJbgjllIT3p9jJSaOXiGB2q2_Bk'
and a.template_no = 'MPS001'
and a.date_sent >= to_date('2016-05-09', 'yyyy-MM-dd')
and a.date_sent < to_date('2016-06-01', 'yyyy-MM-dd');
select *
from gim_template_push_info a
where a.client_im_no = 'oiBF4jqEqvQ_QpQa4kEgru4ZFNq0'
and a.template_no = 'PABMSG02'
and a.date_sent >= to_date('2016-09-23', 'yyyy-MM-dd')
--and a.date_sent < to_date('2016-06-01', 'yyyy-MM-dd');
select t.msgid,
t.clientimno,
t.msg,
t.templateno,
t.createddate,
t.pushflag
from gim_pns_push_sending_msg t
where t.msgid in ('f4f2a232e02f4aa8ae8fcc7963207c3c',
'bc17eba64ef34c7ead4d9fd509774de5');
待辦任務查詢
select *
from gim_task_info a
where -- a.task_status in('02','07')
a.client_im_no = '9000014449678' ----楊
--and a.client_im_no = '9000001314617' ----姜
and a.pa_im_no = 'PAXYK_09'
/* <dynamic>
<isNotEmpty prepend="and" property="business_type">
a.business_type=#business_type#
</isNotEmpty>
</dynamic>*/
and a.pa_im_type = '09'
業務型別查詢系列
select j.group_name 業務系列,
e.name 業務名稱,
i.name 坐席組名稱,
e.business_type,
j.id_gim_admin_group
from gim_busi_agrp_relation d, ---業務-業務組關係表
gim_business_info e, ---業務定義表
GIM_AGENT_GROUP i, ---坐席組表
GIM_ADMIN_GROUP j ---行政組表
where e.id_gim_business_info = d.id_gim_business_info
and d.id_gim_agent_group = i.id_gim_agent_group
and i.id_gim_admin_group = j.id_gim_admin_group
--and e.business_type in ('BANK_1')
and j.id_gim_admin_group='100161'
線上客服資料
select n.created_date 時間,
n.name 業務型別,
n.count1 進線總數,
n.count2 sid為空數,
n.count3 sid為空且未派工,
round((n.count3 / n.count1) * 100, 2) || '%' 為空率
from (select to_char(t.created_date, 'yyyy-mm') created_date,
y.name name,
(sum(case
when t.id_portal_info is not null then
1
else
0
end)) count1,
(sum(case
when t.sid is null then
1
else
0
end)) count2,
(sum(case
when t.sid is null and t.id_gim_task_info is null then
1
else
0
end)) count3
from gim_portal_info t, gim_business_info y
where t.business_type = y.business_type
and t.pa_im_type = '02'
and t.created_date >= to_date('2016-01', 'yyyy-MM')
and t.created_date < to_date('2016-06', 'yyyy-MM')
group by to_char(t.created_date, 'yyyy-mm'), y.name) n;
分時段統計：
select to_char((trunc(sysdate) +
trunc((t.created_date - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
'hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((t.created_date - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
'hh24:mi') period,
count(0)
from gim_portal_info t
where trunc(t.created_date) = to_date('20160601', 'yyyymmdd') and t.pa_im_type='02'
group by to_char((trunc(sysdate) +
trunc((t.created_date - trunc(sysdate)) * 24 * 60 / 30) * 30 / 60 / 24),
'hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((t.created_date - trunc(sysdate)) * 24 * 60 / 30 + 1) * 30 / 60 / 24),
'hh24:mi');
坐席報表許可權資訊
select b.name, c.description, d.umid
from gim_role_resources_relate a,
GIM_ROLE b,
GIM_RESOURCES_INFO c,
gim_user_role_relate d
where c.resource_id = a.id_gim_resources
and a.id_gim_role = b.id_gim_role
and b.id_gim_role = d.id_gim_role
and c.resource_id in ('0020202',
'0020205',
'0020102',
'0020106',
'0020112',
'0020409',
'0020705');
轉人工率
select y.date_created 時間,
(case
when pa_im_type = '01' then
'微信'
when pa_im_type = '02' then
'線上客服'
when pa_im_type = '03' then
'簡訊'
when pa_im_type = '04' then
'郵件'
when pa_im_type = '06' then
'天下通'
when pa_im_type = '07' then
'個人微信'
when pa_im_type = '08' then
'支付寶'
when pa_im_type = '09' then
'APP'
when pa_im_type = '10' then
'IVR'
when pa_im_type = '11' then
'視屏語音'
else
pa_im_type
end) 渠道,
jiqiCount 機器人總數,
rengongCount 人工總數,
round((rengongCount / jiqiCount) * 100, 2) || '%' 轉人工率
from (select to_char(t.date_created, 'yyyy-MM-dd') date_created,
t.pa_im_type pa_im_type, --- 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR 11-音視訊
(sum(case
when t.agent_type = '01' then
1
else
0
end)) rengongCount,
(sum(case
when t.agent_type = '02' then
1
else
0
end)) jiqiCount
from gim_task_info t
where t.business_type = '3'
and t.date_created >= to_date('2016-07-01', 'yyyy-MM-dd')
and t.date_created < to_date('2016-08-09', 'yyyy-MM-dd')
group by to_char(t.date_created, 'yyyy-MM-dd'), t.pa_im_type) y;
模板：
select t.template_no 模板編號,
t.description 模板名稱,
t.template_prio 模板級別,
t.load_period_begin 取數開始時間,
t.load_period_end 取數截止時間,
t.send_period_begin 傳送開始時間,
t.send_period_end 傳送截止時間
from gim_noti_template_info t
where t.load_period_begin>'00:59:58' and t.template_prio!=1 t.template_no in ('WX_PAZQ_CJ', 'TXTXYKMPS009', 'TXTXYKBCMP003')
SELECT
A.DICTATE_NAME,
A.DICTATE_CODE,
A.PA_IM_NO,
A.TYPE,
CASE
WHEN A.TYPE = '0' THEN
'使用者指令'
ELSE
'系統指令'
end typeName
FROM GIM_DICTATE A, GIM_DICTATE_RECORD B
WHERE A.ID_GIM_DICTATE = B.ID_GIM_DICTATE
AND B.CLIENT_IM_TYPE in ('01', '06')
AND B.DATE_CREATED > trunc(sysdate) - 20
and B.DATE_CREATED < trunc(sysdate)-19
and a.pa_im_no='gh_5505cf46c5ec'
GROUP BY A.DICTATE_CODE, A.DICTATE_NAME, A.TYPE, A.PA_IM_NO
查詢聊天記錄：
select t.id_gim_task_info,
t.from_type,
t.to_type,
t.msg_context,
t.date_created
from gim_msg_info t
where t.client_im_no = ''
and t.pa_im_type
and t.pa_im_no = ''
order by t.date_created desc
查詢未關閉的會話：
select A.id_gim_task_info
from (select t.id_gim_task_info id_gim_task_info,
max(msg.date_updated) date_updated
from gim_msg_info msg, gim_task_info t
where t.id_gim_task_info = msg.id_gim_task_info
and t.task_status = '02'
and t.business_type = 'HD01'
and t.date_created <= date '2016-10-20'
group by t.id_gim_task_info) A
where to_char(A.date_updated, 'yyyymmdd') <= '20161020'
關閉會話：
update gim_task_info tk
set tk.task_status = '03',
tk.date_updated = sysdate,
tk.updated_by = 'AutoAgentCannelJob',
tk.date_completed = systimestamp
where tk.id_gim_task_info in
(select A.id_gim_task_info
from (select t.id_gim_task_info id_gim_task_info,
max(msg.date_updated) date_updated
from gim_msg_info msg, gim_task_info t
where t.id_gim_task_info = msg.id_gim_task_info
and t.task_status = '02'
and t.business_type = 'HD01'
and t.date_created <= date '2016-10-20'
group by t.id_gim_task_info) A
where to_char(A.date_updated, 'yyyymmdd') <= '20161020')
appim:
select * from gim_task_info t where t.date_created>=date'2017-03-13' and t.pa_im_type='09' and t.client_cookies is null
MSG_TYPE
text/plain
SCAN
template
image/jpeg
application/sdp
text
news
location
system
video
event
image
001
voice
官網進線量：
select
to_char(t.date_created, 'yyyy-MM-dd') 會話結束時間,
count(0)
from gim_client_contact cc,
gim_task_info t,
gim_agent_info u,
GIM_AGENT_GROUP A, ---坐席組表
GIM_AGENT_GROUP_RELATION B ---坐席-坐席組關係表
where
cc.id_gim_client_contact = t.id_gim_client_contact
and t.id_gim_agent_info = u.id_gim_agent_info
AND t.ID_GIM_AGENT_INFO = b.ID_GIM_AGENT_INFO
AND B.ID_GIM_AGENT_GROUP = A.ID_GIM_AGENT_GROUP
and t.business_type = 'PA_GW'
and t.pa_im_type = '02'
and t.date_created >= to_date('2017-03-23', 'yyyy-MM-dd')
group by to_char(t.date_created, 'yyyy-MM-dd')
select to_char(t.created_date, 'yyyy-mm-dd') 渠道, --- 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR 11-音視訊
count(0) 接入數量
from gim_portal_info t
where t.business_type = 'PA_GW'
--and t.pa_im_type = '02'
--and t.agent_type = '01'
and t.created_date >=
to_date('2017-03-23', 'yyyy-MM-dd')
and t.created_date <
to_date('2017-03-28', 'yyyy-MM-dd')
group by to_char(t.created_date, 'yyyy-mm-dd');
select t.pa_im_type 渠道, --- 01-微信 02-線上客服 03-簡訊 04-郵件 06-天下通 07-個人微信 08-支付寶 09-APP 10-IVR 11-音視訊
count(0) 接入數量
from gim_portal_info t
where t.business_type = 'PA_GW'
--and t.pa_im_type = '02'
--and t.agent_type = '01'
and t.created_date >=
to_date('2017-03-22 08:00:00', 'yyyy-MM-dd hh24:mi:ss')
and t.created_date <
to_date('2017-03-22 21:00;00', 'yyyy-MM-dd hh24:mi:ss')
group by t.pa_im_type;
客戶資訊頁籤
select a.* from (
select t.* from GIM_EXTAPP_EVENT t
where 1=1
/* <dynamic>
<isNotEmpty prepend="and" property="flowType">
</isNotEmpty>
<isNotEmpty prepend="and" property="eventType">
t.EVENT_TYPE=#eventType#
</isNotEmpty>
<isNotEmpty prepend="and" property="weAppNo">*/
and t.WEAPP_NO='XTAPP_09'
and t.FLOW_TYPE='in'
/* </isNotEmpty>
<isNotEmpty prepend="and" property="businessType">*/
and t.BUSINESS_TYPE='XTAPP_BIZ_01'
/* </isNotEmpty>
<isNotEmpty prepend="and" property="paImType">
t.PA_IM_TYPE=#paImType#
</isNotEmpty>
<isNotEmpty prepend="and" property="clientImNo">*/
and t.CLIENT_IM_NO='1015071400349850'
and t.PA_IM_TYPE='09'
/* </isNotEmpty>
<isNotEmpty prepend="and" property="socketId">
t.SOCKET_ID=#socketId#
</isNotEmpty>
</dynamic> */
order by t.date_created desc
) a
where rownum=1
通話
select t.cumtomer_id 客戶號, t.acctnum 錄音流水號,
t.dnis 外呼號碼,
t.agentid 坐席工號,
t.date_callstarttime 開始時間,
t.date_callendtime 結束時間,
t.talklong 通話時長，
t.buss_line
from gim_phone_call_logs t
where t.cumtomer_id = 'A2016091400000486816'
任務狀態：
“01”標示新建聊天任務，
“02”標示聊天任務處理中，
“03”標示坐席主動移除客戶結束當前任務，
“04”標示聊天任務已經轉接其他坐席，
“05”標示案件異常結案聊天任務（業務異常），
“06”標示客戶主動關閉會話或任務超時導致任務結束
“07”標示客戶暫時離開（如客戶按home鍵的狀態）
“11”標示系統異常導致任務結束，如job（尚未確定）
GIMOPR/gimap12345@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.10.17)(PORT = 1531))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = t1eim)))