<?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"];
    $prnzero = $_POST["prnzero"];

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

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

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

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


    $getitem = mysqli_query($conn, "SELECT * from `brand` where name != '' $brandcond 
                           GROUP BY code order by name") or die(mysqli_error($conn));

//    print table($getitem);
//    die();

//browse($fromdt);
//browse($godowncond);
//browse($brandcond);

    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:4%;"> SR.</th>
                                        <th style="width:20%;">Brand</th>
                                        <th style="width:6%;" class="text-right">OPEN</th>
                                        <th style="width:6%;" class="text-right">PURCHASE</th>
                                        <th style="width:6%;" class="text-right">SALE</th>
                                        <th style="width:6%;" class="text-right">CREDIT NOTE</th>
                                        <th style="width:6%;" class="text-right">DEBIT NOTE</th>
                                        <th style="width:6%;" class="text-right">EXCESS</th>
                                        <th style="width:6%;" class="text-right">DAMAGED</th>
                                        <th style="width:6%;" class="text-right">DIFFERNCE</th>
                                        <th style="width:6%;" class="text-right">CLOSING</th>
                                        <th style="width:10%;" class="text-right">AMOUNT</th>
                                    </tr>

                                    <?php
                                    $ctr = 0;
                                    $openqty = $purqty = $salqty = $addqty = $lesqty = $damqty = $excqty = $balqty = 0;
                                    $topenqty = $tpurqty = $tsalqty = $taddqty = $tlessqty = $tdamqty = $texcqty = $tbalqty = 0;

                                    while ($item = mysqli_fetch_array($getitem)) {
//                                        browse($item[code]);

                                        $getopen = mysqli_query($conn, "SELECT sum(totqty) as actqty, itemcode FROM `transacn` 
                                                                        WHERE `brand`='$item[code]'  AND `vouchdt` < '$fromdt' 
                                                                        $godowncond group by itemcode, shade, actbatch having sum(totqty) > 1 ") or die(mysqli_error($conn));
//                                        print table($getopen);
//                                        die();

                                        $openqty = 0;
                                        $openamt = 0;
                                        while ($rw = mysqli_fetch_array($getopen)) {
                                            $openqty1 = $rw[actqty];
                                            $openamt1 = $rw[amt];

                                            $openqty = $openqty + $openqty1;
                                            $openamt = $openamt + $openamt1;
                                        }

                                        $getpur = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b 
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                        AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                        and a.source = 'PU' $godowncond group by a.itemcode, a.shade, a.actbatch ") or die(mysqli_error($conn));
                                        $purqty = 0;
                                        $puramt = 0;

                                        while ($rw1 = mysqli_fetch_array($getpur)) {
                                            $purqty1 = $rw1[actqty];
                                            $puramt1 = $rw1[amt];

                                            $purqty = $purqty + $purqty1;
                                            $puramt = $puramt + $puramt1;
                                        }


                                        $getsal = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b 
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                        AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                        and a.source = 'SL'  $godowncond group by a.itemcode, a.shade, a.actbatch ") or die(mysqli_error($conn));

                                        $salqty = 0;
                                        $salamt = 0;

                                        while ($rw2 = mysqli_fetch_array($getsal)) {
                                            $salqty1 = $rw2[actqty];
                                            $salamt1 = $rw2[amt];

                                            $salqty = $salqty + $salqty1;
                                            $salamt = $salamt + $salamt1;
                                        }


                                        $getadd = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b 
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                         AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                         and a.source = 'SR' $godowncond group by a.itemcode, a.shade, a.actbatch  ") or die(mysqli_error($conn));

                                        $addqty = 0;
                                        $addamt = 0;

                                        while ($rw3 = mysqli_fetch_array($getadd)) {
                                            $addqty1 = $rw3[actqty];
                                            $addamt1 = $rw3[amt];

                                            $addqty = $addqty + $addqty1;
                                            $addamt = $addamt + $addamt1;
                                        }


                                        $getless = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                         AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                         and a.source = 'PR' $godowncond group by a.itemcode, a.shade, a.actbatch  ") or die(mysqli_error($conn));

                                        $lessqty = 0;
                                        $lessamt = 0;

                                        while ($rw4 = mysqli_fetch_array($getless)) {
                                            $addqty1 = $rw4[actqty];
                                            $addamt1 = $rw4[amt];

                                            $lessqty = $lessqty + $lessqty1;
                                            $lessamt = $lessamt + $lessamt1;
                                        }

                                        $getdam = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                         AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                         and a.source = 'DM' $godowncond group by a.itemcode, a.shade, a.actbatch  ") or die(mysqli_error($conn));

                                        $damqty = 0;
                                        $damamt = 0;

                                        while ($rw5 = mysqli_fetch_array($getdam)) {
                                            $damqty1 = $rw5[actqty];
                                            $damamt1 = $rw5[amt];

                                            $damqty = $damqty + $damqty1;
                                            $damamt = $damamt + $damamt1;
                                        }

                                        $getexc = mysqli_query($conn, "SELECT sum(a.totqty) as actqty, b.cost, sum(a.totqty * b.cost) as amt FROM `transacn` a, item b
                                                                        WHERE a.`brand`='$item[code]' and a.itemcode = b.code 
                                                                         AND a.`vouchdt` >='$fromdt' AND a.`vouchdt` <='$todt' 
                                                                         and a.source = 'EX' $godowncond group by a.itemcode, a.shade, a.actbatch  ") or die(mysqli_error($conn));

                                        $excqty = 0;
                                        $excamt = 0;

                                        while ($rw6 = mysqli_fetch_array($getexc)) {
                                            $excqty1 = $rw6[actqty];
                                            $excamt1 = $rw6[amt];

                                            $excqty = $excqty + $excqty1;
                                            $excamt = $excamt + $excamt1;
                                        }
                                        
                                        
                                        $getclos = mysqli_query($conn, "SELECT sum(totqty) as actqty FROM `transacn` 
                                                                        WHERE `brand`='$item[code]'  AND `vouchdt` <= '$todt' 
                                                                        $godowncond group by itemcode, shade, actbatch having sum(totqty) > 1 ") or die(mysqli_error($conn));
                                        $closqty = 0;
                                        $closamt = 0;
                                        while ($rw = mysqli_fetch_array($getclos)) {
                                            $openqty1 = $rw[actqty];
                                            $openamt1 = $rw[amt];

                                            $closqty = $closqty + $openqty1;
                                            $closamt = $closamt + $closamt1;
                                        }
                                        
                                        

                                        $balqty = $openqty + $purqty + $salqty + $addqty + $lesqty + $excqty + $damqty;
                                        $balamt = round($openamt + $puramt + $salamt + $addamt + $lesamt + $excamt + $damamt);

                                        $checkzero = abs($openqty) + abs($purqty) + abs($salqty) + abs($addqty) + abs($lesqty) + abs($excqty) + abs($damqty);

                                        $diffqty = $closqty - $balqty ;
                                        $diffamt = $closamt - $balamt ;

                                        $topenqty = $topenqty + $openqty;
                                        $tpurqty = $tpurqty + $purqty;
                                        $tsalqty = $tsalqty + $salqty;
                                        $taddqty = $taddqty + $addqty;
                                        $tlessqty = $tlessqty + $lessqty;
                                        $tdamqty = $tdamqty + $damqty;
                                        $texcqty = $texcqty + $excqty;
                                        $tbalqty = $tbalqty + $balqty;
                                        $tclosqty = $tclosqty + $closqty;
                                        $tdiffqty = $tdiffqty + $diffqty;
                                        $tbalamt = $tbalamt + $balamt;
                                        $tclosamt = $tclosamt + $closamt;

                                        IF ($checkzero != 0) {
                                            $ctr++;
                                            ?>
                                            <tr>
                                                <td><label class="label-control"><?php Echo $ctr ?></label></td>
                                                <td><label class="label-control"><?php Echo $item[name]; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $openqty; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $purqty; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $salqty * -1; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $addqty; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $lesqty * -1; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $excqty; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $damqty * -1; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo number_format($diffqty,2) ; ?></label></td>
                                                <td class="text-right"><label class="label-control"><?php Echo $closqty; ?></label></td>
                                                <td class="text-right" style="color:green"><label class="label-control"><?php Echo $closamt; ?></label></td>
                                                <?php
                                            }
                                            ?>
                                        </tr>
                                    <?php } ?>
                                    <tr>
                                        <td></td>
                                        <td style="text-align:right"><label class="label-control"><?php Echo 'Total' ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $topenqty; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $tpurqty; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $tsalqty * -1; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $taddqty; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $tlessqty * -1; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $texcqty; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $tdamqty * -1; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo  number_format($tdiffqty,2) ; ?></label></td>
                                        <td class="text-right" style="color:blue"><label class="label-control"><?php Echo $tclosqty; ?></label></td>
                                        <td class="text-right" style="color:red"><label class="label-control"><?php  Echo $tclosamt; ?></label></td>
                                    </tr>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <?php
    }

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

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


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


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

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

        if (!empty($godown)) {
            $godowncond = " AND `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 `brand` where name != '' $brandcond 
                           GROUP BY code order by name") or die(mysqli_error($conn));
//browse($fromdt);


        $contents.="SR.,BRAND,OPEN,PURCHASE,SALE,CREDIT NOTE,DEBIT NOTE,EXCESS,DAMAGE,CLOSING\n";




        $ctr = 0;
        $openqty = $purqty = $totqty = $salqty = $addqty = $lesqty = $damqty = $excqty = $balqty = 0;

        while ($item = mysqli_fetch_array($getitem)) {
            $getopen = mysqli_query($conn, "SELECT SUM(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` < '$fromdt' $godowncond") or die(mysqli_error($conn));
//                                        $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(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'PU' $godowncond ") or die(mysqli_error($conn));
//                                        $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(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'SL'  $godowncond ") or die(mysqli_error($conn));
//                                        $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));
            $getadd = mysqli_query($conn, "SELECT SUM(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'SR' $godowncond ") or die(mysqli_error($conn));
            $othad = mysqli_fetch_array($getadd);
            $addqty = $othad[actqty];

            $getless = mysqli_query($conn, "SELECT SUM(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'PR' $godowncond ") or die(mysqli_error($conn));

            $othls = mysqli_fetch_array($getless);
            $lesqty = $othls[actqty];

            $getdam = mysqli_query($conn, "SELECT SUM(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'DM' $godowncond ") or die(mysqli_error($conn));

            $getdm = mysqli_fetch_array($getdam);
            $damqty = $getdm[actqty];

            $getexc = mysqli_query($conn, "SELECT SUM(actqty) as actqty FROM `transacn` WHERE 
                                                                        `brand`='$item[code]'
                                                                         AND `vouchdt` >='$fromdt' AND `vouchdt` <='$todt' 
                                                                         and source = 'EX' $godowncond ") or die(mysqli_error($conn));
            $getex = mysqli_fetch_array($getexc);
            $excqty = $getex[actqty];

            $balqty = $openqty + $purqty + $salqty + $addqty + $lesqty + $excqty + $damqty;
            $totqty = $totqty + $balqty;

            IF ($balqty != 0) {
                $ctr++;

                $mctr = $ctr;
                $mitem = $item[name];
                $mopenqty = $openqty;
                $mpurqty = $purqty;
                $msalqty = $salqty;
                $maddqty = $addqty;
                $mlesqty = $lesqty;
                $mexcqty = $excqty;
                $mdamqty = $damqty;
                $mbalqty = $balqty;


                $contents.=$mctr . ",";
                $contents.=$mitem . ",";
                $contents.=$mopenqty . ",";
                $contents.=$mpurqty . ",";
                $contents.=$msalqty . ",";
                $contents.=$maddqty . ",";
                $contents.=$mlesqty . ",";
                $contents.=$mexcqty . ",";
                $contents.=$mdamqty . ",";
                $contents.=$mbalqty . "\n";
            }
        }

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


