USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_PO_GetListSettupPoDetailToDelivery_V01]    Script Date: 11/20/2017 4:06:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Entity Name:	APIClient_PO_GetListSettupPoDetailToDelivery_V01
-- Author:		Hà Trọng Sỹ
-- Create date:	24/05/2017 09:44:33
-- Description:	Lấy toàn bộ PO chi tiết
-- =============================================
ALTER PROCEDURE [dbo].[APIClient_PO_GetListSettupPoDetailToDelivery_V01]
(
	@CurrentUser INT,
	@SesstionKey VARCHAR(200),
	@StationId INT,
	@SourceClientId INT
)
AS
BEGIN
DECLARE	@PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
	BEGIN TRY
		EXEC		[dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME

		DECLARE @VinmartId TABLE (VinmartId int)
		DECLARE @VinmartConfig varchar(max) = ''
		SELECT @VinmartConfig = sc.[Value] FROM dbo.SystemConfigs sc WHERE sc.Name = 'VinmartMerchantId'
		INSERT INTO @VinmartId SELECT slti.[Value] FROM dbo.SplitListToINT(@VinmartConfig, ',') slti

		SELECT PODetailID
		,sd.LogisticSKULabel
		,ClientSKU
		,ProductName
		,sd.[Amount]
		,sd.SellPrice * sd.[Amount] AS UnitBuyPrice
		,sd.PoId
		,[PODetailStatusID]
		,CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
		,PO.[Priority]
		,POD.[TotalSKU]
		,CP.ProductUnit
		,CASE WHEN MW.HybrisMerchantWarehouseId IS NOT NULL AND MW.SourceClientId = 3 THEN CONCAT(MW.HybrisMerchantWarehouseId, ' - ') ELSE N'' END +  CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.FullAddress ELSE MW.FullAddress END AS FullAddress
		, M.MerchantName
		, CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.Phone ELSE MW.PhoneN0 END AS PhoneN0
		,CAST(M.MerchantId AS NVARCHAR) AS MerchantCode
		,ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.ClientMerchantWarehouseId, '000000') + ' - ' + MW.FullAddress AS NccGroup
		,po.UID_COD
		,sd.SODetailID
		,SCM.ClientSoId
		,[ProductItemId]
		,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoLabel
		,SCM.SourceClientId
		,SCM.TotalPo
		,PO.RouteId
		,RD.RouteName
		,ST.SName
		,SCM.DeliveryFullAddress
		,SCM.CustomerName
		,SCM.CustomerPhone
		,r.RoutId AS RouteDest
		,POD.Priority AS PodPriority
		,CP.MerchantProductName
		,'1' + FORMAT(SCM.SourceClientId,'00') + CAST(SCM.ClientSoId AS NVARCHAR) AS ClientSoIdView
		,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoIdView
		,ISNULL(sd.Note,N'') + ISNULL(SCM.Note,N'') AS Note
		,SCM.CreateDatetime AS SoCreateDateTime
		,r.RouteName AS SoRouteName
		,CASE WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) <= 0 THEN 'COD'
			 WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) > 0 AND SCM.RemainingAmount > 0 THEN N'COD + Online'
			 ELSE 'Online' END AS CashType
		--,DATEDIFF(MINUTE, GETDATE(), CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN po.TempDeliveryDateFrom ELSE po.DeliveryDateFrom END) AS RemainTime
		,DATEDIFF(MINUTE, GETDATE(), PO.TempDeliveryDateFrom) AS RemainTime
		,sd.SODetailStatusID
		,M.TradingAddress AS MerchantAddress
		,M.PhoneN0 AS MerchantPhone
		,CASE WHEN sd.SODetailStatusID = 1 THEN ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.MerchantWarehouseId, '000000') + ' - '  + MW.FullAddress ELSE '' END AS PuId
		,CP.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
		,sd.SAPReservationNumber
		,sd.SAPPONumberInterim
		,sd.SAPReservationLineItem
		,0 AS IsSelect
		,CASE WHEN EXISTS (SELECT 1 FROM @VinmartId vi WHERE vi.VinmartId = m.MerchantId) THEN 1 ELSE 0 END AS OrderType
		,pommw.ReverseCode
		--,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) < 2 THEN Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByPolicy
		--,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN Format(po.TempDeliveryDateFrom, 'HH:mm') + ' - ' + Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByCustomer
		,Format(po.MerchantPickupDateFrom, 'dd/MM/yyyy') AS DatePickupAgain -- Ngay hen lay
		,Format(PO.TempDeliveryDateFrom, 'dd/MM/yyyy') AS DateDelivery
		,sd.ClientMerchantId
		,pt.ProductTypeName
		,sc.Name AS SourceClientName
		,PO.ClientMerchantWarehouseId
		,CASE WHEN vlr.ClientRPO IS NOT NULL THEN 1 ELSE 0 END AS IsCreatedRpo
	FROM
					PurchaseOrder PO WITH (NOLOCK)
	    INNER JOIN	dbo.SO_Detail sd WITH (NOLOCK) ON sd.PoId = PO.PoId
	    INNER JOIN	PurchaseOrder_Detail POD WITH (NOLOCK) ON POD.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND POD.PoId = PO.PoId
	    INNER JOIN	ClientProduct CP WITH (NOLOCK) ON CP.ClientProductId = POD.ClientProductId
	    INNER JOIN	ClientMerchantWarehouse MW WITH (NOLOCK) ON MW.ClientMerchantWarehouseId = PO.ClientMerchantWarehouseId
	    INNER JOIN	ClientMerchant M WITH (NOLOCK) ON M.ClientMerchantId = sd.ClientMerchantId
	    INNER JOIN	dbo.SO SCM WITH (NOLOCK) ON SCM.SOID = PO.SoId
	    LEFT JOIN	dbo.Routs RD WITH (NOLOCK) ON RD.RoutId = PO.RouteId
	    LEFT JOIN	dbo.Routs r WITH (NOLOCK) ON PO.DeliveryRoutsId = r.RoutId
	    LEFT JOIN	dbo.Stations ST WITH (NOLOCK) ON ST.SID = PO.SID_Tally
		--LEFT JOIN	dbo.Stations s2 WITH (NOLOCK) ON SCM.DeliveryCityId = s2.CityID AND SCM.DeliveryDistId = s2.DistID AND SCM.DeliveryWardId = s2.WardID AND SCM.DeliveryStreetId = s2.StreetID AND SCM.DeliveryHouseN0 = s2.HouseN0
		LEFT JOIN	dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = PO.ClientPoId --AND pommw.IsComplete = 1
		INNER JOIN	dbo.ProductType pt WITH (NOLOCK) ON pt.ProductTypeID = po.ProductTypeId
		INNER JOIN	dbo.SourceClient sc WITH (NOLOCK) ON sc.SourceClientId = SCM.SourceClientId
		LEFT JOIN	dbo.Vinpro_LogRPO vlr WITH (NOLOCK) ON sd.ClientSoId = vlr.ClientSoid
	 WHERE
		(
			(po.POStatusId = 1 AND sd.SODetailStatusID = 1) OR
			(po.POStatusId = 4 AND sd.SODetailStatusID = 4)
		)
		AND SID_Tally = @StationId
		AND SCM.SourceClientId = @SourceClientId
		AND po.ProductTypeId < 4
		AND po.Visible = 1
		AND po.DeliveryFlowId = 2
		AND SCM.UnitTransportId = 1
		AND ISNULL(po.IsDropOff, 0) = 0
		AND po.SID_Destination != 0
		AND ISNULL(scm.IsDeliveryPiC, 0) = 0
		AND ISNULL(po.IsOnhold, 0) = 0
		AND ISNULL(scm.OrderType, 0) = 1
		AND sd.Visible = 1

	 END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END