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

$formname = 'Combine Stock';
$grid = 'conbstock.php';
$zroot = $_SESSION["zdir"];

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);

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

    $dtype = $_POST["dtype"];
    $zeroprn = $_POST["zerostk"];
    $prnval = trim($_POST["prnval"]);

    $maindb = 'item';
    $trandb = 'transacn';
    $stkcdb = $trandb;

    $icond = "";
    $scond = "";
    $pcond = "";

    if (!empty($_POST["name"])) {
        $iname = trim($_POST["name"]);
        $icond = "AND a.item = '$iname'";
//        browse($iname);

        $icond1 = "AND code = '$iname'";
        $getitemname = mysqli_query($conn, "SELECT `name` FROM `item` WHERE code='$iname'")or die(mysqli_error($conn));
        $itname = mysqli_fetch_object($getitemname);
        $mitem = $itname->name;
    }

    $qryser = mysqli_query($conn, "SELECT * from $zroot.`series`  WHERE code != '' order by series ")or die(mysqli_error($conn));

    $sr = 1;
    while ($cser = mysqli_fetch_array($qryser)) {
        $serno = 'serno' + $sr;
        $serno = $cser['code'];
        $sr++;
    }

    if (!empty($_POST["itemgrp"])) {
        $pname = trim($_POST["itemgrp"]);
        $pcond = "AND `b.itemgrp` = '$pname'";
    }
    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; itemwise_sale.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; itemwise_sale.php");
    }


    $fromdt = $_POST["frmdt"];
    $todt = $_POST["todt"];

//    $get = mysqli_query($conn, "SELECT *, DATE_FORMAT(`billdate`, '%d-%m-%Y') as mydt FROM `$maindb` WHERE
//                       `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'") or die(mysqli_error($conn));
//    $getno = mysqli_num_rows($get);
    $getmain = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `name` != '' $icond1 ORDER BY name") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($getmain);

    if ($getno > 0) {

        $mfdt = date("d-m-Y", strtotime($fromdt));
        $mtdt = date("d-m-Y", strtotime($todt));

        include("includes/linkshead.php");
        ?>
        <style type="text/css">
            #tabital{
                font-style: italic;
            }
            #font1{
                font-style: italic;
            }
            #boldfont{
                font-weight: bold; 
            }
        </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">
                            <thead class="mySearchTr">
                                <tr>

                                    <th>Sr.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th>Barcode<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th>Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style="text-align: right;">Bal. 1<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style="text-align: right;">Bal. 2<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style="text-align: right;">Bal. 3<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style="text-align: right;">Total <i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <?php if ($prnval == 2) { ?>
                                        <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 } ?>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                mysqli_select_db($conn, $zdb);
                                $gropen = 0;
                                $grinw = 0;
                                $grout = 0;
                                $grbal = 0;
                                $grval = 0;
                                $grbook = 0;
                                $grbkafbl = 0;
                                $ctr = 0;
//                                browse($zeroprn);
//                                require("includes/dbconnect.php");
                                mysqli_query($conn, "drop temporary table if exists tempstk ") or die(mysqli_error($conn));
                                mysqli_query($conn, "create temporary table 
                                                            SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `vouchdt` <= '$todt'
                                                                   GROUP BY `itemcode`, `series` ") or die(mysqli_error($conn));

                                while ($cmain = mysqli_fetch_array($getmain)) {
                                    $ctr++;

                                    $itemcode = $cmain["code"];
                                    $cost = $cmain["cost"];
                                    $name = $cmain["name"];

                                    ########### OPENING ##################

                                    $opening = 0;
                                    $tqry1 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series` = '$serno1' ") or die(mysqli_error($conn));
                                    $getqty1 = mysqli_fetch_object($tqry1);

                                    $opening1 = $getqty1->totqty;
                                    if ($opening1 != 0) {
                                        $opening = $opening1;
                                    } else {
                                        $opening = 0;
                                    }

                                    $opening3 = 0;
                                    $tqry2 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series`= '$serno2' ") or die(mysqli_error($conn));
                                    $getqty2 = mysqli_fetch_object($tqry2);
                                    $opening2 = $getqty2->totqty;
                                    if ($opening2 != 0) {
                                        $opening3 = $opening2;
                                    } else {
                                        $opening3 = 0;
                                    }

                                    $opening4 = 0;
                                    $tqry3 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series`= '$serno3' ") or die(mysqli_error($conn));
                                    $getqty3 = mysqli_fetch_object($tqry3);
                                    $opening5 = $getqty3->totqty;
                                    if ($opening5 != 0) {
                                        $opening4 = $opening5;
                                    } else {
                                        $opening4 = 0;
                                    }

                                    ########### INWARD ##################

                                    $inqry = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
                                    $getin = mysqli_fetch_object($inqry);
                                    $inward = abs($getin->totqty1);

                                    $inqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
                                    $getin1 = mysqli_fetch_object($inqry1);
                                    $inward1 = abs($getin1->totqty1);

                                    $inqry2 = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
                                    $getin2 = mysqli_fetch_object($inqry2);
                                    $inward2 = abs($getin2->totqty1);

                                    ########### OUTWARD  ##################

                                    $otqry = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
                                    $getot = mysqli_fetch_object($otqry);
                                    $outward = ABS($getot->totqty);

                                    $otqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
                                    $getot1 = mysqli_fetch_object($otqry1);
                                    $outward1 = ABS($getot1->totqty);

                                    $otqry2 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
                                    $getot2 = mysqli_fetch_object($otqry2);
                                    $outward2 = ABS($getot2->totqty);

                                    $totbal = $opening + $inward - $outward;
                                    $totbal1 = $opening3 + $inward1 - $outward1;
                                    $totbal2 = $opening5 + $inward2 - $outward2;

                                    $a = 1;
                                    if ($zeroprn == 1) {
                                        if ($totbal != 0 || $totbal1 != 0 || $totbal2 != 0) {
                                            $a = 1;
                                        } else {
                                            $a = 0;
                                        }
                                    }

                                    if ($a == 1) {
                                        echo "<tr>";
                                        echo "<td>";
                                        echo $ctr;
                                        echo "</td>";
                                        echo "<td>";
                                        echo $itemcode;
                                        echo "</td>";
                                        echo "<td>";
                                        echo "$name";
                                        echo "</td>";


                                        $gropen = $gropen + $opening;
                                        $grinw = $grinw + $inward;
                                        $grout = $grout + $outward;
//                                        } 

                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format("$totbal", 0, ".", ",") . "</b>";
                                        echo "</td>";

                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format("$totbal1", 0, ".", ",") . "</b>";
                                        echo "</td>";

                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format("$totbal2", 0, ".", ",") . "</b>";
                                        echo "</td>";

                                        $totbalance = $totbal + $totbal1 + $totbal2;

                                        $itemvalue = $cost * $totbalance;

                                        echo "<td style='text-align:right;'>";
                                        echo "<font><b> " . number_format("$totbalance", 0, ".", ",") . "</b>";
                                        echo "</td>";
                                        if ($prnval == 2) {

                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$cost", 0, ".", ",") . "</b>";
                                            echo "</td>";

                                            echo "<td style='text-align:right;'>";
                                            echo "<font><b> " . number_format("$itemvalue", 0, ".", ",") . "</b>";
                                            echo "</td>";
                                        }

                                        echo "</tr>";
                                        $grbal = $grbal + $totbal;
                                        $grbal1 = $grbal1 + $totbal1;
                                        $grbal2 = $grbal2 + $totbal2;
                                        $grtot = $grtot + $totbalance;
                                        $grval = $grval + $itemvalue;
                                    }
                                }

                                echo "<tr>";
                                echo "<td>";
                                echo "</td>";
                                echo "<td>";
                                echo "</td>";
                                echo "<td align='right'>";
                                echo "Total";
                                echo "</td>";

                                echo "<td align='right'>";
                                echo "<font><b> " . $grbal . "</b>";
                                echo "</td>";
                                echo "<td align='right'>";
                                echo "<font><b> " . $grbal1 . "</b>";
                                echo "</td>";
                                echo "<td align='right'>";
                                echo "<font><b> " . $grbal2 . "</b>";
                                echo "</td>";
                                echo "<td align='right'>";
                                echo "<font><b> " . $grtot . "</b>";
                                echo "</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
    } else {
        browse("Record Not Found");
        echo "<script>
            window.close();
            </script>";
    }
}

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

    $getmain = mysqli_query($conn, "SELECT * FROM `$maindb` WHERE `name` != '' $icond1 ORDER BY name") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($getmain);

    $ctr = 1;
    while ($pmain = mysqli_fetch_array($getpmain)) {


        $contents.=$ctr . ",";
        $contents.=$ptran[Barcode] . ",";
        $contents.=$ptran[Name] . ",";
        $contents.=$ptran[Bal . 1] . ",";
        $contents.=$ptran[Bal . 2] . ",";
        $contents.=$ptran[Bal . 3] . ",";
        $contents.=$ptran[Total] . ",";
        $contents.=$ptran[Cost] . ",";
        $contents.=$ptran[Value] . "\n";
        $ctr++;
        ?>

        <?php
        mysqli_select_db($conn, $zdb);
        $gropen = 0;
        $grinw = 0;
        $grout = 0;
        $grbal = 0;
        $grval = 0;
        $grbook = 0;
        $grbkafbl = 0;
        $ctr = 0;
//                                browse($zeroprn);
//                                require("includes/dbconnect.php");
        mysqli_query($conn, "drop temporary table if exists tempstk ") or die(mysqli_error($conn));
        mysqli_query($conn, "create temporary table 
                                                            SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `vouchdt` <= '$todt'
                                                                   GROUP BY `itemcode`, `series` ") or die(mysqli_error($conn));

        while ($cmain = mysqli_fetch_array($getmain)) {
            $ctr++;

            $itemcode = $cmain["code"];
            $cost = $cmain["cost"];
            $name = $cmain["name"];

            ########### OPENING ##################

            $opening = 0;
            $tqry1 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series` = '$serno1' ") or die(mysqli_error($conn));
            $getqty1 = mysqli_fetch_object($tqry1);

            $opening1 = $getqty1->totqty;
            if ($opening1 != 0) {
                $opening = $opening1;
            } else {
                $opening = 0;
            }

            $opening3 = 0;
            $tqry2 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series`= '$serno2' ") or die(mysqli_error($conn));
            $getqty2 = mysqli_fetch_object($tqry2);
            $opening2 = $getqty2->totqty;
            if ($opening2 != 0) {
                $opening3 = $opening2;
            } else {
                $opening3 = 0;
            }

            $opening4 = 0;
            $tqry3 = mysqli_query($conn, "SELECT totqty FROM `$tempstk` WHERE `itemcode` ='$itemcode' AND `series`= '$serno3' ") or die(mysqli_error($conn));
            $getqty3 = mysqli_fetch_object($tqry3);
            $opening5 = $getqty3->totqty;
            if ($opening5 != 0) {
                $opening4 = $opening5;
            } else {
                $opening4 = 0;
            }

            ########### INWARD ##################

            $inqry = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
            $getin = mysqli_fetch_object($inqry);
            $inward = abs($getin->totqty1);

            $inqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
            $getin1 = mysqli_fetch_object($inqry1);
            $inward1 = abs($getin1->totqty1);

            $inqry2 = mysqli_query($conn, "SELECT SUM(qty) AS totqty1 FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3 AND `vouchdt` >= '$fromdt'  AND `vouchdt` <= '$todt' AND `qty` > 0 GROUP BY `itemcode` ") or die(mysqli_error($conn));
            $getin2 = mysqli_fetch_object($inqry2);
            $inward2 = abs($getin2->totqty1);

            ########### OUTWARD  ##################

            $otqry = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
            $getot = mysqli_fetch_object($otqry);
            $outward = ABS($getot->totqty);

            $otqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
            $getot1 = mysqli_fetch_object($otqry1);
            $outward1 = ABS($getot1->totqty);

            $otqry2 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3  AND `vouchdt` >= '$fromdt' AND `vouchdt` <= '$todt' AND  `qty` < 0  GROUP BY `itemcode`") or die(mysqli_error($conn));
            $getot2 = mysqli_fetch_object($otqry2);
            $outward2 = ABS($getot2->totqty);

            $totbal = $opening + $inward - $outward;
            $totbal1 = $opening3 + $inward1 - $outward1;
            $totbal2 = $opening5 + $inward2 - $outward2;

            $a = 1;
            if ($zeroprn == 1) {
                if ($totbal != 0 || $totbal1 != 0 || $totbal2 != 0) {
                    $a = 1;
                } else {
                    $a = 0;
                }
            }

            if ($a == 1) {
                echo $ctr;
                echo "$name";
                echo $itemcode;
                echo "$zb1";
                echo "$zbx";
                echo "$str";
                echo "$total";
                echo "$cost";
                echo "$value";


                $gropen = $gropen + $opening;
                $grinw = $grinw + $inward;
                $grout = $grout + $outward;
//                                        } 

                echo "<td style='text-align:right;'>";
                echo "<font><b> " . number_format("$totbal", 0, ".", ",") . "</b>";
                echo "</td>";

                echo "<td style='text-align:right;'>";
                echo "<font><b> " . number_format("$totbal1", 0, ".", ",") . "</b>";
                echo "</td>";

                echo "<td style='text-align:right;'>";
                echo "<font><b> " . number_format("$totbal2", 0, ".", ",") . "</b>";
                echo "</td>";

                $totbalance = $totbal + $totbal1 + $totbal2;

                $itemvalue = $cost * $totbalance;

                echo "<td style='text-align:right;'>";
                echo "<font><b> " . number_format("$totbalance", 0, ".", ",") . "</b>";
                echo "</td>";
                if ($prnval == 2) {

                    echo "<td style='text-align:right;'>";
                    echo "<font><b> " . number_format("$cost", 0, ".", ",") . "</b>";
                    echo "</td>";

                    echo "<td style='text-align:right;'>";
                    echo "<font><b> " . number_format("$itemvalue", 0, ".", ",") . "</b>";
                    echo "</td>";
                }

                echo "</tr>";
                $grbal = $grbal + $totbal;
                $grbal1 = $grbal1 + $totbal1;
                $grbal2 = $grbal2 + $totbal2;
                $grtot = $grtot + $totbalance;
                $grval = $grval + $itemvalue;
            }
        }

        echo "<tr>";
        echo "<td>";
        echo "</td>";
        echo "<td>";
        echo "</td>";
        echo "<td align='right'>";
        echo "Total";
        echo "</td>";

        echo "<td align='right'>";
        echo "<font><b> " . $grbal . "</b>";
        echo "</td>";
        echo "<td align='right'>";
        echo "<font><b> " . $grbal1 . "</b>";
        echo "</td>";
        echo "<td align='right'>";
        echo "<font><b> " . $grbal2 . "</b>";
        echo "</td>";
        echo "<td align='right'>";
        echo "<font><b> " . $grtot . "</b>";
        echo "</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>";
        ?>
        <?php
        $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;
        }
    }
}
?>
