IP : 3.137.186.203Hostname : host45.registrar-servers.comKernel : Linux host45.registrar-servers.com 4.18.0-513.18.1.lve.2.el8.x86_64 #1 SMP Sat Mar 30 15:36:11 UTC 2024 x86_64Disable Function : None :) OS : Linux
PATH:
/
home/
../
./
./
var/
softaculous/
tangobb/
../
uvdesk/
../
cube/
../
jorani/
jorani.sql/
/
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
-- -- Database: `jorani100` --
-- -- Table structure for table `actions` --
CREATE TABLE `actions` ( `name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL, `mask` bit(16) NOT NULL, `Description` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='List of possible actions';
-- -- Dumping data for table `actions` --
INSERT INTO `actions` VALUES ('accept_requests', b'0011000100110010', 'Accept the request of my team members'), ('admin_menu', b'0011000100110010', 'View admin menu'), ('change_password', b'0011000100110010', 'Change password'), ('create_leaves', b'0011000100110010', 'Create a new user leave request'), ('create_user', b'0011000100110010', 'Create a new user'), ('delete_user', b'0011000100110010', 'Delete an existing user'), ('edit_leaves', b'0011000100110010', 'Edit a leave request'), ('edit_settings', b'0011000100110010', 'Edit application settings'), ('edit_user', b'0011000100110010', 'Edit a user'), ('export_leaves', b'0011000100110010', 'Export the list of leave requests into an Excel file'), ('export_user', b'0011000100110010', 'Export the list of users into an Excel file'), ('hr_menu', b'0011000100110010', 'View HR menu'), ('individual_calendar', b'0011000100110010', 'View my leaves in a calendar'), ('list_leaves', b'0011000100110010', 'List my leave requests'), ('list_requests', b'0011000100110010', 'List the request of my team members'), ('list_users', b'0011000100110010', 'List users'), ('reject_requests', b'0011000100110010', 'Reject the request of my team members'), ('reset_password', b'0011000100110010', 'Modifiy the password of another user'), ('team_calendar', b'0011000100110010', 'View the leaves of my team in a calendar'), ('update_user', b'0011000100110010', 'Update a user'), ('view_leaves', b'0011000100110010', 'View the details of a leave request'), ('view_user', b'0011000100110010', 'View user''s details');
CREATE TABLE `contracts` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of a contract', `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the contract', `startentdate` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Day and month numbers of the left boundary', `endentdate` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Day and month numbers of the right boundary', `weekly_duration` int(11) DEFAULT NULL COMMENT 'Approximate duration of work per week (in minutes)', `daily_duration` int(11) DEFAULT NULL COMMENT 'Approximate duration of work per day and (in minutes)', `default_leave_type` int(11) DEFAULT NULL COMMENT 'default leave type for the contract (overwrite default type set in config file).', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='A contract groups employees having the same days off and entitlement rules' AUTO_INCREMENT=2 ;
CREATE TABLE `dayoffs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `contract` int(11) NOT NULL COMMENT 'Contract id', `date` date NOT NULL COMMENT 'Date of the day off', `type` int(11) NOT NULL COMMENT 'Half or full day', `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Description of day off', PRIMARY KEY (`id`), KEY `type` (`type`), KEY `contract` (`contract`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='List of non working days' AUTO_INCREMENT=1 ;
CREATE TABLE `entitleddays` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of an entitlement', `contract` int(11) DEFAULT NULL COMMENT 'If entitlement is credited to a contract, Id of contract', `employee` int(11) DEFAULT NULL COMMENT 'If entitlement is credited to an employee, Id of employee', `overtime` int(11) DEFAULT NULL COMMENT 'Optional Link to an overtime request, if the credit is due to an OT', `startdate` date DEFAULT NULL COMMENT 'Left boundary of the credit validity', `enddate` date DEFAULT NULL COMMENT 'Right boundary of the credit validity. Duration cannot exceed one year', `type` int(11) NOT NULL COMMENT 'Leave type', `days` decimal(10,2) NOT NULL COMMENT 'Number of days (can be negative so as to deduct/adjust entitlement)', `description` text COLLATE utf8mb4_unicode_ci COMMENT 'Description of a credit / debit (entitlement / adjustment)', PRIMARY KEY (`id`), KEY `contract` (`contract`), KEY `employee` (`employee`), KEY `type` (`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Add or substract entitlement on employees or contracts (can be the result of an OT)' AUTO_INCREMENT=1 ;
-- -- Table structure for table `excluded_types` --
CREATE TABLE `excluded_types` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id of exclusion', `contract_id` int(11) NOT NULL COMMENT 'Id of contract', `type_id` int(11) NOT NULL COMMENT 'Id of leave ype to be excluded to the contract', PRIMARY KEY (`id`), KEY `contract_id` (`contract_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Exclude a leave type from a contract' AUTO_INCREMENT=1 ;
CREATE TABLE `leaves` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of the leave request', `startdate` date DEFAULT NULL COMMENT 'Start date of the leave request', `enddate` date DEFAULT NULL COMMENT 'End date of the leave request', `status` int(11) DEFAULT NULL COMMENT 'Identifier of the status of the leave request (Requested, Accepted, etc.). See status table.', `employee` int(11) DEFAULT NULL COMMENT 'Employee requesting the leave request', `cause` text COLLATE utf8mb4_unicode_ci COMMENT 'Reason of the leave request', `startdatetype` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Morning/Afternoon', `enddatetype` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Morning/Afternoon', `duration` decimal(10,3) DEFAULT NULL COMMENT 'Length of the leave request', `type` int(11) DEFAULT NULL COMMENT 'Identifier of the type of the leave request (Paid, Sick, etc.). See type table.', `comments` text COLLATE utf8mb4_unicode_ci COMMENT 'Comments on leave request (JSon)', `document` blob COMMENT 'Optional supporting document', PRIMARY KEY (`id`), KEY `status` (`status`), KEY `employee` (`employee`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Leave requests' AUTO_INCREMENT=1 ;
CREATE TABLE `organization` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of the department', `name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the department', `parent_id` int(11) DEFAULT NULL COMMENT 'Parent department (or -1 if root)', `supervisor` int(11) DEFAULT NULL COMMENT 'This user will receive a copy of accepted and rejected leave requests', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tree of the organization' AUTO_INCREMENT=1 ;
-- -- Dumping data for table `organization` --
INSERT INTO `organization` VALUES (0, 'LMS root', -1, NULL);
CREATE TABLE `org_lists` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of a list', `user` int(11) NOT NULL COMMENT ' Identifier of Jorani user owning the list', `name` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `org_lists_user` (`user`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Custom lists of employees are an alternative to organization' AUTO_INCREMENT=1 ;
-- -- Table structure for table `org_lists_employees` --
CREATE TABLE `org_lists_employees` ( `list` int(11) NOT NULL COMMENT 'Id of the list', `user` int(11) NOT NULL COMMENT 'id of an employee', `orderlist` int(11) NOT NULL COMMENT 'order in the list', KEY `org_list_id` (`list`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Children table of org_lists (custom list of employees)';
CREATE TABLE `overtime` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of the overtime request', `employee` int(11) NOT NULL COMMENT 'Employee requesting the OT', `date` date NOT NULL COMMENT 'Date when the OT was done', `duration` decimal(10,3) NOT NULL COMMENT 'Duration of the OT', `cause` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Reason why the OT was done', `status` int(11) NOT NULL COMMENT 'Status of OT (Planned, Requested, Accepted, Rejected)', PRIMARY KEY (`id`), KEY `status` (`status`), KEY `employee` (`employee`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Overtime worked (extra time)' AUTO_INCREMENT=1 ;
CREATE TABLE `parameters` ( `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `scope` int(11) NOT NULL COMMENT 'Either global(0) or user(1) scope', `value` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'PHP/serialize value', `entity_id` text COLLATE utf8mb4_unicode_ci COMMENT 'Entity ID (eg. user id) to which the parameter is applied', KEY `param_name` (`name`,`scope`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Application parameters';
CREATE TABLE `positions` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of the position', `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the position', `description` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Description of the position', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Position (job position) in the organization' AUTO_INCREMENT=2 ;
-- -- Dumping data for table `positions` --
INSERT INTO `positions` VALUES (1, 'Employee', 'Employee.');
CREATE TABLE `types` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier of the type', `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the leave type', `acronym` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Acronym of the leave type', `deduct_days_off` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Deduct days off when computing the balance of the leave type', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='List of leave types (LoV table)' AUTO_INCREMENT=6 ;
DELIMITER $$ -- -- Functions -- CREATE FUNCTION `GetAncestry`(GivenID INT) RETURNS varchar(1024) CHARSET utf8 READS SQL DATA SQL SECURITY INVOKER BEGIN DECLARE rv VARCHAR(1024); DECLARE cm CHAR(1); DECLARE ch INT;
SET rv = ''; SET cm = ''; SET ch = GivenID; WHILE ch > 0 DO SELECT IFNULL(parent_id,-1) INTO ch FROM (SELECT parent_id FROM organization WHERE id = ch) A; IF ch > 0 THEN SET rv = CONCAT(rv,cm,ch); SET cm = ','; END IF; END WHILE; RETURN rv; END$$
CREATE FUNCTION `GetFamilyTree`(`GivenID` INT) RETURNS varchar(1024) CHARSET utf8 READS SQL DATA SQL SECURITY INVOKER BEGIN
SET rv = ''; SET queue = GivenID; SET queue_length = 1;
WHILE queue_length > 0 DO SET front_id = FORMAT(queue,0); IF queue_length = 1 THEN SET queue = ''; ELSE SET pos = LOCATE(',',queue) + 1; SET q = SUBSTR(queue,pos); SET queue = q; END IF; SET queue_length = queue_length - 1;
SELECT IFNULL(qc,'') INTO queue_children FROM (SELECT GROUP_CONCAT(id) qc FROM organization WHERE parent_id = front_id) A;
IF LENGTH(queue_children) = 0 THEN IF LENGTH(queue) = 0 THEN SET queue_length = 0; END IF; ELSE IF LENGTH(rv) = 0 THEN SET rv = queue_children; ELSE SET rv = CONCAT(rv,',',queue_children); END IF; IF LENGTH(queue) = 0 THEN SET queue = queue_children; ELSE SET queue = CONCAT(queue,',',queue_children); END IF; SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1; END IF; END WHILE; RETURN rv; END$$
DELIMITER ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;