Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Sum of Time Attendance

    Hi!

    I created a payroll system, and now I don't have any idea how can I sum the time of the employee in and out.

    I need the Sum of Hours that the employee attendance.

    I have table for the Attendance and table for the sum of hours, the data in attendance table is from the upload files .xml the format of time is 08:00:00


    I attach the codes

    Thank you.
    Attached Files Attached Files

  • #2
    Senior Coder Rowsdower!'s Avatar
    Join Date
    Oct 2008
    Location
    Some say it's everything.
    Posts
    2,027
    Thanks
    5
    Thanked 397 Times in 390 Posts
    Do you need to store the in/out times as human-readable values like that? You could just store a unix timestamp and subtract "out" from "in" to get the number of miliseconds worked, then calculate it to hours from there.

    Otherwise (if you are committed to the 08:00:00 format) you might just use strtotime() for each of your stored times and then proceed as above with your subtraction and calculation of hours.
    The object of opening the mind, as of opening the mouth, is to shut it again on something solid. –G.K. Chesterton
    See Mediocrity in its Infancy
    It's usually a good idea to start out with this at the VERY TOP of your CSS: * {border:0;margin:0;padding:0;}
    Seek and you shall find... basically:
    validate your markup | view your page cross-browser/cross-platform | free web tutorials | free hosting

  • Users who have thanked Rowsdower! for this post:

    newphpcoder (10-19-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Rowsdower! View Post
    Do you need to store the in/out times as human-readable values like that? You could just store a unix timestamp and subtract "out" from "in" to get the number of miliseconds worked, then calculate it to hours from there.

    Otherwise (if you are committed to the 08:00:00 format) you might just use strtotime() for each of your stored times and then proceed as above with your subtraction and calculation of hours.
    Right now I encountered problem in uploading and saving of date and time, my column date in .xml file has a format mm/dd/yyyy like for example 10/1/2011 and the time column in .xml 11:40:00 AM, 8:02:00 PM , in my code I dont know how can I save it with the same format.

    The In and Out is in one field onyl the time column..


    Thank you...

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    you can do something like this (copy and paste from something i've done recently)

    PHP Code:
    //$date = date_format(DateTime::createFromFormat('d.m.y', $avars[0]), 'Y-m-d');//php 5.3+ only
                                
                                
    list($day$month$year) = sscanf($avars[0], '%02d.%02d.%04d'); //php 5.2
                                
    $datetime = new DateTime("$year-$month-$day");
                                
    $expiration_date strtotime($datetime->format('Y-m-d')); 
    it'll reformat a date from d.m.y to a unix time stamp. its easily adjusted to suit your stuff by adjusting the sscanf() line and the $datetime = new line to suit your formatting.

    you could even go one step further and join your 2x input xml strings into a single string and just reformat that entire string rather than reformatting the 2 strings separately.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    newphpcoder (10-19-2011)

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by hinch View Post
    you can do something like this (copy and paste from something i've done recently)

    PHP Code:
    //$date = date_format(DateTime::createFromFormat('d.m.y', $avars[0]), 'Y-m-d');//php 5.3+ only
                                
                                
    list($day$month$year) = sscanf($avars[0], '%02d.%02d.%04d'); //php 5.2
                                
    $datetime = new DateTime("$year-$month-$day");
                                
    $expiration_date strtotime($datetime->format('Y-m-d')); 
    it'll reformat a date from d.m.y to a unix time stamp. its easily adjusted to suit your stuff by adjusting the sscanf() line and the $datetime = new line to suit your formatting.

    you could even go one step further and join your 2x input xml strings into a single string and just reformat that entire string rather than reformatting the 2 strings separately.
    Where should I put those code..???

    Thank you

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    this is my code in importing .xml file

    PHP Code:
    <?php
    $data 
    = array();


    $con mysql_connect("localhost""root","");
    if (!
    $con) { 
      die(
    mysql_error());
    }
    $db mysql_select_db("db_upload"$con);
    if (!
    $db) { 
      die(
    mysql_error());
    }

    $sql "select * from employee";
    $result =  mysql_query($sql$con);
    if (!
    $result) {
        die(
    mysql_error());
    }
    $total mysql_num_rows($result);
    if (
    $total 0) {
        
    $sql "delete from employee";
        
    $result =  mysql_query($sql$con);
        if (!
    $result) {
            die(
    mysql_error());
        }
    }
      
    function 
    add_employee($emp$employee$last$mi$date$time)
      {
          global 
    $data
          
          
    $con mysql_connect("localhost""root","");
          if (!
    $con){ die(mysql_error());}
          
    $db mysql_select_db("db_upload"$con);
          if (!
    $db) { 
              die(
    mysql_error());
          }

         
          
    $emp $emp;
          
    $employee $employee;
          
    $last $last;
          
    $mi $mi;
          
    $date substr($date,0,-13);
          
    $time substr($time,11,-4);
        
               
          
    $sql "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
          
    mysql_query($sql$con);
          
        
          
    $data []= array('EMP_NO' => $emp'Name' => $employee'last' => $last'mi' => $mi'date' => $date'time' => $time);
          
      }
      
      if ( 
    $_FILES['file']['tmp_name'] )
      {
          
    $dom DOMDocument::load$_FILES['file']['tmp_name'] );
                  
          
    $rows $dom->getElementsByTagName'Row' );
          global 
    $last_row;
          
    $last_row false;
          
    $first_row true;
          foreach (
    $rows as $row)
          {
              if ( !
    $first_row )
              {
                 
                  
    $emp "";
                  
    $employee "";
                  
    $last "";
                  
    $mi "";
                  
    $date "";
                  
    $time "";
                  
                  
                  
    $index 1;
                  
    $cells $row->getElementsByTagName'Cell' );
              
                  foreach( 
    $cells as $cell )
                  { 
                      
    $ind $cell->getAttribute'Index' );
                      if ( 
    $ind != null $index $ind;
                      
                      if ( 
    $index == $emp $cell->nodeValue;
                      if ( 
    $index == $employee $cell->nodeValue;
                      if ( 
    $index == $last $cell->nodeValue;
                      if ( 
    $index == $mi $cell->nodeValue;
                      if ( 
    $index == $date $cell->nodeValue;
                      if ( 
    $index == $time $cell->nodeValue;
                      
    $index += 1;
                  }
             
                  if (
    $emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                        
    $last_row true;
                  }      
                  else {
          
                        
    add_employee($emp$employee$last$mi$date$time);
                  }      
              }
              if (
    $last_row==true) {
                  
    $first_row true;
              }     
              else {
                  
    $first_row false;
              }
            
          }
      }
      
    ?>
      
      <html>
      <body>
      <table>
      <tr>
          <th>Employee Attendance</th>
      </tr>

      <?php foreach( $data as $row ) { ?>
      <tr>
      <td><?php echo( $row['EMP_NO'] ); ?></td> 
      <td><?php echo( $row['Name'] ); ?></td>
      <td><?php echo( $row['last'] ); ?></td>
      <td><?php echo( $row['mi'] ); ?></td>
      <td><?php echo( $row['date'] ); ?></td>
      <td><?php echo( $row['time'] ); ?></td>
      </tr>
      <?php ?>
      </table>
      </body>
     </html>

  • #7
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    personally I would put it in your add_employee function

    post examples of your $date and $time inputs to that function (before you start doing sub str stuff) and I can knock you up a better example. and how are you storing them in the DB as separate columns or in a single column etc and what data type is the column string? datetime? timestamp?
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    newphpcoder (10-19-2011)

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    PHP Code:
    $date substr($date,0,-13); 
    $time substr($time,11,-4); 
    before that code the output is:

    date:2011-10-01T00:00:00.000
    time: 1899-12-31T08:00:40.000

    and the .xml file:
    date: 10/1/2011
    time: 8:00:40 AM

    and it save to database:
    date: 2011-10-01
    time: 08:00:40
    now it save in database but the time AM and PM did not display and save.and the hours become 24 hours.

    The time and date field is very important for me to resolve because of computing the attendance.
    Thank you

  • #9
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by hinch View Post
    personally I would put it in your add_employee function

    post examples of your $date and $time inputs to that function (before you start doing sub str stuff) and I can knock you up a better example. and how are you storing them in the DB as separate columns or in a single column etc and what data type is the column string? datetime? timestamp?
    the date and time is separate column...

    date as date format and time as time format

    from xml.

    the data are:

    time : 8:00:00 AM
    date: 10/15/2011

    and now it save in DB:

    date 2011-10-15
    time: 08:00:00

    Thank you

  • #10
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    AM/PM are not supported by databases or time functions so stop using them use 24 hour if you must

    also why in your xmp file are you brining out a separate date time? it would make more sense to just bring back something in the form of "dd/mm/yyyy hh:mm:ss" as just a single xml node rather than 2.

    Anyway reformatting your date is

    PHP Code:
    list($month$day$year) = sscanf($date'%02d/%02d/%04d'); //php 5.2
    $datetime = new DateTime("$year-$month-$day");
    $unixdate strtotime($datetime->format('Y-m-d')); 
    This will return your date as a unix time stamp which you can then compare against / save whatever you want to do with it do something similar with your time stamp.

    I do think you're making life hard on yourself though. get your xml to sumply supply a single node back in a combined date/time stamp and you'll make your life easier
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #11
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    The hours that save in database is formatted in 24hours. If i combined the date and time in one column in .xml how can I compute the hours per employee? and is it also in database the date and time is in one field?

    Thank you

  • #12
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I tried INT and FLOAT datatype in time column, and the data save in database is only number 18...
    But in my xml file it is 6:00:00 PM

    I really don't know what data type shopuld I used and also the format cells in .xmkl file...

    I also tried to combine date and time i one field...
    And I dont know how can I add the time of employee..


    Thank you for your help...

  • #13
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I have date and time column separately...in time column the data is in and out of the employee...now I need the total sum of per employee working hours
    I tried this code for the importing the .xml fiole and save to database:

    PHP Code:
    <?php
    $data 
    = array();

    $con mysql_connect("localhost""root","");
    if (!
    $con) { 
      die(
    mysql_error());
    }
    $db mysql_select_db("db_upload"$con);
    if (!
    $db) { 
      die(
    mysql_error());
    }

    $sql "select * from employee";
    $result =  mysql_query($sql$con);
    if (!
    $result) {
        die(
    mysql_error());
    }
    $total mysql_num_rows($result);
    if (
    $total 0) {
        
    $sql "delete from employee";
        
    $result =  mysql_query($sql$con);
        if (!
    $result) {
            die(
    mysql_error());
        }
    }
      
    function 
    add_employee($emp$employee$last$mi$date$time)
      {
          global 
    $data
          
          
    $con mysql_connect("localhost""root","");
          if (!
    $con){ die(mysql_error());}
          
    $db mysql_select_db("db_upload"$con);
          if (!
    $db) { 
              die(
    mysql_error());
          }

          
    $emp $emp;
          
    $employee $employee;
          
    $last $last;
          
    $mi $mi;
          
    $date substr($date,0,-13);
          
    $time substr($time,11,-4);
         
    $date strtotime($date); 
         
    $date date('d-m-Y'$date);
          
    $time strftime('%I:%M %p'strtotime($time));


               
          
    $sql "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
          
    mysql_query($sql$con);
          
    $data []= array('EMP_NO' => $emp'Name' => $employee'last' => $last'mi' => $mi'date' => $date'time' => $time);
          
      }
      
      if ( 
    $_FILES['file']['tmp_name'] )
      {
          
    $dom DOMDocument::load$_FILES['file']['tmp_name'] );       
          
    $rows $dom->getElementsByTagName'Row' );
          global 
    $last_row;
          
    $last_row false;
          
    $first_row true;
          foreach (
    $rows as $row)
          {
              if ( !
    $first_row )
              {

                  
    $emp "";
                  
    $employee "";
                  
    $last "";
                  
    $mi "";
                  
    $date "";
                  
    $time "";
                  
                  
                  
    $index 1;
                  
    $cells $row->getElementsByTagName'Cell' );
              
                  foreach( 
    $cells as $cell )
                  { 
                      
    $ind $cell->getAttribute'Index' );
                      if ( 
    $ind != null $index $ind;
                      
                      if ( 
    $index == $emp $cell->nodeValue;
                      if ( 
    $index == $employee $cell->nodeValue;
                      if ( 
    $index == $last $cell->nodeValue;
                      if ( 
    $index == $mi $cell->nodeValue;
                      if ( 
    $index == $date $cell->nodeValue;
                      if ( 
    $index == $time $cell->nodeValue;
                      
    $index += 1;
                  }
             
                  if (
    $emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                        
    $last_row true;
                  }      
                  else {
                      
                        
    add_employee($emp$employee$last$mi$date$time);
                  }      
              }
              if (
    $last_row==true) {
                  
    $first_row true;
              }     
              else {
                  
    $first_row false;
              }
           
          }
      }
      
    ?>
      
      <html>
      <body>
      <table>
      <tr>
          <th>Employee Attendance</th>
      </tr>

      <?php foreach( $data as $row ) { ?>
      <tr>
      <td><?php echo( $row['EMP_NO'] ); ?></td> 
      <td><?php echo( $row['Name'] ); ?></td>
      <td><?php echo( $row['last'] ); ?></td>
      <td><?php echo( $row['mi'] ); ?></td>
      <td><?php echo( $row['date'] ); ?></td>
      <td><?php echo( $row['time'] ); ?></td>
      </tr>
      <?php ?>
      </table>
      </body>
     </html>
    and i have data

    date: 2011-10-01
    time: 07:30:00
    date: 2011-10-01
    time: 06:00:00

    and i tried also this code to sum the hours:
    Code:
    SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time))) AS total FROM employee WHERE EMP_NO = '100603' ORDER BY 'Date';
    and output is : 13.30

    but I want the result is the total hours that the employee work

  • #14
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    ok i'll make it simple for you

    in your xml have 2 nodes

    PHP Code:
    <starttime></starttime>
    <
    endtime></endtime
    In these nodes put in the users start time and end time in the format "yyyy-mm-dd hh:mm:ss"

    when you load your xml feed run it through this block of code.

    PHP Code:
    list($month$day$year$hour$min$sec) = sscanf($date'%04d-%02d-%02d %02d:%02d:%02d'); //php 5.2
    $datetime = new DateTime("$year-$month-$day $hour:$min:$sec");
    $unixdate strtotime($datetime->format('Y-m-d H:i:s')); 
    That will spit you back a purely numeric string

    Create 2 columns in your database one called start date one called end date.
    Insert the variable $unixdate for both the start and end date into the selected column.

    When you want to then calculate how many hours a person worked you just do end date column - start date column and it'll return you the number of seconds worked.

    I'm sure you can work out how to convert from seconds into hours/minutes yourself.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    newphpcoder (10-20-2011)

  • #15
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by hinch View Post
    ok i'll make it simple for you

    in your xml have 2 nodes

    PHP Code:
    <starttime></starttime>
    <
    endtime></endtime
    In these nodes put in the users start time and end time in the format "yyyy-mm-dd hh:mm:ss"

    when you load your xml feed run it through this block of code.

    PHP Code:
    list($month$day$year$hour$min$sec) = sscanf($date'%04d-%02d-%02d %02d:%02d:%02d'); //php 5.2
    $datetime = new DateTime("$year-$month-$day $hour:$min:$sec");
    $unixdate strtotime($datetime->format('Y-m-d H:i:s')); 
    That will spit you back a purely numeric string

    Create 2 columns in your database one called start date one called end date.
    Insert the variable $unixdate for both the start and end date into the selected column.

    When you want to then calculate how many hours a person worked you just do end date column - start date column and it'll return you the number of seconds worked.

    I'm sure you can work out how to convert from seconds into hours/minutes yourself.
    Actually I dont have any idea about the computation of time. And the .xml file that I have is like a excel file that was save as a .xml file...
    Now I have a date and time separate column in .xml and also in my database...and the datatype of date is date and time is time.

    and now here is my revise code:
    PHP Code:
    <?php
    $data 
    = array();


    $con mysql_connect("localhost""root","");
    if (!
    $con) { 
      die(
    mysql_error());
    }
    $db mysql_select_db("db_upload"$con);
    if (!
    $db) { 
      die(
    mysql_error());
    }

    $sql "select * from employee";
    $result =  mysql_query($sql$con);
    if (!
    $result) {
        die(
    mysql_error());
    }
    $total mysql_num_rows($result);
    if (
    $total 0) {
        
    $sql "delete from employee";
        
    $result =  mysql_query($sql$con);
        if (!
    $result) {
            die(
    mysql_error());
        }
    }
      
    function 
    add_employee($emp$employee$last$mi$date$time)
      {
          global 
    $data
          
          
    $con mysql_connect("localhost""root","");
          if (!
    $con){ die(mysql_error());}
          
    $db mysql_select_db("db_upload"$con);
          if (!
    $db) { 
              die(
    mysql_error());
          }

          
    $emp $emp;
          
    $employee $employee;
          
    $last $last;
          
    $mi $mi;
       
          
    $date substr($date,0,-13); //i used substr to remove the last character, because if im not used this the data display is :2011-01-10T00:00:00.000
          
    $time substr($time,11,-4);
          
    $date strtotime($date); 
          
    $date date('d-m-Y'$date);
          
          
    $time strftime('%I:%M %p'strtotime($time)); // i used this for 12 hours


               
          
    $sql "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
          
    mysql_query($sql$con);
          
        
          
    $data []= array('EMP_NO' => $emp'Name' => $employee'last' => $last'mi' => $mi'date' => $date'time' => $time);
          
      }
      
      if ( 
    $_FILES['file']['tmp_name'] )
      {
          
    $dom DOMDocument::load$_FILES['file']['tmp_name'] );

          
    $rows $dom->getElementsByTagName'Row' );
          global 
    $last_row;
          
    $last_row false;
          
    $first_row true;
          foreach (
    $rows as $row)
          {
              if ( !
    $first_row )
              {
                 
                  
    $emp "";
                  
    $employee "";
                  
    $last "";
                  
    $mi "";
                  
    $date "";
                  
    $time "";
                  
                  
                  
    $index 1;
                  
    $cells $row->getElementsByTagName'Cell' );
              
                  foreach( 
    $cells as $cell )
                  { 
                      
    $ind $cell->getAttribute'Index' );
                      if ( 
    $ind != null $index $ind;
                      
                      if ( 
    $index == $emp $cell->nodeValue;
                      if ( 
    $index == $employee $cell->nodeValue;
                      if ( 
    $index == $last $cell->nodeValue;
                      if ( 
    $index == $mi $cell->nodeValue;
                      if ( 
    $index == $date $cell->nodeValue;
                      if ( 
    $index == $time $cell->nodeValue;
                      
    $index += 1;
                  }
             
                  if (
    $emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                        
    $last_row true;
                  }      
                  else {
                    
                        
    add_employee($emp$employee$last$mi$date$time);
                  }      
              }
              if (
    $last_row==true) {
                  
    $first_row true;
              }     
              else {
                  
    $first_row false;
              }
           
          }
      }
      
    ?>
      
      <html>
      <body>
      <table>
      <tr>
          <th>Employee Attendance</th>
      </tr>

      <?php foreach( $data as $row ) { ?>
      <tr>
      <td><?php echo( $row['EMP_NO'] ); ?></td> 
      <td><?php echo( $row['Name'] ); ?></td>
      <td><?php echo( $row['last'] ); ?></td>
      <td><?php echo( $row['mi'] ); ?></td>
      <td><?php echo( $row['date'] ); ?></td>
      <td><?php echo( $row['time'] ); ?></td>
      </tr>
      <?php ?>
      </table>
      </body>
     </html>

    My question is...is it possible in my code to compute the hours per employee if my date and time is separately...because the time field has the data of time in and time out of employee.

    and honestly , i felt difficulties how to compute the hours of employee and it only basic hours per day is 8...Other said that it should be the date and time is combine in one column, but i don't have idea if how can I compute the time of employee..


    Thank you so much.. I hope you understand...Its my first time to do that..

    Thank you


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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