-- 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;
|
Download
