--增加分类 GO IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1) insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null) else print 'SYS_ScriptCategories.id=1 已经存在.' GO --删除旧记录 delete from SYS_Scripts where id=650 --删除脚本内容 GO GO IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=650) insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode) Values(650, 32, 'Sel_RegisterB', Null, '门诊医师工作站检索就诊或已诊病人信息', 1, 1, Null, 1, 'DECLARE @lVAC34 int, @lBCK01 int, @lDt1 datetime, @lDt2 datetime,@lBCE01 int declare @lPara varchar(20) SET @lVAC34 = %d SET @lBCK01 = %d Set @lDt1 = Convert(varchar(10),Cast(%s as datetime),21)+'' 00:00:00.000'' Set @lDt2 = Convert(varchar(10),Cast(%s as datetime),21)+'' 23:59:59.999'' set @lPara = %s set @lBCE01 = %d if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC select b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51,b.VAA10,b.VAC34,b.VAC35,b.ABC02 ,b.BCK01A,b.BCE01A,b.VAC70,b.VAA01,b.AAU01,b.UAA01,b.BDP02,a.BDP05 into #tmpVAC from VAC1 b with(nolock) left join BDP1 a on a.BDP02 = b.BDP02 left join VCJ1 c on c.VAA07 = b.VAC01 WHERE b.VAC34 = @lVAC34 and b.VAC45=1 and b.VAC09 <> -3 and ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or b.BCE01A =0))) or (@lPara not in (''05'',''06'') AND b.BCK01A = @lBCK01)) AND ((b.VAC35 >= @lDt1 and b.VAC35 <= @lDt2) or (c.VCJ09 >= @lDt1 and c.VCJ09 <= @lDt2)) SELECT a.VAA01,a.VAA02,a.VAA03,a.VAA05,a.VAA15,e.ABW02,f.AAU02, h.VCB04 ,Agep = CASE WHEN isnull(b.VAA10,0)=0 THEN NULL else (ltrim(str(b.VAA10))+f.AAU02) END ,b.VAC01,b.VAC02,b.VAC06,b.VAC10,b.BCE03A,b.VAC36,b.VAC42,b.BAK05,b.VAC51 , c.BCK03, d.UAA03, d.UAA02, b.VAC34, b.VAC35, b.ABC02, b.BCK01A,b.BCE01A ,b.VAC70 AS VAKID,b.BDP02,b.BDP05 FROM VAA1 AS a with(nolock) JOIN #tmpVAC AS b ON a.VAA01 = b.VAA01 LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01 LEFT JOIN AAU1 AS f ON b.AAU01 = f.AAU01 LEFT JOIN BCK1 AS c ON b.BCK01A = c.BCK01 LEFT JOIN UAA1 AS d ON b.UAA01 = d.UAA01 LEFT JOIN VBU1 h with(nolock) ON a.VBU01 = h.VBU01 Order By b.VAC35 DESC if object_id(''tempdb..#tmpVAC'') is not null drop table #tmpVAC', '2014-12-10 16:01:21', '(8058)高瑜', 0) else print 'SYS_Scripts.id=650 已经存在.' GO --删除旧记录 delete from SYS_Scripts where id=1546 --删除脚本内容 GO GO IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1546) insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode) Values(1546, 78, 'SQL_查找结帐收费明细', '查找结帐收费明细', '查找结帐收费明细', 1, 0, Null, 1, '--查找要退费的明细 DECLARE @VAK01 int SET @VAK01 = %d select d.VAI04 ,b.BBY05, b.BBY06, b.AAS01, b.ABF01, C.BCK03, a.VAJ01, a.VAA01, a.VAA07, a.VAJ04, a.VAJ05, a.ROWNR, a.VAJ01A, a.VAJ01B ,a.VAJ09 ,a.VAJ10, a.VAI01, a.VAF01, a.VAK01, a.ACF01, a.VAJ15, a.BCK01A, a.BCK01B, a.BDN01, a.BBY01, a.BCJ02, a.VAJ21 ,a.VAJ22 ,a.VAJ23, a.VAJ24, ISNULL(e.VAJ25 , a.VAJ25) VAJ25 , a.VAJ26, a.VAJ27, a.VAJ28, a.VAJ29, a.VAJ30, a.VAJ31, a.VAJ32, a.VAJ33 ,a.VAJ34 ,a.VAJ35 ,a.VAJ36 * ISNULL(e.VAJ25 , a.VAJ25) / a.VAJ25 VAJ36 , a.VAJ37 * ISNULL(e.VAJ25 , a.VAJ25) / a.VAJ25 VAJ37 , a.VAJ38 * ISNULL(e.VAJ25 , a.VAJ25) / a.VAJ25 VAJ38, a.VAJ39, a.VAJ40, a.VAJ41, a.BCE03A, a.BCK01C, a.BCE02B, a.BCE03B, a.VAJ46 ,a.VAJ47 ,a.VAJ48, a.BCK01D, a.BCE03C, a.VAJ51, a.VAJ52, a.VAJ53, a.VAJ54, a.BCE02D, a.BCE03D, a.VAJ57, a.FAB03, a.VAJ59 , a.VAJ61 * ISNULL(e.VAJ25 , a.VAJ25) / a.VAJ25 VAJ61 ,f.BAX03 , g.ABF02 , isnull(e.VBY09,0) VBY09 , a.VAJ67 , a.DSK01 , s.AAS07 from V_VAJ_FULL a JOIN BBY1 b ON a.BBY01 = b.BBY01 left JOIN V_VAI_FULL d ON a.VAI01 = d.VAI01 JOIN BCK1 c ON a.BCK01D = c.BCK01 JOIN BAX1 f on b.BAX01 = f.BAX01 JOIN ABF1 g on b.ABF01 = g.ABF01 left JOIN VBY1 e on e.VAJ01 = a.VAJ01 and e.VBY09 = 1 LEFT JOIN AAS1 s on s.AAS01 = b.AAS01 WHERE a.VAK01 = @VAK01 order by a.ROWNR', '2014-11-24 14:53:12', '(8065)冯义强', 0) else print 'SYS_Scripts.id=1546 已经存在.' GO /****** Object: StoredProcedure [dbo].[HORate_InHosCheckOut_CancelEx] Script Date: 12/01/2014 10:45:05 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HORate_InHosCheckOut_CancelEx]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[HORate_InHosCheckOut_CancelEx] GO /****** Object: StoredProcedure [dbo].[HORate_InHosCheckOut_CancelEx] Script Date: 12/01/2014 10:45:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --======================================================================================================= --Author <...fyq> --Alter Date <...2014-12-01> --Description<...住院取消结帐(门诊、住院费用分表)> --2014.09.16 <...住院取消结账时根据参数决定是否清空IAS1记录中的VAK01> --2014.11.11 <...增加会员接口类型的退费> --2014.12.01 <...取消结账时会员退费不处理预交款扣费情况> --======================================================================================================= CREATE PROC [dbo].[HORate_InHosCheckOut_CancelEx] @VAK01 int , @BCE01 int , @BCE02 varchar(20) , @BCE03 varchar(20) , @VAK17 varchar(255) = '', @BCE03B varchar(20) AS DECLARE @i int,--循环变量 @Count int,--记录条数 @VAA01 int,--病人ID @VAA07 int,--就诊ID @ErrId int, @Value int,--主键ID @VAI01 int,--单据ID @VAI01A int,--新单据ID @VBL01 int,--病人缴款ID @VAJ01 int,--病人费用明细ID @VAK01A int,--冲销结账ID @VAI04 varchar(20),--单据号 @VAK04 varchar(20),--结账单号 @VBL02 varchar(20),--病人缴款单号 @VAK08 numeric(18,4),--结账金额 @BBY01 int,--费用明细ID @AAO05 numeric(18 ,4) , @AAO42 numeric(18 ,4) , @Result int , @SCA01 int , @SCF01 int , @SCH01 int , @SCH12 varchar(1024) , @SelfFeePatient varchar(1024) , @BackFeeSet int , @CashPay varchar(20) --如果当前表中数据转移到备份表中，则转回原数据 IF NOT EXISTS(SELECT * FROM VAK1 WHERE VAK01 = @VAK01) BEGIN EXEC @ErrId = HORate_Transfer_Data @VAK01 , 0 IF @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('转移数据时发生错误.', 16, 1) WITH NOWAIT Return 1 END END --如果不存在要取消结账的数据，则提示 IF NOT EXISTS(SELECT * FROM VAK1 WHERE VAK01 = @VAK01) BEGIN RAISERROR('系统中要取消结账的数据不存在.', 16, 1) WITH NOWAIT Return 1 END --如果结帐数据已经退费，则退出 IF EXISTS(SELECT * FROM VAK1 WHERE VAK01A = @VAK01 ) BEGIN RAISERROR('系统中要取消结账的数据已经退费.', 16, 1) WITH NOWAIT Return 1 END --判断是否有权限退自费病人结帐 SELECT @SelfFeePatient = Value from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 86 SET @SelfFeePatient = ISNULL(@SelfFeePatient , '') If @SelfFeePatient <> '' BEGIN If not exists(select * from IAS1 where VAK01 = @VAK01 ) BEGIN If CHARINDEX(@BCE02 , @SelfFeePatient) = 0 BEGIN RAISERROR('当前操作员没有权限对自费病人进行退费.', 16, 1) WITH NOWAIT Return 1 END END END SELECT @VAA01 = VAA01 ,@VAA07 = VAA07 , @VAK08 = VAK08 FROM VAK1 WHERE VAK01 = @VAK01 /*=========================================================生成冲销预交款临时表=============================================================*/ --如果明细数据不存在，则退出 If not exists(select * from VAJ2 where VAK01 = @VAK01) BEGIN RAISERROR('没有要冲销的明细，请检查.', 16, 1) WITH NOWAIT Return 2 END --把原结账记录的支付方式全部冲掉 SELECT @BackFeeSet = Value from SYS_Parameters where ProgramID = 103017 and ProductID = 100 and ParamNo = 35 SELECT IDENTITY(int,1,1) AS ID, * into #tmpVBL1 from VBL1 where VAK01 =@VAK01 --托收类型的支付方式，退费时默认退费现金(补交款) If ISNULL(@BackFeeSet , 0) = 1 BEGIN If exists(select * from #tmpVBL1 where VBL15 = '02' and VBL27 = 2) BEGIN SELECT @CashPay = BBP02 from BBP1 where AAO01 = '01' If ISNULL(@CashPay , '') <> '' BEGIN Update #tmpVBL1 SET VBL14 = @CashPay , VBL15 = '01' where VBL15 = '02' and VBL27 = 2 END END END SELECT @i =0 , @Count = count(1) from #tmpVBL1 Exec @ErrId = Core_NewIDEx 'VBL1' , 'VBL01' , @Value out , @Count If @ErrId > 0 BEGIN Return 1 END While @i < @Count BEGIN SELECT @VBL01 = VBL01 from #tmpVBL1 where [ID] = @i + 1 SET @VBL02 = '000000000' SET @VBL02 = @VBL02 + Cast(@Value - @Count + @i + 1 AS VARCHAR) SET @VBL02 = 'L' + SUBSTRING(@VBL02, Len(@VBL02) - 9, 10) Update #tmpVBL1 set VBL01 = @Value - @Count + @i + 1 , VBL02 = @VBL02 , VBL01A = @VBL01 where [ID] = @i + 1 SET @i = @i + 1 END --生成预交款副本数据集 SELECT IDENTITY(int,1,1) AS ID, * into #tmpNewVBL1 from VBL1 where VAK01 = @VAK01 and VBL27 = 1 SELECT @i = 0 , @Count = count(1) from #tmpNewVBL1 Exec @ErrId = Core_NewIDEx 'VBL1' , 'VBL01' , @Value out , @Count If @ErrId > 0 BEGIN Return 1 END WHILE @i < @Count BEGIN SET @VBL02 = '000000000' SET @VBL02 = @VBL02 + Cast(@Value - @Count + @i + 1 AS VARCHAR) SET @VBL02 = 'L' + SUBSTRING(@VBL02, Len(@VBL02) - 9, 10) Update #tmpNewVBL1 set VBL01B = Case when ISNULL(VBL01B , 0) = 0 then VBL01 Else VBL01B End , VBL01 = @Value - @Count + @i + 1 , VBL02 = @VBL02 where [ID] = @i + 1 SET @i = @i + 1 END /*=========================================================生成冲销单据与明细=================================================================*/ SELECT * INTO #tmpOldVAJ1 from VAJ2 where VAK01 = @VAK01 --生成单据临时表、副本生成2个单据 SELECT top 1 @VAI01 = VAI01 from VAJ2 where VAK01 = @VAK01 SELECT IDENTITY(int,1,1) as ID, * into #tmpVAI1 from (SELECT VAI01 = -1 ,VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, VAI12, VAI13, VAI14 , VAI17 , VAI18 , CBM01 , ACF01 , VAP01 from VAI2 where VAI01 = @VAI01 UNION ALL SELECT VAI01 = -2 ,VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, VAI12, VAI13, VAI14 , VAI17 , VAI18 , CBM01 , ACF01 , VAP01 from VAI2 where VAI01 = @VAI01 ) a SELECT @i =0 , @Count = Count(1) from #tmpVAI1 Exec @ErrId = Core_NewIdEx 'VAI1','VAI01',@VAI01A out ,@Count If @ErrId > 0 BEGIN Return 1 END WHILE @i < @Count BEGIN SET @VAI04 = '000000000' SET @VAI04 = @VAI04 + Cast(@VAI01A - @Count + @i + 1 AS VARCHAR) SET @VAI04 = 'I' + SUBSTRING(@VAI04, Len(@VAI04) - 9, 10) Update #tmpVAI1 SET VAI01 = @VAI01A - @Count + @i + 1, VAI04 = @VAI04 where [ID] = @i + 1 SET @i = @i + 1 END --生成明细临时表 SELECT @VAI01 = VAI01 from #tmpVAI1 where [ID] = 1 SELECT IDENTITY(int,1,1) as ID, * into #tmpVAJ1 from VAJ2 where VAK01 = @VAK01 and VAJ05 = 2 SELECT @i = 0 , @Count = count(1) from #tmpVAJ1 Exec @ErrId = Core_NewIdEx 'VAJ1' , 'VAJ01' ,@Value out, @Count If @ErrId > 0 BEGIN Return 1 END WHILE @i < @Count BEGIN Update #tmpVAJ1 SET VAJ01 = @Value - @Count + @i + 1, VAI01 = @VAI01 where [ID] = @i + 1 SET @i = @i + 1 END --更新明细状态为未执行的明细为2 Update #tmpVAJ1 set VAJ53 =2 where VAJ53 = 0 --新插入明细记录 SELECT IDENTITY(int,1,1) as ID, * into #tmpNewVAJ1 from VAJ2 where VAK01 = @VAK01 and VAJ05 = 2 Exec @ErrId = Core_NewIdEx 'VAJ1' , 'VAJ01' , @Value out , @Count If @ErrId > 0 BEGIN Return 1 END SELECT @i =0 ,@Count = count(1) from #tmpNewVAJ1 WHILE @i < @Count BEGIN Update #tmpNewVAJ1 SET VAJ01 = @Value - @Count + @i + 1, VAI01 = @VAI01A where [ID] = @i + 1 SET @i = @i + 1 END --更新未执行的明细状态为2 Update #tmpNewVAJ1 set VAJ53 = 2 where VAJ53 = 0 --如果已经取消结帐不允许再取消结帐 IF EXISTS(SELECT * from VAK1 WHERE VAK01A = @VAK01) BEGIN RAISERROR('系统已经取消结帐，请刷新界面.', 16, 1) WITH NOWAIT Return 1 END --开始事务 BEGIN TRAN /*=========================================================冲销结账记录=========================================================================*/ Exec @ErrId = Core_NewIdEx 'VAK1','VAK01',@VAK01A out If @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN Return 1 END SET @VAK04 = '000000000' SET @VAK04 = @VAK04 + Cast(@VAK01A AS VARCHAR) SET @VAK04 = 'K' + SUBSTRING(@VAK04, Len(@VAK04) - 9, 10) --插入冲销记录 INSERT INTO VAK1(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, VAK13, VAK01A, VAK15, VAK16, VAK17,VAK18, VAK19 ,VAK20 ,VAK21,VAK23 ,VAK24 , BCE01A , VAK28 , BCE03B) select @VAK01A , VAA01 , VAA07 , @VAK04 , '' , VAK06 , -VAK07, -VAK08, 0 , 0 , @BCE02 , @BCE03 , Getdate() , VAK01 , VAK15 , VAK16 ,'取消结账:' + @VAK17,0, 2 ,-VAK20 ,-VAK21 ,-VAK23 ,-VAK24 ,@BCE01 , @VAK01A , @BCE03B from VAK1 where VAK01 = @VAK01 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('冲销病人结帐记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --更新原结账记录 update VAK1 set VAK01A = @VAK01A where VAK01 = @VAK01 --取消医保结账 If not exists(select * from sys_parameters where programid = 103017 and ParamNo = 38 and Value ='1') BEGIN Update a SET a.VAK01 =0 from IAS1 a join VAK1 b on a.VAK01 = b.VAK01 where a.VAK01 = @VAK01 END /*============================================冲销补交款并生成预交款副本=================================================================*/ --冲销补交款 INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, VBL13, VBL14, VBL15, VAK01, VBL17, VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, VBL27 , VBL29 ) SELECT VBL01 , VBL02 , VBL03 , VBL04 , 4 , VAA01 , VAA07 , BCK01 , BAQ03 , BBO02 , VBL11 , '取消结账' , -VBL13 , VBL14 , VBL15 , @VAK01A ,0 , getdate() ,getdate() ,VBL01A, @BCE01 , @BCE02 , @BCE03 , 0 , 0 , 0 ,VBL27 , 1 from #tmpVBL1 --如果一卡通模式或者混合模式，而且退费方式为一卡通支付，则退钱到一卡通(补交款) If dbo.GetWorkingMode() >= 1 BEGIN If exists(select * from #tmpVBL1 a, BBP1 b where a.VBL27 =2 and a.VBL14 = b.BBP02 and ISNULL(b.BBP09 , 0) = 0 and ((VBL15 ='05') or (VBL15 = '42'))) BEGIN SELECT @AAO05 = -sum(VBL13) FROM #tmpVBL1 a, BBP1 b where a.VBL27 =2 and a.VBL14 = b.BBP02 and ISNULL(b.BBP09 , 0) = 0 and VBL15 ='05' SELECT @AAO42 = -SUM(VBL13) from #tmpVBL1 a, BBP1 b where a.VBL27 =2 and a.VBL14 = b.BBP02 and ISNULL(b.BBP09 , 0) = 0 and VBL15 ='42' SELECT @VBL01 = VBL01 from #tmpVBL1 a, BBP1 b where a.VBL27 =2 and a.VBL14 = b.BBP02 and ISNULL(b.BBP09 , 0) = 0 and VBL15 = '05' IF ISNULL(@VBL01 , 0) = 0 SELECT @VBL01 = VBL01 from #tmpVBL1 a, BBP1 b where a.VBL27 =2 and a.VBL14 = b.BBP02 and ISNULL(b.BBP09 , 0) = 0 and VBL15 ='42' Exec HORate_CardCharge_Update @VAA01 , @VAA07 , 0 , 2 , @BCE01 , 0 , @BCE02 , @BCE03 , @AAO05 , @Result out , 1 , 7 , @VAK01 , @VBL01 , @AAO42 If @Result =3 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('一卡通病人扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END END --插入预交款副本 INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, BAQ03, BBO02, VBL11, VBL12, VBL13, VBL14, VBL15, VAK01, VBL17, VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, VBL27 , VBL29 , VBL01B ) SELECT VBL01 , VBL02 , VBL03 , VBL04 , 0 , VAA01 , VAA07 , BCK01 , BAQ03 , BBO02 , VBL11 , VBL12 , VBL13 , VBL14 , VBL15 , 0 ,0 , getdate() ,getdate() ,0, @BCE01 , @BCE02 , @BCE03 , 0 , 0 , 0 ,1 ,1 , VBL01B from #tmpNewVBL1 /*=====================================================冲销会员积分====================================================================*/ DECLARE @VBU01 int SELECT @VBU01=VBU01 FROM VAA1 WHERE VAA01=@VAA01 IF @VBU01>0 BEGIN DECLARE @Exchange numeric(18,2),@tmpVBL01 int --冲减会员积分支付 SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15='43' --注意这里要取正数,因为是退还帐户 SELECT @tmpVBL01=VBL01 FROM #tmpVBL1 WHERE VBL15='43' IF ISNULL(@Exchange,0)<>0 AND @tmpVBL01>0 BEGIN EXEC @ErrId=Customer_VBU17_DESC @VBU01,23,@VAK01A,@tmpVBL01,@Exchange,@BCE03 IF @ErrId > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('会员客户积分支付时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END --冲减结算时产生的积分 IF dbo.GetCRMMode()=2--伊莱美：药品、检验不送积分 BEGIN DECLARE @Exchange2 numeric(18,2) --不能送积分的支付方式合计 SELECT @Exchange=-SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 NOT IN('01','02','03','05') --能送积分的项目合计 SELECT @Exchange2=-SUM(VAJ38) FROM #tmpVAJ1 WHERE BDN01>'4' AND BDN01<>'L' SET @Exchange=ISNULL(@Exchange2,0)-ISNULL(@Exchange,0) END ELSE BEGIN SELECT @Exchange=-SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 IN('01','02','03','05') END IF ISNULL(@Exchange,0)<>0 BEGIN EXEC @ErrId=Customer_VBU17_INC @VBU01,21,0,@VAK01A,0,@Exchange,@BCE03 IF @ErrId > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('会员客户现金交易冲减积分时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END END /*===================================================会员卡接口类型支付方式退费================================================*/ SELECT @VBL01 = VBL01 from #tmpVBL1 a join BBP1 b on b.BBP02 = a.VBL14 where a.VBL15 = '05' and ISNULL(b.BBP09 , 0) = 1 If ISNULL(@VBL01 , 0) > 0 BEGIN Exec @ErrId = HORate_CardUpdate_Interface '' ,@VAA01 , @VAA07 , 2 , @BCE01 , @BCE03 , @Result out , 0 ,1 , 7 , @VAK01A , @VBL01 If (@ErrId > 0) or (@Result = 3) or (@@ERROR > 0) BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('会员卡接口退费时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*=====================================================冲销单据记录====================================================================*/ INSERT INTO VAI2 (VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B, VAI12, VAI13, VAI14, VAI01A, VAI16, VAI17, VAI18 , CBM01 , ACF01 , VAK01 , VAP01 ) SELECT VAI01 , VAA01 , VAA07 , VAI04 , '取消结账' , BCK01A , BCK01B , @BCE02 , @BCE03 , @BCE02 , @BCE03 , VAI12 , VAI13 , '取消结账' , 0 , 4 , VAI17 , VAI18 , CBM01 , ACF01 , @VAK01A , VAP01 from #tmpVAI1 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('冲销病人费用单据时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END INSERT INTO VAJ2(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, BCE03D, VAJ57, FAB03, VAJ59 , VAJ61 ,VAJ62 , BCK01E , DSK01 , VAJ67 , BCE01E , BCE03E , BCK01F , BCQ04) SELECT VAJ01, VAA01, VAA07, 6, 3 , ROWNR, VAJ01A, VAJ01B, VAJ09 , VAJ10, VAI01, VAF01, @VAK01 , ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38 , VAJ39, VAJ40, VAJ41, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, 0 , BCK01D, BCE03C, VAJ51, VAJ52, VAJ53 , VAJ54, @BCE02, @BCE03, VAJ57, FAB03, VAJ59 ,VAJ61 ,VAJ62 , BCK01E , DSK01 , VAJ67 , BCE01E , BCE03E , BCK01F , BCQ04 from #tmpNewVAJ1 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('冲病人收费明细时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END INSERT INTO VAJ2(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, VAJ40, VAJ41, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, BCE03C, VAJ51, VAJ52, VAJ53, VAJ54, BCE02D, BCE03D, VAJ57, FAB03, VAJ59 , VAJ61 , VAJ62 , BCK01E , DSK01, VAJ67 , BCE01E , BCE03E , BCK01F , BCQ04) SELECT VAJ01, VAA01, VAA07, 6, 4, ROWNR, VAJ01A, VAJ01B, VAJ09 , VAJ10, VAI01, VAF01, @VAK01A, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, -VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, -VAJ36, -VAJ37, -VAJ38, VAJ39, -VAJ40, -VAJ41, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46 , VAJ47 , 0 , BCK01D, BCE03C, VAJ51, VAJ52, VAJ53 , VAJ54, @BCE02, @BCE03, '取消结账', FAB03, VAJ59 ,-VAJ61 , VAJ62 ,BCK01E , DSK01, VAJ67 , BCE01E , BCE03E , BCK01F , BCQ04 from #tmpVAJ1 IF @@ERROR>0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('冲病人收费明细时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*======================================================更新原结账明细与单据===========================================================*/ update a set VAI16 = 1 from VAI2 a , #tmpOldVAJ1 b where a.VAI01 = b.VAI01 and a.VAI16 =2 update a set VAK01 = 0 from VAJ2 a, #tmpOldVAJ1 b where a.VAJ01 = b.VAJ01 update a set VAJ05 = 1 from VAJ2 a ,#tmpOldVAJ1 b where a.VAJ01 = b.VAJ01 and a.VAJ05 = 2 /*=====================================================更新病人状态及费用余额===================================================================*/ Declare @VAK06 tinyint SELECT @VAK06 = VAK06 from VAK1 where VAK01 = @VAK01 --更新病人状态为在院 if @VAK06 =4 BEGIN update VAE1 set VAE44 =4 where VAE01 = @VAA07 END --更新病人费用总额 Declare @VBM01 int , @VBM04 numeric(18,6) , @VBM05 numeric(18,6) select @VBM05 =isnull(sum(isnull(VAJ38,0)),0) from VAJ2 where VAA07 = @VAA07 and ACF01 = 2 and VAJ05 = 1 select @VBM04 = sum(VBL13) from VBL1 where VBL04 = 4 and VBL05 =0 and VBL27 = 1 and VAK01 = 0 and VAA07 = @VAA07 update VBM1 set VBM04 = @VBM04, VBM05 = @VBM05 where VAA01 = @VAA01 and VBM03 = 2 update VBM2 set VBM04 = @VBM04, VBM05 = @VBM05 where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 2 /*===========================================================写入CRM信息========================================================*/ SELECT @SCF01 = SCF01 from VAC1 where VAC01 = @VAA07 If ISNULL(@SCF01 , 0) > 0 BEGIN SELECT @SCA01 = SCA01 from SCF1 where SCF01 = @SCF01 --更新对应的CRM跟进人退费金额 Update SCI1 SET SCI11 = ISNULL(SCI11 , 0) - ISNULL(@VAK08 , 0) , SCI14 = '取消住院结算，退费金额：' + CAST(@VAK08 as varchar(20)) where SCI05 = @VAK01 SET @SCH12 = '退费时间：' + convert(varchar(30) , GETDATE() , 111) Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAK1' , 'VAK01' , 206 , '住院取消结算' , @SCH12 , 0 , 0 , @BCE01 ,@BCE03 END /*==============================================================================================================================================*/ COMMIT TRAN --删除临时表 IF Object_id('tempdb..#tmpVBL1') IS NOT NULL Drop table #tmpVBL1 IF Object_id('tempdb..#tmpVAI1') IS NOT NULL Drop table #tmpVAI1 IF Object_id('tempdb..#tmpVAJ1') IS NOT NULL Drop table #tmpVAJ1 IF Object_id('tempdb..#tmpNewVAJ1') IS NOT NULL Drop table #tmpNewVAJ1 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --========================================================================================== --Author <...Fyq> --Alter Date <...2014.12.25> --Description<...体检划价数据保存,从VAJT中转移数据到VAJ1中> --Description<...更新虚拟库存时根据参数判断是否更新DPT表> --修改VCB04长度 --========================================================================================== ALTER PROC [HORate_VAJ1_Update3] @Xml ntext,@VAI01 int out, @VAI04 varchar(20) out , @VAA07 int = 0 out , @Refuse tinyint = 0 AS DECLARE @iDOM int, @iret int Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML if @iret > 0 BEGIN exec sp_xml_removedocument @iDOM RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait return @iret END /*Root/VAA1病人信息*/ SELECT * INTO #TmpVAA1 from OpenXml(@iDOM,'/Root/VAA/Ie',8) WITH ( VAA01 int , VAA07 int , ACF01 int , VAA03 varchar(20) , EmpId int , EmpNo varchar(20) , EmpName varchar(20) , HostName varchar(128) , HostIP varchar(32) , WorkName varchar(20) , WorkType smallint , WorkFrom smallint , BCK01A int , BCK01B int , BCE01A int , BCE03A varchar(20) , VAA05 varchar(64) , ABBRW varchar(10) , ABBRP varchar(10) , ABW01 varchar(1) , VAA10 int , AAU01 varchar(1) , BDP02 varchar(50) , ABC02 varchar(20) , BCE02A varchar(20) , VAK17 varchar(255) , KuoZhan1 varchar(255) , VBU01 int , VCB04 varchar(64) , VAP01 int , VCJ10 varchar(64) , VCJ11 varchar(64) , VCJ12 varchar(50) , VCJ13 varchar(128) , VCJ14 varchar(128) ) If not exists(select * from #tmpVAA1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人信息为空', 16, 1) with nowait RETURN 2 END --如果前台传入多条病人信息，报错 If exists(select VAA01 from #TmpVAA1 group by VAA01 having COUNT(VAA01) > 1 ) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，存在多条病人信息,请联系开发商.', 16, 1) with nowait RETURN 2 END If exists(select * from #TmpVAA1 where ISNULL(VAA01 , 0) = 0) BEGIN If exists(select * from #TmpVAA1 where (ISNULL(VAA03 , '') = '') or (ISNULL(VAA05 , '') = '')) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人姓名或者门诊号为空.', 16, 1) with nowait RETURN 2 END END /*Root/VAI病人收费单据*/ select * into #tmpVAI1 from OpenXml(@iDOM,'/Root/VAI/Ie',8) with ( VAI00 varchar(38) , VAI01 int , VAI04 varchar(20) , VAI05 varchar(1024) , BCK01B int , BCE02A varchar(20) , BCE03A varchar(20) , Enabled smallint , VAI17 smallint , VAI22 numeric(18,4) , VAI23 numeric(18,4) , BCE01C int , BCE03C varchar(20) , BCE01D int , BCE03D varchar(20) ) If not exists(select * from #tmpVAI1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人单据信息为空', 16, 1) with nowait RETURN 2 END /*Root/VAJ病人收费记录信息*/ SELECT * , 0 VAJ53 into #tmpVAJ1 from OpenXml(@iDOM,'/Root/VAJ/Ie',8) with ( VAI00 varchar(38) , VAJ01 int , ROWNR int , VAI01 int , BDN01 varchar(2) , VAF01 int , BBY01 int , VAJ01A int , VAJ22 smallint , VAJ23 smallint , VAJ24 numeric(18,4) , VAJ25 numeric(18,4) , VAJ26 smallint , BCK01B int , BCE02B varchar(20) , BCE03B varchar(20) , VAJ27 int , BCK01D int , VAJ30 numeric(9,4) , VAJ31 numeric(9,4) , VAJ59 numeric(18,6) , VAJ32 numeric(18,4) , VAJ33 numeric(18,4) , VAJ34 numeric(18,4) , VAJ35 varchar(20) , VAJ36 numeric(18,4) , VAJ37 numeric(18,4) , VAJ38 numeric(18,4) , VAJ61 numeric(18,4) , VAJ39 smallint , BCK01E int , BCK01C int , VAJ57 varchar(1024) , DSK01 int , VAJ54 varchar(255) , VAJ67 numeric(18,4) ) If not exists(select * from #tmpVAJ1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人收费记录信息为空.', 16, 1) with nowait RETURN 2 END If exists(select * from #tmpVAJ1 where ISNULL(BCK01D , 0) = 0 ) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人收费记录执行科室不能为空.', 16, 1) with nowait RETURN 2 END --如果是血库调用,更新费用明细中开单科室与开单医生 If exists(SELECT * from #tmpVAI1 where VAI05 = '血库记账') BEGIN Update a set a.BCK01C = b.BCK01B , a.BCE02B = b.BCE02A , a.BCE03B = b.BCE03A , a.VAJ53 = 1 from #tmpVAJ1 a join #tmpVAI1 b on a.VAI00 = b.VAI00 END --更新明细表中单据ID Update a SET a.VAI01 = b.VAI01 from #tmpVAJ1 a join #tmpVAI1 b on b.VAI00 = a.VAI00 exec sp_xml_removedocument @iDOM Declare @i int , @VAA01 int , @Count int , @ACF01 int , @BBY01 int , @VAJ01 int , @BCK01A int , @BCK01B int , @InDept int , @InWard int , @VAI01A int , @BCK01D int , @BCE01A int , @EmpId int , @KuoZhan1 int , @ErrId int , @IsHyType int , @VBU01 int , @VAI18 int , @Result int , @ABC14 tinyint , @BDN01 varchar(2) , @WorkName varchar(20) , @BCE02A varchar(20) , @BCE03A varchar(20) , @WorkType smallint , @WorkFrom smallint , @VAI00 varchar(38) , @EmpNo varchar(20) , @BDP02 varchar(20) , @ABC02 varchar(20) , @EmpName varchar(20) , @VAJ24 numeric(18,4) , @VAJ25 numeric(18,4) , @VAJ30 numeric(9,4) , @VAJ31 numeric(9,4) , @VAJ32 numeric(18,4) , @VAJ33 numeric(18,4) , @VAJ34 numeric(18,4) , @VAJ35 varchar(20) , @FAB03 varchar(20) ,--门诊单位或者住院单位 @TotalFee numeric(18,4) , @VCB04 varchar(64) , @VAI22 numeric(18,4) , @VAI23 numeric(18,4) , @VAP01 int , @DSK01 int , @VAJ10 int select @VAA01 = VAA01 ,@VAA07= VAA07,@ACF01 = ACF01,@WorkName = WorkName,@WorkType = WorkType,@EmpId = EmpId, @EmpNo = EmpNo,@EmpName = EmpName, @BCK01A = BCK01A,@WorkFrom = WorkFrom ,@BCK01B = BCK01B,@BCE01A = BCE01A, @BCE02A = BCE02A,@BCE03A = BCE03A, @TotalFee = 0,@BDP02 = BDP02 ,@ABC02 = ABC02 , @VBU01 = VBU01 , @VCB04 = VCB04 , @VAP01 = ISNULL(VAP01 , 0) from #tmpVAA1 --会员病人绑定病人费别 IF EXISTS(SELECT * FROM SYS_PARAMETERS WHERE PRODUCTID=100 AND PROGRAMID=116001 AND PRIVILEGE=0 AND PARAMNO='2' AND VALUE='1') AND EXISTS(SELECT * FROM VAA1 WHERE VAA01=@VAA01 AND VBU01>0 AND ABC02>'' AND ABC02<>@ABC02) BEGIN RAISERROR('已绑定会员卡类型，禁止修改病人费别.', 16, 1) with nowait RETURN 2 END --如果医生不在职，则不让保存数据 If isnull(@BCE01A ,0) > 0 BEGIN If not exists(select * from BCE1 where BCE01 = @BCE01A and BCE41 = 1) BEGIN RAISERROR('提交错误，开单医生不在职，不能进行记账操作..', 16, 1) with nowait RETURN 2 END END --对应记账如果就诊表中不存在数据，则退出 If @ACF01 = 2 BEGIN If not exists(select * from VAE1 where VAE01 = @VAA07 and VAA01 = @VAA01 ) BEGIN RAISERROR('提交错误，病人就诊记录不存在，不能进行记账操作..', 16, 1) with nowait RETURN 2 END END else If (@ACF01 = 1 ) and (@VAA01 > 0) BEGIN If not exists(select * from VAC1 where VAC01 = @VAA07 and VAA01 = @VAA01) BEGIN RAISERROR('提交错误，病人就诊记录不存在，不能进行记账操作..', 16, 1) with nowait RETURN 2 END END --判断是否存在开单科室为空的单据信息 If exists(select * from #tmpVAI1 where isnull(BCK01B , 0) = 0) BEGIN RAISERROR('提交错误，存在收费单据开单科室为空.', 16, 1) with nowait RETURN 2 END --判断是否存在开单人为空的单据信息 If exists(select * from #tmpVAI1 where isnull(BCE02A , '') = '') and (@WorkType <> 1 ) BEGIN RAISERROR('提交错误，存在收费单据开单人为空.', 16, 1) with nowait RETURN 2 END --如果存在开单科室为空或者开单医生为空，则不让保存数据 If exists(select * from #tmpVAJ1 where ISNULL(BCK01C , 0) = 0) and (@WorkType <> 1 ) BEGIN RAISERROR('提交错误，病人收费记录开单科室不能为空.', 16, 1) with nowait RETURN 2 END If exists(select * from #tmpVAJ1 where ISNULL(BCE02B , '') = '') and (@WorkType <> 1 ) BEGIN RAISERROR('提交错误，病人收费记录开单医生不能为空.', 16, 1) with nowait RETURN 2 END --住院病人领药科室为空提示,参数设置为记帐必须有领药科室 If exists(select * from SYS_Parameters where ProgramID = 103012 and ParamNo = 14 and Value = '0') and (@ACF01 = 2) BEGIN If exists(select * from #tmpVAJ1 where BDN01 <= '3' and ISNULL(BCK01E , 0) = 0 ) BEGIN RAISERROR('提交错误，领药科室不能为空.', 16, 1) with nowait RETURN 2 END END --如果是手术工作站，不允许传入zhuozhan1值为空 If (@WorkFrom = 2) and (@WorkType = 5) BEGIN If exists(select * from #TmpVAA1 where ISNULL(KuoZhan1 , 0) <= 0) BEGIN RAISERROR('提交错误，手术记账未传入手术ID.', 16, 1) with nowait RETURN 2 END END SELECT @ABC14 = ABC14 from ABC1 where ABC02 = @ABC02 SET @ABC14 = ISNULL(@ABC14 , 0 ) SELECT @IsHyType = dbo.GetWorkingMode() --找出未保存并且存在明细的单据 SELECT IDENTITY(int,1,1) AS ID, * Into #tmpNewVAI1 from #tmpVAI1 where VAI01 = 0 and exists(select * from #tmpVAJ1 where #tmpVAI1.VAI00 = #tmpVAJ1.VAI00) --找出未保存的明细 select IDENTITY(int,1,1) AS ID, * Into #tmpNewVAJ1 from #tmpVAJ1 where VAJ01 = 0 --找出已保存的明细 select IDENTITY(int,1,1) AS ID, * Into #tmpOldVAJ1 from #tmpVAJ1 where VAJ01 > 0 --如果药品记帐不用发药，更新药品状态为拒绝发药 If exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 106004 and ParamNo = 4 and Value = '1') and (@WorkFrom = 2) BEGIN update #tmpNewVAJ1 set VAJ53 = 2 where BDN01 <= '3' END --找出已保存单据更新其单据全额、金额 SELECT * INTO #tmpSumVAJ1 from (SELECT VAI01 , sum(VAJ36) VAI22 , sum(VAJ38) VAI23 from #tmpVAJ1 where VAI01 > 0 GROUP by VAI01) a /*===============================================================生成单据、明细临时表=========================================================*/ SELECT @i =0 , @Count = COUNT(1) FROM #tmpNewVAI1 EXEC Core_NewIDEx 'VAI1', 'VAI01', @VAI01 out , @Count WHILE @i < @Count BEGIN SELECT @VAI00 = VAI00 FROM #tmpNewVAI1 WHERE [ID] = @i + 1 SET @VAI04 = '000000000' SET @VAI04 = @VAI04 + Cast(@VAI01 - @Count + @i + 1 AS VARCHAR) SET @VAI04 = 'I' + SUBSTRING(@VAI04, Len(@VAI04) - 9, 10) SELECT @VAI22 = sum(VAJ36) , @VAI23 = sum(VAJ38) from #tmpVAJ1 where VAI00 = @VAI00 UPDATE #tmpNewVAI1 SET VAI01 = @VAI01 - @Count + @i + 1, VAI04 = @VAI04 , VAI22 = @VAI22 , VAI23 = @VAI23 WHERE [ID] = @i + 1 SET @i = @i + 1 END --更新原单据ID UPDATE a SET a.VAI01 = b.VAI01 from #tmpVAI1 a join #tmpNewVAI1 b on a.VAI00 = b.VAI00 --更新临时明细表中单据ID UPDATE b SET b.VAI01 = a.VAI01 FROM #tmpVAI1 a JOIN #tmpVAJ1 b ON a.VAI00 = b.VAI00 --更新新增明细临时表中单据ID UPDATE b SET b.VAI01 = a.VAI01 FROM #tmpVAI1 a JOIN #tmpNewVAJ1 b ON a.VAI00 = b.VAI00 --转换明细中药品单位、价格 SELECT @i = 0 , @Count = COUNT(1) FROM #tmpOldVAJ1 WHILE @i < @Count BEGIN SELECT @BDN01 = BDN01 ,@BBY01 = BBY01 ,@VAJ25 = VAJ25 ,@VAJ30 = VAJ30 , @VAJ31 = VAJ31 , @VAJ32 = VAJ32 ,@VAJ33 = VAJ33 ,@VAJ34 = VAJ34 , @VAJ35 = VAJ35 , @VAJ24 = VAJ24 , @FAB03 = VAJ35 from #tmpOldVAJ1 where [ID] = @i + 1 --如果是药品,保存的时候转为为基本单位对应的数量、价格及保存基本单位 IF @BDN01 < '4' BEGIN SELECT @VAJ32 = @VAJ32 / @VAJ34 , @VAJ35 = BBY08 FROM BBY1 a join BAG1 b on b.BBY01 = a.BBY01 WHERE a.BBY01 = @BBY01 SET @VAJ33 = @VAJ32*@VAJ30/@VAJ31 SET @VAJ25 = @VAJ25*@VAJ34 --如果是中草药，转换其单量为基本单量 SET @VAJ24 = @VAJ24*@VAJ34 UPDATE #tmpOldVAJ1 SET VAJ24 = @VAJ24 , VAJ25 = @VAJ25 , VAJ32 = @VAJ32 , VAJ33 = @VAJ33 , VAJ35 = @VAJ35 WHERE [ID] = @i + 1 END SET @i = @i + 1 END SELECT @i = 0 , @Count = COUNT(1) FROM #tmpNewVAJ1 Exec Core_NewIDEx 'VAJ1', 'VAJ01', @VAJ01 out , @Count WHILE @i < @Count BEGIN SELECT @BDN01 = BDN01 ,@BBY01 = BBY01 ,@VAJ25 = VAJ25 ,@VAJ30 = VAJ30 , @VAJ31 = VAJ31 , @VAJ32 = VAJ32 ,@VAJ33 = VAJ33 ,@VAJ34 = VAJ34 , @VAJ35 = VAJ35 , @VAJ24 = VAJ24 , @FAB03 = VAJ35 from #tmpNewVAJ1 where [ID] = @i + 1 --如果是药品,保存的时候转为为基本单位对应的数量、价格及保存基本单位 IF @BDN01 < '4' BEGIN --SELECT @VAJ32 = Case @ABC14 when 1 then (case when isnull(b.BAG23 , 0) = 0 then a.BBY25 else b.BAG23 end) else a.BBY25 end, SELECT @VAJ32 = @VAJ32 / @VAJ34 , @VAJ35 = BBY08 FROM BBY1 a join BAG1 b on b.BBY01 = a.BBY01 WHERE a.BBY01 = @BBY01 SET @VAJ33 = @VAJ32*@VAJ30/@VAJ31 SET @VAJ25 = @VAJ25*@VAJ34 --如果是中草药，转换其单量为基本单量 --IF @BDN01 = '3' SET @VAJ24 = @VAJ24*@VAJ34 UPDATE #tmpNewVAJ1 SET VAJ24 = @VAJ24 , VAJ25 = @VAJ25 , VAJ32 = @VAJ32 , VAJ33 = @VAJ33 , VAJ35 = @VAJ35 WHERE [ID] = @i + 1 END UPDATE #tmpNewVAJ1 SET VAJ01 = @VAJ01 - @Count + @i + 1 WHERE [ID] = @i + 1 SET @i = @i + 1 END --统计单据总额 select @TotalFee = SUM(VAJ38) from #tmpNewVAJ1 --检索出待保存的药品明细 select IDENTITY(int,1,1) AS ID, BBY01 , VAJ25 , BCK01D , DSK01 Into #tmpSumNewVAJ1 from (select BBY01 , sum(VAJ25) VAJ25 , BCK01D , DSK01 from #tmpNewVAJ1 where BDN01 <= '3' group by BBY01 , BCK01D , DSK01) a BEGIN TRAN --开始一个事物 /*================================================================收费处新增病人=======================================================*/ UPDATE VAA1 SET BDP02 =@BDP02 , ABC02 = @ABC02 WHERE VAA01=@VAA01 UPDATE VAC1 SET BDP02 = @BDP02 , ABC02 = @ABC02 WHERE VAC01 =@VAA07 --更新挂号医生 IF EXISTS(SELECT * FROM VAC1 WHERE VAC01 = @VAA07 AND BCE01A = 0) BEGIN UPDATE VAC1 SET BCE01A = @BCE01A , BCE02A = @BCE02A , BCE03A = @BCE03A WHERE VAC01 = @VAA07 END --更新转诊信息 UPDATE a SET a.VCJ10 = b.VCJ10 , a.VCJ11 = b.VCJ11 , a.VCJ12 = b.VCJ12 , a.VCJ13 = b.VCJ13 , a.VCJ14 = b.VCJ13 FROM VCJ1 a JOIN #TmpVAA1 b on b.VAA07 = a.VAA07 where a.VAA07 = @VAA07 --新明细数据、单价、全额、金额、核算金额 Update a SET a.VAJ24 = b.VAJ24 , a.VAJ25 = b.VAJ25 , a.VAJ32 = b.VAJ32 , a.VAJ33 = b.VAJ33 , a.VAJ36 = b.VAJ36 , a.VAJ37 = b.VAJ37 , a.VAJ38 = b.VAJ38 , a.VAJ61 = b.VAJ61 from VAJ1 a join #tmpOldVAJ1 b on a.VAJ01 = b.VAJ01 --更新已有单据上的全额、金额、挂起 If exists(select * from #tmpSumVAJ1) BEGIN Update a SET a.VAI22 = b.VAI22 , a.VAI23 = b.VAI23 from VAI1 a join #tmpSumVAJ1 b on a.VAI01 = b.VAI01 END --更新已有单据是否挂起标志 Update a SET a.VAI25 = b.Enabled from VAI1 a join #tmpVAI1 b on a.VAI01 = b.VAI01 --更新单据明细单据ID(门诊结算重新调整单据后) Update a SET a.VAI01 = b.VAI01 from VAJ1 a join #tmpVAJ1 b on b.VAJ01 = a.VAJ01 /*======================================================保存VAJT中数据到VAJ1中==========================================================*/ If exists(select * from #tmpVAJ1 a , VAJT b where b.VAJ01 = a.VAJ01 and a.VAJ01 > 0 and b.DFLAG = 0 ) BEGIN INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, VAJ54, BCE02D, BCE03D,VAJ53, VAJ57, FAB03, VAJ59 , VAJ61 , VAJ62 , BCK01E , DSK01 , VAJ67) SELECT a.VAJ01, a.VAA01, a.VAA07, a.VAJ04, a.VAJ05, a.ROWNR, a.VAJ01A, a.VAJ01B, a.VAJ09, a.VAJ10, a.VAI01, a.VAF01, a.VAK01, a.ACF01, a.VAJ15, a.BCK01A, a.BCK01B, a.BDN01, a.BBY01, a.BCJ02, a.VAJ21, a.VAJ22, b.VAJ23, b.VAJ24, b.VAJ25, a.VAJ26, a.VAJ27, a.VAJ28, a.VAJ29, b.VAJ30, b.VAJ31, b.VAJ32, b.VAJ33, a.VAJ34, a.VAJ35, b.VAJ36, b.VAJ37, b.VAJ38, a.VAJ39, a.BCE03A, a.BCK01C, a.BCE02B, a.BCE03B, a.VAJ46, a.VAJ47, a.VAJ48, b.BCK01D, a.VAJ54, a.BCE02D, a.BCE03D,a.VAJ53, a.VAJ57, a.FAB03, a.VAJ59 , b.VAJ61 , a.VAJ62 , a.BCK01E , a.DSK01 , b.VAJ67 from VAJT a join #tmpVAJ1 b on b.VAJ01 = a.VAJ01 where a.DFLAG = 0 IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人收费明细时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --更新VAJT中删除标记 Update a set a.DFLAG = 1 from VAJT a join #tmpVAJ1 b on b.VAJ01 = a.VAJ01 where a.DFLAG = 0 END --==================================================如果不存在要新增的明细数据，则返回=================================================== If not exists(select * from #tmpNewVAJ1 ) and not exists(select * from #tmpNewVAI1) BEGIN COMMIT TRAN SELECT * , @VAA01 VAA01 ,@VAA07 VAA07 from #tmpVAI1 RETURN 3 END /*========================================================检索病人科室、病区=============================================================*/ If @ACF01 = 1 --门诊 BEGIN Select @InDept =BCK01A , @InWard = 0 from VAC1 where VAC01 = @VAA07 END /*========================================================保存病人收费单据=================================================================*/ SET @KuoZhan1 = 0 SET @VAJ10 = 0 --如果是收费处新增的单据,状态为划价状态 If @WorkType In(3 , 4 ) BEGIN SET @VAI18 = 2 END else BEGIN SET @VAI18 = @WorkType END INSERT INTO VAI1(VAI01, VAA01, VAA07, VAI04, VAI05, BCK01A, BCK01B, BCE02A, BCE03A, BCE02B, BCE03B, VAI12, VAI13, VAI14, VAI01A, VAI16, VAI17, VAI18 , CBM01 , ACF01 , VAI22 , VAI23 , VAK01 ,VAI25 , BCE01C , BCE03C , BCE01D , BCE03D , VAP01) SELECT VAI01, @VAA01, @VAA07, VAI04, VAI05, @BCK01B, BCK01B, BCE02A, BCE03A, @EmpNo, @EmpName, getdate(), getdate(), @WorkName, 0, 1, VAI17, @VAI18 ,ISNULL(@KuoZhan1 , 0) , @ACF01 , VAI22 , VAI23 , 0 , Enabled , BCE01C , BCE03C , BCE01D , BCE03D , @VAP01 from #tmpNewVAI1 IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入病人费用单据时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*========================================================保存收费明细数据========================================================*/ INSERT INTO VAJ1(VAJ01, VAA01, VAA07, VAJ04, VAJ05, ROWNR, VAJ01A, VAJ01B, VAJ09, VAJ10, VAI01, VAF01, VAK01, ACF01, VAJ15, BCK01A, BCK01B, BDN01, BBY01, BCJ02, VAJ21, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, VAJ27, VAJ28, VAJ29, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38, VAJ39, BCE03A, BCK01C, BCE02B, BCE03B, VAJ46, VAJ47, VAJ48, BCK01D, VAJ54, BCE02D, BCE03D,VAJ53, VAJ57, FAB03, VAJ59 , VAJ61 , VAJ62 , BCK01E , DSK01 , VAJ67) SELECT VAJ01, @VAA01, @VAA07, @VAI18 , 1, ROWNR , ISNULL(VAJ01A , 0), 0, 0, @VAJ10 , VAI01, ISNULL(VAF01 , 0) , 0, @ACF01, 0, @InWard, @InDept, BDN01, BBY01, '', 0, VAJ22, VAJ23, VAJ24, VAJ25, VAJ26, @VAP01, 1, 1, VAJ30, VAJ31, VAJ32, VAJ33, VAJ34, VAJ35, VAJ36, VAJ37, VAJ38 , VAJ39, @EmpName, BCK01C, BCE02B, BCE03B, getdate(), getdate(), 0, BCK01D, ISNULL(VAJ54 ,@WorkName), @EmpNo, @EmpName, VAJ53 ,VAJ57, ISNULL(@FAB03 , ''), VAJ59 , VAJ38 , Getdate() , BCK01E , DSK01 , VAJ67 from #tmpNewVAJ1 IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人收费明细时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*=======================================================更新虚拟库存====================================================================*/ If exists(select * from #tmpSumNewVAJ1) and not exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 46 and value ='1') BEGIN SELECT @i = 0 , @Count = COUNT(1) from #tmpSumNewVAJ1 while @i < @Count BEGIN SELECT @BCK01D = BCK01D , @BBY01 = BBY01 , @VAJ25 = VAJ25 , @DSK01 = DSK01 from #tmpSumNewVAJ1 WHERE [ID] = @i + 1 Exec @ErrId = DPS_DPT1_Update @BCK01D , @BBY01 , @DSK01 ,@VAJ25 IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新药品虚拟库存时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END SET @i = @i + 1 END END Commit TRAN /*=========================================================返回保存成功的单据列表==================================================================*/ select * ,@VAA01 VAA01 ,@VAA07 VAA07 from #tmpNewVAI1 /*=================================================================================================================================================*/ GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --===================================================================================================== --Author <...Fyq> --Alter Date <...2014-12-16> --Description<...一卡通扣费过程> --2011-06-17,HHY,增加“基本帐户交易时产生积分” --2011-06-21,HHY,增加“基本帐户交易时自动升级” --2011.12.20,FYQ,扣费时增加自动结帐过程 --2012.04.12,FYQ,判断会员扣费场所 --2013.03.15,FYQ,增加调用场合医生站简易挂号 --2013.03.15,FYQ,增加简易挂号、医嘱发送根据参数允许会员卡透支扣款 --2013.07.15,FYQ,处理简易挂号不能自动结算的问题 --2013.09.10,FYQ,门诊会员扣费时判断住院冻结金额 --2014.02.25,FYQ,单条单据扣费场合传入金额后后台重新取值 --修改VCB04长度 --2014.12.12,8065,减小锁粒度 --2014.12.16,8065,门诊、住院结算校验余额不足返回3 --==================================================================================================== ALTER PROC [HORate_CardCharge_Update] @VAA01 int , @VAA07 int , @VAI01 int , @ACF01 int , @BCE01 int , @BCK01 int , @BCE02 varchar(20) , @BCE03 varchar(20) , @VAJ38 numeric(18,2) ,--基本账户扣费 @Result int out , --0=成功、1=单据已经扣费、2=余额不足、3=更新失败、4=不需要扣费 @AType tinyint = 0 , --0 = 扣费,1 = 退费 @AWorkType tinyint = 1 , --1=门诊记账，2=药房发药，3=医嘱记账，4=医技执行,5=挂号，6=门诊结账 , 7=住院结帐,8=简易挂号 @VAK01 int =0 , --结账ID，默认为0 @VBL01 int =0 ,--缴款ID，默认为0 @VCA20 numeric(18,2) = 0 ,--赠送账户扣费 @VBU01 int = 0 --会员帐号 AS Declare @VCA01 int, @IsHyType smallint , @VCB04 varchar(64) , @VBU14 numeric(18,2) , @VBU15 numeric(18,2) , @CVBU14 numeric(18,2) , @CVBU15 numeric(18,2) , @BEP06B numeric(18 ,2 ), @ErrId int , @AutoCharge int , @AutoChargePlace varchar(128) , @ChargeMoney varchar(20) , @AvailableMoney varchar(20) , @BalanceMoney varchar(20), @BlockedMoneyShow varchar(20) , @EasyRegOverDraft tinyint, @AdviceOverDraft tinyint, @BlockedMoney numeric(18,4) --如果不在会员扣费场所中，退出 SELECT @AutoChargePlace = Value from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 43 IF ((@AWorkType < 5) or (@AWorkType = 8)) and exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 43) BEGIN IF @AWorkType < 5 BEGIN IF CHARINDEX(CAST(@AWorkType - 1 as varchar(1)) , ISNUll(@AutoChargePlace , '')) <= 0 BEGIN RETURN 4 END END ELSE IF @AWorkType = 8 BEGIN IF CHARINDEX('4' , ISNUll(@AutoChargePlace , '')) <= 0 BEGIN RETURN 4 END END END IF exists(select * from VCA1 with(nolock) where VAI01 = @VAI01 and @VAI01 > 0 and @AType = 0 and ((@AWorkType < 5) or (@AWorkType = 8))) BEGIN SET @Result = 1 RETURN 1 END SELECT @AutoCharge = Value from SYS_Parameters with(nolock) where ProgramID = 103010 and ParamNo = 72 SET @AutoCharge = ISNULL(@AutoCharge , 0) --如果参数选择扣费时自动结算，自动结算操作员为空，则退出 If (@AutoCharge = 1) AND ((@AWorkType < 5) or (@AWorkType = 8)) BEGIN If not exists(select * from SYS_Parameters with(nolock) where ProgramID = 103010 and ParamNo = 73 and Value <> '') BEGIN RAISERROR('请设置自动结算操作员.', 16, 1) with nowait Return 2 END END SET @Result = 0 --2014.02.25单条单据扣费场合系统从后台重新获取扣费金额 IF (@VAI01 > 0) and ((@AWorkType < 5) or (@AWorkType = 8)) BEGIN SELECT @VAJ38 = SUM(VAJ38) from VAJ1 with(nolock) WHERE VAI01 = @VAI01 and VAJ05 <>2 and (VAJ04 =2 or VAJ04 =5) END SET @VAJ38 = ISNULL(@VAJ38 , 0) SET @VCA20 = ISNULL(@VCA20 , 0) --IF (@VAJ38 = 0) AND (@VCA20 = 0 ) --BEGIN --RETURN 0 --END SELECT @IsHyType = dbo.GetWorkingMode() SELECT @BlockedMoney = dbo.GetBlockedMoney(@AWorkType , @VAA01) SELECT @BEP06B = BEP06B from VAA1 where VAA01 = @VAA01 SET @BEP06B = ISNULL(@BEP06B , 0) --如果当前单据已经扣费或者作废则退出 If exists(select * from VAI1 with(nolock) where VAI01 = @VAI01 and (VAI18 = 3 or VAI16 = 9 or VAK01 > 0)) AND @AWorkType < 6 BEGIN SET @Result = 1 RETURN 2 END --如果当前卡余额不足本次扣费，则退出 SELECT @VBU01 = a.VBU01 ,@VBU14 = VBU14 , @VBU15 = VBU15 , @VCB04 = VCB04 from VBU1 a with(nolock) JOIN VAA1 b with(nolock) ON a.VBU01 = b.VBU01 where b.VAA01 = @VAA01 If ISNULL(@VBU01 , 0) = 0 BEGIN RETURN 2 END SET @CVBU14 = @VBU14 SET @CVBU15 = @VBU15 SELECT @EasyRegOverDraft = ISNULL(dbo.GetSysParamValue(100,105002,260) , 0) SELECT @AdviceOverDraft = ISNULL(dbo.GetSysParamValue(100,105002,261) , 0) --判断卡余额是否够扣费(与病人门诊信用额度结合使用) IF (@AWorkType < 6) or (@AWorkType = 8) BEGIN IF ((ABS(@VAJ38) > 0) and (@VBU14 + @BEP06B - @BlockedMoney < @VAJ38)) or ((ABS(@VCA20) > 0) and (@VBU15 < @VCA20)) BEGIN --简易挂号或者医嘱允许会员卡透支扣款，此处不做判断 If ((@AWorkType <> 3) and (@AWorkType <> 8)) or ((@AWorkType = 3) and (@AdviceOverDraft = 0)) or ((@AWorkType = 8) and (@EasyRegOverDraft = 0)) BEGIN SET @Result = 2 IF (@IsHyType >= 1) and (@AWorkType < 6) BEGIN --基本余额扣费 If ABS(@VAJ38) > 0 BEGIN SET @ChargeMoney = CAST(@VAJ38 as varchar(20)) SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20)) SET @AvailableMoney = cast((@VBU14 + @BEP06B - @BlockedMoney) as varchar(20)) SET @BalanceMoney = cast((@VAJ38 - @VBU14-@BEP06B) as varchar(20)) RAISERROR('病人余额[%s],住院冻结金额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney ,@BlockedMoneyShow, @ChargeMoney , @BalanceMoney) with nowait END --赠送余额扣费 If ABS(@VCA20) > 0 BEGIN SET @ChargeMoney = CAST(@VCA20 as varchar(20)) SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20)) SET @AvailableMoney = cast(@VBU15 as varchar(20)) SET @BalanceMoney = cast((@VCA20 - @VBU15) as varchar(20)) RAISERROR('病人余额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney , @ChargeMoney , @BalanceMoney) with nowait END END RETURN 2 END END END ELSE BEGIN IF (@VBU14 - @BlockedMoney < @VAJ38) or ( @VBU15 < @VCA20) BEGIN SET @Result = 3 IF (@IsHyType >= 1) --and (@AWorkType <> 6) BEGIN --基本余额扣费 If ABS(@VAJ38) > 0 BEGIN SET @ChargeMoney = CAST(@VAJ38 as varchar(20)) SET @BlockedMoneyShow = CAST(@BlockedMoney as varchar(20)) SET @AvailableMoney = cast((@VBU14 - @BlockedMoney) as varchar(20)) SET @BalanceMoney = cast((@VAJ38-@VBU14) as varchar(20)) RAISERROR('病人余额[%s],住院冻结金额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney ,@BlockedMoneyShow, @ChargeMoney , @BalanceMoney) with nowait END --赠送余额扣费 If ABS(@VCA20) > 0 BEGIN SET @ChargeMoney = CAST(@VCA20 as varchar(20)) SET @AvailableMoney = cast(@VBU15 as varchar(20)) SET @BalanceMoney = cast((@VCA20-@VBU15) as varchar(20)) RAISERROR('病人余额[%s],需扣会员金额[%s],差额[%s],不足扣费.', 16, 1,@AvailableMoney , @ChargeMoney , @BalanceMoney) with nowait END END RETURN 2 END END --开始一个事物 BEGIN TRAN --更新单据状态 If @Atype = 0 --扣费 BEGIN UPDATE VAI1 with(rowlock) SET VAI18 =3 WHERE VAI01 = @VAI01 AND VAI18 = 2 and VAI16 = 1 --更新明细状态 UPDATE VAJ1 with(rowlock) SET VAJ04 =3 WHERE VAI01 = @VAI01 AND VAJ04 = 2 and VAJ05 = 1 END ELSE If @AType = 1 --退费 BEGIN UPDATE VAI1 with(rowlock) SET VAI18 =3 WHERE VAI01 = @VAI01 AND VAI18 = 2 and VAI16 = 4 --更新明细状态 UPDATE VAJ1 with(rowlock) SET VAJ04 =3 WHERE VAI01 = @VAI01 AND VAJ04 = 2 and VAJ05 = 4 END --更新支付方式的VBU01 IF isnull(@VBL01 , 0) > 0 BEGIN UPDATE VBL1 with(rowlock) SET VBU01 = @VBU01 WHERE VBL01 = @VBL01 END --更新卡余额 IF abs(@VAJ38) > 0 --基本账户 BEGIN SET @CVBU14 = isnull(@VBU14 , 0) - @VAJ38 Update VBU1 with(rowlock) set VBU14 = isnull(VBU14 ,0) - @VAJ38 where VBU01 = @VBU01 END IF abs(@VCA20) > 0 --赠送账户 BEGIN SET @CVBU15 = isnull(@VBU15 , 0) - @VCA20 Update VBU1 with(rowlock) set VBU15 = isnull(VBU15 ,0) - @VCA20 where VBU01 = @VBU01 END IF @@ERROR > 0 BEGIN SET @Result = 3 IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 2 END --更新病人余额表 Update VBM1 with(rowlock) set VBM05 = isnull(VBM05 , 0) + @VAJ38 where VAA01 = @VAA01 and VBM03 = 3 IF @@ERROR > 0 BEGIN SET @Result = 3 IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 2 END --写入会员交易跟踪 EXEC Core_NewId_VCA01 @VCA01 out INSERT INTO VCA1 (VCA01, VAA01, VAA07, ACF01, VBU01 , VCB04 ,VCA07, VAI01, VCA09 ,VCA10, VBU14, VBU15 ,BCE03, VCA18, BCE01 , VAK01 , VBL01 , VCA20) SELECT @VCA01, @VAA01, @VAA07, @ACF01, @VBU01 ,@VCB04 ,@AWorkType, @VAI01, @AType ,@VAJ38, @CVBU14 , @CVBU15 , @BCE03, getdate(), @BCE01 , @VAK01 , @VBL01 , @VCA20 --如果参数设置为自动结算，病人没有医保登记记录，调用自动结算过程 If (@AutoCharge = 1) and (@AWorkType <= 4 or @AWorkType = 8) BEGIN If not exists(select * from IAR1 with(nolock) where VAA01 = @VAA01 and VAA07 = @VAA07 and IAR32 = 1 and ACF01 <> 2) BEGIN DECLARE @AutoVAK01 int Exec @ErrId = HORate_VAK1_AutoCharge @VAI01 , @VCA01 , @AutoVAK01 out If @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 2 END END END COMMIT TRAN --基本帐户交易时产生积分 IF @VAJ38 <> 0 --基本帐户交易额 BEGIN DECLARE @Cur_VBU17 numeric(18,2),@Style int SET @Style=@ACF01*10 --10表示门诊消费；20表示住院消费 EXEC @ErrId=Customer_VBU17_INC @VBU01,@Style,@VCA01,@VAK01,@VBL01,@VAJ38,@BCE03 IF @ErrId>0 OR @@ERROR>0 BEGIN --ROLLBACK TRAN RAISERROR('基本帐户交易产生积分时发生错误。',16,1) WITH NOWAIT RETURN @@ERROR END END --基本帐户交易时自动升级 IF @VAJ38>0 --基本帐户交易额 BEGIN EXEC @ErrId=Customer_Upgrade_update @VBU01,@BCE01,@BCE03 IF @ErrId>0 OR @@ERROR>0 BEGIN --ROLLBACK TRAN RAISERROR('基本帐户交易自动升级时发生错误。',16,1) WITH NOWAIT RETURN @@ERROR END END IF @@ERROR > 0 BEGIN SET @Result = 3 --IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 2 END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --============================================================================================================ --Author <...fyq> --Alter date <...2015-01-28> --Description<...门诊结帐过程(合并结帐)> --2011.07.19,HHY,会员非帐户支付时也产生积分 --2011.08.25,HHY,会员积分支付 --2011.08.30,FYQ,结帐时更新CRM中对应本次就诊ID的预约记录为就诊,写入客户服务日志 --2011.09.19,HHY,积分余额不足时终止结算 --2011.09.20,HHY,卡消费时自动升级；伊莱美药品、材料和检验不送积分 --2012.05.07,FYQ,结算时更新CRM跟进人金额 --2012.05.28,FYQ,挂账时允许录入挂账单位 --2012.12.18,FYQ,如果存在医保结算记录，本地未传入医保支付方式，则不允许结算 --2012.12.31,FYQ,结算时改变CRM预约中状态 --2013.06.17,FYQ,体检病人结算时更新病人缴费明细表 --2013.07.09,FYQ,部分退费生成单据结算后，更新其处理状态为收费 --2013.10.18,FYQ,赠送或者积分类型的支付方式保存时校验是否会员病人 --2013.12.24,FYQ,会员卡支付方式只校验未设置接口驱动的记账类型 --2013.12.25,FYQ,前台传入多会员卡支付方式(用其他人卡消费) --2013.12.26,FYQ,加入多卡扣费过程 --2014.01.21,FYQ,结账模式判断区分体检、His病人 --修改VCB04长度 --2014.09.16,FYQ,医保病人结算时写入医保结账关联记录 --2014.11.25,8065,根据药房参数写入药品摆药单 --2014.12.31,8065,减小锁粒度 --============================================================================================================= ALTER PROC [HORate_VAK1_Update] @Xml ntext,@VAK01 int out,@VAK04 varchar(20) out,@VAK06 int =2 AS DECLARE @iDOM int, @iret int Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML if @iret > 0 BEGIN exec sp_xml_removedocument @iDOM RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait return @iret END --获取操作员基本信息 DECLARE @EmpId int , @EmpNo varchar(20) , @EmpName varchar(20) , @HostIp varchar(256) , @HostName varchar(256) , @DeptId int , @SCA01 int , @SCH01 int , @DeptNo varchar(20) , @DeptName varchar(20) , @HostMac varchar(256) , @FileVer varchar(20) , @Event varchar(4086) , @SCH12 varchar(1024) , @TotalVBL numeric(18,4) , @VAA07 int SELECT @EmpId = EmpId , @EmpNo = EmpNo , @EmpName = EmpName , @DeptId = DeptId , @DeptNo = DeptNo , @DeptName = DeptName , @HostIp = HostIp , @HostName = HostName , @HostMac = HostMac , @FileVer = FileVer FROM OpenXml(@iDOM,'/Root/OperInfo',8) WITH ( EmpId int , EmpNo varchar(20) , EmpName varchar(20) , DeptId int , DeptNo varchar(20) , DeptName varchar(20) , HostIp varchar(256) , HostName varchar(256) , HostMac varchar(256) , FileVer varchar(20) ) --如果发现当前的版本号低于指定的Dll文件版本号，提示客户升级 Declare @ISSAME int , @Description varchar(1024) select @ISSAME = ISSAME ,@Description = Description from dbo.[FileVerCheck](@FileVer , 'HORate.dll') If @ISSAME = 0 BEGIN exec sp_xml_removedocument @iDOM RAISERROR(@Description, 16, 1) with nowait RETURN 2 END --病人信息 SELECT * INTO #TmpVAA1 from OpenXml(@iDOM,'/Root/VAA/Ie',8) WITH ( VAA01 int , VAA07 int , ACF01 int , VAA03 varchar(20) , VAK07 numeric(18,4) , VAK08 numeric(18,4) , VAK09 numeric(18,4) , VAK10 numeric(18,4) , BCK01A int , BCE01A int , BCE02A varchar(20) , BCE03A varchar(20) , EmpId int , EmpNo varchar(20) , EmpName varchar(20) , HostName varchar(128) , HostIP varchar(32) , WorkName varchar(20) , WorkType smallint , WorkFrom smallint , VAK17 varchar(255) , KuoZhan1 varchar(255) , VBU01 int ) IF NOT EXISTS(SELECT * FROM #tmpVAA1) BEGIN EXEC sp_xml_removedocument @iDOM RAISERROR('提交错误，病人信息为空', 16, 1) with nowait RETURN 2 END IF EXISTS(SELECT * FROM #tmpVAA1 where ISNULL(BCK01A , 0) <= 0) BEGIN EXEC sp_xml_removedocument @iDOM RAISERROR('提交错误，病人科室不能为空', 16, 1) with nowait RETURN 2 END SELECT @VAA07 = VAA07 from #TmpVAA1 SET @VAA07 = ISNULL(@VAA07 , 0) --如果当前系统参数设置为合并结帐，提示操作员关闭结帐窗口，重新结帐 If exists(select * from VAC1 with(nolock) where VAC01 = @VAA07 and VAC09 <> -3) BEGIN If not exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 93 and Value='1') BEGIN If (@VAK06 = 2) and exists(select * from SYS_Parameters where ProductID = 100 and ProgramID = 103010 and ParamNo = 6 and Value = 1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('系统结帐参数已经改变为[分单结帐]模式，请关闭收费窗口，重新进入后结帐.', 16, 1) with nowait RETURN 2 END END END --结帐总金额不能为负数 IF NOT EXISTS(SELECT * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 33 and Value = 1) BEGIN IF EXISTS(SELECT * from #TmpVAA1 where VAK07 < 0) BEGIN EXEC sp_xml_removedocument @iDOM RAISERROR('提交错误，结帐总金额不能为负数.', 16, 1) with nowait RETURN 2 END END --病人交款信息 select IDENTITY(int,1,1) AS ID, * into #tmpVBL1 from OpenXml(@iDOM,'/Root/VBL/Ie',8) with ( VBL01 int , FAF01 int , VBL02 varchar(20) , VBL11 varchar(20) , VBL13 numeric(18,4) , VBL14 varchar(32) , VBL15 varchar(2) , VBU01 int , BAQ03 varchar(64) ) --如果存在尾数处理，并且金额大于1，提示错误 If exists(select * from #tmpVBL1 where VBL15 = '98' and ABS(VBL13) > 1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，尾数金额不能大于1，请检查.', 16, 1) with nowait RETURN 2 END --如果存在没有会员帐号的记帐类型的支付方式不允许 If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '05' and @VAK06 = 2) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，记帐类型支付方式必须与会员卡关联', 16, 1) with nowait RETURN 2 END --如果存在没有会员帐号的赠送类型的支付方式不允许 If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '42' and @VAK06 = 2) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，赠送类型支付方式必须与会员卡关联', 16, 1) with nowait RETURN 2 END --如果存在没有会员帐号的积分类型的支付方式不允许 If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and ISNULL(VBU01 , 0) = 0 and VBL15 = '43' and @VAK06 = 2) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，积分类型支付方式必须与会员卡关联', 16, 1) with nowait RETURN 2 END If exists(select * from #TmpVAA1 where ISNULL(VBU01 , 0) = 0 and @VAK06 = 1) BEGIN If exists(select * from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and VBL15 = '05' ) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，记帐类型支付方式必须与会员卡关联', 16, 1) with nowait RETURN 2 END END --多个会员卡消费情况 select IDENTITY(int,1,1) AS ID, * into #tmpVCA1 from OpenXml(@iDOM,'/Root/VCA/Ie',8) with ( VBU01 int , VCB04 varchar(64) , VCA10 numeric(18 ,4) ) --存在会员卡接口支付方式，未传入卡号消费的情况报错 If exists(select * from #tmpVBL1 a, BBP1 b where a.VBL14 = b.BBP02 and ISNULL(BBP09 ,0) = 1 and a.VBL15 ='05') BEGIN If not exists(select * from #tmpVCA1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，记帐类型接口类型支付方式未传入待扣费会员卡信息。', 16, 1) with nowait RETURN 2 END END --by 潇湘子2010-12-18，折扣全为0的情况 IF EXISTS(SELECT * FROM #tmpVAA1 WHERE VAK07 > 0) BEGIN IF (NOT EXISTS(SELECT * FROM #tmpVBL1)) BEGIN EXEC sp_xml_removedocument @iDOM RAISERROR('提交错误，病人交款信息为空', 16, 1) with nowait RETURN 2 END END select @TotalVBL = SUM(VBL13) from #tmpVBL1 --医保结算信息 select IDENTITY(int,1,1) AS ID, * into #tmpIAS1 from OpenXml(@iDOM,'/Root/IAS/Ie',8) with ( IBL01 int, IAS01 int, VAI01 int ) --如果存在医保结算记录，本地未传入医保支付方式，则不允许结算 If exists(select * from #tmpIAS1 a join IAS1 b with(nolock) on b.IAS01 = a.IAS01 where ISNULL(b.IAS45 ,0) + ISNULL(b.IAS46 , 0) > 0 ) BEGIN If not exists(select * from #tmpVBL1 where VBL15 = '03') BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，未传入已经结算的医保记帐信息.', 16, 1) with nowait RETURN 2 END END --病人收费单据 select IDENTITY(int,1,1) AS ID, * into #tmpVAI1 from OpenXml(@iDOM,'/Root/VAI/Ie',8) with ( VAI00 varchar(38) , VAI01 int , VAI04 varchar(20) , VAI05 varchar(1024) , Enabled smallint , VAI17 smallint ) If not exists(select * from #tmpVAI1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人单据信息为空', 16, 1) with nowait RETURN 2 END --如果传入后台的单据费用中存在VAI01 <- 0，抛错 If exists(select * from #tmpVAI1 where VAI01 <= 0) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，费用单据信息不正确.', 16, 1) with nowait RETURN 2 END --病人收费记录信息 select IDENTITY(int,1,1) AS ID, * into #tmpVAJ1 from OpenXml(@iDOM,'/Root/VAJ/Ie',8) with ( VAJ01 int , ROWNR int , VAI00 varchar(38) , VAI01 int , BDN01 varchar(2) , BBY01 int , VAJ22 smallint , VAJ23 smallint , VAJ24 numeric(18,4) , VAJ25 numeric(18,4) , VAJ26 smallint , VAJ27 int , BCK01D int , VAJ30 numeric(9,4) , VAJ31 numeric(9,4) , VAJ59 numeric(18,6) , VAJ32 numeric(18,4) , VAJ33 numeric(18,4) , VAJ34 numeric(18,4) , VAJ35 varchar(20) , VAJ36 numeric(18,4) , VAJ37 numeric(18,4) , VAJ38 numeric(18,4) , VAJ39 smallint , VAJ61 numeric(18,4) ) Declare @i int , @j int , @VAA01 int , @VAI01 int , @VAI16 int , @Count int , @Value int , @VBL01 int , @VBU01 int , @IAS01 int , @ErrId int , @BCK01A int , @BCK01B int , @BCE01A int , @Result int , @BBP02 varchar(32) , @VAK09 numeric(18 ,2) , @VAK10 numeric(18 ,2) , @IsHyType int , @BBP06 tinyint , @WorkType smallint , @AWorkType smallint , @WorkFrom smallint , @VBL02 varchar(20) , @VAK07 numeric(18,4) , @WorkName varchar(20) , @VAK08 numeric(18,4) , @BCE02A varchar(20) , @BCE03A varchar(20) , @VAK17 varchar(255) , @SumAAO05 numeric(18,4) , @SumAAO42 numeric(18,4) , @SumVCA10 numeric(18,4) , @SumVAJ38 numeric(18,4) , @YbMoney numeric(18,4) , @CashMoney numeric(18,4) , @VAK13 datetime /*========================================================数据校验=============================================================*/ If not exists(select * from #tmpVAI1 I join #tmpVAJ1 J on J.VAI00 = I.VAI00) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('保存错误，重新操作。', 16, 1) with nowait RETURN 2 END If not exists(select * from #tmpVAJ1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人收费记录信息为空', 16, 1) with nowait RETURN 2 END --如果存在没有明细的单据信息则返回 If exists(select * from #tmpVAI1 where VAI00 not in(select VAI00 from #tmpVAJ1)) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，存在没有明细的单据信息，请重新检索收费信息.', 16, 1) with nowait RETURN 2 END /*======================================如果是会员模式，并且存在多个类型为“记帐”的支付方式，不允许==================================*/ If (dbo.GetWorkingMode() > 0) BEGIN If exists(select AAO01 from BBP1 where AAO01 = '05' and ISNULL(BBP09 ,0) = 0 group by AAO01 having COUNT(AAO01) > 1) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，会员模式下只允许一种类型为“记帐”的支付方式', 16, 1) with nowait RETURN 2 END END exec sp_xml_removedocument @iDOM select @BBP02 = VBL14 from #tmpVBL1 where VBL15 = '03' select @YbMoney = SUM(ISNULL(VBL13 , 0)) from #tmpVBL1 where VBL15 = '03' select @CashMoney = SUM(ISNULL(VBL13 ,0)) from #tmpVBL1 where VBL15 = '01' set @YbMoney = ISNULL(@YbMoney , 0) set @CashMoney = ISNULL(@CashMoney , 0) select @BBP06 = ISNULL(BBP06 , 0) from BBP1 where AAO01 = '03' and BBP02 = @BBP02 IF ISNULL(@BBP06 , 0) = 0 --本地医保输入 set @YbMoney = 0 else set @YbMoney = isnull(@YbMoney , 0) select @VAA01 = VAA01,@VAA07 = VAA07 ,@WorkName = WorkName,@WorkType = WorkType,@EmpId = EmpId,@EmpNo = EmpNo,@EmpName = EmpName, @BCK01A = BCK01A,@WorkFrom = WorkFrom ,@VAK07 = VAK07,@VAK08 = VAK08 + @YbMoney ,@VAK17 = VAK17 ,@BCE01A = BCE01A, @BCE02A = BCE02A, @BCE03A = BCE03A ,@VBU01 = isnull(VBU01 ,0) , @VAK09 = VAK09 , @VAK10 = VAK10 from #tmpVAA1 select @IsHyType = dbo.GetWorkingMode() /*===========================================如果实收金额大于0，判断实收金额是否 = 现金支付 + 找零=======================================*/ If (@VAK09 > 0 ) and (ABS(@CashMoney) > 0) and (@VAK06 = 2) BEGIN If @VAK09 <> @CashMoney + @VAK10 BEGIN RAISERROR('提交错误，实收金额不等于(现金支付 + 找零)，请检查.', 16, 1) WITH NOWAIT Return 1 END END /*===================================================判断支付方式总额与结帐金额是否相等===================================================*/ If @VAK07 <> @TotalVBL BEGIN RAISERROR('提交错误，支付方式总额与结帐金额不等，请检查.', 16, 1) WITH NOWAIT Return 1 END --查找后台的金额合计 select @SumVAJ38 = sum(a.VAJ38) from VAJ1 a with(nolock) join #tmpVAI1 b on b.VAI01 = a.VAI01 --join #tmpVAJ1 c on c.VAI00 = b.VAI00 where b.Enabled = 0 and a.VAJ05 = 1 /*===================================================如果病人没有ID,不让保存数据=========================================================*/ IF (@VAA01 <= 0) OR (@VAA07 <= 0) BEGIN RAISERROR('提交错误，病人信息为空.', 16, 1) WITH NOWAIT Return 1 END /*=================================================如果尾数金额大于1,不让保存数据========================================================*/ If abs(@VAK07 - @VAK08) >= 1 BEGIN RAISERROR('尾数金额错误，不能大于1.', 16, 1) WITH NOWAIT Return 1 END /*=================================================判断尾数金额与前台传入尾数金额是否相等================================================*/ If exists(select * from #tmpVBL1 where VBL15 = '98' and (VBL13 <> (@VAK07 - @VAK08))) BEGIN RAISERROR('提交错误，前台传入尾数金额与支付方式中尾数金额不等.', 16, 1) WITH NOWAIT Return 1 END /*==============================================判断如果明细中有已经结账的信息，则退出===================================================*/ IF EXISTS(SELECT * FROM #tmpVAI1 a JOIN VAI1 b with(nolock) ON a.VAI01 = b.VAI01 WHERE a.Enabled = 0 AND b.VAI16 = 2 ) BEGIN RAISERROR('待结账信息中存在已结账单据.', 16, 1) WITH NOWAIT Return 1 END /*=============================================判断如果前台跟后台的金额不符，则提示不让结帐===============================================*/ IF @VAK07 <> @sumVAJ38 BEGIN RAISERROR('前台传入金额与后台合计不一致，请重新检索.', 16, 1) WITH NOWAIT Return 1 END /*=====================================================生成病人交款、明细临时表============================================================*/ SELECT * INTO #tmpVAIVCA FROM ( SELECT VAI01 FROM #tmpVAI1 WHERE Enabled = 0 UNION ALL SELECT b.VAI01 FROM #tmpVAI1 a join VAI1 b with(nolock) on b.VAI01A = a.VAI01 WHERE Enabled = 0 ) a SELECT @i = 0 , @Count = COUNT(1) FROM #tmpVBL1 EXEC @ErrId = Core_NewIDEx 'VBL1' , 'VBL01' , @Value out , @Count If @ErrId > 0 BEGIN RETURN 1 END WHILE @i < @Count BEGIN SET @VBL02 = '000000000' SET @VBL02 = @VBL02 + Cast(@Value - @Count + @i + 1 AS VARCHAR) SET @VBL02 = 'L' + SUBSTRING(@VBL02, Len(@VBL02) - 9, 10) UPDATE #tmpVBL1 SET VBL01 = @Value - @Count + @i + 1, VBL02 = @VBL02 WHERE [ID] = @i + 1 SET @i = @i + 1 END BEGIN TRAN --开始事务 /*=====================================================保存病人结账信息及交款信息==========================================================*/ --写入病人结帐单据,生成结帐单据号及ID DECLARE @VAK21 numeric(18,4),--病人费用总额包含尾数处理 @VAK23 numeric(18,4), @VAK24 numeric(18,4) Exec @ErrId = Core_NewIdEx 'VAK1','VAK01',@VAK01 out If @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 1 END SET @VAK04 = '000000000' SET @VAK04 = @VAK04 + Cast(@VAK01 AS VARCHAR) SET @VAK04 = 'K' + SUBSTRING(@VAK04, Len(@VAK04) - 9, 10) SET @VAK17 = '结账方式：[合并结账] ' + ISNULL(@VAK17 , '') IF isnull(@VAK17,'') ='' --如果没有结账备注信息，则写入默认的备注信息 BEGIN SET @VAK17 = @WorkName END --插入数据 INSERT INTO VAK1(VAK01, VAA01, VAA07, VAI04, FAB03, VAK06, VAK07, VAK08, VAK09, VAK10, BCE02A, BCE03A, VAK13, VAK01A, VAK15, VAK16, VAK17, VAK18, VAK19 , BCE01A , VAK28) SELECT @VAK01, @VAA01, @VAA07, @VAK04, '', @VAK06, VAK07 - @VAK08, VAK08 + @YbMoney , VAK09, VAK10, EmpNo, EmpName, getdate(), 0, getdate(), getdate(), @VAK17, 0, 1, EmpID , @VAK01 from #tmpVAA1 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入病人结帐单据时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --如果是挂号，结帐成功后，更新挂号状态为1 If @VAK06 = 1 BEGIN Update VAC1 with(rowlock) set VAC45 = 1 where VAC01 = @VAA07 END --写入病人交款单据 INSERT INTO VBL1(VBL01, VBL02, VBL03, VBL04, VBL05, VAA01, VAA07, BCK01, VBL11 ,VBL12, VBL13, VBL14, VBL15, VAK01, VBL17, VBL18, VBL19, VBL01A, BCE01, BCE02, BCE03, VBL24, VBL25, VBL26, VBL27 , VBU01 , FAF01 , BAQ03 ) Select VBL01 , VBL02, '', @VAK06 + 1, 0, @VAA01, @VAA07, @DeptId, VBL11 ,@WorkName, VBL13, VBL14, VBL15, @VAK01, 0, getdate(), getdate(), 0, @EmpID, @EmpNo, @EmpName, 0, 0, 0, 2 , VBU01 , ISNULL(FAF01 , 0), BAQ03 from #tmpVBL1 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入病人交款记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*======================================积分消费和现金支付产生积分================================================================================*/ IF @VBU01>0 BEGIN DECLARE @Exchange numeric(18,2),@tmpVBL01 int --会员积分支付 SELECT @Exchange=-SUM(VBL13) FROM #tmpVBL1 WHERE VBL15='43' SELECT @tmpVBL01=VBL01 FROM #tmpVBL1 WHERE VBL15='43' IF ISNULL(@Exchange,0)<>0 BEGIN EXEC @ErrId=Customer_VBU17_DESC @VBU01,13,@VAK01,@tmpVBL01,@Exchange,@EmpName IF @ErrId > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('会员客户积分支付时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END --会员非帐户支付时也产生积分 IF dbo.GetCRMMode()=2--伊莱美：药品、检验不送积分 BEGIN DECLARE @Exchange2 numeric(18,2) --不能送积分的支付方式合计 SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 NOT IN('01','02','03','05') --能送积分的项目合计 SELECT @Exchange2=SUM(VAJ38) FROM #tmpVAJ1 WHERE BDN01>'4' AND BDN01<>'L' SET @Exchange=ISNULL(@Exchange2,0)-ISNULL(@Exchange,0) END ELSE SELECT @Exchange=SUM(VBL13) FROM #tmpVBL1 WHERE VBL15 IN('01','02','03','05') IF ISNULL(@Exchange,0)>0 BEGIN EXEC @ErrId=Customer_VBU17_INC @VBU01,11,0,@VAK01,0,@Exchange,@EmpName IF @ErrId>0 OR @@ERROR<>0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('会员客户现金交易产生积分时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END --卡消费时自动升级 IF @Exchange>0 BEGIN EXEC @ErrId=Customer_Upgrade_update @VBU01,@EmpId,@EmpName IF @ErrId>0 OR @@ERROR<>0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('卡消费自动升级时发生错误。',16,1) WITH NOWAIT RETURN 1 END END END /*======================================如果病人持卡消费，对于当前已经扣费的金额返还到病人卡上，然后再进行扣费====================================*/ IF (@IsHyType >= 1) and exists(select * from VBU1 where VAA01 = @VAA01) BEGIN If @VAK06 = 1 set @AWorkType = 5 else if @VAK06 = 2 set @AWorkType = 6 --如果该病人存在会员交易跟踪 select @SumVCA10 = -sum(VCA10) from VCA1 a join #tmpVAIVCA b on b.VAI01 = a.VAI01 --退还已扣费金额到病人卡上 If abs(Isnull(@SumVCA10 , 0)) > 0 BEGIN Exec HORate_CardCharge_Update @VAA01 , @VAA07 , 0 , 1 , @EmpId , 0 , @EmpNo ,@EmpName , @SumVCA10 , @Result out , 1 , @AWorkType , @VAK01 If @Result =3 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('一卡通病人扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END SELECT @SumAAO05 = sum(Isnull(VBL13 , 0)) from #tmpVBL1 a ,BBP1 b where a.VBL14 = b.BBP02 and ISNULL(b.BBP09 ,0) =0 and VBL15 = '05' SELECT @SumAAO42 = sum(Isnull(VBL13 , 0)) from #tmpVBL1 where VBL15 = '42' and VBU01 > 0 If (ABS(ISNULL(@SumAAO05 ,0)) > 0) or (ABS(ISNULL(@SumAAO42 , 0)) > 0) BEGIN --从会员卡上扣除用卡支付部分的金额 If exists(select * from #tmpVBL1 where VBL15 = '05' or (VBL15 = '42' and VBU01 > 0)) BEGIN SELECT @VBL01 = Isnull(VBL01 , 0) from VBL1 a with(nolock) join BBP1 b on b.BBP02 = a.VBL14 WHERE VAK01 = @VAK01 and VBL15 = '05' and ISNULL(b.BBP09 , 0) = 0 If ISNULL(@VBL01 , 0) = 0 SELECT @VBL01 = VBL01 from VBL1 where VAK01 = VAK01 and VBL15 = '42' and VBU01 > 0 Exec HORate_CardCharge_Update @VAA01 , @VAA07 , 0 , 1 , @EmpId , 0 , @EmpNo ,@EmpName , @SumAAO05 , @Result out , 0 , @AWorkType , @VAK01 , @VBL01 , @SumAAO42 --余额不足 If @Result = 2 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 1 END If @Result = 3 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('一卡通病人扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END END END /*=============================================================其他会员卡扣费===============================================================*/ If exists(select * from #tmpVCA1) BEGIN If @VAK06 = 1 set @AWorkType = 5 else if @VAK06 = 2 set @AWorkType = 6 SELECT @VBL01 = Isnull(VBL01 , 0) from VBL1 a with(nolock) join BBP1 b on b.BBP02 = a.VBL14 WHERE VAK01 = @VAK01 and VBL15 = '05' and ISNULL(b.BBP09 , 0) = 1 Exec HORate_CardUpdate_Interface @Xml , @VAA01 , @VAA07 , 1 , @EmpId , @EmpName ,@Result out ,0 , 0 ,@AWorkType,@VAK01 , @VBL01 If @Result = 3 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('使用会员卡扣费时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*===========================================================更新挂号状态为结算=============================================================*/ If @VAK06 = 2 BEGIN Update VAC1 with(rowlock) SET VAC70 = 1 where VAC01 = @VAA07 END /*============================================================更新病人收费单据==============================================================*/ If exists(select * from #tmpVAI1 a join VAI1 b on a.VAI01 = b.VAI01 where b.VAI16 = 9 and a.Enabled = 0) BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('病人存在已作废的费用单据，请重新检索.，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END Update a set a.VAI16 = 2 , a.VAI18 = @WorkType , a.VAK01 = @VAK01 from VAI1 a with(rowlock) join #tmpVAI1 b on a.VAI01 = b.VAI01 where a.VAI16 = 1 and b.Enabled = 0 /*===========================================================写入病人收费记录明细信息============================================================*/ If exists(select * from #tmpVAJ1 a join VAJ1 b with(nolock) on a.VAJ01 = b.VAJ01 where b.VAJ05 = 9) BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('病人存在已作废的费用明细，请重新检索.，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END update a set a.VAJ05 = 2 , a.VAK01 = @VAK01 , a.VAJ04 = @WorkType from VAJ1 a with(rowlock) join #tmpVAI1 b on a.VAI01 = b.VAI01 join #tmpVAJ1 c on b.VAI00 = c.VAI00 where b.Enabled = 0 and a.VAJ05 = 1 select @VAI01 = VAI01 from VAJ1 with(nolock) where VAK01 = @VAK01 order by VAI01 desc /*======================================================更新结账费用总额,包含尾数处理==============================================================*/ select @VAK21 = sum(VAJ36),@VAK23 = sum(VAJ38),@VAK24 = sum(VAJ61) from VAJ1 with(nolock) where VAK01 = @VAK01 update VAK1 with(rowlock) set VAK21 = @VAK21, VAK23 = @VAK23, VAK24 = @VAK24 where VAK01 = @VAK01 /*========================================================更新本地医保结算信息=====================================================================*/ If Exists(select * from #tmpIAS1) BEGIN --SELECT @IAS01 = IAS01 from #tmpIAS1 --IF Isnull(@IAS01 , 0) > 0 --BEGIN /*Update IAS1 SET VAK01 = @VAK01 where IAS01 = @IAS01 */ Update a set a.VAK01 = @VAK01 from IAS1 a with(rowlock) join #tmpIAS1 b on b.IAS01 = a.IAS01 DECLARE @IBL01 int Exec Core_NewIDEx 'IBL1' , 'IBL01' , @IBL01 out SELECT @i =0 , @Count = COUNT(0) from #tmpIAS1 while @i < @Count BEGIN Update #tmpIAS1 SET IBL01 = @IBL01 - @Count + @i + 1 where ID = @i + 1 SET @i = @i + 1 END INSERT INTO IBL1(IBL01 , IAS01 , VAK01) SELECT c.IBL01 , a.IAS01 , @VAK01 from IAS1 a join #tmpIAS1 c on a.IAS01 = c.IAS01 IF @@ERROR > 0 --如果发生错误则回滚数据 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入医保结账记录关联时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --END END /*=======================================================删除业务单据通知中的数据==================================================================*/ If Exists(select * from VBX1 where VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2) BEGIN /*Delete from VBX1 where VBX19 in(select VAI01 from #tmpVAI1 where VAI01 > 0 and Enabled = 0) and VAA01 = @VAA01 and VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2 */ Update a SET VBX22 = 2 from VBX1 a with(rowlock) join #tmpVAI1 b on b.VAI01 = a.VBX19 where b.Enabled = 0 and a.VAA01 = @VAA01 and a.VAA07 = @VAA07 and ACF01 = 1 and VBX03 = 1 and VBX04 = 2 END /*==================================================更新CRM中对应本次就诊ID的预约记录状态为就诊====================================================*/ If @VAK06 = 2 --门诊结算 BEGIN Update SCF1 with(rowlock) SET SCF11 = 5 where VAA07 = @VAA07 and ACF01 = 1 END ELSE If @VAK06 = 1 --挂号 BEGIN Update SCI1 with(rowlock) SET SCI11 = @VAK08 , SCI05 = @VAK01 where VAA07 = @VAA07 and ACF01 = 1 Update SCF1 with(rowlock) SET SCF11 = 4 where VAA07 = @VAA07 and ACF01 = 1 and SCF11 <= 2 END /*=====================================================体检病人结算后更新病人缴款明细表==========================================================*/ If exists(select * from VAC1 where VAC01 = @VAA07 and VAC09 = -3) BEGIN Declare @VBM01 int Exec HORate_VBM1_Update @VBM01 out , @VAA01 , 4 , 0 , @VAK23 , 0 ,@VAA07 END /*================================================================================================================================================*/ Commit Tran /*============================================================写入客户服务日志===================================================================*/ If @VAK06 = 2 BEGIN SELECT @SCA01 = SCA01 from SCA1 where VAA01 = @VAA01 and SCA37 <> 2 If ISNULL(@SCA01 , 0) > 0 BEGIN SELECT @VAK13 = VAK13 from VAK1 where VAK01 = @VAK01 SET @SCH12 = '结帐时间：' + convert(varchar(30) , @VAK13 , 111) + ' 结帐金额：' + CAST(@VAK08 as varchar(20)) Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAK1' , 'VAK01' , 106 , '门诊收费' , @SCH12 , 0 , 0 , @EmpId ,@EmpName END END /*==============================================================根据参数写入药品摆药单============================================================*/ If exists(select * from Sys_Parameters where productid =100 and programid = 111001 and paramno = 63 and Value='1') BEGIN If exists(select * from #tmpVAJ1 where BDN01 <= '3') BEGIN SELECT BCK01D INTO #tmpDSY1 from #tmpVAJ1 where BDN01 <= '3' GROUP BY BCK01D INSERT INTO DSY1(VAA01 , VAA07 , ACF01 ,VAK01 , PNRCP , DFLAG , BCK01) SELECT @VAA01 , @VAA07 , 1 , @VAK01 , 0 , 0 , BCK01D from #tmpDSY1 END END /*=================================================================================================================================================*/ GO