
/****** Object:  StoredProcedure [dbo].[AccBalanceReport]    Script Date: 24/4/2024 05:44:54 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER     PROCEDURE [dbo].[AccBalanceReport]
	(
		 @UseInitial bit=1,
		 @UseFinal bit=1,
		 @UsePermanent as bit=1,
		 @UseNote  bit=1,
		 @ByDate bit=1,
		 @StartNum  as bigint=0,
		 @EndNum  as bigint=0,
		 @StartDate as datetime=getdate,
		 @EndDate as datetime=getdate,
		 @TotalID  bigint=0,
		 @ControlID  bigint=0,
		 @DetailID  bigint=0,
		 @ParentCode  as NVarChar(30),
		@CostCenterFilter NVarChar(1000)='',
		@ProjectFilter NVarChar(1000)='',
		@SellFactorSellBuyType NVarChar(1000)=''
	)
AS
	SET NOCOUNT ON 
	DECLARE @SQL as nvarchar(4000)
	
	SET @StartDate=cast(@StartDate as date)
	SET @EndDate=cast(@EndDate as date)

	DECLARE @OrderMultiplyer as nvarchar(10)
	select @OrderMultiplyer=TheValue from AppSetting where Name like 'Company.SellFactor.UnitMultiply'
	--print @OrderMultiplyer
	IF @TotalID =0
	BEGIN
	SET @SQL='SELECT  Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
	    > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
	    CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
	    - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.TotalID AS AccID, RTRIM(dbo.AccTotal.Code) as AccCode, dbo.AccTotal.Title
		FROM  dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN
              dbo.AccTotal ON dbo.AccDocumentGoods.TotalID = dbo.AccTotal.ID
		WHERE dbo.AccTotal.Title is not null AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
            (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
            dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
	        dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
            dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
			dbo.AccDocumentGoods.Flags <> 3)
			and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
			and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.TotalID, RTRIM(dbo.AccTotal.Code), dbo.AccTotal.Title '

		if Len(@SellFactorSellBuyType)> 2
		BEGIN
			SET @SQL = 'SELECT  Cast(0 as bit) as Selected, COALESCE(SUM(Debit),0) AS Debit, COALESCE(SUM(Credit),0) AS Credit, CASE WHEN SUM(Debit) > SUM(Credit) THEN SUM(Debit) - SUM(Credit) ELSE 0 END AS RemDebit, CASE WHEN SUM(Credit) > SUM(Debit) THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS RemCredit, AccID, AccCode, Title from('
			+ @SQL + '
			UNION ALL
			
			SELECT Cast(0 as bit) as Selected, Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Debit, 0 as Credit, 
			Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemDebit, 0 as RemCredit, O.BuyerTotalID as AccID, 
			RTRIM(T.Code) as AccCode, T.Title
			FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccTotal as T ON O.BuyerTotalID  = T.ID
			Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  AND ControlD<>1 and ('+REPLACE(@SellFactorSellBuyType,'BuyType','SellType')+')
			Group by O.ID, O.BuyerTotalID, RTRIM(T.Code), T.Title 
			
			UNION ALL
			
				SELECT Cast(0 as bit) as Selected, 0 as Debit, min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Credit, 
				 0 as RemDebit, Min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemCredit, O.SellerTotalID as AccID, 
				RTRIM(T.Code) as AccCode, T.Title
				FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccTotal as T ON O.SellerTotalID  = T.ID
				Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  and ControlD<>1 AND ('+@SellFactorSellBuyType+')
				Group by O.ID, O.SellerTotalID, RTRIM(T.Code), T.Title 
			) t
			where AccCode is not null
			group by AccID, AccCode, Title'
		END
print @SQL
exec(@SQL)
	SET @SQL=	'Select COALESCE(Sum(Debit),0) as Debit, COALESCE(Sum(Credit),0) as Credit, COALESCE(Sum(RemCredit),0) as RemCredit, COALESCE(Sum(RemDebit),0) as RemDebit from
		(SELECT  Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		                      > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		                      CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		                      - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.TotalID AS AccID, RTRIM(dbo.AccTotal.Code) as AccCode, dbo.AccTotal.Title
		FROM         dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccTotal ON dbo.AccDocumentGoods.TotalID = dbo.AccTotal.ID
		WHERE  dbo.AccTotal.Title is not null AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
		                      (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.TotalID, RTRIM(dbo.AccTotal.Code), dbo.AccTotal.Title ) as t'
		print @SQL
		exec(@SQL)
	END
	ELSE
	IF @ControlID =0
	BEGIN
	--‪-‫
		SET @SQL='SELECT  Cast(0 as bit) as Selected,COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		    > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		    CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		    - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.ControlID AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccControl.Code) as AccCode, dbo.AccControl.Title
		FROM  dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN

		      dbo.AccControl ON dbo.AccDocumentGoods.ControlID = dbo.AccControl.ID
		WHERE dbo.AccControl.Title is not null AND  dbo.AccDocumentGoods.TotalID = ' + CAST(@TotalID as NvarChar(15)) + ' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
		    (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.ControlID, dbo.AccControl.Code, dbo.AccControl.Title '
		
		if Len(@SellFactorSellBuyType)> 2
		BEGIN
			SET @SQL = 'SELECT  Cast(0 as bit) as Selected, COALESCE(SUM(Debit),0) AS Debit, COALESCE(SUM(Credit),0) AS Credit, CASE WHEN SUM(Debit) > SUM(Credit) THEN SUM(Debit) - SUM(Credit) ELSE 0 END AS RemDebit, CASE WHEN SUM(Credit) > SUM(Debit) THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS RemCredit, AccID, AccCode, Title from('
			+ @SQL + '
			UNION ALL

			SELECT Cast(0 as bit) as Selected, Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Debit, 0 as Credit, 
			Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemDebit, 0 as RemCredit, O.BuyerControlID as AccID, 
			''' + @ParentCode + '''+N''-''+RTRIM(C.Code) as AccCode, C.Title
			FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccControl as C ON O.BuyerControlID  = C.ID
			Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  AND ControlD<>1 and BuyerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and ('+REPLACE(@SellFactorSellBuyType,'BuyType','SellType')+')
			Group by O.ID, O.BuyerControlID, RTRIM(C.Code), C.Title 

			UNION ALL

				SELECT Cast(0 as bit) as Selected, 0 as Debit, min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Credit, 
				 0 as RemDebit, Min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemCredit, O.SellerControlID as AccID, 
				''' + @ParentCode + '''+N''-''+RTRIM(C.Code) as AccCode, C.Title
				FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccControl as C ON O.SellerControlID  = C.ID
				Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  and ControlD<>1 AND SellerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and ('+@SellFactorSellBuyType+')
				Group by O.ID, O.SellerControlID, RTRIM(C.Code), C.Title 
			) t
			where AccCode is not null
			group by AccID, AccCode, Title'
		END
		print @SQL
		Exec(@SQL)
		SET @SQL='Select COALESCE(Sum(Debit),0) as Debit, COALESCE(Sum(Credit),0) as Credit, COALESCE(Sum(RemCredit),0) as RemCredit, COALESCE(Sum(RemDebit),0) as RemDebit from
		(SELECT  Cast(0 as bit) as Selected,COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
              > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
              CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
               - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.ControlID AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccControl.Code) as AccCode, dbo.AccControl.Title
		FROM         dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN
		      dbo.AccControl ON dbo.AccDocumentGoods.ControlID = dbo.AccControl.ID
		WHERE     dbo.AccControl.Title is not null AND dbo.AccDocumentGoods.TotalID = '+ CAST(@TotalID as NvarChar(15)) +' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
		    (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.ControlID, dbo.AccControl.Code, dbo.AccControl.Title ) as t'
		
print @SQL
exec(@SQL)
	END
	ELSE
	IF @DetailID =0
	BEGIN
		SET @SQL='SELECT   Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		    > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		    CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		    - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.DetailID1 AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM  dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN
            dbo.AccDetail ON dbo.AccDocumentGoods.DetailID1 = dbo.AccDetail.ID
		WHERE dbo.AccDetail.Title is not null AND dbo.AccDocumentGoods.TotalID = '+ CAST(@TotalID as NvarChar(15)) +' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(15)) + ' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND 
	        dbo.AccDocumentGoods.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
	         (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		    dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.DetailID1, dbo.AccDetail.Code, dbo.AccDetail.Title '

		if Len(@SellFactorSellBuyType)> 2
		BEGIN
			SET @SQL = 'SELECT  Cast(0 as bit) as Selected, COALESCE(SUM(Debit),0) AS Debit, COALESCE(SUM(Credit),0) AS Credit, CASE WHEN SUM(Debit) > SUM(Credit) THEN SUM(Debit) - SUM(Credit) ELSE 0 END AS RemDebit, CASE WHEN SUM(Credit) > SUM(Debit) THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS RemCredit, AccID, AccCode, Title from('
			+ @SQL + '
			UNION ALL
			SELECT Cast(0 as bit) as Selected, Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Debit, 0 as Credit, 
			Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemDebit, 0 as RemCredit, O.BuyerDetailID1 as AccID, 
			''' + @ParentCode + '''+N''-''+RTRIM(D.Code) as AccCode, D.Title
			FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccDetail as D ON O.BuyerDetailID1  = D.ID
			Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  AND ControlD<>1 and BuyerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and BuyerControlID = ' + CAST(@ControlID as NvarChar(15)) + ' and ('+REPLACE(@SellFactorSellBuyType,'BuyType','SellType')+')
			GROUP BY O.ID, O.BuyerDetailID1, RTRIM(D.Code), D.Title 
			UNION ALL
				SELECT Cast(0 as bit) as Selected, 0 as Debit, min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Credit, 
				 0 as RemDebit, Min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemCredit, O.SellerDetailID1 as AccID, 
				''' + @ParentCode + '''+N''-''+RTRIM(D.Code) as AccCode, D.Title
				FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccDetail as D ON O.SellerDetailID1  = D.ID
				Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  and ControlD<>1 AND SellerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and SellerControlID = ' + CAST(@ControlID as NvarChar(15)) + ' and ('+@SellFactorSellBuyType+')
				Group by O.ID, O.SellerDetailID1, RTRIM(D.Code), D.Title 
			) t
			where AccCode is not null
			group by AccID, AccCode, Title'
		END
print @SQL
		Exec(@SQL)
		SET @SQL='Select COALESCE(Sum(Debit),0) as Debit, COALESCE(Sum(Credit),0) as Credit, COALESCE(Sum(RemCredit),0) as RemCredit, COALESCE(Sum(RemDebit),0) as RemDebit from
		(SELECT   Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		 > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		                      CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		                      - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.DetailID1 AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM         dbo.AccDocumentGoods WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccDetail ON dbo.AccDocumentGoods.DetailID1 = dbo.AccDetail.ID
		WHERE     dbo.AccDetail.Title is not null AND dbo.AccDocumentGoods.TotalID = '+ CAST(@TotalID as NvarChar(15)) +' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(15)) + ' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND 
		                      dbo.AccDocumentGoods.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
		         (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.DetailID1, dbo.AccDetail.Code, dbo.AccDetail.Title ) as t'
--print @SQL
print @SQL
exec(@SQL)
	END
	ELSE
	BEGIN
		SET @SQL='SELECT   Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		                      > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		                      CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		                      - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.DetailID2 AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM         dbo.AccDocumentGoods LEFT OUTER JOIN
		                      dbo.AccDetail ON dbo.AccDocumentGoods.DetailID2 = dbo.AccDetail.ID
		WHERE     dbo.AccDetail.Title is not null AND dbo.AccDocumentGoods.TotalID = '+ CAST(@TotalID as NvarChar(15)) +' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(15)) + ' AND 
		                      dbo.AccDocumentGoods.DetailID1 = ' + CAST(@DetailID as NvarChar(20)) + ' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
		                      (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.DetailID2,  dbo.AccDetail.Code, dbo.AccDetail.Title '

		if Len(@SellFactorSellBuyType)> 2
		BEGIN
			SET @SQL = 'SELECT Cast(0 as bit) as Selected, COALESCE(SUM(Debit),0) AS Debit, COALESCE(SUM(Credit),0) AS Credit, CASE WHEN SUM(Debit) > SUM(Credit) THEN SUM(Debit) - SUM(Credit) ELSE 0 END AS RemDebit, CASE WHEN SUM(Credit) > SUM(Debit) THEN SUM(Credit) - SUM(Debit) ELSE 0 END AS RemCredit, AccID, AccCode, Title from('
			+ @SQL + '
			UNION ALL
			SELECT Cast(0 as bit) as Selected, Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Debit, 0 as Credit, 
			Min(O.SellPrice) * (Min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemDebit, 0 as RemCredit, O.BuyerDetailID2 as AccID, 
			''' + @ParentCode + '''+N''-''+RTRIM(D.Code) as AccCode, D.Title
			FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccDetail as D ON O.BuyerDetailID2  = D.ID
			Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  AND ControlD<>1 and BuyerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and BuyerControlID = ' + CAST(@ControlID as NvarChar(15)) + ' and BuyerDetailID1 = ' + CAST(@DetailID as NvarChar(15)) + ' and ('+REPLACE(@SellFactorSellBuyType,'BuyType','SellType')+')
			Group by O.ID, O.BuyerDetailID2, RTRIM(D.Code), D.Title 
			UNION ALL
				SELECT Cast(0 as bit) as Selected, 0 as Debit, min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as Credit, 
				 0 as RemDebit, Min(O.BuyPrice) * (min(O.Request) * '+@OrderMultiplyer+' - Sum(O.TrucksWeight)) as RemCredit, O.SellerDetailID2 as AccID, 
				''' + @ParentCode + '''+N''-''+RTRIM(D.Code) as AccCode, D.Title
				FROM ViwAccSellFactorTrucks as O WITH(NOLOCK)  LEFT OUTER JOIN dbo.AccDetail as D ON O.SellerDetailID2  = D.ID
				Where NOT (TrucksDocItemID>0) AND (ControlA=1 or ControlB=1)  and ControlD<>1 AND SellerTotalID = ' + CAST(@TotalID as NvarChar(15)) + ' and SellerControlID = ' + CAST(@ControlID as NvarChar(15)) + ' and SellerDetailID1 = ' + CAST(@DetailID as NvarChar(15)) + ' and ('+@SellFactorSellBuyType+')
				Group by O.ID, O.SellerDetailID2, RTRIM(D.Code), D.Title 
			) t
			where AccCode is not null
			group by AccID, AccCode, Title'
		END
		--print @SQL
print @SQL
Exec(@SQL)
		SET @SQL='Select COALESCE(Sum(Debit),0) as Debit, COALESCE(Sum(Credit),0) as Credit, COALESCE(Sum(RemCredit),0) as RemCredit, COALESCE(Sum(RemDebit),0) as RemDebit from
		(SELECT   Cast(0 as bit) as Selected, COALESCE(SUM(dbo.AccDocumentGoods.Debit),0) AS Debit, COALESCE(SUM(dbo.AccDocumentGoods.Credit),0) AS Credit, CASE WHEN SUM(dbo.AccDocumentGoods.Debit) 
		                      > SUM(dbo.AccDocumentGoods.Credit) THEN SUM(dbo.AccDocumentGoods.Debit) - SUM(dbo.AccDocumentGoods.Credit) ELSE 0 END AS RemDebit, 
		                      CASE WHEN SUM(dbo.AccDocumentGoods.Credit) > SUM(dbo.AccDocumentGoods.Debit) THEN SUM(dbo.AccDocumentGoods.Credit) 
		                      - SUM(dbo.AccDocumentGoods.Debit) ELSE 0 END AS RemCredit, dbo.AccDocumentGoods.DetailID2 AS AccID, ''' + @ParentCode + '''+N''-''+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM         dbo.AccDocumentGoods LEFT OUTER JOIN
		                      dbo.AccDetail ON dbo.AccDocumentGoods.DetailID2 = dbo.AccDetail.ID
		WHERE     dbo.AccDetail.Title is not null AND dbo.AccDocumentGoods.TotalID = '+ CAST(@TotalID as NvarChar(15)) +' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(15)) + ' AND 
		                      dbo.AccDocumentGoods.DetailID1 = ' + CAST(@DetailID as NvarChar(20)) + ' AND ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
		         (NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND dbo.AccDocumentGoods.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
		                      dbo.AccDocumentGoods.Flags <> 3)
		    and (dbo.AccDocumentGoods.CostCenter is NULL OR dbo.AccDocumentGoods.' + @CostCenterFilter + ')
		    and (dbo.AccDocumentGoods.Project is NULL OR dbo.AccDocumentGoods.' + @ProjectFilter + ')
		GROUP BY dbo.AccDocumentGoods.DetailID2,  dbo.AccDetail.Code, dbo.AccDetail.Title ) as t'
print @SQL
exec(@SQL)
	END
	
	RETURN 