远程桌面测试数据库账号：
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)))
网友评论