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

$cancel = $_REQUEST["cancel"];
if ($_GET['month'] != '') {
    $weekmon = "MONTHLY";
} else {
    $weekmon = "WEEKLY";
}
$lseries = $_GET['series'];
if ($cancel == "no") {
    $maindb = "chalmain";
    $trandb = "chaltran";
    $formname = "CHALLAN REGISTER $weekmon - $lseries";
    $grid = "chalreg.php?cancel=no";
} else {
    $maindb = "chaldel";
    $trandb = "chaldeltran";
    $formname = "CANCELLED CHALLAN REGISTER";
    $grid = "chalreg.php?cancel=yes";
}

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
mysqli_select_db($conn, $zdb);

if (isset($_POST["view"]) || $_GET['series']) {

    $ccond = "";
    $cuscond = "";
    $vcond = "";
    $rcond = "";
    $crwcond = "";
    $ppcond = "";
    $cdcond = "";
    $locond = "";
    $srcond = "";
    $ctcond = "";

    $custcond = '';
    $custcond2 = '';

    $gsrcond = "";

    if (!empty($_POST['company'])) {
        foreach ($_POST['company'] as $cucheck2) {
            $ccheck2[$ccount2++] .= "OR `custcode` = '" . $cucheck2 . "' ";
            $ccheckoth[$ccountoth++] .= "OR `custcode` = '" . $cucheck2 . "' ";
        }

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

        $custcond = " AND (`custcode` = '' " . $cuscheck2 . " )";
        $custcond2 = " AND (`custcode` = '' " . $cuscheckoth . " )";
    }

    if (!empty($_POST["vessel"])) {
        $vessel = trim($_POST["vessel"]);
        $vcond = "AND `vessel` ='$vessel'";
    }

    if (!empty($_POST["cmbrank"])) {
        $cmbrank = trim($_POST["cmbrank"]);
        $rcond = "AND `rankcode` ='$cmbrank'";
    }

    if (!empty($_POST["crew"])) {
        $crew = trim($_POST["crew"]);
        $crwcond = "AND `crew` ='$crew'";
    }

    if (!empty($_POST["ppno"])) {
        $ppno = trim($_POST["ppno"]);
        $ppcond = "AND `ppno` ='$ppno'";
    }

    if (!empty($_POST["cdcno"])) {
        $cdno = trim($_POST["cdcno"]);
        $cdcond = "AND `cdcno` ='$cdno'";
    }

    if (!empty($_POST["custtype"])) {
        $custype = trim($_POST["custtype"]);
        $ctcond = "AND `bcode` ='$custype'";
    }

    if (!empty($_POST["location"])) {
        $locate = trim($_POST["location"]);
        $locond = "and `bcode` = '$locate'";
    }

    if (!empty($_POST["series"])) {
        $series = trim($_POST["series"]);
        $srcond = "and `series` = '$series'";
    }

    if (!empty($_GET["series"])) {
        $gseries = trim($_GET["series"]);
        $gsrcond = "and `series` = '$gseries'";
    }
    $gcrcond = "";
//    if (!empty($_GET["ctype"])) {
//        $gctype = trim($_GET["ctype"]);
//        if ($gctype == 'CR') {
//            $gcrcond = "and `delvchgs` > 0";
//        } else if ($gctype == 'N') {
//            $gcrcond = "and `cashflag` !='Y'";
//        } else {
//            $gcrcond = "and `cashflag` ='Y'";
//        }
//    }

    if (empty($_POST["frmdt"])) {
        if ($_GET["fdate"] != '') {
            $frmdt = $_GET["fdate"];
        } else {
            browse("From Date Cannot Be Empty");
            header("refresh:1; $grid");
        }
    } else {
        $frmdt = $_POST["frmdt"];
    }

    if (empty($_POST["todt"])) {
        if ($_GET["tdate"] != '') {
            $todt = $_GET["tdate"];
        } else {
            browse("To Date Cannot Be Empty");
            header("refresh:1; $grid");
        }
    } else {
        $todt = $_POST["todt"];
    }


    $type = $_POST["dtype"];


    $mfrmdate = trim($frmdt);
    $mtodate = trim($todt);

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));

    $datecond = "and date BETWEEN '$mfrmdate' AND '$mtodate'";
    if ($_GET['month'] != '') {
        $month = $_GET['month'];
        $datecond = "and month(date)='$month'";
    }

    $getcash = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as date FROM `$maindb` WHERE `chalno` != '' and `cashflag`='Y' $datecond $gsrcond
                       $custcond $vcond $rcond $crwcond $ppcond $cdcond $ctcond $srcond $gcrcond ORDER BY `date`, `chalno` ") or die(mysqli_error($conn));

    $getcred = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as date FROM `$maindb` WHERE `chalno` != '' and `cashflag`!='Y' $datecond $gsrcond
                       $custcond $vcond $rcond $crwcond $ppcond $cdcond $ctcond $srcond $gcrcond ORDER BY `date`, `chalno` ") or die(mysqli_error($conn));

    $getcourier = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as date FROM `$maindb` WHERE `chalno` != '' and `delvchgs` > 0 and `cashflag` != 'Y'
                            $datecond $gsrcond $custcond $vcond $rcond $crwcond $ppcond $cdcond $ctcond $srcond $gcrcond ORDER BY `date`, `chalno` ") or die(mysqli_error($conn));

    include("includes/linkshead.php");
    ?>
    <style>
        h5{
            font-size: 18px;
            margin-top: 7px;
            margin-left: 6px;
        }
        .report{
            margin-top: 0px;
        }
        #detail td{
            padding-left: 19px;
        } 
    </style>    
    </head>
    <body>
        <div class="container-fluid">
            <?php include("includes/head_rep.php"); ?>
            <div id="sortableTable" class="body collapse in row">
                <ul class="nav nav-tabs">
                    <li class="active"><a data-toggle="tab" href="#home" style="background: #4285f4;color: white">Credit</a></li>
                    <li><a data-toggle="tab" href="#menu1" style="background: #ea4335; color: white">Cash </a></li>
                    <li><a data-toggle="tab" href="#menu2" style="background: #fbbc05;color: white">Courier</a></li>
                </ul>

                <div class="tab-content">
                    <div id="home" class="tab-pane fade in active">
                        <table style="width:100%" class="table table-responsive table-condensed table-hover table-bordered" id="example">     
                            <thead >
                                <tr style="height:10px;background-color: #4285f4;color: #ffffff;" id="font">
                                    <th style="width:2%;">Sr</th>
                                    <th style="width:6%;">Date</th>
                                    <th style="width:6%;">Challan</th>
                                    <th style="width:4%;">Series</th>
                                    <th style="width:25%;">Customer Name</th>
                                    <th style="width:12%;">Vessel</th>
                                    <th style="width:10%;">Rank</th>
                                    <th style="width:19%;">Crew</th>
                                    <th style="width:8%;">PP. No.</th>
                                    <th style="width:10%;">Id No.</th>
                                </tr>
                            </thead>
                            <tbody>

                                <?php
                                $gross = 0;
                                $delchgs = 0;
                                $vat4 = 0;
                                $vat = 0;
                                $netamt = 0;

                                $repline = 1;
                                $ctr = 0;
//                        $replen = 20;
                                while ($row = mysqli_fetch_object($getcred)) {

                                    $getparty = mysqli_query($conn, "SELECT custcode, name FROM `customer` WHERE  `custcode`='$row->custcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $partyname = mysqli_fetch_object($getparty);

                                    $getrank = mysqli_query($conn, "SELECT code, name FROM `rank` WHERE  `code`='$row->rankcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $rankname = mysqli_fetch_object($getrank);

                                    $ctr++;
//                                    echo "<tr style='background: white;' data-target='_blank' id='font' data-href='challangst.php?mode=last&series=$row->series&reason=yes' class='clickable-row'> ";
                                    echo "<tr style='background: white;'> ";
                                    echo "<td align='left'>$ctr</td>";
                                    echo "<td align='left' id='font'>$row->date</td>";
                                    echo "<td align='left' id='font'><a target='_blank' href='challangst.php?mode=edit&chalno=$row->chalno&series=$row->series&reason=yes'>$row->chalno</a></td>";
                                    echo "<td align='left' id='font'>$row->series</td>";
                                    echo "<td align='left' id='font'>$partyname->name</td>";
                                    echo "<td id='font'>$row->vessel</td>";
                                    echo "<td id='font'>$rankname->name</td>";
                                    echo "<td id='font'>$row->crew</td>";
                                    echo "<td id='font'>$row->ppno</td>";
                                    echo "<td id='font'>$row->cdcno</td>";
                                    echo "</tr>";

                                    $gross = $gross + $row->gross;
                                    $delchgs = $delchgs + $row->delvchgs;
                                    $vat4 = $vat4 + $row->vat4;
                                    $vat = $vat + $row->vat;
                                    $netamt = $netamt + $row->netamt;
                                }
                                ?>


                            </tbody>
                        </table>
                    </div>
                    <div id="menu1" class="tab-pane fade">

                        <table style="width:100%" class="table table-responsive table-condensed table-hover table-bordered" id="example">     
                            <thead >
                                <tr style="height:10px;background-color: #ea4335;color: #ffffff;" id="font">
                                    <th style="width:2%;">Sr</th>
                                    <th style="width:6%;">Date</th>
                                    <th style="width:6%;">Challan</th>
                                    <th style="width:4%;">Series</th>
                                    <th style="width:25%;">Customer Name</th>
                                    <th style="width:12%;">Vessel</th>
                                    <th style="width:10%;">Rank</th>
                                    <th style="width:19%;">Crew</th>
                                    <th style="width:8%;">PP. No.</th>
                                    <th style="width:10%;">Id No.</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $gross = 0;
                                $delchgs = 0;
                                $vat4 = 0;
                                $vat = 0;
                                $netamt = 0;

                                $repline = 1;
                                $ctr = 0;
//                        $replen = 20;
                                while ($row = mysqli_fetch_object($getcash)) {

                                    $getparty = mysqli_query($conn, "SELECT custcode, name FROM `customer` WHERE  `custcode`='$row->custcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $partyname = mysqli_fetch_object($getparty);

                                    $getrank = mysqli_query($conn, "SELECT code, name FROM `rank` WHERE  `code`='$row->rankcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $rankname = mysqli_fetch_object($getrank);

                                    $ctr++;
                                    echo "<tr style='background: white;' id='font'> ";
                                    echo "<td align='left'>$ctr</td>";
                                    echo "<td align='left' id='font'>$row->date</td>";
                                    echo "<td align='left' id='font'><a target='_blank' href='chalcashgst.php?mode=edit&chalno=$row->chalno&series=$row->series&reason=yes'>$row->chalno</a></td>";
                                    echo "<td align='left' id='font'>$row->series</td>";
                                    echo "<td align='left' id='font'>$partyname->name</td>";
                                    echo "<td id='font'>$row->vessel</td>";
                                    echo "<td id='font'>$rankname->name</td>";
                                    echo "<td id='font'>$row->crew</td>";
                                    echo "<td id='font'>$row->ppno</td>";
                                    echo "<td id='font'>$row->cdcno</td>";
                                    echo "</tr>";

                                    $gross = $gross + $row->gross;
                                    $delchgs = $delchgs + $row->delvchgs;
                                    $vat4 = $vat4 + $row->vat4;
                                    $vat = $vat + $row->vat;
                                    $netamt = $netamt + $row->netamt;
                                }
                                ?>

                            </tbody>
                        </table>

                    </div>
                    <div id="menu2" class="tab-pane fade">
                        <table style="width:100%" class="table table-responsive table-condensed table-hover table-bordered" id="example">     
                            <thead >
                                <tr style="height:10px;background-color: #fbbc05;color: #ffffff;" id="font">
                                    <th style="width:2%;">Sr</th>
                                    <th style="width:6%;">Date</th>
                                    <th style="width:6%;">Challan</th>
                                    <th style="width:4%;">Series</th>
                                    <th style="width:25%;">Customer Name</th>
                                    <th style="width:12%;">Vessel</th>
                                    <th style="width:10%;">Rank</th>
                                    <th style="width:19%;">Crew</th>
                                    <th style="width:8%;">PP. No.</th>
                                    <th style="width:10%;">Id No.</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $gross = 0;
                                $delchgs = 0;
                                $vat4 = 0;
                                $vat = 0;
                                $netamt = 0;

                                $repline = 1;
                                $ctr = 0;
//                        $replen = 20;
                                while ($rowcour = mysqli_fetch_object($getcourier)) {


                                    $getparty = mysqli_query($conn, "SELECT custcode, name FROM `customer` WHERE  `custcode`='$rowcour->custcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $partyname = mysqli_fetch_object($getparty);

                                    $getrank = mysqli_query($conn, "SELECT code, name FROM `rank` WHERE  `code`='$rowcour->rankcode' ORDER BY `name`") or die(mysqli_error($conn));
                                    $rankname = mysqli_fetch_object($getrank);

                                    $ctr++;
                                    echo "<tr style='background: white;' id='font'> ";
                                    echo "<td align='left'>$ctr</td>";
                                    echo "<td align='left' id='font'>$rowcour->date</td>";
                                    echo "<td align='left' id='font'><a target='_blank' href='challangst.php?mode=edit&chalno=$rowcour->chalno&series=$rowcour->series&reason=yes'>$rowcour->chalno</a></td>";
                                    echo "<td align='left' id='font'>$rowcour->series</td>";
                                    echo "<td align='left' id='font'>$partyname->name</td>";
                                    echo "<td id='font'>$rowcour->vessel</td>";
                                    echo "<td id='font'>$rankname->name</td>";
                                    echo "<td id='font'>$rowcour->crew</td>";
                                    echo "<td id='font'>$rowcour->ppno</td>";
                                    echo "<td id='font'>$rowcour->cdcno</td>";
                                    echo "</tr>";

                                    $gross = $gross + $rowcour->gross;
                                    $delchgs = $delchgs + $rowcour->delvchgs;
                                    $vat4 = $vat4 + $rowcour->vat4;
                                    $vat = $vat + $rowcour->vat;
                                    $netamt = $netamt + $rowcour->netamt;
                                }
                                ?>
                            </tbody>
                        </table>
                    </div>
                </div>
                <div class="col-lg-12 text-center" style="height:20px;background-color: #EF5350;color: #ffffff;">
                    End 
                </div> 
            </div>


            <?php
            include("includes/linksfoot.php");
//        include("includes/exp_lib.php");
            ?>
    </body>
    </html>
    <?php
}
if (isset($_POST["excel"])) {
    $ccond = "";
    $cuscond = "";
    $vcond = "";
    $rcond = "";
    $crwcond = "";
    $ppcond = "";
    $cdcond = "";
    $locond = "";
    $srcond = "";
    $ctcond = "";

    $custcond = '';
    $custcond2 = '';

    if (!empty($_POST['company'])) {
        foreach ($_POST['company'] as $cucheck2) {
            $ccheck2[$ccount2++] .= "OR `custcode` = '" . $cucheck2 . "' ";
            $ccheckoth[$ccountoth++] .= "OR `custcode` = '" . $cucheck2 . "' ";
        }

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

        $custcond = " AND (`custcode` = '' " . $cuscheck2 . " )";
        $custcond2 = " AND (`custcode` = '' " . $cuscheckoth . " )";
    }

    if (!empty($_POST["vessel"])) {
        $vessel = trim($_POST["vessel"]);
        $vcond = "AND `vessel` ='$vessel'";
    }

    if (!empty($_POST["cmbrank"])) {
        $cmbrank = trim($_POST["cmbrank"]);
        $rcond = "AND `rankcode` ='$cmbrank'";
    }

    if (!empty($_POST["crew"])) {
        $crew = trim($_POST["crew"]);
        $crwcond = "AND `crew` ='$crew'";
    }

    if (!empty($_POST["ppno"])) {
        $ppno = trim($_POST["ppno"]);
        $ppcond = "AND `ppno` ='$ppno'";
    }

    if (!empty($_POST["cdcno"])) {
        $cdno = trim($_POST["cdcno"]);
        $cdcond = "AND `cdcno` ='$cdno'";
    }

    if (!empty($_POST["custtype"])) {
        $custype = trim($_POST["custtype"]);
        $ctcond = "AND `bcode` ='$custype'";
    }

    if (!empty($_POST["location"])) {
        $locate = trim($_POST["location"]);
        $locond = "and `bcode` = '$locate'";
    }

    if (!empty($_POST["series"])) {
        $series = trim($_POST["series"]);
        $srcond = "and `series` = '$series'";
    }

    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; $grid");
    }

    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; $grid");
    }

    $type = $_POST["dtype"];


    $mfrmdate = trim($_POST["frmdt"]);
    $mtodate = trim($_POST["todt"]);

    $get = mysqli_query($conn, "SELECT *, DATE_FORMAT(`date`, '%d-%m-%Y') as date FROM `$maindb` WHERE date BETWEEN '$mfrmdate' AND '$mtodate'  
                       $custcond $vcond $rcond $crwcond $ppcond $cdcond $ctcond $srcond ORDER BY `date`, `id`, `chalno` ") or die(mysqli_error($conn));

    $contents .= "Sr.,Date,Challan,Series,Customer Name,Vessel,Rank,Crew,PP. No.,Id No.\n";

    $gross = 0;
    $delchgs = 0;
    $vat4 = 0;
    $vat = 0;
    $netamt = 0;

    $repline = 1;
    $ctr = 0;
//                        $replen = 20;
    while ($row = mysqli_fetch_object($get)) {

        $getparty = mysqli_query($conn, "SELECT custcode, name FROM `customer` WHERE  `custcode`='$row->custcode' ORDER BY `name`") or die(mysqli_error($conn));
        $partyname = mysqli_fetch_object($getparty);

        $getrank = mysqli_query($conn, "SELECT code, name FROM `rank` WHERE  `code`='$row->rankcode' ORDER BY `name`") or die(mysqli_error($conn));
        $rankname = mysqli_fetch_object($getrank);


        $ctr++;
        $date = $row->date;
        $chalno = $row->chalno;
        $srs = $row->series;
        $name = $partyname->name;
        $vess = $row->vessel;
        $name2 = $rankname->name;
        $crw = $row->crew;
        $ppno1 = $row->ppno;
        $cdcno = $row->cdcno;

        $contents .= $ctr . ",";
        $contents .= $date . ",";
        $contents .= $chalno . ",";
        $contents .= $srs . ",";
        $contents .= $name . ",";
        $contents .= $vess . ",";
        $contents .= $name2 . ",";
        $contents .= $crw . ",";
        $contents .= $ppno1 . ",";
        $contents .= $cdcno . "\n";

        $gross = $gross + $row->gross;
        $delchgs = $delchgs + $row->delvchgs;
        $vat4 = $vat4 + $row->vat4;
        $vat = $vat + $row->vat;
        $netamt = $netamt + $row->netamt;

        if ($_POST["dtype"] == 2) {
            $gettran = mysqli_query($conn, "SELECT * FROM $trandb WHERE id = '$row->id' ORDER BY id, srno") or die(mysqli_error($conn));

            $contents .= "Sr.,Item Code & Name,Unit,Qty,Rate\n";
            $ctrdtl = 0;
            while ($row2 = mysqli_fetch_object($gettran)) {

                $ctrdtl++;
                $itmdtl = $row2->itemcode . " " . $row2->itemname;
                $unit = $row2->unit;
                $qty = $row2->qty;
                $rate = $row2->rate;

                $contents .= $ctrdtl . ",";
                $contents .= $itmdtl . ",";
                $contents .= $unit . ",";
                $contents .= $qty . ",";
                $contents .= $rate . "\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;
    }
}
?>