会员导入存储过程

会员导入分析:

1.先将excel导入服务器,

2.分页读取服务器对应的excel文件,批量存入对应的缓存表格,根据spId和shopId区分

3.执行存储过程

4.获取存储过程的返回值,如果有返回,说明数据有问题,抛出异常,没有返回,清掉缓存表格中对应spId,shopId的数据, 继续执行2,3步骤,至无分页数据

 

存储过程分析:

会员级别, 计次卡(套餐卡)名称, 计次卡项目: 要求导入之前需要在系统中先定义好,如果没有定义,会抛出对应的异常;

 

会员信息,根据手机号判断是否存在,系统中有对应的信息,没有添加,有对应跟新成excel中的数据;

 

会员级别:有级别的更新正excel中的,没有的新建

 

计次卡和项目: 没有关联excel对应的计次卡, 添加计次卡,将对应项目信息添加add,关联该计次卡下; 有excel对应计次卡, 将对应项目信息添加add,关联在对应计次卡下

 

 

 

 

存储过程sql:

 

CREATE  PROCEDURE proc_import_member(IN p_sp_id INT, IN p_shop_id INT, OUT return_type INT, OUT return_msg VARCHAR(255))

label_pro: BEGIN

  

  #1.数据校验 (return_type=1: 会员级别校验失败;  return_type=2: 计次卡验证失败;  return_type=3: 计次卡项目验证失败)

    #a. 会员级别验证: 查看系统中是否存在对应的级别,

            # 有:将对应的级别信息放入return_res,结束存储过程;

            #没有:继续;

    

    select COUNT(1) from excel_member a

      WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

   AND not EXISTS (

   select 1 from sp_vip_level b

   WHERE sp_id = a.sp_id

   AND b.shop_id = a.shop_id

   AND b.level_name = a.level_name

   AND b.sp_id = p_sp_id

   AND b.shop_id = p_shop_id

   AND b.status = 0

   ) INTO @a;

 

     IF @a>0 THEN

      #设置return_type=1: level_name 有不存在的情况

      SET return_type = 1;

      #将 level_name不存在的第一个值放到return_msg中

      select level_name from excel_member a

      WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

   AND not EXISTS (

   select 1 from sp_vip_level b

   WHERE b.sp_id = a.sp_id

   AND b.shop_id = a.shop_id

   AND b.level_name = a.level_name

   AND b.sp_id = p_sp_id

   AND b.shop_id = p_shop_id

   AND b.status = 0

   ) LIMIT 1 INTO return_msg;

      #结束存储过程

      LEAVE label_pro;

     END IF;

    

    #b. 计次卡验证: 查看系统中是否存在对应的计次卡,

          #有:将对应的计次卡信息放入return_res,结束存储过程;

          #没有:继续;

      

    select COUNT(1) from excel_member a

      WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

   AND not EXISTS (

   select 1 from sp_meals b

   WHERE sp_id = a.sp_id

   AND b.sp_shop_id = a.shop_id

   AND b.sp_id = p_sp_id

   AND b.sp_shop_id = p_shop_id

   AND b.name = a.meals_name

   AND b.type = 1

   AND b.status = 0

   ) INTO @b;

 

    IF @b > 0 THEN

      # 计次卡的return_type=2

      SET return_type = 2;

      # 将不存在的计次卡名称赋值给return_msg

      select meals_name from excel_member a

        WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

     AND not EXISTS (

     select 1 from sp_meals b

     WHERE sp_id = a.sp_id

     AND b.sp_shop_id = a.shop_id

       AND b.sp_id = p_sp_id

       AND b.sp_shop_id = p_shop_id

     AND b.name = a.meals_name

       AND b.type = 1

     AND b.status = 0

     ) LIMIT 1 INTO return_msg;

      # 跳出存储过程

      LEAVE label_pro;

    END IF;

    

  

    #c. 计次卡项目验证: 查看系统中是否存在对应的项目,

          # 有:将对应的项目信息放入return_res,结束存储过程;

          # 没有:继续;

    select COUNT(1) from excel_member a

     LEFT JOIN excel_member_item b ON a.id = b.excel_member_info_id

     WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

     AND NOT EXISTS (

     SELECT

     ssi.sp_id spId,

     ssi.sp_shop_id shopId,

     si.no,

     si.name,

     si.parent_no parentNo,

     psi.name parentName,

     ssi.status

     FROM

     sp_service_item ssi,

     code_service_item si,

     code_service_item psi

     WHERE

     ssi.no = si.no

     AND si.parent_no = psi.no

     AND si.`name` = b.item_name

     AND ssi.sp_id = p_sp_id

     AND ssi.sp_shop_id = p_shop_id

     ) INTO @c;

 

   IF @c > 0 THEN

      # 项目名不存在

      SET return_type = 3;

      # 将不存在的项目名的第一个放入return_msg

      select b.item_name from excel_member a

       LEFT JOIN excel_member_item b ON a.id = b.excel_member_info_id

       WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id

       AND NOT EXISTS (

       SELECT

       ssi.sp_id spId,

       ssi.sp_shop_id shopId,

       si.no,

       si.name,

       si.parent_no parentNo,

       psi.name parentName,

       ssi.status

       FROM

       sp_service_item ssi,

       code_service_item si,

       code_service_item psi

       WHERE

       ssi.no = si.no

       AND si.parent_no = psi.no

       AND si.`name` = b.item_name

       AND ssi.sp_id = p_sp_id

       AND ssi.sp_shop_id = p_shop_id

       ) LIMIT 1 INTO return_msg;

  # 结束存储过程

  END IF;

 

  #2.新增会员信息:

    #a.查看owner中是否有对应mobile, 没有:新增, 并在sp_owner中新增; 有: 查看sp_owner中是否存在对应mobile, 没有:新增; 有:继续

    # 新增 owner

    INSERT INTO owner (mobile, name, status)

      SELECT em.mobile, em.name, 0 FROM excel_member em LEFT JOIN owner o ON em.mobile = o.mobile

      WHERE em.sp_id = p_sp_id AND em.shop_id = p_shop_id AND o.id IS NULL;

 

    # 修改 sp_owner, 当系统中有对应的会员信息,但是名字,剩余储值不相等的,用excel中的值覆盖原系统中对应的值

    update sp_owner a, excel_member b

    set a.`name` = b.`name`, a.balance = b.balance

     WHERE 1=1

     AND a.mobile = b.mobile

     AND a.sp_id = b.sp_id

     AND a.sp_shop_id = b.shop_id

     AND (a.`name` <> b.`name` OR a.balance <> b.balance)

     AND b.sp_id = p_sp_id

     AND b.shop_id = p_shop_id

      AND a.id IS NOT NULL;

 

    # 新增 sp_owner

    INSERT INTO sp_owner (mobile,sp_id,sp_shop_id,owner_id,name,create_time, balance)

       SELECT a.mobile, a.sp_id, a.shop_id, b.id, a.`name`, UNIX_TIMESTAMP(NOW())*1000, a.balance from excel_member a

        LEFT JOIN `owner` b ON a.mobile = b.mobile

        LEFT JOIN sp_owner c ON a.mobile = c.mobile AND a.sp_id = c.sp_id AND a.shop_id = c.sp_shop_id

        WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id AND c.id is NULL;

    

    

    

  #3. 建立会员信息sp_owner_vip,

    #会员级别和对应折扣都是在系统中定义好的,因此,只要根据excel中的level_name拿到对应的sp_vip_level的id,在sp_owner_vip存储对应信息即可:

    #a. 有会员关系的更新成excel中的

    UPDATE sp_owner_vip v, (select a.sp_id, a.shop_id, b.id ownerId, c.id spOwnerId, d.id vipId, UNIX_TIMESTAMP(NOW())*1000 createTime FROM excel_member a

     LEFT JOIN `owner` b ON a.mobile = b.mobile

     LEFT JOIN sp_owner c ON a.mobile = c.mobile AND a.sp_id = c.sp_id AND a.shop_id = c.sp_shop_id AND b.id = c.owner_id

     LEFT JOIN sp_vip_level d ON a.level_name = d.level_name AND a.sp_id = d.sp_id AND a.shop_id = d.shop_id

     WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id AND d.id IS NOT NULL) p

   SET v.sp_vip_level_id = p.vipId

   WHERE v.sp_id = p.sp_id AND v.shop_id = p.shop_id AND v.owner_id = p.ownerId AND v.sp_owner_id = v.sp_owner_id AND v.sp_vip_level_id <> p.vipId;

 

 

    #b. 将没有会员关联关系的人建立关系

    INSERT INTO sp_owner_vip (sp_id, shop_id, owner_id, sp_owner_id, sp_vip_level_id, create_time, status)

       SELECT a.sp_id, a.shop_id, b.id, c.id, d.id, UNIX_TIMESTAMP(NOW())*1000, 0 FROM excel_member a

       LEFT JOIN `owner` b ON a.mobile = b.mobile

       LEFT JOIN sp_owner c ON a.mobile = c.mobile AND a.sp_id = c.sp_id AND a.shop_id = c.sp_shop_id AND b.id = c.owner_id

       LEFT JOIN sp_vip_level d ON a.level_name = d.level_name AND a.sp_id = d.sp_id AND a.shop_id = d.shop_id

        LEFT JOIN sp_owner_vip e ON e.sp_id = a.sp_id AND e.shop_id = a.shop_id AND  e.owner_id = b.id AND e.sp_owner_id = c.id AND e.sp_vip_level_id = d.id

      WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id AND e.id is NULL;

 

 

  #4. 计次卡, 项目

    #a. 没有关联excel对应的计次卡, 添加计次卡,将对应项目信息添加add,关联该计次卡下

   

    INSERT INTO owner_meals (owner_id, sp_id, shop_id, meals_name, meals_type, expire, is_shop, create_time, sp_meals_id)

      SELECT c.id ownerId, a.sp_id, a.shop_id, a.meals_name, b.type, a.expire_time, 0, UNIX_TIMESTAMP(NOW())*1000, b.id mealsId

       from excel_member a

       LEFT JOIN sp_meals b ON a.sp_id = b.sp_id AND a.shop_id = b.sp_shop_id AND a.meals_name = b.`name`

       LEFT JOIN `owner` c ON a.mobile = c.mobile

       WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id AND b.id is not null AND b.type =1

       AND NOT EXISTS (

       select * from owner_meals d WHERE sp_id = a.sp_id AND shop_id = a.shop_id AND meals_type = 1 AND meals_name = a.meals_name AND sp_meals_id = b.id AND d.owner_id = c.id

       );

    

 

    #b. 有excel对应计次卡, 将对应项目信息添加add,关联在对应计次卡下

    INSERT INTO owner_meals_item (owner_meals_id, item_no, item_name, free_times)

        select c.id ownerMealsId, d.`NO` itemNo, d.`NAME` itemName,  b.free_times

         from excel_member a

         LEFT JOIN excel_member_item b ON b.excel_member_info_id = a.id

         LEFT JOIN owner_meals c ON a.meals_name = c.meals_name AND c.sp_id = a.sp_id AND a.shop_id = c.shop_id

         LEFT JOIN (SELECT

         ssi.sp_id spId,

         ssi.sp_shop_id shopId,

         si. NO,

         si. NAME,

         si.parent_no parentNo,

         psi. NAME parentName,

         ssi. STATUS

         FROM

         sp_service_item ssi,

         code_service_item si,

         code_service_item psi

         WHERE

         ssi. NO = si. NO

         AND si.parent_no = psi. NO

         AND ssi.sp_id = p_sp_id

         AND ssi.sp_shop_id = p_shop_id) d ON d.spId = a.sp_id AND d.shopId = a.shop_id AND d.`NAME` = b.item_name

         WHERE a.sp_id = p_sp_id AND a.shop_id = p_shop_id;

    

 

END

 

 

 

 

Mybatis批量插入:

参数是对应javaBean的list集合

 

mybatis调用存储过程代码:

p_sp_id,p_shop_id是输入参数,return_type,return_msg是输出参数,将其赋好值都放在调用参数中即可,不用返回.

Service:

 

Repository:

 

XML: