<?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"];

$paxdb = "visaform";
$bookdb = "bookform";
$formname = "Outstanding Register";

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

$tour = $_POST['tour'];

$name = $_POST['name'];
$regnno = $_POST['regnno'];
$paxno = $_POST['paxno'];
$group = $_POST['group'];
$class = $_POST['class'];
$canc = $_POST['canc'];
$prphoto = $_POST['prphoto'];
$city = $_POST['city'];
$state = $_POST['state'];
$sex = $_POST['sex'];
$ppexpiry = $_POST['ppexpiry'];
$ppinhand = $_POST['ppinhand'];
$depdate = $_POST['depdate'];
$deppnr = $_POST['deppnr'];
$airlines = $_POST['airlines'];
$agent = $_POST['agent'];
$arrvdate = $_POST['arrvdate'];
$arrvpnr = $_POST['arrvpnr'];
$infonly = $_POST['inf'];
$canconly = $_POST['canc'];

$tourcond = '';
$namecond = '';
$regncond = '';
$paxcond = '';
$groupcond = '';
$classcond = '';
$canccond = '';
$prphotocond = '';
$citycond = '';
$statecond = '';
$sexcond = '';
$ppexpirycond = '';
$ppinhandcond = '';
$depdatecond = '';
$deppnrcond = '';
$airlinescond = '';
$agentcond = '';
$arrvdatecond = '';
$arrvpnrcond = '';

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

if (!empty($name)) {
    $namecond = " AND b.`name` like '%$name%' ";
}

if (!empty($regnno)) {
    $regncond = " AND a.`regnno` = '$regnno' ";
}

if (!empty($paxno)) {
    $paxcond = " AND b.`vouchno`= '$paxno' ";
}

if (!empty($group)) {
    $groupcond = " AND a.`groupno`= '$group' ";
}

if (!empty($class)) {
    $classcond = " AND a.`batchno`= '$class' ";
}

if (!empty($city)) {
    $citycond = " AND a.`city`= '$city' ";
}

if (!empty($state)) {
    $statecond = " AND a.`state` = '$state' ";
}

if ($sex == '') {
    
} else {
    $sexcond = "AND left(b.`cmbgender`,1) = trim('$sex') ";
}

//if ($canconly > 0) {
//   $canccond =  " and b.cancdate != '0000-00-00' ";
//} else {
//   $canccond =  " and b.cancdate = '0000-00-00' ";
//}

if ($ppexpiry != 99) {
    $time = date("Y-m-d");

    $mcurrmon = date("Y-m-d", strtotime($time . "+" . $ppexpiry . " month"));

    $ppexpirycond = " and b.expdate != '0000-00-00' and b.expdate >= '$time' and b.expdate < '$mcurrmon' ";
}

$depdate = $_POST['depdate'];
if (!empty($depdate)) {
    $depdatecond = " and a.depdate = '$depdate' ";
}

$arrvdate = $_POST['arrvdate'];
if (!empty($arrvdate)) {
    $arrvdatecond = " and a.arrdate = '$arrvdate' ";
}

$airlines = $_POST['airlines'];
if ($airlines != '') {
    $airlinecond = " and a.craft = '$airlines' ";
}

$agent = $_POST['agent'];
if ($agent != '') {
    $agentcond = " and a.agent like '%$agent%' ";
}

// browse($deppnr);
if ($deppnr == 'P') {
    $deppnrcond = " and a.deppnr = '' ";
}

if ($arrpnr == 'P') {
    $arrpnrcond = " and a.arrpnr = '' ";
}

$string = '0123456789';
//$string = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$string_shuffled = str_shuffle($string);
$pass = substr($string_shuffled, 1, 6);
$mixdata = 'qx' . "$pass";

mysqli_query($conn, "drop temporary table if exists $mixdata ") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table $mixdata 
                            SELECT a.vouchno, 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.full, a.half, a.childwob, a.inf, a.full2, a.half2, a.childwob2, a.inf2, a.childwobno, a.childwobamt, a.airtaxamt,
                                  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.halfno2, a.halfno, a.infno, a.visachg, a.visachgno, a.cancelchg2, a.farediffamt, a.visachgamt,
                                  a.airtax, a.airtaxno, a.st, a.stno, a.farediff, a.farediff2, 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 
                                  $tourcond $namecond $regncond $paxcond $groupcond $classcond $prphotocond $citycond $statecond $sexcond
                                  $ppexpirycond $ppinhandcond $depdatecond $deppnrcond $airlinecond $agentcond $arrvdatecond $arrvpnrcond $canccond 
                                  order by b.vregnno, b.vouchno") or die(mysqli_error($conn));


$getpmain2 = mysqli_query($conn, "SELECT vregnno, vouchno, haji, groupno, agent, agent as monname, count(*) as paxno,  
                                  fname, add1, add2, add3, tel, tel2, city, mobile, mobile2, pin, state, 
                                  sum(full * fullno) as full, sum(half * halfno) as half, sum(childwob * childwobno) as childwob, sum(inf * infno) as inf, 
                                  sum(full2 * fullno) as full2, sum(half2 * halfno) as half2, sum(childwob2 * childwobno) as childwob2, sum(inf2 * infno) as inf2, 
                                  sum(visachgamt) as visachgamt, sum(airtaxamt) as airtaxamt, sum(discamt) as discamt, sum(farediffamt) as farediffamt, 
                                  sum(cancelchgamt) as cancelchgamt, sum(nettot) as nettot, sum(cgstamt) as cgstamt, sum(sgstamt) as sgstamt,
                                  sum(tcsamt) as tcsamt, sum(netamt) as netamt, 
                                  sum(fullno) as fullno, sum(halfno) as halfno, sum(childwobno) as childwobno, sum(infno) as infno, 
                                  cancelchgno, sum(farediff * farediffno) as farediff, sum(farediff2 * farediffno) as farediff2,
                                  sum(disc * discno ) as disc, sum(disc2 * discno) as disc2, 
                                  sum(cancelchg * cancelchgno) as cancelchg, sum(cancelchg2 * cancelchgno) as cancelchg2, sum(cgstamt + sgstamt + tcsamt) as gstamt
                                  FROM $mixdata group by vregnno order by vregnno ") or die(mysqli_error($conn));
//print table($getpmain2);
//die();

$getno2 = mysqli_num_rows($getpmain2);


if ($a == 0) {
    // echo "<script>"
    //     . "window.close()"
    //     . "</script>";
}

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

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }
    </style>
    </head>

    <body>
        <div class="container" style="width:100%">
    <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table style="background: white;width: 100%" class="table table-bordered table-condensed table-hover">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:5%" rowspan="2">Sr.</th>
                                <th style="width:6%" class="text-right">Regn.No.</th>
                                <th style="width:20%" >Name</th>
                                <th style="width:7%" class="text-right">No.of Pax.</th>
                                <th style="width:7%" class="text-right">Cancel Pax</th>
                                <th style="width:7%" class="text-right">Ticket Amt.</th>
                                <th style="width:7%" class="text-right">Paid Amt.</th>
                                <th style="width:7%" class="text-right">Balance Amt.</th>
                                <th style="width:7%" class="text-right">Tour Amt.</th>
                                <th style="width:7%" class="text-right">Paid Amt.</th>
                                <th style="width:7%" class="text-right">Balance Amt.</th>
                                <th style="width:18%" >Agent</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $discamt = 0;
                            $totamt = 0;
                            while ($fmain = mysqli_fetch_array($getpmain2)) {
                                $vregnno = $fmain['vregnno'];

                                $tktamt = $fmain['full'] + $fmain['half'] + $fmain['childwob'] + $fmain['inf'] + $fmain['farediffamt'] + $fmain['cancelchg'] - $fmain['disc'];

                                $touramt = $fmain['full2'] + $fmain['half2'] + $fmain['childwob2'] + $fmain['inf2'] + $fmain['visachgamt'] + $fmain['airtaxamt'] + $fmain['farediffamt2'] + $fmain['cancelchg2'] - $fmain['disc2'] + $fmain['gstamt'];

                                $getcanc = mysqli_query($conn, "SELECT count(*) as cancno from $mixdata where vregnno = '$vregnno' and `cancdate` != '0000-00-00' ") or die(mysqli_error($conn));
                                $fetcanc = mysqli_fetch_array($getcanc);
                                $paxcanc = $fetcanc['cancno'];

                                $gettbank = mysqli_query($conn, "SELECT sum(amount) as amt, payfor from bankrecp where regnno = '$vregnno' and payfor = 'T' ") or die(mysqli_error($conn));
                                $fettbank = mysqli_fetch_array($gettbank);
                                $tbank = $fettbank['amt'];

                                $gettcash = mysqli_query($conn, "SELECT sum(amount) as amt, payfor from cashrecp where regnno = '$vregnno' and payfor = 'T' ") or die(mysqli_error($conn));
                                $fettcash = mysqli_fetch_array($gettcash);
                                $tcash = $fettcash['amt'];

                                $getubank = mysqli_query($conn, "SELECT sum(amount) as amt, payfor from bankrecp where regnno = '$vregnno' and payfor = 'U' ") or die(mysqli_error($conn));
                                $fetubank = mysqli_fetch_array($gettbank);
                                $ubank = $fetubank['amt'];

                                $getucash = mysqli_query($conn, "SELECT sum(amount) as amt, payfor from cashrecp where regnno = '$vregnno' and payfor = 'U' ") or die(mysqli_error($conn));
                                $fetucash = mysqli_fetch_array($getucash);
                                $ucash = $fetucash['amt'];

                                $tktpaid = $tbank + $tcash;
                                $tourpaid = $ubank + $ucash;

                                $tktbal = $tktamt - $tktpaid;
                                $tourbal = $touramt - $tourpaid;

                                if ($tktbal + $tourbal > 0) {
                                    $ctr++;
                                    echo "<tr id='boldfont'>";
                                    echo "<td>$ctr</td>";
                                    echo "<td class='text-right'>$fmain[vregnno]</td>";
                                    echo "<td>$fmain[haji]</td>";
                                    echo "<td class='text-right'>$fmain[paxno]</td>";
                                    if ($paxcanc > 0) {
                                        echo "<td class='text-right' style='color:red'>$paxcanc</td>";
                                    } else {
                                        echo "<td class='text-right'>$paxcanc</td>";
                                    }
                                    echo "<td class='text-right'>$tktamt</td>";
                                    echo "<td class='text-right'>$tktpaid</td>";
                                    echo "<td class='text-right' style='color:blue'>$tktbal</td>";
                                    echo "<td class='text-right'>$touramt</td>";
                                    echo "<td class='text-right'>$tourpaid</td>";
                                    echo "<td class='text-right' style='color:blue'>$tourbal</td>";
                                    echo "<td>$fmain[agent]</td>";
                                    echo "</tr>";

                                    $getpaxnum += $paxno;
                                    $getcancel += $paxcancel;
                                    $ttktamt += $tktamt;
                                    $ttktpaid += $tktpaid;
                                    $ttouramt += $touramt;
                                    $ttourpaid += $tourpaid;
                                }
                            }
                            $tbal = $ttktamt - $ttktpaid;
                            $ubal = $ttouramt - $ttourpaid;
                            $colr = "red";
                            echo "<tr style='color:$colr'>";
                            echo "<td class='text-right' colspan='3'><h4><b>Total</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$getpaxnum</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$getcancel</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$ttktamt</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$ttktpaid</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$tbal</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$ttouramt</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$ttourpaid</b></h4></td>";
                            echo "<td class='text-right'><h4><b>$ubal</b></h4></td>";
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

    <?php include("includes/linksfoot.php");
    ?>
    </body>

    </html>
    <?php
}

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

    $contents .= "Sr.,Tenant,Amount\n";

    $ctr = 0;
    $gtotamt = 0;
    $totamt = 0;

    while ($fmain = mysqli_fetch_array($mainqry)) {

        $monthno = $fmain['monthno'];
        $monname = $fmain['monname'];
        $accode = $fmain['accode'];
        $amount = $fmain['amt'];
        $gettenant = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE accode = '$accode' ") or die(mysqli_error($conn));
        $ftenant = mysqli_fetch_array($gettenant);
        $tenant = $ftenant['name'];

        $ctr++;

        $contents .= "$ctr" . ",";
        $contents .= "$monname" . ",";
        $contents .= "$amount" . "\n";


        $totamt = $totamt + $amount;
    }

    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$totamt" . "\n";
    $contents .= "" . "\n";


    $contents .= "Sr.,Tenant,Amount\n";

    $ctr2 = 0;
    $totamt2 = 0;

    while ($fmain2 = mysqli_fetch_array($mainqry2)) {

        $accode2 = $fmain2['accode'];
        $monname2 = $fmain2['monname'];
        $amount2 = $fmain2['amt'];
        $gettenant2 = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE accode = '$accode2' ") or die(mysqli_error($conn));
        $ftenant2 = mysqli_fetch_array($gettenant2);
        $tenant2 = $ftenant2['name'];

        $ctr2++;

        $contents .= "$ctr2" . ",";
        $contents .= "$monname2" . ",";
        $contents .= "$amount2" . "\n";


        $totamt2 = $totamt2 + $amount2;
    }

    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$totamt2" . "\n";
    $contents .= "" . "\n";

    $gtotamt = $totamt + $totamt2;

    $contents .= "" . ",";
    $contents .= "Grand Total" . ",";
    $contents .= "$gtotamt" . "\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;
    }
}
?>