22,209
社区成员
发帖
与我相关
我的任务
分享
@CustomerOrdersNo VARCHAR(30)='%',
@CustomerNo VARCHAR(15)='%',
@ProductNo VARCHAR(30)='%',
@ProductClass VARCHAR(5)='%',
@OrdersClass VARCHAR(5)='%',
@DeliveryClass VARCHAR(1)='0',
@ClosedClass VARCHAR(1)='0',
@OrdresStartDate varchar(10)='2013-03-01',
@OrdresEndDate varchar(10)='2013-03-31',
@ProjectNo VARCHAR(30)='%',
@BusinessWwithASingle VARCHAR(12)='%',
@Clerk VARCHAR(30)='%',
@Region VARCHAR(30)='%',
@DeliveryStartDate varchar(10)='2003-01-01',
@DeliveryEndDate varchar(10)='2013-01-01',
@ReceiptClass VARCHAR(2)='0'
这一堆的参数就表示,有这么一堆和查询条件,并不是每一个都会启用的。这个得让前台应用程序去进行选择项的查询。
存储过程这里,不能去逐个判断哪个查询条件是否启用而单独写 WHERE 吧。
DECLARE @I INT,@J INT,@GDBH VARCHAR(100),@CP VARCHAR(30),@DH VARCHAR(30),@ProductID VARCHAR(10),@ProductionQty INT,@StorageQty INT
SET @I=0
SET @J=1
UPDATE #TEMP2 SET Fnum=@I,@I=@I+1
WHILE @J<=@I
BEGIN
SELECT @CP=ProductNo,@DH=CustomerOrdersNo,@ProductID=ProductID FROM #TEMP2 WHERE Fnum=@J
SET @GDBH=''
SELECT @GDBH=@GDBH+';' +TableNumber
FROM Produce_ProductionEngineeringSingle_master A INNER JOIN
Produce_ProductionEngineeringSingle_D B ON A.XH=B.MasterTableNo
WHERE ProductID=@ProductID AND ProductNo=@CP AND CustomerOrdersNo=@DH
IF Isnull(@GDBH,'')<>''
BEGIN
SET @GDBH=Substring(@GDBH,2,Len(@GDBH)-1)
SELECT @ProductionQty=SUM(Isnull(B.ProductionQty,0))
FROM Produce_ProductionEngineeringSingle_master A INNER JOIN
Produce_ProductionEngineeringSingle_D B ON A.XH=B.MasterTableNo
WHERE ProductID=@ProductID AND ProductNo=@CP AND CustomerOrdersNo=@DH
SELECT @StorageQty=SUM(Isnull(C.StorageQty,0))
FROM Produce_ProductionEngineeringSingle_master A INNER JOIN
Produce_ProductionEngineeringSingle_D B ON A.XH=B.MasterTableNo INNER JOIN
KC_CPRKD_D C ON A.TableNumber=C.ProjectNo AND B.ProductNo=C.ProductNo AND C.CustomerOrdersNo=B.CustomerOrdersNo
WHERE B.ProductID=@ProductID AND B.ProductNo=@CP AND B.CustomerOrdersNo=@DH
UPDATE #TEMP2 SET ProjectNo=@GDBH,ProductionQty=@ProductionQty,StorageQty=@StorageQty
WHERE Fnum=@J
END
SET @J=@J+1
END
END
BEGIN
DECLARE @SCS INT
SELECT DDCP=B.CustomerOrdersNo+B.ProductNo+B.ProductID,ProductionQty
INTO #TEMP_CustomerOrdersNo
FROM Produce_ProductionEngineeringSingle_master A INNER JOIN
Produce_ProductionEngineeringSingle_D B ON A.XH=B.MasterTableNo
WHERE A.TableNumber=@ProjectNo
IF @DeliveryClass='0' OR @DeliveryClass='%'
BEGIN
SELECT A.CustomerOrdersNo,/*A.CustomerOrdersNo2,*/A.ProductID,A.ProductNo,YSSL=SUM(Isnull(A.SHSL,0)),FSSL=SUM(isnull(A.FSSL,0)),
SHuoRQ=(SELECT MAX(SHuoRQ) FROM Orders_SHDan_master C
INNER JOIN Orders_SHDan_D D ON C.XH=D.MasterTableNo
WHERE D.CustomerOrdersNo=A.CustomerOrdersNo AND D.ProductNo=A.ProductNo AND D.ProductID=A.ProductID
)
INTO #TEMP10
FROM Orders_SHDan_D A INNER JOIN
Orders_SHDan_master B ON A.MasterTableNo=B.XH
WHERE B.SHF=1
AND (A.CustomerOrdersNo+A.ProductNo+A.ProductID) IN (SELECT DDCP FROM #TEMP_CustomerOrdersNo)
AND A.DeliveryClass=0
GROUP BY A.CustomerOrdersNo,A.ProductID,A.ProductNo--,A.CustomerOrdersNo2
INSERT INTO #TEMP2
SELECT Fnum=0,ReturnNo='%',A.CustomerOrdersNo,A.CustomerOrdersNo2,A.CustomerNo,CustomerName=E.ZWMC,B.ProductID,B.ProductNo,D.ProductName,D.ProductSize,D.CustomerLH,A.Clerk,D.ProductHH,B.OrderAccount,B.FSSL,YSSL=isnull(C.YSSL,0),
WSSL=ROUND(B.OrderAccount-ISNULL(C.YSSL,0),0),YSBP=C.FSSL,CKSL=H.CPSL,B.ProductPrice,A.DDRQ,JHRQ=Isnull(BGRQ,B.JHRQ),C.SHuoRQ,F.ClassName,G.TypeName,
DeliveryClass='订单送货',B.ClosedYN,B.MasterTableNo,ProjectNo=@ProjectNo,A.CurrenyUnit,B.ZYSM,E.GDGDY,
ProductionQty=(SELECT SUM(ProductionQty) FROM #TEMP_CustomerOrdersNo ),
StorageQty=(SELECT SUM(StorageQty) FROM KC_CPRKD_D WHERE ProjectNo=@ProjectNo AND ProductNo=B.ProductNo AND CustomerOrdersNo=A.CustomerOrdersNo),A.HL
FROM Orders_KHOrders_master A INNER JOIN
Orders_KHOrders_D B ON A.XH=B.MasterTableNo LEFT JOIN
#TEMP10 C ON A.CustomerOrdersNo=C.CustomerOrdersNo AND B.ProductNo=C.ProductNo AND B.ProductID=C.ProductID LEFT JOIN
BasicData_ProductDataTable_master D ON B.ProductNo=D.ProductNo LEFT JOIN
BasicData_KHZLB_master E ON A.CustomerNo=E.CustomerNo LEFT JOIN
BasicData_ProductClassBH F ON D.ProductClass=F.LBBH LEFT JOIN
BasicData_OrdersClassBH G ON G.LXBH=A.OrdersClass LEFT JOIN
V_KC_GetCPDQKCB H ON H.ProductNo=B.ProductNo
WHERE A.SHF=1
AND Isnull(A.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND A.CustomerOrdersNo+B.ProductNo+B.ProductID IN (SELECT DDCP FROM #TEMP_CustomerOrdersNo)
-- AND B.ProductNo =@CP
END
IF @DeliveryClass='1' OR @DeliveryClass='%'
BEGIN
SELECT A.ReturnNo,A.CustomerOrdersNo,/*A.CustomerOrdersNo2,*/A.ProductID,A.ProductNo,YSSL=SUM(isnull(A.SHSL,0)),FSSL=SUM(isnull(A.FSSL,0)),
SHuoRQ=(SELECT MAX(SHuoRQ) FROM Orders_SHDan_master C
INNER JOIN Orders_SHDan_D D ON C.XH=D.MasterTableNo
WHERE D.CustomerOrdersNo=A.CustomerOrdersNo AND D.ProductNo=A.ProductNo AND D.ProductID=A.ProductID AND D.ReturnNo=A.ReturnNo
)
INTO #TEMP30
FROM Orders_SHDan_D A INNER JOIN
Orders_SHDan_master B ON A.MasterTableNo=B.XH
WHERE B.SHF=1 AND A.CustomerOrdersNo+A.ProductNo+A.ProductID IN (SELECT DDCP FROM #TEMP_CustomerOrdersNo)
-- AND A.ProductNo=@CP
AND A.DeliveryClass=1
GROUP BY A.ReturnNo,A.CustomerOrdersNo,A.ProductID,A.ProductNo--,A.CustomerOrdersNo2
INSERT INTO #TEMP2
SELECT Fnum=0,ReturnNo=A.TableNumber,A.CustomerOrdersNo,A.CustomerOrdersNo2,A.CustomerNo,CustomerName=E.ZWMC,B.ProductID,B.ProductNo,D.ProductName,D.ProductSize,D.CustomerLH,Clerk='',D.ProductHH,B.THSL AS OrderAccount,FSSL=0,YSSL=isnull(C.YSSL,0),
WSSL=ROUND(B.THSL-ISNULL(C.YSSL,0),0),YSBP=C.FSSL,CKSL=H.CPSL,B.ProductPrice,A.THRQ AS DDRQ,B.BJRQ AS JHRQ,C.SHuoRQ,F.ClassName,G.TypeName,
DeliveryClass='退货送货',B.ClosedYN,B.MasterTableNo,ProjectNo=@ProjectNo,A.CurrenyUnit,ZYSM=B.Remark,E.GDGDY,
ProductionQty=(SELECT SUM(ProductionQty) FROM #TEMP_CustomerOrdersNo ),
StorageQty=(SELECT SUM(StorageQty) FROM KC_CPRKD_D WHERE ProjectNo=@ProjectNo AND ProductNo=B.ProductNo ),A.HL
FROM Orders_THDan_master A INNER JOIN
Orders_THDan_D B ON A.XH=B.MasterTableNo LEFT JOIN
#TEMP30 C ON A.TableNumber=C.ReturnNo AND A.CustomerOrdersNo=C.CustomerOrdersNo AND B.ProductNo=C.ProductNo AND B.ProductID=C.ProductID LEFT JOIN
BasicData_ProductDataTable_master D ON B.ProductNo=D.ProductNo LEFT JOIN
BasicData_KHZLB_master E ON A.CustomerNo=E.CustomerNo LEFT JOIN
BasicData_ProductClassBH F ON D.ProductClass=F.LBBH LEFT JOIN
BasicData_OrdersClassBH G ON G.LXBH=A.OrdersClass LEFT JOIN
V_KC_GetCPDQKCB H ON H.ProductNo=B.ProductNo
WHERE A.SHF=1
AND A.BDF=1
AND Isnull(A.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND A.TableNumber+B.ProductNo+B.ProductID IN (SELECT DDCP FROM #TEMP_CustomerOrdersNo)
END
END
SELECT A.*,B.CurrencyName
FROM #TEMP2 A LEFT JOIN
BasicData_HBBH B ON A.CurrenyUnit=B.HBBH
ORDER BY JHRQ,CustomerNo
RETURN
GO
CREATE PROCEDURE [dbo].[SP_QryOrderDeliverySchedule]
@CustomerOrdersNo VARCHAR(30)='%',
@CustomerNo VARCHAR(15)='%',
@ProductNo VARCHAR(30)='%',
@ProductClass VARCHAR(5)='%',
@OrdersClass VARCHAR(5)='%',
@DeliveryClass VARCHAR(1)='0',
@ClosedClass VARCHAR(1)='0',
@OrdresStartDate varchar(10)='2013-03-01',
@OrdresEndDate varchar(10)='2013-03-31',
@ProjectNo VARCHAR(30)='%',
@BusinessWwithASingle VARCHAR(12)='%',
@Clerk VARCHAR(30)='%',
@Region VARCHAR(30)='%',
@DeliveryStartDate varchar(10)='2003-01-01',
@DeliveryEndDate varchar(10)='2013-01-01',
@ReceiptClass VARCHAR(2)='0'
AS
--取到订单送货进度查询表
CREATE TABLE #TEMP2
(Fnum INT ,
ReturnNo VARCHAR(30),
CustomerOrdersNo VARCHAR(30),
CustomerOrdersNo2 VARCHAR(50),
CustomerNo VARCHAR(15),
CustomerName VARCHAR(50),
ProductID VARCHAR(50),
ProductNo VARCHAR(30),
ProductName VARCHAR(200),
ProductSize VARCHAR(100),
CustomerLH VARCHAR(30),
Clerk VARCHAR(30),
ProductHH VARCHAR(30),
OrderAccount FLOAT,
FSSL FLOAT,
YSSL FLOAT,
WSSL FLOAT,
YSBP FLOAT,
KCSL FLOAT,
ProductPrice FLOAT,
DDRQ SMALLDATETIME,
JHRQ SMALLDATETIME,
SHuoRQ SMALLDATETIME,
ClassName VARCHAR(50),
TypeName VARCHAR(50),
DeliveryClass VARCHAR(50),
ClosedYN INT,
MasterTableNo INT,
ProjectNo VARCHAR(100),
CurrenyUnit VARCHAR(10),
ZYSM VARCHAR(200),
GDGDY VARCHAR(12),
ProductionQty INT,
StorageQty INT,
HL float
)
IF @ProjectNo='%'
BEGIN
IF @DeliveryClass='0' OR @DeliveryClass='%'
BEGIN
SELECT A.CustomerOrdersNo,/*A.CustomerOrdersNo2,*/A.ProductID,A.ProductNo,YSSL=SUM(Isnull(A.SHSL,0)),FSSL=SUM(isnull(A.FSSL,0)),SHuoRQ=MAX(B.SHuoRQ)
-- SHuoRQ=(SELECT MAX(SHuoRQ) FROM Orders_SHDan_master C
-- INNER JOIN Orders_SHDan_D D ON C.XH=D.MasterTableNo
-- WHERE D.CustomerOrdersNo=A.CustomerOrdersNo AND D.ProductNo=A.ProductNo AND D.ProductID=A.ProductID
-- )
INTO #TEMP1
FROM Orders_SHDan_D A INNER JOIN
Orders_SHDan_master B ON A.MasterTableNo=B.XH INNER JOIN
Orders_KHOrders_master C ON B.CustomerNo=C.CustomerNo AND A.CustomerOrdersNo=C.CustomerOrdersNo
WHERE B.SHF=1 AND B.CustomerNo LIKE '%'+@CustomerNo+'%'
AND A.CustomerOrdersNo LIKE '%'+@CustomerOrdersNo+'%'
AND A.ProductNo LIKE '%'+@ProductNo+'%'
AND A.DeliveryClass=0
AND Isnull(C.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND CONVERT(CHAR(10),C.DDRQ,121) BETWEEN @OrdresStartDate AND @OrdresEndDate
GROUP BY A.CustomerOrdersNo,A.ProductID,A.ProductNo--,A.CustomerOrdersNo2
INSERT INTO #TEMP2
SELECT Fnum=0,ReturnNo='%',A.CustomerOrdersNo,A.CustomerOrdersNo2,A.CustomerNo,CustomerName=E.ZWMC,B.ProductID,B.ProductNo,D.ProductName,D.ProductSize,D.CustomerLH,A.Clerk,D.ProductHH,B.OrderAccount,B.FSSL,YSSL=isnull(C.YSSL,0),
WSSL=ROUND(B.OrderAccount-ISNULL(C.YSSL,0),0),YSBP=C.FSSL,CKSL=H.CPSL,B.ProductPrice,A.DDRQ,JHRQ=Isnull(BGRQ,B.JHRQ),C.SHuoRQ,F.ClassName,G.TypeName,
DeliveryClass='订单送货',B.ClosedYN,B.MasterTableNo,ProjectNo='',A.CurrenyUnit,B.ZYSM,A.BusinessWwithASingle,ProductionQty=0,StorageQty=0,A.HL
FROM Orders_KHOrders_master A INNER JOIN
Orders_KHOrders_D B ON A.XH=B.MasterTableNo LEFT JOIN
#TEMP1 C ON A.CustomerOrdersNo=C.CustomerOrdersNo AND B.ProductNo=C.ProductNo AND B.ProductID=C.ProductID LEFT JOIN
BasicData_ProductDataTable_master D ON B.ProductNo=D.ProductNo LEFT JOIN
BasicData_KHZLB_master E ON A.CustomerNo=E.CustomerNo LEFT JOIN
BasicData_ProductClassBH F ON D.ProductClass=F.LBBH LEFT JOIN
BasicData_OrdersClassBH G ON G.LXBH=A.OrdersClass LEFT JOIN
V_KC_GetCPDQKCB H ON H.ProductNo=B.ProductNo
WHERE A.SHF=1 AND A.CustomerNo LIKE @CustomerNo
AND A.CustomerOrdersNo LIKE '%'+@CustomerOrdersNo+'%'
AND B.ProductNo LIKE '%'+@ProductNo+'%'
AND D.ProductClass LIKE '%'+@ProductClass+'%'
AND A.OrdersClass LIKE '%'+@OrdersClass+'%'
AND Isnull(A.BusinessWwithASingle,'%') LIKE '%'+@BusinessWwithASingle+'%'
AND (Isnull(A.Clerk,'%') LIKE '%'+@Clerk+'%' OR ISNULL(A.KDR,'%') LIKE '%'+@Clerk+'%' OR Isnull(A.BusinessWwithASingle,'%') LIKE '%'+@Clerk+'%')
AND Isnull(E.Region,'%') LIKE '%'+@Region+'%'
AND Isnull(A.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND CAST(Isnull(B.ClosedYN,0) AS VARCHAR) LIKE '%'+@ClosedClass+'%'
AND CONVERT(CHAR(10),A.DDRQ,121) BETWEEN @OrdresStartDate AND @OrdresEndDate
AND CONVERT(CHAR(10),B.JHRQ,121) BETWEEN @DeliveryStartDate AND @DeliveryEndDate
END
IF @DeliveryClass='1' OR @DeliveryClass='%'
BEGIN
SELECT A.ReturnNo,A.CustomerOrdersNo,/*A.CustomerOrdersNo2,*/A.ProductID,A.ProductNo,YSSL=SUM(isnull(A.SHSL,0)),FSSL=SUM(isnull(A.FSSL,0)),SHuoRQ=MAX(B.SHuoRQ)
-- SHuoRQ=(SELECT MAX(SHuoRQ) FROM Orders_SHDan_master C
-- INNER JOIN Orders_SHDan_D D ON C.XH=D.MasterTableNo
-- WHERE D.ProductNo=A.ProductNo AND D.ProductID=A.ProductID AND D.ReturnNo=A.ReturnNo
-- )
INTO #TEMP3
FROM Orders_SHDan_D A INNER JOIN
Orders_SHDan_master B ON A.MasterTableNo=B.XH INNER JOIN
Orders_THDan_master C ON B.CustomerNo=C.CustomerNo AND A.ReturnNo=C.TableNumber
WHERE B.SHF=1 AND B.CustomerNo LIKE '%'+@CustomerNo+'%'
AND Isnull(A.CustomerOrdersNo,'') LIKE '%'+@CustomerOrdersNo+'%'
AND A.ProductNo LIKE '%'+@ProductNo+'%'
AND A.DeliveryClass=1
AND Isnull(C.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND CONVERT(CHAR(10),C.THRQ,121) BETWEEN @OrdresStartDate AND @OrdresEndDate
GROUP BY A.ReturnNo,A.CustomerOrdersNo,A.ProductID,A.ProductNo--,A.CustomerOrdersNo2
INSERT INTO #TEMP2
SELECT Fnum=0,ReturnNo=A.TableNumber,A.CustomerOrdersNo,A.CustomerOrdersNo2,A.CustomerNo,CustomerName=E.ZWMC,B.ProductID,B.ProductNo,D.ProductName,D.ProductSize,D.CustomerLH,Clerk='',D.ProductHH,B.THSL AS OrderAccount,FSSL=0,YSSL=isnull(C.YSSL,0),
WSSL=ROUND(B.THSL-ISNULL(C.YSSL,0),0),YSBP=C.FSSL,CKSL=H.CPSL,B.ProductPrice,A.THRQ AS DDRQ,B.BJRQ AS JHRQ,C.SHuoRQ,F.ClassName,G.TypeName,
DeliveryClass='退货送货',B.ClosedYN,B.MasterTableNo,ProjectNo='',A.CurrenyUnit,ZYSM=B.Remark,E.BusinessWwithASingle,ProductionQty=0,StorageQty=0,A.HL
FROM Orders_THDan_master A INNER JOIN
Orders_THDan_D B ON A.XH=B.MasterTableNo LEFT JOIN
#TEMP3 C ON A.TableNumber=C.ReturnNo AND B.ProductNo=C.ProductNo AND B.ProductID=C.ProductID LEFT JOIN
BasicData_ProductDataTable_master D ON B.ProductNo=D.ProductNo LEFT JOIN
BasicData_KHZLB_master E ON A.CustomerNo=E.CustomerNo LEFT JOIN
BasicData_ProductClassBH F ON D.ProductClass=F.LBBH LEFT JOIN
BasicData_OrdersClassBH G ON G.LXBH=A.OrdersClass LEFT JOIN
V_KC_GetCPDQKCB H ON H.ProductNo=B.ProductNo
WHERE A.SHF=1 AND A.BDF=1 AND A.CustomerNo LIKE @CustomerNo
AND Isnull(A.CustomerOrdersNo,'') LIKE '%'+@CustomerOrdersNo+'%'
AND B.ProductNo LIKE '%'+@ProductNo+'%'
AND D.ProductClass LIKE '%'+@ProductClass+'%'
AND A.OrdersClass LIKE '%'+@OrdersClass+'%'
AND Isnull(E.BusinessWwithASingle,'%') LIKE '%'+@BusinessWwithASingle+'%'
AND Isnull(E.Region,'%') LIKE '%'+@Region+'%'
AND Isnull(Clerk,'%') LIKE '%'+@Clerk+'%'
AND Isnull(A.ReceiptClass,0) LIKE '%'+@ReceiptClass+'%'
AND CAST(Isnull(B.ClosedYN,0) AS VARCHAR) LIKE '%'+@ClosedClass+'%'
AND CONVERT(CHAR(10),A.THRQ,121) BETWEEN @OrdresStartDate AND @OrdresEndDate
AND CONVERT(CHAR(10),Isnull(B.BJRQ,getdate()),121) BETWEEN @DeliveryStartDate AND @DeliveryEndDate
END