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

$maindb = "product";
$trandb = "transacn";
$formname = "Inward Outward History";
$file = "inwoutrep.php";
$zroot = $_SESSION["zdir"];
$zcomp = $_SESSION["comp_" . $dbname];
$bseries = $_SESSION["user_series"];

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

$itemname = $_POST['itemname'];
$godown = trim($_POST["godown"]);

$name = $itemname;
$a = 1;
$godowncond = "";

$nmcond = "";
$nmcond1 = "";
if (!empty($itemname)) {
    $nmcond = " and `code` = '$itemname'";
    $nmcond1 = " and `itemcode` = '$itemname'";
} else {
    browse("Item Name Cannot Be Empty");
    $a = 0;
}
if (!empty($_POST["godown"])) {
    $godowncond = " and `godown` = '$godown' ";
}

if (empty($_POST["frmdt"])) {
    browse("From Date Cannot Be Empty");
    $a = 0;
}

if (empty($_POST["todt"])) {
    browse("TO DATE CANNOT BE EMPTY");
    $a = 0;
}

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

$mycomp = $_SESSION["comp_" . $dbname];
$getcomp = mysqli_query($conn, "SELECT *,UCASE(compname)as cname FROM $zroot.`company` WHERE `code`='$mycomp'") or die(mysqli_error($conn));
$comp = mysqli_fetch_array($getcomp);

$mfdt = date("d-m-Y", strtotime($mfrmdate));
$mtdt = date("d-m-Y", strtotime($mtodate));
if (isset($_POST["view"])) {

    if ($a == 0) {
        echo "<script>window.close();</script>";
    } else {
        $gettran = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS mydate FROM `$trandb` WHERE `itemcode` = '$itemname' 
                                        and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godowncond order by vouchdt, ordno, vouchno") or die(mysqli_error($conn));
        $getno = mysqli_num_rows($gettran);
        include("includes/linkshead.php");
        $getitem = mysqli_query($conn, "Select code, name from product where code = '$itemname'") or die(mysqli_error($conn));
        $fetitem = mysqli_fetch_array($getitem);
        ?>
        <style>
            @media print {
                .noPrint {
                    display:none;
                }
            }

        </style>
        </head>
        <body>
            <div class="container">
                <?php
                include("includes/head_rep.php");
                ?>
                <div class="row">

                    <div class="col-lg-12" style="width:100%;">
                        <div class="box">
                            <div id="sortableTable" class="body collapse in" >
                                <table id="" class="table table-bordered sortableTable table-responsive" table-hover table-striped style="width:100%;">
                                    <thead>
                                        <tr style="height:10px;background-color: #78909C;color: #ffffff;">
                                            <?php
                                            echo "<th align='left' style = 'width:100%'>$fetitem[name]</th>";
                                            ?>  
                                        </tr>
                                    </thead>
                                </table>
                                <table id="" class="table table-bordered sortableTable table-responsive" table-hover table-striped style="width:100%;">
                                    <thead>
                                        <tr style="height:10px;background-color: #78909C ;color: #ffffff;">
                                            <th align="left" width='5%' style="font-family: arial;">Sr.</th>
                                            <th align="left" width='10%'>Date</th>
                                            <th align="left" width='10%'>Voucher</th>
                                            <th align="left" width='20%'>Party</th>
                                            <th align="left" width='10%' style="text-align: right;">Add</th>
                                            <th align="right" width='10%' style="text-align: right;">Less</th>
                                            <th align="right" width='10%' style="text-align: right;">Cost / Rate</th>
                                            <th align="left" width='10%' style="text-align: right;">Balance</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $gradd = 0;
                                        $grless = 0;

//                                        $getopen = mysqli_query($conn, "SELECT sum(opening) as sqty FROM `$maindb` WHERE `code` = '$itemname'") or die(mysqli_error($conn));
//                                        $iopen = mysqli_fetch_array($getopen);
//                                        $openqty1 = $iopen['sqty'];

                                        $getop = mysqli_query($conn, "SELECT sum(qty) as balqty FROM `$trandb` WHERE   `itemcode` = '$itemname' and
                                                          `vouchdt` < '$mfrmdate' $godowncond") or die(mysqli_error($conn));
                                        $iop = mysqli_fetch_array($getop);
                                        $opqty = $iop['balqty'];
                                        $openqty = $openqty1 + $opqty;

                                        $ctr = 1;
                                        $balqty = $openqty;
                                        echo "<tr>";
                                        echo "<td align='left'>$ctr</td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'>Opening</td>";

                                        if ($balqty > 0) {
                                            echo "<td align='right'>$balqty</td>";
                                            echo "<td align='right'></td>";
                                            $gradd = $gradd + $balqty;
                                        } else {
                                            $lqty = abs($balqty);
                                            echo "<td align='right'></td>";
                                            echo "<td align='right'>$lqty</td>";
                                            $grless = $grless + $lqty;
                                        }

                                        echo "<td align='right'>$balqty</td>";
                                        echo "</tr>";

                                        while ($row = mysqli_fetch_object($gettran)) {
                                            $qty = $row->qty;

                                            $source = $row->source;
                                            $ctr++;
                                            switch ($source) {
                                                case "PU":
                                                    $getpartname = mysqli_query($conn, "Select a.name,b.vouchno from accmast a, purmain b where a.accode =b.party 
                                                                                       and b.vouchno = '$row->vouchno'   ") or die(mysqli_error($conn));
                                                    $fetpart = mysqli_fetch_object($getpartname);
                                                    $partname = $fetpart->name;
                                                    $url = "purchase.php?vouchno=$fetpart->vouchno&mode=edit";
                                                    break;
                                                case "CONS":
                                                    $getpartname = mysqli_query($conn, "Select a.name, b.vouchno from accmast a, consultmain b where a.accode =b.accode 
                                                                                       and b.vouchno = '$row->vouchno'   ") or die(mysqli_error($conn));
                                                    $fetpart = mysqli_fetch_object($getpartname);
                                                    $partname = $fetpart->name;
                                                    $url = "consult_ent.php?vouchno=$fetpart->vouchno&mode=edit";

                                                    break;
                                                case "SL":
                                                    $getpartname = mysqli_query($conn, "Select a.name from accmast a, salemain b where a.accode =b.party 
                                                                                       and b.vouchno = '$row->vouchno'   ") or die(mysqli_error($conn));
                                                    $fetpart = mysqli_fetch_object($getpartname);
                                                    $partname = $fetpart->name;
                                                    $url = "sale.php?vouchno=$fetpart->vouchno&mode=edit";

                                                    break;
                                                case "IN":
                                                    $partname = "Inward";
                                                    $url = "inward.php?vouchno=$row->vouchno&mode=edit";

                                                    break;

                                                default:
                                                    break;
                                            }
                                            echo "<tr class='clickable-row' data-href='$url' data-target='_blank'>";
                                            echo "<td align='left'>$ctr</td>";
                                            echo "<td align='left'>$row->mydate</td>";
                                            echo "<td align='left'>$row->source  $row->vouchno</td>";
                                            $partname = "";

                                            echo "<td align='left'>$partname</td>";

                                            if ($qty > 0) {
                                                echo "<td align='right'>$qty</td>";
                                                echo "<td align='right'></td>";
                                                $balqty = $balqty + $qty;
                                                $gradd = $gradd + $qty;
                                            } else {
                                                $lqty = abs($qty);
                                                echo "<td align='right'></td>";
                                                echo "<td align='right'>$lqty</td>";
                                                $balqty = $balqty - $lqty;
                                                $grless = $grless + $lqty;
                                            }
                                            echo "<td align='right'>$row->rate</td>";

                                            echo "<td align='right'>$balqty</td>";

                                            echo "</tr>";
                                        }
                                        $grbal = $balqty;

                                        echo "<tr style =''> ";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'><font size='3px'><b>Total</b></font></td>";
                                        echo "<td align='right'><font size='3px'><b>$gradd</b></font></td>";
                                        echo "<td align='right'><font size='3px'><b>$grless</b></font></td>";
                                        echo "<td align='right'><font size='3px'><b></b></font></td>";
                                        echo "<td align='right'><font size='3px'><b>$grbal</b></font></td>";
                                        echo "</tr>";
                                        ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                    <!-- /.col-lg-6 -->
                </div>
                <?php
                include("includes/linksfoot.php");
            }
            ?>
        </div>
    </body>
    </html>
    <?php
}
if (isset($_POST["excel"])) {


    if ($a == 0) {
        echo "<script>window.close();</script>";
    } else {
        $gettran = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS mydate FROM `$trandb` WHERE `itemcode` = '$itemname' 
                                        and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godowncond order by vouchdt, ordno, vouchno") or die(mysqli_error($conn));
        $getno = mysqli_num_rows($gettran);

        $getitem = mysqli_query($conn, "Select code, name from item where code = '$itemname'") or die(mysqli_error($conn));
        $fetitem = mysqli_fetch_array($getitem);
    }

    $contents .= "Item Name,$fetitem[name] ,\n";
    $contents .= "Sr.,Date,Voucher,Add,Less,Rate,Balance\n";

    $gradd = 0;
    $grless = 0;

//    $getopen = mysqli_query($conn, "SELECT sum(opening) as sqty FROM `$maindb` WHERE `code` = '$itemname'") or die(mysqli_error($conn));
//    $iopen = mysqli_fetch_array($getopen);
//    $openqty1 = $iopen['sqty'];

    $getop = mysqli_query($conn, "SELECT sum(qty) as balqty FROM `$trandb` WHERE   `itemcode` = '$itemname' and
                                                          `vouchdt` < '$mfrmdate' $godowncond") or die(mysqli_error($conn));
    $iop = mysqli_fetch_array($getop);
    $opqty = $iop['balqty'];
    $openqty = $openqty1 + $opqty;

    $ctr = 1;
    $balqty = $openqty;
    $contents .= $ctr . ",";
    $contents .= " " . ",";
    $contents .= "Opening" . ",";

    if ($balqty > 0) {
        $contents .= $balqty . ",";
        $contents .= " " . ",";
        $gradd = $gradd + $balqty;
    } else {
        $lqty = abs($balqty);
        $contents .= " " . ",";
        $contents .= $lqty . ",";
        $grless = $grless + $lqty;
    }

    $contents .= $balqty . "\n";

    while ($row = mysqli_fetch_object($gettran)) {
        $qty = $row->qty;

        $ctr++;
        $contents .= $ctr . ",";
        $contents .= $row->mydate . ",";
        $contents .= $row->source . $row->vouchno . ",";

        if ($qty > 0) {
            $contents .= $qty . ",";
            $contents .= " " . ",";
            $balqty = $balqty + $qty;
            $gradd = $gradd + $qty;
        } else {
            $lqty = abs($qty);
            $contents .= " " . ",";
            $contents .= $lqty . ",";
            $balqty = $balqty - $lqty;
            $grless = $grless + $lqty;
        }

        $contents .= $row->rate . ",";
        $contents .= $balqty . "\n";
    }
    $grbal = $balqty;

    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $gradd . ",";
    $contents .= $grless . ",";
    $contents .= " " . ",";
    $contents .= $grbal . "\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"])) {

    if ($a == 0) {
        echo "<script>window.close();</script>";
    } else {
        $gettran = mysqli_query($conn, "SELECT *, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') AS mydate FROM `$trandb` WHERE `itemcode` = '$itemname' 
                                        and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godowncond order by vouchdt, ordno, vouchno") or die(mysqli_error($conn));
        $getno = mysqli_num_rows($gettran);

        $getitem = mysqli_query($conn, "Select code, name from item where code = '$itemname'") or die(mysqli_error($conn));
        $fetitem = mysqli_fetch_array($getitem);

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

        class pdf extends fpdf {

//            function header() {
//                $this->SetX(15);
//                $this->SetFont('Arial', 'I', 8);
//                $this->Cell(0, 10, "$fetitem[name]", 1);
//            }

            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', 'I', 10);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(96, 130, 191);
        $pdf->Cell(8, 7, "Sr.", 1, 0, '', 1);
        $pdf->Cell(20, 7, "Date", 1, 0, '', 1);
        $pdf->Cell(20, 7, "Voucher", 1, 0, '', 1);
        $pdf->Cell(30, 7, "Add", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Less", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Rate", 1, 0, '', 1, 'R');
        $pdf->Cell(30, 7, "Balance", 1, 0, '', 1, 'R');
        $pdf->SetFont('Arial', '', 1, 0);

        $pdf->SetTextColor(0, 0, 0);
        $pdf->Ln();
        $gradd = 0;
        $grless = 0;

//        $getopen = mysqli_query($conn, "SELECT sum(opening) as sqty FROM `$maindb` WHERE `code` = '$itemname'") or die(mysqli_error($conn));
//        $iopen = mysqli_fetch_array($getopen);
//        $openqty1 = $iopen['sqty'];

        $getop = mysqli_query($conn, "SELECT sum(qty) as balqty FROM `$trandb` WHERE   `itemcode` = '$itemname' and
                                                          `vouchdt` < '$mfrmdate' $godowncond") or die(mysqli_error($conn));
        $iop = mysqli_fetch_array($getop);
        $opqty = $iop['balqty'];
        $openqty = $openqty1 + $opqty;

        $ctr = 1;
        $balqty = $openqty;
        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(8, 7, "$ctr", 1);
        $pdf->Cell(20, 7, " ", 1);
        $pdf->Cell(20, 7, "Opening", 1);

        if ($balqty > 0) {
            $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
            $pdf->Cell(30, 7, " ", 1);
            $gradd = $gradd + $balqty;
        } else {
            $lqty = abs($balqty);
            $pdf->Cell(30, 7, " ", 1);
            $pdf->Cell(30, 7, "$lqty", 1, 0, 'R');
            $grless = $grless + $lqty;
        }
        $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
        $pdf->Ln();

        while ($row = mysqli_fetch_object($gettran)) {
            $qty = $row->qty;

            $ctr++;
            $pdf->Cell(8, 7, "$ctr", 1);
            $pdf->Cell(20, 7, "$row->mydt", 1);
            $pdf->Cell(20, 7, "$row->source" . "$row->vouchno", 1);
            if ($qty > 0) {
                $pdf->Cell(30, 7, "$qty", 1, 0, 'R');
                $pdf->Cell(30, 7, " ", 1);
                $balqty = $balqty + $qty;
                $gradd = $gradd + $qty;
            } else {
                $lqty = abs($qty);
                $pdf->Cell(30, 7, " ", 1, 0, 'R');
                $pdf->Cell(30, 7, "$lqty", 1, 0, 'R');
                $balqty = $balqty - $lqty;
                $grless = $grless + $lqty;
            }
            $pdf->Cell(30, 7, "$row->rate", 1, 0, 'R');
            $pdf->Cell(30, 7, "$balqty", 1, 0, 'R');
            $pdf->Ln();
        }
        $grbal = $balqty;
        $pdf->Cell(8, 7, " ", 1);
        $pdf->Cell(20, 7, " ", 1);
        $pdf->Cell(20, 7, "Total", 1, 0, 'R');
        $pdf->Cell(30, 7, "$gradd", 1, 0, 'R');
        $pdf->Cell(30, 7, "$grless", 1, 0, 'R');
        $pdf->Cell(30, 7, " ", 1, 0, 'R');
        $pdf->Cell(30, 7, "$grbal", 1, 0, 'R');
        $pdf->SetFont('Arial', '', 1, 0);

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

        $pdf->Output("Inward/Outward.pdf", "D");
    }
}
?>