<?php include ('header.php');?>
<?php
include ('../../db/db_connect.php');

$query="SELECT  DISTINCT(USE_USERSCODE),
STA_STAFFNAME
FROM STKN.TKN_LWTPRIKSA,
STKN.TKN_LWTAKAUNS,
SADM.ADM_USERSCODE,
SADM.VPY_STAFFLIST
WHERE LAW_ACMTABLES='E'
AND LAW_NODAFTARS = LWT_NODAFTARS
AND LAW_ENTRYOPER = USE_USERSCODE
AND STA_PAYNUMBER = USE_USERSCODE
AND STA_JAWATNAME ='PEMBANTU PENILAIAN'
AND to_char(LAW_LWTTARIKH,'yyyy') = '2019'
ORDER BY
STA_STAFFNAME";
$query = ociparse($db_conn, $query);
oci_execute($query);
$counter=1;


$query1="SELECT
USE_USERSCODE,
STA_STAFFNAME,
COUNT (*) LAW_ENTRYOPER,
to_char(LAW_LWTTARIKH,'yyyy-mm-dd') AS LAW_LWTTARIKH
FROM STKN.TKN_LWTPRIKSA,
STKN.TKN_LWTAKAUNS,
SADM.ADM_USERSCODE,
SADM.VPY_STAFFLIST
WHERE LAW_ACMTABLES='E'
AND LAW_NODAFTARS = LWT_NODAFTARS
AND LAW_ENTRYOPER = USE_USERSCODE
AND STA_PAYNUMBER = USE_USERSCODE
AND STA_JAWATNAME ='PEMBANTU PENILAIAN'
AND to_char(LAW_LWTTARIKH,'yyyy') = '2019'
AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') >= '2019-01-01' AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') <= '2019-07-31'
GROUP BY
USE_USERSCODE,
STA_STAFFNAME,
LAW_LWTTARIKH,
LAW_ENTRYOPER
ORDER BY
STA_STAFFNAME";
$query1 = ociparse($db_conn, $query1);
oci_execute($query1);
$dataRow1 = [];
while ($row1= oci_fetch_array($query1) ) {
    $dataRow1[] = $row1;
}

$query2="SELECT
DISTINCT(USE_USERSCODE),
COUNT (*) LAW_ENTRYOPER
FROM STKN.TKN_LWTPRIKSA,
STKN.TKN_LWTAKAUNS,
SADM.ADM_USERSCODE,
SADM.VPY_STAFFLIST
WHERE LAW_ACMTABLES='E'
AND LAW_NODAFTARS = LWT_NODAFTARS
AND LAW_ENTRYOPER = USE_USERSCODE
AND STA_PAYNUMBER = USE_USERSCODE
AND STA_JAWATNAME ='PEMBANTU PENILAIAN'
AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') >= '2019-01-01' AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') <= '2019-07-31'
GROUP BY
USE_USERSCODE";
$query2 = ociparse($db_conn, $query2);
oci_execute($query2);
$dataRow2 = [];
while ($row2= oci_fetch_array($query2) ) {
    $dataRow2[] = $row2;
}

$query3="SELECT
COUNT (*) LAW_ENTRYOPER,
to_char(LAW_LWTTARIKH,'yyyy-mm-dd') AS LAW_LWTTARIKH
FROM STKN.TKN_LWTPRIKSA,
STKN.TKN_LWTAKAUNS,
SADM.ADM_USERSCODE,
SADM.VPY_STAFFLIST
WHERE LAW_ACMTABLES='E'
AND LAW_NODAFTARS = LWT_NODAFTARS
AND LAW_ENTRYOPER = USE_USERSCODE
AND STA_PAYNUMBER = USE_USERSCODE
AND STA_JAWATNAME ='PEMBANTU PENILAIAN'
AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') >= '2019-01-01' AND to_char(LAW_LWTTARIKH,'yyyy-mm-dd') <= '2019-07-31'
GROUP BY
USE_USERSCODE,
LAW_LWTTARIKH";
$query3 = ociparse($db_conn, $query3);
oci_execute($query3);
$dataRow3 = [];
while ($row3= oci_fetch_array($query3) ) {
    $dataRow3[] = $row3;
}
$rowTarikh = array(
    'JANUARY' => array(
        array(
            'start' => '2019-01-01',
            'end' => '2019-01-17',
        ),
        array(
            'start' => '2019-01-18',
            'end' => '2019-01-31',
        ),
    ),
    'FEBUARY' => array(
        array(
            'start' => '2019-02-01',
            'end' => '2019-02-17',
        ),
        array(
            'start' => '2019-02-18',
            'end' => '2019-02-28',
        ),
    ),
    'MARCH' => array(
        array(
            'start' => '2019-03-01',
            'end' => '2019-03-13',
        ),
        array(
            'start' => '2019-03-14',
            'end' => '2019-03-27',
        ),
    ),
    'APRIL' => array(
        array(
            'start' => '2019-03-28',
            'end' => '2019-04-09',
        ),
        array(
            'start' => '2019-04-10',
            'end' => '2019-04-23',
        ),

    ),

    'MAY' => array(
        array(
            'start' => '2019-04-24',
            'end' => '2019-05-08',
        ),
        array(
            'start' => '2019-05-09',
            'end' => '2019-05-22',
        ),
    ),
    'JUNE' => array(
        array(
            'start' => '2019-05-23',
            'end' => '2019-06-05',
        ),
        array(
            'start' => '2019-06-06',
            'end' => '2019-06-19',
        ),
    ),
    'JULY' => array(
        array(
            'start' => '2019-06-20',
            'end' => '2019-07-03',
        ),
        array(
            'start' => '2019-07-04',
            'end' => '2019-07-17',
        ),
        array(
            'start' => '2019-07-18',
            'end' => '2019-07-31',
        ),
    ),
);

?>
<div class="content-wrapper">

    <style>

        @page {
   size: 7in 9.25in;
   margin: 27mm 16mm 27mm 16mm;
}


        th, td {
  padding: 1px;
}

        * {
            box-sizing: border-box;
        }

        input[type=text], select, textarea {
            width: 30%;
            padding: 12px;
            border: 1px solid #ccc;
            border-radius: 4px;
            resize: vertical;
        }

        label {
            padding: 10px 10px 10px 0;
            display: inline-block;
        }

        input[type=submit] {
            background-color: #4CAF50;
            color: white;
            padding: 12px 20px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            float: right;
        }

        input[type=submit]:hover {
            background-color: #45a049;
        }

        .container {
            border-radius: 5px;
            background-color: #ffffff;
            padding: 20px;
        }

        .col-25 {
            float: left;
            width: 15%;
            margin-top: 6px;
        }

        .col-75 {
            float: left;
            width: 75%;
            margin-top: 6px;
        }

        /* Clear floats after the columns */
        .row:after {
            content: "";
            display: table;
            clear: both;
        }
        @media screen and (max-width: 100px) {
            .col-25, .col-75, input[type=submit] {
                width: 70%;
                margin-top: 0;
            }
        }
    </style>
    <section class="content-header">
    </section>
    <div class="container">
        <table width="100%" border="0" class="bott">
            <tr>
                <td></td>
            </tr>
        </table>
        <br>
        <table width="100%" border="0" class="bott">
            <tr>
                <td style="text-align: center;"><b>BORANG SEMAKAN MAKLUMAT PEGANGAN</b>
                </td>
            </tr>
        </table>
        <br>
  <table width="100%" border=1>
    <thead >
        <tr align="center">
                <th rowspan="2">BIL</th>
            <th rowspan="2">STAFF</th>
            <?php
            // loop tarikh untuk kira colspan table th
            foreach ($rowTarikh as $key => $value) { ?>
                <th colspan="<?php echo count($value) ?>"><?php echo $key ?></th>
            <?php } ?>
                <th align="center" rowspan="2">JUMLAH UNIT</th>
        </tr>
        <tr>
            <?php
            // loop data dalam satu bulan untuk papar tarikh. Untuk ni, just papar tarikh end
            foreach ($rowTarikh as $key => $value) { ?>

                <?php foreach ($value as $k => $v) { ?>
                    <th><?php echo $v['end']?></th>

                <?php } ?>

            <?php } ?>
        </tr>
    </thead>
    <tbody >
        <?php
                    // loop setiap staff di sini
      while ($row = oci_fetch_array($query)) { ?>
            <tr>
                     <td><?php echo $counter++;?></td>
                <td><?php  echo $row['STA_STAFFNAME'] ; ?></td>

                <?php
                // loop setiap bulan
                foreach ($rowTarikh as $key => $value) { ?>

                    <?php
                    $grandTotal = []; // haezal
                    // loop setiap tarikh dalam satu bulan
                    foreach ($value as $k => $v) { ?>
                        <th align="center">
                            <?php
                            $total=0;
                            $grandTotal[$k] = 0; // haezal
                    $tot=0;
                                   foreach ($dataRow1 as $row1) {

                                // make sure kiraan pada row data pada row staff yang berkenaan

                                if ($row1['USE_USERSCODE'] == $row['USE_USERSCODE']) {


                                // make sure kiraan pada row data pada row staff yang berkenaan


                                if ($v['start'] <= $row1['LAW_LWTTARIKH'] && $v['end'] >= $row1['LAW_LWTTARIKH']) {

                                    // bila tarikh row data tu in between tarikh yang di loop, count 1


                                        $total=+$row1['LAW_ENTRYOPER'];
                                        $grandTotal[$k]+=$total; // haezal

                                                                    }
                                }

                                }
                                                    echo $total;  // display result
                            ?>

                        </th>
                        <?php } ?>
                <?php } ?>

               <?php
                                               $t=0;

        foreach ($dataRow2 as $row2) {

                                if ($row['USE_USERSCODE'] == $row2['USE_USERSCODE']) {

                                        $tot=$row2['LAW_ENTRYOPER'];
                                }
                                }
       ?>
   <th bgcolor="#98AFC7"> <?php echo $tot; ?> </th>
            </tr>

        <?php } ?>
           <td></td>
    <td bgcolor="#98AFC7"> <B>JUMLAH PENGHANTARAN</B></td>

        <?php
            // haezal tambah
                // loop setiap bulan
                foreach ($rowTarikh as $key => $value) { ?>

                    <?php
                    // loop setiap tarikh dalam satu bulan
                    foreach ($value as $k => $v) { ?>
                        <td align="center">
                            <?php echo $grandTotal[$k] ?>
                        </td>

                    <?php } ?>

                <?php } ?>

    <?php /*<td bgcolor="#98AFC7">

               <?php   foreach ($dataRow3 as $row3) {

                                if ( $row3['LAW_LWTTARIKH'] >= '2019-01-01' && $row3['LAW_LWTTARIKH'] <= '2019-01-17'  ) {


                                        $t=+$row3['LAW_ENTRYOPER'];
                                                                                                    }
                                }
            echo $t;

        ?>
        */ ?>
        </td>
    </tbody>
</table>