/****** Object:  StoredProcedure [dbo].[AccBalanceCostCenterReport]    Script Date: 24/4/2024 06:05:28 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[AccBalanceCostCenterReport]
(
	@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,
	@ByProject bit=0,
	@WhereStr as nvarchar(1000)='1=1'
)
AS
	SET NOCOUNT ON 
	DECLARE @SQL as nvarchar(2000)

	SET @StartDate=cast(@StartDate as date)
	SET @EndDate=cast(@EndDate as date)

	if @ByProject=0
	BEGIN
		SET @SQL='SELECT Cast(0 as bit) as Selected, 
				COALESCE(SUM(Docs.Debit),0) AS Debit, 
				COALESCE(SUM(Docs.Credit),0) AS Credit, 
				CASE WHEN SUM(Docs.Debit) > SUM(Docs.Credit) THEN SUM(Docs.Debit) - SUM(Docs.Credit) ELSE 0 END AS RemDebit, 
				CASE WHEN SUM(Docs.Credit) > SUM(Docs.Debit) THEN SUM(Docs.Credit) - SUM(Docs.Debit) ELSE 0 END AS RemCredit, 
				COALESCE(RTRIM(Acc.Code),Str(Docs.CostCenter)) as AccCode, COALESCE(Acc.Title,Str(Docs.CostCenter)) as Title, Docs.CostCenter AS AccID
			FROM	dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
					dbo.AccDetail as Acc ON Docs.CostCenter = Acc.ID
			WHERE '+@WhereStr+' AND not Acc.Title is null and ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
				(NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
				Docs.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
				Docs.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
				Docs.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
				Docs.Flags <> 3)
				GROUP BY Docs.CostCenter, RTRIM(Acc.Code), Acc.Title'
		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(Docs.Debit),0) AS Debit, 
				COALESCE(SUM(Docs.Credit),0) AS Credit, 
				CASE WHEN SUM(Docs.Debit) > SUM(Docs.Credit) THEN SUM(Docs.Debit) - SUM(Docs.Credit) ELSE 0 END AS RemDebit, 
				CASE WHEN SUM(Docs.Credit) > SUM(Docs.Debit) THEN SUM(Docs.Credit) - SUM(Docs.Debit) ELSE 0 END AS RemCredit, 
				COALESCE(RTRIM(Acc.Code),Str(Docs.CostCenter)) as AccCode, COALESCE(Acc.Title,Str(Docs.CostCenter)) as Description, Docs.CostCenter AS AccID
			FROM	dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
					dbo.AccDetail as Acc ON Docs.CostCenter = Acc.ID
			WHERE '+@WhereStr+' AND not Acc.Title is null and  ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
				(NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
				Docs.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
				Docs.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
				Docs.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
				Docs.Flags <> 3)
				GROUP BY Docs.CostCenter, RTRIM(Acc.Code), Acc.Title) as t'
		exec(@SQL)
	END
	ELSE
	BEGIN
		SET @SQL='SELECT Cast(0 as bit) as Selected, 
				COALESCE(SUM(Docs.Debit),0) AS Debit, 
				COALESCE(SUM(Docs.Credit),0) AS Credit, 
				CASE WHEN SUM(Docs.Debit) > SUM(Docs.Credit) THEN SUM(Docs.Debit) - SUM(Docs.Credit) ELSE 0 END AS RemDebit, 
				CASE WHEN SUM(Docs.Credit) > SUM(Docs.Debit) THEN SUM(Docs.Credit) - SUM(Docs.Debit) ELSE 0 END AS RemCredit, 
				COALESCE(RTRIM(Acc.Code),Str(Docs.Project)) as AccCode, COALESCE(Acc.Title,Str(Docs.Project)) as Title, Docs.Project AS AccID
			FROM	dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
					dbo.AccDetail as Acc ON Docs.Project = Acc.ID
			WHERE '+@WhereStr+' AND not Acc.Title is null and ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.DocDate BETWEEN ''' + CAST(@StartDate as varchar(20)) + ''' AND ''' + CAST(@EndDate as varchar(20)) + ''') OR
				(NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.Number BETWEEN ' + CAST(@StartNum as varchar(20)) + ' AND ' + CAST(@EndNum as varchar(20)) + ')) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
				Docs.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
				Docs.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
				Docs.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
				Docs.Flags <> 3)
				GROUP BY Docs.Project, RTRIM(Acc.Code), Acc.Title'
		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(Docs.Debit),0) AS Debit, 
				COALESCE(SUM(Docs.Credit),0) AS Credit, 
				CASE WHEN SUM(Docs.Debit) > SUM(Docs.Credit) THEN SUM(Docs.Debit) - SUM(Docs.Credit) ELSE 0 END AS RemDebit, 
				CASE WHEN SUM(Docs.Credit) > SUM(Docs.Debit) THEN SUM(Docs.Credit) - SUM(Docs.Debit) ELSE 0 END AS RemCredit, 
				COALESCE(RTRIM(Acc.Code),Str(Docs.Project)) as AccCode, COALESCE(Acc.Title,Str(Docs.Project)) as Title, Docs.Project AS AccID
			FROM	dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
					dbo.AccDetail as Acc ON Docs.Project = Acc.ID
			WHERE '+@WhereStr+' AND not Acc.Title is null and  ((' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.DocDate < ''' + CAST(@StartDate as varchar(20)) + ''' ) OR
				(NOT ' + CAST(@ByDate as CHAR(1)) + ' = 1 AND Docs.Number < ' + CAST(@StartNum as varchar(20)) + ' )) AND (' + CAST(@UsePermanent as char(1)) + ' = 1 OR
				Docs.IsPermanent = 0) AND (' + CAST(@UseNote as char(1)) + ' = 1 OR
				Docs.Flags <> 0) AND (' + CAST(@UseInitial as char(1)) + ' = 1 OR
				Docs.Flags <> 2) AND (' + CAST(@UseFinal as char(1)) + ' = 1 OR
				Docs.Flags <> 3)
				GROUP BY Docs.Project, RTRIM(Acc.Code), Acc.Title) as t'
		exec(@SQL)
	END		
RETURN 
