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

$formname = 'Groupwise Sale Register';
$grid = 'itemgrpsale.php';
$zroot = 'cepheisy_msp_root';

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

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

    $dtype = $_POST["dtype"];
    if ($dtype == 1) {

        $maindb = 'saletran';
        $trandb = 'item';

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

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


        $mfrmdate = trim($_POST["frmdt"]);
        $mtodate = trim($_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);
        $get = mysqli_query($conn, "SELECT a.vouchdt,  sum(a.qty) as totqty, sum(a.total) as total, b.itemgrp from `$maindb` a,
                        `$trandb` b where a.item = b.code and a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $icond $pcond group by b.itemgrp") or die(mysqli_error($conn));

        $getno = mysqli_num_rows($get);
        if ($getno == 0) {

            browse("Record Not Found");
            echo "<script>
            window.close();
            </script>";
        }

        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">
                <div class="row">
                    <div class="col-lg-6">
                        <label class="label-control marg">Date - </label>
                        <label class="label-control marg"><?php Echo Date("d-m-Y"); ?></label>
                    </div>
                    <div class="col-lg-5">
                        <label class="label-control marg">Print - </label>
                        <label class="label-control marg"><?php Echo $formname; ?></label>
                    </div>
                    <div class="col-lg-1">
                        <a onclick="window.close()" class="btn btn-primary">GO Back</a>
                    </div>
                </div>
                <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 align="left" width=''>Item Group<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style=text-align:right;" width=''>Qty.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                    <th style=text-align:right;" width=''>Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $tqty = 0;
                                $trate = 0;
                                $totamt = 0;


//                                            echo "<label>Item Name - $mitem</label>";
                                $ctr = 0;
                                while ($row = mysqli_fetch_object($get)) {
                                    mysqli_select_db($conn, $zdb);
                                    $getitemgrp = mysqli_query($conn, "SELECT * FROM `itemgrp` WHERE  `code`='$row->itemgrp' ORDER BY `name`") or die(mysqli_error($conn));
                                    $itemgrp = mysqli_fetch_object($getitemgrp);

                                    $ctr++;
                                    echo "<tr> ";
                                    #echo "<td align='left'>$ctr</td>";
                                    echo "<td align='left'>$itemgrp->name</td>";
                                    echo "<td align='right'>$row->totqty</td>";
                                    echo "<td align='right'>$row->total</td>";


                                    $tqty = $tqty + $row->totqty;
                                    $totamt = $totamt + $row->total;

                                    echo "</tr>";
                                }
                                echo "<tr style='background-color:white;'> ";
                                echo "<td align='right'><font><b>Total&nbsp;&nbsp;</font></b></td>";
                                echo "<td align='right'><font><b>" . number_format($tqty, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($totamt, 2, '.', '') . "</b></font></td>";
                                echo "</tr>";
                                ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>


        </body>
        </html>
        <?php
    } else {

        $maindb = 'saletran';
        $trandb = 'item';

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

        if (!empty($_POST["name"])) {
            $iname = trim($_POST["name"]);
            $icond = "AND a.item = '$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; itemgrpsale.php");
        }
        if (empty($_POST["todt"])) {
            browse("To Date Cannot Be Empty");
            header("refresh:1; itemgrpsale.php");
        }


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

        $get = mysqli_query($conn, "SELECT a.vouchdt, sum(a.qty) as totqty, sum(a.total) as total, b.itemgrp, b.name as itemname from `$maindb` a,
                        `$trandb` b where a.item = b.code and a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $icond $pcond 
                        group by b.itemgrp,b.name ") or die(mysqli_error($conn));

        $getno = mysqli_num_rows($get);
//        $getno1 = mysqli_fetch_object($get);
        if ($getno == 0) {

            browse("Record Not Found");
            echo "<script>
            window.close();
            </script>";
        }

        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">
                <div class="row">
                    <div class="col-lg-6">
                        <label class="label-control marg">Date - </label>
                        <label class="label-control marg"><?php Echo Date("d-m-Y"); ?></label>
                    </div>
                    <div class="col-lg-5">
                        <label class="label-control marg">Print - </label>
                        <label class="label-control marg"><?php Echo $formname; ?></label>
                    </div>
                    <div class="col-lg-1">
                        <a onclick="window.close()" class="btn btn-primary">GO Back</a>
                    </div>
                </div>
                <div class="row">
                    <div class="col-lg-12 pad">
                        <table style="background: white;width: 100%" class="table table-condensed">
                            <tbody>
                                <?php
                                $tqty = 0;
                                $trate = 0;
                                $totamt = 0;
                                $tname = '';
//                                $aqty = 0;
//                                $aamt = 0;
                                $a1 = 0;
                                $b1 = 0;


//                                $state = '';
//                            echo "<label>Item Group - $mitemgrp</label>";
                                $ctr = 0;
                                while ($row = mysqli_fetch_object($get)) {
                                    mysqli_select_db($conn, $zdb);
                                    $getitemgrp = mysqli_query($conn, "SELECT code, name FROM `itemgrp` WHERE  `code`='$row->itemgrp' ORDER BY `name`") or die(mysqli_error($conn));
                                    $itemgrp = mysqli_fetch_object($getitemgrp);
                                    $grpname = $itemgrp->name;

                                    if ($grpname != $tname) {
                                        if ($a1 == 0) {
                                            ?>
                                            <tr>
                                                <td style=""><b>Item Group : - <?= $grpname ?> </b></td>
                                            </tr>

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            <!--<thead class="mySearchTr">-->
                                            <tr class="mySearchTr">
                                                <th align="left" width=''>Item Name<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                                <th style=text-align:right;" width=''>Qty.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                                <th style=text-align:right;" width=''>Amount<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                            </tr>
                                            <!--</thead>-->      
                                            <?php
                                        }
                                        if ($a1 > 0) {
                                            ?>
                    <!--                                            <tr>
                                                <td colspan="" style="text-align: right; "><b>Total</b></td>
                                                <td style="text-align: right; "><? echo $aqty; ?></td>
                                                <td></td>
                                                <td style="text-align: right; "><? echo $aamt; ?></td>
                                                <td></td>
                                            </tr>-->
                                            <?php
                                            $aqty = 0;
                                            $aamt = 0;
                                        }
                                        $tname = $grpname;
                                    }



                                    //                                    if ($state != $row->itemgrp) {
                                    ////                                        if (!$first_color) {
                                    ////                                            $first_color = true;
                                    ////                                        }
                                    //                                        $ctr = 1;
                                    //                                        echo "<tr style='background-color:white;height:30px;'><td><b>Item Group</b> $itemgrp->name </td></tr>";
                                    //                                        $state = $row->itemgrp;
                                    //                                    }
                                    $ctr++;

                                    echo "<tr> ";
                                    #echo "<td align='left'>$ctr</td>";
                                    echo "<td align='left'>$row->itemname</td>";
                                    echo "<td align='right'>$row->totqty</td>";
                                    echo "<td align='right'>$row->total</td>";




                                    echo "</tr>";

                                    $aqty = $row->totqty + $aqty;
                                    $aamt = $row->total + $aamt;
                                    $tqty = $tqty + $row->totqty;
                                    $totamt = $totamt + $row->total;
                                    $b1 = $b1 + 1;
                                }
                                ?>
        <!--                                        <tr>
        <td colspan="" style="text-align: right; "><b>Total</b></td>
        <td style="text-align: right; "><? // echo $aqty;          ?></td>
        <td></td>
        <td style="text-align: right; "><? // echo $aamt;          ?></td>
        <td></td>
        </tr>-->
                                <?php
                                echo "<tr style='background-color:white;'> ";
                                echo "<td align='right'><font><b>Grand Total&nbsp;&nbsp;</font></b></td>";
                                echo "<td align='right'><font><b>" . number_format($tqty, 2, '.', '') . "</b></font></td>";
                                echo "<td align='right'><font color='green'><b>" . number_format($totamt, 2, '.', '') . "</b></font></td>";
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>


    </body>
    </html>

    <?php
}
if (isset($_POST["excel"])) {

    $dtype = $_POST["dtype"];
    if ($dtype == 1) {

        $maindb = 'saletran';
        $trandb = 'item';

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

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


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

        $get = mysqli_query($conn, "SELECT a.vouchdt,  sum(a.qty) as totqty, sum(a.total) as total, b.itemgrp from `$maindb` a,
                        `$trandb` b where a.item = b.code and a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $icond $pcond group by b.itemgrp") or die(mysqli_error($conn));

        $getno = mysqli_num_rows($get);

        $contents.="Item Group,Qty.,Amount\n";

        $tqty = 0;
        $trate = 0;
        $totamt = 0;

        $ctr = 0;
        while ($row = mysqli_fetch_object($get)) {
            mysqli_select_db($conn, $zdb);
            $getitemgrp = mysqli_query($conn, "SELECT * FROM `itemgrp` WHERE  `code`='$row->itemgrp' ORDER BY `name`") or die(mysqli_error($conn));
            $itemgrp = mysqli_fetch_object($getitemgrp);

            $ctr++;
            $pitemgrp = $itemgrp->name;
            $ptotqty = $row->totqty;
            $ptotal = $row->total;

            $contents.=$pitemgrp . ",";
            $contents.=$ptotqty . ",";
            $contents.=$ptotal . "\n";

            $tqty = $tqty + $row->totqty;
            $totamt = $totamt + $row->total;
        }
        $contents.="Total" . ",";
        $contents.="$tqty" . ",";
        $contents.="$totamt" . "\n";
    } else {

        $maindb = 'saletran';
        $trandb = 'item';

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

        if (!empty($_POST["name"])) {
            $iname = trim($_POST["name"]);
            $icond = "AND a.item = '$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; itemgrpsale.php");
        }
        if (empty($_POST["todt"])) {
            browse("To Date Cannot Be Empty");
            header("refresh:1; itemgrpsale.php");
        }


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

        $get = mysqli_query($conn, "SELECT a.vouchdt, sum(a.qty) as totqty, sum(a.total) as total, b.itemgrp, b.name as itemname from `$maindb` a,
                        `$trandb` b where a.item = b.code and a.vouchdt >= '$mfrmdate' and a.vouchdt <= '$mtodate' $icond $pcond 
                        group by b.itemgrp,b.name ") or die(mysqli_error($conn));

        $getno = mysqli_num_rows($get);

        $tqty = 0;
        $trate = 0;
        $totamt = 0;
        $tname = '';

        $a1 = 0;
        $b1 = 0;

        $ctr = 0;
        while ($row = mysqli_fetch_object($get)) {
            mysqli_select_db($conn, $zdb);
            $getitemgrp = mysqli_query($conn, "SELECT code, name FROM `itemgrp` WHERE  `code`='$row->itemgrp' ORDER BY `name`") or die(mysqli_error($conn));
            $itemgrp = mysqli_fetch_object($getitemgrp);
            $grpname = $itemgrp->name;

            if ($grpname != $tname) {
                if ($a1 == 0) {

                    $contents.="Item Group :-" . "$grpname" . "\n";

                    $contents.="Itemname,Qty.,Amount\n";
                }
                if ($a1 > 0) {
                    $aqty = 0;
                    $aamt = 0;
                }
                $tname = $grpname;
            }
            $ctr++;
            $pitemname = $row->itemname;
            $ptotqty = $row->totqty;
            $ptotal = $row->total;

            $contents.=$pitemname . ",";
            $contents.=$ptotqty . ",";
            $contents.=$ptotal . "\n";

            $aqty = $row->totqty + $aqty;
            $aamt = $row->total + $aamt;

            $tqty = $tqty + $row->totqty;
            $totamt = $totamt + $row->total;
            $b1 = $b1 + 1;
        }

        $contents.="Grand Total" . ",";
        $contents.="$tqty" . ",";
        $contents.="$totamt" . "\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;
    }
}
?>