存储过程影响服务器cpu，该优化哪里
----2009-03-25 针对开开，增加查看远程商店的记录CREATE PROCEDURE PRO_ReadGatheringGuideLine(
@vDate VARCHAR(10),
@vChlCode VARCHAR(20),
@vUserCode VARCHAR(20)
)
AS
DECLARE @iShopSum INT, @iHasTradeSum INT, @iNoTradeSum INT,
@iSaleQuantity INT, @iUntreadQuantity INT, @fSaleMoney FLOAT,
@vBestShop VARCHAR(100), @fBestMoney FLOAT, @fBestRate FLOAT,
@iBillSum INT, @iCancelSum INT, @iPutupSum INT, @fChange FLOAT,
@fCashMoney FLOAT, @fBankMoney FLOAT, @fOtherMoney FLOAT,
@iKHRight INT
declare
@vKHRight VARCHAR(5000)
----2090806添加客户权限的控制
select @iKHRight=isnull(bAllCustomers,0) from UserParams where vUserCode = @vUserCode
if isnull(@iKHRight, 0) = 1
begin
SET @iShopSum=ISNULL((SELECT COUNT(*) FROM KEHU WHERE QDDM=@vChlCode AND XZDM='2' AND ISNULL(BYZD4, '0')='2'), 0)
SELECT DISTINCT vShop INTO #TEMP FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
SET @iHasTradeSum=(SELECT COUNT(vShop) FROM #TEMP) DROP TABLE #TEMP
SET @iNoTradeSum=ISNULL(@iShopSum, 0)-ISNULL(@iHasTradeSum, 0)
SET @iSaleQuantity=ISNULL((SELECT SUM(CASE WHEN fQuantity>=0 THEN fQuantity ELSE 0 END) FROM SG_Gatherings WHERE vMBillID IN (
SELECT vMBillID FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0)), 0)
SET @iUntreadQuantity=ISNULL((SELECT SUM(CASE WHEN fQuantity<0 THEN ABS(fQuantity) ELSE 0 END) FROM SG_Gatherings WHERE vMBillID IN (
SELECT vMBillID FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0)), 0)
SET @fSaleMoney=ISNULL((SELECT SUM(fRealMoney) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0), 0)
SELECT vShop, SUM(fRealMoney) AS fRealMoney INTO #TABLE FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0 GROUP BY vShop
IF @@ROWCOUNT>0 SET @fBestMoney=(SELECT TOP 1 fRealMoney FROM #TABLE ORDER BY fRealMoney DESC) ELSE SET @fBestMoney=0
IF @fBestMoney=0 SET @fBestRate=0 ELSE SET @fBestRate=@fBestMoney/@fSaleMoney
SET @vBestShop=ISNULL((SELECT TOP 1 vShop FROM #TABLE WHERE fRealMoney=@fBestMoney), '无')
SET @vBestShop=@vBestShop + ' ' + (SELECT TOP 1 KHMC FROM KEHU WHERE KHDM=@vBestShop) DROP TABLE #TABLE
SET @iBillSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0)
SET @iCancelSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=1)
SET @iPutupSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=1)
SET @fChange=ISNULL((SELECT SUM(fChange) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0), 0)
SET @fCashMoney=ISNULL((SELECT SUM(A.fMoney) FROM SG_PayMethod A, SG_Gathering B WHERE A.vMBillID=B.vMBillID AND
A.vShop=B.vShop AND A.vPFCode=B.vPFCode AND CONVERT(VARCHAR(10), B.dtDate, 20)=@vDate AND B.bCancel=0 AND B.bPutup=0 AND A.vBalCode='000'), 0)
SET @fCashMoney=@fCashMoney-@fChange
SET @fBankMoney=ISNULL((SELECT SUM(A.fMoney) FROM SG_PayMethod A, SG_Gathering B WHERE A.vMBillID=B.vMBillID AND
A.vShop=B.vShop AND A.vPFCode=B.vPFCode AND CONVERT(VARCHAR(10), B.dtDate, 20)=@vDate AND B.bCancel=0 AND B.bPutup=0 AND
A.vBalCode IN (SELECT JSDM FROM POSJS WHERE XZDM='2')), 0)
SET @fOtherMoney=@fSaleMoney-@fCashMoney-@fBankMoney
end
else
begin
SET @iShopSum=ISNULL((SELECT COUNT(*) FROM KEHU WHERE QDDM=@vChlCode AND XZDM='2' AND ((ISNULL(BYZD4, '0')='2') OR
(ISNULL(BYZD4,'0')='1' AND ISNULL(BYZD7,'0')='1'))
AND KEHU.KHDM IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)), 0)
SELECT DISTINCT vShop INTO #TEMP1 FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)
SET @iHasTradeSum=(SELECT COUNT(vShop) FROM #TEMP1) DROP TABLE #TEMP1
SET @iNoTradeSum=ISNULL(@iShopSum, 0)-ISNULL(@iHasTradeSum, 0)
SET @iSaleQuantity=ISNULL((SELECT SUM(CASE WHEN fQuantity>=0 THEN fQuantity ELSE 0 END) FROM SG_Gatherings WHERE vMBillID IN (
SELECT vMBillID FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode))), 0)
SET @iUntreadQuantity=ISNULL((SELECT SUM(CASE WHEN fQuantity<0 THEN ABS(fQuantity) ELSE 0 END) FROM SG_Gatherings WHERE vMBillID IN (
SELECT vMBillID FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode))), 0)
SET @fSaleMoney=ISNULL((SELECT SUM(fRealMoney) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)), 0)
SELECT vShop, SUM(fRealMoney) AS fRealMoney INTO #TABLE1 FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)
GROUP BY vShop
IF @@ROWCOUNT>0 SET @fBestMoney=(SELECT TOP 1 fRealMoney FROM #TABLE1 ORDER BY fRealMoney DESC) ELSE SET @fBestMoney=0
IF @fBestMoney=0 SET @fBestRate=0 ELSE SET @fBestRate=@fBestMoney/@fSaleMoney
SET @vBestShop=ISNULL((SELECT TOP 1 vShop FROM #TABLE1 WHERE fRealMoney=@fBestMoney), '无')
SET @vBestShop=@vBestShop + ' ' + (SELECT TOP 1 KHMC FROM KEHU WHERE KHDM=@vBestShop
AND KEHU.KHDM IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)) DROP TABLE #TABLE1
SET @iBillSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode))
SET @iCancelSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=1
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode))
SET @iPutupSum=(SELECT COUNT(vMBillID) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=1
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode))
SET @fChange=ISNULL((SELECT SUM(fChange) FROM SG_Gathering WHERE CONVERT(VARCHAR(10), dtDate, 20)=@vDate AND bCancel=0 AND bPutup=0
AND SG_Gathering.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)), 0)
SET @fCashMoney=ISNULL((SELECT SUM(A.fMoney) FROM SG_PayMethod A, SG_Gathering B WHERE A.vMBillID=B.vMBillID AND
A.vShop=B.vShop AND A.vPFCode=B.vPFCode AND CONVERT(VARCHAR(10), B.dtDate, 20)=@vDate AND B.bCancel=0 AND B.bPutup=0 AND A.vBalCode='000'
AND B.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode)), 0)
SET @fCashMoney=@fCashMoney-@fChange
SET @fBankMoney=ISNULL((SELECT SUM(A.fMoney) FROM SG_PayMethod A, SG_Gathering B WHERE A.vMBillID=B.vMBillID AND
A.vShop=B.vShop AND A.vPFCode=B.vPFCode AND CONVERT(VARCHAR(10), B.dtDate, 20)=@vDate AND B.bCancel=0 AND B.bPutup=0 AND
B.vShop IN (select UserCustomers.KHDM from UserCustomers, KEHU where UserCustomers.KHDM = KEHU.KHDM AND KEHU.XZDM='2' and vUserCode=@vUserCode) AND
A.vBalCode IN (SELECT JSDM FROM POSJS WHERE XZDM='2')), 0)
SET @fOtherMoney=@fSaleMoney-@fCashMoney-@fBankMoney
end
SELECT @iShopSum AS iShopSum, @iHasTradeSum AS iHasTradeSum, @iNoTradeSum AS iNoTradeSum,
@iSaleQuantity AS iSaleQuantity, @iUntreadQuantity AS iUntreadQuantity, @fSaleMoney AS fSaleMoney,
@vBestShop AS vBestShop, @fBestMoney AS fBestMoney, @fBestRate AS fBestRate,
@iBillSum AS iBillSum, @iCancelSum AS iCancelSum, @iPutupSum AS iPutupSum,
@fCashMoney AS fCashMoney, @fBankMoney AS fBankMoney, @fOtherMoney AS fOtherMoney
GO
