<?php
ob_start();

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


$zdb = $_SESSION["zdbyear_" . $dbname];
$zroot = $_SESSION["zdbroot"];
$admin = $_SESSION["admin_" . $dbname];
$spltype = $_SESSION["spltype_" . $dbname];

mysqli_select_db($conn, $zdb);
$formname = 'Combine Stock';
$maindb = 'product';
$trandb = 'transacn';

$grid = 'stockall.php';

//$getprevyear = mysqli_query($conn, "SELECT cono, finyear FROM $zroot.`compyear` where `cono`='$compcd' 
//                            and `finyear`!='$myyear' ORDER BY `lastdate` DESC LIMIT 0,1 ")or die(mysqli_error($conn));
//$showprevyear = mysqli_fetch_array($getprevyear);
//$prevyear = $showprevyear[finyear];
//
//$olddb = $zroot1 . $compcd . '_' . $prevyear;

$giname = trim($_POST["itemname"]);
$gname = trim($_POST["itemgrp"]);
$prnval = trim($_POST["prnval"]);

$gcond = "";
$itcond = "";
if (!empty($_POST["itemname"])) {
    $itcond = " and b.`code` = '$giname' ";
}


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

if (empty($_POST["todt"])) {
    browse("TO DATE CANNOT BE EMPTY");
    header("refresh:1; $grid");
}



$mtodate = trim($_POST["todt"]);
$itemvalue = 0;

$gctr = 1;
$getgodw1 = mysqli_query($conn, "SELECT code, name as gcode FROM `godown` order by code limit 0,1") or die(mysqli_error($conn));
$fetgodw1 = mysqli_fetch_array($getgodw1);
$g1 = $fetgodw1['code'];
$gn1 = $fetgodw1['gcode'];


$getgodw2 = mysqli_query($conn, "SELECT code, name as gcode FROM `godown` order by code limit 1,1") or die(mysqli_error($conn));
$fetgodw2 = mysqli_fetch_array($getgodw2);
$g2 = $fetgodw2['code'];
$gn2 = $fetgodw2['gcode'];


$getgodw3 = mysqli_query($conn, "SELECT code, name as gcode FROM `godown` order by code limit 2,1") or die(mysqli_error($conn));
$fetgodw3 = mysqli_fetch_array($getgodw3);
$g3 = $fetgodw3['code'];
$gn3 = $fetgodw3['gcode'];


$getgodw4 = mysqli_query($conn, "SELECT code, name as gcode FROM `godown` order by code limit 3,1") or die(mysqli_error($conn));
$fetgodw4 = mysqli_fetch_array($getgodw4);
$g4 = $fetgodw4['code'];
$gn4 = $fetgodw4['gcode'];

$getgodw5 = mysqli_query($conn, "SELECT code, name as gcode FROM `godown` order by code limit 4,1") or die(mysqli_error($conn));
$fetgodw5 = mysqli_fetch_array($getgodw5);
$g5 = $fetgodw5['code'];
$gn5 = $fetgodw5['gcode'];


mysqli_query($conn, "drop temporary table if exists tempdb") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table tempdb 
                            SELECT sum(a.cartqty) as sqty, b.code as itemcode, a.godown, b.barcode,
                                   b.name, b.cost, b.rate FROM `$trandb` a, `$maindb` b 
                                   WHERE a.itemcode = b.code and a.`vouchdt` <= '$mtodate' 
                                   $gcond $itcond group by a.godown, a.itemcode") or die(mysqli_error($conn));

$gettran = mysqli_query($conn, "SELECT * FROM `tempdb` group by itemcode order by name ") or die(mysqli_error($conn));

$get = mysqli_query($conn, "SELECT * FROM `tempdb` order by name ") or die(mysqli_error($conn));

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

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>

    <style type="text/css">

        #tabital{

            font-style: italic;

        }

        #font1{

            font-style: italic;

        }

        #boldfont{

            font-weight: bold; 

        }

        th {

            /*background : green ;*/

            position: sticky;

            top:0;

            background-color:#7E827E
        }



    </style>



    </head>

    <body>

        <div class="container" style="width:100%">

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

            <div class="row">
                <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 pad">
                    <table style="background: white;width: 100%" class="table table-condensed table-bordered">
                        <thead class="" style="background-color:#7E827E" >
                            <tr>
                                <th align="left"  width='3%' style="font-family: arial;">Sr.</th>
                                <th align="left"  width='15%'>Barcode</th>
                                <th align="left"  width='25%'>Item Name</th>
                                <th width='6%' style="text-align: right;"><?php echo $gn1 ?></th>
                                <th width='6%' style="text-align: right;"><?php echo $gn2 ?></th>
                                <th width='6%' style="text-align: right;"><?php echo $gn3 ?></th>
                                <th width='6%' style="text-align: right;"><?php echo $gn4 ?></th>
                                <th width='6%' style="text-align: right;"><?php echo $gn5 ?></th>
                                <th width='8%' style="text-align: right;">Total</th>
                            </tr>
                        </thead>
                        <tbody>

                            <?php
                            $gropen = 0;
                            $grsale = 0;
                            $grpur = 0;
                            $grbal = 0;
                            $grval = 0;
                            $mopen = 0;
                            $ctr = 0;

                            $totqty = $totval = 0;
                            while ($row = mysqli_fetch_object($gettran)) {

                                $ctr++;

                                $ibarcode = $row->barcode;
                                $icode = $row->itemcode;
                                $itemname = $row->name;
                                $cost = $row->cost;
                                $rate = $row->rate;

//                                if ($prnval == 2) {
//                                    if ($cost > 0) {
//                                        $actcost = $cost;
//                                    } else {
//
//                                        $getpur = mysqli_query($conn, "SELECT `rate` FROM `purtran` WHERE `itemcode`='$icode' and rate > 0 
//                                                                           and `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
//                                        $getnum = mysqli_num_rows($getpur);
//                                        if ($getnum > 0) {
//                                            $chkpurrate = mysqli_fetch_array($getpur);
//                                            $actcost = $chkpurrate['rate'];
//                                        } else {
//                                            $actcost = $rate;
//                                        }
//                                    }
//                                    $newcost = $actcost;
//                                }

                                $balqqty1 = $balqty2 = $balqty3 = $balqty4 = 0;

                                $getstk1 = mysqli_query($conn, "SELECT sum(sqty) as qty1 FROM `tempdb` where `itemcode`='$icode' and godown = '$g1'  ") or die(mysqli_error($conn));
                                $fetstk1 = mysqli_fetch_array($getstk1);
                                $balqty1 = $fetstk1['qty1'];

                                $getstk2 = mysqli_query($conn, "SELECT sum(sqty) as qty2 FROM `tempdb` where `itemcode`='$icode' and godown = '$g2'  ") or die(mysqli_error($conn));
                                $fetstk2 = mysqli_fetch_array($getstk2);
                                $balqty2 = $fetstk2['qty2'];

                                $getstk3 = mysqli_query($conn, "SELECT sum(sqty) as qty3 FROM `tempdb` where `itemcode`='$icode' and godown = '$g3'  ") or die(mysqli_error($conn));
                                $fetstk3 = mysqli_fetch_array($getstk3);
                                $balqty3 = $fetstk3['qty3'];

                                $getstk4 = mysqli_query($conn, "SELECT sum(sqty) as qty4 FROM `tempdb` where `itemcode`='$icode' and godown = '$g4'  ") or die(mysqli_error($conn));
                                $fetstk4 = mysqli_fetch_array($getstk4);
                                $balqty4 = $fetstk4['qty4'];

                                $getstk5 = mysqli_query($conn, "SELECT sum(sqty) as qty5 FROM `tempdb` where `itemcode`='$icode' and godown = '$g5'  ") or die(mysqli_error($conn));
                                $fetstk5 = mysqli_fetch_array($getstk5);
                                $balqty5 = $fetstk5['qty5'];

                                $balqty = $balqty1 + $balqty2 + $balqty3 + $balqty4 + $balqty5;

                                echo "<tr>";
                                echo "<td align='left'><font size='2px'>$ctr</td>";
                                echo "<td align='left'><font size='2px'>$ibarcode</td>";
                                echo "<td align='left'><font size='2px'>$itemname</td>";

                                if ($balqty1 != 0) {
                                    echo "<td align='right' style='background-color:#B8F4F7'><font size='3px'><b>" . number_format($balqty1) . "</b></td>";
                                } else {
                                    echo "<td align='right' style='background-color:#B8F4F7'><font size='3px'></td>";
                                }

                                if ($balqty2 != 0) {
                                    echo "<td align='right' style='background-color:#ffdd95'><font size='3px'><b>" . number_format($balqty2) . "</b></td>";
                                } else {
                                    echo "<td align='right' style='background-color:#ffdd95'><font size='3px'></td>";
                                }


                                if ($balqty3 != 0) {
                                    echo "<td align='right' style='background-color:#B8F7BA'><font size='3px'><b>" . number_format($balqty3) . "</b></td>";
                                } else {
                                    echo "<td align='right' style='background-color:#B8F7BA'><font size='3px'></td>";
                                }

                                if ($balqty4 != 0) {
                                    echo "<td align='right' style='background-color:#FFFAA0'><font size='3px'><b>" . number_format($balqty4) . "</b></td>";
                                } else {
                                    echo "<td align='right' style='background-color:#FFFAA0'><font size='3px'></td>";
                                }
                                
                                if ($balqty5 != 0) {
                                    echo "<td align='right' style='background-color:#98F9F0'><font size='3px'><b>" . number_format($balqty5) . "</b></td>";
                                } else {
                                    echo "<td align='right' style='background-color:#98F9F0'><font size='3px'></td>";
                                }

                                if ($balqty != 0) {
                                    if ($balqty > 0) {
                                        echo "<td align='right' style='color:blue'><font size='3px'>" . number_format($balqty) . "</td>";
                                    } else {
                                        echo "<td align='right' style='color:red'><font size='3px'>" . number_format($balqty) . "</td>";
                                    }
                                } else {
                                    echo "<td align='right' style='color:darkgreen'><font size='3px'></td>";
                                }

                                if ($prnval == 2) {
                                    $itemvalue = $newcost * $balqty;
                                    $totval = $totval + $itemvalue;
                                }

                                echo "</tr>";
                                $gr1 = $gr1 + $balqty1;
                                $gr2 = $gr2 + $balqty2;
                                $gr3 = $gr3 + $balqty3;
                                $gr4 = $gr4 + $balqty4;
                                $gr5 = $gr5 + $balqty5;

                                $grbal = $grbal + $balqty;
                                $grval = $grval + $itemvalue;
                            }
                            echo "<tr>";
                            echo "<td></td>";
                            echo "<td></td>";
                            echo "<td><font size='3px'>Grand Total</td>";
                            if ($gr1 != 0) {
                                echo "<td align='right' style='background-color:#B8F4F7'><font size='3px'><b>$gr1</b></td>";
                            } else {
                                echo "<td align='right' style='background-color:#B8F4F7'><font size='3px'></td>";
                            }
                            
                            if ($gr2 != 0) {
                                echo "<td align='right' style='background-color:#ffdd95'><font size='3px'><b>$gr2</b></td>";
                            } else {
                                echo "<td align='right' style='background-color:#ffdd95'><font size='3px'></td>";
                            }

                            if ($gr3 != 0) {
                                echo "<td align='right' style='background-color:#B8F7BA'><font size='3px'><b>$gr3</b></td>";
                            } else {
                                echo "<td align='right' style='background-color:#B8F7BA'><font size='3px'></td>";
                            }

                            if ($gr4 != 0) {
                                echo "<td align='right' style='background-color:#FFFAA0'><font size='3px'><b>$gr4</b></td>";
                            } else {
                                echo "<td align='right' style='background-color:#FFFAA0'><font size='3px'></td>";
                            }

                            if ($gr5 != 0) {
                                echo "<td align='right' style='background-color:#98F9F0'><font size='3px'><b>$gr5</b></td>";
                            } else {
                                echo "<td align='right' style='background-color:#98F9F0'><font size='3px'></td>";
                            }

                            if ($grbal != 0) {
                                if ($grbal > 0) {
                                    echo "<td align='right' style='color:darkgreen'><font size='3px'>" . number_format($grbal, 0) . "</td>";
                                } else {
                                    echo "<td align='right' style='color:red'><font size='3px'>" . number_format($grbal, 0) . "</td>";
                                }
                            } else {
                                echo "<td align='right' style='color:darkgreen'><font size='3px'></td>";
                            }
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </body>
    </html>
    <?php
}





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


    $contents .= "Sr., Barcode, Itemname, $gn1, $gn2, $gn3, $gn4, $gn5, Total\n";


    $gropen = 0;

    $grsale = 0;

    $grpur = 0;

    $grbal = 0;

    $grval = 0;

    $mopen = 0;

    $ctr = 0;



    $totqty = $totval = 0;



    while ($row = mysqli_fetch_object($gettran)) {

        $ctr++;

        $ibarcode = $row->barcode;

        $icode = $row->itemcode;

        $itemname = $row->name;

        $cost = $row->cost;

        $rate = $row->rate;



        if ($prnval == 2) {

            if ($cost > 0) {

                $actcost = $cost;
            } else {

                $getpur = mysqli_query($conn, "SELECT `rate` FROM `purtran` WHERE `itemcode`='$icode' and rate > 0 

                                                                           and `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));

                $getnum = mysqli_num_rows($getpur);

                if ($getnum > 0) {

                    $chkpurrate = mysqli_fetch_array($getpur);

                    $actcost = $chkpurrate['rate'];
                } else {

                    $actcost = $rate;
                }
            }

            $newcost = $actcost;
        }



        $balqqty1 = $balqty2 = $balqty3 = $balqty4 = 0;



        $getstk1 = mysqli_query($conn, "SELECT sum(sqty) as qty1 FROM `tempdb` where `itemcode`='$icode' and series = '$g1'  ") or die(mysqli_error($conn));

        $fetstk1 = mysqli_fetch_array($getstk1);

        $balqty1 = $fetstk1['qty1'];



        $getstk2 = mysqli_query($conn, "SELECT sum(sqty) as qty2 FROM `tempdb` where `itemcode`='$icode' and series = '$g2'  ") or die(mysqli_error($conn));

        $fetstk2 = mysqli_fetch_array($getstk2);

        $balqty2 = $fetstk2['qty2'];



        $getstk3 = mysqli_query($conn, "SELECT sum(sqty) as qty3 FROM `tempdb` where `itemcode`='$icode' and series = '$g3'  ") or die(mysqli_error($conn));

        $fetstk3 = mysqli_fetch_array($getstk3);

        $balqty3 = $fetstk3['qty3'];



        $getstk4 = mysqli_query($conn, "SELECT sum(sqty) as qty4 FROM `tempdb` where `itemcode`='$icode' and series = '$g4'  ") or die(mysqli_error($conn));

        $fetstk4 = mysqli_fetch_array($getstk4);

        $balqty4 = $fetstk4['qty4'];

        $getstk5 = mysqli_query($conn, "SELECT sum(sqty) as qty4 FROM `tempdb` where `itemcode`='$icode' and series = '$g5'  ") or die(mysqli_error($conn));

        $fetstk5 = mysqli_fetch_array($getstk5);

        $balqty5 = $fetstk5['qty4'];



        $balqty = $balqty1 + $balqty2 + $balqty3 + $balqty4 + $balqty5;




        $contents .= "$ctr" . ",";
        $contents .= "$ibarcode" . ",";
        $contents .= "$itemname" . ",";


        if ($balqty1 != 0) {

            $contents .= "$balqty1" . ",";
        } else {

            $contents .= "" . ",";
        }



        if ($balqty2 != 0) {

            $contents .= "$balqty2" . ",";
        } else {

            $contents .= "" . ",";
        }



        if ($balqty3 != 0) {

            $contents .= "$balqty3" . ",";
        } else {

            $contents .= "" . ",";
        }



        if ($balqty4 != 0) {

            $contents .= "$balqty4" . ",";
        } else {

            $contents .= "" . ",";
        }


        if ($balqty5 != 0) {

            $contents .= "$balqty5" . ",";
        } else {

            $contents .= "" . ",";
        }



        if ($balqty != 0) {

            if ($balqty > 0) {

                $contents .= "$balqty" . "\n";
            } else {

                $contents .= "$balqty" . "\n";
            }
        } else {

            $contents .= "" . "\n";
        }



        if ($prnval == 2) {

            $itemvalue = $newcost * $balqty;

            $totval = $totval + $itemvalue;
        }


        $gr1 = $gr1 + $balqty1;

        $gr2 = $gr2 + $balqty2;

        $gr3 = $gr3 + $balqty3;

        $grbal = $grbal + $balqty;

        $grval = $grval + $itemvalue;
    }





    $contents .= "" . ",";
    $contents .= "" . ",";
    $contents .= "Grand Total" . ",";


    if ($gr1 != 0) {

        $contents .= "$gr1" . ",";
    } else {

        $contents .= "" . ",";
    }



    if ($gr2 != 0) {

        $contents .= "$gr2" . ",";
    } else {
        $contents .= "" . ",";
    }



    if ($gr3 != 0) {

        $contents .= "$gr3" . ",";
    } else {

        $contents .= "" . ",";
    }



    if ($gr4 != 0) {

        $contents .= "$gr4" . ",";
    } else {

        $contents .= "" . ",";
    }



    if ($grbal != 0) {

        if ($grbal > 0) {

            $contents .= "$grbal" . ",";
        } else {

            $contents .= "$grbal" . ",";
        }
    } else {

        $contents .= "" . ",";
    }



    if ($prnval == 2) {

        // echo "<td></td>";
        // echo "<td style='text-align:right;'>";
        // echo "<font size='3px'><b> " . number_format($grval, 0, ".", ",") . "</b>";
        // echo "</td>";
    }
    ?>

    <?php
    $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;
    }
}
?>