
/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 2023/03/27 01:23:13 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [dbo].[ViwAccWarehouseRem]
AS
select coalesce(Ware.OrderTotalID,INPUT.OrderTotalID,Delivery.OrderTotalID,0) AS OrderTotalID, 
			coalesce(Ware.OrderControlID,INPUT.OrderControlID,Delivery.OrderControlID,0) AS OrderControlID,
			coalesce(Ware.OrderDetailID1,INPUT.OrderDetailID1,Delivery.OrderDetailID1,0) AS OrderDetailID1,
			coalesce(Ware.OrderDetailID2,INPUT.OrderDetailID2,Delivery.OrderDetailID2,0) AS OrderDetailID2,
			coalesce(Ware.WarehouseNo,INPUT.WarehouseNo,Delivery.WarehouseNo,0) AS WarehouseNo, 
			coalesce(Sum(Input.Request),0) as Request, coalesce(Sum(Delivery.DeliveryValue),0) as DeliveryValue, coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0) as RequestRem,
			coalesce(Sum(Ware.RequestIn),0) as RequestIn, coalesce(Sum(Ware.RequestOut),0) as RequestOut, coalesce(Sum(Ware.RequestRem),0) as WareRem,
			coalesce(Sum(Ware.RequestIn),0)-coalesce(Sum(Ware.RequestOut),0)-(coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0)) as TotalDiff,
			coalesce(Sum(Delivery.DeliveryRem),0) as DeliveryRem, coalesce(Sum(Ware.RequestIn),0)-coalesce(Sum(Ware.RequestOut),0)-(coalesce(Sum(Input.Request),0)-coalesce(Sum(Delivery.DeliveryValue),0))+coalesce(Sum(Delivery.DeliveryRem),0) as Diff
--select Ware.* , input.*
	from 
	(	
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(Request),0) as Request, coalesce(Sum(RequestIn),0) as RequestIn, coalesce(Sum(RequestOut),0) as RequestOut
		from ViwAccWareHouse with(nolock) where (FormType in (0,2,4,6,7,10,13,121) or InOrOut = N'انتقالی به') and Len(SerialFrom)>5 and Len(SerialTo)>5
		--From ViwAccWarehouseDetails  with(nolock)
		--where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (0,2,4,6,7,10,13,121)) and Len(SerialFrom)>5 and Len(SerialTo)>5
		Group By OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo 
		--HAVING WarehouseNo =2
		--having orderdetailid2=2163
	) as Input FULL OUTER join 
	(	
			Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(DeliveryValue),0) as DeliveryValue, coalesce(Sum(DeliveryRem),0) as DeliveryRem, coalesce(Sum(DetailVRequest),0) as DetailVRequest
	from (
			Select detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, WarehouseNo, coalesce(Sum(Delivery.VRequest),0) as DeliveryValue, 
					coalesce(Sum(Detail.Request),0)-coalesce(Sum(Detail.VRequest),0) as DeliveryRem, coalesce(Sum(Detail.VRequest),0) as DetailVRequest
			From (SELECT * FROM AccWarehouseDelivery with(nolock) where Flags=0) as Delivery
			right outer join (
				Select ID, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, Request, VRequest, WarehouseNo from ViwAccWareHouse with(nolock) where (FormType in (1,3,5,8,9,11,120) )) as Detail on Delivery.WareDetailsID = Detail.id

		
			--	Select ID, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, Request, VRequest, WarehouseNo from ViwAccWarehouseDetails with(nolock) where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (1,3,5,8,9,11,120) )) as Detail on Delivery.WareDetailsID = Detail.id
			Group By detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, Detail.WarehouseNo
			--HAVING WarehouseNo =2
			--SELECT * FROM AccDetail WHERE ID=2291
			UNION ALL
			Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(Request),0) as DeliveryValue, 0 as DeliveryRem, coalesce(Sum(VRequest),0) as DetailVRequest
			from ViwAccWareHouse with(nolock) where (not FormType in (0,2,4,6,7,10,13,121) or InOrOut = N'انتقالی از') and Len(SerialFrom)>5 and Len(SerialTo)>5
			--From ViwAccWarehouseDetails  with(nolock)
			--where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (0,2,4,6,7,10,13,121)) and Len(SerialFrom)>5 and Len(SerialTo)>5
			Group By OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo 
			--HAVING WarehouseNo =2
			--having orderdetailid2=2241
		) t
		Group By OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo	) as Delivery on input.OrderTotalID=Delivery.OrderTotalID and Input.OrderControlID = Delivery.OrderControlID and Input.OrderDetailID1 = Delivery.OrderDetailID1 and Input.OrderDetailID2 = Delivery.OrderDetailID2 and Input.WarehouseNo = Delivery.WarehouseNo 
	right outer join 
	(
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(RequestIn),100000000) as RequestIn, coalesce(Sum(RequestOut),100000000) as RequestOut, coalesce(Sum(RequestIn),0) - coalesce(Sum(RequestOut),0) as RequestRem 
		--select * 
		from ViwAccWareHouse with(nolock)
		--where (FormType in (0,2,4,6,7,10,13,121) or InOrOut = N'انتقالی از') and Len(SerialFrom)>5 and Len(SerialTo)>5	
		--OrderDetailID2 = 2405
		group by OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo 
		--HAVING WarehouseNo =2
	) as Ware on Ware.OrderTotalID=input.OrderTotalID and Ware.OrderControlID = input.OrderControlID and Ware.OrderDetailID1 = input.OrderDetailID1 and Ware.OrderDetailID2 = input.OrderDetailID2 and ware.WarehouseNo=input.WarehouseNo


	Group By Ware.OrderTotalID, Ware.OrderControlID, Ware.OrderDetailID1, Ware.OrderDetailID2, 
			input.OrderTotalID, input.OrderControlID, input.OrderDetailID1, input.OrderDetailID2, 
			Delivery.OrderTotalID, Delivery.OrderControlID, Delivery.OrderDetailID1, Delivery.OrderDetailID2, 
			Ware.WarehouseNo,INPUT.WarehouseNo,Delivery.WarehouseNo
			
--having coalesce(Ware.OrderDetailID2,INPUT.OrderDetailID2,Delivery.OrderDetailID2,0)=2405
GO


