/****** Object:  StoredProcedure [dbo].[AccWarehouse8ColBalance]    Script Date: 19/10/2022 09:21:29 ق.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[AccWarehouse8ColBalance]
	(
		@ByDate bit=1,
		@StartNum as bigint=0,
		@EndNum as bigint=0,
		@StartDate as datetime=getdate,
		@EndDate as datetime=getdate,
		@TotalID bigint=0,
		@ControlID bigint=0,
		@DetailID bigint=0,
		@ParentCode as NVarChar(30)='',
		@WarehouseNo as bigint=0,
		@Filter as NVarChar(1000)=' FormType in (0,1,2,3,4,5,6,7,8,9,10,11,12,120,13)',
		@Flags as int=0,
		@WarehouseNums as NVarChar(250)='',
		@Flags2 as int=0
	)
AS
	SET NOCOUNT ON 
	DECLARE @CMD as NVARCHAR(Max)

	IF @TotalID=-1
		SET @CMD = 'SELECT COALESCE(D2.Title, D1.Title, C.Title, T.Title) as Title,
case when C.Code is null then RTrim(T.Code)
	when D1.Code is null then RTrim(T.Code)+N''-''+RTRIM(C.Code)
	when D2.Code is null then RTrim(T.Code)+N''-''+RTRIM(C.Code)+N''-''+RTRIM(D1.Code)
	else RTrim(T.Code)+N''-''+RTRIM(C.Code)+N''-''+RTRIM(D1.Code)+N''-''+RTRIM(D2.Code)
END as AccCode,
COALESCE(W.OrderDetailID2, W.OrderDetailID1, W.OrderControlID,W.OrderTotalID) as AccID,'
	ELSE IF @TotalID=0
		SET @CMD = 'SELECT T.Title as Title, T.Code as AccCode, W.OrderTotalID AS AccID,'
	ELSE IF @ControlID=0
		SET @CMD = 'SELECT C.Title as Title, N'''+@ParentCode+N'‪-‫'+'''+RTRIM(C.Code) as AccCode, W.OrderControlID AS AccID,'
	ELSE IF @DetailID=0
		SET @CMD = 'SELECT D.Title as Title, N'''+@ParentCode+N'‪-‫'+'''+RTRIM(D.Code) as AccCode, W.OrderDetailID1 AS AccID,'

	SET @CMD = @CMD+'COALESCE(SUM(W.RequestIn-W.RequestOut),0) AS RequestFirst, COALESCE(SUM(CASE when W.RequestIn<>0 then XXX else -(YYY) end),0) AS RequestFirstRial, COALESCE(SUM(W.RequestIn2-W.RequestOut2),0) AS RequestFirst2, '

	if @Flags = 1 or @Flags = 3
		SET @CMD=REPLACE(@CMD,'XXX','W.TotalPrice+W.DetailHaml-W.DetailTakhfif')
	else
		SET @CMD=REPLACE(@CMD,'XXX','W.TotalPrice')

	if @Flags = 2 or @Flags = 3
		SET @CMD=REPLACE(@CMD,'YYY','W.TotalPrice+W.DetailHaml-W.DetailTakhfif')
	else
		SET @CMD=REPLACE(@CMD,'YYY','W.TotalPrice')
	
	IF @TotalID=-1
		SET @CMD = @CMD+'W.OrderTotalID, W.OrderControlID, W.OrderDetailID1, W.OrderDetailID2'
	ELSE IF @TotalID=0
		SET @CMD = @CMD+'W.OrderTotalID'
	ELSE IF @ControlID=0
		SET @CMD = @CMD+'W.OrderControlID'
	ELSE IF @DetailID=0
		SET @CMD = @CMD+'W.OrderDetailID1'
	
	SET @CMD = @CMD+' into #TempFirst FROM dbo.ViwAccWareHouse as W WITH(NOLOCK)'
	
	IF @TotalID=-1
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccTotal as T ON W.OrderTotalID = T.ID LEFT OUTER JOIN dbo.AccControl as C ON W.OrderControlID = C.ID LEFT OUTER JOIN dbo.AccDetail as D1 ON W.OrderDetailID1 = D1.ID LEFT OUTER JOIN dbo.AccDetail As D2 ON W.OrderDetailID2 = D2.ID WHERE '
	ELSE IF @TotalID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccTotal as T ON W.OrderTotalID = T.ID WHERE T.Title is not null AND '
	ELSE IF @ControlID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccControl as C ON W.OrderControlID = C.ID WHERE C.Title is not null AND '
	ELSE IF @DetailID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccDetail as D ON W.OrderDetailID1 = D.ID WHERE D.Title is not null AND '

	if @WarehouseNo > 0 
		set @CMD=@CMD + ' WarehouseNo='+str(@WarehouseNo)+' AND '

	if @WarehouseNums <> ''
		set @CMD=@CMD + ' WarehouseNo in ('+@WarehouseNums+') AND '

	if @ByDate = 1
		SET @CMD =@CMD + ' (FormType=2 OR W.Date < '''+CONVERT(nvarchar(50),@StartDate)+''')'
	else if @ByDate = 0
		SET @CMD =@CMD + ' (FormType=2 OR W.Number < '+str(@StartNum)+')'
	else
		SET @CMD =@CMD + ' (FormType=2 OR W.UsedDocNumber < '+str(@StartNum)+')'
  	SET @CMD = @CMD + ' AND ' + @Filter 

	IF @TotalID>0
		set @CMD=@CMD + ' AND W.OrderTotalID = ' + Str(@TotalID)
	IF @ControlID>0
		set @CMD=@CMD + ' AND W.OrderControlID = ' + Str(@ControlID)
	IF @DetailID>0
		set @CMD=@CMD + ' AND W.OrderDetailID1 = ' + Str(@DetailID)

	if @Flags2 >=0
			SET @CMD=@CMD + ' AND (Flags & ' + Str(@Flags2) + ') =  ' + Str(@Flags2) 
		
	IF @TotalID=-1
		SET @CMD = @CMD + ' GROUP BY W.OrderTotalID, W.OrderControlID, W.OrderDetailID1, W.OrderDetailID2, T.Title,C.Title,D1.Title,D2.Title,T.Code,C.Code,D1.Code,D2.Code;'
	else IF @TotalID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderTotalID, T.Code, T.Title;'
	ELSE IF @ControlID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderControlID, C.Code, C.Title;'
	ELSE IF @DetailID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderDetailID1, D.Code, D.Title;'

	IF @TotalID=-1
		SET @CMD = @CMD  + 'SELECT COALESCE(D2.Title, D1.Title, C.Title, T.Title) as Title,
case when C.Code is null then RTrim(T.Code)
	when D1.Code is null then RTrim(T.Code)+N''-''+RTRIM(C.Code)
	when D2.Code is null then RTrim(T.Code)+N''-''+RTRIM(C.Code)+N''-''+RTRIM(D1.Code)
	else RTrim(T.Code)+N''-''+RTRIM(C.Code)+N''-''+RTRIM(D1.Code)+N''-''+RTRIM(D2.Code)
END as AccCode,
COALESCE(W.OrderDetailID2, W.OrderDetailID1, W.OrderControlID,W.OrderTotalID) as AccID,'
	ELSE IF @TotalID=0
		SET @CMD = @CMD  +'SELECT T.Title as Title, T.Code as AccCode, W.OrderTotalID AS AccID,'
	ELSE IF @ControlID=0
		SET @CMD = @CMD  +'SELECT C.Title as Title, N'''+@ParentCode+N'‪-‫'+'''+RTRIM(C.Code) as AccCode, W.OrderControlID AS AccID,'
	ELSE IF @DetailID=0
		SET @CMD = @CMD  +'SELECT D.Title as Title, N'''+@ParentCode+N'‪-‫'+'''+RTRIM(D.Code) as AccCode, W.OrderDetailID1 AS AccID,'
	
	SET @CMD = @CMD+'COALESCE(SUM(case when W.FormType<>2 then W.RequestIn else 0 end),0) AS RequestIn, COALESCE(SUM(case when W.FormType<>2 then W.RequestOut else 0 end),0) AS RequestOut, COALESCE(SUM(case when W.FormType<>2 and W.RequestIn<>0 then XXX else 0 end),0) AS RequestInRial, COALESCE(SUM(case when W.FormType<>2 and W.RequestOut<>0 then YYY else 0 end),0) AS RequestOutRial, COALESCE(SUM(case when W.FormType<>2 then W.RequestIn2 else 0 end),0) AS RequestIn2, COALESCE(SUM(case when W.FormType<>2 then W.RequestOut2 else 0 end),0) AS RequestOut2, COALESCE(SUM(case when W.FormType<>2 and W.RequestIn2<>0 then XXX else 0 end),0) AS RequestInRial2, COALESCE(SUM(case when W.FormType<>2 and W.RequestOut2<>0 then YYY else 0 end),0) AS RequestOutRial2,'
	if @Flags = 1 or @Flags = 3
		SET @CMD=REPLACE(@CMD,'XXX','W.TotalPrice+W.DetailHaml-W.DetailTakhfif')
	else
		SET @CMD=REPLACE(@CMD,'XXX','W.TotalPrice')

	if @Flags = 2 or @Flags = 3
		SET @CMD=REPLACE(@CMD,'YYY','W.TotalPrice+W.DetailHaml-W.DetailTakhfif')
	else
		SET @CMD=REPLACE(@CMD,'YYY','W.TotalPrice')
	
	IF @TotalID=-1
		SET @CMD = @CMD+'W.OrderTotalID, W.OrderControlID, W.OrderDetailID1, W.OrderDetailID2'
	else IF @TotalID=0
		SET @CMD = @CMD+'W.OrderTotalID'
	ELSE IF @ControlID=0
		SET @CMD = @CMD+'W.OrderControlID'
	ELSE IF @DetailID=0
		SET @CMD = @CMD+'W.OrderDetailID1'

	SET @CMD = @CMD+' into #Temp FROM dbo.ViwAccWareHouse as W WITH(NOLOCK)'
	IF @TotalID=-1
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccTotal as T ON W.OrderTotalID = T.ID LEFT OUTER JOIN dbo.AccControl as C ON W.OrderControlID = C.ID LEFT OUTER JOIN dbo.AccDetail as D1 ON W.OrderDetailID1 = D1.ID LEFT OUTER JOIN dbo.AccDetail As D2 ON W.OrderDetailID2 = D2.ID WHERE '
	ELSE IF @TotalID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccTotal as T ON W.OrderTotalID = T.ID WHERE T.Title is not null AND '
	ELSE IF @ControlID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccControl as C ON W.OrderControlID = C.ID WHERE C.Title is not null AND '
	ELSE IF @DetailID=0
		set @CMD=@CMD + ' LEFT OUTER JOIN dbo.AccDetail as D ON W.OrderDetailID1 = D.ID WHERE D.Title is not null AND '

	if @WarehouseNo > 0 
		set @CMD=@CMD + ' WarehouseNo='+str(@WarehouseNo)+' AND '

	if @WarehouseNums <> ''
		set @CMD=@CMD + ' WarehouseNo in ('+@WarehouseNums+') AND '
	--set @CMD=@CMD + ' ('+str(@WarehouseNo)+'=0 or WarehouseNo='+str(@WarehouseNo)+') AND ('

	if @ByDate = 1
		SET @CMD =@CMD + 'W.Date BETWEEN '''+CONVERT(nvarchar(50),@StartDate)+''' AND '''+CONVERT(nvarchar(50),@EndDate)+''''
	else if @ByDate = 0
		SET @CMD =@CMD + 'W.Number BETWEEN '+str(@StartNum)+' AND '+str(@EndNum)+''
	else
		SET @CMD =@CMD + 'W.UsedDocNumber BETWEEN '+str(@StartNum)+' AND '+str(@EndNum)+''
	SET @CMD =@CMD + ' AND ' + @Filter 
	
	IF @TotalID>0
		set @CMD=@CMD + ' AND W.OrderTotalID = ' + Str(@TotalID)
	IF @ControlID>0
		set @CMD=@CMD + ' AND W.OrderControlID = ' + Str(@ControlID)
	IF @DetailID>0
		set @CMD=@CMD + ' AND W.OrderDetailID1 = ' + Str(@DetailID)

	if @Flags2 >=0
			SET @CMD=@CMD + ' AND (Flags & ' + Str(@Flags2) + ') =  ' + Str(@Flags2) 
	
	IF @TotalID=-1
		SET @CMD = @CMD + ' GROUP BY W.OrderTotalID, W.OrderControlID, W.OrderDetailID1, W.OrderDetailID2, T.Title,C.Title,D1.Title,D2.Title,T.Code,C.Code,D1.Code,D2.Code;'
	else IF @TotalID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderTotalID, T.Code, T.Title;'
	ELSE IF @ControlID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderControlID, C.Code, C.Title;'
	ELSE IF @DetailID=0
		SET @CMD = @CMD + ' GROUP BY W.OrderDetailID1, D.Code, D.Title;'

	SET @CMD = @CMD + 'SELECT cast(0 as bit) as Selected, COALESCE(P.Title, c.Title,'''') as Title, COALESCE(P.AccCode, C.AccCode,'''') as AccCode, COALESCE(P.RequestFirst,0) as RequestFirst, COALESCE(C.RequestIn,0) as RequestIn,
			COALESCE(C.RequestOut,0) as RequestOut, COALESCE(P.RequestFirstRial,0) as RequestFirstRial, COALESCE(C.RequestInRial,0) as RequestInRial, COALESCE(C.RequestOutRial,0) as RequestOutRial,
			COALESCE(P.RequestFirst2,0) as RequestFirst2, COALESCE(C.RequestIn2,0) as RequestIn2, COALESCE(C.RequestOut2,0) as RequestOut2, COALESCE(C.RequestInRial2,0) as RequestInRial2, COALESCE(C.RequestOutRial2,0) as RequestOutRial2, 
			COALESCE(P.AccID, C.AccID) as AccID,'
			
	IF @TotalID=-1
		SET @CMD = @CMD + 'COALESCE(P.OrderTotalID, c.OrderTotalID,0) as OrderTotalID, COALESCE(P.OrderControlID,C.OrderControlID,0) as OrderControlID, COALESCE(P.OrderDetailID1,C.OrderDetailID1,0) as OrderDetailID1, COALESCE(P.OrderDetailID2,C.OrderDetailID2,0) as OrderDetailID2'
	else IF @TotalID=0
		SET @CMD = @CMD + 'COALESCE(P.OrderTotalID, c.OrderTotalID,0) as OrderTotalID'
	ELSE IF @ControlID=0
		SET @CMD = @CMD + 'COALESCE(P.OrderControlID,C.OrderControlID,0) as OrderControlID'
	ELSE IF @DetailID=0
		SET @CMD = @CMD + 'COALESCE(P.OrderDetailID1,C.OrderDetailID1,0) as OrderDetailID1'

	SET @CMD = @CMD + '	from #Temp as c full outer join #TempFirst as p '
	IF @TotalID=-1
		SET @CMD = @CMD + 'ON p.OrderTotalID = c.OrderTotalID and p.OrderControlID = c.OrderControlID and p.OrderDetailID1=c.OrderDetailID1 and p.OrderDetailID2=c.OrderDetailID2;'
	else IF @TotalID=0
		SET @CMD = @CMD + 'ON p.OrderTotalID = c.OrderTotalID;'
	ELSE IF @ControlID=0
		SET @CMD = @CMD + 'ON p.OrderControlID = c.OrderControlID;'
	ELSE IF @DetailID=0
		SET @CMD = @CMD + 'ON p.OrderDetailID1=c.OrderDetailID1;'
			
	SET @CMD = @CMD + ' Drop Table #TempFirst;drop table #Temp;'
print @CMD
	exec(@CMD)

RETURN