-- Start of DDL Script for Function SD_HOSPITAL.SF_ZZQH_GETQHXX
-- Generated 2019/4/29 16:55:47 from SD_HOSPITAL@CLZSK

CREATE OR REPLACE 
function sf_zzqh_getqhxx
(
  PBRID00  in    number,     --传入病人id
  PYFBMBH  in    varchar     --传入参与排号的药房编号窜 每个编号之间用逗号隔开
)
return varchar2 is
-- MODIFICATION HISTORY
-- Person      Date         Comments
-- zhangyc     2014.01.01  重新整理过程
-- ----------  ----------   ----------------------------------------------------------------------     
  TmpStr    varchar2(1000);    --255
  Ls_xsxx   varchar2(1000);    --255
  ls_xsxh00 varchar2(200);     --100
  lBrxm00   varchar2(40);--病人姓名
  I         number(5);
  J         number(5);
  L         number(5);
  v_COUNT   number(5);
  TmpCKMC   varchar(20);
  ls_YFBMBH BM_BMBM00.BMBH00%TYPE; --部门编号
  ls_BMMC00 BM_BMBM00.BMMC00%TYPE; --部门名称
  MZHS_CYPHYF       XT_XTCS00.VALUE0%type; --参与排号药房
  MZPY_JZHSFZJFY    XT_XTCS00.VALUE0%type; --排号后是否直接发药
  ZZQH_XHXSFS       XT_XTCS00.VALUE0%type; --序号显示方式, 0:最小序号 1:所有序号
  ZZQH_JRPDJHCFBLTS XT_XTCS00.VALUE0%type; --默认为0则是当天的处方
  ls_COUNT  number(3,0);
begin
	--取参数
--	begin
--	select trim(VALUE0) into MZHS_CYPHYF from XT_XTCS00 where NAME00='MZHS_CYPHYF';
	--exception
--	  when others then
	    --MZHS_CYPHYF:='N';
  --end;
  
  MZHS_CYPHYF:=pYFBMBH;
  
	--取参数
	begin
		select trim(VALUE0) into ZZQH_JRPDJHCFBLTS from XT_XTCS00 where NAME00='ZZQH_JRPDJHCFBLTS';
	exception
	  when others then
	    ZZQH_JRPDJHCFBLTS:='0';
  end;  

	begin
		select trim(VALUE0) into MZPY_JZHSFZJFY from XT_XTCS00 where NAME00='MZPY_JZHSFZJFY';
	exception
	  when others then
	    MZPY_JZHSFZJFY:='N';
  end;  

	begin
		select trim(VALUE0) into ZZQH_XHXSFS from XT_XTCS00 where NAME00='ZZQH_XHXSFS';
	exception
	  when others then
	    ZZQH_XHXSFS:='0';
  end;  
  
  
  TmpStr:=' ';
  if MZHS_CYPHYF<>'N' then
      --I,记录出现'*'的上一次的位置,
      --J,计数器,
      --L,记录出现'*'的当前位置
    MZHS_CYPHYF:=trim(MZHS_CYPHYF)||',';
    I:=0;    
    J:=1;      
    SELECT  INSTR(MZHS_CYPHYF,',',1,J)  INTO  L  FROM  DUAL;  --取出第J次出现','的位置
        
    WHILE  L>0  LOOP 
            --取出参数组合中的药房部门编号                
      LS_YFBMBH:=TO_NUMBER(SUBSTR(MZHS_CYPHYF,I+1,L-I-1));
      
      select nvl(MAX(BMMC00),' ') into ls_BMMC00 from BM_BMBM00 where BMBH00=LS_YFBMBH;
      
      select count(*) into ls_COUNT  from BM_CKBM00 a,BM_CKBM00 b,BM_CKDY00 c where a.FLAG00='2' and b.FLAG00='3'
      and a.CKBH00=c.SCKBH0 and b.CKBH00=c.DCKBH0 and a.BMBH00=LS_YFBMBH and b.BMBH00=LS_YFBMBH
      and a.ZT0000='0' and b.ZT0000='0' ;   
      
      Ls_xsxx:=' ';
      
      if (ls_BMMC00<>' ') and (ls_COUNT>0) then
         
        for v_xsxx in (
          select CKBH00,CKMC00,min(FYDLXH) ZXXH00,brxm00,COUNT(*) CFS000 from (
            select b.CKMC00,b.CKBH00,A.FYDLXH,c.brxm00
               from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,YF_CFDL00 A,BM_CKBM00 B
               where C.BRID00=pbrid00 and C.YFBMBH=LS_YFBMBH 
               and ((((C.CFZT00 = '1'  and not exists (select 1 from YF_MZCF00 where BCCFH0=c.CFLSH0))
                   or (C.CFZT00='2' and MZPY_JZHSFZJFY='Y' 
                       and not exists (select 1 from YF_CFDL00 where CFLSH0=C.CFLSH0  and ZT0000='3')))
                   --and C.SRRQ00>=TO_CHAR(SYSDATE-1/3-ZZQH_JRPDJHCFBLTS,'YYYYMMDD')
                   and C.SRRQ00>=TO_CHAR(SYSDATE-1/3,'YYYYMMDD')
                   and C.BCCFH0 IS NULL))
               and C.GHKS00=M.BMBH00
               and C.CZY000=G.YGBH00
               and C.SFKSLY='N'
               and C.CFLSH0=A.CFLSH0 and a.FYCKBH=b.CKBH00 and nvl(a.FYCKBH,'0')<>'0' AND NVL(to_char(a.FYDLXH),'')<>'未排'  --and a.phbz00>='1'
               )
          group by BRXM00,CKBH00,CKMC00 order by CKBH00) loop
          lBrxm00:=v_xsxx.brxm00;
          ls_xsxh00:=' ';
          if ZZQH_XHXSFS='1' then            
            for v_xsxh in ( select distinct a.fydlxh from YF_MZCF00 C,BM_BMBM00 M,BM_YGBM00 G,YF_CFDL00 A,BM_CKBM00 B
               where C.BRID00=pbrid00 and C.YFBMBH=LS_YFBMBH 
               and ((((C.CFZT00 = '1'  and not exists (select 1 from YF_MZCF00 where BCCFH0=c.CFLSH0))
                   or (C.CFZT00='2' and MZPY_JZHSFZJFY='Y' 
                       and not exists (select 1 from YF_CFDL00 where CFLSH0=C.CFLSH0  and ZT0000='3')))
                   --and C.SRRQ00>=TO_CHAR(SYSDATE-1/3-ZZQH_JRPDJHCFBLTS,'YYYYMMDD')
                   and C.SRRQ00>=TO_CHAR(SYSDATE-1/3,'YYYYMMDD')
                   and C.BCCFH0 IS NULL))
               and C.GHKS00=M.BMBH00
               and C.CZY000=G.YGBH00
               and C.SFKSLY='N'
               and C.CFLSH0=A.CFLSH0 and a.FYCKBH=b.CKBH00 and nvl(a.FYCKBH,0)<>0 AND NVL(to_char(a.FYDLXH),'')<>'未排'  --and a.phbz00>='1'
               and a.FYCKBH=v_xsxx.CKBH00 ) loop
               if ls_xsxh00=' ' then
                  ls_xsxh00:=to_char(v_xsxh.FYDLXH);
               else
                  ls_xsxh00:=ls_xsxh00||'、'||to_char(v_xsxh.FYDLXH);
               end if;
             end loop;
          else
            ls_xsxh00:=to_char(v_xsxx.zxxh00);
          end if;
          
          TmpCKMC := '';
          if instr(v_xsxx.CKMC00,'窗')<=0 then
            TmpCKMC := '号窗口';
          end if;
          	  
          if  ls_xsxx=' ' then
             --ls_xsxx:=RPAD(ls_BMMC00,10,' ')||' '||RPAD(v_xsxx.CKMC00,8,' ')||' '||RPAD(to_char(v_xsxx.CFS000)||'张处方',8,' ')||RPAD('序号:'||ls_xsxh00,16,' ');
             ls_xsxx:='您已经打印过['||ls_BMMC00||']的取药凭证！';  --'['||lBrxm00||']
             ls_xsxx:=ls_xsxx||chr(13)||rpad(' ',10,' ')||' '||RPAD(v_xsxx.CKMC00||TmpCKMC,8,' ')||' '||RPAD(to_char(v_xsxx.CFS000)||'张处方',8,' ')||RPAD('序号:'||ls_xsxh00,16,' ');
          else
             ls_xsxx:=ls_xsxx||chr(13)||rpad(' ',10,' ')||' '||RPAD(v_xsxx.CKMC00||TmpCKMC,8,' ')||' '||RPAD(to_char(v_xsxx.CFS000)||'张处方',8,' ')||RPAD('序号:'||ls_xsxh00,16,' ');
          end if;
        end loop;
        if ls_xsxx<>' ' then
          if TmpStr=' ' then
            TmpStr:=ls_xsxx;
          else
           TmpStr:=TmpStr||chr(13)||ls_xsxx; 
          end if ;
        end if;
      end if;      
      I:=L;
      J:=J+1;
      SELECT  INSTR(MZHS_CYPHYF,',',1,J)  INTO  L  FROM  DUAL;  --取出第J次出现','的位置
    end loop; 
  end if;
  
  if tMPsTR=' ' then
     TmpStr:='-1';
  end if;
  return TmpStr;
end ;
/

-- Grants for Function
GRANT EXECUTE ON sf_zzqh_getqhxx TO sst
/


-- End of DDL Script for Function SD_HOSPITAL.SF_ZZQH_GETQHXX