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

$zdb = $_SESSION["zdbyear_" . $dbname];
$admin = $_SESSION["admin_" . $dbname];
$zroot = $_SESSION['zdir'];

$trandb = "actran";

$myear = $_SESSION["tit_" . $dbname];
mysqli_select_db($conn, $zdb);

//browse('Work in progress...');

$acond = "";
$scond = "";
$curdate = date("Y-m-d");
$tourcode = $_SESSION['tourcode'];
$tourcond = "AND tourno = '$tourcode'";

if (!empty($_POST["acname"])) {
    $accode = trim($_POST["acname"]);
    $pcond = " AND  a.`partycode`='$accode'";
    $pcond1 = " AND  `party`='$accode'";
    $pcond2 = " AND a.agent = '$accode'";
    $pcond3 = " AND a.customer = '$accode'";

//    $pcond3 = " AND a.`accode`='$accode'";
//    $pcond2 = " AND a.`partyname`='$accode'";
//    $acond = " AND a.`accode`='$accode'";
}
$mtodate = $_POST["frmdt"];

$mfile = "pax";
$visfile = "visanewmain";
$hotelfile = "hotelslmain";

$mfile2 = "bookform";
$mfile3 = "jvtran";
$mtype = "R";
$mdate = 'vouchdt';
$fintot = "netamt";
$grid = "osbill.php?para=R";
$formname = "Outstanding Receivable";

if ($_POST["dtype"] == 1) {
    $dtype = 1;
} else {
    $dtype = 2;
}

mysqli_query($conn, "drop temporary table if exists cmain") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table cmain 
                    SELECT a.partycode as party, b.name, a.netamt as finamt, a.invdate  FROM `$mfile` a, accmast b WHERE a.agent = '' and a.partycode = b.accode and a.invno != '' and a.invdate <= '$mtodate'  and cancdate = '0000-00-00' $pcond 
                    union all   
                    SELECT a.agent as party, b.name, a.netamt as finamt, a.invdate  FROM `$mfile` a, accmast b WHERE a.agent = b.accode and a.invno != '' and a.invdate <= '$mtodate'  and cancdate = '0000-00-00' $pcond2 
                    union all   
                    SELECT a.customer as party, b.name, a.totamt as finamt, a.vouchdt as invdate FROM `$visfile` a, accmast b WHERE a.customer = b.accode and a.vouchno != '' and a.vouchdt <= '$mtodate'  $pcond3 
                    union all   
                    SELECT a.customer as party, b.name, a.totamt as finamt, a.vouchdt as invdate  FROM `$hotelfile` a, accmast b WHERE a.customer = b.accode and a.vouchno != '' and a.vouchdt <= '$mtodate'  $pcond3 
                    ") or die(mysqli_error($conn));


$acc = mysqli_query($conn, "SELECT party, name FROM cmain group by party order by name ") or die(mysqli_error($conn));
$getno = mysqli_num_rows($acc);

mysqli_query($conn, "drop temporary table if exists bsettdbf") or die(mysqli_error($conn));
mysqli_query($conn, "create temporary table bsettdbf 
                            SELECT * from billsett where setoff > 0 $pcond1 
                                   and concat(source, bnkvouch) in (select concat(source, vouchno) from actran where date <= '$mtodate' )
                            ") or die(mysqli_error($conn));


if (isset($_POST["view"])) {
    include("includes/linkshead.php");

    $mtdt = date("d-m-Y", strtotime($mtodate));
    ?>
    <link href="MyStyle/rep_format.css" rel="stylesheet">

    <style>
        h5 {
            font-size: 18px;
            margin-top: 7px;
            margin-left: 6px;
        }

        .report {
            margin-top: 0px;
        }
    </style>
    </head>

    <body>
        <div class="row" style="margin-top: 15px;">

            <div class="col-lg-6" style="width:100%;">


                <div class="box">
                    <?php include("includes/head_rep.php"); ?>
                    <div id="sortableTable" class="body collapse in">
                        <table class="table table-bordered sortableTable responsive-table">
                            <table style="width:100%;" id="" class="table-bordered sortableTable responsive-table report table table-bordered">
                                <thead>
                                    <tr class="mySearchTr">
                                        <th style="" colspan="5">Name</th>
                                        <?php if($dtype == 1){ ?>
                                        <th style="text-align: right;">Amount</th>
                                        <?php } ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    $totbalamt = 0;
                                    $ctr = 0;
                                    $grtotbalamt1 = 0;
                                    while ($getac = mysqli_fetch_array($acc)) {
                                        $maccode = $getac["party"];
                                        $macname = $getac["name"];
                                        //browse($maccode);
                                        
//                                        $sub1 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `partycode` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
//                                        $sub2 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `agent` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
//                                        $sub3 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $visfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
//                                        $sub4 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $hotelfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
//
//                                        $msub1 = mysqli_fetch_array($sub1);
//                                        $msub2 = mysqli_fetch_array($sub2);
//                                        $msub3 = mysqli_fetch_array($sub3);
//                                        $msub4 = mysqli_fetch_array($sub4);
//
//                                        $msamt1 = $msub1["amt1"];
//                                        $msamt2 = $msub2["amt1"];
//                                        $msamt3 = $msub3["amt1"];
//                                        $msamt4 = $msub4["amt1"];
//                                        
//                                        $qamt1 = $msamt1 + $msamt2 + $msamt3 + $msamt4;
                                        
                                        
                                        $sub1 = mysqli_query($conn, "SELECT SUM(finamt) AS amt1 FROM cmain WHERE `party` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
                                        $msub1 = mysqli_fetch_array($sub1);
                                        $qamt1 = $msub1["amt1"];

                                        $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `bsettdbf` WHERE  `party`= '$maccode' AND `type`='S'") or die(mysqli_error($conn));
                                        $gtrn1 = mysqli_fetch_object($trn1);
                                        $msetoff1 = $gtrn1->setoff;

                                        $mbalamt1 = $qamt1 - $msetoff1;
//                                        browse($qamt1);
//                                        browse($msetoff1);
//                                        browse($mbalamt1);
                                        

                                        if ($mbalamt1 > 0) {
                                            if ($dtype == 2 && $ctr > 0) {
                                                echo "<tr style='background-color:white;border-top:3px solid black'>";
                                            } else {
                                                echo "<tr style='background-color:white'>";
                                            }
                                            $ctr++;
                                            echo "<td colspan='5' style=\"text-align:left;\"><b>" . strtoupper($macname) . "</b></td>";
                                            if ($dtype == 1) {

                                                echo "<td style=\"text-align:right;\">" . rajnumformat($mbalamt1, 2) . "</td>";
                                            }
                                            echo "</tr>";
                                            $grtotbalamt = $grtotbalamt + $mbalamt1;
                                            if ($dtype == 2) {
                                                ?>

                                                <tr style="height:10px;" class="mySearchTr">
                                                    <th align="left" width="10%">Date</th>
                                                    <th align="left" width="30%">Bill No.</th>
                                                    <!--<th align="left" width="15%">Due Date</th>-->
                                                    <th align="right" width="15%" style="text-align:right;">Bill Amount</th>
                                                    <th align="right" width="15%" style="text-align:right;">Received Amount</th>
                                                    <th align="right" width="15%" style="text-align:right;">Balance</th>
                                                    <th align="right" width="9%" style="text-align:right;">Days</th>
                                                </tr>
                                                <?php
//                                                $subtmp1 = mysqli_query($conn, "SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
//                                                                        DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`partycode` as  accode 
//                                                                        FROM $mfile WHERE `partycode` = '$maccode' AND invdate <= '$mtodate' and cancdate = '0000-00-00' 
//                                                                        UNION
//                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,regnno as `billno`, 'HUT' as src, vouchdt as vcdt,
//                                                                        DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`,  `$fintot` as billamt, accode 
//                                                                        FROM $mfile2 WHERE `accode` = '$maccode' AND vouchdt <= '$mtodate' and cancdate = '0000-00-00' $tourcond
//                                                                         order by vcdt ASC") or die(mysqli_error($conn));
                                                
                                               
                                                
                                                $subtmp1 = mysqli_query($conn, "select '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
                                                                        DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`partycode` as  accode ,'' as guestname
                                                                        FROM $mfile WHERE  agent = '' and `partycode` = '$maccode' AND invdate <= '$mtodate' 
                                                                        and invno != '' and cancdate = '0000-00-00'    
                                                                        union
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
                                                                        DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`agent` as  accode  ,'' as guestname
                                                                        FROM $mfile WHERE `agent` = '$maccode' AND invdate <= '$mtodate' and invno != '' and cancdate = '0000-00-00'    
                                                                        union
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'VN' as src, vouchdt as vcdt,
                                                                        DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, '' as guestname
                                                                        FROM $visfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
                                                                        union
                                                                        SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'HTSL' as src, vouchdt as vcdt,
                                                                        DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, prname as guestname 
                                                                        FROM $hotelfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
                                                                        order by vcdt,billno ASC") or die(mysqli_error($conn));
                                                
//                                                print table($subtmp1);
//                                                die();

                                                $totbillamt = 0;
                                                $totrecdamt = 0;
                                                $totbalamt = 0;
                                                while ($murl = mysqli_fetch_object($subtmp1)) {

                                                    $qyear = $murl->yearno;
                                                    $qbilldate = $murl->billdate;
                                                    $qbill = $murl->billno;
                                                    $qvouch = $murl->vouchno;
                                                    $qamt = $murl->billamt;
                                                    $qparty = $murl->accode;
                                                    $src = $murl->src;
                                                    $duedt = $murl->duedt;
                                                    $duedate = $murl->duedate;

                                                    $dt1 = date_create($mtodate);
                                                    $dt2 = date_create($qbilldate);

                                                    $subtdate = date_diff($dt1, $dt2);
                                                    $mldays = $subtdate->format('%a');

                                                
                                                    $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `bsettdbf` WHERE `party`= '$qparty' 
                                                                                       AND `yearno` ='$qyear' AND `vouchno`='$qvouch' AND `type`='S' ") or die(mysqli_error($conn));

                                                    $gtrn = mysqli_fetch_object($trn);

                                                    $msetoff = $gtrn->setoff;
                                                    $mbalamt = $qamt - $msetoff;

                                                    if (round($mbalamt) > 0) {
                                                        $mbill = $src . " - " . $qvouch;

//                                                        browse($src);

                                                        $prname = $murl->guestname;
                                                        if ($prname != "") {
                                                            $mbill = $mbill . " ( " . $prname . " )";
                                                        } else {

                                                            if ($src == "FIT") {
                                                                $nqry1 = mysqli_query($conn, "SELECT concat(surname, ' ' , name, ' ' , midname) as fname, agent FROM `pax`
                                                                       WHERE vouchno='$qvouch'") or die(mysqli_error($conn));
//                                                            print table($nqry1);
//                                                            die();
                                                            } else {
                                                                $nqry1 = mysqli_query($conn, "SELECT fname FROM `bookform`
                                                                       WHERE vouchno='$qbill'") or die(mysqli_error($conn));
                                                            }
                                                            $nqry = mysqli_fetch_array($nqry1);
//                                                $mvouch = $nqry["regnno"];
                                                            $mbill = $mbill . " ( " . $nqry['fname'] . " )";
                                                        }


                                                        $totbillamt = $totbillamt + $qamt;
                                                        $totrecdamt = $totrecdamt + $msetoff;
                                                        $totbalamt = $totbalamt + $mbalamt;
                                                        echo "<tr style='height:10px;background-color:white;'>";
                                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"300\">$qbilldate</td>";
                                                        echo "<td style=\"text-align:left;\" scope=\"col\" width=\"100\">$mbill</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"300\">$qamt</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$msetoff</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$mbalamt</td>";
                                                        echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\">$mldays</td>";
                                                        echo "</tr>";
                                                    }
                                                }
                                                echo "<tr style='height:10px;background-color:white'>";
                                                echo "<td style=\"text-align:left;\" scope=\"col\" width=\"300\"></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"100\"><b>Total</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"300\"><b>" . rajnumformat($totbillamt) . "</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>" . rajnumformat($totrecdamt) . "</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"><b>" . rajnumformat($totbalamt) . "</b></td>";
                                                echo "<td style=\"text-align:right;\" scope=\"col\" width=\"200\"></td>";
                                                echo "</tr>";
                                                $grtotbalamt1 = $grtotbalamt1 + $totbalamt;
                                            }
                                        }
                                    }
                                    if ($dtype == 2) {
                                        echo "<tr style='background-color:white'>";
                                        echo "<td colspan='4' style=\"text-align:right;font-size:15px\"><b>Total</b></td>";
                                        echo "<td style=\"text-align:right;font-size:15px\">";
                                        echo "<b>$grtotbalamt1</b>";
                                        echo "</td>";
                                        echo "</tr>";
                                    } 
                                    ?>
                                </tbody>
                            </table>
                    </div>
                </div>
            </div>
        </div>
        <?php
    }

    if (isset($_POST["excel"])) {
     
        
        if($dtype == 1){
            $contents .= "Name,,,,,Amount" . "\n";
        }else{
            $contents .= ",Name,,,,\n";
        }


        $totbalamt = 0;
        $ctr = 0;
        $grtotbalamt1 = 0;
        while ($getac = mysqli_fetch_array($acc)) {

            $maccode = $getac["party"];
            $macname = $getac["name"];

            $sub1 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `partycode` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
            $sub2 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `agent` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
            $sub3 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $visfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
            $sub4 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $hotelfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));

            $msub1 = mysqli_fetch_array($sub1);
            $msub2 = mysqli_fetch_array($sub2);
            $msub3 = mysqli_fetch_array($sub3);
            $msub4 = mysqli_fetch_array($sub4);

            $msamt1 = $msub1["amt1"];
            $msamt2 = $msub2["amt1"];
            $msamt3 = $msub3["amt1"];
            $msamt4 = $msub4["amt1"];

            $qamt1 = $msamt1 + $msamt2 + $msamt3 + $msamt4;

            $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$maccode' AND `type`='S'") or die(mysqli_error($conn));
            $gtrn1 = mysqli_fetch_object($trn1);
            $msetoff1 = $gtrn1->setoff;

            $mbalamt1 = $qamt1 - $msetoff1;

            if ($mbalamt1 > 0) {
                
                $ctr++;
                if ($dtype == 1) {
                    
                    $contents .=  strtoupper($macname) . ",,,,,";
                    $contents .=  $mbalamt1 . "\n";
                }else{
                    $contents .=  ",".strtoupper($macname) . ",,,,\n";
                }
                $grtotbalamt = $grtotbalamt + $mbalamt1;
                if ($dtype == 2) {
                
                        $contents .="Date, Bill No., Bill Amount, Received Amount, Balance,Days\n";

                    $subtmp1 = mysqli_query($conn, "select '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
                                            DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`partycode` as  accode ,'' as guestname
                                            FROM $mfile WHERE `partycode` = '$maccode' AND invdate <= '$mtodate' 
                                            union
                                            SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
                                            DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`agent` as  accode  ,'' as guestname
                                            FROM $mfile WHERE `agent` = '$maccode' AND invdate <= '$mtodate' 
                                            union
                                            SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'VN' as src, vouchdt as vcdt,
                                            DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, '' as guestname
                                            FROM $visfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
                                            union
                                            SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'HTSL' as src, vouchdt as vcdt,
                                            DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, prname as guestname 
                                            FROM $hotelfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
                                            order by vcdt,billno ASC") or die(mysqli_error($conn));

                    $totbillamt = 0;
                    $totrecdamt = 0;
                    $totbalamt = 0;
                    while ($murl = mysqli_fetch_object($subtmp1)) {

                        $qyear = $murl->yearno;
                        $qbilldate = $murl->billdate;
                        $qbill = $murl->billno;
                        $qvouch = $murl->vouchno;
                        $qamt = $murl->billamt;
                        $qparty = $murl->accode;
                        $src = $murl->src;
                        $duedt = $murl->duedt;
                        $duedate = $murl->duedate;


                        $dt1 = date_create($mtodate);
                        $dt2 = date_create($qbilldate);

                        $subtdate = date_diff($dt1, $dt2);
                        $mldays = $subtdate->format('%a');

                        $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
                                           `vouchno`='$qvouch' AND `type`='S'") or die(mysqli_error($conn));

                        $gtrn = mysqli_fetch_object($trn);

                        $msetoff = $gtrn->setoff;
                        $mbalamt = $qamt - $msetoff;

                        if (round($mbalamt) > 0) {
                            $mbill = $src . " - " . $qvouch;

                            $prname = $murl->guestname;
                            if ($prname != "") {
                                $mbill = $mbill . " ( " . $prname . " )";
                            } else {

                                if ($src == "FIT") {
                                    $nqry1 = mysqli_query($conn, "SELECT concat(surname, ' ' , name, ' ' , midname) as fname, agent FROM `pax`
                                           WHERE vouchno='$qvouch'") or die(mysqli_error($conn));
                                } else {
                                    $nqry1 = mysqli_query($conn, "SELECT fname FROM `bookform`
                                           WHERE vouchno='$qbill'") or die(mysqli_error($conn));
                                }
                                $nqry = mysqli_fetch_array($nqry1);
                                $mbill = $mbill . " ( " . $nqry['fname'] . " )";
                            }


                            $totbillamt = $totbillamt + $qamt;
                            $totrecdamt = $totrecdamt + $msetoff;
                            $totbalamt = $totbalamt + $mbalamt;
                            $contents .= "$qbilldate" . ",";
                            $contents .= "$mbill" . ",";
                            $contents .= "$qamt" . ",";
                            $contents .= "$msetoff" . ",";
                            $contents .= "$mbalamt" . ",";
                            $contents .= "$mldays" . "\n";
                        }
                    }

                    $contents .= "" . ",";
                    $contents .= "Total" . ",";
                    $contents .= $totbillamt . ",";
                    $contents .= $totrecdamt . ",";
                    $contents .= $totbalamt . ",";
                    $contents .= "" . "\n";
                    $grtotbalamt1 = $grtotbalamt1 + $totbalamt;
                }
            }
        }
        
        if ($dtype == 2) {

            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "" . ",";
            $contents .= "Total" . ",";
            $contents .= "$grtotbalamt1" . "\n";
        }



        // $totbalamt = 0;
        // $ctr = 0;
        // $grtotbalamt1 = 0;
        // while ($getac = mysqli_fetch_array($acc)) {


        //     $maccode = $getac["party"];
        //     $macname = $getac["name"];
        //     $sub1 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `partycode` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
        //     $sub2 = mysqli_query($conn, "SELECT SUM($fintot) AS amt1 FROM $mfile WHERE `agent` = '$maccode'  and invdate <= '$mtodate'") or die(mysqli_error($conn));
        //     $sub3 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $visfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));
        //     $sub4 = mysqli_query($conn, "SELECT SUM(totamt) AS amt1 FROM $hotelfile WHERE `customer` = '$maccode'  and vouchdt <= '$mtodate'") or die(mysqli_error($conn));

                              
        //     $msub1 = mysqli_fetch_array($sub1);
        //     $msub2 = mysqli_fetch_array($sub2);
        //     $msub3 = mysqli_fetch_array($sub3);
        //     $msub4 = mysqli_fetch_array($sub4);

        //     $msamt1 = $msub1["amt1"];
        //     $msamt2 = $msub2["amt1"];
        //     $msamt3 = $msub3["amt1"];
        //     $msamt4 = $msub4["amt1"];

        //     $qamt1 = $msamt1 + $msamt2 + $msamt3 + $msamt4;


        //     $trn1 = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$maccode' AND `type`='S'") or die(mysqli_error($conn));
        //     $gtrn1 = mysqli_fetch_object($trn1);
        //     $msetoff1 = $gtrn1->setoff;

        //     $mbalamt1 = $qamt1 - $msetoff1;

        //     if ($mbalamt1 > 0) {
               
        //         $ctr++;
                
        //         $contents .= "$macname" . "\n";
        //         if ($dtype == 1) {
        //             $contents .= "$mbalamt1" . "\n";

                  
        //         }
        //         $grtotbalamt = $grtotbalamt + $mbalamt1;

        //         if ($dtype == 2) {

        //             $contents .= "Date, Bill No., Bill Amount, Received Amount, Balance, Days, \n";
                   
        //             $subtmp1 = mysqli_query($conn, "select '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
        //                                                                 DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`partycode` as  accode ,'' as guestname
        //                                                                 FROM $mfile WHERE `partycode` = '$maccode' AND invdate <= '$mtodate' 
        //                                                                 union
        //                                                                 SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,invno as `billno`, 'FIT' as src, invdate as vcdt,
        //                                                                 DATE_FORMAT(invdate, '%d-%m-%Y') AS `billdate`,  `netamt` as billamt,`agent` as  accode  ,'' as guestname
        //                                                                 FROM $mfile WHERE `agent` = '$maccode' AND invdate <= '$mtodate' 
        //                                                                 union
        //                                                                 SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'VN' as src, vouchdt as vcdt,
        //                                                                 DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, '' as guestname
        //                                                                 FROM $visfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
        //                                                                 union
        //                                                                 SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`, vouchno as `billno`, 'HTSL' as src, vouchdt as vcdt,
        //                                                                 DATE_FORMAT(vouchdt, '%d-%m-%Y') AS `billdate`,  `totamt` as billamt, `customer` as  accode, prname as guestname 
        //                                                                 FROM $hotelfile WHERE `customer` = '$maccode' AND vouchdt <= '$mtodate' 
        //                                                                 order by vcdt,billno ASC") or die(mysqli_error($conn));

        //                                         $totbillamt = 0;
        //                                         $totrecdamt = 0;
        //                                         $totbalamt = 0;
        //                                         while ($murl = mysqli_fetch_object($subtmp1)) {

        //                                             $qyear = $murl->yearno;
        //                                             $qbilldate = $murl->billdate;
        //                                             $qbill = $murl->billno;
        //                                             $qvouch = $murl->vouchno;
        //                                             $qamt = $murl->billamt;
        //                                             $qparty = $murl->accode;
        //                                             $src = $murl->src;
        //                                             $duedt = $murl->duedt;
        //                                             $duedate = $murl->duedate;


        //                                             $dt1 = date_create($mtodate);
        //                                             $dt2 = date_create($qbilldate);

        //                                             $subtdate = date_diff($dt1, $dt2);
        //                                             $mldays = $subtdate->format('%a');

        //                                             $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
        //                                                                `vouchno`='$qvouch' AND `type`='S'") or die(mysqli_error($conn));

        //                                             $gtrn = mysqli_fetch_object($trn);

        //                                             $msetoff = $gtrn->setoff;
        //                                             $mbalamt = $qamt - $msetoff;

        //                                             if (round($mbalamt) > 0) {
        //                                                 $mbill = $src . " - " . $qvouch;


        //                                                 $prname = $murl->guestname;
        //                                                 if ($prname != "") {
        //                                                     $mbill = $mbill . " ( " . $prname . " )";
        //                                                 } else {

        //                                                     if ($src == "FIT") {
        //                                                         $nqry1 = mysqli_query($conn, "SELECT concat(surname, ' ' , name, ' ' , midname) as fname, agent FROM `pax`
        //                                                                WHERE vouchno='$qvouch'") or die(mysqli_error($conn));
    
        //                                                     } else {
        //                                                         $nqry1 = mysqli_query($conn, "SELECT fname FROM `bookform`
        //                                                                WHERE vouchno='$qbill'") or die(mysqli_error($conn));
        //                                                     }
        //                                                     $nqry = mysqli_fetch_array($nqry1);

        //                                                     $mbill = $mbill . " ( " . $nqry['fname'] . " )";
        //                                                 }


        //                                                 $totbillamt = $totbillamt + $qamt;
        //                                                 $totrecdamt = $totrecdamt + $msetoff;
        //                                                 $totbalamt = $totbalamt + $mbalamt;
                                                      
        //                                                 $contents .= "$qbilldate" . ",";
        //                                                 $contents .= "$mbill" . ",";
        //                                                 $contents .= "$qamt" . ",";
        //                                                 $contents .= "$msetoff" . ",";
        //                                                 $contents .= "$mbalamt" . ",";
        //                                                 $contents .= "$mldays" . "\n";

        //                                                 $contents .= "" . ",";
        //                                                 $contents .= "Total" . ",";
        //                                                 $contents .= "$totbillamt" . ",";
        //                                                 $contents .= "$totrecdamt" . ",";
        //                                                 $contents .= "$totbalamt" . ",";
        //                                                 $contents .= "" . "\n";

        //                                                 $grtotbalamt1 = $grtotbalamt1 + $totbalamt;


        //                                                 if ($dtype == 2) {

        //                                                     $contents .= "Total" . ",,,,";
        //                                                     $contents .= "$totbillamt" . ",";
        //                                                     $contents .= "$grtotbalamt1" . "\n";

        //                                                     $colsp = "4";
        //                                                 } else {
        //                                                     $colsp = "5";
        //                                                 }
        $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;
        }
    }

    ?>