I have to create a button in php for when I click generate a monthly shift schedule, but I'm not sure how to do it. Can anyone suggest an idea?
What I am thinking is to randomly choose the collaborations I need per day of each shift, but it is giving error because of UNION and ORDER BY .
This is the code I have:
SELECT * FROM centrodb.InfoColaboradores WHERE Turno = 'M' ORDER BY RAND() LIMIT 8
UNION
SELECT * FROM centrodb.InfoColaboradores WHERE Turno = 'T' ORDER BY RAND() LIMIT 4
UNION
SELECT * FROM centrodb.InfoColaboradores WHERE Turno = 'T2' ORDER BY RAND() LIMIT 3
UNION
SELECT * FROM centrodb.InfoColaboradores WHERE Turno = 'N' ORDER BY RAND() LIMIT 2;
This is my table structure:
1- DaysDate Table with fields: id, Days. In this table I put every day of the week as the image shows:
2-H_Turntablewiththefollowingfields:id,Shift,HStart,H_end.Tablewiththebeginningandendofeachshiftasshownintheimage:
3- InfoColumns table with the following fields: id, CollationName, Shift, Horadia. This indicates each employee's shift and the hours that can be done per day as shown in the image:
Do I have the structure of well-created tables?
The idea now is to create a random and automatic monthly schedule, where the morning shift can only have 8 employees, the afternoon 4 employees, the afternoon2 3 employees and 2 employees.
Rules: If you work 4 days in a row 1 slack, if it is 5 days 2 slack. Work a Sunday every 2 months
I used the code above where with Rand() I can create the first random selection of each shift.
In this way I can only start to generate automatically for the first day and only for the morning shift, but still without rules, code example:
SELECT NomeColaborador, centrodb.H_Turno.Turno, centrodb.H_Turno.H_inicio, centrodb.H_Turno.H_Fim, MONTH(NOW())
FROM centrodb.InfoColaboradores LEFT OUTER JOIN centrodb.H_Turno
ON centrodb.H_Turno.Turno = centrodb.InfoColaboradores.Turno
WHERE centrodb.H_Turno.Turno = 'M' ORDER BY RAND() LIMIT 8
How to generate this shift automatically for the month and not just for a day?