<?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';
$zroot = $_SESSION["zdbroot"];

$maindb = 'product';
$trandb = 'transacn';

$grid = 'stock.php';




if (isset($_POST["submit"])) {
    $count = $_POST["items"];
    for ($i = 0; $i <= $count; $i++) {
        $mbarcode = $_POST["barcode_$i"];
        $mgodown = $_POST["godown_$i"];
        $mqty = $_POST["balqty_$i"];
        $tqty = $mqty * -1;

        $geti = mysqli_query($conn, "SELECT code,prodgrp FROM `product` WHERE barcode = '$mbarcode' ") or die(mysqli_error($conn));
        $feti = mysqli_fetch_array($geti);
        $itcode = $feti['code'];
        $itemgrp = $feti['prodgrp'];
        if ($mqty > 0) {

            mysqli_query($conn, "INSERT INTO `$trandb`(`srno`, `yearno`, `vouchno`, `vouchdt`, `itemcode`,  `cartqty`,godown,barcode,
                                                  `qty`, `source`, amount, accode, `group` , `totqty`, `cost`, `itemgrp`, `ordno`, `monno`)
                            VALUES('$cnt', '$myyear', '', '2023-07-24', '$itcode',  '$tcartqty','$mgodown','$mbarcode',
                                   '$tqty', 'OUAD', '$amt', '$party', '$group', '$ttotqty', '$cost', '$itemgrp', '1', '$mono')") or die(mysqli_error($conn));
        } else {
            mysqli_query($conn, "INSERT INTO `$trandb`(`srno`, `yearno`, `vouchno`, `vouchdt`, `itemcode`,  `cartqty`,godown,barcode,
                                                  `qty`, `source`, amount, accode, `group` , `totqty`, `cost`, `itemgrp`, `ordno`, `monno`)
                            VALUES('$cnt', '$myyear', '', '2023-07-24', '$itcode',  '$tcartqty','$mgodown','$mbarcode',
                                   '$tqty', 'INAD', '$amt', '$party', '$group', '$ttotqty', '$cost', '$itemgrp', '2', '$mono')") or die(mysqli_error($conn));
        }
    }
    browse("Stock Updated Successfully");
    echo "<script>"
    . "window.close();"
            . "</script>";
}


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

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


    $giname = trim($_POST["itemname"]);
    $gbrcd = trim($_POST["itemcode"]);
    $gnm = $_POST["name"];
    $gname = trim($_POST["itemgrp"]);
    $gprodtype = trim($_POST["prodtype"]);
    $gsubgrp = trim($_POST["subgrp"]);
    $prnval = trim($_POST["prnval"]);
    $gperc = trim($_POST["gperc"]);

    $prnzero = trim($_POST["prnzero"]);


    $brcond = "";
    $ncond = "";
    $gcond = "";
    $subcond = "";
    $gperccond = "";
    $gprodcond = "";

    if (!empty($_POST["itemcode"])) {
        $brcond = " and `barcode` = '$gbrcd' ";
    }

    $itcond = "";
    if (!empty($_POST["itemname"])) {
        $itcond = " and `name` like '%$giname%' ";
    }

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

    $godowncond = "";
    if (!empty($_POST["godown"])) {
        $godowncond = " and a.`godown` = '$godown' ";
    }

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

    if (!empty($_POST["itemgrp"])) {
        $gcond = " and `prodgrp` = '$gname' ";
    }

//    if (!empty($_POST["prodtype"])) {
//        $gprodcond = " and `prodtype` = '$gprodtype' ";
//    }
//
//    if (!empty($_POST["subgrp"])) {
//        $subcond = " and `itemsubgrp` = '$gsubgrp' ";
//    }
//
//    if (!empty($_POST["gsttype"])) {
//        $gperccond = " and `gstperc` = '$gperc' ";
//    }

    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 proddbf ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table proddbf
                            SELECT * FROM `$maindb` WHERE code != '' $ncond $brcond $subcond $itcond $gcond ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists stkdbf ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table stkdbf
                            SELECT sum(a.qty) as sqty, a.godown, a.barcode, b.name, b.cost FROM $trandb a, proddbf b 
                                   WHERE a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.qty > 0 
                                   and a.barcode = b.barcode  $godowncond group by a.godown, a.barcode") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists stkdbfles ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table stkdbfles
                            SELECT sum(a.qty) as sqty, a.godown, a.barcode, b.name, b.cost FROM $trandb a, proddbf b 
                                   WHERE a.vouchdt >= '$mfrmdate' AND a.vouchdt <= '$mtodate' and a.qty < 0
                                   and a.barcode = b.barcode  $godowncond group by a.godown, a.barcode") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists stkopen ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table stkopen
                            SELECT sum(a.qty) as oqty, a.godown, a.barcode, b.name, b.cost FROM $trandb a, proddbf b 
                                   WHERE a.vouchdt < '$mfrmdate' and a.barcode = b.barcode $godowncond  group by a.godown, a.barcode ") or die(mysqli_error($conn));

//$getitem = mysqli_query($conn, "SELECT * FROM `stkdbf` group by name, godown ") or die(mysqli_error($conn));
    $getitem = mysqli_query($conn, "SELECT a.* from (SELECT * FROM `stkdbf` 
                                                 union  
                                                 Select * from stkopen )  a group by name, godown ") 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>";
    }
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        .table-condensed > thead > tr > th, .table-condensed > tbody > tr > th, .table-condensed > tfoot > tr > th, .table-condensed > thead > tr > td, .table-condensed > tbody > tr > td, .table-condensed > tfoot > tr > td {
            padding: 2px;
        } 
    </style>

    </head>
    <body>
        <form action='' method='POST'>

            <div class="container">
                <?php
                include("includes/head_rep.php");
                ?>
                <input type="submit" name="submit" value="Update Stock"> 

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

                                    <th width='3%' style="font-family: arial;">Sr.</th>
                                    <th width='12%'>Barcode</th>
                                    <th width='25%'>Item Name</th>
                                    <th width='7%'>Godown</th>
                                    <th width='7%' style="text-align: right;">Opening</th>
                                    <th width='7%' style="text-align: right;">Add</th>
                                    <th width='7%' style="text-align: right;">Less</th>
                                    <th width='9%' style="text-align: right;">Balance</th>
                                    <?php if ($prnval == 2) { ?>
                                        <th width='9%' style="text-align: right;">Cost</th>
                                        <th width='9%' style="text-align: right;">Value</th>
                                        <?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)) {

                                    $ibarcode = $row->barcode;
                                    $itemname = $row->name;
                                    $igodown = $row->godown;
                                    $cost = $row->cost;

                                    if ($cost > 0) {
                                        $actcost = $cost;
                                    } else {
                                        $getcost = mysqli_query($conn, "SELECT `rate` FROM `purtran` WHERE `barcode`='$ibarcode' and rate > 0 
                                                           and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' order by vouchdt desc limit 0,1")or die(mysqli_error($conn));
                                        $chkpurrate = mysqli_fetch_array($getcost);
                                        $actcost = $chkpurrate['rate'];
                                    }
                                    $newcost = $actcost;

                                    $getopen = mysqli_query($conn, "SELECT sum(oqty) as oqty FROM `stkopen` WHERE barcode = '$ibarcode'  
                                                                      and godown = '$igodown'  ") or die(mysqli_error($conn));
                                    $iopen = mysqli_fetch_array($getopen);
                                    $openqty = $iopen['oqty'];

                                    $getpur = mysqli_query($conn, "SELECT sum(sqty) as aqty FROM `stkdbf` WHERE  barcode = '$ibarcode' 
                                                                      and godown = '$igodown' ") or die(mysqli_error($conn));
                                    $ipur = mysqli_fetch_array($getpur);
                                    $purqty = $ipur['aqty'];


                                    $getsale = mysqli_query($conn, "SELECT sum(sqty) as lqty FROM `stkdbfles` WHERE barcode = '$ibarcode' 
                                                                       and godown = '$igodown' ") or die(mysqli_error($conn));
                                    $isale = mysqli_fetch_array($getsale);
                                    $saleqty1 = $isale['lqty'];

                                    $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') {
                                        $getg = mysqli_query($conn, "SELECT gcode FROM `godown` WHERE code = '$igodown' ") or die(mysqli_error($conn));
                                        $fetg = mysqli_fetch_array($getg);
                                        $godwname = $fetg['gcode'];


//                                if ($balqty != 0) {
                                        $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'>$godwname</td>";
                                        echo "<td align='right'>$openqty</td>";
                                        echo "<td align='right'>$purqty</td>";
                                        echo "<td align='right'>$saleqty</td>";
//                                    echo "<td align='right'>" . number_format($balqty, 0, ".", ",") . "</td>";
                                        echo "<td align='right'>$balqty"
                                        . "<input type='hidden' name='balqty_$ctr' value='$balqty'>"
                                        . "<input type='hidden' name='barcode_$ctr' value='$ibarcode'>"
                                        . "<input type='hidden' name='godown_$ctr' value='$igodown'>"
                                        . "</td>";
                                        if ($prnval == 2) {

                                            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;font-weight:bolder;font-size:18px'> ";
                                echo "<td align='left'><input type='hidden' name='items' value='$ctr'></td>";
                                echo "<td align='left'></td>";
                                echo "<td align='left'></td>";
                                echo "<td align='left'><font size='4px'> 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'>" . number_format("$grbal", 2, ".", ",") . "</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>

        </form>

    </body>
    </html>
    <?php
}
?>