
/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 25/11/2022 10:02:31 ?.? ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ViwAccWarehouseRem]
AS

	select Input.OrderTotalID, Input.OrderControlID, Input.OrderDetailID1, Input.OrderDetailID2, 
			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, coalesce(Sum(Request),0) as Request
		From AccWarehouseDetails with(nolock)
		where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (0)) and Len(SerialFrom)>5 and Len(SerialTo)>5
		Group By OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2
	) as Input left outer join 
	(	
		Select detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, 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 from AccWarehouseDetails with(nolock) where WarehouseID in (select id from AccWarehouse with(nolock) where InOROut in (1) )) as Detail on Delivery.WareDetailsID = Detail.id
		Group By detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2
		--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 
	left outer join 
	(
		Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, 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
	) as Ware on Ware.OrderTotalID=input.OrderTotalID and Ware.OrderControlID = input.OrderControlID and Ware.OrderDetailID1 = input.OrderDetailID1 and Ware.OrderDetailID2 = input.OrderDetailID2 
	Group By input.OrderTotalID, input.OrderControlID, input.OrderDetailID1, input.OrderDetailID2
GO


