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

$item = $_POST["item"];
$godwn = $_POST["godown"];
$action = 'itemhist_fr.php';
$itemcond = '';
$godwncond = '';

if (!empty($item)) {
    $itemcond = " AND `code` = '$item' ";
}
if (!empty($godwn)) {
    $godwncond = " AND `godown` = '$godown' ";
    $getgodown = mysqli_query($conn, "SELECT code, name FROM `godown` WHERE `code`='$godwn'")or die(mysql_error);
    $fgodwn = mysqli_fetch_array($getgodown);
    $godown = $fgodwn['name'];
}



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

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

$gitem = mysqli_query($conn, "SELECT code , name, rate, cost, unit, hsncode,gstperc
                            FROM `item` WHERE code != '' $itemcond order by name")or die(mysqli_error($conn));

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


    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 class="table table-bordered">
                        <tr class="mySearchTr ">
                            <th width="10%"><b>Godown</b></th>
                            <th width="90%"><b><?php echo $godown; ?></b></th>
                        </tr>
                    </table>
                    <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 ($fitem = mysqli_fetch_array($gitem)) {


                                $icode = $fitem['code'];
                                $itemname = $fitem['name'];
                                $cost = $fitem['cost'];
                                $unit = $fitem['unit'];



                                if ($cost > 0) {
                                    $actcost = $cost;
                                } else {
                                    $getcost = mysqli_query($conn, "SELECT `rate` FROM rawsacn WHERE `itemcode`='$icode' and rate > 0 
                                                            and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));
                                    $fcost = mysqli_fetch_array($getcost);
                                    $actcost = $fcost['rate'];
                                }
                                $newcost = $actcost;

                                $getopen = mysqli_query($conn, "SELECT sum(qty) as oqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'OP' AND `vouchdt` < '$mfrmdate' $godwncond  ")or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $getinw = mysqli_query($conn, "SELECT sum(qty) as iqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'PURW' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'   and qty > 0  $godwncond ")or die(mysqli_error($conn));
                                $finwd = mysqli_fetch_array($getinw);

                                $goutwd = mysqli_query($conn, "SELECT sum(qty) as outqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'PRRW' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'   and qty > 0  $godwncond ")or die(mysqli_error($conn));
                                $outwd = mysqli_fetch_array($goutwd);

                                $gused = mysqli_query($conn, "SELECT sum(qty) as usdqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        `source` = 'BMR' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'   and qty > 0 $godwncond ")or die(mysqli_error($conn));
                                $fused = mysqli_fetch_array($gused);

                                $gdmg = mysqli_query($conn, "SELECT sum(qty) as dmgqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        `source` = 'DMRM' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'   and qty < 0 $godwncond ")or die(mysqli_error($conn));
                                $fdmg = mysqli_fetch_array($gdmg);



                                if ($fopen['oqty'] != '') {
                                    $open = $fopen['oqty'];
                                } else {
                                    $open = 0;
                                }

                                if ($fdmg['dmgqty'] != '') {
                                    $dmged = abs($fdmg['dmgqty']);
                                } else {
                                    $dmged = 0;
                                }

                                if ($outwd['outqty'] != '') {
                                    $out = $outwd['outqty'];
                                } else {
                                    $out = 0;
                                }

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

                                if ($finwd ['iqty'] != '') {
                                    $inwd = $finwd ['iqty'];
                                } else {
                                    $inwd = 0;
                                }

                                $balqty = $open + $inwd - $out - $used - $dmged;
                                $balvalue = $balqty * $cost;

                                $ctr++;

                                Echo"<tr class='clickable-row' data-href='$action?view=view&item=$icode&todt=$mtodate' data-target='_blank'>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$itemname</td>";
                                Echo"<td class=''>$unit</td>";
                                Echo"<td class='text-right'><b>$balqty </b></td>";
                                Echo"<td class='text-right'>$cost</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"])) {


    $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 ($fitem = mysqli_fetch_array($gitem)) {


        $icode = $fitem['code'];
        $itemname = $fitem['name'];
        $cost = $fitem['cost'];
        $unit = $fitem['unit'];

        if ($cost > 0) {
            $actcost = $cost;
        } else {
            $getcost = mysqli_query($conn, "SELECT `rate` FROM rawsacn WHERE `itemcode`='$icode' and rate > 0 
                                                            and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));
            $fcost = mysqli_fetch_array($getcost);
            $actcost = $fcost['rate'];
        }
        $newcost = $actcost;

        $getopen = mysqli_query($conn, "SELECT sum(qty) as oqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'OP' AND `vouchdt` < '$mfrmdate'  ")or die(mysqli_error($conn));
        $fopen = mysqli_fetch_array($getopen);
//                                $openqty = $iopen['oqty'] ;

        $getinw = mysqli_query($conn, "SELECT sum(qty) as iqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'PURW' AND `vouchdt` >= '$mfrmdate'  and qty > 0  ")or die(mysqli_error($conn));
        $finwd = mysqli_fetch_array($getinw);
//                                $inwqty = $inwd['iqty'] ;

        $goutwd = mysqli_query($conn, "SELECT sum(qty) as outqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        source = 'PRRW' AND `vouchdt` >= '$mfrmdate'  and qty > 0 ")or die(mysqli_error($conn));
        $outwd = mysqli_fetch_array($goutwd);
//                                $outwdqty = $outwd['outqty'] ;

        $gused = mysqli_query($conn, "SELECT sum(qty) as usdqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        `source` = 'BMR' AND `vouchdt` >= '$mfrmdate'  and qty > 0 ")or die(mysqli_error($conn));
        $fused = mysqli_fetch_array($gused);
//                                $usedqty = $fused['usdqty'] ;

        $gdmg = mysqli_query($conn, "SELECT sum(qty) as dmgqty FROM $maindb WHERE `itemcode` = '$icode' AND 
                                                        `source` = 'DM' AND `vouchdt` >= '$mfrmdate'  and qty > 0 ")or die(mysqli_error($conn));
        $fdmg = mysqli_fetch_array($gdmg);
//                                $usedqty = $fdmg['dmgqty'] ;



        if ($fopen['oqty'] != '') {
            $open = $fopen['oqty'];
        } else {
            $open = 0;
        }

        if ($fdmg['dmgqty'] != '') {
            $dmged = $fdmg['dmgqty'];
        } else {
            $dmged = 0;
        }

        if ($outwd['outqty'] != '') {
            $out = $outwd['outqty'];
        } else {
            $out = 0;
        }

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

        if ($finwd ['iqty'] != '') {
            $inwd = $finwd ['iqty'];
        } else {
            $inwd = 0;
        }



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


        $ctr++;
        $contents .= $ctr . ",";
        $contents .= $itemname . ",";
        $contents .= $unit . ",";
        $contents .= $balqty . ",";
        $contents .= $cost . ",";
        $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;
    }
}
?>