if not exists(select * from syscolumns where name = 'BCK01A' and object_Name(id)='VEZ1') begin Alter table VEZ1 Add BCK01A int default(0) with values end go /****** Object: StoredProcedure [HOPatient_VAE1_Update] Script Date: 2020-12-18 16:50:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --存储过程.HOPatient_VAE1_Update /* 2011-08-24,by HHy,会员病人绑定病人费别 */ --============================================================================================================== --Author <...8065> --Alter Date <...2020-02-19> --Description<...入院登记过程> --2011-12-27 增加籍贯、出生地址、健康卡号、身高、体重等录入信息 --2012.02.08 增加CRM预约记录对应 --2012.02.14 增加住院号生成规则，按年月生成 --2012.03.19 判断住院次数是否存在-- by wht --2012.04.25 增加住院就诊ID --2012.05.04 入院登记时增加CRM跟进人录入 --2012.10.26 出院状态不允许编辑 --2012.11.08 CRM客户经理用另外的过程实现 --2013.01.28 计算详细日期调用医生站计算详细日期函数 --2013.04.19 增加部分地址信息录入，与病案首页保持一致 --2013.07.15 增加病人余额表限制费用总额、补充费用总额记录 --2013.08.14 入院登记调整病人类别重新计算信用额度 --2013.12.05 新增入院登记时清空一部分病人信息(信用额度、担保金额) --2014.01.15 增加对住院号前缀YY（取年份最后两位）的支持 --2014.03.05 增加监护人信息的维护 --2014.06.20 入院登记支持保存就诊卡 --2014.08.29 禁止修改已出院病人的姓名和住院号（权限） --2014.10.11 增加了解途径维护 --2014.01.23 VAE02修改为住院号+入院次数 --2015.06.02 已结账是否允许修改姓名及住院号，提前到事务之前 --2015.11.24 前台住院次数小于1时，系统自动获取病人最大住院次数 --2016.03.28 增加病人身份证详细信息保存,包括病人照片、身份证签发机关等 --2016.07.11 增加病人资料变动同步到CRM客户资料功能 --2016-08-24 记录移动电话变更记录 --2016.09.09 校验前台传入的费别是否适用住院 --2016.10.20 记录身份证变更记录 --2106.11.07 入院登记增加记录住院号、姓名、性别、出生日期、身份证 --2016.11.08 入院登记根据参数自动生成新的住院号 --2016.11.23 取最近一次入院扩展信息进行补充更新 --2016.12.15 入院登记自动插入1条VAM1记录 --2017.03.21 入院床位变动更新床位表状态 --2017.05.03 根据参数预约入院时不生成住院号 --2017.08.30 增加监护人证件类型、证件号码维护 --2018.05.14 增加入院登记扩展字段维护 --2018.05.17 增加病人资料扩展字段维护 --2018.05.30 根据参数处理留观登记新生成住院号 --2018.10.15 预约床位更新床位表就诊ID字段 --2018.11.13 诊断支持按医保疾病目录记录 --2019.01.30 入院诊断允许输入多个 --2019.09.05 YSP 新增信用额度上限更新到表VBM2的VBM14字段 --2019.09.26 8065 防止入科时入院登记编辑未关闭,某些信息如果前台未传入,取后台信息 --2019.10.24 YSP 参数开启手工输入住院号，但是另一个参数开启@IS_ManualInputSave，手工输入住院号时允许保存时自动生成,前台输住院号就后台自动生成 --2020.01.03 YSP 新增VEZ31保存镇 --2020.03.10 YSP 新增住院号前缀支持YYMMDD --2020.03.10 YSP 新增如果月子中心单独编码 同时 科室有月子中心的属性 则调用月子中心的生成编码 --2020.06.12 YSP 没有住院号前缀时查找纯数字最大的 --2020.10.13 YSP 自动添加社区信息 --2020.10.14 YSP 当前台没有传拼音吗和五笔码时，后台自动生成 --============================================================================================================= ALTER PROC [HOPatient_VAE1_Update] @XML ntext, --XML文档 @VAE01 int out , --登记号 @VBL01 int out, --缴款ID @DiagId int, --前就诊号(针对再入院情况) @EmpId int, --操作员ID @EmpDept int, --部门 @FAA01 int, --领用票据Id @EmpCode varchar(20), --操作员编码 @EmpName varchar(20), --操作员 @WorkStation tinyint, --工作站模式 1=病人管理 2=护士工作站 @WardNoLen int, --住院号长度 @WardNoPre varchar(30), --住院号前缀 @VAA01 int out, --病人ID @VAA04 varchar(20) out, --住院号 @VBO01 int =0 out, --病人变动记录 @ATTRS varchar(max) = '', --扩展字段内容 @ATTRS_Patient varchar(max) = '' --病人资料扩展字段内容 AS If exists(select * from VAE1 with(nolock) where VAE01 = @VAE01 and VAE44 = 5) BEGIN RAISERROR('病人已经出院，不允许修改登记信息.', 16, 1) with nowait return 1 END DECLARE @iDOM int, @iret int --解析XML文档 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 /* 病人信息表 病人登记表 病人诊断记录 病人预交款记录 写入临时表中 */ SELECT * INTO #tmpInHosPatInfo FROM OpenXml(@iDOM, '/Root/VAE1/Item', 8) WITH( VAE01 integer --登记Id ,VAE02 varchar(20) --登记号 ,VAA01 integer --病人Id ,VAA02 varchar(64)--会员卡号 ,VAA04 varchar(20)--住院号 ,VAA05 nvarchar(128) --姓名 ,ABW01 varchar(1)--性别 ,VAA12 datetime--出生日期 ,VAA15 varchar(20)--身份证号 ,VAA38 varchar(48)--学 ,ABQ02 varchar(32)--民族 ,ACM02 varchar(20)--从业状况、身份 ,ABBRP varchar(10)--拼音码 ,ABBRW varchar(10)--五笔码 ,VAA16 varchar(20)--其他证件 ,VAE04 tinyint --病人性质 ,ABJ01 varchar(2)--医疗付款方式 all ,BDP02 varchar(50)--病人类别 all ,ABC02 varchar(20)--折扣费别 all ,VAA20 varchar(64) ,VAA25 varchar(64) ,BDX02 varchar(64) ,VAE08 tinyint--再入院 ,BCK01A int--入院病区ID all ,BCK01B int--入院科室ID all ,VAE11 datetime--入院日期 ,ABO01 varchar(2)--入院病情 all ,ABR01 varchar(1)--入院方式 all ,ABT02 varchar(20)--就诊方式 all ,VAE15 varchar(64)--转院名称 ,ABZ02 varchar(10)--入院待遇 all ,ABK02 varchar(20)--住院目的 all ,BCQ04A varchar(20)--住院床位 all ,VAE19 int--住院次数 ,VAE20 tinyint--陪伴 ,AAG01 int--护理等级 all ,VAE22 varchar(2)--住院病情 ,BCK01C int --住院病区ID all ,BCK01D int --住院科室ID all ,BCQ04B varchar(20) --住院床位 all ,VAE26 datetime --出院日期 ,VAE27 int --住院天数 ,ABV01 varchar(2) --出院类型 all ,VAE29 tinyint --确诊 ,VAE30 datetime --确诊日期 ,VAE31 tinyint --新发肿瘤 ,ABL01 varchar(2) --血型 ,VAE33 int --抢救次数 ,VAE34 int --成功次数 ,VAE35 tinyint --随诊标志 ,VAE36 int --随诊期限 ,VAE37 varchar(2) --随诊单位 ,VAE38 tinyint --尸检标志 ,BCE02A varchar(20) ,BCE03A varchar(20)--门诊医师 all ,BCE03B varchar(20)--责任护士 all ,BCE03C varchar(20)--住院医师 all ,VAE42 varchar(64) --疾病兵种 ,VAE44 tinyint --状态 ,VAE45 numeric(18,4)--金额 ,VAE46 int --年龄 ,AAU01 varchar(1) --年龄单位 all ,ACK01 varchar(2) --婚姻状况 all ,AAT02 varchar(128) --职业 all ,ACC02 varchar(32) --国籍 all ,AAY02 varchar(48) --学历 all ,BAQ01 int --合约单位ID all ,BAQ02 varchar(10) --合约单位编码 all ,BAQ03 varchar(64) --工作单位 all ,VAE55 varchar(32) --单位电话 ,VAE56 varchar(64) --单位地址 ,VAE57 varchar(128) --户口地址 ,VAE58 varchar(6) --户籍邮编 ,VAE59 varchar(20) --户籍电话 ,VAE60 varchar(20) --省市 ,VAE61 varchar(64) --县市, 城市, 县区市 ,VAE62 varchar(64) --地址, 常住地址, 街道, 乡镇村, resident address ,VAE63 varchar(20) --电话 ,VAE64 varchar(13) --移动电话 ,VAE65 varchar(128) --电子邮箱 ,VAE66 varchar(64) --其他联系方式 ,VAE67 varchar(64) --监护人 ,VAE68 varchar(20) --联系人姓名 ,AAZ02 varchar(32) --与病人关系 ,VAE70 varchar(64) --联系人地址 ,VAE71 varchar(20) --联系人电话 ,VAE72 varchar(16) --联系人移动电话 ,VAE73 varchar(4) --中医治疗内别 ,IAA01 int --保险机构 all ,UAA01 int --社区 all ,UAA03 varchar(100) --社区名称 ,IAK05 varchar(50)--社会保障号 ,VAE76 tinyint --审核 ,BCE03D varchar(20) --审核人 ,VAE78 datetime --审核日期 ,VAE79 tinyint --上传标志 ,VAE80 tinyint --转出标志 ,BCE03E varchar(20) --登记人 all ,VAE82 datetime --制表时间 all ,VAE83 varchar(255) --备注 ,VAE84 int --档案状态 ,VAE85 datetime --存档时间 ,VAA50 varchar(6) --单位邮编 ,VAA51 varchar(64) --单位开户行 ,VAA52 varchar(20) --单位银行帐号 ,VAO01A int --门诊诊断号 ,VAO01B int --中医诊断号 ,VAO01C int --入院诊断 ,BAK01A int --门诊诊断 ,BAK01B int --中医诊断 ,BAK01C int --入院诊断 ,BAK05A varchar(128)--门诊诊断 ,BAK05B varchar(128)--中医诊断 ,BAK05C varchar(128)--入院诊断 ,VBL01 int--交款Id ,VBL02 varchar(20)--单据号 ,VBL03 varchar(20)--票据号 ,VBL04 tinyint--类型 ,VBL05 tinyint--状态 ,VBL13 numeric(18,4)--金额 ,VBL14 varchar(32)--支付方式 ,BAQ03B varchar(128)--工作单位 ,BBO02 varchar(64)--开户行 ,VBL11 varchar(20)--银行帐号 ,VBO01 int--病人变动记录 ,VAE86 varchar(32)--介绍人 ,BEP05 numeric(18,4)--报警值 ,BEP06 numeric(18,4)--信用额度 ,VAC01 int--挂号ID ,VAE88 datetime ,VAE89 numeric(9 ,3) ,VAE90 numeric(9 ,3) ,VAE91 numeric(9 ,2) ,VAE92 varchar(64) ,VAA82 varchar(64) ,SCF01 int ,CManageId int ,CManage varchar(20) ,QueryPeopleId int ,QueryPeople varchar(20) ,BCE02C varchar(20) ,VCT32 varchar(20) ,VCT33 varchar(20) ,VCT34 varchar(20) ,VCT35 varchar(20) ,VCT36 varchar(20) ,VCT37 varchar(20) ,VCT38 varchar(20) ,VCT39 varchar(20) ,VCT41 varchar(20) ,VCT42 varchar(20) ,BEP07 numeric(18 ,4) ,VBM11 numeric(18 ,4) ,VAB03 varchar(100) ,VAB04 datetime ,VAB05 datetime ,VCT99 varchar(2) ,VCTA1 varchar(20) ,VAA14 varchar(2) ,VEZ23 varchar(20) ,IAA01A int ,IAD01A int ,IAD03A varchar(32) ,IAD04A varchar(128) ,IAA01B int ,IAD01B int ,IAD03B varchar(32) ,IAD04B varchar(128) ,IAA01C int ,IAD01C int ,IAD03C varchar(32) ,IAD04C varchar(128) ,VEZ19 tinyint ,VEZ31 varchar(128) ,VEZ38 datetime ,BCK01M int ) --如果病人信息不存在，则退出 If not exists(select * from #tmpInHosPatInfo) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('提交错误，病人信息为空.', 16, 1) with nowait RETURN 2 END --如果住院序列号表为空，则退出 If not exists(select * from VAA1_ID_4) BEGIN exec sp_xml_removedocument @iDOM RAISERROR('住院号序列号表为空，不能生成住院号.', 16, 1) with nowait RETURN 2 END IF Object_id('tempdb..#tmpInhosVAO2') IS NOT NULL DROP TABLE #tmpInhosVAO2 SELECT IDENTITY(int , 1 , 1) as ID ,* INTO #tmpInhosVAO2 FROM OpenXml(@iDOM, '/Root/VAO2/Ie', 8) WITH ( VAO01 int , VAO07 smallint, VAO15 varchar(150), BAK01A int, DFLAG smallint ) --移除XML文档 exec sp_xml_removedocument @iDOM /*===================================如果病人信息不存在，则写入一条新的病人信息，否则更新病人资料====================================================*/ DECLARE @id int, @ErrId int, @PriKey int, @VAlue int, @AAG01 int, @ZAE01 int, @ABO01 int, @VAC01 int, @BAK01A int, @BAK01B int, @BAK01C int, @VAO01A int, @BCK01A int, @BCK01B int, @VAO01B int, @VAO01C int, @IAA01A int, @IAD01A int, @IAD03A varchar(32), @IAD04A varchar(128), @IAA01B int, @IAD01B int, @IAD03B varchar(32), @IAD04B varchar(128), @IAA01C int, @IAD01C int, @IAD03C varchar(32), @IAD04C varchar(128), @VAA10 int, @VAE04 tinyint, @AAU01 varchar(1), @VAE11 datetime, @VAE82 datetime, @VBL13 numeric(18,4), @tmpstr varchar(30), @BDP02 varchar(20), @ABC02 varchar(20), @VAE02 varchar(20), @BCE03B varchar(20), @BCE03C varchar(20), @BAK05A varchar(128),--门诊诊断 @BAK05B varchar(128),--中医诊断 @BAK05C varchar(128),--入院诊断 @VAA05 varchar(20), @OVAA05 varchar(20), @OVAA04 varchar(20), @OABC02 varchar(20), @OBDP02 varchar(20), @VAE87 varchar(20), @OVAE88 datetime, @VAE88 datetime, @SCH12 varchar(1024), @BCK03B varchar(20), @SCF01 int, @VAE19 int, @CManageId int , @CManage varchar(20) , @QueryPeopleId int , @QueryPeople varchar(20) , @SCI01 int , @VAA12 datetime , @BEP07 numeric(18 ,4) , @VBM11 numeric(18 ,4) , @VAA02 varchar(64) , @VAA35 varchar(13) , @OVAA35 varchar(13) , @VAA15 varchar(20) , @OVAA15 varchar(20) , @CurDate datetime , @InptNumAlone varchar(2) , @MzInptNumAlone varchar(2) , @InptNumBySelf varchar(2) , @MzInptNumNew varchar(2) , @BCQ04B varchar(20) , @OBCQ04B varchar(20) , @BCK01C int , @BookNoWardNo int , @VAE44 tinyint , @IsNewWardNo tinyint , @VBO06 tinyint, @IS_ManualInputSave int, --Add YSP 20191023 手工输入住院号时允许保存时自动生成 默认0未启用，1为开启 @IS_BckYZZX int, --Add YSP 20200310 是否为月子中心的科室 0否 1是 @ISYZZX_Separate varchar(2), --Add YSP 20200310 是否月子中心单独编码 0否 1是 @UAA01 int, --Add YSP 20201013 社区ID @UAA03 varchar(128) --Add YSP 20201013 社区名称 SET @CurDate = GETDATE() --Add YSP 20201014 当前台没有传拼音吗和五笔码时，后台自动生成 IF EXISTS (SELECT 1 FROM #tmpInHosPatInfo WHERE ISNULL(ABBRP, '')='' AND ISNULL(ABBRW, '')='') BEGIN UPDATE #tmpInHosPatInfo SET ABBRP=dbo.GetABBRW(VAA05, 0),ABBRW=dbo.GetABBRW(VAA05, 1) WHERE ISNULL(ABBRP, '')='' AND ISNULL(ABBRW, '')='' END IF ISNULL(@VAE01 , 0) > 0 BEGIN Update a SET a.BCK01C = CASE when ISNULL(a.BCK01C ,0) = 0 then b.BCK01C ELSE a.BCK01C END , a.BCK01C = CASE when ISNULL(a.BCK01D ,0) = 0 then b.BCK01D ELSE a.BCK01D END , a.BCQ04B = CASE when ISNULL(a.BCQ04B ,'') = '' then b.BCQ04B ELSE a.BCQ04B END , a.BCE02C = CASE when ISNULL(a.BCE02C ,'') = '' then b.BCE02C ELSE a.BCE02C END , a.BCE03C = CASE when ISNULL(a.BCE03C ,'') = '' then b.BCE03C ELSE a.BCE03C END FROM #tmpInHosPatInfo a JOIN VAE1 b with(nolock) on a.VAE01 = b.VAE01 END select @id=VAA01, @VAA01=ISNULL(VAA01 , 0) ,@VAA04 =VAA04,@VAE01=VAE01 ,@BAK01A=BAK01A ,@BAK01B=BAK01B ,@VAO01A=VAO01A ,@VAO01B=VAO01B ,@VAE82=Getdate(), @VBO01=VBO01, @AAG01=AAG01, @VAO01C = VAO01C, @BAK01C = BAK01C, @BCK01A=BCK01A, @BCK01B=BCK01B, @VBL13=VBL13, @BCE03B=BCE03B, @BCE03C=BCE03C , @BAK05A = BAK05A ,@BAK05B =BAK05B ,@BAK05C = BAK05C , @VAC01 = VAC01 , @VAE11 = VAE11 , @VAA05 = VAA05 , @BDP02 = BDP02 , @ABC02 = ABC02 , @VAA10 = VAE46 , @AAU01 = AAU01 , @VAE04 = VAE04 , @VAE88 = VAE88 , @SCF01 = SCF01 ,@VAE19=VAE19 ,@CManage = CManage , @CManageId = CManageId , @QueryPeople = QueryPeople , @QueryPeopleId = QueryPeopleId , @VAA12 = VAA12 , @VBM11 = VBM11 , @BEP07 = BEP07 , @VAA02 = VAA02 , @VAA35=VAE64 , @VAA15 = VAA15 , @BCQ04B = BCQ04B , @BCK01C = ISNULL(BCK01C , BCK01A), @VAE44 = VAE44 , @IAA01A = IAA01A , @IAD01A = IAD01A , @IAD03A = IAD03A , @IAD04A = IAD04A , @IAA01B = IAA01B , @IAD01B = IAD01B ,@IAD03B = IAD03B , @IAD04B = IAD04B , @IAA01C = IAA01C , @IAD01C = IAD01C ,@IAD03C = IAD03C , @IAD04C = IAD04C, @UAA01=UAA01,@UAA03=UAA03 from #tmpInHosPatInfo SELECT @BookNoWardNo = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 137 SET @BookNoWardNo = ISNULL(@BookNoWardNo , 0) --Add YSP 20191023 手工输入住院号时允许保存时自动生成 默认0未启用，1为开启 SELECT @IS_ManualInputSave = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 147 SET @IS_ManualInputSave = ISNULL(@IS_ManualInputSave , 0) ------------------------------------------------------------------- --add YSP 20200310 获取当前科室是否为月子中心 SET @IS_BckYZZX = 0 --默认不是月子中心的科室 --select A.BAU01,A.BAU02,A.BAU04,ISNULL(B.ACF01, A.ACF01) [ACF01],ISNULL(b.Selected, 0) [Checked],C.ACF05 select @IS_BckYZZX = ISNULL(b.Selected, 0) from BAU1 A left join (select 1 Selected, BAU01, ACF01 from baz1 WHERE BCK01=@BCK01B) B ON A.BAU01=B.BAU01 LEFT JOIN ACF1 C ON B.ACF01=C.ACF01 WHERE A.BAU01=24 --月子中心 ------------------------------------------------------------------- SELECT @VAE87 = [dbo].[GetAgeDetailFromAge](0 , 'Y' , @VAA12 ) If (ISNULL(@VAA04 , '') = '') and (ISNULL(@VAE01 , 0) > 0) BEGIN if (@VAE44 =0 and @BookNoWardNo =0) or @VAE44 <> 0 BEGIN RAISERROR('病人住院号不能为空.', 16, 1, @iret) with nowait return 2 END END --校验住院床位是否存在 If (ISNULL(@BCQ04B ,'') <> '') BEGIN If not exists(select * from BCQ1 where BCQ04 = @BCQ04B) BEGIN RAISERROR('住院床位号不存在，请检查.', 16, 1, @iret) with nowait return 2 END END --判断入院时间如果为空，不让保存 If ISNULL(@VAE11 , '') = '' BEGIN RAISERROR('入院时间不能为空.', 16, 1, @iret) with nowait return 2 END --获取默认病人类别 if isnull(@BDP02,'')='' select @BDP02=BDP02,@ABC02=ABC02 from BGY1 where ISDEF=1 if isnull(@BDP02,'')='' select top 1 @BDP02=BDP02,@ABC02=ABC02 from BGY1 --通过病人类别来确定费别 If exists(select * from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 18 and Value = '1') BEGIN If not exists(select * from BGY1 where ABC02 = @ABC02 and BDP02 = @BDP02) BEGIN RAISERROR('提交错误，病人类别与费别不对应.', 16, 1) with nowait RETURN 2 END END --校验病人费别是否应用住院 If not exists(select * from ABC1 where ACF01 >= 2 and ACF01 <=3 and ABC02 = @ABC02) BEGIN RAISERROR('提交错误，当前费别未应用在住院，请重新调整.', 16, 1) with nowait RETURN 2 END --判断住院次数是否存在 If exists(select * from VAE1 with(nolock) where VAA01=@VAA01 and VAE19=@VAE19 and VAE01<> ISNULL(@VAE01 , 0) and VAE44 <> 9) BEGIN RAISERROR('已存在第%d次住院记录，请修改住院次数后重新保存.', 16, 1,@VAE19) with nowait RETURN 2 END IF ISNULL(@VAE19,0)<=0 BEGIN select @VAE19=max(VAE19)+1 from VAE1 with(nolock) where VAA01=@VAA01 and VAE01<> ISNULL(@VAE01 , 0) and VAE44 <> 9 set @VAE19=ISNULL(@VAE19,1) END --会员病人绑定病人费别 IF EXISTS(SELECT * FROM SYS_PARAMETERS with(nolock) WHERE PRODUCTID=100 AND PROGRAMID=116001 AND PRIVILEGE=0 AND PARAMNO='2' AND VALUE='1') AND EXISTS(SELECT * FROM VAA1 with(nolock) WHERE VAA01=@VAA01 AND VBU01>0 AND ABC02>'' AND ABC02<>@ABC02) BEGIN RAISERROR('已绑定会员卡类型，禁止修改病人费别.', 16, 1) with nowait RETURN 2 END --判断会员卡号是否重复 If ISNULL(@VAA02 , '') <> '' BEGIN IF EXISTS(SELECT * FROM VAA1 with(nolock) WHERE VAA02 = @VAA02 AND VAA01 <> @id) BEGIN RAISERROR('卡号为[%s]已经使用', 16, 1,@VAA02) with nowait RETURN 2 END END If ISNULL(@VAC01 ,0) > 0 BEGIN If exists(select * from VBN1 with(nolock) where VAA07 = @VAC01 and VBN12 > 0) BEGIN RAISERROR('当前住院证信息已经办理入院登记。', 16, 1) with nowait RETURN 2 END END /*===================================================如果住院号为空将自动生成住院号==============================================*/ SELECT @InptNumAlone = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 133 --允许手工输入住院号 SELECT @InptNumBySelf = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 107 SELECT @MzInptNumAlone = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 140 --@MzInptNumNew 门诊留观病人住院号独立编码 SELECT @MzInptNumNew = Value from SYS_Parameters with(nolock) where ProgramID = 104004 and ParamNo = 117 --Add YSP 20200310 是否月子中心单独编码 0否 1是 SELECT @ISYZZX_Separate = Value from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 151 SET @InptNumAlone = ISNULL(@InptNumAlone , '0') SET @InptNumBySelf = ISNULL(@InptNumBySelf , '0') SET @MzInptNumAlone = ISNULL(@MzInptNumAlone , '0') SET @MzInptNumNew = ISNULL(@MzInptNumNew , '0') SET @ISYZZX_Separate = ISNULL(@ISYZZX_Separate , '0') --Add YSP 20200310 是否月子中心单独编码 0否 1是 SET @IsNewWardNo = 0 --@VAE44 0=预约, 1=入院, 2=在院, 3=转科, 4=预出院, 5=结账,6=离院(未结账)9=取消 --@VAE04 0=预约, 病人性质, 0=住院, 1=门诊留观, 2=住院留观 --@BookNoWardNo 预约入院不生成住院号 137 --@InptNumBySelf 允许手工输入住院号 107 --@IS_ManualInputSave 手工输入住院号时允许保存时自动生成 147 --@InptNumAlone 入院登记新生成住院号 133 --@MzInptNumAlone 门诊留观登记新生成住院号 140 If ISNULL(@VAE01 , 0) = 0 BEGIN --这里是新增入院登记或者预约登记时进来的事件 SET @IsNewWardNo = 1 --登记时默认生成住院号 IF @VAE44 = 0 --预约 BEGIN --启用预约不生成住院号 If @BookNoWardNo = 1 BEGIN SET @IsNewWardNo = 0 --SET @VAA04 = '' END END ELSE IF @VAE44 = 1 --入院 BEGIN IF @VAE04 = 0 --入院登记 BEGIN --允许手工输入住院号，住院号不为空 --IF (@InptNumBySelf = 1) and (ISNULL(@VAA04 , '') <> '') --SET @IsNewWardNo = 0 IF @InptNumAlone = '0' AND (ISNULL(@VAA04 , '') <> '') SET @IsNewWardNo = 0 END ELSE BEGIN --门诊留观，住院留观 --2018.06.06 非预约登记，选择门诊留观住院号独立参数，病人再次办理入院时重新生成住院号，同理门诊留观 --@VAE44 0=住院, 1=门诊留观, 2=住院留观 --@MzInptNumNew 门诊留观病人住院号独立编码 If (@VAE04 <> 0) and (@MzInptNumNew = '1' ) and (@VAA01 > 0) and (ISNULL(@VAA04 , '') = '') BEGIN IF EXISTS(SELECT TOP 1 * FROM VAE1 WITH(NOLOCK) WHERE VAA01 = @VAA01 AND VAE04 =1 ORDER BY VAE01 DESC) BEGIN SET @VAA04 = '' SELECT @VAA04 = VAE94 from VAE1 with(nolock) where VAA01 = @VAA01 and VAE04 = @VAE04 and VAE44 >= 1 and VAE44 <= 6 SET @VAA04 = ISNULL(@VAA04 , '') IF @VAA04 <> '' SET @IsNewWardNo = 0 END END END END END If @IsNewWardNo = 1 BEGIN DECLARE @OUT_VAA04 VARCHAR(30), @NB0TYPE INT --1住院 2门诊留观 IF @VAE04 <> 0 SET @NB0TYPE = 2 ELSE SET @NB0TYPE = 1 --住院 Exec HOPatient_NewId_VAA04_BCK01 @BCK01B , @NB0TYPE , @OUT_VAA04 out SET @VAA04 = ISNULL(@OUT_VAA04, '') END /*================================================================================================================================*/ If ISNULL(@VAA05 , '') = '' BEGIN RAISERROR('病人姓名不能为空.', 16, 1) with nowait RETURN 2 END If ISNULL(@VAA04 , '' ) = '' BEGIN if (@VAE44 =0 and @BookNoWardNo =0) or (@VAE44 <> 0) BEGIN RAISERROR('住院号不能为空.', 16, 1) with nowait RETURN 2 END END If ISNULL(@VAA04 , '') <> '' BEGIN If exists(select * from VAA1 with(nolock) where VAA04 = @VAA04 and VAA01 <> @VAA01) BEGIN RAISERROR('住院号[%s]已经使用.', 16, 1 , @VAA04) with nowait RETURN 2 END If exists(select * from VAE1 with(nolock) where VAE94 = @VAA04 and VAA01 <> @VAA01) BEGIN RAISERROR('住院号[%s]已经使用.', 16, 1 , @VAA04) with nowait RETURN 2 END END SELECT @OBDP02 = ISNULL(b.BDP02 , a.BDP02) ,@OABC02 = ISNULL(b.ABC02 , a.ABC02) from VAA1 a with(nolock) left join VAE1 b with(nolock) on b.VAA01 = a.VAA01 and VAE01 = @VAE01 where a.VAA01 = @Id SELECT @OVAA04 = VAA04 ,@OVAA05 = VAA05,@OVAA35 = VAA35 , @OVAA15 = VAA15 from VAA1 with(nolock) where VAA01 = @ID If @VAA05 <> @OVAA05 BEGIN If dbo.GetEmpPermissionValue(104004022,@EmpId)=1 BEGIN if exists(select * from VAE1 with(nolock) where VAA01 = @ID and VAE01 =@VAE01 and VAE44=5) begin if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('已结账病人的姓名不允许修改', 16, 1) WITH NOWAIT Return 1 end END END If @VAA04 <> @OVAA04 BEGIN If dbo.GetEmpPermissionValue(104004021,@EmpId)=1 BEGIN if exists(select * from VAE1 with(nolock) where VAA01 = @ID and VAE01 =@VAE01 and VAE44=5) begin if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('已结账病人的住院号不允许修改', 16, 1) WITH NOWAIT Return 1 end END END --------------------------------------------------------- --Add YSP 20201013 自动添加社区信息 IF (@UAA03 <> '') AND (NOT EXISTS (SELECT 1 FROM UAA1 WHERE UAA03=@UAA03)) BEGIN DECLARE @TMP_UAA01 INT, @UAA02 VARCHAR(10) SELECT @TMP_UAA01=isnull(max(cast(UAA02 AS numeric)+ 1),1) FROM UAA1 WHERE IsNumeric(UAA02) > 0 SELECT @UAA02=(case when len(@TMP_UAA01)<4 then right('0000'+ltrim(@TMP_UAA01),4) else ltrim(@TMP_UAA01) end) INSERT UAA1(UAA01,UAA02,UAA03,UAA04) VALUES (@TMP_UAA01, @UAA02, @UAA03, '') SET @UAA01=@TMP_UAA01 END --------------------------------------------------------- BEGIN TRAN --开始一个事务 /*===============================================如果Id为0或者null新增一条病人信息记录==============================================*/ if ISNULL(@id,0) = 0 BEGIN exec @ErrId = Core_NewIDEx 'VAA1','VAA01',@VAA01 out --获取新的病人信息ID If @ErrId > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 1 END INSERT INTO VAA1 (VAA01, VAA04, VAA05, ABBRP, ABBRW, ABW01, VAA10, AAU01, VAA12, ACK01, VAA15, VAA16, ABJ01, BDP02, ABC02, VAA20 , ACM02, AAT02, ACC02, ABQ02, VAA25, VAA28, VAA29, VAA30, VAA31, VAA32, VAA33, VAA34, VAA35, VAA36, VAA37, VAA38, VAA39, VAA40, VAA41, VAA42, VAA43, VAA44, BAQ01, BAQ02, VAA47, VAA48, VAA49, VAA50, VAA51, VAA52, VAA56, VAA61, VAA64, IAA01,IAK05, BCK01B, BCK01C, BCQ04, VAA73,VAA75,ABL01 ,BEP05 ,BEP06 , VAA82 , VAA02 , BDX02 , VAA14) select @VAA01, @VAA04, VAA05, ABBRP, ABBRW, ABW01, VAE46, AAU01, VAA12, ACK01, VAA15, VAA16, ABJ01, BDP02, ABC02, VAA20 ,ACM02, AAT02, ACC02, ABQ02, VAA25, VAE57, VAE58, VAE59, VAE60, VAE61, VAE62, VAE63, VAE64, VAE65, VAE66, AAY02, VAE67, VAE68, AAZ02, VAE70, VAE71, VAE72, BAQ01, BAQ02, BAQ03, VAE55, VAE56, VAA50, VAA51, VAA52, @VAE19, 2, VAE83, IAA01, IAK05, BCK01B, BCK01A, BCQ04A, VAE11,@CurDate,ABL01, ISNULL(BEP05 , 0) , ISNULL(BEP06 ,0) , VAA82 , VAA02 , BDX02 , VAA14 from #tmpInHosPatInfo IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*===========================================================更新病人信息=================================================================*/ ELSE BEGIN /*================================================初始化部分病人信息========================================================*/ If ISNULL(@VAE01 , 0) <= 0 BEGIN Update VAA1 with(rowlock) SET VAA53 = '', VAA54 = 0 , BEP05 = 0 , BEP06 = 0 where VAA01 = @Id END /*=============================================如果病人姓名变动，加入变动记录===============================================*/ If @VAA05 <> @OVAA05 begin Exec @ErrId = Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out If @ErrId > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 1 END INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAA1', 'VAA05', @ID, @VAE01 , @OVAA05, @VAA05, '病人姓名变更', @EmpId , @EmpName , @CurDate IF @@ERROR >0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*=========================================如果病人住院号变动，加入变动记录===============================================*/ IF @VAA04 <> @OVAA04 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAA1', 'VAA04', @ID, @VAE01 , @OVAA04, @VAA04, '住院号变更', @EmpId , @EmpName , @CurDate IF @@ERROR > 0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*==============================================如果费别变化，加入变动记录================================================*/ IF @OABC02 <> @ABC02 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAE1', 'ABC02', @ID, @VAE01 , @OABC02, @ABC02 , '住院登记费别变更', @EmpId , @EmpName , @CurDate IF @@ERROR > 0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*==============================================如果移动电话变化，加入变动记录================================================*/ If @VAA35 <> @OVAA35 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAA1', 'VAA35', @ID, @VAE01, @OVAA35, @VAA35, '移动电话变更', @EmpId, @EmpName, @CurDate IF @@ERROR >0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*=============================================如果身份证变化，加入变动记录===================================================*/ If @VAA15 <> @OVAA15 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAA1', 'VAA15', @ID, @VAE01, @OVAA15, @VAA15, '身份证变更', @EmpId, @EmpName, @CurDate IF @@ERROR >0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END update a with(rowlock) SET a.VAA04= CASE @VAA04 when '' then @OVAA04 else @VAA04 END ,a.VAA02= b.VAA02 ,a.VAA05= b.VAA05 ,a.ABBRP= b.ABBRP ,a.ABBRW= b.ABBRW ,a.ABW01= b.ABW01 ,a.VAA10= b.VAE46 ,a.AAU01= b.AAU01 ,a.VAA12= b.VAA12 ,a.ACK01= b.ACK01 ,a.VAA15= b.VAA15 ,a.VAA16= b.VAA16 ,a.ABJ01= b.ABJ01 ,a.BDP02= b.BDP02 ,a.ABC02= b.ABC02 ,a.ACM02= b.ACM02 ,a.AAT02= b.AAT02 ,a.ACC02= b.ACC02 ,a.ABQ02= b.ABQ02 ,a.VAA20= b.VAA20 ,a.VAA25= b.VAA25 ,a.VAA28= b.VAE57 ,a.VAA29= b.VAE58 ,a.VAA30= b.VAE59 ,a.VAA31= b.VAE60 ,a.VAA32= b.VAE61 ,a.VAA33= b.VAE62 ,a.VAA34= b.VAE63 ,a.VAA35= b.VAE64 ,a.VAA36= b.VAE65 ,a.VAA37= b.VAE66 ,a.VAA38= b.AAY02 ,a.VAA39= b.VAE67 ,a.VAA40= b.VAE68 ,a.VAA41= b.AAZ02 ,a.VAA42= b.VAE70 ,a.VAA43= b.VAE71 ,a.VAA44= b.VAE72 ,a.BAQ01= b.BAQ01 ,a.BAQ02= b.BAQ02 ,a.VAA47= b.BAQ03 ,a.VAA48= b.VAE55 ,a.VAA49= b.VAE56 ,a.VAA50= b.VAA50 ,a.VAA51= b.VAA51 ,a.VAA52= b.VAA52 ,a.VAA56= @VAE19 ,a.VAA61= 2 ,a.VAA64= b.VAE83 ,a.IAK05= b.IAK05 ,a.IAA01= b.IAA01 ,a.BCK01B=b.BCK01B ,a.BCK01C=b.BCK01A ,a.BCQ04= b.BCQ04B ,a.VAA73= b.VAE11 ,a.ABL01= b.ABL01 ,a.BEP05 = b.BEP05 ,a.BEP06 = b.BEP06 ,a.VAA82 = b.VAA82 ,a.BDX02 = b.BDX02 ,a.VAA14 = b.VAA14 from VAA1 a,#tmpInHosPatInfo b where a.VAA01 = @Id IF @@ERROR >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --病人信息变动同步到CRM客户资料 If exists(select * from sys_parameters with(nolock) where ProductID = 100 and ProgramID = 103005 and ParamNo = 62 and Value='1') BEGIN Exec HOCRM_VAA1_Synchronize 0 , @Id , 2 IF @@ERROR >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('同步病人信息到CRM客户资料时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*===========================================如果病人类别变化，加入变动记录================================================*/ If @BDP02 <> @OBDP02 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAE1', 'BDP02', @ID, @VAE01 , @OBDP02, @BDP02, '病人类别变更', @EmpId , @EmpName , @CurDate IF @@ERROR > 0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --根据规则重新计算其信用额度 If exists(select * from SYS_Parameters with(nolock) where ProductID = 100 and ProgramID = 104004 and ParamNo = 123 and Value = '1') BEGIN Declare @VBM04 numeric(18,4) , @VAA54 numeric(18,4) , @BEP06 numeric(18,4) SELECT @VBM04 = VBM04 from VBM2 with(nolock) where VAA07 = @VAE01 and ACF01 =2 SELECT @VAA54 = VAA54 from VAA1 with(nolock) where VAA01 = @id SET @VBM04 = ISNULL(@VBM04 , 0) SET @VAA54 = ISNULL(@VAA54 , 0) SELECT @BEP06 = dbo.GetCreditLimit(@VBM04 , @VAA54 , @BDP02 , @BCK01A , 1) Update VAA1 with(rowlock) SET BEP06 = ISNULL(@BEP06 , 0) where VAA01 = @id END --2015.06.15增加限制费用总额和药品总额 if exists(select * from BLP1 where BLP02=31 and BLP03=@BCK01B and BLP06=@BDP02 and (BLP04 > 0 or BLP05 > 0)) begin if exists(select * from VBM2 with(nolock) where VAA07=@VAE01 and ACF01=2) update a with(rowlock) set a.BEP07=case when b.blp05>0 then b.blp05 else a.BEP07 end,VBM13=b.blp04 from VBM2 a join (select @vae01 vae01,blp04,blp05 from BLP1 where BLP02=31 and BLP03=@bck01b and BLP06=@BDP02 and (BLP04 > 0 or BLP05 > 0) ) b on b.vae01 = a.vaa07 where VAA07=@VAE01 and ACF01=2 else begin declare @lvbm01 int EXEC Core_NewIDEx 'VBM1','VBM01',@lVBM01 out insert into VBM2(VBM01,VAA01,VAA07,ACF01,VBM04,VBM05,VBM06,VBM08,VBM09,BEP07,VBM11,VBM12,VBM13) select @lvbm01,@id,@vae01,2,0,0,0,0,0,blp05,0,0,blp04 from BLP1 where BLP02=31 and BLP03=@bck01b and BLP06=@BDP02 and (BLP04 > 0 or BLP05 > 0) end end END END /*=============================================================保存病人入院登记=================================================================*/ Declare @SCA01 int , @SCH01 int , @VBL02 varchar(20), @VBL03 varchar(30), @BAQ03 varchar(128),--工作单位 @BBO02 varchar(64), --开户行 @VBL11 varchar(20), --银行帐号 @VBL14 varchar(32), --支付方式 @VBL18 datetime, @VBL19 datetime, @VAA08 varchar(20) --病案号 select top 1 @VAA08 =VAA08 from VAE1 where VAA01=@VAA01 order by VAE01 desc --病人多次入院，自动取上次病案号 SELECT @SCA01 = SCA01 from SCA1 with(nolock) where VAA01 = @VAA01 SELECT @id = VAE01, @VBL02 = VBL02,@VBL03 = VBL03,@BAQ03 = BAQ03B,@BBO02 = BBO02,@VBL11 = VBL11,@VBL14 = VBL14 FROM #tmpInHosPatInfo /*==========================================================如果Id为0或者null,新增一条入院登记记录===============================================*/ IF ISNULL(@id,0)=0 BEGIN SET @OBCQ04B = '' EXEC @ErrId = Core_NewIDEx 'VAE1','VAE01',@VAE01 out --获取新的病人登记ID号 If @ErrId > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RETURN 1 END Set @tmpstr ='00000000000000000000' + cast(@VAE01 as varchar) SET @VAE02 = right(@tmpstr,10) /*=======================================================================插入新的入院登记记录==================================================================*/ INSERT INTO VAE1( VAE01, VAE02, VAA01, VAE04, ABJ01, BDP02, ABC02, VAE08, BCK01A, BCK01B, VAE11, ABO01, ABR01, ABT02, VAE15, ABZ02, ABK02, BCQ04A, VAE19, VAE20, AAG01, VAE22, BCK01C, BCK01D, BCQ04B, VAE26, VAE27, ABV01, VAE29, VAE30, VAE31, VAE33, VAE34, VAE35, VAE36, VAE37, VAE38, BCE03A, BCE03B, BCE03C, VAE42, VAE44, VAE45, VAE46, AAU01, ACK01, AAT02, ACC02, AAY02, BAQ01, BAQ02, BAQ03, VAE55, VAE56, VAE57, VAE58, VAE59, VAE60, VAE61, VAE62, VAE63, VAE64, VAE65, VAE66, VAE67, VAE68, AAZ02, VAE70, VAE71, VAE72, VAE73, IAA01, UAA01, VAE76, BCE03D, VAE78, VAE79, VAE80, BCE03E, VAE82, VAE83, VAE84, VAE85 , VAE86 , VAE87 , VAE88 , VAE89 , VAE90 , VAE91 , VAE92 , SCF01 , BCE02C , VAE94 , VAE95 , VAE96 , VAE97 , VAE98 , BCE02A,VAA08) SELECT @VAE01, @VAA04+convert(varchar(2),@VAE19), @VAA01, VAE04, ABJ01, BDP02, ABC02, VAE08, BCK01A, BCK01B, VAE11, ABO01, ABR01, ABT02, VAE15, ABZ02, ABK02, BCQ04A, @VAE19, VAE20, AAG01, VAE22, BCK01A, BCK01B, BCQ04B, VAE26, VAE27, ABV01, VAE29, VAE30, VAE31, VAE33, VAE34, VAE35, VAE36, VAE37, VAE38, BCE03A, BCE03B, BCE03C, VAE42, VAE44, VAE45, VAE46, AAU01, ACK01, AAT02, ACC02, AAY02, BAQ01, BAQ02, BAQ03, VAE55, VAE56, VAE57, VAE58, VAE59, VAE60, VAE61, VAE62, VAE63, VAE64, VAE65, VAE66, VAE67, VAE68, AAZ02, VAE70, VAE71, VAE72, VAE73, IAA01, @UAA01, VAE76, BCE03D, VAE78, VAE79, VAE80, BCE03E, @CurDate, VAE83, VAE84, VAE85 ,VAE86 , @VAE87 , VAE88 , VAE89 , VAE90 , VAE91 , VAE92 , SCF01 , BCE02C , @VAA04 , VAA05 , ABW01 , VAA12 , VAA15 , BCE02A,@VAA08 from #tmpInHosPatInfo IF @@ERROR > 0--如果发生错误，则回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人入院登记时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*=====================================================================插入病人预交款记录===================================================================*/ If ISNULL(@VBL13,0) > 0 BEGIN Set @VBL18 = @CurDate Set @VBL19 = @CurDate exec HOPatient_PrePay_Entry @VBL01 out,@VBL02,'',4,0,@VAA01,@VAE01,@EmpDept,@BAQ03,@BBO02, @VBL11,'',@VBL13,@VBL14,@VBL18,@VBL19,@EmpId,@EmpCode,@EmpName,@FAA01 IF @@ERROR > 0 --如果发生错误，则回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人预交款时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*============================================================如果从门诊开住院证过来的病人，更新住院证中信息==================================================*/ If ISNULL(@VAC01 , 0) > 0 BEGIN Update VBN1 with(rowlock) SET VAA04 = @VAA04 , VBN12 = @VAE01 , VBN13 = @VAE11 where VAA07 = @VAC01 END /*========================================================================更新余额表信息======================================================================*/ Declare @VBM01 int SELECT @BEP07 = BEP07 from BEP1 where BCK01 = @BCK01A and BDP02 = @BDP02 SET @BEP07 = ISNULL(@BEP07 , 0) SET @VBM11 = ISNULL(@VBM11 , 0) Exec @ErrId = HORate_VBM1_Update @VBM01 , @VAA01 , 2 , 0 , 0 , 0 ,@VAE01 , @BEP07 , @VBM11, 1 If @ErrId > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新病人余额表时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END /*============================================================================================================================================================*/ END ELSE --更新入院登记记录 BEGIN SELECT @OVAE88 = VAE88 , @OBCQ04B = BCQ04B from VAE1 with(nolock) where VAE01 = @id IF @OVAE88 <> @VAE88 BEGIN Exec Core_NewId 'ZAE1' , 'ZAE01' , @ZAE01 out INSERT INTO ZAE1 (ZAE01, TNAME, CNAME, ZAE04, ZAE05, ZAE06, ZAE07, ZAE08, BCE01, BCE03, ZAE11) select @ZAE01, 'VAE1', 'VAE88', @ID, @VAE01 , @OVAE88, @VAE88 , '住院登记有效日期变更', @EmpId , @EmpName , @CurDate IF @@ERROR > 0 --如果发生错误，回滚数据 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人信息变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END UPDATE a with(rowlock) SET a.VAE04 = b.VAE04, a.ABJ01 = b.ABJ01, a.BDP02 = b.BDP02, a.ABC02 = b.ABC02, a.VAE08 = b.VAE08, a.BCK01A = b.BCK01A, a.BCK01B = b.BCK01B, a.VAE11 =b.VAE11 , a.ABO01 =b.ABO01 , a.ABR01 =b.ABR01 , a.ABT02 =b.ABT02 , a.VAE15 =b.VAE15 , a.ABZ02 =b.ABZ02 , a.ABK02 =b.ABK02 , a.BCQ04A = b.BCQ04A, a.VAE19 =@VAE19 , a.VAE20 =b.VAE20 , a.AAG01 =b.AAG01 , a.VAE22 =b.VAE22 , a.BCK01C = b.BCK01C , a.BCK01D = b.BCK01D , a.BCQ04B = b.BCQ04B , a.VAE27 = b.VAE27 , a.ABV01 = b.ABV01 , a.VAE29 = b.VAE29 , a.VAE30 = b.VAE30 , a.VAE31 = b.VAE31 , a.VAE33 = b.VAE33 , a.VAE34 = b.VAE34 , a.VAE35 = b.VAE35 , a.VAE36 = b.VAE36 , a.VAE37 = b.VAE37 , a.VAE38 = b.VAE38 , a.BCE02A = b.BCE02A , a.BCE03A = b.BCE03A , a.BCE03B = b.BCE03B , a.BCE02C = b.BCE02C , a.BCE03C = b.BCE03C , a.VAE42 = b.VAE42 , a.VAE45 = b.VAE45 , a.VAE46 =b.VAE46 , a.AAU01 =b.AAU01 , a.ACK01 =b.ACK01 , a.AAT02 =b.AAT02 , a.ACC02 =b.ACC02 , a.AAY02 =b.AAY02 , a.BAQ01 =b.BAQ01 , a.BAQ02 =b.BAQ02 , a.BAQ03 =b.BAQ03 , a.VAE55 =b.VAE55 , a.VAE56 =b.VAE56 , a.VAE57 =b.VAE57 , a.VAE58 =b.VAE58 , a.VAE59 =b.VAE59 , a.VAE60 =b.VAE60 , a.VAE61 =b.VAE61 , a.VAE62 =b.VAE62 , a.VAE63 =b.VAE63 , a.VAE64 = b.VAE64 , a.VAE65 = b.VAE65 , a.VAE66 = b.VAE66 , a.VAE67 = b.VAE67 , a.VAE68 = b.VAE68 , a.AAZ02 = b.AAZ02 , a.VAE70 = b.VAE70 , a.VAE71 = b.VAE71 , a.VAE72 = b.VAE72 , a.VAE73 = b.VAE73 , a.IAA01 = b.IAA01 , a.UAA01 = @UAA01 , a.VAE76 = b.VAE76 , a.BCE03D = b.BCE03D, a.VAE78 = b.VAE78 , a.VAE79 = b.VAE79 , a.VAE80 = b.VAE80 , a.BCE03E = b.BCE03E, a.VAE83 = b.VAE83 , a.VAE84 = b.VAE84 , a.VAE85 = b.VAE85 , a.VAE86 = b.VAE86 , a.VAE88 = b.VAE88 , a.VAE89 = b.VAE89 , a.VAE90 = b.VAE90 , a.VAE91 = b.VAE91 , a.VAE92 = b.VAE92 , a.SCF01 = b.SCF01 , a.VAE87 = @VAE87 , a.VAE94 = @VAA04 , a.VAE95 = b.VAA05 , a.VAE96 = b.ABW01 , a.VAE97 = b.VAA12 , a.VAE98 = b.VAA15 FROM VAE1 a,#tmpInHosPatInfo b WHERE a.VAE01 = @id IF @@ERROR > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人入院登记时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --如果没有入科的病人，修改时改变其住院科室、病区 UPDATE VAE1 SET BCK01C = @BCK01A , BCK01D = @BCK01B WHERE VAE01 = @id AND VAE44 <= 1 --维护病人余额记录 SET @BEP07 = ISNULL(@BEP07 , 0) SET @VBM11 = ISNULL(@VBM11 , 0) Exec @ErrId = HORate_VBM1_Update @VBM01 , @VAA01 , 2 , 0 , 0 , 0 ,@VAE01 , @BEP07 , @VBM11, 1 If @ErrId > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新病人余额表时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END -----------------------------------------------更新床位信息---------------------------------------------------------------- If ISNULL(@BCQ04B , '') <> ISNULL(@OBCQ04B , '') BEGIN If ISNULL(@OBCQ04B , '') <> '' BEGIN Update BCQ1 SET VAA01 = 0 , BCQ13 = 0 , VAA07 = 0 where VAA01 = @VAA01 END If ISNULL(@BCQ04B , '') <> '' BEGIN Update BCQ1 SET VAA01 = @VAA01 , BCQ13 = 1 where BCK01A = @BCK01C and BCQ04 = @BCQ04B --预约时更新床位表中就诊ID If @VAE44 = 0 BEGIN Update BCQ1 SET VAA07 = @VAE01 , BCQ13 = 0 where BCK01A = @BCK01C and BCQ04 = @BCQ04B END END END /*=============================================保存病人诊断信息(门诊诊断、中医诊断、入院诊断)====================================================*/ Exec @ErrId =HOPatient_VAO1_Update @VAA01,@VAE01,@VAO01A,@BAK01A,2,1,1,0,1,null,0,@EmpName,@BAK05A ,0, 0, 0,0,0,@IAA01A , @IAD01A , @IAD03A , @IAD04A IF @ErrId >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人院门诊诊断时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --入院门诊诊断(中医) Exec @ErrId =HOPatient_VAO1_Update @VAA01, @VAE01, @VAO01B, @BAK01B, 2, 1, 0, 1, 1, null, 0, @EmpName, @BAK05B ,0, 0, 0,0,0,@IAA01B , @IAD01B , @IAD03B , @IAD04B IF @ErrId >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人院中医诊断时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --入院诊断 If exists(select * from #tmpInhosVAO2 ) BEGIN Declare @i int , @Count int , @DFLAG int , @VAO07 int SELECT @i = 0 , @Count = COUNT(1) from #tmpInhosVAO2 WHILE @i < @Count BEGIN SELECT @VAO01C = VAO01 , @BAK01C = BAK01A , @BAK05C = VAO15 , @DFLAG = DFLAG , @IAA01C = 0 , @IAD01C = 0 , @IAD03C = '' , @IAD04C = '',@VAO07=VAO07 FROM #tmpInhosVAO2 WHERE ID = @i + 1 IF @DFLAG = 0 BEGIN Exec @ErrId =HOPatient_VAO1_Update @VAA01 , @VAE01, @VAO01C, @BAK01C, 2, 1, @VAO07, 0, 2, null, 0, @EmpName, @BAK05C ,0, 0, 0,0,0,@IAA01C , @IAD01C , @IAD03C , @IAD04C IF @ErrId >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人院诊断时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END ELSE If @DFLAG = 1 BEGIN Delete from VAO2 WHERE VAO01 = @VAO01C END SET @i = @i + 1 END END /*=========================================保存病人变动记录(入院登记)================================================*/ If @WorkStation=1 BEGIN If @VAE44 = 0 SET @VBO06 = 0 else SET @VBO06 = 1 Exec @ErrId= HOPatient_Pat_Change @VBO01, @VAA01, @VAE01, @VAE82, @VBO06, @BCK01A, @BCK01B, @AAG01, @ABO01, @EmpId, @EmpName IF @ErrId >0 BEGIN If @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('入院登记保存病人变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END end else /*========================================保存病人变动记录(护士工作站)===============================================*/ if @WorkStation=2 BEGIN Declare @VBO04 datetime set @VBO04=@CurDate select @VBO01=VBO01 from VBO1 with(nolock) where VAA01=@VAA01 and VAA07=@VAE01 and VBO06=7 and ISNULL(VBO21,0)=0 Exec @ErrId=HOPatient_Nurse_PatChange @VBO01,@VAA01,@VAE01,@VBO04,7,@BCK01A,@BCK01B,@AAG01,@ABO01,@BCE03B,@BCE03C,@EmpId,@EmpName IF @ErrId >0 BEGIN If @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('护士站保存病人变动记录时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /* ==============================================更新病人再入院情况=================================================*/ if ISNULL(@DiagId,0) <> 0 BEGIN update VAE1 with(rowlock) set VAE08=1 where VAE01 = @DiagId and VAE44 <> 0 IF @@ERROR >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新病人再入院状态时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END /*===============================================更新CRM预约信息===================================================*/ If ISNULL(@SCF01 , 0) > 0 BEGIN update SCF1 with(rowlock) SET VAA07B = @VAE01 , SCF11 = 4 where SCF01 = @SCF01 IF @@ERROR >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新CRM预约信息时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END --写入CRM咨询师 If ISNULL(@QueryPeopleId , 0) > 0 BEGIN Exec @ErrId = HOCRM_SCI1_Update @SCI01 out , @SCA01 , 2 , @SCF01 , 0 , @QueryPeopleId , @QueryPeople , '' , 0 , 3 , 0 , @VAE01 , 1 , '' IF @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入咨询师时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END SELECT @SCA01 = SCA01 from SCF1 with(nolock) where SCF01 = @SCF01 --如果没有对应病人信息，对应病人信息 If not exists(select * from SCA1 where VAA01 > 0 and SCA01 = @SCA01) BEGIN update SCA1 with(rowlock) SET VAA01 = @VAA01 where SCA01 = @SCA01 IF @@ERROR >0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('更新CRM客户信息时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END END IF ISNULL(@SCA01 , 0) > 0 BEGIN If ISNULL(@CManageId , 0) > 0 BEGIN SET @SCI01 = 0 --Exec @ErrId = HOCRM_SCI1_Update @SCI01 out , @SCA01 , 1 , @SCF01 , 0 , @CManageId , @CManage Exec @ErrId = HOCRM_SCA1_Status @SCA01 , @CManageId , @CManage , 0 /*IF @ErrId > 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('写入客户经理时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END */ END SELECT @BCK03B = BCK03 from BCK1 where BCK01 = @BCK01B SET @SCH12 = '住院号：' + @VAA04 + ' 入院时间：' + convert(varchar(30) , @VAE11 , 111) + ' 入院科室：' + @BCK03B + ' 门诊医生：' + ISNULL(@BCE03B ,'') Exec @ErrId = HOCRM_SCH1_Update @SCH01 out , @SCA01 , 0 , 'VAE1' , 'VAE01' , 201 , '入院' , @SCH12 , 0 , 0 , @EmpId ,@EmpName END /*========================================================返回入院登记病人变动ID==================================================================*/ SELECT @VBO01 = VBO01 from VBO1 with(nolock) where VAA01 = @VAA01 and VAA07 = @VAE01 and VBO06 =1 /*==========================================================写入病人登记扩展表内容=============================================================== */ If not exists(SELECT * from VCT1 where VAA07 = @VAE01) BEGIN INSERT INTO VCT1(VAA07 , VCT32 , VCT33 , VCT34 , VCT35 , VCT36 , VCT37 , VCT38 , VCT39 , VCT41 , VCT42 , VCT99 , VCTA1 , ATTRS) SELECT @VAE01 , VCT32 , VCT33 , VCT34 , VCT35 , VCT36 , VCT37 , VCT38 , VCT39 , VCT41 , VCT42 , VCT99 , VCTA1 , @ATTRS from #tmpInHosPatInfo IF @@ERROR > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人登记扩展表时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END ELSE BEGIN Update a with(rowlock) SET a.VCT32 = b.VCT32 , a.VCT33 = b.VCT33 , a.VCT34 = b.VCT34 , a.VCT35 = b.VCT35 , a.VCT36 = b.VCT36 , a.VCT37 = b.VCT37 , a.VCT38 = b.VCT38 , a.VCT39 = b.VCT39 , a.VCT41 = b.VCT41 , a.VCT42 = b.VCT42 , a.VCT99 = b.VCT99 , a.VCTA1 = b.VCTA1 , a.ATTRS = @ATTRS from VCT1 a ,#tmpInHosPatInfo b where a.VAA07 = @VAE01 END If not exists(SELECT * from VFA1 where VAA01 = @VAA01) BEGIN INSERT VFA1(VAA01 , ATTRS) SELECT @VAA01 , @ATTRS_Patient END ELSE BEGIN Update VFA1 SET ATTRS = @ATTRS_Patient where VAA01 = @VAA01 END If not exists(SELECT * from VEZ1 where VAA07 = @VAE01) BEGIN INSERT INTO VEZ1(VAA07 , VEZ23 , VEZ19, VEZ31, VEZ38) SELECT @VAE01 , VEZ23 , VEZ19, VEZ31, VEZ38 from #tmpInHosPatInfo IF @@ERROR > 0 BEGIN if @@TRANCOUNT > 0 ROLLBACK TRAN RAISERROR('保存病人登记扩展表时发生错误，错误号为: %d.', 16, 1, @@ERROR) WITH NOWAIT Return 1 END END ELSE BEGIN UPDATE a with(rowlock) SET a.VEZ23 = b.VEZ23 , a.VEZ19 = b.VEZ19 , a.VEZ31 = b.VEZ31 , A.BCK01A = B.BCK01M from VEZ1 a join #tmpInHosPatInfo b on b.VAE01 = a.VAA07 where a.VAA07 = @VAE01 END /* --Mod YSP 20200924 屏蔽，因为每次都会去找之前的入院记录就行更新，如果第一次的入院地址是错误的，后面的入院前台填写正确也会更新成错误 --放在脚本18424检索病人时在客户端显示上一次入院的地址信息 --取最近一次入院扩展信息进行补充更新 declare @LastVAE01 int=0 set @LastVAE01=(select top 1 VAE01 from VAE1 where VAA01=@VAA01 and VAE01 < @VAE01 and VAE44=5 order by VAE01 desc) if ISNULL(@LastVAE01,0) > 0 Update a with(rowlock) SET a.VCT32 = ISNULL(b.VCT32 ,a.VCT32), a.VCT33 = ISNULL(b.VCT33 ,a.VCT33), a.VCT34 = ISNULL(b.VCT34 ,a.VCT34), a.VCT35 = ISNULL(b.VCT35 ,a.VCT35), a.VCT36 = ISNULL(b.VCT36 ,a.VCT36), a.VCT37 = ISNULL(b.VCT37 ,a.VCT37), a.VCT38 = ISNULL(b.VCT38 ,a.VCT38), a.VCT39 = ISNULL(b.VCT39 ,a.VCT39), a.VCT41 = ISNULL(b.VCT41 ,a.VCT41), a.VCT42 = ISNULL(b.VCT42 ,a.VCT42) from VCT1 a , VCT1 b where b.VAA07 = @LastVAE01 and a.VAA07 = @VAE01 */ ----------------------------------------------------------------------------------------------------------------------------------------------- If not exists(select * from VAM1 with(nolock) where VAA07= @VAE01) BEGIN INSERT INTO VAM1(VAA07) SELECT @VAE01 END /*==============================================================================================================================================*/ /*===========================================YSP 20190904 新增信用额度上限=====================================================================*/ Declare @VBM14 numeric(18,4)=0 --信用额度上限 SELECT @VBM14 = BEP10 FROM BEP1 with(nolock) WHERE BDP02=@BDP02 --通过类别获取到对应的信用额度上限值 if @VBM14>0 UPDATE VBM2 with(rowlock) SET VBM14=@VBM14 WHERE VAA07=@VAE01 --然后把信用额度上限值更新到VBM2 /*==============================================================================================================================================*/ COMMIT TRAN SET @VAA04=ISNULL(@VAA04, '') IF Object_id('tempdb..#tmpInHosPatInfo') IS NOT NULL DROP TABLE #tmpInHosPatInfo IF Object_id('tempdb..#tmpInhosVAO2') IS NOT NULL DROP TABLE #tmpInhosVAO2