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

$maindb = "challan";
// $trandb = "saletran";
$formname = "Challan Register";
$file = "chalreg_fr.php";
$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
$zroot = $_SESSION["zdir"];

$zdbpath = substr($zroot, 0, -4);
mysqli_select_db($conn, $zdb);

$entfile = "challan.php";

$party = $_POST["party"];
$vessel = $_POST["vessel"];

$dtypecond = "";
$partycond = "";
$vesselcond = "";

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

if (empty($_POST["frmdt"])) {
    browse("From Date Cannot Be Empty");
    header("refresh:1; salereg.php");
}
if (empty($_POST["todt"])) {
    browse("To Date Cannot Be Empty");
    header("refresh:1; salereg.php");
}
$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($mtodate));


if (!empty($party)) {
    $partycond = " AND `accode`='$party' ";
}
if (!empty($vessel)) {
    $vesselcond = " AND `vessel`='$vessel' ";
}

$zroot = $_SESSION["zdir"];

$dtsqry = mysqli_query($conn, "SELECT * FROM  $zroot.compyear WHERE finyear != '1001' and ('$mfrmdate' between `startdate` and `lastdate`)") or die(mysqli_error($conn));
$dtsfet = mysqli_fetch_array($dtsqry);
$stdate = $dtsfet['startdate'];
$lstdate = $dtsfet['lastdate'];

$dtlqry = mysqli_query($conn, "SELECT * FROM  $zroot.compyear WHERE finyear != '1001' and ('$mtodate' between `startdate` and `lastdate`)") or die(mysqli_error($conn));
$dtlfet = mysqli_fetch_array($dtlqry);
$lsdate = $dtlfet['lastdate'];

$dateqry = mysqli_query($conn, "SELECT * FROM  $zroot.compyear WHERE finyear != '1001' and (`startdate` >= '$stdate'  and `lastdate` <= '$lsdate') 
                        order by lastdate ") or die(mysqli_error($conn));
$yrnos = mysqli_num_rows($dateqry);

// clubbing data from all year
$salpurstring = '';
$yctr = 1;

while ($getyr = mysqli_fetch_array($dateqry)) {
    $mfinyear = $getyr["finyear"];
    $dbfyear = $zdbpath . $mfinyear;
    if ($yctr == 1) {
        $firstyear = $dbfyear;
    }

    $salpurstring .= "select * from $dbfyear.$maindb where accode != '' $partycond $vesselcond";

    if ($yctr < $yrnos) {
        $salpurstring .= " union all ";
    }
    $yctr++;
}
mysqli_query($conn, "create temporary table saldbf $salpurstring order by vouchdt") or die(mysqli_error($conn));

$mainqry = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`vouchdt`, '%d-%m-%Y') as mydt,DATE_FORMAT(a.`frmdt`, '%d-%m-%Y') as frmdt,
                                DATE_FORMAT(a.`todt`, '%d-%m-%Y') as todt,b.name as lname, c.name as anchname, d.name as partyname, e.name as vname FROM saldbf a 
                                LEFT JOIN launch b ON b.code=a.launch 
                                LEFT JOIN port c ON c.code=a.anchor
                                LEFT JOIN accmast d ON d.accode=a.accode
                                LEFT JOIN vessel e ON e.code=a.vessel
                                WHERE a.`vouchdt` >= '$mfrmdate' AND a.`vouchdt` <= '$mtodate'
                                order by vouchdt ASC") or die(mysqli_error($conn));
$qry = mysqli_num_rows($mainqry);

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

    include("includes/linkshead.php");

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));
?>
    <style type="text/css">
        #tabital {
            font-style: italic;
        }

        #font1 {
            font-style: italic;
        }

        #boldfont {
            font-weight: bold;
        }

        body {
            font-family: verdana;
        }

        @media print {
            a[href]:after {
                display: none;
                visibility: hidden;
            }
        }
        .font-22 {
            font-size: 10px;
        }
    </style>
    </head>

    <body>
        <div class="container-fluid" style="width:100%">
            <!--<div class="row" style="background-color: #b9408f;">-->
            <?php include("includes/head_rep.php"); ?>
            <!--</div>-->
            <div id="" class="row">
                <div class="col-lg-12 pad">
                    <table style="background: white; width: 100%;" class="table table-bordered table-condensed myTable" id="example">
                        <thead class="mySearchTr">
                            <tr class="mySearchTr">
                                <th style="width:5%">Sr</th>
                                <th style="width:7%">Date</th>
                                <th style="width:11%">Party</th>
                                <th style="width:11%">Vessel</th>
                                <th style="width:5%">Order By</th>
                                <th style="width:5%">Anchor</th>
                                <th style="width:5%">Purpose</th>
                                <th style="width:5%">Launch</th>
                                <th style="width:2%">#</th>
                                <th style="width:7%">F.Date</th>
                                <th style="width:5%">Time</th>
                                <th style="width:7%">T.Date</th>
                                <th style="width:5%">Time</th>
                                <th style="width:4%">Hours</th>
                                <th style="width:4%">Min.</th>
                                <th style="width:7%" class="text-right dth">Rate</th>
                                <th style="width:5%" class="text-right dth">Amount</th>
                                <!-- <th style="width:10%">Challan No</th> -->
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totpcs = 0;
                            $totqty = 0;
                            $totamt = 0;

                            while ($sale = mysqli_fetch_array($mainqry)) {

                                $type = $sale['type'];
                                $date = $sale['mydt'];
                                $partyname = $sale['partyname'];
                                $ordby = $sale['ordby'];
                                $vessel = $sale['vname'];
                                $anchor = $sale['anchname'];
                                $launch = $sale['lname'];
                                $purpose = $sale['purpose'];
                                $frmdate = $sale['frmdt'];
                                $frmtime = $sale['frmtime'];
                                $todt = $sale['todt'];
                                $totime = $sale['totime'];
                                $nohrs = $sale['nohrs'];
                                $nomin = $sale['nomin'];
                                $rate = $sale['rate'];
                                $amount = $sale['amount'];
                                if ($type == 'H') {
                                    $bgrow = "style='background-color: #fee5d9'";
                                } else {
                                    $bgrow = "";
                                }
                                $ctr++;
                                echo "<tr class='clickable-row2' data-href='$entfile?mode=edit&vouchno=$sale[vouchno]&cancel=1' [target]='_blank' $bgrow>";
                                echo "<td>$ctr</td>";
                                echo "<td>$date</td>";
                                echo "<td class='font-22'>$partyname</td>";
                                echo "<td class='font-22'>$vessel</td>";
                                echo "<td class='font-22'>$ordby</td>";
                                echo "<td class='font-22'>$anchor</td>";
                                echo "<td class='font-22'>$purpose</td>";
                                echo "<td class='font-22'>$launch</td>";
                                echo "<td style='font-weight:bold'>$type</td>";
                                echo "<td>$frmdate</td>";
                                echo "<td>$frmtime</td>";
                                echo "<td>$todt</td>";
                                echo "<td>$totime</td>";
                                echo "<td class='text-right'>$nohrs</td>";
                                echo "<td class='text-right'>$nomin</td>";
                                echo "<td class='text-right dth'>$rate</td>";
                                echo "<td class='text-right dth amount'>$amount</td>";
                                // echo "<td><a href='$entfile?mode=edit&vouchno=$sale[vouchno]&cancel=1' target='_blank'>$sale[vouchno]</a></td>";
                                echo "</tr>";

                                $totamt = $totamt + $amount;
                            }

                            $colr = "red";
                            echo "<thead>";
                            echo "<th align='right' style='color:$colr;' colspan='16' class='text-right'><h4><b>Total</b></h4></th>";
                            echo "<th align='right' style='color:$colr;' class='totamt text-right'><h6><b>" . number_format($totamt, 2) . "</b></h6></th>";
                            echo "<th align='right' style='color:$colr;'><h6><b></b></h6></th>";
                            echo "</thead>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <?php
        include("includes/linksfoot.php");
        ?>
    </body>

    </html>
<?php
}

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

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

    $contents .= "Sr., Date, Party, Vessel, Order By, Anchor, Purpose, Launch, Type, From Date, From Time, To Date, To Time, Hours, Minutes, Rate, Amount, Challan No \n";

    $ctr = 0;
    $totpcs = 0;
    $totqty = 0;
    $totamt = 0;

    while ($sale = mysqli_fetch_array($mainqry)) {

        if ($sale['type'] == "H") {
            $type = "Hours";
        } else {
            $type = "Lumsum";
        }
        $date = $sale['mydt'];
        $partyname = $sale['partyname'];
        $ordby = $sale['ordby'];
        $vessel = $sale['vessel'];
        $anchor = $sale['anchname'];
        $launch = $sale['lname'];
        $purpose = $sale['purpose'];
        $frmdate = $sale['frmdt'];
        $frmtime = $sale['frmtime'];
        $todt = $sale['todt'];
        $totime = $sale['totime'];
        $nohrs = $sale['nohrs'];
        $nomin = $sale['nomin'];
        $rate = $sale['rate'];
        $amount = $sale['amount'];

        $ctr++;

        $contents . -"$ctr" . ",";
        $contents . -"$date" . ",";
        $contents . -"$partyname" . ",";
        $contents . -"$vessel" . ",";
        $contents . -"$ordby" . ",";
        $contents . -"$anchor" . ",";
        $contents . -"$purpose" . ",";
        $contents . -"$launch" . ",";
        $contents . -"$type" . ",";
        $contents . -"$frmdate" . ",";
        $contents . -"$frmtime" . ",";
        $contents . -"$todt" . ",";
        $contents . -"$totime" . ",";
        $contents . -"$nohrs" . ",";
        $contents . -"$nomin" . ",";
        $contents . -"$rate" . ",";
        $contents . -"$amount" . ",";
        $contents . -"$sale[vouchno]" . "\n";

        $totamt = $totamt + $amount;
    }

    $colr = "red";
    $contents .= "" . ",,,,,,,,,,,,,,,";
    $contents .= "Total" . ",";
    $contents .= "$totamt" . ",";
    $contents .= "$totamt" . "\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;
    }
}

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


    $mycomp = $_SESSION["comp_" . $dbname];
    $zroot = $_SESSION["zdbroot"];
    $getcomp = mysqli_query($conn, "SELECT * FROM $zroot.`company`") or die(mysqli_error($conn));
    $comp = mysqli_fetch_array($getcomp);

    require("library/fpdf/fpdf.php");

    class pdf extends fpdf
    {

        function Footer()
        {

            //Position at 1.5 cm from bottom

            $this->SetY(-15);

            //Arial italic 8

            $this->SetFont('Arial', 'I', 8);

            //Page number

            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        }
    }

    $pdf = new pdf('P', 'mm', 'A4');

    $pdf->AliasNbPages();

    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 18);
    $pdf->Cell(100, 7, "$comp[compname]", 0, 'L');
    $pdf->Ln();
    $pdf->SetFont('Arial', '', 9);
    $pdf->Cell(100, 7, $comp['hoadd1'] . $comp['hoadd2'] . $comp['hoadd3'], 0, 'L');
    $pdf->Ln(8);
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->SetFillColor(249, 244, 245);

    $pdf->Cell(155, 6, $formname, 0, 'L');
    // $pdf->Ln();
    $pdf->Cell(200, 6, "Report Date - " . Date("d-m-y"), 0, 'R');
    $pdf->Ln();
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->Cell(50, 10, 'From Date - ' . $mfdt, 0);
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(000, 000, 000);
    $pdf->Cell(50, 10, 'To Date - ' . $mtdt, 0);
    $pdf->Ln();

    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(86, 21, 133);
    $pdf->Cell(10, 7, "Sr ", 1, 0, '', 1);
    $pdf->Cell(21, 7, "Date", 1, 0, '', 1);
    $pdf->Cell(50, 7, "Party", 1, 0, '', 1);
    $pdf->Cell(12, 7, "Pcs", 1, 0, 'R', 1);
    $pdf->Cell(20, 7, "Qty", 1, 0, 'R', 1);
    $pdf->Cell(18, 7, "Rate", 1, 0, 'R', 1);
    $pdf->Cell(27, 7, "Amount", 1, 0, 'R', 1);
    $pdf->Cell(20, 7, "Vechicle", 1, 0, '', 1);
    $pdf->Cell(20, 7, "Challan No.", 1, 0, '', 1);
    $pdf->SetFont('Arial', '', 1, 0);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();




    $ctr = 0;
    $totpcs = 0;
    $totqty = 0;
    $totamt = 0;

    while ($sale = mysqli_fetch_array($mainqry)) {

        $getparty = mysqli_query($conn, "SELECT `accode`, `name` FROM `accmast` WHERE `accode` = '$sale[accode]'") or die(mysqli_error($conn));
        $fparty = mysqli_fetch_array($getparty);
        $party = $fparty['name'];

        if ($sale['paidby'] == "C") {
            $type = "CASH";
        } else {
            $type = "CREDIT";
        }


        $pcs = $sale['pcs'];
        $qty = $sale['qty'];
        $rate = $sale['rate'];

        $amt = $qty * $rate;


        $ctr++;
        $pdf->SetFont('Arial', '', 10);
        $pdf->Cell(10, 7, "$ctr", 1);
        $pdf->Cell(21, 7, "$sale[mydt]", 1);
        $pdf->SetFont('Arial', '', 9);
        $pdf->Cell(50, 7, "$party", 1);
        $pdf->SetFont('Arial', '', 10);
        $pdf->Cell(12, 7, "$sale[pcs]", 1, 0, 'R');
        $pdf->Cell(20, 7, "$sale[qty]", 1, 0, 'R');
        $pdf->Cell(18, 7, "$sale[rate]", 1, 0, 'R');
        $pdf->Cell(27, 7, "$sale[amount]", 1, 0, 'R');
        $pdf->Cell(20, 7, "$sale[vehicle]", 1);
        $pdf->Cell(20, 7, "$sale[vouchno]", 1);
        $pdf->Ln();
        $totpcs = $totpcs + $pcs;
        $totqty = $totqty + $qty;
        $totamt = $totamt + $amt;
    }
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(12, 7, "", 1);
    $pdf->Cell(108, 7, "", 1);
    $pdf->Cell(32, 7, "", 1);
    $pdf->Cell(15, 7, "Total", 1);
    $pdf->Cell(20, 7, "$gropen", 1);
    $pdf->Cell(30, 7, "$grpur", 1);
    $pdf->Cell(30, 7, "$grsale", 1);
    $pdf->Cell(30, 7, "$grbal", 1);
    $pdf->Ln();
    $ctr++;

    $pdf->Output("SALE REGISTER.pdf", "I");
}
