-- =============================================
-- Author:		<Zeb>
-- Create date: <2017-8-17>
-- Description:	 Forecast Report 
-- =============================================
ALTER PROCEDURE [dbo].[BPEC_SP_GetSalesFcstReport]
(  
	@FamilyFilter NVARCHAR(4000) = NULL,
	@ItemFilter NVARCHAR(4000) = NULL,
	@IsTagOnFamily INT,
	@IsInitData INT,
	@IsShowFamily INT,
	@PageIndex INT,
	@PageSize INT,
	@TotalItemCount INT OUTPUT,
	@ItemCount INT OUTPUT,
	@FamilyCount INT OUTPUT
)
AS


SET @TotalItemCount=0
SET @ItemCount=0
SET @FamilyCount=0

DECLARE @CurrentWeek CHAR(8);
DECLARE @CurrentWeekDate NVARCHAR(32);
DECLARE @strSql nvarchar(MAX)=''
DECLARE @Table NVARCHAR(32)='BPEC_ItemSalesForecast'
IF(@IsInitData=1)
BEGIN
	SET @Table='BPEC_ItemSalesForecastInitial'
END

SET @CurrentWeek=(SELECT MAX(CurrentWeek) FROM dbo.BPEC_ItemSalesForecast(NOLOCK))
SET @CurrentWeekDate= dbo.GetDateByWeekIndex(@CurrentWeek)

CREATE TABLE #TempAllFamily
(
	[FamilyNo] [NVARCHAR](10) NOT NULL,
	[ItemCount] [INT] NOT NULL,
	[RowIndex] [INT] NOT NULL
) 


CREATE TABLE #TempItemIDList
(
	[ItemID] [INT] NOT NULL,
	[MainFamilyNo][NVARCHAR](10) NOT NULL,
	[RequestOrderQty] INT 
) 

CREATE TABLE #TempItemList
(
	[ItemID] [INT] NOT NULL,
	[ItemNo] [NVARCHAR] (30)  NOT NULL,
	[Loc] [CHAR] (3)  NOT NULL,
	[ItemStatus] [NVARCHAR] (10)  NULL,
	[MOQ] [INT] NULL,
	[ProductionLeadTime] [INT] NOT NULL,
	[OrderSpan] [INT] NOT NULL,
	[SafetyStockWOS] INT ,
	[FamilyNo] [NVARCHAR] (10)  NOT NULL,
	[Brand] [NVARCHAR] (50)  NULL,
	[Pattern] [NVARCHAR] (1500)  NULL,
	[Color] [NVARCHAR] (75)  NULL,
	[Division] [NVARCHAR] (75)  NULL,
	[DivisionCode] [NVARCHAR] (4)  NULL,
	[ProductCat] [NVARCHAR] (3)  NULL,
	[Category] [NVARCHAR] (75)  NULL,
	[Size] [NVARCHAR] (150)  NULL,
	[Description] [NVARCHAR] (250)  NULL,
	[SalesPrice] [DECIMAL] (9, 2) NULL,
	[RetailPrice] [DECIMAL] (9, 2) NULL,
	[StartSeason] [NVARCHAR] (20)   NULL,
	[ProducedIn] [NVARCHAR] (20)   NULL,
	[PM] [NVARCHAR] (50)   NULL,
	[CasePack] [INT] NULL,
	[Factory] [NVARCHAR] (50)   NULL,
	[Seasonal] [NVARCHAR] (50)   NULL,
	[AmzFineline] NVARCHAR(50),
	[Productionteam] [NVARCHAR] (50)   NULL,
	[TagOnFamily] [NVARCHAR] (10)   NULL,
	[ShippingPort] [NVARCHAR] (30)   NULL,
	[InitialDDfcst] [DECIMAL] (18, 2) NULL,
	[DCStartWeek] [INT] NULL,
	[DCEndWeek] [INT] NULL,
	[ReviewStatus] [BIT] NULL,
	[CycleCountInv] [INT] NULL,
	[SysSalesQty] [INT] NULL,
	[InputSalesQty] [INT] NULL,
	[WarningAvgSale][BIT] NULL,
	[OnSeasonSysSalesQty] [INT] NULL,
	[OnSeasonInputSalesQty] [INT] NULL,
	[ClassificationCode] NVARCHAR(4),
	[ProgramTier] NVARCHAR(128),
	[ProgramTierTooltip] NVARCHAR(1000),
	[LocPercentage] DECIMAL(3,2),
	[MainPOSCustomerIntransit] INT,
	[FamilyOrderOverMOQQty] INT NOT NULL,
	[AmzExclusive] BIT,
	[IsSeasonal] BIT,
	[SaleChangePercentage] DECIMAL(10,1),
	[WarningReview] BIT ,
	[ItemRowIndex] [INT] NOT NULL,
	[IsTopSalesItem] BIT
)



-- 先计算出New Item Sales Weeks 优化性能
SELECT ItemNo 
INTO #TempNewProductItem
FROM BPEC_ItemWeeklyBasicData(NOLOCK)
WHERE CurrentWeek=@CurrentWeek AND  IsNewProduct=1
GROUP BY ItemNo


SELECT Result.ItemNo, DATEDIFF(WEEK,dbo.GetDateByWeekIndex(MAX(Result.WeekID)),@CurrentWeekDate) NewItemSalesWeeks
INTO #TempNewItemSales
FROM
(
	SELECT #TempNewProductItem.ItemNo ,MIN(RealWeek) WeekID FROM dbo.BPEC_CusItemValidWeekSale(NOLOCK)
	INNER JOIN #TempNewProductItem ON #TempNewProductItem.ItemNo = BPEC_CusItemValidWeekSale.ItemNo
	WHERE WeekRealSaleQty>0 AND CustomerCode NOT IN(SELECT CustomerCode FROM dbo.BPEC_Customer WHERE IsPOSCustomer=1)
	GROUP BY #TempNewProductItem.ItemNo
	UNION ALL
	SELECT #TempNewProductItem.ItemNo ,MIN(WeekID) WeekID FROM dbo.BPEC_CustomerPOS(NOLOCK)
	INNER JOIN #TempNewProductItem ON #TempNewProductItem.ItemNo = BPEC_CustomerPOS.ItemNo
	WHERE POSQty>0
	GROUP BY #TempNewProductItem.ItemNo
)Result
GROUP BY Result.ItemNo

-- 插入没有历史销量的
INSERT INTO #TempNewItemSales
SELECT ItemNo,0 FROM #TempNewProductItem
WHERE NOT EXISTS(SELECT * FROM #TempNewItemSales WHERE #TempNewItemSales.ItemNo=#TempNewProductItem.ItemNo)



SET @strSql='SELECT BPEC_RetailItem.ItemID,BPEC_RetailItem.FamilyNo,TagOnFamily,RequestOrderQty INTO #TempFitlerItemList FROM dbo.BPEC_RetailItem(NOLOCK)
INNER JOIN '+@Table+'(NOLOCK) ON '+@Table+'.ItemNo = BPEC_RetailItem.ItemNo AND BPEC_RetailItem.Loc='+@Table+'.Loc AND isLeadTime=1
LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemWeeklyBasicData.ItemNo=BPEC_RetailItem.ItemNo 
	AND BPEC_ItemWeeklyBasicData.Loc=BPEC_RetailItem.Loc
LEFT JOIN BPEC_User_RetailItem(NOLOCK) ON BPEC_User_RetailItem.Item_ID=BPEC_RetailItem.ItemID
LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5
WHERE 1=1 '+@ItemFilter


IF @IsShowFamily=1
BEGIN

SET @strSql+='
INSERT INTO #TempFitlerItemList
SELECT BPEC_RetailItem.ItemID,BPEC_RetailItem.FamilyNo,TagOnFamily,RequestOrderQty  FROM dbo.BPEC_RetailItem(NOLOCK)
INNER JOIN '+@Table+'(NOLOCK) ON '+@Table+'.ItemNo = BPEC_RetailItem.ItemNo AND BPEC_RetailItem.Loc='+@Table+'.Loc AND isLeadTime=1
LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemWeeklyBasicData.ItemNo=BPEC_RetailItem.ItemNo 
	AND BPEC_ItemWeeklyBasicData.Loc=BPEC_RetailItem.Loc
LEFT JOIN BPEC_User_RetailItem(NOLOCK) ON BPEC_User_RetailItem.Item_ID=BPEC_RetailItem.ItemID
LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5
WHERE BPEC_RetailItem.FamilyNo IN(SELECT FamilyNo FROM #TempFitlerItemList) AND BPEC_RetailItem.ItemID NOT IN(SELECT ItemID FROM #TempFitlerItemList) '

END


IF @IsTagOnFamily=1
	BEGIN
		SET @strSql+='
		INSERT INTO #TempItemIDList
		SELECT DISTINCT ItemID,FamilyNo,RequestOrderQty FROM (
			--删选条件的item列表 
			SELECT ItemID,CASE WHEN ISNULL(TagOnFamily,'''')='''' THEN FamilyNo ELSE TagOnFamily END FamilyNo,RequestOrderQty FROM #TempFitlerItemList  

			UNION ALL

			--删选条件 对应的tag on family 的item列表
			SELECT BPEC_RetailItem.ItemID,#TempFitlerItemList.FamilyNo,'+@Table+'.RequestOrderQty FROM #TempFitlerItemList
			INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.TagOnFamily = #TempFitlerItemList.FamilyNo
			INNER JOIN '+@Table+'(NOLOCK) ON '+@Table+'.ItemNo = BPEC_RetailItem.ItemNo AND BPEC_RetailItem.Loc='+@Table+'.Loc AND isLeadTime=1
			LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemWeeklyBasicData.ItemNo=BPEC_RetailItem.ItemNo 
			LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5
				AND BPEC_ItemWeeklyBasicData.Loc=BPEC_RetailItem.Loc
			WHERE 1=1 '+@FamilyFilter+'

			UNION ALL

			--删选条件 对应的 被tag on family 的item列表
			SELECT BPEC_RetailItem.ItemID,#TempFitlerItemList.TagOnFamily,'+@Table+'.RequestOrderQty FROM #TempFitlerItemList
			INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.FamilyNo = #TempFitlerItemList.TagOnFamily
			INNER JOIN '+@Table+'(NOLOCK) ON '+@Table+'.ItemNo = BPEC_RetailItem.ItemNo AND BPEC_RetailItem.Loc='+@Table+'.Loc AND isLeadTime=1
			LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemWeeklyBasicData.ItemNo=BPEC_RetailItem.ItemNo 
				AND BPEC_ItemWeeklyBasicData.Loc=BPEC_RetailItem.Loc
			LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5
			WHERE 1=1 '+@FamilyFilter+'
		)Result'
	END
ELSE
	BEGIN
		SET @strSql+='INSERT INTO #TempItemIDList SELECT ItemID,#TempFitlerItemList.FamilyNo,RequestOrderQty FROM #TempFitlerItemList'
	END

EXEC(@strSql)


--第一步排序1位 group family 不考虑下单量
SELECT  CASE WHEN LEN(MainFamilyNo)>=1 THEN SUBSTRING(MainFamilyNo,1,1) ELSE MainFamilyNo END TopMainFamilyNo,
ROW_NUMBER() OVER(ORDER BY 
							CASE WHEN LEN(MainFamilyNo)>=1 THEN SUBSTRING(MainFamilyNo,1,1) ELSE MainFamilyNo END) MainRowIndex 
INTO #TempFamilyOrderMain
FROM #TempItemIDList
INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.ItemID = #TempItemIDList.ItemID
GROUP BY CASE WHEN LEN(MainFamilyNo)>=1 THEN SUBSTRING(MainFamilyNo,1,1) ELSE MainFamilyNo END



--第二步排序5位 group family 有下单量在前
SELECT  CASE WHEN LEN(#TempItemIDList.MainFamilyNo)>=5 THEN SUBSTRING(#TempItemIDList.MainFamilyNo,1,5) ELSE #TempItemIDList.MainFamilyNo END FirstMainFamilyNo,
ROW_NUMBER() OVER(ORDER BY  MainRowIndex,
							CASE WHEN MAX(RequestOrderQty)>0 THEN 1 ELSE 0 END DESC,
							CASE WHEN LEN(MainFamilyNo)>=5 THEN SUBSTRING(MainFamilyNo,1,5) ELSE MainFamilyNo END) FirstRowIndex 
INTO #TempFamilyOrderFirst
FROM #TempItemIDList
INNER JOIN #TempFamilyOrderMain ON #TempFamilyOrderMain.TopMainFamilyNo=(CASE WHEN LEN(#TempItemIDList.MainFamilyNo)>=1 THEN SUBSTRING(#TempItemIDList.MainFamilyNo,1,1) ELSE #TempItemIDList.MainFamilyNo END)
INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.ItemID = #TempItemIDList.ItemID
GROUP BY MainRowIndex,CASE WHEN LEN(#TempItemIDList.MainFamilyNo)>=5 THEN SUBSTRING(#TempItemIDList.MainFamilyNo,1,5) ELSE #TempItemIDList.MainFamilyNo END



--第三步排序6位 group family 有下单量在前  依赖第一步排序
SELECT CASE WHEN LEN(MainFamilyNo)>=6 THEN SUBSTRING(MainFamilyNo,1,6) ELSE MainFamilyNo END SecondMainFamilyNo,
ROW_NUMBER() OVER(ORDER BY	FirstRowIndex,
							CASE WHEN MAX(RequestOrderQty)>0 THEN 1 ELSE 0 END DESC,MIN(OrderbyId),
							CASE WHEN LEN(MainFamilyNo)>=6 THEN SUBSTRING(MainFamilyNo,1,6) ELSE MainFamilyNo END) SecondRowIndex 
INTO #TempFamilyOrderSecond
FROM #TempItemIDList
INNER JOIN #TempFamilyOrderFirst ON FirstMainFamilyNo=(CASE WHEN LEN(MainFamilyNo)>=5 THEN SUBSTRING(MainFamilyNo,1,5) ELSE MainFamilyNo END)
INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.ItemID = #TempItemIDList.ItemID
INNER JOIN 
(
	SELECT FamilyNo,MIN(OrderbyId) OrderbyId FROM dbo.BPEC_RetailItem(NOLOCK)
	INNER JOIN EECSystem.dbo.RTL_ECOMItemMaster(NOLOCK) ON BPEC_RetailItem.ItemNo=RTL_ECOMItemMaster.ItemNo AND BusinessUnit_ID=5
	INNER JOIN EECSystem.dbo.tblClassificationCode(NOLOCK) ON ClassificationCode=Code AND tblClassificationCode.BusinessUnit_ID = RTL_ECOMItemMaster.BusinessUnit_ID
	GROUP BY FamilyNo
)FamilyClassifcationOrderResult ON FamilyClassifcationOrderResult.FamilyNo = BPEC_RetailItem.FamilyNo  --ClassificationCode 排序
GROUP BY FirstRowIndex,CASE WHEN LEN(MainFamilyNo)>=6 THEN SUBSTRING(MainFamilyNo,1,6) ELSE MainFamilyNo END



--最终排序
INSERT INTO #TempAllFamily
SELECT MainFamilyNo,SUM(1) ItemCount, ROW_NUMBER() OVER(ORDER BY SecondRowIndex,MainFamilyNo ,
							CASE WHEN MAX(RequestOrderQty)>0 THEN 1 ELSE 0 END DESC) RowIndex 
FROM #TempItemIDList
INNER JOIN #TempFamilyOrderSecond ON SecondMainFamilyNo=(CASE WHEN LEN(MainFamilyNo)>=6 THEN SUBSTRING(MainFamilyNo,1,6) ELSE MainFamilyNo END)
INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.ItemID = #TempItemIDList.ItemID
GROUP BY MainFamilyNo,SecondRowIndex




--分页过滤
SELECT FamilyNo,RowIndex
INTO #TempFamily 
FROM #TempAllFamily WHERE RowIndex>(@PageIndex-1)*@PageSize AND RowIndex <=@PageIndex*@PageSize


-- Item 过滤
SET @strSql='
INSERT INTO #TempItemList
SELECT BPEC_RetailItem.ItemID,BPEC_RetailItem.ItemNo,BPEC_RetailItem.Loc,BPEC_RetailItem.ItemStatus,BPEC_RetailItem.MOQ,BPEC_RetailItem.ProductionLeadTime,BPEC_RetailItem.OrderSpan,0 SafetyStockWOS,BPEC_RetailItem.FamilyNo,
View_ItemMaster.BrandName,View_ItemMaster.Pattern,View_ItemMaster.Color,View_ItemMaster.CommCode,View_ItemMaster.CommCode,View_ItemMaster.ProductCat,View_ItemMaster.ProductCategoryDesc,
CASE WHEN LEN(View_ItemMaster.Size)>10 THEN SUBSTRING(View_ItemMaster.Size,1,10) ELSE View_ItemMaster.Size END,View_ItemMaster.Description,
BPEC_RetailItem.SalesPrice,BPEC_RetailItem.RetailPrice,BPEC_RetailItem.StartSeason,BPEC_RetailItem.ProducedIn,BPEC_RetailItem.PM,BPEC_RetailItem.CasePack,BPEC_RetailItem.Factory,
BPEC_RetailItem.Seasonal,AmzFineline,BPEC_RetailItem.Productionteam,BPEC_RetailItem.TagOnFamily,BPEC_RetailItem.ShippingPort,BPEC_RetailItem.InitialDDfcst,dbo.GetWeekIndex(BPEC_RetailItem.DCStartDate) DCStartWeek,
dbo.GetWeekIndex(BPEC_RetailItem.DCEndDate) DCEndWeek ,ReviewStatus,CycleCountInv,SysSalesQty,InputSalesQty,
(SELECT Warning FROM BPEC_ItemLocPartialAvgSale(NOLOCK) WHERE BPEC_ItemLocPartialAvgSale.ItemNo=BPEC_RetailItem.ItemNo AND BPEC_ItemLocPartialAvgSale.LocCode=BPEC_RetailItem.Loc AND IsMainPOSCustomer=0) WarningAvgSale,
ISNULL(OnSeasonSysSalesQty,0) OnSeasonSysSalesQty,OnSeasonInputSalesQty,ClassificationCode,ProgramTier,ProgramTierTooltip,LocPercentage,MainPOSCustomerIntransit,
(SELECT CASE WHEN SUM(TotalAllocation)>0 THEN SUM(TotalAllocation)-MAX(MOQ) ELSE 0 END FROM BPEC_ItemCalculativeProcess(NOLOCK) 
WHERE CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemCalculativeProcess.FamilyNo=BPEC_RetailItem.FamilyNo) FamilyOrderOverMOQQty,AmzExclusive,IsSeasonal,SaleChangePercentage,
CASE WHEN DATEADD(WEEK,3,LastReviewDate)>'''+ @CurrentWeekDate+''' THEN 0 ELSE 1 END WarningReview,
ROW_NUMBER() OVER(ORDER BY  RowIndex,CASE WHEN LEN(TagOnFamily)>0 THEN 1 ELSE 0 END,BPEC_RetailItem.FamilyNo,BPEC_RetailItem.ItemNo,BPEC_RetailItem.Loc) ItemRowIndex,
CASE WHEN EXISTS(
	SELECT * FROM dbo.BPEC_TopSalesItemList(NOLOCK) 
	WHERE BPEC_TopSalesItemList.ItemNo=BPEC_RetailItem.ItemNo  AND Type=1
) THEN 1 ELSE 0 END IsTopSalesItem
FROM dbo.BPEC_RetailItem(NOLOCK) 
INNER JOIN #TempFamily ON #TempFamily.FamilyNo =BPEC_RetailItem.FamilyNo '+(CASE WHEN @IsTagOnFamily=1 THEN ' OR #TempFamily.FamilyNo =BPEC_RetailItem.TagOnFamily ' ELSE '' END)+'
INNER JOIN '+@Table+' ON '+@Table+'.ItemNo = BPEC_RetailItem.ItemNo AND BPEC_RetailItem.Loc='+@Table+'.Loc AND isLeadTime=1
INNER JOIN #TempItemIDList on #TempItemIDList.ItemID=BPEC_RetailItem.ItemID
LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek='+ @CurrentWeek+' AND BPEC_ItemWeeklyBasicData.ItemNo=BPEC_RetailItem.ItemNo AND BPEC_ItemWeeklyBasicData.Loc=BPEC_RetailItem.Loc
LEFT JOIN BPEC_User_RetailItem(NOLOCK) ON BPEC_User_RetailItem.Item_ID=BPEC_RetailItem.ItemID
LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5'

EXEC(@strSql)





UPDATE #TempItemList SET 
	SafetyStockWOS=ISNULL(
				(
					SELECT SafetyStockWOS FROM dbo.BPEC_SafetyStockWOS(NOLOCK)
					INNER JOIN dbo.BPEC_SafetyStockWOSClassificationCode(NOLOCK) ON BPEC_SafetyStockWOS.ID =BPEC_SafetyStockWOSClassificationCode.BPEC_SafetyStockWOSID
					INNER JOIN dbo.BPEC_SafetyStockWOSProductCategory(NOLOCK) ON BPEC_SafetyStockWOS.ID =BPEC_SafetyStockWOSProductCategory.BPEC_SafetyStockWOSID
					INNER JOIN dbo.BPEC_SafetyStockWOSTier(NOLOCK) ON BPEC_SafetyStockWOS.ID = BPEC_SafetyStockWOSTier.BPEC_SafetyStockWOSID
					WHERE BPEC_SafetyStockWOS.FamilyDivision= #TempItemList.DivisionCode
						AND BPEC_SafetyStockWOSClassificationCode.ClassificationCode =#TempItemList.ClassificationCode
						AND BPEC_SafetyStockWOSProductCategory.ProductCategory= #TempItemList.ProductCat
						AND (CASE WHEN EXISTS(SELECT * FROM EECSystem.dbo.RTL_ECOMCustomerItem WHERE RTL_ECOMCustomerItem.ItemNo=#TempItemList.ItemNo AND ProgramTier IN(1,2)) THEN 1 ELSE 0 END)
						= dbo.BPEC_SafetyStockWOSTier.TierValue
				),
				(SELECT TOP 1 DefaultWOS FROM dbo.BPEC_SysConfig(NOLOCK))),
	OrderSpan=ISNULL(OrderSpanResult.OrderSpanWeek,(SELECT TOP 1 DefaultOrderSpanWeek FROM dbo.BPEC_SysConfig(NOLOCK)))
FROM #TempItemList
LEFT JOIN (
	SELECT FamilyClassificationCode.FamilyNo,tblClassificationCode.Code ClassificationCode FROM 
	(
		SELECT FamilyNo,MIN(OrderbyId) OrderbyId FROM dbo.BPEC_RetailItem(NOLOCK)
		INNER JOIN EECSystem.dbo.tblItemMaster(NOLOCK) ON BPEC_RetailItem.ItemNo=tblItemMaster.ItemNo AND BusinessUnit_ID=5
		INNER JOIN EECSystem.dbo.RTL_ECOMItemMaster(NOLOCK) ON tblItemMaster.ItemID=RTL_ECOMItemMaster.Item_ID 
		INNER JOIN EECSystem.dbo.tblClassificationCode(NOLOCK) ON ClassificationCode=Code AND tblClassificationCode.BusinessUnit_ID = tblItemMaster.BusinessUnit_ID
		GROUP BY FamilyNo
	)FamilyClassificationCode
	INNER JOIN EECSystem.dbo.tblClassificationCode(NOLOCK) ON tblClassificationCode.OrderbyId = FamilyClassificationCode.OrderbyId AND tblClassificationCode.BusinessUnit_ID = 5
)FamilyClassificationCode ON FamilyClassificationCode.FamilyNo = (CASE WHEN ISNULL(#TempItemList.TagOnFamily,'')!='' THEN #TempItemList.TagOnFamily ELSE #TempItemList.FamilyNo END)
LEFT JOIN 
(
	SELECT BPEC_OrderSpanWeek.*,ClassificationCode FROM dbo.BPEC_OrderSpanWeek(NOLOCK)
	INNER JOIN dbo.BPEC_OrderSpanWeekClassificationCode(NOLOCK) ON BPEC_OrderSpanWeek.ID =BPEC_OrderSpanWeekClassificationCode.BPEC_OrderSpanWeekID
)OrderSpanResult ON OrderSpanResult.FamilyDivision=
	(CASE WHEN ISNULL(#TempItemList.TagOnFamily,'')!='' THEN (SELECT TOP 1 Division FROM dbo.BPEC_RetailItem(NOLOCK) WHERE BPEC_RetailItem.FamilyNo=#TempItemList.TagOnFamily) ELSE #TempItemList.Division END)
		 AND OrderSpanResult.ClassificationCode = FamilyClassificationCode.ClassificationCode
LEFT JOIN 
(
	SELECT BPEC_SafetyStockWOS.*,ClassificationCode,ProductCategory FROM dbo.BPEC_SafetyStockWOS(NOLOCK)
	INNER JOIN dbo.BPEC_SafetyStockWOSClassificationCode(NOLOCK) ON BPEC_SafetyStockWOS.ID =BPEC_SafetyStockWOSClassificationCode.BPEC_SafetyStockWOSID
	INNER JOIN dbo.BPEC_SafetyStockWOSProductCategory(NOLOCK) ON BPEC_SafetyStockWOS.ID =BPEC_SafetyStockWOSProductCategory.BPEC_SafetyStockWOSID
)SafetyStockResult ON SafetyStockResult.FamilyDivision=#TempItemList.DivisionCode
	AND SafetyStockResult.ClassificationCode = #TempItemList.ClassificationCode
	AND SafetyStockResult.ProductCategory=#TempItemList.ProductCat


-- 插入MainPOSItem
INSERT INTO #TempItemList
SELECT 0 ItemID,BPEC_RetailItem.ItemNo,'ALL',BPEC_RetailItem.ItemStatus,BPEC_RetailItem.MOQ,BPEC_RetailItem.ProductionLeadTime,BPEC_RetailItem.OrderSpan,0 SafetyStockWOS,BPEC_RetailItem.FamilyNo,
View_ItemMaster.BrandName,View_ItemMaster.Pattern,View_ItemMaster.Color,View_ItemMaster.CommCode,View_ItemMaster.CommCode,View_ItemMaster.ProductCat,View_ItemMaster.ProductCategoryDesc,
CASE WHEN LEN(View_ItemMaster.Size)>10 THEN SUBSTRING(View_ItemMaster.Size,1,10) ELSE View_ItemMaster.Size END,View_ItemMaster.Description,
BPEC_RetailItem.SalesPrice,BPEC_RetailItem.RetailPrice,BPEC_RetailItem.StartSeason,BPEC_RetailItem.ProducedIn,BPEC_RetailItem.PM,BPEC_RetailItem.CasePack,BPEC_RetailItem.Factory,
BPEC_RetailItem.Seasonal,AmzFineline,BPEC_RetailItem.Productionteam,BPEC_RetailItem.TagOnFamily,BPEC_RetailItem.ShippingPort,BPEC_RetailItem.InitialDDfcst,dbo.GetWeekIndex(BPEC_RetailItem.DCStartDate) DCStartWeek,
dbo.GetWeekIndex(BPEC_RetailItem.DCEndDate) DCEndWeek ,ReviewStatus,0 CycleCountInv,SysSalesQty,InputSalesQty,
(SELECT Warning FROM BPEC_ItemLocPartialAvgSale(NOLOCK) WHERE BPEC_ItemLocPartialAvgSale.ItemNo=Result.ItemNo AND IsMainPOSCustomer=1) WarningAvgSale,
ISNULL(OnSeasonSysSalesQty,0) OnSeasonSysSalesQty,OnSeasonInputSalesQty,ClassificationCode,ProgramTier,ProgramTierTooltip,LocPercentage,MainPOSCustomerIntransit,0 FamilyOrderOverMOQQty,AmzExclusive,IsSeasonal,SaleChangePercentage, 
CASE WHEN DATEADD(WEEK,3,LastReviewDate)> @CurrentWeekDate THEN 0 ELSE 1 END WarningReview,
ItemRowIndex,
CASE WHEN EXISTS(
	SELECT * FROM dbo.BPEC_TopSalesItemList(NOLOCK) 
	WHERE BPEC_TopSalesItemList.ItemNo=BPEC_RetailItem.ItemNo  AND Type=2
) THEN 1 ELSE 0 END IsTopSalesItem
FROM (

	SELECT ItemNo,MAX(ItemID)ItemID, MIN(ItemRowIndex)ItemRowIndex FROM  #TempItemList
	GROUP BY ItemNo
)Result 
INNER JOIN BPEC_RetailItem(NOLOCK) ON BPEC_RetailItem.ItemID = Result.ItemID
LEFT JOIN BPEC_ItemWeeklyBasicData(NOLOCK) ON BPEC_ItemWeeklyBasicData.CurrentWeek=@CurrentWeek AND BPEC_ItemWeeklyBasicData.ItemNo=Result.ItemNo AND BPEC_ItemWeeklyBasicData.Loc='ALL'
LEFT JOIN EECSystem.dbo.View_ItemMaster(NOLOCK) ON View_ItemMaster.ItemNo=BPEC_RetailItem.ItemNo and View_ItemMaster.BusinessUnit_ID=5
WHERE EXISTS(SELECT * FROM dbo.BPEC_ItemSalesForecast(NOLOCK) WHERE BPEC_ItemSalesForecast.ItemNo=Result.ItemNo AND BPEC_ItemSalesForecast.Loc='ALL')




SELECT @FamilyCount= COUNT(1) FROM #TempAllFamily;
SELECT @TotalItemCount=SUM(ItemCount) FROM #TempAllFamily;
SELECT @ItemCount=COUNT(1) FROM #TempItemList



--查询结果
SET @strSql='
SELECT *, CONVERT(BIT, CASE WHEN  Fabric=1 AND isLeadTime=0 AND isFirstSchedule=0 AND isSecondSchedule=0 AND isOrderSpan=0 THEN 1 ELSE 0 END) FabricIncoming ,
(SELECT TOP 1 User_ID FROM dbo.BPEC_User_RetailItem(NOLOCK)
INNER JOIN dbo.BPEC_RetailItem(NOLOCK) ON ItemID=Item_ID
WHERE BPEC_RetailItem.ItemNo=#TempItemList.ItemNo) PlannerID
FROM #TempItemList
INNER JOIN dbo.'+@Table+'(NOLOCK) ON '+@Table+'.ItemNo = #TempItemList.ItemNo AND #TempItemList.Loc='+@Table+'.Loc
ORDER BY ItemRowIndex,#TempItemList.Loc'

EXEC(@strSql)

DROP TABLE #TempAllFamily
DROP TABLE #TempFamily
DROP TABLE #TempItemList
DROP TABLE #TempItemIDList


GO