<?php
class ReportService
{
/**
* @var mixed
*/
private $db;
public function __construct()
{
$this->db = Database::getInstance();
}
public function getDashboardStats()
{
// Today's sales total
$todaySales = $this->db->fetchColumn(
"SELECT COALESCE(SUM(grand_total), 0) as total
FROM sales
WHERE DATE(created_at) = CURDATE()
AND payment_status != 'voided'"
);
// Today's orders count
$todayOrders = $this->db->fetchColumn(
"SELECT COUNT(*)
FROM sales
WHERE DATE(created_at) = CURDATE()
AND payment_status != 'voided'"
);
// Low stock count
$lowStockCount = $this->db->fetchColumn(
"SELECT COUNT(*)
FROM products
WHERE quantity <= low_stock_threshold
AND status = 'active'"
);
// Total customers
$totalCustomers = $this->db->fetchColumn(
"SELECT COUNT(*) FROM customers"
);
return [
'today_sales' => floatval($todaySales),
'today_orders' => intval($todayOrders),
'low_stock_count' => intval($lowStockCount),
'total_customers' => intval($totalCustomers)
];
}
/**
* @param $period
*/
public function getSalesChartData($period = 'week')
{
$labels = [];
$salesData = [];
switch ($period) {
case 'week':
// Get sales for the last 7 days
$result = $this->db->fetchAll(
"SELECT
DATE(created_at) as sale_date,
COALESCE(SUM(grand_total), 0) as total
FROM sales
WHERE
created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
AND payment_status != 'voided'
GROUP BY DATE(created_at)
ORDER BY sale_date ASC"
);
// Create an array for the last 7 days
for ($i = 6; $i >= 0; $i--) {
$date = date('Y-m-d', strtotime("-$i days"));
$labels[] = $date;
$salesData[] = 0; // Default to 0
}
// Fill in actual data
foreach ($result as $row) {
$dateIndex = array_search($row['sale_date'], $labels);
if ($dateIndex !== false) {
$salesData[$dateIndex] = floatval($row['total']);
}
}
break;
case 'month':
// Get sales for the current month by day
$result = $this->db->fetchAll(
"SELECT
DATE(created_at) as sale_date,
COALESCE(SUM(grand_total), 0) as total
FROM sales
WHERE
MONTH(created_at) = MONTH(CURDATE())
AND YEAR(created_at) = YEAR(CURDATE())
AND payment_status != 'voided'
GROUP BY DATE(created_at)
ORDER BY sale_date ASC"
);
// Create an array for each day of the current month
$daysInMonth = date('t');
for ($i = 1; $i <= $daysInMonth; $i++) {
$date = date('Y-m-').sprintf('%02d', $i);
$labels[] = $date;
$salesData[] = 0; // Default to 0
}
// Fill in actual data
foreach ($result as $row) {
$dateIndex = array_search($row['sale_date'], $labels);
if ($dateIndex !== false) {
$salesData[$dateIndex] = floatval($row['total']);
}
}
break;
case 'year':
// Get sales for each month of the current year
$result = $this->db->fetchAll(
"SELECT
DATE_FORMAT(created_at, '%Y-%m-01') as sale_month,
COALESCE(SUM(grand_total), 0) as total
FROM sales
WHERE
YEAR(created_at) = YEAR(CURDATE())
AND payment_status != 'voided'
GROUP BY sale_month
ORDER BY sale_month ASC"
);
// Create an array for each month of the year
for ($i = 1; $i <= 12; $i++) {
$month = date('Y-').sprintf('%02d', $i).'-01';
$labels[] = $month;
$salesData[] = 0; // Default to 0
}
// Fill in actual data
foreach ($result as $row) {
$dateIndex = array_search($row['sale_month'], $labels);
if ($dateIndex !== false) {
$salesData[$dateIndex] = floatval($row['total']);
}
}
break;
default:
throw new Exception('Invalid period');
}
return [
'labels' => $labels,
'sales' => $salesData
];
}
/**
* @param $limit
* @return mixed
*/
public function getRecentSales($limit = 10)
{
return $this->db->fetchAll(
"SELECT
s.*,
c.name as customer_name,
(SELECT COUNT(*) FROM sale_items WHERE sale_id = s.id) as item_count
FROM sales s
LEFT JOIN customers c ON s.customer_id = c.id
ORDER BY s.created_at DESC
LIMIT ?",
[$limit]
);
}
/**
* @param $dateFrom
* @param $dateTo
* @param $groupBy
*/
public function getSalesReport($dateFrom, $dateTo, $groupBy = 'day')
{
// Determine SQL grouping based on groupBy parameter
switch ($groupBy) {
case 'day':
$groupFormat = 'DATE(s.created_at)';
$labelFormat = 'DATE(s.created_at)';
break;
case 'month':
$groupFormat = "DATE_FORMAT(s.created_at, '%Y-%m')";
$labelFormat = "DATE_FORMAT(s.created_at, '%Y-%m')";
break;
case 'year':
$groupFormat = 'YEAR(s.created_at)';
$labelFormat = 'YEAR(s.created_at)';
break;
default:
throw new Exception('Invalid grouping');
}
// Get report data
$reportData = $this->db->fetchAll(
"SELECT
{$labelFormat} as label,
COUNT(*) as order_count,
COALESCE(SUM(s.total_amount), 0) as total_amount,
COALESCE(SUM(s.discount_amount), 0) as discount_amount,
COALESCE(SUM(s.tax_amount), 0) as tax_amount,
COALESCE(SUM(s.grand_total), 0) as grand_total
FROM sales s
WHERE
DATE(s.created_at) BETWEEN ? AND ?
AND s.payment_status != 'voided'
GROUP BY {$groupFormat}
ORDER BY {$labelFormat} ASC",
[$dateFrom, $dateTo]
);
// Calculate totals
$totalOrders = 0;
$totalSales = 0;
$totalDiscounts = 0;
$totalTax = 0;
$totalGrand = 0;
foreach ($reportData as $row) {
$totalOrders += $row['order_count'];
$totalSales += $row['total_amount'];
$totalDiscounts += $row['discount_amount'];
$totalTax += $row['tax_amount'];
$totalGrand += $row['grand_total'];
}
return [
'report_data' => $reportData,
'totals' => [
'total_orders' => $totalOrders,
'total_sales' => $totalSales,
'total_discounts' => $totalDiscounts,
'total_tax' => $totalTax,
'total_grand' => $totalGrand
],
'filters' => [
'date_from' => $dateFrom,
'date_to' => $dateTo,
'group_by' => $groupBy
]
];
}
/**
* @param $dateFrom
* @param $dateTo
* @param $categoryId
* @param null $limit
* @return mixed
*/
public function getProductSalesReport($dateFrom, $dateTo, $categoryId = null, $limit = 20)
{
$conditions = [
"DATE(s.created_at) BETWEEN ? AND ?",
"s.payment_status != 'voided'"
];
$params = [$dateFrom, $dateTo];
if ($categoryId) {
$conditions[] = "p.category_id = ?";
$params[] = $categoryId;
}
$whereClause = " WHERE ".implode(' AND ', $conditions);
$limitClause = $limit ? " LIMIT ?" : "";
if ($limit) {
$params[] = $limit;
}
return $this->db->fetchAll(
"SELECT
p.id,
p.sku,
p.name,
c.name as category_name,
COUNT(DISTINCT si.sale_id) as order_count,
SUM(si.quantity) as quantity_sold,
COALESCE(SUM(si.total), 0) as total_sales
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN sale_items si ON p.id = si.product_id
LEFT JOIN sales s ON si.sale_id = s.id
$whereClause
GROUP BY p.id, p.sku, p.name, c.name
ORDER BY total_sales DESC
$limitClause",
$params
);
}
/**
* @param $categoryId
* @param null $stockStatus
*/
public function getInventoryReport($categoryId = null, $stockStatus = null)
{
$conditions = ["p.status = 'active'"];
$params = [];
if ($categoryId) {
$conditions[] = "p.category_id = ?";
$params[] = $categoryId;
}
if ($stockStatus) {
switch ($stockStatus) {
case 'low':
$conditions[] = "p.quantity <= p.low_stock_threshold AND p.quantity > 0";
break;
case 'out':
$conditions[] = "p.quantity <= 0";
break;
}
}
$whereClause = " WHERE ".implode(' AND ', $conditions);
$inventory = $this->db->fetchAll(
"SELECT
p.id,
p.sku,
p.name,
p.quantity,
p.low_stock_threshold,
c.name as category_name,
p.price,
p.cost,
(p.quantity * p.cost) as inventory_value
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
$whereClause
ORDER BY p.name ASC",
$params
);
// Calculate totals
$totalItems = count($inventory);
$totalQuantity = 0;
$totalValue = 0;
$lowStockCount = 0;
$outOfStockCount = 0;
foreach ($inventory as $item) {
$totalQuantity += $item['quantity'];
$totalValue += $item['inventory_value'];
if ($item['quantity'] <= 0) {
$outOfStockCount++;
} elseif ($item['quantity'] <= $item['low_stock_threshold']) {
$lowStockCount++;
}
}
return [
'inventory' => $inventory,
'totals' => [
'total_items' => $totalItems,
'total_quantity' => $totalQuantity,
'total_value' => $totalValue,
'low_stock_count' => $lowStockCount,
'out_of_stock_count' => $outOfStockCount
]
];
}
/**
* @param $dateFrom
* @param $dateTo
* @param $userId
*/
public function getCashierPerformanceReport($dateFrom, $dateTo, $userId = null)
{
$conditions = ["DATE(s.created_at) BETWEEN ? AND ?"];
$params = [$dateFrom, $dateTo];
if ($userId) {
$conditions[] = "u.id = ?";
$params[] = $userId;
}
$whereClause = !empty($conditions) ? " WHERE ".implode(' AND ', $conditions) : "";
$cashiers = $this->db->fetchAll(
"SELECT
u.id, u.username, u.full_name,
COUNT(DISTINCT s.id) as order_count,
(SELECT SUM(si.quantity) FROM sale_items si
JOIN sales s2 ON si.sale_id = s2.id
WHERE s2.user_id = u.id
AND DATE(s2.created_at) BETWEEN ? AND ?
AND s2.payment_status != 'voided') as items_sold,
SUM(CASE WHEN s.payment_status != 'voided' THEN s.grand_total ELSE 0 END) as total_sales,
(SELECT COUNT(*) FROM sales
WHERE user_id = u.id
AND payment_status = 'voided'
AND DATE(created_at) BETWEEN ? AND ?) as cancelled_orders
FROM users u
LEFT JOIN sales s ON u.id = s.user_id AND DATE(s.created_at) BETWEEN ? AND ?
WHERE u.role IN ('admin', 'manager', 'cashier')
GROUP BY u.id, u.username, u.full_name
ORDER BY total_sales DESC",
array_merge([$dateFrom, $dateTo, $dateFrom, $dateTo, $dateFrom, $dateTo], $params)
);
// Ensure proper data types
foreach ($cashiers as &$cashier) {
$cashier['order_count'] = intval($cashier['order_count']);
$cashier['items_sold'] = intval($cashier['items_sold'] ?? 0);
$cashier['total_sales'] = floatval($cashier['total_sales'] ?? 0);
$cashier['cancelled_orders'] = intval($cashier['cancelled_orders']);
}
return [
'cashiers' => $cashiers,
'filters' => [
'date_from' => $dateFrom,
'date_to' => $dateTo,
'user_id' => $userId
]
];
}
/**
* @param $dateFrom
* @param $dateTo
* @param $period
*/
public function getTaxReport($dateFrom, $dateTo, $period = 'daily')
{
// Determine SQL grouping based on period
switch ($period) {
case 'daily':
$groupFormat = 'DATE(created_at)';
$periodFormat = 'DATE(created_at)';
break;
case 'monthly':
$groupFormat = "DATE_FORMAT(created_at, '%Y-%m')";
$periodFormat = "DATE_FORMAT(created_at, '%Y-%m')";
break;
case 'quarterly':
$groupFormat = "CONCAT(YEAR(created_at), '-Q', QUARTER(created_at))";
$periodFormat = "CONCAT(YEAR(created_at), '-Q', QUARTER(created_at))";
break;
default:
throw new Exception('Invalid period');
}
// Query for tax data by period
$taxData = $this->db->fetchAll(
"SELECT
{$periodFormat} as period,
SUM(total_amount - discount_amount) as taxable_sales,
SUM(tax_amount) as tax_collected
FROM sales
WHERE
DATE(created_at) BETWEEN ? AND ?
AND payment_status != 'voided'
GROUP BY {$groupFormat}
ORDER BY MIN(created_at) ASC",
[$dateFrom, $dateTo]
);
// Calculate totals
$totalTaxableSales = 0;
$totalTaxCollected = 0;
foreach ($taxData as &$period) {
$period['taxable_sales'] = floatval($period['taxable_sales']);
$period['tax_collected'] = floatval($period['tax_collected']);
$totalTaxableSales += $period['taxable_sales'];
$totalTaxCollected += $period['tax_collected'];
}
return [
'periods' => $taxData,
'totals' => [
'taxable_sales' => $totalTaxableSales,
'tax_collected' => $totalTaxCollected
],
'filters' => [
'date_from' => $dateFrom,
'date_to' => $dateTo,
'period' => $period
]
];
}
}