PDA

View Full Version : Sum of Time Attendance



newphpcoder
10-18-2011, 07:42 AM
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.

Rowsdower!
10-18-2011, 01:56 PM
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() (http://php.net/manual/en/function.strtotime.php) for each of your stored times and then proceed as above with your subtraction and calculation of hours.

newphpcoder
10-19-2011, 12:57 AM
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() (http://php.net/manual/en/function.strtotime.php) 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...

hinch
10-19-2011, 10:30 AM
you can do something like this (copy and paste from something i've done recently)


//$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.

newphpcoder
10-19-2011, 10:41 AM
you can do something like this (copy and paste from something i've done recently)


//$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

newphpcoder
10-19-2011, 10:43 AM
this is my code in importing .xml file



<?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 == 1 ) $emp = $cell->nodeValue;
if ( $index == 2 ) $employee = $cell->nodeValue;
if ( $index == 3 ) $last = $cell->nodeValue;
if ( $index == 4 ) $mi = $cell->nodeValue;
if ( $index == 5 ) $date = $cell->nodeValue;
if ( $index == 6 ) $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>

hinch
10-19-2011, 10:46 AM
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?

newphpcoder
10-19-2011, 10:49 AM
$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

newphpcoder
10-19-2011, 10:53 AM
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

hinch
10-19-2011, 10:54 AM
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


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

newphpcoder
10-20-2011, 12:49 AM
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

newphpcoder
10-20-2011, 02:43 AM
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...

newphpcoder
10-20-2011, 06:22 AM
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
$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 == 1 ) $emp = $cell->nodeValue;
if ( $index == 2 ) $employee = $cell->nodeValue;
if ( $index == 3 ) $last = $cell->nodeValue;
if ( $index == 4 ) $mi = $cell->nodeValue;
if ( $index == 5 ) $date = $cell->nodeValue;
if ( $index == 6 ) $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:


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

hinch
10-20-2011, 09:01 AM
ok i'll make it simple for you

in your xml have 2 nodes


<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.


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.

newphpcoder
10-20-2011, 10:26 AM
ok i'll make it simple for you

in your xml have 2 nodes


<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.


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
$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 == 1 ) $emp = $cell->nodeValue;
if ( $index == 2 ) $employee = $cell->nodeValue;
if ( $index == 3 ) $last = $cell->nodeValue;
if ( $index == 4 ) $mi = $cell->nodeValue;
if ( $index == 5 ) $date = $cell->nodeValue;
if ( $index == 6 ) $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

hinch
10-20-2011, 01:28 PM
it would be possible storing them in separate columns but it makesthings difficult if you merge your date times into single strings then convert them to unix time you can do simple subtraction to give you the number of seconds worked then just do $numbersecondsworked / 60/60 to give you the number of hours worked.

newphpcoder
10-21-2011, 01:30 AM
So, what is the easy way should I do ...Thank you....