PHP Classes

File: db/create.sql

Recommend this page to a friend!
  Classes of Istvan Dobrentei   PHP Timesheet Management System   db/create.sql   Download  
File: db/create.sql
Role: Auxiliary data
Content type: text/plain
Description: Auxiliary data
Class: PHP Timesheet Management System
Manage project tasks and the respective work times
Author: By
Last change:
Date: 5 years ago
Size: 16,218 bytes
 

Contents

Class file image Download
-- MySQL Script generated by MySQL Workbench -- Wed Aug 8 12:27:26 2018 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema timesheet_manager -- ----------------------------------------------------- DROP SCHEMA IF EXISTS `timesheet_manager` ; -- ----------------------------------------------------- -- Schema timesheet_manager -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `timesheet_manager` DEFAULT CHARACTER SET utf8 ; USE `timesheet_manager` ; -- ----------------------------------------------------- -- Table `timesheet_manager`.`user` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`user` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `last_name` VARCHAR(255) NULL DEFAULT NULL, `first_name` VARCHAR(255) NULL DEFAULT NULL, `active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1', `created_at` DATETIME NULL DEFAULT NULL, `created_by` INT(10) UNSIGNED NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `email_UNIQUE` (`email` ASC)) ENGINE = InnoDB AUTO_INCREMENT = 121 DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`project` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`project` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`project` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `parent_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `name` VARCHAR(255) NOT NULL, `description` LONGTEXT NOT NULL, `wt_sum_minutes` INT(11) NOT NULL DEFAULT '0', `active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1', `created_at` DATETIME NOT NULL, `created_by` INT(11) NOT NULL, PRIMARY KEY (`id`), INDEX `fk_project_user_id_idx` (`created_by` ASC), CONSTRAINT `fk_project_user_id` FOREIGN KEY (`created_by`) REFERENCES `timesheet_manager`.`user` (`id`) ON UPDATE CASCADE) ENGINE = InnoDB AUTO_INCREMENT = 122 DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`setting` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`setting` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`setting` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `group` VARCHAR(32) NOT NULL, `key` VARCHAR(64) NOT NULL, `value` MEDIUMTEXT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`user_project` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`user_project` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`user_project` ( `user_id` INT(11) NOT NULL, `project_id` INT(11) NOT NULL, `is_leader` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0', INDEX `fk_user_id_idx` (`user_id` ASC), INDEX `fk_project_id_idx` (`project_id` ASC), CONSTRAINT `fk_user_project_project_id` FOREIGN KEY (`project_id`) REFERENCES `timesheet_manager`.`project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_user_project_user_id` FOREIGN KEY (`user_id`) REFERENCES `timesheet_manager`.`user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`working_time` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`working_time` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`working_time` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `project_id` INT(11) NOT NULL, `date_from` DATETIME NOT NULL, `date_to` DATETIME NOT NULL, `description` LONGTEXT NULL DEFAULT NULL, `approved` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0', `approved_at` DATETIME NULL DEFAULT NULL, `approved_by` INT(11) NULL DEFAULT NULL, `created_at` DATETIME NOT NULL, `created_by` INT(11) NOT NULL, PRIMARY KEY (`id`), INDEX `fk_working_time_user_id_idx` (`created_by` ASC), INDEX `fk_working_time_project_id_idx` (`project_id` ASC), INDEX `fk_working_time_approved_by_idx` (`approved_by` ASC), CONSTRAINT `fk_working_time_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `timesheet_manager`.`user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_working_time_created_by` FOREIGN KEY (`created_by`) REFERENCES `timesheet_manager`.`user` (`id`) ON UPDATE CASCADE, CONSTRAINT `fk_working_time_project_id` FOREIGN KEY (`project_id`) REFERENCES `timesheet_manager`.`project` (`id`) ON UPDATE CASCADE) ENGINE = InnoDB AUTO_INCREMENT = 299 DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`working_time_summary` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`working_time_summary` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`working_time_summary` ( `year` INT(10) UNSIGNED NOT NULL, `month` INT(10) UNSIGNED NOT NULL, `day` INT(10) UNSIGNED NOT NULL, `wt_sum_minutes` INT(10) NULL DEFAULT NULL, `user_id` INT(11) NOT NULL, PRIMARY KEY (`year`, `month`, `day`), INDEX `fk_wt_summary_user_id_idx` (`user_id` ASC), CONSTRAINT `fk_wt_summary_user_id` FOREIGN KEY (`user_id`) REFERENCES `timesheet_manager`.`user` (`id`) ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`invoice` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`invoice` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`invoice` ( `invoice_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `invoice_no` VARCHAR(10) NOT NULL, PRIMARY KEY (`invoice_id`), UNIQUE INDEX `invoice_no_UNIQUE` (`invoice_no` ASC)) ENGINE = InnoDB; DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `timesheet_manager`.`invoice_item` -- ----------------------------------------------------- DROP TABLE IF EXISTS `timesheet_manager`.`invoice_item` ; CREATE TABLE IF NOT EXISTS `timesheet_manager`.`invoice_item` ( `invoice_item_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `invoice_id` INT UNSIGNED NOT NULL, `wt_id` INT NOT NULL, PRIMARY KEY (`invoice_item_id`), INDEX `fk_inv_item_wt_idx` (`wt_id` ASC), INDEX `fk_inv_item_inv_idx` (`invoice_id` ASC), CONSTRAINT `fk_inv_item_wt` FOREIGN KEY (`wt_id`) REFERENCES `timesheet_manager`.`working_time` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_inv_item_inv` FOREIGN KEY (`invoice_id`) REFERENCES `timesheet_manager`.`invoice` (`invoice_id`) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE = InnoDB; DEFAULT CHARACTER SET = utf8; USE `timesheet_manager` ; -- ----------------------------------------------------- -- procedure setDayWorkingHours -- ----------------------------------------------------- USE `timesheet_manager`; DROP procedure IF EXISTS `timesheet_manager`.`setDayWorkingHours`; DELIMITER $$ USE `timesheet_manager`$$ CREATE DEFINER=`root`@`%` PROCEDURE `setDayWorkingHours`(IN start_date DATETIME, IN end_date DATETIME, IN puser_id INT, IN papproved INT) BEGIN DECLARE start_year INT; DECLARE end_year INT; DECLARE start_month INT; DECLARE end_month INT; DECLARE start_day INT; DECLARE end_day INT; DECLARE sum_minutes INT DEFAULT -1; DECLARE start_day_minutes INT; DECLARE end_day_minutes INT; DECLARE startpone DATE; DECLARE sum_days INT; -- SET start_year := (SELECT EXTRACT(YEAR FROM start_date)); SELECT EXTRACT(YEAR FROM start_date) INTO start_year; -- SET start_month := (SELECT EXTRACT(MONTH FROM start_date)); SELECT EXTRACT(MONTH FROM start_date) INTO start_month; -- SET start_day := (SELECT EXTRACT(DAY FROM start_date)); SELECT EXTRACT(DAY FROM start_date) INTO start_day; -- SET end_year := (SELECT EXTRACT(YEAR FROM end_date)); SELECT EXTRACT(YEAR FROM end_date) INTO end_year; -- SET end_month := (SELECT EXTRACT(MONTH FROM end_date)); SELECT EXTRACT(MONTH FROM end_date) INTO end_month; -- SET end_day := (SELECT EXTRACT(DAY FROM end_date)); SELECT EXTRACT(DAY FROM end_date) INTO end_day; IF start_day = end_day AND start_month = end_month AND start_year = end_year THEN /* Ha egy napon belül történt a munkavégzés */ -- SET sum_minutes := (SELECT wt_sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id); SELECT wt_sum_minutes INTO sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; IF sum_minutes =-1 THEN INSERT INTO working_time_summary (year,month,day,wt_sum_minutes,user_id) VALUES (start_year,start_month,start_day, TIMESTAMPDIFF(MINUTE, start_date, end_date), puser_id); ELSE IF papproved = 1 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes + TIMESTAMPDIFF(MINUTE, start_date, end_date) WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; ELSEIF papproved = 0 AND sum_minutes > 0 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes - TIMESTAMPDIFF(MINUTE, start_date, end_date) WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; END IF; END IF; ELSE /* Ha több napon belül történt a munkavégzés és több napot érint */ SET startpone := DATE_ADD(start_date, INTERVAL 1 DAY); /* Az intervallum els? napjának munkaideje percekben */ -- SET start_day_minutes := ABS((SELECT TIMESTAMPDIFF(MINUTE, DATE(startpone), start_date))); SELECT ABS(TIMESTAMPDIFF(MINUTE, DATE(startpone), start_date)) INTO start_day_minutes; /* Az intervallum els? napjának kezelése */ -- SET sum_minutes := (SELECT wt_sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id); SELECT wt_sum_minutes INTO sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; IF sum_minutes =-1 THEN INSERT INTO working_time_summary (year,month,day,wt_sum_minutes,user_id) VALUES (start_year,start_month,start_day, start_day_minutes, puser_id); ELSE IF papproved = 1 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes + start_day_minutes WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; ELSEIF papproved = 0 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes - start_day_minutes WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; END IF; END IF; /* Ha az els? és az utolsó nap közötti különbség nagyobb, mint 1 */ -- SET sum_days := (SELECT TIMESTAMPDIFF(DAY, DATE(start_date), DATE(end_date))); SELECT TIMESTAMPDIFF(DAY, DATE(start_date), DATE(end_date)) INTO sum_days; IF sum_days > 1 THEN /* iterálunk az intervallum napjai között (kivéve a kezd? és záró dátumot), ilyenkor kezeljük a 24*60 percet a teljes napra */ WHILE DATE(startpone) < DATE(end_date) DO -- SET start_year := (SELECT EXTRACT(YEAR FROM startpone)); SELECT EXTRACT(YEAR FROM startpone) INTO start_year; -- SET start_month := (SELECT EXTRACT(MONTH FROM startpone)); SELECT EXTRACT(MONTH FROM startpone) INTO start_month; -- SET start_day := (SELECT EXTRACT(DAY FROM startpone)); SELECT EXTRACT(DAY FROM startpone) INTO start_day; -- SET sum_minutes := (SELECT wt_sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id); SELECT IFNULL(wt_sum_minutes, -1) INTO sum_minutes FROM working_time_summary WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; IF sum_minutes =-1 THEN INSERT INTO working_time_summary (year,month,day,wt_sum_minutes,user_id) VALUES (start_year,start_month,start_day, 24*60, puser_id); ELSE IF papproved = 1 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes + (24*60) WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; ELSEIF papproved = 0 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes - (24*60) WHERE year=start_year AND month=start_month AND day=start_day AND user_id=puser_id; END IF; END IF; SET startpone := DATE_ADD(startpone, INTERVAL 1 DAY); END WHILE; END IF; /* Az intervallum utolsó napjának munkaideje percekben */ -- SET end_day_minutes := (SELECT EXTRACT(HOUR FROM TIME(end_date)) * 60 + EXTRACT(MINUTE FROM TIME(end_date))); SELECT EXTRACT(HOUR FROM TIME(end_date)) * 60 + EXTRACT(MINUTE FROM TIME(end_date)) INTO end_day_minutes; /* Az intervallum utolsó napjának kezelése */ -- SET sum_minutes := (SELECT wt_sum_minutes FROM working_time_summary WHERE year=end_year AND month=end_month AND day=end_day AND user_id=puser_id); SELECT wt_sum_minutes FROM working_time_summary WHERE year=end_year AND month=end_month AND day=end_day AND user_id=puser_id INTO sum_minutes; IF sum_minutes = -1 THEN INSERT INTO working_time_summary (year,month,day,wt_sum_minutes,user_id) VALUES (end_year,end_month,end_day, end_day_minutes, puser_id); ELSE IF papproved = 1 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes + end_day_minutes WHERE year=end_year AND month=end_month AND day=end_day AND user_id=puser_id; ELSEIF papproved = 0 THEN UPDATE working_time_summary SET wt_sum_minutes = wt_sum_minutes - end_day_minutes WHERE year=end_year AND month=end_month AND day=end_day AND user_id=puser_id; END IF; END IF; END IF; END$$ DELIMITER ; USE `timesheet_manager`; DELIMITER $$ USE `timesheet_manager`$$ DROP TRIGGER IF EXISTS `timesheet_manager`.`working_time_AFTER_UPDATE` $$ USE `timesheet_manager`$$ CREATE DEFINER=`root`@`%` TRIGGER `timesheet_manager`.`working_time_AFTER_UPDATE` AFTER UPDATE ON `timesheet_manager`.`working_time` FOR EACH ROW BEGIN DECLARE sum_minutes INT DEFAULT 0; SELECT IFNULL(SUM(timestampdiff(MINUTE, date_from, date_to)),0) INTO sum_minutes FROM working_time WHERE project_id=NEW.project_id AND approved=1; UPDATE project SET wt_sum_minutes=sum_minutes WHERE id=NEW.project_id; /* statisztikai célból letárolom az összesített napi munkaid?t a summary táblába az adott felhasználóhoz akkor, ha jóváhagyták, illetve újraszámolom, ha visszavonták */ CALL setDayWorkingHours(NEW.date_from, NEW.date_to, NEW.created_by, NEW.approved); END$$ USE `timesheet_manager`$$ DROP TRIGGER IF EXISTS `timesheet_manager`.`working_time_AFTER_DELETE` $$ USE `timesheet_manager`$$ CREATE DEFINER=`root`@`%` TRIGGER `timesheet_manager`.`working_time_AFTER_DELETE` AFTER DELETE ON `timesheet_manager`.`working_time` FOR EACH ROW BEGIN DELETE FROM working_time_summary WHERE wt_sum_minutes = 0; END$$ DELIMITER ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;