<?php
class Sale extends Model
{
/**
* @var string
*/
protected $table = 'sales';
/**
* @param $saleData
* @param $items
*/
public function create($saleData, $items)
{
// Start transaction
$this->db->beginTransaction();
try {
// Calculate totals
$subTotal = 0;
foreach ($items as $item) {
$quantity = intval($item['quantity']);
$unitPrice = floatval($item['unit_price']);
$subTotal += $quantity * $unitPrice;
}
// Add calculated totals
$discountAmount = isset($saleData['discount_amount']) ? floatval($saleData['discount_amount']) : 0;
$taxAmount = isset($saleData['tax_amount']) ? floatval($saleData['tax_amount']) : 0;
$grandTotal = $subTotal - $discountAmount + $taxAmount;
// Generate reference number (format: SALE-YYYYMMDD-XXXX)
$date = date('Ymd');
$count = $this->db->fetchColumn(
"SELECT COUNT(*) FROM {$this->table} WHERE reference_no LIKE ?",
["SALE-$date-%"]
);
$refNumber = sprintf("SALE-%s-%04d", $date, $count + 1);
// Create sale header
$saleId = $this->insert([
'reference_no' => $refNumber,
'customer_id' => $saleData['customer_id'],
'user_id' => $saleData['user_id'],
'total_amount' => $subTotal,
'discount_amount' => $discountAmount,
'tax_amount' => $taxAmount,
'grand_total' => $grandTotal,
'payment_method' => $saleData['payment_method'],
'payment_status' => 'paid', // Default to paid
'notes' => $saleData['notes']
]);
// Create sale items and update inventory
$productModel = new Product();
$inventory = new Inventory();
$saleItemModel = new SaleItem();
foreach ($items as $item) {
$productId = intval($item['product_id']);
$quantity = intval($item['quantity']);
$unitPrice = floatval($item['unit_price']);
$discount = isset($item['discount']) ? floatval($item['discount']) : 0;
$total = ($unitPrice * $quantity) - $discount;
// Get product
$product = $productModel->getById($productId);
if (!$product) {
throw new Exception("Product ID {$productId} not found");
}
// Check stock
if ($product['quantity'] < $quantity) {
throw new Exception("Insufficient stock for product: {$product['name']}");
}
// Create sale item
$saleItemModel->insert([
'sale_id' => $saleId,
'product_id' => $productId,
'quantity' => $quantity,
'unit_price' => $unitPrice,
'discount' => $discount,
'total' => $total
]);
// Update product stock
$newQuantity = $product['quantity'] - $quantity;
$productModel->updateStock(
$productId,
$newQuantity,
"Sale: {$refNumber}",
$saleData['user_id']
);
// Record inventory transaction
$inventory->recordTransaction(
$productId,
'sale',
$quantity,
$saleId,
"Sale: {$refNumber}",
$saleData['user_id']
);
}
$this->db->commit();
return $saleId;
} catch (Exception $e) {
$this->db->rollBack();
throw $e;
}
}
/**
* @param $saleId
*/
public function getDetailsForReceipt($saleId)
{
// Get sale header
$sale = $this->db->fetch(
"SELECT s.*, c.name as customer_name, u.username as cashier_name, u.full_name as cashier_full_name
FROM {$this->table} s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN users u ON s.user_id = u.id
WHERE s.id = ?",
[$saleId]
);
if (!$sale) {
return null;
}
// Get sale items
$items = $this->db->fetchAll(
"SELECT si.*, p.name as product_name, p.sku
FROM sale_items si
JOIN products p ON si.product_id = p.id
WHERE si.sale_id = ?",
[$saleId]
);
// Get store settings
$settingModel = new Setting();
$settings = $settingModel->getSettingsByKeys([
'store_name',
'store_address',
'store_phone',
'receipt_footer'
]);
// Combine data
$sale['items'] = $items;
$sale['store_name'] = $settings['store_name'] ?? 'My POS Store';
$sale['store_address'] = $settings['store_address'] ?? '';
$sale['store_phone'] = $settings['store_phone'] ?? '';
$sale['receipt_footer'] = $settings['receipt_footer'] ?? 'Thank you for shopping with us!';
// Use full name if available, otherwise use username
$sale['cashier_name'] = $sale['cashier_full_name'] ?: $sale['cashier_name'];
unset($sale['cashier_full_name']);
return $sale;
}
/**
* @param $page
* @param $limit
* @param $dateFrom
* @param null $dateTo
* @param null $customerId
* @param null $paymentStatus
* @param null $paymentMethod
* @param null $search
*/
public function getSalesWithPagination($page = 1, $limit = 20, $dateFrom = null, $dateTo = null, $customerId = null, $paymentStatus = null, $paymentMethod = null, $search = null)
{
$conditions = [];
$params = [];
if ($dateFrom) {
$conditions[] = "DATE(s.created_at) >= ?";
$params[] = $dateFrom;
}
if ($dateTo) {
$conditions[] = "DATE(s.created_at) <= ?";
$params[] = $dateTo;
}
if ($customerId) {
$conditions[] = "s.customer_id = ?";
$params[] = $customerId;
}
if ($paymentStatus) {
$conditions[] = "s.payment_status = ?";
$params[] = $paymentStatus;
}
if ($paymentMethod) {
$conditions[] = "s.payment_method = ?";
$params[] = $paymentMethod;
}
if ($search) {
$conditions[] = "(s.reference_no LIKE ? OR c.name LIKE ?)";
$params[] = "%$search%";
$params[] = "%$search%";
}
$whereClause = empty($conditions) ? "" : " WHERE ".implode(' AND ', $conditions);
// Count total records
$countQuery = "
SELECT COUNT(*)
FROM {$this->table} s
LEFT JOIN customers c ON s.customer_id = c.id
$whereClause
";
$totalCount = $this->db->fetchColumn($countQuery, $params);
$offset = ($page - 1) * $limit;
// Get data with pagination
$query = "
SELECT s.*, c.name as customer_name, u.username as user_name,
(SELECT COUNT(*) FROM sale_items WHERE sale_id = s.id) as item_count
FROM {$this->table} s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN users u ON s.user_id = u.id
$whereClause
ORDER BY s.created_at DESC
LIMIT ? OFFSET ?
";
$queryParams = array_merge($params, [$limit, $offset]);
$sales = $this->db->fetchAll($query, $queryParams);
return [
'sales' => $sales,
'pagination' => [
'total' => $totalCount,
'page' => $page,
'limit' => $limit,
'pages' => ceil($totalCount / $limit)
]
];
}
/**
* @param $saleId
*/
public function getDetailedSale($saleId)
{
// Get sale header with related info
$sale = $this->db->fetch(
"SELECT s.*, c.name as customer_name, c.phone as customer_phone, c.email as customer_email,
u.username as user_name, u.full_name as user_full_name
FROM {$this->table} s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN users u ON s.user_id = u.id
WHERE s.id = ?",
[$saleId]
);
if (!$sale) {
return null;
}
// Get sale items with product details
$items = $this->db->fetchAll(
"SELECT si.*, p.name as product_name, p.sku
FROM sale_items si
JOIN products p ON si.product_id = p.id
WHERE si.sale_id = ?",
[$saleId]
);
$sale['items'] = $items;
return $sale;
}
/**
* @param $saleId
* @param $reason
* @param $userId
*/
public function voidSale($saleId, $reason, $userId)
{
// Start transaction
$this->db->beginTransaction();
try {
// Get sale and items
$sale = $this->getDetailedSale($saleId);
if (!$sale) {
throw new Exception('Sale not found');
}
if ($sale['payment_status'] === 'voided') {
throw new Exception('Sale is already voided');
}
// Update sale status
$notes = $sale['notes'].' | Voided: '.$reason;
$this->update($saleId, [
'payment_status' => 'voided',
'notes' => $notes
]);
// Return items to inventory
$productModel = new Product();
$inventory = new Inventory();
foreach ($sale['items'] as $item) {
// Get current product quantity
$product = $productModel->getById($item['product_id']);
if ($product) {
// Update product stock
$newQuantity = $product['quantity'] + $item['quantity'];
$productModel->updateStock(
$item['product_id'],
$newQuantity,
"Sale voided: {$sale['reference_no']} - {$reason}",
$userId
);
// Record inventory transaction
$inventory->recordTransaction(
$item['product_id'],
'return',
$item['quantity'],
$saleId,
"Sale voided: {$reason}",
$userId
);
}
}
$this->db->commit();
return true;
} catch (Exception $e) {
$this->db->rollBack();
throw $e;
}
}
/**
* @param $dateFrom
* @param null $dateTo
* @param null $paymentStatus
* @param null $paymentMethod
* @param null $search
*/
public function getSalesForExport($dateFrom = null, $dateTo = null, $paymentStatus = null, $paymentMethod = null, $search = null)
{
$conditions = [];
$params = [];
if ($dateFrom) {
$conditions[] = "DATE(s.created_at) >= ?";
$params[] = $dateFrom;
}
if ($dateTo) {
$conditions[] = "DATE(s.created_at) <= ?";
$params[] = $dateTo;
}
if ($paymentStatus) {
$conditions[] = "s.payment_status = ?";
$params[] = $paymentStatus;
}
if ($paymentMethod) {
$conditions[] = "s.payment_method = ?";
$params[] = $paymentMethod;
}
if ($search) {
$conditions[] = "(s.reference_no LIKE ? OR c.name LIKE ?)";
$params[] = "%$search%";
$params[] = "%$search%";
}
$whereClause = empty($conditions) ? "" : " WHERE ".implode(' AND ', $conditions);
$query = "
SELECT s.*, c.name as customer_name, u.username as user_name,
(SELECT COUNT(*) FROM sale_items WHERE sale_id = s.id) as item_count
FROM {$this->table} s
LEFT JOIN customers c ON s.customer_id = c.id
LEFT JOIN users u ON s.user_id = u.id
$whereClause
ORDER BY s.created_at DESC
";
return $this->db->fetchAll($query, $params);
}
/**
* @param $customerId
* @return mixed
*/
public function countByCustomerId($customerId)
{
return $this->db->fetchColumn(
"SELECT COUNT(*) FROM {$this->table} WHERE customer_id = ?",
[$customerId]
);
}
}