0

I have a database with a table of login and logoff times for staff.

Im looking for which staff clocked on or off between Sunday 6am and Thursday 4am or Saturday 6:30am and Saturday 3pm.

I want the end user to be able to select any date range from a web interface (ie 1/1/14 start and 12/1/14 end) and from this work out who worked during the day and time ranges. I want to be able to display a list of workers and their login and logoff time.

thus far I have worked out how to check if a user has logged by using Determine Whether Two Date Ranges Overlap

But im stumped with the pseudo code for how to work out the daterangeB

so far I have:

start at datefrom,
check if datefrom = dateto, if true end loop,
find out day name, 
check if saturday, if saturday add timestamps to array (start and finish),
increment datefrom by 1 day, start loop again.

this will give me an array of the saturday daterangeB's between the dates selected by the user.

However the bigger date range is an issue, because its possible that the first day is say a Tuesday, and if i'm checking for Sundays and Thursday, say i find a Thursday, i won't have a start day as sunday, I will need to make the Tuesday at 00:00:00. And similarly the opposite could happen, i could find a Sunday, and the last day that the user selects could be a Monday and therefore I need to make that the end date for daterangeB

start at datefrom,
check if datefrom = dateto, if true end loop,
find out day name, 
check if Sunday, if Sunday add timestamp to array (start),
increment datefrom by 1 day, start loop again.

and

start at datefrom,
check if datefrom = dateto, if true end loop,
find out day name, 
check if Thursday, if Thursday add timestamps to array (start and finish),
increment datefrom by 1 day, start loop again.
Community
  • 1
  • 1
Mike
  • 511
  • 3
  • 10
  • 28
  • Well the easiest way [maybe not very optimize] is your construct a sql query within PHP that use Between your desire time, and have more than one between in query. In PHP you can iterate over date range [usually this is like 30-40 days iteration], and check if day match your time, than append query with that day' range... You can alternatively write a Mysql function with same code. – Sumit Gupta Apr 04 '14 at 05:42
  • Does the answer you want specifically have to use the method mentioned in the question you stated? Oh, and another case that can happen - what if the selected date range includes now. Someone maybe have logged on but not logged off yet. The issue is the use of negative logic, you have to stop and work it out carefully. And, with SQL we tend to think about positive selections. – Ryan Vincent Apr 04 '14 at 07:02
  • it can be done in one sql statement but it will contain some 'or' tests. If you look at some of the other answers on the question you mentioned, then they consider including date ranges. those are the one i would consider using in the SQL. – Ryan Vincent Apr 04 '14 at 07:19

1 Answers1

0

So for all those that are interested, here is a dumbed down verson of what i ended up doing:

$stcars = array();

// Create connection
try {
    $db = new PDO('mysql:host=localhost;dbname=Reports;charset=utf8', 'XXX', 'XXX');

    $db->query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!" . $ex->getMessage(); //user friendly message
}

    $stmt = $db->prepare('SELECT CarNumber FROM STcars WHERE CarNumber > 0');


    if ($stmt->execute()){

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

array_push($stcars, $row[CarNumber]);


}// end while

}// end if

$DateFrom = $_POST["DateFrom"];
$DateTo = $_POST["DateTo"];

$DateToArray = date('m/d/Y', strtotime($DateTo . ' + 1 day')); 


$period = new DatePeriod(
     new DateTime($DateFrom),
     new DateInterval('P1D'),
     new DateTime($DateToArray)
);

foreach($period as $date) 
{

    $daterange[] = $date->format('m/d/Y');

}

//  MS SQL Server with PDO_DBLIB
$DBH = new PDO("dblib:host=$myServer;dbname=$myDB", $myUser, $myPass);

for ($i = 0; $i < count($stcars); ++$i) {

$CarNumber = $stcars[$i];

$STH3 = $DBH->query("SELECT dbo.Driver.DriverNumber, dbo.Driver.DriverName, dbo.DriverLogon.LogonTime, dbo.DriverLogon.LogoffTime, dbo.Vehicle.RegistrationNumber, dbo.OwnerOperator.OOName FROM dbo.Driver INNER JOIN dbo.DriverLogon ON dbo.Driver.DriverID = dbo.DriverLogon.DriverID INNER JOIN dbo.Vehicle ON dbo.DriverLogon.VehicleID = dbo.Vehicle.VehicleID INNER JOIN dbo.OwnerOperator ON dbo.Vehicle.OperatorID = dbo.OwnerOperator.OwnerOperatorID WHERE dbo.Vehicle.CarNumber = '$CarNumber' AND dbo.DriverLogon.LogonTime >= '$DateFrom $Beginning' AND dbo.DriverLogon.LogoffTime <= '$DateTo $End' AND dbo.Driver.DriverNumber != '7777' AND dbo.Driver.DriverNumber != '83890';");

$STH3->setFetchMode(PDO::FETCH_OBJ);

while($row3 = $STH3->fetch()) {

$logonday = date("D", strtotime($row3->LogonTime));

$chkStartTime = strtotime($row3->LogonTime);
$chkEndTime   = strtotime($row3->LogoffTime);
$Tplate = $row3->RegistrationNumber;
$Operator = $row3->OOName;
$logontime = gmdate("D d/m/Y H:i:s", strtotime($row3->LogonTime));
$logofftime = gmdate("D d/m/Y H:i:s", strtotime($row3->LogoffTime));




for ($e = 0; $e < count($daterange); ++$e) {
       $Date = $daterange[$e];
$day = date("D", strtotime($Date)); 




if(strpos( $day, $Mon ) !== FALSE){


$startTime = strtotime($Date . " " . $Beginning);
$endTime   = strtotime($Date . " " . $End);



if($chkStartTime >= $endTime || $chkEndTime <= $startTime){}else
{   
echo "<tr>" ;
echo "<td>" . $day .  "</td> ";
echo "<td>" . $row3->DriverNumber .  "</td> ";
echo "<td>" . $row3->DriverName .  "</td> ";
echo "<td>" . $Operator .  "</td> ";
echo "<td>" . $CarNumber .  "</td> ";
echo "<td>" . $Tplate .  "</td> ";
echo "<td>" . $logontime   .  "</td> ";
echo "<td>" . $logofftime   .  "</td> ";
//echo "<td>" . "Logon after Time End or Logoff before Time Start" .  "</td> ";
//echo "<td>" . gmdate("D d/m/Y G:i:s", strtotime($Date . " " . $Beginning))   . "</td> ";
//echo "<td>" . gmdate("D d/m/Y G:i:s", strtotime($Date . " " . $End))   .  "</td> ";
 echo "</tr>" ;

$list1 ="$day, $row3->DriverNumber, $row3->DriverName, $Operator, $CarNumber, $Tplate, $logontime, $logofftime " . "\n";

fwrite($handle, $list1);

}
Mike
  • 511
  • 3
  • 10
  • 28