set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[FunTarazSoodKol] ( @__StartDate char(8)='00/00/00', @__EndDate char(8)='99/99/99' ) RETURNS @a table (temp int ,RowNo int,AmountIn money ,AmountOut money ,Remain money ,Title nvarchar(1000)) AS BEGIN declare @FirstRemainKala money,@SndRemainKala money,@AllBuy money, @AllSell Money,@AllCost money,@Sood money,@AllDarAmad money,@AllFromBuy money,@AllFromSell Money /* by avrage fee 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 @FirstRemainKala =SUM(FirstRemainAmount) ,@SndRemainKala= SUM(EndRemainAmount) FROM (SELECT StockTbl.GoodSyscode, CAST(CASE WHEN lastStart.lastfee IS NULL THEN 0 ELSE lastStart.lastfee * remain_start.remain END AS money) AS FirstRemainAmount, CAST(CASE WHEN last_End.lastfee IS NULL THEN 0 ELSE last_End.lastfee * 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.FunGoodlastFeeByDate(@__StartDate) AS lastStart ON StockTbl.GoodSyscode = lastStart.GoodSysCode LEFT OUTER JOIN dbo.FunGoodlastFeeByDate(@__EndDate) AS last_End ON StockTbl.GoodSyscode = last_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 ) SELECT @AllfromBuy= -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 = 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 ) SELECT @AllfromSell= 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 = 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) SELECT @AllDarAmad=SUM(DocDetailTbl.AmmountIN - DocDetailTbl.AmmountOUT) FROM DocDetailTbl INNER JOIN CodeMNGDWNTbl ON DocDetailTbl.Fk_AccountSyscode = CodeMNGDWNTbl.AccountSysCode INNER JOIN CodeMNGUPTbl ON CodeMNGDWNTbl.FK_AccountSysCode0 = CodeMNGUPTbl.AccountSysCode0 WHERE (CodeMNGUPTbl.Kind = 2) AND (CodeMNGUPTbl.AccountSysCode0 <> 2 AND CodeMNGUPTbl.AccountSysCode0 <> 3 AND CodeMNGUPTbl.AccountSysCode0 <> 14 AND CodeMNGUPTbl.AccountSysCode0 <> 15 AND CodeMNGUPTbl.AccountSysCode0 <> 16) if @AllDarAmad is null set @AllDarAmad=0 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 @AllfromBuy is null set @AllfromBuy=0 if @AllfromSell is null set @AllfromSell=0 if @allCost is null set @allCost=0 --set @sood = -@FirstRemainKala-@AllBuy+@SndRemainKala+@AllSell-@allCost+@AllDarAmad+@AllfromBuy-@Allfromsell+@AllDarAmad INSERT INTO @a values (0,1,@FirstRemainKala,0,0,N'موجودی اول دوره') INSERT INTO @a values (0,2,@AllBuy,0,0,N'خرید طی دوره') INSERT INTO @a values (0,3,0,@AllfromBuy,0,N'برگشت از خرید') INSERT INTO @a values (0,4,0,0,0,N'کالای آماده به فروش') -- ردیف 5 در تراز استفاده می شود INSERT INTO @a values (0,5,0,@SndRemainKala,0,N'موجودی پایان دوره') INSERT INTO @a values (0,6,0,0,0,N'بهای تمام شده کالای فروش رفته') INSERT INTO @a values (0,7,0,@AllSell,0,N'فروش طی دوره') INSERT INTO @a values (0,8,@AllfromSell,0,0,N'برگشت از فروش') INSERT INTO @a values (0,9,@AllDarAmad,0,0,N'درآمد ها') INSERT INTO @a values (0,10,0,0,0,N'سود ناویژه') INSERT INTO @a values (0,11,@allCost,0,0,N'هزینه ها') INSERT INTO @a values (0,12,0,0,0,N'سود ویژه') RETURN END