-- Start of DDL Script for Function SD_HOSPITAL.SF_SF_GETCKBH
-- Generated 2008/12/06 11:37:56 from SD_HOSPITAL@dyhis

CREATE OR REPLACE 
FUNCTION sf_sf_getckbh(ID NUMBER,SFCK NUMBER,BZ NUMBER,CFDGS CHAR)
RETURN  NUMBER IS
  ls_PYCKBH   YF_CFDL00.PYCKBH%TYPE;   --配药窗口对应的编码
  ls_FYCKBH   YF_CFDL00.FYCKBH%TYPE;   --发药窗口对应的编码
  ls_CKBH00   YF_CFDL00.FYCKBH%TYPE;
  ls_CFDGS0   YF_CFDL00.CFDGS0%TYPE;   --处方单格式
  ls_YPDLBH   YF_CFDL00.YPDLBH%TYPE;   --处方大类编号
  ls_yyid00   XT_YYXX00.YYID00%TYPE;
  ls_Count    Number(5);
-- MODIFICATION HISTORY      
-- Person      Date        Comments
-- zhr         2007.08.06  BM_CKBM00增加字段cfdgs0，计算发往窗口时要考虑本窗口是否有发该格式药品，窗口处方格式为空表示可发所有格式的处方
-- zhr         2007.11.03  BM_CKBM00增加字段ypdlbh，计算发往窗口时要考虑本窗口是否有发该类药品，窗口处方类别为空表示可发所有类型的处方
-- zhr         2008.02.29  优化语句写法 select PYCKBH,COUNT(*)NUM  from YF_CFDL00 where ZT0000 in ('0','1','2')  GROUP BY PYCKBH
-- qks         2008.07.14  广东省中医院: 1、当西区配药窗口开放的时候，东区交费的开单科室为简易门诊的病人的处方要发往西区配药窗口。其余的科室开出的处方按窗口流程设置分配配药窗口。
--                                       2、当节假日，西区配药窗口不开的时候，就按窗口流程设置分配配药窗口。
--                                       3、所有的处方，都按平均分配的原则分配配药窗口 
-- qks         2008.08.08  2008.07.14改的基础上：不包括颗粒药房 MZSF-20080808-001
begin
  ls_CFDGS0:=substrb(CFDGS,1,1);
  if instrb(CFDGS,',')>0 then
     ls_YPDLBH:=substrb(CFDGS,3,1);
  end if;
    
  ls_PYCKBH:= 0;
  ls_FYCKBH:= 0;
  ls_CKBH00:= 0;
  
  IF  BZ = 1 and Instrb(CFDGS,'60036')>0 and (ID<>60035) THEN   --广东中医院简易门诊 计算配药窗口  不包括颗粒药房 
    select count(*) into ls_Count from xt_yyxx00 where flag00='1' and yyjc00='大院' and yyqc00='广东省中医院';
    if ls_Count>0 then 
      if ls_YPDLBH='2' then --草药
        BEGIN
          select  CKBH00 into ls_PYCKBH  from
           (select  B.CKBH00,nvl(NUM,0),B.ZT0000 from BM_CKBM00 B,
           (select PYCKBH,COUNT(*)NUM  from YF_CFDL00 
              where  ZT0000 in ('0','1','2')  
                and  PYCKBH   in (6000000248,6000000252,6000000258) 
                GROUP BY PYCKBH) C
              where  B.FLAG00 = '2' and  B.ZT0000 = '0'
                and  B.CKBH00 in (6000000248,6000000252,6000000258) 
                and  B.CKBH00 = C.PYCKBH(+)
                and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0 
                and  instrb(nvl(B.YPDLBH,'0,1,2,3,4'),nvl(ls_YPDLBH,'0'))>0
           order by B.ZT0000,nvl(NUM,0))
           where  rownum = 1;
        EXCEPTION
           WHEN OTHERS THEN
              ls_PYCKBH:=0;
        END;
      else  --西成药
      	BEGIN
          select  CKBH00 into ls_PYCKBH  from
           (select  B.CKBH00,nvl(NUM,0),B.ZT0000 from BM_CKBM00 B,
           (select PYCKBH,COUNT(*)NUM  from YF_CFDL00 
              where  ZT0000 in ('0','1','2')  
                and  PYCKBH   in (6000000440,6000000268,6000000272,6000000438)
                GROUP BY PYCKBH) C
              where  B.FLAG00 = '2' and  B.ZT0000 = '0'
                and  B.CKBH00 in (6000000440,6000000268,6000000272,6000000438)
                and  B.CKBH00 = C.PYCKBH(+)
                and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0 
                and  instrb(nvl(B.YPDLBH,'0,1,2,3,4'),nvl(ls_YPDLBH,'0'))>0
           order by B.ZT0000,nvl(NUM,0))
           where  rownum = 1;
        EXCEPTION
           WHEN OTHERS THEN
              ls_PYCKBH:=0;
        END;
      end if;  
      IF ls_PYCKBH<>0 then
        RETURN ls_PYCKBH;
      end if;
    end if;
  end if;
  
  if  BZ = 1   THEN  --计算配药窗口
      BEGIN
        select  CKBH00 into ls_PYCKBH  from
         (select  B.CKBH00,nvl(NUM,0),B.ZT0000 from BM_CKDY00 A, BM_CKBM00 B,
         (select PYCKBH,COUNT(*)NUM  from YF_CFDL00 where ZT0000 in ('0','1','2')  GROUP BY PYCKBH) C
            where  A.DCKBH0 = B.CKBH00  and  B.FLAG00 = '2'  --and B.ZT0000='0',='0'是开机状态
              and (A.SCKBH0 = SFCK  or SFCK = 0)
              and  B.CKBH00 = C.PYCKBH(+)
              and  B.BMBH00 = ID
              and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0 
              and  instrb(nvl(B.YPDLBH,'0,1,2,3,4'),nvl(ls_YPDLBH,'0'))>0 
         order by B.ZT0000,nvl(NUM,0))
         where  rownum = 1;
      EXCEPTION
         WHEN OTHERS THEN
            ls_PYCKBH:=0;
      END;
      ls_CKBH00:=ls_PYCKBH;
  END IF;
  IF  BZ = 2   THEN  --计算发药窗口
      BEGIN
        select  CKBH00 into ls_FYCKBH  from
         (select  B.CKBH00, nvl(NUM,0),B.ZT0000 from BM_CKDY00 A, BM_CKBM00 B,
         (select FYCKBH,COUNT(*)NUM  from YF_CFDL00 where ZT0000 in ('0','1','2') GROUP BY FYCKBH) C
            where  A.DCKBH0 = B.CKBH00  and  B.FLAG00 = '3'  --and B.ZT0000='0',='0'是开机状态
              and (A.SCKBH0 = SFCK  or SFCK = 0)
              and  B.CKBH00 = C.FYCKBH(+)
              and  B.BMBH00 = ID
              and  instrb(nvl(B.CFDGS0,'0,1,2,3,4,5'),nvl(ls_CFDGS0,'0'))>0
              and  instrb(nvl(B.YPDLBH,'0,1,2,3,4'),nvl(ls_YPDLBH,'0'))>0 
         order by B.ZT0000,nvl(NUM,0))
         where  rownum = 1;
      EXCEPTION
         WHEN  OTHERS  THEN
             ls_FYCKBH := 0;
      END;
      ls_CKBH00:=ls_FYCKBH;
  END IF;
  RETURN ls_CKBH00;
EXCEPTION
  when no_data_found then
    raise_application_error(-20001,substr('系统未无法查找到对应的可用的窗口,请记录本信息并与系统管理员联系！*'||sqlerrm,1,220));
    rollback;
  when others        then
    raise_application_error(-20002,substr(nvl(sqlerrm, '原因不明出错')||'！*',1,220));
    rollback;
end;
/



-- End of DDL Script for Function SD_HOSPITAL.SF_SF_GETCKBH