<?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';

$getprevyear = mysqli_query($conn, "SELECT cono, finyear FROM $zroot.`compyear` where `cono`='$compcd' 
                            and `finyear`!='$myyear' ORDER BY `lastdate` DESC LIMIT 0,1 ") or die(mysqli_error($conn));
$showprevyear = mysqli_fetch_array($getprevyear);
$prevyear = $showprevyear['finyear'];


$olddb = $zroot1 . $compcd . '_' . $prevyear;


$gnm = $_POST["name"];
$gname = trim($_POST["itemgrp"]);
$gprodtype = trim($_POST["prodtype"]);
$gsubgrp = trim($_POST["subgrp"]);
$prnval = trim($_POST["prnval"]);
$stockval = trim($_POST["stockval"]);
$gperc = trim($_POST["gperc"]);
$zerostk = trim($_POST["zerostk"]);
$size = trim($_POST["size"]);


$brcond = "";
$ncond = "";
$gcond = "";
$gsubgrpcond = "";
$subcond = "";
$gperccond = "";
$gprodcond = "";
$sizecond = "";
$zerostkcond = "";


if (!empty($_POST["name"])) {
    $ncond = " and b.`code` = '$gnm' ";
}

if (!empty($_POST["size"])) {
    $sizecond = " and a.`size` = '$size' ";
}
//if ($_POST["zerostk"] == 1) {
//    $zerostkcond = "and a.`qty` < 0 ";
//}

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

$getitem = mysqli_query($conn, "SELECT a.*,b.code, b.name, b.itemgrp, b.cost, b.opening,b.hsncode FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code 
                          $ncond $brcond $gcond $gsubgrpcond $sizecond group by a.itemcode,a.size order by b.name+0") or die(mysqli_error($conn));
$getno = mysqli_num_rows($getitem);

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

if (isset($_POST["view"])) {
    if ($getno == 0) {

        browse("Record Not Found");
        echo "<script>
            window.close();
            </script>";
    }

    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">
            <?php
            include("includes/head_rep.php");
            ?>

            <div class="row">
                <div class="col-lg-12 pad">
                    <table style="background: white;width: 100%" class="table table-condensed table-bordered">
                        <thead class="mySearchTr">
                            <tr>

                                <th align="left" width='3%' style="font-family: arial;">Sr.</th>
                                <th align="left" width='25%'>Item Name</th>
                                <th align="left" width='15%'>Size</th>
                                <th align="left" width='10%'>Hsncode</th>
                                <th align="left" width='8%' style="text-align: right;">Opening<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <th align="left" width='8%' 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='8%' 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='8%' style="text-align: right;">Balance<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <?php if ($prnval == 2) { ?>
                                    <?php if ($stockval == "M") { ?>

                                        <th style="text-align: right;">MRP<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th style="text-align: right;">Value<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <?php } else { ?>
                                        <th style="text-align: right;">Cost<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                        <th style="text-align: right;">Value<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <?php } ?>
                                <?php } ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $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->code;
                                $ibarcode = $row->code;
                                $sizee = $row->size;
                                $hsncode = $row->hsncode;


                                //                                $getcolr = mysqli_query($conn, "SELECT * FROM `color` where `code` = '$colrcode'") or die(mysqli_error($conn));
                                //                                $fetcolr = mysqli_fetch_array($getcolr);
                                //                                $getitnmgd = mysqli_query($conn, "SELECT * FROM `item` where `code` = '$icode' $gcond") or die(mysqli_error($conn));
                                //                                print "SELECT * FROM `item` where `code` = '$icode' $gcond";
                                //                                $nmcnt = mysqli_num_rows($getitnmgd);
                                //                                if ($nmcnt == 0) {
                                //                                    browse("Record Not Found");
                                //                                    echo "<script>
                                //                                               window.close();
                                //                                         </script>";
                                //                                }
                                //                                $mopen = $row->opening;
                                $itemname = $row->name;
                                $cost = $row->cost;
                                $mrp = $row->mrp;
                                if ($cost > 0) {
                                    $actcost = $cost;
                                } else {
                                    $getpur = mysqli_query($conn, "SELECT `rate`,mrp FROM `pgsttran` WHERE `itemcode`='$icode' and size='$sizee' and rate > 0 
                                                                   and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'") or die(mysqli_error($conn));
                                    $chkpurrate = mysqli_fetch_array($getpur);
                                    $actcost = $chkpurrate['rate'];
                                }
                                $newcost = $actcost;

                                if ($mrp > 0) {
                                    $actmrp = $mrp;
                                } else {
                                    //                                    $ggetpur = mysqli_query($conn, "SELECT `mrp` FROM `purtran` WHERE `itemcode`='$icode' and rate > 0 
                                    //                                                           and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
                                    //                                    $gchkpurrate = mysqli_fetch_array($ggetpur);
                                    $actmrp = $chkpurrate['mrp'];
                                }
                                $newmrp = $actmrp;

                                //                                if ($actcost > 0) {
                                //                                    $newcost = $actcost;
                                //                                } else {
                                //                                    if (!empty($prevyear)) {
                                //                                        $getpurold = mysqli_query($conn, "SELECT `rate` FROM $olddb.`purtran` WHERE `itemcode`='$icode' and `rate` > 0 ")or die(mysqli_error($conn));
                                //                                        $tnum = mysqli_fetch_array($getpurold);
                                //                                        $newcost = $tnum[rate];
                                //                                    }
                                //                                }

                                $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode' and color = '$colrcode' 
                                                                and size='$sizee' and `vouchdt` < '$mfrmdate' ") 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  itemcode = '$icode' and color = '$colrcode'
                                                               and size='$sizee' and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0") 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 itemcode = '$icode' and color = '$colrcode'  
                                                                and size='$sizee' and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0") or die(mysqli_error($conn));
                                $isale = mysqli_fetch_array($getsale);
                                $saleqty1 = $isale['sqty'];


                                $saleqty = $saleqty1 * -1;

                                $balqty = $openqty + $purqty - $saleqty;
                                if ($stockval == 'M') {
                                    $itemvalue = $newmrp * $balqty;
                                } else {
                                    $itemvalue = $newcost * $balqty;
                                }

                                $show = 1;

                                if ($zerostk == 2) {
                                    $show = 1;
                                } else {
                                    if ($balqty == 0) {
                                        $show = 0;
                                    }
                                }
                                if ($show == 1) {

                                    $ctr++;
                                    echo "<tr> ";
                                    echo "<td align='left'>$ctr</td>";
                                    //                                echo "<td align='left'>$ibarcode</td>";
                                    echo "<td align='left'>$itemname</td>";
                                    echo "<td align='left'>$sizee</td>";
                                    echo "<td align='left'>$hsncode</td>";
                                    echo "<td align='right'>$openqty</td>";
                                    echo "<td align='right'>$purqty</td>";
                                    echo "<td align='right'>$saleqty</td>";
                                    echo "<td align='right'>$balqty</td>";
                                    if ($prnval == 2) {
                                        if ($stockval == 'M') {
                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$newmrp", 0, ".", ",") . "</b>";
                                            echo "</td>";

                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$itemvalue", 0, ".", ",") . "</b>";
                                            echo "</td>";
                                        } else {
                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$newcost", 0, ".", ",") . "</b>";
                                            echo "</td>";

                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$itemvalue", 0, ".", ",") . "</b>";
                                            echo "</td>";
                                        }
                                    }
                                    echo "</tr>";
                                    $gropen = $gropen + $openqty;
                                    $grsale = $grsale + $saleqty;
                                    $grpur = $grpur + $purqty;
                                    $grbal = $grbal + $balqty;
                                    $grval = $grval + $itemvalue;
                                }
                            }
                            echo "<tr style ='background-color:white;'> ";
                            //                            echo "<td align='left'></td>";
                            echo "<td align='left'></td>";
                            echo "<td align='left'></td>";
                            echo "<td align='left'></td>";
                            echo "<td align='left'><font size='2px'> Total</font></td>";
                            echo "<td align='right'>$gropen</td>";
                            echo "<td align='right'>$grpur</td>";
                            echo "<td align='right'>$grsale</font></td>";
                            echo "<td align='right'>$grbal</td>";
                            if ($prnval == 2) {
                                echo "<td align='right'>";
                                echo "<font><b></b>";
                                echo "</td>";
                                echo "<td align='right'>";
                                echo "<font><b> " . $grval . "</b>";
                                echo "</td>";
                            }
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>


    </body>

    </html>
<?php
}
if (isset($_POST["excel"])) {


    $contents .= "Sr., Item Name, Size, Hsncode, Opening, Add, Less, Balance\n";

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

    $ctr = 0;
    mysqli_select_db($conn, $zdb);
    while ($row = mysqli_fetch_object($getitem)) {
        $icode = $row->itemcode;
        $ibarcode = $row->barcode;
        $mopen = $row->opening;
        $iname = $row->name;
        $color = $row->color;
        $sizee = $row->size;
        $hsncode = $row->hsncode;

        $getcolor = mysqli_query($conn, "SELECT * FROM `color` where code = $color") or die(mysqli_error($conn));
        $ncolor = mysqli_fetch_array($getcolor);

        $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode'  
                                                             and `vouchdt` < '$mfrmdate'") 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  itemcode = '$icode' and
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0") 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 itemcode = '$icode' and
                                                          `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0") or die(mysqli_error($conn));
        $isale = mysqli_fetch_array($getsale);
        $saleqty1 = $isale['sqty'];

        $getopen = mysqli_query($conn, "SELECT sum(qty) as sqty FROM `$trandb` WHERE itemcode = '$icode' and color = '$colrcode' 
        and size='$sizee' and `vouchdt` < '$mfrmdate' ") 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  itemcode = '$icode' and color = '$colrcode'
               and size='$sizee' and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0") 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 itemcode = '$icode' and color = '$colrcode'  
                and size='$sizee' and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0") or die(mysqli_error($conn));
        $isale = mysqli_fetch_array($getsale);
        $saleqty1 = $isale['sqty'];

        $saleqty = $saleqty1 * -1;

        $balqty = $openqty + $purqty - $saleqty;

        $show = 1;

        if ($zerostk == 2) {
            $show = 1;
        } else {
            if ($balqty == 0) {
                $show = 0;
            }
        }
        if ($show == 1) {

            $ctr++;
            $contents .= "$ctr" . ",";
            $contents .= "$iname" . ",";
            $contents .= "$sizee" . ",";
            $contents .= "$hsncode" . ",";
            $contents .= "$openqty" . ",";
            $contents .= "$purqty" . ",";
            $contents .= "$saleqty" . ",";
            $contents .= "$balqty" . "\n";

            $gropen = $gropen + $openqty;
            $grsale = $grsale + $saleqty;
            $grpur = $grpur + $purqty;
            $grbal = $grbal + $balqty;
            $itemvalue = $cost * $balqty;
            $grval = $grval + $itemvalue;
        }
    }
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= "$gropen" . ",";
    $contents .= "$grpur" . ",";
    $contents .= "$grsale" . ",";
    $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;
    }
}
?>