324 lines
11 KiB
PHP
324 lines
11 KiB
PHP
<?php
|
|
|
|
class InvoicesModel
|
|
{
|
|
protected static $db;
|
|
|
|
public static function setDb($pdo)
|
|
{
|
|
self::$db = $pdo;
|
|
}
|
|
|
|
// ყველა ინვოისის წამოღება
|
|
public static function getAllInvoicesWithClientNames()
|
|
{
|
|
$stmt = self::$db->query("
|
|
SELECT i.*, CONCAT(c.first_name, ' ', c.last_name) AS client_name
|
|
FROM invoices i
|
|
JOIN clients c ON i.client_id = c.id
|
|
ORDER BY i.id DESC
|
|
");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
// ყველა ინვოისის წამოღება
|
|
public static function getAllInvoices()
|
|
{
|
|
$stmt = self::$db->query("SELECT i.*, c.first_name, c.last_name FROM invoices i JOIN clients c ON i.client_id = c.id ORDER BY i.id DESC");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
// ერთი ინვოისის წამოღება დეტალურად
|
|
public static function getInvoiceById($id)
|
|
{
|
|
$stmt = self::$db->prepare("SELECT * FROM invoices WHERE id = ?");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
// ინვოისის დამატება
|
|
public static function createInvoice($data)
|
|
{
|
|
$stmt = self::$db->prepare("INSERT INTO invoices (invoice_number, client_id, description, payment_method, status, total_amount, is_recurring, issue_date, due_date, payment_date, recurring) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
|
|
|
|
$stmt->execute([
|
|
$data['invoice_number'],
|
|
$data['client_id'],
|
|
$data['description'],
|
|
$data['payment_method'],
|
|
$data['status'],
|
|
$data['total_amount'],
|
|
$data['is_recurring'],
|
|
$data['issue_date'],
|
|
$data['due_date'],
|
|
$data['payment_date'],
|
|
$data['recurring']
|
|
]);
|
|
|
|
return self::$db->lastInsertId();
|
|
}
|
|
|
|
// ინვოისის განახლება
|
|
public static function updateInvoice($id, $data)
|
|
{
|
|
$stmt = self::$db->prepare("UPDATE invoices SET
|
|
client_id = ?, invoice_number = ?, description = ?, status = ?,
|
|
total_amount = ?, payment_method = ?, issue_date = ?, due_date = ?, recurring = ?
|
|
WHERE id = ?");
|
|
|
|
return $stmt->execute([
|
|
$data['client_id'],
|
|
$data['invoice_number'],
|
|
$data['description'],
|
|
$data['status'],
|
|
$data['total_amount'],
|
|
$data['payment_method'],
|
|
$data['issue_date'],
|
|
$data['due_date'],
|
|
isset($data['recurring']) ? 1 : 0,
|
|
$id
|
|
]);
|
|
}
|
|
|
|
// ინვოისის წაშლა
|
|
public static function deleteInvoice($id)
|
|
{
|
|
$stmt = self::$db->prepare("DELETE FROM invoices WHERE id = ?");
|
|
return $stmt->execute([$id]);
|
|
}
|
|
|
|
// ინვოისთან დაკავშირებული ნივთების წამოღება
|
|
public static function getInvoiceItems2($invoice_id)
|
|
{
|
|
$stmt = self::$db->prepare("SELECT * FROM invoice_items WHERE invoice_id = ?");
|
|
$stmt->execute([$invoice_id]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
// ინვოისის ნივთების დამატება
|
|
public static function addInvoiceItem($invoice_id, $item)
|
|
{
|
|
$stmt = self::$db->prepare("INSERT INTO invoice_items (invoice_id, product_id, description, amount) VALUES (?, ?, ?, ?)");
|
|
return $stmt->execute([
|
|
$invoice_id,
|
|
$item['product_id'],
|
|
$item['description'],
|
|
$item['amount']
|
|
]);
|
|
}
|
|
|
|
// ინვოისის ნივთების წაშლა
|
|
public static function deleteInvoiceItems($invoice_id)
|
|
{
|
|
$stmt = self::$db->prepare("DELETE FROM invoice_items WHERE invoice_id = ?");
|
|
return $stmt->execute([$invoice_id]);
|
|
}
|
|
|
|
public static function getInvoiceWithClient($id)
|
|
{
|
|
$stmt = self::$db->prepare("
|
|
SELECT i.*, c.first_name, c.last_name, c.email
|
|
FROM invoices i
|
|
JOIN clients c ON c.id = i.client_id
|
|
WHERE i.id = ?
|
|
");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
public static function getInvoiceItems($invoiceId)
|
|
{
|
|
$stmt = self::$db->prepare("
|
|
SELECT ii.*, p.name
|
|
FROM invoice_items ii
|
|
JOIN products p ON p.id = ii.product_id
|
|
WHERE ii.invoice_id = ?
|
|
");
|
|
$stmt->execute([$invoiceId]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public static function generateNextInvoiceNumber(): string
|
|
{
|
|
$currentYear = date('Y');
|
|
$prefix = 'INV-' . $currentYear . '-';
|
|
|
|
$stmt = self::$db->prepare("SELECT invoice_number FROM invoices WHERE invoice_number LIKE ? ORDER BY invoice_number DESC LIMIT 1");
|
|
$stmt->execute([$prefix . '%']);
|
|
$lastInvoice = $stmt->fetchColumn();
|
|
|
|
if ($lastInvoice) {
|
|
$lastNumber = (int) substr($lastInvoice, strrpos($lastInvoice, '-') + 1);
|
|
$newNumber = str_pad($lastNumber + 1, 3, '0', STR_PAD_LEFT);
|
|
} else {
|
|
$newNumber = '001';
|
|
}
|
|
|
|
return $prefix . $newNumber;
|
|
}
|
|
|
|
public static function getClientsList(): array
|
|
{
|
|
$stmt = self::$db->query("SELECT id, first_name, last_name FROM clients ORDER BY first_name");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public static function getProductList(): array
|
|
{
|
|
$stmt = self::$db->query("SELECT id, name, price FROM products ORDER BY name");
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public static function createInvoiceWithItems($data)
|
|
{
|
|
$stmt = self::$db->prepare("INSERT INTO invoices
|
|
(client_id, invoice_number, description, total_amount, payment_method, status, issue_date, due_date, recurring)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
|
|
|
|
$stmt->execute([
|
|
$data['client_id'],
|
|
$data['invoice_number'],
|
|
$data['description'],
|
|
$data['total_amount'],
|
|
$data['payment_method'],
|
|
$data['status'],
|
|
$data['issue_date'],
|
|
$data['due_date'],
|
|
$data['recurring']
|
|
]);
|
|
|
|
$invoice_id = self::$db->lastInsertId();
|
|
|
|
$stmtItem = self::$db->prepare("INSERT INTO invoice_items (invoice_id, product_id, amount, description) VALUES (?, ?, ?, ?)");
|
|
|
|
for ($i = 0; $i < count($data['products']); $i++) {
|
|
$stmtItem->execute([
|
|
$invoice_id,
|
|
$data['products'][$i],
|
|
$data['amounts'][$i],
|
|
$data['descriptions'][$i]
|
|
]);
|
|
}
|
|
|
|
return $invoice_id;
|
|
}
|
|
|
|
public static function generateInvoiceNumber(): string
|
|
{
|
|
$year = date('Y');
|
|
$prefix = 'INV-' . $year . '-';
|
|
|
|
$stmt = self::$db->prepare("SELECT invoice_number FROM invoices WHERE invoice_number LIKE ? ORDER BY invoice_number DESC LIMIT 1");
|
|
$stmt->execute([$prefix . '%']);
|
|
$last = $stmt->fetchColumn();
|
|
|
|
$lastNumber = $last ? (int) substr($last, strrpos($last, '-') + 1) : 0;
|
|
$newNumber = str_pad($lastNumber + 1, 3, '0', STR_PAD_LEFT);
|
|
|
|
return $prefix . $newNumber;
|
|
}
|
|
|
|
|
|
public static function isValidStatus(string $status): bool
|
|
{
|
|
$allowed = ['დრაფტი', 'გადაუხდელი', 'გადასახდელი', 'გადახდილი', 'გაუქმებული'];
|
|
return in_array($status, $allowed);
|
|
}
|
|
|
|
|
|
public static function isDuplicateInvoiceNumber(string $invoice_number): bool
|
|
{
|
|
$stmt = self::$db->prepare("SELECT COUNT(*) FROM invoices WHERE invoice_number = ?");
|
|
$stmt->execute([$invoice_number]);
|
|
return $stmt->fetchColumn() > 0;
|
|
}
|
|
|
|
public static function getInvoicesDueTomorrow()
|
|
{
|
|
$tomorrow = date('Y-m-d', strtotime('+1 day'));
|
|
$stmt = self::$db->prepare("
|
|
SELECT invoices.*, clients.first_name, clients.last_name, clients.email
|
|
FROM invoices
|
|
JOIN clients ON invoices.client_id = clients.id
|
|
WHERE invoices.due_date = ? AND invoices.status IN ('გადაუხდელი', 'გადასახდელი')
|
|
");
|
|
$stmt->execute([$tomorrow]);
|
|
return $stmt->fetchAll();
|
|
}
|
|
|
|
public static function getInvoiceWithItems($id)
|
|
{
|
|
$stmt = self::$db->prepare("
|
|
SELECT invoices.*, clients.email, clients.first_name, clients.last_name
|
|
FROM invoices
|
|
JOIN clients ON invoices.client_id = clients.id
|
|
WHERE invoices.id = ?
|
|
");
|
|
$stmt->execute([$id]);
|
|
$invoice = $stmt->fetch();
|
|
|
|
if (!$invoice) {
|
|
return null;
|
|
}
|
|
|
|
$stmtItems = self::$db->prepare("
|
|
SELECT products.name, ii.description, ii.amount
|
|
FROM invoice_items ii
|
|
JOIN products ON products.id = ii.product_id
|
|
WHERE ii.invoice_id = ?
|
|
");
|
|
$stmtItems->execute([$id]);
|
|
$invoice['items'] = $stmtItems->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return $invoice;
|
|
}
|
|
|
|
|
|
public static function generateInvoicePDF($invoice)
|
|
{
|
|
$html = self::renderInvoiceHTML($invoice);
|
|
|
|
$options = new \Dompdf\Options();
|
|
$options->set('isRemoteEnabled', true);
|
|
$options->set('defaultFont', 'DejaVu Sans');
|
|
|
|
$dompdf = new \Dompdf\Dompdf($options);
|
|
$dompdf->loadHtml($html, 'UTF-8');
|
|
$dompdf->setPaper('A4', 'portrait');
|
|
$dompdf->render();
|
|
|
|
$pdfPath = '/tmp/invoice_' . $invoice['id'] . '.pdf';
|
|
file_put_contents($pdfPath, $dompdf->output());
|
|
|
|
return $pdfPath;
|
|
}
|
|
|
|
public static function renderInvoiceHTML($invoice)
|
|
{
|
|
ob_start();
|
|
include __DIR__ . '/../views/invoices/pdf_template.php'; // თუ გინდა გამოყავი ცალკე template ფაილად
|
|
return ob_get_clean();
|
|
}
|
|
|
|
// მოაქვს პდფ ფაილში კლიენტის ბაზიდან დამატებითი მონაცემები
|
|
public static function getInvoiceWithClientById($id)
|
|
{
|
|
$stmt = self::$db->prepare("
|
|
SELECT
|
|
invoices.*,
|
|
clients.company_name,
|
|
clients.vat_number,
|
|
clients.address1,
|
|
clients.first_name AS client_first_name,
|
|
clients.last_name AS client_last_name
|
|
FROM invoices
|
|
LEFT JOIN clients ON invoices.client_id = clients.id
|
|
WHERE invoices.id = ?
|
|
");
|
|
$stmt->execute([$id]);
|
|
return $stmt->fetch();
|
|
}
|
|
|
|
|
|
}
|