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

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

$para = $_GET['para'];

if ($para == 'B') {
    $maindb = 'bankrecp';
    $formname = 'Payment Received Register - Bank';
    $chqretcond = ' and chqrtn != 1 ';
    $source = 'BR';
} else {
    $maindb = 'cashrecp';
    $formname = 'Payment Received Register - Cash ';
    $chqretcond = '';
    $source = 'CR';
}
$zroot = $_SESSION["zdbroot"];

$root = $_SESSION['zdir'];
$file = 'paymrecd_fr.php';

    $centre = $_POST["centcode"];
    $school = $_POST["name"];

    $centcond = "";
    $schlcond = "";
    if (!empty($centre)) {
        $centcond = " and party in (SELECT accode from `accmast` where centcode = '$centre')";
    }

    if (!empty($school)) {
        foreach ($school as $cucheck2) {
            $ccheck2[$ccount2++] .= "OR `party` = '" . $cucheck2 . "' ";
        }
        foreach ($ccheck2 as $g => $cusvalues2) {
            $cuscheck2 .= $cusvalues2;
        }
        $schlcond = " AND (`party` = '' " . $cuscheck2 . " )";
    }
    if (empty($_POST["frmdt"])) {
        browse("From Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }
    if (empty($_POST["todt"])) {
        browse("To Date Cannot Be Empty");
        header("refresh:1; saleitemgrp.php");
    }

    $mfrmdate = trim($_POST["frmdt"]);
    $mtodate = trim($_POST["todt"]);

    $mfdt = date("d-m-Y", strtotime($mfrmdate));
    $mtdt = date("d-m-Y", strtotime($mtodate));


    $getpmain = mysqli_query($conn, "SELECT * from `$maindb` WHERE `vouchdt` >= '$mfrmdate' AND `vouchdt` <= '$mtodate' $chqretcond  $schlcond $centcond ") or die(mysqli_error($conn));
    $get = mysqli_num_rows($getpmain);

    if (!empty($centre)) {
        $centcode = mysqli_query($conn, "SELECT `centcode`,`name` FROM `center` WHERE `centcode` = '$centre' ")or die(mysqli_error($conn));
        $cent = mysqli_fetch_array($centcode);
        $centre1 = $cent['name'];
    }
if (isset($_POST["view"])) {

    include("includes/linkshead.php");
    ?>
    <style type="text/css">
        #tabital{
            font-style: italic;
        }
        #font1{
            font-style: italic;
        }
        #boldfont{
            font-weight: bold; 
        }
        th{
            background: #6082BF;
        }

    </style>
    </head>
    <body>
        <?php
        $comp = mysqli_query($conn, "SELECT * FROM $zroot.`company`") or die(mysqli_error($conn));
        $zcomp = mysqli_fetch_assoc($comp);
        ?>
        <div class="container">
            <div class="row">
                <?php
                include("includes/head_rep.php");
                if ($dexam['name'] != '') {
                    ?>
                    <div class="col-lg-4">
                        <label class="label-control marg">Specific Exam - </label>
                        <label class="label-control marg"><?php Echo $dexam['name']; ?></label>
                    </div>
                <?php } ?>
            </div>
            <div class="row">
                <div class="col-lg-12 pad">
                    <? if (!empty($centre)) {
                        ?>
                        <div class="col-lg-4">
                            <label class="label-control marg">Specific Center - </label>
                            <label class="label-control marg"><? echo $centre1 ?></label> 

                        </div>
                    <? } ?>
                </div>
                <div class="col-lg-12 pad">
                    <table style="background: white;width: 100%" class="table table-condensed table-hover table-bordered">
                        <thead class="mySearchTr">
                            <tr>
                                <th style="width:5%">Sr</th>
                                <th style="width:30%">School</th>
                                <th style="width:15%">Exam</th>
                                <th style="width:8%">Receipt No.</th>
                                <th style="width:13%" class="text-right">Previous Year Amount</th>
                                <th style="width:13%" class="text-right">Current Year Amount</th>
                                <th style="width:10%" class="text-right">Amount</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $ctr = 0;
                            $totamount = 0;
                            $totprevamt = 0;
                            $totcurramt = 0;
                            while ($pmain = mysqli_fetch_array($getpmain)) {
                                $ecode = $pmain['examcode'];

                                $gexam = mysqli_query($conn, "SELECT `name` FROM `exam` WHERE `code` = '$ecode' ")or die(mysqli_error($conn));
                                $dexam = mysqli_fetch_array($gexam);

                                $gschool = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE `accode` = '$pmain[party]' ")or die(mysqli_error($conn));
                                $sschool = mysqli_fetch_array($gschool);

                                $getprev = mysqli_query($conn, "SELECT yearno, bnkvouch, source, setoff as amount from billsett where bnkvouch = '$pmain[vouchno]' 
                                                        and source = '$source'")or die(mysqli_error($conn));

                                $curramt = 0;
                                $prevamt = 0;

                                while ($fprev = mysqli_fetch_array($getprev)) {

                                    if ($fprev['yearno'] == $myyear) {
                                        $curramt = $curramt + $fprev['amount'];
                                    } else {
                                        $prevamt = $prevamt + $fprev['amount'];
                                    }
                                }


                                $ctr++;
                                Echo "<tr id='boldfont'>";
                                Echo "<td>$ctr</td>";
                                Echo "<td>$sschool[name]</td>";
                                Echo "<td>$dexam[name]</td>";
                                Echo "<td>$pmain[recpno]</td>";
                                Echo "<td class='text-right'>$prevamt</td>";
                                Echo "<td class='text-right'>$curramt</td>";
                                Echo "<td class='text-right'>$pmain[amount]</td>";
                                Echo "</tr>";

                                $totamount = $totamount + $pmain['amount'];
                                $totprevamt = $totprevamt + $prevamt;
                                $totcurramt = $totcurramt + $curramt;
                            }
                            Echo "<tr>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='text-right'><b>Total</b></td>";
                            Echo "<td class='numeric text-right' style='color:red'><b>" . number_format($totprevamt, 2) . "</b></td>";
                            Echo "<td class='numeric text-right' style='color:red'><b>" . number_format($totcurramt, 2) . "</b></td>";
                            Echo "<td class='numeric text-right' style='color:red'><b>" . number_format($totamount, 2) . "</b></td>";
                            Echo "</tr>";

                            Echo "<tr>";
                            Echo "<td></td>";
                            Echo "<td colspan='2'></td>";
                            Echo "<td class='text-center' style='color:blue'><b>Summary</b></td>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "</tr>";
                            Echo "<tr>";
                            Echo "<td></td>";
                            Echo "<td></td>";
                            Echo "<td class='' colspan='2' style='color:blue'><b>Exam</b></td>";
                            Echo "<td class='text-right' style='color:blue'>Previous Amount</td>";
                            Echo "<td class='text-right' style='color:blue'>Current Amount</td>";
                            Echo "<td class='text-right' style='color:blue'>Total Amount</td>";
                            Echo "</tr>";

                            $getsumm = mysqli_query($conn, "SELECT  examcode, sum(amount) as amt from `$maindb` WHERE `vouchdt` >= '$mfrmdate' AND 
                                                    `vouchdt` <= '$mtodate' $chqretcond  $schlcond $centcond group by examcode ") or die(mysqli_error($conn));

                            $ctr = 1;
                            while ($psumm = mysqli_fetch_array($getsumm)) {
                                $examcode = $psumm['examcode'];
                                $eamt = $psumm['amt'];

                                $getexam = mysqli_query($conn, "SELECT `code`,`name` FROM `exam` WHERE `code` = '$examcode'")or die(mysqli_error($conn));
                                $fetexam = mysqli_fetch_array($getexam);
                                $ename = $fetexam['name'];

                                $getvouch = mysqli_query($conn, "SELECT a.vouchno, b.bnkvouch, b.yearno from $maindb a, billsett b
                                                         where a.vouchno = b.bnkvouch and a.examcode = '$examcode' and 
                                                         b.yearno = '$myyear' and  a.`vouchdt` >= '$mfrmdate' AND 
                                                         a.`vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));

                                $camt = 0;
                                $curr = 0;
                                while ($fvouch = mysqli_fetch_array($getvouch)) {

                                    $vouchcond = "and bnkvouch = '$fvouch[vouchno]' ";
                                    $yearcond = "and yearno = '$myyear' ";
                                    
                                $getcurramt = mysqli_query($conn, "SELECT SUM(setoff) as curramt from billsett where bnkvouch != '' $vouchcond $yearcond")or die(mysqli_error($conn));
                                $fcurramt = mysqli_fetch_array($getcurramt);
                                $camt = $fcurramt['curramt'];

                                $curr = $curr + $camt;
                                
                                }
                                
                                $pamt = $eamt - $curr ;

                                Echo "<tr id='boldfont'>";
                                Echo "<td style='color:blue'>$ctr</td>";
                                Echo "<td></td>";
                                Echo "<td style='color:blue' colspan='2'>$ename</td>";
                                Echo "<td class='text-right'>$pamt</td>";
                                Echo "<td class='text-right'>$curr</td>";
                                Echo "<td class='text-right' style='color:blue'>$eamt</td>";
                                Echo "</tr>";
                                $ctr++;
                            }
                            ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <?php include("includes/linksfoot.php");
        ?>
    </body>
    </html>
    <?php
}

if (isset($_POST["excel"])) {
    $contents .="Sr,School,Exam,Receipt No.,Previous Year Amount,Current Year Amount,Amount\n";
    ?>
    
        <?php
        $comp = mysqli_query($conn, "SELECT * FROM $zroot.`company`") or die(mysqli_error($conn));
        $zcomp = mysqli_fetch_assoc($comp);
        ?>
                

                            <?php
                            $ctr = 0;
                            $totamount = 0;
                            $totprevamt = 0;
                            $totcurramt = 0;
                            while ($pmain = mysqli_fetch_array($getpmain)) {
                                $ecode = $pmain['examcode'];

                                $gexam = mysqli_query($conn, "SELECT `name` FROM `exam` WHERE `code` = '$ecode' ")or die(mysqli_error($conn));
                                $dexam = mysqli_fetch_array($gexam);

                                $gschool = mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE `accode` = '$pmain[party]' ")or die(mysqli_error($conn));
                                $sschool = mysqli_fetch_array($gschool);

                                $getprev = mysqli_query($conn, "SELECT yearno, bnkvouch, source, setoff as amount from billsett where bnkvouch = '$pmain[vouchno]' 
                                                        and source = '$source'")or die(mysqli_error($conn));

                                $curramt = 0;
                                $prevamt = 0;

                                while ($fprev = mysqli_fetch_array($getprev)) {

                                    if ($fprev['yearno'] == $myyear) {
                                        $curramt = $curramt + $fprev['amount'];
                                    } else {
                                        $prevamt = $prevamt + $fprev['amount'];
                                    }
                                }


                                $ctr++;
                                $contents .= "$ctr" . ",";
                                $contents .= "$sschool[name]" . ",";
                                $contents .= "$dexam[name]" . ",";
                                $contents .= "$pmain[recpno]" . ",";
                                $contents .= "$prevamt" . ",";
                                $contents .= "$curramt" . ",";
                                $contents .= "$pmain[amount]" . "\n";
                                
                                $totamount = $totamount + $pmain['amount'];
                                $totprevamt = $totprevamt + $prevamt;
                                $totcurramt = $totcurramt + $curramt;
                            }
                                $contents .= "" . ",";
                                $contents .= "" . ",";
                                $contents .= "" . ",";
                                $contents .= "Total" . ",";
                                $contents .= "$totprevamt" . ",";
                                $contents .= "$totcurramt" . ",";
                                $contents .= "$totamount" . "\n";
                                
                                $contents .= "" .",";
                                $contents .= "" .",";
                                $contents .= "Summary" .",";
                                $contents .= "" .",";
                                $contents .= "" .",";
                                $contents .= "" .",";
                                $contents .= "" .",";
                                $contents .= "Exam" .",";
                                $contents .= "Previous Amount" .",";
                                $contents .= "Current Amount" .",";
                                $contents .= "Total Amount" ."\n";

                            $getsumm = mysqli_query($conn, "SELECT  examcode, sum(amount) as amt from `$maindb` WHERE `vouchdt` >= '$mfrmdate' AND 
                                                    `vouchdt` <= '$mtodate' $chqretcond  $schlcond $centcond group by examcode ") or die(mysqli_error($conn));

                            $ctr = 1;
                            while ($psumm = mysqli_fetch_array($getsumm)) {
                                $examcode = $psumm['examcode'];
                                $eamt = $psumm['amt'];

                                $getexam = mysqli_query($conn, "SELECT `code`,`name` FROM `exam` WHERE `code` = '$examcode'")or die(mysqli_error($conn));
                                $fetexam = mysqli_fetch_array($getexam);
                                $ename = $fetexam['name'];

                                $getvouch = mysqli_query($conn, "SELECT a.vouchno, b.bnkvouch, b.yearno from $maindb a, billsett b
                                                         where a.vouchno = b.bnkvouch and a.examcode = '$examcode' and 
                                                         b.yearno = '$myyear' and  a.`vouchdt` >= '$mfrmdate' AND 
                                                         a.`vouchdt` <= '$mtodate' ")or die(mysqli_error($conn));

                                $camt = 0;
                                $curr = 0;
                                while ($fvouch = mysqli_fetch_array($getvouch)) {

                                    $vouchcond = "and bnkvouch = '$fvouch[vouchno]' ";
                                    $yearcond = "and yearno = '$myyear' ";
                                    
                                $getcurramt = mysqli_query($conn, "SELECT SUM(setoff) as curramt from billsett where bnkvouch != '' $vouchcond $yearcond")or die(mysqli_error($conn));
                                $fcurramt = mysqli_fetch_array($getcurramt);
                                $camt = $fcurramt['curramt'];

                                $curr = $curr + $camt;
                                
                                }
                                
                                $pamt = $eamt - $curr ;
                                
                                $contensts .= "$ctr" . ",";
                                $contensts .= "" . ",";
                                $contensts .= "$ename" . ",";
                                $contensts .= "$pamt" . ",";
                                $contensts .= "$curr" . ",";
                                $contensts .= "$eamt" . "\n";

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