--增加分类
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=11988 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=11988)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(11988, 131, 'SQL_BCT1Load', '检索材料', '检索材料', 1, 1, '<?xml version="1.0"?>
<Configs Ver="1"><Win SZ="1"/><Grid Head="1"><Sql>Declare @lText varchar(20)
SET @lText = %s 
select a.BBX01 , a.BBY01 , c.BBY05 , c.BBE02 , a.BCT15   
from BCT1 a
  join BBY1 c on c.BBY01 = a.BBY01 
where c.BDN01 = ''4'' 
  and BCT33 = 1 
  and ((c.BBY04 like @lText) or  exists(select * from BCL1 d where d.BBY01 = a.BBY01 and (BCL03 like @lText or ABBRP like @lText or ABBRW like @lText) ))</Sql><HD><Ie FD="BBY05" CA="材料" WD="200" SZ="1"/><Ie FD="BBE02" CA="生产商" WD="200" SZ="1"/><Ie FD="BCT15" CA="批号" WD="100" SZ="1"/></HD></Grid></Configs>
', 1, Null, '2015-04-07 14:53:51', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=11988 已经存在.'

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'RBE1') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--传染病报告卡性病附卡, 
--RBE1
create table RBE1(
    [RAZ01] int --传染病报告卡ID, 关联字段:RAZ1.RAZ01
  , [ACK01] varchar(2) --婚姻状况, 关联字段:ACK1.ACK01
  , [ABQ02] varchar(32) --民族, 关联字段:ABQ1.ABQ02
  , [RBE04] varchar(32) --文化程度
  , [RBE05] varchar(20) --户籍属于 1.本县区, 2.本市其他县区, 3.外省, 4.港澳台, 5.外籍
  , [RBE06] varchar(30) --省
  , [RBE07] varchar(30) --市
  , [RBE08] varchar(30) --县
  , [RBE09] varchar(30) --村
  , [RBE10] varchar(30) --门牌号
  , [RBE11] varchar(512) --接触史
  , [RBE12] int --与多少人公用过注射器
  , [RBE13] int --与多少人有非婚性行为
  , [RBE14] int --与多少人有过同性性行为
  , [RBE15] varchar(128) --其他接触史
  , [RBE16] tinyint --0=有，1=无，2=不详
  , [RBE17] varchar(30) --最有可能感染途径
  , [RBE18] varchar(30) --样本来源
  , [RBE19] tinyint --0=确认结果阳性1=替代策略检测阳性
  , [RBE20] datetime --确认（替代策略）检测阳性日期
  , [RBE21] varchar(64) --确认（替代策略）检测单位
  , [RBE22] datetime --艾滋病确诊日期
)

GO
--==================================================================================================
--Author     <...Fyq>
--AlterDate  <...2015.04.03>
--Description<...病例监测存取过程>
--2014.07.07 <...手术感染记录增加手术开始时间，手术结束时间，手术医生，切口类型，麻醉方式>
--2015.04.03 <...调整保存附表过程>
--==================================================================================================
ALTER Proc [HoInfect_ZIA1_Update] @Xml ntext , @ZIA01 int out
As
DECLARE @i int ,
        @Value int ,
        @Count int ,
        @iDOM int, 
        @iret int,
        @ErrId int 
--解析XML文档	
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
if @iret > 0
BEGIN
   exec sp_xml_removedocument @iDOM  	
   RAISERROR('解析XML文档时发生错误，错误号b. %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,  
        @DeptNo varchar(20),
        @DeptName varchar(20),
        @HostMac varchar(256),
        @FileVer varchar(20)          
        
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) 
  )    
--------------------------------------读取提交的[ZIA1(病例监测记录)]信息到临时表-------------------------------------
SELECT * into #tmpZIA1
  FROM OpenXml(@iDOM, '/Root/ZIA1/Ie', 8)
    WITH (
       ZIA01  INT  --
      ,BIX02  VARCHAR(30)  --
      ,ZIA03  VARCHAR(20)  --
      ,ZIA04  DATETIME  --
      ,ACF01  INT  --
      ,VAA01  INT  --
      ,VAA07  INT  --
      ,ZIA08  INT  --
      ,ZIA09  NUMERIC(18, 4)  --
      ,ZIA10  TINYINT  --
      ,ZIA11  TINYINT  --
      ,BIU02  VARCHAR(64)  --
      ,BIW02  VARCHAR(64)  --
      ,ZIA14  TINYINT  --
      ,ZIA15  VARCHAR(30)  --
      ,ZIA16  TINYINT  --
      ,BIT02  VARCHAR(64)  --
      ,ZIA18  TINYINT  --
      ,BIV02  VARCHAR(64)  --
      ,ZIA20  TINYINT  --
      ,ZIA21  VARCHAR(10)  --
      ,ZIA22  VARCHAR(20)  --
      ,ZIA23  TINYINT  --
      ,ZIA24  NUMERIC(18, 4)  --
      ,ZIA25  NUMERIC(18, 4)  --
      ,ZIA26  NUMERIC(18, 4)  --
      ,BCK01A INT  --
      ,BCE01A INT  --
      ,BCE03A VARCHAR(20)  --
      ,ZIA30  DATETIME  --
      ,BCE01B INT  --
      ,BCE03B VARCHAR(20)  --
      ,ZIA33  DATETIME  --
      ,ZIA34  INT  --
      ,ZIA35  varchar(255)
    )
--------------------------------------读取提交的[ZIB1(病例侵袭操作)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZIB1
  FROM OpenXml(@iDOM, '/Root/ZIB1/Ie', 8)
    WITH (
       ZIB01 INT  --
      ,ZIA01 INT  --
      ,BIS02 VARCHAR(64)  --
      ,ROWNR INT  --次序
    )
    
If exists(select * from #tmpZIB1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZIB1 
  Exec Core_NewId_ZIB01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZIB1  
      SET ZIB01 = @Value - @Count + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END
--------------------------------------读取提交的[ZIC1(病例感染记录)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZIC1
  FROM OpenXml(@iDOM, '/Root/ZIC1/Ie', 8)
    WITH (
       ZIC01  INT  --
      ,ZIA01  INT  --
      ,ZIC03  DATETIME  --
      ,ROWNR  INT  --次序
      ,ZIC05  VARCHAR(10)  --
      ,BIY02  VARCHAR(50)  --
      ,BAK01  INT  --
      ,ZIC08  VARCHAR(255)  --
      ,BBX01  INT  --
      ,ZIC10  VARCHAR(1024)  --
      ,ZIC11  VARCHAR(10)  --
      ,ZIC12  VARCHAR(30)  --
      ,BCK01A INT  --
      ,BCK01B INT  --
      ,ZIC15  TINYINT  --
      ,ZIC16  TINYINT  --
      ,ZIC17  TINYINT  --
      ,ZIC18  TINYINT  --
      ,ZIC19  TINYINT  --
    )
    
If exists(select * from #tmpZIC1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZIC1  
  Exec Core_NewId_ZIC01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZIC1  
      SET ZIC01 = @Value - @Count + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END   
--------------------------------------读取提交的[ZID1(病例易感因素)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZID1
  FROM OpenXml(@iDOM, '/Root/ZID1/Ie', 8)
    WITH (
       ZID01 INT  --
      ,ZIA01 INT  --
      ,BIZ01 INT  --
      ,ROWNR INT  --次序
      ,ZID05 TINYINT  --
      ,RecordID int 
    )

If exists(select * from #tmpZID1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZID1 
  Exec Core_NewId_ZID01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZID1  
      SET ZID01 = @Value - @Count - @i + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END       
--------------------------------------读取提交的[ZIE1( 病例手术感染记录)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZIE1
  FROM OpenXml(@iDOM, '/Root/ZIE1/Ie', 8)
    WITH (
       ZIE01 INT  --
      ,ZIA01 INT  --
      ,BBX01 INT  --
      ,BCK01 INT  --
      ,ROWNR INT  --
      ,BEE03 VARCHAR(20)  --
      ,ZIE07 TINYINT  --
      ,ZIE08 TINYINT  --
      ,ZIE09 TINYINT  --
      ,ZIE10 TINYINT  --
      ,ZIE11 TINYINT  --
      ,ZIE12 VARCHAR(10)  --
      ,ZIE13 datetime
      ,ZIE14 datetime
      ,ZIE15 varchar(20)
      ,AAB02 varchar(10)
      ,ACI02 varchar(30)
    )
    
If exists(select * from #tmpZIE1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZIE1 
  Exec Core_NewId_ZIE01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZIE1 
      SET ZIE01 = @Value - @Count + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END    
--------------------------------------读取提交的[ZIF1(病例检查记录)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZIF1
  FROM OpenXml(@iDOM, '/Root/ZIF1/Ie', 8)
    WITH (
       ZIF01 INT  --
      ,ZIA01 INT  --
      ,ZIF03 DATETIME  --
      ,ZIF04 INT  --
      ,ZIF05 INT  --
      ,ZIF06 VARCHAR(30)  --
      ,BIY02 VARCHAR(50)  --
      ,BFO01 INT  --
      ,ZIF09 VARCHAR(30)  --
      ,ZIF10 VARCHAR(30)  --
      ,ZIF11 VARCHAR(30)  --
      ,BFI01 INT  --
      ,ZIF13 VARCHAR(20)  --
    )   

If exists(select * from #tmpZIF1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZIF1 
  Exec Core_NewId_ZIF01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZIF1 
      SET ZIF01 = @Value - @Count + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END       
--------------------------------------读取提交的[ZIG1(病例药物明细)]信息到临时表-------------------------------------
SELECT IDENTITY(int , 1 , 1) as [ID],* into #tmpZIG1
  FROM OpenXml(@iDOM, '/Root/ZIG1/Ie', 8)
    WITH (
       ZIG01 INT  --
      ,ZIA01 INT  --
      ,ROWNR INT  --
      ,VAF01 INT  --
      ,BBX01 INT  --
      ,ZIG06 NUMERIC(18, 4)  --
      ,ZIG07 VARCHAR(30)  --
      ,ZIG08 VARCHAR(50)  --
      ,ZIG09 NUMERIC(18, 4)  --
      ,ZIG10 DATETIME  --
    )

If exists(select * from #tmpZIG1)
BEGIN
  SELECT @i = 0 , @Count = COUNT(1) from #tmpZIG1 
  Exec Core_NewId_ZIG01 @Value out , @Count 
  
  WHILE @i < @Count 
  BEGIN
    Update #tmpZIG1 
      SET ZIG01 = @Value - @Count + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
END        
----------------------------------------------------------------------------------------------------------------------
BEGIN TRAN
   /*=======================================================病例监测操作===========================================*/
    If ISNULL(@ZIA01 , 0) <= 0   --插入病例监测记录
    BEGIN
      Exec Core_NewId_ZIA01 @ZIA01 out 
      
      INSERT INTO ZIA1(ZIA01, BIX02, ZIA03, ZIA04, ACF01, VAA01, VAA07, ZIA08, ZIA09, ZIA10, ZIA11, BIU02,
        BIW02, ZIA14, ZIA15, ZIA16, BIT02, ZIA18, BIV02, ZIA20, ZIA21, ZIA22, ZIA23, ZIA24, ZIA25, ZIA26, 
        BCK01A, BCE01A, BCE03A, ZIA30, BCE01B, BCE03B, ZIA33, ZIA34 , ZIA35)
        
      SELECT @ZIA01, BIX02, ZIA03, ZIA04, ACF01, VAA01, VAA07, ZIA08, ZIA09, ZIA10, ZIA11, BIU02, 
        BIW02, ZIA14, ZIA15, ZIA16, BIT02, ZIA18, BIV02, ZIA20, ZIA21, ZIA22, ZIA23, ZIA24, ZIA25, ZIA26, 
        BCK01A, @EmpId , @EmpName , GETDATE(), BCE01B, BCE03B, ZIA33, ZIA34 , ZIA35 from #tmpZIA1 
        
      IF @@ERROR >0  --如果发生错误，回滚数据
      BEGIN
     	 if @@TRANCOUNT > 0 ROLLBACK TRAN
         RAISERROR('保存病例监测操作时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
         Return 1
      END         
    END ELSE BEGIN
      update a Set        
        a.BIX02 = b.BIX02 ,
        a.ZIA03 = b.ZIA03 ,
        a.ZIA04 = b.ZIA04 ,
        a.ACF01 = b.ACF01 ,
        a.VAA01 = b.VAA01 ,
        a.VAA07 = b.VAA07 ,
        a.ZIA08 = b.ZIA08 ,
        a.ZIA09 = b.ZIA09 ,
        a.ZIA10 = b.ZIA10 ,
        a.ZIA11 = b.ZIA11 ,
        a.BIU02 = b.BIU02 ,
        a.BIW02 = b.BIW02 ,
        a.ZIA14 = b.ZIA14 ,
        a.ZIA15 = b.ZIA15 ,
        a.ZIA16 = b.ZIA16 ,
        a.BIT02 = b.BIT02 ,
        a.ZIA18 = b.ZIA18 ,
        a.BIV02 = b.BIV02 ,
        a.ZIA20 = b.ZIA20 ,
        a.ZIA21 = b.ZIA21 ,
        a.ZIA22 = b.ZIA22 ,
        a.ZIA23 = b.ZIA23 ,
        a.ZIA24 = b.ZIA24 ,
        a.ZIA25 = b.ZIA25 ,
        a.ZIA26 = b.ZIA26 ,
        a.BCK01A = b.BCK01A ,
        a.BCE01A = b.BCE01A ,
        a.BCE03A = b.BCE03A ,        
        a.BCE01B = b.BCE01B ,
        a.BCE03B = b.BCE03B ,
        a.ZIA33 = b.ZIA33 ,
        a.ZIA34 = b.ZIA34 ,
        a.ZIA35 = b.ZIA35 
    from ZIA1 a
      join #tmpZIA1 b on b.ZIA01 = a.ZIA01 
    where a.ZIA01 = @ZIA01 
   END
   /*==============================================病例侵袭操作=================================================*/
   Delete from ZIB1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZIB1)
   BEGIN
      INSERT INTO ZIB1(ZIB01, ZIA01, BIS02, ROWNR) 
      SELECT ZIB01, @ZIA01 , BIS02, ROWNR from #tmpZIB1       
   END
   
   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例侵袭操作时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END  
   /*===============================================病例感染记录==================================================*/
   Delete from ZIC1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZIC1 )
   BEGIN
     INSERT INTO ZIC1(ZIC01, ZIA01, ZIC03, ROWNR, ZIC05, BIY02, BAK01, ZIC08, BBX01, ZIC10, ZIC11, ZIC12,
       BCK01A, BCK01B, ZIC15, ZIC16, ZIC17, ZIC18, ZIC19)
       
     SELECT ZIC01, @ZIA01, ZIC03, ROWNR, ZIC05, BIY02, BAK01, ZIC08, BBX01, ZIC10, ZIC11, ZIC12,
       BCK01A, BCK01B, ZIC15, ZIC16, ZIC17, ZIC18, ZIC19 from #tmpZIC1  
   END

   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例感染记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END          
   /*================================================病例感染因素=========================================*/
   Delete from ZID1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZID1)
   BEGIN
     INSERT INTO ZID1(ZID01, ZIA01, BIZ01, ROWNR, ZID05)
     SELECT ZID01, @ZIA01, BIZ01, RecordID, ZID05 from #tmpZID1      
   END

   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例感染因素时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END         
   /*=================================================病例手术感染记录=====================================*/
   Delete from ZIE1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZIE1)
   BEGIN
     INSERT INTO ZIE1(ZIE01, ZIA01, BBX01, BCK01, ROWNR, BEE03, ZIE07, ZIE08, ZIE09, ZIE10, ZIE11, ZIE12 ,
                      ZIE13 , ZIE14 , ZIE15 , AAB02 , ACI02)
     SELECT ZIE01, @ZIA01, BBX01, BCK01, ROWNR, BEE03, ZIE07, ZIE08, ZIE09, ZIE10, ZIE11, ZIE12 ,
                     ZIE13 , ZIE14 , ZIE15 , AAB02 , ACI02 from #tmpZIE1 
   END
   
   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例手术感染记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END      
   /*===================================================病例检查记录=======================================*/
   Delete from ZIF1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZIF1)
   BEGIN
     INSERT INTO ZIF1(ZIF01, ZIA01, ZIF03, ZIF04, ZIF05, ZIF06, BIY02, BFO01, ZIF09, ZIF10, ZIF11, BFI01, ZIF13)
     SELECT ZIF01, @ZIA01, ZIF03, ZIF04, ZIF05, ZIF06, BIY02, BFO01, ZIF09, ZIF10, ZIF11, BFI01, ZIF13 from #tmpZIF1 
   END
   
   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例检查记录时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END                
   /*====================================================病例药物明细======================================*/
   Delete from ZIG1 where ZIA01 = @ZIA01 
   If exists(select * from #tmpZIG1)
   BEGIN
     INSERT INTO ZIG1(ZIG01, ZIA01, ROWNR, VAF01, BBX01, ZIG06, ZIG07, ZIG08, ZIG09, ZIG10)
     SELECT ZIG01, @ZIA01, ROWNR, VAF01, BBX01, ZIG06, ZIG07, ZIG08, ZIG09, ZIG10 from #tmpZIG1 
   END

   IF @@ERROR >0  --如果发生错误，回滚数据
   BEGIN
      if @@TRANCOUNT > 0 ROLLBACK TRAN
      RAISERROR('保存病例药物明细时发生错误，错误号为: %d.', 16, 1, @@ERROR)  WITH NOWAIT
      Return 1
   END         
   /*======================================================================================================*/
COMMIT TRAN      
--删除临时表
IF Object_id('tempdb..#tmpZIA1') IS NOT NULL DROP TABLE #tmpZIA1   
IF Object_id('tempdb..#tmpZIB1') IS NOT NULL DROP TABLE #tmpZIB1   
IF Object_id('tempdb..#tmpZIC1') IS NOT NULL DROP TABLE #tmpZIC1  
IF Object_id('tempdb..#tmpZID1') IS NOT NULL DROP TABLE #tmpZID1 
IF Object_id('tempdb..#tmpZIE1') IS NOT NULL DROP TABLE #tmpZIE1 
IF Object_id('tempdb..#tmpZIF1') IS NOT NULL DROP TABLE #tmpZIF1 
IF Object_id('tempdb..#tmpZIG1') IS NOT NULL DROP TABLE #tmpZIG1 

GO