----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

先看看