Hi guys,
At my regular employer we have to add a lines every year to the timesheet database so people can put in their hours (its built in Access and was developed in Access '97). The first column needs to be the employee number, the second is the monday of the current week being iterated, and the third column needs to be a date, sequentially. So basically there will end up being 1 row per employee times 1 row per day = ~roughly 365 but since they need to be in full 2 week increments (for pay periods) it ends up being more.

Previously before I worked here they were building an excel file by hand (typing out each and every date!) and doing a replace on the values in the first column to add each user one at a time. I decided it would be easier to do it with PHP since they tasked me with it (and there was no way I was typing all that out!) and this is what I came up with to solve the problem. It works as desired, but looking for your feedback:

<?php

define('ACCESS_DATE_FORMAT', 'Y-m-d');

$EmpIDs = array(
      81=>'Name',
      46=>'Name',
      71=>'Name',
      74=>'Name',
      60=>'Name',
      58=>'Name',
      84=>'Name',
      57=>'Name',
      28=>'Name',
      49=>'Name',
      55=>'Name',
      38=>'Name',
      96=>'Name',
      48=>'Name',
   );
ksort($EmpIDs);

$FirstMonday = '2012-01-09';
$LastSunday = '2013-01-20';

$csv = fopen('php://output','w');

foreach( $EmpIDs as $Emp => $v )
{

   $Date = strtotime($FirstMonday);
   $Monday = strtotime($FirstMonday);

   while( $Date <= strtotime($LastSunday) )
   {
      fputcsv($csv,
              array($Emp,
                    date(ACCESS_DATE_FORMAT,$Monday),
                    date(ACCESS_DATE_FORMAT,$Date)),
              ",",'"');

  $Date = $Date + (60*60*24);
  if( date('l',$Date) == 'Monday' )
     $Monday = $Date;
   }

}

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=Fiscal (Yearly) - Access Append to Timesheet.csv");
fclose($csv);

*Note: All values in the array changed to Name to protect employee names

    One possible issue/corner case to note is this:

    $Date = $Date + (60*60*24);

    since not every day is 86,400 seconds in length.

    Personally, I would probably utilize the [man]DateTime[/man] class and create a [man]DateInterval[/man] object to use in conjunction with the DateTime::add() method ([man]datetime.add[/man]).

    But that's just because I think it looks fancier to manipulate dates in that OOP-centric way. 😉

      Ok then, how's this:

      <?php 

define('ACCESS_DATE_FORMAT', 'Y-m-d'); 

$EmpIDs = array( 
      'Name'=>81, 
      'Name'=>46, 
      'Name'=>71, 
      'Name'=>74, 
      'Name'=>60, 
      'Name'=>58, 
      'Name'=>84, 
      'Name'=>57, 
      'Name'=>28, 
      'Name'=>49, 
      'Name'=>55, 
      'Name'=>38, 
      'Name'=>96, 
      'Name'=>48, 
   ); 
sort($EmpIDs); 

$FirstMonday = '2012-01-09'; 
$LastSunday = new DateTime('2013-01-20'); 

// Intervals
$OneDay = new DateInterval('P1D');
$OneWeek = new DateInterval('P1W');

$csv = fopen('php://output','w'); 

foreach( $EmpIDs as $Emp ) 
{ 

   $Date = new DateTime($FirstMonday); 
   $Monday = new DateTime($FirstMonday); 

   while( $Date <= $LastSunday ) 
   { 
      fputcsv($csv, 
              array($Emp, 
                    $Monday->format(ACCESS_DATE_FORMAT), 
                    $Date->format(ACCESS_DATE_FORMAT)
                    ), 
              ",",'"'); 

  $Date->add($OneDay);
  if( $Date->format('l') == 'Monday' )
     $Monday->add($OneWeek); 
   } 

} 

header("Content-type: text/html"); 
header("Content-Disposition: attachment; filename=Fiscal (Yearly) - Access Append to Timesheet.csv"); 
fclose($csv);

      You're right that looks much better. 🙂 I always forget about the datetime class...

        Write a Reply...