SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--导入药品信息表 2016.8.4
ALTER proc [Master_BBX1_BBY1_Import]
  @lxml ntext
  ,@lMode tinyint  --0:清楚原数据，新增 1:追加与替换现有数据  
as
  DECLARE @iDOM int, @iret int
  Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @lXml

  if @iret > 0
  BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
    return @iret
  END 
  
  declare @ReBBX04 varchar(20)
  set @ReBBX04 = ''
    
  Create TABLE #TmpImport(uid int IDENTITY(1,1),BDA02 varchar(20),ABF02 varchar(20),BDO03 varchar(64),BBX04 varchar(20)
    ,BBX05 varchar(128),BBY06 varchar(48),BAP02 varchar(20),Rownr int,BBY11 varchar(50),BBE02 varchar(64),BBY08 varchar(20)
    ,BAG03 numeric(18,6),BDG02 varchar(20),BDG02A varchar(20),BAG05 numeric(18,4),BDG02B varchar(20),BAG07 numeric(18,4)
    ,BDG02C varchar(20),BAG09 numeric(18,4),BBY25 numeric(18,6),AAS02 varchar(32),BAG15 varchar(32),BDA01 varchar(2)
    ,ABF01 varchar(8),BDO01 int,BBY04 varchar(20),BAX01 int,AAS01 varchar(4),BBX01 int,BBY01 int)
  
  create table #tmpBBX1(uid int IDENTITY(1,1),BBX01 int,BBX04 varchar(20),BDA01 varchar(2),BDO01 int,BBX05 varchar(128),BDG02 varchar(20),BAP02 varchar(20))    
  create table #tmpBBY1(uid int IDENTITY(1,1),BBY01 int,BBY04 varchar(20))      
 
  INSERT INTO #TmpImport(BDA02,ABF02,BDO03,BBX04,BBX05,BBY06,BAP02,Rownr,BBY11,BBE02,BBY08,BAG03,BDG02,BDG02A,BAG05
    ,BDG02B,BAG07,BDG02C,BAG09,BBY25,AAS02,BAG15)
  SELECT 统计类别,票据项目,药理分类,编号,名称,规格,剂型,规格序号,医保编码,产地,基本单位,含量,剂量单位,门诊单位,门诊包装
    ,住院单位,住院包装,药库单位,药库包装,单价,医保类型,批准文号
  from OpenXml(@iDOM,'/Root/import/Ie',8)
    WITH (统计类别 varchar(20)
    ,票据项目 varchar(20)
    ,药理分类 varchar(64)
    ,编号 varchar(20)
    ,名称 varchar(128)
    ,规格 varchar(48)
    ,剂型 varchar(20)
    ,规格序号 int
    ,医保编码 varchar(50)
    ,产地 varchar(64)
    ,基本单位 varchar(20)
    ,含量 numeric(18,6)
    ,剂量单位 varchar(20)
    ,门诊单位 varchar(20)
    ,门诊包装 numeric(18,4)
    ,住院单位 varchar(20)
    ,住院包装 numeric(18,4)
    ,药库单位 varchar(20)
    ,药库包装 numeric(18,4)
    ,单价 numeric(18,6)
    ,医保类型 varchar(32)
    ,批准文号 varchar(32)
    )	
  
  If not exists(select * from #TmpImport)
  BEGIN
    exec sp_xml_removedocument @iDOM
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR('提交文档错误，没有数据信息.', 16, 1) with nowait
    RETURN 1
  END 
  exec sp_xml_removedocument @iDOM
  
  declare @smsg varchar(200)
  
  if exists(select * from BDA1 a
            where a.BDA01 <= '3' and not exists(select * from BAX1 b where b.BAX03 = a.BDA02 ))
  begin
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR('统计类别与收入项目不一致，请先调整收入项目.', 16, 1) with nowait
    RETURN 2
  end   
  
  if exists(select * from #TmpImport a where not exists(select * from BDA1 b where b.BDA02 = a.BDA02)) 
  begin    
    select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BDA1 b where b.BDA02 = a.BDA02)    
    set @smsg = '['+@ReBBX04+']统计类别不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 3
  end        
  
  if exists(select * from #TmpImport a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02)) 
  begin
    select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from ABF1 b where b.ABF02 = a.ABF02)
    set @smsg = '['+@ReBBX04+']票据项目不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 4
  end  
   
  if exists(select * from #TmpImport a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3')) 
  begin
    select @ReBBX04 = a.BBX04 from #TmpImport a where not exists(select * from BDO1 b where b.BDO03 = a.BDO03 and b.BDM01 <= '3')
    set @smsg = '['+@ReBBX04+']药理分类不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 5
  end   
  
  if exists(select * from #TmpImport a where isnull(a.BAP02,'')<>'' and  not exists(select * from BAP1 b where b.BAP02 = a.BAP02)) 
  begin
    select @ReBBX04 = a.BBX04 from #TmpImport a where isnull(a.BAP02,'')<>'' and  not exists(select * from BAP1 b where b.BAP02 = a.BAP02)
    set @smsg = '['+@ReBBX04+']剂型不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 6
  end  
  
  if exists(select * from #TmpImport a where isnull(a.AAS02,'')<>'' and  not exists(select * from AAS1 b where b.AAS02 = a.AAS02)) 
  begin
    select @ReBBX04 = a.BBX04 from #TmpImport a where isnull(a.AAS02,'')<>'' and  not exists(select * from AAS1 b where b.AAS02 = a.AAS02)
    set @smsg = '['+@ReBBX04+']医保类型不对，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 6
  end 
  
  if exists(select * from #TmpImport where ISNULL(BBX04,'')='' or ISNULL(BBX05,'')='' or isnull(Rownr,-1)=-1
     or ISNULL(BBY08,'')='' or ISNULL(BDG02,'')='' or ISNULL(BDG02A,'')='' or ISNULL(BDG02B,'')='' or ISNULL(BDG02C,'')=''
     or isnull(BAG03,-1)= -1 or isnull(BAG05,-1)= -1 or isnull(BAG07,-1)= -1 or isnull(BAG09,-1)= -1 or isnull(BBY25,-1)= -1) 
  begin
    select @ReBBX04 = BBX04 from #TmpImport where ISNULL(BBX04,'')='' or ISNULL(BBX05,'')='' or isnull(Rownr,-1)=-1
     or ISNULL(BBY08,'')='' or ISNULL(BDG02,'')='' or ISNULL(BDG02A,'')='' or ISNULL(BDG02B,'')='' or ISNULL(BDG02C,'')=''
     or isnull(BAG03,-1)= -1 or isnull(BAG05,-1)= -1 or isnull(BAG07,-1)= -1 or isnull(BAG09,-1)= -1 or isnull(BBY25,-1)= -1    
    set @smsg = '['+@ReBBX04+']编号、名称、规格序号、各种单位、包装系数、单价都不能省略，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 7
  end
  
  update #tmpimport set BBY04 = BBX04 + CAST(rownr as varchar)
  
  if exists(select BBY04,COUNT(1) from #tmpimport group by BBY04 having COUNT(1)>1)
  begin
    select @ReBBX04 = a.BBY04
    from (select BBY04,COUNT(1) num from #tmpimport group by BBY04 having COUNT(1)>1) a
    set @smsg = '['+@ReBBX04+']编号加序号有重复号码，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 8
  end
  
  update a set a.BDA01 = b.BDA01
  from #TmpImport a
  join BDA1 b on b.BDA02 = a.BDA02
  
  update a set a.BAX01 = b.BAX01
  from #TmpImport a
  join BAX1 b on b.BAX03 = a.BDA02
  
  update a set a.ABF01 = b.ABF01
  from #TmpImport a
  join ABF1 b on b.ABF02 = a.ABF02
  
  update a set a.AAS01 = b.AAS01
  from #TmpImport a
  join AAS1 b on b.AAS02 = a.AAS02
  
  update a set a.BDO01 = b.BDO01
  from #TmpImport a
  join (select BDO03,MAX(BDO01) BDO01 from BDO1 where BDM01 <= '3' group by BDO03) b on b.BDO03 = a.BDO03
  
  insert into #tmpBBX1(BBX04,BBX05,BDA01,BDO01,BDG02,BAP02)
  select distinct BBX04,BBX05,BDA01,BDO01,BDG02,BAP02
  from #tmpImport a
  where (@lMode <> 1) or (@lMode = 1 and not exists(select * from BBX1 b where b.BBX04 = a.BBX04))
  
  if exists(select BBX04,COUNT(1) from #tmpBBX1 group by BBX04 having COUNT(1)>1)
  begin
    select @ReBBX04 = s.BBX04
    from (select BBX04,COUNT(1) num from #tmpBBX1 group by BBX04 having COUNT(1)>1) s
    set @smsg = '['+@ReBBX04+']品种信息中，编号一致，而其它信息不一致导致有重复品种，请重新校验数据并修改.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 8
  end
  
  insert into #tmpBBY1(BBY04)
  select BBY04
  from #tmpImport a
  where (@lMode <> 1) or (@lMode = 1 and not exists(select * from BBY1 b where b.BBY04 = a.BBY04))
  
  if (@lMode = 0) and exists(select * from VAJ1 where BDN01 <= '3')
  begin    
    set @smsg = '已有费用明细记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 0) and exists(select * from VAF1)
  begin    
    set @smsg = '已有医嘱使用记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  if (@lMode = 0) and exists(select * from DSB2)
  begin    
    set @smsg = '药品进货单已有使用记录，不能清空原有数据进行保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 2) and exists(select * from BBY1 a join #tmpBBY1 c on c.BBY04 = a.BBY04)
  begin
    set @ReBBX04 = (select top 1 d.BBX04
                   from BBY1 a join #tmpBBY1 c on c.BBY04 = a.BBY04 join #tmpImport d on d.BBY04 = c.BBY04)
    set @smsg = '['+@ReBBX04+']已存在此药品规格记录，不能保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 9
  end
  
  if (@lMode = 2) and exists(select * from BBX1 a join #tmpBBX1 c on c.BBX04 = a.BBX04)
  begin
    set @ReBBX04 = (select top 1 c.BBX04
                   from BBX1 a join #tmpBBX1 c on c.BBX04 = a.BBX04)
    set @smsg = '['+@ReBBX04+']已存在此药品品种记录，不能保存.'
    IF Object_id('tempdb..#TmpImport') IS NOT NULL DROP TABLE #TmpImport
    RAISERROR(@smsg, 16, 1) with nowait
    RETURN 10
  end
  
  declare @ID int, @aDate datetime
  set @aDate = GETDATE()
  Exec Core_NewID 'BBY1' , 'BBY01' , @ID out 
  set @ID = @ID + 1
  update #tmpBBY1 set BBY01 = uid + @ID
  
  Exec Core_NewID 'BBX1' , 'BBX01' , @ID out 
  set @ID = @ID + 1
  update #tmpBBX1 set BBX01 = uid + @ID
 begin tran 
   if @lMode = 0
   begin
     delete a from BDK1 a join BBX1 b on b.BBX01 = a.BBX01 where b.BDA01 <= '3'
     delete a from BCL1 a join BBY1 b on b.BBY01 = a.BBY01 where b.BDN01 <= '3' 
     delete from BBY1 where BDN01 <= '3'
     delete from BBX1 where BDA01 <= '3'
     truncate table BBT1
     truncate table BAG1
   end
   if @lMode = 1
   begin
     update a set a.BDA01 = b.BDA01,a.BDO01 = b.BDO01,a.BBX05 = b.BBX05,a.BDG02 = b.BDG02
     from BBX1 a
     join #tmpImport b on b.BBX04 = a.BBX04
     
     update c set c.BAP02 = b.BAP02
     from BBX1 a
     join BBT1 c on c.BBX01 = a.BBX01
     join #tmpImport b on b.BBX04 = a.BBX04
     
     update a set a.BDN01 = b.BDA01,a.BCA01 = b.BDO01,a.BBY05 = b.BBX05,a.BBY06 = b.BBY06,a.BBE02 = b.BBE02,a.BBY08 = b.BBY08
       ,a.BBY11 = b.BBY11,a.BAX01 = b.BAX01,a.ABF01 = b.ABF01,a.AAS01 = b.AAS01,a.BBY25 = b.BBY25
     from BBY1 a
     join #tmpImport b on b.BBY04 = a.BBY04
     
     update c set c.BAG03 = b.BAG03,c.BDG02A = b.BDG02A,c.BAG05 = b.BAG05,c.BDG02B = b.BDG02B,c.BAG07 = b.BAG07,c.BDG02C = b.BDG02C
       ,c.BAG09 = b.BAG09,c.BAG15 = b.BAG15
     from BBY1 a
     join BAG1 c on c.BBY01 = a.BBY01
     join #tmpImport b on b.BBY04 = a.BBY04
   end 
   
   insert into BBX1(BBX01,BDA01,BDO01,BBX04,BBX05,BBX06,BDG02,BBX08,BBX09,ABW01,BBX11
     ,BBX12,BBX13,BBX14,BBX15,ACF01,BBX17,BBX18,BBX19,BBX20,BBX21,BCW01,BCE01,BBX24  --创建时间
     ,BBX25,BBX26,ROWNR,BBX28,BBX29  ) 
   select BBX01,BDA01,BDO01,BBX04,BBX05,null,BDG02,0,0,'0',1
     ,0,'',0,0,3,1,0,'',0,0,0,0,@aDate,'9999-12-31',0,0,0,''
   from #tmpBBX1  
   
   insert into BBT1(BBX01,BAP02,BAM01,BAN01,BBA01,BBM01,BBT07,BBT08,BBT09,BBT10,BBT11,BBT12,BBT13,BBT14,AAH01,BBS01,BBT17,BBT18)
   select BBX01,BAP02,'1','1','1','1',0,0,0,0,0,0,0,0,'0',0,0,0
   from #tmpBBX1
  
   
   insert into BBY1(BBY01,BDN01,BCA01,BBY04,BBY05,BBY06,BBE02,BBY08,BCF01,BBY10,BBY11,BAX01,AAS01,ABF01,ABA01,BCG01,BCH01,ACF01 
     ,BBY18,BBY19,BBY20,BBY21,BBY22,BBY23,BCK01,BBY25,BBY26,BBY27,BBY28,BBY29,BBY30,BBY31,BBY32,BBY34,BBY35,BGF01,BHH01,BBY38)
   select b.BBY01,a.BDA01,a.BDO01,b.BBY04,a.BBX05,a.BBY06,a.BBE02,a.BBY08,0,'',a.BBY11,a.BAX01,a.AAS01,a.ABF01,'','','',3
     ,0,0,0,0,0,0,0,a.BBY25,0,0,0,'',@aDate,'9999-12-31',0,0,0,'','',0
   from #tmpImport a
   join #tmpBBY1 b on b.BBY04 = a.BBY04 
   
   insert into BAG1(BBY01,BBX01,BAG03,BDG02A,BAG05,BDG02B,BAG07,BDG02C,BAG09,BAG40,BAG41,BAG10,BAG11,BAG12,BAG13,BAT02
     ,BAG15,BAG16,BAG17,BAG18,BAG19,BAG20,BAG21,BAG22,BAG23,BAG24,BAG25,BAG26,BAG27,BAG28,BAG29,BAG30,BAG31,BDG02D
     ,BAG33,BAG34,BBH01A,BBH01B,BAG37,BAG38,BAG39) 
   select a.BBY01,c.BBX01,b.BAG03,b.BDG02A,b.BAG05,b.BDG02B,b.BAG07,b.BDG02C,b.BAG09,'',1,null,null,0,0,null
     ,b.BAG15,'','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,4
     ,1,0,0,0,'','',null
   from #tmpBBY1 a
   join #tmpImport b on b.BBY04 = a.BBY04
   join #tmpBBX1 c on c.BBX04 = b.BBX04  
    
 commit tran