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

$paxdb = "visaform";
$bookdb = "bookform";
$maindb = 'pax';

$formname = "Certificate";

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];

$tourcode = $_SESSION['tourcode'];
$tourcond = "AND a.tour = '$tourcode'";

mysqli_select_db($conn, $zdb);

$namecond = $regnocond = $paxnocond = $groupcond = $crnocond = $classcond = $tournocond = $citycond = $statecond = $infcond = $cancond = $addcond = $ppexpcond = $ppinhand = $dpdtcond = $dppnrcond = $aircond = $prregnocond = $prphcond = $agentcond = $arrvdatecond = $arrvpnrcond = $buscond = $roomcond = "";

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

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

$class = $_POST['class'];
$agent = $_POST['agent'];
$orderby = $_POST['orderby'];
$regnnocond = "";

$regnfromcond = "";
$regntocond = "";
$fromdatecond = "";
$todatecond = "";
$scopecond = "";
$classcond = "";
$depcond = "";

if (!empty($frmregnno) && !empty($toregnno)) {
    $regnnocond = "AND vregnno >= '$frmregnno' AND vregnno <= '$toregnno' ";
}

if (!empty($frmpaxno) && !empty($topaxno)) {
    $paxnocond = "AND vouchno >= '$frmpaxno' AND vouchno <= '$topaxno' ";
}

if (!empty($toregnno)) {
    $toregnnocond = "";
}
if (!empty($regnfrom)) {
    $regnfromcond = "";
}
if (!empty($regnto)) {
    $regntocond = "";
}

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

if (!empty($name)) {
    $namecond = "AND `haji` = '$name' ";
}

if ($orderby == "R") {
    $ordercond = "order by vregnno ";
} else if ($orderby == "P") {
    $ordercond = "order by vouchno ";
} else {
    $ordercond = "order by name ";
}


$zroot = $_SESSION["zdir"];
$repocolor = $_SESSION["repocolor"];
$compcd = $_SESSION["comp_" . $dbname];

$getcomp = mysqli_query($conn, "SELECT * FROM $zroot.`company` WHERE `code`= '$compcd'") or die(mysqli_error($conn));

$comp = mysqli_fetch_array($getcomp);



$mainqry = mysqli_query($conn, "SELECT a.*,b.othhotel,b.makhotel,b.batchno FROM `visaform` a,bookform  b  
                                              WHERE a.vouchno != '' AND a.vregnno = b.regnno AND a.tour = b.tourno
                                              AND b.cancdate = '0000-00-00' $regnnocond  $paxnocond $classcond $tourcond $ordercond  ") or die(mysqli_error($conn));

$m1 = mysqli_num_rows($mainqry);

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

    include("includes/linkshead.php");
?>
    <style type="text/css">
        .HDFont {
            font-family: "Verdana";
            color: #57606f;
            font-weight: bold;
        }

        @media print {
            .noPrint {
                display: none;
            }

            /* .skimg {
                width: 50% !important;
            }

            .skmarg {
                width: 50% !important;
            } */

            .mainpage {
                margin-top: -5px;
            }

            body {
                margin: 0;
            }

        }

        /* .head {
            font-size: 30px;
            font-weight: bold;
            padding: 3px;
            color: #57606f;
        } */

        .table>tbody>tr>td {
            padding: 1px;
            border-top: 0px;
        }

        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }

        .font {
            font-size: 12px;
        }

        .skdiv {
            width: 50%;
            /* margin: auto; */
        }
    </style>
    </head>

    <body>
        <div class="mainpage" style="width:100%">


            <div class="" style="background-color: <?= $repocolor; ?>">

                <table width="100%" class="table table-condesed1 ">

                    <tr>

                        <td class="" style="width:35%">

                            <div class="skdiv">
                                <img style="height:auto;width:100%;" src="<?php echo $comp['reportheadimg']; ?>">
                            </div>
                            <!-- <img src="<?= $comp['reportheadimg']; ?>" style="width:50%" class="skimg"> -->

                        </td>

                        <td style="font-size: 12px;text-align:left;width:65%" class="HDFont">

                            <?php echo $comp['hoadd1']; ?>

                            <?php echo $comp['hoadd2']; ?>

                            <?php echo $comp['hoadd3']; ?>

                            <?php echo $comp['hocity']; ?>

                            - <?php echo $comp['hopin']; ?>

                        </td>
                    </tr>

                    <tr>

                        <td style="font-size: 14px;text-align:center" class="HDFont" colspan="2">

                            <?= $formname ?>

                        </td>

                    </tr>
                    <tr>

                        <td style="font-size: 13px;text-align:center" colspan="2">

                            This is to certify that the below mentioned details are informed to me by the tour operator before departure from India to Kingdom of Saudi Arabia

                        </td>

                    </tr>

                </table>

            </div>
            <div class="row">
                <div class="col-lg-12">
                    <table style="background: white;width: 100%" class="table table-bordered  table-hover">
                        <thead class="">
                            <tr>
                                <th style="font-weight:bold;color:black !important;width:4%" class="font">SR NO.</th>
                                <th style="font-weight:bold;color:black !important;width:25%" class="font">NAME OF PILGRIM</th>
                                <th style="font-weight:bold;color:black !important;width:8%" class="font">Passport No.</th>
                                <th style="font-weight:bold;color:black !important;width:10%" class="font">Orientation Programme organized by the PTO.</th>
                                <th style="font-weight:bold;color:black !important;width:10%" class="font">Attented Orientation Programme organized by the PTO</th>
                                <th style="font-weight:bold;color:black !important;width:16%" class="font">Building Information and Distance in Makkah</th>
                                <th style="font-weight:bold;color:black !important;width:15%" class="font">Building Information in case of shifting and Distance in Makkah</th>
                                <th style="font-weight:bold;color:black !important;width:28%" class="font">Signature and Date</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $discamt = 0;
                            $tkttotamt = 0;
                            $tourtotamt = 0;

                            while ($fmain = mysqli_fetch_array($mainqry)) {
                                $tktamt = 0;
                                $touramt = 0;

                                $getmakhotel = mysqli_query($conn, "SELECT code,hotelnm,distance FROM hotels where code='$fmain[makhotel]'") or die(mysqli_error($conn));
                                $fetmakhotel = mysqli_fetch_array($getmakhotel);
                                $getothhotel = mysqli_query($conn, "SELECT code,hotelnm,distance FROM hotels where code='$fmain[othhotel]'") or die(mysqli_error($conn));
                                $fetothhotel = mysqli_fetch_array($getothhotel);

                                $yes = "Yes";
                                $ctr++;
                                echo "<tr style='height:55px;'>";
                                echo "<td class='text-center' style='font-size:15px'>$ctr</td>";
                                echo "<td style='font-size:15px'>$fmain[name]</td>";
                                echo "<td style='font-size:15px'>$fmain[redypass]</td>";
                                echo "<td class='text-center' style='font-size:15px'>$yes</td>";
                                echo "<td class='text-center' style='font-size:15px'>$yes</td>";
                                echo "<td>$fetmakhotel[hotelnm] <br> $fetmakhotel[distance] </td>";
                                echo "<td>$fetothhotel[hotelnm] <br> $fetothhotel[distance] </td>";
                                echo "<td></td>";
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

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

    </html>
<?php
}

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

    $contents .= "SR NO., NAME OF PILGRIM, Passport No., Orientation Programme organized by the PTO., Attented Orientation Programme organized by the PTO,";
    $contents .= "Building Information and Distance in Makkah, Building Information in case of shifting and Distance in Makkah, Signature and Date\n";

    $ctr = 0;
    $discamt = 0;
    $tkttotamt = 0;
    $tourtotamt = 0;



    while ($fmain = mysqli_fetch_array($mainqry)) {
        $tktamt = 0;
        $touramt = 0;

        $getmakhotel = mysqli_query($conn, "SELECT code,hotelnm,distance FROM hotels where code='$fmain[makhotel]'") or die(mysqli_error($conn));
        $fetmakhotel = mysqli_fetch_array($getmakhotel);
        $getothhotel = mysqli_query($conn, "SELECT code,hotelnm,distance FROM hotels where code='$fmain[othhotel]'") or die(mysqli_error($conn));
        $fetothhotel = mysqli_fetch_array($getothhotel);

        $yes = "Yes";
        $ctr++;
        $contents .= "$ctr" . ",";
        $contents .= "$fmain[name]" . ",";
        $contents .= "$fmain[redypass]" . ",";
        $contents .= "$yes" . ",";
        $contents .= "$yes" . ",";
        $contents .= "$fetmakhotel[hotelnm]" . "  " . "$fetmakhotel[distance]" . ",";
        $contents .= "$fetothhotel[hotelnm]" . "  " . "$fetothhotel[distance]" . ",";
        $contents .= "" . "\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;
    }
}
?>