
/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 21/11/2022 04:00:03 ب.ظ ******/
--DROP VIEW [dbo].[ViwAccWarehouseRem]
--GO

-- select * from [ViwAccWarehouseRem]
/****** Object:  View [dbo].[ViwAccWarehouseRem]    Script Date: 21/11/2022 03:58:52 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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
	from 
	(	Select OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, coalesce(Sum(Request),0) as Request
		From AccWarehouseDetails 
		where WarehouseID in (select id from AccWarehouse 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
		From AccWarehouseDelivery as Delivery
		left outer join (Select ID, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, Request from AccWarehouseDetails where WarehouseID in (select id from AccWarehouse where InOROut in (1) )) as Detail on Delivery.WareDetailsID = Detail.id
		Group By detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2
	) as Delivery on input.OrderTotalID=Delivery.OrderTotalID and Input.OrderControlID = Delivery.OrderControlID  and Input.OrderDetailID1 = Delivery.OrderDetailID1 and Input.OrderDetailID2 = Delivery.OrderDetailID2 
	Group By input.OrderTotalID, input.OrderControlID, input.OrderDetailID1, input.OrderDetailID2
/*
	select detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2, coalesce(Sum(Delivery.VRequest),0) as DeliveryValue, coalesce(Sum(Detail.Request),0) as Request, coalesce(Sum(Detail.Request),0)-coalesce(Sum(Delivery.VRequest),0) as RequestRem
	from AccWarehouseDelivery as Delivery
	left outer join (Select ID, OrderTotalID, OrderControlID, OrderDetailID1, OrderDetailID2, Request from AccWarehouseDetails where WarehouseID in (select id from AccWarehouse where InOROut in (0) )) as Detail on Delivery.WareDetailsID = Detail.id
	group by detail.OrderTotalID, Detail.OrderControlID, Detail.OrderDetailID1, Detail.OrderDetailID2
	*/
GO


