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

$maindb = "item";
$trandb = "transacn";
$formname = "Stock New";
$file = "stock_new.php";

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

if (isset($_POST["view"])) {
    $name = mysqli_real_escape_string($conn, $_POST["name"]);
    $asdt = $_POST["asdt"];
    $brand = $_POST["brand"];
    $type = $_POST["type"];
    $smode = $_POST["smode"];
    $godown = $_POST["godown"];
    $quality = $_REQUEST['quality'];

    $namecond = "";
    $qualitycond = "";
    $brandcond = "";
    $typecond = "";
    $smodecond = "";
    $godowncond = "";

    if (!empty($name)) {
        $namecond = " AND b.`name` LIKE '$name%' ";
    }
    if (!empty($quality)) {
//        $qualitycond = " AND `quality` LIKE '$quality' ";
        $qualitycond = " AND b.`quality` = '$quality' ";
    }
    if (!empty($brand)) {
        $brandcond = " AND b.`brand`='$brand' ";
    }
    if (!empty($type)) {
        $typecond = " AND a.`type`='$type' ";
    }

    if (!empty($smode)) {
        $smodecond = " AND a.`salemode`='$smode' ";
    }

    if (!empty($godown)) {
        $godowncond = " AND a.`godown`='$godown' ";
    }


    mysqli_query($conn, "drop temporary table if existS itemdbf ") or die(mysqli_error($conn));
    mysqli_query($conn, "create temporary table itemdbf 
                                SELECT a.itemcode, a.baleqty, sum(a.qty) as qty, b.cost, b.code, b.name as itemname, a.brand, a.containerno  
                                       FROM `transacn` a, `item` b 
                                       WHERE a.`itemcode` =  b.`barcode` AND a.`vouchdt` <= '$asdt' 
                                       $namecond $brandcond $qualitycond $yarncond $typecond $shadecond 
                                       GROUP BY a.itemcode, a.brand, a.containerno, a.baleqty having sum(a.qty) > 1 ") or die(mysqli_error($conn));

    $getitem = mysqli_query($conn, "SELECT a.*, b.name as brandname FROM itemdbf a, brand b where a.brand = b.code 
                                           GROUP BY a.itemcode, a.brand, a.baleqty ORDER BY a.`code` ")or die(mysqli_error($conn));
//    print table($getitem);

    include("includes/linkshead.php");
    ?>
    <script src="MyScript/item.js" type="text/javascript"></script>
    </head>
    <body>
        <div class="container">
            <div class="row">
                <?php include("includes/head_rep.php"); ?>
                <div class="col-lg-12 pad">
                    <div class="panel panel-default">
                        <div class="panel-body">
                            <div class="col-lg-12 pad">
                                <table class="table table-condensed table-bordered">
                                    <tr style="background-color: #1ABC9C;">
                                        <th class="text-center" style="width:7%;">CODE</th>
                                        <th class="text-center" style="width:25%;">ITEMNAME</th>
                                        <th class="text-center" style="width:8%;">LOGO</th>
                                        <th class="text-center" style="width:5%;">KGS.</th>
                                        <th style="width:55%;">CONTAINER NO./ QTY.</th>
                                    </tr>
                                    <?php
                                    while ($item = mysqli_fetch_array($getitem)) {
                                        $icode = $item['code'];
                                        $itemcode = $item['itemcode'];
                                        $brand = $item['brand'];
                                        $bale = $item['baleqty'];
                                        $brandname = $item['brandname'];
                                        $itemname = $item['itemname'];
                                        ?>
                                        <tr>
                                            <td class="text-center"><label class="label-control"><?php Echo $icode ; ?></label></td>
                                            <td class="text-center"><label class="label-control"><?php Echo $itemname ; ?></label></td>
                                            <td class="text-center"><label class="label-control"><?php Echo $brandname ; ?></label></td>
                                            <td class="text-center"><label class="label-control"><?php Echo number_format($bale) ; ?></label></td>
                                            <td>
                                                <?php
                                                $getstock = mysqli_query($conn, "SELECT SUM(qty) as actqty, SUM(flag) as tflag, shade, containerno
                                                                    FROM `transacn` WHERE `itemcode`='$itemcode' and `brand`='$brand' 
                                                                    AND `vouchdt`<='$asdt' GROUP BY containerno
                                                                    having sum(totqty) > 1 ORDER BY containerno") or die(mysqli_error($conn));
//                                                if ($icode == 1965) {
//                                                print table($getstock) ;
//                                                die();
//                                                }

                                                while ($stock = mysqli_fetch_array($getstock)) {
                                                    $actcol = "";
                                                    if ($stock['actqty'] < 0) {
                                                        $actcol = "style='color : red;'";
                                                    }
                                                    if ($stock['actqty'] != 0) {
                                                        ?>

                                                        <label class="label-control" style="color: #1ABC9C;">
                                                            <?php Echo $stock['containerno'] ?>
                                                        </label><label class="label-control">
                                                            &nbsp;/&nbsp;
                                                        </label>
                                                        <label class="label-control" <?php Echo $actcol; ?>> 
                                                            <?php
                                                            Echo number_format($stock['actqty']);
                                                            ?>
                                                        </label>
                                                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                                        <?php
                                                        $totqty = $stock['actqty'] + $totqty;
                                                    }
                                                }
                                                ?>
                                            </td>
                                            <?php
                                        }
                                        ?>
                                    </tr>
                                    <tr>
                                        <td></td>
                                        <td></td>
                                        <td></td>
                                        <td><label class="label-control" style="color: red;">TOTAL</label></td>
                                        <td><label class="label-control" style="color: red;"><? Echo $totqty; ?></label></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <?php
    }


    if (isset($_POST["subpdf"])) {
        $name = $_POST["name"];
        $asdt = $_POST["asdt"];
        $brand = $_POST["brand"];
        $type = $_POST["type"];
        $smode = $_POST["smode"];
        $godown = $_POST["godown"];
        $quality = $_REQUEST['quality'];


        $namecond = "";
        $qualitycond = "";
        $brandcond = "";
        $typecond = "";
        $smodecond = "";
        $godowncond = "";

        if (!empty($name)) {
            $namecond = " AND b.`name` LIKE '$name%' ";
        }
        if (!empty($quality)) {
//        $qualitycond = " AND `quality` LIKE '$quality' ";
            $qualitycond = " AND b.`quality` = '$quality' ";
        }
        if (!empty($brand)) {
            $brandcond = " AND b.`brand`='$brand' ";
        }
        if (!empty($type)) {
            $typecond = " AND a.`type`='$type' ";
        }
        if (!empty($smode)) {
            $smodecond = " AND a.`salemode`='$smode' ";
        }
        if (!empty($godown)) {
            $godowncond = " AND a.`godown`='$godown' ";
        }

//    $getitem = mysqli_query($conn, "SELECT * from item where code in (SELECT a.itemcode FROM `transacn` a, `item` b 
//                          WHERE a.`itemcode` =  b.`code` and a.`vouchdt`<='$asdt' $namecond $brandcond 
//                          $typecond $smodecond $godowncond
//                          GROUP BY a.itemcode, a.shade, a.actbatch having sum(a.actqty) > 1) 
//                          group by code order by name") or die(mysqli_error($conn));

        $getitem = mysqli_query($conn, "SELECT * from item where code in (SELECT itemcode FROM `transacn` a, `item` b 
                           WHERE a.`itemcode` =  b.`code` AND a.`vouchdt` <= '$asdt' 
                           $namecond $brandcond $qualitycond $yarncond $typecond $shadecond $qualitycond
                           GROUP BY a.itemcode, a.shade, a.actbatch having sum(a.totqty) > 1) 
                           group by code order by name") or die(mysqli_error($conn));

        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('L', 'mm', 'A4');

        $pdf->AliasNbPages();

        $pdf->AddPage();
        $pdf->SetFont('Arial', 'I', 6);
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(26, 188, 156);
        $pdf->Cell(8, 7, "Sr.", 1, 0, '', 1);
        $pdf->Cell(55, 7, "ItemName", 1, 0, '', 1);
        $pdf->Cell(55, 7, "Shade/Qty", 1, 0, '', 1);
        $pdf->SetFont('Arial', '', 1, 0);

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


        $ctr = 1;
        while ($pmain = mysqli_fetch_array($getitem)) {
            $pdf->SetFont('Arial', 'B', 6);
            $pdf->Cell(8, 7, "$ctr", 1, 'C');
            $pdf->Cell(55, 7, "$pmain[name]", 1);
            if ($pmain[actqty] != 0) {
                $pdf->Cell(55, 7, $pmain['shade'] . $actcol, 1);
                if ($pmain[tflag] > 0) {
                    $pdf->Cell(55, 7, $pmain['actqty'] . $pmain['tflag'], 1);
                } else {
                    $pdf->Cell(55, 7, $pmain['actqty'], 1);
                }
                $totqty = $pmain['actqty'] + $totqty;
            }
            $ctr++;
            $pdf->Ln();
        }

        $pdf->Output("stocknew.pdf", "D");
    }

    if (isset($_POST["excel"])) {
        $name = $_POST["name"];
        $asdt = $_POST["asdt"];
        $brand = $_POST["brand"];
        $type = $_POST["type"];
        $smode = $_POST["smode"];
        $godown = $_POST["godown"];

        $namecond = "";
        $brandcond = "";
        $typecond = "";
        $smodecond = "";
        $godowncond = "";

        if (!empty($name)) {
            $namecond = " AND b.`name` LIKE '%$name%' ";
        }
        if (!empty($brand)) {
            $brandcond = " AND b.`brand`='$brand' ";
        }
        if (!empty($type)) {
            $typecond = " AND a.`type`='$type' ";
        }
        if (!empty($smode)) {
            $smodecond = " AND a.`salemode`='$smode' ";
        }
        if (!empty($godown)) {
            $godowncond = " AND a.`godown`='$godown' ";
        }


        $getitem = mysqli_query($conn, "SELECT * from item where code in (SELECT itemcode FROM `transacn` a, `item` b 
                           WHERE a.`itemcode` =  b.`code` AND a.`vouchdt` <= '$asdt' 
                           $brandcond $qualitycond $yarncond $typecond $shadecond
                           GROUP BY a.itemcode, a.shade, a.actbatch having sum(a.totqty) > 1) 
                           group by code order by name") or die(mysqli_error($conn));

        $contents .= "ITEMNAME,SHADE/QTY\n";




        while ($item = mysqli_fetch_array($getitem)) {


            $getstock = mysqli_query($conn, "SELECT SUM(totqty) as actqty, SUM(flag) as tflag, shade, actbatch
                                                                    FROM `transacn` WHERE `itemcode`='$item[code]' 
                                                                    AND `vouchdt`<='$asdt' GROUP BY shade, actbatch 
                                                                    having sum(totqty) > 1 ORDER BY shade, actbatch") or die(mysqli_error($conn));
            while ($stock = mysqli_fetch_array($getstock)) {
                $name = $item['name'];
                $contents .= $name . ",";
                $actcol = "";
                if ($stock['actqty'] < 0) {
//                    $actcol = "style='color : red;'";
                }
                if ($stock['actqty'] != 0) {


//                    $shade = $stock[shade];
//                    $contents.=$shade . ",";

                    if ($stock['tflag'] > 0) {
                        $actflag = $stock['shade'] . "/" . $stock['actqty'] . " " . "($stock[tflag])" . "F";
                        $contents .= $actflag . "\n";
                    } else {
                        $act = $stock['actqty'];
                        $contents .= $act . "\n";
                    }

                    $totqty = $stock['actqty'] + $totqty;
                }
            }
        }
        $contents .= "\n";
        $contents .= "Total" . "$totqty " . "\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;
        }
    }
    ?>
                                

