if (exists (select * from sys.objects where name = 'Clinic_Get_TreatmentBck'))
    drop proc Clinic_Get_TreatmentBck

go 
create PROC [dbo].[Clinic_Get_TreatmentBck]
@lXml ntext,--治疗方案列表数据
@bck01d int,--住院科室ID
@rList VARCHAR(8000) output



as
   DECLARE @iDOM int, @iret int
	Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lXml

  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
  END 
--IDENTITY(int,1,1) AS ID,
SELECT  * INTO #TmpBBX1 FROM OpenXml(@iDOM,'/Root/BBX1/Ie',8)
    WITH
     (LISIZE int
      ,BEF01 int
      ,BBX01C int
      ,BBY01 int  
     )	
  If not exists(select * from #TmpBBX1)
  BEGIN
   exec sp_xml_removedocument @iDOM
   RAISERROR('医嘱列表信息为空', 16, 1) with nowait
   RETURN 2
  END
--SELECT * FROM #TmpBBX1
exec sp_xml_removedocument @iDOM


DECLARE @lisize int
,@bef01 int
,@bbx01c int
,@bby01 int
--------部门表字段
,@bck01 INT
,@bck03 VARCHAR(64)
,@lACF01 int

,@aParamno varchar(10)
,@BDN01 varchar(2)
--临时存储xml
,@tmpXml VARCHAR(8000)
set @lACF01 = 2


SET @tmpXml='<Root>'

BEGIN
	DECLARE order_cursor CURSOR FOR select LISIZE,BEF01,BBX01C,BBY01 from #TmpBBX1
	open order_cursor
	FETCH NEXT FROM order_cursor INTO @lisize,@bef01,@bbx01c,@bby01
	
	WHILE @@FETCH_STATUS = 0 --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
		
	begin
--开始循环游标变量
	SET @tmpXml+='<BCK BEF01="'+CONVERT(varchar(10),@bef01)+'" LIZE="'+CONVERT(varchar(2),@lisize)+'">'
	IF @lisize =0
--没值
		BEGIN
				set @bck03 =NULL
				SELECT top 1 @bck01= a.BCK01B,@bck03=BCK03 
				FROM BAV1 a
				join BBX1 b on b.BBX01=a.BBX01 and b.BDA01 not in ('4','M')
				JOIN BCK1 c ON a.BCK01B = c.BCK01
				WHERE a.ACF01 IN (2,3,-1) AND a.BCK01A = @bck01d  AND a.BBX01 = @bbx01c
		--		SELECT @bck03
				IF (@bck03 IS NULL)
				BEGIN
					SELECT TOP 1 @bck01=a.BCK01B,@bck03=b.BCK03
					FROM BAV1 a
					LEFT JOIN BCK1  b ON a.BCK01B = b.BCK01
					WHERE a.ACF01 = @lACF01 AND a.BBX01 = @bbx01c and isnull(a.BCK01A,0)=0
			--		SELECT @bck03
					IF (@bck03 IS NOT NULL)
					BEGIN
						SET @tmpXml+='<EXBCK BCK01="'+CONVERT(varchar(10),@bck01)+'" BCK03="'+@bck03+'"/>'
					END
				END
				ELSE
				BEGIN
					SET @tmpXml+='<EXBCK BCK01="'+CONVERT(varchar(10),@bck01)+'" BCK03="'+@bck03+'"/>'
				END
			
		END
	ELSE
--有值
		BEGIN
--给药科室
			SET @bck03 = NULL
									set @aParamno = dbo.GetSysParamValue(9999,9999,15)
									if @aParamno = '0'
												begin
													set @BDN01 = ISNULL((select BDN01 from BBY1 where BBY01 = @bby01),'1')
													declare @tmpbck table(BCK01 int)
													
													insert into @tmpbck
													select ISNULL(b.Value,a.Value) BCK01
													from SYS_Parameters a
													join SYS_UserParameters b on b.ParamID = a.ID and b.UserID = @bck01d
													where a.ProductID = 100 and ((@lACF01 = 1 and a.ProgramID = 105002 and ((@BDN01 = '1' and a.ParamNo =108)
														 or (@BDN01 = '2' and a.ParamNo =109)or (@BDN01 = '3' and a.ParamNo =110)))
														or (@lACF01 = 2 and a.ProgramID = 105003 and ((@BDN01 = '1' and a.ParamNo =115)
														 or (@BDN01 = '2' and a.ParamNo =116)or (@BDN01 = '3' and a.ParamNo =117)))
													 )
													select @bck01=BCK01, @bck03=BCK03 
													from (
													select top 1 e.BCK01,e.BCK03,ISNULL(f.BCK01,0) rownr 
													from BBJ1 a 
													join BAI1 b on (b.BCK01 = a.BCK01B or b.BCK01 = 0) and (b.BAI03 = 0 or b.BAI03 = datepart(dw, getdate()))
													join BCO1 c on c.BCO02 = b.BCO02
													join BCK1 e on e.BCK01 = a.BCK01B
													left join @tmpbck f on f.BCK01 = e.BCK01
													where a.BBY01 = @bby01 and a.BCK01A = @bck01d and b.ACF01 in (@lACF01,3) and a.ACF01 in (@lACF01,3)
														and (
														 (c.BCO03 <= c.BCO04 and c.BCO03 <= convert(char(8), getdate(), 108) and c.BCO04 >= convert(char(8), getdate(), 108))
														or (c.BCO03 >= c.BCO04 and (c.BCO03 <= convert(char(8), getdate(), 108) or c.BCO04 >= convert(char(8), getdate(), 108)))
														)
														and exists(select * from BAZ1 d where d.BCK01 = a.BCK01B and d.ACF01 in (3,@lACF01)
																			and d.BAU01 in ('51','52','53'))   ) m
													order by m.rownr desc               
												end
									if @aParamno = '1'
												begin
													select @bck01=a.BCK01,@bck03=a.BCK03 
													from BCK1 a
													join BAI1 b on b.BCK01 = a.BCK01 and (b.BAI03 = 0 or b.BAI03 = datepart(dw, getdate()))
													join BCO1 c on c.BCO02 = b.BCO02  
													where b.ACF01 in (@lACF01,3)
														and (
														 (c.BCO03 <= c.BCO04 and c.BCO03 <= convert(char(8), getdate(), 108) and c.BCO04 >= convert(char(8), getdate(), 108))
														or (c.BCO03 >= c.BCO04 and (c.BCO03 <= convert(char(8), getdate(), 108) or c.BCO04 >= convert(char(8), getdate(), 108)))
														) 
												end
								
						
				if(@bck03 IS NOT NULL)
					BEGIN
						SET @tmpXml+='<MIBCK BCK01="'+CONVERT(varchar(10),@bck01)+'" BCK03="'+@bck03+'"/>'
					END
			
--执行科室
	
				set @bck03 =NULL
				SELECT top 1 @bck01= a.BCK01B,@bck03=BCK03 
				FROM BAV1 a
				join BBX1 b on b.BBX01=a.BBX01 and b.BDA01 not in ('4','M')
				JOIN BCK1 c ON a.BCK01B = c.BCK01
				WHERE a.ACF01 IN (2,3,-1) AND a.BCK01A = @bck01d  AND a.BBX01 = @bbx01c
		--		SELECT @bck03
				IF (@bck03 IS NULL)
				BEGIN
					SELECT TOP 1 @bck01=a.BCK01B,@bck03=b.BCK03
					FROM BAV1 a
					LEFT JOIN BCK1  b ON a.BCK01B = b.BCK01
					WHERE a.ACF01 = @lACF01 AND a.BBX01 = @bbx01c and isnull(a.BCK01A,0)=0
			--		SELECT @bck03
					IF (@bck03 IS NOT NULL)
					BEGIN
						SET @tmpXml+='<EXBCK BCK01="'+CONVERT(varchar(10),@bck01)+'" BCK03="'+@bck03+'"/>'
					END
				END
				ELSE
				BEGIN
					SET @tmpXml+='<EXBCK BCK01="'+CONVERT(varchar(10),@bck01)+'" BCK03="'+@bck03+'"/>'
				END
		END

	SET @tmpXml+='</BCK>'
	FETCH NEXT FROM order_cursor INTO @lisize,@bef01,@bbx01c,@bby01
	END
	CLOSE order_cursor--关闭游标
	DEALLOCATE order_cursor--释放游标
END

SET @tmpXml+='</Root>'
SET @rList =@tmpXml


IF Object_id('tempdb..#TmpBBX1') IS NOT NULL DROP TABLE #TmpBBX1