5.2、SQL脚本和开通示例

SQL脚本和开通示例

当前业务中台不支持界面创建局端节点及初始化账号，下面是对应的脚本，通过以下脚步，可以完成局端节点创建及账号的初始化.

存储过程

• 拼音函数：获取输入字符串的大写拼音首字母

CREATE DEFINER=`bmp_admin_zhpj`@`%` PROCEDURE `bmp_admin_zhpj`.`fristPinyin`(IN P_NAME VARCHAR(255),OUT RES VARCHAR(255))
BEGIN
    DECLARE V_RETURN VARCHAR(255);
    SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),
        0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
        0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
        0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),    'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
    SET RES =  V_RETURN;
END;

CREATE DEFINER=`bmp_admin_zhpj`@`%` PROCEDURE `bmp_admin_zhpj`.`pinyin_first`(P_NAME VARCHAR(255), isFirstUpper tinyint(1), OUT RES VARCHAR(255))
BEGIN
    DECLARE V_COMPARE VARCHAR(255);
    DECLARE V_RETURN VARCHAR(255);
    DECLARE I INT;
    DECLARE rpy VARCHAR(255);
        DECLARE fp VARCHAR(255);
    SET I = 1;
    SET V_RETURN = '';
    while I < LENGTH(P_NAME) do
        SET V_COMPARE = SUBSTR(P_NAME, I, 1);
        IF (V_COMPARE != '') THEN
                        call fristPinyin(V_COMPARE,fp);
            SET rpy = IFNULL(fp,V_COMPARE);
            SET V_RETURN = CONCAT(V_RETURN, rpy);
        END IF;
        SET I = I + 1;
    end while;
    IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
        SET V_RETURN = P_NAME;
        END IF;
        IF isFirstUpper > 0 THEN
                SET V_RETURN =  CONCAT(UPPER(left(V_RETURN,1)),substring(LOWER(V_RETURN),2,(length(V_RETURN))));
        END IF;
    SET RES =  V_RETURN;
END;

CREATE DEFINER=`bmp_admin_zhpj`@`%` PROCEDURE `bmp_admin_zhpj`.`add_admin_user`(
            IN adminTypeName varchar(20),
            IN phone varchar(20),
            IN idcard varchar(100),
            IN pwd varchar(100),
            IN orgType int,
            IN orgId bigint,
            IN useParent int
    )
BEGIN
    SELECT org_parent_id INTO @P_ORG_ID FROM t_org_tree WHERE org_id = orgId;
    SELECT org_name INTO @ORG_NAME FROM t_org_tree WHERE org_id = orgId;
    SELECT org_name INTO @P_ORG_NAME FROM t_org_tree WHERE org_id = @P_ORG_ID;
    SELECT CONCAT(if(useParent = 1, CONCAT(@P_ORG_NAME, '-'), ''), @ORG_NAME, adminTypeName) INTO @nickname;
    SELECT CONCAT(if(useParent = 1, @P_ORG_NAME, ''),, @ORG_NAME, adminTypeName) INTO @account_cn;
    CALL pinyin_first(@account_cn,1, @account);
    insert into `sys_authz_user`(`nickname`,`phone`,`id_type`,`idcard`) values(@nickname, phone, 1, idcard);
    SELECT @USERID := LAST_INSERT_ID();
    insert into `sys_authz_account`(`account`,`password`,`user_id`,`type`) values(@account,pwd,@USERID,'password');
    insert into `sys_authz_user_identity`(`user_id`,`identity_id`,`info_id`) values(@USERID,6,NULL);

    INSERT INTO t_org_tree_user ( org_id, user_id, org_type )
    SELECT
        _id AS org_id,( SELECT @USERID ) user_id,( SELECT orgType ) org_type
        FROM
            (
            SELECT
                    @r AS _id,
                    ( SELECT @r := org_parent_id FROM t_org_tree WHERE org_id = _id ) AS org_parent_id
            FROM
                    ( SELECT @r := orgId ) vars,
                    t_org_tree h
            WHERE
                    @r != 0
                    AND org_parent_id > 0
            ) AS a
            LEFT JOIN ( SELECT * FROM t_org_tree_user WHERE user_id = @USERID AND org_type = orgType ) b ON a._id = b.org_id
        WHERE
            b.id IS NULL;

    INSERT INTO `t_role_org_user`(`role_id`, `org_id`, `user_id`, `is_delete`) VALUES (1, orgId,@USERID , 0);
END;

添加数据脚步

• 添加组织机构节点

-- 添加市节点
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(3301, 'node_sjyj', NULL, NULL, NULL, 'XX市', NULL, 2, NULL, 1, 3301, '1', '01', '3301', 0, 1, '2024-01-25 15:59:12', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- 添加区（县）节点
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(330102, 'node_jyj', NULL, NULL, NULL, '上城区', NULL, 3, NULL, 3301, 330102, '1', '01', '330102', 0, 1, '2023-12-27 18:51:54', NULL, '2024-06-04 09:48:02', NULL, NULL, NULL, NULL, NULL, NULL);

-- 添加区节点下的子节点

INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(331182, 'node_category', NULL, NULL, NULL, '机关科室', NULL, 4, NULL, 330102, 1, '1', '82', '997', 0, 1, '2023-12-27 18:53:26', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(331272, 'node_category', NULL, NULL, NULL, '直属单位', NULL, 4, NULL, 330102, 2, '1', '82', '998', 0, 1, '2023-12-27 18:53:26', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(331542, 'node_category', NULL, NULL, NULL, '九年一贯制学校', NULL, 4, NULL, 330102, 5, '1', '82', '312', 0, 1, '2023-12-27 18:53:26', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(331812, 'node_category', NULL, NULL, NULL, '小学', NULL, 4, NULL, 330102, 7, '1', '82', '211', 0, 1, '2023-12-27 18:53:26', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO t_org_tree
(org_id, org_type, org_code, org_serial_no, org_short_name, org_name, org_icon, org_level, org_intro, org_parent_id, org_order, org_status, source_type, source_value, is_delete, creator, create_time, modifyer, modify_time, tree_stage, term, source_id, school_dept_id, binding_count, status_flag)
VALUES(1744974093327032322, 'node_category', NULL, NULL, NULL, '培智学校', NULL, 4, NULL, 330102, 8, '1', '82', '513', 0, 1, '2023-12-27 18:53:26', NULL, '2024-03-06 09:30:12', NULL, NULL, NULL, NULL, NULL, NULL);

• 增加默认管理员

SET @org_name := '天音测试小学';
SET @nickname := '超管';
SET @phone := '15964703683';
SET @idcard := '45050019740806006X';
SET @org_id := (SELECT org_id FROM t_org_tree
WHERE org_name = @org_name AND org_type in  ('node_school','node_sydw','node_sjyj','node_jyj'));

call add_admin_user(@nickname,@phone,@idcard,"{SHA}0R9jFDDys6gz3W6U4CIfUEc2fs4=",10,@org_id);