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

ini_set('display_errors', 1);
error_reporting(E_ERROR);

$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"];
$size = trim($_POST["size"]);
$grpname = trim($_POST["itemgrp"]);

$prnval = trim($_POST["prnval"]);
$stockval = trim($_POST["stockval"]);
$zerostk = trim($_POST["zerostk"]);

$ncond = "";
$gcond = "";
$sizecond = "";

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

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

if (!empty($_POST["itemgrp"])) {
    $gcond = " and b.`itemgrp` = '$grpname' ";
}

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 maintmp1 ") or die(mysqli_error($conn));
// print "
//                             SELECT a.itemcode, a.size, a.qty, b.name, b.itemgrp, b.hsncode 
//                                        FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code and
//                                        a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $ncond $gcond $sizecond 
//                             union all
//                             SELECT a.itemcode, a.size, a.qty, b.name, b.itemgrp, b.hsncode 
//                                        FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code and source = 'OP' 
//                                        $ncond $gcond $sizecond ";
mysqli_query($conn, "create temporary table maintmp1 
                            SELECT a.itemcode, a.size, a.qty, b.name, b.itemgrp, b.hsncode 
                                       FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code and
                                       a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $ncond $gcond $sizecond 
                            union all
                            SELECT a.itemcode, a.size, a.qty, b.name, b.itemgrp, b.hsncode 
                                       FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code and source = 'OP' 
                                       $ncond $gcond $sizecond ") or die(mysqli_error($conn));


mysqli_query($conn, "drop temporary table if exists maintmp ") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table maintmp 
                            SELECT *, sum(qty) as balqty FROM `maintmp1` group by itemcode, size ") or die(mysqli_error($conn));

$getitem = mysqli_query($conn, "SELECT a.*, b.cost, b.rate FROM `maintmp` a left join `prodsize` b 
                                       on a.itemcode = b.code and a.size = b.size
                                       group by a.itemcode, a.size
                                       order by a.name+0") or die(mysqli_error($conn));

$getno = mysqli_num_rows($getitem);

// print table($getitem);
// die();

$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;">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 == "R") { ?>
                                        <th style="text-align: right;">Rate<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>
                                    <?php } ?>
                                    <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 } ?>
                            </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;
                                $balqty = $row->balqty;

                                $itemname = $row->name;
                                $cost = $row->cost;
                                $rate = $row->rate;

                                $ratecost = 0;
                                if ($stockval == 'R') {
                                    $ratecost = $rate;
                                } else {
                                    $ratecost = $cost;
                                }
                                $itemvalue = $ratecost * $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'>$itemname</td>";
                                    echo "<td align='left'>$sizee</td>";
                                    echo "<td align='left'>$hsncode</td>";
                                    echo "<td align='right'>$balqty</td>";
                                    if ($prnval == 2) {
                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format($ratecost, 0) . "</b>";
                                        echo "</td>";

                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format($itemvalue, 0) . "</b>";
                                        echo "</td>";
                                    }
                                    echo "</tr>";
                                    $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'>$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>";

                                mysqli_query($conn, "update profit  set prodstk = '$grval' ") or die(mysqli_error($conn));

                                $getstk = mysqli_query($conn, "SELECT * FROM `profit` ") or die(mysqli_error($conn));
                                $fetstk = mysqli_fetch_array($getstk);
                                $totstk = $fetstk['prodstk'] + $fetstk['rawstk'];
                                if ($totstk > 0) {
                                    $stkdc = "D";
                                } else {
                                    $stkdc = "C";
                                }
                                mysqli_query($conn, "update accmast set currbal = '$totstk', currdc = '$stkdc' WHERE accode = 23 ") or die(mysqli_error($conn));
                            }
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </body>

    </html>
    <?php
}

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

    $contents .= "Sr, Item Name, Size, Hsncode, Balance,";
    if ($prnval == 2) {
        if ($stockval == "R") {
            $contents .= "Rate,";
        } else {
            $contents .= "Cost,";
        }
        $contents .= "Value";
    }
    $contents .= "\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->code;
        $ibarcode = $row->code;
        $sizee = $row->size;
        $hsncode = $row->hsncode;
        $balqty = $row->balqty;

        $itemname = $row->name;
        $cost = $row->cost;
        $rate = $row->rate;

        if ($stockval == 'R') {
            $ratecost = $rate;
        } else {
            $ratecost = $cost;
        }
        $itemvalue = $ratecost * $balqty;

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

        if ($show == 1) {
            $ctr++;
            $contents .= "$ctr" . ",";
            $contents .= "$itemname" . ",";
            $contents .= "$sizee" . ",";
            $contents .= "$hsncode" . ",";
            $contents .= "$balqty" . ",";
            if ($prnval == 2) {
                $contents .= "$ratecost" . ",";
                $contents .= "$itemvalue" . ",";
            }
            $contents .= "" . "\n";
            $grbal = $grbal + $balqty;
            $grval = $grval + $itemvalue;
        }
    }
    $contents .= "" . ",,,";
    $contents .= "Total" . ",";
    $contents .= "$grbal" . ",";
    if ($prnval == 2) {
        $contents .= "" . ",";
        $contents .= "$grval" . ",";
    }
    $contents .= "" . "\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;
    }
}
?>