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