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

$zroot = $_SESSION["zdbroot"];
$formname = 'Stock';
$file = 'stock.php';

$prodcode = $_POST["prodcode"];
$product = $_POST["product"];
$prodgrp = $_POST["prodgrp"];
$type = $_POST["type"];
$godown = $_POST["godown"];
$prinval = $_POST["prnval"];
//browse($prinval);
$action = 'prodhist_fr.php';
$typecond = '';
$prodcond = '';
$grpcond = '';
$godwncond = '';

if ($type == 'Organic') {
    $typecond = " AND `type` = 'Organic' ";
} else if ($type == 'Other') {
    $typecond = " AND `type` != 'Organic' ";
} else {
    $typecond = "";
}


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

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

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

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

$forclosestk = trim($_POST["forclosestk"]);

if ($forclosestk > 0) {
    $grval = 0;
    mysqli_query($conn, "drop temporary table if exists stkdbf ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table stkdbf
                                SELECT sum(a.qty) as sqty, a.itemcode, b.cost, b.stkunit, sum(a.qty * b.cost) as closstkcost, 
                                       sum(a.qty * b.rate) as closstkrate FROM transacn a, $maindb b 
                                       WHERE a.vouchdt <= '$mtodate' and a.itemcode = b.code group by a.itemcode") or die(mysqli_error($conn));

    if ($prinval != "") {
        if ($prinval == "C") {
            $getstk = mysqli_query($conn, "SELECT sum(closstkcost) as clstk FROM `stkdbf` ") or die(mysqli_error($conn));
        } else {
            $getstk = mysqli_query($conn, "SELECT sum(closstkrate) as clstk FROM `stkdbf` ") or die(mysqli_error($conn));
        }
        $fetstk = mysqli_fetch_object($getstk);
        $grval = $fetstk->clstk;
    }
    //    browse($grval);

    mysqli_query($conn, "update accmast set currbal = '$grval', currdc = 'D' WHERE accode = '23' ") or die(mysqli_error($conn));

    browse('Calculation of Closing Stock Completed.');
    die();
    //    echo "<script>
    //            window.close();
    //            </script>";
} else {
//    $gprodct = mysqli_query($conn, "SELECT * FROM `product` WHERE code in (select itemcode from transacn ) $pcodecond $grpcond $typecond order by name") or die(mysqli_error($conn));
    $gprodct = mysqli_query($conn, "SELECT a.*,b.batch FROM `product` a, transacn b WHERE a.code =b.itemcode $pcodecond $grpcond $typecond group by a.code,b.batch order by a.name,b.vouchdt") or die(mysqli_error($conn));
//    print table($gprodct);
}

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: #7E827E;
        }

        .tsize {
            font-size: 18px;
            padding: 6px;
        }

        tr td {
            font-size: 14px;
        }
    </style>
    </head>

    <body>
        <div class="col-lg-8 container">

            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 ">
                    <table class="table table-bordered">
                        <tr class="mySearchTr">
                            <td style="color: white"><b><?php echo $title; ?></b></td>
                        </tr>
                    </table>


                    <table style="background: white;"
                           class="table table-condensed table-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:3%;">Sr.</th>
                                <th style="width:40%;">Product Name</th>
                                <th style="width:7%;" class=''>Batch</th>
                                <th style="width:7%;" class='text-right'>Opening</th>
                                <th style="width:7%;" class="text-right">Add</th>
                                <th style="width:7%;" class="text-right">Less</th>
                                <th style="width:8%;" class="text-right">Balance</th>
                                <th style="width:8%;" class="text-left">Stock Unit</th>
                                <?php
                                if ($prinval == 2) {
                                    if ($prinval == "2") {
                                        ?>
                                        <th style="width:8%;" class="text-right">Cost </th>
                                        <?php
                                    } else {
                                        ?>
                                        <th style="width:8%;" class="text-right">Sell </th>
                                        <?php
                                    }
                                    ?>
                                    <th style="width:10%;" class="text-right">Value</th>
                                    <?php
                                }
                                ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $balance = $totinwd = 0;
                            while ($fproduct = mysqli_fetch_array($gprodct)) {

                                $barcode = $fproduct['barcode'];
                                $itemname = $fproduct['name'];
                                $icode = $fproduct['code'];
                                $irate = $fproduct['rate'];
                                $icost = $fproduct['cost'];
                                $stkunit = $fproduct['stkunit'];
                                $batch = $fproduct['batch'];
                                $gunit = mysqli_query($conn, "SELECT code,name FROM $zroot.`unit` WHERE `code`='$stkunit'") or die(mysqli_error($conn));
                                $funit = mysqli_fetch_array($gunit);

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

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

                                $goutwd = mysqli_query($conn, "SELECT SUM(`stkqty`) AS `sqty` FROM `transacn` WHERE `itemcode`='$icode'
                                                      and qty < 0  and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'  and batch = '$batch'") 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;

                                if ($stkunit == "1") {

                                    $costval = number_format($icost * ($balance / 1000), 2);
                                } else {
                                    $costval = number_format($icost * $balance, 2);
                                }
                                $rateval = $irate * $balance;

                                if ($stkunit == 1) {
                                    if ($open != 0) {
                                        $open = number_format($open / 1000, 3);
                                    }
                                    if ($inwd != 0) {
                                        $inwd = number_format($inwd / 1000, 3);
                                    }
                                    if ($out != 0) {
                                        $out = number_format($out / 1000, 3);
                                    }
                                    if ($balance != 0) {
                                        $balance = number_format($balance / 1000, 3);
                                    }
                                }

                                $ctr++;

                                echo "<tr class='clickable-row2' data-href='$action?view=view&pcode=$icode&pbatch=$batch&todate=$mtodate' [target]='_blank'>";
                                echo "<td class=''>$ctr</td>";
                                echo "<td class=''>$itemname</td>";
                                echo "<td class=''>$batch</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: black;'><b>$balance</b></td>";
                                    echo "<td></td>";
                                } else {
                                    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 "<td class='text-left'>$funit[name]</td>";
                                if ($prinval == 2) {
                                    $colr = "";
                                    if ($costval > 0) {
                                        $colr = "green";
                                    } else
                                    if ($costval < 0) {
                                        $colr = "red";
                                    }
                                    if ($prinval == "2") {
                                        echo "<td class='text-right' style=''><b>$icost</b></td>";
                                        echo "<td class='text-right' style='color:$colr'><b>$costval</b></td>";
                                    } else {
                                        echo "<td class='text-right' style=''><b>$irate</b></td>";
                                        echo "<td class='text-right' style=''><b>$rateval</b></td>";
                                    }
                                }


                                echo "</tr>";

                                $totopen = $totopen + $open;
                                $totinwd = $totinwd + $inwd;
                                $totout = $totout + $out;
                                $totbal = $totbal + $balance;
                                $totcost = $totcost + $costval;
                                $totrate = $totrate + $rateval;
                            }

                            echo "<tr style='background-color:#7E827E'>";
                            echo "<td></td>";
                            Echo "<td></td>";
                            //                            Echo "<td></td>";
                            echo "<td class='text-right tsize' style='color: white;'>Total</td>";
                            echo "<td class='text-right tsize' style='color: white;'><b>" . number_format($totopen, 3) . "</b></td>";
                            echo "<td class='text-right tsize' style='color: white;'><b>" . number_format($totinwd, 3) . "</b></td>";
                            echo "<td class='text-right tsize' style='color: white;'><b>" . number_format($totout, 3) . "</b></td>";
                            echo "<td class='text-right tsize' style='color: white;'><b>" . number_format($totbal, 3) . "</b></td>";
                            echo "<td class='text-right' style='color: white;'><b></b></td>";
                            if ($prinval == "2") {
                                echo "<td class='text-right' style='color: white;'></td>";
                                if ($prinval == "2") {
                                    echo "<td class='text-right' style='color: white;'><b>$totcost</b></td>";
                                } else {
                                    echo "<td class='text-right' style='color: white;'><b>$totrate</b></td>";
                                }
                            }

                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>

    </html>
    <?php
}


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

    $contents .= "$title \n";
    $contents .= "Sr., Product Name, Opening, Add, Less, Balance, Stock Unit \n";

    if ($prinval != "") {
        if ($prinval == "C") {
            ?>
            <th style="width:8%;" class="text-right">Cost </th>
            <?php
        } else {
            ?>
            <th style="width:8%;" class="text-right">Sell </th>
            <?php
        }
        ?>
        <th style="width:10%;" class="text-right">Value</th>
        <?php
    }

    $ctr = 0;
    $balance = $totinwd = 0;
    while ($fproduct = mysqli_fetch_array($gprodct)) {

        $barcode = $fproduct['barcode'];
        $itemname = $fproduct['name'];
        $icode = $fproduct['code'];
        $irate = $fproduct['rate'];
        $icost = $fproduct['cost'];
        $stkunit = $fproduct['stkunit'];
        $gunit = mysqli_query($conn, "SELECT code,name FROM $zroot.`unit` WHERE `code`='$stkunit'") or die(mysqli_error($conn));
        $funit = mysqli_fetch_array($gunit);

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

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

        $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `sqty` FROM `transacn` WHERE `itemcode`='$icode'
                                                      and qty < 0  and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'") 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;
        }

        if ($stkunit == 1) {
            if ($open != 0) {
                $open = number_format($open / 1000, 3);
            }
            if ($inwd != 0) {
                $inwd = number_format($inwd / 1000, 3);
            }
            if ($out != 0) {
                $out = number_format($out / 1000, 3);
            }
            if ($balance != 0) {
                $balance = number_format($balance / 1000, 3);
            }
        }

        $balance = $open + $inwd - $out;
        $costval = $icost * $balance;
        $rateval = $irate * $balance;

        $ctr++;

        $contents .= "$ctr" . ",";
        $contents .= "$itemname" . ",";
        $contents .= "$open" . ",";
        $contents .= "$inwd" . ",";
        $contents .= "$out" . ",";
        if ($balance == 0) {
            $contents .= "" . ",";
        } else {
            if ($balance > 0) {
                $contents .= "$balance" . ",";
            } else {
                $contents .= "$balance" . ",";
            }

            if ($prinval != "") {
                if ($prinval == "C") {
                    $contents .= "$icost" . ",";
                    $contents .= "$costval" . ",";
                } else {
                    $contents .= "$irate" . ",";
                    $contents .= "$rateval" . ",";
                }
            }
        }
        $contents .= "$funit[name]" . ",";
        $contents .= "\n";

        $totopen = $totopen + $open;
        $totinwd = $totinwd + $inwd;
        $totout = $totout + $out;
        $totbal = $totbal + $balance;
        $totcost = $totcost + $costval;
        $totrate = $totrate + $rateval;
    }

    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$totopen" . ",";
    $contents .= "$totinwd" . ",";
    $contents .= "$totout" . ",";
    $contents .= "$totbal" . ",";
    if ($prinval != "") {
        $contents .= "" . ",";
        if ($prinval == "C") {
            $contents .= "$totcost" . ",";
        } else {
            $contents .= "$totrate" . ",";
        }
    }
    $contents .= "" . ",";
    $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;
    }
}
