create or replace procedure biller533.shzc_202101_dhm_qdgl_gc (p_userid varchar2,p_hm VARCHAR2, is_lishi VARCHAR2, p_id VARCHAR2, p_cursor in out Results.ref_cursor_type) as v_hm VARCHAR2(18); v_qx VARCHAR2(18); v_sql VARCHAR2(6000); SQL_STRING VARCHAR2(6000); v_last_day varchar2(8); v_last_day2 varchar2(8); v_day VARCHAR2(8); v_monq VARCHAR2(6); V_TAB1 varchar2(900); v_lishi VARCHAR2(10); v_mm1 VARCHAR2(6); v_mm2 VARCHAR2(6); v_mm3 VARCHAR2(6); v_orgid VARCHAR2(50); v_name VARCHAR2(50); v_sjhm VARCHAR2(50); v_id VARCHAR2(10); v_dfhm_cd number; begin v_hm :=substr(trim(p_hm),1,18); v_lishi := substr( is_lishi,1,6); v_day :=to_char(sysdate-2,'yyyymmdd'); v_monq :=to_char(add_months(sysdate-1,-1),'yyyymm') ; v_last_day :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymmdd') ;--上月最后一天 v_last_day2 :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymmdd') ;--上上月最后一天 v_mm1 :=to_char(sysdate-2,'yyyymm'); v_mm2 :=to_char(last_day(add_months(sysdate-1,-1)),'yyyymm') ; v_mm3 :=to_char(last_day(add_months(sysdate-1,-2)),'yyyymm') ; ---经分工号获取区县 select max(qx) into v_qx from (select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(ORGID) into v_orgid from (select a.userid,a.qx,NVL(case when a.qx='0' then 'SD.LC' END,'SD.LC.0'||A.QX ) ORGID from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,NVL(SUBSTR(ORGID,8,1),'0'),ORGID from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(username) into v_name from (select a.userid,a.username from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,opername from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; select max(mobilephone) into v_sjhm from (select a.userid,a.mobilephone from zhyw.zibo_jyfx_staff a UNION ALL select OPERID,CONTACTPHONE from tbcs.operator@Bcv WHERE ORGID like 'SD.LC%' ) where trim(userid)=p_userid ; if v_lishi='是' then v_sql:=' select ''ID'',''经分工号'',''申请人姓名'',''申请人号码'',''归属区县'',''组织机构'',''查询时间'',''查询号码'',''号码数'' from dual union all select a.* from (select a.id,a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构, to_char(a.in_time,''yyyymmddhh24miss'') in_time,a.hm,to_char(b.subsid) 号码数 from shzc.shzc_dhm_qdgl_id_bdcd a, shzc.shzc_dhm_qdgl_id_bdcd_jg b where a.id=b.id(+) and a.jfgh='''||p_userid||''' order by a.id desc) a' ; elsif v_lishi='明细' then v_sql:=' select ''ID'',''SERVNUMBER'',''SUBSID'',''HD_IDEN_NO'',''CREATEDATE'',''NAME'', ''REGISTERORGID'',''ORGNAME'',''DICTNAME'',''状态时间'',''本地'',''漫游地'',''本月分钟数'', ''本月通话次数'',''上月分钟数'',''上月通话次数'',''通话次数'',''通话号码数'',''离散度'',''短信发送量'', ''短信发送号码'',''短信离散度'' from dual union all select a.* from (select ID,SERVNUMBER,SUBSID,substr(HD_IDEN_NO,1,6)||''******''||substr(HD_IDEN_NO,13,6), to_char(CREATEDATE,''yyyy-mm-dd hh24:mi:ss''),NAME,REGISTERORGID,ORGNAME,DICTNAME, to_char(状态时间,''yyyy-mm-dd hh24:mi:ss''),本地,漫游地,to_char(本月分钟数),to_char(本月通话次数), to_char(上月分钟数),to_char(上月通话次数),to_char(通话次数),to_char(通话号码数),to_char(离散度), to_char(短信发送量),to_char(短信发送号码),to_char(短信离散度) from shzc.SHZC_DHM_QDGL_'||p_id||'_JG a order by a.HD_IDEN_NO desc ) a ' ; elsif v_lishi='否' then ------原始导入 fan_drop_retable(upper('shzc_dhm_qdgl_id_bd'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_id_bd as select '''||p_userid||''' jfgh,'''||v_name||''' 申请人姓名,'''||v_sjhm||''' 申请人号码, '''||v_qx||''' 归属区县,'''||v_orgid||''' 组织机构,sysdate in_time,'''||v_hm||''' hm from dual ' ; execute immediate (SQL_STRING); commit; SQL_STRING:='insert into shzc.shzc_dhm_qdgl_id_bdcd (jfgh,申请人姓名,申请人号码,归属区县,组织机构,in_time,hm) select a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构,a.in_time,a.hm from shzc.shzc_dhm_qdgl_id_bd a ' ; execute immediate (SQL_STRING); commit; select max(a.id) into v_id from shzc.shzc_dhm_qdgl_id_bdcd a where a.jfgh=p_userid and a.hm=v_hm ; fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_a'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_a as select a.* from (select a.id,a.hm,b.subsid,b.registerorgid,b.createdate,b.status, row_number() over (partition by b.servnumber order by b.createdate desc ) 排名 from shzc.shzc_dhm_qdgl_id_bdcd a , (select * from zhyw.subscriber b where b.status not in (''US26'',''US28'')) b where a.id='''||v_id||''' and a.hm=b.servnumber ) a where a.排名=1 ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_b'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_b as select a.id,b.servnumber,b.subsid,b.registerorgid,b.createdate,b.status from (select * from zhyw.subscriber b where b.status not in (''US26'',''US28'') and b.nettype=''GSM'') b, shzc.shzc_dhm_qdgl_'||v_id||'_a a where b.registerorgid=a.registerorgid and nvl(b.createdate,sysdate-9999)>a.createdate-31 and nvl(b.createdate,sysdate-9999)<a.createdate+1 ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_c'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_c as select a.*,b.hd_iden_no from shzc.shzc_dhm_qdgl_'||v_id||'_b a, shzc.shzc_age_jqb b where a.subsid=b.subsid(+) ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_d'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_d as select distinct a.* from (select t.id,t.servnumber,to_char(t.subsid) subsid,t.registerorgid,t.createdate,t.status,t.hd_iden_no from shzc.shzc_dhm_qdgl_'||v_id||'_c t union all select a.id,to_char(b.servnumber),b.subsid,d.registerorgid,d.createdate,d.status, b.hd_iden_no from shzc.shzc_age_jqb b, (select distinct a.id,a.hd_iden_no from shzc.shzc_dhm_qdgl_'||v_id||'_c a where nvl(a.hd_iden_no,''0'') <>''0'' ) a , (select * from zhyw.subscriber d where nettype=''GSM'' ) d where b.hd_iden_no=a.hd_iden_no and b.subsid=d.subsid ) a ' ; execute immediate (SQL_STRING); --1、所有号码现在的使用状态 --2、所有号码的同一证件下其他号码的状态 --3、证件下所有号码的近一个月的漫游情况 --4、证件下所有号码近两个月的通话数量、分钟数 --5、办理的渠道名称,办理日期 --离散度 --短信发送量 --3、证件下所有号码的近一个月的漫游情况 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_e'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_e as select a.user_id,a.roamcity_id,count(distinct a.call_tm) call_tm,sum(a.cs) cs,sum(a.call_duration_m) call_duration_m from ZHYW.ZWZX_MHH_CALL_CELL_HD a, shzc.shzc_dhm_qdgl_'||v_id||'_d b where a.user_id=b.subsid group by a.user_id,a.roamcity_id ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_emy'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_emy as select a.user_id, listagg (a.PROVINCENAME||''_''||a.AREAname,'' ,'') within group (order by user_id) 漫游地 from (select B.PROVINCENAME,b.AREAname,a.*, row_number() over (partition by a.user_id order by a.call_tm desc , a.call_duration_m desc ) 排名 from shzc.shzc_dhm_qdgl_'||v_id||'_e a , (select * from common.SA_DB_NATAREAINFO@bcv b WHERE AREATYPE=1 ) B where a.ROAMCITY_ID=b.AREAID(+) and a.roamcity_id<>''533'') a group by a.user_id ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_ebd'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_ebd as select a.user_id, listagg (a.PROVINCENAME||''_''||a.AREAname,'' ,'') within group (order by user_id) 本地 from (select B.PROVINCENAME,b.AREAname,a.*, row_number() over (partition by a.user_id order by a.call_tm desc , a.call_duration_m desc ) 排名 from shzc.shzc_dhm_qdgl_'||v_id||'_e a , (select * from common.SA_DB_NATAREAINFO@bcv b WHERE AREATYPE=1 ) B where a.ROAMCITY_ID=b.AREAID(+) and a.roamcity_id=''533'') a group by a.user_id ' ; execute immediate (SQL_STRING); --4、证件下所有号码近两个月的通话数量、分钟数 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_f'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_f as select a.subsid, nvl(b.本月_mou,0) 本月分钟数,nvl(b.本月通话次数,0) 本月通话次数, nvl(b.上月_mou,0) 上月分钟数,nvl(b.上月通话次数,0) 上月通话次数 from shzc.shzc_dhm_qdgl_'||v_id||'_d a, zhyw.shc_all_label_mx b where a.subsid=b.subsid ' ; execute immediate (SQL_STRING); ---离散度 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_g'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_g as select a.user_id,a.opposite_regular_no,a.call_tm,a.cs,a.call_duration_m,nvl(a.zj_duration_m,0) zj_duration_m from zhyw.zwzx_mhh_call_oppo_hd a, shzc.shzc_dhm_qdgl_'||v_id||'_d b where a.user_id=b.subsid ' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_gh'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_gh as select a.user_id,sum(a.cs) 通话次数,count(distinct a.opposite_regular_no) 通话号码数, round(sum(a.cs)/count(distinct a.opposite_regular_no),4) 离散度 from shzc.shzc_dhm_qdgl_'||v_id||'_g a group by a.user_id' ; execute immediate (SQL_STRING); ---短信发送量 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_h'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_h as select a.* from zibo.cdr_sms_'||v_mm1||' a, shzc.shzc_dhm_qdgl_'||v_id||'_d b where a.user_id=b.subsid' ; execute immediate (SQL_STRING); fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_hh'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_hh as select a.user_id,count(*) 短信发送量,count(distinct a.opposite_no) 短信发送号码, round(count(*)/count(distinct a.opposite_no),4) 短信离散度 from shzc.shzc_dhm_qdgl_'||v_id||'_h a group by a.user_id' ; execute immediate (SQL_STRING); ---组合 fan_drop_retable(upper('shzc_dhm_qdgl_'||v_id||'_jg'),'SHZC'); SQL_STRING:='create table shzc.shzc_dhm_qdgl_'||v_id||'_jg as select a.id,a.servnumber,a.subsid,a.hd_iden_no,a.createdate , nvl(q.name,''全市'') name,a.registerorgid,b.orgname,zt.dictname,t.statusdate 状态时间, nvl(bd.本地,''无'') 本地,nvl(my.漫游地,''无'') 漫游地, nvl(f.本月分钟数,0) 本月分钟数,nvl(f.本月通话次数,0) 本月通话次数, nvl(f.上月分钟数,0) 上月分钟数,nvl(f.上月通话次数,0) 上月通话次数, nvl(g.通话次数,0) 通话次数,nvl(g.通话号码数,0) 通话号码数,nvl(g.离散度,0) 离散度, nvl(h.短信发送量,0) 短信发送量,nvl(h.短信发送号码,0) 短信发送号码,nvl(h.短信离散度,0) 短信离散度 from shzc.shzc_dhm_qdgl_'||v_id||'_d a, (select * from tbcs.dict_item@bcv where groupid=''US'') zt, tbcs.organization@bcv b, zhyw.rpt_county q , shzc.shzc_dhm_qdgl_'||v_id||'_ebd bd, shzc.shzc_dhm_qdgl_'||v_id||'_emy my, shzc.shzc_dhm_qdgl_'||v_id||'_f f , shzc.shzc_dhm_qdgl_'||v_id||'_gh g, shzc.shzc_dhm_qdgl_'||v_id||'_hh h, zhyw.subscriber t where a.status=zt.dictid(+) and a.registerorgid=b.orgid(+) and substr(a.registerorgid,8,1) =q.county_id(+) and a.subsid=bd.user_id(+) and a.subsid=my.user_id(+) and a.subsid=f.subsid(+) and a.subsid=g.user_id(+) and a.subsid=h.user_id(+) and a.subsid=t.subsid(+)' ; execute immediate (SQL_STRING); SQL_STRING:='insert into shzc.shzc_dhm_qdgl_id_bdcd_jg select a.id,count(distinct a.subsid) subsid from shzc.shzc_dhm_qdgl_'||v_id||'_jg a group by a.id ' ; execute immediate (SQL_STRING); commit; v_sql:=' select ''ID'',''经分工号'',''申请人姓名'',''申请人号码'',''归属区县'',''组织机构'',''查询时间'',''查询号码'',''号码数'' from dual union all select a.* from (select a.id,a.jfgh,a.申请人姓名,a.申请人号码,a.归属区县,a.组织机构, to_char(a.in_time,''yyyymmddhh24miss'') in_time,a.hm,to_char(b.subsid) 号码数 from shzc.shzc_dhm_qdgl_id_bdcd a, shzc.shzc_dhm_qdgl_id_bdcd_jg b where a.id=b.id(+) and a.jfgh='''||p_userid||''' order by a.id desc) a ' ; end if ; open p_cursor for v_sql; end ;
通过录入号码生成一个工单号,根据工单ID创建系列后台表,避免查询生成数据冲突
最新推荐文章于 2022-05-11 18:32:42 发布