- Messages
- 94
- Reaction score
- 0
- Points
- 26
my sql code:
SELECT
distinct
a.date,
a.employee_id as emp_id,
b.time_in,
a.time_in,
b.time_out,
a.time_out,
case
when timediff(a.time_out,b.time_out) > time_format('02:00:00','%H:%i') then
case
when a.time_in > b.time_in then
timediff(timediff(a.time_out,b.time_out),timediff(a.time_in,b.time_in))
else
timediff(a.time_out,b.time_out)
end
else
0
end as overtime
FROM
daily_attendance a,
daily_schedule b
WHERE
a.date = b.date
and
a.employee_id = b.employee_id
and
a.date between '2015-06-10' and '2015-07-08'
and a.employee_id = 330
having timediff(a.time_out,b.time_out) >= time_format('02:00:00','%H:%i')
;
result:
View attachment 237501
eto naman yung php code ko.
$query6 = "SELECT
distinct
a.date as ddate,
a.employee_id as emp_id,
b.time_in,
a.time_in,
b.time_out,
a.time_out,
case
when timediff(a.time_out,b.time_out) > time_format('02:00:00','%H:%i') then
case
when a.time_in > b.time_in then
timediff(timediff(a.time_out,b.time_out),timediff(a.time_in,b.time_in))
else
timediff(a.time_out,b.time_out)
end
else
0
end as overtime
FROM
daily_attendance a,
daily_schedule b
WHERE
a.date = b.date
and
a.employee_id = b.employee_id
and
a.date between '".$_GET['date1']."' and '".$_GET['date']."'
and
a.employee_id = '".$user['employee_id']."'
/*AND
a.employee_id in
(select c.employee_id from daily_attendance c, daily_schedule d
where timediff(c.time_out,d.time_out) >= '02:00:00' and c.employee_id = 330)*/
having timediff(a.time_out,b.time_out) >= time_format('02:00:00','%H:%i')
"
;
$result = mysql_query($query6) or die(mysql_error());
$overtime = mysql_fetch_array($result);
echo "
<table id='example' class='table table-bordered'>
<thead>
<tr>
<th>Date</th>
<th>Attendance</th>
<th>Overtime</th>
</tr>
</thead>
<tbody>
";
$result7 = mysql_query($query6) or die(mysql_error());
while($row = mysql_fetch_array( $result7 )) {
$attendance = $class->getAttendance($row['ddate'],$user['employee_id']);
echo "
<tr>
<td>".$row['ddate']."</td>
<td>".date('g:i A',strtotime($attendance['time_in'])).' - '
.date('g:i A',strtotime($attendance[ 'time_out']))."</td>
<td>".$row['overtime']."</td>
</tr>
";
}
$ot_array = array($row['overtime']);
echo "<tfoot>
<tr>
<td></td>
<th>Total Overtime</th>
<td>".array_sum($ot_array)."</td>
</tr>
</tfoot></tbody>";
echo"</table>";
eto result:
View attachment 237503
as you can see po 0 lang yung lumalabas which is dapat 04:20:00 po.
pahelp nmn guys!!!!
SELECT
distinct
a.date,
a.employee_id as emp_id,
b.time_in,
a.time_in,
b.time_out,
a.time_out,
case
when timediff(a.time_out,b.time_out) > time_format('02:00:00','%H:%i') then
case
when a.time_in > b.time_in then
timediff(timediff(a.time_out,b.time_out),timediff(a.time_in,b.time_in))
else
timediff(a.time_out,b.time_out)
end
else
0
end as overtime
FROM
daily_attendance a,
daily_schedule b
WHERE
a.date = b.date
and
a.employee_id = b.employee_id
and
a.date between '2015-06-10' and '2015-07-08'
and a.employee_id = 330
having timediff(a.time_out,b.time_out) >= time_format('02:00:00','%H:%i')
;
result:
View attachment 237501
eto naman yung php code ko.
$query6 = "SELECT
distinct
a.date as ddate,
a.employee_id as emp_id,
b.time_in,
a.time_in,
b.time_out,
a.time_out,
case
when timediff(a.time_out,b.time_out) > time_format('02:00:00','%H:%i') then
case
when a.time_in > b.time_in then
timediff(timediff(a.time_out,b.time_out),timediff(a.time_in,b.time_in))
else
timediff(a.time_out,b.time_out)
end
else
0
end as overtime
FROM
daily_attendance a,
daily_schedule b
WHERE
a.date = b.date
and
a.employee_id = b.employee_id
and
a.date between '".$_GET['date1']."' and '".$_GET['date']."'
and
a.employee_id = '".$user['employee_id']."'
/*AND
a.employee_id in
(select c.employee_id from daily_attendance c, daily_schedule d
where timediff(c.time_out,d.time_out) >= '02:00:00' and c.employee_id = 330)*/
having timediff(a.time_out,b.time_out) >= time_format('02:00:00','%H:%i')
"
;
$result = mysql_query($query6) or die(mysql_error());
$overtime = mysql_fetch_array($result);
echo "
<table id='example' class='table table-bordered'>
<thead>
<tr>
<th>Date</th>
<th>Attendance</th>
<th>Overtime</th>
</tr>
</thead>
<tbody>
";
$result7 = mysql_query($query6) or die(mysql_error());
while($row = mysql_fetch_array( $result7 )) {
$attendance = $class->getAttendance($row['ddate'],$user['employee_id']);
echo "
<tr>
<td>".$row['ddate']."</td>
<td>".date('g:i A',strtotime($attendance['time_in'])).' - '
.date('g:i A',strtotime($attendance[ 'time_out']))."</td>
<td>".$row['overtime']."</td>
</tr>
";
}
$ot_array = array($row['overtime']);
echo "<tfoot>
<tr>
<td></td>
<th>Total Overtime</th>
<td>".array_sum($ot_array)."</td>
</tr>
</tfoot></tbody>";
echo"</table>";
eto result:
View attachment 237503
as you can see po 0 lang yung lumalabas which is dapat 04:20:00 po.
pahelp nmn guys!!!!