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

ini_set("display_errors", 1);
error_reporting(E_ERROR);

$formname = 'Raw Material wise Sale Register';
$maindb = 'rawmain';
$trandb = 'rawtran';
$grid = 'rawmatwise_sale.php';

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

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

    $pcond = "";
    $brcond = "";
    $icond = "";
    $gcond = "";
    $subcond = "";
    $typecond = "";

    if (!empty($_POST["pname"])) {
        $pname = trim($_POST["pname"]);
        $pcond = "AND b.`party` = '$pname'";
    }

    $gbrcd = trim($_POST["itemcode"]);
    $icode = $_POST["name"];
    $gname = trim($_POST["itemgrp"]);
    $gprodtype = trim($_POST["prodtype"]);
    $gsubgrp = trim($_POST["subgrp"]);


    if (!empty($_POST["itemcode"])) {
        $brcond = " and a.`item` = '$gbrcd' ";
    }

    if (!empty($_POST["name"])) {
        $icond = " and a.`item` = '$icode' ";
    }

    if (!empty($_POST["itemgrp"])) {
        $gcond = " and b.`rawmatgrp` = '$gname' ";
    }

    if (!empty($_POST["subgrp"])) {
        $subcond = " and b.`itemsubgrp` = '$gsubgrp' ";
    }

    if (!empty($_POST["prodtype"])) {
        $typecond = " and b.`prodtype` = '$gprodtype' ";
    }

    $orderby = $_POST["orderby"];
    if ($orderby == 1) {
        $ordsumm = ' order by `totamt` desc ';
    } else {
        $ordsumm = ' order by b.name ';
    }


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

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

    //    $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);

    mysqli_query($conn, "create temporary table datatmp SELECT a.item, a.qty, a.amount, DATE_FORMAT(b.`invdt`, '%d-%m-%Y') as mydt, b.vouchdt, b.vouchno, 
                                       b.lesother + b.discount as totdisc, a.amount -((a.amount/b.subtotal)*(b.lesother+b.discount)) as itamt, 
                                       b.subtotal FROM `$trandb` a, `$maindb` b WHERE b.vouchdt >= '$mfrmdate' AND b.vouchdt <= '$mtodate' 
                                       AND a.`vouchno`=b.`vouchno` $icond $pcond ") or die(mysqli_error($conn));

    //    $get1 =  mysqli_query($conn, "select a.item, a.qty, a.amount, DATE_FORMAT(b.`invdt`, '%d-%m-%Y') as mydt, b.vouchdt, b.vouchno, 
//                                      b.lesother + b.discount as totdisc, a.amount -((a.amount/b.subtotal)*(b.lesother+b.discount)) as itamt, 
//                                       b.subtotal FROM `$trandb` a, `$maindb` b WHERE b.vouchdt >= '$mfrmdate' AND b.vouchdt <= '$mtodate' 
//                                       AND a.`vouchno`=b.`vouchno` $icond $pcond ") or die(mysqli_error($conn));
//    print table ($get1) ;
//    die();


    $get = mysqli_query($conn, "SELECT a.*, b.name as itemname, sum(a.qty) as totqty, sum(a.itamt) as totamt from datatmp a, rawmat b 
                                       where a.item = b.code $gcond $subcond $typecond group by a.item $ordsumm ") 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">
            <?php include("includes/head_rep.php"); ?>

            <!--            <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: 70%" class="table table-condensed table-hover">
                        <thead class="mySearchTr">
                            <tr>
                                <th align="left" width='5%'>Sr.<i class="icon-sort"></i><i class="icon-sort-down"></i> <i
                                        class="icon-sort-up"></i></th>
                                <th align="left" width='40%'>Raw Material Name<i class="icon-sort"></i><i
                                        class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <th align="right" width='15%' style="text-align: right;">Qty.<i class="icon-sort"></i><i
                                        class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>
                                <th align="right" width='15%' style="text-align: right;">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;
                            $tamt = 0;

                            if ("$gname" != '') {
                                $getgrp = mysqli_query($conn, "SELECT `name` FROM `rawmatgrp` WHERE code='$gname'") or die(mysqli_error($conn));
                                $fetgrp = mysqli_fetch_object($getgrp);
                                $grpname = $fetgrp->name;

                                echo "<label>Item Group - $grpname</label>";
                            }
                            if ("$gsubgrp" != '') {
                                $getgrpsub = mysqli_query($conn, "SELECT `name` FROM `itemsubgrp` WHERE code='$gsubgrp'") or die(mysqli_error($conn));
                                $fetgrpsub = mysqli_fetch_object($getgrpsub);
                                $grpsub = $fetgrpsub->name;

                                echo "<label>Sub Group - $grpsub</label>";
                            }

                            $ctr = 0;
                            while ($row = mysqli_fetch_object($get)) {
                                $itemname = $row->itemname;
                                $totqty = $row->totqty;
                                $totamt = $row->totamt;

                                $ctr++;
                                echo "<tr> ";
                                echo "<td align='left'>$ctr</td>";
                                echo "<td align='left'>$itemname</td>";
                                echo "<td align='right'>" . number_format($totqty, 2) . "</td>";
                                echo "<td align='right'>" . number_format($totamt, 2) . "</td>";

                                $tqty = $tqty + $totqty;
                                $tamt = $tamt + $totamt;

                                echo "</tr>";
                            }
                            echo "<tr style='background-color:white;'> ";
                            echo "<td align='left'></td>";
                            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><b>" . number_format($tamt, 2, '.', '') . "</b></font></td>";
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

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

    </html>
    <?php
}

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

    $pcond = "";
    $brcond = "";
    $icond = "";
    $gcond = "";
    $subcond = "";
    $typecond = "";

    if (!empty($_POST["pname"])) {
        $pname = trim($_POST["pname"]);
        $pcond = "AND b.`party` = '$pname'";
    }

    $gbrcd = trim($_POST["itemcode"]);
    $icode = $_POST["name"];
    $gname = trim($_POST["itemgrp"]);
    $gprodtype = trim($_POST["prodtype"]);
    $gsubgrp = trim($_POST["subgrp"]);


    if (!empty($_POST["itemcode"])) {
        $brcond = " and a.`item` = '$gbrcd' ";
    }

    if (!empty($_POST["name"])) {
        $icond = " and a.`item` = '$icode' ";
    }

    if (!empty($_POST["itemgrp"])) {
        $gcond = " and b.`rawmatgrp` = '$gname' ";
    }

    if (!empty($_POST["subgrp"])) {
        $subcond = " and b.`itemsubgrp` = '$gsubgrp' ";
    }

    if (!empty($_POST["prodtype"])) {
        $typecond = " and b.`prodtype` = '$gprodtype' ";
    }

    $orderby = $_POST["orderby"];
    if ($orderby == 1) {
        $ordsumm = ' order by `totamt` desc ';
    } else {
        $ordsumm = ' order by b.name ';
    }


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

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


    mysqli_query($conn, "create temporary table datatmp SELECT a.item, a.qty, a.amount, DATE_FORMAT(b.`invdt`, '%d-%m-%Y') as mydt, b.vouchdt, b.vouchno, 
                                       b.lesother + b.discount as totdisc, a.amount -((a.amount/b.subtotal)*(b.lesother+b.discount)) as itamt, 
                                       b.subtotal FROM `$trandb` a, `$maindb` b WHERE b.vouchdt >= '$mfrmdate' AND b.vouchdt <= '$mtodate' 
                                       AND a.`vouchno`=b.`vouchno` $icond $pcond ") or die(mysqli_error($conn));




    $get = mysqli_query($conn, "SELECT a.*, b.name as itemname, sum(a.qty) as totqty, sum(a.itamt) as totamt from datatmp a, rawmat b 
                                       where a.item = b.code $gcond $subcond $typecond group by a.item $ordsumm ") or die(mysqli_error($conn));
    $getno = mysqli_num_rows($get);

    if ($getno == 0) {
    }
    $mydt = Date("d-m-Y");
    $contents .= "Date - $mydt" . "\n";
    $contents .= "$formname" . "\n";

    $contents .= "Sr., Raw Material Name, Qty., Amount \n";



    $tqty = 0;
    $tamt = 0;

    if ("$gname" != '') {
        $getgrp = mysqli_query($conn, "SELECT `name` FROM `rawmatgrp` WHERE code='$gname'") or die(mysqli_error($conn));
        $fetgrp = mysqli_fetch_object($getgrp);
        $grpname = $fetgrp->name;

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



    }
    if ("$gsubgrp" != '') {
        $getgrpsub = mysqli_query($conn, "SELECT `name` FROM `itemsubgrp` WHERE code='$gsubgrp'") or die(mysqli_error($conn));
        $fetgrpsub = mysqli_fetch_object($getgrpsub);
        $grpsub = $fetgrpsub->name;


        $contents .= "Sub Group - $grpsub" . "\n";


    }

    $ctr = 0;
    while ($row = mysqli_fetch_object($get)) {
        $itemname = $row->itemname;
        $totqty = $row->totqty;
        $totamt = $row->totamt;

        $ctr++;

        $contents .= "$ctr" . ",";
        $contents .= "$itemname" . ",";
        $contents .= "$totqty" . ",";
        $contents .= "$totamt" . ",";

        $tqty = $tqty + $totqty;
        $tamt = $tamt + $totamt;

        $contents .= "" . "\n";
    }
    $contents .= "" . ",";
    $contents .= "Total" . ",";
    $contents .= "$tqty" . ",";
    $contents .= "$tamt" . "\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;
    }
}

if (isset($_POST["subpdf"])) {
    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["pname"])) {
        $pname = trim($_POST["pname"]);
        $pcond = "AND b.`party` = '$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"]);

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

    $get = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(b.`invdt`, '%d-%m-%Y') as mydt, b.vouchdt,  b.vouchno, b.party, b.netamt FROM `$trandb`a, `$maindb`b
                       WHERE b.vouchdt >= '$mfrmdate' AND b.vouchdt <= '$mtodate' AND a.`vouchno`=b.`vouchno` $icond $pcond  order by a.vouchno") or die(mysqli_error($conn));

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

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


    require("library/fpdf/fpdf.php");

    class pdf extends fpdf
    {

        function Footer()
        {



            //Position at 1.5 cm from bottom

            $this->SetY(-15);

            //Arial italic 8

            $this->SetFont('Arial', 'I', 8);

            //Page number

            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        }

    }

    $pdf = new pdf('P', 'mm', 'A4');

    $pdf->AliasNbPages();

    $pdf->AddPage();
    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(96, 130, 191);
    $pdf->SetFillColor(255, 255, 255);
    $pdf->Cell(193, 7, "Item Name - " . $mitem, 1, 0, '', 1);
    $pdf->SetFont('Arial', '', 1, 0);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();


    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(96, 130, 191);
    $pdf->Cell(20, 7, "Voucher", 1, 0, '', 1);
    $pdf->Cell(30, 7, "Date", 1, 0, '', 1);
    $pdf->Cell(53, 7, "Party Name", 1, 0, '', 1);
    $pdf->Cell(30, 7, "Qty.", 1, 0, '', 1);
    $pdf->Cell(30, 7, "Rate", 1, 0, '', 1);
    $pdf->Cell(30, 7, "Bill Amount", 1, 0, '', 1);
    $pdf->SetFont('Arial', '', 1, 0);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();

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

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

        $ctr++;
        $amount = $row->rate * $row->qty;
        $pdf->SetFont('Arial', '', 8);
        $pdf->Cell(20, 7, "$row->vouchno", 1);
        $pdf->Cell(30, 7, "$row->mydt", 1);
        $pdf->Cell(53, 7, "$party->name", 1);
        $pdf->Cell(30, 7, "$row->qty", 1);
        $pdf->Cell(30, 7, "$row->rate", 1);
        $pdf->Cell(30, 7, "$amount", 1);
        $pdf->Ln();

        $tqty = $tqty + $row->qty;
        $trate = $trate + $row->rate;
        $totamt = $totamt + $amount;
    }
    $pdf->SetFont('Arial', 'I', 10);
    $pdf->SetTextColor(255, 255, 255);
    $pdf->SetFillColor(255, 0, 0);
    $pdf->Cell(20, 7, " ", 1, 0, '', 1);
    $pdf->Cell(30, 7, " ", 1, 0, '', 1);
    $pdf->Cell(53, 7, "Total", 1, 0, '', 1);
    $pdf->Cell(30, 7, number_format($tqty, 2, '.', ''), 1, 0, '', 1);
    $pdf->Cell(30, 7, number_format($trate, 2, '.', ''), 1, 0, '', 1);
    $pdf->Cell(30, 7, number_format($totamt, 2, '.', ''), 1, 0, '', 1);
    $pdf->SetFont('Arial', '', 1, 0);

    $pdf->SetTextColor(0, 0, 0);
    $pdf->Ln();

    $pdf->Output("ItemWise Sale.pdf", "D");
}
?>