--------
营销型网站建设费用
------- 进销存系统软件中的一个先入先出或任意出库的储存全过程
公布：昆明沃德手机软件 公布時间：2012⑽-26 访问次数：4870
这是大家沃德手机软件企业(p>
本手机软件开发设计关键思路是在实际操作中先以DATATABLE临时性储存条码扫描仪的商品与数量，自然在这个情况下第一步要分辨库存的数量够不足，随后将DATATABLE里的纪录变换成xml递交给储存全过程，由于是联网式多消费收银点实际操作，储存全过程在真实实际操作数据信息库时需要再度分辨库存的数量，假如堵塞过则回到库存不够的商品给顾客端，假如根据则开展下一步的实际操作。由于同一样产品将会会有多个供货商，多个批次供货，每一个批次的进货价钱将会不一样，以便精准成本费，不可以选用均值进货价的优化算法，因此务必又要和进货表关系起来出库而不可以只是对库存表数量开展增减。
/****** 目标: StoredProcedure [dbo].[proc_FWD_ProductSell] 脚本制作时间: 05/30/2012 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
****************************************************************************************************
*** Author: 沃德手机软件(
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&panyID=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&panyID=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, ,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, ,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&munityOfGroupID 0 THEN&munityOfGroupPrice*S.[sellCount]+(S.[sellPrice]-V.leftBasePrice)*S.[sellCount]*V.communityOfGroupPercent ELSE 0 END,
[travelPrice]=CASE WHEN&munityOfTravelID 0 THEN&munityOfRRPrice*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]=munityOfPrice*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(panyPrice,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&panyPrice 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 凡科抠图 INNER JOIN (SELECT&panyID,max(orderID) AS orderID,sum(sellCount) AS sellCount FROM @tmpSell GROUP BY&panyID ) SD ON 凡科抠图.normID=SD.normID AND&panyID
--刚开始升级进货详尽数据信息
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
---------营销型网站建设费用
------------
