/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 21/09/1401 10:50:12 ب.ظ ******/
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.RequestIn),0)-coalesce(Sum(Ware.RequestOut),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

	from 
	(	
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(Request),0) as Request
		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 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) or InOrOut = N'انتقالی به')) 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
	) 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 
	left outer join 
	(
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, WarehouseNo, coalesce(Sum(RequestIn),0) as RequestIn, coalesce(Sum(RequestOut),0) as RequestOut, coalesce(Sum(RequestIn),0) - coalesce(Sum(RequestOut),0) as RequestRem 
		from ViwAccWareHouse with(nolock)
		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
GO


