select * from(
select
*
FROM OAS_CERTIFICATE_TASK t
WHERE
t.id not in(
select
TASK_ID
from
OAS_CERTIFICATE_TASK_RECORD r where r.RESPONSECODE ='0'
)
and
CERTIFICATE_TIMES <= 6
order by t.UPDATED_DATE asc , t.id asc
) where rownum <= 1000;
select count(*) from OAS_CERTIFICATE_TASK where
updated_date >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')
and updated_date < to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')+1 ;
select count(*) from OAS_CERTIFICATE_TASK_RECORD where
updated_date >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')
and updated_date < to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')+1 ;
-- 科技视频 雪球 蚂蚁 身份证升为 密码重置 修改手机号码
-- 如果见证状态为未审核
select count(*) from (select
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id
where t1.flow_sn is not null
and t1.channel_id in ('xqkh')
--and t2.mobileno in('','')
--and t1.cust_id = ''
--and t2.cuacct_code = ''
-- and t2.idno = ''
-- and t2.user_code = ''
-- and (t1.approve_stat = '0' or t1.approve_stat is null)
-- and t1.srv_id = ''
--and t1.call_time >= '2018-07-25'
--and t1.call_time <= '2018-10-25'
and t1.cust_name like '金宇'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum
--雪球视频
select count(*) from (select
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id
where t1.flow_sn is not null
and t1.channel_id in ('xqkh')
and t2.mobileno in('177172252948946619562011')
and t1.cust_id = '10603264'
and t2.cuacct_code = '307000012278'
and t2.idno = '340825199712207422'
and t2.user_code = '150309931'
--and (t1.approve_stat = '0' or t1.approve_stat is null) and t1.reject_reason is null --未审核
--and t1.approve_stat = '2'--审核不通过PA18只有状态2才是不通过
--and t1.reject_reason is not null and t1.approve_stat < '4'--驳回
--and t1.approve_stat = '4'--审核通过
--and t1.approve_stat = '1'--见证通过
and t1.srv_id = 'CHENCESHI012'
and t1.call_time >= to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')--这里表示最近三个月数据
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name like '林机'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum
--蚂蚁坐席
select * from (select
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id
where
t1.channel_id in ('ant')
--and t2.mobileno in('13536703840')
-- and t1.cust_id = '10523246'
-- and t2.cuacct_code = '301719985055'
--and t2.idno = '310103197805282033'
-- and t2.user_code = '180909369'
--and (t1.approve_stat = '0' or t1.approve_stat is null) and t1.reject_reason is null --未审核
--and t1.approve_stat = '2'--审核不通过PA18只有状态2才是不通过
-- and t1.reject_reason is not null and t1.approve_stat < '4'--驳回
-- and t1.approve_stat = '4'--审核通过
--and t1.approve_stat = '1'--见证通过
--and t1.srv_id = 'YANCESHI437'
and t1.call_time >= to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')--这里表示最近三个月数据
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name = '王赛'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum
--信托双录, 补充客户影像资料(xtsl,wszlkh)
SELECT w.cust_id AS custId,
w.cust_name AS custName,
c.user_code AS userCode,
c.cuacct_code AS cuacctCode,
c.mobileno AS mobileNo,
c.idno AS idNo,
w.srv_id AS srvId,
w.srv_name AS srvName,
w.approve_stat AS approveStat,
w.approve_date AS approveDate,
w.flow_sn AS flowSn,
w.channel_id AS channelId,
w.call_time AS callTime
FROM t_openacc_witnessvideo_info w
LEFT JOIN oas_openacc_customer_info c ON to_char(c.id)=w.cust_id
where w.channel_id = 'xtsl'
and w.call_time >= '2018-07-25 16:50:14'
and w.call_time <= '2018-10-25 16:50:14'
and w.cust_name='双录一'
--信托双录, 补充客户影像资料(xtsl,wszlkh)
--账户检测身份证上传页面资料(补全资料视频信息表)
SELECT i.rowid,i.mobilestate,i.* FROM oas_openacc_customer_info i where i.userid='10606209' ORDER BY ID DESC;
--账户检测视频资料（信托双录, 补充客户影像资料视频表）
SELECT t.rowid,t.approve_stat,t.approve_date,t.address_description,t.* FROM t_openacc_witnessvideo_info t where t.channel_id='wszlkh' and t.cust_id='10603888'and t.call_time is not null ORDER BY t.call_time DESC for update;
SELECT t.rowid,t.approve_stat,t.approve_date,t.address_description,t.* FROM t_openacc_witnessvideo_info t where t.channel_id='xtsl' and t.cust_id='10606209'and t.call_time is not null ORDER BY t.call_time DESC for update;
---销户
select * from (sELECT w.user_id as custId,c.cust_name as custName,c.user_code as userCode,c.cuacct_code as cuacctCode,
c.mobile_no as mobileNo,
c.id_no as idNo,
w.srv_id as srvId,
w.srv_name as srvName,
nvl(w.review_status, '0') as approveStat,
w.review_date as approveDate,
w.video_sn as videoSn,
'wsxh' channelId,
w.call_time callTime
FROM oas_cancelacc_video_witness w, oas_cancelacc_customer_info c
where c.id=w.user_id
-- and w.user_id = ''
-- and c.user_code = ''
and c.cuacct_code = '123456'
and c.mobile_no in('13110000539')
and c.id_no = '350500199402281895'
--and w.srv_id =? ''
--and (w.review_status = '0' or w.review_status is null)
--and w.call_time >=to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')--这里表示最近三个月数据to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')
and w.call_time >='2018-10-16 15:49:08'
and w.call_time<= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and w.srv_id ='LIANGCESHI869'order by w.call_time desc) where 10000 > rownum
-- anychat flush
SELECT *
FROM (
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,
t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t1.cust_stream,t1.srv_stream,t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from
t_accepted_witnessvideo t1, t_accepted_customer_info t2
where t1.cust_id = t2.id
and (t1.cust_stream like '%flv' or t1.cust_stream like '%mp4')
and t1.call_time >= to_char(sysdate - interval '3' month, 'yyyy-MM-dd HH24:mi:ss')
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name=''
order by t1.call_time desc) TEMP WHERE 10000 > ROWNUM
-- anychat
SELECT *
FROM (select
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,
t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t1.cust_stream,t1.srv_stream,t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from
t_accepted_witnessvideo t1, t_accepted_customer_info t2
where t1.cust_id = t2.id
and t1.cust_stream like '%flv'
and t1.call_time >= to_char(sysdate - interval '3' month, 'yyyy-MM-dd HH24:mi:ss')
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name=''
order by t1.call_time desc) TEMP WHERE 10000 > ROWNUM
-- flush
SELECT *
FROM (select
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,
t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,
t1.cust_stream,t1.srv_stream,t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from
t_accepted_witnessvideo t1, t_accepted_customer_info t2
where t1.cust_id = t2.id
and t1.cust_stream like '%mp4'
and t1.call_time >= to_char(sysdate - interval '3' month, 'yyyy-MM-dd HH24:mi:ss')
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name=''
order by t1.call_time desc) TEMP WHERE 10000 > ROWNUM
-- h5
SELECT *
FROM (SELECT t1.call_time, t1.flow_sn, t1.approve_stat, t1.approve_date, t1.reject_reason, t1.srv_id, t1.srv_name,
t1.channel_id, t1.cust_id, t1.cust_name, t2.userid, t2.idno, t2.mobileno, t2.cuacct_code, t2.user_code
FROM t_openacc_witnessvideo_info t1 LEFT JOIN t_accepted_customer_info t2 ON t1.cust_id = t2.id
WHERE t1.flow_sn IS NOT NULL AND t1.channel_id = 'h5kh'
and t1.call_time >= to_char(sysdate - interval '3' month, 'yyyy-MM-dd HH24:mi:ss')
and t1.call_time <= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and t1.cust_name=''
order by t1.call_time desc ) TEMP WHERE 10000 > ROWNUM
SELECT t1.call_time, t1.flow_sn, t1.approve_stat, t1.approve_date, t1.reject_reason, t1.srv_id, t1.srv_name,
t1.channel_id, t1.cust_id, t1.cust_name, t2.userid, t2.idno, t2.mobileno, t2.cuacct_code, t2.user_code
FROM t_openacc_witnessvideo_info t1 LEFT JOIN t_accepted_customer_info t2 ON t1.cust_id = t2.id
WHERE t1.flow_sn IS NOT NULL AND t1.channel_id = 'h5kh' and t1.call_time is not null order by t1.call_time desc for update
select l.cuacct_code,l.user_code,l.* from t_accepted_customer_info l where l.userid='10607371' for update
-- 两融 金证云(金证云视频是好早以前的了)
select *
from (select
w.witnessid, w.userinfoid,w.srvid,w.srvname,w.reviewdate,w.reviewstatus,w.videosn,w.create_date,
w.calltime, i.idno,i.custname,i.mobileno,i.channelid,i.channelname,i.Infoid
from oas_third_openacc_witness w left join oas_third_openacc_info i on w.userinfoId = i.Infoid
where
i.channelid = 'rzrqkh'
--and w.userinfoid = '78792C30BD43FBD3E05387A5190AA12E'
-- and c.user_code = ''--表中没有，不需要查
--and c.cuacct_code = ''--表中没有，不需要查
--and c.mobileno in('15907852535')
-- and i.idno = '220000198403275234'
--and i.custname = '征信违约'--这个用户可以查以视频
--and (w.review_status = '0' or w.review_status is null)
and w.calltime >=to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')--这里表示最近三个月数据to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')
--and w.calltime >='2018-10-16 15:49:08'
and w.calltime<= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
and w.srvid ='LIANGCESHI869'
order by i.channelid desc, w.calltime desc) t where 10000 > rownum
select *
from (select
w.witnessid, w.userinfoid,w.srvid,w.srvname,w.reviewdate,w.reviewstatus,w.videosn,w.create_date,
w.calltime, i.idno,i.custname,i.mobileno,i.channelid,i.channelname,i.Infoid
from oas_third_openacc_witness w left join oas_third_openacc_info i on w.userinfoId = i.Infoid
where
i.channelid = 'jzykh'and w.calltime is not null order by w.calltime desc
--and w.userinfoid = '78792C30BD43FBD3E05387A5190AA12E'
-- and c.user_code = ''--表中没有
--and c.cuacct_code = ''--表中没有
--and c.mobileno in('15907852535')
-- and i.idno = '220000198403275234'
--and i.custname = '征信违约'--这个用户可以查以视频
--and (w.review_status = '0' or w.review_status is null)
--and w.calltime >=to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')--这里表示最近三个月数据to_char(sysdate - INTERVAL '3' MONTH, 'yyyy-MM-dd HH24:mi:ss')
and w.calltime >='2017-01-16 15:49:08'
and w.calltime<= to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss')
--and w.srvid ='LIANGCESHI869'
order by i.channelid desc, w.calltime desc) t where 10000 > rownum
--两融、金证云视频表
select * from oas_third_openacc_info m where m.c
select * from oas_third_openacc_witness k where k.srvid='LIANGCESHI869' and k.calltime is not null order by k.calltime desc for update
select
w.witnessid, w.userinfoid,w.srvid,w.srvname,w.reviewdate,w.reviewstatus,w.videosn,w.create_date,
w.calltime, i.idno,i.custname,i.mobileno,i.channelid,i.channelname,i.Infoid
from oas_third_openacc_witness w left join oas_third_openacc_info i on w.userinfoId = i.Infoid
where
i.channelid = 'jzykh' for update
渠道话术接口调营销平台的接口入参查询SQL
select *
from (select y.recommendidno as recommendNo,
y.source as source,
y.banktype as thrBankNo,
z.ouid as ouid,
z.aid as aid,
z.sid as sid
from t_accepted_customer_info y
left join t_accepted_analyse z on y.userid = z.userid
where y.userid = '10517357'
order by y.createdate desc)
where rownum = 1
本地话术查询SQL
select id as id,
channel_name as channelName,
channel_title as channelTitle,
describ as describ,
Speech_short as SpeechShort,
Speech_full as SpeechFull,
Speech_extend as SpeechExtend,
recom_financy as recomfinancy,
recom_download as recomDownload,
double_mutation as doubleMutation,
remark as remark,
extension1 as extension1,
extension2 as extension2
from oas_channel_speech
报表--开户坐席报表-选择视频类型--anyChat
1. SQL:
select
count(distinct a.cust_id) as allUserNum,-- 总客户数
count(1) as allVedioNum,-- 总接入量
count(case when a.srv_id !='null' then 1 else null end) as allAccVedioNum, -- 应答总量
count(case when a.approve_stat = '4' then 1 else null end) as allApproveVideoNum -- 审核通过量
from t_accepted_witnessvideo a
where a.call_time >='2017-01-01'
and a.call_time < '2017-09-04'
and a.flow_sn is null
and a.ant_uuid is null;
select * from t_accepted_openacc_report_log A where A.Created_Date >= to_date('2017-09-08 08','yyyy-MM-dd HH24') and A.Created_Date <= to_date( '2017-09-08 17','yyyy-MM-dd HH24') and A.Created_By='anyChat' --创建时间是指去定时抓数据的时间，即抓的是8点到16点的数据
select a.call_time,a.flow_sn,a.ant_uuid,a.approve_stat,a.cust_stream,a.cust_id,a.srv_id,a.srv_stream,a.sate
from t_accepted_witnessvideo a
where
a.call_time >='2017-09-13' and
a.call_time < '2017-09-14' and
a.flow_sn is null and
a.ant_uuid is null
order by a.approve_stat desc ,a.srv_id;
1、复核平台默认播放精简视频
2、视频在线调阅与复核平台都是调用同一个视频接口
OAS:
--1.未审核，SQL拼接条件为：w.reviewstatus = '0'
--2.见证通过，SQL拼接条件为：w.reviewstatus = '1'
--3.驳回，SQL拼接条件为：w.reviewstatus = '2'
--4.审核不通过，SQL拼接条件为：w.reviewstatus= '3'
--5.审核通过，SQL拼接条件为：w.reviewstatus = '4'
w.reviewstatus = '3' and
PA18:
--1.未审核，SQL拼接条件为：t1.approve_stat = '0' and 1.reject_reason is null
--2.驳回，SQL拼接条件为：t1.reject_reason is not null and t1.approve_stat !='4'
--3.审核不通过，SQL拼接条件为：t1.approve_stat = '2'
--4.审核通过，SQL拼接条件为：t1.approve_stat = '4'
-- t1.approve_stat = '2' and --当pa18查询未审核、审核不通过、审核通过时拼接到查询条件中
-- t1.reject_reason is not null and --仅当pa18查询驳回的时候拼接到查询条件中
PA18:
select /*+ FIRST_ROWS INDEX(t2,IDX_CUSTOMER_MOBILENO) INDEX(t2,IDX_CUACCT_CODE) INDEX(t2,IDX_CUSTOMER_INFO_USER_CODE) INDEX(t2,IDX_CUSTOMER_IDNO) INDEX(t1,T_ACCEPTED_CUST_ID)?INDEX(t1,IDX_WITNESSVIDE_FLOWSN) */
t1.*,t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id
where t1.flow_sn is not null
--根据手机号查询
and t2.mobileno = #mobileNo#
--根据客户代码查询
and t1.cust_id = #custId#
--根据资金账号查询
t2.cuacct_code = #cuacctCode#
--根据身份证查询
t2.idno = #idNo#
--根据客户号查询
t2.user_code = #userCode#
--查询未审核的
and (t1.approve_stat = '0' or t1.approve_stat is null)
--查询见证通过-1、审核不通过-3、审核通过-4的
and t1.approve_stat = #approveStat#
--查询驳回的
and t1.reject_reason is not null and t1.approve_stat != '4'
--根据坐席UM查询
and t1.srv_id = #srvId#
--根据时间段查询
and t1.call_time >= #strBeginDate# and t1.call_time <= #strEndDate#
order by t1.call_time desc
OAS:
select /*+ FIRST_ROWS INDEX(i,IDX_OAS_THIRD_OPENACC_I_IDNO) INDEX(w,UK_OAS_TH_OPENACC_VIDEOSN) */
w.witnessid, w.userinfoid,i.idno,i.custname,i.mobileno,i.channelid,i.channelname,
w.srvid,w.srvname,w.reviewdate,w.reviewstatus,w.videosn
from oas_third_openacc_witness w left join oas_third_openacc_info i on w.userinfoId = i.Infoid
where
--根据身份证查询
i.idno = #idNo#
--根据客户代码查询
and i.Infoid = #custId#
--根据手机号查询
and i.mobileno = #mobileNo#
--查询未审核的
and (w.reviewstatus = '0' or w.reviewstatus is null)
--查询见证通过，驳回，审核不通过，审核通过的
and w.reviewstatus = #approveStat#
--根据坐席UM查询
and w.srvid = #srvId#
--根据时间段查询
and w.create_date >= to_date(#strBeginDate#,'yyyy-MM-dd HH24:mi:ss')
and w.create_date <= to_date(#strEndDate#,'yyyy-MM-dd HH24:mi:ss')
order by w.create_date desc
PA18：t_accepted_witnessvideo-审核状态 0未审核，4通过，2不通过
OAS：oas_third_openacc_info-审核状态 0待审核 1已通过；oas_third_openacc_witness-见证状态 0未审核 1见证通过 2驳回 3审核不通过 4审核通过
405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
