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

// $maindb = "rawmat";
// $trandb = "rawsacn";
$maindb = "item";
$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'];
$size = $_POST['size'];


$name = $itemname;
$a = 1;

$nmcond = "";
$nmcond1 = "";
if (!empty($itemname)) {
    $nmcond = " and `code` = '$itemname'";
    $nmcond1 = " and `itemcode` = '$itemname'";
    } else {
        browse("Item Name Cannot Be Empty");
        $a = 0;
    }
    
    if (!empty($size)) {
        $sizecond = " and `size` = '$size'";
        $sizecond1 = " and `size` = '$size'";
    } else {
        browse("Size Cannot Be Empty");
        $a = 0;
    }
    
    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(name)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 ($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' $sizecond order by vouchdt, ordno, vouchno") or die(mysqli_error($conn));

//        print table($gettran);
//        die();
        
$getno = mysqli_num_rows($gettran);
$getitem = mysqli_query($conn, "Select code, name from $maindb where code = '$itemname'") or die(mysqli_error($conn));
$fetitem = mysqli_fetch_array($getitem);

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
        ?>
        <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 class="mySearchTr" style="height:10px;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 class="mySearchTr" style="height:10px;color: #ffffff;">
                                            <th align="left" width='5%' style="font-family: arial;">Sr.</th>
                                            <th align="left" width='12%'>Date</th>
                                            <th align="left" width='12%'>Voucher</th>
                                            <th align="left" width='12%'>Size</th>
                                            <th align="left" width='12%' style="text-align: right;">Add<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="right" width='12%' style="text-align: right;">Less<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="left" width='12%'>Name</th>
                                            <th align="left" width='12%' style="text-align: right;">Rate<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="left" width='12%' style="text-align: right;">Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            <th align="left" width='12%' style="text-align: right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $gradd = 0;
                                        $grless = 0;

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


                                        $ctr = 1;
                                        $balqty = $openqty;
                                        echo "<tr>";
                                        echo "<td align='left'>$ctr</td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'>Opening</td>";
                                        echo "<td align='right'></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='left'></td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'></td>";

                                        echo "<td align='right'>" . number_format($balqty, 2) . "</td>";
                                        echo "</tr>";


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

                                            $qty = $row->qty;

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

                                            $ctr++;
                                            echo "<tr>";
                                            echo "<td align='left'>$ctr</td>";
                                            echo "<td align='left'>$row->mydate</td>";
                                            echo "<td align='left'>$row->source  $row->vouchno</td>";
                                            echo "<td align='left'>$row->size</td>";

                                            if ($qty > 0) {
                                                echo "<td align='right'>$qty</td>";
                                                echo "<td align='right'></td>";
                                                $balqty = $balqty + $qty;
                                                $gradd = $gradd + $qty;
                                                $amt = $row->rate * $qty;
                                            } else {
                                                $lqty = abs($qty);
                                                echo "<td align='right'></td>";
                                                echo "<td align='right'>$lqty</td>";
                                                $balqty = $balqty - $lqty;
                                                $grless = $grless + $lqty;
                                                $amt = $row->rate * $lqty;
                                            }
                                            echo "<td align='left'>$partname</td>";
                                            echo "<td align='right'>$row->rate</td>";
                                            echo "<td align='right'>$amt</td>";
                                            echo "<td align='right'>" . number_format($balqty, 2) . "</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='left'></td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='left'></td>";
                                        echo "<td align='right'><font size='3px'><b>" . number_format($grbal, 2) . "</b></font></td>";
                                        echo "</tr>";
                                    ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
                <!-- /.col-lg-6 -->
            </div>
        </div>
    </body>
    </html>
    <?php
    include("includes/linksfoot.php");
    ?>
    <?php
}
    }
if (isset($_POST["excel"])) {

    $contents .="Sr., Date, Voucher, Add, Less, Name, Rate, Amount, Balance\n";
$gradd = 0;
$grless = 0;

$getop = mysqli_query($conn, "SELECT sum(qty) as balqty FROM `$trandb` WHERE `itemcode` = '$itemname' and
                  `vouchdt` < '$mfrmdate' $sizecond ") or die(mysqli_error($conn));
$iop = mysqli_fetch_array($getop);
$opqty = $iop['balqty'];
$openqty = $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 .="" . ",";
$contents .="" . ",";
$contents .="" . ",";

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

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

    $qty = $row->qty;

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

    $ctr++;
    $contents .="$ctr" . ",";
    $contents .="$row->mydate" . ",";
    $contents .="$row->source $row->vouchno" . ",";
    if ($qty > 0) {
        
        $contents .="$qty" . ",";
        $contents .="" . ",";
        $balqty = $balqty + $qty;
        $gradd = $gradd + $qty;
        $amt = $row->rate * $qty;
    } else {
        $lqty = abs($qty);
        $contents .="" . ",";
        $contents .="$lqty" . ",";
        $balqty = $balqty - $lqty;
        $grless = $grless + $lqty;
        $amt = $row->rate * $lqty;
    }
    $contents .="$partname" . ",";
    $contents .="$row->rate" . ",";
    $contents .="$amt" . ",";
    $contents .="$balqty" . "\n";
}
$grbal = $balqty;

$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;
    }
}
?>