PHP staff leave planner - need suggestions to optimize project

[jackalope]

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

[Christian F.]

Your biggest time thief comes from this:

  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

Move all queries outside of the loops, and you'll notice a huge speedup.

 

You can also cut down the number of loop to 3, by using the following structure:

foreach ($months as $days) {
    foreach ($days as $date) {
        foreach ($employees as $person) {
            if ($person[$date]) {
                // Add date marker;
                continue;
            }
            // Add open marker.
        }
    }
}

I'm also almost positive you can generate a completed array containing the information you need with an SQL sentence too, but I leave that as an exercise for you (or anyone else who wants to play around with it). Ideally that would cut the code down to one loop, by the way.

 

The database seems well structured, from the little information you posted about it above. And assuming you've named the fields "employee_id", "type", "from", "to" or something similarly obvious, then I don't think there's a need to change the columns either. Might want to use an enum for the type field though, if you have a limited types of "vacations".

[Psycho]

Never, EVER run queries in loops. It is probably the most inefficient thing you can do. I've seen it bring servers to a crawl. Having said that, I tried looking at your logic and just couldn't focus enough to understand it and am not willing to invest the time. SO, I will provide some suggestions based upon what I see you are trying to achieve.

 

First off, it appears you are wanting to show the vacation calendar for the entire calendar year. You might want to rethink that and create a process where you can specify the start and end periods to report on.The reason I say this is because when you get to December you are only going to be seeing data a few weeks out. You would probably want to see data into the next several months.

 

Anyway, I would run ONE query to get all vacation plans by users where the end_date of the vacation is > the start date of the report AND the start_date of the vacation is < the end date of the report. That will get all the vacation plans that overlap any part of the report period. I would have the results ORDERED BY name, start date and then end date. Important the first table in the query should be the users table with a LEFT join on the other tables. This will ensure that all staff will be listed in the report even if they have no vacations planned yet.

 

Next I would extract all the records from the results into a temporary array using the user id or name as the primary indexes and then sub arrays for each vacation request. Then I would start a process to create the calendar. As each day is being created I would check the first record in the sub array for each user. If the vacation period in that record is for the current day I would indicate that in the output. If the period for that record ends before the current day I would remove that record and look at the next one.

[jackalope]

hi guys

@ChristianF: i will try to follow your suggestions

 

@Psycho: we usually could set holidays from 1 jan until 31 dec so usually use an excel spreadsheet with the entire year showed. in fact, usually we mark also the holiday of the next jan (in this case 2013)

so i will get from db all the dates between 1-1-[this year] until 31-12-[this year]

then i will try to follow also you suggestion and putout from loops the queries

 

thanks guys!