absensi_v2.pehape

<?php
date_default_timezone_set('Asia/Jakarta');

$host = 'localhost';
$pass = '';
$user = 'root';
$dbname='chl';
$connect = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($dbname);

// $tahun = $_POST["tahun"];
// $bulan = $_POST["bulan"];
$tahun = 2014;
$bulan = 8;

if($bulan<10) $bulan = "0$bulan";
echo "tahun: $tahun<br>";
echo "bulan: $bulan<br>";

/*
cari jumlah hari cukup pake built_in function php aja, bisa dgn 2cara:
$jh = date("t",mktime(0,0,0,$bulan,1,$tahun)); // <== hasilnya jumlah hari 28-31
$jh = date("j",mktime(0,0,0,$bulan+1,0,$tahun)); // <== hasilnya hari terakhir dlm suatu bulan
//function cari jumlah hari
function jumlah_hari($bulan,$tahun) {
        return ( date("t",mktime(0,0,0,$bulan,1,$tahun)) );
}
//cara menggunakan fungsi menghitung jumlah hari
echo "Jumlah Hari :";
echo jumlah_hari($bulan,$tahun);
$ar= jumlah_hari($bulan,$tahun);
*/

$thnbln = "$tahun-$bulan-";
$jh = date("t",mktime(0,0,0,$bulan,1,$tahun));
$t_awal = $thnbln."01";
$t_akhir = $thnbln."$jh";

echo "<table border=2 align=center name=outpu2>
<tr height=24><th>#</th><th>Nama</th><th>gender</th><th>NIK</th>\n";

$query = "SELECT S.nama,S.gender,S.NIK,\n";
for($t=1;$t<=$jh;$t++) {                                        // bikin BIG query, sekalian table header
        $tg = $t<10 ? "0$t" : $t;
        echo "<th>$tg</th>\n";
        $tgl = $thnbln.$tg;
        $query .= "(SELECT t$tg.id_hadir FROM tbl_absensi t$tg WHERE t$tg.id_siswa=S.id_siswa AND t$tg.tanggal='$tgl') 'tgl_$t', \n";
}
echo "<th width=20> Hadir</th>
<th width=20>Sakit</th>
<th width=20>Izin</th>
<th width=20>Alfa</th>
<th width=20>Total</th>
</tr>";

$mark = array("","bgcolor=#33ff33","bgcolor=#3333ff","bgcolor=#33ffff","bgcolor=#ff3333");
$query = substr(trim($query),0,-1);
$query .= " \nFROM tbl_absensi T JOIN tbl_siswa S ON S.id_siswa=T.id_SISWA
WHERE T.tanggal BETWEEN '$t_awal' AND '$t_akhir' GROUP BY T.id_siswa ORDER BY S.nama";

echo "<pre><b>Inilah BIG QUERY-nya</b>:\n $query</pre>"; // exit(0);

$result = mysql_query($query);
$n = 0;
while($row = mysql_fetch_row($result)) {
        $hadir = $sakit = $ijin = $alfa = 0;
        $n++;
        echo "<tr><td>$n</td>\n";
        foreach($row as $k=>$val) {
                $bg = "";
                $display = is_null($val) ? "&nbsp;" : $val;
                if($k>2 && !is_null($val) ) {
                        $bg = $mark[$val];
                        $display = "&nbsp;";
                        switch($val) {
                                case 1: $hadir++; break;
                                case 2: $sakit++; break;
                                case 3: $ijin++; break;
                                case 4: $lfa++; break;
                        }
                }
                echo "<td $bg>&nbsp;$display</td>\n";
        }
        $total = $hadir+$sakit+$ijin+$alfa;
        echo "<td align=center>$hadir</td>
        <td align=center>$sakit</td>
        <td align=center>$ijin</td>
        <td align=center>$alfa</td>
        <td align=center>$total</td></tr>\n";
}
mysql_free_result($result);
mysql_close($connect);
?>
</table><br>
1 HADIR<br>
2 SAKIT<br>
3 IZIN<br>
4 ALFA<br>