
DECLARE @sql nvarchar(500);
----第1步：每个产品自动创建cad_bom,并更新cad_bom的序列
DECLARE @cad_bom_object_id_offset bigint;
--获取产品到cad_bom 最大的object_id
SELECT @cad_bom_object_id_offset = ISNULL(MAX(object_id), 0)  FROM t_cad_bom;

INSERT INTO t_cad_bom (object_id,internal_name,name,creator,description)
SELECT object_id+@cad_bom_object_id_offset,'cad_bom','设计BOM',creator,'产品：'+name+'的设计BOM'
from t_product where object_id not in(select rolea_object_id from t_product_to_bom)
--处理cad_bom序列
DECLARE @max_object_id_cad_bom bigint;
-- 获取cad_bom的最大object_id并加1
SELECT @max_object_id_cad_bom = ISNULL(MAX(object_id), 0)+1  FROM T_CAD_BOM;

-- 构建动态SQL语句
SET @sql = N'ALTER SEQUENCE SEQ_ID_CAD_BOM RESTART WITH ' + CAST(@max_object_id_cad_bom AS nvarchar(20));

-- 执行动态SQL
EXEC sp_executesql @sql;


----第2步：创建产品到cad_bom的关系，并更新product_to_bom的序列
DECLARE @max_object_id_product_to_cad_bom_offset bigint;
SELECT @max_object_id_product_to_cad_bom_offset = ISNULL(MAX(object_id), 0)  FROM t_product_to_bom;

insert into t_product_to_bom (internal_name,creator,object_id,rolea_internal_name,rolea_object_id,roleb_internal_name,roleb_object_id)
select 'product_to_bom',creator,object_id+@max_object_id_product_to_cad_bom_offset,'product',object_id,'cad_bom',object_id+@cad_bom_object_id_offset
from t_product where object_id not in(select rolea_object_id from t_product_to_bom)

-- 获取product_to_bom的最大object_id并加1
DECLARE @max_object_id_product_to_cad_bom bigint;
SELECT @max_object_id_product_to_cad_bom = ISNULL(MAX(object_id), 0)+1  FROM T_PRODUCT_TO_BOM;

-- 构建动态SQL语句
SET @sql = N'ALTER SEQUENCE SEQ_ID_PRODUCT_TO_BOM RESTART WITH ' + CAST(@max_object_id_product_to_cad_bom AS nvarchar(20));
-- 执行动态SQL
EXEC sp_executesql @sql;


----第3步：处理库里的零部件
--补齐零件属性
update t_part set valid_flag = 1  where MASTER_OBJECT_ID is null;
update t_part set MASTER_OBJECT_ID = OBJECT_ID where MASTER_OBJECT_ID is null or MASTER_OBJECT_ID = 0;
--todo 需要整理分类库，将零部件归类到不同的库中


----第4步：迁移产品到总装的关系到cad_bom_to_bom_item，并更新该表序列
DECLARE @cad_bom_to_bom_item_object_id_offset bigint;
--获取产品到cad_bom 最大的object_id
SELECT @cad_bom_to_bom_item_object_id_offset = ISNULL(MAX(object_id), 0)  FROM T_CAD_BOM_TO_BOM_ITEM;
insert into T_CAD_BOM_TO_BOM_ITEM (internal_name,creator,object_id,rolea_internal_name,rolea_object_id,roleb_internal_name,roleb_object_id)
select 'cad_bom_to_bom_item',creator, object_id+@cad_bom_to_bom_item_object_id_offset,'cad_bom',rolea_object_id+@cad_bom_object_id_offset ,ROLEB_INTERNAL_NAME,ROLEB_OBJECT_ID
from T_PRODUCT_TO_ASSEMBLY where rolea_internal_name = 'product' and roleb_internal_name = 'part'
 and rolea_object_id  not in (
 --查找在 cad_bom到bom_item中 未升级过来的产品id
 select product_to_bom.rolea_object_id from T_PRODUCT_TO_BOM product_to_bom inner join T_CAD_BOM_TO_BOM_ITEM bom_to_item on product_to_bom.ROLEA_INTERNAL_NAME = 'product'
 and product_to_bom.ROLEB_OBJECT_ID = bom_to_item.ROLEA_OBJECT_ID and product_to_bom.ROLEB_INTERNAL_NAME = bom_to_item.ROLEA_INTERNAL_NAME
 ) 
 and ROLEA_OBJECT_ID in (select object_id from t_product);

 --处理cad_bom_to_bom_item序列
DECLARE @max_object_id_cad_bom_to_bom_item bigint;
-- 获取cad_bom的最大object_id并加1
SELECT @max_object_id_cad_bom_to_bom_item = ISNULL(MAX(object_id), 0)+1  FROM T_CAD_BOM_TO_BOM_ITEM;
-- 构建动态SQL语句
SET @sql = N'ALTER SEQUENCE SEQ_ID_CAD_BOM_TO_BOM_ITEM RESTART WITH ' + CAST(@max_object_id_cad_bom_to_bom_item AS nvarchar(20));
-- 执行动态SQL
EXEC sp_executesql @sql;




----第5步：迁移产品到总装的关系到cad_bom_to_bom_item，并更新该表序列

 --处理part_to_child 到cad_bom_item_to_child 
DECLARE @cad_bom_item_to_child_object_id_offset bigint;
--获取产品到cad_bom 最大的object_id
SELECT @cad_bom_item_to_child_object_id_offset = ISNULL(MAX(object_id), 0)  FROM T_CAD_BOM_ITEM_TO_CHILD;

insert into T_CAD_BOM_ITEM_TO_CHILD(internal_name,creator,object_id,rolea_internal_name,rolea_object_id,roleb_internal_name,roleb_object_id)
select 'cad_bom_item_to_child',creator, object_id+@cad_bom_item_to_child_object_id_offset,'part',rolea_object_id ,'part',ROLEB_OBJECT_ID
from t_part_to_child where rolea_internal_name = 'part' and roleb_internal_name = 'part'
 and rolea_object_id not in (
 select part_to_child.rolea_object_id 
 from T_PART_TO_CHILD part_to_child
 inner join t_cad_bom_item_to_child cad_bom_item_to_child
 on part_to_child.ROLEA_OBJECT_ID = cad_bom_item_to_child.ROLEA_OBJECT_ID
 and part_to_child.ROLEA_INTERNAL_NAME = cad_bom_item_to_child.ROLEA_INTERNAL_NAME
 and part_to_child.ROLEB_OBJECT_ID = cad_bom_item_to_child.ROLEB_OBJECT_ID
 and part_to_child.ROLEB_INTERNAL_NAME = cad_bom_item_to_child.ROLEB_INTERNAL_NAME
 )
 --处理cad_bom_item_to_child序列
DECLARE @max_object_id_cad_bom_item_to_child bigint;

SELECT @max_object_id_cad_bom_item_to_child = ISNULL(MAX(object_id), 0)+1  FROM T_CAD_BOM_ITEM_TO_CHILD;
-- 构建动态SQL语句
SET @sql = N'ALTER SEQUENCE SEQ_ID_CAD_BOM_ITEM_TO_CHILD RESTART WITH ' + CAST(@max_object_id_cad_bom_item_to_child AS nvarchar(20));
-- 执行动态SQL
EXEC sp_executesql @sql;
