<?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 = 'tran';
    $stkcdb = $trandb . $myyear;



    $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;
    }

    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");
                            while ($cmain = mysqli_fetch_array($getmain)) {
                                $ctr++;

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

                                $name = $cmain["name"];

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

                                $opening = 0;
                                $tqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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 SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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 SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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"])) {

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

    $maindb = 'item';
    $trandb = 'tran';
    $stkcdb = $trandb . $myyear;

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

    if (!empty($_POST["name"])) {
        $iname = trim($_POST["name"]);
        $icond = "AND a.item = '$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;
    }

    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"];

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

    $getno = mysqli_num_rows($getmain);


    $contents.="Sr.,Barcode,Name,Bal. 1,Bal. 2,Bal. 3,Total,";

    if ($prnval == 2) {
        $contents.="Cost,Value\n";
    }
    mysqli_select_db($conn, $zdb);

    $gropen = 0;
    $grinw = 0;
    $grout = 0;
    $grbal = 0;
    $grval = 0;
    $grbook = 0;
    $grbkafbl = 0;
    $ctr = 0;

    require("includes/dbconnect.php");
    while ($cmain = mysqli_fetch_array($getmain)) {
        $ctr++;

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

        $name = $cmain["name"];

        $contents.=" " . "\n";

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

        $opening = 0;
        $tqry1 = mysqli_query($conn, "SELECT SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=1 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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 SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=2 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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 SUM(qty) AS totqty FROM `$stkcdb` WHERE `itemcode` ='$itemcode' AND `compcode`=3 AND `vouchdt` < '$fromdt' GROUP BY `itemcode`") 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) {
            $contents.="$ctr" . ",";
            $contents.="$itemcode" . ",";
            $contents.="$name" . ",";

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

            $contents.="$totbal" . ",";
            $contents.="$totbal1" . ",";
            $contents.="$totbal2" . ",";

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

            $itemvalue = $cost * $totbalance;

            $contents.="$totbalance" . "\n";
            if ($prnval == 2) {
                $contents.="$cost" . ",";
                $contents.="$itemvalue" . ",";
            }

            $grbal = $grbal + $totbal;
            $grbal1 = $grbal1 + $totbal1;
            $grbal2 = $grbal2 + $totbal2;
            $grtot = $grtot + $totbalance;
            $grval = $grval + $itemvalue;
        }
    }
    $contents.=" " . ",";
    $contents.=" " . ",";
    $contents . " " . ",";
    $contents.="Total" . ",";

    $contents.="$grbal" . ",";
    $contents.="$grbal1" . ",";
    $contents.="$grbal2" . ",";
    $contents.="$grtot" . ",";

    if ($prnval == 2) {

        $contents.=" " . ",";
        $contents.="$grval" . "\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;
    }
}
?>