流失客户分配

select * from tfp_cpdm where cpmc like '%国通信托·方兴1070号重庆市黔江区债权投资集合资金信托计划第二期%'
select * from tfp_xtjh where id=4311
var rs=LB_sqlResultSet("SELECT b.roleid RYJS,replace(wm_concat(c.id),',',';') RYXX FROM lbmember b,tryxx c where  c.zhzt=0 and c.dyyh=b.userid AND (b.roleid in (1,16)  or (b.roleid=3 and b.userid=9) OR UPPER(c.dyry)=?) group by roleid",[rybh]);




SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
  FROM lbmember b, tryxx c
 where c.zhzt = 0
   and c.dyyh = b.userid
   AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
       UPPER(c.dyry) = 'XINGWENJIA')
 group by roleid
select  chr(39)||123||chr(39) from dual;
 select chr(39)||UPPER(htrybh)||chr(39) from tfp_xtjh t,tfp_cpdm cp where  t.id=cp.xtjh and  cp.id=36044
 
 
 
 SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
   FROM lbmember b, tryxx c
  where c.zhzt = 0
    and c.dyyh = b.userid
    AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
        UPPER(c.dyry) = 'CHENGXI')
  group by roleid   [ 1 :"'CHENGXI'" ]
  
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
  FROM lbmember b, tryxx c
 where c.zhzt = 0
   and c.dyyh = b.userid
   AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
       UPPER(c.dyry) = 'XINGWENJIA')
 group by roleid
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
  FROM lbmember b, tryxx c
 where c.zhzt = 0
   and c.dyyh = b.userid
   AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
       UPPER(c.dyry) = 'XINGWENJIA')
 group by roleid
 select khjl from tkhxx where id=59272
 select *from tryxx where id=316
select  ORGID  from tryxx where ryxm like '%陈伟%'
select PROVINCE,ZJLB,fn_decrypt(zjbh),t.* from tkhxx t where khxm like '%吴媛%'  120000  --59272
--吴媛
select *from lborganization where id=103
select * from lborganization b where province=to_char(120000)
-----------------------------------------------------------------------------
insert into tempdata(n1,n2,c1)     --n2 0:非全国分配  n3 对应tryxx.id
    select id,0,substr(fn_decrypt(ZJBH),1,2)||'0000' from tkhxx a where a.khzt in(2,4) and a.sfdxkh=1;
-----------------------------------------------------------------------------------------------

select * from tempdata WHERE N1=59272
TRUNCATE TABLE tempdata
-------------------------------------------------------------------------------------
SELECT * FROM  tempdata A
     where not exists(select 1 from lborganization b,tryxx c                 select * from tryxx r where  r.orgid=23
                              where
                             -- 42000=42000 
                               -- a.c1=to_char(b.province)
                                a.c1=to_char(b.province)
                                and b.orgtype=31 and b.province is NOT null ---天津分中心没有理财经理
                                and b.id=c.orgid
                                and c.zhzt=0                                     select *from lborganization where orgtype=31
                                and c.ryfl in(1,3)) AND a.N1=59272;
 -------------------------------------------------------------------------------                               
     SELECT * FROM  tempdata a
     where  exists(select 1 from lborganization b,tryxx c
                              where 
                                --42000=42000
                                a.c1=to_char(b.province) 
                                 and b.orgtype=31 and b.province is NOT null 
                                 and b.id=c.orgid 
                                 and c.zhzt=0    
                                 and c.ryfl in(1,3)) 
                                 AND a.N1=59272;
                               
-------------------------------------------------------------------------------------
SELECT * FROM  tempdata a
     where  exists(select 1 from lborganization b,tryxx c
                              where
                             -- 42000=42000 
                               -- a.c1=to_char(b.province)
                                a.c1=to_char(b.province)
                                and b.orgtype=31 and b.province is NOT null
                                and b.id=c.orgid
                                and c.zhzt=0
                                and c.ryfl in(1,3)) AND a.N1=59272;
------------------------------------------------------------------------------------
update tempdata a set a.n2=1
     where not exists(select 1 from lborganization b,tryxx c
                              where a.c1=to_char(b.province)
                                and b.orgtype=31 and b.province is not null
                                and b.id=c.orgid
                                and c.zhzt=0
                                and c.ryfl in(1,3));
COMMIT;
--------------select * from tempdata WHERE N1=59272
select * from   tempdata_bb
select * from    tempdata_cc
select * from tempdata WHERE N1=59272  select rownum from tryxx   where rownum<2

select mod(1,2) from dual;

select * from   tempdata where N1=59272 
                                           ---          tempdata  N1: 客户id      N2:渠道客户全国分配  C1:省份  
-----------------------------------------------         tempdata_bb : N1:客户id   N2:行号
   --------------                                       tempdate_cc : N1:理财经理id  N2:行号-1          
  --无省直辖市的全国分配
    execute immediate 'truncate table tempdata_bb';
    execute immediate 'truncate table tempdata_cc';
    insert into tempdata_bb(n1,n2)
       select n1,rownum from tempdata a where a.n2=1;
      --取出待分配的人员
      insert into tempdata_cc(n1,n2)                        -- select *from tempdata_bb WHERE N1=59272   
        select a.id,rownum-1                                   select * from tempdata_cc 
          from tryxx a,lborganization b                        select count(*) from tempdata_cc 
         where a.orgid=b.id
           and b.orgtype=31
           and a.ryfl in(1,3)
           and a.zhzt=0;
    --平均分配
    select count(1) into v_count from tempdata_cc;
    update tempdata_bb a set a.n3=(select b.n1 from tempdata_cc b where mod(a.n2,v_count)=b.n2);
    --把分配结果更新回主表tempdata
    update tempdata a set a.n3=(select b.n3 from tempdata_bb b where a.n1=b.n1)
    where exists(select 1 from tempdata_bb b where a.n1=b.n1);
    --最后落地所有渠道客户的分配数据
    insert into tlskhfpb(id,fppc,fprq,ryxx,khh,khfl,fplb,SFFP)

    select a.n1,v_fppc,v_rq,a.n3,a.n1,0,a.n2,2 from tempdata a;
    commit;