create  PROCEDURE proc_register(in userId varchar(200),in userName varchar(100),in trueName varchar(200),in userPwd varchar(100),in userMobile varchar(20))

BEGIN

        DECLARE defDept varchar(40); 

        DECLARE defRole varchar(40); 

        DECLARE starttime varchar(40);

        DECLARE endtime varchar(40);

        DECLARE deptsort int;

        DECLARE deptId VARCHAR(40);

        DECLARE companyRoleName VARCHAR(40);

        DECLARE companyRoleId VARCHAR(40);

DECLARE finalPwd VARCHAR(100);-- 存放加密后的密码

DECLARE toRoleName varchar(40); -- 用于存储需要复制的角色名

DECLARE toRoleId VARCHAR(40); -- 用于存储原有的角色名

DECLARE recordIndex INT DEFAULT 0; -- 

DECLARE targetRoleId varchar(40); -- 用于添加新的角色编号

DECLARE toMenuId varchar(40); -- 

DECLARE rolemenuIndex INT DEFAULT 0; -- 

DECLARE toDataScope int DEFAULT 0; -- 用于存储原有的角色名

DECLARE toMenuRoleId VARCHAR(40);-- 存储角色菜单的角色id

DECLARE toOpRoleId VARCHAR(40);-- 存放操作权限的角色编号

DECLARE toOpMenuId VARCHAR(40);-- 存放操作权限的菜单编号

DECLARE toOpId VARCHAR(40);-- 存放操作权限的操作编号

DECLARE done INT DEFAULT 0;  

DECLARE cursor_role CURSOR FOR select id,roleName from main_role where isCopy=1;-- 查询需要复制的角色

DECLARE cursor_menurole CURSOR FOR select roleId,menuId,dataScope from main_rolemenu where roleId in (select id from main_role where isCopy=1);-- 查询需要复制的角色

DECLARE cursor_oprole CURSOR FOR select roleId,menuId,operateId  from main_roleoperate  where roleId in (select id from main_role where isCopy=1);-- 查询复制的角色

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 

        select bdValue INTO endtime from main_bd where typeId='ced8a9c861ff4d189da7bdc63dc67d81' and id='53af510f16674189a0997e86e5d0c8d7';

        select bdValue INTO starttime from main_bd where typeId='ced8a9c861ff4d189da7bdc63dc67d81' and id='1361d82667a44818984ad3727680d89f';

        select bdValue INTO defDept from main_bd where typeId='ced8a9c861ff4d189da7bdc63dc67d81' and id='9f6bfdd9f4304410b159305f3788f570';

        select bdValue INTO defRole from main_bd where typeId='ced8a9c861ff4d189da7bdc63dc67d81' and id='fc8d695471ae4cb4a41bbb61c7124d33';

        select bdValue INTO companyRoleName from main_bd where typeId='ced8a9c861ff4d189da7bdc63dc67d81' and id='2a2ebbc9746840c2a8dfa810a6b667e3';

select MD5(userPwd) into finalPwd;

        select max(sortNum)+1 into deptsort from main_dept where pid=defDept;

        select concat(defDept,'_',(select count(*) deptsort from main_dept where pid=defDept)) INTO deptId;

        insert into main_user(id,userName,trueName,userPwd,createtime,createId,createName,createDeptId,companyId,userMobile) values(userId,userName,trueName,finalPwd,NOW(),userId,trueName,deptId,deptId,userMobile);

        -- 添加公司 同时也是部门

        insert into main_dept(id,deptName,sortNum,pid,createTime,createId,createDeptId) values(deptId,trueName,deptsort,defDept,NOW(),userId,deptId);

        -- 添加用户与部门的关联

        insert into main_deptUser values(replace(UUID(),'-',''),deptId,userId,NOW(),userId,deptId);-- 填加默认角色

        -- select starttime,endtime,defDept,defRole,deptsort,deptId,finalPwd;

        -- insert into main_roleUser values(UUID(),defRole,userId,NOW(),trueName,userId,deptId);-- 填加默认的角色

-- 先插入菜单的数据 再变更角色编号 根据用户编号

OPEN  cursor_menurole; /*接着使用OPEN打开游标*/

menurole_loop: LOOP

FETCH  cursor_menurole INTO toMenuRoleId,toMenuId,toDataScope; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/

insert into main_rolemenu(id,roleId,menuId,createId,createTime,createDeptId,dataScope) values(concat(replace(UUID(),'-',''),recordIndex),toMenuRoleId,toMenuId,userId,NOW(),deptId,toDataScope);

set recordIndex=recordIndex+1;

IF done=1 THEN  

LEAVE menurole_loop;  

END IF;  

END LOOP menurole_loop;

CLOSE cursor_menurole;

set done=0;

-- 先插入操作的数据 再变更角色编号 根据用户编号

OPEN  cursor_oprole; /*接着使用OPEN打开游标*/

oprole_loop: LOOP

FETCH  cursor_oprole INTO toOpRoleId,toOpMenuId,toOpId; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/

insert into main_roleoperate(id,roleId,menuId,operateId,createId,createTime,createDeptId ) values(concat(replace(UUID(),'-',''),recordIndex),toOpRoleId,toOpMenuId,toOpId,userId,NOW(),deptId);

set recordIndex=recordIndex+1;

IF done=1 THEN  

LEAVE oprole_loop;  

END IF;  

END LOOP oprole_loop;

CLOSE cursor_oprole;

set done=0;

        -- 轮循添加角色

OPEN  cursor_role; /*接着使用OPEN打开游标*/

role_loop: LOOP

FETCH  cursor_role INTO toRoleId,toRoleName; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/

set targetRoleId=concat(replace(UUID(),'-',''),recordIndex);

insert into main_role(id,roleName,remarkf,createTime,createId,createDeptId,companyId,sortNum)

values(targetRoleId,toRoleName,'',NOW(),userId,deptId,deptId,recordIndex);

update main_rolemenu set roleId=targetRoleId where roleId=toRoleId and createId=userId;

update main_roleoperate set roleId=targetRoleId where roleId=toRoleId and createId=userId;

set recordIndex=recordIndex+1;

if companyRoleName=toRoleName then 

    set companyRoleId=targetRoleId;

end if; 

IF done=1 THEN  

LEAVE role_loop;  

END IF;  

END LOOP role_loop;

CLOSE cursor_role;

select replace(UUID(),'-',''),companyRoleId,userId,deptId,length(userId);

-- 添加默认角色 添加当前用户是公司管理员角色

insert into main_roleUser values(replace(UUID(),'-',''),companyRoleId,userId,NOW(),userId,deptId);

-- 添加用户与公司的关联

insert into pro_company(id,relateCompanyId,startTime,relateUserId,endTime,createTime,createId,createDeptId) values(

        replace(UUID(),'-',''),deptId,now(),userId,'',now(),userId,deptId);

end;