CREATE OR REPLACE PROCEDURE SP_SS_KSGZLTJ 
--工作量统计
(
 LD_QSRQ00	in	varchar2	,--统计开始日期
 LD_JSRQ00	in	varchar2	,--统计结束日期	
 LD_SSKS00	in	number		 --手术科室
)
AS
  LS_BMBH00	VW_SS_YW0000.SSKS00%type	;
  LS_BMMC00	VW_SS_YW0000.SSKSMC%type	;
  ptdss00	ss_ksgzl0.tdsssl%type		;--特大手术
  pdss000	ss_ksgzl0.dsssl0%type		;--大手术
  pzss000	ss_ksgzl0.zsssl0%type		;--中手术
  pxss000	ss_ksgzl0.xsssl0%type		;--小手术
  pjzss00	ss_ksgzl0.jzsssl%type		;--急诊手术
  ptsypss ss_ksgzl0.tsypsl%type		;--特殊药品手术数量
  pssf000	ss_ksgzl0.ssf000%type		;--手术费
  pmzssf0   ss_ksgzl0.ssf000%type		;--门诊手术费
  pzlf000	ss_ksgzl0.zlf000%type		;--治疗费
  
  phjsl00	ss_ksgzl0.hjsl00%type		;--合计数量
  LS_SFSL00	NUMBER(5);
  v_SSLSSF char(1)              ;--手术例数算法
  	
  CURSOR CUR_SSBM00 IS 				--手术部门
--    select distinct SQKS00
--  from VW_SS_YW0000 a where SSZT00 in ('4','2','0') and (SSKS00=LD_SSKS00 or SSKS00 IS NULL)
--      AND SSQSRQ>=LD_QSRQ00 AND SSQSRQ<=LD_JSRQ00;       
	SELECT distinct b.kdks00 SQKS00
	FROM ZY_FYMX00 B
	WHERE	B.CZRQ00 BETWEEN LD_QSRQ00 AND LD_JSRQ00 
	AND B.ZXKS00=LD_SSKS00; 
  CURSOR CUR_SSSQBM IS 				--申请部门
--    select distinct SQKS00
--  from VW_SS_YW0000 a where SSZT00 in ('4','2','0') and (SSKS00=LD_SSKS00 or SSKS00 IS NULL)
--      AND SSQSRQ>=LD_QSRQ00 AND SSQSRQ<=LD_JSRQ00;       
	SELECT distinct A.SQKS00 SQKS00
	FROM ss_yw0000 A
	where A.sszt00 = '4'
    and A.SSQSRQ BETWEEN LD_QSRQ00 AND LD_JSRQ00
	AND A.SSKS00=LD_SSKS00;    		
CURSOR CUR_SSLX00 IS 		  --手术类型
   select BH0000,MC0000 from BM_TYZD00 where zdmc00='手术类型';	
      
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- yangy       2008.05.06  手术室收入统计
-- yangy       2008.06.12  手术室统计大中小手术的个数
-- chenqw      2009.12.16   增加手术例数算法v_SSLSSF=3 
-- chenqw      2009.12.17   修改v_SSLSSF=3时无手术费的问题
-- dsm         2012.04.17   南平微创手术统计分开 for SSMZ_20120416-003
-- dsm         2012.07.05   合计例数要用d.SSLB00不能用b.SSLB00 for SSMZ-20120705-001
BEGIN
	select substrb(nvl(trim(upper(max(Value0))),'0'),1,1) into v_SSLSSF from XT_XTCS00 where name00='ZH_SSLSSF';  
	
	if v_SSLSSF <> '3' then
	--begin
	  Delete SS_KSGZL0;
	  FOR LXVR IN CUR_SSLX00 LOOP
      OPEN CUR_SSBM00;              
        
        LOOP                        
          FETCH CUR_SSBM00 INTO LS_BMBH00;   --遍历所有手术部门
          EXIT WHEN (CUR_SSBM00%NOTFOUND);
          
	  			select count(1) INTO ptdss00 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
		  		where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
			  	and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
				  and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
				  and ((b.cxdjh0=0) or (b.cxdjh0 is null)) --and d.ssdjmc='特大'
				  and exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.zlxmid and i.SSDJMC='四级')
				  and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3') and d.SSLB00=to_number(LXVR.BH0000);

  				select count(1) INTO pdss000 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
	  			--bm_yysfxm e, bm_fpxm00 f 
		  		where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
			  	and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
  				and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
	  			and ((b.cxdjh0=0) or (b.cxdjh0 is null)) --and d.ssdjmc='大'
		  		and exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.zlxmid and i.SSDJMC='三级')				
			  	and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 				  
				  and b.XMZT00 in ('2','4','3') and d.SSLB00=to_number(LXVR.BH0000);				
		     
				  select count(1) INTO pzss000 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
				  --bm_yysfxm e, bm_fpxm00 f 
  				where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
	  			and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
		  		and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
			  	and ((b.cxdjh0=0) or (b.cxdjh0 is null)) --and d.ssdjmc='中'
				  and exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.zlxmid and i.SSDJMC='二级')					
				  and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3') and d.SSLB00=to_number(LXVR.BH0000);	
			
			  	select count(1) INTO pxss000 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
		  		--bm_yysfxm e, bm_fpxm00 f 
			  	where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
  				and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
	  			and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
		  		and ((b.cxdjh0=0) or (b.cxdjh0 is null)) --and d.ssdjmc='小'
			  	and exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.zlxmid and i.SSDJMC='一级')					
			  	and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3') and d.SSLB00=to_number(LXVR.BH0000);	 	
				
			   	select count(1) INTO pjzss00 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
				  --bm_yysfxm e, bm_fpxm00 f 
				  where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
				  and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
				  and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
				  and ((b.cxdjh0=0) or (b.cxdjh0 is null)) and d.JZBZ00='Y'
				  and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3') and d.SSLB00=to_number(LXVR.BH0000);					

  				select count(1) INTO ptsypss from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
	  			--bm_yysfxm e, bm_fpxm00 f 
		  		where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
			  	and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
				  and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
				  and ((b.cxdjh0=0) or (b.cxdjh0 is null)) and 	d.SFTSYP='Y'
				  and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3')  and d.SSLB00=to_number(LXVR.BH0000);			

				  select count(1) INTO phjsl00 from vw_ss_ywjj00 b, ss_ssyjd0 c, vw_ss_yw0000 d
				  --bm_yysfxm e, bm_fpxm00 f 
				  where b.YJDJH0=c.YJDJH0 and c.ssdh00=d.ssdh00 and b.kdksbh=LS_BMBH00
				  and b.YJKSBH = LD_SSKS00 and d.ssqsrq between LD_QSRQ00 and LD_JSRQ00
				  and (not exists (select 'y' from yj_yw0000 a where a.cxdjh0=b.yjdjh0))
				  and ((b.cxdjh0=0) or (b.cxdjh0 is null))
				  and b.kdksbh not in (select bmbh00 from vw_bm_ssmzbm)
				  --and b.sfxmid=e.sfxmid and e.zyfpid=f.FPXMID and trim(f.xmmc00)='手术费' 
				  and b.XMZT00 in ('2','4','3')   and d.SSLB00=to_number(LXVR.BH0000)	   --原来b.SSLB00  改称d.SSLB00
           and exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.zlxmid )	;
				  
				  SELECT nvl(SUM(B.HJJE00),0) into pssf000
				  FROM ZY_FYMX00 B
				  WHERE  B.KDKS00=LS_BMBH00
				  AND B.CZRQ00 BETWEEN LD_QSRQ00 AND LD_JSRQ00 
				  AND B.KDKS00 not in (select bmbh00 from vw_bm_ssmzbm) AND B.ZXKS00=LD_SSKS00 
				  and nvl(b.SSLB00,0)=to_number(LXVR.BH0000);
				          

				  SELECT nvl(SUM(B.HJJE00),0) into pzlf000
				  FROM ZY_FYMX00 B
				  WHERE  B.KDKS00=LS_BMBH00
				  AND B.CZRQ00 BETWEEN LD_QSRQ00 AND LD_JSRQ00 
				  AND B.KDKS00 in (select bmbh00 from vw_bm_ssmzbm) AND B.ZXKS00=LD_SSKS00  
				  and nvl(b.SSLB00,0)=to_number(LXVR.BH0000);
     
          insert into SS_KSGZL0(SSLBMC,SSKS00,TDSSSL,DSSSL0,ZSSSL0,XSSSL0,JZSSSL,HJSL00,TSYPSL,SSF000,ZLF000) 
          	VALUES(LXVR.MC0000,LS_BMBH00,ptdss00,pdss000,pzss000,pxss000,pjzss00,phjsl00,ptsypss,pssf000,pzlf000);
        END LOOP;                
      CLOSE CUR_SSBM00;
    end loop;
  END IF;  
  if v_SSLSSF = '3' then
	begin
    OPEN CUR_SSSQBM;              
      Delete SS_KSGZL0;
      LOOP                        
        FETCH CUR_SSSQBM INTO LS_BMBH00;   --遍历所有手术部门
        EXIT WHEN (CUR_SSSQBM%NOTFOUND);
        
        
        select count(1) INTO ptdss00 from vw_ss_yw0000 b where    
        exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.SSBH00 and i.SSDJMC='四级')
				and sszt00 = '4'
				and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
				and ssqsrq between LD_QSRQ00 and LD_JSRQ00;
				
		select count(1) INTO pdss000  from vw_ss_yw0000 b where    
        exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.SSBH00 and i.SSDJMC='三级')
				and sszt00 = '4'
				and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
				and ssqsrq between LD_QSRQ00 and LD_JSRQ00;
				
		select count(1) INTO pzss000  from vw_ss_yw0000 b where    
        exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.SSBH00 and i.SSDJMC='二级')
				and sszt00 = '4'
				and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
				and ssqsrq between LD_QSRQ00 and LD_JSRQ00;	
				
		select count(1) INTO pxss000  from vw_ss_yw0000 b where    
        exists (select 1 from bm_zlzd00 h, bm_ssdj00 i where h.ssdjbh=i.ssdjbh
										and h.zlxmid=b.SSBH00 and i.SSDJMC='一级')
				and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
				and sszt00 = '4'
				and ssqsrq between LD_QSRQ00 and LD_JSRQ00;	
				
		select count(1) INTO pjzss00  from vw_ss_yw0000 b where    
        b.JZBZ00='Y'
		and sszt00 = '4'
		and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
		and ssqsrq between LD_QSRQ00 and LD_JSRQ00;	
				
		select count(1) INTO ptsypss  from vw_ss_yw0000 b where    
        b.SFTSYP='Y'
		and sszt00 = '4'
		and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
		and ssqsrq between LD_QSRQ00 and LD_JSRQ00;						
         				    					
		select count(1) INTO phjsl00  from vw_ss_yw0000 b where    
		sszt00 = '4'
		and b.SQKS00=LS_BMBH00 and b.SSKS00 = LD_SSKS00
		and ssqsrq between LD_QSRQ00 and LD_JSRQ00;	
        
        SELECT SUM(B.HJJE00) into pssf000
		FROM ZY_BRFY00 A,ZY_FYMX00 B,ss_yw0000 C, yj_yw0000 D,SS_SSYJD0 E,BM_FPXM00 F,BM_YYSFXM G
		WHERE A.DJH000=B.DJH000 AND A.djh000= D.sfdjh0 AND D.yjdjh0=E.yjdjh0 
        AND E.ssdh00 = C.ssdh00 AND B.xmbh00=G.sfxmid AND G.zyfpid=f.fpxmid and f.bh0000='11'
        and C.SQKS00=LS_BMBH00 and C.SSKS00 = LD_SSKS00	 and c.sszt00='4'
		AND C.SSQSRQ BETWEEN LD_QSRQ00 AND LD_JSRQ00; 
		
        SELECT SUM(B.HJJE00) into pmzssf0
		FROM SF_BRFY00 A,SF_FYMX00 B,ss_yw0000 C, yj_yw0000 D,SS_SSYJD0 E,BM_FPXM00 F,BM_YYSFXM G
		WHERE A.DJH000=B.DJH000 AND A.djh000= D.sfdjh0 AND D.yjdjh0=E.yjdjh0 
        AND E.ssdh00 = C.ssdh00 AND B.xmbh00=G.sfxmid AND G.mzfpid=f.fpxmid and f.bh0000='11'
        and C.SQKS00=LS_BMBH00 and C.SSKS00 = LD_SSKS00	 and c.sszt00='4'
		AND C.SSQSRQ BETWEEN LD_QSRQ00 AND LD_JSRQ00;
		
		pssf000:= nvl(pssf000,0)+nvl(pmzssf0,0);
       	

				SELECT SUM(B.HJJE00) into pzlf000
				FROM ZY_BRFY00 A,ZY_FYMX00 B
				WHERE A.DJH000=B.DJH000 AND B.KDKS00=LS_BMBH00
				AND B.CZRQ00 BETWEEN LD_QSRQ00 AND LD_JSRQ00 
				AND B.ZXKS00=LD_SSKS00; 		

				--group by c.XMMC00,c.FPXMID
 					       	
        --phjsl00 := ptdss00 + pdss000 + pzss000 + pxss000;         
        insert into SS_KSGZL0(SSKS00,TDSSSL,DSSSL0,ZSSSL0,XSSSL0,JZSSSL,HJSL00,TSYPSL,SSF000,ZLF000) 
        	VALUES(LS_BMBH00,ptdss00,pdss000,pzss000,pxss000,pjzss00,phjsl00,ptsypss,pssf000,pzlf000);
      END LOOP;                
    CLOSE CUR_SSSQBM;
    end;
  END IF; 
                  
END SP_SS_KSGZLTJ;
/