<?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 = 'transacn';
$zroot = $_SESSION["zdbroot"];
$formname = 'Product Stock';
$file = 'stkprod.php';


$prodcode = $_POST["prodcode"];
$product = $_POST["product"];
$prodgrp = $_POST["prodgrp"];
$type = $_POST["type"];
$godown = $_POST["godown"];
$action = 'prodhist_fr.php';
$pcodecond = '';
$prodcond = '';
$grpcond = '';
$godwncond = '';

if (!empty($prodcode)) {
    $pcodecond = " AND `prodcode` = '$prodcode' ";
}

if (!empty($prodgrp)) {
    $grpcond = " AND `prodgrp` = '$prodgrp' ";
}
if (!empty($godown)) {
    $godwncond = " AND `godown` = '$godown' ";
}
switch ($type) {

    case "A":
        $sercond = "";
        $title = "Product Stock";
        break;
    case "L":
        $sercond = " and series = 'LO'  ";
        $title = "Local - Product Stock";
        break;

    case "D":
        $sercond = " and series = 'DE'  ";
        $title = "Deemed - Product Stock";
        break;

    case "E":
        $sercond = " and series = 'EX'  ";
        $title = "Export - Product Stock";
        break;

    case "N":
        $sercond = " and series = 'ON'  ";
        $title = "Export - Product Stock";
        break;
}

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

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

$gprodct = mysqli_query($conn, "SELECT code , prodcode, name, barcode, prodgrp, cost, rate, mrp, msp, gram, unit
                            FROM `product` WHERE code != '' $pcodecond $grpcond  order by name")or die(mysqli_error($conn));

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

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #26A69A;
        }
        .tsize{
            font-size:18px;
            padding : 6px;
        }
        tr td{
            font-size:14px;
        }

    </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">
                            <td style="color: white"><b><?php echo $title; ?></b></td>
                        </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:3%;">Sr</th>
                                <th style="width:7%;"> Code</th>
                                <th style="width:42%;">Product Name</th>
                                <th style="width:6%;" class="">Net Wt.</th>
                                <th style="width:7%;" class='text-right'>Opening</th>
                                <th style="width:7%;" class="text-right">Inward</th>
                                <th style="width:7%;" class="text-right">Outward</th>
                                <th style="width:8%;" class="text-right">Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $balance = $totinwd = 0;
                            while ($fproduct = mysqli_fetch_array($gprodct)) {

                                $prodcode = $fproduct['prodcode'];
                                $barcode = $fproduct['barcode'];
                                $itemname = $fproduct['name'];
                                $icode = $fproduct['code'];
                                $gram = $fproduct['gram'];
                                $irate = $fproduct['rate'];
                                $cost = $fproduct['cost'];


                                $getopen = mysqli_query($conn, "SELECT sum(`qty`) as sqty FROM `transacn` WHERE `itemcode`='$prodcode'
                                                        and vouchdt < '$mfrmdate'  $sercond $godwncond ")or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $ginwd = mysqli_query($conn, "SELECT SUM(qty) AS `sqty`,itemcode FROM `transacn` WHERE `itemcode`='$prodcode'
                                                      and qty > 0 AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $sercond $godwncond ")or die(mysqli_error($conn));
                                $indward = mysqli_fetch_array($ginwd);

                                $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `sqty` FROM `transacn` WHERE `itemcode`='$prodcode'
                                                      and qty < 0  and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $sercond $godwncond ")or die(mysqli_error($conn));
                                $outwd = mysqli_fetch_array($goutwd);


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

                                if ($indward['sqty'] != '') {
                                    $inwd = $indward['sqty'];
                                } else {
                                    $inwd = 0;
                                }


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


                                $balance = $open + $inwd - $out;
                                $ctr++;

                                Echo"<tr class='clickable-row2' data-href='$action?view=view&pcode=$prodcode&todate=$mtodate' [target]='_blank'>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$prodcode</td>";
                                Echo"<td class=''>$itemname</td>";
                                Echo"<td class=''>$gram</td>";
                                Echo"<td class='text-right'>$open</td>";
                                Echo"<td class='text-right'>$inwd</td>";
                                Echo"<td class='text-right'>$out</td>";
                                if ($balance >= 0) {
                                    Echo"<td class='text-right' style='color: blue;'><b>$balance</b></td>";
                                } else {
                                    Echo"<td class='text-right' style='color: red;'>$balance</td>";
                                }
                                Echo"</tr>";

                                $totopen = $totopen + $open;
                                $totinwd = $totinwd + $inwd;
                                $totout = $totout + $out;
                                $totbal = $totbal + $balance;
                            }

                            Echo "<tr style='background-color:#26A69A'>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right tsize'>Total</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>$totbal</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                    <? // } elseif($type == "L"){     ?>





                    <? // }     ?>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}


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


    $contents .="Sr,Code,Product Name,Net Wt.,Opening,Inward,Outward,Balance\n";


    $ctr = 0;
    $balance = $totinwd = 0;
    while ($fproduct = mysqli_fetch_array($gprodct)) {
                                $prodcode = $fproduct['prodcode'];
                                $barcode = $fproduct['barcode'];
                                $itemname = $fproduct['name'];
                                $icode = $fproduct['code'];
                                $gram = $fproduct['gram'];
                                $irate = $fproduct['rate'];
                                $cost = $fproduct['cost'];


                                $getopen = mysqli_query($conn, "SELECT sum(`qty`) as sqty FROM `transacn` WHERE `itemcode`='$prodcode'
                                                        and vouchdt < '$mfrmdate'  $sercond $godwncond ")or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $ginwd = mysqli_query($conn, "SELECT SUM(qty) AS `sqty`,itemcode FROM `transacn` WHERE `itemcode`='$prodcode'
                                                      and qty > 0 AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $sercond $godwncond ")or die(mysqli_error($conn));
                                $indward = mysqli_fetch_array($ginwd);

                                $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `sqty` FROM `transacn` WHERE `itemcode`='$prodcode'
                                                      and qty < 0  and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $sercond $godwncond ")or die(mysqli_error($conn));
                                $outwd = mysqli_fetch_array($goutwd);


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

                                if ($indward['sqty'] != '') {
                                    $inwd = $indward['sqty'];
                                } else {
                                    $inwd = 0;
                                }


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


                                $balance = $open + $inwd - $out;
                                $ctr++;

        $contents .="$ctr" . ",";
        $contents .="$prodcode" . ",";
        $contents .="$itemname" . ",";
        $contents .="$gram" . ",";
        $contents .="$open" . ",";
        $contents .="$inwd" . ",";
        $contents .="$out" . ",";
        $contents .="$balance" . "\n";



        $totopen = $totopen + $open;
        $totinwd = $totinwd + $inwd;
        $totout = $totout + $out;
        $totbal = $totbal + $balance;
    }

    $contents .="" . ",";
    $contents .="" . ",";
    $contents .="" . ",";
    $contents .="Total" . ",";
    $contents .="$totopen" . ",";
    $contents .="$totinwd" . ",";
    $contents .="$totout" . ",";
    $contents .="$totbal" . "\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;
    }
}