<?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];
if (isset($_POST['view'])) {

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


    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="">Medium</th>
                                    <th style="">Section</th>
                                    <th style="text-align: right;">No. Bills</th>
                                    <th style="text-align: right;">Bill Amt.</th>
                                    <th style="text-align: right;">Total Recd.</th>
                                    <th style="text-align: right;">Balance</th>
                                    <th style="text-align: right;">No. Bills</th>
                                    <th style="text-align: right;">Bill Amt.</th>
                                    <th style="text-align: right;">Total Recd.</th>
                                    <th style="text-align: right;">Balance</th>
                                    <th style="text-align: right;">Total Balance</th>
                                    <?php
//                                    if ($dtype == 1) {
//                                        echo '<th>Send Mail</th>';
//                                    }
                                    ?>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $mbalamt1 = $mtr = 0;
                                $grbill = $grrecd = $grbal = 0;
//                                    $acc = mysqli_query($conn, "Select count(*) as nos,* from `invmain` where `examcode`='$exam' group by medmcode, section") or die(mysqli_error($conn));
                                $acc = mysqli_query($conn, "Select a.*,b.section from `medium` a, invmain b where a.`code`=b.medmcode and b.vouchdt <= '$mtodate' $medcond1 $sectioncond1   group by a.code,b.section order by a.code ") or die(mysqli_error($conn));
//                                    $acc = mysqli_query($conn, "SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
//                                                    a.email, b.examcode,b.section 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,b.section 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 group by b.medmcode,b.section order by custcode") or die(mysqli_error($conn));
                                $tname = "";
                                $lsetoff = 0;
                                $nlsetoff = 0;
                                $lbal = 0;
                                $nlbal = 0;
                                $tbal = 0;
                                $tlcount = 0;
                                $tlbill = 0;
                                $tlsetoff = 0;
                                $tlbal = 0;
                                $tnlcount = 0;
                                $tnlbill = 0;
                                $tnlsetoff = 0;
                                $tnlbal = 0;
                                $gbal = 0;
                                while ($getac = mysqli_fetch_array($acc)) {
                                    $medmcode = $getac['code'];
                                    $medmname = $getac['name'];
                                    $sectcode = $getac['section'];
                                    if ($sectcode == "P") {
                                        $sectname = "Primary";
                                    } elseif ($sectcode == "S") {
                                        $sectname = "Secondary";
                                    } else {
                                        $sectname = "Prelim";
                                    }
                                    if ($medmname == $tname) {
                                        $medmname = "";
                                    } else {

                                        $medmname = $medmname;
                                    }
//                                    for local schools
//                                    $getlbill = mysqli_query($conn, "Select count(*) as nos,sum(a.billamt) as tbilamt from `invmain`  a, accmast b
//                                                                where a.`examcode`='$exam' and a.medmcode='$medmcode' and a.section = '$sectcode'
//                                                                and a.accode in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $getlbill = mysqli_query($conn, "Select count(*) as nos,sum(billamt) as tbilamt from `invmain`  
                                                                where `examcode`='$exam' and medmcode='$medmcode' and section = '$sectcode'
                                                                and accode in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $fetlbill = mysqli_fetch_array($getlbill);
                                    $lcount = $fetlbill['nos'];
                                    $lbillamt = $fetlbill['tbilamt'];

//                                    $trn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, accmast b WHERE  a.`party`= b.accode AND 
//                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.localflg = '1' and a.billno in (Select 
//                                                                billno from invmain where section = '$sectcode' and examcode = '$exam'  and medmcode = '$medmcode')") or die(mysqli_error($conn));
                                    $trn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, invmain b WHERE  a.`billno`= b.billno AND 
                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.section = '$sectcode'
                                                                and b.medmcode = '$medmcode' and a.party in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $gtrn1 = mysqli_fetch_object($trn1);
                                    $lsetoff = $gtrn1->setoff;
                                    $lbal = $lbillamt - $lsetoff;


//                                    for outside schools
                                    $getnlbill = mysqli_query($conn, "Select count(*) as nos,sum(a.billamt) as tbilamt from `invmain`  a, accmast b
                                                                where a.`examcode`='$exam' and a.medmcode='$medmcode' and a.section = '$sectcode'
                                                                and a.accode=b.accode and b.localflg =''") or die(mysqli_error($conn));
                                    $fetnlbill = mysqli_fetch_array($getnlbill);
                                    $nlcount = $fetnlbill['nos'];
                                    $nlbillamt = $fetnlbill['tbilamt'];

                                    $ntrn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, invmain b WHERE  a.`billno`= b.billno AND 
                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.section = '$sectcode'
                                                                and b.medmcode = '$medmcode' and a.party in (Select accode from accmast where localflg = '')") or die(mysqli_error($conn));
                                    $ngtrn1 = mysqli_fetch_object($ntrn1);
                                    $nlsetoff = $ngtrn1->setoff;

                                    $nlbal = $nlbillamt - $nlsetoff;

                                    $tbal = $lbal + $nlbal;


                                    echo "<tr>";
                                    echo "<td>$medmname</td>";
                                    echo "<td>$sectname</td>";
                                    echo "<td class='text-right'>$lcount</td>";
                                    echo "<td class='text-right'>$lbillamt</td>";
                                    echo "<td class='text-right'>$lsetoff</td>";
                                    echo "<td class='text-right'>$lbal</td>";
                                    echo "<td class='text-right'>$nlcount</td>";
                                    echo "<td class='text-right'>$nlbillamt</td>";
                                    echo "<td class='text-right'>$nlsetoff</td>";
                                    echo "<td class='text-right'>$nlbal</td>";
                                    echo "<td class='text-right'>$tbal</td>";
                                    echo "</tr>";
                                    $tname = $medmname;

                                    $tlcount = $tlcount + $lcount;
                                    $tlbill = $tlbill + $lbillamt;
                                    $tlsetoff = $tlsetoff + $lsetoff;
                                    $tlbal = $tlbal + $lbal;
                                    $tnlcount = $tnlcount + $nlcount;
                                    $tnlbill = $tnlbill + $nlbillamt;
                                    $tnlsetoff = $tnlsetoff + $nlsetoff;
                                    $tnlbal = $tnlbal + $nlbal;
                                    $gbal = $gbal + $tbal;
                                }
                                ?>
                                <tr class="mySearchTr">
                                    <td class="text-right" style="color:white" colspan="2"><b>Grand Total</b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tlcount; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tlbill; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tlsetoff; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tlbal; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tnlcount; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tnlbill; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tnlsetoff; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $tnlbal; ?></b></td>
                                    <td class="text-right" style="color:white"><b><?php echo $gbal; ?></b></td>

                                </tr>
                            </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'])) {
       

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


    

    $mtdt = date('d-m-Y', strtotime($mtodate));
   
    
                if ($dtype == 1 && $para == 'R') {
                   
                }
                
                $contents .= "Medium, Section, No. Bill, Bill Amt., Total Recd, Balance, No.Bills, Bill Amt, Total Recd, Balance, Total Balance \n";
                                   
//                                    if ($dtype == 1) {
//                                        echo '<th>Send Mail</th>';
//                                    }
                                    
                                
                                $mbalamt1 = $mtr = 0;
                                $grbill = $grrecd = $grbal = 0;
//                                    $acc = mysqli_query($conn, "Select count(*) as nos,* from `invmain` where `examcode`='$exam' group by medmcode, section") or die(mysqli_error($conn));
                                $acc = mysqli_query($conn, "Select a.*,b.section from `medium` a, invmain b where a.`code`=b.medmcode and b.vouchdt <= '$mtodate' $medcond1 $sectioncond1   group by a.code,b.section order by a.code ") or die(mysqli_error($conn));
//                                    $acc = mysqli_query($conn, "SELECT a.accode, a.custcode, a.name, a.tel, a.mobile,
//                                                    a.email, b.examcode,b.section 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,b.section 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 group by b.medmcode,b.section order by custcode") or die(mysqli_error($conn));
                                $tname = "";
                                $lsetoff = 0;
                                $nlsetoff = 0;
                                $lbal = 0;
                                $nlbal = 0;
                                $tbal = 0;
                                $tlcount = 0;
                                $tlbill = 0;
                                $tlsetoff = 0;
                                $tlbal = 0;
                                $tnlcount = 0;
                                $tnlbill = 0;
                                $tnlsetoff = 0;
                                $tnlbal = 0;
                                $gbal = 0;
                                while ($getac = mysqli_fetch_array($acc)) {
                                    $medmcode = $getac['code'];
                                    $medmname = $getac['name'];
                                    $sectcode = $getac['section'];
                                    if ($sectcode == "P") {
                                        $sectname = "Primary";
                                    } elseif ($sectcode == "S") {
                                        $sectname = "Secondary";
                                    } else {
                                        $sectname = "Prelim";
                                    }
                                    if ($medmname == $tname) {
                                        $medmname = "";
                                    } else {

                                        $medmname = $medmname;
                                    }
//                                    for local schools
//                                    $getlbill = mysqli_query($conn, "Select count(*) as nos,sum(a.billamt) as tbilamt from `invmain`  a, accmast b
//                                                                where a.`examcode`='$exam' and a.medmcode='$medmcode' and a.section = '$sectcode'
//                                                                and a.accode in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $getlbill = mysqli_query($conn, "Select count(*) as nos,sum(billamt) as tbilamt from `invmain`  
                                                                where `examcode`='$exam' and medmcode='$medmcode' and section = '$sectcode'
                                                                and accode in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $fetlbill = mysqli_fetch_array($getlbill);
                                    $lcount = $fetlbill['nos'];
                                    $lbillamt = $fetlbill['tbilamt'];

//                                    $trn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, accmast b WHERE  a.`party`= b.accode AND 
//                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.localflg = '1' and a.billno in (Select 
//                                                                billno from invmain where section = '$sectcode' and examcode = '$exam'  and medmcode = '$medmcode')") or die(mysqli_error($conn));
                                    $trn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, invmain b WHERE  a.`billno`= b.billno AND 
                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.section = '$sectcode'
                                                                and b.medmcode = '$medmcode' and a.party in (Select accode from accmast where localflg = '1')") or die(mysqli_error($conn));
                                    $gtrn1 = mysqli_fetch_object($trn1);
                                    $lsetoff = $gtrn1->setoff;
                                    $lbal = $lbillamt - $lsetoff;


//                                    for outside schools
                                    $getnlbill = mysqli_query($conn, "Select count(*) as nos,sum(a.billamt) as tbilamt from `invmain`  a, accmast b
                                                                where a.`examcode`='$exam' and a.medmcode='$medmcode' and a.section = '$sectcode'
                                                                and a.accode=b.accode and b.localflg =''") or die(mysqli_error($conn));
                                    $fetnlbill = mysqli_fetch_array($getnlbill);
                                    $nlcount = $fetnlbill['nos'];
                                    $nlbillamt = $fetnlbill['tbilamt'];

                                    $ntrn1 = mysqli_query($conn, "SELECT SUM(a.setoff) AS setoff FROM `billsett` a, invmain b WHERE  a.`billno`= b.billno AND 
                                                                a.`bnkdate` <= '$mtodate' and a.examcode='$exam' and b.section = '$sectcode'
                                                                and b.medmcode = '$medmcode' and a.party in (Select accode from accmast where localflg = '')") or die(mysqli_error($conn));
                                    $ngtrn1 = mysqli_fetch_object($ntrn1);
                                    $nlsetoff = $ngtrn1->setoff;

                                    $nlbal = $nlbillamt - $nlsetoff;

                                    $tbal = $lbal + $nlbal;

                                        $contents .= $medmname . ",";
                                        $contents .= $sectname . ",";
                                        $contents .= $lcount . ",";
                                        $contents .= $lbillamt . ",";
                                        $contents .= $lsetoff . ",";
                                        $contents .= $lbal . ",";
                                        $contents .= $nlcount . ",";
                                        $contents .= $nlbillamt . ",";
                                        $contents .= $nlsetoff . ",";
                                        $contents .= $nlbal . ",";
                                        $contents .= $tbal . "\n";
            
                                   
                                    $tname = $medmname;

                                    $tlcount = $tlcount + $lcount;
                                    $tlbill = $tlbill + $lbillamt;
                                    $tlsetoff = $tlsetoff + $lsetoff;
                                    $tlbal = $tlbal + $lbal;
                                    $tnlcount = $tnlcount + $nlcount;
                                    $tnlbill = $tnlbill + $nlbillamt;
                                    $tnlsetoff = $tnlsetoff + $nlsetoff;
                                    $tnlbal = $tnlbal + $nlbal;
                                    $gbal = $gbal + $tbal;
                                }
                                        $contents .= '' . ',';
                                       $contents .= 'Grand Total' . ',';
                                       $contents .= $tlcount . ",";
                                       $contents .= $tlbill . ",";
                                       $contents .= $tlsetoff . ",";
                                       $contents .= $tlbal . ",";
                                       $contents .= $tnlcount . ",";
                                       $contents .= $tnlbill . ",";
                                       $contents .= $tnlsetoff . ",";
                                       $contents .= $tnlbal . ",";
                                       $contents .= $gbal . ",";
                               
                 
        
        
        

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