<?php
$month = isset($_GET['month']) ? $_GET['month'] : date("Y-m");
$totalCollection = 0;
$totalDiscount = 0;
$totalExpenses = 0;
// Fetch collection data
$collectionQry = $conn->query("SELECT c.*, CONCAT(m.firstname, ' ', COALESCE(m.middlename,''), ' ', m.lastname) as fullname
FROM `collection_list` c
INNER JOIN `member_list` m
ON c.member_id = m.id
WHERE DATE_FORMAT(c.date_collected,'%Y-%m') = '{$month}'
ORDER BY DATE(c.date_collected) DESC, CONCAT(m.firstname, ' ', COALESCE(m.middlename,''), ' ', m.lastname) ASC");
if ($collectionQry->num_rows > 0) {
while($row = $collectionQry->fetch_assoc()) {
$totalCollection += $row['total_amount'];
$totalDiscount += $row['discount_amount'];
}
}
// Fetch expenses data
$expensesQry = $conn->query("SELECT *
FROM `expense_list`
WHERE DATE_FORMAT(expense_date,'%Y-%m') = '{$month}'
ORDER BY DATE(expense_date) DESC");
if ($expensesQry->num_rows > 0) {
while($row = $expensesQry->fetch_assoc()) {
$totalExpenses += $row['total_amount'];
}
}
?>
<div class="content py-3 mt-3">
<div class="card card-outline card-navy shadow rounded-0">
<div class="card-header">
<h5 class="card-title">Monthly Financial Report</h5>
</div>
<div class="card-body">
<div class="container-fluid">
<div class="callout callout-primary shadow rounded-0">
<form action="" id="filter">
<div class="row align-items-end">
<div class="col-lg-3 col-md-4 col-sm-12">
<div class="form-group">
<label for="month" class="control-label">Month</label>
<input type="month" name="month" id="month" value="<?= $month ?>" class="form-control rounded-0" required>
</div>
</div>
<div class="col-lg-3 col-md-4 col-sm-12">
<div class="form-group">
<button class="btn btn-primary btn-flat btn-sm"><i class="fa fa-filter"></i> Filter</button>
<button class="btn btn-light border btn-flat btn-sm" type="button" id="print"><i class="fa fa-print"></i> Print</button>
</div>
</div>
</div>
</form>
<div class="clear-fix mb-3"></div>
<div id="outprint">
<h4>Collection Report</h4>
<table class="table table-bordered table-stripped">
<colgroup>
<col width="5%">
<col width="15%">
<col width="15%">
<col width="15%">
<col width="10%">
<col width="10%">
<col width="15%">
</colgroup>
<thead>
<tr>
<th class="text-center align-middle py-1">#</th>
<th class="text-center align-middle py-1">Payment Date</th>
<th class="text-center align-middle py-1">Collected By</th>
<th class="text-center align-middle py-1">Client Name</th>
<th class="text-center align-middle py-1">Receipt #</th>
<th class="text-center align-middle py-1">Amount</th>
<th class="text-center align-middle py-1">Discount Amount</th>
</tr>
</thead>
<tbody>
<?php
if ($collectionQry->num_rows > 0) {
$i = 1;
while($row = $collectionQry->fetch_assoc()):
?>
<tr>
<td class="text-center align-middle px-2 py-1"><?php echo $i++; ?></td>
<td class="align-middle px-2 py-1"><?php echo date("m-d-y",strtotime($row['date_collected'])) ?></td>
<td class="align-middle px-2 py-1"><?php echo ucwords($row['collected_by']) ?></td>
<td class="align-middle px-2 py-1"><?php echo ucwords($row['fullname']) ?></td>
<td class="align-middle px-2 py-1"><?php echo $row['receipt']; ?></td>
<td class="text-right align-middle px-2 py-1"><?php echo '₱'. format_num($row['total_amount']) ?></td>
<td class="text-right align-middle px-2 py-1"><?php echo '₱'. format_num($row['discount_amount']) ?></td>
</tr>
<?php
endwhile;
} else { ?>
<tr>
<td colspan="7" class="text-center">No collection records found for this month.</td>
</tr>
<?php } ?>
</tbody>
<tfoot>
<tr>
<th class="text-center px-1 py-1 align-middle" colspan="5">Total</th>
<th class="text-right px-1 py-1 align-middle"><?= '₱'. format_num($totalCollection) ?></th>
<th class="text-right px-1 py-1 align-middle"><?= '₱'. format_num($totalDiscount) ?></th>
</tr>
</tfoot>
</table>
<hr>
<h4>Expenses Report</h4>
<table class="table table-bordered table-stripped">
<colgroup>
<col width="5%">
<col width="15%">
<col width="15%">
<col width="25%">
<col width="20%">
<col width="20%">
</colgroup>
<thead>
<tr>
<th class="text-center align-middle py-1">#</th>
<th class="text-center align-middle py-1">Expense Date</th>
<th class="text-center align-middle py-1">Recorded By</th>
<th class="text-center align-middle py-1">Expense Category</th>
<th class="text-center align-middle py-1">Item Description</th>
<th class="text-center align-middle py-1">Amount</th>
</tr>
</thead>
<tbody>
<?php
if ($expensesQry->num_rows > 0) {
$i = 1;
while($row = $expensesQry->fetch_assoc()):
?>
<tr>
<td class="text-center align-middle px-2 py-1"><?php echo $i++; ?></td>
<td class="align-middle px-2 py-1"><?php echo date("m-d-y",strtotime($row['expense_date'])) ?></td>
<td class="align-middle px-2 py-1"><?php echo ucwords($row['collected_by']) ?></td>
<td class="align-middle px-2 py-1"><?php echo ucwords($row['name']) ?></td>
<td class="align-middle px-2 py-1"><?php echo $row['item_name']; ?></td>
<td class="text-right align-middle px-2 py-1"><?php echo '₱'. format_num($row['total_amount']) ?></td>
</tr>
<?php
endwhile;
} else { ?>
<tr>
<td colspan="6" class="text-center">No expense records found for this month.</td>
</tr>
<?php } ?>
</tbody>
<tfoot>
<tr>
<th class="text-center px-1 py-1 align-middle" colspan="5">Total</th>
<th class="text-right px-1 py-1 align-middle"><?= '₱'. format_num($totalExpenses) ?></th>
</tr>
</tfoot>
</table>
<canvas id="financialChart" width="400" height="150"></canvas>
</div>
</div>
</div>
</div>
</div>
</div>
<noscript id="print-header">
<style>
#sys_logo{
width:5em !important;
height:5em !important;
object-fit:scale-down !important;
object-position:center center !important;
}
</style>
<div class="d-flex align-items-center">
<div class="col-auto text-center pl-4">
<img src="<?= validate_image($_settings->info('logo')) ?>" alt=" System Logo" id="sys_logo" class="img-circle border border-dark">
</div>
<div class="col-auto flex-shrink-1 flex-grow-1 px-4">
<h4 class="text-center m-0"><?= $_settings->info('name') ?></h4>
<h3 class="text-center m-0"><b>Financial Report</b></h3>
<h5 class="text-center m-0">For the Month of</h5>
<h5 class="text-center m-0"><?= date("F Y", strtotime($month)) ?></h5>
</div>
</div>
<hr>
</noscript>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
$(function(){
$('#filter').submit(function(e){
e.preventDefault();
location.href = "./?page=financial_report&"+$(this).serialize();
});
$('#print').click(function(){
start_loader();
var head = $('head').clone();
var p = $('#outprint').clone();
var el = $('<div>');
var header = $($('noscript#print-header').html()).clone();
head.find('title').text("Financial Report - Print View");
el.append(head);
el.append(header);
el.append(p);
var nw = window.open("","_blank","width=1000,height=900,top=50,left=75");
nw.document.write(el.html());
nw.document.close();
setTimeout(() => {
nw.print();
setTimeout(() => {
nw.close();
end_loader();
}, 200);
}, 500);
});
// Generate the bar chart
var ctx = document.getElementById('financialChart').getContext('2d');
var financialChart = new Chart(ctx, {
type: 'bar',
data: {
labels: ['Collection', 'Expenses'],
datasets: [{
label: 'Amount in ₱',
data: [<?= $totalCollection ?>, <?= $totalExpenses ?>],
backgroundColor: [
'rgba(75, 192, 192, 0.2)',
'rgba(255, 99, 132, 0.2)'
],
borderColor: [
'rgba(75, 192, 192, 1)',
'rgba(255, 99, 132, 1)'
],
borderWidth: 1
}]
},
options: {
scales: {
y: {
beginAtZero: true
}
}
}
});
});
</script>