SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculating hours worked

    Hi, I'm setting up a timesheet system with PHP / MySQL and can't figure out how I can calculate hours worked based on a start time, end time and deducting a lunch time value. I saw another thread for this but no information about how the functions could be used.

    Example:

    Someone starts at 08:30 and finishes at 17:00, with a half-hour lunch. I have three fields for this- StartTime, LunchTime (the user enters 00:30 for half an hour) and EndTime. What I need is for the system to look at these times and work out that 8 hours have been worked, and enter this as an integer when the form has been sent (it doesn't need to go into the database, just on the summary / confirmation page for the person who's filling in the timesheets)

    Likewise, If they started a quarter of an hour later, it should return 7.75.

    That way, I can easily add up the total number of hours worked for the week, multiply by the pay rate and therefore report on the pay.

    Anyone know how this can be done? Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Can you post the code you've got so far?

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Can you post the code you've got so far?
    Sure:

    This is the timesheet form that the user fills in

    Code:
    <?php
    include ('inc/dbconnect.php');
    
    $searchstring = mysql_real_escape_string($_GET['ID']);
    
    $query = "select * from contractors WHERE `ID` = '$searchstring'"; // specify the table and field names for the SQL query
    
     $numresults=mysql_query($query);
     $numrows=mysql_num_rows($numresults);
    
    if ($numrows == 0)
      {
      echo "<p>No record matching these details currently exists. Please check that the details are correct.</p>";
      }
    // get results
      $result = mysql_query($query) or die("The Admin system is undergoing maintenance at the moment and will be available shortly");
    
    // begin to show results set
    $count = 1 + $s ;
    // display the results returned
    while ($row= mysql_fetch_array($result)) {
      $id = $row["ID"];
      $surname = $row["Surname"];
      $firstname = $row["Firstname"];
      $dateadded = $row["DateAdded"];
      $datelastmodified = $row["DateLastModified"];
      		//Date formatting for output in the summary screen
    		$dateaddednice = date("F j, Y", strtotime($dateadded));
    		$datelastmodifiednice = date("F j, Y", strtotime($datelastmodified));
      
      echo '<br /><h3>Creating timesheet for ' .$firstname. ' ' .$surname. '</h3>';
    	echo '<form action="createtimesheet-addtotable.php" method="post" name="createtimesheet-addtotable">
    	          <table width="1000" border="0" cellspacing="5" cellpadding="5" >
    		      <tr>
    			  <td align="left">Surname</td>
    			  <td align="left"><input type="hidden" name="_AddedBy" value="[[+username]]" /><input name="_ID" value= ' . $id . ' readonly type="hidden" /><input name="_Surname" class="textinput" readonly size="50" value="'.$surname.'" /></td>
    			  </tr>
    			  <tr>
    			  <td align="left">First Name</td>
    			  <td align="left"><input name="_Firstname" class="textinput" readonly size="50" value="'.$firstname.'" /></td>
    			  </tr>
    			  <tr>
    			  <td align="left">Client</td>
    			  <td align="left"><select name="_Client" class="textinput" size="1">';
    			  			$sql = "SELECT * FROM clients ORDER BY ClientName ASC";
    						$rs = mysql_query($sql);                             
    							while($row = mysql_fetch_array($rs)){                 
            						echo "<option value = '".$row['ClientName']."'>".$row['ClientName']."</option>";   
    								}
    			  echo '</select></td>
    			  </tr>
    			  <tr>
    			  <td align="left">Week commencing<br /><span class="formnotes">Important: use the Select Date link to ensure that the date is in the right format for the database</span></td>
    			  <td align="left">';
    			  echo "<input name=\"_WeekCommencing\" class=\"smallinput\" size=\"50\" />&nbsp;&nbsp;<a href=\"javascript:showCal('Calendar1')\">Select Date</a></td>";
    			  echo '</tr>
    			  <tr>
    			  </table><br /><br />';
    //Timesheet table
    echo '<span class="formnotes">Enter Rate per hour, without pound symbol. For example, 25 per hour should be written as 25.00. Enter start and end times as 24-hour clock times, for example 08:00 and 17:00.</span><br /><br />';
    	echo '<table width="800" border="0" cellspacing="5" cellpadding="5">
    	      <tr>
    		  <td width="120">Monday</td><td width="200">Rate <input name="_MondayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_MondayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_MondayEndTime" class="smallinput" size="10" /><td width="200">Lunch <input name="_MondayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Tuesday</td><td width="200">Rate <input name="_TuesdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_TuesdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_TuesdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_TuesdayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Wednesday</td><td width="200">Rate <input name="_WednesdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_WednesdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_WednesdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_WednesdayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Thursday</td><td width="200">Rate <input name="_ThursdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_ThursdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_ThursdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_ThursdayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Friday</td><td width="200">Rate <input name="_FridayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_FridayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_FridayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_FridayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Saturday</td><td width="200">Rate <input name="_SaturdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_SaturdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_SaturdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_SaturdayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    	      <tr>
    		  <td width="120">Sunday</td><td width="200">Rate <input name="_SundayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_SundayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_SundayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_SundayLunch" class="smallinput" size="10" /></td>
    		  </tr>
    		  </table><br />';
    
    		echo '<input type="submit" class="signinbutton" value="Create Timesheet" name="createtimesheet-addtotable" /></form>';
    
      $count++ ;
      }
      ?>
    And this is the form handler code (I haven't added in anything to convert or calculate times worked etc. yet so it's just a simple insert to the db)

    Code:
    <?php
    include ('inc/dbconnect.php');
    
    if($_POST['createtimesheet-addtotable'])
    {
      $contractorid = mysql_real_escape_string(@$_POST['_ID']);
      $surname = mysql_real_escape_string(@$_POST['_Surname']);
      $firstname = mysql_real_escape_string(@$_POST['_Firstname']);
      $clientname = mysql_real_escape_string(@$_POST['_ClientName']);
      $weekcommencing = mysql_real_escape_string(@$_POST['_WeekCommencing']);
      
      $mondayrate = mysql_real_escape_string(@$_POST['_MondayRate']);
      $mondaystarttime = mysql_real_escape_string(@$_POST['_MondayStartTime']);
      $mondayendtime = mysql_real_escape_string(@$_POST['_MondayEndTime']);
      
      $tuesndayrate = mysql_real_escape_string(@$_POST['_TuesdayRate']);
      $tuesdaystarttime = mysql_real_escape_string(@$_POST['_TuesdayStartTime']);
      $tuesdayendtime = mysql_real_escape_string(@$_POST['_TuesdayEndTime']);
      
      $wednesdayrate = mysql_real_escape_string(@$_POST['_WednesdayRate']);
      $wednesdaystarttime = mysql_real_escape_string(@$_POST['_WednesdayStartTime']);
      $wednesdayendtime = mysql_real_escape_string(@$_POST['_WednesdayEndTime']);
      
      $thursdayrate = mysql_real_escape_string(@$_POST['_ThursdayRate']);
      $thursdaystarttime = mysql_real_escape_string(@$_POST['_ThursdayStartTime']);
      $thursdayendtime = mysql_real_escape_string(@$_POST['_ThursdayEndTime']);
      
      $fridayrate = mysql_real_escape_string(@$_POST['_FridayRate']);
      $fridaystarttime = mysql_real_escape_string(@$_POST['_FridayStartTime']);
      $fridayendtime = mysql_real_escape_string(@$_POST['_FridayEndTime']);
      
      $saturdayrate = mysql_real_escape_string(@$_POST['_SaturdayRate']);
      $saturdaystarttime = mysql_real_escape_string(@$_POST['_SaturdayStartTime']);
      $saturdayendtime = mysql_real_escape_string(@$_POST['_SaturdayEndTime']);
      
      $sundayrate = mysql_real_escape_string(@$_POST['_SundayRate']);
      $sundaystarttime = mysql_real_escape_string(@$_POST['_SundayStartTime']);
      $sundayendtime = mysql_real_escape_string(@$_POST['_SundayEndTime']);
      
      $dateadded = date("Y-m-d");
      $addedby = mysql_real_escape_string(@$_POST['_AddedBy']);
    
    // Build SQL Query  
    $query = "INSERT INTO timesheets (ContractorID, Surname, Firstname, ClientName, WeekCommencing, MondayRate, MondayStartTime, MondayEndTime, MondayLunch, TuesdayRate, TuesdayStartTime, TuesdayEndTime, TuesdayLunch, WednesdayRate, WednesdayStartTime, WednesdayEndTime, WednesdayLunch, ThursdayRate, ThursdayStartTime, ThursdayEndTime, ThursdayLunch, FridayRate, FridayStartTime, FridayEndTime, FridayLunch, SaturdayRate, SaturdayStartTime, SaturdayEndTime, SaturdayLunch, SundayRate, SundayStartTime, SundayEndTime, SundayLunch, DateAdded, AddedBy) VALUES ('$contractorid','$surname','$firstname','$clientname','$weekcommencing','$mondayrate','$mondaystarttime','$mondayendtime','$mondaylunch','$tuesdayrate','$tuesdaystarttime','$tuesdayendtime','$tuesdaylunch','$wednesdayrate','$wednesdaystarttime','$wednesdayendtime','$wednesdaylunch','$thursdayrate','$thursdaystarttime','$thursdayendtime','$thursdaylunch','$fridayrate','$fridaystarttime''$fridayendtime','$fridaylunch','$saturdayrate','$saturdaystarttime','$saturdayendtime','$saturdaylunch','$sundayrate','$sundaystarttime','$sundayendtime','$sundaylunch','$dateadded','$addedby')"; // specify the table and field names for the SQL query
    if($result = mysql_query($query)) 
    { 	
        header('location: admin-confirmed.html');
    } else {
         echo "ERROR: File not moved correctly";
    }
    }
    
    ?>

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, this is the correct form handler code:
    PHP Code:
    <?php
    include ('inc/dbconnect.php');

    if(
    $_POST['createtimesheet-addtotable'])
    {
      
    $contractorid mysql_real_escape_string(@$_POST['_ID']);
      
    $surname mysql_real_escape_string(@$_POST['_Surname']);
      
    $firstname mysql_real_escape_string(@$_POST['_Firstname']);
      
    $clientname mysql_real_escape_string(@$_POST['_ClientName']);
      
    $weekcommencing mysql_real_escape_string(@$_POST['_WeekCommencing']);
      
      
    $mondayrate mysql_real_escape_string(@$_POST['_MondayRate']);
      
    $mondaystarttime mysql_real_escape_string(@$_POST['_MondayStartTime']);
      
    $mondayendtime mysql_real_escape_string(@$_POST['_MondayEndTime']);
      
    $mondaylunchstart mysql_real_escape_string(@$_POST['_MondayLunchStart']);
      
    $mondaylunchend mysql_real_escape_string(@$_POST['_MondayLunchEnd']);
      
      
    $tuesdayrate mysql_real_escape_string(@$_POST['_TuesdayRate']);
      
    $tuesdaystarttime mysql_real_escape_string(@$_POST['_TuesdayStartTime']);
      
    $tuesdayendtime mysql_real_escape_string(@$_POST['_TuesdayEndTime']);
      
    $tuesdaylunchstart mysql_real_escape_string(@$_POST['_TuesdayLunchStart']);
      
    $tuesdaylunchend mysql_real_escape_string(@$_POST['_TuesdayLunchEnd']);
      
      
    $wednesdayrate mysql_real_escape_string(@$_POST['_WednesdayRate']);
      
    $wednesdaystarttime mysql_real_escape_string(@$_POST['_WednesdayStartTime']);
      
    $wednesdayendtime mysql_real_escape_string(@$_POST['_WednesdayEndTime']);
      
    $wednesdaylunchstart mysql_real_escape_string(@$_POST['_WednesdayLunchStart']);
      
    $wednesdaylunchend mysql_real_escape_string(@$_POST['_WednesdayLunchEnd']);
      
      
    $thursdayrate mysql_real_escape_string(@$_POST['_ThursdayRate']);
      
    $thursdaystarttime mysql_real_escape_string(@$_POST['_ThursdayStartTime']);
      
    $thursdayendtime mysql_real_escape_string(@$_POST['_ThursdayEndTime']);
      
    $thursdaylunchstart mysql_real_escape_string(@$_POST['_ThursdayLunchStart']);
      
    $thursdaylunchend mysql_real_escape_string(@$_POST['_ThursdayLunchEnd']);
      
      
    $fridayrate mysql_real_escape_string(@$_POST['_FridayRate']);
      
    $fridaystarttime mysql_real_escape_string(@$_POST['_FridayStartTime']);
      
    $fridayendtime mysql_real_escape_string(@$_POST['_FridayEndTime']);
      
    $fridaylunchstart mysql_real_escape_string(@$_POST['_FridayLunchStart']);
      
    $fridaylunchend mysql_real_escape_string(@$_POST['_FridayLunchEnd']);
      
      
    $saturdayrate mysql_real_escape_string(@$_POST['_SaturdayRate']);
      
    $saturdaystarttime mysql_real_escape_string(@$_POST['_SaturdayStartTime']);
      
    $saturdayendtime mysql_real_escape_string(@$_POST['_SaturdayEndTime']);
      
    $saturdaylunchstart mysql_real_escape_string(@$_POST['_SaturdayLunchStart']);
      
    $saturdaylunchend mysql_real_escape_string(@$_POST['_SaturdayLunchEnd']);
      
      
    $sundayrate mysql_real_escape_string(@$_POST['_SundayRate']);
      
    $sundaystarttime mysql_real_escape_string(@$_POST['_SundayStartTime']);
      
    $sundayendtime mysql_real_escape_string(@$_POST['_SundayEndTime']);
      
    $sundaylunchstart mysql_real_escape_string(@$_POST['_SundayLunchStart']);
      
    $sundaylunchend mysql_real_escape_string(@$_POST['_SundayLunchEnd']);
      
      
    $dateadded date("Y-m-d");
      
    $addedby mysql_real_escape_string(@$_POST['_AddedBy']);

    // Build SQL Query  
    $query "INSERT INTO timesheets (ContractorID, Surname, Firstname, ClientName, WeekCommencing, MondayRate, MondayStartTime, MondayEndTime, MondayLunchStart, MondayLunchEnd, TuesdayRate, TuesdayStartTime, TuesdayEndTime, TuesdayLunchStart, TuesdayLunchEnd, WednesdayRate, WednesdayStartTime, WednesdayEndTime, WednesdayLunchStart, WednesdayLunchEnd, ThursdayRate, ThursdayStartTime, ThursdayEndTime, ThursdayLunchStart, ThursdayLunchEnd, FridayRate, FridayStartTime, FridayEndTime, FridayLunchStart, FridayLunchEnd, SaturdayRate, SaturdayStartTime, SaturdayEndTime, SaturdayLunchStart, SaturdayLunchEnd, SundayRate, SundayStartTime, SundayEndTime, SundayLunchStart, SundayLunchEnd, DateAdded, AddedBy) VALUES ('$contractorid','$surname','$firstname','$clientname','$weekcommencing','$mondayrate','$mondaystarttime','$mondayendtime','$mondaylunchstart','$mondaylunchend','$tuesdayrate','$tuesdaystarttime','$tuesdayendtime','$tuesdaylunchstart','$tuesdaylunchend','$wednesdayrate','$wednesdaystarttime','$wednesdayendtime','$wednesdaylunchstart','$wednesdaylunchend','$thursdayrate','$thursdaystarttime','$thursdayendtime','$thursdaylunchstart','$thursdaylunchend','$fridayrate','$fridaystarttime''$fridayendtime','$fridaylunchstart','$fridaylunchend','$saturdayrate','$saturdaystarttime','$saturdayendtime','$saturdaylunchstart','$saturdaylunchend','$sundayrate','$sundaystarttime','$sundayendtime','$sundaylunchstart','$sundaylunchend','$dateadded','$addedby')"// specify the table and field names for the SQL query

    echo $query;

    if(
    $result mysql_query($query)) 
    {     
    $gethours 'SELECT SUBTIME(TIMEDIFF(MondayEndTime, MondayStartTime), MondayLunch) FROM timesheets';

    $result2 mysql_query($gethours);
    echo 
    $result2;

        
    header('location: admin-confirmed.html');
    } else {
         echo 
    "ERROR: File not moved correctly";
    }
    }

    ?>

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn't tie you down so much to a particular database server software.

    Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    take a look at strtotime

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Have I understood this correctly.

    The payrate can be different for each day?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •