BEGIN
DECLARE rowid INT DEFAULT 0;
#用户的创建时间
DECLARE createDate date DEFAULT NOW();
#用户的修改时间
DECLARE editDate date DEFAULT NOW();
#站点ID
DECLARE siteId INT DEFAULT 19;
#Employee ID
DECLARE employeeId VARCHAR(50) DEFAULT '';
DECLARE userId INT;
DECLARE username VARCHAR(50) DEFAULT '';
DECLARE firstname VARCHAR(50) DEFAULT '';
DECLARE middlename VARCHAR(50) DEFAULT '';
DECLARE preferredFirstName VARCHAR(50) DEFAULT '';
DECLARE jobTitle VARCHAR(50) DEFAULT 'participant';
DECLARE lastname VARCHAR(50) DEFAULT '';
DECLARE managerTree VARCHAR(50) DEFAULT '';
DECLARE suffix VARCHAR(50) DEFAULT '';
DECLARE email VARCHAR(50) DEFAULT '';
#状态修改时间
DECLARE statusChangeDate date DEFAULT NOW();
#加密入职时间
DECLARE secureHireDate VARBINARY(255);
#加密生日
DECLARE secureBirthDate VARBINARY(255);
DECLARE area VARCHAR(50) DEFAULT '';
DECLARE bussinessUnit VARCHAR(50) DEFAULT '';
DECLARE department VARCHAR(50) DEFAULT '';
DECLARE division VARCHAR(50) DEFAULT '';
DECLARE functions VARCHAR(50) DEFAULT '';
DECLARE glCode VARCHAR(50) DEFAULT '';
DECLARE levels VARCHAR(50) DEFAULT '';
DECLARE location VARCHAR(50) DEFAULT '';
DECLARE region VARCHAR(50) DEFAULT '';
DECLARE station VARCHAR(50) DEFAULT '';
DECLARE vbl1 VARCHAR(50) DEFAULT '';
DECLARE vbl2 VARCHAR(50) DEFAULT '';
DECLARE vbl3 VARCHAR(50) DEFAULT '';
WHILE rowid < num DO
SET userId = 300000 + rowid;
SET createDate = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 10000) DAY);
SET editDate = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 10000) DAY);
SET statusChangeDate = DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 10000) DAY);
SET secureHireDate = AES_ENCRYPT(DATE(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 10000) DAY)), '100nrupert');
SET secureBirthDate = AES_ENCRYPT(DATE(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 10000) DAY)), '100nrupert');
SET employeeId = CONCAT('r0000_', rowid);
SET username = employeeId;
SET middlename = CONCAT('r0000-middle-', rowid);
SET firstname = CONCAT('r0000-first-', rowid);
SET lastname = CONCAT('r0000-last-', rowid);
SET preferredFirstName = CONCAT('r00-preferredFirstName-', rowid);
SET suffix = CONCAT('r0000-suffix-', rowid);
SET email = CONCAT('r0000-email-', rowid, '@refiny.com');
SET area = CONCAT('area');
SET bussinessUnit = CONCAT('bussinessUnit-', rowid);
SET department = CONCAT('department-', rowid);
SET division = CONCAT('division-', rowid);
SET functions = CONCAT('functions-', rowid);
SET glCode = CONCAT('glCode-', rowid);
SET levels = CONCAT('levels-', rowid);
SET location = CONCAT('location-', rowid);
SET region = CONCAT('region-', rowid);
SET station = CONCAT('station-', rowid);
SET vbl1 = CONCAT('vbl1-', rowid);
SET vbl2 = CONCAT('vbl2-', rowid);
SET vbl3 = CONCAT('vbl3-', rowid);
SET managerTree = CONCAT('$,', userId, ',69,162,$');
SET rowid = rowid + 1;
INSERT INTO CNG_USER (
`USER_ID`,
`CREATE_DATE`,
`CREATE_USER`,
`EDIT_DATE`,
`EDIT_USER`,
`SITE_ID`,
`EMPLOYEE_ID`,
`MANAGER_ID`,
`MANAGER_EMPLOYEE_ID`,
`USER_NAME`,
`PASSWORD`,
`STATUS`,
`FIRST_NAME`,
`LAST_NAME`,
`MIDDLE_NAME`,
`PREFERRED_FIRST_NAME`,
`SUFFIX`,
`JOB_TITLE`,
`ADMIN`,
`MANAGER_STATUS`,
`SSO_EXCEPTION`,
`REGISTERED`,
`PASSWORD_CHANGE_REQ`,
`EMAIL`,
`COUNTRY_CODE`,
`STATUS_CHANGE_DATE`,
`SECURE_HIRE_DATE`,
`SECURE_BIRTH_DATE`,
`AREA`,
`BUSINESS_UNIT`,
`DEPARTMENT`,
`DIVISION`,
`FUNCTION`,
`GL_CODE`,
`LEVEL`,
`LOCATION`,
`REGION`,
`STATION`,
`VBU1`,
`VBU2`,
`VBU3`,
`REMINDE_ON_THE_DAY_OF`,
`REMINDE_WEEKLY`,
`MANAGER_TREE`,
`TAX_ELECTION_REQUIRED`,
`DEDUCT_FROM_PAYCHECK`,
`DEDUCT_FROM_POINTS`,
`EMAIL_UPDATED`,
`AGREE_TERMS_AND_CONDITIONS`,
`BIRTHDAY_OPT_OUT`,
`IS_PREFFER_FIRST_NAME_CHANGED`,
`CAN_BYPASS_MAINTENANCE`,
`IS_PASSWORD_CHANGED`,
`FAILED_LOGIN_ATTEMPTS`,
`FAILED_SECURITY_QUESTION_ATTEMPTS`,
`VIEWED_TAX`,
`REMINDER_TYPE`
)
VALUES
(
userId,
createDate,
1,
editDate,
1,
siteId,
employeeId,
69,
'r002',
username,
'F2D778E29C03AF157E0D48F7774C985C',
'ACTIVE',
firstname,
lastname,
middlename,
preferredFirstName,
suffix,
jobTitle,
0,
1,
0,
1,
0,
email,
'UNITED_STATES',
statusChangeDate,
secureHireDate,
secureBirthDate,
area,
bussinessUnit,
department,
division,
functions,
glCode,
levels,
location,
region,
station,
vbl1,
vbl2,
vbl3,
0,
0,
managerTree,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
1,
'SELECTED_ONLY'
);
END WHILE;
END
