DECLARE @StationId int = 18
DECLARE @getTypeId int = 0

DECLARE @IsSameProvinceWithSc bit = 0
		SELECT @IsSameProvinceWithSc = 1
		FROM dbo.Stations s
			INNER JOIN dbo.Stations s2 ON s2.CityID = s.CityID
		WHERE s.SID = @StationId AND s2.STID = 2 AND s2.Visible = 1

		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

		DECLARE @TimeToPickup int = 0
		SELECT @TimeToPickup = CAST(sc.[Value] AS int) FROM dbo.SystemConfigs sc WHERE sc.Name = 'TimeToProcessPickup'


		SELECT
				po.ProductTypeId,
				po.MTID,
				cmw.MerchantWarehouseId AS ClientMerchantWarehouseId,
				po.PoId,
				cm.MerchantName,
				cmw.Name,
				CONCAT(cmw.Name + ' - ',
					ISNULL(STUFF((
						SELECT	concat(N'; ' ,datepart(hh,wtm.TimeStart), ':' , format(datepart(mi,wtm.TimeStart),'00'), N'-', datepart(hh,wtm.TimeEnd), ':' , format(datepart(mi,wtm.TimeEnd),'00'))
						FROM	dbo.WorkingTimeMWH wtm WITH (NOLOCK)
						WHERE	DATEPART(DW, DATEADD(DAY,-1,GETDATE()))= wtm.DayWorking AND wtm.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId AND wtm.Visible = 1
						ORDER BY wtm.TimeStart ASC
						FOR XML PATH ('')
					), 1,2,''), ''), ' - ', FORMAT(cmw.MerchantWarehouseId, '000000')) AS PoGroup,
				po.RouteId,
				r.RouteName,
				po.SKUAmount,
				(SELECT COUNT(po2.PoId) FROM dbo.PurchaseOrder po2 WHERE po2.SoId = po.SoId AND po2.DeliveryFlowId = 3) AS TotalInboundPo,
				--po.TotalItemAmount,
				SUM(cp.CBM * pod.Amount) AS TotalSkuCbm,
				SUM(cp.Weight * pod.Amount) AS TotalWeight,
				SUM(pod.SellPrice * pod.Amount) AS AmountInclVat,
				DATEDIFF(Minute, GETDATE(), po.DeliveryDateFrom) AS RemainTime,
				s.SourceClientId,
				DATEADD(minute, CASE WHEN ISNULL(pof.PoFeeId, 0) IN (2,3) THEN - 1 ELSE 0 END, po.DeliveryDateFrom) AS DeliveryDateFrom,
				po.UID_COD,
				s.ClientSoId,
				s.SpecialNoteStatus,
				CASE WHEN s.SpecialNoteStatus = 1 THEN N'!'ELSE N'' END AS SpecialNoteStatusText,
				pod.Note,
				cm.MerchantId,
				DATEDIFF(Minute, GETDATE(), po.DeliveryDateFrom)+ CASE WHEN po.MerchantPickupDateFrom IS NOT NULL AND DATEDIFF(MINUTE, GETDATE(), po.MerchantPickupDateFrom) > @TimeToPickup THEN 1000000  WHEN ISNULL(pof.PoFeeId, 0) IN (2,3) THEN - 1 ELSE 0 END AS ProcessStatus,
				sde.SName,
				CASE WHEN ISNULL(pof.PoFeeId, 0) = 2 THEN N'CPN' WHEN ISNULL(pof.PoFeeId, 0) = 3 THEN N'GCĐ' ELSE N'' END AS DeliveryType,
				CASE WHEN (EXISTS (SELECT 1 FROM @VinmartId vi WHERE vi.VinmartId = cm.MerchantId) AND po.SID_Tally = po.SID_Destination) THEN 0
					WHEN @IsSameProvinceWithSc = 1 AND
							EXISTS (SELECT 1 FROM dbo.PurchaseOrder po2 WITH (NOLOCK)
										LEFT JOIN dbo.PickUpDetail pud WITH (NOLOCK) ON pud.PoId = po2.PoId
										LEFT JOIN dbo.PickUp pu WITH (NOLOCK) ON pu.PuId = pud.PuId
										LEFT JOIN dbo.Stations sp WITH (NOLOCK) ON sp.SID = pu.DestinationStationId
									WHERE po2.SoId = po.SoId AND po2.DeliveryFlowId = 3
										AND ((po2.SID_Tally <> po2.SID_Destination)
											OR (pu.PuId IS NOT NULL AND sp.STID = 2 AND po2.POStatusId > 1)))
					THEN 1 ELSE 0 END AS InBoundType,
				CASE WHEN EXISTS (SELECT 1 FROM @VinmartId vi WHERE vi.VinmartId = cm.MerchantId) THEN 1 ELSE 0 END AS IsSupermaket,
				s.CreateDatetime AS SoReceivedDateTime,

				-- field để chuyển luồng giao hàng
				ISNULL(stM.StationId,0)												[StationMerchant],
				ISNULL(dbo.Fuc_ConfigMerchantCate(cm.MerchantId,po.PoId),0)			[ConfigMerchantCate],
				[dbo].[GetCatoragyforStationMerchant](po.PoId)						[Catogary],
				Replace(ISNULL(stM.ProductTypeId, 0),' ','')						[StrProductype],
				ISNULL(IsBulkySetup,0) AS											[IsBulkySetup],
				ISNULL(IsBulkyNotSetup,0) AS										[IsBulkyNotSetup],
				0							as										[IsRequireInstall],
				s.CustomerTypeName,
				--[dbo].[GetRequireInstallOfPo](po.ClientPoId)    IsRequireInstall

				po.MerchantPickupDateFrom AS DatePickupAgain,
				CASE WHEN po.MerchantPickupDateFrom IS NULL OR (po.MerchantPickupDateFrom IS NOT NULL AND DATEDIFF(MINUTE, GETDATE(), po.MerchantPickupDateFrom) <= @TimeToPickup) THEN 1 ELSE 0 END AS IsProcessPickup,

				'' AS SAPInternalMerchantSONumber

		FROM
							dbo.PurchaseOrder			po		WITH (NOLOCK)
				INNER JOIN	dbo.PurchaseOrder_Detail	pod		WITH (NOLOCK) ON pod.PoId = po.PoId
				--INNER JOIN	dbo.SO_Detail				sd		WITH (NOLOCK) ON pod.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID)
				INNER JOIN	dbo.SO						s		WITH (NOLOCK) ON s.SOID = po.SoId
				INNER JOIN	dbo.ClientMerchantWarehouse cmw		WITH (NOLOCK) ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
				--INNER JOIN	ClientProductCategory		cat		with(nolock)  ON cat.ClientProductCateId = pod.ClientProductCateId
				LEFT JOIN	dbo.ClientProduct			cp		WITH (NOLOCK) ON cp.ClientProductId = pod.ClientProductId
				LEFT JOIN	dbo.ClientMerchant			cm		WITH (NOLOCK) ON cm.ClientMerchantId = cmw.ClientMerchantId
				LEFT JOIN	dbo.Routs					r		WITH (NOLOCK) ON po.RouteId = r.RoutId
				LEFT JOIN	dbo.Stations                sta		WITH (NOLOCK) ON sta.SID  = po.SID_Tally
				LEFT JOIN	dbo.Stations                sde		WITH (NOLOCK) ON sde.SID  = po.SID_Destination
				LEFT JOIN	Station_Merchant			stM		WITH (NOLOCK) ON stm.ClientMerchantId = cmw.ClientMerchantId	AND stM.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId AND stm.Visible	=1
				--LEFT JOIN	ConfigMerchantCate			cf		WITH (NOLOCK) ON cf.MerchantId = m.MerchantId and cat.ClientCateId= cf.ClientCateId
				-------------------------------------------------
				LEFT JOIN	(SELECT pofm.ClientPoId, MIN(pof.PoFeeId) AS PoFeeId
								FROM dbo.PurchaseOrder_Fee_Map pofm WITH (NOLOCK)
									INNER JOIN dbo.PurchaseOrder_Fee pof WITH (NOLOCK) ON pof.PoFeeId = pofm.PoFeeId
								WHERE pofm.PoFeeId > 1
								GROUP BY pofm.ClientPoId) pofm					ON pofm.ClientPoId = po.ClientPoId
				LEFT JOIN	dbo.PurchaseOrder_Fee		pof		WITH (NOLOCK)	ON pof.PoFeeId = pofm.PoFeeId
				--------------------------------------------------------------------
				--LEFT JOIN	dbo.PurchaseOrder_MerchantDate pomd	WITH (NOLOCK)	ON pomd.PoId = po.PoId
		WHERE
				po.DeliveryFlowId	= 3
			AND po.POStatusId		= 1
			AND po.Visible			= 1
			AND po.UnitTransportId	= 1
			AND po.SID_Tally		= @StationId
			AND (
				(@getTypeId = 0 AND po.ProductTypeId IN (1, 2, 3)) OR
				(@getTypeId = 1 AND po.ProductTypeId = 4)
				)
			AND ISNULL(po.IsDropOff, 0) = 0
			AND po.SID_Destination != 0
		GROUP BY
				po.ProductTypeId,
				po.MTID,
				po.ClientMerchantWarehouseId,
				po.PoId,
				cm.MerchantName,
				cmw.Name,
				po.RouteId,
				r.RouteName,
				po.SKUAmount,
				--po.TotalItemAmount,
				s.SourceClientId,
				po.DeliveryDateFrom,
				po.UID_COD,
				s.ClientSoId,
				s.SpecialNoteStatus,
				pod.Note,
				sde.SName,
				cmw.MerchantWarehouseId,
				pof.PoFeeId,
				po.SoId,
				po.SID_Tally,
				po.SID_Destination,
				s.CreateDatetime,
				stM.ProductTypeId,
				IsBulkySetup,
				IsBulkyNotSetup,
				stM.StationId,
				s.CustomerTypeName,
				po.MerchantPickupDateFrom,
				cm.MerchantId