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

$maindb = "actran";
$formname = "Agent Out Report";
$file = "agentoutrep_fr.php";

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

$agent = $_POST["acname"];

$agentcond = "";
if (!empty($agent)) {
    $agentcond = " AND m.`agent`='$agent' ";
}

if (empty($_POST["frmdt"])) {
    browse("From Date Cannot Be Empty");
    header("refresh:1; contreg.php");
}
if (empty($_POST["todt"])) {
    browse("To Date Cannot Be Empty");
    header("refresh:1; contreg.php");
}
$mfrmdate = trim($_POST["frmdt"]);
$mtodate = trim($_POST["todt"]);

$mfdt = format_date($mfrmdate);
$mtdt = format_date($mtodate);

$getpmain = mysqli_query($conn, "SELECT a.*, DATE_FORMAT(a.`date`, '%d-%m-%Y') as mydt, b.name AS agent FROM `$maindb` a LEFT JOIN accmast b 
                                        ON a.accode = b.accode WHERE a.`date` >= '$mfrmdate' AND a.`date` <= '$mtodate' and a.feamt > 0 
                                        and a.accode = '$agent' ")or die(mysqli_error($conn));
$getno = mysqli_num_rows($getpmain);

if (isset($_POST["view"])) {
    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
    </style>
    </head>
    <body>
        <div class="container-fluid">
            <?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-bordered">
                        <thead>
                            <tr class="mySearchTr">
                                <th style="width:5%">Voucher</th>
                                <th style="width:10%">Date</th>
                                <th style="width:18%">Agent</th>
                                <th style="width:18%">Party</th>
                                <th style="width:8%" class="text-right">RMB</th>
                                <th style="width:8%" class="text-right">ROE</th>
                                <th style="width:8%" class="text-right">INR</th>
                                <th style="width:24%">Remark</th>

                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = $totinr = $totroe = $totdollar = $totrmb = $totrmbamt = $totdollarrecd = $totchgs = $totinrtormb = 0;
                            if ($agent != '') {
                                $getagm = mysqli_query($conn, "SELECT openbal, dc FROM `accmast` WHERE accode = '$agent' ") or die(mysqli_error($conn));
                                $fetagm = mysqli_fetch_array($getagm);
                                $opfe = $fetagm['openbal'];
                                $opfedc = $fetagm['dc'];

                                if ($opfedc == 'D') {
                                    $opfe = $opfe * - 1;
                                }

                                $getag = mysqli_query($conn, "SELECT DATE_FORMAT(`date`, '%d-%m-%Y') as opdt, sum(feamt) as opfeamt FROM `actran`
                                                                     WHERE `date` < '$mfrmdate' and accode = '$agent' ") or die(mysqli_error($conn));
                                $fetag = mysqli_fetch_array($getag);
                                $opfeamt = $fetag['opfeamt'];

                                $opagamt = $opfe + $opfeamt;

                                Echo "<tr id='boldfont' class='clickable-row2' data-href='agentin.php?vouchno=$vouch&mode=edit' data-target='_blank'>";
                                Echo "<td></td>";
                                Echo "<td></td>";
                                Echo "<td>Opening As On - $mfdt</td>";
                                Echo "<td class='text-right'>$opagamt</td>";
                                Echo "<td></td>";
                                Echo "<td class='text-right'></td>";
                                Echo "<td class='text-right'></td>";
                                Echo "<td></td>";
                                Echo "</tr>";

                                $getagt = mysqli_query($conn, "SELECT sum(feamt) as trfeamt FROM `actran` WHERE feamt < 0 and 
                                                                     `date` >= '$mfrmdate' and `date` <= '$mtodate' and accode = '$agent' ") or die(mysqli_error($conn));
                                $fetagt = mysqli_fetch_array($getagt);
                                $trfeamt = $fetagt['trfeamt'];

                                Echo "<tr id='boldfont' class='' >";
                                Echo "<td></td>";
                                Echo "<td></td>";
                                Echo "<td>In Amount ($mfdt --- $mtdt)</td>";
                                Echo "<td class='text-right'>$trfeamt</td>";
                                Echo "<td></td>";
                                Echo "<td class='text-right'></td>";
                                Echo "<td class='text-right'></td>";
                                Echo "<td></td>";
                                Echo "</tr>";

                                $tottrrmb = $opagamt + $trfeamt;
                            }

                            while ($pmain = mysqli_fetch_array($getpmain)) {
                                $vouchno = $pmain['vouchno'];
                                $source = $pmain['source'];
//                                browse($vouchno);
//browse($source);
                                if ($source == 'FRAG') {
                                    $url = "agentout.php?vouchno=$vouchno&mode=edit";
                                    $url2 = "agentout_fr.php?vouchno=$vouchno";
                                    $desc = "Payment From Agent";

                                    $getcash = mysqli_query($conn, "Select party as accode from `agentout` where `vouchno`='$vouchno' ") or die(mysqli_error($conn));
                                    $fetcash = mysqli_fetch_array($getcash);
                                    $accode = $fetcash['accode'];

                                    $getacc = mysqli_query($conn, "Select name from `accmast` where `accode`='$accode' ") or die(mysqli_error($conn));
                                    $fetacc = mysqli_fetch_array($getacc);
                                    $desc = $fetacc['name'];
                                }
                                
                                if ($source == 'CARR') {
                                    $url = "carrierfees.php?vouchno=$vouchno&mode=edit";
                                    $url2 = "";
                                    $desc = "Carrier Fees";

                                    $getcash = mysqli_query($conn, "Select party as accode from `carrierfees` where `vouchno`='$vouchno' ") or die(mysqli_error($conn));
                                    $fetcash = mysqli_fetch_array($getcash);
                                    $accode = $fetcash['accode'];

                                    $getacc = mysqli_query($conn, "Select name from `accmast` where `accode`='$accode' ") or die(mysqli_error($conn));
                                    $fetacc = mysqli_fetch_array($getacc);
                                    $desc = $fetacc['name'];
                                }

                                $ctr++;
                                Echo "<tr id='boldfont' class='clickable-row' data-href='$url' data-target='_blank'>";
                                Echo "<td>$pmain[vouchno]</td>";
                                Echo "<td>$pmain[mydt]</td>";
                                Echo "<td>$pmain[agent]</td>";
                                Echo "<td>$desc</td>";
                                Echo "<td class='text-right'>$pmain[feamt]</td>";
                                Echo "<td class='text-right'>$pmain[roe]</td>";
                                Echo "<td class='text-right'>$pmain[amount]</td>";
                                Echo "<td>$pmain[remark1]  $pmain[remark2]</td>";
                                Echo "</tr>";

                                $totrmb  += $pmain['feamt'];
                                $totinr  += $pmain['amount'];
                            }

                            $colr = "green";
                            echo "<tr> ";
                            echo "<td style='color:$colr;' colspan='4' class='text-right'><h5><b>Grand Total</b></h5></td>";
                            echo "<td align='right' style='color:$colr;' id='font'><h5><b>$totrmb</b></h5></td>";
                            echo "<td align='right' style='color:$colr;' id='font'><h5><b></b></h5></td>";
                            echo "<td align='right' style='color:$colr;' id='font'><h5><b>$totinr</b></h5></td>";
                            echo "</tr>";

                            if ($agent != '') {
                                $balrmb = $tottrrmb - $totrmb;

                                echo "<tr> ";
                                echo "<td style='color:$colr;' colspan='4' class='text-right'><h5><b>Balance</b></h5></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h5><b>$balrmb</b></h5></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h5><b></b></h5></td>";
                                echo "<td align='right' style='color:$colr;' id='font'><h5><b></b></h5></td>";
                                echo "</tr>";
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

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

if (isset($_POST["excel"])) {
    $contents .="Sr, Voucher, Date, Agent, INR, ROE, Dollar, RMB, RMB Amount, Remark\n";

    $ctr = $totinr = $totroe = $totdollar = $totrmb = $totrmbamt = 0;

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

        $ctr++;
        $contents .="$ctr" . ",";
        $contents .="$pmain[vouchno]" . ",";
        $contents .="$pmain[mydt]" . ",";
        $contents .="$pmain[agent]" . ",";
        $contents .="$pmain[inr]" . ",";
        $contents .="$pmain[roe]" . ",";
        $contents .="$pmain[dollar]" . ",";
        $contents .="$pmain[rmb]" . ",";
        $contents .="$pmain[rmbamt]" . ",";
        $contents .="$pmain[remark1]" . "$pmain[remark2]" . "\n";

        $totinr = $totinr + $pmain['inr'];
        $totroe = $totroe + $pmain['roe'];
        $totdollar = $totdollar + $pmain['dollar'];
        $totrmb = $totrmb + $pmain['rmb'];
        $totrmbamt = $totrmbamt + $pmain['rmbamt'];
    }

    $contents .="Grand Total" . ",,,,";
    $contents .="$totinr" . ",";
    $contents .="$totroe" . ",";
    $contents .="$totdollar" . ",";
    $contents .="" . ",";
    $contents .="$totrmbamt" . "\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;
    }
}
?>

