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

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

$formname = 'Itemwise Stock';

$maindb = 'item';
$trandb = 'transacn';

$grid = 'stock.php';

$barcode = $_POST["barcode"];
$prodname = $_POST["prodname"];
$brand = $_POST["brand"];
$quality = trim($_POST["quality"]);
$godown = trim($_POST["godown"]);
$prnval = trim($_POST["prnval"]);
$prnzero = trim($_POST["prnzero"]);
$gperc = trim($_POST["gperc"]);
$size = trim($_POST["size"]);
$reptype = trim($_POST["reptype"]);

$brcond = "";
$pdnmcond = "";
$brandcond = "";
$godowncond = "";
$qualitycond = "";
$sizecond = "";

if (!empty($_POST["barcode"])) {
    $brcond = " and `prodcode` = '$barcode' ";
}
if (!empty($_POST["prodname"])) {
    $pdnmcond = " and `prodname` = '$prodname' ";
}
if (!empty($_POST["brand"])) {
    $brandcond = " and `brand` = '$brand' ";
}
if (!empty($_POST["quality"])) {
    $qualitycond = " and `quality` = '$quality' ";
}
if (!empty($_POST["size"])) {
    $sizecond = " and `size` = '$size' ";
}
$rtit = "";
if (!empty($_POST["godown"])) {
    $godowncond = " and `godown` = '$godown' ";
    $getgodown = mysqli_query($conn, "SELECT code,name FROM `godown` WHERE code = '$godown'") or die(mysqli_error($conn));
    $fgodown = mysqli_fetch_array($getgodown);
    $rtit = $fgodown['name'];
} else {
    $rtit = "All Godown";
}

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");
}

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

mysqli_query($conn, "drop temporary table if exists nondbf") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table nondbf
                            select * from item where code not in (SELECT prodcode FROM `transacn` 
                                       where `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ) 
                                       order by prodname, brand, size, quality") or die(mysqli_error($conn));

$getitem = mysqli_query($conn, "select sum(qty) as sqty, prodcode, prodname, brand, size, quality  from transacn 
                                       where `vouchdt` <= '$mtodate' and 
                                       prodcode in ( select code from nondbf ) group by prodcode    
                                       order by prodname, brand, size, quality") or die(mysqli_error($conn));

$getno = mysqli_num_rows($getitem);

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

if ($getno == 0) {
    browse("Record Not Found");
    echo "<script>
            window.close();
            </script>";
}

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
    </style>

    </head>
    <body>
        <div class="container" style="">
            <?php
            include("includes/head_rep.php");
            ?>
            <div class="row">
                <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 pad">
                    <table style="background: white;" class="table table-condensed table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th align="left" width='4%' style="font-family: arial;">Sr.</th>
                                <th style='width:30%;'>Name</th>
                                <th style='width:20%;'>Brand</th>
                                <th style='width:15%;'>Size</th>
                                <th style='width:15%;'>Quality</th>
                                <th style="width:15%; 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
                         $ctr = 0;
//                            mysqli_select_db($conn, $zdb);
                            while ($row = mysqli_fetch_array($getitem)) {
                                $icode = $row['prodcode'];
                                $isize = $row['size'];
                                $ibrand = $row['brand'];
                                $iquality = $row['quality'];
                                $iprodname = $row['prodname'];
                                $stkqty = $row['sqty'];
                                $balqty = $stkqty;

                                $ctr++;
                                
                                echo "<tr>";
                                echo "<td align='left'>$ctr</td>";
                                echo "<td align='left'>$iprodname</td>";
                                echo "<td align='left'>$ibrand</td>";
                                echo "<td align='left'>$isize</td>";
                                echo "<td align='left'>$iquality</td>";
                                echo "<td align='right'><b>$balqty</b></td>";
                                echo "</tr>";
                                $grbal = $grbal + $balqty;
                            }
                            echo "<tr style ='background-color:white;color:blue'> ";
                            $colspan = '5';
                            echo "<td align='right' colspan='$colspan'><font size='3px'><b> Total</b></font></td>";
                            echo "<td align='right'><b>$grbal</b></td>";
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </body>
    </html>
    <?php
}


if (isset($_POST["excel"])) {
    $contents .= "Sr, Barcode, Name, Brand, Size, Quality, Opening, Add, Less,  Balance\n";

    $gropen = 0;
    $grsale = 0;
    $grpur = 0;
    $grbal = 0;
    $grval = 0;
    $mopen = 0;

    $ctr = 0;
    mysqli_select_db($conn, $zdb);
    while ($row = mysqli_fetch_object($getitem)) {


        $icode = $row->prodcode;
        $isize = $row->size;
        $ibrand = $row->brand;
        $iquality = $row->quality;
        $iprodname = $row->prodname;
        $cost = $fetname['cost'];

        $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE prodcode = '$icode'
                                                             and `vouchdt` < '$mfrmdate' $godowncond") or die(mysqli_error($conn));
        $iopen = mysqli_fetch_array($getopen);
        $openqty = $iopen['sqty'] + $mopen;

        $getpur = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE  prodcode = '$icode' and
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0 $godowncond") or die(mysqli_error($conn));


        $ipur = mysqli_fetch_array($getpur);
        $purqty = $ipur['sqty'];


        $getsale = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE prodcode = '$icode' and 
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0 $godowncond") or die(mysqli_error($conn));
        $isale = mysqli_fetch_array($getsale);
        $saleqty1 = $isale['sqty'];


        $saleqty = $saleqty1 * -1;

        $balqty = $openqty + $purqty - $saleqty;
        $itemvalue = $newcost * $balqty;


        $prnflag = 'N';
        if ($balqty != 0) {
            $prnflag = 'Y';
        } else {
            if ($prnzero == 'Y') {
                $prnflag = 'Y';
            } else {
                $prnflag = 'N';
            }
        }
        if ($prnflag == 'Y') {
            $ctr++;

            $contents .= $ctr . ",";
            $contents .= $icode . ",";
            $contents .= $iprodname . ",";
            $contents .= $ibrand . ",";
            $contents .= $isize . ",";
            $contents .= $iquality . ",";
            $contents .= $openqty . ",";
            $contents .= $purqty . ",";
            $contents .= $saleqty . ",";
            $contents .= $balqty . "\n";
        }

        $gropen = $gropen + $openqty;
        $grsale = $grsale + $saleqty;
        $grpur = $grpur + $purqty;
        $grbal = $grbal + $balqty;
        $grval = $grval + $itemvalue;
    }
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= $gropen . ",";
    $contents .= $grpur . ",";
    $contents .= $grsale . ",";
    $contents .= $grbal . "\n";

    if ($prnval == 2) {
        $contents .= " " . ",";
        $contents .= $grval . "\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;
    }
}
?>