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

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);
$maindb = 'demdmain';
$trandb = 'demdtran';
$zroot = $_SESSION["zdbroot"];
$formname = 'Packet Summary';

if (isset($_POST["view"])) {

    $section = $_POST["section"];
    $session = $_POST["session"];
    $exam = $_POST["exam"];

    $sessncond = '';
    $sectcond = '';
    $examcond = '';

    if (!empty($exam)) {
        $examcond = " AND `examcode` = '$exam' ";
    }

    if (!empty($session)) {
        $sessncond = " AND `session` = '$session' ";
    }

    if (!empty($section)) {
        $sectcond = " AND `section` = '$section' ";
    }

    mysqli_query($conn, "CREATE TEMPORARY TABLE pacsum (SELECT `examcode`,`medmcode`,`session`,`section`, sum(nopack) as nopack from `demdmain` 
                                                where vouchno != '' $examcond and year = '$myyear' $sectcond $sessncond group by `examcode`, medmcode)") or die(mysql_error);

    $mainqry = mysqli_query($conn, "SELECT `examcode` from `demdmain` where vouchno != '' and year = '$myyear'
                            $examcond $sectcond $sessncond  group by `examcode`") or die(mysql_error);

    $getno = mysqli_num_rows($mainqry);

    if ($getno == 0) {
        browse("No Record Found");
        echo "<script>window.close()</script>";
    }

    $comp = mysqli_query($conn, "SELECT * FROM $zroot.`company`")or die(mysqli_error($conn));
    $zcomp = mysqli_fetch_assoc($comp);

    include("includes/linkshead.php");
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">
    <style>

        body{
            font-size: 14px;

        }
        .mtable{
            border:1px solid black;
            boder-collapse:collapse;
        }
        .mtd{
            border:1px solid black;
            boder-collapse:collapse;
            text-align:right;
        }

        td,th,.t4{
            padding:0px;
            font-size:14px;
        }

        .padd{
            padding:7px;
        }

        .border{
            border-top-style:solid;
            border-right-style:none;
            border-left-style:none;
            border-bottom-style:solid;
            border-width:2px;
        }
        .border2{
            border-top-style:solid;
            border-right-style:none;
            border-left-style:none;
            border-bottom-style:solid;
            border-width:2px;
        }

        table{
            width:100%;
        }
        hr { 
            border-style: inset;
            border-width: 2px;
        } 

    </style>
    </head>
    <body>    
        <div class="page">
            <div class="col-lg-11"></div>
            <div class="col-lg-1">
                <a  class="btn btn-primary goback"  onclick="window.close()">Go Back</a>
            </div>

            <div class="subpage" style="margin-top:5px;width:100%;">
                <table width="100%" style="padding:5px; ">
                    <tr>
                        <td style="padding:3px;font-family: verdana"><b><center><h2 style="margin-bottom: 0px"><?php echo $zcomp['compname']; ?></h2></center></b></td>
                    </tr>
                    <tr>
                        <td style="font-size:12px"><center>
                        REG.NO. BOMOBAY P.T.ACT 1950/F 17203 PUNE /SOCIETY'S REG.ACT 1860 MAHARASHTRA/ 330 - 2000/ PUNE
                    </center></td>
                    </tr>
                    <tr>
                        <td style="font-size:13px;font-family: serif;padding-top: 5px"><center><?php echo $zcomp['add1'] . $zcomp['add2']; ?></center></td>
                    </tr>
                    <tr>
                        <td style="font-size:13px;font-family: serif"><center><?php echo $zcomp['add3'] . "&nbsp;" . "&nbsp;" . "&nbsp;" . "&nbsp;" . "Tel. -" . ' ' . $zcomp['tel']; ?></center></td>
                    </tr>
                </table>
                <br>


                <table style="width:100%">
                    <tr>
                        <td style="width:50%"><h4><b>SALES SUMMARY - PACKET WISE</b></h4></td>
                        <td style="width:50%"></td>
                    </tr>
                </table>
                <br>
                <?php
                ?>

                <table>
                    <tr class="border">
                        <td style="width:3%;" class="padd"><b>Sr.</b></td>
                        <td style="width:25%;" class="padd"><b>Exam</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>English</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>Marathi</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>Hindi</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>Gujrati</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>Urdu</b></td>
                        <td style="width:10%; text-align:right" class="padd"><b>Kannad</b></td>
                        <td style="width:11%; text-align:right" class="padd"><b>Total</b></td>
                    </tr>

                    <?php
                    $ctr = $rowtot = $coltot = 0;
                    $etot = $mtot = $htot = $gtot = $utot = $ktot = 0;
                    while ($fetqry = mysqli_fetch_array($mainqry)) {

                        $examqry = mysqli_query($conn, "SELECT * from `exam` where `code` ='$fetqry[examcode]' ")or die(mysqli_error($conn));
                        $examfet = mysqli_fetch_array($examqry);
                        $examname = $examfet['name'];
                        $medm = $fetqry['medmcode'];

                        $getepack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '1' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                        $getmpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '2' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                        $gethpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '3' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                        $getgpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '4' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                        $getupack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '5' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                        $getkpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from pacsum
                                 where `medmcode` = '6' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));

                        $epack = mysqli_fetch_array($getepack);
                        $mpack = mysqli_fetch_array($getmpack);
                        $hpack = mysqli_fetch_array($gethpack);
                        $gpack = mysqli_fetch_array($getgpack);
                        $upack = mysqli_fetch_array($getupack);
                        $kpack = mysqli_fetch_array($getkpack);

                        if ($epack['pack'] < 5) {
                            $epack['pack'] = 0;
                        }
                        if ($mpack['pack'] < 5) {
                            $mpack['pack'] = 0;
                        }
                        if ($hpack['pack'] < 5) {
                            $hpack['pack'] = 0;
                        }
                        if ($gpack['pack'] < 5) {
                            $gpack['pack'] = 0;
                        }
                        if ($upack['pack'] < 5) {
                            $upack['pack'] = 0;
                        }
                        if ($kpack['pack'] < 5) {
                            $kpack['pack'] = 0;
                        }

                        $rowtot = $epack['pack'] + $mpack['pack'] + $hpack['pack'] + $gpack['pack'] + $upack['pack'] + $kpack['pack'];
                        $ctr++;
                        echo "<tr>";
                        echo "<td class='padd'>$ctr</td>";
                        echo "<td class='padd'>$examname</td>";
                        echo "<td style='text-align:right'class='padd'>$epack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'>$mpack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'>$hpack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'>$gpack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'>$upack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'>$kpack[pack]</td>";
                        echo "<td style='text-align:right'class='padd'><b>$rowtot</b></td>";
                        echo "</tr>";

                        $etot = $etot + $epack['pack'];
                        $mtot = $mtot + $mpack['pack'];
                        $htot = $htot + $hpack['pack'];
                        $gtot = $gtot + $gpack['pack'];
                        $utot = $utot + $upack['pack'];
                        $ktot = $ktot + $kpack['pack'];
                        $coltot = $etot + $mtot + $htot + $gtot + $utot + $ktot;
                    }
                    echo "<tr>";
                    echo "<td style='text-align:right'  class='padd border2'><b></b></td>";
                    echo "<td style='text-align:right'  class='padd border2'><b>Total</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$etot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$mtot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$htot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$gtot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$utot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$ktot</b></td>";
                    echo "<td style='text-align:right' class='padd border2'><b>$coltot</b></td>";
                    echo "</tr>";
                    ?>
                </table>
                <?php include('includes/linksfoot.php'); ?>
                </body>
                </html>
                <?php
            }
            if (isset($_POST["excel"])) {

                $section = $_POST["section"];
                $session = $_POST["session"];
                $exam = $_POST["exam"];

                $sessncond = '';
                $sectcond = '';
                $examcond = '';

                if (!empty($exam)) {
                    $examcond = " AND `examcode` = '$exam' ";
                }

                if (!empty($session)) {
                    $sessncond = " AND `session` = '$session' ";
                }

                if (!empty($section)) {
                    $sectcond = " AND `section` = '$section' ";
                }

                $mainqry = mysqli_query($conn, "SELECT `examcode`,`medmcode`,`session`,`section` from `demdmain` where vouchno != ''
                         $examcond $sectcond $sessncond  group by `examcode`")or die(mysql_error);

                $getno = mysqli_num_rows($mainqry);





                $comp = mysqli_query($conn, "SELECT * FROM $zroot.`company`")or die(mysqli_error($conn));
                $zcomp = mysqli_fetch_assoc($comp);

                $contents .= "Sr.No., English, Marathi, Hindi, Gujrati, Urdu, Kannad, Total,\n";


                $ctr = $rowtot = $coltot = 0;
                $etot = $mtot = $htot = $gtot = $utot = $ktot = 0;
                while ($fetqry = mysqli_fetch_array($mainqry)) {

                    $examqry = mysqli_query($conn, "SELECT * from `exam` where `code` ='$fetqry[examcode]' ")or die(mysqli_error($conn));
                    $examfet = mysqli_fetch_array($examqry);
                    $examname = $examfet[name];
                    $medm = $fetqry[medmcode];

                    $getepack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '1' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                    $getmpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '2' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                    $gethpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '3' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                    $getgpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '5' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                    $getupack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '7' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));
                    $getkpack = mysqli_query($conn, "SELECT SUM(nopack) as pack from `demdmain`
                                 where `medmcode` = '8' and `examcode` = '$fetqry[examcode]'")or die(mysqli_error($conn));

                    $epack = mysqli_fetch_array($getepack);
                    $mpack = mysqli_fetch_array($getmpack);
                    $hpack = mysqli_fetch_array($gethpack);
                    $gpack = mysqli_fetch_array($getgpack);
                    $upack = mysqli_fetch_array($getupack);
                    $kpack = mysqli_fetch_array($getkpack);

                    if ($epack[pack] < 5) {
                        $epack[pack] = 0;
                    }
                    if ($mpack[pack] < 5) {
                        $mpack[pack] = 0;
                    }
                    if ($hpack[pack] < 5) {
                        $hpack[pack] = 0;
                    }
                    if ($gpack[pack] < 5) {
                        $gpack[pack] = 0;
                    }
                    if ($upack[pack] < 5) {
                        $upack[pack] = 0;
                    }
                    if ($kpack[pack] < 5) {
                        $kpack[pack] = 0;
                    }

                    $rowtot = $epack[pack] + $mpack[pack] + $hpack[pack] + $gpack[pack] + $upack[pack] + $kpack[pack];

                    $ctr++;

                    $contents .= $ctr . ",";
                    $contents .= $examname . ",";
                    $contents .= $epack[pack] . ",";
                    $contents .= $mpack[pack] . ",";
                    $contents .= $hpack[pack] . ",";
                    $contents .= $gpack[pack] . ",";
                    $contents .= $upack[pack] . ",";
                    $contents .= $kpack[pack] . ",";
                    $contents .= $rowtot . "\n";


                    $etot = $etot + $epack[pack];
                    $mtot = $mtot + $mpack[pack];
                    $htot = $htot + $hpack[pack];
                    $gtot = $gtot + $gpack[pack];
                    $utot = $utot + $upack[pack];
                    $ktot = $ktot + $kpack[pack];
                    $coltot = $etot + $mtot + $htot + $gtot + $utot + $ktot;
                }

                $contents .= " " . ",";
                $contents .= "Total" . ",";
                $contents .= $etot . ",";
                $contents .= $mtot . ",";
                $contents .= $htot . ",";
                $contents .= $gtot . ",";
                $contents .= $utot . ",";
                $contents .= $ktot . ",";
                $contents .= $coltot . "\n";


                $contents = strip_tags($contents);

                if ($_POST["dtype"] == 1) {
                    header("Content-Disposition: attachment; filename=$formname" . "_Summary" . date('d-m-Y') . ".csv");
                    print $contents;
                } else {
                    header("Content-Disposition: attachment; filename=$formname" . "_Detail" . date('d-m-Y') . ".csv");
                    print $contents;
                }
            }
            ?>           