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





ALTER PROCEDURE [dbo].[AccBalanceReportInitial]
	(
		@UsePermanent as bit=1,
		@ByDate as bit=1,
		@StartNum as bigint=0,
		@EndNum as bigint=0,
		@StartDate as datetime=getdate,
		@EndDate as datetime=getdate,
		 @TotalID as bigint=0,
		 @ControlID as bigint=0,
		 @DetailID as bigint=0,
		 @ParentCode as NVarChar(30),
		@CostCenterFilter NVarChar(1000)='',
		@ProjectFilter NVarChar(1000)=''
	)
AS
	SET NOCOUNT ON 

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


	DECLARE @SQL as nvarchar(2000)
	--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, 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 dbo.AccDocumentGoods.Flags = 2
	--	GROUP BY dbo.AccDocumentGoods.TotalID, dbo.AccTotal.Code, dbo.AccTotal.Title 
 --   return
	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, 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 dbo.AccDocumentGoods.Flags = 2
		    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, dbo.AccTotal.Code, dbo.AccTotal.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(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, 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 dbo.AccDocumentGoods.Flags = 2
		    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, dbo.AccTotal.Code, dbo.AccTotal.Title ) as t'
		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(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 dbo.AccDocumentGoods.Flags = 2
		    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 '
		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(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 dbo.AccDocumentGoods.Flags = 2
		    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'
		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(20)) + ' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID 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 dbo.AccDocumentGoods.Flags = 2
		    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 '
		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(20)) + ' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID 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 dbo.AccDocumentGoods.Flags = 2
		    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'
		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(20)) + ' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(20)) + ' 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 dbo.AccDocumentGoods.Flags = 2
		    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'
		--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(20)) + ' AND dbo.AccDocumentGoods.ControlID = ' + CAST(@ControlID as NvarChar(20)) + ' 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 dbo.AccDocumentGoods.Flags = 2
		    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 