<?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 = 'classrep2_fr.php';

$formname = 'Class Report';

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

$tourcode = $_SESSION['tourcode'];
$tourname = $_SESSION['tourname'];

$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 = '';
$regnocond = '';
$paxcond = '';
$groupcond = '';
$classcond = '';
$canccond = '';
$prphotocond = '';
$citycond = '';
$statecond = '';
$sexcond = '';
$ppexpirycond = '';
$ppinhandcond = '';
$depdatecond = '';
$deppnrcond = '';
$airlinescond = '';
$agentcond = '';
$arrvdatecond = '';
$arrvpnrcond = '';

$tourcond1 = '';
$namecond1= '';
$regnocond1 = '';
$paxcond1 = '';
$groupcond1 = '';
$classcond1 = '';
$canccond1 = '';
$prphotocond1 = '';
$citycond1 = '';
$statecond1 = '';
$sexcond1 = '';
$ppexpirycond1 = '';
$ppinhandcond1 = '';
$depdatecond1 = '';
$deppnrcond1 = '';
$airlinescond1 = '';
$agentcond1 = '';
$arrvdatecond1 = '';
$arrvpnrcond1 = '';

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

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

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

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

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

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

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

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

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

if ($canconly > 0) {
    $canccond = " and b.cancdate != '0000-00-00' ";
    $canccond1 = " and cancdate != '0000-00-00' ";
} else {
    $canccond = " and b.cancdate = '0000-00-00' ";
    $canccond1 = " and 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' ";
    $ppexpirycond1 = " and expdate != '0000-00-00' and expdate >= '$time' and expdate < '$mcurrmon' ";
}

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

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

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

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

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

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

$getpmain2 = mysqli_query($conn, "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, a.mkroom, a.mdroom, a.othroom, 
                                  b.surname, b.name as paxname, b.suffix, b.midname, b.lastname, b.chkticket, b.chkvisa, b.chkland,
                                  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.panno, b.cmbgender, b.cancdate, b.chkchildwob, 
                                  b.qurname, b.tour FROM `bookform` a, visaform b where a.regnno = b.vregnno 
                                  $tourcond $namecond $regnocond $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));
//print table($getpmain2);
//die();

$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;
        }

        #boldfont {
            font-weight: bold;
        }

    </style>
    </head>

    <body>
        <div class="container-fluid">
            <?php
            include("includes/report_head.php");
            if ($getno2 > 0) {
                ?>
                <table style="background: white;width: 100%" class="table table-condensed table-striped table-hover table-bordered">
                    <thead class="mySearchTr">
                        <tr style='font-size:14px'>
                            <th width="3%;">Sr.</th>
                            <th width="4%;">Regn.</th>
                            <th width="27%;">Name</th>
                            <th width="3%;">Wob</th>
                            <th width="4%;">T V L</th>
        <!--                            <th width="5%;">Pax #</th>-->
                            <th width="5%;">Sex</th>
                            <th width="5%;">Passport</th>
                            <th width="4%;">Craft</th>
                            <th width="16%;">Agent</th>
                            <th width="8%;">Makka</th>
                            <th width="8%;">Madina</th>
                            <th width="7%;">Mobile</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $pctr = 0;
                        $pchtypecond = "";
                        $s1 = $s2 = $s3 = $s4 = $s5 = $s6 = $s0;
                        $m1 = $m2 = $m3 = $m4 = $m5 = $m6 = $m0;
                        $wob = $inf = 0 ;

                        $tregnno = -1;
                        $colflag = 0;
                        $actr = 0;

                        while ($pmain = mysqli_fetch_array($getpmain2)) {
                            $dob = $pmain['dob'];

                            $proceed = 1;
                            if ($infonly == 1) {
                                $curdate = date("Y-m-d");
                                $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));
                                if ($infmon > $curdate) {
                                    $proceed = 1;
                                } else {
                                    $proceed = 0;
                                }
                            }

                            if ($proceed > 0) {
                                if ($pmain['paidby'] == "C") {
                                    $ptyped = "CASH";
                                } else {
                                    $ptyped = "CREDIT";
                                }

                                $voucher   = $pmain['vouchno'];
                                $vregnno   = $pmain['vregnno'];
                                $regnno    = $pmain['regnno'];
                                $paxno     = $pmain['paxno'];
                                $chkchildwob = $pmain['chkchildwob'];

                                $partynm   = trim($pmain['surname']) . ' ' . trim($pmain['lastname']) . ' ' . trim($pmain['midname']);
                                $invno     = $pmain['invno'];
                                $totalqty  = $pmain['totalqty'];
                                $mydt      = $pmain['mydt'];
                                $tel       = $pmain['tel'];
                                $mobile    = $pmain['mobile'];
                                $mkroom    = $pmain['mkroom'];
                                $mdroom    = $pmain['mdroom'];
                                $chkticket = $pmain['chkticket'];
                                $chkvisa   = $pmain['chkvisa'];
                                $chkland   = $pmain['chkland'];

                                $paxcount = 0;
                                $paxqry = mysqli_query($conn, "SELECT vouchno FROM `visaform` WHERE `vregnno` = '$regnno' ") or die(mysqli_error($conn));
                                $paxcount = mysqli_num_rows($paxqry);

                                if ($regnno != $tregnno) {
                                    if ($actr == 1) {
                                        echo "<tr >";
                                        echo "<td colspan='8'></td>";
                                        echo "<td colspan='4'>$pmain[rem1]</td>";
                                        $remprint = 1;
                                        echo "</tr>";
                                    }

                                    if ($actr == 0) {
                                        
                                    } else {
                                        echo "<tr><td colspan='13'><hr style='margin-top:-2px;margin-bottom:-6px;border-top: 2px solid black'></td></tr>";
                                    }
                                    if ($colflag == 0) {
                                        $backcol = 'background-color:#F9F9F9';
                                        $colflag = 1;
                                    } else {
                                        $backcol = 'background-color: rgba(131, 238, 255,0.5)';
                                        $colflag = 0;
                                    }
                                    $tregnno = $regnno;
                                    $actr = 1;
                                } else {
                                    $actr++;
                                }

                                $tvl = '';
                                if ($chkticket > 0) {
                                    $tvl = 'T';
                                }
                                if ($chkvisa > 0) {
                                    $tvl = $tvl . ' V';
                                }
                                if ($chkland > 0) {
                                    $tvl = $tvl . ' L';
                                }

                                $gender = '';
                                if (left($pmain['cmbgender'], 1) == "M") {
                                    $gender = "Male";
                                } else {
                                    $gender = "Female";
                                }

                                if ($chkchildwob == 0) {
                                    $dispwob = '';
                                    
                                    $mkrm = '';
                                    if ($pmain['mksingle'] != 0) {
                                        $mkroom = $pmain['mksingle'];
                                        $mk_rtype = 'SGL';
                                        $mkrm = 'SGL';
                                        
                                        if ($pmain['mk_singlepax'] > 0) {
                                            $s1++ ;
                                        }
                                    }
                                    if ($pmain['mkdouble'] > 0) {
                                        $mkroom = $pmain['mkdouble'];
                                        $mk_rtype = 'DBL';
                                        if ($mkrm != '') {
                                            $mkrm = $mkrm . ' / DBL';
                                        } else {
                                            $mkrm = 'DBL';
                                        }
                                        if ($pmain['mk_doublepax'] > 0) {
                                            $s2++ ;
                                        }
                                    }
                                    
                                    if ($pmain['mktriple'] != 0) {
                                        $mkroom = $pmain['mktriple'];
                                        $mk_rtype = 'TRP';
                                        if ($mkrm != '') {
                                            $mkrm = $mkrm . ' / TRP';
                                        } else {
                                            $mkrm = 'TRP';
                                        }
                                        if ($pmain['mk_triplepax'] > 0) {
                                            $s3++ ;
                                        }
                                    }
                                    
                                    if ($pmain['mkquad'] != 0) {
                                        $mkroom = $pmain['mkquad'];
                                        $mk_rtype = 'QUAD';
                                        if ($mkrm != '') {
                                            $mkrm = $mkrm . ' / QUAD';
                                        } else {
                                            $mkrm = 'QUAD';
                                        }
                                        if ($pmain['mk_quadpax'] > 0) {
                                            $s4++ ;
                                        }
                                    }
                                    if ($pmain['mkquint'] != 0) {
                                        $mkroom = $pmain['mkquint'];
                                        $mk_rtype = 'QUINT';
                                        if ($mkrm != '') {
                                            $mkrm = $mkrm . ' / QUINT';
                                        } else {
                                            $mkrm = 'QUINT';
                                        }
                                        if ($pmain['mk_quintpax'] > 0) {
                                            $s5++ ;
                                        }
                                    }
                                    if ($pmain['mksuite'] != 0) {
                                        $mkroom = $pmain['mksuite'];
                                        $mk_rtype = 'SUITE';
                                        if ($mkrm != '') {
                                            $mkrm = $mkrm . ' / SUITE';
                                        } else {
                                            $mkrm = 'SUITE';
                                        }
                                        if ($pmain['mk_suitepax'] > 0) {
                                            $s6++ ;
                                        }
                                    }

                                    if ($pmain['mknobed'] != 0) {
                                        $mkroom = $pmain['mknobed'];
                                        $mk_rtype = 'NOBED';
                                        $s0++;
                                    }

                                    /////////////// madina ////////////////////////

                                    $mdrm = '';
                                    if ($pmain['mdsingle'] != 0) {
                                        $mdroom = $pmain['mdsingle'];
                                        $md_rtype = 'SGL';
                                        $mdrm = 'SGL';
                                        $m1++;
                                    }

                                    if ($pmain['mddouble'] != 0) {
                                        $mdroom = $pmain['mddouble'];
                                        $md_rtype = 'DBL';
                                        if ($mdrm != '') {
                                            $mdrm = $mdrm . ' / DBL';
                                        } else {
                                            $mdrm = 'DBL';
                                        }
                                        $m2++;
                                    }

                                    if ($pmain['mdtriple'] != 0) {
                                        $mdroom = $pmain['mdtriple'];
                                        $md_rtype = 'TRP';
                                        if ($mdrm != '') {
                                            $mdrm = $mdrm . ' / TRP';
                                        } else {
                                            $mdrm = 'TRP';
                                        }
                                        $m3++;
                                    }
                                    if ($pmain['mdquad'] != 0) {
                                        $mdroom = $pmain['mdquad'];
                                        $md_rtype = 'QUAD';
                                        if ($mdrm != '') {
                                            $mdrm = $mdrm . ' /  QUAD';
                                        } else {
                                            $mdrm = 'QUAD';
                                        }
                                        $m4++;
                                    }

                                    if ($pmain['mdquint'] != 0) {
                                        $mdroom = $pmain['mdquint'];
                                        $md_rtype = 'QUINT';
                                        if ($mdrm != '') {
                                            $mdrm = $mdrm . ' / QUINT';
                                        } else {
                                            $mdrm = 'QUINT';
                                        }
                                        $m5++;
                                    }

                                    if ($pmain['mdsuite'] != 0) {
                                        $mdroom = $pmain['mdsuite'];
                                        $md_rtype = 'SUITE';
                                        if ($mdrm != '') {
                                            $mdrm = $mdrm . ' / SUITE';
                                        } else {
                                            $mdrm = 'SUITE';
                                        }
                                        $m6++;
                                    }

                                    if ($pmain['mdnobed'] != 0) {
                                        $mdroom = $pmain['mdnobed'];
                                        $md_rtype = 'NOBED';
                                        $m0++;
                                    }
                                } else {
                                    $dispwob = 'WOB';
                                    $wob++;
                                }

//////////////////////////////////////////
                                $pctr++;
                                echo "<tr data-href='bookform.php?mode=edit&vouchno=$voucher' class='clickable-row2' style='cursor: pointer; $backcol;' [target]='_blank'>";
                                echo "<td>$pctr</td>";
                                echo "<td>$regnno</td>";

                                if ($pmain['cancdate'] != "0000-00-00") {
                                    echo "<td>$partynm</td>";
                                } else {
                                    if ($paxcount == 1) {
                                        echo "<td><b style='color:blue  !important;'>$partynm</b></td>";
                                    } else {
                                        $curdate = date("Y-m-d");
                                        $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));

                                        if ($infmon > $curdate) {
                                            echo "<td><b style='color:red !important;'>$partynm</b></td>";
                                            $inf++;
                                        } else {
                                            echo "<td>$partynm</td>";
                                        }
                                    }
                                }
                                if ($dispwob != '') {
                                    echo "<td style='color:red'>$dispwob</td>";
                                } else {
                                    echo "<td></td>";
                                }
                                echo "<td>$tvl</td>";
//                                echo "<td>$paxno</td>";
                                echo "<td>$gender</td>";
                                echo "<td>$pmain[redypass]</td>";
                                echo "<td>$pmain[craft]</td>";
                                if ($actr == 1) {
                                    $remprint = 0;
                                    echo "<td>$pmain[agent]</td>";
                                  
                                    echo "<td >$mkrm</td>";
                                    echo "<td >$mdrm</td>";

                                    echo "<td>$tel</td>";
                                    echo "</tr>";
                                } else {
                                    if ($actr > 1) {
                                        if ($remprint == 0) {
                                            echo "<td colspan='3'>$pmain[rem1]</td>";
                                            echo "<td>$mobile</td>";
                                        }
                                        $remprint = 1;
                                    }
                                }
                            }
                        }
                        if ($actr == 1) {
                            echo "<tr >";
                            echo "<td colspan='8'></td>";
                            echo "<td colspan='3'>$pmain[rem1]</td>";
                            echo "<td>$mobile</td>";
                            $remprint = 1;
                            echo "</tr>";
                        }

                        echo "<tr><td colspan='13'><hr style='margin-top:-2px;margin-bottom:-6px;border-top: 2px solid black'></td></tr>";

//                        $stot = $s1 + $s2 + $s3 + $s4 + $s5 + $s6;
//                        $mtot = $m1 + $m2 + $m3 + $m4 + $m5 + $m6;
//                        
                        ?>
                    </tbody>
                </table>
                <table  style='width:60%' class="table table-condensed table-striped table-hover table-bordered summ">
                    <tr style="background-color: #2EBFFE">
                        <td colspan="9" style="font-size: 18px">Total No. Of Pax - <?php echo $getno2 - $inf .  ' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Child - wob - ' . $wob 
                        . ' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Infant - ' . $inf?></td>
                    </tr>    
                    <?php
                        $getpmain3 = mysqli_query($conn, "SELECT sum(mk_singlepax) as mkp1, sum(mk_doublepax) as mkp2, sum(mk_triplepax) as mkp3,
                                                          sum(mk_quadpax) as mkp4, sum(mk_quintpax) as mkp5, sum(mk_suitepax) as mkp6,    
                                                          sum(md_singlepax) as mdp1, sum(md_doublepax) as mdp2, sum(md_triplepax) as mdp3,
                                                          sum(md_quadpax) as mdp4, sum(md_quintpax) as mdp5, sum(md_suitepax) as mdp6 
                                                          FROM `bookform` WHERE regnno != '' 
                                                          $tourcond1 $regnocond1 $groupcond1 $classcond1 $citycond1 $statecond1 
                                                          $depdatecond1 $deppnrcond1 $airlinecond1 $agentcond1 $arrvdatecond1 $arrvpnrcond1 
                                                          and regnno in (select vregnno from visaform where vregnno != ''
                                                          $namecond1 $paxcond1 $sexcond1 $ppexpirycond1 $canccond1 )
                                                          ") or die(mysqli_error($conn));
                        $fetpmain3 = mysqli_fetch_array($getpmain3);
                        $s1 = $fetpmain3['mkp1'];
                        $s2 = $fetpmain3['mkp2'];
                        $s3 = $fetpmain3['mkp3'];
                        $s4 = $fetpmain3['mkp4'];
                        $s5 = $fetpmain3['mkp5'];
                        $s6 = $fetpmain3['mkp6'];

                        $m1 = $fetpmain3['mdp1'];
                        $m2 = $fetpmain3['mdp2'];
                        $m3 = $fetpmain3['mdp3'];
                        $m4 = $fetpmain3['mdp4'];
                        $m5 = $fetpmain3['mdp5'];
                        $m6 = $fetpmain3['mdp6'];

                        
                        $sr1 = $s1;
                        $sr2 = round($s2 / 2, 2);
                        $sr3 = round($s3 / 3, 2);
                        $sr4 = round($s4 / 4, 2);
                        $sr5 = round($s5 / 5, 2);
                        $sr6 = round($s6 / 6, 2);
                        $sr0 = $s0;

                        $mr1 = $m1;
                        $mr2 = round($m2 / 2, 2);
                        $mr3 = round($m3 / 3, 2);
                        $mr4 = round($m4 / 4, 2);
                        $mr5 = round($m5 / 5, 2);
                        $mr6 = round($m6 / 6, 2);
                        $mr0 = $m0;

                        $srtot = $sr1 + $sr2 + $sr3 + $sr4 + $sr5 + $sr6;
                        $mrtot = $mr1 + $mr2 + $mr3 + $mr4 + $mr5 + $mr6;
                        
                    ?>
                    <tr style="background-color: #2EBFFE; font-size: 12px; align-items:center">
                        <th width="5%;" class="text-center"></th>
                        <th width="3%;" class="text-center">SINGLE</th>
                        <th width="3%;" class="text-center">DOUBLE</th>
                        <th width="3%;" class="text-center">TRIPLE</th>
                        <th width="3%;" class="text-center">QUAD</th>
                        <th width="3%;" class="text-center">QUINT</th>
                        <th width="3%;" class="text-center">SUITE</th>
                        <th width="7%;" class="text-center">NO BED</th>
                        <th width="4%;" class="text-center">TOTAL</th>
                    </tr>
                    <tr style="background-color: #F7F3B5 ; color:black !important;">
                        <td class="text-center">MAKKAH</td>
                        <td class="text-center"><?= $sr1 ?></td>
                        <td class="text-center"><?= $sr2 ?></td>
                        <td class="text-center"><?= $sr3 ?></td>
                        <td class="text-center"><?= $sr4 ?></td>
                        <td class="text-center"><?= $sr5 ?></td>
                        <td class="text-center"><?= $sr6 ?></td>
                        <td class="text-center"><?= $sr0 ?></td>
                        <td class="text-center"><?= $srtot ?></td>
                    </tr>
                    <tr style="background-color: lightgreen ; color:black !important;">
                        <td class="text-center">MADINA</td>
                        <td class="text-center"><?= $mr1 ?></td>
                        <td class="text-center"><?= $mr2 ?></td>
                        <td class="text-center"><?= $mr3 ?></td>
                        <td class="text-center"><?= $mr4 ?></td>
                        <td class="text-center"><?= $mr5 ?></td>
                        <td class="text-center"><?= $mr6 ?></td>
                        <td class="text-center"><?= $mr0 ?></td>
                        <td class="text-center"><?= $mrtot ?></td>
                    </tr>
                </table>
                <?php
            }
            ?>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}


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

    if ($getno2 > 0) {
$contents .="Sr., Regn., Name, Wob, T V L, Sex, Passport, Craft, Agent, Makka, Madina, Mobile\n";

$pctr = 0;
$pchtypecond = "";
$s1 = $s2 = $s3 = $s4 = $s5 = $s6 = $s0;
$m1 = $m2 = $m3 = $m4 = $m5 = $m6 = $m0;
$wob = $inf = 0 ;

$tregnno = -1;
$colflag = 0;
$actr = 0;

while ($pmain = mysqli_fetch_array($getpmain2)) {
    $dob = $pmain['dob'];

    $proceed = 1;
    if ($infonly == 1) {
        $curdate = date("Y-m-d");
        $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));
        if ($infmon > $curdate) {
            $proceed = 1;
        } else {
            $proceed = 0;
        }
    }

    if ($proceed > 0) {
        if ($pmain['paidby'] == "C") {
            $ptyped = "CASH";
        } else {
            $ptyped = "CREDIT";
        }

        $voucher = $pmain['vouchno'];
        $vregnno = $pmain['vregnno'];
        $regnno = $pmain['regnno'];
        $paxno = $pmain['paxno'];
        $chkchildwob = $pmain['chkchildwob'];

        $partynm = trim($pmain['surname']) . ' ' . trim($pmain['lastname']) . ' ' . trim($pmain['midname']);
        $invno = $pmain['invno'];
        $totalqty = $pmain['totalqty'];
        $mydt = $pmain['mydt'];
        $tel = $pmain['tel'];
        $mobile = $pmain['mobile'];
        $mkroom = $pmain['mkroom'];
        $mdroom = $pmain['mdroom'];
        $chkticket = $pmain['chkticket'];
        $chkvisa = $pmain['chkvisa'];
        $chkland = $pmain['chkland'];

        $paxcount = 0;
        $paxqry = mysqli_query($conn, "SELECT vouchno FROM `visaform` WHERE `vregnno` = '$regnno' ") or die(mysqli_error($conn));
        $paxcount = mysqli_num_rows($paxqry);

        if ($regnno != $tregnno) {
            if ($actr == 1) {
                $contents .="" . ",";
                $contents .="$pmain[rem1]" . "\n";
                $remprint = 1;
            }

            if ($actr == 0) {
                
            } else {
                $contents .="" . "\n";
            }
            if ($colflag == 0) {
                $backcol = 'background-color:#F9F9F9';
                $colflag = 1;
            } else {
                $backcol = 'background-color: rgba(131, 238, 255,0.5)';
                $colflag = 0;
            }
            $tregnno = $regnno;
            $actr = 1;
        } else {
            $actr++;
        }

        $tvl = '';
        if ($chkticket > 0) {
            $tvl = 'T';
        }
        if ($chkvisa > 0) {
            $tvl = $tvl . ' V';
        }
        if ($chkland > 0) {
            $tvl = $tvl . ' L';
        }

        $gender = '';
        if (left($pmain['cmbgender'], 1) == "M") {
            $gender = "Male";
        } else {
            $gender = "Female";
        }

        if ($chkchildwob == 0) {
            $dispwob = '';
            
            $mkrm = '';
            if ($pmain['mksingle'] != 0) {
                $mkroom = $pmain['mksingle'];
                $mk_rtype = 'SGL';
                $mkrm = 'SGL';
                
                if ($pmain['mk_singlepax'] > 0) {
                    $s1++ ;
                }
            }
            if ($pmain['mkdouble'] > 0) {
                $mkroom = $pmain['mkdouble'];
                $mk_rtype = 'DBL';
                if ($mkrm != '') {
                    $mkrm = $mkrm . ' / DBL';
                } else {
                    $mkrm = 'DBL';
                }
                if ($pmain['mk_doublepax'] > 0) {
                    $s2++ ;
                }
            }
            
            if ($pmain['mktriple'] != 0) {
                $mkroom = $pmain['mktriple'];
                $mk_rtype = 'TRP';
                if ($mkrm != '') {
                    $mkrm = $mkrm . ' / TRP';
                } else {
                    $mkrm = 'TRP';
                }
                if ($pmain['mk_triplepax'] > 0) {
                    $s3++ ;
                }
            }
            
            if ($pmain['mkquad'] != 0) {
                $mkroom = $pmain['mkquad'];
                $mk_rtype = 'QUAD';
                if ($mkrm != '') {
                    $mkrm = $mkrm . ' / QUAD';
                } else {
                    $mkrm = 'QUAD';
                }
                if ($pmain['mk_quadpax'] > 0) {
                    $s4++ ;
                }
            }
            if ($pmain['mkquint'] != 0) {
                $mkroom = $pmain['mkquint'];
                $mk_rtype = 'QUINT';
                if ($mkrm != '') {
                    $mkrm = $mkrm . ' / QUINT';
                } else {
                    $mkrm = 'QUINT';
                }
                if ($pmain['mk_quintpax'] > 0) {
                    $s5++ ;
                }
            }
            if ($pmain['mksuite'] != 0) {
                $mkroom = $pmain['mksuite'];
                $mk_rtype = 'SUITE';
                if ($mkrm != '') {
                    $mkrm = $mkrm . ' / SUITE';
                } else {
                    $mkrm = 'SUITE';
                }
                if ($pmain['mk_suitepax'] > 0) {
                    $s6++ ;
                }
            }

            if ($pmain['mknobed'] != 0) {
                $mkroom = $pmain['mknobed'];
                $mk_rtype = 'NOBED';
                $s0++;
            }

            /////////////// madina

            $mdrm = '';
            if ($pmain['mdsingle'] != 0) {
                $mdroom = $pmain['mdsingle'];
                $md_rtype = 'SGL';
                $mdrm = 'SGL';
                $m1++;
            }

            if ($pmain['mddouble'] != 0) {
                $mdroom = $pmain['mddouble'];
                $md_rtype = 'DBL';
                if ($mdrm != '') {
                    $mdrm = $mdrm . ' / DBL';
                } else {
                    $mdrm = 'DBL';
                }
                $m2++;
            }

            if ($pmain['mdtriple'] != 0) {
                $mdroom = $pmain['mdtriple'];
                $md_rtype = 'TRP';
                if ($mdrm != '') {
                    $mdrm = $mdrm . ' / TRP';
                } else {
                    $mdrm = 'TRP';
                }
                $m3++;
            }
            if ($pmain['mdquad'] != 0) {
                $mdroom = $pmain['mdquad'];
                $md_rtype = 'QUAD';
                if ($mdrm != '') {
                    $mdrm = $mdrm . ' /  QUAD';
                } else {
                    $mdrm = 'QUAD';
                }
                $m4++;
            }

            if ($pmain['mdquint'] != 0) {
                $mdroom = $pmain['mdquint'];
                $md_rtype = 'QUINT';
                if ($mdrm != '') {
                    $mdrm = $mdrm . ' / QUINT';
                } else {
                    $mdrm = 'QUINT';
                }
                $m5++;
            }

            if ($pmain['mdsuite'] != 0) {
                $mdroom = $pmain['mdsuite'];
                $md_rtype = 'SUITE';
                if ($mdrm != '') {
                    $mdrm = $mdrm . ' / SUITE';
                } else {
                    $mdrm = 'SUITE';
                }
                $m6++;
            }

            if ($pmain['mdnobed'] != 0) {
                $mdroom = $pmain['mdnobed'];
                $md_rtype = 'NOBED';
                $m0++;
            }
        } else {
            $dispwob = 'WOB';
            $wob++;
        }

        $pctr++;
        $contents .="$pctr" . ",";
        $contents .="$regnno" . ",";

        if ($pmain['cancdate'] != "0000-00-00") {
        $contents .="$partynm" . ",";
        } else {
            if ($paxcount == 1) {
        $contents .="$partynm" . ",";
            } else {
                $curdate = date("Y-m-d");
                $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));

                if ($infmon > $curdate) {
        $contents .="$partynm" . ",";
                    $inf++;
                } else {
        $contents .="$partynm" . ",";
                }
            }
        }
        if ($dispwob != '') {
        $contents .="$dispwob" . ",";
        } else {
        $contents .="" . ",";
        }
        $contents .="$tvl" . ",";
        $contents .="$gender" . ",";
        $contents .="$pmain[redypass]" . ",";
        if ($actr == 1) {
            $remprint = 0;
            $contents .="$pmain[craft]" . ",";
            $contents .="$pmain[agent]" . ",";
            $contents .="$mkrm" . ",";
            $contents .="$mdrm" . ",";
            $contents .="$tel" . "\n";
        } else {
            
            $rem = $pmain['rem1'];
            $rem1 = str_replace(","," ",$rem);
            
            if ($actr > 1) {
                if ($remprint == 0) {
                    $contents .="$pmain[craft]" . ",";
                    $contents .="$rem1" . ",,,";
                    $contents .="$mobile" . "\n";
                }
                $remprint = 1;
            }
        }
        $contents .="" . "\n";
    }
}
if ($actr == 1) {
    $contents .="" . ",";
    $contents .="$rem1" . ",";
    $contents .="$mobile" . "\n";
    $remprint = 1;
}

$contents .="" . "\n";


//                        $stot = $s1 + $s2 + $s3 + $s4 + $s5 + $s6;
//                        $mtot = $m1 + $m2 + $m3 + $m4 + $m5 + $m6;
//                        
$contents .="Total No. Of Pax" . "\n";
                $getpmain3 = mysqli_query($conn, "SELECT sum(mk_singlepax) as mkp1, sum(mk_doublepax) as mkp2, sum(mk_triplepax) as mkp3,
                                                  sum(mk_quadpax) as mkp4, sum(mk_quintpax) as mkp5, sum(mk_suitepax) as mkp6,    
                                                  sum(md_singlepax) as mdp1, sum(md_doublepax) as mdp2, sum(md_triplepax) as mdp3,
                                                  sum(md_quadpax) as mdp4, sum(md_quintpax) as mdp5, sum(md_suitepax) as mdp6 
                                                  FROM `bookform` WHERE regnno != '' 
                                                  $tourcond1 $regnocond1 $groupcond1 $classcond1 $citycond1 $statecond1 
                                                  $depdatecond1 $deppnrcond1 $airlinecond1 $agentcond1 $arrvdatecond1 $arrvpnrcond1 
                                                  and regnno in (select vregnno from visaform where vregnno != ''
                                                  $namecond1 $paxcond1 $sexcond1 $ppexpirycond1 $canccond1 )
                                                  ") or die(mysqli_error($conn));
                $fetpmain3 = mysqli_fetch_array($getpmain3);
                $s1 = $fetpmain3['mkp1'];
                $s2 = $fetpmain3['mkp2'];
                $s3 = $fetpmain3['mkp3'];
                $s4 = $fetpmain3['mkp4'];
                $s5 = $fetpmain3['mkp5'];
                $s6 = $fetpmain3['mkp6'];

                $m1 = $fetpmain3['mdp1'];
                $m2 = $fetpmain3['mdp2'];
                $m3 = $fetpmain3['mdp3'];
                $m4 = $fetpmain3['mdp4'];
                $m5 = $fetpmain3['mdp5'];
                $m6 = $fetpmain3['mdp6'];

                
                $sr1 = $s1;
                $sr2 = round($s2 / 2, 2);
                $sr3 = round($s3 / 3, 2);
                $sr4 = round($s4 / 4, 2);
                $sr5 = round($s5 / 5, 2);
                $sr6 = round($s6 / 6, 2);
                $sr0 = $s0;

                $mr1 = $m1;
                $mr2 = round($m2 / 2, 2);
                $mr3 = round($m3 / 3, 2);
                $mr4 = round($m4 / 4, 2);
                $mr5 = round($m5 / 5, 2);
                $mr6 = round($m6 / 6, 2);
                $mr0 = $m0;

                $srtot = $sr1 + $sr2 + $sr3 + $sr4 + $sr5 + $sr6;
                $mrtot = $mr1 + $mr2 + $mr3 + $mr4 + $mr5 + $mr6;
                
            $contents .=" , SINGLE, DOUBLE, TRIPLE, QUAD, QUINT, SUITE, NO BED, TOTAL\n";
               
                $contents .="MAKKAH" . ",";
                $contents .="$sr1" . ",";
                $contents .="$sr2" . ",";
                $contents .="$sr3" . ",";
                $contents .="$sr4" . ",";
                $contents .="$sr5" . ",";
                $contents .="$sr6" . ",";
                $contents .="$sr0" . ",";
                $contents .="$srtot" . "\n";
                
                $contents .="MADINA" . ",";
                $contents .="$mr1" . ",";
                $contents .="$mr2" . ",";
                $contents .="$mr3" . ",";
                $contents .="$mr4" . ",";
                $contents .="$mr5" . ",";
                $contents .="$mr6" . ",";
                $contents .="$mr0" . ",";
                $contents .="$mrtot" . "\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;
    }
}

if (isset($_POST["genview"])) {
    $j = 0;
    $i = 0;
    browse($getno2);
    while ($pmain = mysqli_fetch_array($getpmain2)) {
        $dob = $pmain['dob'];
        $depdate = $pmain['depdate'];
        $paxno = $pmain['paxno'];
        browse($paxno);

        IF ($dob != "0000-00-00") {
            $newdate = date('Y-m-d', strtotime($dob . ' + 730 days'));
            $date1 = $newdate;
            $date2 = $depdate;

            $diff = abs(strtotime($date2) - strtotime($date1));

            $years = floor($diff / (365 * 60 * 60 * 24));
            $months = floor(($diff - $years * 365 * 60 * 60 * 24) / (30 * 60 * 60 * 24));
            $days = floor(($diff - $years * 365 * 60 * 60 * 24 - $months * 30 * 60 * 60 * 24) / (60 * 60 * 24));

            $j = $days;

            if ($j > 0 || $dob == "0000-00-00") {
                $i = $i + 1;
            }
        } ELSE {
            $i = $i + 1;
        }

        mysqli_query($conn, "update visaform set srno = '$i' where vouchno = '$paxno' ") or die(mysqli_error($conn));
    }

    browse("Processing Complete");
}
?>