CREATE TRIGGER tr_update_Stock --自动采购触发器
ON T_Product
FOR update
AS
IF (update(Stock)) --判断stock字段是否更新
BEGIN
--查询出库存低于下限的产品近一个月的消耗量
--创建临时表
IF object_id('[tempdb].[dbo].##table1') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
DROP TABLE ##table1
SELECT * INTO ##table1 FROM (SELECT row_number() OVER (ORDER BY ProductID) AS rowNo,c.ZNumber - c.Stock AS purchaseNum,* FROM (
SELECT ProductID,SUM(s.Numbers) ZNumber,p.Stock,SUM(s.OutMoney) ZMoney,p.Price
FROM [dbo].[T_StorageOut] t inner join[dbo].[T_StorageRelation] s
ON t.StorageOutID=s.StorageOutID inner join T_Product p ON s.ProductID=p.Id
WHERE t.MakerTime between Convert(VARCHAR,DATEADD(MONTH,-1,GETDATE()),23) and Convert(VARCHAR,GETDATE(),23) and ProductID in (SELECT Id FROM V_ProductSupplierInfo where (Stock-StockLowerLimit)<0)
GROUP BY ProductID,p.Stock,p.Price) c) ddd
--计算用参数
DECLARE @purchaseNum INT,@Price NVARCHAR(50),@totalMoney NVARCHAR(50),@ZpurchaseTotal INT --采购数量,单价,总金额,总数量
SELECT @totalMoney = 0,@purchaseNum = 0,@Price = 0,@ZpurchaseTotal=0
--采购单所需参数
DECLARE @PurchaseNumber NVARCHAR(50),@DopurchaseId INT,@DopurchaseTime NVARCHAR(200),@PurchaseTotal INT,@PurchasePrice NVARCHAR(200),@PurchaseState INT,@PurchaseType INT
SET @PurchaseNumber='JH'+replace(replace(replace(convert(VARCHAR,getdate(),120),'-',''),' ',''),':','') --进货单号JH20171031092322
--采购单详情所需参数 （产品id，产品采购价格）
DECLARE @StockProductId INT,@DPurchasePrice NVARCHAR(20)
--循环用参数
DECLARE @curIndex INT,@rowCount INT
--判断用参数
DECLARE @OTCount INT,@PTCount INT
SET @curIndex = 1
SELECT @rowCount = COUNT(1) FROM ##table1
IF(@rowCount>0)
BEGIN
WHILE @curIndex <= @rowCount
BEGIN
--查询出第一行数据的采购数量，单价，产品id
SELECT @purchaseNum = purchaseNum,@Price = Price,@StockProductId =ProductID FROM ##table1 WHERE rowNo = @curIndex
--采购总金额
SET @totalMoney = @totalMoney + @purchaseNum * CONVERT (INT,@price)
--采购总数量
SET @ZpurchaseTotal =@ZpurchaseTotal + @purchaseNum
--单个产品的采购金额
SET @DPurchasePrice=@purchaseNum * CONVERT (INT,@price)
IF object_id('[tempdb].[dbo].#PTable') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
DROP TABLE #PTable
IF object_id('[tempdb].[dbo].#OTable') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
DROP TABLE #OTable
--根据产品id查询出包含该产品的采购单已全部审批
SELECT * INTO #PTable FROM (SELECT PurchaseState FROM [dbo].[T_Purchase_Order] WHERE PurchaseNumber in(SELECT PurchaseOrderId FROM [dbo].[T_Purchase_OrderDetails] WHERE StockProductId=@StockProductId) and PurchaseState=0) AS a
--根据产品id查询出包含该产品的订单已全部入库
SELECT * INTO #OTable FROM (SELECT OrderState FROM T_Order WHERE OrderNumber in(SELECT OrderNumber FROM T_OrderDetails WHERE ProductId=@StockProductId) and OrderState=0) AS c
SELECT @PTCount = COUNT(1) FROM #PTable
SELECT @OTCount = COUNT(1) FROM #OTable
IF (@PTCount=0) --已全部审批
BEGIN
IF(@OTCount=0) --=0表示包含该产品的订单均已入库，可以生成新的
BEGIN
INSERT INTO [dbo].[T_Purchase_OrderDetails] VALUES(@PurchaseNumber,@StockProductId,@purchaseNum,@DPurchasePrice)
END
END
SET @curIndex = @curIndex + 1
END
END
set @DopurchaseId = 16646 --自动生成
set @DopurchaseTime=Convert(NVARCHAR,getdate(),23) --当前时间 2017-10-31
set @PurchaseTotal =@ZpurchaseTotal
set @PurchasePrice =@totalMoney
set @PurchaseState =0 --未审核
set @PurchaseType = 2 --自动生成
--变量赋值完成，对采购单做添加操作
IF (@PTCount=0)
BEGIN
INSERT INTO T_Purchase_Order VALUES(@PurchaseNumber,@DopurchaseId,@DopurchaseTime,@PurchaseTotal,@PurchasePrice,@PurchaseState,@PurchaseType)
END
END
