<?php
require ("includes/check_auth_user.php");
require ("includes/dbconnect.php");
include ('includes/function.php');

$admin = $_SESSION["admin_" . $dbname];


if (isset($_POST["submit"])) {
    require("includes/dbconnect.php");

    $cocond = "";
    $scond = "";
    $dcond = "";
    $tcond = "";
    $ccond = "";
    $catcond = "";

    if (!empty($_POST["cmbcount"])) {
        $pcon = trim($_POST["cmbcount"]);
        $cocond = "AND a.country='$pcon'";
    }

    if (!empty($_POST["cmbstate"])) {
        $pstat = trim($_POST["cmbstate"]);
        $scond = "AND a.state='$pstat'";
    }

    if (!empty($_POST["cmbdist"])) {
        $pdist = trim($_POST["cmbdist"]);
        $dcond = "AND a.district='$pdist'";
    }

    if (!empty($_POST["cmbteh"])) {
        $pteh = trim($_POST["cmbteh"]);
        $tcond = "AND a.tehsil='$pteh'";
    }

    if (!empty($_POST["cmbcity"])) {
        $pcity = trim($_POST["cmbcity"]);
        $ccond = "AND a.city='$pcity'";
    }

    if (!empty($_POST["cmbcat"])) {
        $pcat = trim($_POST["cmbcat"]);
        $catcond = "AND a.category='$pcat'";
    }


    $get = mysqli_query($conn, "SELECT a.category, count(*) AS totmakh, sum(a.students) as totstud, sum(a.batch) as totbatch, 
                          b.name as categoryname FROM `makhtab` a, `category` b WHERE category != '' and a.category = b.code 
                      $cocond $scond $dcond $tcond $ccond $catcond GROUP BY a.category ORDER BY b.name") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($get);

    /* Normal Browser View Part */
    if ($_POST["submit"] == "View") {
        ?>    
        <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
        <html xmlns="http://www.w3.org/1999/xhtml">
            <head>


                <style type="text/css">
                    <!--
                    @import url("css/table.css");
                    -->
                </style>

                <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

            </head>
            <body>
                <?php
                $comp = mysqli_query($conn, "SELECT * FROM `company`") or die(mysql_errno());
                $zcomp = mysqli_fetch_assoc($comp);
                echo "<font size='4px' color='#039'><b><em>$zcomp[compname]</font></em></b></font><br />";
                echo "<font size='2px' color='#039'><b><em> $zcomp[add1]</em></b></font><br /><br />";
                echo "<b><font size='2px' color='#039'>Category List &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font size='3px'>Date : " . date("M-d-Y") . "</b><br />";
                echo "<b><font size='2px' color='#039'>Total Category - $getno</b><br />";
                echo "<br />";
                ?>
                <table id="hor-minimalist-b" summary="Employee Pay Sheet" width='100%'>
                    <thead>
                        <tr>
                            <th style="text-align:left;">Sr.</th>
                            <th style="text-align:left;">Category Name</th>
                            <th style="text-align:right;">Total Makhtab</th>
                            <th style="text-align:right;">No. of Students</th>
                            <th style="text-align:right;">No. of Batch</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $ctr = 0;
                        while ($mcent = mysqli_fetch_assoc($get)) {
                            $ctr++;

                            $mcatcode = $mcent["category"];
                            $gcatname = mysqli_query($conn, "SELECT name FROM `category` WHERE code ='$mcatcode'") or die(mysqli_error($conn));
                            $getcat1 = mysqli_fetch_array($gcatname);
                            
                            echo "<tr> \n";
                            echo "<td style=\"text-align:left;\" width='60px'> \n <span><font size='$font'> $ctr</font> </span> \n </td> \n";
                            echo "<td style=\"text-align:left;\" width='400px'> \n <span> <font size='$font'>$getcat1[name]</font> </span> \n </td> \n";
                            echo "<td style=\"text-align:right;\" width='250px'> \n <span> <font size='$font'>$mcent[totmakh]</font> </span> \n </td> \n";
                            echo "<td style=\"text-align:right;\" width='250px'> \n <span> <font size='$font'>$mcent[totstud]</font> </span> \n </td> \n";
                            echo "<td style=\"text-align:right;\" width='250px'> \n <span> <font size='$font'>$mcent[totbatch]</font> </span> \n </td> \n";
                        }
                        ?>
                    </tbody>
                    <tfoot>

                    </tfoot>
                </table>
                </div>
            </body>
        </html>

        <?php
    } elseif ($_POST["submit"] == "Export to PDF") {
        require("library/fpdf/fpdf.php");

        class pdf extends fpdf {

            function Footer() {

                //Position at 1.5 cm from bottom
                $this->SetY(-15);
                //Arial italic 8
                $this->SetFont('Arial', 'I', 8);
                //Page number
                $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
            }

        }

        $pdf = new pdf('P', 'mm', 'A4');
        $pdf->AliasNbPages();
        $pdf->AddPage();
        $pdf->SetMargins(10, 10, 10);
        $pdf->SetXY(5, 5);


        $pdf->SetFont('Arial', 'B', 12);
        $pdf->Cell(0, 0, "Total Subjects : " . $getno, 0, 0, 'L');

        $pdf->Ln(10);
        $pdf->SetFont('Arial', 'BI', 10);

        $pdf->Cell(10, 8, 'Sr.', 1);

        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(120, 8, 'Name', 1);
        $pdf->Cell(25, 8, 'Code', 1);
        $pdf->Ln();
        $ctr = 0;
        $pdf->SetFont('Arial', '', 10);


        while ($cty = mysqli_fetch_assoc($get)) {
            $ctr++;
            $pdf->SetFont('Arial', 'I', 8);

            $pdf->Cell(10, 7, $ctr, 1);

            $pdf->SetFont('Arial', '', 10);

            $pdf->Cell(120, 7, $cty['name'], 1);
            $pdf->Cell(25, 7, $cty['code'], 1);
            $pdf->Ln();
        }

        $pdf->Output('subject_list.pdf', 'D');
    }



    /* For Excel */ else {

        function cleanData(&$str) {
            $str = preg_replace("/\t/", "\\t", $str);
            $str = preg_replace("/\r?\n/", "\\n", $str);
            if (strstr($str, '"'))
                $str = '"' . str_replace('"', '""', $str) . '"';
        }

// filename for download
        $filename = "subject_List_" . date('Ymd') . ".xls";

        header("Content-Disposition: attachment; filename=\"$filename\"");
        header("Content-Type: application/vnd.ms-excel");

        $flag = false;


        $result = mysqli_query($conn, "SELECT code, `name` FROM subject WHERE `name`!='' $cond ORDER BY `name`") or die(mysqli_error($conn));

        while (false !== ($row = mysqli_fetch_assoc($result))) {
            if (!$flag) {
                echo "" . implode("\t", array_keys($row)) . "\r\n";
                $flag = true;
            }
            array_walk($row, 'cleanData');
            echo implode("\t", array_values($row)) . "\r\n";
        }
        exit;
    }
}// Submit Tag Close
?>