远程桌面测试数据库账号：
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');
作者：AlbenXie
链接：https://www.jianshu.com/p/fbe396f8252b
随时随地看视频
