CREATE DATABASE IF NOT EXISTS `ControlEO`;
CREATE USER IF NOT EXISTS 'control_user'@'localhost' IDENTIFIED BY 'qwerty';
GRANT ALL PRIVILEGES ON ControlEO.* TO 'control_user'@'localhost';
FLUSH PRIVILEGES;
USE `ControlEO`;
CREATE TABLE IF NOT EXISTS Departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(255) NOT NULL UNIQUE
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS Positions (
position_id INT AUTO_INCREMENT PRIMARY KEY,
position_name VARCHAR(255) NOT NULL UNIQUE
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS Executors (
executor_id INT AUTO_INCREMENT PRIMARY KEY,
executor_name VARCHAR(255) NOT NULL,
executor_contact VARCHAR(255),
department_id INT,
position_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
FOREIGN KEY (position_id) REFERENCES Positions(position_id)
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS Issuers (
issuer_id INT AUTO_INCREMENT PRIMARY KEY,
issuer_name VARCHAR(255) NOT NULL,
issuer_contact VARCHAR(255),
department_id INT,
position_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id),
FOREIGN KEY (position_id) REFERENCES Positions(position_id)
) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
order_name VARCHAR(255) NOT NULL,
order_content TEXT,
order_date_issued DATE NOT NULL,
order_deadline DATE NOT NULL,
order_date_completed DATE DEFAULT NULL,
executor_id INT NOT NULL,
issuer_id INT NOT NULL,
order_status ENUM('active', 'completed', 'overdue') DEFAULT 'active',
order_priority ENUM('high', 'medium', 'low') DEFAULT 'medium',
order_creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (executor_id) REFERENCES Executors (executor_id),
FOREIGN KEY (issuer_id) REFERENCES Issuers (issuer_id)
) ENGINE = InnoDB;
CREATE INDEX idx_orders_executor_id ON Orders (executor_id);
CREATE INDEX idx_orders_issuer_id ON Orders (issuer_id);
CREATE INDEX idx_orders_date_issued ON Orders (order_date_issued);
CREATE INDEX idx_orders_deadline ON Orders (order_deadline);
DROP TRIGGER IF EXISTS trg_auto_update_status;
CREATE TRIGGER trg_auto_update_status
BEFORE UPDATE ON Orders
FOR EACH ROW
BEGIN
IF NEW.order_date_completed IS NULL AND NEW.order_deadline < CURDATE() THEN
SET NEW.order_status = 'overdue';
ELSEIF NEW.order_date_completed IS NOT NULL AND NEW.order_status != 'completed' THEN
SET NEW.order_status = 'completed';
END IF;
END;
DELIMITER $$
CREATE PROCEDURE GetOrdersByDate(IN target_date DATE)
BEGIN
SELECT * FROM Orders
WHERE order_date_issued = target_date;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE PrintDailyOrders()
BEGIN
SELECT * FROM Orders
WHERE order_deadline = CURDATE();
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE GetOrdersByPeriod(IN start_date DATE, IN end_date DATE)
BEGIN
SELECT * FROM Orders
WHERE order_date_issued BETWEEN start_date AND end_date;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE GetOverdueOrders()
BEGIN
SELECT o.*, e.executor_name, i.issuer_name
FROM Orders o
JOIN Executors e ON o.executor_id = e.executor_id
JOIN Issuers i ON o.issuer_id = i.issuer_id
WHERE o.order_deadline < CURDATE() AND o.order_date_completed IS NULL;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE GetOrdersByExecutor(IN exec_id INT)
BEGIN
SELECT o.*, i.issuer_name
FROM Orders o
JOIN Issuers i ON o.issuer_id = i.issuer_id
WHERE o.executor_id = exec_id;
END$$
DELIMITER ;
USE `ControlEO`;
INSERT INTO
Departments (department_name)
VALUES
('IT Department'),
('Human Resources');
INSERT INTO
Positions (position_name)
VALUES
('Manager'),
('Specialist');
INSERT INTO
Executors (
executor_name,
executor_contact,
department_id,
position_id
)
VALUES
('John Doe', 'john.doe@company.com', 1, 1),
('Jane Smith', 'jane.smith@company.com', 2, 2);
INSERT INTO
Issuers (
issuer_name,
issuer_contact,
department_id,
position_id
)
VALUES
(
'Robert Johnson',
'robert.johnson@company.com',
1,
1
),
('Emily Davis', 'emily.davis@company.com', 2, 2);
INSERT INTO
Orders (
order_name,
order_content,
order_date_issued,
order_deadline,
order_date_completed,
executor_id,
issuer_id,
order_status,
order_priority
)
VALUES
(
'System Maintenance',
'Perform regular system maintenance',
'2025-03-15',
'2025-03-20',
'2025-03-19',
1,
1,
'completed',
'high'
),
(
'HR Policy Update',
'Update company HR policies',
'2025-03-20',
'2025-04-10',
NULL,
2,
2,
'active',
'medium'
);
Telegram: DB developers channel