SELECT CustomerCode,
       OriCustomerCode,
       CONVERT(INT,NULL) BusinessUnit
INTO #TempCustomer
FROM
(
    SELECT CASE
               WHEN ISNULL(DisplayName, '') != ''
                    AND tblCustomers.BusinessUnit_ID = 1 THEN
                   DisplayName
               ELSE
                   WH_CustomerProfile.CustomerCode
           END CustomerCode,
           WH_CustomerProfile.CustomerCode OriCustomerCode
    FROM Warehouse.dbo.WH_CustomerProfile
        INNER JOIN EECSystem.dbo.tblCustomers
            ON tblCustomers.CustomerCode = WH_CustomerProfile.CustomerCode
               AND tblCustomers.DisplayInEcomReports = 1
        LEFT JOIN Sys_EcomWholeCustomer
            ON Sys_EcomWholeCustomer.CustomerCode = WH_CustomerProfile.CustomerCode
    WHERE Active = 1
) Result
GROUP BY Result.CustomerCode,
         Result.OriCustomerCode;


SELECT ItemNo,MAX(Result.BusinessUnit_ID) BusinessUnit_ID 
INTO #tbItemNo 
FROM (
	SELECT ItemNo,tblItemMaster.BusinessUnit_ID FROM EECSystem.dbo.tblCustomerItem
	INNER JOIN EECSystem.dbo.tblItemMaster ON Item_ID=ItemID
	INNER JOIN #TempCustomer ON #TempCustomer.OriCustomerCode = tblCustomerItem.CustomerCode 
	WHERE tblItemMaster.BusinessUnit_ID IN (1,5)  AND (#TempCustomer.BusinessUnit IS NULL OR #TempCustomer.BusinessUnit=tblItemMaster.BusinessUnit_ID)
	UNION ALL
	SELECT RTL_ECOMItemMaster.ItemNo,RTL_ECOMItemMaster.BusinessUnit_ID FROM EECSystem.dbo.RTL_ECOMItemMaster
	INNER JOIN EECSystem.dbo.RTL_ECOMCustomerItem ON RTL_ECOMCustomerItem.Item_ID = RTL_ECOMItemMaster.Item_ID
	INNER JOIN #TempCustomer ON #TempCustomer.OriCustomerCode = RTL_ECOMCustomerItem.CustomerCode
	WHERE RTL_ECOMItemMaster.BusinessUnit_ID IN (1,5)  AND (#TempCustomer.BusinessUnit IS NULL OR #TempCustomer.BusinessUnit=RTL_ECOMItemMaster.BusinessUnit_ID)
)Result
GROUP BY Result.ItemNo




SELECT RPT_ECOMSalesReport.*
INTO  #TempSalesReport
FROM RPT_ECOMSalesReport
INNER JOIN  #TempCustomer ON #TempCustomer.CustomerCode =RPT_ECOMSalesReport.CustomerCode 
WHERE (DateID>='2024/5/1' AND DateID<'2024/6/1')



SELECT ItemID,tblItemMaster.ItemNo,CommCode, InvAvailableDate,ClassificationCode
INTO #TempItem
FROM #tbItemNo
INNER JOIN EECSystem.dbo.tblItemMaster ON tblItemMaster.ItemNo = #tbItemNo.ItemNo AND tblItemMaster.BusinessUnit_ID = #tbItemNo.BusinessUnit_ID
LEFT JOIN EECSystem.dbo.RTL_ECOMItemMaster ON tblItemMaster.ItemID = RTL_ECOMItemMaster.Item_ID
WHERE CommCode='FUR'
	AND 
	(
		EXISTS(SELECT * FROM EECSystem.dbo.RTL_ECOMCustomerItem INNER JOIN #TempCustomer ON #TempCustomer.CustomerCode=RTL_ECOMCustomerItem.CustomerCode   
				WHERE RTL_ECOMCustomerItem.Item_ID=tblItemMaster.ItemID AND (RetailStatus=1 OR RetailStatusDate>GetDate()) 
				AND EXISTS(SELECT * FROM EECSystem.dbo.RTL_ECOMCustomerItem INNER JOIN EECSystem.dbo.tblCustomers ON Customer_ID=CustomerID AND ISNULL(DisplayInEcomReports,0)=1
				WHERE  RTL_ECOMCustomerItem.ItemNo=tblItemMaster.ItemNo )) 
			OR  (NOT EXISTS(SELECT * FROM EECSystem.dbo.RTL_ECOMCustomerItem WHERE RTL_ECOMCustomerItem.Item_ID=tblItemMaster.ItemID) 
				AND	EXISTS(SELECT * FROM EECSystem.dbo.tblCustomerItem INNER JOIN EECSystem.dbo.tblCustomers ON tblCustomerItem.Customer_ID=tblCustomers.CustomerID AND ISNULL(DisplayInEcomReports,0)=1 
					INNER JOIN EECSystem.dbo.tblItemMaster ci ON ci.ItemID=tblCustomerItem.Item_ID WHERE tblItemMaster.ItemID=ci.ItemID AND ISNULL(InactiveFlag,0)=0))
			OR	EXISTS(
				SELECT * FROM #TempSalesReport
				INNER JOIN #TempCustomer ON #TempSalesReport.ItemNo = tblItemMaster.ItemNo AND #TempSalesReport.CustomerCode=#TempCustomer.CustomerCode
				WHERE #TempSalesReport.ItemNo=tblItemMaster.ItemNo
				)
	)


SELECT #TempItem.ItemNo,CommCode,IsConsignmentLoc,
	CASE When (DATEDIFF(DAY,CASE WHEN '2024/5/1'>InvAvailableDate THEN '2024/5/1' ELSE InvAvailableDate END,CASE WHEN GETDATE()<'2024/6/1' THEN GETDATE() ELSE '2024/6/1' END))<=0 Then 0 
	ELSE ROUND(COUNT(*) * 1.0 
	/ (DATEDIFF(DAY,CASE WHEN '2024/5/1'>InvAvailableDate THEN '2024/5/1' ELSE InvAvailableDate END,CASE WHEN GETDATE()<'2024/6/1' THEN GETDATE() ELSE '2024/6/1' END)), 4) END AS InStockPercent
INTO #TempInstock
FROM dbo.MID_ItemLocDayInventory
INNER JOIN #TempItem ON #TempItem.ItemNo = MID_ItemLocDayInventory.ItemNo
WHERE SafetyStockQty>0 AND RecordDate>=CASE WHEN '2024/5/1'>InvAvailableDate THEN '2024/5/1' ELSE InvAvailableDate END AND RecordDate<CASE WHEN GETDATE()<'2024/6/1' THEN GETDATE() ELSE '2024/6/1' END
	AND ClassificationCode NOT IN ('C+', 'C', 'ORC', 'ARC+', 'ARC')
GROUP BY #TempItem.ItemNo,CommCode,InvAvailableDate,IsConsignmentLoc



SELECT Result.CommCode,Result.IsConsignmentLoc,AVG(ISNULL(InStockPercent,0))
FROM (
	SELECT *,0 IsConsignmentLoc FROM #TempItem
	UNION ALL
	SELECT *,1 IsConsignmentLoc FROM #TempItem
)Result
LEFT JOIN #TempInstock ON #TempInstock.ItemNo = Result.ItemNo
WHERE ClassificationCode NOT IN ('C+', 'C', 'ORC', 'ARC+', 'ARC')
GROUP BY Result.CommCode,Result.IsConsignmentLoc