PHP Classes
elePHPant
Icontem

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: 11 months 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;