-- MySQL Workbench Forward Engineering


SET GLOBAL log_bin_trust_function_creators = 1;


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema hr
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema hr
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `hr` DEFAULT CHARACTER SET utf8mb3 ;
USE `hr` ;
USE `hr` ;

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`detect_fraud`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`detect_fraud` (`id` INT, `user_hash` INT, `fingerprint_type` INT, `fingerprint_time` INT, `in_out` INT, `data_entry` INT, `hash` INT, `location` INT, `user_create_record` INT, `user_update_record` INT, `mobile_info` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`emp_info`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`emp_info` (`emp_id` INT, `emp_hash` INT, `emp_name` INT, `primary_salary` INT, `vacations_limit` INT, `sick_leave` INT, `work_from` INT, `work_to` INT, `allow_time` INT, `location` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`emp_today_status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`emp_today_status` (`name` INT, `status` INT, `time` INT, `arrive_time` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`emp_vacation_summery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`emp_vacation_summery` (`name` INT, `count` INT, `year` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`finger_print`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`finger_print` (`user_hash` INT, `type1` INT, `in_at` INT, `type2` INT, `out_at` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`payroll_main`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`payroll_main` (`user_hash` INT, `emp_name` INT, `primary_salary` INT, `annual_percentage_increase` INT, `annual_price_increase` INT, `start_work_date` INT, `emp_info_id` INT, `specification_id` INT, `specifications_type` INT, `name` INT, `percentage` INT, `price` INT, `maximumPrice` INT, `nameValue` INT);

-- -----------------------------------------------------
-- Placeholder table for view `hr`.`test_emp_payroll`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `hr`.`test_emp_payroll` (`emp_name` INT, `user_hash` INT, `start_work_date` INT, `primary_salary` INT, `annual_percentage_increase` INT, `annual_price_increase` INT, `number_of_year` INT, `price_increase_per_year` INT, `price_group` INT);

-- -----------------------------------------------------
-- procedure CalculateMonthlyVacationDays
-- -----------------------------------------------------

DELIMITER $$


USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `CalculateMonthlyVacationDays`(IN userHash VARCHAR(255), IN startDateInput VARCHAR(255), IN endDateInput VARCHAR(255))
BEGIN
    SELECT 
        v.id,
        v.user_hash,
        vt.hash AS vacation_type_hash,
        v.date_start AS startDate,
        v.date_end AS endDate,
        SUM(DATEDIFF(v.date_end, v.date_start)) AS difference,
        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) AS number_of_hours_per_day,
        vt.type AS vacation_type,

        IFNULL(
            CASE 
                WHEN vt.hash IN ('111', '222', '555', '333', '444') THEN  
                
                    CASE
                        WHEN v.date_start < CONCAT(DATE(v.date_start), ' ', ei.work_from) THEN
                            -- Adjust the start time to the work start time if the vacation starts before work
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        WHEN v.date_end > CONCAT(DATE(v.date_start), ' ', ei.work_to) THEN
                            -- Adjust the end time to the work end time if the vacation ends after work
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        ELSE
                            ROUND(TIMESTAMPDIFF(MINUTE, v.date_start, v.date_end) / 60.0)
                    END
                ELSE 0
            END,
        0) AS number_of_hours_v,

        IFNULL(
            CASE 
                WHEN DATEDIFF(v.date_end, v.date_start) = 0 AND vt.hash IN ('111','333','444','222', '555') THEN
                    IF(TIMESTAMPDIFF(HOUR, v.date_start, v.date_end) >= (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash),
                        1, -- If less than a day, consider it as a full day
                        ROUND(SUM(DATEDIFF(v.date_end, v.date_start)) * 
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) /
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)) -- Otherwise, calculate the days normally
                    )
                WHEN v.vication_type_hash IN ('111','333','444','222', '555') THEN
                    Round(SUM(DATEDIFF(v.date_end, v.date_start)) 
                        * 
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        /
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash))

                WHEN (v.date_start IS NULL OR v.date_end IS NULL) THEN
                    0
                ELSE 
                    'not found'
            END,
        0
        ) AS number_of_days
    FROM 
        vacations_type vt 
    LEFT JOIN 
        vacations v ON vt.hash = v.vication_type_hash AND v.status = '111' 
        AND v.user_hash = userHash 
       
			AND DATE(v.date_start) >= startDateInput 
             AND DATE(v.date_end) <= endDateInput
          -- AND DATE(v.date_start) >= '2024-03-01' 
          -- AND DATE(v.date_end) <= '2024-04-01'
    LEFT JOIN 
        emp_information ei ON v.user_hash = ei.user_hash
    GROUP BY 
        v.user_hash, 
        vt.hash;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure CalculateMonthlyVacationDays(IN userHash VARCHAR(255))
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE  `CalculateMonthlyVacationDays(IN userHash VARCHAR(255))`()
BEGIN
    SELECT 
        v.id,
        v.user_hash,
        vt.hash AS vacation_type_hash,
        v.date_start AS startDate,
        v.date_end AS endDate,
        SUM(DATEDIFF(v.date_end, v.date_start)) AS difference,
        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) AS number_of_hours_per_day,
        vt.type AS vacation_type,

        IFNULL(
            CASE 
                WHEN vt.hash IN ('111', '222', '555', '333', '444') THEN  
                    CASE
                        WHEN v.date_start < CONCAT(DATE(v.date_start), ' ', ei.work_from) THEN
                            -- Adjust the start time to the work start time if the vacation starts before work
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        WHEN v.date_end > CONCAT(DATE(v.date_start), ' ', ei.work_to) THEN
                            -- Adjust the end time to the work end time if the vacation ends after work
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        ELSE
                            ROUND(TIMESTAMPDIFF(MINUTE, v.date_start, v.date_end) / 60.0)
                    END
                ELSE 0
            END,
        0) AS number_of_hours_v,

        IFNULL(
            CASE 
                WHEN DATEDIFF(v.date_end, v.date_start) = 0 AND vt.hash IN ('111','333','444','222', '555') THEN
                    IF(TIMESTAMPDIFF(HOUR, v.date_start, v.date_end) >= (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash),
                        1, -- If less than a day, consider it as a full day
                        ROUND(SUM(DATEDIFF(v.date_end, v.date_start)) * 
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) /
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)) -- Otherwise, calculate the days normally
                    )
                WHEN v.vication_type_hash IN ('111','333','444','222', '555') THEN
                    Round(SUM(DATEDIFF(v.date_end, v.date_start)) 
                        * 
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        /
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash))

                WHEN (v.date_start IS NULL OR v.date_end IS NULL) THEN
                    0
                ELSE 
                    'not found'
            END,
        0
        ) AS number_of_days
    FROM 
        vacations_type vt 
    LEFT JOIN 
        vacations v ON vt.hash = v.vication_type_hash AND v.status = '111' 
        AND v.user_hash = userHash 
        AND YEAR(v.date_start) = YEAR(CURRENT_DATE())
        AND MONTH(v.date_start) = MONTH(CURDATE())
    LEFT JOIN 
        emp_information ei ON v.user_hash = ei.user_hash
    GROUP BY 
        v.user_hash, 
        vt.hash;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function check_if_in_exist
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `check_if_in_exist`(user_hash_para bigint(20)) RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash=user_hash_para and  date(fingerprint_time)=curdate() and in_out='in'; 

RETURN number_of_row;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function check_in_fingerprint
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `check_in_fingerprint`(user_hash_para bigint(20),type_fingerprint varchar(20)) RETURNS int
BEGIN
DECLARE number_of_row int ; 
DECLARE in_time time ; 
DECLARE out_time time ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash=user_hash_para and  date(fingerprint_time)=curdate() and in_out=type_fingerprint; 
#SELECT  disable_in_finger_time,disable_out_finger_time into in_time,in_time FROM emp_fingerprint where user_hash=user_hash_para ;

RETURN number_of_row;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure finger_print_report
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `finger_print_report`(
in date_from_v date , date_to_v date
)
BEGIN
	DECLARE salary_per_sec,emp_id_v,base_salary_v,sick_leave_v,number_of_holidays_v double DEFAULT 0;
    DECLARE emp_hash_v bigint default 0;
    DECLARE vacations_limit_v,work_from_v , work_to_v , allow_time_v time default '00:00:00' ;
    DECLARE emp_name_v,location_v varchar(255) DEFAULT '' ;
	DECLARE done_v TINYINT DEFAULT FALSE;
	
      -- variables for setting  
	DECLARE  over_time_after_v time default '00:00:00' ; -- موعد بدء احتساب الساعات الاضافية
	DECLARE  over_time_allow_v time default '00:15:00' ; -- الوقت المضاف على نهاية الدوام لبدء احتساب الساعات الاضافية
	-- variables for save emp result befor insert in table

    DECLARE cut_off_amount_v,cut_off_days_v double default 0 ;
    -- DECLARE hour_work_day_v double default 0 ;
    declare vacation_limit_out_v,finger_limit_out_v,finger_limit_out_leave_v,cut_off_hours_v,over_time_v,over_time_holiday_v,work_hour_target_v,duty_hour_v,vacation_hour_v,sick_vacation_hour_v,vacation_limit_day_v,work_hour_v,hour_work_day_v,in_allow_diff,absence_hour_v time default '00:00:00';
    
    DECLARE cursor_emp_info CURSOR FOR SELECT emp_info.`emp_id` ,emp_info.`emp_hash`, emp_info.`emp_name` , emp_info.`primary_salary`,   emp_info.`vacations_limit`,    emp_info.`sick_leave`,   emp_info.`work_from`,  emp_info.`work_to`,   emp_info.`allow_time`,  emp_info.`location` FROM `emp_info`  inner join emp_information on emp_information.user_hash = emp_info.emp_hash and is_work = 1 and start_work_date <= date_to_v;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_v = TRUE;
    
	DROP TABLE IF EXISTS  finger_print_report ;
	CREATE TABLE `finger_print_report` (
			  `id` int NOT NULL AUTO_INCREMENT,
			  `name` varchar(255) DEFAULT NULL,
			  `emp_hash` bigint DEFAULT 0,
			  `vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة',
			  `vacation_limit_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المسموح بها ',
			  `duty_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الواجب',
			  `vacation_limit_out` time DEFAULT '00:00:00' COMMENT 'تجاوز الساعات المسموح بها',
			  `work_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الحضور',
			  `finger_limit_out`time DEFAULT '00:00:00' COMMENT 'ساعات التاخير البصمة',
			  `work_hour_target`time DEFAULT '00:00:00' COMMENT 'ساعات الدوام المطلوبة',
			  `cut_off_hours` time DEFAULT '00:00:00' COMMENT 'ساعات الخصم',
			  `over_time` time DEFAULT '00:00:00' COMMENT 'ساعات اضافية',
			  `cut_off_amount` double DEFAULT 0 COMMENT 'مبلغ الخصم',
			  `sick_vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المرضية',
			  `cut_off_days` double DEFAULT '0' COMMENT 'ايام  الخصم',
			  `absence_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الغياب',
              `finger_limit_out_leave` time DEFAULT '00:00:00' COMMENT 'ساعات المغادره قبل انتهاء الدوام الرسمي',
	
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;



	
	OPEN cursor_emp_info;
    -- calculate number of day work between date_from_v and date_to_v 
	set number_of_holidays_v = (select count(*) from holiday where holiday.date between date_from_v and date_to_v) ; 
		loop_on_each_emp: 
		LOOP
			set vacation_hour_v = 0 ;
            set sick_vacation_hour_v = 0 ;
			set over_time_after_v = '00:00:00' ;
            set in_allow_diff = '00:00:00' ;
			FETCH cursor_emp_info INTO emp_id_v  , emp_hash_v , emp_name_v , base_salary_v,   vacation_limit_day_v,    sick_leave_v,   work_from_v,  work_to_v,   allow_time_v,  location_v ; 
			-- INSERT INTO `log_procedure` (`msg`)VALUES(concat('done_v is: ', done_v));
			-- INSERT INTO `log_procedure` (`msg`)VALUES(concat('done_v 1: ', done_v));
            
            
			-- set in_allow_diff = allow_time_v - work_from_v ;
            set in_allow_diff = timediff(allow_time_v , work_from_v) ;
            
            
			IF done_v THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
				LEAVE loop_on_each_emp ; 
			ELSE 
				-- INSERT INTO `log_procedure` (`msg`)VALUES(concat('hours is: ', work_hour_v,' emp_hash ',emp_hash_v));
				-- INSERT INTO `log_procedure` (`msg`)VALUES(concat('done_v 2: ', done_v));
				# INSERT INTO `log_procedure` (`msg`)VALUES(concat('emp_id: ', emp_id_v, ' and emp_hash is: ', emp_hash_v, ' and emp_name is: ', emp_name_v,' and base_salary is: ', base_salary_v, ' and vacations_limit is: ', vacations_limit_v,' and sick_leave is: ', sick_leave_v, ' and work_from is: ', work_from_v,' and work_to is: ', work_to_v, ' and allow_time is: ',allow_time_v,' and location is: ', location_v));
			    -- select TIMEDIFF(work_to , allow_time_v) into hour_work_day_v  from emp_information  where user_hash = emp_hash_v;
                select TIMEDIFF(work_to , work_from) into hour_work_day_v  from emp_information  where user_hash = emp_hash_v;
			            
                -- calculate number of hours for normal and part vacation
                set vacation_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,222,emp_hash_v)))) ; 
   			    -- INSERT INTO `log_procedure` (`msg`)VALUES(concat('emp_hash_v : ', emp_hash_v , ' vacation_hour_v ' , vacation_hour_v , ' hour_work_day_v ', hour_work_day_v , ' time_to_sec(hour_work_day_v) ' ,time_to_sec(hour_work_day_v)));

				set vacation_hour_v = sec_to_time(time_to_sec(vacation_hour_v) + time_to_sec( get_vacations_hours(date_from_v,date_to_v,333,emp_hash_v))) ; 
			
				set vacations_limit_v = (SEC_TO_TIME((vacation_limit_day_v * time_to_sec(hour_work_day_v))/12)) ;
				set vacation_limit_out_v =if( (time_to_sec(vacation_hour_v)- vacations_limit_v ) > 0,SEC_TO_TIME(time_to_sec(vacation_hour_v)- vacations_limit_v ),0);
				--  ----------------------------------------------
                -- calculate number of hours for absence
				set absence_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,555,emp_hash_v)))) ; 
                IF(absence_hour_v IS NULL) THEN
					SET absence_hour_v := '00:00:00';
				END IF;
				-- ----------------------------------------
				-- calculate number of hours for sick vacation
				set sick_vacation_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,111,emp_hash_v)))) ; 
                
                IF(sick_vacation_hour_v IS NULL) THEN
					SET sick_vacation_hour_v := '00:00:00';
				END IF;
				-- ----------------------------------------
  			    -- INSERT INTO `log_procedure` (`msg`)VALUES(concat('done_v 5: ', done_v));

                -- calculate number of hours for duty
				set duty_hour_v =   ( get_vacations_hours(date_from_v,date_to_v,444,emp_hash_v)) ;
			    -- INSERT INTO `log_procedure` (`msg`)VALUES(concat('done_v 6: ', done_v));
				-- --------------------------------------------
                -- calculate target hour between date_from_v and date_to_v 
				set work_hour_target_v = sec_to_time((time_to_sec(hour_work_day_v) * (DATEDIFF(date_to_v , date_from_v) - number_of_holidays_v + 1 )) - time_to_sec(vacation_hour_v) - time_to_sec(sick_vacation_hour_v) - time_to_sec(duty_hour_v) ) ;
                -- ------------------------------
				-- calculate work hour from finger print
                set work_hour_v  = (SELECT 
				-- ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,work_to_v,time(print_out.fingerprint_time  )), if(time(print_in.fingerprint_time)  > work_from_v ,time(print_in.fingerprint_time) , work_from_v)),0)))),'00:00:00') as 'h' 
				-- ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,work_to_v,time(print_out.fingerprint_time  )), if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) - time(in_allow_diff) , work_from_v)),0)))),'00:00:00') as 'h' 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,work_to_v,time(print_out.fingerprint_time  )), if(time(print_in.fingerprint_time)  > allow_time_v , timediff( time(print_in.fingerprint_time) , time(in_allow_diff))  , work_from_v)),0)))),'00:00:00') as 'h' 

                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and time(print_in.fingerprint_time) <= work_to_v
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                group by print_in.user_hash);
                                
				IF(work_hour_v IS NULL) THEN
					SET work_hour_v := '00:00:00';
				END IF;
				-- ---------------------------------------------------
                -- calculate late hour
				set finger_limit_out_v  = (SELECT 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) , allow_time_v),allow_time_v),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL 
                group by print_in.user_hash);
                                
				IF(finger_limit_out_v IS NULL) THEN
					SET finger_limit_out_v := '00:00:00';
				END IF;
                
                -- ---------------------------------------
                -- calculate Departure before the end of the official working hours
                
                set finger_limit_out_leave_v = (SELECT 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( work_to_v,if(time(print_out.fingerprint_time)  < work_to_v ,time(print_out.fingerprint_time) , work_to_v)),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                group by print_in.user_hash);
                                
				IF(finger_limit_out_leave_v IS NULL) THEN
					SET finger_limit_out_leave_v := '00:00:00';
				END IF;
                
				set finger_limit_out_v  = ADDTIME(finger_limit_out_v,finger_limit_out_leave_v);   
                
                
                
                -- calculate cut_off_hours_v
				set cut_off_hours_v = TIMEDIFF(work_hour_target_v , work_hour_v  ) ;
                -- ----------------------------
                -- calculate overtime hour from finger print
                /*
                set over_time_v  = (SELECT 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,time(print_out.fingerprint_time),work_to_v),if(time(print_in.fingerprint_time) > work_to_v , time(print_in.fingerprint_time) ,work_to_v ) ),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL  group by print_in.user_hash);
                                
				IF(over_time_v IS NULL) THEN
					SET over_time_v := '00:00:00';
				END IF;
                */
				-- ---------------------------------------------------
                -- ----------------------------------------------------- calculate late hour
                set finger_limit_out_v = (SELECT 
				-- ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) , allow_time_v),allow_time_v),0)))),'00:00:00') as 'h' 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) , work_from_v),work_from_v),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out FORCE INDEX (index5) 
			
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                group by print_in.user_hash);
				IF(finger_limit_out_v IS NULL) THEN
					SET finger_limit_out_v := '00:00:00';
				END IF;
                -- --------------------------------------------------
                -- calculate Departure before the end of the official working hours
                
                set finger_limit_out_leave_v = (SELECT 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( work_to_v,if(time(print_out.fingerprint_time)  < work_to_v ,time(print_out.fingerprint_time) , work_to_v)),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out  FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                
                group by print_in.user_hash);
                                
				IF(finger_limit_out_leave_v IS NULL) THEN
					SET finger_limit_out_leave_v := '00:00:00';
				END IF;
                
				set finger_limit_out_v  = ADDTIME(finger_limit_out_v,finger_limit_out_leave_v);   

                -- ----------------------------------------------------
                
                -- ----------------------------from alumex
                -- calculate cut_off_hours_v
				-- set cut_off_hours_v = TIMEDIFF(work_hour_target_v , TIMEDIFF(work_hour_v,absence_hour_v)  ) ;
                set cut_off_hours_v = TIMEDIFF(work_hour_target_v , work_hour_v ) ;
				-- set cut_off_hours_v = SEC_TO_TIME( TIME_TO_SEC(vacation_limit_out_v) + TIME_TO_SEC(finger_limit_out_v) + TIME_TO_SEC(absence_hour_v) ) ;
                -- ----------------------------
                
                 -- calculate cut_off_hours_v
				set cut_off_days_v = TIME_TO_SEC(cut_off_hours_v)/TIME_TO_SEC(hour_work_day_v) ;
                -- ----------------------------
                
                
                
                -- calculate overtime hour from finger print
                set over_time_after_v =  ADDTIME(work_to_v,over_time_allow_v);
                
                set over_time_v  = (SELECT 
				-- ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,time(print_out.fingerprint_time  ),work_to_v), work_to_v ),0)))),'00:00:00') as 'h' 
                -- ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > over_time_after_v,time(print_out.fingerprint_time  ),over_time_after_v), over_time_after_v ),0)))),'00:00:00') as 'h' 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > over_time_after_v,time(print_out.fingerprint_time  ),work_to_v), if(time(print_in.fingerprint_time) <= work_to_v  ,work_to_v, time(print_in.fingerprint_time) ) ),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and time(print_out.fingerprint_time) > work_to_v
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                group by print_in.user_hash);
               
                         
				IF(over_time_v IS NULL) THEN
					SET over_time_v := '00:00:00';
				END IF;
            
				   -- calculate work hour when emp print at holiday as  overtime         
                set over_time_holiday_v = (SELECT 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(time(print_out.fingerprint_time), time(print_in.fingerprint_time)),0)))),'00:00:00') as 'h' 
				FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				left JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
                where holiday.id IS  not NULL
                group by print_in.user_hash);
               
               
               
               IF(over_time_holiday_v IS NULL) THEN
					SET over_time_holiday_v := '00:00:00';
				END IF;
                
			   set over_time_v  = ADDTIME(over_time_v,over_time_holiday_v);   
                         
				-- ---------------------------------------------------
                
                
                
                
				-- calculate amount cut of
				 
                set salary_per_sec =  (base_salary_v / TIME_TO_SEC(work_hour_target_v));
                
                set cut_off_amount_v = round((TIME_TO_SEC(cut_off_hours_v) * salary_per_sec),2) ;
				-- ---------------------------------------------------
                
				-- INSERT INTO `log_procedure` (`msg`)VALUES(concat('  ', ( time_to_sec(vacation_hour_v)-((vacation_limit_day_v * time_to_sec(hour_work_day_v))/12))));
				-- ***** cut_off_hours = cut_off_hours_v + vacation_limit_out 
					INSERT INTO `finger_print_report`(`name`,`emp_hash`,`vacation_hour`,`vacation_limit_hour`,`duty_hour`,`vacation_limit_out`,`work_hour`,`finger_limit_out`,`work_hour_target`,`cut_off_hours`,`over_time`,`cut_off_amount`,`sick_vacation_hour`,`cut_off_days`,`finger_limit_out_leave`, `absence_hour`)
				VALUES 
				(emp_name_v,
                emp_hash_v,
                vacation_hour_v,
                vacations_limit_v,duty_hour_v
                ,vacation_limit_out_v ,
                work_hour_v,
                finger_limit_out_v,
                work_hour_target_v,
                -- ADDTIME(cut_off_hours_v , vacation_limit_out_v) ,
                cut_off_hours_v ,
                over_time_v,
                cut_off_amount_v,
                sick_vacation_hour_v,
                cut_off_days_v,
                finger_limit_out_leave_v,
                absence_hour_v
                );

			END IF;
		END LOOP;
		#SELECT  `name` as 'الاسم',    `vacation_hour` as 'ساعات الاجازة',    `vacation_limit_hour` as 'ساعات الاجازة المسموح بها',  `duty_hour` as 'ساعات الواجب',`vacation_limit_out` as 'تجاوز الساعات المسموح بها',  `work_hour` as 'ساعات الحضور',  `finger_limit_out` as 'ساعات التاخير البصمة',  `work_hour_target` as 'ساعات الدوام المطلوبة', `cut_off_hours` as 'ساعات الخصم',   `over_time` as 'ساعات اضافية', `cut_off_amount` as 'مبلغ الخصم'FROM `finger_print_report`;	CLOSE cursor_emp_info;
       	SELECT  `name` ,    `vacation_hour` ,    `vacation_limit_hour` ,  `duty_hour` ,`vacation_limit_out` ,  `work_hour` ,  `finger_limit_out` ,  `work_hour_target` , `cut_off_hours` ,   `over_time` , `cut_off_amount`,`sick_vacation_hour` FROM `finger_print_report`;	CLOSE cursor_emp_info;
# select * from finger_print_report ;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure fp_report
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE  IF NOT EXISTS `fp_report`(IN start_date DATE,
    IN end_date DATE,
    IN department_id INT,
    IN search VARCHAR(255),
    IN page1 INT,
    IN perPage INT)
BEGIN


call hr.finger_print_report(start_date, end_date);
select 
  finger_print_report.*,
  partments.name as 'department' ,
  image_profile
from 
  finger_print_report 
left join system_users on system_usres.hash = finger_print_report.emp_hash
left join
  emp_information on emp_information.user_hash = finger_print_report.emp_hash
left join
  partments on partments.hash = emp_information.partment
  
   where partments.hash LIKE CONCAT('%', department_id, '%') AND
        finger_print_report.emp_hash LIKE CONCAT('%', search, '%')
        
        limit perPage  offset page1;
   
  

END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure getNotFingerPrint
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `getNotFingerPrint`(dt_start date, dt_end date)
BEGIN
CREATE TEMPORARY TABLE TempTable (dt date, name char(250),data_entey datetime, holiday date); 
WHILE dt_start <= dt_end DO
	Insert into TempTable SELECT date(dt_start) AS date, name,
    (select data_entry from emp_fingerprint where system_users.hash=emp_fingerprint.user_hash and in_out="in" and date(data_entry)=dt_start limit 1) as x,
    holiday.date as holiday
    from system_users 
    left join holiday on holiday.date=dt_start;
    SET dt_start = date_add(dt_start, INTERVAL 1 DAY);
END WHILE;
select * from TempTable where data_entey IS NULL and holiday is null;
DROP TEMPORARY TABLE TempTable;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_vacations_current_month
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_current_month`(
	vacations_type_v bigint, emp_hash_v bigint
) RETURNS double
    DETERMINISTIC
BEGIN
    DECLARE number_of_hours_v double;
	DECLARE number_of_day double;
(SELECT 
    TIMESTAMPDIFF(HOUR, work_from, work_to)
INTO number_of_day FROM
    emp_information
WHERE
    user_hash = emp_hash_v);
    CASE vacations_type_v
        WHEN '111' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)), 0) * number_of_day INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND year(date_start) = year(CURDATE())
                AND status = '111'
                AND vication_type_hash = '111';
        
        WHEN '222' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)), 0) * number_of_day INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND year(date_start) = year(CURDATE())
                AND status = '111'
                AND vication_type_hash = '222';
        
        WHEN '333' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end, date_start)))), '00:00:00') INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND year(date_start) = year(CURDATE())
                AND status = '111'
                AND vication_type_hash = '333';
        
        WHEN '444' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end, date_start)))), '00:00:00') INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND year(date_start) = year(CURDATE())
                AND status = '111'
                AND vication_type_hash = '444';
        
        WHEN '555' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)), 0) * number_of_day INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND year(date_start) = year(CURDATE())
                AND status = '111'
                AND vication_type_hash = '555';
        
        ELSE
            SET number_of_hours_v = 404;
    END CASE;

    RETURN number_of_hours_v;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_vacations_current_year
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_current_year`(
    vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE number_of_hours_v double ; 
    DECLARE number_of_day double;

    SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) INTO number_of_day FROM emp_information WHERE user_hash = emp_hash_v;

    CASE vacations_type_v
        WHEN '111' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '111';
        WHEN '222' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '222';
        WHEN '333' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end , date_start)))), '00:00:00') INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '333';
        WHEN '444' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end , date_start)))), '00:00:00') INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '444';
        WHEN '555' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '555';
        ELSE
            SET number_of_hours_v = 404;
    END CASE;

    RETURN number_of_hours_v;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_vacations_days
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_days`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v double ; 
CASE vacations_type_v
	WHEN  '111' THEN
		   -- select ifnull(sum(datediff( if(date_end > date_to_v ,(date_to_v + INTERVAL 1 DAY),date_end) , if(date_start < date_from_v ,date_from_v , date_start )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) ) ),0) into number_of_hours_v from vacations  						   where  ( user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' ) or ( user_hash = emp_hash_v and date_end between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' );
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) )  ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' ) or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' );
	WHEN  '222' THEN
		   -- select ifnull(sum(datediff( if(date_end > date_to_v ,(date_to_v + INTERVAL 1 DAY),date_end) , if(date_start < date_from_v ,date_from_v , date_start )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) ) ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '222')  or ( user_hash = emp_hash_v and date_end between date_from_v and date_to_v and status = '111' and vication_type_hash = '222');
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) )  ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '222')  or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '222');

	WHEN  '555' THEN
		   -- select ifnull(sum(datediff( if(date_end > date_to_v ,(date_to_v + INTERVAL 1 DAY),date_end) , if(date_start < date_from_v ,date_from_v , date_start )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) ) ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '555')  or ( user_hash = emp_hash_v and date_end between date_from_v and date_to_v and status = '111' and vication_type_hash = '555');
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) ) ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '555')  or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '555');

	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function get_vacations_hours
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_hours`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v time default '00:00:00' ; 
CASE vacations_type_v
	when '333'  then
		   select ifnull(sec_to_time(sum(time_to_sec(timediff(date_end , date_start)))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between cast(concat(date_from_v, ' ', '00:00:00') as datetime)  and cast(concat(date_to_v, ' ', '23:59:59') as datetime) and status = '111' and vication_type_hash = '333' ;
	when '444'  then
		   select ifnull(sec_to_time(sum(time_to_sec(timediff(date_end , date_start)))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between cast(concat(date_from_v, ' ', '00:00:00') as datetime)  and cast(concat(date_to_v, ' ', '23:59:59') as datetime) and status = '111' and vication_type_hash = '444' ;
	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function make_fingerprint
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `make_fingerprint`() RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash='16593540552923218' and  date(fingerprint_time)=curdate() and in_out="in"; 
RETURN number_of_row;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- function make_in_fingerprint
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `make_in_fingerprint`() RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash='16593540552923218' and  date(fingerprint_time)=curdate() and in_out="in"; 
RETURN number_of_row;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure mobile_dashborad
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `mobile_dashborad`(IN user_hash bigint(20),
	OUT current_task json,
    OUT emergency_task json,
    OUT late_task json,
    OUT complete_task json,
	OUT not_complete json)
BEGIN


SELECT * into current_task  FROM task where  receiver=user_hash and status='111' order by id desc limit 3 ;
SELECT * into emergency_task FROM task where emergency='1' and receiver=user_hash;
SELECT * into late_task FROM task where curdate()>date(deadline) and receiver=user_hash and status='111' ;
SELECT count(id) into complete_task  FROM task where month(curdate())=month(date(deadline)) and receiver=user_hash and status='222';
SELECT count(id) into not_complete FROM task where month(curdate())=month(date(deadline)) and receiver=user_hash and status='111';

#SELECT ifnull(time_format(time(fingerprint_time),"%r"),"لاتوجد بصمة لهذا اليوم") as arrive_time FROM emp_fingerprint  where date(fingerprint_time)=curdate() and user_hash=user_hash ;
#SELECT (ceil((vacations_limit/12))-get_vacations_current_month('222',user_hash)) as balance_vacations_month FROM emp_information where user_hash=user_hash;
#SELECT (vacations_limit-get_vacations_current_year('222',user_hash)) as balance_vacations_year FROM emp_information where user_hash=user_hash;
#select get_vacations_current_month('222',user_hash) as vication_month;
#select get_vacations_current_year('222',user_hash) as vication_year;


END$$

DELIMITER ;

-- -----------------------------------------------------
-- function new_function
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` FUNCTION `new_function`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v time default '00:00:00' ; 
CASE vacations_type_v
	when '333'  then
		   select ifnull(sum((timediff(date_end , date_start))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '333' ;
	when '444'  then
		   select ifnull(sum((timediff(date_end , date_start))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '444' ;
	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure new_procedure
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `new_procedure`(
in start_date date , end_date date
)
BEGIN


CREATE TABLE `finger_print_report` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `vacation_hour` double DEFAULT NULL COMMENT 'ساعات الاجازة',
  `vacation_limit_hour` double DEFAULT NULL COMMENT 'ساعات الاجازة المسموح بها ',
  `duty_hour` double DEFAULT NULL COMMENT 'ساعات الواجب',
  `vacation_limit_out` double DEFAULT NULL COMMENT 'تجاوز الساعات المسموح بها',
  `work_hour` double DEFAULT NULL COMMENT 'ساعات الحضور',
  `finger_limit_out` double DEFAULT NULL COMMENT 'ساعات التاخير البصمة',
  `work_our_target` double DEFAULT NULL COMMENT 'ساعات الدوام المطلوبة',
  `cut_off_hours` double DEFAULT NULL COMMENT 'ساعات الخصم',
  `over_time` double DEFAULT NULL COMMENT 'ساعات اضافية',
  `cut_off_amount` double DEFAULT NULL COMMENT 'مبلغ الخصم',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;



END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure test
-- -----------------------------------------------------

DELIMITER $$
USE `hr`$$
CREATE DEFINER=`redha`@`%` PROCEDURE IF NOT EXISTS `test`()
BEGIN
declare x varchar(255);
select "xxx" into x;
select x;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- View `hr`.`detect_fraud`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`detect_fraud` AS select `hr`.`emp_fingerprint`.`id` AS `id`,`hr`.`emp_fingerprint`.`user_hash` AS `user_hash`,`hr`.`emp_fingerprint`.`fingerprint_type` AS `fingerprint_type`,`hr`.`emp_fingerprint`.`fingerprint_time` AS `fingerprint_time`,`hr`.`emp_fingerprint`.`in_out` AS `in_out`,`hr`.`emp_fingerprint`.`data_entry` AS `data_entry`,`hr`.`emp_fingerprint`.`hash` AS `hash`,`hr`.`emp_fingerprint`.`location` AS `location`,`hr`.`emp_fingerprint`.`user_create_record` AS `user_create_record`,`hr`.`emp_fingerprint`.`user_update_record` AS `user_update_record`,`hr`.`emp_fingerprint`.`mobile_info` AS `mobile_info` from `hr`.`emp_fingerprint` where `hr`.`emp_fingerprint`.`user_hash` in (select `t1`.`user_hash` from (select `hr`.`emp_fingerprint`.`user_hash` AS `user_hash`,`hr`.`emp_fingerprint`.`mobile_info` AS `mobile_info`,count(`hr`.`emp_fingerprint`.`mobile_info`) AS `cc` from `hr`.`emp_fingerprint` group by `hr`.`emp_fingerprint`.`mobile_info`) `t1` group by `t1`.`user_hash` having (count(`t1`.`mobile_info`) > 1));

-- -----------------------------------------------------
-- View `hr`.`emp_info`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`emp_info` AS select `hr`.`system_users`.`id` AS `emp_id`,`hr`.`emp_information`.`user_hash` AS `emp_hash`,`hr`.`system_users`.`name` AS `emp_name`,`hr`.`emp_information`.`primary_salary` AS `primary_salary`,`hr`.`emp_information`.`vacations_limit` AS `vacations_limit`,`hr`.`emp_information`.`sick_leave` AS `sick_leave`,`hr`.`emp_information`.`work_from` AS `work_from`,`hr`.`emp_information`.`work_to` AS `work_to`,`hr`.`emp_information`.`allow_time` AS `allow_time`,`hr`.`office_location`.`name` AS `location` from ((`hr`.`system_users` join `hr`.`emp_information` on((`hr`.`system_users`.`hash` = `hr`.`emp_information`.`user_hash`))) join `hr`.`office_location` on((`hr`.`office_location`.`hash` = `hr`.`emp_information`.`location_of_work`)));

-- -----------------------------------------------------
-- View `hr`.`emp_today_status`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`emp_today_status` AS select `su`.`name` AS `name`,(case when `v`.`date_start` then 'المجازون' when (count(0) = 0) then 'الغياب' when ((count(0) = 1) and (min(`ef`.`fingerprint_time`) is null)) then 'الغياب' when ((count(0) = 1) and (`ef`.`in_out` = 'in') and (`ei`.`allow_time` < cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' when ((count(0) = 1) and (`ei`.`allow_time` <= cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' when ((count(0) = 2) and (`ei`.`allow_time` < cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' else 'الحضور' end) AS `status`,`ei`.`allow_time` AS `time`,min(cast(`ef`.`fingerprint_time` as time)) AS `arrive_time` from (((`hr`.`system_users` `su` left join `hr`.`emp_fingerprint` `ef` on(((`ef`.`user_hash` = `su`.`hash`) and (cast(`ef`.`fingerprint_time` as date) = cast(curdate() as date))))) join `hr`.`emp_information` `ei` on(((`su`.`hash` = `ei`.`user_hash`) and (`ei`.`is_work` = 1)))) left join `hr`.`vacations` `v` on(((`v`.`user_hash` = `su`.`hash`) and (`v`.`date_start` <= curdate()) and (`v`.`date_end` > curdate()) and (`v`.`status` = '111')))) group by `su`.`hash`;

-- -----------------------------------------------------
-- View `hr`.`emp_vacation_summery`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`emp_vacation_summery` AS select `hr`.`system_users`.`name` AS `name`,ifnull(sec_to_time(sum(time_to_sec(timediff(`hr`.`vacations`.`date_end`,`hr`.`vacations`.`date_start`)))),0) AS `count`,ifnull(year(`hr`.`vacations`.`date_start`),'year') AS `year` from (`hr`.`system_users` left join `hr`.`vacations` on(((`hr`.`system_users`.`hash` = `hr`.`vacations`.`user_hash`) and (`hr`.`vacations`.`status` = '111') and (`hr`.`vacations`.`vication_type_hash` in ('222','333'))))) group by `hr`.`system_users`.`name`,year(`hr`.`vacations`.`date_start`);

-- -----------------------------------------------------
-- View `hr`.`finger_print`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`finger_print` AS select `print_in`.`user_hash` AS `user_hash`,`print_in`.`in_out` AS `type1`,`print_in`.`fingerprint_time` AS `in_at`,`print_out`.`in_out` AS `type2`,`print_out`.`fingerprint_time` AS `out_at` from (`hr`.`emp_fingerprint` `print_in` join `hr`.`emp_fingerprint` `print_out` on(((`print_in`.`user_hash` = `print_out`.`user_hash`) and (cast(`print_in`.`fingerprint_time` as date) = cast(`print_out`.`fingerprint_time` as date)) and (`print_in`.`in_out` = 'in') and (`print_out`.`in_out` = 'out'))));

-- -----------------------------------------------------
-- View `hr`.`payroll_main`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`redha`@`%` SQL SECURITY DEFINER VIEW `hr`.`payroll_main` AS select `hr`.`emp_information`.`user_hash` AS `user_hash`,(select `hr`.`system_users`.`name` from `hr`.`system_users` where (`hr`.`system_users`.`hash` = `hr`.`emp_information`.`user_hash`)) AS `emp_name`,`hr`.`emp_information`.`primary_salary` AS `primary_salary`,`hr`.`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`hr`.`emp_information`.`annual_price_increase` AS `annual_price_increase`,`hr`.`emp_information`.`start_work_date` AS `start_work_date`,`specification_view`.`emp_info_id` AS `emp_info_id`,`specification_view`.`specification_id` AS `specification_id`,`specification_view`.`specifications_type` AS `specifications_type`,`specification_view`.`name` AS `name`,`specification_view`.`percentage` AS `percentage`,`specification_view`.`price` AS `price`,`specification_view`.`maximumPrice` AS `maximumPrice`,`specification_view`.`nameValue` AS `nameValue` from (`hr`.`emp_information` join (select `hr`.`user_specifications`.`emp_info_id` AS `emp_info_id`,`hr`.`user_specifications`.`specification_id` AS `specification_id`,`hr`.`specifications`.`specifications_type` AS `specifications_type`,`hr`.`specifications`.`name` AS `name`,`hr`.`specifications`.`percentage` AS `percentage`,`hr`.`specifications`.`price` AS `price`,`hr`.`specifications`.`maximumPrice` AS `maximumPrice`,`hr`.`specifications`.`nameValue` AS `nameValue` from (`hr`.`user_specifications` join `hr`.`specifications` on((`hr`.`user_specifications`.`specification_id` = `hr`.`specifications`.`id`)))) `specification_view` on((`hr`.`emp_information`.`user_hash` = `specification_view`.`emp_info_id`))) union select `hr`.`emp_information`.`user_hash` AS `user_hash`,(select `hr`.`system_users`.`name` from `hr`.`system_users` where (`hr`.`system_users`.`hash` = `hr`.`emp_information`.`user_hash`)) AS `emp_name`,`hr`.`emp_information`.`primary_salary` AS `primary_salary`,`hr`.`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`hr`.`emp_information`.`annual_price_increase` AS `annual_price_increase`,`hr`.`emp_information`.`start_work_date` AS `start_work_date`,`discount_view`.`emp_info_id` AS `emp_info_id`,`discount_view`.`id` AS `id`,`discount_view`.`-` AS `-`,`discount_view`.`name` AS `name`,`discount_view`.`percentage * -1` AS `percentage * -1`,`discount_view`.`price * -1` AS `price * -1`,`discount_view`.`maximumPrice` AS `maximumPrice`,`Name_exp_28` AS `Name_exp_28` from (`hr`.`emp_information` join (select `hr`.`user_financial_discounts`.`emp_info_id` AS `emp_info_id`,`hr`.`financial_discounts`.`id` AS `id`,'-' AS `-`,`hr`.`financial_discounts`.`name` AS `name`,(`hr`.`financial_discounts`.`percentage` * -(1)) AS `percentage * -1`,(`hr`.`financial_discounts`.`price` * -(1)) AS `price * -1`,`hr`.`financial_discounts`.`maximumPrice` AS `maximumPrice`,'' AS `` from (`hr`.`user_financial_discounts` join `hr`.`financial_discounts` on((`hr`.`user_financial_discounts`.`financial_discounts_id` = `hr`.`financial_discounts`.`id`)))) `discount_view` on((`hr`.`emp_information`.`user_hash` = `discount_view`.`emp_info_id`))) order by `user_hash`;

-- -----------------------------------------------------
-- View `hr`.`test_emp_payroll`
-- -----------------------------------------------------
USE `hr`;
CREATE  OR REPLACE ALGORITHM=UNDEFINED DEFINER=`zone`@`%` SQL SECURITY DEFINER VIEW `hr`.`test_emp_payroll` AS select `payroll_view`.`emp_name` AS `emp_name`,`payroll_view`.`user_hash` AS `user_hash`,`payroll_view`.`start_work_date` AS `start_work_date`,`payroll_view`.`primary_salary` AS `primary_salary`,`payroll_view`.`annual_percentage_increase` AS `annual_percentage_increase`,`payroll_view`.`annual_price_increase` AS `annual_price_increase`,truncate(((to_days(curdate()) - to_days(`payroll_view`.`start_work_date`)) / 365.25),0) AS `number_of_year`,(truncate(((to_days(curdate()) - to_days(`payroll_view`.`start_work_date`)) / 365.25),0) * ((if(((`payroll_view`.`annual_percentage_increase` / 100) = 0),1,(`payroll_view`.`annual_percentage_increase` / 100)) * `payroll_view`.`primary_salary`) + `payroll_view`.`annual_price_increase`)) AS `price_increase_per_year`,json_arrayagg(json_object(`payroll_view`.`specifications_type`,json_object('primary_salary',`payroll_view`.`primary_salary`,'name_specifications',`payroll_view`.`name`,'amount',`payroll_view`.`price`,'percentage',`payroll_view`.`percentage`,'total_price',((`payroll_view`.`primary_salary` * if(((`payroll_view`.`percentage` / 100) = 0),1,(`payroll_view`.`percentage` / 100))) + `payroll_view`.`price`),'maximumPrice',`payroll_view`.`maximumPrice`))) AS `price_group` from (select `hr`.`emp_information`.`user_hash` AS `user_hash`,(select `hr`.`system_users`.`name` from `hr`.`system_users` where (`hr`.`system_users`.`hash` = `hr`.`emp_information`.`user_hash`)) AS `emp_name`,`hr`.`emp_information`.`primary_salary` AS `primary_salary`,`hr`.`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`hr`.`emp_information`.`annual_price_increase` AS `annual_price_increase`,`hr`.`emp_information`.`start_work_date` AS `start_work_date`,`specification_view`.`emp_info_id` AS `emp_info_id`,`specification_view`.`specification_id` AS `specification_id`,`specification_view`.`specifications_type` AS `specifications_type`,`specification_view`.`name` AS `name`,`specification_view`.`percentage` AS `percentage`,`specification_view`.`price` AS `price`,`specification_view`.`maximumPrice` AS `maximumPrice`,`specification_view`.`nameValue` AS `nameValue` from (`hr`.`emp_information` join (select `hr`.`user_specifications`.`emp_info_id` AS `emp_info_id`,`hr`.`user_specifications`.`specification_id` AS `specification_id`,(case when (`hr`.`specifications`.`specifications_type` = 1) then 'مخصصات شهادة' when (`hr`.`specifications`.`specifications_type` = 2) then 'مخصصات منصب' when (`hr`.`specifications`.`specifications_type` = 3) then 'مخصصات اخرى' when (`hr`.`specifications`.`specifications_type` = '-') then 'استقطاعات' end) AS `specifications_type`,`hr`.`specifications`.`name` AS `name`,`hr`.`specifications`.`percentage` AS `percentage`,`hr`.`specifications`.`price` AS `price`,`hr`.`specifications`.`maximumPrice` AS `maximumPrice`,`hr`.`specifications`.`nameValue` AS `nameValue` from (`hr`.`user_specifications` join `hr`.`specifications` on((`hr`.`user_specifications`.`specification_id` = `hr`.`specifications`.`id`)))) `specification_view` on((`hr`.`emp_information`.`user_hash` = `specification_view`.`emp_info_id`))) union select `hr`.`emp_information`.`user_hash` AS `user_hash`,(select `hr`.`system_users`.`name` from `hr`.`system_users` where (`hr`.`system_users`.`hash` = `hr`.`emp_information`.`user_hash`)) AS `emp_name`,`hr`.`emp_information`.`primary_salary` AS `primary_salary`,`hr`.`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`hr`.`emp_information`.`annual_price_increase` AS `annual_price_increase`,`hr`.`emp_information`.`start_work_date` AS `start_work_date`,`discount_view`.`emp_info_id` AS `emp_info_id`,`discount_view`.`id` AS `id`,`discount_view`.`specifications_type` AS `specifications_type`,`discount_view`.`name` AS `name`,`discount_view`.`percentage * -1` AS `percentage * -1`,`discount_view`.`price * -1` AS `price * -1`,`discount_view`.`maximumPrice` AS `maximumPrice`,`` AS `` from (`hr`.`emp_information` join (select `hr`.`user_financial_discounts`.`emp_info_id` AS `emp_info_id`,`hr`.`financial_discounts`.`id` AS `id`,'استقطاعات' AS `specifications_type`,`hr`.`financial_discounts`.`name` AS `name`,(`hr`.`financial_discounts`.`percentage` * -(1)) AS `percentage * -1`,(`hr`.`financial_discounts`.`price` * -(1)) AS `price * -1`,`hr`.`financial_discounts`.`maximumPrice` AS `maximumPrice`,'' AS `` from (`hr`.`user_financial_discounts` join `hr`.`financial_discounts` on((`hr`.`user_financial_discounts`.`financial_discounts_id` = `hr`.`financial_discounts`.`id`)))) `discount_view` on((`hr`.`emp_information`.`user_hash` = `discount_view`.`emp_info_id`))) order by `user_hash`) `payroll_view` group by `payroll_view`.`emp_name`;
USE `hr`;

DELIMITER $$
USE `hr`$$
CREATE
DEFINER=`redha`@`%`
TRIGGER `hr`.`emp_information_BEFORE_DELETE`
BEFORE DELETE ON `hr`.`emp_information`
FOR EACH ROW
BEGIN
INSERT INTO `hr`.`emp_information_log`
(`id1`,
`user_hash`,
`primary_salary`,
`vacations_limit`,
`sick_leave`,
`status`,
`work_from`,
`work_to`,
`allow_time`,
`location_of_work`,
`birthday`,
`address`,
`phone1`,
`phone2`,
`partment`,
`disable_in_finger_time`,
`disable_out_finger_time`,
`start_work_date`,
`emp_id`,
`jop_type`,
`is_work`)
VALUES
(
old.`id`,
old.`user_hash`,
old.`primary_salary`,
old.`vacations_limit`,
old.`sick_leave`,
old.`status`,
old.`work_from`,
old.`work_to`,
old.`allow_time`,
old.`location_of_work`,
old.`birthday`,
old.`address`,
old.`phone1`,
old.`phone2`,
old.`partment`,
old.`disable_in_finger_time`,
old.`disable_out_finger_time`,
old.`start_work_date`,
old.`emp_id`,
old.`jop_type`,
old.`is_work`
);
END$$

USE `hr`$$
CREATE
DEFINER=`redha`@`%`
TRIGGER `hr`.`emp_information_BEFORE_UPDATE`
BEFORE UPDATE ON `hr`.`emp_information`
FOR EACH ROW
BEGIN
INSERT INTO `hr`.`emp_information_log`
(`id1`,
`user_hash`,
`primary_salary`,
`vacations_limit`,
`sick_leave`,
`status`,
`work_from`,
`work_to`,
`allow_time`,
`location_of_work`,
`birthday`,
`address`,
`phone1`,
`phone2`,
`partment`,
`disable_in_finger_time`,
`disable_out_finger_time`,
`start_work_date`,
`emp_id`,
`jop_type`,
`is_work`)
VALUES
(
old.`id`,
old.`user_hash`,
old.`primary_salary`,
old.`vacations_limit`,
old.`sick_leave`,
old.`status`,
old.`work_from`,
old.`work_to`,
old.`allow_time`,
old.`location_of_work`,
old.`birthday`,
old.`address`,
old.`phone1`,
old.`phone2`,
old.`partment`,
old.`disable_in_finger_time`,
old.`disable_out_finger_time`,
old.`start_work_date`,
old.`emp_id`,
old.`jop_type`,
old.`is_work`
);

END$$


DELIMITER ;

