USE [ZhiHuiTongForSpuSplit]
GO

/*****************************************
从产品资料导入非spu
*****************************************/

ALTER PROCEDURE [dbo].[UP_ZhiHuiTong_ItemSKUImport_V5]
AS

--DELETE FROM dbo.WebsiteCategories
--DELETE FROM  dbo.PropertyDescriptions
--DELETE FROM  dbo.PropertyValueDescriptions
--DELETE FROM  dbo.ItemPropertyDescriptions 
--DELETE FROM  dbo.ItemProperties
--DELETE FROM  dbo.ItemSKUProperties
--DELETE FROM  dbo.ItemSKUPropertyDescription
--DELETE FROM  dbo.PropertyValues
--DELETE FROM  dbo.Properties
--DELETE FROM  dbo.ItemDescriptions
--DELETE FROM  dbo.ItemVendors
--DELETE FROM  dbo.ItemWarehouses
--DELETE FROM  dbo.ItemPriceSettings
--DELETE FROM  dbo.Items
--DELETE FROM  dbo.ItemBaseInfos
--DELETE FROM  dbo.ItemImages
--DELETE FROM  dbo.WebsiteCategories
--DELETE FROM  dbo.CategoryPropertyTemplateDescriptions
--DELETE FROM  dbo.CategoryPropertyTemplates
--DELETE FROM  dbo.Warehouses

DECLARE @TotalCount INT = 0
DECLARE @Index INT  = 1

DECLARE @CreatedItems TABLE
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ItemId INT NOT NULL,
	SKU NVARCHAR(32) NOT NULL
)

BEGIN TRY
BEGIN TRANSACTION
DECLARE @WebsiteCategories TABLE
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CategoryCode] [nvarchar](32) NOT NULL,
	[CreationTime] [datetime2](7) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[Name] [nvarchar](64) NOT NULL,
	[ParentCategoryId] [int] NULL,
	[Priority] [int] NOT NULL
)

DECLARE @SourceTable TABLE
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[SKU] [nvarchar](255) NULL,
	[SPU] [float] NULL,
	[采购名称] [nvarchar](255) NULL,
	[产品状态] [nvarchar](255) NULL,
	[报价] [float] NULL,
	[侵权原因] [nvarchar](255) NULL,
	[禁售平台] [nvarchar](255) NULL,
	[物流属性] [float] NULL,
	[仓别] [nvarchar](255) NULL,
	[英文标题] [nvarchar](255) NULL,
	[速卖通英文标题] [nvarchar](255) NULL,
	[英文描述] [nvarchar](max) NULL,
	[库存可用数量] [float] NULL,
	[长] [float] NULL,
	[宽] [float] NULL,
	[高] [float] NULL,
	[重量(g)] [float] NULL,
	[打包长] [float] NULL,
	[打包宽] [float] NULL,
	[打包高] [float] NULL,
	[打包重量(g)] [float] NULL,
	[一级分类] [nvarchar](255) NULL,
	[二级分类] [nvarchar](255) NULL,
	[三级分类] [nvarchar](255) NULL,
	[海关编码] [nvarchar](255) NULL,
	[关键词] [nvarchar](255) NULL,
	[图片1] [nvarchar](255) NULL,
	[图片2] [nvarchar](255) NULL,
	[图片3] [nvarchar](255) NULL,
	[图片4] [nvarchar](255) NULL,
	[图片5] [nvarchar](255) NULL,
	[图片6] [nvarchar](255) NULL,
	[图片7] [nvarchar](255) NULL,
	[图片8] [nvarchar](255) NULL,
	[图片9] [nvarchar](255) NULL,
	[图片10] [nvarchar](255) NULL,
	[图片11] [nvarchar](255) NULL,
	[图片12] [nvarchar](255) NULL,
	[图片13] [nvarchar](255) NULL,
	[图片14] [nvarchar](255) NULL,
	[图片15] [nvarchar](255) NULL,
	[图片16] [nvarchar](255) NULL,
	[图片17] [nvarchar](255) NULL,
	[图片18] [nvarchar](255) NULL,
	[图片19] [nvarchar](255) NULL,
	[图片20] [nvarchar](255) NULL,
	[图片21] [nvarchar](255) NULL,
	[SKUColor] [nvarchar](50) NULL,
	[SKUSize] [nvarchar](50) NULL
)

INSERT INTO @SourceTable
(
	[SKU]
	,[SPU]
	,[采购名称]
	,[产品状态]
	,[报价]
	,[侵权原因]
	,[禁售平台]
	,[物流属性]
	,[仓别]
	,[英文标题]
	,[速卖通英文标题]
	,[英文描述]
	,[库存可用数量]
	,[长]
	,[宽]
	,[高]
	,[重量(g)]
	,[打包长]
	,[打包宽]
	,[打包高]
	,[打包重量(g)]
	,[一级分类]
	,[二级分类]
	,[三级分类]
	,[海关编码]
	,[关键词]
	,[图片1]
	,[图片2]
	,[图片3]
	,[图片4]
	,[图片5]
	,[图片6]
	,[图片7]
	,[图片8]
	,[图片9]
	,[图片10]
	,[图片11]
	,[图片12]
	,[图片13]
	,[图片14]
	,[图片15]
	,[图片16]
	,[图片17]
	,[图片18]
	,[图片19]
	,[图片20]
	,[图片21]
	,[SKUColor]
	,[SKUSize]
)
SELECT
	A.SKU
	,A.[SPU]
	,A.[采购名称]
	,[产品状态]
	,[报价]
	,[侵权原因]
	,[禁售平台]
	,[物流属性]
	,[仓别]
	,[英文标题]
	,[速卖通英文标题]
	,[英文描述]
	,[库存可用数量]
	,[长]
	,[宽]
	,[高]
	,[重量(g)]
	,[打包长]
	,[打包宽]
	,[打包高]
	,[打包重量(g)]
	,[一级分类]
	,[二级分类]
	,[三级分类]
	,[海关编码]
	,[关键词]
	,[图片1]
	,[图片2]
	,[图片3]
	,[图片4]
	,[图片5]
	,[图片6]
	,[图片7]
	,[图片8]
	,[图片9]
	,[图片10]
	,[图片11]
	,[图片12]
	,[图片13]
	,[图片14]
	,[图片15]
	,[图片16]
	,[图片17]
	,[图片18]
	,[图片19]
	,[图片20]
	,[图片21]
	,A.[SKUColor]
	,A.[SKUSize]
FROM sellbillion.[dbo].[产品资料] A
WHERE [一级分类] IS NOT NULL
		AND [二级分类] IS NOT NULL
		AND [三级分类] IS NOT NULL
		AND 英文标题 IS NOT NULL
		AND NOT EXISTS(
			SELECT TOP 1 1
			FROM ZhiHuiTongForSpuSplit.dbo.Items I WITH(NOLOCK)
			WHERE I.SKU = A.SKU
		)

IF EXISTS (
	SELECT * FROM tempdb.dbo.sysobjects 
	WHERE id = object_id(N'tempdb..#WebsiteCategoryTable') AND TYPE = 'U'
)
BEGIN
	DROP TABLE #WebsiteCategoryTable
END

CREATE TABLE #WebsiteCategoryTable
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CategoryCode NVARCHAR(32) NOT NULL,
	ParentCategoryId INT,
	Name NVARCHAR(64) NOT NULL
)

DECLARE @OrgWebsiteCategory TABLE
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name NVARCHAR(64) NOT NULL
)

INSERT INTO @OrgWebsiteCategory(Name)
(
	SELECT DISTINCT
		[一级分类]
	FROM @SourceTable
	WHERE [一级分类] NOT IN(
		SELECT Name
		FROM ZhiHuiTongForSpuSplit.[dbo].[WebsiteCategories]
		WHERE ParentCategoryId IS NULL
	)
)

SELECT @TotalCount = @@ROWCOUNT

SET @Index = 1
DECLARE @TempCategoryCode VARCHAR(20)
DECLARE @TempCategoryName NVARCHAR(64)

DECLARE @MaxCategoryCode INT
SELECT TOP(1)@MaxCategoryCode = CAST(CategoryCode AS INT)
FROM ZhiHuiTongForSpuSplit.[dbo].[WebsiteCategories]
WHERE ParentCategoryId IS NULL
ORDER BY Id DESC

SET @MaxCategoryCode = ISNULL(@MaxCategoryCode,0)

WHILE @Index <= @TotalCount
BEGIN
	SET @MaxCategoryCode = @MaxCategoryCode + 1
	SET @TempCategoryCode = ZhiHuiTongForSpuSplit.dbo.PadLeft(CAST(@MaxCategoryCode AS VARCHAR(10)),'0',2);
	SELECT TOP(1) @TempCategoryName = Name FROM @OrgWebsiteCategory WHERE ID = @Index

	INSERT INTO #WebsiteCategoryTable(CategoryCode,Name)
	VALUES(@TempCategoryCode,@TempCategoryName)

	SET  @Index = @Index + 1                                                                                                                                                                                                                                                                                                                                                                                                                                                          
END

--INSERT First Level WebsiteCategories
INSERT INTO ZhiHuiTongForSpuSplit.dbo.WebsiteCategories
(
	CategoryCode,
	Name,
	DisplayName,
	Priority,
	ParentCategoryId,
	IsActive,
	IsDeleted,
	CreationTime
)
SELECT
	CategoryCode,
	Name,
	Name,
	0,
	ParentCategoryId,
	1,
	0,
	GETDATE()
FROM #WebsiteCategoryTable
PRINT '一级分类处理完成'
--第二级分类处理
TRUNCATE TABLE #WebsiteCategoryTable
INSERT INTO #WebsiteCategoryTable(CategoryCode,ParentCategoryId,Name)
SELECT
	A.CategoryCode --一级分类Code
	,A.ID
	,T.[二级分类]
FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories A
INNER JOIN(
	SELECT DISTINCT
		[二级分类],
		[一级分类]
	FROM @SourceTable
) T
ON A.Name = T.一级分类
AND LEN(A.CategoryCode) = 2
AND T.[二级分类] NOT IN(
	SELECT Name
	FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories WITH(NOLOCK)
	WHERE ParentCategoryId = 
	(
		SELECT TOP(1) Id 
		FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories WITH(NOLOCK)
		WHERE DisplayName = T.一级分类
	)
)
ORDER BY A.CategoryCode

SET @TotalCount = @@ROWCOUNT
SET @Index = 1
DECLARE @LastCategoryCode NVARCHAR(32)=''
DECLARE @BuildCategoryCode NVARCHAR(32)
DECLARE @TempParentCategoryID INT
SET @TempCategoryCode = ''
SET @MaxCategoryCode = 0

WHILE(@Index <= @TotalCount)
BEGIN
	SELECT TOP(1) 
		@TempCategoryCode = CategoryCode,
		@TempParentCategoryID = ParentCategoryId
	FROM #WebsiteCategoryTable
	WHERE ID = @Index

	IF @LastCategoryCode <> @TempCategoryCode
	BEGIN
		SELECT TOP(1)
			@MaxCategoryCode = CAST(SUBSTRING(CategoryCode, 3, 2) AS INT)
		FROM ZhiHuiTongForSpuSplit.[dbo].[WebsiteCategories]
		WHERE ParentCategoryId = @TempParentCategoryID
			AND LEN(CategoryCode) = 4 --二级
		ORDER BY Id DESC

		SET @MaxCategoryCode = ISNULL(@MaxCategoryCode,0)%1
	END

	SET @MaxCategoryCode = @MaxCategoryCode + 1
	SELECT @MaxCategoryCode

	SET @LastCategoryCode = @TempCategoryCode
	SET @BuildCategoryCode = CONCAT(@TempCategoryCode,ZhiHuiTongForSpuSplit.dbo.PadLeft(CAST(@MaxCategoryCode AS VARCHAR(10)),'0',2));

	UPDATE #WebsiteCategoryTable
	SET CategoryCode = @BuildCategoryCode
	WHERE ID = @Index

	SET @Index  = @Index + 1
END

INSERT INTO ZhiHuiTongForSpuSplit.dbo.WebsiteCategories
(
	CategoryCode,
	Name,
	DisplayName,
	Priority,
	ParentCategoryId,
	IsActive,
	IsDeleted,
	CreationTime
)
SELECT
	CategoryCode,
	Name,
	Name,
	0,
	ParentCategoryId,
	1,
	0,
	GETDATE()
FROM #WebsiteCategoryTable
PRINT '二级分类处理完成'
--第三级分类处理
TRUNCATE TABLE #WebsiteCategoryTable
INSERT INTO #WebsiteCategoryTable(CategoryCode,ParentCategoryId,Name)
SELECT
	A.CategoryCode
	,A.ID
	,T.[三级分类]
FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories A
INNER JOIN(
	SELECT DISTINCT
		[三级分类],
		[二级分类],
		[一级分类]
	FROM @SourceTable
) T
ON A.Name = T.二级分类
	AND LEN(A.CategoryCode) = 4
	AND A.ParentCategoryId = 
		(
			SELECT TOP(1) Id 
			FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories WITH(NOLOCK)
			WHERE DisplayName = T.一级分类
		)
	AND T.[三级分类] NOT IN(
		SELECT Name
		FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories WITH(NOLOCK)
		WHERE Name = T.三级分类
			AND ParentCategoryID = A.ID
)
ORDER BY A.CategoryCode

SET @TotalCount = @@ROWCOUNT
SET @Index = 1
SET @LastCategoryCode = ''
SET @BuildCategoryCode = NULL
SET @TempCategoryCode = NULL
SET @MaxCategoryCode = 0
SET @TempParentCategoryID = NULL
WHILE(@Index <= @TotalCount)
BEGIN
	SELECT TOP(1) 
		@TempCategoryCode = CategoryCode,
		@TempParentCategoryID = ParentCategoryId
	FROM #WebsiteCategoryTable
	WHERE ID = @Index

	IF @LastCategoryCode <> @TempCategoryCode
	BEGIN
		SELECT TOP(1)
			@MaxCategoryCode = CAST(SUBSTRING(CategoryCode, 5, 2) AS INT)
		FROM ZhiHuiTongForSpuSplit.[dbo].[WebsiteCategories]
		WHERE ParentCategoryId = @TempParentCategoryID
			AND LEN(CategoryCode) = 6 --三级
		ORDER BY Id DESC

		SET @MaxCategoryCode = ISNULL(@MaxCategoryCode,0)
	END
	SELECT @MaxCategoryCode

	SET @MaxCategoryCode = @MaxCategoryCode + 1

	SET @LastCategoryCode = @TempCategoryCode

	SET @BuildCategoryCode = CONCAT(@TempCategoryCode,ZhiHuiTongForSpuSplit.dbo.PadLeft(CAST(@MaxCategoryCode AS VARCHAR(10)),'0',2));

	UPDATE #WebsiteCategoryTable
	SET CategoryCode = @BuildCategoryCode
	WHERE ID = @Index

	SET @Index  = @Index  + 1
END

INSERT INTO ZhiHuiTongForSpuSplit.dbo.WebsiteCategories
(
	CategoryCode,
	Name,
	DisplayName,
	Priority,
	ParentCategoryId,
	IsActive,
	IsDeleted,
	CreationTime
)
SELECT
	CategoryCode,
	Name,
	Name,
	0,
	ParentCategoryId,
	1,
	0,
	GETDATE()
FROM #WebsiteCategoryTable
PRINT '三级分类处理完成'

DECLARE @AllCategories TABLE
(
	CategoryId INT NOT NULL,
	CategoryCode NVARCHAR(32) NOT NULL,
	ThirdCategoryName NVARCHAR(64) NOT NULL,
	SecondCategoryName NVARCHAR(64) NOT NULL,
	FirstCategoryName NVARCHAR(64) NOT NULL
)
;WITH cte_child(id,CategoryCode,Name,ParentCategoryID,level)
AS
(
    SELECT id,CategoryCode,name,parentcategoryId,0 as level
    FROM dbo.websitecategories WITH(NOLOCK)
    WHERE parentCategoryID IS NULL
    UNION ALL
    SELECT a.id,a.CategoryCode,a.name,a.parentcategoryId,b.level+1
    FROM dbo.websitecategories a
    INNER JOIN cte_child b
   		ON a.parentCategoryID = b.id
)

INSERT INTO @AllCategories
(
	CategoryId,
	CategoryCode,
	ThirdCategoryName,
	SecondCategoryName,
	FirstCategoryName
)
SELECT
	c.Id AS CategoryId,
	c.CategoryCode,
	c.Name AS ThirdCategoryName,
	B.Name AS SecondCategoryName,
	a.Name AS FirstCategoryName
FROM cte_child a
INNER JOIN cte_child b
	ON a.Id =  b.ParentCategoryID
INNER JOIN cte_child c
	ON b.Id = C.ParentCategoryID

DECLARE @AdminUserID BIGINT
DECLARE @BrandID INT
SELECT TOP(1) @AdminUserID = ID FROM ZhiHuiTongForSpuSplit.dbo.Users

SELECT TOP(1) @BrandID = Id FROM ZhiHuiTongForSpuSplit.dbo.Brands WITH(NOLOCK)

INSERT INTO dbo.Items
(
	SKU,
	CustomerSKU,
	Title,
	Image,
	ItemTotalCount,
	IsActive,
	ActiveTime,
	ActiveUserId,
	BrandId,
	WebsiteCategoryCode,
	HasBattery,
	BatteryType,
	BatteryInstallType,
	ItemActivityType,
	ItemImitationType,
	QualityInspectionPercent,
	QualityInspectionStandard,
	ProductCertification,
	PurchaserId,
	DeveloperId,
	ProductManagerId,
	ImageManagerId,
	ProductManagerVendorId,
	ItemOwnerType,
	Manufacturer,
	ItemApproveStatus,
	TenantId,
	IsDeleted,
	CreationTime,
	CreatorUserId
)
OUTPUT
	INSERTED.Id,
	INSERTED.SKU
INTO @CreatedItems(ItemId,SKU)
SELECT
	SKU,
	SUBSTRING(REPLACE(CONVERT(VARCHAR(38),NEWID()),'-',''),1,16),
	SUBSTRING(英文标题,1,256),
	图片1,
	库存可用数量,
	1,
	GETDATE(),
	@AdminUserID,
	@BrandID,
	(SELECT TOP(1) CategoryCode FROM @AllCategories 
		WHERE FirstCategoryName = 一级分类 
			AND SecondCategoryName = 二级分类
			AND ThirdCategoryName = 三级分类),
	0,
	NULL,
	NULL,
	0,
	0,
	0.9,
	'ISO-9001',
	'ISO-9001',
	@AdminUserID,
	@AdminUserID,
	@AdminUserID,
	@AdminUserID,
	NULL,
	0,
	NULL,
	0,
	1,
	0,
	GETDATE(),
	@AdminUserID
FROM @SourceTable

--Init Description
DECLARE @Indate DATETIME  = GETDATE()
INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemDescriptions
(
	ItemId,
	LanguageCode,
	Title,
	SubTitle,
	Tags,
	Detail,
	MobileDetail,
	CreationTime,
	IsDeleted,
	TenantId
)
SELECT DISTINCT
	B.ItemId,
	'zh-Hans',
	SUBSTRING(A.英文标题,1,256),    --SUBSTRING(A.英文标题,1,256),
	A.采购名称,
	SUBSTRING(A.关键词,1,256),
	A.英文描述,
	SUBSTRING(A.英文描述,1,1024),
	@Indate,
	0,
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU

PRINT 'Description处理完成'

--ItemImages
INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemImages
(
	ItemId,
	ImagePath,
	Priority,
	CreationTime,
	IsDeleted,
	IsPrimary
)
SELECT DISTINCT
	A.ID,
	IMG.图片,
	0,
	@Indate,
	0,
	CASE WHEN IMG.Series = '图片1' 
			AND NOT EXISTS(
				SELECT TOP 1 1 
				FROM ZhiHuiTongForSpuSplit.dbo.ItemImages WHERE IsPrimary=1 AND ItemId = A.ItemID
			) 
		THEN 1 
	ELSE 0 END
FROM @CreatedItems A
INNER JOIN (
	SELECT  三级分类,SKU,SPU,图片=value,Series=attribute
	FROM (SELECT * FROM @SourceTable)a
	  UNPIVOT
	  (
		value FOR attribute IN(
		图片1, 图片2,图片3,图片4,图片5)
	  ) AS UPV
) IMG
	ON IMG.SKU = A.SKU
WHERE IMG.图片 IS NOT NULL

PRINT 'IMage处理完成'
INSERT INTO dbo.ItemCustoms
(
	ItemId,
	CustomsCode,
	CustomsMaterial,
	CustomsLength,
	CustomsWidth,
	CustomsHeight,
	CustomsPackagePieces,
	CustomsNetWeight,
	TenantId,
	IsDeleted,
	CreatorUserId,
	CreationTime
)
SELECT
	B.ItemId,
	A.海关编码,
	'金属混合/织物',
	A.打包长,
	A.打包宽,
	A.打包高,
	10,
	A.[打包重量(g)],
	1,
	0,
	@AdminUserId,
	GETDATE()
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU

PRINT 'ItemCustoms处理完成'

INSERT INTO dbo.ItemDimensions
(
	ItemId,
	Length,
	Width,
	Height,
	PackageLength,
	PackageWidth,
	PackageHeight,
	NetWeight,
	GrossWeight,
	PackageWeight,
	TenantId,
	CreatorUserId,
	CreationTime,
	IsDeleted
)
SELECT
	B.ItemId,
	A.长,
	A.宽,
	A.高,
	A.打包长,
	A.打包宽,
	A.打包高,
	A.[重量(g)],
	A.[重量(g)]*1.1,
	A.[打包重量(g)],
	1,
	@AdminUserId,
	GETDATE(),
	0
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU

PRINT 'ItemDimensions处理完成'

--Init仓库
DECLARE @WarehouseTempTable TABLE
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	WarehouseName NVARCHAR(32) NOT NULL 
)

INSERT INTO @WarehouseTempTable(WarehouseName)
SELECT DISTINCT 仓别
FROM @SourceTable
WHERE 仓别 IS NOT NULL 
	AND 仓别 <> ''
	AND 仓别 NOT IN(
		SELECT Name
		FROM ZhiHuiTongForSpuSplit.dbo.Warehouses WITH(NOLOCK)
	)

SET @TotalCount = @@ROWCOUNT
SET @Index = 1
DECLARE @WarehouseCode VARCHAR(10)
DECLARE @WarehouseName NVARCHAR(32)
DECLARE @MaxWarehouseCode INT

SELECT TOP(1) @MaxWarehouseCode = CAST(Code AS INT)
FROM ZhiHuiTongForSpuSplit.dbo.Warehouses WITH(NOLOCK)
ORDER BY ID DESC

SET @MaxWarehouseCode = ISNULL(@MaxWarehouseCode,0) 

WHILE @Index <= @TotalCount
BEGIN
	SET @MaxWarehouseCode = @MaxWarehouseCode + 1
	SET @WarehouseCode = ZhiHuiTongForSpuSplit.dbo.PadLeft(CAST(@MaxWarehouseCode AS VARCHAR(10)),'0',4)
	SELECT TOP(1) @WarehouseName = WarehouseName
	FROM @WarehouseTempTable
	WHERE ID  = @Index

	INSERT INTO ZhiHuiTongForSpuSplit.dbo.Warehouses
	(
		Name,
		DisplayName,
		WarehouseType,
		IsDeleted,
		IsVirtual,
		CreationTime,
		Code,
		CountryCode,
		TenantId
	)
	SELECT
		@WarehouseName,
		@WarehouseName,
		0,
		0,
		0,
		GETDATE(),
		@WarehouseCode,
		'CN',
		1

	SET  @Index = @Index + 1
END
PRINT '仓库处理完成'

INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemWarehouses
(
	WarehouseId,
	ItemId,
	ItemCount,
	IsDeleted,
	TenantId,
	CreationTime
)
SELECT
	C.ID,
	B.ItemId,
	A.库存可用数量,
	0,
	1,
	GETDATE()
FROM @SourceTable A	
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU
INNER JOIN ZhiHuiTongForSpuSplit.dbo.Warehouses C
	ON A.仓别 = C.Name

PRINT '商品仓库库存处理完成'

INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemVendors
(
	VendorId,
	ItemId,
	CreationTime,
	IsDefault,
	TenantId,
	IsDeleted
)
SELECT
	(SELECT TOP(1) ID FROM ZhiHuiTongForSpuSplit.dbo.Vendors),
	B.ItemID,
	GETDATE(),
	1,
	1,
	0
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU

PRINT '供应商处理完成'

--ItemPriceSetting
INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemPriceSettings
(
	ItemId,
	Cost,
	Price,
	OriginalPrice,
	CurrencyCode,
	CreationTime,
	IsDeleted,
	TenantId
)
SELECT
	B.ItemId,
	A.报价,
	A.报价,
	A.报价,
	'RMB',
	GETDATE(),
	0,
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU

--Property
DECLARE @TempPropertyId INT

SELECT TOP(1) @TempPropertyId = Id
FROM ZhiHuiTongForSpuSplit.dbo.Properties WITH(NOLOCK)
WHERE PropertyCode = 'SKUSize'

IF @TempPropertyId IS NULL
BEGIN
	INSERT INTO ZhiHuiTongForSpuSplit.dbo.Properties
	(
		PropertyCode,
		IsDeleted,
		CreationTime
	)
	VALUES('SKUSize',0,GETDATE())

	INSERT INTO ZhiHuiTongForSpuSplit.dbo.PropertyDescriptions
	(
		PropertyId,
		LanguageCode,
		DisplayName,
		IsDeleted,
		CreationTime
	)
	SELECT
		ID,
		'zh-Hans',
		'尺寸',
		0,
		GETDATE()
	FROM ZhiHuiTongForSpuSplit.dbo.Properties
	WHERE PropertyCode IN('SKUSize')
END

SET @TempPropertyId = NULL
SELECT TOP(1) @TempPropertyId = Id
FROM ZhiHuiTongForSpuSplit.dbo.Properties WITH(NOLOCK)
WHERE PropertyCode = 'SKUColor'

IF @TempPropertyId IS NULL
BEGIN
	INSERT INTO ZhiHuiTongForSpuSplit.dbo.Properties
	(
		PropertyCode,
		IsDeleted,
		CreationTime
	)
	VALUES('SKUColor',0,GETDATE())

	INSERT INTO ZhiHuiTongForSpuSplit.dbo.PropertyDescriptions
	(
		PropertyId,
		LanguageCode,
		DisplayName,
		IsDeleted,
		CreationTime
	)
	SELECT
		ID,
		'zh-Hans',
		'颜色',
		0,
		GETDATE()
	FROM ZhiHuiTongForSpuSplit.dbo.Properties
	WHERE PropertyCode IN('SKUColor')
END

--ItemProperty
INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemProperties
(
	ItemId,
	PropertyId,
	PropertyValueId,
	PropertyDisplayType,
	IsDeleted,
	Priority,
	CreationTime,
	TenantId
)
SELECT
	B.ItemId,
	(SELECT TOP 1 Id FROM dbo.Properties WITH(NOLOCK) WHERE PropertyCode = 'SKUSize'),
	NULL,
	0,
	0,
	0,
	GETDATE(),
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU
WHERE A.SKUSize IS NOT NULL

INSERT INTO ZhiHuiTongForSpuSplit.dbo.ItemProperties
(
	ItemId,
	PropertyId,
	PropertyValueId,
	PropertyDisplayType,
	IsDeleted,
	Priority,
	CreationTime,
	TenantId
)
SELECT
	B.ItemId,
	(SELECT TOP 1 Id FROM dbo.Properties WITH(NOLOCK) WHERE PropertyCode = 'SKUColor'),
	NULL,
	0,
	0,
	0,
	GETDATE(),
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU
WHERE A.SKUColor IS NOT NULL

INSERT INTO dbo.ItemPropertyDescriptions
(
	ItemPropertyId,
	UserInputedValue,
	IsDeleted,
	LanguageCode,
	CreationTime,
	TenantId
)
SELECT
	C.Id,
	A.SKUSize,
	0,
	'zh-Hans',
	@InDate,
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU
INNER JOIN dbo.ItemProperties C WITH(NOLOCK)
	ON C.ItemId = B.Id
INNER JOIN dbo.Properties D WITH(NOLOCK)
	ON C.PropertyId = D.Id
WHERE A.SKUSize IS NOT NULL
	AND D.PropertyCode = 'SKUSize'

INSERT INTO dbo.ItemPropertyDescriptions
(
	ItemPropertyId,
	UserInputedValue,
	IsDeleted,
	LanguageCode,
	CreationTime,
	TenantId
)
SELECT
	C.Id,
	A.SKUColor,
	0,
	'zh-Hans',
	@InDate,
	1
FROM @SourceTable A
INNER JOIN @CreatedItems B
	ON A.SKU = B.SKU
INNER JOIN dbo.ItemProperties C WITH(NOLOCK)
	ON C.ItemId = B.Id
INNER JOIN dbo.Properties D WITH(NOLOCK)
	ON C.PropertyId = D.Id
WHERE A.SKUColor IS NOT NULL
	AND D.PropertyCode = 'SKUColor'

INSERT INTO ZhiHuiTongForSpuSplit.dbo.CategoryPropertyTemplates
(
	WebSiteCategoryId,
	PropertyId,
	PropertyType,
	PropertyDisplayType,
	IsSKUProperty,
	IsRequired,
	IsAllowUserInputed,
	CreationTime,
	Priority,
	IsInSearch,
	IsDeleted
)
SELECT
	B.ID,
	C.ID,
	0,
	0,
	1,
	1,
	1,
	GETDATE(),
	0,
	1,
	0
FROM ZhiHuiTongForSpuSplit.dbo.WebsiteCategories B WITH(NOLOCK)
CROSS APPLY ZhiHuiTongForSpuSplit.dbo.Properties C
WHERE C.PropertyCode IN('SKUColor','SKUSize')
	AND B.ID NOT IN(
		SELECT WebSiteCategoryId
		FROM ZhiHuiTongForSpuSplit.dbo.CategoryPropertyTemplates WITH(NOLOCK)
	)
	
COMMIT TRANSACTION
END TRY
BEGIN CATCH 

SELECT ERROR_Message()

ROLLBACK TRANSACTION

END CATCH