rekap lembur

                <div class="row mt">
                    <div class="col-lg-12">
                      <div class="form-panel">

                          <section id="unseen">

<?php

$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='dbpegawai';
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
$start_date=isset($_POST['start_date'])?$_POST['start_date']:'';
$end_date=isset($_POST['end_date'])?$_POST['end_date']:'';
$status=  isset($_POST['status'])?$_POST['status']:'';
$sql="
SELECT a.nid, b.nama, a.f1, a.f2, a.f3, a.f4,a.tanggal, DAY(tanggal) AS tgl
FROM lembur a
JOIN karyawan b USING(nid)
WHERE 1=1 and status='$status'"
.(!empty($start_date)?"AND a.tanggal>= '{$start_date}' ":"")
.(!empty($end_date)?"AND a.tanggal<= '{$end_date}' ":"")
."ORDER BY b.nama,a.tanggal";
$qry=$db->query($sql);
$data=array();
while($row=$qry->fetch_object()){
if(!isset($data[$row->nid])){
$data[$row->nid]=array();
$data[$row->nid]['nama']=$row->nama;
}
$data[$row->nid][$row->tgl]=$row;
}

$sql_2="SELECT nid, tanggal FROM piket WHERE  tanggal>= '$start_date' AND tanggal<= '$end_date'";
$qry_2=$db->query($sql_2);
$data_2=array();
while($row_2=$qry_2->fetch_object()){
    $xx = substr($row_2->tanggal, 8, 2);
    $data_2[$row_2->nid][$xx]='';
}

?>



<form class="form-inline" method='post'  >

  <div class="form-group">
            <input type="text" class="form-control date-picker" data-date-format="yyyy-mm-dd" name='start_date'placeholder="tanggal awal" />
    </div>
<div class="form-group">
        <input type="text" class="form-control date-picker" data-date-format="yyyy-mm-dd" name='end_date' placeholder="tanggal akhir" />
   </div>
     <div class="form-group">
                  <select name="status" class="form-control">
                        <option value="organik">Organik</option>
                        <option value="OJT">OJT</option>
                        <option value="GSP">GSP</option>
                </select>

            </div>
<input type='submit' class="btn btn-theme"  name='submit' value='t                               ampilkan' />
</form>



<?php
$start_counter=!empty($start_date)?(int)substr($start_date,-2):1;
$end_counter=!empty($end_date)?(int)substr($end_date,-2):31;
?>

 <div class="row mt">
  <div class="col-md-12">

   <h4><i class="fa fa-angle-right"></i> tabel lembur</h4>
<table  class="table table-bordered table-striped table-condensed" border='1'>
<tr>
    <th class="centered" rowspan='2'>No</th>
<th class="centered" rowspan='2'>NID</th>
<th class="centered" rowspan='2'>Nama</th>
<th class="centered" rowspan='2'>Fakorial</th>
<th class="centered" colspan='<?=($end_counter-$start_counter)+1;?>'>Tanggal</th>
<th class="centered" rowspan='2'>Total Lembur</th>
<th class="centered" rowspan='2'>Total Faktorial</th>
<th class="centered" rowspan='2'>Total jam lembur</th>
<th rowspan='2'>Total jam weekend</th>
<th rowspan='2'>Total jam hari hari kerja</th>
</tr>
<tr>
<?php
for($i=$start_counter;$i<=$end_counter;$i++) echo "<th>$i</th>"; ?>
</tr>
<?php
function isWeekend($date)
{
    $weekDay = date('w', strtotime($date));
    return ($weekDay == 0 || $weekDay == 6);
}
function total_jam($data)
{
    $total = '';
    //$total .= '('.$data[1].' ) + ('.$data[2].' ) + ('.$data[3].' ) + ('.$data[4].')'.'<br />';
    //$total .= '('.($data[1]  ).') + ('.($data[2] ).') + ('.($data[3] ).') + ('.($data[4] ).')'.'<br />';
    $total .= ($data[1] ) + ($data[2] ) + ($data[3] ) + ($data[4] );
    return $total;
}
function total_faktorial($data)
{
    $total = '';
//  $total .= '('.$data[1].' * 1.5) + ('.$data[2].' * 2) + ('.$data[3].' * 3) + ('.$data[4].' * 4)'.'<br />';
//  $total .= '('.($data[1] * '1.5').') + ('.($data[2] * '2').') + ('.($data[3] * '3').') + ('.($data[4] * '4').')'.'<br />';
    $total .= ($data[1] * '1.5') + ($data[2] * '2') + ($data[3] * '3') + ($data[4] * '4');
    return $total;
}

$nid=0;
$no=0;
foreach($data as $n=>$d)
{
    $total_lembur_hari_biasa = $total_lembur_hari_libur = 0;
    $faktorial_array = array(
        '1' => 0,
        '2' => 0,
        '3' => 0,
        '4' => 0,
    ); // default faktorial array
    echo "<tr>";
    echo    "<td class='centered' rowspan='4'>".(++$no)."</td>";
    echo    "<td class='centered' rowspan='4'>{$n}</td>";
    echo    "<td class='centered' rowspan='4'>{$d['nama']}</td>";

    for ($f = 1; $f <= 4; $f++) // loop 1 to 4
    {
        echo ($f!=1?"</tr><tr>":"")."<td class='centered'>f{$f}</td>";
        $fak="f$f";
        for($i=$start_counter;$i<=$end_counter;$i++) // loop 1 to 31
        {
                    $yy =  sprintf("%02d", $i);
            if(isset($d[$i]))
            {
                if (isWeekend($d[$i]->tanggal))
                {
                    $total_lembur_hari_libur += $d[$i]->$fak;
                }
                else
                {
                    $total_lembur_hari_biasa += $d[$i]->$fak;
                }

                                if(isset($data_2[$n][$yy])){
                                    echo "<td class='centered' style='background-color:green;'>{$d[$i]->$fak}</td>";
                                } else {
                                    echo "<td class='centered'>{$d[$i]->$fak}</td>";
                                }

                $faktorial_array[$f] += $d[$i]->$fak; // sum faktorial array based on f1, f2, f3, f4
            }
            else
            {
                            if(isset($data_2[$n][$yy])){
                                echo "<td style='background-color:green;'></td>";
                            } else {
                                echo "<td></td>";
                            }
            }
        }
        echo ($f==1?"<td class='centered' rowspan='4'>".(count($d)-1)."</td>":"");
    }
    echo    '<td class="centered" valign="top">'.total_faktorial($faktorial_array).'</td>';
    echo    '<td class="centered"  valign="top">'.total_jam($faktorial_array).'</td>';
    echo    '<td valign="top">'.$total_lembur_hari_libur.'</td>';
    echo    '<td valign="top">'.$total_lembur_hari_biasa.'</td>';
    echo "</tr>";
}
?>
</table>