CREATE TABLE IF NOT EXISTS `sys_user` (
`Uuid` CHAR(35) NOT NULL DEFAULT '',
`Package` CHAR(255) NOT NULL,
`Module` VARCHAR(255) DEFAULT 'ObjPerson',
`User` CHAR(150) NOT NULL DEFAULT '',
`Active` CHAR(2) NOT NULL DEFAULT '',
`UuidDate` DATETIME DEFAULT NULL,
`Avatar` MEDIUMTEXT DEFAULT NULL,
`PasswordMustChange` CHAR(1) DEFAULT 'N',
`Name` CHAR(150) DEFAULT '',
`Usergroups` MEDIUMTEXT DEFAULT NULL,
`UserGroup` CHAR(50) DEFAULT NULL,
`TimeZone` TINYINT(5) DEFAULT 2,
`PasswordExpires` DATE DEFAULT NULL,
`Email` CHAR(255) DEFAULT NULL,
`Teamleader` CHAR(255) DEFAULT '',
`Firstnames` CHAR(255) DEFAULT '',
`Password` TEXT DEFAULT NULL,
`PasswordAge` DATETIME DEFAULT NULL,
`RegisterDate` DATETIME DEFAULT NULL,
`Otp` CHAR(50) DEFAULT NULL,
`OtpAge` DATETIME DEFAULT NULL,
`Level` TINYINT(3) UNSIGNED DEFAULT 1,
`MsIsdn` CHAR(255) DEFAULT '',
`Companies` MEDIUMTEXT DEFAULT NULL,
`UserCompany` CHAR(150) DEFAULT 'Guest',
`Company` CHAR(150) DEFAULT 'Guest',
`Store` CHAR(50) DEFAULT NULL,
`LastLoginDate` DATETIME DEFAULT NULL,
`LastInteraction` DATETIME DEFAULT NULL,
`TrackUser` CHAR(1) DEFAULT NULL,
`RealmUuid` CHAR(255) DEFAULT NULL,
PRIMARY KEY (`Uuid`),
KEY `idx_package` (`Package`),
KEY `idx_user` (`User`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `sys_user_session` (
`Package` VARCHAR(255) NOT NULL,
`Username` VARCHAR(150) NOT NULL,
`Session` VARCHAR(255) NOT NULL,
`SessionStart` DATETIME DEFAULT NULL,
PRIMARY KEY (`Package`, `Username`, `Session`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE `data_person` (
`PersonGuid` char(50) NOT NULL,
`Package` varchar(255) NOT NULL,
`Module` varchar(255) DEFAULT NULL,
`Name` varchar(255) NOT NULL,
`Nickname` varchar(255) DEFAULT NULL,
`User` varchar(255) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL,
`Msisdn` varchar(50) DEFAULT NULL,
`CellNumber` varchar(50) DEFAULT NULL,
`WhatsappNumber` varchar(50) DEFAULT NULL,
`SlackId` varchar(100) DEFAULT NULL,
`DiscordId` varchar(100) DEFAULT NULL,
`PagerdutyId` varchar(255) DEFAULT NULL,
`Role` varchar(100) DEFAULT NULL,
`JobTitle` varchar(255) DEFAULT NULL,
`Companies` text DEFAULT NULL,
`Packages` text DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
`Timezone` varchar(100) DEFAULT NULL,
`Notes` text DEFAULT NULL,
`Active` char(1) NOT NULL DEFAULT 'Y',
`LastUpdateDate` datetime DEFAULT NULL,
`LastContactDate` datetime DEFAULT NULL,
`BitbucketUuid` varchar(255) DEFAULT NULL,
`BitbucketNickname` varchar(255) DEFAULT NULL,
`AsanaGid` varchar(255) DEFAULT NULL,
`GoogleAccountId` varchar(255) DEFAULT NULL,
`GitHubUsername` varchar(255) DEFAULT NULL,
`JiraAccountId` varchar(255) DEFAULT NULL,
`KeycloakId` varchar(255) DEFAULT NULL,
PRIMARY KEY (`PersonGuid`),
KEY `idx_data_person_package` (`Package`),
KEY `idx_data_person_user` (`User`),
KEY `idx_data_person_email` (`Email`),
KEY `idx_data_person_bb` (`BitbucketUuid`),
KEY `idx_data_person_asana` (`AsanaGid`),
KEY `idx_data_person_slack` (`SlackId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
SELECT * FROM data_person
WHERE PersonGuid = '{person_guid}'
INSERT INTO data_person (
PersonGuid, Package, Module, Name, Nickname, User,
Email, Msisdn, WhatsappNumber, SlackId,
DiscordId, PagerdutyId, Role, JobTitle,
Companies, Packages, City, Timezone, Notes, Active,
LastUpdateDate, LastContactDate
) VALUES (
'{person_guid}', '{package}', '{module}', '{name}', '{nickname}',
'{user}', '{email}', '{msisdn}', '{whatsapp_number}', '{slack_id}',
'{discord_id}', '{pagerduty_id}', '{role}', '{job_title}',
'{companies}', '{packages}', '{city}', '{timezone}', '{notes}', '{active}',
NOW(), {last_contact_date}
)
ON DUPLICATE KEY UPDATE
Package = VALUES(Package),
Module = VALUES(Module),
Name = VALUES(Name),
Nickname = VALUES(Nickname),
User = VALUES(User),
Email = VALUES(Email),
Msisdn = VALUES(Msisdn),
WhatsappNumber = VALUES(WhatsappNumber),
SlackId = VALUES(SlackId),
DiscordId = VALUES(DiscordId),
PagerdutyId = VALUES(PagerdutyId),
Role = VALUES(Role),
JobTitle = VALUES(JobTitle),
Companies = VALUES(Companies),
Packages = VALUES(Packages),
City = VALUES(City),
Timezone = VALUES(Timezone),
Notes = VALUES(Notes),
Active = VALUES(Active),
LastUpdateDate = NOW(),
LastContactDate = VALUES(LastContactDate)
SELECT * FROM data_person
WHERE Active = 'Y'
AND (
Packages IS NULL
OR Packages = ''
OR FIND_IN_SET('{package}', REPLACE(Packages, ' ', '')) > 0
)
ORDER BY Name
SELECT * FROM data_person
WHERE (
Packages IS NULL
OR Packages = ''
OR FIND_IN_SET('{package}', REPLACE(Packages, ' ', '')) > 0
)
ORDER BY Name
SELECT * FROM data_person
WHERE Active = 'Y'
AND (
PersonGuid = '{identifier}'
OR User = '{identifier}'
OR Name = '{identifier}'
)
AND (
Packages IS NULL
OR Packages = ''
OR FIND_IN_SET('{package}', REPLACE(Packages, ' ', '')) > 0
)
ORDER BY LastUpdateDate DESC
LIMIT 1
SELECT * FROM data_person
WHERE Active = 'Y'
AND (
Packages IS NULL
OR Packages = ''
OR FIND_IN_SET('{package}', REPLACE(Packages, ' ', '')) > 0
)
AND (
Name LIKE '%{search}%'
OR Nickname LIKE '%{search}%'
OR Email LIKE '%{search}%'
OR Role LIKE '%{search}%'
OR Companies LIKE '%{search}%'
OR JobTitle LIKE '%{search}%'
)
ORDER BY Name
UPDATE data_person
SET LastContactDate = NOW(), LastUpdateDate = NOW()
WHERE PersonGuid = '{person_guid}'
UPDATE data_person
SET Active = 'N'
WHERE PersonGuid = '{person_guid}'