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

$maindb = "item";
$trandb = "transacn";

$formname = "Stock Flow";
$file = "stock_flow.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"]);
    $brand = $_POST["brand"];
    $type = $_POST["type"];
    $smode = $_POST["smode"];
    $godown = $_POST["godown"];

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

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

    if (!empty($name)) {
        $namecond = " AND `name` LIKE '$name%' ";
        $namecond1 = " AND b.`name` LIKE '$name%' ";
    }

    if (!empty($brand)) {
        $brandcond = " AND `brand`='$brand' ";
        $brandcond1 = " AND b.`brand`='$brand' ";
    }

    if (!empty($type)) {
        $typecond = " AND `type`='$type' ";
        $typecond1 = " AND a.`type`='$type' ";
    }

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

    if (!empty($godown)) {
        $godowncond = " AND `godown`='$godown' ";
        $godowncond1 = " 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` >= '$fromdt' and a.`vouchdt` <= '$todt' 
//                           $namecond $brandcond $qualitycond $yarncond $typecond $shadecond
//                           GROUP BY a.itemcode, a.shade having sum(a.totqty) > 1) 
//                           group by code order by name") or die(mysqli_error($conn));


    $getitem = mysqli_query($conn, "SELECT * from item where name != '' $namecond $brandcond $qualitycond $yarncond $typecond $shadecond
                           GROUP BY code, shade  order by name, shade") or die(mysqli_error($conn));
//    print table($getitem);
//    die();

    include("includes/linkshead.php");
    ?>
    <script src="MyScript/item.js" type="text/javascript"></script>
    </head>
    <body style="width:100%">
        <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 style="width:2%;"> SR.</th>
                                        <th style="width:20%;">ITEMNAME</th>
                                        <th style="width:6%;">SHADE</th>
                                        <th style="width:6%;">OPEN</th>
                                        <th style="width:6%;">OPEN</th>
                                        <th style="width:6%;">PURCHASE</th>
                                        <th style="width:6%;">PURCHASE</th>
                                        <th style="width:5%;">SALE</th>
                                        <th style="width:6%;">SALE</th>
                                        <th style="width:5%;">OTHER</th>
                                        <th style="width:6%;">OTHER</th>
                                        <th style="width:6%;">OTHER</th>
                                        <th style="width:6%;">OTHER</th>
                                        <th style="width:6%;">BALANCE</th>
                                        <th style="width:6%;">BALANCE</th>
                                    </tr>

                                    <tr style="background-color: #1ABC9C; ">
                                        <th></th>
                                        <th></th>
                                        <th></th>
                                        <th>QTY.</th>
                                        <th>AMOUNT</th>
                                        <th>QTY.</th>
                                        <th>AMOUNT</th>
                                        <th>QTY.</th>
                                        <th>AMOUNT</th>
                                        <th>ADD</th>
                                        <th>LESS</th>
                                        <th>ADD AMT.</th>
                                        <th>LESS AMT.</th>
                                        <th>QTY.</th>
                                        <th>AMOUNT</th>
                                    </tr>

                                    <?php
                                    $ctr = 0;
                                    $openqty = $purqty = $salqty = $addqty = $lesqty = $balqty = 0;
                                    $openamt = $puramt = $salamt = $addamt = $lesamt = $balamt = 0;
                                   
                                    while ($item = mysqli_fetch_array($getitem)) {
                                        $ctr++;

                                        $getopen = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE 
                                                                        `itemcode`='$item[code]' and `shade`='$item[shade]' 
                                                                         AND `vouchdt` <='$fromdt' ") or die(mysqli_error($conn));
                                        $getop = mysqli_fetch_array($getopen);
                                        $openqty  = $getop[actqty];

                                        $getpur = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE 
                                                                        `itemcode`='$item[code]' and `shade`='$item[shade]' 
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'PU' ") or die(mysqli_error($conn));
                                        $getpu = mysqli_fetch_array($getpur);
                                        $purqty  = $getpu[actqty];

                                        $getsal = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE 
                                                                        `itemcode`='$item[code]' and `shade`='$item[shade]' 
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'SL' ") or die(mysqli_error($conn));
                                        $getsl = mysqli_fetch_array($getsal);
                                        $salqty  = $getsl[actqty];

                                        $getadd = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE 
                                                                        `itemcode`='$item[code]' and `shade`='$item[shade]' 
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source != 'PU' and totqty > 0 ") or die(mysqli_error($conn));
                                        $othad = mysqli_fetch_array($getadd);
                                        $addqty = $othad[actqty];

                                        $getless = mysqli_query($conn, "SELECT SUM(totqty) as actqty FROM `transacn` WHERE 
                                                                        `itemcode`='$item[code]' and `shade`='$item[shade]' 
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source != 'SL' and totqty < 0 ") or die(mysqli_error($conn));
                                        $othls = mysqli_fetch_array($getless);
                                        $lesqty = $othls[actqty];
                                        
                                        $balqty = $openqty + $purqty - abs($salqty) + $addqty - abs($lesqty) ;
                                        $balamt = $openamt + $puramt - abs($salamt) + $addamt - abs($lesamt) ;
                                        
                                        ?>
                                    
                                        <tr>
                                            <td><label class="label-control"><?php Echo $ctr ?></label></td>
                                            <td><label class="label-control"><?php Echo $item[name]; ?></label></td>
                                            <td><label class="label-control"><?php Echo $item[shade]; ?></label></td>

                                            <td class="text-right"><label class="label-control"><?php Echo $openqty ; ?></label></td>
                                            <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $openamt ; ?></label></td>
                                            
                                            <td class="text-right"><label class="label-control"><?php Echo $purqty ; ?></label></td>
                                            <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $puramt ; ?></label></td>

                                            <td class="text-right"><label class="label-control"><?php Echo $salqty * -1; ?></label></td>
                                            <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $salamt ; ?></label></td>

                                            <td class="text-right"><label class="label-control"><?php Echo $addqty ; ?></label></td>
                                            <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $addamt ; ?></label></td>

                                            <td class="text-right"><label class="label-control"><?php Echo $lesqty * -1 ; ?></label></td>
                                            <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $lesamt ; ?></label></td>
                                            
                                            <td class="text-right"><label class="label-control"><?php Echo $balqty ; ?></label></td>
                                            <td class="text-right" style="color:red"><label class="label-control"><?php Echo $balamt ; ?></label></td>
                                        <?php
                                        }
                                        ?>
                                    </tr>
                                    <tr>
                                        <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["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;
        }
    }
    ?>
                                

