pahelp po sa sql mag sum ng time, or pwede rin kung idadaan sa php.

[specdroid]

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

 

[jskhulitz]

}
$ot_array = array($row['overtime']);

yung $ot_array assignment mo, nasa labas ng curly braces (delimiter ng loop mo), meaning ang nai-aassign as value ng $ot_array mo ay yung last value lang ng $row['overtime']. Gawin mong ganito

$ot_array = array($row['overtime']);
}

Plus, yung $ot_array mo ay hindi naman talaga array, kung isang simpleng variable lang. Buti nga walang na-issue na error

Warning: array_sum() expects parameter 1 to be array, integer given

para makapag assign ka ng value sa array, dapat ganito

$ot_array[B][COLOR="#FF0000"][][/COLOR][/B] = array($row['overtime']);

tsaka mo ngayon gamitin to

array_sum($ot_array);

 

[specdroid]

sir ty sa tulong. but same output parin e. still 0 parin yung binibigay nyang value.

 

[jskhulitz]

idisplay mo nga yung content nung array mo

echo '<pre>';
print_r($ot_array);
echo '</pre>';

or

var_dump($ot_array);

tapos i-display mo dito

 

[specdroid]

idisplay mo nga yung content nung array mo

echo '<pre>';
print_r($ot_array);
echo '</pre>';

or

var_dump($ot_array);

tapos i-display mo dito

View attachment 237547

eto po sir

 

[jskhulitz]

Bakit ganyan format ng array mo? 2-dimensional array ba talaga yan? kapag ganyan ang format ng array mo, ito lang ang way mo

$sum = 0;
foreach($ot_array as $key=>$value) {
    $sum += $value[0];
}
echo $sum;

 

[specdroid]

Bakit ganyan format ng array mo? 2-dimensional array ba talaga yan? kapag ganyan ang format ng array mo, ito lang ang way mo

$sum = 0;
foreach($ot_array as $key=>$value) {
    $sum += $value[0];
}
echo $sum;

sir tinanggal ko yung [] dun sa sinabi mong $ot_array[] = array($row['overtime']);

tas eto output nung pang kuha ng array

View attachment 237554

 

[jskhulitz]

tanggalin mo yan... na-copy ko pa pala yung array() dun sa script mo, after nyan, proceed ka na lang sa array_sum

sir tinanggal ko yung [] dun sa sinabi mong $ot_array[] = array($row['overtime']);

tas eto output nung pang kuha ng array

View attachment 1071648

 

[specdroid]

tanggalin mo yan... na-copy ko pa pala yung array() dun sa script mo, after nyan, proceed ka na lang sa array_sum

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[] = $row['overtime'];
}

sir eto na yung code ko. output niya is 4. tama naman yun kasi 2 + 2 =4, kaya lang hindi naka time yung format nya. pano kaya yun sir?
ayaw din gumana ng strtotime nageerror na siya.

 

[jskhulitz]

				$parts = explode(':',$row['overtime']);
				$ot_array[] = ($parts[0] * 60) + $parts[1];
                                }
				
				$totalMinutes = array_sum($ot_array);
				
				$hours = floor($totalMinutes / 60);
				$minutes = ($totalMinutes % 60);
				$totalTime = $hours . ':' . $minutes;
				echo $totalTime;

may cases ba na more than 24 hours ang OT?

 

[specdroid]

will edit correct codes

may cases ba na more than 24 hours ang OT?

merong more than 12:00AM ok na yun sa sql file ko na separated na sa biometrics kinuha. yung nakalagay dyan sir galing lang yan sa ELID nmen which is yung parang id na bibasa ng system n nsa may pintuhan nmen.

 

[kritus98]

visit this site: http://www.w3schools.com/sql/func_dateadd.asp

 

[specdroid]

solved. by using php class. thanks sa mga nagreply