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

$formname = "Jobwork Balance";
$file = "jobpend_rep_fr.php";

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

$zcomp = $_SESSION["comp_" . $dbname];
$zroot = $_SESSION["zdir"];

$maindb = 'item';
$trandb = 'jobsacn';

$prod = $_POST["prod"];
$staff = $_POST["staff"];
$frmdt = $_POST["frmdt"];
$todt = $_POST["todt"];

$prodcond = '';
$staffcond = '';
$sizecond = '';

if (!empty($prod)) {
    $prodcond = " AND `itemcode` = '$prod' ";
}
if (!empty($staff)) {
    $staffcond = " AND `accode` = '$staff' ";
}
if (empty($frmdt)) {
    browse("From Date Cannot Be Empty");
    echo "<script> window.close(); </script>";
}
if (empty($todt)) {
    browse("To Date Cannot Be Empty");
    echo "<script> window.close(); </script>";
}

$mfrmdate = $frmdt;
$mtodate = $todt;

$fdate = date("d-m-Y", strtotime($mfrmdate));
$tdate = date("d-m-Y", strtotime($mtodate));

$getpmain = mysqli_query($conn, "SELECT a.* FROM (SELECT *,sum(qty) as tqty, date_format(`vouchdt`,'%d-%m-%Y') as mydt FROM  `$trandb`
                                                  WHERE (`vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' or vouchdt = '0000-00-00')
                                                  AND itemcode in (Select code from $maindb where code != '' $prodgrpcond $prodsubgrpcond)
                                                  $prodcond $colourcond group by itemcode, accode 
                                                  order by itemcode, vouchdt desc) a
                                            WHERE tqty != 0   ") or die(mysqli_error($conn));

$numrow = mysqli_num_rows($getpmain);

$cmpname = mysqli_query($conn, "SELECT * From $zroot.`company` where `code`='$zcomp'") or die(mysqli_error($conn));
$getcmpname = mysqli_fetch_array($cmpname);

if ($numrow == 0) {

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

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

    include("includes/linkshead.php");
?>
    <style>
        @media print {

            .no-print,
            .no-print * {
                display: none !important;
            }
        }

        .th1 {
            background-color: #037D95;
            /*background-color: #00AAFC;*/
        }

        .th2 {
            background-color: #88D8BF;
        }
    </style>
    </head>

    <body>
        <div class="container">
            <?php
            include("includes/head_rep.php");
            ?>


            <div class="row">

                <div class="col-lg-12 pad">

                    <table style="width:100%;background: white; " class="table table-condensed table-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr style="">
                                <th style="width:5%;">Sr.no</th>
                                <th style="width:15%;">Product</th>
                                <th style="width:15%;">Karigar</th>
                                <th style="width:10%;">Size</th>
                                <th style="width:10%;" class='text-right'>Qty</th>

                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totqty = 0;

                            while ($pmain = mysqli_fetch_array($getpmain)) {

                                $gstaff = mysqli_query($conn, "SELECT name,accode  from accmast where accode ='$pmain[accode]' ") or die(mysqli_error($conn));
                                $fstaff = mysqli_fetch_array($gstaff);

                                $gprod = mysqli_query($conn, "SELECT name,code  from item where code ='$pmain[itemcode]' ") or die(mysqli_error($conn));
                                $sprod = mysqli_fetch_array($gprod);


                                $ctr++;
                                echo "<tr>";
                                echo "<td><b>$ctr</b></td>";
                                echo "<td><b> $sprod[name]</b></td>";
                                echo "<td><b> $fstaff[name]</b></td>";
                                echo "<td><b>$pmain[size]</b></td>";
                                echo "<td class='text-right'><b>$pmain[tqty]</b></td>";
                                echo "</tr>";

                                $totqty = $totqty + $pmain['tqty'];
                            }

                            echo "<tr>";
                            echo "<td><b></b></td>";
                            echo "<td><b></b></td>";
                            echo "<td><b></b></td>";
                            echo "<td align='right' style='font-size:larger'><b>Total </b></td>";
                            echo "<td align='right' style='font-size:larger'><b>$totqty</b></td>";
                            echo "</tr>";
                            ?>

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

    </html>
<?php
}

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

    $contents .= "Sr No.,Karigar,Product,Size,Qty.\n";

    $ctr = 0;
    $totqty = 0;

    while ($pmain = mysqli_fetch_array($getpmain)) {

        $gstaff = mysqli_query($conn, "SELECT name,accode  from accmast where accode ='$pmain[accode]' ") or die(mysqli_error($conn));
        $fstaff = mysqli_fetch_array($gstaff);

        $gprod = mysqli_query($conn, "SELECT name,code  from item where code ='$pmain[itemcode]' ") or die(mysqli_error($conn));
        $sprod = mysqli_fetch_array($gprod);



        $ctr++;
        $contents .= $ctr . ",";
        $contents .= $fstaff['name'] . ",";
        $contents .= $sprod['name'] . ",";
        $contents .= $pmain['size'] . ",";
        $contents .= $pmain['tqty'] . "\n";

        $totqty = $totqty + $pmain['tqty'];
    }
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $totqty . ",";
    $contents .= " " . "\n";

    $contents = strip_tags($contents);

    header("Content-Disposition: attachment; filename=$formname" . date('d-m-Y') . ".csv");
    print $contents;
}

?>