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

$admin = $_SESSION["admin_" . $dbname];
$zdb = $_SESSION["zdbyear_" . $dbname];
$myyear = $_SESSION[$myyear];
mysqli_select_db($conn, $zdb);
//$maindb = 'rawsacn';
$maindb = 'packing';
$zroot = $_SESSION["zdbroot"];
$formname = 'Packing Stock ';
$file = 'stkpack.php';

$product = $_POST["product"];
$packgrp = $_POST["packgrp"];
$godown = $_POST["godown"];


$prodcond = '';
$grpcond = '';

if (!empty($product)) {
    $prodcond = " AND `code` = '$product' ";
}
if (!empty($packgrp)) {
    $grpcond = " AND `packgrp` = '$packgrp' ";
}
if (!empty($godown)) {
    $godwncond = " AND `godown` = '$godown' ";
}


$mfrmdate = trim($_POST["frmdt"]);
$mtodate = trim($_POST["todt"]);

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

$mainqry = mysqli_query($conn, "SELECT name, code, unit, cost,rate, msp, mrp,hsncode, packgrp
                        FROM `$maindb` where code != '' $prodcond $grpcond group by code order by name")or die(mysqli_error($conn));

$getno = mysqli_num_rows($mainqry);
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: #26A69A;
        }
        .tsize{
            font-size:18px;
            padding : 6px;
        }
    </style>
    </head>
    <body>
        <div class="container">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">

                    <table style="background: white;width: 100%" class="table table-condensed table-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th width="5%">Sr</th>
                                <th width="23%">Item Name</th>
                                <th width="8%" class="">Net Wt.</th>
                                <th width="12%" class='text-right'>Opening</th>
                                <th width="12%" class="text-right">Purchased</th>
                                <th width="12%" class="text-right">Product Produced</th>
                                <th width="12%" class="text-right">Outward</th>
                                <th width="12%" class="text-right">Damaged </th>
                                <th width="12%" class="text-right">Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $balance = 0;
                            $tpadd = 0;
                            $totopen = 0;
                            $totprod = 0;
                            $totout = 0;
                            $totdmgd = 0;
                            $totbal = 0;
                            while ($fmain = mysqli_fetch_array($mainqry)) {

                                $itemname = $fmain['name'];
                                $itemcode = $fmain['code'];
                                $cost = $fmain['cost'];
                                $unit = $fmain['unit'];

                                if ($cost > 0) {
                                    $actcost = $cost;
                                } else {
                                    $getcost = mysqli_query($conn, "SELECT `rate` FROM rawsacn WHERE `itemcode`='$icode' and rate > 0 
                                                            and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));
                                    $fcost = mysqli_fetch_array($getcost);
                                    $actcost = $fcost['rate'];
                                }
                                $newcost = $actcost;


                                $getopen = mysqli_query($conn, "SELECT  `itemcode`, sum(qty) as qty FROM `packsacn` WHERE `itemcode`='$itemcode'
                                                        AND source='OP' AND `vouchdt` < '$mfrmdate' $godwncond ")or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $getadd = mysqli_query($conn, "SELECT `itemcode`, SUM(`qty`) as `qty`  FROM `packsacn` WHERE `itemcode`='$itemcode' 
                                                        and `source` = 'PUPK' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godwncond ")or die(mysqli_error($conn));
                                $fetadd = mysqli_fetch_array($getadd);

                                $gprodcd = mysqli_query($conn, "SELECT `itemcode`, SUM(`qty`) as `qty`  FROM `prodsacn` WHERE `itemcode`='$itemcode' 
                                                        and `source` = 'BMR' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godwncond ")or die(mysqli_error($conn));
                                $prodpcd = mysqli_fetch_array($gprodtn);

                                $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `prodsacn` WHERE `itemcode`='$itemcode' 
                                                       AND `source` = 'DM' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godwncond ")or die(mysqli_error($conn));
                                $outwd = mysqli_fetch_array($goutwd);

                                $gdmg = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode'
                                                     AND `source` = 'DMRM' AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $godwncond ")or die(mysqli_error($conn));
                                $damaged = mysqli_fetch_array($gdmg);


                                if ($fopen['qty'] != '') {
                                    $open = $fopen['qty'];
                                } else {
                                    $open = 0;
                                }
                                
                                if ($fetadd['qty'] != '') {
                                    $padd = $fetadd['qty'];
                                } else {
                                    $padd = 0;
                                }

                                if ($prodpcd['qty'] != '') {
                                    $produced = $prodpcd['qty'];
                                } else {
                                    $produced = 0;
                                }

                                if ($outwd['qty'] != '') {
                                    $out = $outwd['qty'];
                                } else {
                                    $out = 0;
                                }

                                $dam = abs($damaged['qty']);
                                if ($dam != '') {
                                    $dmgd = $dam;
                                } else {
                                    $dmgd = 0;
                                }
                                $balance = $open + $padd + $produced - $out - $dmgd;
                                $ctr++;
                                Echo"<tr>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$itemname</td>";
                                Echo"<td class=''>$unit</td>";
                                Echo"<td class='text-right'>$open</td>";
                                Echo"<td class='text-right'>$padd</td>";
                                Echo"<td class='text-right'>$produced</td>";
                                Echo"<td class='text-right'>$out</td>";
                                Echo"<td class='text-right'>$dmgd</td>";
                                Echo"<td class='text-right'>$balance</td>";
                                Echo"</tr>";

                                $tpadd = $tpadd + $padd;
                                $totopen = $totopen + $open;
                                $totprod = $totprod + $produced;
                                $totout = $totout + $out;
                                $totdmgd = $totdmgd + $dmgd;
                                $totbal = $totbal + $balance;
                            }

                            Echo "<tr style='background-color:#26A69A'>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right tsize'>Total</td>";
                            Echo "<td class='text-right tsize'><b>$totopen</b></td>";
                            Echo "<td class='text-right tsize'><b>$tpadd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totprod</b></td>";
                            Echo "<td class='text-right tsize'><b>$totout</b></td>";
                            Echo "<td class='text-right tsize'><b>$totdmgd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totbal</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}

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


    $contents .= "Sr,Item Name,Net Wt.,Opening,Product Produced,Outward,Damaged,Balance\n";


    $ctr = 0;
    $balance = 0;
    while ($fmain = mysqli_fetch_array($mainqry)) {


        $itemname = $fmain['name'];
        $itemcode = $fmain['code'];
        $cost = $fmain['cost'];
        $unit = $fmain['unit'];

        if ($cost > 0) {
            $actcost = $cost;
        } else {
            $getcost = mysqli_query($conn, "SELECT `rate` FROM rawsacn WHERE `itemcode`='$icode' and rate > 0 
                                                            and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));
            $fcost = mysqli_fetch_array($getcost);
            $actcost = $fcost['rate'];
        }
        $newcost = $actcost;


        $getopen = mysqli_query($conn, "SELECT  `itemcode`, `qty` FROM `packsacn` WHERE `itemcode`='$itemcode'
                                AND source='OP' AND `vouchdt` < '$mfrmdate' $godwncond ")or die(mysqli_error($conn));
        $fopen = mysqli_fetch_array($getopen);

        $gprodcd = mysqli_query($conn, "SELECT `itemcode`, SUM(`qty`) as `qty`  FROM `prodsacn` WHERE `itemcode`='$itemcode' 
                                and `source` = 'BMR' AND `vouchdt` < '$mfrmdate' $godwncond ")or die(mysqli_error($conn));
        $prodpcd = mysqli_fetch_array($gprodtn);

        $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `prodsacn` WHERE `itemcode`='$itemcode' 
                               AND `source` = 'DM' AND `vouchdt` < '$mfrmdate' $godwncond ")or die(mysqli_error($conn));
        $outwd = mysqli_fetch_array($goutwd);

        $gdmg = mysqli_query($conn, "SELECT SUM(`qty`) AS `qty`, `itemcode` FROM `rawsacn` WHERE `itemcode`='$itemcode'
                             AND `source` = 'DMRM' AND `vouchdt` < '$mfrmdate' $godwncond ")or die(mysqli_error($conn));
        $damaged = mysqli_fetch_array($gdmg);


        if ($fopen['qty'] != '') {
            $open = $fopen['qty'];
        } else {
            $open = 0;
        }

        if ($prodpcd['qty'] != '') {
            $produced = $prodpcd['qty'];
        } else {
            $produced = 0;
        }

        if ($outwd['qty'] != '') {
            $out = $outwd['qty'];
        } else {
            $out = 0;
        }

        $dam = abs($damaged['qty']);
        if ($dam != '') {
            $dmgd = $dam;
        } else {
            $dmgd = 0;
        }
        $balance = $open + $produced - $out - $dmgd;
        $ctr++;

        $contents .= $ctr . ",";
        $contents .= $itemname . ",";
        $contents .= $unit . ",";
        $contents .= $open . ",";
        $contents .= $produced . ",";
        $contents .= $out . ",";
        $contents .= $dmgd . ",";
        $contents .= $balance . "\n";


        $totopen = $totopen + $open;
        $totprod = $totprod + $produced;
        $totout = $totout + $out;
        $totdmgd = $totdmgd + $dmgd;
        $totbal = $totbal + $balance;
    }

    $contents .= " " . ",";
    $contents .= " " . ",";
    $contents .= "Total" . ",";
    $contents .= $totopen . ",";
    $contents .= $totprod . ",";
    $contents .= $totout . ",";
    $contents .= $totdmgd . ",";
    $contents .= $totbal . "\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;
    }
}
?>