set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go










ALTER PROCEDURE [dbo].[STPGridList_Sood]
	@__StartDate char(8)='00/00/00',
	@__EndDate char(8)='99/99/99',
	@FormCaption nvarchar(1000) output
AS
if not (@__StartDate='00/00/00' and @__EndDate='99/99/99') set @FormCaption='<' + @__EndDate + '~' + @__StartDate +'>'
declare @FirstRemainKala money,@SndRemainKala money,@AllBuy money,@AllSell Money,@AllCost   money,@Sood money


SELECT     @FirstRemainKala =SUM(FirstRemainAmount)  ,@SndRemainKala= SUM(EndRemainAmount) 
FROM         (SELECT     StockTbl.GoodSyscode, CAST(CASE WHEN AvgStart.avgfee IS NULL THEN 0 ELSE AvgStart.avgfee * remain_start.remain END AS money) 
                      AS FirstRemainAmount, CAST(CASE WHEN Avg_End.avgfee IS NULL THEN 0 ELSE Avg_End.avgfee * remain_enddate.remain END AS money) 
                      AS EndRemainAmount
FROM         dbo.FunGoodRemainByDate(@__StartDate) AS Remain_Start RIGHT OUTER JOIN
                      StockTbl ON Remain_Start.GoodSysCode = StockTbl.GoodSyscode LEFT OUTER JOIN
                      dbo.FunGoodAvgFeeByDate(@__StartDate) AS AVGStart ON StockTbl.GoodSyscode = AVGStart.GoodSysCode LEFT OUTER JOIN
                      dbo.FunGoodAvgFeeByDate(@__EndDate) AS AVG_End ON StockTbl.GoodSyscode = AVG_End.GoodSysCode LEFT OUTER JOIN
                      dbo.FunGoodRemainByDate(@__EndDate) AS Remain_EndDate ON StockTbl.GoodSyscode = Remain_EndDate.GoodSysCode) 
                      AS derivedtbl_1



SELECT    @AllBuy= SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT)  
FROM         CodeMNGDWNTbl LEFT OUTER JOIN
                      VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode
WHERE     (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 2 OR
                      CodeMNGDWNTbl.FK_AccountSysCode0 = 16)

SELECT    @AllSell= -SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT)  
FROM         CodeMNGDWNTbl LEFT OUTER JOIN
                      VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode
WHERE     (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 14 OR
                      CodeMNGDWNTbl.FK_AccountSysCode0 = 15)


SELECT    @allCost= SUM(VWDocumentDetail_DocHeader.AmmountIN - VWDocumentDetail_DocHeader.AmmountOUT)  
FROM         CodeMNGDWNTbl LEFT OUTER JOIN
                      VWDocumentDetail_DocHeader ON CodeMNGDWNTbl.AccountSysCode = VWDocumentDetail_DocHeader.Fk_AccountSyscode
WHERE     (VWDocumentDetail_DocHeader.DocDate BETWEEN @__StartDate AND @__Enddate) AND (CodeMNGDWNTbl.FK_AccountSysCode0 = 3)

declare @a table (FirstRemain money ,SeccondRemain money ,AllBuy money ,AllSell money ,AllCost  money , Sood  money )
if @FirstRemainKala is null set @FirstRemainKala=0
if @SndRemainKala is null set @SndRemainKala=0
if @AllBuy is null set @AllBuy=0
if @AllSell is null set @AllSell=0
if @allCost is null set @allCost=0
set @sood = -@FirstRemainKala-@AllBuy+@SndRemainKala+@AllSell-@allCost

INSERT INTO @a
(FirstRemain   ,SeccondRemain   ,AllBuy   ,AllSell   ,AllCost    , Sood    )
VALUES     ( @FirstRemainKala ,@SndRemainKala,@AllBuy,@AllSell,@allCost,@sood)

select * from @a