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

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

$zroot = $_SESSION["zdbroot"];

$trandb = "actran";
$myear = $_SESSION["tit_" . $dbname];
mysqli_select_db($conn, $zdb);

$acond = "";
$vcond = "";
$icond = "";
$ccond = "";
$scond = "";

if (!empty($_POST["acname"])) {
    $accode = trim($_POST["acname"]);
    $acond = "AND a.`accode`='$accode'";
    $acond1 = "AND `accode`='$accode'";
}
if (!empty($_POST["visano"])) {
    $visano = trim($_POST["visano"]);
    $vcond = "AND a.`visano`='$visano'";
    $vcond1 = "AND `visano`='$visano'";
}
if (!empty($_POST["idno"])) {
    $idno = trim($_POST["idno"]);
    $icond = "AND a.`idno`='$idno'";
    $icond1 = "AND `idno`='$idno'";
}
if (!empty($_POST["category"])) {
    $category = trim($_POST["category"]);
    $ccond = "AND a.`category`='$category'";
    $ccond1 = "AND `category`='$category'";
}
// browse($acond);
// browse($vcond);
// browse($icond);
// browse($ccond);
//    if (!empty($_POST["cmbstation"])) {
//        $station = trim($_POST["cmbstation"]);
//        $scond = "AND a.`station`='$station'";
//    }
$dtype = $_POST["dtype"];
// $dtype = 2;
//    $mtodate = $_POST["frmdt"];
$mtodate = date("Y-m-d");
if (isset($_POST["view"]) || $_GET["view"]) {

    include("includes/linkshead.php");

    //    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">

    <style>
        h5 {
            font-size: 18px;
            margin-top: 7px;
            margin-left: 6px;
        }

        .report {
            margin-top: 0px;
        }

        .table-bordered>tbody>tr>th{
         padding: 3px;
        }
        .table-bordered>tbody>tr>td{
         padding: 3px;
        }
    </style>
    </head>

    <body>
        <div class="row" style="margin-top: 5px;">

            <div class="col-lg-7">


                <div class="box">
                    <?php include("includes/head_rep.php"); ?>
                    <div id="sortableTable" class="body collapse in">
                        <table class="table table-bordered">
                            <table style="width:100%;" id="" class="table-bordered">
                                <thead>
                                    <tr class="mySearchTr">
                                        <th></th>
                                        <th colspan="2" style="width:42%">Company</th>
                                        <th style="width:12%">Visa No.</th>
                                        <th style="width:10%">Id No.</th>
                                        <th style="width:14%" class="text-right">Total Pax</th>
                                        <th style="width:12%" class="text-right">Done</th>
                                        <th style="width:12%" class="text-right">Balance</th>

                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $stotqty = 0;
                                    $sdoneqty = 0;
                                    $sbalqty = 0;

                                    $acc = mysqli_query($conn, "SELECT *, sum(qty) as sqty FROM visasacn WHERE accode !='' and qty > 0  
                                                                       $acond1 $vcond1 $icond1 $ccond1 group by visano, idno ") or die(mysqli_error($conn));
                                    while ($getac = mysqli_fetch_array($acc)) {
                                        $gname = mysqli_query($conn, "SELECT `name` FROM accmast WHERE accode=$getac[accode]") or die(mysqli_error($conn));
                                        $fname = mysqli_fetch_array($gname);
                                        $maccode = $getac["accode"];
                                        $macname = $fname["name"];
                                        $mvisano = $getac["visano"];
                                        $midno = $getac["idno"];


                                        $getdoneqry = mysqli_query($conn, "SELECT sum(qty) as doneqty FROM visasacn 
                                                                                      WHERE `visano` = '$mvisano'  AND  `idno` ='$midno' AND qty < 0") or die(mysqli_error($conn));
                                        $fetdoneqry = mysqli_fetch_object($getdoneqry);

                                        $sdoneqty = abs($fetdoneqry->doneqty) ;
                                        $sbalqty = $getac['sqty'] - $sdoneqty;


                                        echo "<tr>";
                                        echo "<td></td>";
                                        echo "<td colspan='2' style='text-align:left;font-size:14px'><b>" . strtoupper($macname) . "</b></td>";
                                        echo "<td style='text-align:left;font-size:14px'><b>" . strtoupper($getac['visano']) . "</b></td>";
                                        echo "<td style='text-align:left;font-size:14px'><b>" . strtoupper($getac['idno']) . "</b></td>";
                                        echo "<td style='text-align:right;font-size:14px'><b>$getac[sqty]</b></td>";
                                        echo "<td style='text-align:right;font-size:14px'><b>$sdoneqty</b></td>";
                                        echo "<td style='text-align:right;font-size:14px'><b>$sbalqty</b></td>";
                                        echo "</tr>";

                                        $stotsqty = $stotsqty + $getac['sqty'];
                                        $stotsdoneqty = $stotsdoneqty + $sdoneqty;
                                        $stotsbalqty = $stotsbalqty + $sbalqty;

                                        if ($dtype == 2) {
                                            ?>

                                            <tr style="height:10px;" class="mySearchTr1">
                                                <th width="5%"  style="font-size:13px;background-color:#babfc2"><i>Sr.</i></th>
                                                <th width="15%" style="font-size:13px;background-color:#babfc2" colspan="2"><i>Category (Englsih)</e></th>
                                                <th width="15%" style="font-size:13px;background-color:#babfc2" colspan="2"><i>Category (Arabic)</e></th>
                                                <th width="6%"  style="font-size:13px;text-align:right;background-color:#babfc2"><i>Qty</i></th>
                                                <th width="6%"  style="font-size:13px;text-align:right;background-color:#babfc2"><i>Done</i></th>
                                                <th width="6%"  style="font-size:13px;text-align:right;background-color:#babfc2"><i>Balance</i></th>
                                            </tr>
                                            <?php
                                            $subtmp1 = mysqli_query($conn, "SELECT * FROM visasacn WHERE `visano` = '$mvisano' AND
                                                                            `idno` ='$midno' group by cateng order by cateng") or die(mysqli_error($conn));
//                                                                                        print table($subtmp1);
                                            $totbillamt = 0;
                                            $totrecdamt = 0;
                                            $totbalamt = 0;

                                            $tctr = 0;
                                            $balqty = $add = $les = 0;

                                            while ($murl = mysqli_fetch_object($subtmp1)) {

                                                $qcateng = $murl->cateng;
                                                $qcategory = $murl->category;

                                                $subadd = mysqli_query($conn, "SELECT sum(qty) as adqty from visasacn 
                                                                            WHERE `visano` = '$mvisano' AND cateng='$qcateng' AND
                                                                            `idno` ='$midno' AND qty > 0") or die(mysqli_error($conn));
                                                $madd = mysqli_fetch_object($subadd);
                                                $subless = mysqli_query($conn, "SELECT sum(qty) as adqty from visasacn 
                                                                            WHERE `visano` = '$mvisano' AND cateng='$qcateng' AND
                                                                            `idno` ='$midno' AND qty < 0") or die(mysqli_error($conn));
                                                $mless = mysqli_fetch_object($subless);
                                                $add = $madd->adqty;
                                                $les = abs($mless->adqty);
                                                $balqty = $add - $les;
                                                $tctr++;

                                                echo "<tr style='height:10px;'>";
                                                echo "<td style='font-size:14px;text-align:left;'>$tctr</td>";
                                                echo "<td style='font-size:14px;text-align:left;' colspan='2'>$qcateng</td>";
                                                echo "<td style='font-size:14px;text-align:left;' colspan='2'>$qcategory</td>";
                                                echo "<td style='font-size:14px;text-align:right;'>$add</td>";
                                                echo "<td style='font-size:14px;text-align:right;'>$les</td>";
                                                echo "<td style='font-size:14px;text-align:right;'>$balqty</td>";
                                                echo "</tr>";
                                            }
                                            echo "<tr style='height:15px;background-color: #989898;'>";
                                            echo "</tr>";
                                            //                                            echo "<td style=\"text-align:left;\" scope=\"col\" width=\"300\"></td>";
//                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"100\" colspan='5'><b>Total</b></td>";
//                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"300\"><b>$totbillamt</b></td>";
//                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>$totrecdamt</b></td>";
//                                            echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>$totbalamt</b></td>";
////                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"></td>";
//                                            echo "</tr>";
                                        }
                                    }
                                   echo "<tr style='background-color:#98f9f0;color:red'>";
                                   echo "<td colspan='5' class='text-right' style='font-size:14px'><b>Total</b></td>";
                                   echo "<td style='text-align:right;font-size:14px'><b>$stotsqty</b></td>";
                                   echo "<td style='text-align:right;font-size:14px'><b>$stotsdoneqty</b></td>";
                                   echo "<td style='text-align:right;font-size:14px'><b>$stotsbalqty</b></td>";
                                   echo "</tr>";
                                    ?>

                                </tbody>
                            </table>
                    </div>
                </div>
            </div>
        </div>
        <?php
}
?>