1 建表语句

-- ----------------------------
-- Table structure for sys_tenants
-- ----------------------------
DROP TABLE IF EXISTS sys_tenants;
CREATE TABLE sys_tenants  (
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '租户，对应乐创者的 userTenantIdent',
  user_code_prefix varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工工号的前缀',
  default_roles varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '新建用户默认分配的角色，支持一个或多个角色。是 一个或多个 role_inner_name 值的逗号连接',
  op_last varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '/' COMMENT '修改人',
  op_last_time datetime NOT NULL COMMENT '修改时间',
  default_org varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '默认组织',
  PRIMARY KEY (tenant) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sys_divisions
-- ----------------------------
DROP TABLE IF EXISTS sys_divisions;
CREATE TABLE sys_divisions  (
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  division_inner_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  division_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  division_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  division_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  org_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  parent_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  level_code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  sx_ int(11) NULL DEFAULT NULL,
  is_leaf smallint(6) NULL DEFAULT NULL,
  is_abort smallint(6) NULL DEFAULT NULL,
  is_delete smallint(6) NULL DEFAULT NULL,
  op_first varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_first_time datetime NULL DEFAULT NULL,
  op_last varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_last_time datetime NULL DEFAULT NULL,
  op_delete varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_detete_time datetime NULL DEFAULT NULL,
  parent_inner_name_alter varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (division_inner_id, tenant) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sys_orgs
-- ----------------------------
DROP TABLE IF EXISTS sys_orgs;
CREATE TABLE sys_orgs  (
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'lcz' COMMENT '标准字段-主键-租户',
  org_inner_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-主键-组织 ID',
  org_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-组织 NAME',
  org_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-组织 ALIAS',
  parent_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-上级组织的 NAME',
  level_code varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-组织层码',
  is_leaf smallint(1) NOT NULL DEFAULT 1 COMMENT '标准字段-组织是否为叶节点',
  org_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-组织代码',
  is_abort smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-组织禁用标记',
  op_first varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-创建人工号',
  op_first_time datetime NOT NULL COMMENT '标准字段-创建时间',
  op_last varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-修改人工号',
  op_last_time datetime NOT NULL COMMENT '标准字段-修改时间',
  is_delete smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-假删标记',
  parent_inner_name_alter varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-上级组织Name 修改新值',
  is_managed smallint(6) NULL DEFAULT NULL,
  division_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete_time datetime NULL DEFAULT NULL,
  PRIMARY KEY (org_inner_id) USING BTREE,
  UNIQUE INDEX idx_t_inner_name(tenant, org_inner_name) USING BTREE,
  UNIQUE INDEX idx_t_code(tenant, org_code) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-组织（部门）表-符合标准规范' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sys_roles
-- ----------------------------
DROP TABLE IF EXISTS sys_roles;
CREATE TABLE sys_roles  (
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'lcz' COMMENT '标准字段-租户',
  role_inner_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-角色 ID',
  role_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-角色 NAME',
  role_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-角色 ALIAS',
  sx smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-顺序',
  is_abort smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-禁用标记',
  op_first varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-创建人工号',
  op_first_time datetime NOT NULL COMMENT '标准字段-创建时间',
  op_last varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-修改人工号',
  op_last_time datetime NOT NULL COMMENT '标准字段-修改时间',
  is_delete smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-假删标记',
  division_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete_time datetime NULL DEFAULT NULL,
  PRIMARY KEY (role_inner_id) USING BTREE,
  UNIQUE INDEX idx_t_name(tenant, role_inner_name) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-角色表-符合标准规范' ROW_FORMAT = Dynamic;


-- ----------------------------
-- Table structure for sys_users
-- ----------------------------
DROP TABLE IF EXISTS sys_users;
CREATE TABLE sys_users  (
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'lcz' COMMENT '标准字段-租户',
  user_inner varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-主键-用户 ID',
  user_no int(6) NULL DEFAULT NULL COMMENT '标准字段-企业内员工编号',
  user_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-用户工号',
  user_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-用户 ALIAS',
  login_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-登录名-用户 NAME',
  password varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
  sx int(6) NOT NULL DEFAULT 0 COMMENT '标准字段-顺序，作用于同一个部门内的排序',
  org_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-所属组织的组织 NAME',
  supervisor_inner varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-直接主管的 ID',
  supervisor_inner_alter varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-为调整直接主管服务',
  level_code_supervisor varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-用户的隶属关系层码',
  is_leaf_supervisor smallint(1) NOT NULL DEFAULT 1 COMMENT '标准字段-隶属关系是否为叶节点',
  is_abort smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-停用标记',
  is_delete smallint(1) NOT NULL DEFAULT 0 COMMENT '标准字段-假删标记',
  op_first varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-创建人工号',
  op_first_time datetime NOT NULL COMMENT '标准字段-创建时间',
  op_last varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-修改人工号',
  op_last_time datetime NOT NULL COMMENT '标准字段-修改时间',
  id_card varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-身份证号码',
  sex smallint(1) NULL DEFAULT NULL COMMENT '标准字段-业务字段-性别：0 男；1 女',
  is_married smallint(1) NULL DEFAULT NULL COMMENT '标准字段-业务字段-是否已婚',
  birth_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-生日',
  on_board_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-入职日期',
  enroll_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-转正日期',
  quit_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-离职日期',
  contract_begin_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-合同开始日期',
  contract_end_date date NULL DEFAULT NULL COMMENT '标准字段-业务字段-合同结束日期',
  mobile varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-手机号码',
  phone varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-固定电话号码',
  email varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-电邮地址',
  qq varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-QQ',
  postal_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-邮编',
  ding_user_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-钉钉企业内部用户编码',
  ding_union_id varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-钉钉用户全局编码',
  ding_gov_emp_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-政务钉人员编码',
  ding_gov_account_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-政务钉账户编码',
  wecorp_user_id varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-企业微信企业内部用户编码',
  wechat_union_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '标准字段-业务字段-微信用户全局编码',
  user_no_alter int(6) NULL DEFAULT NULL COMMENT '标准字段-业务字段-编号老值',
  level_code_order int(6) NULL DEFAULT NULL COMMENT '标准字段-业务字段-隶属关系排序字段',
  division_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  account_type int(10) UNSIGNED NULL DEFAULT NULL,
  main_account varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  default_account varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_delete_time datetime NULL DEFAULT NULL,
  PRIMARY KEY (user_inner) USING BTREE,
  UNIQUE INDEX idx_user_login(tenant, login_name) USING BTREE,
  INDEX idx_user_no(tenant, user_no) USING BTREE,
  INDEX idx_user_code(tenant, user_code) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-用户（员工）表-符合标准规范' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sys_users_roles
-- ----------------------------
DROP TABLE IF EXISTS sys_users_roles;
CREATE TABLE sys_users_roles  (
  user_role_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-映射关系主键',
  tenant varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'lcz' COMMENT '标准字段-租户',
  user_inner varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-用户 ID',
  role_inner_id varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标准字段-角色 ID',
  division_inner_name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_first varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  op_first_time datetime NULL DEFAULT NULL,
  PRIMARY KEY (user_role_id) USING BTREE,
  UNIQUE INDEX idx_t_user_role(tenant, user_inner, role_inner_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统-用户角色映射关系表-符合标准规范' ROW_FORMAT = Dynamic;

2 存储过程

-- ----------------------------
-- Function structure for replace_levelcode
-- ----------------------------
DROP FUNCTION IF EXISTS `replace_levelcode`;
delimiter ;;
CREATE DEFINER=`root`@`%` FUNCTION `replace_levelcode`(`p_levelcode` varchar(100),`p_old_parent_levelcode` varchar(100),`p_new_parent_levelcode` varchar(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
    RETURN CONCAT(p_new_parent_levelcode, SUBSTRING(p_levelcode, LENGTH(p_old_parent_levelcode) + 1));
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_init
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_init`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sp_sys_init`()
BEGIN
    #初始化系统数据,初始化用户体系 确实字段-序号【user_id】，工号【jobNumber】，隶属关系层码【levelCode】；初始化组织层码
    # 定义接受游标的变量
  DECLARE row_org_id, temp_org_self_name, temp_org_parent_name, temp_org_self_levelcode, temp_tenant_id varchar(50);
    DECLARE row_user_id, temp_user_no, temp_supervisor_id, temp_user_jobnumber, temp_user_levelcode varchar(50);
    # 定义循环退出标志符变量
    DECLARE flag INT DEFAULT 0;
    #用户游标
    DECLARE c_user CURSOR
    FOR
SELECT dn_user.id FROM dn_user where (levelCode is null or levelCode = '' OR jobNumber is null OR jobNumber = '' OR user_no is null OR user_no = 0)
                                 AND is_delete = 0 AND id NOT LIKE 'dn_sys%' AND `type` = 0;
#组织游标
DECLARE c_org CURSOR
    FOR
SELECT dn_organization.id FROM dn_organization where (levelCode is null or levelCode = '') AND is_delete = 0 order by `index` asc;
# 定义EXIT 监听器
    -- DECLARE EXIT HANDLER FOR NOT FOUND set flag :=1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

    # 开始循环处理组织
    open c_org;
  REPEAT
FETCH c_org INTO row_org_id;
        set temp_org_self_levelcode = NULL;
        set temp_org_self_name = NULL;
        set temp_org_parent_name = NULL;
        # 重新查询层码，可能在递归时已处理
select `name`, parentId, levelCode, tenantId into temp_org_self_name, temp_org_parent_name, temp_org_self_levelcode, temp_tenant_id from dn_organization where id = row_org_id;
IF temp_org_self_levelcode is null OR temp_org_self_levelcode = '' THEN
            # 生成层码并更新
            call sp_sys_org_init(temp_tenant_id, temp_org_self_name);
END IF;
  UNTIL flag=1 END REPEAT;
    # 循环结束
    # 关闭游标
    CLOSE c_org;

    set flag = 0;

    # 开始循环处理用户
    open c_user;
  REPEAT
FETCH c_user INTO row_user_id;
        set temp_user_levelcode = NULL;
        # 重新查询，可能在递归时已处理
SELECT supervisorId, tenantId, user_no, jobNumber, levelCode INTO temp_supervisor_id, temp_tenant_id, temp_user_no, temp_user_jobnumber, temp_user_levelcode FROM dn_user WHERE id = row_user_id;
IF temp_user_levelcode is null OR temp_user_levelcode = '' OR temp_user_jobnumber is null OR temp_user_jobnumber = '' OR temp_user_no is null OR temp_user_no = 0 THEN
            # 更新用户
            call sp_sys_user_init(temp_tenant_id, row_user_id);
END IF;
  UNTIL flag=1 END REPEAT;
    # 循环结束
    # 关闭游标
    CLOSE c_user;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for sp_sys_level_code_from_user_supervisor
-- ----------------------------
DROP FUNCTION IF EXISTS `sp_sys_level_code_from_user_supervisor`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `sp_sys_level_code_from_user_supervisor`(`v_tenant` varchar(50),`v_user_inner` varchar(50),`v_user_no` int) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE v_supervisor_inner VARCHAR(50) DEFAULT NULL; 
    DECLARE v_level_code VARCHAR(255) DEFAULT ''; 
    DECLARE parent_level_code VARCHAR(255) DEFAULT '';

    SELECT supervisor_inner INTO v_supervisor_inner FROM sys_users WHERE tenant = v_tenant AND user_inner = v_user_inner;
    IF v_supervisor_inner IS NOT NULL THEN
        SELECT level_code_supervisor INTO parent_level_code FROM sys_users WHERE tenant = v_tenant AND user_inner = v_supervisor_inner; 
    END IF;
    RETURN CONCAT(parent_level_code, LPAD(conv(v_user_no,10,36),3,'0'));

--     WHILE v_user_inner_tmp IS NOT NULL DO 
--         SET v_supervisor_inner = NULL;
--         SET i_user_id = NULL; 
--         SELECT user_id,supervisor_inner INTO i_user_id,v_supervisor_inner FROM sys_users WHERE tenant = v_tenant AND user_inner = v_user_inner_tmp; 
--         IF i_user_id IS NOT NULL THEN #把当前 user_id 进行了从 10 位数转换到 36 位数，最终形成 3 位 
--             SET v_level_code = CONCAT(parent_level_code, LPAD(conv(i_user_id,10,36),3,'0')); 
--         END IF; 
--         SET v_user_inner_tmp = v_supervisor_inner; 
--     END WHILE; 
--     RETURN v_level_code;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for sp_sys_next_tenant_user_no
-- ----------------------------
DROP FUNCTION IF EXISTS `sp_sys_next_tenant_user_no`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `sp_sys_next_tenant_user_no`(`v_tenant` varchar(50)) RETURNS int(11)
BEGIN
    DECLARE i_user_no INT DEFAULT NULL; 
    SELECT MAX(user_no) INTO i_user_no FROM sys_users WHERE sys_users.tenant = v_tenant; 
    IF ISNULL(i_user_no) OR i_user_no = ''  THEN SET i_user_no = 0; 
    END IF; 
    RETURN i_user_no + 1;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_org_add
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_org_add`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_org_add`(IN `v_tenant` varchar(50),IN `v_org_inner` varchar(50))
BEGIN
    DECLARE v_org_parent_inner_name,v_org_parent_inner_name_alter VARCHAR(50) DEFAULT NULL; 
    select parent_inner_name,parent_inner_name_alter into v_org_parent_inner_name,v_org_parent_inner_name_alter from sys_orgs where tenant = v_tenant and org_inner_id = v_org_inner;
    IF v_org_parent_inner_name = v_org_parent_inner_name_alter THEN
      -- do nothing
        set v_org_parent_inner_name_alter = v_org_parent_inner_name_alter;
    ELSE
        UPDATE sys_orgs
        SET 
            level_code = sp_sys_org_next_levelcode(v_tenant,v_org_parent_inner_name,'null'),
            parent_inner_name_alter = v_org_parent_inner_name
        WHERE
            tenant = v_tenant AND org_inner_id = v_org_inner;
    END IF;
    # 上级处理是否叶子
    update sys_orgs set is_leaf = 0 where tenant = v_tenant AND org_inner_name = v_org_parent_inner_name;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_org_delete
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_org_delete`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_org_delete`(IN `v_tenant` varchar(50),IN `v_org_inner` varchar(50),IN `v_parent_org_inner` varchar(50))
BEGIN
    DECLARE v_parent_leafcount int; 
    DECLARE v_levelcode VARCHAR(50) DEFAULT NULL; 
    select level_code into v_levelcode from sys_orgs WHERE tenant = v_tenant and parent_inner_name = v_org_inner;
    # 查询更新上级层码的是否叶子
    SELECT count(org_inner_id) into v_parent_leafcount FROM sys_orgs where tenant = v_tenant and parent_inner_name = v_parent_org_inner;
    IF v_parent_leafcount = 0 THEN 
        UPDATE sys_orgs SET is_leaf = 1
        WHERE tenant = v_tenant AND org_inner_name = v_parent_org_inner;
    END IF;
    # 删除子用户 及  子组织 及子组织下的用户
    IF v_levelcode IS NOT NULL AND v_levelcode != '' THEN
        call sp_sys_org_del_cursor(tenant_id, v_levelcode);
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_org_del_cursor
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_org_del_cursor`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_org_del_cursor`(IN `v_tenant` varchar(50),IN `v_del_org_levelcode` varchar(50))
BEGIN
# 删除子用户 及  子组织 及子组织下的用户
    # 定义接受游标的变量
  DECLARE row_org_name varchar(50);
    # 定义循环退出标志符变量
    DECLARE flag INT DEFAULT 0;
    DECLARE c_org_name CURSOR
    FOR
    SELECT org_inner_name FROM sys_orgs where level_code like v_del_org_levelcode || '%' AND is_delete = 0 AND tenant = v_tenant;
    # 定义EXIT 监听器
    DECLARE EXIT HANDLER FOR NOT FOUND set flag :=1;
    # 打开游标 
    open c_org_name;
    # 开始循环
  REPEAT
        FETCH c_org_name INTO row_org_name;
        # 先删除用户
        UPDATE sys_users set is_delete = 1 WHERE org_inner_name = row_org_name AND tenant = v_tenant;
        # 删除组织
        UPDATE sys_orgs set is_delete = 1 WHERE org_inner_name = row_org_name AND tenant = v_tenant;
  UNTIL flag=1 END REPEAT;
    # 循环结束

    # 关闭游标
    CLOSE c_org_name;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_org_modify
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_org_modify`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_org_modify`(IN `v_tenant` varchar(50),IN `v_org_inner` varchar(50), IN `v_old_inner_name` varchar(50))
BEGIN
    DECLARE v_parent, v_parent_name, v_parent_name_alter VARCHAR(255) DEFAULT ''; 
    DECLARE v_org_inner_name VARCHAR(50) DEFAULT ''; 
    DECLARE v_old_parent_leafcount int; 
    DECLARE v_old_level_code,v_new_level_code VARCHAR(50) DEFAULT ''; 
    select org_inner_name, level_code, parent_inner_name, parent_inner_name_alter into v_org_inner_name, v_old_level_code, v_parent_name, v_parent_name_alter from sys_orgs where tenant = v_tenant and org_inner_id = v_org_inner;
    select parent_inner_name into v_parent from sys_orgs where tenant = v_tenant and org_inner_name = v_org_inner_name;
    # 如果父编码变化了则需要修改层码，否则不需要
    IF v_parent_name != v_parent_name_alter THEN 
        set v_new_level_code = sp_sys_org_next_levelcode(v_tenant,v_parent_name,v_org_inner_name);
        # 更新层码
        UPDATE sys_orgs
        SET
                level_code = replace_levelcode(
                                level_code,v_old_level_code,v_new_level_code
                        )
        WHERE
                tenant = v_tenant  AND level_code LIKE CONCAT(v_old_level_code,'%') AND is_delete = 0;
        # 更新parent_inner_name_alter字段
        UPDATE sys_orgs
        SET
                parent_inner_name_alter = parent_inner_name
        WHERE
                tenant = v_tenant  AND org_inner_id = v_org_inner;
        # 查询更新老上级层码的是否叶子
        SELECT count(org_inner_id) into v_old_parent_leafcount FROM sys_orgs where tenant = v_tenant and parent_inner_name = v_parent_name_alter;
        IF v_old_parent_leafcount = 0 THEN 
            UPDATE sys_orgs SET is_leaf = 1
            WHERE tenant = v_tenant AND org_inner_name = v_parent_name_alter;
        END IF;
        # 更新当前上级为非叶子节点
        UPDATE sys_orgs SET is_leaf = 0
        WHERE tenant = v_tenant AND org_inner_name = v_parent_name and is_leaf = 1;
    END IF;
    # 如果内部名称inner_name变化了则需要子组织和用户表，否则不需要
    IF v_org_inner_name != v_old_inner_name AND v_old_inner_name is not null AND v_old_inner_name != '' THEN 
        # 更新子组织
        UPDATE sys_orgs
        SET 
            parent_inner_name = v_org_inner_name
        WHERE
            tenant = v_tenant AND parent_inner_name = v_old_inner_name AND is_delete = 0;
        UPDATE sys_orgs
        SET 
            parent_inner_name_alter = v_org_inner_name
        WHERE
            tenant = v_tenant AND parent_inner_name_alter = v_old_inner_name AND is_delete = 0;
        # 更新组织下用户
        UPDATE sys_users
        SET 
            org_inner_name = v_org_inner_name
        WHERE
            tenant = v_tenant AND org_inner_name = v_old_inner_name AND is_delete = 0;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for sp_sys_org_next_levelcode
-- ----------------------------
DROP FUNCTION IF EXISTS `sp_sys_org_next_levelcode`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `sp_sys_org_next_levelcode`(`v_tenant` varchar(50), `parentOrgInnerName` varchar(50), `selfOrgInnerName` varchar(50)) RETURNS varchar(255) CHARSET utf8
BEGIN
    DECLARE parent_level_code VARCHAR(255) DEFAULT ''; 
    DECLARE maxlevelcode VARCHAR(255) DEFAULT NULL; 
    DECLARE maxlevelNum int DEFAULT NULL; 
    # 父节点的层码
    select level_code into parent_level_code from sys_orgs where org_inner_name = parentOrgInnerName and tenant = v_tenant;
    # 子节点的最大层码
    select max(level_code) into maxlevelcode from sys_orgs where parent_inner_name = parentOrgInnerName and tenant = v_tenant and is_delete = 0 and org_inner_name <> selfOrgInnerName;
    IF maxlevelcode = '' THEN 
        set maxlevelcode = '000';
        RETURN CONCAT(parent_level_code, maxlevelcode);
    END IF;
    IF maxlevelcode is NULL THEN 
        set maxlevelcode = '000';
        RETURN CONCAT(parent_level_code, maxlevelcode);
    END IF;
    set maxlevelNum = CAST(maxlevelcode  AS  UNSIGNED) % 1000;
    -- set maxlevelNum = conv(right(maxlevelcode, 3),36,10);
    RETURN CONCAT(parent_level_code, LPAD(maxlevelNum+1,3,'0'));
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_user_add
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_user_add`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_user_add`(IN `v_tenant` varchar(50),IN `v_user_inner` varchar(50))
BEGIN
    DECLARE v_user_code,v_supervisor_inner VARCHAR(50) DEFAULT NULL; 
    DECLARE i_user_no INT DEFAULT NULL; 
    select user_code,user_no into v_user_code,i_user_no FROM sys_users WHERE tenant = v_tenant AND user_inner = v_user_inner; 
    IF i_user_no is not null AND    i_user_no != '' THEN
        START TRANSACTION;
        #设置本员工的字段 
        UPDATE sys_users 
        SET 
            level_code_supervisor = sp_sys_level_code_from_user_supervisor(v_tenant,user_inner,i_user_no), 
            supervisor_inner_alter = supervisor_inner,
            user_no_alter = user_no,
            is_leaf_supervisor = 1
        WHERE
            tenant = v_tenant AND user_inner = v_user_inner; 
        #获取直接主管的用户 ID 
        SELECT supervisor_inner INTO v_supervisor_inner 
        FROM sys_users 
        WHERE tenant = v_tenant AND user_inner = v_user_inner;
        #设置直接主管的 is_leaf_supervisor 字段 
        UPDATE sys_users SET is_leaf_supervisor = 0 WHERE tenant = v_tenant AND user_inner = v_supervisor_inner; #赋予默认角色 
        INSERT INTO sys_users_roles(user_role_id,tenant,user_inner,role_inner_id)
            SELECT LEFT(UUID(),20),v_tenant,v_user_inner,role_inner_id 
            FROM sys_roles 
            WHERE tenant = v_tenant 
                AND INSTR( 
                    (SELECT CONCAT(',',default_roles,',') 
                    FROM sys_tenants 
                    WHERE tenant = v_tenant), CONCAT(',',role_inner_id,',') 
                ) > 0; 
        COMMIT;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_user_delete
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_user_delete`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_user_delete`(IN `v_tenant` varchar(50),IN `v_supervisor_inner` varchar(50))
BEGIN
    # 上级是否叶子
    DECLARE v_supervisor_leafcount int; 
    SELECT count(user_inner) into v_supervisor_leafcount FROM sys_users where tenant = v_tenant and supervisor_inner = v_supervisor_inner;
    IF v_supervisor_leafcount = 0 THEN
        UPDATE sys_users SET is_leaf_supervisor = 1 
        WHERE tenant = v_tenant AND user_inner = v_supervisor_inner; 
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for sp_sys_user_modify
-- ----------------------------
DROP PROCEDURE IF EXISTS `sp_sys_user_modify`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sys_user_modify`(IN `v_tenant` varchar(50),IN `v_user_inner` varchar(50))
BEGIN
    DECLARE v_supervisor_inner_old,v_supervisor_inner_new VARCHAR(50) DEFAULT NULL; 
    DECLARE v_level_code_supervisor_old,v_level_code_supervisor_new VARCHAR(255) DEFAULT NULL; 
    DECLARE v_user_no, v_user_no_alter int DEFAULT NULL; 
    DECLARE i_count_child INT DEFAULT 0; 

    SELECT supervisor_inner,supervisor_inner_alter,level_code_supervisor, user_no, user_no_alter
    INTO v_supervisor_inner_new,v_supervisor_inner_old,v_level_code_supervisor_old, v_user_no, v_user_no_alter
    FROM sys_users 
    WHERE tenant = v_tenant AND user_inner = v_user_inner;

    #判断直接主管是否被修改
    IF ISNULL(v_supervisor_inner_old) OR v_supervisor_inner_old <> v_supervisor_inner_new OR v_user_no_alter <> v_user_no THEN 
        # START TRANSACTION; 
        #更新本员工及其下属的隶属关系层码 
        SET v_level_code_supervisor_new = sp_sys_level_code_from_user_supervisor(v_tenant,v_user_inner, v_user_no); 

        UPDATE sys_users 
        SET level_code_supervisor = REPLACE( 
            level_code_supervisor,v_level_code_supervisor_old,v_level_code_supervisor_new 
        )
        WHERE
            tenant = v_tenant 
            AND level_code_supervisor LIKE CONCAT(v_level_code_supervisor_old,'%'); 
        #更新直接主管的 is_leaf_supervisor 
        UPDATE sys_users SET is_leaf_supervisor = 0 
        WHERE tenant = v_tenant AND user_inner = v_supervisor_inner_new; 
        # 用到了 v_sys_users_supervisor_tree 
        # 直接用 sys_users 也是可以的，但是可能会包含一些已经无效的人员导致误差 
        SELECT COUNT(user_inner) INTO i_count_child FROM sys_users 
        WHERE tenant = v_tenant AND supervisor_inner = v_supervisor_inner_old AND is_delete = 0; 
        IF 0 = i_count_child THEN 
            UPDATE sys_users SET is_leaf_supervisor = 1 
            WHERE tenant = v_tenant AND user_inner = v_supervisor_inner_old; 
        END IF;
        UPDATE sys_users 
            SET supervisor_inner_alter = supervisor_inner,
                    user_no_alter = user_no
        WHERE tenant = v_tenant AND user_inner = v_user_inner;
        # COMMIT; 
    END IF;
END
;;
delimiter ;