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

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);
$maindb = 'rawsacn';
$zroot = $_SESSION["zdbroot"];
$formname = 'Raw Material Stock ';
$file = 'stkrawmat.php';


if (isset($_POST["view"])) {
    $item = $_POST["item"];
   
    $itemcond = '';

    if (!empty($item)) {
        $itemcond = " AND `itemcode` = '$item' ";
    }

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

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

    $mainqry = mysqli_query($conn, "SELECT vouchno, itemcode, itemname, qty, rate, unit,DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt
                            FROM `$maindb` WHERE `vouchno` != '' $itemcond group by itemcode")or die(mysqli_error($conn));

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #26A69A;
        }
        table td{
            font-size:14px;
        }
        .tsize{
            font-size:18px;
            padding : 6px;
        }
    </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-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:5%;">Sr</th>
                                <th style="width:25%;">Item Name</th>
                                <th style="width:7%;" class="">Unit</th>
                                <th style="width:7%;" class='text-right'>Bal. Qty</th>
                                <th style="width:7%;" class="text-right">Cost</th>
                                <th style="width:9%;" class="text-right">Balance Value</th>
                                <th style="width:7%;background: #6082BF" class="text-right">Opening</th>
                                <th style="width:7%;background: #6082BF" class="text-right">Purchase</th>
                                <th style="width:10%;background:#6082BF" class="text-right">Purchase Return</th>
                                <th style="width:8%;background: #6082BF" class="text-right">Production</th>
                                <th style="width:7%;background: #6082BF" class="text-right">Damaged</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totinwd = 0;
                            $totopen = 0;
                            $totout = 0;
                            $balqty = 0;
                            $totbalqty = 0;
                            $totbalval = 0;
                            while ($fmain = mysqli_fetch_array($mainqry)) {

                                $itemcode = $fmain['itemcode'];
                                
                                $gitem = mysqli_query($conn, "SELECT `code`, `name` FROM `item` WHERE `code`='$itemcode'")or die(mysqli_error($conn));
                                $item = mysqli_fetch_array($gitem);

                                $gopen = mysqli_query($conn, "SELECT `itemcode`, SUM(`qty`) as `qty`  FROM `rawsacn` WHERE `itemcode`='$itemcode' 
                                                      and `source` = 'OP' ")or die(mysqli_error($conn));
                                $opening = mysqli_fetch_array($gopen);

                                $ginwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode' AND `source` = 'PURW'")or die(mysqli_error($conn));
                                $inward = mysqli_fetch_array($ginwd);

                                $goutw = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode' AND `source` = 'PRRW'")or die(mysqli_error($conn));
                                $outward = mysqli_fetch_array($goutw);

                                $gdmg = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode' AND `source` = 'DM'")or die(mysqli_error($conn));
                                $damaged = mysqli_fetch_array($gdmg);

                                $gused = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode' AND `source` = 'BMR'")or die(mysqli_error($conn));
                                $fused = mysqli_fetch_array($gused);

                                if ($opening['qty'] != '') {
                                    $open = $opening['qty'];
                                } else {
                                    $open = 0;
                                }

                                if ($damaged['qty'] != '') {
                                    $dmged = $damaged['qty'];
                                } else {
                                    $dmged = 0;
                                }

                                if ($outward['qty'] != '') {
                                    $out = $outward['qty'];
                                } else {
                                    $out = 0;
                                }

                                if ($fused['qty'] != '') {
                                    $used = $fused['qty'];
                                } else {
                                    $used = 0;
                                }

                                if ($inward['qty'] != '') {
                                    $inwd = $inward['qty'];
                                } else {
                                    $inwd = 0;
                                }



                                $balqty = $open + $inwd - $out - $used - $dmged;
                                $balvalue = $balqty * $fmain['rate'];


                                $ctr++;
                                Echo"<tr>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$item[name]</td>";
                                Echo"<td class=''>$fmain[unit]</td>";
                                Echo"<td class='text-right'><b>$balqty </b></td>";
                                Echo"<td class='text-right'>$fmain[rate]</td>";
                                Echo"<td class='text-right'>$balvalue </td>";
                                Echo"<td class='text-right'>$open</td>";
                                Echo"<td class='text-right'>$inwd</td>";
                                Echo"<td class='text-right'>$out</td>";
                                Echo"<td class='text-right'>$used</td>";
                                Echo"<td class='text-right'>$dmged</td>";
                                Echo"</tr>";

                                $totbalqty = $totbalqty + $balqty;
                                $totbalval = $totbalval + $balvalue;
                                $totopen = $totopen + $open;
                                $totinwd = $totinwd + $inwd;
                                $totout = $totout + $out;
                                $totused = $totused + $used;
                                $totdmged = $totdmged + $dmged;
                            }

                            Echo "<tr style='background-color:#26A69A'>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right tsize'><b>Total</b></td>";
                            Echo "<td class='text-right tsize'><b>$totbalqty</b></td>";
                            Echo "<td class='text-right tsize'><b></b></td>";
                            Echo "<td class='text-right tsize'><b>$totbalval</b></td>";
                            Echo "<td class='text-right tsize'><b>$totopen</b></td>";
                            Echo "<td class='text-right tsize'><b>$totinwd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totout</b></td>";
                            Echo "<td class='text-right tsize'><b>$totused</b></td>";
                            Echo "<td class='text-right tsize'><b>$totdmged </b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}

if (isset($_POST["excel"])) {
    $item = $_POST["item"];
   
    $itemcond = '';

    if (!empty($item)) {
        $itemcond = " AND `itemcode` = '$item' ";
    }

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

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

    $mainqry = mysqli_query($conn, "SELECT *,DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE `vouchno` != '' 
                            $itemcond group by itemcode")or die(mysqli_error($conn));


    $contents.= "Sr, Item Name, Unit, Bal. Qty, Cost, Balance Value, Opening, Purchase, Purchase Return, Production, Damaged,\n ";

    $ctr = 0;
    $totinwd = 0;
    $totopen = 0;
    $totout = 0;
    $balqty = 0;
    $totbalqty = 0;
    $totbalval = 0;
    while ($fmain = mysqli_fetch_array($mainqry)) {

        $gitem = mysqli_query($conn, "SELECT `code`, `name` FROM `item` WHERE `code`='$fmain[itemcode]'")or die(mysqli_error($conn));
        $item = mysqli_fetch_array($gitem);

        $gopen = mysqli_query($conn, "SELECT `itemcode`, SUM(`qty`) as `qty`  FROM `rawsacn` WHERE `itemcode`='$fmain[itemcode]' 
                                                      and `source` = 'OP' ")or die(mysqli_error($conn));
        $opening = mysqli_fetch_array($gopen);

        $ginwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$fmain[itemcode]' AND `source` = 'PURW'")or die(mysqli_error($conn));
        $inward = mysqli_fetch_array($ginwd);

        $goutw = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$fmain[itemcode]' AND `source` = 'PRRW'")or die(mysqli_error($conn));
        $outward = mysqli_fetch_array($goutw);

        $gdmg = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$fmain[itemcode]' AND `source` = 'DM'")or die(mysqli_error($conn));
        $damaged = mysqli_fetch_array($gdmg);

        $gused = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$fmain[itemcode]' AND `source` = 'BMR'")or die(mysqli_error($conn));
        $fused = mysqli_fetch_array($gused);

        if ($opening[qty] != '') {
            $open = $opening['qty'];
        } else {
            $open = 0;
        }

        if ($damaged[qty] != '') {
            $dmged = $damaged['qty'];
        } else {
            $dmged = 0;
        }

        if ($outward[qty] != '') {
            $out = $outward['qty'];
        } else {
            $out = 0;
        }

        if ($fused[qty] != '') {
            $used = $fused['qty'];
        } else {
            $used = 0;
        }

        if ($inward[qty] != '') {
            $inwd = $inward['qty'];
        } else {
            $inwd = 0;
        }



        $balqty = $open + $inwd - $out - $used - $dmged;
        $balvalue = $balqty * $fmain[rate];


        $ctr++;
        $contents .= $ctr . ",";
        $contents .= $item['name'] . ",";
        $contents .= $fmain['unit'] . ",";
        $contents .= $balqty . ",";
        $contents .= $fmain['rate'] . ",";
        $contents .= $balvalue . ",";
        $contents .= $open . ",";
        $contents .= $inwd . ",";
        $contents .= $out . ",";
        $contents .= $used . ",";
        $contents .= $dmged . "\n";

        $totbalqty = $totbalqty + $balqty;
        $totbalval = $totbalval + $balvalue;
        $totopen = $totopen + $open;
        $totinwd = $totinwd + $inwd;
        $totout = $totout + $out;
        $totused = $totused + $used;
        $totdmged = $totdmged + $dmged;
    }


    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $totbalqty . ",";
    $contents .= " " . ",";
    $contents .= $totbalval . ",";
    $contents .= $totopen . ",";
    $contents .= $totinwd . ",";
    $contents .= $totout . ",";
    $contents .= $totused . ",";
    $contents .= $totdmged . "\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;
    }
}
?>