/****** Object:  StoredProcedure [dbo].[AccWarehouseBalance]    Script Date: 11/12/2022 10:50:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[AccWarehouseBalance]
	(
		@ByDate bit=1,
		@StartNum as bigint=0,
		@EndNum as bigint=0,
		@StartDate as datetime='1900-01-01',
		@EndDate as datetime='1900-01-01',
		@TotalID bigint=0,
		@ControlID bigint=0,
		@DetailID bigint=0,
		@ParentCode as NVarChar(30)='',
		@WarehouseNo as bigint=0,
		@WarehouseNums as NVarChar(250)='',
		@Flags as int=0
	)
AS

print @FLAGS
	SET NOCOUNT ON 
	if @ByDate = 1
	begin
		if @StartDate = '1900-01-01' 
			select @StartDate =  min([Date]) from dbo.ViwAccWareHouse
		if @EndDate = '1900-01-01' 
			select @EndDate =  Max([Date]) from dbo.ViwAccWareHouse
	END

	IF @TotalID=-1
	BEGIN
		SELECT Cast(0 as bit) as Selected, 
			COALESCE(SUM(W.RequestIn),0) AS RequestIn, 
			COALESCE(SUM(W.RequestOut),0) AS RequestOut, 
			SUM(W.RequestIn) - SUM(W.RequestOut) AS RemRequest, 
			COALESCE(SUM(W.RequestIn2),0) AS RequestIn2, 
			COALESCE(SUM(W.RequestOut2),0) AS RequestOut2, 
			SUM(W.RequestIn2) - SUM(W.RequestOut2) AS RemRequest2, 

			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,
            W.OrderTotalID, W.OrderControlID, W.OrderDetailID1, W.OrderDetailID2
           
		FROM dbo.ViwAccWareHouse  as W WITH(NOLOCK)
			 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 ((@WarehouseNo=0 and (@WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ','))))) or WarehouseNo=@WarehouseNo) AND ((@ByDate = 1 AND W.Date BETWEEN @StartDate AND @EndDate) OR
		                      (@ByDate = 0 AND W.Number BETWEEN @StartNum AND @EndNum) OR
		                      (@ByDate IS Null AND W.UsedDocNumber BETWEEN @StartNum AND @EndNum))
				AND (@Flags=0 OR (@Flags > 0 AND (@Flags & Flags) = @Flags) OR (@Flags < 0 AND (-@Flags & Flags) <> -@Flags))
		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
	END
	ELSE
	IF @TotalID=0
	BEGIN
		SELECT Cast(0 as bit) as Selected, COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
							SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest, 
							COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
							SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2, 
                     dbo.ViwAccWareHouse.OrderTotalID AS AccID, RTrim(dbo.AccTotal.Code) as AccCode, dbo.AccTotal.Title
		FROM dbo.ViwAccWareHouse WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccTotal ON dbo.ViwAccWareHouse.OrderTotalID = dbo.AccTotal.ID
		WHERE ((@WarehouseNo=0 and (@WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ','))))) or WarehouseNo=@WarehouseNo) AND dbo.AccTotal.Title is not null AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
		                      (@ByDate = 0 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum) OR
		                      (@ByDate IS Null AND dbo.ViwAccWareHouse.UsedDocNumber BETWEEN @StartNum AND @EndNum))
				AND (@Flags=0 OR (@Flags > 0 AND (@Flags & Flags) = @Flags) OR (@Flags < 0 AND (-@Flags & Flags) <> -@Flags))
		GROUP BY dbo.ViwAccWareHouse.OrderTotalID, dbo.AccTotal.Code, dbo.AccTotal.Title


--		SELECT  COALESCE(SUM(RequestIn),0) AS RequestIn, COALESCE(SUM(RequestOut),0) AS RequestOut, COALESCE(Sum(RemRequest),0) as RemRequest,
--				  COALESCE(SUM(RequestIn2),0) AS RequestIn2, COALESCE(SUM(RequestOut2),0) AS RequestOut2, COALESCE(Sum(RemRequest2),0) as RemRequest2
--		from (SELECT COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
--									SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest,
--						  COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
--									SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2
--		FROM dbo.ViwAccWareHouse WITH(NOLOCK) --LEFT OUTER JOIN dbo.AccTotal ON dbo.ViwAccWareHouse.OrderTotalID = dbo.AccTotal.ID
--		WHERE (((@WarehouseNo=0 and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) or WarehouseNo=@WarehouseNo) AND --dbo.AccTotal.Title is not null AND 
--			((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
--		                      (NOT @ByDate = 1 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum))
--		GROUP BY dbo.ViwAccWareHouse.OrderTotalID) as t
		
	END
	ELSE
		IF @ControlID=0
	BEGIN
		SELECT Cast(0 as bit) as Selected, COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
							SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest, 
							COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
							SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2, 
						 dbo.ViwAccWareHouse.OrderControlID AS AccID, @ParentCode+N'‪-‫'+RTRIM(dbo.AccControl.Code) as AccCode, dbo.AccControl.Title
		FROM dbo.ViwAccWareHouse WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccControl ON dbo.ViwAccWareHouse.OrderControlID = dbo.AccControl.ID
		WHERE ((@WarehouseNo=0 and (@WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ','))))) or WarehouseNo=@WarehouseNo) AND dbo.AccControl.Title is not null AND dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
		                      (@ByDate = 0 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum) OR
		                      (@ByDate IS Null AND dbo.ViwAccWareHouse.UsedDocNumber BETWEEN @StartNum AND @EndNum))
				AND (@Flags=0 OR (@Flags > 0 AND (@Flags & Flags) = @Flags) OR (@Flags < 0 AND (-@Flags & Flags) <> -@Flags))
		GROUP BY dbo.ViwAccWareHouse.OrderControlID, dbo.AccControl.Code, dbo.AccControl.Title 

--		SELECT  COALESCE(SUM(RequestIn),0) AS RequestIn, COALESCE(SUM(RequestOut),0) AS RequestOut, COALESCE(Sum(RemRequest),0) as RemRequest,
--				  COALESCE(SUM(RequestIn2),0) AS RequestIn2, COALESCE(SUM(RequestOut2),0) AS RequestOut2, COALESCE(Sum(RemRequest2),0) as RemRequest2
--		from (SELECT COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
--									SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest,
--						  COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
--									SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2
--		FROM dbo.ViwAccWareHouse WITH(NOLOCK) -- LEFT OUTER JOIN dbo.AccTotal ON dbo.ViwAccWareHouse.OrderTotalID = dbo.AccTotal.ID
--		WHERE ((@WarehouseNo=0 and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) or WarehouseNo=@WarehouseNo AND  --dbo.AccTotal.Title is not null AND 
--				dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
--		                      (NOT @ByDate = 1 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum))
--		GROUP BY dbo.ViwAccWareHouse.OrderControlID) as t
	END
	ELSE
		IF @DetailID=0
	BEGIN
		SELECT Cast(0 as bit) as Selected, COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
							SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest, 
							COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
							SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2, 
									dbo.ViwAccWareHouse.OrderDetailID1 AS AccID, @ParentCode+N'‪-‫'+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM dbo.ViwAccWareHouse WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccDetail ON dbo.ViwAccWareHouse.OrderDetailID1 = dbo.AccDetail.ID
		WHERE ((@WarehouseNo=0 and (@WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ','))))) or WarehouseNo=@WarehouseNo) AND dbo.AccDetail.Title is not null AND dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND dbo.ViwAccWareHouse.OrderControlID = @ControlID AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
		                      (@ByDate = 0 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum) OR
		                      (@ByDate IS Null AND dbo.ViwAccWareHouse.UsedDocNumber BETWEEN @StartNum AND @EndNum))
				AND (@Flags=0 OR (@Flags > 0 AND (@Flags & Flags) = @Flags) OR (@Flags < 0 AND (-@Flags & Flags) <> -@Flags))
		GROUP BY dbo.ViwAccWareHouse.OrderDetailID1, dbo.AccDetail.Code, dbo.AccDetail.Title 

--		SELECT  COALESCE(SUM(RequestIn),0) AS RequestIn, COALESCE(SUM(RequestOut),0) AS RequestOut, COALESCE(Sum(RemRequest),0) as RemRequest,
--				  COALESCE(SUM(RequestIn2),0) AS RequestIn2, COALESCE(SUM(RequestOut2),0) AS RequestOut2, COALESCE(Sum(RemRequest2),0) as RemRequest2
--		from (SELECT COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
--									SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest,
--						  COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
--									SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2
--		FROM dbo.ViwAccWareHouse WITH(NOLOCK) -- LEFT OUTER JOIN dbo.AccTotal ON dbo.ViwAccWareHouse.OrderTotalID = dbo.AccTotal.ID
--		WHERE (((@WarehouseNo=0 and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) or WarehouseNo=@WarehouseNo) AND  --dbo.AccTotal.Title is not null AND 
--				dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND dbo.ViwAccWareHouse.OrderControlID = @ControlID AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
--		                      (NOT @ByDate = 1 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum))
--		GROUP BY dbo.ViwAccWareHouse.OrderDetailID1) as t
	END
	ELSE
	BEGIN
		SELECT Cast(0 as bit) as Selected, COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
							SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest, 
							COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
							SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2, 
									dbo.ViwAccWareHouse.OrderDetailID2 AS AccID, @ParentCode+N'‪-‫'+RTRIM(dbo.AccDetail.Code) as AccCode, dbo.AccDetail.Title
		FROM dbo.ViwAccWareHouse WITH(NOLOCK) LEFT OUTER JOIN
		                      dbo.AccDetail ON dbo.ViwAccWareHouse.OrderDetailID2 = dbo.AccDetail.ID
		WHERE ((@WarehouseNo=0 and (@WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ','))))) or WarehouseNo=@WarehouseNo) AND dbo.AccDetail.Title is not null 
						AND dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND dbo.ViwAccWareHouse.OrderControlID = @ControlID AND dbo.ViwAccWareHouse.OrderDetailID1 = @DetailID
										AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
		                      (@ByDate = 0 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum) OR
		                      (@ByDate IS Null AND dbo.ViwAccWareHouse.UsedDocNumber BETWEEN @StartNum AND @EndNum))
				AND (@Flags=0 OR (@Flags > 0 AND (@Flags & Flags) = @Flags) OR (@Flags < 0 AND (-@Flags & Flags) <> -@Flags))
		GROUP BY dbo.ViwAccWareHouse.OrderDetailID2, dbo.AccDetail.Code, dbo.AccDetail.Title 

--		SELECT  COALESCE(SUM(RequestIn),0) AS RequestIn, COALESCE(SUM(RequestOut),0) AS RequestOut, COALESCE(Sum(RemRequest),0) as RemRequest,
--				  COALESCE(SUM(RequestIn2),0) AS RequestIn2, COALESCE(SUM(RequestOut2),0) AS RequestOut2, COALESCE(Sum(RemRequest2),0) as RemRequest2
--		from (SELECT COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn),0) AS RequestIn, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut),0) AS RequestOut, 
--									SUM(dbo.ViwAccWareHouse.RequestIn) - SUM(dbo.ViwAccWareHouse.RequestOut) AS RemRequest,
--						  COALESCE(SUM(dbo.ViwAccWareHouse.RequestIn2),0) AS RequestIn2, COALESCE(SUM(dbo.ViwAccWareHouse.RequestOut2),0) AS RequestOut2, 
--									SUM(dbo.ViwAccWareHouse.RequestIn2) - SUM(dbo.ViwAccWareHouse.RequestOut2) AS RemRequest2
--		FROM dbo.ViwAccWareHouse WITH(NOLOCK) -- LEFT OUTER JOIN dbo.AccTotal ON dbo.ViwAccWareHouse.OrderTotalID = dbo.AccTotal.ID
--		WHERE (((@WarehouseNo=0 and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) and @WarehouseNums='' or (WarehouseNo in (select * from dbo.SplitStr(@WarehouseNums, ',')))) or WarehouseNo=@WarehouseNo) AND  --dbo.AccTotal.Title is not null AND 
--				dbo.ViwAccWareHouse.OrderTotalID = @TotalID AND dbo.ViwAccWareHouse.OrderControlID = @ControlID AND dbo.ViwAccWareHouse.OrderDetailID1 = @DetailID
--				AND ((@ByDate = 1 AND dbo.ViwAccWareHouse.Date BETWEEN @StartDate AND @EndDate) OR
--		                      (NOT @ByDate = 1 AND dbo.ViwAccWareHouse.Number BETWEEN @StartNum AND @EndNum))
--		GROUP BY dbo.ViwAccWareHouse.OrderDetailID1) as t
	END
	
	RETURN 