![]()
USE [MiLaiShopTemp]
GO
/****** Object: StoredProcedure [dbo].[StoreElementTransfer] Script Date: 08/03/2016 09:10:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--导入店铺元素[LayerTB] TextElementTB ProductElementTB AdvertElementTB TextLinkElementTB SublineElementTB BlankElement SearchElement
/*广告元素太啰嗦 先不导*/
ALTER PROCEDURE [dbo].[StoreElementTransfer]
AS
declare @www varchar(100)--商户域名
declare @m varchar(100)--页面生成码
declare @t varchar(100)--店铺元素类型
declare @num int--元素顺序 (已废弃该变量)
declare @sort_id int=0
declare @htm varchar(2000)--元素内容
declare @sys varchar(2000)--元素属性?
declare @create_time datetime=getdate()
declare get_attr cursor for
select www,m,t,num,htm,[sys] from [BingSNS_Client].[dbo].[BingSNS_DivID]
BEGIN
begin transaction
open get_attr
fetch next from get_attr into @www,@m,@t,@num,@htm,@sys print('@www:')print(@www)
WHILE @@FETCH_STATUS = 0
begin
--根据商户域名获取StoreId
declare @store_id int=0
declare get_store_id cursor for
select ID from [MiLaiShopTemp].[dbo].[StoreTB]
where www=@www
open get_store_id
fetch get_store_id into @store_id --print('@store_id:')print(@store_id)
close get_store_id
DEALLOCATE get_store_id
--根据店铺ID和页面生成码获取PageID
declare @page_id int=0
declare get_page_id cursor for
select id from [MiLaiShopTemp].[dbo].[PageTB]
where StoreTBID=@store_id and GuidNumber=@m
open get_page_id
fetch get_page_id into @page_id
close get_page_id
DEALLOCATE get_page_id
/*插入元素楼层表及元素表*/
declare @element_name varchar(100)
declare @layer_id int=0
--------------------------------测试通过--------------------------------
if @t=1 --文本元素
begin
--获取楼层最大序号
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--1.插入楼层表
set @element_name='文本元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
--2.文本元素表
insert into [MiLaiShopTemp].[dbo].[TextElementTB]
([Text],LayerTBID,CreateTime)
values
(@htm,@layer_id,@create_time)
end
--------------------------测试通过------------------------------------------
if @t=2 --商品元素
begin
print('商品元素')
--2.商品元素表
declare @display_number int=0--显示数量
declare @product_id_list varchar(1000)=''--商品id列表 ,123,22,135,
declare @product_type int=0--商品类别
declare @display_type int=0--显示类型
declare @is_display_price bit--是否显示价格
declare @is_display_title bit--是否显示标题
--解析变量@htm @sys
/* 图片元素信息htm:
14123|10|商品名称|http://test002.ddomi.com/UploadFiles/UserData/test002/image/20160421/20160421145165756575.jpg
$$$
206|0|【测试】面膜|http://test002.ddomi.com/UploadFiles/UserData/test002/image/20160421/20160421145247264726.jpg
$$$
14126|10|商品名称|http://test002.ddomi.com/UploadFiles/UserData/test002/image/20160421/20160421145165756575.jpg
元素属性sys:
0|0|0 大图
1|0|0 小图
2|0|0 一大两小
2|1|0 显示名称
2|1|1 显示价格
我们先解析元素属性sys，在去解析图片元素信息
1.大图：每一张作为一个楼层
2.小图：每两张作为一个楼层
3.一大两小：一大作为一个楼层 两小最为一个楼层
*/
declare @sys_1 varchar(100)=[MiLaiShopTemp].[dbo].[GetElementByIndex](@sys,'|',1)--获取显示类型
if ISNUMERIC(@sys_1)=1
set @display_type=convert(int,@sys_1)
else
begin
set @display_type=-1
print('商品元素显示类型异常')
end
--是否显示价格
if [MiLaiShopTemp].[dbo].[GetElementByIndex](@sys,'|',3)=1
set @is_display_price=1
else
set @is_display_price=0
--是否显示名称
if [MiLaiShopTemp].[dbo].[GetElementByIndex](@sys,'|',2)=1
set @is_display_title=1
else
set @is_display_title=0
set @product_type=0
declare @product_id varchar(100) --图片元素中商品ID
declare @element_index int=1--htm中元素的索引值
declare @element varchar(1000)--图片元素
print('@display_type') print(@display_type)
--1.大图---------------------------测试通过-----------------------------------
if @display_type=0
begin
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
while(ISNULL(@element,'')!='')
begin
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @product_id_list=','
set @product_id=SUBSTRING(@element,1,CHARINDEX('|',@element)-1)--获取图片元素的属性产品Id
print('@product_id') print(@product_id)
set @product_id_list=@product_id_list+@product_id+','
set @display_number=1
set @element_index=@element_index+1
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
end
--2.小图------------------------测试通过---------------------------------
if @display_type=1
begin
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
print('@element:'+@element)
while(ISNULL(@element,'')!='')
begin
set @product_id=SUBSTRING(@element,1,CHARINDEX('|',@element)-1)--获取图片元素的属性产品Id
if @element_index%2=1
begin
set @product_id_list=','
set @product_id_list=@product_id_list+@product_id+','
end
else--索引值为2的时候,存数据到表中
begin
print('------------------1------------------------')
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @product_id_list=@product_id_list+@product_id+','
set @display_number=2
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
set @element_index=@element_index+1--索引值+1
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
--索引值为奇数时,且该索引值的元素不为空,且该索引值的元素的下一个元素为空时也要存数据到表中
if (@element_index%2=1 and (ISNULL(@element,'')!=''))
begin
print('------------------2------------------------')
declare @element_next varchar(1000)--第奇数个元素的下一个元素
set @element_next=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index+1)
--print('@element_next:'+@element_next)
if (ISNULL(@element_next,'')='')
begin
set @product_id=SUBSTRING(@element,1,CHARINDEX('|',@element)-1)--获取图片元素的属性产品Id
set @product_id_list=','
set @product_id_list=@product_id_list+@product_id+','
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @display_number=1
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
end
end
end
---------------------------测试通过--------------------------
--3.一大两小
if @display_type=2
begin
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
print('@element:'+@element)
while(ISNULL(@element,'')!='')
begin
set @product_id=SUBSTRING(@element,1,CHARINDEX('|',@element)-1)--获取图片元素的属性产品Id
if @element_index%3=1--第一张图作为大图
begin
set @product_id_list=','
set @product_id_list=@product_id_list+@product_id+','
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @display_number=1
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
---------------------------------
if @element_index%3=2--第二张图作为小图
begin
set @product_id_list=','
set @product_id_list=@product_id_list+@product_id+','
end
--------------------------------------
if @element_index%3=0--第三张图作为小图
begin
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @product_id_list=@product_id_list+@product_id+','
set @display_number=2
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
set @element_index=@element_index+1--索引值+1
set @element=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index)
--特殊情况，小图只有一张时单独建个楼层
if (@element_index%3=2 and (ISNULL(@element,'')!=''))
begin
declare @element_next1 varchar(1000)--第奇数个元素的下一个元素
set @element_next1=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'$$$',@element_index+1)
--print('@element_next:'+@element_next)
if (ISNULL(@element_next1,'')='')
begin
set @product_id=SUBSTRING(@element,1,CHARINDEX('|',@element)-1)--获取图片元素的属性产品Id
set @product_id_list=','
set @product_id_list=@product_id_list+@product_id+','
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--插入楼层表
set @element_name='商品元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
set @display_number=1
insert into [MiLaiShopTemp].[dbo].[ProductElementTB]
(LayerTBID,DisplayNumber,ProductIDList,DisplayType,IsDisplayPrice,IsDisplayTitle,CreateTime,ProductType)
values
(@layer_id,@display_number,@product_id_list,@display_type,@is_display_price,@is_display_title,@create_time,@product_type)
end
end
end
end
end
-------------------------------测试成功-------------------------------
if @t=5 --文本链接元素( 文本链接 5 click|V1001_ABOUT lianjiemingchen|1 )
begin
--获取楼层最大序号
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--1.插入楼层表
set @element_name='文本链接元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID(理论上一定有值的)
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
--存入文本链接元素信息到表中
declare @link_name varchar(1000)--链接名称
declare @link_type int=0--链接类别
declare @link_type_str varchar(1000)--链接类别（记录老米来的文本链接元素链接类别）
declare @link varchar(1000) --链接内容
set @link_name=[MiLaiShopTemp].[dbo].[GetElementByIndex](@sys,'|',1)
set @link=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'|',2)
set @link_type_str= [MiLaiShopTemp].[dbo].[GetElementByIndex](@sys,'|',2)
if @link_type_str=2 set @link_type=1
if @link_type_str=3 set @link_type=3
if @link_type_str=4 set @link_type=4
if @link_type_str=5 set @link_type=5
insert into [MiLaiShopTemp].[dbo].[TextLinkElementTB]
(LayerTBID,LinkName,LinkType,Link,CreateTime)
values
(@layer_id,@link_name,@link_type,@link,@create_time)
end
------------------------测试通过---------------------------------
if @t=8 --辅助线元素
begin
--获取楼层最大序号
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--1.插入楼层表
set @element_name='辅助线元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID(理论上一定有值的)
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
--存入下划线元素信息到表中
declare @color varchar(100) --颜色
declare @subline_type int=2--默认直线
declare @margin_top_and_bottom int=10--上下间距
declare @subline_height int=1 --辅助线高度
set @color=@htm
insert into [MiLaiShopTemp].[dbo].[SublineElementTB]
(LayerTBID,Color,CreateTime,SublineType,MarginTopAndBottom,SublineHeight)
values
(@layer_id,@color,@create_time,@subline_type,@margin_top_and_bottom,@subline_height)
end
---------------------------------------------------------------
if @t=9 --辅助空白元素
begin
--获取楼层最大序号
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--1.插入楼层表
set @element_name='辅助线元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID(理论上一定有值的)
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
--存入辅助空白元素信息到表中
declare @color_blank varchar(100) --颜色
declare @height varchar(100)--高度
set @color_blank=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'|',2)
set @height=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'|',1)
if ISNUMERIC(@height)=1
begin
insert into [MiLaiShopTemp].[dbo].[BlankElementTB]
(LayerTBID,Color,Height,CreateTime)
values
(@layer_id,@color_blank,@height,@create_time)
end
end
-----------------------测试通过----------------------------
if @t=7--搜索框元素
begin
--获取楼层最大序号
set @sort_id=[MiLaiShopTemp].[dbo].[GetMaxSortIdFromLayerTBByPageId](@page_id) --获取装修页楼层的最大排序号
if (ISNULL(@sort_id,0)=0)
set @sort_id=1
else
set @sort_id=@sort_id+1
--1.插入楼层表
set @element_name='搜索框元素'
insert into [MiLaiShopTemp].[dbo].[LayerTB]
(PageTBID,SortID,CreateTime,ElementName)
values
(@page_id,@sort_id,@create_time,@element_name)
--获取刚插入的楼层表的ID(理论上一定有值的)
declare get_layer_id cursor for
SELECT TOP 1 [ID]
FROM [MiLaiShopTemp].[dbo].[LayerTB]
order by ID desc
open get_layer_id
fetch get_layer_id into @layer_id
close get_layer_id
DEALLOCATE get_layer_id
--存入搜索框元素信息到表中
declare @border_color varchar(100) --边框颜色
set @border_color=[MiLaiShopTemp].[dbo].[GetElementByIndex](@htm,'|',1)
insert into [MiLaiShopTemp].[dbo].[SearchElementTB]
(LayerTBID,BorderColor,CreateTime)
values
(@layer_id,@border_color,@create_time)
end
fetch next from get_attr into @www,@m,@t,@num,@htm,@sys
end
close get_attr
commit transaction
END
