SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
--查询
DECLARE @sqlstr varchar(2000), @BeginDate datetime, @EndDate datetime, @VAA05 varchar(30), @BBY01 int, @ACF01 int
--select @BeginDate = '%s', @EndDate = '%s', @VAA05 = '%s', @BBY01 = %d, @ACF01 = %d
select @BeginDate = '2010-10-01 00:00:00', @EndDate = '2016-11-01 23:59:59', @VAA05 = '铁路', @BBY01 = 0, @ACF01 = 1
if OBJECT_ID('tempdb..#tmp_Material') is not null drop table #tmp_Material

set @sqlstr = '
SELECT 0 as Checked, B.VAA05, C.BBY01, C.BBY05, C.BBY06, D.ABW02, A.VAJ25, A.BCQ04
, A.VAJ32, A.VAJ33, A.VAJ34, A.VAJ35, A.VAJ36, A.VAJ37, A.VAJ38, E.BCK01, E.BCK03, A.VAJ54
, A.VAJ01, A.VAJ09
into #tmp_Material FROM '
if @ACF01 = 1
  set @sqlstr = @sqlstr + 'VAJ1'
else
  set @sqlstr = @sqlstr + 'VAJ2'
set @sqlstr = @sqlstr + ' A join VAA1 B on B.VAA01 = A.VAA01
 join BBY1 C on C.BBY01 = A.BBY01
 join ABW1 D on D.ABW01 = B.ABW01
 join BCK1 E on E.BCK01 = A.BCK01D
WHERE A.VAJ53 = 0 AND A.BDN01 = ''4'' and A.VAJ05 = 4 and A.VAJ46 >= ''' 
+ convert(varchar(30), @BeginDate, 121) + ''' and A.VAJ46 <= ''' + convert(varchar(30), @EndDate, 121) + ''''
+ ' and B.VAA05 like ''%' + @VAA05 + '%'''
set @sqlstr = @sqlstr + ' select * from #tmp_Material'
EXEC(@sqlstr)

*/

-- EXEC MDS_MaterialSend_Task1 '<Root><VAJ><Ie VAJ01="123" VAJ09="84411"/></VAJ></Root>', 0, '', ''
ALTER proc [MDS_MaterialSend_Task1]
  @XML ntext
  , @UserID int
  , @UserName varchar(30)
  , @TerminalNO varchar(50)
as
declare @iDoc int, @iret int
 , @i int, @iCount int, @VAJ09 int, @BCK01 int, @BillID int, @BillNO varchar(30)
 , @TradeID int, @MaxID int, @NewBillID int

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

if OBJECT_ID('tempdb..#tmpVAJ') is not null drop table #tmpVAJ
SELECT *, IDENTITY(INT, 1, 1) AS xRowNum
INTO #tmpVAJ
FROM openxml(@iDoc, '/Root/VAJ/Ie', 8)
  with(
	VAJ01 int,	VAJ09 int ,Checked int
	)
where Checked=1
EXEC sp_xml_removedocument @iDoc

--------------------检测数据
if not exists (select * from #tmpVAJ)
begin
  raiserror('没有找到需要退回的材料，请重试！', 16, 1) with nowait
  return 1
end

if object_id('tempdb..#tmp01') is not null drop table #tmp01
select distinct A.BCK01, A.BillID, identity(int, 1, 1) xRow into #tmp01 
from MDD2 A join #tmpVAJ B on B.VAJ09 = A.VAJ01 order by A.BCK01

if OBJECT_ID('tempdb..#tmpMDD1') is not null drop table #tmpMDD1
select *, IDENTITY(INT, 1, 1) AS xRow into #tmpMDD1 from MDD1 where 1=2

if OBJECT_ID('tempdb..#tmpMDD2') is not null drop table #tmpMDD2
select *, identity(int, 1, 1) as xRow into #tmpMDD2 from MDD2 where 1=2

select @iCount = max(xRow), @i = 1, @BCK01 = 0, @BillID = 0, @NewBillID = 0 from #tmp01

while @i <= @iCount
begin
  if (select BCK01 from #tmp01 where xRow = @i) <> @BCK01
  begin
    select @BCK01 = BCK01, @BillID = BillID from #tmp01 where xRow = @i
    set @NewBillID = (select max(BillID)+1 from MDD1 where BCK01 = @BCK01)
  end
  else
  begin
    select @BCK01 = BCK01, @BillID = BillID from #tmp01 where xRow = @i
    set @NewBillID = @NewBillID + 1
  end
  
  --产生发料流水ID
  EXEC Core_NewId_MDD1_ID @TradeID OUT
  insert into #tmpMDD2(ID, BCK01, BillID, xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
      , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
      , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ38, MDK_ID)
  select A.ID, @BCK01, @NewBillID, A.xRowNum, A.BBY01, A.Unit, A.PackSize, A.BatchNo, -A.Quantity
      , A.PurchasePrice, -A.PurchaseAmount, A.RetailPrice, -A.RetailAmount, A.CostPrice, -A.CostAmount
      , A.VAA01, B.VAJ01, B.VAJ09, A.VAJ32, A.VAJ33, -A.VAJ36, -A.VAJ38, A.MDK_ID
  from MDD2 A join #tmpVAJ B on a.BCK01 = @BCK01 and A.BillID = @BillID and B.VAJ09 = A.VAJ01

  insert into #tmpMDD1(BCK01, BillID, BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
   , MakeDate, Maker, Abstract, BillType, TradeType, ExpType, AuditingDate, Assessor, TradeID
   , TradeTerm, LPntTimes, GPntTimes, UserID, TerminalNO, TransferFlag, SysDate)
  select DISTINCT @BCK01, @NewBillID, RIGHT('0000000000' + CAST(ISNULL(@NewBillID,0) AS VARCHAR),8), A.DeptID, A.Accepter, A.Ratifier, A.Dispenser, A.Sender
   , getdate(), @UserName, A.Abstract, (Case when BillType = 0 then 2 when BillType = 1 then 3 when BillType = 4 then 5 else 6 end) BillType, 1, A.ExpType, GETDATE(), @UserName, @TradeID
   , A.TradeTerm, A.LPntTimes, A.GPntTimes, @UserID, @TerminalNO, A.TransferFlag, GETDATE()
  from MDD1 A where A.BCK01 = @BCK01 and A.BillID = @BillID

  set @i = @i + 1
end

set @MaxID = isnull((select MAX(ID) from MDD2), 0)
update #tmpMDD2 set ID = xRow + @MaxID

--select * from #tmpMDD1
--select * from #tmpMDD2
--return

begin tran
  insert into MDD1(BCK01, BillID, BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
	, MakeDate, Maker, Abstract, BillType, TradeType, ExpType, AuditingDate, Assessor, TradeID
	, TradeTerm, LPntTimes, GPntTimes, UserID, TerminalNO, TransferFlag, SysDate)
  select BCK01, BillID, BillNO, DeptID, Accepter, Ratifier, Dispenser, Sender
	, MakeDate, Maker, Abstract, BillType, TradeType, ExpType, AuditingDate, Assessor, TradeID
	, TradeTerm, LPntTimes, GPntTimes, UserID, TerminalNO, TransferFlag, SysDate
  from #tmpMDD1
  if (@@ROWCOUNT = 0) or (@@ERROR <> 0)
  begin
    rollback tran
    raiserror('无法生成发料单主表，请重试！', 16, 1)
    return @@ERROR
  end
  
  insert into MDD2(ID, BCK01, BillID, xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
    , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
    , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ38, MDK_ID, Remark)
  select ID, BCK01, BillID, xRowNum, BBY01, Unit, PackSize, BatchNo, Quantity
    , PurchasePrice, PurchaseAmount, RetailPrice, RetailAmount, CostPrice, CostAmount
    , VAA01, VAJ01, VAJ09, VAJ32, VAJ33, VAJ36, VAJ38, MDK_ID, Remark
  from #tmpMDD2
  if (@@ROWCOUNT = 0) or (@@ERROR <> 0)
  begin
    rollback tran
    raiserror('无法生成发料单子表，请重试！', 16, 1)
    return @@ERROR
  end

  --生成单据成功  则增加库存
  update A set A.LastQuantity = A.LastQuantity - B.Quantity
  from MDK1 A join #tmpMDD2 B on B.MDK_ID = A.ID

  --退料反填VAJ1
  update J set 
    BCK01D = A.BCK01--执行科室ID
    , BCE03C = @UserName--执行者
    , VAJ48  = (SELECT top 1 B.TradeID FROM #tmpMDD1 B where B.BCK01 = A.BCK01 AND B.BillID = A.BillID)
    , VAJ51  = GetDate()--执行时间
    , VAJ52  = Getdate()--执行交易时间
    , VAJ53  = 1--执行情况：0:未执行; 1:执行完成; 2:拒绝执行; 3:正在执行
  from VAJ1 J join #tmpMDD2 A on A.VAJ01 = J.VAJ01
  
  update J set 
    BCK01D = A.BCK01--执行科室ID
    , BCE03C = @UserName--执行者
    , VAJ48  = (SELECT top 1 B.TradeID FROM #tmpMDD1 B where B.BCK01 = A.BCK01 AND B.BillID = A.BillID)
    , VAJ51  = GetDate()--执行时间
    , VAJ52  = Getdate()--执行交易时间
    , VAJ53  = 1--执行情况：0:未执行; 1:执行完成; 2:拒绝执行; 3:正在执行
  from VAJ2 J join #tmpMDD2 A on A.VAJ01 = J.VAJ01
  
  update J set
    VDE03 = 3 -- 2:已扣减  3:退回
  from VDE1 J join #tmpMDD2 A on A.VAJ09 = J.VAJ09
  
commit tran

select top 1 BCK01, BillNO from #tmpMDD1