沃德软件技术资讯频道

传递网站建设与软件开发资讯

C#\JAVA\IOS\Android等技术研究

Ajax+DIV+CSS技术技巧/HTML5研究

视觉设计、软件体验度与流行趋势

沃德软件团队经验与心得体会分享

门户网站设计有很多种。最为国人熟知的是像迅雷看看、新浪、腾讯网那种信息类门户。现在也有博客门户等。...[详细]
沃德团队

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

发布:昆明沃德软件 发布时间:2012-10-26  浏览次数:3282
更多

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

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

  1. USE [FWD_ProductSaleStore]  
  2. GO  
  3. /****** 对象:  StoredProcedure [dbo].[proc_FWD_ProductSell]    脚本日期: 05/30/2012 11:23:29 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. /*   
  9. ****************************************************************************************************   
  10. *** Author: 沃德软件(http://www.ynorg.cn)  
  11. *** Create date: 2012-05-30  
  12. *** Description:产品销售、提成计算、先进先出与随机成本计算  
  13. ****************************************************************************************************   
  14. */   
  15. ALTER PROCEDURE [dbo].[proc_FWD_ProductSell]  
  16.     -- 销售产品列表和总单的xml  
  17.     @ISRandom BIT,--0是随机销售1为先进先出(针对进货记录扣减)  
  18.     @SellOrderxml  XML,--销售主表的XML  
  19.     @SellOrderDetailxml  XML,--销售详细的XML  
  20.     @ISERR INT OUTPUT,--(0成功,1库存不足,2保存失败)  
  21.     @ERRINFO VARCHAR(1000) OUTPUT--库存不足时返回哪些产品库存不足  
  22. WITH ENCRYPTION AS  
  23. BEGIN  
  24.     SET NOCOUNT ON;  
  25.     DECLARE @Pointer INT  
  26.     DECLARE @ERRCOUNT INT  
  27.     --申明销售表  
  28.     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])  
  29.     --申明临时销售详细表  
  30.     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))  
  31.     --申明正式销售详细表(已拆分)  
  32.     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))  
  33.     --申明进货详细表  
  34.     DECLARE @PurchaseDetail TABLE(ID INT,orderID INT,productID INT,normID INT,singlePrice DECIMAL(18,2),SuplusNum DECIMAL(18,2),supplierID INT)  
  35.     --将主销售记录插入临时表  
  36.     EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@SellOrderxml  
  37.     INSERT @tmpSellOrderXML   
  38.       SELECT * FROM OPENXML (@Pointer,'/DocumentElement/Table1',2)   
  39.       WITH   
  40.       (   
  41.             [orderCode] [varchar](50),  
  42.             [sellAdminID] [int],  
  43.             [SalesID] [int],  
  44.             [companyID] [int],  
  45.             [sellDate] [datetime],  
  46.             [createDate] [datetime],  
  47.             [communityOfGroupID] [int],  
  48.             [communityOfTravelID] [int],  
  49.             [guideID] [int],  
  50.             [driverID] [int],  
  51.             [ppID] [int],  
  52.             [spID] [int],  
  53.             [totalOldPrice] [decimal](18, 2),  
  54.             [totalPrice] [decimal](18, 2),  
  55.             [creditcard] [decimal](18, 2),  
  56.             [cash] [decimal](18, 2),  
  57.             [transfer] [decimal](18, 2),  
  58.             [orderStatus] [int],  
  59.             [RoyaltyStatus] [int],  
  60.             [IsBackOrder] [bit],  
  61.             [payTotalPrice] [decimal](18, 2),  
  62.             [commissionPriceOfGroup] [decimal](18, 2),  
  63.             [commissionPriceOfGiveGroup] [decimal](18, 2),  
  64.             [commissionPriceOfTravel] [decimal](18, 2),  
  65.             [commissionPriceOfGiveTravel] [decimal](18, 2),  
  66.             [commissionPriceOfGuide] [decimal](18, 2),  
  67.             [commissionPriceOfGiveGuide] [decimal](18, 2),  
  68.             [commissionPriceOfDriver] [decimal](18, 2),  
  69.             [commissionPriceOfGiveDriver] [decimal](18, 2),  
  70.             [commissionPriceOfCompany] [decimal](18, 2),  
  71.             [commissionPriceOfGiveCompany] [decimal](18, 2),  
  72.             [remark] [text]  
  73.       )  
  74.     EXEC sp_xml_removedocument @Pointer  
  75.     --将销售详细插入临时表  
  76.     EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@SellOrderDetailxml   
  77.     INSERT @tmpXML   
  78.       SELECT * FROM OPENXML (@Pointer,'/DocumentElement/Table1',2)   
  79.       WITH   
  80.       (   
  81.             ProductNormID INT,   
  82.             ProductbarCode VARCHAR(100),   
  83.             ProductID INT,   
  84.             ProductName VARCHAR(100),   
  85.             ProductNorm VARCHAR(100),  
  86.             ProductNum DECIMAL(18,2),  
  87.             ProductPrice DECIMAL(18,4),  
  88.             ProductDiscount DECIMAL(18,2),  
  89.             ProudctOldPrice DECIMAL(18,2),  
  90.             ProductUnit VARCHAR(100),  
  91.             SinglePrice DECIMAL(18,2),  
  92.             SupplierID INT,  
  93.             [Type] VARCHAR,  
  94.             PurchaseDetailID INT,  
  95.             CompanyID INT,  
  96.             DetailtotalPrice DECIMAL(18,4),  
  97.             Stock DECIMAL(18,2)  
  98.       )  
  99.     EXEC sp_xml_removedocument @Pointer  
  100.     --查询是否有库存不足的商品  
  101.     SELECT @ERRCOUNT=COUNT(1) FROM  VR_FWD_SellPDetail_Validity V  
  102.     INNER JOIN @tmpXML X   
  103.     ON  V.productID=X.productID AND V.normID=X.ProductNormID AND V.companyID=X.CompanyID   
  104.     WHERE  V.stockCount<X.ProductNum  
  105.     --如果有则返回库存不足的信息  
  106.     IF @ERRCOUNT>0  
  107.         BEGIN  
  108.             SET @ISERR=1  
  109.             SET @ERRINFO=''  
  110.             SELECT @ERRINFO=@ERRINFO+','+V.productName+'/'+V.normName+'-库存'+CAST(V.stockCount AS VARCHAR(100)) FROM  VR_FWD_SellPDetail_Validity V  
  111.             INNER JOIN @tmpXML X   
  112.             ON  V.productID=X.productID AND V.normID=X.ProductNormID AND V.companyID=X.CompanyID   
  113.             WHERE  V.stockCount<X.ProductNum  
  114.             SET @ERRINFO=RIGHT(@ERRINFO,len(@ERRINFO)-1)  
  115.             RETURN  
  116.         END  
  117.     --根据销售详细查询进货详细入表变量  
  118.     INSERT @PurchaseDetail  
  119.     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   
  120.     LEFT JOIN FWD_PurchaseOrder PO on PD.orderID= PO.ID  
  121.     INNER JOIN @tmpXML X ON PD. normID=X.ProductNormID AND PD.productID=X.ProductID  
  122.     INNER JOIN FWD_COMPANY CP ON X.CompanyID=CP.ID AND PO.purchaseCompanyID=CP.ROOTID  
  123.     WHERE PO.STATUS=1 and PD.sellStatus=0 ORDER BY normID  
  124.     --开始根据进货详细对销售详细进行先入先出或者随机销售进行拆分核算成本  
  125.     DECLARE @ProductNormID INT --规格ID  
  126.     DECLARE @ProductID INT--产品ID  
  127.     DECLARE @ProductNum DECIMAL(18,2)--销售数量  
  128.     DECLARE @ProductPrice DECIMAL(18,4)--销售价格(原价*折扣)  
  129.     DECLARE @ProductDiscount DECIMAL(18,2)--销售折扣  
  130.     DECLARE @ProudctOldPrice DECIMAL(18,2)--销售原价  
  131.     DECLARE @CompanyID INT--所属公司  
  132.     DECLARE @SinglePrice DECIMAL(18,2)--进货价格  
  133.     DECLARE @SupplierID INT--供应商ID  
  134.     DECLARE @PurchaseDetailID INT--进货详细ID  
  135.     DECLARE @DetailtotalPrice DECIMAL(18,4)--小计  
  136.     DECLARE @orderID INT--进货主ID  
  137.     DECLARE @SuplusNum DECIMAL(18,2)--进货详细的数量  
  138.     --开始循环销售表  
  139.     DECLARE sell_cur CURSOR FOR  
  140.         SELECT ProductNormID,ProductID,ProductNum,ProductPrice,ProductDiscount,ProudctOldPrice,CompanyID,DetailtotalPrice FROM @tmpXML  
  141.     OPEN sell_cur  
  142.     FETCH NEXT FROM sell_cur INTO @ProductNormID,@ProductID,@ProductNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@CompanyID,@DetailtotalPrice  
  143.     WHILE @@FETCH_STATUS = 0  
  144.     BEGIN  
  145.     --开始循环供应表  
  146.         IF @ISRandom=0--如果是随机出库  
  147.         BEGIN  
  148.         DECLARE suppy_cur CURSOR FOR  
  149.             SELECT ID ,orderID,singlePrice,SuplusNum ,supplierID  FROM @PurchaseDetail  WHERE normid=@ProductNormID ORDER BY NEWID()  
  150.         END  
  151.         ELSE--先进先出  
  152.         BEGIN  
  153.         DECLARE suppy_cur CURSOR FOR  
  154.             SELECT ID ,orderID,singlePrice,SuplusNum ,supplierID  FROM @PurchaseDetail  WHERE normid=@ProductNormID ORDER BY ID ASC  
  155.         END  
  156.         OPEN suppy_cur  
  157.         FETCH NEXT FROM suppy_cur INTO @PurchaseDetailID,@orderID,@SinglePrice,@SuplusNum,@SupplierID  
  158.         WHILE @@FETCH_STATUS = 0 AND @ProductNum>0--供应记录循环完和销售数量分拆完  
  159.         BEGIN  
  160.             IF @ProductNum<=@SuplusNum --如果销售数量小于等于供应数量将销售数量写入  
  161.             BEGIN  
  162.                 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  
  163.                 SET @ProductNum=@ProductNum-@SuplusNum  
  164.             END  
  165.             ELSE--如果销售数量大于供应数量将供应数量替换原来的销售数量并进行下一轮循环  
  166.             BEGIN  
  167.                 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  
  168.                 SET @ProductNum=@ProductNum-@SuplusNum  
  169.             END  
  170.             FETCH NEXT FROM suppy_cur INTO @PurchaseDetailID,@orderID,@SinglePrice,@SuplusNum,@SupplierID  
  171.         END  
  172.         CLOSE suppy_cur         
  173.         DEALLOCATE suppy_cur  
  174.         FETCH NEXT FROM sell_cur INTO @ProductNormID,@ProductID,@ProductNum,@ProductPrice,@ProductDiscount,@ProudctOldPrice,@CompanyID,@DetailtotalPrice  
  175.     END  
  176.     CLOSE sell_cur         
  177.     DEALLOCATE sell_cur  
  178.     --批量计算提成  
  179.     UPDATE @tmpSell SET   
  180.     [groupPrice]=CASE WHEN O.communityOfGroupID>0 THEN V.communityOfGroupPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfGroupPercent ELSE 0 END,  
  181.     [travelPrice]=CASE WHEN O.communityOfTravelID>0 THEN V.communityOfRRPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfRRPercent ELSE 0 END,  
  182.     [guidePrice]=CASE WHEN O.guideID>0 THEN V.guidePrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.guidePercent ELSE 0 END,  
  183.     [driverPrice]=CASE WHEN O.driverID>0 THEN V.driverPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.driverPercent ELSE 0 END,   
  184.     [companyPrice]=ISNULL(V.communityOfPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfPercent,0)   
  185.     FROM  @tmpSell S LEFT JOIN VR_FWD_PromotionalProtocolDetail_Validity V ON S.[normID]=V.normid  
  186.     LEFT JOIN @tmpSellOrderXML O ON 1=1  
  187.     --更新临时销售主表的提成数据  
  188.     UPDATE @tmpSellOrderXML SET  
  189.         [commissionPriceOfGroup]=ISNULL(D.TotalgroupPrice,0),  
  190.         [commissionPriceOfTravel]=ISNULL(D.TotaltravelPrice,0),  
  191.         [commissionPriceOfGuide]=ISNULL(D.TotalguidePrice,0),  
  192.         [commissionPriceOfDriver]=ISNULL(D.TotaldriverPrice,0),  
  193.         [commissionPriceOfCompany]=ISNULL(D.TotalcompanyPrice,0),  
  194.         [ppID]=0,--提成协议ID  
  195.         [spID]=0--销售协议ID  
  196.         FROM @tmpSellOrderXML O  
  197.         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  
  198.     BEGIN TRANSACTION  
  199.     DECLARE @error INT  
  200.     SET @error=0   
  201.     --正式将销售主记录插入表  
  202.     INSERT INTO [FWD_SellOrder]  
  203.     ([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])  
  204.     SELECT * FROM @tmpSellOrderXML  
  205.     SET @error=@@error+@error  
  206.     --更新销售临时表里的数据  
  207.     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]  
  208.     FROM @tmpSell D RIGHT JOIN  
  209.     (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  
  210.     --正式将销售明细插入销售明细表  
  211.     INSERT INTO [FWD_SellOrderDetail]  
  212.     ([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])  
  213.     SELECT * FROM @tmpSell  
  214.     --开始减库存表里的数据  
  215.     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  
  216.     --开始更新进货详细数据  
  217.     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  
  218.     IF @error=0   
  219.     BEGIN  
  220.         SET @ISERR=0  
  221.         COMMIT TRANSACTION  
  222.     END  
  223.     ELSE  
  224.     BEGIN  
  225.         SET @ISERR=2--保存失败并回滚  
  226.         ROLLBACK  
  227.     END   
  228.     --SELECT * FROM @tmpSell  
  229.     --SELECT * FROM @PurchaseDetail  
  230. END