513 lines
21 KiB
PHP
513 lines
21 KiB
PHP
<?php
|
|
$title = 'reports';
|
|
require_once __DIR__ . '/includes/header.php';
|
|
|
|
// View check is already handled by header.php global check.
|
|
// If someone needs specific action permission on reports, we could check has_permission('add') etc.
|
|
|
|
$session_branch_id = $_SESSION['branch_id'] ?? 'all';
|
|
$from_date = $_GET['from_date'] ?? date('Y-m-01');
|
|
$to_date = $_GET['to_date'] ?? date('Y-m-t');
|
|
// Default to session branch if no explicit GET filter
|
|
$branch_filter = $_GET['branch_id'] ?? $session_branch_id;
|
|
$user_filter = $_GET['user_id'] ?? 'all';
|
|
|
|
// Base queries
|
|
$where = "WHERE DATE(o.created_at) BETWEEN ? AND ?";
|
|
$params = [$from_date, $to_date];
|
|
|
|
if ($branch_filter !== 'all') {
|
|
$where .= " AND o.branch_id = ?";
|
|
$params[] = $branch_filter;
|
|
}
|
|
|
|
if ($user_filter !== 'all') {
|
|
$where .= " AND o.user_id = ?";
|
|
$params[] = $user_filter;
|
|
}
|
|
|
|
// Summary Stats
|
|
$stmt = db()->prepare("SELECT
|
|
SUM(total_price) as total_revenue,
|
|
COUNT(*) as total_orders,
|
|
AVG(total_price) as avg_order_value
|
|
FROM orders o $where");
|
|
$stmt->execute($params);
|
|
$summary = $stmt->fetch();
|
|
|
|
// Revenue by Branch
|
|
$stmt = db()->prepare("SELECT
|
|
b.name_en, b.name_ar, SUM(o.total_price) as revenue
|
|
FROM orders o
|
|
JOIN branches b ON o.branch_id = b.id
|
|
$where
|
|
GROUP BY o.branch_id");
|
|
$stmt->execute($params);
|
|
$revenue_by_branch = $stmt->fetchAll();
|
|
|
|
// Revenue by Cashier
|
|
$stmt = db()->prepare("SELECT
|
|
u.full_name_en, u.full_name_ar, SUM(o.total_price) as revenue
|
|
FROM orders o
|
|
JOIN users u ON o.user_id = u.id
|
|
$where
|
|
GROUP BY o.user_id");
|
|
$stmt->execute($params);
|
|
$revenue_by_user = $stmt->fetchAll();
|
|
|
|
// Orders by Status
|
|
$stmt = db()->prepare("SELECT
|
|
status, COUNT(*) as count
|
|
FROM orders o
|
|
$where
|
|
GROUP BY status");
|
|
$stmt->execute($params);
|
|
$orders_by_status = $stmt->fetchAll();
|
|
|
|
// Revenue by Payment Method
|
|
$stmt = db()->prepare("SELECT
|
|
p.payment_method, SUM(p.amount) as revenue
|
|
FROM payments p
|
|
JOIN orders o ON p.order_id = o.id
|
|
$where
|
|
GROUP BY p.payment_method");
|
|
$stmt->execute($params);
|
|
$revenue_by_method = $stmt->fetchAll();
|
|
|
|
// Top Items
|
|
$stmt = db()->prepare("SELECT
|
|
i.name_en, i.name_ar, SUM(oi.quantity) as total_qty, SUM(oi.subtotal) as total_revenue
|
|
FROM order_items oi
|
|
JOIN orders o ON oi.order_id = o.id
|
|
JOIN items i ON oi.item_id = i.id
|
|
$where
|
|
GROUP BY oi.item_id
|
|
ORDER BY total_qty DESC LIMIT 10");
|
|
$stmt->execute($params);
|
|
$top_items = $stmt->fetchAll();
|
|
|
|
// Top Services
|
|
$stmt = db()->prepare("SELECT
|
|
s.name_en, s.name_ar, SUM(oi.quantity) as total_qty, SUM(oi.subtotal) as total_revenue
|
|
FROM order_items oi
|
|
JOIN orders o ON oi.order_id = o.id
|
|
JOIN services s ON oi.service_id = s.id
|
|
$where
|
|
GROUP BY oi.service_id
|
|
ORDER BY total_qty DESC LIMIT 10");
|
|
$stmt->execute($params);
|
|
$top_services = $stmt->fetchAll();
|
|
|
|
// Fetch branches and users for filters
|
|
$branches = db()->query("SELECT id, name_en, name_ar FROM branches")->fetchAll();
|
|
$users = db()->query("SELECT id, full_name_en, full_name_ar FROM users")->fetchAll();
|
|
|
|
// Filter display names
|
|
$branch_name = __('all_branches');
|
|
if ($branch_filter !== 'all') {
|
|
foreach($branches as $b) {
|
|
if ($b['id'] == $branch_filter) {
|
|
$branch_name = is_arabic() ? $b['name_ar'] : $b['name_en'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
$user_name = __('all');
|
|
if ($user_filter !== 'all') {
|
|
foreach($users as $u) {
|
|
if ($u['id'] == $user_filter) {
|
|
$user_name = is_arabic() ? $u['full_name_ar'] : $u['full_name_en'];
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Fallback values if database is empty
|
|
$display_company_name = ($lang === 'ar' ? ($company_info['name_ar'] ?: $company_info['name_en']) : $company_info['name_en']) ?: 'Laundry Brand';
|
|
$display_address = ($lang === 'ar' ? ($company_info['address_ar'] ?: $company_info['address_en']) : $company_info['address_en']);
|
|
$display_phone = $company_info['phone'];
|
|
$display_email = $company_info['email'];
|
|
$display_vat = $company_info['vat_no'] ?: $company_info['vat_number'];
|
|
$display_ctr = $company_info['ctr_no'];
|
|
|
|
?>
|
|
|
|
<div class="d-flex justify-content-between align-items-center mb-4 no-print">
|
|
<h4 class="fw-bold mb-0"><?= __('reports') ?></h4>
|
|
<div class="d-flex gap-2">
|
|
<button type="button" class="btn btn-outline-primary shadow-sm" style="border-radius: 12px;" data-bs-toggle="modal" data-bs-target="#customerStatementModal">
|
|
<i class="bi bi-person-lines-fill me-2"></i> <?= __('customer_statement') ?>
|
|
</button>
|
|
<button onclick="window.print()" class="btn btn-primary shadow-sm" style="border-radius: 12px;">
|
|
<i class="bi bi-printer me-2"></i> <?= __('print_report') ?>
|
|
</button>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Customer Statement Search Modal -->
|
|
<div class="modal fade no-print" id="customerStatementModal" tabindex="-1">
|
|
<div class="modal-dialog modal-dialog-centered">
|
|
<div class="modal-content border-0 shadow-lg" style="border-radius: 20px;">
|
|
<div class="modal-header border-0 pb-0">
|
|
<h5 class="modal-title fw-bold"><?= __('customer_statement') ?></h5>
|
|
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
|
|
</div>
|
|
<div class="modal-body p-4">
|
|
<form action="customer_statement.php" method="GET">
|
|
<div class="mb-3">
|
|
<label class="form-label small fw-bold"><?= __('search_customer') ?? 'Search Customer' ?></label>
|
|
<div class="input-group">
|
|
<span class="input-group-text bg-white border-end-0" style="border-radius: 12px 0 0 12px;"><i class="bi bi-search"></i></span>
|
|
<input type="text" id="customerSearch" class="form-control border-start-0" placeholder="<?= __('type_name_or_phone') ?? 'Type name or phone...' ?>" style="border-radius: 0 12px 12px 0;">
|
|
</div>
|
|
<div id="customerList" class="list-group mt-2 overflow-auto" style="max-height: 200px;"></div>
|
|
<input type="hidden" name="id" id="selectedCustomerId" required>
|
|
</div>
|
|
<div id="selectedCustomerInfo" class="alert alert-light border d-none mb-3" style="border-radius: 12px;">
|
|
<div class="fw-bold" id="selectedCustomerName"></div>
|
|
<div class="small text-muted" id="selectedCustomerPhone"></div>
|
|
</div>
|
|
<button type="submit" id="generateStatementBtn" class="btn btn-primary w-100 py-3 fw-bold shadow-sm" style="border-radius: 15px;" disabled>
|
|
<?= __('generate_statement') ?? 'Generate Statement' ?>
|
|
</button>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
document.getElementById('customerSearch').addEventListener('input', function() {
|
|
const query = this.value;
|
|
if (query.length < 2) {
|
|
document.getElementById('customerList').innerHTML = '';
|
|
return;
|
|
}
|
|
|
|
fetch('api/search_customers.php?query=' + encodeURIComponent(query))
|
|
.then(response => response.json())
|
|
.then(data => {
|
|
const list = document.getElementById('customerList');
|
|
list.innerHTML = '';
|
|
if (data.length === 0) {
|
|
list.innerHTML = '<div class="list-group-item small text-muted">No customers found</div>';
|
|
return;
|
|
}
|
|
data.forEach(c => {
|
|
const item = document.createElement('button');
|
|
item.className = 'list-group-item list-group-item-action small';
|
|
item.type = 'button';
|
|
item.innerHTML = `<strong>${c.name_en}</strong> (${c.phone})`;
|
|
item.onclick = () => {
|
|
document.getElementById('selectedCustomerId').value = c.id;
|
|
document.getElementById('selectedCustomerName').textContent = c.name_en;
|
|
document.getElementById('selectedCustomerPhone').textContent = c.phone;
|
|
document.getElementById('selectedCustomerInfo').classList.remove('d-none');
|
|
document.getElementById('generateStatementBtn').disabled = false;
|
|
list.innerHTML = '';
|
|
document.getElementById('customerSearch').value = '';
|
|
};
|
|
list.appendChild(item);
|
|
});
|
|
});
|
|
});
|
|
</script>
|
|
|
|
<!-- Print Header -->
|
|
<div class="d-none d-print-block mb-5 border-bottom pb-4">
|
|
<div class="row align-items-center">
|
|
<div class="col-7">
|
|
<?php if (!empty($company_info['logo'])): ?>
|
|
<img src="<?= $company_info['logo'] ?>" alt="Logo" style="max-height: 80px;" class="mb-3">
|
|
<?php else: ?>
|
|
<div class="mb-3 d-flex align-items-center text-primary">
|
|
<i class="bi bi-tsunami fs-1 me-2"></i>
|
|
<span class="fs-4 fw-bold">Laundry Management</span>
|
|
</div>
|
|
<?php endif; ?>
|
|
<h2 class="fw-bold mb-1"><?= htmlspecialchars($display_company_name) ?></h2>
|
|
<div class="text-muted small">
|
|
<?php if ($display_address): ?>
|
|
<?= htmlspecialchars($display_address) ?><br>
|
|
<?php endif; ?>
|
|
|
|
<?php if ($display_phone): ?>
|
|
<?= __('phone') ?>: <?= htmlspecialchars($display_phone) ?>
|
|
<?php endif; ?>
|
|
|
|
<?php if ($display_email): ?>
|
|
<?= $display_phone ? ' | ' : '' ?><?= __('email') ?>: <?= htmlspecialchars($display_email) ?><br>
|
|
<?php elseif($display_phone): ?>
|
|
<br>
|
|
<?php endif; ?>
|
|
|
|
<?php if ($display_vat): ?>
|
|
<?= __('vat_no') ?>: <?= htmlspecialchars($display_vat) ?>
|
|
<?php endif; ?>
|
|
|
|
<?php if ($display_ctr): ?>
|
|
<?= $display_vat ? ' | ' : '' ?><?= __('ctr_no') ?>: <?= htmlspecialchars($display_ctr) ?>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
<div class="col-5 text-end">
|
|
<h1 class="fw-bold text-uppercase mb-2" style="color: #0d6efd;"><?= __('reports') ?></h1>
|
|
<div class="mt-3 small">
|
|
<p class="mb-0"><strong><?= __('date_range') ?>:</strong> <?= $from_date ?> - <?= $to_date ?></p>
|
|
<p class="mb-0"><strong><?= __('outlet') ?>:</strong> <?= $branch_name ?></p>
|
|
<p class="mb-0"><strong><?= __('cashier') ?>:</strong> <?= $user_name ?></p>
|
|
<p class="mb-0 text-muted mt-1"><?= __('print_date') ?? 'Print Date' ?>: <?= date('d/m/Y H:i') ?></p>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="card p-4 mb-4 no-print border-0 shadow-sm" style="border-radius: 20px;">
|
|
<form method="GET" class="row g-3 align-items-end">
|
|
<div class="col-md-2">
|
|
<label class="form-label small fw-bold"><?= __('from_date') ?></label>
|
|
<input type="date" name="from_date" class="form-control" value="<?= $from_date ?>" style="border-radius: 12px;">
|
|
</div>
|
|
<div class="col-md-2">
|
|
<label class="form-label small fw-bold"><?= __('to_date') ?></label>
|
|
<input type="date" name="to_date" class="form-control" value="<?= $to_date ?>" style="border-radius: 12px;">
|
|
</div>
|
|
<?php if ($_SESSION['role'] === 'super_admin'): ?>
|
|
<div class="col-md-3">
|
|
<label class="form-label small fw-bold"><?= __('outlet') ?></label>
|
|
<select name="branch_id" class="form-select" style="border-radius: 12px;">
|
|
<option value="all"><?= __('all_branches') ?></option>
|
|
<?php foreach($branches as $b): ?>
|
|
<option value="<?= $b['id'] ?>" <?= $branch_filter == $b['id'] ? 'selected' : '' ?>>
|
|
<?= is_arabic() ? $b['name_ar'] : $b['name_en'] ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<?php endif; ?>
|
|
<div class="col-md-3">
|
|
<label class="form-label small fw-bold"><?= __('cashier') ?></label>
|
|
<select name="user_id" class="form-select" style="border-radius: 12px;">
|
|
<option value="all"><?= __('all') ?></option>
|
|
<?php foreach($users as $u): ?>
|
|
<option value="<?= $u['id'] ?>" <?= $user_filter == $u['id'] ? 'selected' : '' ?>>
|
|
<?= is_arabic() ? $u['full_name_ar'] : $u['full_name_en'] ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<button type="submit" class="btn btn-primary w-100 shadow-sm fw-bold" style="border-radius: 12px;">
|
|
<i class="bi bi-filter me-2"></i> <?= __('generate_report') ?>
|
|
</button>
|
|
</div>
|
|
</form>
|
|
</div>
|
|
|
|
<div class="row g-4 mb-4">
|
|
<div class="col-md-4">
|
|
<div class="card p-4 text-center border-0 shadow-sm border-start border-primary border-5" style="border-radius: 15px;">
|
|
<div class="text-muted small mb-1"><?= __('total_revenue') ?></div>
|
|
<div class="fs-3 fw-bold text-primary"><?= format_amount($summary['total_revenue'] ?? 0) ?></div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-4">
|
|
<div class="card p-4 text-center border-0 shadow-sm border-start border-success border-5" style="border-radius: 15px;">
|
|
<div class="text-muted small mb-1"><?= __('total_orders') ?></div>
|
|
<div class="fs-3 fw-bold text-success"><?= $summary['total_orders'] ?? 0 ?></div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-4">
|
|
<div class="card p-4 text-center border-0 shadow-sm border-start border-info border-5" style="border-radius: 15px;">
|
|
<div class="text-muted small mb-1"><?= __('average_order_value') ?></div>
|
|
<div class="fs-3 fw-bold text-info"><?= format_amount($summary['avg_order_value'] ?? 0) ?></div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-4 mb-4">
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('revenue_by_outlet') ?></h6>
|
|
<div style="height: 300px; position: relative;">
|
|
<canvas id="revenueByBranchChart"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('orders_by_status') ?></h6>
|
|
<div style="height: 300px; position: relative;">
|
|
<canvas id="ordersByStatusChart"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-4 mb-4">
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('revenue_by_cashier') ?></h6>
|
|
<div style="height: 300px; position: relative;">
|
|
<canvas id="revenueByCashierChart"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('revenue_by_payment_method') ?></h6>
|
|
<div style="height: 300px; position: relative;">
|
|
<canvas id="revenueByMethodChart"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-4 mb-4">
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('top_items') ?></h6>
|
|
<div class="table-responsive">
|
|
<table class="table table-sm align-middle">
|
|
<thead>
|
|
<tr>
|
|
<th><?= __('item') ?></th>
|
|
<th class="text-center"><?= __('qty') ?></th>
|
|
<th class="text-end"><?= __('revenue') ?></th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach($top_items as $item): ?>
|
|
<tr>
|
|
<td><?= is_arabic() ? $item['name_ar'] : $item['name_en'] ?></td>
|
|
<td class="text-center"><?= $item['total_qty'] ?></td>
|
|
<td class="text-end"><?= format_amount($item['total_revenue']) ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-6">
|
|
<div class="card p-4 h-100 border-0 shadow-sm" style="border-radius: 20px;">
|
|
<h6 class="fw-bold mb-4"><?= __('top_services') ?></h6>
|
|
<div class="table-responsive">
|
|
<table class="table table-sm align-middle">
|
|
<thead>
|
|
<tr>
|
|
<th><?= __('service') ?></th>
|
|
<th class="text-center"><?= __('qty') ?></th>
|
|
<th class="text-end"><?= __('revenue') ?></th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach($top_services as $service): ?>
|
|
<tr>
|
|
<td><?= is_arabic() ? $service['name_ar'] : $service['name_en'] ?></td>
|
|
<td class="text-center"><?= $service['total_qty'] ?></td>
|
|
<td class="text-end"><?= format_amount($service['total_revenue']) ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
|
|
<script>
|
|
// Set default chart options
|
|
Chart.defaults.font.size = 12;
|
|
Chart.defaults.responsive = true;
|
|
Chart.defaults.maintainAspectRatio = false;
|
|
|
|
// Revenue by Branch Chart
|
|
new Chart(document.getElementById('revenueByBranchChart'), {
|
|
type: 'bar',
|
|
data: {
|
|
labels: <?= json_encode(array_map(function($b) use ($lang) {
|
|
return $lang === 'ar' ? $b['name_ar'] : $b['name_en'];
|
|
}, $revenue_by_branch), JSON_UNESCAPED_UNICODE) ?>,
|
|
datasets: [{
|
|
label: '<?= __('revenue') ?>',
|
|
data: <?= json_encode(array_column($revenue_by_branch, 'revenue')) ?>,
|
|
backgroundColor: 'rgba(54, 162, 235, 0.5)',
|
|
borderColor: 'rgba(54, 162, 235, 1)',
|
|
borderWidth: 1
|
|
}]
|
|
},
|
|
options: {
|
|
scales: {
|
|
y: { beginAtZero: true }
|
|
}
|
|
}
|
|
});
|
|
|
|
// Orders by Status Chart
|
|
new Chart(document.getElementById('ordersByStatusChart'), {
|
|
type: 'doughnut',
|
|
data: {
|
|
labels: <?= json_encode(array_map(function($s) {
|
|
return __($s['status']);
|
|
}, $orders_by_status), JSON_UNESCAPED_UNICODE) ?>,
|
|
datasets: [{
|
|
data: <?= json_encode(array_column($orders_by_status, 'count')) ?>,
|
|
backgroundColor: [
|
|
'#6c757d', '#0d6efd', '#198754', '#212529', '#dc3545'
|
|
]
|
|
}]
|
|
},
|
|
options: {
|
|
plugins: {
|
|
legend: { position: 'bottom' }
|
|
}
|
|
}
|
|
});
|
|
|
|
// Revenue by Cashier Chart
|
|
new Chart(document.getElementById('revenueByCashierChart'), {
|
|
type: 'bar',
|
|
data: {
|
|
labels: <?= json_encode(array_map(function($u) use ($lang) {
|
|
return $lang === 'ar' ? $u['full_name_ar'] : $u['full_name_en'];
|
|
}, $revenue_by_user), JSON_UNESCAPED_UNICODE) ?>,
|
|
datasets: [{
|
|
label: '<?= __('revenue') ?>',
|
|
data: <?= json_encode(array_column($revenue_by_user, 'revenue')) ?>,
|
|
backgroundColor: 'rgba(75, 192, 192, 0.5)',
|
|
borderColor: 'rgba(75, 192, 192, 1)',
|
|
borderWidth: 1
|
|
}]
|
|
},
|
|
options: {
|
|
scales: {
|
|
y: { beginAtZero: true }
|
|
}
|
|
}
|
|
});
|
|
|
|
// Revenue by Method Chart
|
|
new Chart(document.getElementById('revenueByMethodChart'), {
|
|
type: 'pie',
|
|
data: {
|
|
labels: <?= json_encode(array_map(function($m) {
|
|
return __($m['payment_method']);
|
|
}, $revenue_by_method), JSON_UNESCAPED_UNICODE) ?>,
|
|
datasets: [{
|
|
data: <?= json_encode(array_column($revenue_by_method, 'revenue')) ?>,
|
|
backgroundColor: [
|
|
'#ffc107', '#0dcaf0', '#6610f2'
|
|
]
|
|
}]
|
|
},
|
|
options: {
|
|
plugins: {
|
|
legend: { position: 'bottom' }
|
|
}
|
|
}
|
|
});
|
|
</script>
|
|
|
|
<?php require_once __DIR__ . '/includes/footer.php'; ?>
|