#  --------------------------歷史數(shù)據(jù)遷移----------------------
# 刪除存儲(chǔ)過(guò)程
drop procedure if exists convertHistory;
# 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程
create procedure convertHistory()
begin
  #   定義一個(gè)主鍵
  declare outerId bigint default (select min(RESOURCE_ID) from mkt_resource_conf);
  # 定義查詢插入的列
  declare caseName varchar(32);
  declare gradeId varchar(32);
  declare flowGrade bigint(10);
  declare allocateNum bigint(10);
  declare province varchar(8);
  declare flowUnit varchar(4);
  #   是否完成
  declare done int default false;
  # 創(chuàng)建游標(biāo)
  declare orignData cursor for select CASE_NAME,
                                      FLOW_GRADE,
                                      GRADE_ID,
                                      QUANTITY,
                                      BUSI_REG_PROVINCE_CODE,
                                      FLOW_UNIT
                               from prd_flow_info
                               where BUSI_REG_PROVINCE_CODE = '100';
  #   指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值
  declare continue HANDLER FOR not found set done = true;
  #   把初始值ID減一個(gè)數(shù)目
  set outerId = outerId - 100;
  #   先把之前遷移的刪掉
  delete from mkt_resource_conf where REMARK = '歷史數(shù)據(jù)割接';
  #     刪掉之前的
  delete from mkt_resource_store_conf where REMARK = '歷史數(shù)據(jù)割接';
  # 打開(kāi)游標(biāo)
  open orignData;
  fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
  while (not done) do
  #     具體的業(yè)務(wù)邏輯
  #     查詢的都是配置項(xiàng)，那么插入到配置表
  #     配置項(xiàng)需要查詢一下該配置的總量
  select sum(QUANTITY)
  from prd_flow_info
  where FLOW_GRADE = flowGrade
    and BUSI_REG_PROVINCE_CODE = '99' into allocateNum;
  #     1、2G 的流量直接做轉(zhuǎn)換,轉(zhuǎn)為MB
  if flowUnit = 'G' then
    set flowGrade = flowGrade * 1024;
  end if;

  insert into mkt_resource_conf
  values (outerId, caseName, gradeId, flowGrade, allocateNum, allocateNum, 1, '沒(méi)什么說(shuō)明', 'system', 'system', sysdate(),
          'system', 'system',
          sysdate(), '1', '歷史數(shù)據(jù)割接');
  #     查詢門店的配置，并且插入到門店的配置信息表
  #     這里就要寫一個(gè)嵌套的游標(biāo)了
  begin
    #       定義一個(gè)配置表的ID
    declare storeConfId bigint default (select min(STORE_CONF_ID) from mkt_resource_store_conf);
    declare storeCode varchar(32);
    declare alloNum bigint(10);
    declare usedNum bigint(10);
    declare storeDone int default false;
    declare storeName varchar(128);
    #     定義游標(biāo)
    declare storeData cursor for select store_code,QUANTITY,USE_NUM
                                 from prd_flow_info
                                 where GRADE_ID = gradeId
                                   and BUSI_REG_PROVINCE_CODE = '99';
    declare continue HANDLER FOR not found set storeDone = true;
    #     select gradeId;

    set storeConfId = storeConfId - 100;

    # 開(kāi)始游標(biāo)了
    open storeData;
    fetch storeData into storeCode,alloNum,usedNum;

    while (not storeDone) do
    #       從表里查一下storeName，沒(méi)有就沒(méi)轍了
    select STORE_NAME from mkt_resource_store_conf where STORE_CODE = storeCode limit 1 into storeName;
    #       開(kāi)始保存到門店配置表
    insert into `mkt_resource_store_conf` (`store_conf_id`, `resource_id`, `store_code`, `STORE_NAME`,
                                           `allocated_res_num`,
                                           `used_res_num`,
                                           `create_id`, `create_name`, `create_time`, `update_id`, `update_name`,
                                           `update_time`,
                                           `state`, `remark`)
    values (storeConfId, outerId, storeCode, storeName, alloNum, usedNum, 'system', 'system', sysdate(), 'system',
            'system',
            sysdate(),
            1, '歷史數(shù)據(jù)割接');
    commit ;
    #       ID -1
    set storeConfId = storeConfId - 1;
    fetch storeData into storeCode,alloNum,usedNum;
    end while;
    #     重置變量
    set storeDone = false;
    #     關(guān)閉內(nèi)層游標(biāo)
    close storeData;
  end;
  #   把初始值ID減一
  set outerId = outerId - 1;
  fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
  end while;
  #   關(guān)閉游標(biāo)
  close orignData;

  SELECT * FROM mkt_resource_conf where REMARK = '歷史數(shù)據(jù)割接';
  SELECT * FROM mkt_resource_store_conf where REMARK = '歷史數(shù)據(jù)割接';
end;

call convertHistory();