<?php
ob_start();
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'];
$file = 'osbill.php';
$trandb = 'actran';
$myear = $_SESSION['tit_' . $dbname];

$getcomp = mysqli_query($conn, "SELECT * FROM $zroot.`company`") or die(mysqli_error($conn));
$comp = mysqli_fetch_array($getcomp);
$compname = $comp['compname'];

mysqli_select_db($conn, $zdb);

//if (isset($_POST['submit'])) {
//    $count = $_POST['mailcount'];
//    $manualmsg = $_POST['manualmsg'];
//
//    for ($i = 0; $i <= $count; ++$i) {
//        $chkmail = $_POST["sendmail_$i"];
//        $to = $_POST["email_$i"];
//        $balance = $_POST["balance_$i"];
//
//        if ($chkmail == 1) {
//            $subject = $compname . ' - Balance Pending';
//            $message = 'Your outstanding amount with ' . $compname . ' as on ' . date('Y-m-d') . ' is Rs.' . $balance . ". \r\n" . $manualmsg;
//            mail($to, $subject, $message);
////            browse($message);
//        }
//    }
//    browse('Mail Send Successfully');
//    header("refresh:1; $file");
//}

$medium = $_POST['medium'];
$exam = $_POST['exam'];
$section = $_POST['section'];
$scope = $_POST['scope'];
$scopecond = "";
if (!empty($scope)) {
    if ($scope == 1) {
        $scopecond = " and localflg = '1'";
    } else {
        $scopecond = " and localflg = ''";
    }
}
//browse($exam);
$acond = '';
$scond = '';
$custcond = '';
$medcond = '';
$examcond = '';
$examcond1 = '';

$mfrdate = $_POST['frmdt'];
if ($mfrdate == "") {
    $mfrdate = "1970-09-01";
}
$mtodate = $_POST['todt'];
if (!empty($exam)) {
    $examcond = " AND b.`examcode` = '$exam' ";
    $examcond1 = " AND `examcode` = '$exam' ";
}

if (!empty($section)) {
//        $sectioncond = " AND a.`section` = '$section'";
    $sectioncond1 = " AND `section` = '$section'";
}

if (!empty($_POST['acname'])) {
    foreach ($_POST['acname'] as $cucheck2) {
        $ccheck2[$ccount2++] .= "OR a.`accode` = '" . $cucheck2 . "' ";
    }
    foreach ($ccheck2 as $g => $cusvalues2) {
        $cuscheck2 .= $cusvalues2;
    }
    $acond = " AND (a.`accode` = '' " . $cuscheck2 . ' )';
}

if (!empty($_POST['acname'])) {
    foreach ($_POST['acname'] as $cucheck1) {
        $ccheck1[$ccount2++] .= "OR `accode` = '" . $cucheck1 . "' ";
    }
    foreach ($ccheck1 as $g => $cusvalues3) {
        $cuscheck1 .= $cusvalues3;
    }
    $acond1 = " AND (`accode` = '' " . $cuscheck1 . ' )';
}

if (!empty($_POST['custcode'])) {
    foreach ($_POST['custcode'] as $cucheck1) {
        $ccheck1[$ccount1++] .= "OR a.`custcode` = '" . $cucheck1 . "' ";
    }
    foreach ($ccheck1 as $t => $cusvalues1) {
        $cuscheck1 .= $cusvalues1;
    }
    $custcond = " AND (a.`custcode` = '' " . $cuscheck1 . ' )';
}

if (!empty($_POST['custcode'])) {
    foreach ($_POST['custcode'] as $cucheck) {
        $ccheck[$ccount1++] .= "OR `custcode` = '" . $cucheck . "' ";
    }
    foreach ($ccheck as $t => $cusvalues) {
        $cuscheck .= $cusvalues;
    }
    $custcond1 = " AND (`custcode` = '' " . $cuscheck . ' )';
}

if (!empty($medium)) {
    $medcond = " AND a.`medmcode` = '$medium' ";
    $medcond1 = " AND `medmcode` = '$medium' ";
}
$para = $_POST['hiddenval'];

if ($_POST['hiddenval'] == 'R') {
    $mfile = 'invmain';
    $mtype = 'S';
    $mdate = 'vouchdt';
    $fintot = 'billamt';
    $grid = 'osbill.php?para=R';
    $formname1 = 'Outstanding Receivable';
} else {
    $mfile = 'purmain';
    $mtype = 'P';
    $mdate = 'vouchdt';
    $fintot = 'netamt';
    $grid = 'osbill.php?para=P';
    $formname1 = 'Outstanding Payable';
}
if ($_POST['dtype'] == 1) {
    $formname = "$formname1 - Summary";
    $dtype = 1;
} else {
    $formname = "$formname1 - Detail";
    $dtype = 2;
}

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;
        }

        table tr td {
            font-size: 11px;
            font-family: Verdana;

        }
        table th{
            font-size: 12px;
            font-family: Verdana;
        }
        .fontchange{
            color:black;
        }

    </style>
    </head>
    <body>
        <div class="container">
            <?php include 'includes/head_rep.php'; ?>
            <br>
            <form action="" method="POST">
                <?php
                if ($dtype == 1 && $para == 'R') {
                    echo "<div class='row'>";
                    echo "<div class='col-lg-1' style='padding: 0px;margin: 0px;'>";
                    echo '<label>Message</label>';
                    echo '</div>';
                    echo "<div class='col-lg-10' style='padding: 0px;margin: 0px;'>";
                    echo "<textarea name='manualmsg' class='form-control'></textarea>";
                    echo '</div>';
                    echo "<div class='col-lg-1'>";
                    echo "<input type='submit' name='submit' value='Send Mail' class='btn btn-primary'>";
                    echo '</div>';
                    echo '</div>';
                }
                ?>
                <div class="box">
                    <div id="sortableTable" class="body collapse in">
                        <table style="width:100%;" id="" class="table table-bordered table-condensed table-stiped sortableTable responsive-table report">
                            <thead>
                                <tr class="mySearchTr">
                                    <th style="" colspan="5">Name</th>
                                    <th style="text-align: right;">Amount</th>
                                    <?php
                                    if ($dtype == 1) {
                                        echo '<th>Send Mail</th>';
                                    }
                                    ?>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $mbalamt1 = $mtr = 0;
                                $grbill = $grrecd = $grbal = 0;
                                if (!empty($exam)) {
                                    $acc = mysqli_query($conn, "SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
                                                    a.email, b.examcode FROM `accmast` a, invmain b WHERE 
                                                    a.`accode` = b.`accode` and a.`grpcode` = '010' AND 
                                                    b.`vouchdt` >= '$mfrdate' and b.`vouchdt` >= '$mtodate' 
                                                    $custcond $medcond $acond $sectioncond $examcond $scopecond
                                                    UNION ALL
                                                    SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
                                                    a.email, b.examcode FROM `accmast` a, prevbill b WHERE 
                                                    a.`accode` = b.`accode` and a.`grpcode` = '010' AND 
                                                    b.`billdate` >= '$mfrdate' and b.`billdate` <= '$mtodate' 
                                                    $custcond $medcond $acond  $sectioncond $examcond $scopecond order by custcode") or die(mysqli_error($conn));
                                } else {
                                    $acc = mysqli_query($conn, "SELECT accode, custcode, name, tel, mobile, email
                                                    FROM `accmast` WHERE `grpcode` = '010' $custcond1 
                                                    $medcond1 $acond1 $scopecond order by custcode") or die(mysqli_error($conn));
                                }

                                //print table($acc);
                                while ($getac = mysqli_fetch_array($acc)) {
                                    $maccode = $getac['accode'];
                                    $mcustcode = $getac['custcode'];
                                    $macname = $getac['name'];
                                    $tel = $getac['tel'];
                                    $mobile = $getac['mobile'];
                                    $email = $getac['email'];

                                    $sub1 = mysqli_query($conn, "SELECT  SUM($fintot) AS amt1 FROM $mfile WHERE `accode` = '$maccode' 
                                                     AND `vouchdt` >= '$mfrdate' AND `vouchdt` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

                                    $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' 
                                                    and `billdate` >= '$mfrdate' AND `billdate` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

                                    $msub1 = mysqli_fetch_array($sub1);
                                    $msub2 = mysqli_fetch_array($sub2);
                                    $msamt1 = $msub1['amt1'];

                                    $msamt2 = $msub2['amt2'];
                                    $qamt1 = $msamt1 + $msamt2;

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

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

                                    if ($mbalamt1 > 0) {
                                        echo '<tr>';
                                        echo "<td colspan='3' style=\"text-align:left;\"><b>" . strtoupper($macname) . '</b></td>';
                                        echo "<td colspan='2' style=\"text-align:left;\"><b>Code : " . $mcustcode . '</b></td>';
                                        if ($dtype == 1 && $para == 'R') {
                                            echo '<td style="text-align:right;">';
                                            echo number_format($mbalamt1, 2);
                                            echo '</td>';
                                            echo "<td><input type='checkbox' name='sendmail_$mtr' value='1'>
                                                      <input type='hidden' name='email_$mtr' value='$email'>
                                                      <input type='hidden' name='balance_$mtr' value='$mbalamt1'>
                                                    </td>";
                                            ++$mtr;
                                        }
                                        echo '</tr>';
                                        $totmbalamt = $totmbalamt + $mbalamt1;

                                        if ($dtype == 2) {
                                            ?>        
                                            <tr>
                                                <td  style="font-size:12px" align="left" colspan="2" >Tel. :<?php echo $tel; ?></td>                                   
                                                <td  style="font-size:12px" align="left" colspan="1" >Mobile :<?php echo $mobile; ?> </td>                                   
                                                <td  style="font-size:12px" align="left" colspan="3" >Email : <?php echo $email; ?></td>  
                                            </tr>

                                            <tr style="background-color: #d6e3fc;color: black" class="">
                                                <th align="left" width="12%" class="fontchange">Date</th>                                   
                                                <th align="left" width="10%" class="fontchange">Bill No.</th>                                   
                                                <th align="left" width="40%" class="fontchange">Exam</th>                                   
                                                <th align="right" width="12%" class="fontchange" style="text-align:right;">Bill Amount</th>                                   
                                                <th align="right" width="14%" class="fontchange" style="text-align:right;">Recd. Amount</th>                                   
                                                <th align="right" width="12%" class="fontchange" style="text-align:right;">Balance</th>                                   
                                                <!--<th align="right" width="9%" style="text-align:right;">Days<i class="icon-sort"></i><i class="icon-sort-down"></i> <i class="icon-sort-up"></i></th>-->                                   
                                            </tr>
                                            <?php
                                            mysqli_query($conn, "create temporary table datatmp 
                                                               (SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as ordbydate, 
                                                                DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode`, examcode  
                                                                FROM `prevbill` WHERE `accode` = '$maccode' AND billdate >= '$mfrdate' 
                                                                AND billdate <= '$mtodate' $examcond1 order by billdate, billno) 
                                                                UNION all
                                                                (SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,  billno, $mdate as ordbydate, 
                                                                DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`, `$fintot` as billamt, accode, examcode 
                                                                FROM $mfile WHERE `accode` = '$maccode' AND vouchdt >= '$mfrdate' 
                                                                AND vouchdt <= '$mtodate' $examcond1 order by $mdate, billno)") or die(mysqli_error($conn));

                                            $subtmp1 = mysqli_query($conn, 'select * from datatmp order by ordbydate, billno') 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;
                                                $qexamcode = $murl->examcode;
                                                $qvouch = $murl->vouchno;
                                                $qamt = $murl->billamt;
                                                $qparty = $murl->accode;

                                                $dt1 = date_create($mtodate);
                                                $dt2 = date_create($qbilldate);
                                                $subtdate = $dt1 - $dt2;
                                                $mldays = $subtdate;

                                                $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
                                                                       `vouchno`='$qvouch' AND `type`='$mtype' AND bnkdate <= '$mtodate' $examcond1") or die(mysqli_error($conn));
                                                $gtrn = mysqli_fetch_object($trn);

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

                                                if ($mbalamt > 0) {
                                                    $texam = mysqli_query($conn, "SELECT name FROM `exam` WHERE  `code`= '$qexamcode' ") or die(mysqli_error($conn));
                                                    $fexam = mysqli_fetch_object($texam);
                                                    $qexam = $fexam->name;

                                                    echo "<tr style='height:14px;' class='fontchange'>";
                                                    echo "<td style=\"text-align:left;\" class='fontchange'>$qbilldate</td>";
                                                    echo "<td style=\"text-align:left;\">$qbill</td>";
                                                    echo "<td style=\"text-align:left;\">$qexam</td>";
                                                    echo "<td style=\"text-align:right;\">$qamt</td>";
                                                    echo "<td style=\"text-align:right;\">$msetoff</td>";
                                                    echo '<td style="text-align:right;">' . number_format($mbalamt, 2) . '</td>';
                                                    echo '</tr>';
                                                    $totbillamt = $totbillamt + $qamt;
                                                    $totrecdamt = $totrecdamt + $msetoff;
                                                    $totbalamt = $totbalamt + $mbalamt;

                                                    $grbill = $grbill + $qamt;
                                                    $grrecd = $grrecd + $msetoff;
                                                    $grbal = $grbal + $mbalamt;
                                                }
                                            }
                                            echo "<tr style='height:10px;'>";
                                            echo '<td style="text-align:left;" ></td>';
                                            echo '<td style="text-align:left;" ></td>';
                                            echo '<td style="text-align:right;" ><b>School Total</b></td>';
                                            echo '<td style="text-align:right;" ><b>' . number_format($totbillamt, 2) . '</b></td>';
                                            echo '<td style="text-align:right;" ><b>' . number_format($totrecdamt, 2) . '</b></td>';
                                            echo '<td style="text-align:right;" ><b>' . number_format($totbalamt, 2) . '</b></td>';
                                            echo '</tr>';

                                            echo "<tr style='height:10px;'>";
                                            echo "<td colspan='6' >&nbsp;</td>";
                                            echo '</tr>';

                                            mysqli_query($conn, 'drop table datatmp') or die(mysqli_error($conn));
                                        }
                                    }
                                }
                                if ($dtype == 1) {
                                    ?>
                                    <tr class="mySearchTr">
                                        <td colspan="5" class="text-right" style="color:white"><b>Grand Total</b></td>
                                        <td class="text-right" style="color:white"><b><?php echo $totmbalamt; ?></b></td>
                                        <td><input type="hidden" name="mailcount" value="<?= $mtr; ?>"></td>
                                    </tr>
                                    <?php
                                } else {
                                    ?>
                                    <tr class="mySearchTr">
                                        <td colspan="3" class="text-right" style="color:white"><b>Grand Total</b></td>
                                        <td class="text-right" style="color:white"><b><?php echo number_format($grbill, 2); ?></b></td>
                                        <td class="text-right" style="color:white"><b><?php echo number_format($grrecd, 2); ?></b></td>
                                        <td class="text-right" style="color:white"><b><?php echo number_format($grbal, 2); ?></b></td>
                                    </tr>
                                <?php } ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </form>
        </div>
        <?php
    }


    if (isset($_POST['subpdf'])) {

        echo "<input type='date' class='mfrmdate' value='$mfrdate'>";
        echo "<input type='date' class='mtodate' value='$mtodate'>";
        if (!empty($exam)) {
            $accmain = mysqli_query($conn, "SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
                            a.email, b.examcode FROM `accmast` a, invmain b WHERE 
                            a.`accode` = b.`accode` and a.`grpcode` = '010' AND 
                            b.`vouchdt` >= '$mfrdate' and b.`vouchdt` >= '$mtodate' 
                            $custcond $medcond $acond $sectioncond $examcond 
                            UNION ALL
                            SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
                            a.email, b.examcode FROM `accmast` a, prevbill b WHERE 
                            a.`accode` = b.`accode` and a.`grpcode` = '010' AND 
                            b.`billdate` >= '$mfrdate' and b.`billdate` <= '$mtodate' 
                            $custcond $medcond $acond  $sectioncond $examcond order by custcode") or die(mysqli_error($conn));
        } else {
            $accmain = mysqli_query($conn, "SELECT accode, custcode, name, tel, mobile, email
                            FROM `accmast` WHERE `grpcode` = '010' $custcond1 
                            $medcond1 $acond1  order by custcode") or die(mysqli_error($conn));
        }

        while ($getacm = mysqli_fetch_array($accmain)) {
            $maccode = $getacm['accode'];
            $mcustcode = $getacm['custcode'];
            $macname = $getacm['name'];
            $tel = $getacm['tel'];
            $mobile = $getacm['mobile'];
            $email = $getacm['email'];

            ////////
            ///////
            ?>
            <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
            <script type = 'text/javascript'>

                $(document).ready(function () {
                    var maccode = <?php echo $maccode; ?>,
                            mfrdate = $(".mfrmdate").val(),
                            mtodate = $(".mtodate").val();
                    url = "osbill_pdf.php?accode=" + maccode + "&mfrdate=" + mfrdate + "&mtodate=" + mtodate;
                    window.open(url);
                });
            </script>
            <?php
        }
    }



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

        $mbalamt1 = 0;
        $grbill = $grrecd = $grbal = 0;
        if (!empty($exam)) {
            $acc = mysqli_query($conn, "SELECT a.accode, a.custcode, a.name, a.tel, a.mobile, a.email, b.examcode FROM `accmast` a, invmain b 
WHERE a.`accode` = b.`accode` and a.`grpcode` = '010' AND b.`vouchdt` >= '$mtodate' AND b.`vouchdt` <= '$mtodate'
$custcond $medcond $acond $sectioncond $examcond 
union all 
SELECT a.accode, a.custcode, a.name, a.tel, a.mobile, a.email, b.examcode FROM `accmast` a, invmain b 
WHERE a.`accode` = b.`accode` and a.`grpcode` = '010' AND b.`vouchdt` >= '$mfrdate' AND b.`vouchdt` <= '$mtodate'
$custcond $medcond $acond $sectioncond $examcond order by a.custcode") or die(mysqli_error($conn));
        } else {
            $acc = mysqli_query($conn, "SELECT accode, custcode, name, tel, mobile, email FROM `accmast`  
WHERE `grpcode` = '010' $custcond1 $medcond1 $acond1  order by custcode") or die(mysqli_error($conn));
        }

        while ($getac = mysqli_fetch_array($acc)) {
            $maccode = $getac['accode'];
            $mcustcode = $getac['custcode'];
            $macname = $getac['name'];
            $tel = $getac['tel'];
            $mobile = $getac['mobile'];
            $email = $getac['email'];

            $sub1 = mysqli_query($conn, "SELECT  SUM($fintot) AS amt1 FROM $mfile WHERE `accode` = '$maccode' and `vouchdt` >= '$mfrdate' AND `vouchdt` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

            $sub2 = mysqli_query($conn, "SELECT  SUM(`billamt`) AS amt2 FROM `prevbill` WHERE `accode` = '$maccode' AND `billdate` >= '$mfrdate' AND `billdate` <= '$mtodate' $examcond1 ") or die(mysqli_error($conn));

            $msub1 = mysqli_fetch_array($sub1);
            $msub2 = mysqli_fetch_array($sub2);
            $msamt1 = $msub1['amt1'];

            $msamt2 = $msub2['amt2'];
            $qamt1 = $msamt1 + $msamt2;

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

            $mbalamt1 = $qamt1 - $msetoff1;

            $mainname = str_replace(',', ' ', "$macname");

            if ($mbalamt1 > 0) {
                $contents .= $mainname . ',';
                $contents .= 'Code: ' . "$mcustcode" . ',';
                if ($dtype == 1) {
                    $contents .= $mbalamt1 . "\n";
                }

                $totmbalamt = $totmbalamt + $mbalamt1;

                if ($dtype == 2) {
                    $contents .= $tel . ',';
                    $contents .= $mobile . ',';
                    $contents .= $email . "\n";

                    $contents .= "Date, BillNo, Exam, BillAmount, Recd.Amount, Balance\n";

                    mysqli_query($conn, "create temporary table datatmp 
(SELECT `yearno`,`type`,`vouchno`, `billno`, billdate as ordbydate, 
DATE_FORMAT(`billdate`, '%d-%m-%Y') AS `billdate`, `billamt`, `accode`, examcode  
FROM `prevbill` WHERE `accode` = '$maccode' AND billdate >= '$mfrdate' 
AND billdate <= '$mtodate' $examcond1 order by billdate, billno) 
UNION all
(SELECT '$myear' AS `yearno`, '$mtype' AS `type`, `vouchno`,  billno, $mdate as ordbydate, 
DATE_FORMAT($mdate, '%d-%m-%Y') AS `billdate`, `$fintot` as billamt, accode, examcode 
FROM $mfile WHERE `accode` = '$maccode' AND vouchdt >= '$mfrdate' 
AND vouchdt <= '$mtodate' $examcond1 order by $mdate, billno)") or die(mysqli_error($conn));

                    $subtmp1 = mysqli_query($conn, 'select * from datatmp order by ordbydate, billno') 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;
                        $qexamcode = $murl->examcode;
                        $qvouch = $murl->vouchno;
                        $qamt = $murl->billamt;
                        $qparty = $murl->accode;

                        $dt1 = date_create($mtodate);
                        $dt2 = date_create($qbilldate);
                        $subtdate = $dt1 - $dt2;
                        $mldays = $subtdate;

                        $trn = mysqli_query($conn, "SELECT SUM(setoff) AS setoff FROM `billsett` WHERE  `party`= '$qparty' AND `yearno` ='$qyear' AND 
`vouchno`='$qvouch' AND `type`='$mtype' AND bnkdate <= '$mtodate' $examcond1") or die(mysqli_error($conn));
                        $gtrn = mysqli_fetch_object($trn);

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

                        if ($mbalamt > 0) {
                            $texam = mysqli_query($conn, "SELECT name FROM `exam` WHERE  `code`= '$qexamcode' ") or die(mysqli_error($conn));
                            $fexam = mysqli_fetch_object($texam);
                            $qexam = $fexam->name;

                            $balance = str_replace(',', ' ', $mbalamt);

                            $contents .= $qbilldate . ',';
                            $contents .= $qbill . ',';
                            $contents .= $qexam . ',';
                            $contents .= $qamt . ',';
                            $contents .= $msetoff . ',';
                            $contents .= $mbalamt . "\n";

                            $totbillamt = $totbillamt + $qamt;
                            $totrecdamt = $totrecdamt + $msetoff;
                            $totbalamt = $totbalamt + $mbalamt;

                            $grbill = $grbill + $qamt;
                            $grrecd = $grrecd + $msetoff;
                            $grbal = $grbal + $mbalamt;
                        }
                    }
                    $contents .= '' . ',';
                    $contents .= '' . ',';
                    $contents .= 'School Total' . ',';
                    $contents .= $totbillamt . ',';
                    $contents .= $totrecdamt . ',';
                    $contents .= $totbalamt . "\n";

                    mysqli_query($conn, 'drop table datatmp') or die(mysqli_error($conn));
                }
            }
        }
        if ($dtype == 1) {
            $contents .= '' . ',';
            $contents .= 'Grand Total' . ',';
            $contents .= $totmbalamt . "\n";
        } else {
            $contents .= 'Grand Total' . ',';
            $contents .= number_format($grbill, 2) . ',';
            $contents .= number_format($grrecd, 2) . ',';
            $contents .= number_format($grbal, 2) . "\n";
        }

        $contents = strip_tags($contents);

        if ($_POST['dtype'] == 1) {
            header("Content-Disposition: attachment; filename=$formname" . '_Summary' . date('d-m-Y') . '.csv');
            echo $contents;
        } else {
            header("Content-Disposition: attachment; filename=$formname" . '_Detail' . date('d-m-Y') . '.csv');
            echo $contents;
        }
    }
    ?>        
