<?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 = 'transacn';
$zroot = $_SESSION["zdbroot"];
$formname = 'Stock Movement';
$file = 'stockmov.php';


$prodcode = $_POST["prodcode"];
$product = $_POST["product"];
$prodgrp = $_POST["prodgrp"];
$type = $_POST["type"];
$godown = $_POST["godown"];
$action = 'prodhist_fr.php';

$pcodecond = '';
$prodcond = '';
$grpcond = '';
$godwncond = '';

if (!empty($prodcode)) {
    $pcodecond = " AND `itemcode` = '$prodcode' ";
    $pcodecond1 = " AND `code` = '$prodcode' ";
}

if (!empty($prodgrp)) {
    $grpcond = " AND `prodgrp` = '$prodgrp' ";
}
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));

$gprodct = mysqli_query($conn, "SELECT * FROM `product` WHERE code in (select itemcode from transacn ) $pcodecond1 $grpcond  order by name")or die(mysqli_error($conn));

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

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
/*        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #7E827E;
        }*/
        /*.tsize{*/
            /*font-size:18px;*/
            /*padding : 6px;*/
        /*}*/
/*        tr td{
            font-size:14px;
        }*/

    </style>
    </head>
    <body>
        <div class="col-lg-4"></div>
        
        <div class="col-lg-12 container">

            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12">
                    <table style="background: white;" class="table table-condensed table-hover table-striped table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:3%;">Sr</th>
                                <th style="width:6%;">Item Code</th>
                                <th style="width:15%;">Item Name</th>
                                <th style="width:6%;">Open Bal. Qty</th>
                                <th style="width:6%;">Open Bal. Value</th>
                                <th style="width:6%;">Purchase Qty.</th>
                                <th style="width:6%;">Inward Qty.</th>
                                <th style="width:8%;">Purchase Stock Value</th>
                                <th style="width:6%;">Sale Qty</th>
                                <th style="width:6%;">Outward Qty</th>
                                <th style="width:6%;">Sale Stock Value</th>
                                <th style="width:7%;">Close Qty. Bal.</th>
                                <th style="width:9%;">Value of Close Bal. Stock</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php

                            mysqli_query($conn, "drop temporary table if exists stk") or die(mysqli_error($conn));
                            mysqli_query($conn, "create temporary table stk 
                                                        SELECT source, itemcode, sum(qty) as sqty, sum(amount) as samt FROM `transacn` 
                                                        WHERE `vouchdt` >= '$mfrmdate'  AND `vouchdt` <= '$mtodate'  and 
                                                        itemcode in (select code from product where code != '' $pcodecond1 $grpcond ) group by source, itemcode ") or die(mysqli_error($conn));

                            $ctr = 0;
                            $balance = $totinwd = 0;
                            
                            while ($fproduct = mysqli_fetch_array($gprodct)) {
                                $barcode = $fproduct['barcode'];
                                $itemname = $fproduct['name'];
                                $icode = $fproduct['code'];
                                $irate = $fproduct['rate'];
                                $cost = $fproduct['cost'];

                                $getopen = mysqli_query($conn, "SELECT sum(`qty`) as opqty FROM `transacn` WHERE `itemcode`='$icode'
                                                        and vouchdt < '$mfrmdate' ") or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $ginwd = mysqli_query($conn, "SELECT sum(sqty) as sqty, sum(samt) as samt FROM `stk` WHERE itemcode = '$icode' and (source = 'IN' or source = 'SR') ")or die(mysqli_error($conn));
                                $finwd = mysqli_fetch_array($ginwd);

                                $goutd = mysqli_query($conn, "SELECT sum(sqty) as sqty, sum(samt) as samt FROM `stk` WHERE itemcode = '$icode' and (source = 'OU' or source = 'PR') ")or die(mysqli_error($conn));
                                $foutw = mysqli_fetch_array($goutd);
                                
                                $gpurq  = mysqli_query($conn, "SELECT `sqty`, samt FROM `stk` WHERE itemcode = '$icode' and source = 'PU' ")or die(mysqli_error($conn));
                                $fpurq  = mysqli_fetch_array($gpurq);

                                $gsalq  = mysqli_query($conn, "SELECT `sqty`, samt FROM `stk` WHERE itemcode = '$icode' and source = 'SL' ")or die(mysqli_error($conn));
                                $fsalq  = mysqli_fetch_array($gsalq);

                                $open = $fopen["opqty"];
                                $openval  =  $open * $cost;
                                
                                $purq = $fpurq["sqty"];
                                $purqamt = $fpurq["samt"];
                                
                                $inwd = $finwd['sqty'];
                                $inwdamt = $finwd['samt'];
                                
                                $salq = abs($fsalq["sqty"]);
                                $salqamt = abs($fsalq["samt"]);
                                
                                $outd = abs($foutw["sqty"]);
                                $outdamt = abs($foutw["samt"]);

                                $balq = $open + $purq + $inwd - $salq - $outd ;
                                $balqamt = $balq * $cost ;
                                
                                $piamt = $purqamt + $inwdamt ; 
                                $soamt = $salqamt + $outdamt ; 
                                
                                $ctr++;
                                
                                Echo"<tr class='clickable-row2' data-href='$action?view=view&pcode=$prodcode&todate=$mtodate' [target]='_blank'>";
                                Echo"<td class=''>$ctr</td>";
                                Echo"<td class=''>$icode</td>";
                                Echo"<td class=''>$itemname</td>";
                                
                                Echo"<td class='text-right'>$open</td>";
                                Echo"<td class='text-right'>$openamt</td>";
                                
                                Echo"<td class='text-right'>$purq</td>";
                                Echo"<td class='text-right'>$inwd</td>";
                                Echo"<td class='text-right'>$piamt</td>";
                                
                                Echo"<td class='text-right'>$salq</td>";
                                Echo"<td class='text-right'>$outd</td>";
                                Echo"<td class='text-right'>$soamt</td>";
                                
                                if ($balq >= 0) {
                                    Echo"<td class='text-right' style='color: blue;'><b>$balq</b></td>";
                                    Echo"<td class='text-right' style='color: blue;'>$balqamt</td>";
                                } else {
                                    Echo"<td class='text-right' style='color: red;'>$balq</td>";
                                    Echo"<td class='text-right' style='color: red;'>$balqamt</td>";
                                }
                                Echo"</tr>";

                                $totopen = $totopen + $open;
                                $totopenval = $totopenval + $openval;
                                
                                $totpurq = $totpurq + $purq;
                                $totinwd = $totinwd + $inwd;
                                $totsalq = $totsalq + $salq;
                                $totoutd = $totoutd + $outd;
                                $totbalq = $totbalq + $balq;
                            }
                            
                            Echo "<tr style='background-color:#7E827E; color:white'>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right tsize'>Total</td>";
                                
                            $totpi = $totpurq + $totinwd ;    
                            $totso = $totsalq + $totoutd ;    
                            Echo "<td class='text-right tsize'><b>$totopen</b></td>";
                            Echo "<td class='text-right tsize'><b>$totopenval</b></td>";
                            Echo "<td class='text-right tsize'><b>$totpurq</b></td>";
                            Echo "<td class='text-right tsize'><b>$totinwd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totpi</b></td>";
                            Echo "<td class='text-right tsize'><b>$totsalq</b></td>";
                            Echo "<td class='text-right tsize'><b>$totoutd</b></td>";
                            Echo "<td class='text-right tsize'><b>$totso</b></td>";
                            Echo "<td class='text-right tsize'><b>$totbal</b></td>";
                            Echo "<td class='text-right tsize'><b>$totbalq</b></td>";
                            Echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                    <? // } elseif($type == "L"){     ?>


                    <? // }     ?>
                </div> 
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}


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

    $contents .= "Sr,Product Name,Opening,Add,Less,Balance\n";
    
                            $ctr = 0;
                            $balance = $totinwd = 0;
                            while ($fproduct = mysqli_fetch_array($gprodct)) {

                                $barcode = $fproduct['barcode'];
                                $itemname = $fproduct['name'];
                                $icode = $fproduct['code'];
                                $irate = $fproduct['rate'];
                                $cost = $fproduct['cost'];


                                $getopen = mysqli_query($conn, "SELECT sum(`qty`) as sqty FROM `transacn` WHERE `itemcode`='$icode'
                                                        and vouchdt < '$mfrmdate' ")or die(mysqli_error($conn));
                                $fopen = mysqli_fetch_array($getopen);

                                $ginwd = mysqli_query($conn, "SELECT SUM(qty) AS `sqty`,itemcode FROM `transacn` WHERE `itemcode`='$icode'
                                                      and qty > 0 AND `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
                                $indward = mysqli_fetch_array($ginwd);

                                $goutwd = mysqli_query($conn, "SELECT SUM(`qty`) AS `sqty` FROM `transacn` WHERE `itemcode`='$icode'
                                                      and qty < 0  and `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate'")or die(mysqli_error($conn));
                                $outwd = mysqli_fetch_array($goutwd);


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

                                if ($indward['sqty'] != '') {
                                    $inwd = $indward['sqty'];
                                } else {
                                    $inwd = 0;
                                }


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


                                $balance = $open + $inwd - $out;
                                $ctr++;

                                $contents .= "$ctr" . ",";
                                $contents .= "$itemname" . ",";
                                $contents .= "$open" . ",";
                                $contents .= "$inwd" . ",";
                                $contents .= "$out" . ",";
                                $contents .= "$balance" . "\n";
                                
                                $totopen = $totopen + $open;
                                $totinwd = $totinwd + $inwd;
                                $totout = $totout + $out;
                                $totbal = $totbal + $balance;
                            }

                            $contents .= "" . ",";
                            $contents .= "Total" . ",";
                            $contents .= "$totopen" . ",";
                            $contents .= "$totinwd" . ",";
                            $contents .= "$totout" . ",";
                            $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;
    }
}