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


ALTER PROCEDURE [dbo].[AccBalanceCostCenterReportInitial]
(
	@UsePermanent as bit=1,
	@ByDate as 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 Docs.Flags = 2
			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 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 < ''' + 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 Docs.Flags = 2
			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 Docs.Flags = 2
			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 Docs.Flags = 2
			GROUP BY Docs.Project, RTRIM(Acc.Code), Acc.Title ) as t'

		exec(@SQL)
	END
RETURN 
