CREATE PROCEDURE [DBO].[SP_BA_YBJK00] @QSRQ00 DATETIME , @ZZRQ00 DATETIME , @CZBZ00 VARCHAR(1), @JFFS00 VARCHAR(600) WITH ENCRYPTION
AS
DECLARE @LSH000		NUMERIC(10)
DECLARE @ZYH000		VARCHAR(20)
DECLARE @ZYCS00		NUMERIC(5)
DECLARE @ZYLSH0		VARCHAR(16)
DECLARE @CARDNO		VARCHAR(20)
DECLARE @YYBAH0		VARCHAR(16)
DECLARE @RYKB00		VARCHAR(20)
DECLARE @ZKKB00		VARCHAR(20)
DECLARE @CYKB00		VARCHAR(20)
DECLARE @RYQK00		VARCHAR(10)
DECLARE @BLZD00		VARCHAR(200)
DECLARE @ZZYSXM		VARCHAR(8)
--诊断
DECLARE @ZDLB00		VARCHAR(1)
DECLARE @JBBM00		VARCHAR(10)
DECLARE @JBMC00		VARCHAR(80)
DECLARE @ZLXG00		VARCHAR(20)
--手术
DECLARE @SSLB00		VARCHAR(1)
DECLARE @SSM000		VARCHAR(10)
DECLARE @SSMC00		VARCHAR(60)
DECLARE @SSRQ00		DATETIME
DECLARE @MZFS00		VARCHAR(16)
--其他
DECLARE @RYZDQK		VARCHAR(200)
DECLARE @CYZD00		VARCHAR(10)
DECLARE @ZDMC00		VARCHAR(60)	--
DECLARE @CYQK00		VARCHAR(1)
DECLARE @CYZD01		VARCHAR(10)
DECLARE @ZDMC01		VARCHAR(60)	--
DECLARE @CYQK01		VARCHAR(1)
DECLARE @CYZD02		VARCHAR(10)
DECLARE @ZDMC02		VARCHAR(60)	--
DECLARE @CYQK02		VARCHAR(1)
DECLARE @CYZD03		VARCHAR(10)
DECLARE @ZDMC03		VARCHAR(60)	--
DECLARE @CYQK03		VARCHAR(1)
DECLARE @CYZD04		VARCHAR(10)
DECLARE @ZDMC04		VARCHAR(60)	--
DECLARE @CYQK04		VARCHAR(1)
DECLARE @CYZD05		VARCHAR(10)
DECLARE @ZDMC05		VARCHAR(60)	--
DECLARE @CYQK05		VARCHAR(1)
DECLARE @SSBH01		VARCHAR(10)
DECLARE @SSMC01		VARCHAR(60)	--
DECLARE @SSRQ01		VARCHAR(8)
DECLARE @MZFS01		VARCHAR(2)		
DECLARE @SSBH02		VARCHAR(10)
DECLARE @SSMC02		VARCHAR(60)	--
DECLARE @SSRQ02		VARCHAR(8)
DECLARE @MZFS02		VARCHAR(2)		
DECLARE @SSBH03		VARCHAR(10)
DECLARE @SSMC03		VARCHAR(60)	--
DECLARE @SSRQ03		VARCHAR(8)
DECLARE @MZFS03		VARCHAR(2)		
DECLARE @SSBH04		VARCHAR(10)
DECLARE @SSMC04		VARCHAR(60)	--
DECLARE @SSRQ04		VARCHAR(8)
DECLARE @MZFS04		VARCHAR(2)
DECLARE @SSBH05		VARCHAR(10)	--
DECLARE @SSMC05		VARCHAR(60)	--
DECLARE @SSRQ05		VARCHAR(8)	--
DECLARE @MZFS05		VARCHAR(2)	--	
DECLARE @SSBH06		VARCHAR(10)	--
DECLARE @SSMC06		VARCHAR(60)	--
DECLARE @SSRQ06		VARCHAR(8)	--
DECLARE @MZFS06		VARCHAR(2)	--	
DECLARE @SSBH07		VARCHAR(10)	--
DECLARE @SSMC07		VARCHAR(60)	--
DECLARE @SSRQ07		VARCHAR(8)	--
DECLARE @MZFS07		VARCHAR(2)	--	
DECLARE @SSBH08		VARCHAR(10)	--
DECLARE @SSMC08		VARCHAR(60)	--
DECLARE @SSRQ08		VARCHAR(8)	--
DECLARE @MZFS08		VARCHAR(2)	--	

	

DECLARE @FSRQ00		VARCHAR(8)
DECLARE @FSSJ00		VARCHAR(8)	

DECLARE @I00000		NUMERIC(10)
DECLARE @ZYID00		NUMERIC(12)

DECLARE @I INT
DECLARE @J INT
DECLARE @K INT

DECLARE @S VARCHAR(40)
IF @CZBZ00='1' BEGIN
 	DELETE FROM BA_YBJKXX WHERE EXISTS(SELECT 1 FROM BA_BRZYXX WHERE BA_BRZYXX.LSH000=BA_YBJKXX.LSH000 
 						AND CYRQ00>=@QSRQ00 AND CYRQ00<@ZZRQ00
 					) AND CGBZ00<>'1'
END
CREATE TABLE ##JFFS00(JFFS00 varchar(40) null)
SET @I=1
SET @K=1
WHILE @I<=LEN(@JFFS00) BEGIN
 IF SUBSTRING(@JFFS00,@I,1)=',' BEGIN
 INSERT INTO ##JFFS00 SELECT SUBSTRING(@JFFS00,@K+1,@I-@K-2)
 SET @K=@I+1
 END
 SET @I=@I+1 
END
INSERT INTO ##JFFS00 SELECT SUBSTRING(@JFFS00,@K+1,@I-@K-2)
--定义基本信息
DECLARE	CUR_BRZYXX CURSOR FOR 
  SELECT T1.ZYID00,T1.LSH000,ZYH000,ZYCS00,BAH000,RYKBMC,ZKKBMC,CYKBMC,
			 (CASE RYQK00 WHEN '危重' THEN '1' WHEN '急诊' THEN '2' WHEN '一般' THEN '3' ELSE '3' END) as RYQK00,BLZD00,T2.ZZYSMC,T1.CARDNO,T1.ZYLSH0
		 FROM BA_BRZYXX T1,BA_YSXX00 T2
		 WHERE T1.LSH000*=T2.LSH000 AND CYRQ00>=@QSRQ00 AND CYRQ00<@ZZRQ00
		 		AND T1.JFFS00 IN (SELECT JFFS00 FROM ##JFFS00) --='社会基本医疗保险'
		 		AND EXISTS(SELECT 1 FROM BA_BRZDXX T4 WHERE T1.LSH000=T4.LSH000)
		 		AND NOT EXISTS(SELECT 1 FROM BA_YBJKXX T3 WHERE T1.LSH000=T3.LSH000)
OPEN CUR_BRZYXX
FETCH CUR_BRZYXX INTO @ZYID00,@LSH000,@ZYH000,@ZYCS00,@YYBAH0,@RYKB00,@ZKKB00,@CYKB00,@RYQK00,@BLZD00,@ZZYSXM,@CARDNO,@ZYLSH0
WHILE (@@FETCH_STATUS=0) BEGIN
	--定义诊断信息
	SET @I00000=1
	SET @RYZDQK=''
	SET @CYZD00=''
	SET @ZDMC00=''
	SET @CYQK00=''
	SET @CYZD01=''
	SET @ZDMC01=''
	SET @CYQK01=''
	SET @CYZD02=''
	SET @ZDM
C02=''
	SET @CYQK02=''
	SET @CYZD03=''
	SET @ZDMC03=''
	SET @CYQK03=''
	SET @CYZD04=''
	SET @ZDMC04=''
	SET @CYQK04=''
	SET @CYZD05=''
	SET @ZDMC05=''
	SET @CYQK05=''

	DECLARE CUR_BRZDXX CURSOR FOR SELECT ZDLB00,JBBM00,JBMC00,ZLXG00 FROM BA_BRZDXX WHERE LSH000=@LSH000
	OPEN CUR_BRZDXX
	FETCH CUR_BRZDXX INTO @ZDLB00,@JBBM00,@JBMC00,@ZLXG00
	WHILE (@@FETCH_STATUS=0) BEGIN
		--出院诊断
		IF @ZDLB00='3' BEGIN
			SET @CYZD00=@JBBM00
			SET @ZDMC00=@JBMC00
			SET @CYQK00=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
		END ELSE IF @ZDLB00='1' BEGIN
		--入院诊断
			IF @RYZDQK='' SET @RYZDQK=@JBMC00+'-'+@JBBM00
		END ELSE BEGIN
		--其他诊断
			IF @I00000=1 BEGIN
				SET @CYZD01=@JBBM00
				SET @ZDMC01=@JBMC00
				SET @CYQK01=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
			END
			IF @I00000=2 BEGIN
				SET @CYZD02=@JBBM00
				SET @ZDMC02=@JBMC00
				SET @CYQK02=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
			END
			IF @I00000=3 BEGIN
				SET @CYZD03=@JBBM00
				SET @ZDMC03=@JBMC00
				SET @CYQK03=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
			END
			IF @I00000=4 BEGIN
				SET @CYZD04=@JBBM00
				SET @ZDMC04=@JBMC00
				SELECT @CYQK04=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
			END
			IF @I00000=5 BEGIN
				SET @CYZD05=@JBBM00
				SET @ZDMC05=@JBMC00
				SET @CYQK05=CASE @ZLXG00 WHEN '治愈' THEN '1' WHEN '好转' THEN '2' WHEN '未愈' THEN '3' WHEN '死亡' THEN '4' WHEN '24小时死亡' THEN '4' ELSE '5' END
			END
			SET @I00000=@I00000+1
		END
		
		FETCH CUR_BRZDXX INTO @ZDLB00,@JBBM00,@JBMC00,@ZLXG00
	END
	CLOSE CUR_BRZDXX
	DEALLOCATE CUR_BRZDXX
	SET @SSBH01=''
	SET @SSMC01=''
	SET @SSRQ01=''
	SET @MZFS01=''
	SET @SSBH02=''
	SET @SSMC02=''
	SET @SSRQ02=''
	SET @MZFS02=''
	SET @SSBH03=''
	SET @SSMC03=''
	SET @SSRQ03=''
	SET @MZFS03=''
	SET @SSBH04=''
	SET @SSMC04=''
	SET @SSRQ04=''
	SET @MZFS04=''
	SET @SSBH05=''
	SET @SSMC05=''
	SET @SSRQ05=''
	SET @MZFS05=''
	SET @SSBH06=''
	SET @SSMC06=''
	SET @SSRQ06=''
	SET @MZFS06=''
	SET @SSBH07=''
	SET @SSMC07=''
	SET @SSRQ07=''
	SET @MZFS07=''
	SET @SSBH08=''
	SET @SSMC08=''
	SET @SSRQ08=''
	SET @MZFS08=''

	SET @I00000=1
	--定义手术信息
	DECLARE CUR_BRSSXX CURSOR FOR SELECT SSLB00,SSM000,SSMC00,SSRQ00,MZFS00 FROM BA_BRSSXX WHERE LSH000=@LSH000
	OPEN CUR_BRSSXX
	FETCH CUR_BRSSXX INTO @SSLB00,@SSM000,@SSMC00,@SSRQ00,@MZFS00
	WHILE (@@FETCH_STATUS=0) BEGIN
		IF @I00000=1 BEGIN
			SET @SSBH01=@SSM000
			SET @SSMC01=@SSMC00
			SET @SSRQ01=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS01=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=2 BEGIN
			SET @SSBH02=@SSM000
			SET @SSMC02=@SSMC00
			SET @SSRQ02=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS02=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=3 BEGIN
			
SET @SSBH03=@SSM000
			SET @SSMC03=@SSMC00
			SET @SSRQ03=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS03=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=4 BEGIN
			SET @SSBH04=@SSM000
			SET @SSMC04=@SSMC00
			SET @SSRQ04=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS04=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=5 BEGIN
			SET @SSBH05=@SSM000
			SET @SSMC05=@SSMC00
			SET @SSRQ05=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS05=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=6 BEGIN
			SET @SSBH06=@SSM000
			SET @SSMC06=@SSMC00
			SET @SSRQ06=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS06=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=7 BEGIN
			SET @SSBH07=@SSM000
			SET @SSMC07=@SSMC00
			SET @SSRQ07=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS07=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		IF @I00000=8 BEGIN
			SET @SSBH08=@SSM000
			SET @SSMC08=@SSMC00
			SET @SSRQ08=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10),@SSRQ00,120),'.',''),'-',''),'/','')
			SET @MZFS08=CASE @MZFS00 WHEN '全麻' THEN '01' WHEN '颈丛' THEN '02'
						 WHEN '静吸腹合麻醉' THEN '03' WHEN '气管麻' THEN '04'
						 WHEN '插管全麻' THEN '05' WHEN '吸入麻醉' THEN '06'
						 WHEN '表麻' THEN '07' WHEN '局麻' THEN '08'
						 WHEN '硬膜外' THEN '09' WHEN '腰麻' THEN '10'
						 WHEN '静脉麻醉' THEN '11' WHEN '基础麻' THEN '12'
						 WHEN '基础麻+局麻' THEN '13' WHEN '骶麻' THEN '14'
						 WHEN '臂丛' THEN '15' ELSE '16' END
		END 
		
		
		SET @I00000=@I00000+1
		FETCH CUR_BRSSXX INTO @SSLB00,@SSM000,@SSMC00,@SSRQ00,@MZFS00
	END
	CLOSE CUR_BRSSXX
	DEALLOCATE CUR_BRSSXX
	SET @FSRQ00=RTRIM(REPLACE(REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-',''),'.',''))
	SET @FSSJ00=REPLACE(REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),'-',''),'.','')
	--插入数据
	INSERT INTO BA_YBJKXX(
		LSH000,ZYLSH0,YYBAH0,RYKB00,ZKKB00,CYKB00,
		RYQK00,RYZDQK,CYZD00,ZDMC00,CYQK00,CYZD01,
		ZDMC01,CYQK01,CYZD02,ZDMC02,CYQK02,C
YZD03,
		ZDMC03,CYQK03,CYZD04,ZDMC04,CYQK04,CYZD05,ZDMC05,
		CYQK05,BLZD00,ZZYSXM,SSBH01,SSMC01,SSRQ01,
		MZFS01,SSBH02,SSMC02,SSRQ02,MZFS02,SSBH03,
		SSMC03,SSRQ03,MZFS03,SSBH04,SSMC04,SSRQ04,
		MZFS04,SSBH05,SSMC05,SSRQ05,MZFS05,SSBH06,
		SSMC06,SSRQ06,MZFS06,SSBH07,SSMC07,SSRQ07,
		MZFS07,SSBH08,SSMC08,SSRQ08,MZFS08,FSRQ00,
		FSSJ00,CGBZ00,ZYH000,ZYCS00,CARDNO)
	VALUES(	@LSH000,@ZYLSH0,@YYBAH0,@RYKB00,@ZKKB00,@CYKB00,
		@RYQK00,@RYZDQK,@CYZD00,@ZDMC00,@CYQK00,@CYZD01,
		@ZDMC01,@CYQK01,@CYZD02,@ZDMC02,@CYQK02,@CYZD03,
		@ZDMC03,@CYQK03,@CYZD04,@ZDMC04,@CYQK04,@CYZD05,@ZDMC05,
		@CYQK05,@BLZD00,@ZZYSXM,@SSBH01,@SSMC01,@SSRQ01,
		@MZFS01,@SSBH02,@SSMC02,@SSRQ02,@MZFS02,@SSBH03,
		@SSMC03,@SSRQ03,@MZFS03,@SSBH04,@SSMC04,@SSRQ04,
		@MZFS04,@SSBH05,@SSMC05,@SSRQ05,@MZFS05,@SSBH06,
		@SSMC06,@SSRQ06,@MZFS06,@SSBH07,@SSMC07,@SSRQ07,
		@MZFS07,@SSBH08,@SSMC08,@SSRQ08,@MZFS08,@FSRQ00,
		@FSSJ00,'',@ZYH000,@ZYCS00,@CARDNO)
	FETCH CUR_BRZYXX INTO @ZYID00,@LSH000,@ZYH000,@ZYCS00,@YYBAH0,@RYKB00,@ZKKB00,@CYKB00,@RYQK00,@BLZD00,@ZZYSXM,@CARDNO,@ZYLSH0
END	
CLOSE CUR_BRZYXX
DEALLOCATE CUR_BRZYXX
DROP TABLE ##JFFS00