SQLize | PHPize | SQLtest

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, executor_notes TEXT, 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, issuer_notes TEXT, 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', 'cancelled', 'failed') 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; DELIMITER $$ CREATE TRIGGER trg_auto_update_status BEFORE UPDATE ON Orders FOR EACH ROW BEGIN IF NEW.order_date_completed IS NULL AND NEW.order_status NOT IN ('cancelled', 'failed') AND NEW.order_deadline < CURDATE() THEN SET NEW.order_status = 'overdue'; ELSEIF NEW.order_date_completed IS NOT NULL AND NEW.order_status != 'completed' AND NEW.order_status != 'failed' AND NEW.order_status != 'cancelled' THEN SET NEW.order_status = 'completed'; END IF; END$$ DELIMITER ; 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 AND o.order_status NOT IN ('cancelled', 'failed'); 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 ; CREATE INDEX idx_orders_status ON Orders (order_status); CREATE INDEX idx_orders_date_completed ON Orders (order_date_completed); CREATE INDEX idx_orders_priority ON Orders (order_priority); CREATE INDEX idx_executors_department ON Executors (department_id); CREATE INDEX idx_executors_position ON Executors (position_id); CREATE INDEX idx_issuers_department ON Issuers (department_id); CREATE INDEX idx_issuers_position ON Issuers (position_id); CREATE INDEX idx_departments_name ON Departments (department_name); CREATE INDEX idx_positions_name ON Positions (position_name); CREATE INDEX idx_executors_name ON Executors (executor_name); CREATE INDEX idx_issuers_name ON Issuers (issuer_name); USE `ControlEO`; INSERT INTO Departments (department_name) VALUES ('IT Department'), ('Human Resources'), ('Marketing'), ('Finance'), ('Operations'), ('Customer Support'), ('Research & Development'), ('Sales'), ('Legal'), ('Quality Assurance'), ('Product Management'), ('Administration'); INSERT INTO Positions (position_name) VALUES ('Manager'), ('Specialist'), ('Director'), ('Coordinator'), ('Supervisor'), ('Assistant'), ('Analyst'), ('Team Lead'), ('Executive'), ('Junior Specialist'), ('Senior Specialist'), ('Consultant'); 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), ( 'Michael Brown', 'michael.brown@company.com', 3, 3 ), ('Emma Wilson', 'emma.wilson@company.com', 4, 4), ('David Lee', 'david.lee@company.com', 5, 5), ('Sarah Chen', 'sarah.chen@company.com', 6, 6), ( 'Thomas Wright', 'thomas.wright@company.com', 7, 7 ), ('Lisa Garcia', 'lisa.garcia@company.com', 8, 8), ( 'Kevin Robinson', 'kevin.robinson@company.com', 9, 9 ), ( 'Amanda Taylor', 'amanda.taylor@company.com', 10, 10 ), ( 'Richard Martin', 'richard.martin@company.com', 11, 11 ), ( 'Jessica White', 'jessica.white@company.com', 12, 12 ); 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), ('Daniel Clark', 'daniel.clark@company.com', 3, 3), ( 'Olivia Rodriguez', 'olivia.rodriguez@company.com', 4, 4 ), ('James Wilson', 'james.wilson@company.com', 5, 5), ( 'Sophia Martinez', 'sophia.martinez@company.com', 6, 6 ), ( 'William Anderson', 'william.anderson@company.com', 7, 7 ), ('Ava Thompson', 'ava.thompson@company.com', 8, 8), ( 'Alexander Harris', 'alexander.harris@company.com', 9, 9 ), ('Mia Lewis', 'mia.lewis@company.com', 10, 10), ( 'Benjamin Young', 'benjamin.young@company.com', 11, 11 ), ( 'Charlotte Hall', 'charlotte.hall@company.com', 12, 12 ); 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' ), ( 'Marketing Campaign', 'Develop new marketing campaign for product launch', '2025-03-25', '2025-04-15', NULL, 3, 3, 'active', 'high' ), ( 'Financial Report Q1', 'Prepare financial report for first quarter', '2025-03-10', '2025-04-05', '2025-04-02', 4, 4, 'completed', 'high' ), ( 'Process Optimization', 'Review and optimize operational processes', '2025-03-18', '2025-05-01', NULL, 5, 5, 'active', 'medium' ), ( 'Customer Support Training', 'Conduct training for customer support team', '2025-03-22', '2025-04-05', '2025-04-04', 6, 6, 'completed', 'low' ), ( 'Research Project', 'Research new technologies for product development', '2025-03-28', '2025-05-10', NULL, 7, 7, 'active', 'high' ), ( 'Sales Strategy', 'Develop new sales strategy for international markets', '2025-03-15', '2025-04-20', NULL, 8, 8, 'active', 'medium' ), ( 'Contract Review', 'Review and update vendor contracts', '2025-03-12', '2025-03-30', '2025-03-28', 9, 9, 'completed', 'low' ), ( 'Quality Testing', 'Perform quality testing on new product features', '2025-03-25', '2025-04-15', NULL, 10, 10, 'active', 'high' ), ( 'Product Roadmap', 'Update product roadmap for next quarter', '2025-03-20', '2025-04-10', NULL, 11, 11, 'active', 'medium' ), ( 'Office Supplies Order', 'Place order for office supplies', '2025-03-15', '2025-03-25', '2025-03-23', 12, 12, 'completed', 'low' ); DROP VIEW IF EXISTS OrdersDetailView; CREATE VIEW OrdersDetailView AS SELECT O.order_id, O.order_name, O.order_content, O.order_date_issued, O.order_deadline, O.order_date_completed, E.executor_name, I.issuer_name, D.department_name AS executor_department, O.order_status, O.order_priority FROM Orders O JOIN Executors E ON O.executor_id = E.executor_id JOIN Issuers I ON O.issuer_id = I.issuer_id JOIN Departments D ON E.department_id = D.department_id; DROP VIEW IF EXISTS ActiveOrdersView; CREATE VIEW ActiveOrdersView AS SELECT O.order_id, O.order_name, O.order_deadline, E.executor_name, I.issuer_name, O.order_priority 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_status = 'active'; DROP VIEW IF EXISTS DepartmentStatsView; CREATE VIEW DepartmentStatsView AS SELECT D.department_name, COUNT(DISTINCT E.executor_id) AS executor_count, COUNT(DISTINCT I.issuer_id) AS issuer_count, COUNT(O.order_id) AS order_count FROM Departments D LEFT JOIN Executors E ON D.department_id = E.department_id LEFT JOIN Issuers I ON D.department_id = I.department_id LEFT JOIN Orders O ON E.executor_id = O.executor_id OR I.issuer_id = O.issuer_id GROUP BY D.department_name;