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



ALTER PROCEDURE [dbo].[AccBalanceUpReport]
(
	@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,
	@GroupBy as nChar(10)='TotalID',
	@ControlID  bigint=0,
	@DetailID1  bigint=0,
	@DetailID2  bigint=0,
	@CostCenterFilter NVarChar(1000)='',
	@ProjectFilter NVarChar(1000)=''
)
AS
	SET NOCOUNT ON 
	DECLARE @SQL as nvarchar(2000)
	DECLARE @SQLBody as nvarchar(500)
	DECLARE @SQLWhere as nvarchar(500)
	DECLARE @SQLWhereBefore as nvarchar(500)
	DECLARE @SQLWhereCommon as nvarchar(500)
	DECLARE @AccTable as nChar(10) 

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

	SET @AccTable = 'AccTotal'

	if @GroupBy = 'TotalID'
		SET @AccTable = 'AccTotal'
	else if @GroupBy = 'ControlID'
		SET @AccTable = 'AccControl'
	else
		SET @AccTable = 'AccDetail'
	
	SET @SQLBody = '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, 
			RTRIM(Acc.Code) as AccCode, Acc.Title,'

	SET @SQLWhere = 'WHERE	Acc.Title is not 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)) + '))'
			
	SET @SQLWhereCommon = ' 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)
		    AND (Docs.CostCenter is NULL OR Docs.' + @CostCenterFilter + ')
		    AND (Docs.Project is NULL OR Docs.' + @ProjectFilter + ')'

	if @ControlID > 0 
		SET @SQLWhereCommon=@SQLWhereCommon + ' AND Docs.ControlID = ' + CAST(@ControlID as varchar(20))
	if @DetailID1 > 0 
		SET @SQLWhereCommon=@SQLWhereCommon + ' AND Docs.DetailID1=' + CAST(@DetailID1 as varchar(20))
	if @DetailID2 > 0 
		SET @SQLWhereCommon=@SQLWhereCommon + ' AND Docs.DetailID2=' + CAST(@DetailID2 as varchar(20))

	SET @SQLWhereBefore ='WHERE	Acc.Title is not 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)) +'))'


	SET @SQL=@SQLBody+'Docs.'+@GroupBy+' AS AccID
		FROM dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
				dbo.'+@AccTable+' as Acc ON Docs.'+@GroupBy+' = Acc.ID
		'+@SQLWhere+@SQLWhereCommon+' GROUP BY Docs.'+@GroupBy+', RTRIM(Acc.Code), Acc.Title '

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
		('+@SQLBody+'Docs.'+@GroupBy+' AS AccID
		FROM	dbo.AccDocumentGoods as Docs WITH(NOLOCK) LEFT OUTER JOIN
				dbo.'+@AccTable+' as Acc ON Docs.'+@GroupBy+' = Acc.ID
		'+@SQLWhereBefore+@SQLWhereCommon+' GROUP BY Docs.'+@GroupBy+', RTRIM(Acc.Code), Acc.Title ) as t'
print @SQL
	exec(@SQL)
	
RETURN 