--
-- Dumping events for database 'hr'
--
/*!50106 DROP EVENT IF EXISTS `redha3` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;

--
-- Final view structure for view `detect_fraud`
--

/*!50001 DROP VIEW IF EXISTS `detect_fraud`*/;

--
-- Final view structure for view `emp_info`
--

/*!50001 DROP VIEW IF EXISTS `emp_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_info` AS select `system_users`.`id` AS `emp_id`,`emp_information`.`user_hash` AS `emp_hash`,`system_users`.`name` AS `emp_name`,`emp_information`.`primary_salary` AS `primary_salary`,`emp_information`.`vacations_limit` AS `vacations_limit`,`emp_information`.`sick_leave` AS `sick_leave`,`emp_information`.`work_from` AS `work_from`,`emp_information`.`work_to` AS `work_to`,`emp_information`.`allow_time` AS `allow_time`,`office_location`.`name` AS `location` from ((`system_users` join `emp_information` on((`system_users`.`hash` = `emp_information`.`user_hash`))) join `office_location` on((`office_location`.`hash` = `emp_information`.`location_of_work`))) */;

--
-- Final view structure for view `emp_today_status`
--

/*!50001 DROP VIEW IF EXISTS `emp_today_status`*/;

--
-- Final view structure for view `emp_vacation_summery`
--

/*!50001 DROP VIEW IF EXISTS `emp_vacation_summery`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_vacation_summery` AS select `system_users`.`name` AS `name`,ifnull(sec_to_time(sum(time_to_sec(timediff(`vacations`.`date_end`,`vacations`.`date_start`)))),0) AS `count`,ifnull(year(`vacations`.`date_start`),'year') AS `year` from (`system_users` left join `vacations` on(((`system_users`.`hash` = `vacations`.`user_hash`) and (`vacations`.`status` = '111') and (`vacations`.`vication_type_hash` in ('222','333'))))) group by `system_users`.`name`,year(`vacations`.`date_start`) */;

--
-- Final view structure for view `finger_print`
--

/*!50001 DROP VIEW IF EXISTS `finger_print`*/;

--
-- Final view structure for view `test_emp_payroll`
--

/*!50001 DROP VIEW IF EXISTS `test_emp_payroll`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`zone`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `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 `emp_information`.`user_hash` AS `user_hash`,(select `system_users`.`name` from `system_users` where (`system_users`.`hash` = `emp_information`.`user_hash`)) AS `emp_name`,`emp_information`.`primary_salary` AS `primary_salary`,`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`emp_information`.`annual_price_increase` AS `annual_price_increase`,`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 (`emp_information` join (select `user_specifications`.`emp_info_id` AS `emp_info_id`,`user_specifications`.`specification_id` AS `specification_id`,(case when (`specifications`.`specifications_type` = 1) then 'مخصصات شهادة' when (`specifications`.`specifications_type` = 2) then 'مخصصات منصب' when (`specifications`.`specifications_type` = 3) then 'مخصصات اخرى' when (`specifications`.`specifications_type` = '-') then 'استقطاعات' end) AS `specifications_type`,`specifications`.`name` AS `name`,`specifications`.`percentage` AS `percentage`,`specifications`.`price` AS `price`,`specifications`.`maximumPrice` AS `maximumPrice`,`specifications`.`nameValue` AS `nameValue` from (`user_specifications` join `specifications` on((`user_specifications`.`specification_id` = `specifications`.`id`)))) `specification_view` on((`emp_information`.`user_hash` = `specification_view`.`emp_info_id`))) union select `emp_information`.`user_hash` AS `user_hash`,(select `system_users`.`name` from `system_users` where (`system_users`.`hash` = `emp_information`.`user_hash`)) AS `emp_name`,`emp_information`.`primary_salary` AS `primary_salary`,`emp_information`.`annual_percentage_increase` AS `annual_percentage_increase`,`emp_information`.`annual_price_increase` AS `annual_price_increase`,`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 (`emp_information` join (select `user_financial_discounts`.`emp_info_id` AS `emp_info_id`,`financial_discounts`.`id` AS `id`,'استقطاعات' AS `specifications_type`,`financial_discounts`.`name` AS `name`,(`financial_discounts`.`percentage` * -(1)) AS `percentage * -1`,(`financial_discounts`.`price` * -(1)) AS `price * -1`,`financial_discounts`.`maximumPrice` AS `maximumPrice`,'' AS `` from (`user_financial_discounts` join `financial_discounts` on((`user_financial_discounts`.`financial_discounts_id` = `financial_discounts`.`id`)))) `discount_view` on((`emp_information`.`user_hash` = `discount_view`.`emp_info_id`))) order by `user_hash`) `payroll_view` group by `payroll_view`.`emp_name` */;


