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


ALTER PROCEDURE [dbo].[AccBalanceUpReportInitial]
(
	@UsePermanent as bit=1,
	@ByDate as bit=1,
	@StartNum as bigint=0,
	@EndNum as bigint=0,
	@StartDate as datetime=getdate,
	@EndDate as datetime=getdate,
	@GroupBy as nChar(10)='TotalID',
	@ControlID as bigint=0,
	@DetailID1 as bigint=0,
	@DetailID2 as 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 (Docs.CostCenter is NULL OR Docs.' + @CostCenterFilter + ')
		    AND (Docs.Project is NULL OR Docs.' + @ProjectFilter + ') AND Docs.Flags = 2'

	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 '

	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'
	exec(@SQL)
