进销存系统中的一个先入先出或随机出库的存储过程

发布：昆明沃德软件　发布时间：2012-10-26　　浏览次数：9880

　　这是我们沃德软件公司(http://www.ynorg.cn)在开发一个进销存系统中的使用到的存储过程，主要实现在销售中货品的先入先出和随机出库，扣减库存，并且完成一系列的提成计算。主要表涉及到进货表、进货明细表、销售主表、销售明细表、库存表、提成协议表的操作与读取，技术上用到了XML传值、虚拟表、游标、批量更新、批量插入与修改技术。

　　本软件开发主要思路是在操作中先以DATATABLE临时存储条码扫描的产品与数量，当然在这个时候第一步要判断库存的数量够不够，然后将DATATABLE里的记录转换成xml提交给存储过程，因为是联网式多收银点操作，存储过程在真正操作数据库时需要再次判断库存的数量，如果不通过则返回库存不足的产品给客户端，如果通过则进行下一步的操作。因为同一样商品可能会有多个供应商，多个批次供应，每个批次的进货价格可能不一样，为了精确成本，不能采用平均进货价的算法，所以必须又要和进货表关联起来出库而不能只是对库存表数量进行增减。

USE [FWD_ProductSaleStore]
GO
/****** 对象: StoredProcedure [dbo].[proc_FWD_ProductSell] 脚本日期: 05/30/2012 11:23:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
****************************************************************************************************
*** Author: 沃德软件(http://www.ynorg.cn)
*** Create date: 2012-05-30
*** Description:产品销售、提成计算、先进先出与随机成本计算
****************************************************************************************************
*/
ALTER PROCEDURE [dbo].[proc_FWD_ProductSell]
-- 销售产品列表和总单的xml
@ISRandom BIT,--0是随机销售1为先进先出（针对进货记录扣减）
@SellOrderxml XML,--销售主表的XML
@SellOrderDetailxml XML,--销售详细的XML
@ISERR INT OUTPUT,--(0成功，1库存不足，2保存失败)
@ERRINFO VARCHAR(1000) OUTPUT--库存不足时返回哪些产品库存不足
WITH ENCRYPTION AS
BEGIN
SET NOCOUNT ON;
DECLARE @Pointer INT
DECLARE @ERRCOUNT INT
--申明销售表
DECLARE @tmpSellOrderXML TABLE([orderCode] [varchar](50),[sellAdminID] [int],[SalesID] [int],[companyID] [int],[sellDate] [datetime],[createDate] [datetime],[communityOfGroupID] [int],[communityOfTravelID] [int],[guideID] [int],[driverID] [int],[ppID] [int],[spID] [int],[totalOldPrice] [decimal](18, 2),[totalPrice] [decimal](18, 2),[creditcard] [decimal](18, 2),[cash] [decimal](18, 2),[transfer] [decimal](18, 2),[orderStatus] [int],[RoyaltyStatus] [int],[IsBackOrder] [bit],[payTotalPrice] [decimal](18, 2),[commissionPriceOfGroup] [decimal](18, 2),[commissionPriceOfGiveGroup] [decimal](18, 2),[commissionPriceOfTravel] [decimal](18, 2),[commissionPriceOfGiveTravel] [decimal](18, 2),[commissionPriceOfGuide] [decimal](18, 2),[commissionPriceOfGiveGuide] [decimal](18, 2),[commissionPriceOfDriver] [decimal](18, 2),[commissionPriceOfGiveDriver] [decimal](18, 2),[commissionPriceOfCompany] [decimal](18, 2),[commissionPriceOfGiveCompany] [decimal](18, 2),[remark] [text])
--申明临时销售详细表
DECLARE @tmpXML TABLE(ProductNormID INT,ProductbarCode VARCHAR(100),ProductID INT,ProductName VARCHAR(100),ProductNorm VARCHAR(100),ProductNum DECIMAL(18,2),ProductPrice DECIMAL(18,4),ProductDiscount DECIMAL(18,2),ProudctOldPrice DECIMAL(18,2),ProductUnit VARCHAR(100),SinglePrice DECIMAL(18,2),SupplierID INT,[Type] INT,PurchaseDetailID INT,CompanyID INT,DetailtotalPrice DECIMAL(18,4),STOCK DECIMAL(18,2))
--申明正式销售详细表（已拆分）
DECLARE @tmpSell TABLE([orderID] [int], [productID] [int], [normID] [int], [sellCount] [decimal](18, 2),[sellPrice] [decimal](18, 2),[sellDiscount] [decimal](18, 2), [sellOldPrice] [decimal](18, 2),[singlePrice] [decimal](18, 2),[supplierID] [int],[purchaseDetailID] [int],[ppdetailID] [int], [groupPrice] [decimal](18, 2), [travelPrice] [decimal](18, 2), [guidePrice] [decimal](18, 2), [driverPrice] [decimal](18, 2), [companyPrice] [decimal](18, 2),[orderCode] [varchar](50), [sellAdminID] [int],[SalesID] [int],[companyID] [int],[sellDate] [datetime],[communityOfGroupID] [int],[communityOfTravelID] [int],[guideID] [int], [driverID] [int],[DetailtotalPrice] [decimal](18, 4))
--申明进货详细表
DECLARE @PurchaseDetail TABLE(ID INT,orderID INT,productID INT,normID INT,singlePrice DECIMAL(18,2),SuplusNum DECIMAL(18,2),supplierID INT)
--将主销售记录插入临时表
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@SellOrderxml
INSERT @tmpSellOrderXML
SELECT * FROM OPENXML (@Pointer,'/DocumentElement/Table1',2)
WITH
(
[orderCode] [varchar](50),
[sellAdminID] [int],
[SalesID] [int],
[companyID] [int],
[sellDate] [datetime],
[createDate] [datetime],
[communityOfGroupID] [int],
[communityOfTravelID] [int],
[guideID] [int],
[driverID] [int],
[ppID] [int],
[spID] [int],
[totalOldPrice] [decimal](18, 2),
[totalPrice] [decimal](18, 2),
[creditcard] [decimal](18, 2),
[cash] [decimal](18, 2),
[transfer] [decimal](18, 2),
[orderStatus] [int],
[RoyaltyStatus] [int],
[IsBackOrder] [bit],
[payTotalPrice] [decimal](18, 2),
[commissionPriceOfGroup] [decimal](18, 2),
[commissionPriceOfGiveGroup] [decimal](18, 2),
[commissionPriceOfTravel] [decimal](18, 2),
[commissionPriceOfGiveTravel] [decimal](18, 2),
[commissionPriceOfGuide] [decimal](18, 2),
[commissionPriceOfGiveGuide] [decimal](18, 2),
[commissionPriceOfDriver] [decimal](18, 2),
[commissionPriceOfGiveDriver] [decimal](18, 2),
[commissionPriceOfCompany] [decimal](18, 2),
[commissionPriceOfGiveCompany] [decimal](18, 2),
[remark] [text]
)
EXEC sp_xml_removedocument @Pointer
--将销售详细插入临时表
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@SellOrderDetailxml
INSERT @tmpXML
SELECT * FROM OPENXML (@Pointer,'/DocumentElement/Table1',2)
WITH
(
ProductNormID INT,
ProductbarCode VARCHAR(100),
ProductID INT,
ProductName VARCHAR(100),
ProductNorm VARCHAR(100),
ProductNum DECIMAL(18,2),
ProductPrice DECIMAL(18,4),
ProductDiscount DECIMAL(18,2),
ProudctOldPrice DECIMAL(18,2),
ProductUnit VARCHAR(100),
SinglePrice DECIMAL(18,2),
SupplierID INT,
[Type] VARCHAR,
PurchaseDetailID INT,
CompanyID INT,
DetailtotalPrice DECIMAL(18,4),
Stock DECIMAL(18,2)
)
EXEC sp_xml_removedocument @Pointer
--查询是否有库存不足的商品
SELECT @ERRCOUNT=COUNT(1) FROM VR_FWD_SellPDetail_Validity V
INNER JOIN @tmpXML X
ON V.productID=X.productID AND V.normID=X.ProductNormID AND V.companyID=X.CompanyID
WHERE V.stockCount<X.ProductNum
--如果有则返回库存不足的信息
IF @ERRCOUNT>0
BEGIN
SET @ISERR=1
SET @ERRINFO=''
SELECT @ERRINFO=@ERRINFO+'，'+V.productName+'/'+V.normName+'-库存'+CAST(V.stockCount AS VARCHAR(100)) FROM VR_FWD_SellPDetail_Validity V
INNER JOIN @tmpXML X
ON V.productID=X.productID AND V.normID=X.ProductNormID AND V.companyID=X.CompanyID
WHERE V.stockCount<X.ProductNum
SET @ERRINFO=RIGHT(@ERRINFO,len(@ERRINFO)-1)
RETURN
END
--根据销售详细查询进货详细入表变量
INSERT @PurchaseDetail
SELECT PD.ID,PD.orderID,PD.productID,PD.normID,PD.singlePrice,(PD.purchaseCount-PD.sellCount-PD.breakageCount-backCount) AS SurplusNum,PO.supplierID from FWD_PurchaseDetail PD
LEFT JOIN FWD_PurchaseOrder PO on PD.orderID= PO.ID
INNER JOIN @tmpXML X ON PD. normID=X.ProductNormID AND PD.productID=X.ProductID
INNER JOIN FWD_COMPANY CP ON X.CompanyID=CP.ID AND PO.purchaseCompanyID=CP.ROOTID
WHERE PO.STATUS=1 and PD.sellStatus=0 ORDER BY normID
--开始根据进货详细对销售详细进行先入先出或者随机销售进行拆分核算成本
DECLARE @ProductNormID INT --规格ID
DECLARE @ProductID INT--产品ID
DECLARE @ProductNum DECIMAL(18,2)--销售数量
DECLARE @ProductPrice DECIMAL(18,4)--销售价格(原价*折扣)
DECLARE @ProductDiscount DECIMAL(18,2)--销售折扣
DECLARE @ProudctOldPrice DECIMAL(18,2)--销售原价
DECLARE @CompanyID INT--所属公司
DECLARE @SinglePrice DECIMAL(18,2)--进货价格
DECLARE @SupplierID INT--供应商ID
DECLARE @PurchaseDetailID INT--进货详细ID
DECLARE @DetailtotalPrice DECIMAL(18,4)--小计
DECLARE @orderID INT--进货主ID
DECLARE @SuplusNum DECIMAL(18,2)--进货详细的数量
--开始循环销售表
DECLARE sell_cur CURSOR FOR
SELECT ProductNormID,ProductID,ProductNum,ProductPrice,ProductDiscount,ProudctOldPrice,CompanyID,DetailtotalPrice FROM @tmpXML
OPEN sell_cur
FETCH NEXT FROM sell_cur INTO @ProductNormID,@ProductID,@ProductNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@CompanyID,@DetailtotalPrice
WHILE @@FETCH_STATUS = 0
BEGIN
--开始循环供应表
IF @ISRandom=0--如果是随机出库
BEGIN
DECLARE suppy_cur CURSOR FOR
SELECT ID ,orderID,singlePrice,SuplusNum ,supplierID FROM @PurchaseDetail WHERE normid=@ProductNormID ORDER BY NEWID()
END
ELSE--先进先出
BEGIN
DECLARE suppy_cur CURSOR FOR
SELECT ID ,orderID,singlePrice,SuplusNum ,supplierID FROM @PurchaseDetail WHERE normid=@ProductNormID ORDER BY ID ASC
END
OPEN suppy_cur
FETCH NEXT FROM suppy_cur INTO @PurchaseDetailID,@orderID,@SinglePrice,@SuplusNum,@SupplierID
WHILE @@FETCH_STATUS = 0 AND @ProductNum>0--供应记录循环完和销售数量分拆完
BEGIN
IF @ProductNum<=@SuplusNum --如果销售数量小于等于供应数量将销售数量写入
BEGIN
INSERT @tmpSell SELECT 0,@ProductID,@ProductNormID,@ProductNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@SinglePrice,@SupplierID,@PurchaseDetailID,0,0,0,0,0,0,'',0,0,@CompanyID,'2012-1-1 00:00:00',0,0,0,0,@ProductPrice*@ProductNum FROM VR_FWD_PromotionalProtocolDetail_Validity WHERE normid=@ProductNormID
SET @ProductNum=@ProductNum-@SuplusNum
END
ELSE--如果销售数量大于供应数量将供应数量替换原来的销售数量并进行下一轮循环
BEGIN
INSERT @tmpSell SELECT 0,@ProductID,@ProductNormID,@SuplusNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@SinglePrice,@SupplierID,@PurchaseDetailID,0,0,0,0,0,0,'',0,0,@CompanyID,'2012-1-1 00:00:00',0,0,0,0,@ProductPrice*@SuplusNum FROM VR_FWD_PromotionalProtocolDetail_Validity WHERE normid=@ProductNormID
SET @ProductNum=@ProductNum-@SuplusNum
END
FETCH NEXT FROM suppy_cur INTO @PurchaseDetailID,@orderID,@SinglePrice,@SuplusNum,@SupplierID
END
CLOSE suppy_cur
DEALLOCATE suppy_cur
FETCH NEXT FROM sell_cur INTO @ProductNormID,@ProductID,@ProductNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@CompanyID,@DetailtotalPrice
END
CLOSE sell_cur
DEALLOCATE sell_cur
--批量计算提成
UPDATE @tmpSell SET
[groupPrice]=CASE WHEN O.communityOfGroupID>0 THEN V.communityOfGroupPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfGroupPercent ELSE 0 END,
[travelPrice]=CASE WHEN O.communityOfTravelID>0 THEN V.communityOfRRPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfRRPercent ELSE 0 END,
[guidePrice]=CASE WHEN O.guideID>0 THEN V.guidePrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.guidePercent ELSE 0 END,
[driverPrice]=CASE WHEN O.driverID>0 THEN V.driverPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.driverPercent ELSE 0 END,
[companyPrice]=ISNULL(V.communityOfPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfPercent,0)
FROM @tmpSell S LEFT JOIN VR_FWD_PromotionalProtocolDetail_Validity V ON S.[normID]=V.normid
LEFT JOIN @tmpSellOrderXML O ON 1=1
--更新临时销售主表的提成数据
UPDATE @tmpSellOrderXML SET
[commissionPriceOfGroup]=ISNULL(D.TotalgroupPrice,0),
[commissionPriceOfTravel]=ISNULL(D.TotaltravelPrice,0),
[commissionPriceOfGuide]=ISNULL(D.TotalguidePrice,0),
[commissionPriceOfDriver]=ISNULL(D.TotaldriverPrice,0),
[commissionPriceOfCompany]=ISNULL(D.TotalcompanyPrice,0),
[ppID]=0,--提成协议ID
[spID]=0--销售协议ID
FROM @tmpSellOrderXML O
RIGHT JOIN (SELECT SUM([groupPrice]) AS TotalgroupPrice,SUM([travelPrice]) AS TotaltravelPrice,SUM([guidePrice]) AS TotalguidePrice,SUM([driverPrice]) AS TotaldriverPrice,SUM([companyPrice]) AS TotalcompanyPrice FROM @tmpSell) D ON 1=1
BEGIN TRANSACTION
DECLARE @error INT
SET @error=0
--正式将销售主记录插入表
INSERT INTO [FWD_SellOrder]
([orderCode],[sellAdminID],[SalesID],[companyID],[sellDate],[createDate],[communityOfGroupID],[communityOfTravelID],[guideID],[driverID],[ppID],[spID],[totalOldPrice],[totalPrice],[creditcard],[cash],[transfer],[orderStatus],[RoyaltyStatus],[IsBackOrder],[payTotalPrice],[commissionPriceOfGroup],[commissionPriceOfGiveGroup],[commissionPriceOfTravel],[commissionPriceOfGiveTravel],[commissionPriceOfGuide],[commissionPriceOfGiveGuide],[commissionPriceOfDriver],[commissionPriceOfGiveDriver],[commissionPriceOfCompany],[commissionPriceOfGiveCompany],[remark])
SELECT * FROM @tmpSellOrderXML
SET @error=@@error+@error
--更新销售临时表里的数据
UPDATE @tmpSell SET [orderID]=SCOPE_IDENTITY(),[orderCode]=O.[orderCode],[sellAdminID]=O.[sellAdminID],[SalesID]=O.[SalesID],[sellDate]=O.[sellDate],[communityOfGroupID]=O.[communityOfGroupID],[communityOfTravelID]=O.[communityOfTravelID],[guideID]=O.[guideID],[driverID]=O.[driverID]
FROM @tmpSell D RIGHT JOIN
(SELECT MAX([orderCode]) AS [orderCode],MAX([sellAdminID]) AS [sellAdminID],MAX([SalesID]) AS [SalesID],MAX([companyID]) AS [companyID],MAX([sellDate]) AS [sellDate],MAX([communityOfGroupID]) AS [communityOfGroupID],MAX([communityOfTravelID]) AS [communityOfTravelID],MAX([guideID]) AS [guideID],MAX([driverID]) AS [driverID] FROM @tmpSellOrderXML) O ON 1=1
--正式将销售明细插入销售明细表
INSERT INTO [FWD_SellOrderDetail]
([orderID],[productID],[normID],[sellCount],[sellPrice],[sellDiscount],[sellOldPrice],[singlePrice],[supplierID],[purchaseDetailID],[ppdetailID],[groupPrice],[travelPrice],[guidePrice],[driverPrice],[companyPrice],[orderCode],[sellAdminID],[SalesID],[companyID],[sellDate],[communityOfGroupID],[communityOfTravelID],[guideID],[driverID],[DetailtotalPrice])
SELECT * FROM @tmpSell
--开始减库存表里的数据
UPDATE FWD_ProductStock SET stockCount = stockCount-SD.sellCount FROM FWD_ProductStock PS INNER JOIN (SELECT normID,companyID,max(orderID) AS orderID,sum(sellCount) AS sellCount FROM @tmpSell GROUP BY normID,companyID ) SD ON PS.normID=SD.normID AND PS.companyID=SD.companyID
--开始更新进货详细数据
UPDATE FWD_PurchaseDetail SET sellCount=sellCount+SD.sellNum FROM FWD_PurchaseDetail PD INNER JOIN (SELECT orderID,sellCount AS sellNum,purchaseDetailID FROM @tmpSell) SD ON PD.ID=SD.purchaseDetailID
IF @error=0
BEGIN
SET @ISERR=0
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @ISERR=2--保存失败并回滚
ROLLBACK
END
--SELECT * FROM @tmpSell
--SELECT * FROM @PurchaseDetail
END

上一篇：我用static变量造了一颗定时炸弹

下一篇：ASP.NET设置用户权限

沃德软件技术资讯频道

门户类网站设计的细节问题及经验分享

进销存系统中的一个先入先出或随机出库的存储过程