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

$formname = 'Main Detail Report';

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

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


$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'];
$locflsec = $_POST['locflsec'];

if ($locflsec == 1){
    $locflseccond = "AND left(a.`sector`,3) = '$complocflsec' ";
}elseif($locflsec == 2){
    $locflseccond = "AND left(a.`sector`,3) != '$complocflsec' ";
}else{
    $locflseccond = "";
}

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

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

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($_POST['class'])) {
    foreach ($_POST['class'] as $cucheck2) {
        $ccheck2[$ccount2++] .= "OR a.`batchno` = '" . $cucheck2 . "' ";
//        $ccheckoth[$ccountoth++] .= "OR `batchno` = '" . $cucheck2 . "' ";
        $classprn = $classprn . $cucheck2 . "  /   ";
    }

    foreach ($ccheck2 as $g => $cusvalues2) {
        $cuscheck2 .= $cusvalues2;
    }
//    foreach ($ccheckoth as $g => $valuesoth) {
//        $cuscheckoth .= $valuesoth;
//    }

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

    $classprn = left($classprn, -4);
}

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 = '' ";
}

$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, 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 $namecond $regncond $paxcond $groupcond $classcond $prphotocond $citycond $statecond $sexcond
                                  $ppexpirycond $ppinhandcond $depdatecond $deppnrcond $airlinecond $agentcond $arrvdatecond $arrvpnrcond $canccond $locflseccond
                                  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 ">
                    <thead class="mySearchTr">
                        <tr>
                            <th width="4%;">Pax #</th>
                            <th width="3%;">Date</th>
                            <th width="20%;">Name</th>
                            <th width="8%;">Caste</th>
                            <th width="10%;">Nationality</th>
                            <th width="8%;">P.O.B</th>
                            <th width="6%;">D.O.B</th>
                            <th width="6%;">Gender</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $pctr = 0;
                        $pchtypecond = "";

                        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'];

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

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

                                $k = 0;

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

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

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

                                        $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));
                                        //                                     browse($infmon);

                                        if ($infmon > $curdate) {
                                            echo "<td><b style='color:red'>$partynm</b></td>";
                                        } else {
                                            echo "<td>$partynm</td>";
                                        }
                                    }
                                }
                                echo "<td>$pmain[caste]</td>";
                                echo "<td>$pmain[nation]</td>";
                                echo "<td>$pmain[plbirth]</td>";                                
                                $skdob = date('d-m-Y', strtotime($pmain["dob"]));
                                echo "<td>$skdob</td>";
                                echo "<td>$gender</td>";
                                echo "</tr>";

                                echo "<tr>";
                                echo "<td><b style='font-weight:900;color:#0080ff'>Address</b></td>";
                                echo "<td colspan='2'>$pmain[add1]</td>";
                                echo "<td colspan='2' style='border: 1px solid black;text-align:center;font-weight:900;color:#0080ff'>Passport Details</td>";
                                echo "<td colspan='3' style='border: 1px solid black;text-align:center;font-weight:900;color:#0080ff'>Airlines Details</td>";
                                echo "</tr>";


                                echo "<tr>";
                                echo "<td></td>";
                                echo "<td colspan='2'>$pmain[add2]</td>";
                                echo "<td colspan='2' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Passport No.</b>
                                             $pmain[redypass]
                                             </td>";
                                echo "<td colspan='3' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Airlines </b> &nbsp;&nbsp; $pmain[craft]    
                                             <b style='font-weight:900;margin-left:140px'>Mofa No. </b> &nbsp;&nbsp; $pmain[mofano]
                                             </td>";
                                echo "</tr>";


                                $issuedt = date('d-m-Y', strtotime($pmain["issdate"]));
                                $depdate = date('d-m-Y', strtotime($pmain["depdate"]));
                                echo "<tr>";
                                echo "<td></td>";
                                echo "<td colspan='2'>$pmain[add3]</td>";
                                echo "<td colspan='2' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Issue Date</b> $issuedt
                                             </td>";
                                echo "<td colspan='3' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Departure Date </b> $depdate
                                             <b style='font-weight:900;margin-left:140px'>Class </b> $pmain[batchno]
                                             </td>";
                                echo "</tr>";


                                $expirydt = date('d-m-Y', strtotime($pmain["expdate"]));
                                $arrivaldt = date('d-m-Y', strtotime($pmain["arrdate"]));
                                echo "<tr>";
                                echo "<td></td>";
                                echo "<td colspan='2'>$pmain[city]  <b style='margin-left:100px;font-weight:900;'>Pin - </b>$pmain[pin]</td>";
                                echo "<td colspan='2' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Expiry Date</b> $expirydt
                                             </td>";
                                echo "<td colspan='3' style='border-left: 1px solid black;border-right: 1px solid black' >
                                             <b style='font-weight:900;'>Arrival Date </b> &nbsp;&nbsp; $arrivaldt
                                             <b style='font-weight:900;margin-left:140px'>Group </b> $pmain[groupno]

                                             </td>";
                                echo "</tr>";

                                echo "<tr>";
                                echo "<td></td>";
                                echo "<td colspan='2'>$pmain[state]</td>";
                                echo "<td colspan='2' style='border-left: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black' >
                                             <b style='font-weight:900;'>P.O.I </b> $pmain[issplace]
                                             </td>";
                                echo "<td colspan='3' style='border-left: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black' >
                                             <b style='font-weight:900;'>Sector </b> $pmain[sector]
                                             
                                             </td>";
                                echo "</tr>";

                                echo "<tr>";
                                echo "<td></td>";
                                echo "<td colspan='7'><b style='font-weight:900;'>Tel </b> $pmain[tel]   <b style='margin-left:50px;font-weight:900;'>Mobile </b>$pmain[mobile]</td>";
                                // echo "<td colspan='2' style='border-left: 1px solid black;border-right: 1px solid black' >
                                //              </td>";
                                // echo "<td colspan='3' style='border-left: 1px solid black;border-right: 1px solid black' >

                                //              </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 .= "Pax #, Date, Name, Caste, Nationality, P.O.B, D.O.B, Gender \n";
                            
                        $pctr = 0;
                        $pchtypecond = "";

                        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'];

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

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

                                $k = 0;

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

                                $pctr++;
                                $contents .= "$paxno" . ",";
                                $contents .= "$pmain[mydt]" . ",";
                                if ($pmain['cancdate'] != "0000-00-00") {
                                    $contents .= "$partynm" . ",";
                                } else {
                                    if ($paxcount == 1) {
                                        $contents .= "$partynm" . ",";
                                    } else {
                                        $curdate = date("Y-m-d");
                                        //                                     browse($dob);
                                        
                                        $infmon = date('Y-m-d', strtotime($dob . ' + 24 month'));
                                        //                                     browse($infmon);
                                        
                                        if ($infmon > $curdate) {
                                            $contents .= "$partynm" . ",";
                                        } else {
                                            $contents .= "$partynm" . ",";
                                        }
                                    }
                                }
                                $contents .= "$pmain[caste]" . ",";
                                $contents .= "$pmain[nation]" . ",";
                                $contents .= "$pmain[plbirth]" . ",";
                                
                                $skdob = format_date($pmain["dob"]);
                                $contents .= "$skdob" . ",";
                                $contents .= "$gender" . "\n";
                                
                                
                                $contents .= "Address" . ",";
                                $contents .= "$pmain[add1]" . ",";
                                $contents .= "" . ",";
                                $contents .= "Passport Details" . ",";
                                $contents .= "" . ",";
                                $contents .= "Airlines Details" . "\n";
                                
                                
                                
                                $contents .= "" . ",";
                                $contents .= "$pmain[add2]" . ",";
                                $contents .= "" . ",";
                                $contents .= "Passport No." . "$pmain[redypass]" . ",";
                                $contents .= "" . ",";
                                $contents .= "Airlines" . "$pmain[craft]" . ",";
                                $contents .= "Mofano"  . ",";
                                $contents .= "$pmain[mofano]"  . "\n";
                                
                                $issuedt = format_date($pmain["issdate"]);
                                $depdate = format_date($pmain["depdate"]);
                                
                                $contents .= ""  . ",";
                                $contents .= "$pmain[add3]"  . ",";
                                $contents .= ""  . ",";
                                $contents .= "Issue Date" . "$issuedt"  . ",";
                                $contents .= ""  . ",";
                                $contents .= "Departure Date" . "$depdate" . ",";
                                $contents .= "Class"  . ",";
                                $contents .= "$pmain[batchno]"  . "\n";
                                
                                $expirydt = date('d-m-Y', strtotime($pmain["expdate"]));
                                $arrivaldt = date('d-m-Y', strtotime($pmain["arrdate"]));
                                
                                $contents .= ""  . ",";
                                $contents .= "$pmain[city]"  . ",";
                                $contents .= "Pin" . "$pmain[pin]" . ",";
                                $contents .= "Expiry Date"  . ",";
                                $contents .= "$expirydt"  . ",";
                                $contents .= "Arrival Date"  . "$arrivaldt" . ",";
                                $contents .= "Group"  . ",";
                                $contents .= "$pmain[groupno]"  . "\n";
                                
                                $contents .= ""  . ",";
                                $contents .= "$pmain[state]"  . ",";
                                $contents .= ""  . ",";
                                $contents .= "P.O.I" . "$pmain[issplace]" . ",";
                                $contents .= ""  . ",";
                                $contents .= "Sector" . "$pmain[sector]"  . ",";
                                $contents .= ""  . ",";
                                $contents .= ""  . "\n";
                                
                                $contents .= ""  . ",";
                                $contents .= "Tel" . "$pmain[tel]"  . ",";
                                $contents .= "Mobile" . "$pmain[mobile]" . ",";
                                $contents .= ""  . ",";
                                $contents .= ""  . ",";
                                $contents .= ""  . ",";
                                $contents .= ""  . ",";
                                $contents .= ""  . "\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;
    }
}
?>