hi all
I need a suggestion for the creation of an internal site for planning office staff holidays
I’ve done something but I believe I can significantly improve
my idea is :
create a database with several tables related to each other:
1 table with a list of colleagues in the
1 table with the list of types of leave (vacation, medical, special permits, etc.)
1 table with the list of holiday plans:
andrea - holiday - from - to
luca - permission - from - to
etc. …
php page should (and already does) to retrieve this data and draw a calendar in this way (html table):
| | jan |
| DAYS> |1|2|3|4|5|6|..|31|
|Andrew |x|x|x|x| | | | |
|Luke | | |x|x|x|x|x | |
|Mark | | | | | | | | |
|John | |x| | | |x| | |
| | feb |
| DAYS> |1|2|3|4|5|6|..|28|
|Andrew | | | | | | | | |
|Luke | | | |x| | | | |
|Mark |x|x|x| | | |x |x |
|John | |x| | | |x| | |
| | mar |
| DAYS> |1|2|3|4|5|6|..|31|
|Andrew | | | | | | | | |
|Luke | | |x| |x|x| | |
|Mark | | | | |x| | | |
|John | |x|x|x| |x| | |
| | apr |
| DAYS> |1|2|3|4|5|6|..|30|
|Andrew | |x| | | | | | |
|Luke |x|x|x| | | | | |
|Mark | | | | | |x|x |x |
|John | | | | | | | | |Obviously I give a different color to Saturday and Sunday (only in the row of numbers)
now the script works like this:
loop for from 1 to 12
Cycle 1 to end of month / / first row of the table
I write the cells with the number of days in that month
end of cycle
i got the list of colleagues with a query
foreach loop for each employee / / the second row on the table
query that retrieves the number of plans for that employee
if the count is <1
write empty cells in the line that connects the entire month x
if the count is> 1
foreach vacation plan for that colleague
Cycle 1 to end of month
if this day is between the dates of his colleague Andrew
if yes flag = 1
end if
end of cycle
if flag = 1
i color the cell
else
empty cell
end if
end foreach
end if
end foreach
end of cycle
what I’m getting better now setting the holiday table plans on splitting the database dates in its elemental parts:
from_year
from_month
from_day
to_year
to_month
to_day
so I can do a query on time for the employee and for the month in question so I search ONLY vacation plans beginning in that month for that colleague
But I think it’s still improved especially on the test date
I thought that perhaps, instead of drawing the table by
testing day by day, I could fill a matrix/array for the colleague
with as many values as are the days of the month
and doing the test if the day is included or not on the matrix/array
and then once you create the array design quickly the line of the
table for the colleague
I empty the array and go forward with employee number 2
Do you have any suggestions for me to optimize this project?
Do you think the database is well structured?
Do you think I should change the columns of the database of vacation plans?
would make sense to save on that table the starting date and the number of days of leave instead the final date?
thanks