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

$zdb = $_SESSION["zdbyear_$dbname"];
$ztit = $_SESSION["tit"];
$usertype = $_SESSION["usertype"];
$user = $_SESSION['username_' . $dbname];
$keyfld = "vouchno";

mysqli_select_db($conn,$zdb);

if (isset($_REQUEST["search"])) {
    $type = $_REQUEST["type"];
    $frmdate = $_REQUEST["fromdate"];
    $todate = $_REQUEST["todate"];
    $tpcond = "AND `status` = '$type'";
    $section = $_REQUEST["section"];

    $searchTerm = $_REQUEST["search"];
    $sortTerm = $_REQUEST["sorderby"];
    $sortorder = $_REQUEST["sortby"];

    if ($_REQUEST["field"] == "1") {
        $scond = "AND `name` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "2") {
        $scond = "AND `lastname` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "3") {
        $scond = "AND `vouchno` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "4") {
        $scond = "AND `mobile` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "5") {
        $vchdate = $_REQUEST["search"];
        $timedt = strtotime($vchdate);
        $vchdt = date('Y-m-d', $timedt);
        $scond = "AND `confdate` LIKE '%$vchdt%'";
    } else if ($_REQUEST["field"] == "6") {
        $scond = "AND `destination` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "7") {
        $scond = "AND `status` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "8") {
        $scond = "AND `remarks` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "9") {
        $scond = "AND `username` LIKE '%$searchTerm%'";
    } else if ($_REQUEST["field"] == "10") {
        $scond = "AND `source` LIKE '%$searchTerm%'";
    }

    if ($_REQUEST["sortby"] == "1") {
        if ($sortTerm == "1") {
            $cond = "order by `name` ASC";
        } else {
            $cond = "order by `name` DESC";
        }
    } else if ($_REQUEST["sortby"] == "2") {
        if ($sortTerm == "1") {
            $cond = "order by `lastname` ASC";
        } else {
            $cond = "order by `lastname` DESC";
        }
    } else if ($_REQUEST["sortby"] == "3") {
        if ($sortTerm == "1") {
            $cond = "order by `vouchno` ASC";
        } else {
            $cond = "order by `vouchno` DESC";
        }
    } else if ($_REQUEST["sortby"] == "4") {
        if ($sortTerm == "1") {
            $cond = "order by `mobile` ASC";
        } else {
            $cond = "order by `mobile` DESC";
        }
    } else if ($_REQUEST["sortby"] == "5") {
        if ($sortTerm == "1") {
            $cond = "order by `confdate` ASC";
        } else {
            $cond = "order by `confdate` DESC";
        }
    } else if ($_REQUEST["sortby"] == "6") {
        if ($sortTerm == "1") {
            $cond = "order by `destination` ASC";
        } else {
            $cond = "order by `destination` DESC";
        }
    } else if ($_REQUEST["sortby"] == "7") {
        if ($sortTerm == "1") {
            $cond = "order by `status` ASC";
        } else {
            $cond = "order by `status` DESC";
        }
    } else if ($_REQUEST["sortby"] == "8") {
        if ($sortTerm == "1") {
            $cond = "order by `remarks` ASC";
        } else {
            $cond = "order by `remarks` DESC";
        }
    } else if ($_REQUEST["sortby"] == "9") {
        if ($sortTerm == "1") {
            $cond = "order by `username` ASC";
        } else {
            $cond = "order by `username` DESC";
        }
    } else if ($_REQUEST["sortby"] == "10") {
        if ($sortTerm == "1") {
            $cond = "order by `source` ASC";
        } else {
            $cond = "order by `source` DESC";
        }
    }

    if (!empty($frmdate) && !empty($todate)) {
        $dtcond = " and `confdate` >= '$frmdate' and `confdate` <= '$todate'";
    }

    $date = date("Y-m-d");
    $date7 = date('Y-m-d', strtotime("-7 days"));

    $mondate = date("Y-m-d", strtotime("monday this week"));

    $sundate = date("Y-m-d", strtotime("sunday this week"));

    $query_date = date("Y-m-d");

// First day of the month.
    $frstdt = date('Y-m-01', strtotime($query_date));

// Last day of the month.
    $lastdt = date('Y-m-t', strtotime($query_date));


    if ($section == '1') {
        $getrec = mysqli_query($conn,"SELECT *, DATE_FORMAT(`deptdate`, '%d-%m-%Y') as deptdate, DATE_FORMAT(`confdate`, '%d-%m-%Y') as confdate 
                           FROM `leadenq` WHERE `vouchno`!='' and `confdate` >= '$mondate' and `confdate` <= '$sundate' $tpcond $scond $cond ")or die(mysqli_error($conn));
    } else if ($section == '2') {
        $getrec = mysqli_query($conn,"Select * from `leadenq` where `confdate` >= '$frstdt' and   `confdate` <= '$lastdt' order by confdate desc") or die(mysqli_error($conn));
    } else {
        if ($usertype == 1) {
            $getrec = mysqli_query($conn,"SELECT *, DATE_FORMAT(`deptdate`, '%d-%m-%Y') as deptdate, DATE_FORMAT(`confdate`, '%d-%m-%Y') as confdate 
                           FROM `leadenq` WHERE `vouchno`!='' $dtcond $tpcond $scond $cond ")or die(mysqli_error($conn));
        } else if ($usertype == 4) {
            $getrec = mysqli_query($conn,"SELECT *, DATE_FORMAT(`deptdate`, '%d-%m-%Y') as deptdate, DATE_FORMAT(`confdate`, '%d-%m-%Y') as confdate  
                           FROM `leadenq` WHERE `vouchno`!='' and `leaddone`='Y' $dtcond $tpcond $scond $cond ")or die(mysqli_error($conn));
        } else {
            $getrec = mysqli_query($conn,"SELECT *, DATE_FORMAT(`deptdate`, '%d-%m-%Y') as deptdate, DATE_FORMAT(`confdate`, '%d-%m-%Y') as confdate  
                           FROM `leadenq` WHERE `vouchno`!='' and `username`='$user' $dtcond $tpcond $scond $cond ")or die(mysqli_error($conn));
        }
    }
    $get1 = mysqli_num_rows($getrec);
//    browse($get1);
    $ctr = 1;
    while ($rec = mysqli_fetch_array($getrec)) {

        $colr1 = "green";
        $colr2 = "red";
        $colr3 = "blue";
        if ($rec[leaddone] == 'Y') {
            $leaddone = 'YES';
        } else {
            $leaddone = 'NO';
        }

        if ($rec[billdone] == 'Y') {
            $billdone = 'YES';
        } else {
            $billdone = 'NO';
        }

        if ($rec[bilaprov] == 'Y') {
            $bilaprov = 'YES';
        } else {
            $bilaprov = 'NO';
        }
        $name = strtoupper($rec[name]);
        $lastname = strtoupper($rec[lastname]);
        Echo "<tr>";
        Echo "<td class='text-right'><input type='text' value='$ctr' name='srno_$ctr' style='border : none;width : 30px;text-align:center;' readonly></td>";
        if ($usertype == 1 || $usertype == 4) {
            Echo "<td class=''><input type='text' value='$rec[username]' name='username_$ctr' style='border : none;width : 100px; padding : 0px;text-align:left;' readonly class='username'></td>";
        }
        Echo "<td class='text-right'><input type='text' value='$rec[vouchno]' name='vouchno_$ctr' style='border : none;width : 30px; padding : 0px;text-align:left;' readonly class='vouchno'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[confdate]' name='confdate_$ctr' style='border : none; padding : 0px;text-align:left;width: 110px;' readonly class='confdate'></td>";
        if ($section != '1' || $section != '2') {
            Echo "<td class='text-right'><input type='text' value='$rec[ledgname]' name='ledgname_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='ledgname'></td>";
        }
        Echo "<td class='text-right'><input type='text' value='$rec[name]' name='name_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='name'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[lastname]' name='lastname_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='lastname'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[destination]' name='destination_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='destination'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[deptdate]' name='deptdate_$ctr' style='width : 100px;border : none; padding : 0px;text-align:left;' readonly class='deptdate'></td>";
        Echo "<td class=''><input type='text' value='$rec[mobile]' name='mobile_$ctr' style='width : 100px;border : none; padding : 0px;text-align:left;' readonly class='mobile'></td>";
        if ($section == '1' || $section == '2') {
            
        } else {
            Echo "<td class=''><input type='text' value='$leaddone' name='leaddone_$ctr' style='color:$colr1;width : 100px;border : none; padding : 0px;text-align:center;' readonly class='leaddone'></td>";
            Echo "<td class=''><input type='text' value='$billdone' name='billdone_$ctr' style='color:$colr2;width : 100px;border : none; padding : 0px;text-align:center;' readonly class='billdone'></td>";
            Echo "<td class=''><input type='text' value='$bilaprov' name='bilaprov_$ctr' style='color:$colr3;width : 100px;border : none; padding : 0px;text-align:center;' readonly class='bilaprov'></td>";
        }
        Echo "<td class=''><input type='text' value='$rec[email]' name='email_$ctr' style='width : 145px;border : none; padding : 0px;text-align:left;' readonly class='email'></td>";
        if ($section == '1' || $section == '2') {
            Echo "<td class=''><input type='text' value='$rec[mup]' name='mup_$ctr' style='width : 100px;border : none; padding : 0px;text-align:right;' class='mup'></td>";
            Echo "<td class=''><input type='text' value='$rec[conftot]' name='conftot_$ctr' style='width : 100px;border : none; padding : 0px;text-align:right;' class='conftot'></td>";
        }
        echo "<td align='left'>"
        ?>
        <select name='quotvisa_<?= $ctr ?>' id="quotvisa"  class="quotvisa form-control" style='text-align: center;height:33px;width:100px;border: 0px;'>
            <option value='N'<?php
            if ($addflag != 1) {
                if ($rec["quotvisa"] == "N") {
                    echo "selected='selected'";
                }
            }
            ?> >No</option>
            <option value='Y'<?php
            if ($addflag != 1) {
                if ($rec["quotvisa"] == "Y") {
                    echo "selected='selected'";
                }
            }
            ?>>Yes</option>
        </select>
        <?php
        ECHO "</td>";
//        Echo "<td class='text-right'><input type='text' value='$rec[visadone]' name='deptdate_$ctr' style='width : 40px;border : none; padding : 0px;text-align:left;' readonly class='deptdate'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[source]' name='source_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='source'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[billno]' name='deptdate_$ctr' style='border : none; padding : 0px;text-align:left;' readonly class='deptdate'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[billdate]' name='deptdate_$ctr' style='width : 100px;border : none; padding : 0px;text-align:left;' readonly class='deptdate'></td>";
        Echo "<td class='text-right'><input type='text' value='$rec[feedback]' name='deptdate_$ctr' style='width : 50px;border : none; padding : 0px;text-align:left;' readonly class='deptdate'></td>";

        $leadvchres = mysqli_query($conn,"SELECT * FROM `confmain` WHERE `leadvouch` = '$rec[vouchno]' ") or die(mysqli_error($conn));
        $getlead = mysqli_num_rows($leadvchres);
        $ldmvch = mysqli_fetch_array($leadvchres);
//                                    browse($getlead);
        if ($getlead > 0) {
            echo "<td align=left><a href=\"confrecd.php?mode=edit&vouchno=$ldmvch[vouchno]\"><img class='leadvouch' src='images/edit.png'></a></td>";
        } else {
//                                        echo "<td align=center><a href=\"confrecd.php?vouchno=$rec[vouchno]\"><img src='images/edit.png'></a></td>";
            echo "<td align=left><a href = 'confrecd.php?mode=true&vouchno=$rec[vouchno]'><img class='leadvouch' src='images/edit.png'></a></td>";
        }
        Echo "</tr>";
        $ctr++;
    }
} 