DROP PROCEDURE [DPS_ExportForm_Update3]
GO

DROP PROCEDURE [DPS_ExportForm_Update2]
GO

DROP PROCEDURE [DPS_ExportForm_Update1Sep]
GO

DROP PROCEDURE [DPS_ExportForm_Update1]
GO

DROP PROCEDURE [DPS_ExportForm_Track]
GO

DROP PROCEDURE [DPS_ExportForm_CheckSep]
GO

DROP PROCEDURE [DPS_ExportForm_Check]
GO

DROP PROCEDURE [DPS_DPT1_Update]
GO

DROP PROCEDURE [DPS_DPT1_Redo]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [DPS_DPT1_Redo]
  @BCK01  int = 0 --部门ID
AS    
BEGIN
  --delete from DPT1 where not exists(select * from BAZ1 where BCK01 = DPT1.BCK01 and BAU01 in ('51', '52', '53')) or refQty<=0 or ExpiryDate<=GETDATE()
  if not exists(select * from BAZ1 where BCK01 = @BCK01 and BAU01 in ('51', '52', '53')) 
  begin
	return  
  end
  if object_id('tempdb..#DPK1') is not null drop table #DPK1
  if object_id('tempdb..#DPT1') is not null drop table #DPT1
  --批次库存临时表
  select A.BCK01, A.BBY01, A.DSK_ID, sum(A.LastQuantity) LastQty
    , B.PurchasePrice/B.PackSize as PurchasePrice, B.PresellPrice/B.PackSize as PresellPrice
  into #DPK1
  from DPK1 A with(nolock) join DSK1 B with(nolock) on B.ID = A.DSK_ID
  where A.BCK01 = @BCK01 and A.ExpiryDate>GETDATE()
  group by A.BCK01, A.BBY01, A.DSK_ID, B.PurchasePrice, B.PresellPrice, B.PackSize
  having sum(A.LastQuantity)<>0
  --规格库存临时表  
  --select * into #DPT1 from DPT1 where BCK01 = @BCK01
  delete T from DPT1 T with(rowlock) where T.BCK01 = @BCK01 and not exists(select * from #DPK1 A where T.BCK01 = A.BCK01 and T.BBY01 = A.BBY01 and T.DSK_ID = a.DSK_ID)
    
  update T set 
    T.RefQty = K.LastQty
  , T.SellQty = 0
  , T.Quantity = K.LastQty
  , T.SysDate = (case when (K.LastQty - T.SellQty) <= 0 then GETDATE() else T.SysDate end)
  , T.PurchasePrice = K.PurchasePrice
  , T.PresellPrice = K.PresellPrice
  from DPT1 T with(rowlock) join #DPK1 K
    on T.BCK01 = K.BCK01 and T.BBY01 = K.BBY01 and T.DSK_ID = K.DSK_ID
  where T.BCK01 = @BCK01 and T.RefQty<>k.LastQty

  insert into DPT1(BCK01, BBY01, DSK_ID, BBH01, BBH04, BatchNo, ProduceDate, ExpiryDate
		, PurchasePrice, PresellPrice, RefQty, SellQty, Quantity, BAL08)
  select A.BCK01, A.BBY01, A.DSK_ID, C.BBH01, C.BBH04, B.BatchNo, B.ProduceDate, B.ExpiryDate
		, B.PurchasePrice / (CASE WHEN ISNULL(B.PackSize, 0) = 0 THEN 1 ELSE B.PackSize END)
		, B.PresellPrice / (CASE WHEN ISNULL(B.PackSize, 0) = 0 THEN 1 ELSE B.PackSize END)
		, A.LastQty, 0, A.LastQty, 0
  from #DPK1 A join DSK1 B with(nolock) on B.ID = A.DSK_ID and B.BBY01 = A.BBY01
	left join BBH1 C on C.BBH01 = B.BBH01
  where not exists(select * from DPT1 T with(nolock) where T.BCK01 = A.BCK01 and T.BBY01 = A.BBY01 and T.DSK_ID = a.DSK_ID)  
    
  if object_id('tempdb..#DPK1') is not null drop table #DPK1
  if object_id('tempdb..#DPT1') is not null drop table #DPT1
END


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--====================================================================
--Alter Date <...2016.10.24>
--Description<...门诊收费记帐/结帐/医嘱记帐时修改 SellQty>
--====================================================================
CREATE PROCEDURE [DPS_DPT1_Update]
    @BCK01     int--部门ID
  , @BBY01     int--药品ID
  , @DSK_ID    int--批次ID
  , @UpdQty    numeric(18,6)--数量,>0增加预扣数,<0减少预扣数
AS
declare
    @iCount    int--计数器
  , @iTatol    int--总行数
  , @RefQty    numeric(18,6)
  , @SellQty   numeric(18,6)    
begin
  --较正可用库存(入库数量小于0的情况下，Quantity与SellQty同时更新为0)

  --update DPT1 set Quantity = 0, SellQty = 0 where BCK01 = @BCK01 and BBY01 = @BBY01 and Quantity < 0
  --建内存临时表
  if Object_Id('tempdb..#tmp_DPT1') is not null drop table #tmp_DPT1
  select identity(int,1,1) as xRowNum, ID, DSK_ID, RefQty, SellQty, Quantity into #tmp_DPT1 from DPT1 with(nolock)
  where BCK01 = @BCK01 and BBY01 = @BBY01 order by DSK_ID --先进先出DSK_ID排序

  if @UpdQty > 0 --增加预扣数*********************************************************************
  begin
    --先处理指定批次的记录
    select @RefQty = RefQty, @SellQty = SellQty FROM #tmp_DPT1 WHERE DSK_ID = @DSK_ID
    if @UpdQty + @SellQty <= @RefQty
    begin
      update #tmp_DPT1 set SellQty = SellQty + @UpdQty, Quantity = Quantity - @UpdQty where DSK_ID = @DSK_ID
      set @UpdQty = 0 --清零
    end else
    begin
      update #tmp_DPT1 set SellQty = RefQty, Quantity = 0 where DSK_ID = @DSK_ID
      set @UpdQty = @UpdQty - (@RefQty - @SellQty) --剩余数量
    end
    
    if @UpdQty <> 0 --一定是大于或等于0的数
    begin          
      --初始化变量
      select @iTatol = COUNT(*), @iCount = 1 FROM #tmp_DPT1
      --循环逐条处理记录
      while @iCount <= @iTatol
      begin
        select @RefQty = RefQty, @SellQty = SellQty FROM #tmp_DPT1 WHERE xRowNum = @iCount        
        if @UpdQty + @SellQty <= @RefQty
        begin
          update #tmp_DPT1 set SellQty = SellQty + @UpdQty, Quantity = Quantity - @UpdQty where xRowNum = @iCount
          set @UpdQty = 0 --清零
          break --中止循环
        end    
      
        update #tmp_DPT1 set SellQty = RefQty, Quantity = 0 where xRowNum = @iCount        
        set @UpdQty = @UpdQty - (@RefQty - @SellQty) --剩余数量
        set @iCount = @iCount + 1 --循环计数器加1
      end--while
    end--if      
  end else  
  if @UpdQty < 0 --减少预扣数****************************************************************
  begin
    --先处理指定批次的记录
    select @RefQty = RefQty, @SellQty = SellQty FROM #tmp_DPT1 WHERE DSK_ID = @DSK_ID
    if @UpdQty + @SellQty >= 0
    begin
      update #tmp_DPT1 set SellQty = SellQty + @UpdQty, Quantity = Quantity - @UpdQty where DSK_ID = @DSK_ID
      set @UpdQty = 0
    end else
    begin      
      update #tmp_DPT1 set SellQty = 0, Quantity = RefQty where DSK_ID = @DSK_ID
      set @UpdQty = @UpdQty + @SellQty
    end
    
    if @UpdQty <> 0 --一定是小于或等于0的数
    begin
      --初始化变量
      select @iTatol = COUNT(*), @iCount = 1 FROM #tmp_DPT1
      --循环逐条处理记录
      while @iCount <= @iTatol
      begin
        select @RefQty = RefQty, @SellQty = SellQty FROM #tmp_DPT1 WHERE xRowNum = @iCount        
        if @UpdQty + @SellQty >= 0
        begin          
          update #tmp_DPT1 set SellQty = SellQty + @UpdQty, Quantity = Quantity - @UpdQty where xRowNum = @iCount
          set @UpdQty = 0 --清零
          break --中止循环
        end    
      
        update #tmp_DPT1 set SellQty = 0, Quantity = RefQty where xRowNum = @iCount
        set @UpdQty = @UpdQty + @SellQty
        set @iCount = @iCount + 1 --循环计数器加1
      end--while
    end        
  end
  --同步正式库******************************************************************************
  update a set SellQty = b.SellQty, Quantity = case when b.Quantity<0 then 0 else b.Quantity end
  from DPT1 a with(rowlock) join #tmp_DPT1 b
    on a.ID = b.ID
  where a.SellQty<>b.SellQty or a.Quantity<>b.Quantity

 
end


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--===============================================================================================
--<...Alter Date >2016.09.13` 
--<...Description>药房药品发药单-审核
--<...History>2013.07.08 当前单据中不存在待发药明细，更新待发药单据处理状态(暂时只处理门诊发药)
--<...History>2014.02.28 更新存取过程优化
--<...History>2014.12.31 减小锁粒度
--<...History>2016.07.06 增加调用场合1=发药、2=手麻消耗
--===============================================================================================
CREATE PROCEDURE [DPS_ExportForm_Check]
    @BCK01              int--部门ID
  , @BillID             int--单据ID
  , @Assessor           varchar(20)--审核人
  , @TerminalNO         varchar(50)--终端编号
  , @TradeID            int = 0 OUT--流水号ID
  , @WorkFrom           smallint = 1--1=发药、2=手麻消耗
AS
DECLARE
    @iCount             int--计数器
  , @iTotal             int--总行数
  , @BBY01              int--药品ID
  , @DPK_ID             int--库存表ID
  , @DSK_ID             int--库存表ID
  , @UpdQty             numeric(18, 6)--预扣数量
  , @Quantity           numeric(18, 6)--执行数量
  , @VAI01              int--单据ID

  IF not exists(SELECT * FROM DPD1 with(nolock) WHERE BCK01 = @BCK01 and BillID = @BillID)
  BEGIN
    RAISERROR('单据不存在或已复核，请刷新后重试！',16, 1) WITH NOWAIT
    RETURN 1
  END

  --更新单据当前售价、成本价
  UPDATE A SET
      RetailPrice = Y.BBY25 --当前售价
    , CostPrice   = G.BAG23 --当前成本价
    , RetailAmount= ROUND(Y.BBY25*A.Quantity, 6)--售价金额
    , CostAmount  = ROUND(G.BAG23*A.Quantity, 6)--成本金额
  FROM DPD2 A with(rowlock)
  join BBY1 Y ON Y.BBY01 = A.BBY01
  join BAG1 G ON G.BBY01 = Y.BBY01
  WHERE A.BCK01 = @BCK01 and A.BillID = @BillID

  --建执行临时表
  SELECT IDENTITY(int,1,1) AS UID, d.BBY01, d.Quantity, d.DPK_ID, k.DSK_ID,d.VAJ01 INTO #TmpDPD2
  FROM DPD2 d with(nolock)
    left join DPK1 k with(nolock) ON d.BCK01 = k.BCK01 and d.DPK_ID = k.ID
  WHERE d.BCK01 = @BCK01 and d.BillID = @BillID
  ORDER BY d.Quantity ASC --负数排第一
    
  --初始化变量
  SELECT @iTotal = COUNT(*), @iCount = 1 FROM #TmpDPD2
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTotal
  BEGIN
    --提取记录
    SELECT @BBY01 = BBY01, @Quantity = Quantity, @DPK_ID = DPK_ID, @DSK_ID = DSK_ID FROM #TmpDPD2 WHERE UID = @iCount

    --更改部门药品库存表(正数是发药,负数是退药)
    UPDATE DPK1 with(rowlock) 
      SET LastQuantity = LastQuantity - @Quantity
    WHERE ID = @DPK_ID and BBY01 = @BBY01 and (LastQuantity >= @Quantity or @Quantity<0) --防止负库存
    
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('库存数不足，更新药房库存药品Id %d 时错误，请刷新后重试。',16, 1, @BBY01) WITH NOWAIT
      RETURN 2
    END
   
    --加计数器
    SET @iCount = @iCount + 1
  END
 
  --产生发药流水ID
  EXEC Core_NewId_DPD1_ID_2 @TradeID OUT
  --更改病人费用明细执行标志
  UPDATE J SET  --允许同一个VAJ01,会被更新多次.故不能加VAJ48=0判断
     BCK01D = @BCK01--执行科室ID
   , BCE03C = @Assessor--执行者
   , VAJ48  = @TradeID--执行ID
   , VAJ51  = GetDate()--执行时间
   , VAJ52  = Getdate()--执行交易时间
   , VAJ53  = 1--执行情况：0:未执行; 1:执行完成; 2:拒绝执行; 3:正在执行
  FROM VAJ1 J with(rowlock)
    join #tmpDPD2 b on b.VAJ01 = J.VAJ01 
	where VAJ48=0
  
  IF (@WorkFrom = 1) and (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('更新病人费用表执行标识时错误', 16, 1) WITH NOWAIT
    RETURN 3
  END
  --数量上部分退费多次退费后自动发药，VAJ01B更新为当前VAJ01
  Update J 
    SET J. VAJ01B = J.VAJ01
  from VAJ1 J with(rowlock) 
    join #tmpDPD2 b on b.VAJ01 = J.VAJ01 
  where J.VAJ01B > 0 and J.VAJ25 > 0
   
  --更新单据主审核标志
  UPDATE DPD1 with(rowlock) 
    SET AuditingDate = GetDate(), Assessor = @Assessor, TradeID = @TradeID
  WHERE BCK01 = @BCK01 and BillID = @BillID
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('更新单据主表审核标志时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN 4
  END
  --更新待发药列表中的处理状态
  If exists(select * from DPD1 with(nolock) where BCK01 = @BCK01 and BillID = @BillID )
  begin
    SELECT VAI01 ,VAJ53 into #tmpVBX1 
    from VAJ1 j with(nolock)
       join #tmpDPD2 d on d.VAJ01 = j.VAJ01 and d.BBY01 = j.BBY01 
    group by j.VAI01 , j.VAJ53 
    
    Update a 
      set a.VBX22 = 1 
    from VBX1 a with(rowlock)
      join #tmpVBX1 b on b.VAI01 = a.VBX18 
    where a.VBX03 = 2 and b.VAJ53 = 1
  end 
  /*
 --更新VDE1中VDE03状态
  If @WorkFrom = 2
  begin
    Update a
      SET a.VDE03 = 2
    from VDE1 a
      join #TmpDPD2 b on b.VAJ01 = a.VAJ01
    where a.VAJ01 > 0
    
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
       ROLLBACK TRAN
       RAISERROR('更新科室消耗记录标志时错误,请重试！', 16, 1) WITH NOWAIT
       RETURN 5
    END
  end
   */
COMMIT TRAN



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--===============================================================================================
--<...Alter Date >2016.09.13
--<...Description>住院发药单-审核(分表)
--<...分批向后台传入数据，接受前台XML文档>
--<...2014.12.31减小锁粒度>
--<...2016.07.05增加传入调用场合>
--===============================================================================================
CREATE PROCEDURE [DPS_ExportForm_CheckSep]
    @BCK01              int--部门ID
  , @BillID             int--单据ID
  , @Assessor           varchar(20)--审核人
  , @TerminalNO         varchar(50)--终端编号
  , @TradeID            int = 0 OUT--流水号ID
  , @Doc                ntext = ''--前台传入XML文档
  , @WorkFrom           int = 1--1=发药、2=手麻消耗
AS
DECLARE
    @iCount             int--计数器
  , @iTotal             int--总行数
  , @BBY01              int--药品ID
  , @DPK_ID             int--库存表ID
  , @DSK_ID             int--库存表ID
  , @UpdQty             numeric(18, 6)--预扣数量
  , @Quantity           numeric(18, 6)--执行数量
  , @VAI01              int--单据ID
  , @iDoc               int
  , @iret               int--返回XML号
  , @Error              int--返回错误号 
  
  --解析XML文档
  If @WorkFrom = 1
  BEGIN
     EXEC @iret = sp_xml_preparedocument @iDoc OUTPUT, @Doc
     IF @iret > 0
     BEGIN
       EXEC sp_xml_removedocument @iDoc
       RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) WITH NOWAIT
       RETURN @iret
     END    
     SELECT xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ38, VAJ54 , BCK01A , BCK01B , BCK01E 
     INTO #tmpVAJ2_1
     FROM OPENXML (@iDoc, '/ROOT/RxList', 8)
          WITH ( xRowNum           int--行号
               , VAA01             int--病人ID
               , VAJ01             int--记费ID
               , VAJ09             int--冲销ID

               , BBY01             int--药品ID
               , BatchNo           varchar(30)--产品批号
               , VAJ25             numeric(18, 6)--数量, 数次, 总数量
               , VAJ32             numeric(18, 6)--全价
               , VAJ33             numeric(18, 6)--单价, 标准单价
               , VAJ36             numeric(18, 6)--全额, 应收金额
               , VAJ38             numeric(18, 6)--结帐金额
               , VAJ54             varchar(255)--备注
               , BCK01A            int--病区ID
               , BCK01B            int--科室ID
               , BCK01E            int--领药科室ID
              )
                          

    EXEC sp_xml_removedocument @iDoc

    IF not exists(SELECT * FROM #tmpVAJ2_1)
    BEGIN
      RAISERROR('没有找到要提交的发药内容，请重新后重试！', 16, 1) WITH NOWAIT
      RETURN 1
    END
  END 
  
  IF not exists(SELECT * FROM DPD1 with(nolock) WHERE BCK01 = @BCK01 and BillID = @BillID)
  BEGIN
    RAISERROR('单据不存在或已复核，请刷新后重试！',16, 1) WITH NOWAIT
    RETURN 1
  END
  

  --更新单据当前售价、成本价
  UPDATE A SET
      RetailPrice = Y.BBY25 --当前售价
    , CostPrice   = G.BAG23 --当前成本价
    , RetailAmount= ROUND(Y.BBY25*A.Quantity, 6)--售价金额
    , CostAmount  = ROUND(G.BAG23*A.Quantity, 6)--成本金额
  FROM DPD2 A with(rowlock)
  join BBY1 Y ON Y.BBY01 = A.BBY01
  join BAG1 G ON G.BBY01 = Y.BBY01
  WHERE A.BCK01 = @BCK01 and A.BillID = @BillID

  --建执行临时表
  Create table #TmpDPD2
  (
    UID int identity(1 ,1) ,
    BBY01 int ,
    Quantity numeric(18,6) ,
    DPK_ID int ,
    DSK_ID int ,
    VAJ01 int 
  ) 
  If @WorkFrom = 1
  BEGIN   
    INSERT INTO #TmpDPD2(BBY01 , Quantity , DPK_ID , DSK_ID , VAJ01)
    SELECT d.BBY01, d.Quantity, d.DPK_ID, k.DSK_ID,d.VAJ01 
    FROM DPD2 d with(nolock)
      left join DPK1 k with(nolock) ON d.BCK01 = k.BCK01 and d.DPK_ID = k.ID
      join #tmpVAJ2_1 c on c.VAJ01 = d.VAJ01 
	where d.Quantity<>0
    ORDER BY d.Quantity ASC --负数排第一
  END ELSE If @WorkFrom = 2
  BEGIN
    INSERT INTO #TmpDPD2(BBY01 , Quantity , DPK_ID , DSK_ID , VAJ01)
    SELECT d.BBY01, d.Quantity, d.DPK_ID, k.DSK_ID,d.VAJ01
    FROM DPD2 d with(nolock) 
      left join DPK1 k with(nolock) ON d.BCK01 = k.BCK01 and d.DPK_ID = k.ID
	where d.Quantity<>0 and d.BCK01 = @BCK01 and d.BillID = @BillID 
    ORDER BY d.Quantity ASC --负数排第一
  END
  
  --初始化变量
  SELECT @iTotal = COUNT(*), @iCount = 1 FROM #TmpDPD2
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTotal
  BEGIN
    --提取记录
    SELECT @BBY01 = BBY01, @Quantity = Quantity, @DPK_ID = DPK_ID, @DSK_ID = DSK_ID FROM #TmpDPD2 WHERE UID = @iCount

    --更改部门药品库存表(正数是发药,负数是退药)
    UPDATE DPK1 with(rowlock) 
      SET LastQuantity = LastQuantity - @Quantity
    WHERE ID = @DPK_ID and BBY01 = @BBY01 and (LastQuantity >= @Quantity or @Quantity<0) --防止负库存
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('库存数不足，更新药房库存药品Id %d 时错误，请刷新后重试。',16, 1, @BBY01) WITH NOWAIT
      RETURN 2
    END
    --加计数器
    SET @iCount = @iCount + 1
  END

  --产生发药流水ID
  If ISNULL(@TradeID , 0) <= 0
    EXEC Core_NewId_DPD1_ID_2 @TradeID OUT
  --更改病人费用明细执行标志
  UPDATE J SET  --允许同一个VAJ01,会被更新多次.故不能加VAJ48=0判断
     BCK01D = @BCK01--执行科室ID
   , BCE03C = @Assessor--执行者
   , VAJ48  = @TradeID--执行ID
   , VAJ51  = GetDate()--执行时间
   , VAJ52  = Getdate()--执行交易时间
   , VAJ53  = 1--执行情况：0:未执行; 1:执行完成; 2:拒绝执行; 3:正在执行
  FROM VAJ2 J with(rowlock)
   join #tmpDPD2 b on b.VAJ01 = J.VAJ01 
  
  IF (@WorkFrom = 1) and (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN   
    RAISERROR('更新病人费用表执行标识时错误', 16, 1) WITH NOWAIT
    RETURN 3
  END
   
  --更新单据主审核标志
  UPDATE DPD1 with(rowlock) 
    SET AuditingDate = GetDate(), Assessor = @Assessor, TradeID = @TradeID
  WHERE BCK01 = @BCK01 and BillID = @BillID
  
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('更新单据主表审核标志时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN 4
  END
  --更新待发药列表中的处理状态
  If exists(select * from DPD1 with(nolock) where BCK01 = @BCK01 and BillID = @BillID)
  begin
    SELECT VAI01 ,VAJ53 into #tmpVBX1 
    from VAJ2 j with(nolock)
       join DPD2 d on d.VAJ01 = j.VAJ01 and d.BBY01 = j.BBY01 
    where d.BCK01 = @BCK01 and d.BillID = @BillID 
    group by j.VAI01 , j.VAJ53 
    
    Update a 
      set a.VBX22 = 1 
    from VBX1 a with(rowlock)
      join #tmpVBX1 b on b.VAI01 = a.VBX18 
    where a.VBX03 = 2 and b.VAJ53 = 1
  end 
  
  /* 
  --更新VDE1中VDE03状态
  If @WorkFrom = 2
  begin
    Update a
      SET a.VDE03 = 2
    from VDE1 a
      join #TmpDPD2 b on b.VAJ01 = a.VAJ01
    where a.VAJ01 > 0
    
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
       ROLLBACK TRAN
       RAISERROR('更新科室消耗记录标志时错误,请重试！', 16, 1) WITH NOWAIT
       RETURN 5
    END
  end
   */
   
   
COMMIT TRAN



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--=============================================================================
--本过程适用于门诊病人及住院病人发药
--本过程支持按产品批号出库(如果@BatchNo传入是空值,将检索所有批号库存).
--部门药品发药单体--循环器(先进先出/先失效先出/后进先出)
--2013.04.22 出错时提示加上药品名称
--2014.11.27 发药选择批次时过滤掉过期的批次
--2014.12.31 减小所粒度
--2015.06.30 修改合计数量刚好等于剩余库存数量的情况
--2015.09.06 退药时不判断库存数量是否为负数
--2016.03.17 退药时不判断库存数量是否为负数
--2016.07.05 增加参数手麻药品消耗记录ID
--==============================================================================
CREATE PROCEDURE [DPS_ExportForm_Track]
    @BCK01             int--部门ID
  , @BillID            int--单据ID
  , @ExpType           int--出库算法,0.先进先出 1.失失效先出 2.后进先出

  , @VAA01             int--病人ID
  , @VAJ01             int--记费ID
  , @BBY01             int--药品ID
  , @BatchNo           varchar(30)--产品批号
  , @SendOutNum        numeric(18, 6)--发药数量
AS
DECLARE
    @iCount            int--计数器
  , @iTatol            int--总行数
  , @DPK_ID            int--部门库存ID
  , @Row_Max           int--单据最大行号
  , @DPD_MaxID         int--单据最大序号
  , @Unit              varchar(20)--单位
  , @PackSize          numeric(18, 4)--单位包装
  , @LastQuantity      numeric(18, 6)--库存数量
  , @PurchasePrice     numeric(18, 6)--进货价
  , @Remark            varchar(255)--备注
  , @BBY05             varchar(128)--药品名称
  , @CDate             datetime --当前时间
  --初始产品批号
  IF @BatchNo = '' SET @BatchNo =  '%'
  --取药品名称
  SELECT @BBY05 = BBY05 from BBY1 with(nolock)  where BBY01 = @BBY01 
  SET @CDate = GETDATE()
  --建循环用临时表
  CREATE TABLE #TmpDPK(UID int identity(1, 1)
    , ID int
    , BBY01 int
    , Unit varchar(20)
    , PackSize numeric(18, 4)
    , BatchNO varchar(30)
    , LastQuantity  numeric(18, 6)
    , PurchasePrice numeric(18, 6)
    , primary key nonclustered(UID)
   )
  --建库存数临时表
  SELECT K.ID, K.BBY01, K.Unit, K.PackSize, K.BatchNO, K.LastQuantity, K.PurchasePrice, K.ExpiryDate INTO #tmp_DPK1
  FROM DPK1 K with(nolock) 
  WHERE K.BCK01 = @BCK01 and K.BBY01 = @BBY01 
	and K.BatchNO like @BatchNo

  SELECT BBY01, DPK_ID, SUM(Quantity) Qty into #tmp_DPD2 
  FROM DPD2 with(nolock)  WHERE BCK01 = @BCK01 and BillID = @BillID and BBY01 = @BBY01 GROUP BY BBY01, DPK_ID

  SELECT K.ID, K.BBY01, K.Unit, K.PackSize, K.BatchNO, K.LastQuantity - ISNULL(D.Qty,0) AS LastQuantity, K.PurchasePrice, K.ExpiryDate INTO #DPK1
  FROM #tmp_DPK1 K 
  left join #tmp_DPD2 D  ON K.ID = D.DPK_ID and K.BBY01 = D.BBY01
  WHERE (K.LastQuantity - ISNULL(D.Qty,0) >= 0 and K.LastQuantity>0 and k.ExpiryDate > @CDate) or @SendOutNum<0

  IF @ExpType = 0 --先进先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ID --排序，非常重要。
  END ELSE
  IF @ExpType = 1 --先失效先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ExpiryDate --排序，非常重要。
  END ELSE
  IF @ExpType = 2 --后进先出
  BEGIN
    INSERT INTO #TmpDPK(ID, BBY01, Unit, PackSize, BatchNO, LastQuantity,  PurchasePrice)
    SELECT ID, BBY01, Unit, PackSize, BatchNO, LastQuantity, PurchasePrice FROM #DPK1
    ORDER BY ID DESC --排序，非常重要。
  END --if
  IF not exists(SELECT * FROM #TmpDPK)
  BEGIN
    RAISERROR('有效期内的库存不足，请刷新后重试！药房ID：%d,药品ID：%d，药品：%s,单据ID：%d', 16, 1,@BCK01,@BBY01,@BBY05 ,@BillID) WITH NOWAIT
    RETURN 1
  END

  --删除临时表
  DROP TABLE #DPK1
  --初始化变量
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #TmpDPK
  --获取发药单最大行号
  SET @Row_Max = ISNULL((SELECT MAX(xRowNum) FROM DPD2 with(nolock)  WHERE BCK01 = @BCK01 and BillID = @BillID),0)
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    --提取当前记录到变量中
    SELECT @DPK_ID = ID, @Unit = Unit, @PackSize = PackSize, @BatchNo = BatchNo, @LastQuantity = LastQuantity, @PurchasePrice = PurchasePrice
    FROM #TmpDPK WHERE UID = @iCount
    --每增加一条记录，序号加1
    SET @Row_Max = @Row_Max + 1
    --获取发药单最大序列Id
    EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT

    --发药数量小于库存数量时
    IF (@SendOutNum <= @LastQuantity and @SendOutNum>0) or @SendOutNum<0
    BEGIN
      --写药房发药单从表
      INSERT INTO DPD2(ID, BCK01, BillID
       , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, DPK_ID, Remark )
      VALUES(@DPD_MaxID, @BCK01, @BillID
       , @Row_Max, @BBY01, @Unit, @PackSize, @BatchNo, @SendOutNum, @PurchasePrice, @SendOutNum*@PurchasePrice
       , @VAA01, @VAJ01, 0, @DPK_ID, @Remark )
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('保存发药内容%d ,药品%s时错误,请重试！', 16, 1, @BBY01 , @BBY05) WITH NOWAIT
        RETURN 2
      END
      --清零
      SET @SendOutNum  = 0
      BREAK --中止循环
    END

    --写药房发药单从表
    INSERT INTO DPD2(ID, BCK01, BillID
     , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
     , VAA01, VAJ01, VAJ09, DPK_ID, Remark )
    VALUES(@DPD_MaxID, @BCK01, @BillID
     , @Row_Max, @BBY01, @Unit, @PackSize, @BatchNo, @LastQuantity, @PurchasePrice, @LastQuantity*@PurchasePrice
     , @VAA01, @VAJ01, 0, @DPK_ID, @Remark  )
    IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('保存发药内容%d,药品%s时错误,请重试！', 16, 1, @BBY01 , @BBY05) WITH NOWAIT
      RETURN 3
    END

    --剩余未发数量/金额
    SET @SendOutNum  = @SendOutNum  - @LastQuantity
    SET @iCount = @iCount + 1 --循环计数器加1
  END--while

  --删除临时表
  DROP TABLE #TmpDPK
  --判断是否发完
  IF @SendOutNum <> 0
  BEGIN
    DECLARE @YS varchar(10)
    SET @YS = CAST(@SendOutNum AS varchar(10))
    ROLLBACK TRAN
    RAISERROR('库存异常错误: 药品Id: %d,药品%s的数量还有: %s 未循环处理完，可能是库存不足，请刷新后重试。', 16, 1, @BBY01,@BBY05 , @YS) WITH NOWAIT
    RETURN 4
  END
   
COMMIT TRAN


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*------------------------------------------------------------------------------------------
--Author     <...king>
--Alter Date <...2016-10-24> 
--Description<...门诊和住院病人-发药(正数)/退药(负数)>
--History,2013.06.18,住院发药按病人所在病区或者领药科室进行自动发药分单
--History,2013.07.29,完善住院按灵药科室自动分单发药
--2013.08.13 根据BillID生成BillNO
--2013.10.29 返回当前BiLLNO
--2013.11.05 判断会员是否选择在药房扣费，未选择不让发药
--2014.12.31 减小锁粒度
--2015.12.03 判断DPD2表中已存在当前待发药记录，报错退出
--2016.09.14 去掉退药部分处理
-------------------------------------------------------------------------------------------*/
CREATE PROCEDURE [DPS_ExportForm_Update1]
    @BCK01              int--部门ID
  , @BillID             int--单据ID,新增单据时 @BillID = 0
  , @Doc                ntext--表单明细内容(发药内容)

  , @BillNO             varchar(30) output--发药单据号
  , @DeptID             int--领药部门Id
  , @Accepter           varchar(20)--领药人
  , @Ratifier           varchar(20)--批准人
  , @Dispenser          varchar(20)--配药者
  , @Sender             varchar(20)--发药人

  , @MakeDate           datetime--开单日期(本处是药房操作时间)
  , @Maker              varchar(20)--开单人(本处是操作员)
  , @Abstract           varchar(255)--摘要

  , @BillType           int--单据类型,0.门诊病人发药 1.住院病人发药 2.门诊病人退药 3.住院病人退药
  , @TradeType          int--交易类型,0.手工 1.系统自动生成
  , @ExpType            int--出库算法,0.先进先出 1.失失效先出 2.后进先出

  , @TradeTerm          varchar(255)--检索条件
  , @UserID             int--操作员ID
  , @TerminalNO         varchar(50)--终端编号
  , @TradeID            int = 0 OUT--流水号
  , @SearchStyle        smallint = 0--前台病人检索方式 0=按病人所在病区、1=按病人领药科室
AS
DECLARE
    @i                  int
  , @Count              int    
  , @iDoc               int
  , @iret               int--返回XML号
  , @Error              int--返回错误号
  , @iCount             int--计数器
  , @iTatol             int--总行数
  , @xRow_Max           int--单据最大行号
  , @DPD_MaxID          int--单据最大序号
  , @VAI_Count          int--扣费单计数器
  , @VAI_Tatol          int--扣费单总行数
  , @CurrBillId         int--当前待发药品BIllId 

  , @VAI01              int--单据ID,一卡通用.
  , @VAA07              int--就诊ID,一卡通用.
  , @VAA01              int--病人ID
  , @VAJ01              int--记费ID
  , @VAJ09              int--冲销ID
  , @BBY01              int--药品ID
  , @BatchNo            varchar(30)--产品批号
  , @VAJ25              numeric(18, 6)--发药数量
  , @VAJ38              numeric(18, 6)--金额
  , @Remark             varchar(255)--备注

if not exists(select * from DPD1_ID_2)
begin
  insert into DPD1_ID_2(Value)
  select ISNULL(Max(TradeID),0) from DPD1 with(nolock)
end

if not exists(select * from DPD2_ID)
begin
  insert into DPD2_ID(Value)
  select ISNULL(Max(ID),0) from DPD2 with(nolock)
end

if not exists(select * from DPD1_ID_B)
begin
  insert into DPD1_ID_B(BCK01, MaxBillID)
  select BCK01, MAX(BillID) AS BillID from DPD1 with(nolock) group by BCK01
end

  --解析XML文档
  EXEC @iret = sp_xml_preparedocument @iDoc OUTPUT, @Doc
  IF @iret > 0
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) WITH NOWAIT
    RETURN @iret
  END

  --===========================================声明发药记录临时表=================================================
  DECLARE @TmpVAJ1_1 TABLE(xRowNum int
   , VAA01 int, VAJ01 int, VAJ09 int, BBY01 int, BatchNO varchar(30)
   , VAJ25 numeric(18, 6), VAJ32 numeric(18, 6), VAJ33 numeric(18, 6)
   , VAJ36 numeric(18, 6), VAJ37 numeric(18, 6), VAJ54 varchar(255)
   , primary key nonclustered(xRowNum) ,BCK01A int , BCK01B int 
   , BCK01E int , BillId int 
   )   
  --=============================================XML内容写到临时表中===============================================
  INSERT INTO @TmpVAJ1_1(xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ37, VAJ54 , BCK01A , BCK01B , BCK01E )
  SELECT xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ38, VAJ54 , BCK01A , BCK01B , BCK01E 
  FROM OPENXML (@iDoc, '/ROOT/RxList', 8)
          WITH ( xRowNum           int--行号
               , VAA01             int--病人ID
               , VAJ01             int--记费ID
               , VAJ09             int--冲销ID

               , BBY01             int--药品ID
               , BatchNo           varchar(30)--产品批号
               , VAJ25             numeric(18, 6)--数量, 数次, 总数量
               , VAJ32             numeric(18, 6)--全价
               , VAJ33             numeric(18, 6)--单价, 标准单价
               , VAJ36             numeric(18, 6)--全额, 应收金额
               , VAJ38             numeric(18, 6)--结帐金额
               , VAJ54             varchar(255)--备注
               , BCK01A            int--病区ID
               , BCK01B            int--科室ID
               , BCK01E            int--领药科室ID
              )
  IF not exists(SELECT * FROM @TmpVAJ1_1)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('没有找到要提交的发药内容，请重新后重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END
  --=============================================按住院病区或者领药科室分组==============================================
   Create table #tmp_DPD1
   (  
     [ID] int identity(1,1) ,
     DeptId int ,
     BillId int      
   ) 
   INSERT INTO #tmp_DPD1(DeptId , BillId )
   SELECT @DeptID , 0   
  --========================================检查是否已发药或销帐,网络环境下,防止用户抢发===================================
  IF exists(SELECT v.VAJ01 FROM @TmpVAJ1_1 V , DPD2 b with(nolock) where b.VAJ01 = V.VAJ01)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('提交的待发药记录中存在发药记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END
  
  IF exists(SELECT v.VAJ01 FROM @TmpVAJ1_1 V , VAJ1 J with(nolock) WHERE (V.VAJ01 = J.VAJ01) and (J.VAJ48 > 0 or J.VAJ53 > 0 or J.VAJ05 = 9))
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('提交的内容中有已经执行或已经销帐或已经作废的记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END

  IF exists(SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33, COUNT(1) FROM @TmpVAJ1_1 GROUP BY VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33 HAVING COUNT(1)>1)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('提交的内容中有重复记录，请刷新后重试。如果问题仍存在，请立即与软件开发商联系。', 16, 1) WITH NOWAIT
    RETURN 2
  END

  IF exists(SELECT a.BBY01 FROM @TmpVAJ1_1 a join BAG1 b on a.BBY01=b.BBY01 join BBT1 c on b.BBX01=c.BBX01 where c.AAH01B>0 and c.AAH01B<(select isnull(AAH01,0) from BCE1 where BCE01=@UserID))
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('当前操作员调剂职务不符合要求。', 16, 1) WITH NOWAIT
    RETURN 2
  END

EXEC sp_xml_removedocument @iDoc
  --为一卡通发药/退药准备
  --IF (dbo.GetWorkingMode() in (1, 2)) and   ---取消会员模式判断
  if (@BillType in (0, 2))--0.发药 2.退药
  BEGIN
    --创建扣费临时表
    DECLARE @TMP_VAI_1 TABLE(
       UID int identity(1,1)
     , VAA01 int
     , VAA07 int
     , VAI01 int
     , primary key nonclustered(UID)
     )     
    --把要扣费的单据写入临时表
    IF @BillType = 0
    BEGIN
      INSERT INTO @TMP_VAI_1(VAA01, VAA07, VAI01)
      SELECT DISTINCT V.VAA01, J.VAA07, J.VAI01 --病人Id,就诊Id,单据Id
      FROM @TmpVAJ1_1 V join VAJ1 J with(nolock) 
        ON V.VAJ01 = J.VAJ01 and V.VAA01 = J.VAA01
      WHERE J.VAJ04 = 2    --2.划价记录
    END ELSE
    BEGIN
      INSERT INTO @TMP_VAI_1(VAA01, VAA07, VAI01)
      SELECT DISTINCT V.VAA01, J.VAA07, J.VAI01 --病人Id,就诊Id,单据Id
      FROM @TmpVAJ1_1 V join VAJ1 J with(nolock) 
        ON V.VAJ01 = J.VAJ01 and V.VAA01 = J.VAA01
      WHERE J.VAJ05 = 4 --4.退药记录
    END
    --判断当前一卡通是否选择在药房扣费，如果不是，则退出
    if exists(select * from @TMP_VAI_1)
    BEGIN
      Declare @AutoChargePlace varchar(128) 
      SELECT @AutoChargePlace = Value from SYS_Parameters where ProductID = 9999 and ProgramID = 9999 and ParamNo = 43 
      
      IF CHARINDEX('1' , ISNUll(@AutoChargePlace , '')) <= 0
      BEGIN
        RAISERROR('当前会员自动扣费场合未选择“药房发药”,请先结账后再来取药.',1,16) WITH NOWAIT
        RETURN 1
      END
    END
    --声明临时变量
    DECLARE 
      @AType int
    , @OP_DeptId int
    , @OP_Code varchar(20)
    , @OP_Name varchar(20)
    
    SELECT @VAI_Tatol = COUNT(*), @VAI_Count = 1 FROM @TMP_VAI_1 --获得要结帐的单据行数
    IF @VAI_Tatol > 0
    BEGIN
      SET @AType = CASE WHEN @BillType = 0 THEN 0 ELSE 1 END --0=扣费、1=退费
      --获得当前操作员姓名、科室
      SELECT @OP_DeptId = BCK01, @OP_Code = BCE02, @OP_Name = BCE03 FROM BCE1 WHERE BCE01 = @UserID
    END
  END
--=============================================产生新的单据ID=================================================
  SELECT @i = 0 , @Count = COUNT(1) from #tmp_DPD1 
  EXEC Core_NewId_DPD1_ID_B @BCK01, @BillID OUT , @Count 
  While @i < @Count 
  BEGIN
    Update #tmp_DPD1 
      SET BillId = @BillID - @Count - @i + 1
    where [ID] = @i + 1 
    SET @i = @i + 1
  END
  
  Update @TmpVAJ1_1 
      SET BillId = @BillID 
   
BEGIN TRAN
  /*******以下是处理会员一卡通扣费过程,循环结帐多张单据.************************************************************/
  WHILE @VAI_Count <= @VAI_Tatol --如果单据都已扣费，则不会执行。
  BEGIN
    SELECT @VAA01 = VAA01, @VAA07 = VAA07, @VAI01 = VAI01 FROM @TMP_VAI_1 WHERE UID = @VAI_Count
    --会员一卡通扣费存取过程   --传单据金额   sum（VAJ38）
    SET @VAJ38 = ISNULL((SELECT SUM(VAJ38) FROM VAJ1 with(nolock) WHERE VAI01 = @VAI01),0)
    
    /*一卡通扣费过程 */
    EXEC HORate_CardCharge_Update @VAA01, @VAA07, @VAI01, 1, @UserID, @OP_DeptId, @OP_Code, @OP_Name, @VAJ38, @Error OUT, @AType, 2
    IF (@Error <> 0) OR (@@ERROR <> 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('会员结帐扣费失败，错误号: %d。(说明：0=成功、1=单据已经扣费、2=余额不足、3=更新失败）', 16, 1, @Error) WITH NOWAIT
      RETURN
    END
    SET @VAI_Count = @VAI_Count + 1 --计数器加,继续执行下一张单据.
  END --while

  /******以下是处理发药****************************************************************************************/
  ---===========================================生成退药临时表===================================================
  /*
  --2014.03.26退药时取原发药批次的数据及数量，退回到原对应批次上面
  SELECT  j.BillID,identity(int , 1 ,1) as xRowNum
       ,  d.Unit, d.PackSize, d.BatchNo, d.Quantity as VAJ25, d.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, d.DPK_ID, j.VAJ54 into #tmpDPD2_2
      FROM @TmpVAJ1_1 j join V_DPD2_Full d with(nolock)   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
        ON j.BBY01 = d.BBY01 and j.VAJ09 = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01
        
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #tmpDPD2_2
  while @iCount < @iTatol 
  BEGIN
      EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT
      --获取发药单最大行号
      SET @xRow_Max = ISNULL((SELECT MAX(xRowNum) FROM DPD2 WHERE BCK01 = @BCK01 and BillID = @BillID),0)+1
      
      --写药房发药单从表
      INSERT INTO DPD2(ID, BCK01, BillID
       , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, DPK_ID, Remark)
      SELECT TOP 1 @DPD_MaxID, @BCK01, j.BillID
       , @xRow_Max, @BBY01, j.Unit, j.PackSize, j.BatchNo, j.VAJ25, j.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, j.DPK_ID, j.VAJ54
      FROM #tmpDPD2_2 J
      WHERE j.xRowNum = @iCount  
      
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('处理退药记费Id: %d, 冲销Id: %d时错误。原因：药房未找到原始发药记录，不能在本药房退药。', 16, 1, @VAJ01, @VAJ09) WITH NOWAIT
        RETURN @@ERROR
      END
  END   
  */    
  --===============================================初始化变量=====================================================
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM @TmpVAJ1_1
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    SELECT @VAA01 = VAA01, @VAJ01 = VAJ01, @VAJ09 = VAJ09, @BBY01 = BBY01, @BatchNo = BatchNo, @VAJ25 = VAJ25 ,
           @CurrBillId = BillId 
    FROM @TmpVAJ1_1 WHERE xRowNum = @iCount

    IF (@VAJ09 <= 0) and (@VAJ25 >= 0)
    BEGIN --处理发药记录
      EXEC @Error = DPS_ExportForm_Track @BCK01, @CurrBillId, @ExpType, @VAA01, @VAJ01, @BBY01, @BatchNo, @VAJ25
      IF @Error > 0  --部门药品发药单体--循环器(先进先出/先失效先出/后进先出)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('循环处理到第%d行时错误,请刷新后重试！', 16, 1, @iCount) WITH NOWAIT
        RETURN @Error
      END
    END --if
    SET @iCount = @iCount + 1 --计数器加1
  END --while

  --更新发药单记费价格
  UPDATE d SET
    VAJ09 = j.VAJ09--冲销ID
  , VAJ32 = j.VAJ32--全价
  , VAJ33 = j.VAJ33--单价
  , VAJ36 = j.VAJ32 * d.Quantity
  , VAJ37 = j.VAJ33 * d.Quantity
  , VAJ38 = j.VAJ33 * d.Quantity
  FROM DPD2 d with(rowlock)
    join @TmpVAJ1_1 j ON d.VAJ01 = j.VAJ01 and d.BBY01 = j.BBY01
  WHERE BCK01 = @BCK01 and d.BillID = j.BillId 

  --写部门发药单主表
  INSERT INTO DPD1(BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
   , MakeDate, Maker, Abstract, BillType, TradeType, TradeTerm, UserID, TerminalNO)
  SELECT @BCK01, BillID
   , Right('0000000'+convert(varchar(10),BillID),8), DeptID, @Accepter, @Ratifier, @Dispenser, @Sender
   , Getdate(), @Maker, @Abstract, @BillType, @TradeType, @TradeTerm, @UserID, @TerminalNO from #tmp_DPD1 
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表头记录时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN @@ERROR
  END

  --审核药房发药单,同时更新库存表.
  SELECT @i = 0 , @Count = COUNT(1) from #tmp_DPD1 
  while @i < @Count 
  BEGIN
     SELECT @CurrBillId = BillId from #tmp_DPD1 where [ID] = @i + 1
     EXEC @Error = DPS_ExportForm_Check @BCK01, @CurrBillId, @Sender, @TerminalNO, @TradeID OUT
     IF @Error > 0
     BEGIN
       ROLLBACK TRAN
       RAISERROR('保存单据表体未能通过审核,错误号%d', 16, 1, @Error) WITH NOWAIT
       RETURN @Error
     END
     SET @i = @i + 1
  END
  
  SELECT @BillNO = BillNO from DPD1 with(nolock) where TradeID = @TradeID
COMMIT TRAN



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*---------------------------------------------------------------------------------
--Author     <...8065>
--Alter Date <...2016-09-19> 
--Description<...住院病人发药(分表)>
--History    <...2014.12.31减小锁粒度>
---------------------------------------------------------------------------------*/
CREATE PROCEDURE [DPS_ExportForm_Update1Sep]
    @BCK01              int--部门ID
  , @BillID             int--单据ID,新增单据时 @BillID = 0
  , @Doc                ntext--表单明细内容(发药内容)

  , @BillNO             varchar(30) output--发药单据号
  , @DeptID             int--领药部门Id
  , @Accepter           varchar(20)--领药人
  , @Ratifier           varchar(20)--批准人
  , @Dispenser          varchar(20)--配药者
  , @Sender             varchar(20)--发药人

  , @MakeDate           datetime--开单日期(本处是药房操作时间)
  , @Maker              varchar(20)--开单人(本处是操作员)
  , @Abstract           varchar(255)--摘要

  , @BillType           int--单据类型,0.门诊病人发药 1.住院病人发药 2.门诊病人退药 3.住院病人退药
  , @TradeType          int--交易类型,0.手工 1.系统自动生成
  , @ExpType            int--出库算法,0.先进先出 1.失失效先出 2.后进先出

  , @TradeTerm          varchar(255)--检索条件
  , @UserID             int--操作员ID
  , @TerminalNO         varchar(50)--终端编号
  , @TradeID            int = 0 OUT--流水号
  , @SearchStyle        smallint = 0--前台病人检索方式 0=按病人所在病区、1=按病人领药科室
AS
DECLARE
    @i                  int
  , @Count              int    
  , @iDoc               int
  , @iret               int--返回XML号
  , @Error              int--返回错误号
  , @iCount             int--计数器
  , @iTatol             int--总行数
  , @xRow_Max           int--单据最大行号
  , @DPD_MaxID          int--单据最大序号
  , @VAI_Count          int--扣费单计数器
  , @VAI_Tatol          int--扣费单总行数
  , @CurrBillId         int--当前待发药品BIllId 

  , @VAI01              int--单据ID,一卡通用.
  , @VAA07              int--就诊ID,一卡通用.
  , @VAA01              int--病人ID
  , @VAJ01              int--记费ID
  , @VAJ09              int--冲销ID
  , @BBY01              int--药品ID
  , @BatchNo            varchar(30)--产品批号
  , @VAJ25              numeric(18, 6)--发药数量
  , @VAJ38              numeric(18, 6)--金额
  , @Remark             varchar(255)--备注

if not exists(select * from DPD1_ID_2)
begin
  insert into DPD1_ID_2(Value)
  select ISNULL(Max(TradeID),0) from DPD1 with(nolock)
end

if not exists(select * from DPD2_ID)
begin
  insert into DPD2_ID(Value)
  select ISNULL(Max(ID),0) from DPD2 with(nolock)
end

if not exists(select * from DPD1_ID_B)
begin
  insert into DPD1_ID_B(BCK01, MaxBillID)
  select BCK01, MAX(BillID) AS BillID from DPD1 with(nolock) group by BCK01
end

  --解析XML文档
  EXEC @iret = sp_xml_preparedocument @iDoc OUTPUT, @Doc
  IF @iret > 0
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) WITH NOWAIT
    RETURN @iret
  END

  --===========================================声明发药记录临时表=================================================
  DECLARE @TmpVAJ1_1 TABLE(xRowNum int
   , VAA01 int, VAJ01 int, VAJ09 int, BBY01 int, BatchNO varchar(30)
   , VAJ25 numeric(18, 6), VAJ32 numeric(18, 6), VAJ33 numeric(18, 6)
   , VAJ36 numeric(18, 6), VAJ37 numeric(18, 6), VAJ54 varchar(255)
   , primary key nonclustered(xRowNum) ,BCK01A int , BCK01B int 
   , BCK01E int , BillId int 
   )   
  --=============================================XML内容写到临时表中===============================================
  INSERT INTO @TmpVAJ1_1(xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ37, VAJ54 , BCK01A , BCK01B , BCK01E )
  SELECT xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ38, VAJ54 , BCK01A , BCK01B , BCK01E 
  FROM OPENXML (@iDoc, '/ROOT/RxList', 8)
          WITH ( xRowNum           int--行号
               , VAA01             int--病人ID
               , VAJ01             int--记费ID
               , VAJ09             int--冲销ID

               , BBY01             int--药品ID
               , BatchNo           varchar(30)--产品批号
               , VAJ25             numeric(18, 6)--数量, 数次, 总数量
               , VAJ32             numeric(18, 6)--全价
               , VAJ33             numeric(18, 6)--单价, 标准单价
               , VAJ36             numeric(18, 6)--全额, 应收金额
               , VAJ38             numeric(18, 6)--结帐金额
               , VAJ54             varchar(255)--备注
               , BCK01A            int--病区ID
               , BCK01B            int--科室ID
               , BCK01E            int--领药科室ID
              )
  IF not exists(SELECT * FROM @TmpVAJ1_1)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('没有找到要提交的发药内容，请重新后重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END

  IF exists(SELECT * FROM @TmpVAJ1_1 where BCK01A<>@DeptID and BCK01B<>@DeptID and BCK01E<>@DeptID)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('存在领药科室[%d]不正确的发药记录，请重新后重试！', 16, 1,@DeptID) WITH NOWAIT
    RETURN 1
  END

  --=============================================按住院病区或者领药科室分组==============================================
   Create table #tmp_DPD1
   (  
     [ID] int identity(1,1) ,
     DeptId int ,
     BillId int      
   ) 
   --===========================================如果TradeId >0重新取BillId================================================
   select @BillID = BillID from DPD1 with(nolock) where TradeID = @TradeID 
   
   INSERT INTO #tmp_DPD1(DeptId , BillId )
   SELECT @DeptID , @BillID    
  --========================================检查是否已发药或销帐,网络环境下,防止用户抢发=================================== 
  IF exists(SELECT VAJ01 FROM @TmpVAJ1_1 V WHERE exists(SELECT VAJ01 FROM VAJ2 J with(nolock)  WHERE (V.VAJ01 = J.VAJ01) and (J.VAJ48 > 0 or J.VAJ53 > 0 or J.VAJ05 = 9)))
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('提交的内容中有已经执行或已经销帐或已经作废的记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END

  IF exists(SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33, COUNT(1) FROM @TmpVAJ1_1 GROUP BY VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33 HAVING COUNT(1)>1)
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('提交的内容中有重复记录，请刷新后重试。如果问题仍存在，请立即与软件开发商联系。', 16, 1) WITH NOWAIT
    RETURN 2
  END

  IF exists(SELECT a.BBY01 FROM @TmpVAJ1_1 a join BAG1 b on a.BBY01=b.BBY01 join BBT1 c on b.BBX01=c.BBX01 where c.AAH01B>0 and c.AAH01B<(select isnull(AAH01,0) from BCE1 where BCE01=@UserID))
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('当前操作员调剂职务不符合要求。', 16, 1) WITH NOWAIT
    RETURN 2
  END
--=============================================产生新的单据ID=================================================
  If ISNULL(@BillID , 0) <= 0 
  BEGIN
     SELECT @i = 0 , @Count = COUNT(1) from #tmp_DPD1 
     EXEC Core_NewId_DPD1_ID_B @BCK01, @BillID OUT , @Count 
     While @i < @Count 
     BEGIN
       Update #tmp_DPD1 
         SET BillId = @BillID - @Count - @i + 1
       where [ID] = @i + 1 
       SET @i = @i + 1
     END   
  END
  
  Update @TmpVAJ1_1 
    SET BillId = @BillID 
--===============================2014.03.26退药时取原发药批次的数据及数量，退回到原对应批次上面====================
  if exists(select * from @TmpVAJ1_1 where VAJ09>0)
  select identity(int , 1 ,1) as xRowNum ,* into #tmpDPD2_2 from (
  SELECT  @BillID BillID, d.BBY01 , @BCK01 as BCK01
       ,  d.Unit, d.PackSize, d.BatchNo, -d.Quantity as VAJ25, d.PurchasePrice, -PurchaseAmount as PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, d.DPK_ID, j.VAJ54 
      FROM @TmpVAJ1_1 j join DPD2 d with(nolock)    --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
        ON j.BBY01 = d.BBY01 and j.VAJ09 = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01
	 where j.VAJ09>0 
     union all
	SELECT  @BillID BillID, d.BBY01 , @BCK01 as BCK01
       ,  d.Unit, d.PackSize, d.BatchNo, -d.Quantity as VAJ25, d.PurchasePrice, -PurchaseAmount as PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, d.DPK_ID, j.VAJ54 
      FROM @TmpVAJ1_1 j join DPD2_2 d with(nolock)    --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
        ON j.BBY01 = d.BBY01 and j.VAJ09 = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01
	where j.VAJ09>0 
) as tmp
EXEC sp_xml_removedocument @iDoc        

BEGIN TRAN

 --==================================================以下是处理退药================================================
  if exists(select * from @TmpVAJ1_1 where VAJ09>0)
  BEGIN
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #tmpDPD2_2
  while @iCount <= @iTatol 
  BEGIN
      EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT
      --获取发药单最大行号
      SET @xRow_Max = ISNULL((SELECT MAX(xRowNum) FROM DPD2 with(nolock)  WHERE BCK01 = @BCK01 and BillID = @BillID),0)+1
      
      --写药房发药单从表
      INSERT INTO DPD2(ID, BCK01, BillID
       , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, DPK_ID, Remark)
      SELECT @DPD_MaxID, @BCK01, j.BillID
       , @xRow_Max, BBY01, j.Unit, j.PackSize, j.BatchNo, j.VAJ25, j.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, j.DPK_ID, j.VAJ54
      FROM #tmpDPD2_2 J
      WHERE j.xRowNum = @iCount  
      
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('处理退药记费Id: %d, 冲销Id: %d时错误。原因：药房未找到原始发药记录，不能在本药房退药。', 16, 1, @VAJ01, @VAJ09) WITH NOWAIT
        RETURN @@ERROR
      END
      SET @iCount = @iCount + 1
  END 
  END
  --===============================================初始化变量=====================================================
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM @TmpVAJ1_1
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    SELECT @VAA01 = VAA01, @VAJ01 = VAJ01, @VAJ09 = VAJ09, @BBY01 = BBY01, @BatchNo = BatchNo, @VAJ25 = VAJ25 ,
           @CurrBillId = BillId 
    FROM @TmpVAJ1_1 WHERE xRowNum = @iCount

    --处理退药记录
    /*IF (@VAJ09 > 0) and (@VAJ25 < 0) --冲销ID大于0并且数量是负数的,视为病人退药.
    BEGIN --对于病人退药,首先确认药房是否发过此药品,没有发过的,禁止从此药房退.
      --获取发药单最大列ID
      EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT
      --获取发药单最大行号
      SET @xRow_Max = ISNULL((SELECT MAX(xRowNum) FROM DPD2 WHERE BCK01 = @BCK01 and BillID = @BillID),0)+1
      
      --写药房发药单从表
      INSERT INTO DPD2(ID, BCK01, BillID
       , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, DPK_ID, Remark)
      SELECT TOP 1 @DPD_MaxID, @BCK01, j.BillID
       , @xRow_Max, @BBY01, d.Unit, d.PackSize, d.BatchNo, j.VAJ25, d.PurchasePrice, j.VAJ25*d.PurchasePrice
       , j.VAA01, j.VAJ01, j.VAJ09, j.VAJ32, j.VAJ33, d.DPK_ID, j.VAJ54
      FROM @TmpVAJ1_1 j join V_DPD2_Full d   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
        ON j.BBY01 = d.BBY01 and j.VAJ09 = d.VAJ01 and j.VAA01 = d.VAA01
      WHERE j.xRowNum = @iCount and d.BCK01 = @BCK01 and d.BBY01 = @BBY01
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('处理退药记费Id: %d, 冲销Id: %d时错误。原因：药房未找到原始发药记录，不能在本药房退药。', 16, 1, @VAJ01, @VAJ09) WITH NOWAIT
        RETURN @@ERROR
      END
    END ELSE*/
    IF (@VAJ09 <= 0) and (@VAJ25 >= 0)
    BEGIN --处理发药记录
      EXEC @Error = DPS_ExportForm_Track @BCK01, @CurrBillId, @ExpType, @VAA01, @VAJ01, @BBY01, @BatchNo, @VAJ25
      IF @Error > 0  --部门药品发药单体--循环器(先进先出/先失效先出/后进先出)
      BEGIN
        ROLLBACK TRAN
        RAISERROR('循环处理到第%d行时错误,请刷新后重试！', 16, 1, @iCount) WITH NOWAIT
        RETURN @Error
      END
    END --if
    SET @iCount = @iCount + 1 --计数器加1
  END --while

  --更新发药单记费价格
  UPDATE d SET
    VAJ09 = j.VAJ09--冲销ID
  , VAJ32 = j.VAJ32--全价
  , VAJ33 = j.VAJ33--单价
  , VAJ36 = j.VAJ32 * d.Quantity
  , VAJ37 = j.VAJ33 * d.Quantity
  , VAJ38 = j.VAJ33 * d.Quantity
  FROM DPD2 d with(rowlock)
   join @TmpVAJ1_1 j ON d.VAJ01 = j.VAJ01 and d.BBY01 = j.BBY01
  WHERE BCK01 = @BCK01 and d.BillID = j.BillId 

  --写部门发药单主表
  If not exists(select * from DPD1 with(nolock)  where BillID = @BillID and BCK01 = @BCK01 ) 
  BEGIN
    INSERT INTO DPD1(BCK01, BillID
     , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
     , MakeDate, Maker, Abstract, BillType, TradeType, TradeTerm, UserID, TerminalNO)
    SELECT @BCK01, BillID
     , Right('0000000'+convert(varchar(10),BillID),8), DeptID, @Accepter, @Ratifier, @Dispenser, @Sender
     , Getdate(), @Maker, @Abstract, @BillType, @TradeType, @TradeTerm, @UserID, @TerminalNO from #tmp_DPD1 
     
    IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('保存单据表头记录时错误,请重试！', 16, 1) WITH NOWAIT
      RETURN @@ERROR
    END
  END

  --审核药房发药单,同时更新库存表.
  SELECT @i = 0 , @Count = COUNT(1) from #tmp_DPD1 
  while @i < @Count 
  BEGIN
     SELECT @CurrBillId = BillId from #tmp_DPD1 where [ID] = @i + 1
     EXEC @Error = DPS_ExportForm_CheckSep @BCK01, @CurrBillId, @Sender, @TerminalNO, @TradeID OUT , @Doc 
     IF @Error > 0
     BEGIN
       ROLLBACK TRAN
       RAISERROR('保存单据表体未能通过审核,错误号%d', 16, 1, @Error) WITH NOWAIT
       RETURN @Error
     END
     SET @i = @i + 1
  END
  
  SELECT @BillNO = BillNO from DPD1 with(nolock)  where TradeID = @TradeID
COMMIT TRAN



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--================================================================================================
--Alter Date 2016.10.24
--门诊药房病人退药
--本过程由[HORate_VAK1_Delete]调用
--2013.01.23 增加调用场合
--2013.03.05 增加会员病人药房退药
--2014.12.31 减小锁粒度
--=================================================================================================
CREATE PROCEDURE [DPS_ExportForm_Update2]
    @BCK01              int--药房ID, 禁止为空
  --, @Doc                ntext--表单内容(退药内容)
  , @VAK01A             int--冲销结帐ID , 禁止为空
  , @DeptID             int--就诊科室Id,  禁止为空
  , @Accepter           varchar(20)--病人姓名
  , @Sender             varchar(20)--发药人
  , @UserID             int--操作员ID
  , @TerminalNO         varchar(50)--终端号
  , @VAI01A             int = 0 --单据ID
AS
DECLARE
    @Error1             int
  , @TradeID            int --流水号
  , @iCount             int--计数器
  , @iTatol             int--总行数
  , @BillID             int--单据ID
  , @VAJ01              int--记费ID
  , @VAJ01B             int--关联ID 
  , @BBY01              int--药品ID
  , @DPD_MaxID          int--单据最大序号  
  , @BillNO             varchar(30)--发药单号
  
  Create table #TmpVAJ1_2
  ( 
    xRowNum int identity ,
    VAA01 int ,
    VAJ01 int ,
    VAJ09 int ,
    BBY01 int ,
    VAJ25 numeric(18,4),
    VAJ01B int ,
    VAJ32 numeric(18,6) ,
    VAJ33 numeric(18,6) ,
    VAJ54 varchar(255)
  )
  --门诊退费药品更新库存
  If ISNULL(@VAK01A , 0 ) > 0
  BEGIN      
     INSERT INTO #TmpVAJ1_2(VAA01 , VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, VAJ54 , VAJ32 , VAJ33)    
     SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, '门诊退费' VAJ54  , VAJ32 , VAJ33
     FROM 
       (SELECT a.VAA01, a.VAJ01, a.VAJ09, a.BBY01, a.VAJ25, case when ISNULL(a.VAJ01B ,0) > 0 then a.VAJ01B else a.VAJ09 end as VAJ01B, VAJ32 , VAJ33 
        FROM VAJ1 a with(nolock)
        join VBY1 b with(nolock) on b.VAJ01 = a.VAJ09      
     WHERE b.BCK01 = @BCK01 AND b.VBY09 = 1 and a.VAK01 = @VAK01A and a.BDN01 <= '3' and a.VAJ48 = 0 AND a.VAJ53 = 0 and a.VAJ25 < 0
     ) A 
   END ELSE If ISNULL(@VAI01A , 0) > 0 --药房退药药品更新库存
   BEGIN
     INSERT INTO #TmpVAJ1_2(VAA01 , VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, VAJ54 , VAJ32 , VAJ33)   
     SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ01B, '药房退药' VAJ54 , VAJ32 , VAJ33 
     FROM 
       (SELECT a.VAA01, a.VAJ01, a.VAJ09, a.BBY01, a.VAJ25, a.VAJ01B , VAJ32 , VAJ33
        FROM VAJ1 a with(nolock)   
     WHERE a.VAI01 = @VAI01A  and a.BCK01D = @BCK01 and a.BDN01 <= '3' and a.VAJ48 = 0 AND a.VAJ53 = 0 and a.VAJ25 < 0
     ) A 
   END
   
  IF not exists(SELECT * FROM #TmpVAJ1_2)
  BEGIN
    RAISERROR('没有找到要提交的退药内容，请重新后重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END

  --检查是否已发药或销帐,网络环境下,防止用户抢发...
  IF exists(SELECT * FROM VAJ1 J with(nolock) WHERE exists(SELECT * FROM #TmpVAJ1_2 V WHERE V.VAJ01 = J.VAJ01) and (J.VAJ48 <> 0 or J.VAJ53 <> 0))
  BEGIN
    RAISERROR('提交的内容中有已经执行或已经销帐的记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END
  
  --IF exists(SELECT * FROM #TmpVAJ1_2 WHERE VAJ09 <= 0 or VAJ25 > 0)
  IF exists(SELECT * FROM #TmpVAJ1_2 WHERE VAJ01B <= 0 or VAJ25 > 0)
  BEGIN
    RAISERROR('提交的内容中有非法数据：关联Id必须大于0并且数据必须小于0', 16, 1) WITH NOWAIT
    RETURN 1
  END
 
  --初始化变量
  --SELECT @iTatol = COUNT(*), @iCount = 1 FROM #TmpVAJ1_2
  --发药单据号
  SELECT @BillNO = RIGHT('0000000000' + CAST((ISNULL(MAX(BillID),0)+1) AS VARCHAR),8) FROM V_DPD1_FULL with(nolock) WHERE BCK01 = @BCK01
  --产生新的单据ID
  EXEC Core_NewId_DPD1_ID_B @BCK01, @BillID OUT
  
  --===============================2014.05.08退药时取原发药批次的数据及数量，退回到原对应批次上面====================
  SELECT  @BillID BillID,identity(int , 1 ,1) as xRowNum , d.BBY01 , @BCK01 as BCK01 , j.VAJ32 , j.VAJ33 
       ,  d.Unit, d.PackSize, d.BatchNo, -d.Quantity as VAJ25, d.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, d.DPK_ID, j.VAJ54 , j.VAJ01B into #tmpDPD2_2
      FROM #TmpVAJ1_2 j
        join DPD2 d with(nolock)   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
         ON j.BBY01 = d.BBY01 and j.VAJ01B = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01 
      where j.VAJ01B>0
  If not exists(select * from #tmpDPD2_2) and exists(select * from #TmpVAJ1_2 where VAJ01B > 0)
  begin
    INSERT INTO #tmpDPD2_2(BillID , BBY01 ,  BCK01 , VAJ32 ,VAJ33 
       , Unit, PackSize, BatchNo,  VAJ25, PurchasePrice, PurchaseAmount
       , VAA01, VAJ01, VAJ09, DPK_ID, VAJ54 , VAJ01B)
       
    SELECT  @BillID BillID, d.BBY01 , @BCK01 as BCK01 , j.VAJ32 , j.VAJ33 
       ,  d.Unit, d.PackSize, d.BatchNo, -d.Quantity as VAJ25, d.PurchasePrice, PurchaseAmount
       , j.VAA01, j.VAJ01, j.VAJ09, d.DPK_ID, j.VAJ54 , j.VAJ01B 
      FROM #TmpVAJ1_2 j 
        join DPD2_2 d with(nolock)   --对于病人退药,要找出之前的发药信息取出BatchNo/DPK_ID
          ON j.BBY01 = d.BBY01 and j.VAJ01B = d.VAJ01 and j.VAA01 = d.VAA01 and d.BCK01 = @BCK01
  end  
--初始化变量
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM #tmpDPD2_2        
BEGIN TRAN
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN   
    SELECT @VAJ01 = VAJ01, @VAJ01B = VAJ01B, @BBY01 = BBY01 FROM #tmpDPD2_2 WHERE xRowNum = @iCount
    --获取发药单最大列ID
    EXEC Core_NewId_DPD2_ID @DPD_MaxID OUT
    
    --写药房发药单从表
    INSERT INTO DPD2(ID, BCK01, BillID  --对于病人退药,首先确认药房是否发过此药品,没有发过的,禁止从此药房退.
     , xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity, PurchasePrice, PurchaseAmount
     , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ37, VAJ38, DPK_ID, Remark)     
    SELECT TOP 1 @DPD_MaxID, @BCK01, @BillID
     , @iCount, j.BBY01, j.Unit, j.PackSize, j.BatchNo, j.VAJ25, j.PurchasePrice, j.VAJ25*j.PurchasePrice
     , j.VAA01, j.VAJ01, 0, j.VAJ32, j.VAJ33, j.VAJ32*j.VAJ25, j.VAJ33*j.VAJ25, j.VAJ33*j.VAJ25, j.DPK_ID, j.VAJ54
    FROM #tmpDPD2_2 j         
    WHERE  
      j.xRowNum = @iCount 
    IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('处理退药记费Id: %d, 关联Id: %d时错误。原因：药房未找到原始发药记录，不能本药房退药。', 16, 1, @VAJ01, @VAJ01B) WITH NOWAIT
      RETURN @@ERROR
    END  
    SET @iCount = @iCount + 1 --计数器加1
  END --while

  --写部门发药单主表
  INSERT INTO DPD1(BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
   , MakeDate, Maker, Abstract, BillType, TradeType, TradeTerm, UserID, TerminalNO)
  VALUES(@BCK01, @BillID
   , @BillNO, @DeptID, @Accepter, @Sender, @Sender, @Sender
   , Getdate(), @Sender, '', 2, 1, '', @UserID, @TerminalNO)
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表头记录时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN @@ERROR
  END

  --审核药房发药单,同时更新库存表.
  EXEC @Error1 = DPS_ExportForm_Check @BCK01, @BillID, @Sender, @TerminalNO, @TradeID OUT
  IF @Error1 > 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表体未能通过审核,错误号%d', 16, 1, @Error1) WITH NOWAIT
    RETURN @Error1
  END
COMMIT TRAN


GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/*--------------------------------------------------------------------------------
--Author     <...king>
--Alter Date <...2015-04-23> 
--Description<...门诊结帐冲销后，自动产生发药单)>
--History    <...2015.12.31减小锁粒度>
----------------------------------------------------------------------------------*/
CREATE PROCEDURE [DPS_ExportForm_Update3]
    @BCK01              int--部门ID
  --, @Doc                ntext--表单明细内容(发药内容)
  , @VAK01               int--结帐ID
  , @DeptID             int--就诊科室ID
  , @Accepter           varchar(20)--病人姓名
  , @Ratifier           varchar(20)--批准人
  , @Sender             varchar(20)--发药人
  , @Maker              varchar(20)

  , @UserID             int--操作员ID
  , @TerminalNO         varchar(50)--终端编号
  , @TradeID            int = 0 OUT--流水号
AS
DECLARE
    @iDoc               int
  , @iret               int--返回XML号
  , @iCount             int--计数器
  , @iTatol             int--总行数
  , @BillID             int--单据ID,新增单据时 @BillID = 0      
  , @BillNO             varchar(30)--发药单据号
  , @VAA01              int--病人ID
  , @VAJ01              int--记费ID
  , @BBY01              int--药品ID
  , @BatchNo            varchar(30)--产品批号
  , @VAJ25              numeric(18, 6)--发药数量
  , @ExpType            int--出库算法,0.先进先出 1.失失效先出 2.后进先出
  
  --解析XML文档
  /*EXEC @iret = sp_xml_preparedocument @iDoc OUTPUT, @Doc
  IF @iret > 0
  BEGIN
    EXEC sp_xml_removedocument @iDoc
    RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) WITH NOWAIT
    RETURN @iret
  END*/

  --声明发药记录临时表
  DECLARE @TmpVAJ1_1 TABLE(
     xRowNum int IDENTITY 
   , VAA01 int
   , VAJ01 int
   , VAJ09 int
   , BBY01 int
   , BatchNO varchar(30)
   , VAJ25 numeric(18, 6)
   , VAJ32 numeric(18, 6)
   , VAJ33 numeric(18, 6)
   , VAJ36 numeric(18, 6)
   , VAJ37 numeric(18, 6)
   , VAJ54 varchar(255)
   , primary key nonclustered(xRowNum)
  )
  
  SELECT VAK01 INTO #tmpAllVAK1
  FROM VAK1 WHERE VAK28 = @VAK01 
  --XML内容写到临时表中
  /*INSERT INTO @TmpVAJ1_1(xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ37, VAJ54)
  SELECT xRowNum, VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ38, VAJ54
  FROM OPENXML (@iDoc, '/ROOT/RxList', 8)
          WITH ( xRowNum           int--行号
               , VAA01             int--病人ID
               , VAJ01             int--记费ID
               , VAJ09             int--冲销ID

               , BBY01             int--药品ID
               , BatchNo           varchar(30)--产品批号
               , VAJ25             numeric(18, 6)--数量, 数次, 总数量
               , VAJ32             numeric(18, 6)--全价
               , VAJ33             numeric(18, 6)--单价, 标准单价
               , VAJ36             numeric(18, 6)--全额, 应收金额
               , VAJ38             numeric(18, 6)--结帐金额
               , VAJ54             varchar(255)--备注
              )*/
              
  INSERT INTO @TmpVAJ1_1 (VAA01, VAJ01, VAJ09, BBY01, BatchNO, VAJ25, VAJ32, VAJ33, VAJ36, VAJ37, VAJ54)
  SELECT VAA01, a.VAJ01, VAJ09, BBY01, '', a.VAJ25, VAJ32, VAJ33, VAJ36, VAJ38, VAJ54
        FROM VAJ1 a with(nolock)
           JOIN #tmpAllVAK1 b ON b.VAK01 = a.VAK01 
           JOIN VBY1 c ON c.VAJ01 = a.VAJ01B 
        WHERE c.BCK01 = @BCK01 AND a.BDN01 <= '3' AND a.VAJ48 = 0 AND a.VAJ53 = 0 
        
  IF not exists(SELECT * FROM @TmpVAJ1_1)
  BEGIN
    RAISERROR('没有找到要提交的发药内容，请重新后重试！', 16, 1) WITH NOWAIT
    RETURN 1
  END

  --检查是否已发药或销帐,网络环境下,防止用户抢发...
  IF exists(SELECT * FROM VAJ1 J with(nolock) WHERE exists(SELECT * FROM @TmpVAJ1_1 V WHERE V.VAJ01 = J.VAJ01) and (J.VAJ48 <> 0 or J.VAJ53 <> 0))
  BEGIN
    RAISERROR('提交的内容中有已经执行或已经销帐的记录，请刷新后重试！', 16, 1) WITH NOWAIT
    RETURN 2
  END

  IF exists(SELECT VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33, COUNT(1) FROM @TmpVAJ1_1 GROUP BY VAA01, VAJ01, VAJ09, BBY01, VAJ25, VAJ33 HAVING COUNT(1)>1)
  BEGIN
    RAISERROR('提交的内容中有重复记录，请刷新后重试。如果问题仍存在，请立即与软件开发商联系。', 16, 1) WITH NOWAIT
    RETURN 3
  END

  --出库算法,0.先进先出
  SET @ExpType = 0
  --产生新的单据ID
  EXEC Core_NewId_DPD1_ID_B @BCK01, @BillID OUT
  --产生新的单据NO
  SET @BillNO = (SELECT RIGHT('0000000000' + CAST((ISNULL(MAX(BillID),0)+1) AS VARCHAR),8) FROM V_DPD1_FULL with(nolock) WHERE BCK01 = @BCK01)  

BEGIN TRAN  
  --初始化变量
  SELECT @iTatol = COUNT(*), @iCount = 1 FROM @TmpVAJ1_1
  --循环逐条处理记录
  WHILE @iCount <= @iTatol
  BEGIN
    SELECT @VAA01 = VAA01, @VAJ01 = VAJ01, @BBY01 = BBY01, @BatchNo = BatchNo, @VAJ25 = VAJ25
    FROM @TmpVAJ1_1 WHERE xRowNum = @iCount    
    --处理发药记录
    EXEC @iret = DPS_ExportForm_Track @BCK01, @BillID, @ExpType, @VAA01, @VAJ01, @BBY01, @BatchNo, @VAJ25
    IF @iret > 0--部门药品发药单体--循环器(先进先出/先失效先出/后进先出)
    BEGIN
      ROLLBACK TRAN
      RAISERROR('循环处理到第%d行时错误,请刷新后重试！', 16, 1, @iCount) WITH NOWAIT
      RETURN @iret
    END    
    SET @iCount = @iCount + 1 --计数器加1
  END --while

  --更新发药单记费价格
  UPDATE DPD2 SET
    VAJ09 = j.VAJ09--冲销ID
  , VAJ32 = j.VAJ32--全价
  , VAJ33 = j.VAJ33--单价
  , VAJ36 = j.VAJ32 * d.Quantity
  , VAJ37 = j.VAJ33 * d.Quantity
  , VAJ38 = j.VAJ33 * d.Quantity
  FROM DPD2 d with(rowlock) join @TmpVAJ1_1 j
    ON d.VAJ01 = j.VAJ01 and d.BBY01 = j.BBY01
  WHERE BCK01 = @BCK01 and BillID = @BillID

  --写部门发药单主表
  INSERT INTO DPD1(BCK01, BillID
   , BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
   , MakeDate, Maker, Abstract, BillType, TradeType, TradeTerm, UserID, TerminalNO)
  VALUES(@BCK01, @BillID
   , @BillNO, @DeptID, @Accepter, @Ratifier, @Sender, @Sender
   , Getdate(), @Maker, '', 0, 1, '', @UserID, @TerminalNO)
  IF (@@ROWCOUNT = 0) OR (@@ERROR > 0)
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表头记录时错误,请重试！', 16, 1) WITH NOWAIT
    RETURN @@ERROR
  END
 
  --审核药房发药单,同时更新库存表.
  EXEC @iret = DPS_ExportForm_Check @BCK01, @BillID, @Sender, @TerminalNO, @TradeID OUT
  IF @iret > 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR('保存单据表体未能通过审核,错误号%d', 16, 1, @iret) WITH NOWAIT
    RETURN @iret
  END
  
  --药房发药后自动调价
  /*
  IF exists(SELECT * FROM SYS_Parameters WHERE ProductID = 100 and ProgramID = 108001 and ParamNo = 100 and Value = 0)
  BEGIN
    EXEC HO_BBY_BBY25Update1
  END
  */
COMMIT TRAN


GO