--增加分类
GO
IF EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=195) 
Delete from SYS_ScriptCategories WHERE id=195
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(195, 'hocommyh_001', '医护公共脚本', 105, 0, 31, 0, '医护公共脚本')

GO


--删除旧记录
delete from SYS_Scripts where id=26081 --删除脚本内容
GO


GO
IF EXISTS(SELECT * FROM SYS_Scripts WHERE id=26081) 
Delete from SYS_Scripts WHERE id=26081
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom,ConnectSlave)  Values(26081, 195, 'Sql_提取记账报警线新', 'Sql_提取记账报警线新', 'Sql_提取记账报警线新', '1', 0, Null, 1, '--@lmode=0默认之前的场合 =1护士记账 =2批量记账 =3护士医嘱发送 =4医生点击新开医嘱   =5医生保存医嘱 
--护士记账、批量记账不受信用额度限制
--ltype 0=禁止继续进行 1=提示允许继续进行  2=提示并选择是否进行   3=只返回信息由具体业务方进行提示 
declare @VAA01 int,@VAA07 int ,@param406 varchar(10) ,@param468 varchar(10),@BIF09 numeric(18,2),@blp264 INT,@BCK01 INT ,@lMoneyS varchar(20),  @lMoney numeric(18,2),
        @Smsg VARCHAR(500),@lSign TINYINT,@blp433 TINYINT,@blp628 TINYINT,@Smsg2  VARCHAR(500),@lSmsg   VARCHAR(500)
declare @lmode int=0 ,@blp681 INT
set @VAA01 = %d
set @VAA07 = %d
set @lmode = %d
SET @BCK01 =%d
SET @lMoneyS = ''%s''
SET @lSign = %d
set @lSmsg=''%s''
set @param406 = dbo.GetSysParamValue(100,105003,406)
set @param468 = dbo.GetSysParamValue(100,105003,468)

SET @lMoney=cast(@lMoneyS as numeric(18,2)) 
IF @lMoney>0
BEGIN
  SET @lMoneyS='' 本次金额：''+@lMoneyS
END
ELSE
BEGIN
   SET @lMoneyS=''''
END

select @blp264=isnull(BLP04,0) from BLP1 with(nolock) where BLP02=264
if isnull(@blp264,0)=0 set @lmode=0

select @blp628=isnull(BLP04,0) from BLP1 with(nolock) where BLP02=628
SET @blp628=ISNULL(@blp628,0)

select @blp433=isnull(BLP04,0) from BLP1 with(nolock) where BLP02=433
SET @blp433=ISNULL(@blp433,0)

select @blp681=isnull(BLP04,0) from BLP1 with(nolock) where BLP02=681
SET @blp681=ISNULL(@blp681,0)

if object_id(''tempdb..#tmpVBM_Balance'') is not null drop table #tmpVBM_Balance

declare @kbmMsg table(Smsg varchar(500),ltype INT,lunion INT )

if @param468=''1''
  select @BIF09=BIF09 from VCN1 with(nolock) where VAA07 = @VAA07 and VCN33=1
set @BIF09=isnull(@BIF09,0)
SET  @Smsg= CASE WHEN  @blp628=0 THEN ''，不能继续操作，请先缴押金！'' WHEN @blp628=1 THEN ''，请及时续缴押金！'' WHEN @blp628=2 THEN ''，是否继续？'' ELSE '''' end
SET @Smsg2= CASE WHEN  @blp433=1 THEN ''，不能继续操作，请先缴押金！'' ELSE ''，是否继续？'' END 
	
IF @blp681=1 AND EXISTS(SELECT 1 FROM VAE1 WITH(nolock) WHERE VAE01=@VAA07 AND dateadd(hour,24,VAE11)>GETDATE() )
BEGIN
  SET @blp681=2 --入院24小时内，不判断信用额度
END


if  @lmode=1 or @lmode=2 OR @blp681=2
begin
  SELECT  Smsg,ltype FROM @kbmMsg
end
else
BEGIN
		SELECT Isnull(a.BEP05,0) FBEP05,isnull(-a.BEP06,0) FBEP06,c.VAE95 VAA05,c.VAE94 VAA04
		  ,isnull(b.VBM04,0) FVBM04,(ISNULL(b.VBM04,0)+isnull(d.VBU14,0)+isnull(b.VBM08,0)+isnull(b.VBM12,0)-ISNULL(b.VBM05,0)-@lMoney) FVBM07
		  ,c.BCQ04B,isnull(d.VBU14,0) FVBU14 ,ISNULL(b.VBM05,0)+@lMoney FVBM05
		  ,case when @param468=''1'' and @BIF09>0.0001 and isnull(b.BEP07,0)>0.001 and @BIF09<isnull(b.BEP07,0) then @BIF09
			  when @param468=''1'' and @BIF09>0.0001 and isnull(b.BEP07,0)<0.001 then @BIF09 else isnull(b.BEP07,0) end FBEP07
		  ,Convert(decimal(18,2),isnull(b.VBM04,0)/(case when isnull(b.VBM05,0)=0 then 1 else b.VBM05 end)*100) FBEP13,isnull(b.VBM20,0) BEP13
                  ,''【'' + ISNULL(BCQ04B,'''') + '':'' + ISNULL(VAE94,'''') + '':'' + ISNULL(VAE95,'''') + ''】'' lName
		INTO #tmpVBM_Balance
		FROM VAA1 a with(nolock)
		join VAE1 c with(nolock) on a.VAA01 = c.VAA01
		LEFT JOIN VBM2 b with(nolock) ON b.VAA07 = c.VAE01 AND b.ACF01 = 2  
		left join VBU1 d with(nolock) on d.VAA01 = c.VAA01 and d.VBU21 = 1 and @param406 <> ''1''
		WHERE c.VAE01 = @VAA07 And c.VAE44 < 5

		INSERT INTO @kbmMsg(Smsg,ltype,lunion)
		SELECT lName+'' 费用总额已超出限制总额('' + cast(FBEP07 as varchar(20)) + '')''+@Smsg+@lMoneyS,CASE WHEN @lSmsg=''1'' THEN 3 ELSE @blp628 END,1
		FROM #tmpVBM_Balance
		WHERE FBEP07>0.01 AND (FVBM05-FBEP07)>0.0001

		IF (@blp628=0 OR @lSmsg=''1'') AND EXISTS(SELECT 1 FROM @kbmMsg)
		BEGIN
			   SELECT  Smsg,ltype,lunion FROM @kbmMsg
			   return
		END


          IF EXISTS( SELECT 1
		  FROM BEQ1 a with(nolock)
		  JOIN BEP1 b with(nolock) ON a.BEP01 = b.BEP01
		  JOIN VAE1 c with(nolock) ON b.BDP02 = c.BDP02
		  WHERE c.VAE44>=2 AND c.VAE44<5 AND c.VAE01 = @VAA07 AND a.BCK01 = @BCK01)
		  BEGIN
			 SELECT Smsg,ltype,lunion FROM  @kbmMsg
			 RETURN 
		  END

		  IF @lSign=1 AND EXISTS(SELECT 1 FROM #tmpVBM_Balance WHERE BEP13>0.0001)
		  BEGIN
				--先判断信用比例
				INSERT INTO @kbmMsg(Smsg,ltype,lunion)
				SELECT   lName+'' 押金余额与总费用的比值为 '' + cast(FBEP13 as varchar(20)) + ''%% ，已低于记账信用比例('' + BEP13 + ''%%)''+@Smsg+@lMoneyS,CASE WHEN @lSmsg=''1'' THEN 3 ELSE @blp628 END,2
				FROM #tmpVBM_Balance
				WHERE  FBEP13-BEP13<0.0001
				IF (@blp628=0 OR @lSmsg=''1'') AND EXISTS(SELECT 1 FROM @kbmMsg WHERE lunion=2)
				BEGIN
				   SELECT  Smsg,ltype,lunion FROM @kbmMsg
				   return
				END
		  END
		  ELSE
		  BEGIN
				--判断信用额度
				INSERT INTO @kbmMsg(Smsg,ltype,lunion)
				SELECT  lName+'' 押金余额已低于记账信用额度('' + cast(FBEP06 as varchar(20))  + '')''+@Smsg+@lMoneyS,CASE WHEN @lSmsg=''1'' THEN 3 ELSE @blp628 END,3
				FROM #tmpVBM_Balance
				WHERE  FBEP06-FVBM07>0.0001  
				IF (@blp628=0 OR @lSmsg=''1'') AND EXISTS(SELECT 1 FROM @kbmMsg WHERE lunion=3)
				BEGIN
				   SELECT  Smsg,ltype,lunion FROM @kbmMsg
				   return
				END				
		  END

	  --然后在报警

	     INSERT INTO @kbmMsg(Smsg,ltype,lunion)
         SELECT   lName+'' 押金余额不足，已低于记账报警线('' + cast(FBEP05 as varchar(20))  + '')''+@Smsg2+@lMoneyS,CASE WHEN @blp433=1 THEN 0 ELSE (CASE WHEN @lSmsg=''1'' THEN 3 ELSE 2 END) END ,4 --@blp628
		 FROM #tmpVBM_Balance
		 WHERE  FVBM07-FBEP05<0.0001 AND FBEP05>0.0001

		 SELECT Smsg,ltype,lunion FROM  @kbmMsg
end
 
 if object_id(''tempdb..#tmpVBM_Balance'') is not null drop table #tmpVBM_Balance

 ', '2023-02-21 15:44:29', '(0069)付枫', '0', '0', '0')

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 26081)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=26081
GO