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

$paxdb = "visaform";
$bookdb = "bookform";

$formname = "Receipt Book Register";

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

$namecond = $regnocond = $paxnocond = $groupcond = $crnocond = $classcond = $tournocond = $citycond = $statecond = $infcond = $cancond = $addcond = $ppexpcond = $ppinhand = $dpdtcond = $dppnrcond = $aircond = $prregnocond = $prphcond = $agentcond = $arrvdatecond = $arrvpnrcond = $buscond = $roomcond = "";

$frmregnno = $_POST['frmregnno'];
$toregnno = $_POST['toregnno'];

$regnfrom = $_POST['regnfrom'];
$regnto = $_POST['regnto'];

$fromdate = $_POST['fromdate'];
$todate = $_POST['todate'];

$scope = $_POST['scope'];
$class = $_POST['class'];
$depdate = $_POST['depdate'];
$agent = $_POST['agent'];

$regnnocond = "";
$regnfromcond = "";
$regntocond = "";
$fromdatecond = "";
$todatecond = "";
$scopecond = "";
$classcond = "";
$depcond = "";
$agentcond = "";

if (!empty($frmregnno) && !empty($toregnno)) {
    $regnnocond = "AND regnno >= '$frmregnno' AND regnno <= '$toregnno' ";
}

if (!empty($regnfrom)) {
    $regnfromcond = "";
}
if (!empty($regnto)) {
    $regntocond = "";
}
if (!empty($fromdate)) {
    $fromdatecond = "";
}
if (!empty($todate)) {
    $todatecond = "";
}

if (!empty($class)) {
    $classcond = "";
}
if (!empty($depdate)) {
    $depcond = "";
}
if (!empty($agent)) {
    $agentcond = "";
}

if (!empty($name)) {
    $namecond = "AND `haji` = '$name' ";
}

if ($scope == 1) {
    $mainqry = mysqli_query($conn, "select 'CR' as source, vouchdt, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, vouchno, payfor, amount, regnno, narr1, narr2, recdby, party, 
                                     '' as rtgsno, '' as chqno, '' as chqdate, '' as drawbank 
                                     FROM `cashrecp` 
                                        WHERE vouchdt >= '$fromdate' and vouchdt <= '$todate' and regnno != ''  $regnnocond  and recpagnst = 'U' order by regnno, vouchdt") or die(mysqli_error($conn));
} elseif ($scope == 2) {
    $mainqry = mysqli_query($conn, "SELECT 'BR' as source, vouchdt, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, vouchno, payfor, amount, regnno, narr1, narr2, recdby, accode as party,
                                rtgsno, chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdate, drawbank 
                                        FROM `bankrecp` 
                                        WHERE vouchdt >= '$fromdate' and vouchdt <= '$todate' and regnno != ''  $regnnocond 
                                        and retdate = '0000-00-00' and recpagnst = 'U' order by regnno, vouchdt") or die(mysqli_error($conn));
} else {
    $scopecond = "";

    $mainqry = mysqli_query($conn, "select a.* from 
                                (SELECT 'CR' as source, vouchdt, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, vouchno, payfor, amount, regnno, narr1, narr2, recdby, party, 
                                     '' as rtgsno, '' as chqno, '' as chqdate, '' as drawbank 
                                     FROM `cashrecp` 
                                        WHERE vouchdt >= '$fromdate' and vouchdt <= '$todate' and regnno != '' and recpagnst = 'U'  $regnnocond 
                                union all
                                SELECT 'BR' as source, vouchdt, DATE_FORMAT(`vouchdt`, '%d-%m-%Y') as mydt, vouchno, payfor, amount, regnno, narr1, narr2, recdby, accode as party,
                                rtgsno, chqno, DATE_FORMAT(`chqdate`, '%d-%m-%Y') as chqdate, drawbank 
                                        FROM `bankrecp` 
                                        WHERE vouchdt >= '$fromdate' and vouchdt <= '$todate' and regnno != '' and recpagnst = 'U'  $regnnocond  
                                        and retdate = '0000-00-00' ) as a 
                                order by regnno, vouchdt") or die(mysqli_error($conn));
}
//print table($mainqry);
//die();

$m1 = mysqli_num_rows($mainqry);
$m = $m1;
if ($m <= 0) {
    // $a = 0;
}
if ($a == 0) {
    // echo "<script>"
    //     . "window.close()"
    //     . "</script>";
}
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" style="width:100%">
            <?php include("includes/head_rep.php"); ?>
            <div class="row">
                <div class="col-lg-12 pad">
                    <table style="background: white;width: 100%" class="table table-bordered table-condensed table-hover">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:10%">Date</th>
                                <th style="width:5%">Source</th>
                                <th style="width:5%">Voucher</th>
                                <th style="width:10%" class="text-center">Regn.No.</th>
                                <th style="width:30%">Haji</th>
                                <th style="width:10%">Ticket Amount</th>
                                <th style="width:15%" class="text-right">Tour Amount</th>
                                <th style="width:15%">Received By</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $discamt = 0;
                            $tkttotamt = 0;
                            $tourtotamt = 0 ;

                            while ($fmain = mysqli_fetch_array($mainqry)) {
                                    $tktamt = 0;
                                    $touramt = 0;

                                $gpaxno = mysqli_query($conn, "SELECT * FROM $paxdb WHERE `vregnno`='$fmain[vouchno]' ") or die(mysqli_error($conn));
                                $paxnum = mysqli_num_rows($gpaxno);
                                $fetgpax = mysqli_fetch_object($gpaxno);

                                $getparty = mysqli_query($conn, "SELECT * FROM `accmast` WHERE `accode` = '$fmain[party]'");
                                $gtpt = mysqli_fetch_array($getparty);
                                $party = $gtpt['name'];

                                if ($fmain['payfor'] == "T") {
                                    $payfor = "TICKET";
                                    $tktamt = $fmain['amount'] ;
                                    $tkttotamt += $fmain['amount'];
                                } else {
                                    $payfor = "TOUR";
                                    $touramt = $fmain['amount'] ;
                                    $tourtotamt += $fmain['amount'];
                                }

                                if ($fmain['source'] == "CR") {
                                    $sour = "CASH";
                                } else {
                                    $sour = "BANK";
                                }

                                $ctr++;
                                if ($fmain['source'] == "CR") {
                                    echo "<tr id='boldfont' data-href='cashrecp.php?vouchno=$fmain[vouchno]&mode=edit' class='clickable-row2'></a>";
                                } else {
                                    echo "<tr id='boldfont' data-href='bankrecp.php?vouchno=$fmain[vouchno]&mode=edit' class='clickable-row2'></a>";
                                }

                                echo "<td>$fmain[mydt]</td>";
                                echo "<td>$sour</td>";
                                echo "<td>$fmain[vouchno]</td>";
                                echo "<td>$fmain[regnno]</td>";
                                echo "<td>$party</td>";
                                echo "<td class='text-right'>". number_format($tktamt,0)."</td>";
                                echo "<td class='text-right'>". number_format($touramt,0)."</td>";
                                echo "<td>$fmain[recdby]</td>";
                                echo "</tr>";

                                $bankdetails = "Rtgs - $fmain[rtgsno] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cheque - $fmain[chqno] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dated - $fmain[chqdate] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Drawee - $fmain[drawbank]";
                                if ($fmain['source'] == "BR") {
                                    echo "<tr id='boldfont' style='background-color:#C6FCFF' data-href='bankrecp.php?vouchno=$fmain[vouchno]&mode=edit' class='clickable-row2'></a>";
                                    echo "<td colspan='2'></td>";
                                    echo "<td colspan='6' style=''><i>$bankdetails</i></td>";
                                    echo "</tr>";
                                }
                            }
                            echo "<tr id='boldfont'>";
                            echo "<td class='text-right' colspan='5'>Total</td>";
                            echo "<td class='text-right'>$tkttotamt</td>";
                            echo "<td class='text-right'>$tourtotamt</td>";
                            echo "<td></td>";
                            echo "</tr>";
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

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

    </html>
    <?php
}

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

    $contents .="Date, Source, Voucher, Regn.No., Haji, For, Amount, Received By\n";

                            $ctr = 0;
                            $discamt = 0;
                            $totamt = 0;

                            while ($fmain = mysqli_fetch_array($mainqry)) {

                                $gpaxno = mysqli_query($conn, "SELECT * FROM $paxdb WHERE `vregnno`='$fmain[vouchno]' ") or die(mysqli_error($conn));
                                $paxnum = mysqli_num_rows($gpaxno);
                                $fetgpax = mysqli_fetch_object($gpaxno);

                                $getparty = mysqli_query($conn, "SELECT * FROM `accmast` WHERE `accode` = '$fmain[party]'");
                                $gtpt = mysqli_fetch_array($getparty);
                                $party = $gtpt['name'];

                                if ($fmain['payfor'] == "tkt") {
                                    $payfor = "TICKET";
                                } else {
                                    $payfor = "TOUR";
                                }

                                if ($fmain['source'] == "CR") {
                                    $sour = "CASH";
                                } else {
                                    $sour = "BANK";
                                }

                                $ctr++;
                                if ($fmain['source'] == "CR") {
                                    // echo "<tr id='boldfont' data-href='cashrecp.php?vouchno=$fmain[vouchno]&mode=edit' class='clickable-row2'></a>";
                                } else {
                                    // echo "<tr id='boldfont' data-href='bankrecp.php?vouchno=$fmain[vouchno]&mode=edit' class='clickable-row2'></a>";
                                }

                                $contents .="$fmain[mydt]" . ",";
                                $contents .="$sour" . ",";
                                $contents .="$fmain[vouchno]" . ",";
                                $contents .="$fmain[regnno]" . ",";
                                $contents .="$party" . ",";
                                $contents .="$payfor" . ",";
                                $contents .="$fmain[amount]" . ",";
                                $contents .="$fmain[recdby]" . "\n";

                                // $bankdetails = "Rtgs - $fmain[rtgsno] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cheque - $fmain[chqno] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dated - $fmain[chqdate] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Drawee - $fmain[drawbank]";
                                if ($fmain['source'] == "BR") {
                                    $contents .="" . ",";
                                    $contents .="$bankdetails" . "\n";
                                }

                                $totamt += $fmain['amount'];
                            }

                            $contents .="" . ",";
                            $contents .="" . ",";
                            $contents .="" . ",";
                            $contents .="" . ",,";
                            $contents .="Total" . ",";
                            $contents .="$totamt" . ",";
                            $contents .="" . "\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;
    }
}
?>