调整指标达成插入yjlx表的sql

-- INSERT INTO "yjlx" ("yjlx_lxqj", "yjlx_lxrq", "yjlx_khbh", "yjlx_khmc", "yjlx_wlbh", "yjlx_wlmc", "yjlx_ggxh", "yjlx_jldw", "yjlx_sl", "yjlx_lxsl", "yjlx_lxdj", "yjlx_dj", "yjlx_je", "yjlx_gysbh", "yjlx_gysmc", "yjlx_ywy", "yjlx_ywymc", "yjlx_tzry", "yjlx_tzrymc", "yjlx_dqbh", "yjlx_gysdqbh", "yjlx_lrrq", "yjlx_lrry", "yjlx_pch", "yjlx_bz", "yjlx_ppkhmc", "yjlx_ppkhbh", "yjlx_spbh", "yjlx_xs", "yjlx_tzbz", "yjlx_khdj", "yjlx_djppbh", "yjlx_djppmc", "yjlx_tx", "yjlx_gyslb", "yjlx_sfzx", "yjlx_sfcx", "yjlx_dqmc", "yjlx_gszf", "yjlx_js", "yjlx_lsbz", "yjlx_xykh", "yjlx_wllb", "yjlx_xyjs", "yjlx_sffx", "yjlx_xybm", "yjlx_sybm", "yjlx_depart5", "yjlx_depart4", "yjlx_sf", "yjlx_ds", "yjlx_label", "yjlx_xybmmc","yjlx_sycategory", "yjlx_sylevel1", "yjlx_xylevel2", "yjlx_xyisflow", "yjlx_xyisotcflow", "yjlx_syisflow", "yjlx_syisotcflow", "yjlx_sydept1", "yjlx_type", "yjlx_lsxl_id", "yjlx_gzlx_id", "yjlx_remark", "yjlx_upd_by", "yjlx_upd_time", "yjlx_agent_client_id", "yjlx_agent_client_name", "yjlx_depart7", "yjlx_lx_wlbh") 
select 
"yjlx_lxqj", "yjlx_lxrq", "yjlx_khbh", "yjlx_khmc", "yjlx_wlbh", "yjlx_wlmc", "yjlx_ggxh", "yjlx_jldw", "yjlx_sl", "yjlx_lxsl", "yjlx_lxdj", "yjlx_dj", "yjlx_je", "yjlx_gysbh", "yjlx_gysmc", "yjlx_ywy", "yjlx_ywymc", "yjlx_tzry", "yjlx_tzrymc", "yjlx_dqbh", "yjlx_gysdqbh", "yjlx_lrrq", "yjlx_lrry", "yjlx_pch", "yjlx_bz", "yjlx_ppkhmc", "yjlx_ppkhbh", "yjlx_spbh", "yjlx_xs", "yjlx_tzbz", "yjlx_khdj", "yjlx_djppbh", "yjlx_djppmc", "yjlx_tx", "yjlx_gyslb", "yjlx_sfzx", "yjlx_sfcx", "yjlx_dqmc", "yjlx_gszf", "yjlx_js", "yjlx_lsbz", "yjlx_xykh", "yjlx_wllb", "yjlx_xyjs", "yjlx_sffx", "yjlx_xybm", "yjlx_sybm", "yjlx_depart5", "yjlx_depart4", "yjlx_sf", "yjlx_ds", "yjlx_label", "yjlx_xybmmc","yjlx_sycategory", "yjlx_sylevel1", "yjlx_xylevel2", "yjlx_xyisflow", "yjlx_xyisotcflow", "yjlx_syisflow", "yjlx_syisotcflow", "yjlx_sydept1", "yjlx_type", "yjlx_lsxl_id", "yjlx_gzlx_id", "yjlx_remark", "yjlx_upd_by", "yjlx_upd_time", "yjlx_agent_client_id", "yjlx_agent_client_name", "yjlx_depart7", "yjlx_lx_wlbh"
from yjlx2026 where yjlx_lxqj='202603'
-- delete from yjlx;

 20260525

CREATE OR REPLACE FUNCTION "public"."sp_upd_quota_flag"("yearmonth" text)
  RETURNS "pg_catalog"."text" AS $BODY$
     --功能：更新quota_flag表，将每个月的销量与指标对比；
   --2026-03-12 
     declare 
  sql text;
  curYear text;    --当前年度，取月份前4位；

BEGIN
    --处理逻辑
    --1、插入当月的数据，如果有的话先清除；分专员，经理，省区经理，事业部经理4类；
    --2、插入当月未有销量的指标数据 ，销量默认0；
    --3、更新月指标、累计指标、完成标志；
    --4、特殊情况处理
    --判断年月参数是否正确；

    if left(yearmonth,3)!='202' then 
        return '0';
    end if;
    if "length"(yearmonth)!=6  then 
        return '0';
    end if;
    curYear = left(yearmonth,4);
    -- 插入前先删除
    sql = 'delete from quota_flag where flow_month='''||yearmonth||'''';
    RAISE NOTICE '插入前先删除语句： %',sql;
    execute sql;
    sql = 'insert into quota_flag(c1,flow_month,department_id,account_id,goods_code,qty)
                select ''专员''  as  positionname,yjlx_lxqj,yjlx_xybm as department_id,yjlx_tzry as                   account_id,b.new_goods_code,round(sum(yjlx_sl/b.coef),2) as yjlx_sl 
                from  yjlx              
                left join goods_convert b on yjlx_sf=b.province_id and yjlx_wlbh=b.goods_code
                where yjlx_sfcx=1 and b.year={0} and yjlx_lxrq>''{0}'' and yjlx_tzry not in (select distinct ownerid from department) and yjlx_lxqj=''{1}''  and (yjlx_lx_wlbh=''7003'' or yjlx_lx_wlbh='''')
                group by  yjlx_lxqj,yjlx_tzry,yjlx_xybm,b.new_goods_code
                union  all   
                select ''地区经理''  as  positionname,yjlx_lxqj,yjlx_xybm as department_id,c.ownerid as account_id,b.new_goods_code,round(sum(yjlx_sl/b.coef),2) as yjlx_sl 
                from  yjlx              
                left join goods_convert b on yjlx_sf=b.province_id and yjlx_wlbh=b.goods_code
                left join department_his c on yjlx_xybm=c.id and yjlx_lxqj=c.yearmonth
                where yjlx_sfcx=1 and b.year={0} and yjlx_lxrq>''{0}'' 
                 and yjlx_lxqj=''{1}'' and (yjlx_lx_wlbh=''7003'' or yjlx_lx_wlbh='''')
                group by  yjlx_lxqj,yjlx_xybm,c.ownerid,b.new_goods_code
                union all 
                select ''省区经理''  as  positionname,yjlx_lxqj,c.dqid as department_id,0 as account_id,b.new_goods_code,round(sum(yjlx_sl/b.coef),2) as yjlx_sl 
                from  yjlx              
                left join goods_convert b on yjlx_sf=b.province_id and yjlx_wlbh=b.goods_code
                left join department_his c on yjlx_xybm=c.id and yjlx_lxqj=c.yearmonth
                where yjlx_sfcx=1 and b.year={0} and yjlx_lxrq>''{0}'' and yjlx_lxqj=''{1}'' 
                and (yjlx_lx_wlbh=''7003'' or yjlx_lx_wlbh='''')
                group by  yjlx_lxqj,c.dqid,b.new_goods_code
                union all 
                select ''事业部经理''  as  positionname,yjlx_lxqj,c.sybid as department_id,0 as account_id,b.new_goods_code,round(sum(yjlx_sl/b.coef),2) as yjlx_sl 
                from  yjlx              
                left join goods_convert b on yjlx_sf=b.province_id and yjlx_wlbh=b.goods_code
                left join department_his c on yjlx_xybm=c.id and yjlx_lxqj=c.yearmonth
                where yjlx_sfcx=1 and b.year={0} and yjlx_lxrq>''{0}'' and yjlx_lxqj=''{1}'' 
                and (yjlx_lx_wlbh=''7003'' or yjlx_lx_wlbh='''')
                group by  yjlx_lxqj,c.sybid,b.new_goods_code ';
    sql= REPLACE(sql, '{0}', curYear);
    sql= REPLACE(sql, '{1}', yearmonth);
    --使用replace 替换比使用拼接简便多了；
    RAISE NOTICE '语句： %',sql;
    execute sql;
    
    --&&插入"特殊人员 "河北张强的招商数据 account_id=8728  dept_id=4499 并且代理类型是终端的
    sql='insert into quota_flag(c1,flow_month,department_id,account_id,goods_code,qty)
                select ''专员''  as  positionname,yjlx_lxqj,yjlx_xybm as department_id,yjlx_tzry as                   account_id,b.new_goods_code,round(sum(yjlx_sl/b.coef),2) as yjlx_sl 
                from  yjlx              
                left join goods_convert b on yjlx_sf=b.province_id and yjlx_wlbh=b.goods_code
                where yjlx_sfcx=1 and b.year={0} and yjlx_lxrq>''{0}'' and yjlx_tzry=8728 and yjlx_xybm=4499 and yjlx_lxqj=''{1}''  and (yjlx_lx_wlbh=''7003'' or yjlx_lx_wlbh='''')
                group by  yjlx_lxqj,yjlx_tzry,yjlx_xybm,b.new_goods_code';
    sql= REPLACE(sql, '{0}', curYear);
    sql= REPLACE(sql, '{1}', yearmonth);
    --使用replace 替换比使用拼接简便多了；
    RAISE NOTICE '语句： %',sql;
    execute sql;            
    --插入当月没有销量的专员指标数据
    sql = 'insert  into quota_flag 
               (account_id,department_id,goods_code,flow_month,c1,quota_qty,quota_ytd)
         select account_id,departmentid,new_goods_code,month ,''专员'',qty,qty
         from view_clientquota_vertical 
         where  (account_id,departmentid,new_goods_code,month) not in (
              select account_id,department_id,goods_code,flow_month from quota_flag where flow_month=''{0}'' )
            and month=''{0}'' and positionname=''学术推广专员''';
     sql= REPLACE(sql, '{0}', yearmonth); 
     RAISE NOTICE '插入当月没有销量的专员语句： %',sql;  
     execute sql;    
    --插入当月没有销量的经理级的数据
    sql='insert  into quota_flag 
               (account_id,department_id,goods_code,flow_month,c1,quota_qty,quota_ytd)
         select account_id,departmentid,new_goods_code,month ,''地区经理'',qty,qty
         from view_clientquota_vertical 
         where  (account_id,departmentid,new_goods_code,month) not in (
              select account_id,department_id,goods_code,flow_month from quota_flag where flow_month=''{0}'' )
            and month=''{0}'' and positionname!=''学术推广专员'' ';
    sql= REPLACE(sql, '{0}', yearmonth);  
    RAISE NOTICE '插入当月没有销量的经理级语句： %',sql; 
    execute sql;
    ---通过clientquota表来更新C1字段为“招商经理”的记录；
    sql = 'update quota_flag set c1=b.positionname from (select distinct account_id,account_name,departmentid,departmentname,positionname from clientquota where year={0} ) as b where quota_flag.account_id=b.account_id and quota_flag.department_id=b.departmentid and c1=''地区经理'' ';
    sql= REPLACE(sql, '{0}', curYear);  
    RAISE NOTICE '更新C1字段为“招商经理”： %',sql; 
    execute sql;
    --插入当月没有销量的quota指标表的数据。但岗位名称很难处理  :(
    
    
    --当月没有销量的省区事业部的数据
    sql='insert  into quota_flag 
               (account_id,department_id,goods_code,flow_month,c1,quota_qty,quota_ytd)
         select account_id,departmentid,new_goods_code,month ,''1'',qty,qty
         from view_quota_vertical 
         where  (account_id,departmentid,new_goods_code,month) not in (
              select account_id,department_id,goods_code,flow_month from quota_flag where flow_month=''{0}'' )
            and month=''{0}'' ';
    sql= REPLACE(sql, '{0}', yearmonth);  
    RAISE NOTICE '插入当月没有销量的省区事业部SQL： %',sql; 
    execute sql;
    
    --更新专员，地区经理当月的指标数量 ；
    sql = 'update quota_flag a set quota_qty = b.qty 
                from view_clientquota_vertical b
                where a.department_id = b.departmentid and a.account_id = b.account_id 
                and a.flow_month = b.month and a.goods_code = b.new_goods_code 
                and flow_month=''{0}'' ';
    sql= REPLACE(sql, '{0}', yearmonth); 
    RAISE NOTICE '更新专员，地区经理当月的指标数量语句： %',sql;
    execute sql;    
    --更新省区，事业部经理当月的指标数量 ；
    sql = 'update quota_flag a set quota_qty = b.qty from view_quota_vertical b
                    where a.department_id = b.departmentid and a.account_id = b.account_id and 
                    a.flow_month = b.month and a.goods_code = b.new_goods_code 
                    and flow_month=''{0}'' ';
    sql= REPLACE(sql, '{0}', yearmonth); 
    RAISE NOTICE '更新省区，事业部经理当月的指标数量语句： %',sql;
    execute sql; 
    --//更新1月的销量数据，指标累计数据 ，参数：当年的第一月；

    if (right(yearmonth,2) = '01') then 
       sql = 'update quota_flag set qty_ytd = qty, quota_ytd = quota_qty where flow_month =''{0}'' ';
       sql= REPLACE(sql, '{0}', yearmonth); 
       RAISE NOTICE '更新1月的销量数据语句： %',sql;
       execute sql; 
    end if;
    
    --更新累计指标；参数：当前月份,注意一个是小于，一个是等于
    sql = 'update quota_flag a set quota_ytd = b.sum_quota_qty
            from(select department_id, account_id, goods_code, c1,sum(quota_qty) as sum_quota_qty from quota_flag 
                    where flow_month <= ''{0}'' group by department_id, account_id, goods_code,c1) as b
            where a.account_id = b.account_id and a.department_id = b.department_id and a.c1=b.c1
                    and a.goods_code = b.goods_code and a.flow_month = ''{0}''';
    
    sql= REPLACE(sql, '{0}', yearmonth); 
    RAISE NOTICE '更新累计指标；参数：当前月份： %',sql;
    execute sql; 
    
    --更新累计销量；参数：当前月份注意一个是小于，一个是等于
    sql = 'update quota_flag a set qty_ytd = b.sum_qty_ytd
                from(select department_id, account_id, goods_code, c1,sum(qty) as sum_qty_ytd 
                from quota_flag where flow_month <= ''{0}''  group by department_id, account_id, goods_code,c1) as b
                where a.account_id = b.account_id and a.department_id = b.department_id and a.c1=b.c1
                    and a.goods_code = b.goods_code and a.flow_month = ''{0}'' ';
    sql= REPLACE(sql, '{0}', yearmonth);
    RAISE NOTICE '更新累计销量；参数：当前月份： %',sql;
    execute sql; 

    --特殊情况：张雷雷 8835   和王孝强  8693,张强 8728的调整
    sql= 'insert into quota_flag(account_id,department_id,goods_code,flow_month,c1,remark,qty,quota_qty,quota_ytd,qty_ytd)
select 8693 ,4496,goods_code,flow_month,''地区经理'',''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where account_id=8693 and flow_month=''{0}''
 group by account_id,goods_code,flow_month
 union all
 select 8835 ,4538,goods_code,flow_month,''地区经理'' ,''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where account_id=8835 and flow_month=''{0}''
 group by account_id,goods_code,flow_month
  union all
 select 8728 ,4498,goods_code,flow_month,''地区经理'' ,''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where account_id=8728 and flow_month=''{0}''
 group by account_id,goods_code,flow_month'
 ;
    sql= REPLACE(sql, '{0}', yearmonth);
    RAISE NOTICE '张雷和王孝强，张强调整；参数：当前月份： %',sql;
    execute sql; 
    
    --删除两人的旧数据
    sql= 'delete from quota_flag where (account_id=8693 or account_id=8835 or account_id=8728) and flow_month=''{0}'' and remark is null;';
    sql= REPLACE(sql, '{0}', yearmonth);
    execute sql; 
     --特殊情况：杨雪琳，周丽琴有3个部门，其中一个（招商）的不取；
      sql= 'insert into quota_flag(account_id,department_id,goods_code,flow_month,c1,remark,qty,quota_qty,quota_ytd,qty_ytd)
select 8790 ,4492,goods_code,flow_month,''地区经理'',''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where account_id=8790 and flow_month=''{0}'' and department_id in (4492,4493)
 group by account_id,goods_code,flow_month
 union all 
select 8866 ,4479,goods_code,flow_month,''地区经理'',''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where account_id=8866 and flow_month=''{0}'' and department_id in (4479,4480)
 group by account_id,goods_code,flow_month ';
     sql= REPLACE(sql, '{0}', yearmonth);
     RAISE NOTICE '杨雪琳，周丽琴调整；参数：当前月份： %',sql;
     execute sql; 
   sql = 'delete from quota_flag where account_id in (8790,8866) and department_id in (4492,4493,4479,4480)
 and flow_month=''{0}'' and remark is null';
    sql= REPLACE(sql, '{0}', yearmonth);
    execute sql; 
    
     --特殊情况：鲁军华的调整
    sql= 'insert into quota_flag(account_id,department_id,goods_code,flow_month,c1,remark,qty,quota_qty,quota_ytd,qty_ytd)
select 8797 ,4384,goods_code,flow_month,''省区经理'',''合并'' ,
sum(qty) as qty,sum(quota_qty) as quota_qty,sum(quota_ytd) as quota_ytd ,sum(qty_ytd) as qty_ytd 
 from quota_flag where department_id in(4446,4447,4448,4449 ) and flow_month=''{0}'' 
 and c1=''地区经理''
 group by goods_code,flow_month' ;
    sql= REPLACE(sql, '{0}', yearmonth);
    RAISE NOTICE '鲁军华调整；参数：当前月份： %',sql;
    execute sql; 
    
    --删除鲁军华的旧数据
    sql= 'delete from quota_flag where (department_id=4384 or account_id=8797 ) and flow_month=''{0}'' and remark is null';
    sql= REPLACE(sql, '{0}', yearmonth);
    RAISE NOTICE '鲁军华调整；参数：当前月份： %',sql;
    execute sql; 
    
    -- 更新完成标志；
    sql='update quota_flag set finished=1 where qty_ytd>=quota_ytd and quota_ytd>0 and flow_month = ''{0}''  ';
    sql= REPLACE(sql, '{0}', yearmonth);
    RAISE NOTICE '更新完成标志；参数：当前月份： %',sql;
    execute sql; 
    --插入专员产品销售为0的数据
    sql='insert into quota_flag(account_id,department_id,goods_code,flow_month,c1)
select id ,department_id,label,''{0}'',''专员'' from account 
CROSS JOIN (
  SELECT ''2004'' AS label
  UNION ALL
  SELECT ''1002''
) AS labels
where position=''学术推广专员'' and statecode=''0''
and (id,label) not in (select account_id,goods_code from quota_flag where flow_month=''{0}'') ';
sql= REPLACE(sql, '{0}', yearmonth);
execute sql; 
    RAISE NOTICE '全部执行OK!!:%',sql;
  return '0';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100