存储过程统计数据

–功能说明：统计各模块各单位年度指标情况
–输入参数：
–p_year 统计年度
–输出参数：
– js_jhzs 计划完成总数
– js_jhwcs 完成数
– js_jhwclv 完成率
– js_zdjhzs 重点计划总数
– js_zdwcs 重点完成数
– js_zdjhwclv 重点完成率
– js_zgs 整改数
– js_pcs 排查数
– js_zglv 整改完成率

procedure prc_fetchjsjddata(p_year in varchar2) is
var_year varchar2(4);
num_jhwcs number(8);
num_jhzs number(8);
num_jhwclv number(8, 2);
num_zdjhwclv number(8, 2);
num_zdwcs number(8);
num_zdjhzs number(8);
num_zglv number(8, 2);
num_zgs number(8);
num_pcs number(8);
num_count number(2);

cursor cur_jczz is
  select b.*, o.dwjb, o.ssbk
    from eam_xt_gg_bizorg b, eam_xt_auth_org o
   where b.orgid = o.orgid
     and b.delflag = 0;
rec_jczz cur_jczz%rowtype;

begin
var_year := p_year;
num_jhwcs := 0;
num_jhzs := 0;
num_jhwclv := 0;
num_zdjhwclv := 0;
num_zdwcs := 0;
num_zdjhzs := 0;
num_zglv := 0;
num_zgs := 0;
num_pcs := 0;
num_count := 0;

-- 如果传进来年度为空，默认当前年度
if var_year is null then
  var_year := to_char(sysdate, 'yyyy');
end if;
for rec_jczz in cur_jczz loop
  if rec_jczz.dwjb = 2 or rec_jczz.dwjb is null then
    SELECT A.ZS jhzs,
           A.YWC jhwcs,
           decode(A.ZS, 0, 0, round(A.YWC * 100 / A.ZS, 2)) jhwclv,
           B.ZDZS zdjhzs,
           B.ZDYWC zdwcs,
           decode(B.ZDZS, 0, 0, round(B.ZDYWC * 100 / B.ZDZS, 2)) zdjhwclv,
           C.ZGZS pcs,
           C.ZGYWC zgs,
           decode(C.ZGZS, 0, 0, round(C.ZGYWC * 100 / C.ZGZS, 2)) zglv
      into num_jhzs,
           num_jhwcs,
           num_jhwclv,
           num_zdjhzs,
           num_zdwcs,
           num_zdjhwclv,
           num_pcs,
           num_zgs,
           num_zglv
      FROM (select COUNT(T.GID) ZS,
                   sum(DECODE(STATE, '99', 1, '510', 1, 0)) YWC
              from EAM_XT_TASK_LISTS t
             WHERE to_char(t.PANFINISHDT, 'YYYY') = var_year
               AND T.SUBBUSCLASSID = '01'
               AND T.PGID IS NULL
               AND T.ZRDWID in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and dwjb = 2
                       and delflag = 0
                     start with orgid = rec_jczz.orgid
                    connect by prior orgid = parentorgid)) A,
           (select COUNT(T.GID) ZDZS,
                   sum(DECODE(STATE, '99', 1, '510', 1, 0)) ZDYWC
              from EAM_XT_TASK_LISTS t
             WHERE to_char(t.PANFINISHDT, 'YYYY') = var_year
               AND T.SUBBUSCLASSID = '02'
               AND T.PGID IS NULL
               AND T.DELFLAG = 0
               AND T.ZRDWID in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and dwjb = 2
                       and delflag = 0
                     start with orgid = rec_jczz.orgid
                    connect by prior orgid = parentorgid)) B,
           (select COUNT(a.GID) ZGZS,
                   sum(DECODE(a.STATE, '99', 1, 0)) ZGYWC
              from eam_qts_jdwtzg a
             where a.DELFLAG = 0
               and to_char(a.reccreatedt, 'yyyy') = var_year
               and a.zgdw in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and dwjb = 2
                       and delflag = 0
                     start with orgid = rec_jczz.orgid
                    connect by prior orgid = parentorgid)) C
     WHERE 1 = 1;
  else
    SELECT A.ZS jhzs,
           A.YWC jhwcs,
           decode(A.ZS, 0, 0, round(A.YWC * 100 / A.ZS, 2)) jhwclv,
           B.ZDZS zdjhzs,
           B.ZDYWC zdwcs,
           decode(B.ZDZS, 0, 0, round(B.ZDYWC * 100 / B.ZDZS, 2)) zdjhwclv,
           C.ZGZS pcs,
           C.ZGYWC zgs,
           decode(C.ZGZS, 0, 0, round(C.ZGYWC * 100 / C.ZGZS, 2)) zglv
      into num_jhzs,
           num_jhwcs,
           num_jhwclv,
           num_zdjhzs,
           num_zdwcs,
           num_zdjhwclv,
           num_pcs,
           num_zgs,
           num_zglv
      FROM (select COUNT(T.GID) ZS,
                   sum(DECODE(STATE, '99', 1, '510', 1, 0)) YWC

              from EAM_XT_TASK_LISTS t
             WHERE to_char(t.PANFINISHDT, 'YYYY') = var_year
               AND T.SUBBUSCLASSID = '01'
               AND T.PGID IS NULL
               AND T.DELFLAG = 0
               AND T.ZRDWID in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and (dwjb = 2 or dwjb is null)
                       and delflag = 0
                     start with orgid in
                                (select orgid
                                   from eam_xt_gg_bizorg
                                  where delflag = 0
                                    and orgid <> rec_jczz.orgid
                                  start with orgid = rec_jczz.orgid
                                 connect by prior gid = parentorgid)
                    connect by prior orgid = parentorgid)) A,
           (select COUNT(T.GID) ZDZS,
                   sum(DECODE(STATE, '99', 1, '510', 1, 0)) ZDYWC
              from EAM_XT_TASK_LISTS t
             WHERE to_char(t.PANFINISHDT, 'YYYY') = var_year
               AND T.SUBBUSCLASSID = '02'
               AND T.PGID IS NULL
               AND T.DELFLAG = 0
               AND T.ZRDWID in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and (dwjb = 2 or dwjb is null)
                       and delflag = 0
                     start with orgid in
                                (select orgid
                                   from eam_xt_gg_bizorg
                                  where delflag = 0
                                    and orgid <> rec_jczz.orgid
                                  start with orgid = rec_jczz.orgid
                                 connect by prior gid = parentorgid)
                    connect by prior orgid = parentorgid)) B,
           (select COUNT(a.GID) ZGZS,
                   sum(DECODE(a.STATE, '99', 1, 0)) ZGYWC
              from eam_qts_jdwtzg a
             where a.DELFLAG = 0
               and to_char(a.reccreatedt, 'yyyy') = var_year
               and a.zgdw in
                   (select orgid
                      from eam_xt_auth_org
                     where orgtype = 0
                       and isww = 0
                       and (dwjb = 2 or dwjb is null)
                       and delflag = 0
                     start with orgid in
                                (select orgid
                                   from eam_xt_gg_bizorg
                                  where delflag = 0
                                    and orgid <> rec_jczz.orgid
                                  start with orgid = rec_jczz.orgid
                                 connect by prior gid = parentorgid)
                    connect by prior orgid = parentorgid)) C
     WHERE 1 = 1;
  end if;
  select count(1)
    into num_count
    from eam_ajh_maindata
   where orgid = rec_jczz.orgid
     and year = var_year;
  -- 不存在当年记录则插入
  if num_count = 0 then
    insert into eam_ajh_maindata
      (orgid,
       year,
       fetchdate,
       js_jhzs,
       js_jhwcs,
       js_jhwclv,
       js_zdjhzs,
       js_zdwcs,
       js_zdjhwclv,
       js_zgs,
       js_pcs,
       js_zglv,
       delflag)
    values
      (rec_jczz.orgid,
       var_year,
       sysdate,
       num_jhzs,
       num_jhwcs,
       num_jhwclv,
       num_zdjhzs,
       num_zdwcs,
       num_zdjhwclv,
       num_zgs,
       num_pcs,
       num_zglv,
       0);
  else
    -- 存在则更新
    update eam_ajh_maindata
       set js_jhzs      = num_jhzs,
           js_jhwcs     = num_jhwcs,
           js_jhwclv    = num_jhwclv,
           js_zdjhzs    = num_zdjhzs,
           js_zdwcs     = num_zdwcs,
           js_zdjhwclv  = num_zdjhwclv,
           js_zgs       = num_zgs,
           js_pcs       = num_pcs,
           js_zglv      = num_zglv,
           lastupdatedt = sysdate
     where orgid = rec_jczz.orgid
       and year = var_year;
  end if;
end loop;
commit;

exception
when others then
rollback;
end prc_fetchjsjddata;