Simple Timesheet help

[ecabrera]

hello all. 

 

I need help making other dates that are not in the database appear also. For example if i would have a something like this date=2015-03-30&end=2015-04-05

 

this would be the result. This is all the days employee A worked but i want all the days even if he didn't work that certain day 

 

Tuesday 
03/31/2015 09:08:00 AM 11:15:20 AM 2h 7m 20s     Wednesday 
04/01/2015 08:43:23 AM 10:14:59 AM 1h 31m 36s     Thursday 
04/02/2015 12:17:08 PM 14:00:56 PM 2h 43m 12s      

//gets the start and end date
	$gdate = $_GET['date'];
	$enddate = $_GET['end'];


//this gets days in between
/*$end = $enddate;
$start = $gdate;
$datediff = strtotime($end) - strtotime($start);
$datediff = floor($datediff/(60*60*24));
for($i = 0; $i < $datediff + 1; $i++){
   $cole[] = array(date("m/d/Y", strtotime($start . ' + ' . $i . 'day'))). ",";

}*/
	$gdate = mysqli_real_escape_string($db,$gdate);
	
			//sql command
	//$sql = "SELECT * FROM `timesheet` WHERE `date` IN (" . implode(',', $cole) . ")";
			$get = "SELECT * FROM `timesheet` WHERE `date` >= CAST('$start' AS DATE) AND `date` <= CAST('$end' AS DATE);";
			
			//query
			$getquery = mysqli_query($db,$get);
			
			while($rows = mysqli_fetch_assoc($getquery)){

      //setting the db info into varibles
      $s = $rows['c_date'];
      $startdb = $rows['start'];
      $enddb = $rows['end'];
      $dayl = $rows['day'];
      $daytotal = $rows['day_total'];
?>

  <tr>
    <td class="tg-031e"><?php echo "$dayl <br> $s"; ?></td>
    <td class="tg-031e"><?php echo $startdb; ?></td>
    <td class="tg-031e"><?php echo $enddb; ?></td>
    <td class="tg-031e"><?php echo $daytotal; ?></td>
    <td class="tg-031e"></td>
    <td class="tg-031e"></td>
  </tr>

[ginerjm]

Why not simply test for dates > one date and less than the other (including equals as well)?

[ecabrera]

Those dates in the database are only the days he worked so nothing else is in there so i want to know if i can create the dates he didn't work without them being in the database i tried creating it here (below) but than i got stuck. 

 

//this gets days in between

/*$end = $enddate;
$start = $gdate;
$datediff = strtotime($end) - strtotime($start);
$datediff = floor($datediff/(60*60*24));
for($i = 0; $i < $datediff + 1; $i++){
$cole[] = array(date("m/d/Y", strtotime($start . ' + ' . $i . 'day'))). ",";

}*/

[ginerjm]

That was not clear in your initial question. Very difficult to understand.

 

So how are you going to "query" for something that doesn't exist? Using your current query logic I don't see how you will have any of the "missing" records in your result set.

 

Perhaps you should create your output based upon the given date range by using PHP date functions to generate the necessary "work dates" and THEN populate those rows/dates using any matching query results within the range as I proposed.

 

Of course there will probably be someone who has a query-based solution, but that's how I would have approached the problem.

[Barand]

// CREATE ARRAY WHOSE KEYS ARE THE DATES IN THE REQUIRED RANGE

$dt1 = new DateTime('2015-03-30');
$dt2 = new DateTime('2015-04-05');
$dt2->modify('+1 day');
$dp = new DatePeriod($dt1, new DateInterval('P1D'), $dt2);
foreach ($dp as $d) {
    $data[$d->format('Y-m-d')] = '';
}

this gives

$data = Array
(
    [2015-03-30] => 
    [2015-03-31] => 
    [2015-04-01] => 
    [2015-04-02] => 
    [2015-04-03] => 
    [2015-04-04] => 
    [2015-04-05] => 
)

When you process your query results drop the data into the array for the dates you have. You can loop through the array to get your final output with all dates

Edited April 6, 2015 by Barand

[ecabrera]

ok im using this the only this im having trouble with is adding the total time together.  I'm having problems with adding the total time. The total hours displays like this EX. 1h 14m 20s I want to be able to get all the total hours and add them up like

1h 14m 20s + 3h 32m 46s + 3h 32m 46s = $weekstotalhours

How would i go about doing this. Should i change convert them to seconds and than add them?

$query = "SELECT date, start, end, day_total, TIMESTAMPDIFF(SECOND, start, end) as day_t
          FROM `timesheet`
          WHERE `date` >= CAST('$start' AS DATE)
            AND `date` <= DATE_ADD(CAST('$start' AS DATE), INTERVAL 6 DAY) AND e_user='$username'";
$result = mysqli_query($db, $query);
 
//Dump results into an array
$records = array();
while($row = mysqli_fetch_assoc($result))
{
    $records[$row['date']] = $row;
}
 
//Create a loop from first to last date
//for each day, check if there is a matching records
//in the records array. If so, use that data
//Else, there was no data for that date
 
for($day=0; $day<7; $day++)
{
    $timeStamp = strtotime("{$start} +{$day} day");
    $dateStamp = date("Y-m-d", $timeStamp);
    $DOW       = date("D", $timeStamp);
    $dateStr   = date("m/d/Y", $timeStamp);
 
    //Set default values
    $startTime = '';
    $endTime = '';
    $totalHours = '';
    if(isset($records[$dateStamp]))
    {
        $startTime = $records[$dateStamp]['start'];
        $endTime   = $records[$dateStamp]['end'];
        $totalTimeer = $records[$dateStamp]['day_t'];
    } 
         $totalTime = $records[$dateStamp]['day_total'];

    //Output results
    echo "<tr>\n";
    echo "<td class='tg-031e'>{$DOW}    {$dateStr}</td>\n";
    echo "<td class='tg-031e'>{$startTime}</td>\n";
    echo "<td class='tg-031e'>{$endTime}</td>\n";
    echo "<td class='tg-031e'>{$totalTime}</td>\n";
    echo "<td class='tg-031e'></td>\n";
    echo "<td class='tg-031e'></td>";
    echo "</tr>\n";
}

?>