<?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 = 'Raw Material Stock';

$maindb = 'rawmat';
$trandb = 'rawsacn';

$grid = 'rawstock.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;

$gbrcd = trim($_POST["itemcode"]);
$gnm = $_POST["name"];
$gtype = trim($_POST["type"]);
$prnval = trim($_POST["prnval"]);
$stockval = trim($_POST["stockval"]);
$color = trim($_POST["color"]);

$typecond = "";
$brcond = "";
$ncond = "";
$gcond = "";
$colorcond = "";

if (!empty($gtype)) {
    $typecond = " and b.type = '$gtype' ";
}
//browse(1);

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

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

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

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, a.color, b.cost, b.type FROM `$trandb` a, `$maindb` b WHERE a.itemcode = b.code 
                          $ncond $brcond $typecond $colorcond group by a.itemcode") or die(mysqli_error($conn));
$getno = mysqli_num_rows($getitem);
//print table($getitem);
//browse(1);

$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; 
        }
        .yd{
            background-color: #E3F2E1;
        }
        .mt{
            background-color: #F7F78B;
        }
        .qt{
            background-color: #F7C6C5;
        }
    </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='12%' colspan="4"></th>
                                <th align="left" colspan="4" style="text-align: center;">Meters<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <th align="left" colspan="4" style="text-align: center;">Yard<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <th align="left" colspan="4" style="text-align: center;">Qty<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>

                            </tr>
                            <tr>
                                <th align="left" width='3%' style="font-family: arial;">Sr.</th>
                                <th align="left" width='5%'>Barcode</th>
                                <th align="left" width='25%'>Item Name</th>
                                <!--<th align="left" width='12%'>Color</th>-->
                                <th align="left" width='3%'>Type</th>
                                <!--for yard-->
                                <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>
                                <!--for meters-->

                                <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>
                                <!--for qty-->

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

                            mysqli_query($conn, "drop temporary table if exists opentmp") or die(mysqli_error($conn));
                            mysqli_query($conn, "CREATE TEMPORARY TABLE opentmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE `vouchdt` < '$mfrmdate' 
                                                               group by itemcode") or die(mysqli_error($conn));

                            mysqli_query($conn, "drop temporary table if exists purtmp") or die(mysqli_error($conn));
                            mysqli_query($conn, "CREATE TEMPORARY TABLE purtmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE 
                                                              `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and (qty > 0 or yard > 0 or meters > 0)
                                                               group by itemcode") or die(mysqli_error($conn));

                            mysqli_query($conn, "drop temporary table if exists saltmp") or die(mysqli_error($conn));
                            mysqli_query($conn, "CREATE TEMPORARY TABLE saltmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE 
                                                              `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and (qty < 0 or yard < 0 or meters < 0)
                                                               group by itemcode") or die(mysqli_error($conn));
                            $getsale = mysqli_query($conn, "SELECT *  FROM `saltmp` ") or die(mysqli_error($conn));
                            
                            while ($row = mysqli_fetch_object($getitem)) {

                                $icode = $row->code;
                                $ibarcode = $row->code;
                                $colrcode = $row->color;
                                $type = $row->type;

                                $getcolr = mysqli_query($conn, "SELECT * FROM `color` where `code` = '$colrcode'") or die(mysqli_error($conn));
                                $fetcolr = mysqli_fetch_array($getcolr);
                                $itemname = $row->name;
                                $cost = $row->cost;
                                $mrp = $row->mrp;

                                $ggetpur = mysqli_query($conn, "SELECT `mrp`, rate FROM `rawtran` WHERE `item`='$icode' and rate > 0 
                                                           and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
                                $gchkpurrate = mysqli_fetch_array($ggetpur);

                                if ($cost > 0) {
                                    $actcost = $cost;
                                } else {
                                    $actcost = $gchkpurrate['rate'];
                                }
                                $newcost = $actcost;

                                if ($mrp > 0) {
                                    $actmrp = $mrp;
                                } else {
                                    $actmrp = $gchkpurrate['mrp'];
                                }
                                $newmrp = $actmrp;

                                $getopen = mysqli_query($conn, "SELECT sqty,meters,yard FROM `opentmp` WHERE itemcode = '$icode' ") or die(mysqli_error($conn));
                                $iopen = mysqli_fetch_array($getopen);
                                $openyard = $iopen['yard'];
                                $openqty = $iopen['sqty'];
                                $openmeter = $iopen['meters'];

                                $getpur = mysqli_query($conn, "SELECT sqty,meters,yard FROM `purtmp` WHERE  itemcode = '$icode' ") or die(mysqli_error($conn));
                                $ipur = mysqli_fetch_array($getpur);
                                $puryard = $ipur['yard'];
                                $purqty = $ipur['sqty'];
                                $purmeter = $ipur['meters'];

                                $getsale = mysqli_query($conn, "SELECT sqty,meters,yard  FROM `saltmp` WHERE itemcode = '$icode' ") or die(mysqli_error($conn));
                                $isale = mysqli_fetch_array($getsale);
                                $saleyard1 = $isale['yard'];
                                $saleqty1 = $isale['sqty'];
                                $salemeter1 = $isale['meters'];

                                $saleyard = $saleyard1 * -1;
                                $saleqty = $saleqty1 * -1;
                                $salemeter = $salemeter1 * -1;

//                                if ($type == 'F') {
                                $balmet = $openmeter + $purmeter - $salemeter;
                                $balqty = $openqty + $purqty - $saleqty;
                                $balyard = $openyard + $puryard - $saleyard;
//                                } else {
//                                }

                                if ($stockval == 'M') {
                                    $itemvalue = $newmrp * $balqty;
                                } else {
                                    $itemvalue = $newcost * $balqty;
                                }
                                $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'>$fetcolr[name]</td>";
                                echo "<td align='right'>$type</td>";
//                                if ($type == 'F') {
//                                yard    
                                
//                                    meters
                                echo "<td align='right' class='mt'>$openmeter</td>";
                                echo "<td align='right' class='mt'>$purmeter</td>";
                                echo "<td align='right' class='mt'>$salemeter</td>";
                                echo "<td align='right' class='mt'>$balmet</td>";
                                
                                echo "<td align='right' class='yd'>$openyard</td>";
                                echo "<td align='right' class='yd'>$puryard</td>";
                                echo "<td align='right' class='yd'>$saleyard</td>";
                                echo "<td align='right' class='yd'>$balyard</td>";


                                $grydopen = $grydopen + $openyard;
                                $grydsale = $grydsale + $saleyard;
                                $grydpur = $grydpur + $puryard;
                                $grydbal = $grydbal + $balyard;

                                $grmtopen = $grmtopen + $openmeter;
                                $grmtsale = $grmtsale + $salemeter;
                                $grmtpur = $grmtpur + $purmeter;
                                $grmtbal = $grmtbal + $balmet;
//                                } else {
//                                    qty
                                echo "<td align='right' class='qt'>$openqty</td>";
                                echo "<td align='right' class='qt'>$purqty</td>";
                                echo "<td align='right' class='qt'>$saleqty</td>";
                                echo "<td align='right' class='qt'>$balqty</td>";

                                $gropen = $gropen + $openqty;
                                $grsale = $grsale + $saleqty;
                                $grpur = $grpur + $purqty;
                                $grbalq = $grbalq + $balqty;
//                                }

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

                                $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'><font size='2px'> Total</font></td>";
                            echo "<td align='right'></td>";
//                            if ($type == 'F') {
                            
                               echo "<td align='right'>$grmtopen</td>";
                                echo "<td align='right'>$grmtpur</td>";
                                echo "<td align='right'>$grmtsale</font></td>";
                                echo "<td align='right'>$grmtbal</font></td>";
                                
                                
                                echo "<td align='right'>$grydopen</td>";
                                echo "<td align='right'>$grydpur</td>";
                                echo "<td align='right'>$grydsale</font></td>";
                                echo "<td align='right'>$grydbal</font></td>";
                                
                             
                                
                                
//                            } else {
                                echo "<td align='right'>$gropen</td>";
                                echo "<td align='right'>$grpur</td>";
                                echo "<td align='right'>$grsale</font></td>";
                                echo "<td align='right'>$grbalq</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.,Barcode,Item Name,Color,Type,Opening,Add,Less,Balance\n";

    if ($prnval == 2) {
        if ($stockval == "M") {
            $contents .= "MRP,Value\n";
        } else {
            $contents .= "Cost,Value\n";
        }
    }

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

    $ctr = 0;
    mysqli_select_db($conn, $zdb);

    mysqli_query($conn, "drop temporary table if exists opentmp") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE opentmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE `vouchdt` < '$mfrmdate' 
                                                               group by itemcode, color ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists purtmp") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE purtmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE 
                                                              `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty > 0
                                                               group by itemcode, color ") or die(mysqli_error($conn));

    mysqli_query($conn, "drop temporary table if exists saltmp") or die(mysqli_error($conn));
    mysqli_query($conn, "CREATE TEMPORARY TABLE saltmp
                                                        SELECT sum(qty) as sqty, sum(yard) as yard, sum(meters) as meters, itemcode, color FROM `$trandb` WHERE 
                                                              `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' and qty < 0
                                                               group by itemcode, color ") or die(mysqli_error($conn));


    while ($row = mysqli_fetch_object($getitem)) {

        $icode = $row->code;
        $ibarcode = $row->code;
        $colrcode = $row->color;


        $getcolr = mysqli_query($conn, "SELECT * FROM `color` where `code` = '$colrcode'") or die(mysqli_error($conn));
        $fetcolr = mysqli_fetch_array($getcolr);
        $itemname = $row->name;
        $cost = $row->cost;
        $mrp = $row->mrp;

        $ggetpur = mysqli_query($conn, "SELECT `mrp`, rate FROM `rawtran` WHERE `item`='$icode' and rate > 0 
                                                           and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
        $gchkpurrate = mysqli_fetch_array($ggetpur);

        if ($cost > 0) {
            $actcost = $cost;
        } else {
            $actcost = $gchkpurrate['rate'];
        }
        $newcost = $actcost;

        if ($mrp > 0) {
            $actmrp = $mrp;
        } else {
            $actmrp = $gchkpurrate['mrp'];
        }
        $newmrp = $actmrp;

        $getopen = mysqli_query($conn, "SELECT sqty FROM `opentmp` WHERE itemcode = '$icode' and color = '$colrcode' ") or die(mysqli_error($conn));
        $iopen = mysqli_fetch_array($getopen);
        $openqty = $iopen['sqty'];
        $openmeter = $iopen['meter'];

        $getpur = mysqli_query($conn, "SELECT sqty FROM `purtmp` WHERE  itemcode = '$icode' and color = '$colrcode' ") or die(mysqli_error($conn));
        $ipur = mysqli_fetch_array($getpur);
        $purqty = $ipur['sqty'];
        $purmeter = $ipur['meters'];

        $getsale = mysqli_query($conn, "SELECT sqty FROM `saltmp` WHERE itemcode = '$icode' and color = '$colrcode' ") or die(mysqli_error($conn));
        $isale = mysqli_fetch_array($getsale);
        $saleqty1 = $isale['sqty'];
        $salemeter1 = $isale['meters'];

        $saleqty = $saleqty1 * -1;
        $salemeter = $salemeter1 * -1;

        if ($type == 'F') {
            $balqty = $openmeter + $purmeter - $salemeter;
        } else {
            $balqty = $openqty + $purqty - $saleqty;
        }

        if ($stockval == 'M') {
            $itemvalue = $newmrp * $balqty;
        } else {
            $itemvalue = $newcost * $balqty;
        }
        $ctr++;
        $contents .= "$ctr" . ",";
        $contents .= "$ibarcode" . ",";
        $contents .= "$itemname" . ",";
        $contents .= "$fetcolr[name]" . ",";
        $contents .= "$type" . ",";

        if ($type == 'F') {
            $contents .= "$openmeter" . ",";
            $contents .= "$purmeter" . ",";
            $contents .= "$salemeter" . ",";

            $grmtopen = $grmtopen + $openmeter;
            $grmtsale = $grmtsale + $salemeter;
            $grmtpur = $grmtpur + $purmeter;
        } else {
            $contents .= "$openqty" . ",";
            $contents .= "$purqty" . ",";
            $contents .= "$saleqty" . ",";

            $gropen = $gropen + $openqty;
            $grsale = $grsale + $saleqty;
            $grpur = $grpur + $purqty;
        }
        $contents .= "$balqty" . ",";

        if ($prnval == 2) {
            if ($stockval == 'M') {
                $contents .= "$newmrp" . ",";
                $contents .= "$itemvalue" . ",";
            } else {
                $contents .= "$newcost" . ",";
                $contents .= "$itemvalue" . ",";
            }
        }

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

        $grbal = $grbal + $balqty;
        $grval = $grval + $itemvalue;
    }
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "" . ",";

    if ($type == 'F') {
        $contents .= "$grmtopen" . ",";
        $contents .= "$grmtpur" . ",";
        $contents .= "$grmtsale" . ",";
    } else {
        $contents .= "$gropen" . ",";
        $contents .= "$grpur" . ",";
        $contents .= "$grsale" . ",";
    }
    $contents .= "$grbal" . ",";

    if ($prnval == 2) {
        $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;
    }
}
?>