CREATE OR REPLACE PROCEDURE SP_JY_ZKGZPD
(
   PZKBBPH in varchar2,
   PYBH000 in varchar2,
   PBGXMID in number,
   PKSRQ00 in varchar2,
   PJSRQ00 in varchar2,
   PZKGZSM out varchar2  --质控规则说明
)
as
  type type_array is table of varchar2(200) index by binary_integer;
  var_arrzkjg type_array;
  --i number(3);
  i number(3);
  j number(3);
  k number(3);
  lslsxh number(3);
  lsSFXK varchar2(3);
  lsCount number(3);
  lvZKCS00 number(5);
  lsZKGZSM varchar2(4000);
  aa varchar2(4000);
  --lsZKGZSM blob(4000);
  ls_error varchar2(500);

  type ZKGZ_array is table of JY_ZKGZB0.ZKGZID%type index by binary_integer;
  var_arrZKGZ ZKGZ_array;
  type SKCS_array is table of number(5) index by binary_integer;
  var_arrSKCS SKCS_array;
  type SKSM_array is table of varchar2(300) index by binary_integer;
  var_arrSKSM SKSM_array;
  lvSKCS00 number(5);
  cursor cur_JYZKSJB  is select YBH000,ZXRQ00,ZKBBPH,XH0000,BGXMID,DYBZC0,DYBZ00,ZKJG00
                         from VW_JY_ZKJG00  where ybh000=PYBH000 and ZKBBPH=PZKBBPH  and bgxmid=PBGXMID and sfyx00='Y'
                         and YQDH00=substr(PYBH000,1,2)  and ZXRQ00 between PKSRQ00 and PJSRQ00 and zkjg00 is not null
                         order by zxrq00,XH0000;

  cursor cur_JYZKXX is select a.GZLXID,a.JCGZLX,a.JCGZSM,a.LXJGS0,a.YJJGS0,a.XBZC00,a.YBZC00,a.ZKGZSM,a.ZKGZMC,a.ZKGZID
                       from VW_JY_ZKGZXX a ,VW_JY_ZKBBCS b where a.zkgzid=b.zkgzid and b.bgxmid=PBGXMID
                       and b.ybh000=PYBH000 and b.ZKBBPH=PZKBBPH and b.YQDH00=substr(PYBH000,1,2)
                       and exists (select 1 from XT_XTCS00 where name00='JY_ZKZPJQZFS' and nvl(trim(value0),'0')='0')
                       union all
                       select a.GZLXID,a.JCGZLX,a.JCGZSM,a.LXJGS0,a.YJJGS0,a.XBZC00,a.YBZC00,a.ZKGZSM,a.ZKGZMC,a.ZKGZID
                       from VW_JY_ZKGZXX a ,VW_JY_ZKBBCS b,JY_YQGZDY c where b.bgxmid=PBGXMID
                       and b.ybh000=PYBH000 and b.ZKBBPH=PZKBBPH and b.YQDH00=substr(PYBH000,1,2)
                       and b.YQDH00=c.YQDH00 and b.ZKLX00=c.ZKLX00 and a.zkgzid=c.zkgzid
                       and exists (select 1 from XT_XTCS00 where name00='JY_ZKZPJQZFS' and nvl(trim(value0),'0')='1');

--Person           Date              Comments
--chenyw           2013.06.13        创建检验质控结果是否失控判断过程   for LIS-20130114-005
--daihq            2016.11.13        增加出参质控规则说明PZKGZSM
--daihq            2016.11.14        参数JY_ZKZPJQZFS控制，增加按仪器规则对应表使用哪些规则判断是否失控
begin
  --  1-3s规则
  i:=0;
  j:=0;
  k:=0;
  lvZKCS00 := 0;
  lvSKCS00 := 0;

  for V_JYZKSJB in cur_JYZKSJB loop
    i:=i+1;
    lvZKCS00 := lvZKCS00 + 1;
    var_arrzkjg(i):=V_JYZKSJB.ZKJG00;
    update jy_zkjg00 set SFSK00='N',WFGZMC=null,SKSM00=null  where ybh000=V_JYZKSJB.YBH000 and zxrq00=V_JYZKSJB.ZXRQ00  and  ZKBBPH=V_JYZKSJB.ZKBBPH
                                     and XH0000=V_JYZKSJB.XH0000   and bgxmid=V_JYZKSJB.BGXMID;
                                       ls_error:='1@';
    for var_JYZKXX in cur_JYZKXX loop
      --N-xS[连续N个结果超过x倍标准差]
      if var_JYZKXX.GZLXID=1 then
        if (i>=var_JYZKXX.LXJGS0) then
           lsSFXK:='Y';
           for lslsxh in i-var_JYZKXX.LXJGS0+1..i loop
               if ((to_number(var_arrzkjg(lslsxh))<V_JYZKSJB.DYBZ00+var_JYZKXX.XBZC00*V_JYZKSJB.DYBZC0) and  (to_number(var_arrzkjg(lslsxh))>V_JYZKSJB.DYBZ00-var_JYZKXX.XBZC00*V_JYZKSJB.DYBZC0))  then
                   lsSFXK:='N';
               end if;
            end loop;
            if lsSFXK='Y' then
              k:=0;
              while ((j>0) and (k<j)) loop --判断数组中是否已经存在此质控规则
                k := k + 1;
                exit when var_arrZKGZ(k)=var_JYZKXX.ZKGZID;
                if k=j then
                  k := k + 1;
                end if;
              end loop;
              if (k=0) or ((k>0) and (k>j)) then --数组不存在此质控规则
                j := j + 1;
                var_arrZKGZ(j) := var_JYZKXX.ZKGZID;
                var_arrSKCS(j) := 1;
                var_arrSKSM(j) := '【'||trim(var_JYZKXX.ZKGZMC)||'】,'||trim(var_JYZKXX.ZKGZSM);
              else
                var_arrSKCS(k) := var_arrSKCS(k) + 1;
              end if;
              update jy_zkjg00 set SFSK00='Y',WFGZMC=WFGZMC||' '||var_JYZKXX.JCGZLX,SKSM00=SKSM00||chr(13)||chr(10)||' '||var_JYZKXX.JCGZSM   where ybh000=V_JYZKSJB.YBH000
                                       and zxrq00=V_JYZKSJB.ZXRQ00 and  ZKBBPH=V_JYZKSJB.ZKBBPH and bgxmid=V_JYZKSJB.bgxmid and XH0000=V_JYZKSJB.XH0000;
            end if;
        end if;
      end if ;
        ls_error:='2@';
      --R-xS[同批两个质控结果之差超过x倍标准差]
      if var_JYZKXX.GZLXID=2 then
        if (i>=2) then
            lsSFXK:='N';
            if abs(to_number(var_arrzkjg(i))-to_number(var_arrzkjg(i-1)))>=var_JYZKXX.XBZC00*V_JYZKSJB.DYBZC0 then
                    lsSFXK:='Y';
            end if;
            if lsSFXK='Y' then
              k:=0;
              while ((j>0) and (k<j)) loop --判断数组中是否已经存在此质控规则
                k := k + 1;
                exit when var_arrZKGZ(k)=var_JYZKXX.ZKGZID;
                if k=j then
                  k := k + 1;
                end if;
              end loop;
              if (k=0) or ((k>0) and (k>j)) then --数组不存在此质控规则
                j := j + 1;
                var_arrZKGZ(j) := var_JYZKXX.ZKGZID;
                var_arrSKCS(j) := 1;
                var_arrSKSM(j) := '【'||trim(var_JYZKXX.ZKGZMC)||'】,'||trim(var_JYZKXX.ZKGZSM);
              else
                var_arrSKCS(k) := var_arrSKCS(k) + 1;
              end if;
              update jy_zkjg00 set SFSK00='Y',WFGZMC=WFGZMC||' '||var_JYZKXX.JCGZLX,SKSM00=SKSM00||chr(13)||chr(10)||' '||var_JYZKXX.JCGZSM  where ybh000=V_JYZKSJB.YBH000
                                        and zxrq00=V_JYZKSJB.ZXRQ00 and  ZKBBPH=V_JYZKSJB.ZKBBPH and bgxmid=V_JYZKSJB.bgxmid and XH0000=V_JYZKSJB.XH0000;
            end if;
        end if;
      end if;
        ls_error:='3@';
      --N-T[连续N个结果升或下贱趋势]
      if var_JYZKXX.GZLXID=3 then
       if (i>=var_JYZKXX.LXJGS0) then
           lsSFXK:='Y';
           if to_number(var_arrzkjg(i))-to_number(var_arrzkjg(i-1))>0 then
              for lslsxh in i+2-var_JYZKXX.LXJGS0..i-1 loop
               if to_number(var_arrzkjg(lslsxh))-to_number(var_arrzkjg(lslsxh-1))<=0 then
                   lsSFXK:='N';
               end if;
              end loop;
           elsif to_number(var_arrzkjg(i))-to_number(var_arrzkjg(i-1))<0 then
              for lslsxh in i-3..i-1 loop
               if to_number(var_arrzkjg(lslsxh))-to_number(var_arrzkjg(lslsxh-1))>=0 then
                   lsSFXK:='N';
               end if;
              end loop;
           end if;
             ls_error:='4@';
           if lsSFXK='Y' then
             k:=0;
             while ((j>0) and (k<j)) loop --判断数组中是否已经存在此质控规则
               k := k + 1;
               exit when var_arrZKGZ(k)=var_JYZKXX.ZKGZID;
               if k=j then
                  k := k + 1;
               end if;
             end loop;
             if (k=0) or ((k>0) and (k>j)) then --数组不存在此质控规则
               j := j + 1;
               var_arrZKGZ(j) := var_JYZKXX.ZKGZID;
               var_arrSKCS(j) := 1;
               var_arrSKSM(j) := '【'||trim(var_JYZKXX.ZKGZMC)||'】,'||trim(var_JYZKXX.ZKGZSM);
             else
               var_arrSKCS(k) := var_arrSKCS(k) + 1;
             end if;
             update jy_zkjg00 set SFSK00='Y',WFGZMC=WFGZMC||' '||var_JYZKXX.JCGZLX,SKSM00=SKSM00||chr(13)||chr(10)||' '||var_JYZKXX.JCGZSM  where ybh000=V_JYZKSJB.YBH000
                                       and zxrq00=V_JYZKSJB.ZXRQ00 and  ZKBBPH=V_JYZKSJB.ZKBBPH and bgxmid=V_JYZKSJB.bgxmid and XH0000=V_JYZKSJB.XH0000;
           end if;
        end if;
      end if;
        ls_error:='5@';
      --N-X[连续N个结果在平均数一侧]
      if var_JYZKXX.GZLXID=4 then
        if (i>=var_JYZKXX.LXJGS0) then
            lsSFXK:='Y';
            if to_number(V_JYZKSJB.zkjg00)-V_JYZKSJB.DYBZ00>=0 then
               for lslsxh in i+1-var_JYZKXX.LXJGS0..i loop
                    if to_number(var_arrzkjg(lslsxh))-V_JYZKSJB.DYBZ00<0  then
                        lsSFXK:='N';
                    end if;
                 end loop;
            else
                for lslsxh in i+1-var_JYZKXX.LXJGS0..i loop
                    if to_number(var_arrzkjg(lslsxh))-V_JYZKSJB.DYBZ00>0  then
                        lsSFXK:='N';
                    end if;
                 end loop;
            end if ;
            if lsSFXK='Y' then
               k:=0;
               while ((j>0) and (k<j)) loop --判断数组中是否已经存在此质控规则
                 k := k + 1;
                 exit when var_arrZKGZ(k)=var_JYZKXX.ZKGZID;
                 if k=j then
                   k := k + 1;
                 end if;
               end loop;
               if (k=0) or ((k>0) and (k>j)) then --数组不存在此质控规则
                 j := j + 1;
                 var_arrZKGZ(j) := var_JYZKXX.ZKGZID;
                 var_arrSKCS(j) := 1;
                 var_arrSKSM(j) := '【'||trim(var_JYZKXX.ZKGZMC)||'】,'||trim(var_JYZKXX.ZKGZSM);
               else
                 var_arrSKCS(k) := var_arrSKCS(k) + 1;
               end if;
               update jy_zkjg00 set SFSK00='Y',WFGZMC=WFGZMC||' '||var_JYZKXX.JCGZLX,SKSM00=SKSM00||chr(13)||chr(10)||' '||var_JYZKXX.JCGZSM  where ybh000=V_JYZKSJB.YBH000
                                       and zxrq00=V_JYZKSJB.ZXRQ00 and  ZKBBPH=V_JYZKSJB.ZKBBPH and bgxmid=V_JYZKSJB.bgxmid and XH0000=V_JYZKSJB.XH0000;
            end if;
        end if;
      end if;
   ls_error:='6@';
      --(M of N)xS[l连续N个结果中,有M个结果超过x倍标准差]
      if var_JYZKXX.GZLXID=5 then
      ls_error:='611@';
        if (i>=var_JYZKXX.LXJGS0) then
           lsSFXK:='N';
           ls_error:='612@';
            if abs(to_number(var_arrzkjg(i))-V_JYZKSJB.DYBZ00)>=V_JYZKSJB.DYBZC0 then
              lsCount:=0;
              for lslsxh in i-var_JYZKXX.LXJGS0+1..i-1 loop
               if abs(to_number(var_arrzkjg(lslsxh))-V_JYZKSJB.DYBZ00)>=V_JYZKSJB.DYBZC0 then
                   lsCount:=lsCount+1;
               end if;
              end loop;
              ls_error:='613@';
              if lscount>=var_JYZKXX.YJJGS0 then
                lsSFXK:='Y';
              end if;
            end if;
            ls_error:='61@';
           if lsSFXK='Y' then
             k:=0;
             while ((j>0) and (k<j)) loop --判断数组中是否已经存在此质控规则
               k := k + 1;
               exit when var_arrZKGZ(k)=var_JYZKXX.ZKGZID;
               if k=j then
                  k := k + 1;
               end if;
             end loop;

             if (k=0) or ((k>0) and (k>j)) then --数组不存在此质控规则
               j := j + 1;
               var_arrZKGZ(j) := var_JYZKXX.ZKGZID;
               var_arrSKCS(j) := 1;
               var_arrSKSM(j) := '【'||trim(var_JYZKXX.ZKGZMC)||'】,'||trim(var_JYZKXX.ZKGZSM);
             else
               var_arrSKCS(k) := var_arrSKCS(k) + 1;
             end if;
             ls_error:='63@';
             update jy_zkjg00 set SFSK00='Y',WFGZMC=WFGZMC||' '||var_JYZKXX.JCGZLX,SKSM00=SKSM00||chr(13)||chr(10)||' '||var_JYZKXX.JCGZSM  where ybh000=V_JYZKSJB.YBH000
                                       and zxrq00=V_JYZKSJB.ZXRQ00 and  ZKBBPH=V_JYZKSJB.ZKBBPH and bgxmid=V_JYZKSJB.bgxmid and XH0000=V_JYZKSJB.XH0000;
             ls_error:='7@';
           end if;
        end if;
      end if;

    end loop;

  end loop;
  while j>0 loop
    if trim(lsZKGZSM)||'A'='A' then
      lsZKGZSM := to_char(var_arrSKCS(j))||'次违反质控规则'||var_arrSKSM(j)||'。';
    else
      lsZKGZSM := lsZKGZSM||chr(10)||to_char(var_arrSKCS(j))||'次违反质控规则'||var_arrSKSM(j)||'。';
    end if;
    lvSKCS00 := lvSKCS00 + var_arrSKCS(j);
    j := j - 1;
  end loop;
    ls_error:='8@';
  if trim(lsZKGZSM)||'A'='A' then
  ls_error:='81@';
    PZKGZSM := '这段时间'||to_char(lvZKCS00)||'次质控中，未出现违反质控规则现象，系统运行稳定！';
    ls_error:='82@';
  else
  ls_error:='83@';

    aa := '这段时间'||to_char(lvZKCS00)||'次质控中，累计违反质控规则'||to_char(lvSKCS00)||'次，具体如下：'||chr(10)||lsZKGZSM;
    ls_error:=TO_CHAR(length(aa));
   -- PZKGZSM :='这段时间'||to_char(lvZKCS00)||'次质控中，累计违反质控规则'||to_char(lvSKCS00)||'次，具体如下：'||chr(10)||lsZKGZSM;
    PZKGZSM :=SUBSTR('这段时间'||to_char(lvZKCS00)||'次质控中，累计违反质控规则'||to_char(lvSKCS00)||'次，具体如下：'||lsZKGZSM,1,200);
    ls_error:='222222222222@';
              ls_error:='84@';
  end if;
  commit;
exception
    when others then
        raise_application_error(-20020, ls_error||substrb(NVL(sqlerrm, '原因不明出错！*'),1,200));
end;