Hi Thomas,
> SELECT * from $DB_TBLName WHERE
> (Trim(WorkerEmail)='$userReplacementEmail')
> AND AND OR OR OR OR OR
This is all a bit complicated, and a simple boy like me gets lost too
easily. KISS principle: Keep it simple...
You have two employees:
A, so named because he Already has Annual leave Approved, and
B, so named because he is Begging to Be allowed to Break out.
Let's imagine we're playing with a wall-chart/wall-planner to assess
requests prior to granting approval. So we mark off which days A will be
away and then view which days B would like to go against existing requests.
Month A Month B Month C Month etc
A <------------------->
B <........................>
Assuming the email/fonts haven't mucked-up my sketch, you can see that B's
request should be denied, because A has already booked the time-out.
Now let's use the diagram to build an algorithm. How many ways are there
that A and B could overlap? (hint: four) Not overlap? (hint: two) Which is
the easiest to implement in code?
Now you can ask, which language should I code in? Answer: stay as close to
the source as possible, ie use SQL (or in this case pseudo-SQL(!)).
SELECT a AS Colleague, CONCAT( astart - afinish ) AS PermissionDenied
FROM tbl
WHERE bstart < afinish
OR bfinish > astart;
Before this will work, you will need to add another WHERE clause to
establish who is the employee's "replacement". The above lists a reason for
rejection - you can reverse the logic, at your peril.
Trust this assists,
=dn
PS The complicating question I wanted to ask from the word "go" is: what if
one person can have more than one other employee act as his/her
"replacement"?
> Background:
> ================
>
> I've created a vacation-request application for our company's intranet.
> When an employee requests a vacation, he has to list the name of another
> employee who will 'fill in' for him while he is gone.
>
> Before a vacation request can be saved in MySQL, I need to check to make
> sure that the person listed as the employee's replacement during this time
> has not already requested a vacation during the same time period...to
> check to be sure that the two vacation periods do not overlap.
>
> Date Ranges:
> ================
>
> I have fields saved in MySQL called unixStartDate & unixEndDate that are
> unix timestamps for the first day of the employee's vacation and the last
> day of the emplosyee's vacation.
>
> I have tried to do a check using purely SQL, but this doesn't account for
> all possibilities of overlapping dates:
>
> (unixStartDate is the unix timestamp for the replacement, whereas the PHP
> var $unixStartDate is the unix timestamp for the employee who wants to
> post a new vacation request)
>
> $checkSQL = "SELECT * from $DB_TBLName WHERE
> (Trim(WorkerEmail)='$userReplacementEmail')
> AND (Status < 40)
> AND
> (
> (
> ($unixStartDate = unixStartDate)
> )
> OR
> (
> ($unixEndDate = unixEndDate)
> )
> OR
> (
> ($unixStartDate = unixEndDate)
> )
> OR
> (
> ($unixEndDate = unixStartDate)
> )
> OR
> (
> (unixStartDate < $unixStartDate) && (unixEndDate > $unixEndDate)
> )
> OR
> (
> (unixStartDate > $unixStartDate) && (unixEndDate < $unixEndDate)
> )
> )
> ";
>
> So I think what I instead need to do is use PHP code instead of SQL to
> check for overlapping dates in the 2 date ranges I have.
> My two ranges would be like this:
>
> Replacement's Date Range:
> ================
> $unixStartDateReplacementVacation
> ...to...
> $unixEndDateReplacementVacation
>
> Employee's Date Range:
> ================
> $unixStartDateEmployeeVacation
> ...to..
> $unixEndDateEmployeeVacation
>
>
> ================
>
> ...so i need to check that none of the dates occuring in the first date
> range listed above appear in the second date range.
> unfortunately, i have no basic idea of how i should go about doing
> this...should i use arrays of dates, for-loops, or what?
> thanks a whole lot in advance,
>
> Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
