-
Notifications
You must be signed in to change notification settings - Fork 4
/
script.sql
175 lines (156 loc) · 8.31 KB
/
script.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
-- ENABLED EVENT SCHEDULE FOR MYSQL
-- IF USE AWS RDS USE CUSTOM GROUP OPTIONS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithOptionGroups.html
SET GLOBAL event_scheduler="ON"
-- REMEMBER VALUE OF FUNCTION DAYOFWEEK IN MYSQL
-- https://www.w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php
-- CREATE STORE PROCEDURE OF ABSENTS OF USER OF DAY
DROP PROCEDURE IF EXISTS REGISTER_ABSENT_ATTENDANCE_BY_USER;
DELIMITER //
CREATE PROCEDURE `REGISTER_ABSENT_ATTENDANCE_BY_USER` ()
BEGIN
SET @CURRENT_DAY := (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y-%m-%d") FROM DUAL);
SET @ATTENDANCE_ID:= (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y%m%d") FROM DUAL);
INSERT IGNORE INTO `attendance` (`attendance`.`codUser`, `attendance`.`id`, `attendance`.`description`, `attendance`.`isAbsent` ,`attendance`.`isLater` ,`attendance`.`isActive`,`attendance`.`entryTime`,`attendance`.`exitTime`,`attendance`.`date`)
SELECT SCHEDULE_VALID_BY_USER.CODUSER , @ATTENDANCE_ID, "User is absent" , 1 , 0 , 0, @ATTENDANCE_ID, @ATTENDANCE_ID, @ATTENDANCE_ID FROM
(
SELECT U.id AS 'CODUSER',
CASE
WHEN (MONDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=2) THEN 'TRUE'
WHEN (TUESDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=3) THEN 'TRUE'
WHEN (WEDNESDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=4) THEN 'TRUE'
WHEN (THURSDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=5) THEN 'TRUE'
WHEN (FRIDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=6) THEN 'TRUE'
WHEN (SATURDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=7) THEN 'TRUE'
WHEN (SUNDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=1) THEN 'TRUE'
ELSE "FALSE"
END AS 'IS_ENABLED'
FROM `schedule` S
JOIN `user` U ON U.codSchedule = S.id) SCHEDULE_VALID_BY_USER
WHERE SCHEDULE_VALID_BY_USER.IS_ENABLED ='TRUE';
END//
DELIMITER ;
-- CREATE STORE PROCEDURE FOR REGISTER DAYOFF ATTENDANCE BY USER
DROP PROCEDURE IF EXISTS REGISTER_DAYOFF_ATTENDANCE_BY_USER;
DELIMITER //
CREATE PROCEDURE `REGISTER_DAYOFF_ATTENDANCE_BY_USER` ()
BEGIN
SET @CURRENT_DAY := (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y-%m-%d") FROM DUAL);
SET @ATTENDANCE_ID:= (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y%m%d") FROM DUAL);
INSERT IGNORE INTO `attendance` (`attendance`.`codUser`, `attendance`.`id`, `attendance`.`description`, `attendance`.`isAbsent` ,`attendance`.`isLater` ,`attendance`.`isActive`,`attendance`.`isDayOff`,`attendance`.`entryTime`,`attendance`.`exitTime`,`attendance`.`date`)
SELECT SCHEDULE_VALID_BY_USER.CODUSER , @ATTENDANCE_ID, "User is dayOff" , 0 , 0 , 0 , 1 , @ATTENDANCE_ID, @ATTENDANCE_ID, @ATTENDANCE_ID FROM
(
SELECT U.id AS 'CODUSER',
CASE
WHEN (MONDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=2) THEN 'TRUE'
WHEN (TUESDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=3) THEN 'TRUE'
WHEN (WEDNESDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=4) THEN 'TRUE'
WHEN (THURSDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=5) THEN 'TRUE'
WHEN (FRIDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=6) THEN 'TRUE'
WHEN (SATURDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=7) THEN 'TRUE'
WHEN (SUNDAY = 1 AND DAYOFWEEK(@CURRENT_DAY)=1) THEN 'TRUE'
ELSE "FALSE"
END AS 'IS_ENABLED'
FROM `schedule` S
JOIN `user` U ON U.codSchedule = S.id) SCHEDULE_VALID_BY_USER
WHERE SCHEDULE_VALID_BY_USER.IS_ENABLED ='FALSE';
END//
DELIMITER ;
-- CREATE STORE PROCEDURE FOR PERMISSIONS OF USERS
DROP PROCEDURE IF EXISTS REGISTER_PERMISSIONS_OF_USERS;
DELIMITER //
CREATE PROCEDURE `REGISTER_PERMISSIONS_OF_USERS` ()
BEGIN
SET @CURRENT_DAY := (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y-%m-%d") FROM DUAL);
SET @ATTENDANCE_ID:= (SELECT DATE_FORMAT( convert_tz(now(),@@session.time_zone,'-05:00') ,"%Y%m%d") FROM DUAL);
INSERT IGNORE INTO `attendance` (`attendance`.`codUser`, `attendance`.`id`, `attendance`.`description`, `attendance`.`isAbsent` ,`attendance`.`isLater` ,`attendance`.`isActive`,`attendance`.`isDayOff`,`attendance`.`isLicence`)
SELECT CODUSER , @ATTENDANCE_ID, "User is licence" , 0 , 0 , 0 , 0, 1 from licence
where @CURRENT_DAY <= date(dateEnd) AND @CURRENT_DAY >=date(dateInit);
END//
DELIMITER ;
-- CHECK CREATION OF STORE PROCEDURE
SHOW PROCEDURE STATUS;
-- CREATE STORE PROCEDURE FOR ATTENDANCE REPORT OF USERS
DROP PROCEDURE IF EXISTS REPORT_ATTENDANCE_BY_USER;
DELIMITER //
CREATE PROCEDURE `REPORT_ATTENDANCE_BY_USER` (id varchar(10) , initDate varchar(10) , endDate varchar(10))
BEGIN
SELECT
DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d") AS 'date',
attendance.description as 'description' ,
CASE
WHEN attendance.isLater = 1 THEN 'TARDE'
WHEN attendance.isAbsent = 1 THEN 'FALTA'
WHEN attendance.isDayOff = 1 THEN 'DIA LIBRE'
WHEN attendance.isLicence = 1 THEN 'LICENCIA'
ELSE 'PUNTUAL' END AS 'status' ,
CONCAT( user.name , " " , user.fatherLastName , " " , user.motherLastName) as 'fullName',
DATE_FORMAT( convert_tz(attendance.entryTime ,@@session.time_zone,'-05:00') ,"%H:%i") AS 'entryTime',
DATE_FORMAT( convert_tz(attendance.exitTime ,@@session.time_zone,'-05:00') ,"%H:%i") as 'exitTime'
FROM attendance
JOIN user
ON user.id = attendance.codUser
WHERE user.id = id AND DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d") between initDate AND endDate;
END//
DELIMITER ;
-- CREATE STORE PROCEDURE FOR CHART REPORT ALL USERS
DROP PROCEDURE IF EXISTS REPORT_CHART_REPORT;
DELIMITER //
CREATE PROCEDURE `REPORT_CHART_REPORT` (initDate varchar(10) , endDate varchar(10))
BEGIN
SELECT
DISTINCT( DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d")) AS 'date',
COUNT(CASE WHEN attendance.isLater = 1 THEN 1 END) AS 'later',
COUNT(CASE WHEN attendance.isLater =0 AND attendance.isAbsent =0 THEN 1 END) AS 'onTime',
COUNT(CASE WHEN attendance.isAbsent =1 THEN 1 END) AS 'absent',
COUNT(CASE WHEN attendance.isLicence =1 THEN 1 END) AS 'licence'
FROM attendance
WHERE DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d") BETWEEN initDate AND endDate
GROUP BY DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d");
END//
DELIMITER ;
-- CREATE STORE PROCEDURE FOR CHART REPORT BY USERS
DROP PROCEDURE IF EXISTS REPORT_CHART_REPORT_BY_USER;
DELIMITER //
CREATE PROCEDURE `REPORT_CHART_REPORT_BY_USER` (id varchar(10) , initDate varchar(10) , endDate varchar(10))
BEGIN
SELECT
DISTINCT( DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d")) AS 'date',
COUNT(CASE WHEN attendance.isLater = 1 THEN 1 END) AS 'later',
COUNT(CASE WHEN attendance.isLater =0 AND attendance.isAbsent =0 THEN 1 END) AS 'onTime',
COUNT(CASE WHEN attendance.isAbsent =1 THEN 1 END) AS 'absent',
COUNT(CASE WHEN attendance.isLicence =1 THEN 1 END) AS 'licence'
FROM attendance
JOIN user
ON user.id = attendance.codUser
WHERE user.id = id AND DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d") BETWEEN initDate AND endDate
GROUP BY DATE_FORMAT( convert_tz(attendance.date ,@@session.time_zone,'-05:00') ,"%Y-%m-%d");
END//
DELIMITER ;
-- CREATION JOB REGISTER ABSENTS OF USERS
DROP EVENT IF EXISTS JOB_REGISTER_ABSENT_ATTENDANCE_BY_USER;
CREATE EVENT JOB_REGISTER_ABSENT_ATTENDANCE_BY_USER
ON SCHEDULE
EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 23 HOUR + INTERVAL 25 MINUTE )
DO
CALL REGISTER_ABSENT_ATTENDANCE_BY_USER()
-- CREATION JOB REGISTER DAYOFF OF USERS
DROP EVENT IF EXISTS JOB_REGISTER_DAYOFF_ATTENDANCE_BY_USER;
CREATE EVENT JOB_REGISTER_DAYOFF_ATTENDANCE_BY_USER
ON SCHEDULE
EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 23 HOUR )
DO
CALL REGISTER_DAYOFF_ATTENDANCE_BY_USER()
-- CREATION JOB REGISTER PERMISSIONS OF USERS
DROP EVENT IF EXISTS JOB_REGISTER_PERMISSIONS_OF_USERS;
CREATE EVENT JOB_REGISTER_PERMISSIONS_OF_USERS
ON SCHEDULE
EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 5 MINUTE )
DO
CALL REGISTER_PERMISSIONS_OF_USERS()
-- CHECK CREATION OF JOBS
SHOW EVENTS;
-- ALTERNATIVE OF A CREATE OF JOBS OF MYSQL IF USE A SHAREHOSTING USE A PHP CRON JOB IN CPANEL
-- More information https://blog.cpanel.com/how-to-configure-a-cron-job/