<?php
ob_start();
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);
$zroot = $_SESSION["zdbroot"];

$file = 'qurbani_fr.php';

$formname = 'Qurbani List';

$saleexec = $_SESSION["saleexec"];
$usertype = $_SESSION["usertype"];
$username = strtoupper($_SESSION["username_" . $dbname]);

$tourcode = $_SESSION['tourcode'];
$tourname = $_SESSION['tourname'];
//$tour = $_POST["tour"];

$tourcond = '';
$namecond = '';
$regncond = '';
$paxcond = '';
$classcond = '';
$canccond = '';
$agentcond = '';

$frmregnno = $_POST['frmregnno'];
$toregnno = $_POST['toregnno'];

if (empty($frmregnno)) {
    $frmregnno = 1;
}
if (empty($toregnno)) {
    $toregnno = 99999999;
}

$frmpaxno = $_POST['frmpaxno'];
$topaxno = $_POST['topaxno'];


if (empty($frmpaxno)) {
    $frmpaxno = 1;
}
if (empty($topaxno)) {
    $topaxno = 99999999;
}

if ($frmregnno > $toregnno) {
    browse('To Regn. No. Cannot Be Greater Than From Regn. No.');
}

if ($frmpaxno > $topaxno) {
    browse('To Pax No. Cannot Be Greater Than From Pax No.');
}

$regncond = '';
$regncond = " and a.regnno >= '$frmregnno' and a.regnno <= '$toregnno' ";
$tit1 = ' From Regn. No. : ' . $frmregnno . ' To Regn. No. : ' . $toregnno;

$paxcond = '';
$paxcond = " and b.vouchno >= '$frmpaxno' and b.vouchno <= '$topaxno' ";
$tit2 = ' From Pax No. : ' . $frmpaxno . ' To Pax No. : ' . $topaxno;

$name = $_POST['name'];
$class = $_POST['class'];

if (!empty($tourcode)) {
    $tourcond = " AND a.`tourno` = '$tourcode' ";
}

$classcond = '';
if (!empty($_POST['class'])) {
    foreach ($_POST['class'] as $check2) {
        $mycheck2[$count2++] .= "OR a.`batchno` = '" . $check2 . "' ";
    }

    foreach ($mycheck2 as $g => $values2) {
        $mcheck2 .= $values2;
    }

    $classcond = " AND (a.`batchno` = '' " . $mcheck2 . " )";
}


$scope = $_POST['scope'];
$sorton = $_POST['sorton'];

$scond = "";
switch ($scope) {
    case "1":
        switch ($sorton) {
            case "A":
                break;
            case "S":
                $scond = ' and (haj_q = 1 or dum_q = 1 or oth_q = 1 or haj_b = 1 or dum_b = 1 or oth_b = 1)';
                break;
            case "H":
                $scond = ' and haj_q = 1 or haj_b = 1 ';
                break;
            case "D":
                $scond = ' and dum_q = 1 or dum_b = 1 ';
                break;
            case "O":
                $scond = ' and oth_q = 1 or oth_b = 1 ';
                break;
        }
        break;
    case "B":
        switch ($sorton) {
            case "A":
                break;
            case "S":
                $scond = ' and (haj_b = 1 or dum_b = 1 or oth_b = 1)';
                break;
            case "H":
                $scond = ' and haj_b = 1 ';
                break;
            case "D":
                $scond = ' and dum_b = 1  ';
                break;
            case "O":
                $scond = ' and oth_b = 1 ';
                break;
        }
        break;
    case "S":
        switch ($sorton) {
            case "A":
                break;
            case "S":
                $scond = ' and (haj_q = 1 or dum_q = 1 or oth_q = 1)';
                break;
            case "H":
                $scond = ' and haj_q = 1 ';
                break;
            case "D":
                $scond = ' and dum_q = 1 ';
                break;
            case "O":
                $scond = ' and oth_q = 1 ';
                break;
        }
        break;
    default:
        break;
}

//$agent = $_POST['agent'];
//if ($agent != '') {
//    $agentcond = " and a.agent like '%$agent%' ";
//}
//$string = '0123456789';
////$string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
//$string_shuffled = str_shuffle($string);
//$qxno = substr($string_shuffled, 1, 6);
//$mtmp = '$qx' . $qxno ;

$canccond = " and b.cancdate = '0000-00-00' ";
$canccond1 = " and cancdate = '0000-00-00' ";
$cancconda = " and a.cancdate = '0000-00-00' ";

mysqli_query($conn, "drop temporary table if exists tmpdbf") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table tmpdbf
                     SELECT a.vouchno, a.regnno, a.vouchdt, DATE_FORMAT(a.`vouchdt`,'%d-%m-%Y') as mydt,  a.haji, a.groupno, a.agent, a.agent as monname,
                                  a.fname, a.add1, a.add2, a.add3, a.tel, a.tel2, a.city, a.mobile, a.mobile2, a.pin, a.state, a.tel as infant, a.tcs as monno,
                                  a.fullamt, a.halfamt, a.infamt, a.atamt, a.stamt, a.fareamt, a.cancelchgamt, a.amount, a.discamt, a.tcs as ftnt, 
                                  a.fullno, a.half2, a.half, a.halfno2, a.halfno, a.inf, a.infno, a.visachg, a.visachgno, a.cancelchg2, a.farediffamt,
                                  a.airtax, a.airtaxno, a.st, a.stno, a.farediff, a.farediffno, a.tcsamt, a.tcs, a.bookamt, a.bookamtno,
                                  a.cancelchg, a.cancelchgno, a.disc2, a.disc, a.discno, a.netamt, a.batchno, a.busno, a.craft, a.sector, a.depdate,
                                  a.deptime, a.deppnr, a.depflt, a.arrdate, a.arrtime, a.arrpnr, a.arrflt, a.tourno, a.rem1, a.rem2, a.rem3,
                                  a.discrem, a.email, a.halfpay, a.fullpay, a.cgstno, a.cgstamt, a.sgstno, a.sgstamt, a.nettot, a.agentcomm,
                                  a.mksingle, a.mkdouble, a.mktriple, a.mkquad, a.mkquint, a.mksuite, a.mdsingle, a.mddouble, a.mdtriple,
                                  a.mdquad, a.mdquint, a.mdsuite, b.vouchno as paxno, b.surname, b.name as paxname, b.suffix, b.midname, b.lastname, 
                                  b.vregnno, b.fhname, b.mehram, b.relation, b.caste, b.nation, b.accomp1, b.accomp2, b.comefrom, b.period, b.addindia, b.airline, 
                                  b.draftno, b.dob, b.plbirth, b.mothname, b.spname, b.prof, b.qual, b.redypass, b.issdate, b.expdate, b.issplace, 
                                  b.visano, b.visadoi, b.visapoi, b.applno, b.appldate, b.applplace, b.passoffice, b.photo, b.tourtype, b.roomno, 
                                  b.policenqno, b.enqdate, b.nominee, b.nrelation, b.nadd1, b.nadd2, b.travsince, b.nopeople, b.fpeople, b.cpeople, 
                                  b.at1, b.at1from, b.at1to, b.at2, b.at2from, b.at2to, b.at3, b.at3from, b.at3to, b.visaplace, b.visadtd, b.sex, b.tkttax, 
                                  b.tktdate, b.mofano, b.bankname, b.appno, b.submdate, b.username, b.airsrno, b.haj_q, b.dum_q, b.oth_q, 
                                  b.haj_b, b.dum_b, b.oth_b,
                                  b.bran, b.branregno, b.branvouch, b.noupd, b.tourdate, b.finyear, b.vbatch, b.tktamt, b.touramt, b.community, 
                                  b.barcode, b.manual, b.mkroom, b.mdroom, b.othroom, b.panno, b.cmbgender, b.cancdate, 
                                  b.qurname, b.tour FROM `bookform` a, visaform b where a.regnno = b.vregnno and a.tourno = b.tour
                                  $tourcond $regncond $paxcond $classcond $scond $agentcond $canccond $cancconda
                                  order by b.vregnno, a.batchno") or die(mysqli_error($conn));

$getpmain2 = mysqli_query($conn, "select * from tmpdbf order by vregnno, paxno") or die(mysqli_error($conn));



$getno2 = mysqli_num_rows($getpmain2);

if (isset($_POST["view"]) || ($_GET["view"])) {
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #fontstyle {
            font-family: sanserif;
        }

        #boldfont {
            font-weight: bold;
        }

        table td {
            font-size: 12px;
        }

        table th {
            font-size: 18px;
        }
    </style>
    </head>

    <body>
        <div class="container-fluid">
            <?php
            include("includes/head_rep.php");
            if ($getno2 > 0) {
                ?>
                <table style="background: white;width: 80%"
                    class="table table-condensed table-striped table-hover table-bordered">
                    <thead class="mySearchTr">
                        <tr>
                            <th width="4%;">Sr.</th>
                            <th width="5%;">Regn.No.</th>
                            <th width="5%;">Pax No.</th>
                            <th width="40%;">Qurbani Name</th>
                            <th width="10%;">Class</th>
                            <th width="5%;">Haj . Big</th>
                            <th width="5%;">Haj Small</th>
                            <th width="5%;">Dum Big</th>
                            <th width="5%;">Dum Small</th>
                            <th width="5%;">Other Big</th>
                            <th width="5%;">Other Small</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $pctr = 0;
                        $hb = $db = $ob = $hq = $dq = $oq = 0;

                        while ($pm = mysqli_fetch_array($getpmain2)) {

                            $pctr++;
                            echo "<tr class=fontstyle>";
                            echo "<td>$pctr</td>";
                            echo "<td>$pm[vregnno]</td>";
                            echo "<td>$pm[paxno]</td>";
                            echo "<td>$pm[qurname]</td>";
                            echo "<td>$pm[batchno]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[haj_b]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[haj_q]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[dum_b]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[dum_q]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[oth_b]</td>";
                            echo "<td style='font-size:14px;text-align: center'>$pm[oth_q]</td>";
                            echo "</tr>";

                            if ($pm['haj_b'] == 1) {
                                $hb++;
                            }
                            if ($pm['dum_b'] == 1) {
                                $db++;
                            }
                            if ($pm['oth_b'] == 1) {
                                $ob++;
                            }
                            if ($pm['haj_q'] == 1) {
                                $hq++;
                            }
                            if ($pm['dum_q'] == 1) {
                                $dq++;
                            }
                            if ($pm['oth_q'] == 1) {
                                $oq++;
                            }

                            $totpax = $totpax + 1;
                        }
                        echo "<tr style='background-color: #2FBFFE; font-size:25px'>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td style='color:white;font-size:18px'>Total</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$hb</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$hq</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$db</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$dq</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$ob</td>";
                        echo "<td style='color:white;font-size:18px;text-align: center'>$oq</td>";
                        echo "</tr>";
                        ?>
                    </tbody>
                </table>
                <?php
            }
            ?>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>

    </html>
    <?php
}


if (isset($_POST["excel"]) || ($_GET["excel"])) {
    if ($getno2 > 0) {

        $contents .= "Sr., Regn No., Pax No., Qurbani Name, Class, Haj Big, Haj Small, Dum Big, Dum Small, Other Big, Other Small \n";

        $pctr = 0;
        $hb = $db = $ob = $hq = $dq = $oq = 0;

        while ($pm = mysqli_fetch_array($getpmain2)) {

            $pctr++;

            $contents .= "$pctr" . ",";
            $contents .= "$pm[vregnno]" . ",";
            $contents .= "$pm[paxno]" . ",";
            $contents .= "$pm[qurname]" . ",";
            $contents .= "$pm[batchno]" . ",";
            $contents .= "$pm[haj_b]" . ",";
            $contents .= "$pm[haj_q]" . ",";
            $contents .= "$pm[dum_b]" . ",";
            $contents .= "$pm[dum_q]" . ",";
            $contents .= "$pm[oth_b]" . ",";
            $contents .= "$pm[oth_q]" . "\n";

            if ($pm['haj_b'] == 1) {
                $hb++;
            }
            if ($pm['dum_b'] == 1) {
                $db++;
            }
            if ($pm['oth_b'] == 1) {
                $ob++;
            }
            if ($pm['haj_q'] == 1) {
                $hq++;
            }
            if ($pm['dum_q'] == 1) {
                $dq++;
            }
            if ($pm['oth_q'] == 1) {
                $oq++;
            }

            $totpax = $totpax + 1;
        }

        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "" . ",";
        $contents .= "Total" . ",";
        $contents .= "$hb" . ",";
        $contents .= "$hq" . ",";
        $contents .= "$db" . ",";
        $contents .= "$dq" . ",";
        $contents .= "$ob" . ",";
        $contents .= "$oq" . "\n";
    }

    $contents = strip_tags($contents);

    if ($_POST["dtype"] == 1 || $_POST["dtype"] == 3) {
        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;
    }
}
?>