552 lines
22 KiB
PHP
552 lines
22 KiB
PHP
<?php
|
|
require_once __DIR__ . '/DocumentNumberManager.php';
|
|
require_once __DIR__ . '/../classes_ac/PostingWindowGuard.php';
|
|
require_once __DIR__ . '/../classes_ac/GlManager.php';
|
|
|
|
class ReceiptManager {
|
|
|
|
private PDO $pdo;
|
|
private int $company_id;
|
|
|
|
public function __construct(PDO $pdo, int $company_id) {
|
|
$this->pdo = $pdo;
|
|
$this->company_id = $company_id;
|
|
}
|
|
|
|
private function generateReceiptNumber(array $data = []): string
|
|
{
|
|
return (new DocumentNumberManager($this->pdo, $this->company_id))
|
|
->resolveNumber($data, 'receipt', 'td_receipt', 'receipt_number');
|
|
}
|
|
|
|
private function assertPostingWindow(?string $date, string $context): void
|
|
{
|
|
global $pdo1;
|
|
|
|
if (!isset($pdo1) || !($pdo1 instanceof PDO)) {
|
|
throw new Exception("Posting-window validation is unavailable.");
|
|
}
|
|
|
|
$guard = new PostingWindowGuard($pdo1, $this->company_id);
|
|
$guard->assertOpenDate($date ?: date('Y-m-d'), $context);
|
|
}
|
|
|
|
private function allocatedAmount(int $invoice_id): float
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT COALESCE(SUM(a.amount), 0)
|
|
FROM td_receipt_item a
|
|
JOIN td_receipt r
|
|
ON r.company_id = a.company_id
|
|
AND r.id = a.receipt_id
|
|
WHERE a.company_id = :company_id
|
|
AND a.invoice_id = :invoice_id
|
|
AND r.status = 1"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':invoice_id' => $invoice_id,
|
|
]);
|
|
return (float)$sth->fetchColumn();
|
|
}
|
|
|
|
private function refreshInvoiceSettlementStatus(int $invoice_id): void
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT grand_total, status FROM td_invoice
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $invoice_id]);
|
|
$row = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$row || (int)$row['status'] === 4 || (int)$row['status'] === 0) return;
|
|
|
|
$target = abs((float)$row['grand_total']);
|
|
$paid = $this->allocatedAmount($invoice_id);
|
|
$new_status = ($target > 0 && $paid + 0.0001 >= $target) ? 2 : 1;
|
|
|
|
$this->pdo->prepare(
|
|
"UPDATE td_invoice SET status = :status
|
|
WHERE company_id = :company_id AND id = :id"
|
|
)->execute([
|
|
':status' => $new_status,
|
|
':company_id' => $this->company_id,
|
|
':id' => $invoice_id,
|
|
]);
|
|
}
|
|
|
|
private function refreshReceiptBillingStatus(int $billing_id): void
|
|
{
|
|
if ($billing_id <= 0) return;
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT b.amount, b.status,
|
|
COALESCE(SUM(CASE WHEN r.status = 1 THEN r.amount ELSE 0 END), 0) AS received_amount
|
|
FROM td_receipt_billing b
|
|
LEFT JOIN td_receipt r
|
|
ON r.company_id = b.company_id
|
|
AND r.receipt_billing_id = b.id
|
|
WHERE b.company_id = :company_id
|
|
AND b.id = :id
|
|
GROUP BY b.id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $billing_id]);
|
|
$billing = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$billing || (int)$billing['status'] === 4) return;
|
|
|
|
$amount = (float)$billing['amount'];
|
|
$received = (float)$billing['received_amount'];
|
|
if ($received + 0.0001 >= $amount) {
|
|
$new_status = 2;
|
|
} elseif ($received > 0.0001) {
|
|
$new_status = 3;
|
|
} else {
|
|
$new_status = 1;
|
|
}
|
|
$this->pdo->prepare(
|
|
"UPDATE td_receipt_billing
|
|
SET status = :status
|
|
WHERE company_id = :company_id
|
|
AND id = :id"
|
|
)->execute([
|
|
':status' => $new_status,
|
|
':company_id' => $this->company_id,
|
|
':id' => $billing_id,
|
|
]);
|
|
}
|
|
|
|
public function getOpenDocuments(int $contact_id = 0): array
|
|
{
|
|
$where = [
|
|
"i.company_id = :company_id",
|
|
"i.doc_type IN ('invoice', 'credit_note')",
|
|
"i.status IN (1, 2)"
|
|
];
|
|
$params = [':company_id' => $this->company_id];
|
|
|
|
if ($contact_id > 0) {
|
|
$where[] = 'i.contact_id = :contact_id';
|
|
$params[':contact_id'] = $contact_id;
|
|
}
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT i.*,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
o.order_number,
|
|
COALESCE(SUM(CASE WHEN r.status = 1 THEN a.amount ELSE 0 END), 0) AS allocated_amount
|
|
FROM td_invoice i
|
|
LEFT JOIN md_contact c
|
|
ON c.company_id = i.company_id
|
|
AND c.id = i.contact_id
|
|
LEFT JOIN td_order o
|
|
ON o.company_id = i.company_id
|
|
AND o.id = i.order_id
|
|
LEFT JOIN td_receipt_item a
|
|
ON a.company_id = i.company_id
|
|
AND a.invoice_id = i.id
|
|
LEFT JOIN td_receipt r
|
|
ON r.company_id = a.company_id
|
|
AND r.id = a.receipt_id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
GROUP BY i.id
|
|
ORDER BY i.issued_date ASC, i.id ASC"
|
|
);
|
|
$sth->execute($params);
|
|
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
$open = [];
|
|
foreach ($rows as $row) {
|
|
$total = abs((float)$row['grand_total']);
|
|
$allocated = (float)$row['allocated_amount'];
|
|
$balance = max(0, $total - $allocated);
|
|
if ($balance <= 0.0001) continue;
|
|
$row['document_total'] = $total;
|
|
$row['balance_amount'] = $balance;
|
|
$open[] = $row;
|
|
}
|
|
|
|
return $open;
|
|
}
|
|
|
|
public function getReceiptList(): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT r.*,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
COALESCE(b.billing_number, '') AS billing_number
|
|
FROM td_receipt r
|
|
LEFT JOIN md_contact c
|
|
ON c.company_id = r.company_id
|
|
AND c.id = r.contact_id
|
|
LEFT JOIN td_receipt_billing b
|
|
ON b.company_id = r.company_id
|
|
AND b.id = r.receipt_billing_id
|
|
WHERE r.company_id = :company_id
|
|
ORDER BY r.id DESC"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getReceiptById(int $receipt_id): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT r.*,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
COALESCE(b.billing_number, '') AS billing_number
|
|
FROM td_receipt r
|
|
LEFT JOIN md_contact c
|
|
ON c.company_id = r.company_id
|
|
AND c.id = r.contact_id
|
|
LEFT JOIN td_receipt_billing b
|
|
ON b.company_id = r.company_id
|
|
AND b.id = r.receipt_billing_id
|
|
WHERE r.company_id = :company_id
|
|
AND r.id = :id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':id' => $receipt_id,
|
|
]);
|
|
$receipt = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$receipt) throw new Exception("Receipt not found.");
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT a.*,
|
|
i.invoice_number,
|
|
i.order_id,
|
|
i.grand_total,
|
|
i.status AS invoice_status,
|
|
o.order_number
|
|
FROM td_receipt_item a
|
|
JOIN td_invoice i
|
|
ON i.company_id = a.company_id
|
|
AND i.id = a.invoice_id
|
|
LEFT JOIN td_order o
|
|
ON o.company_id = i.company_id
|
|
AND o.id = i.order_id
|
|
WHERE a.company_id = :company_id
|
|
AND a.receipt_id = :receipt_id
|
|
ORDER BY a.id ASC"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':receipt_id' => $receipt_id,
|
|
]);
|
|
|
|
$receipt['allocations'] = $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
return $receipt;
|
|
}
|
|
|
|
public function voidReceipt(int $receipt_id, array $logging): void
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT id, receipt_billing_id, receipt_date, status, `log`
|
|
FROM td_receipt
|
|
WHERE company_id = :company_id
|
|
AND id = :id
|
|
FOR UPDATE"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':id' => $receipt_id,
|
|
]);
|
|
$receipt = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$receipt) throw new Exception("Receipt not found.");
|
|
if ((int)$receipt['status'] === 4) throw new Exception("Receipt is already void.");
|
|
$this->assertPostingWindow($receipt['receipt_date'] ?: date('Y-m-d'), 'Receipt');
|
|
|
|
$log = json_decode($receipt['log'] ?: '[]', true);
|
|
if (!is_array($log)) $log = [];
|
|
$log[] = array_merge($logging, ['action' => 'void_receipt']);
|
|
|
|
$this->pdo->prepare(
|
|
"UPDATE td_receipt
|
|
SET status = 4, `log` = :log
|
|
WHERE company_id = :company_id
|
|
AND id = :id"
|
|
)->execute([
|
|
':log' => json_encode($log),
|
|
':company_id' => $this->company_id,
|
|
':id' => $receipt_id,
|
|
]);
|
|
|
|
global $pdo1;
|
|
$guard = new PostingWindowGuard($pdo1, $this->company_id);
|
|
$gl = new GlManager($this->pdo, $this->company_id, $guard);
|
|
$gl->delete('receipt', $receipt_id);
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT invoice_id
|
|
FROM td_receipt_item
|
|
WHERE company_id = :company_id
|
|
AND receipt_id = :receipt_id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':receipt_id' => $receipt_id,
|
|
]);
|
|
foreach ($sth->fetchAll(PDO::FETCH_COLUMN) as $invoice_id) {
|
|
$this->refreshInvoiceSettlementStatus((int)$invoice_id);
|
|
}
|
|
$this->refreshReceiptBillingStatus((int)$receipt['receipt_billing_id']);
|
|
}
|
|
|
|
public function createReceipt(array $data, array $logging): int
|
|
{
|
|
$billing_id = (int)($data['receipt_billing_id'] ?? $data['billing_id'] ?? 0);
|
|
$receipt_date = $data['receipt_date'] ?? date('Y-m-d');
|
|
$this->assertPostingWindow($receipt_date, 'Receipt');
|
|
$payment_method = trim((string)($data['payment_method'] ?? ''));
|
|
$notes = trim((string)($data['notes'] ?? ''));
|
|
$cash_amount = round((float)($data['amount'] ?? 0), 4);
|
|
$allocations = $data['allocations'] ?? [];
|
|
|
|
if (is_string($allocations)) {
|
|
$allocations = json_decode($allocations, true) ?: [];
|
|
}
|
|
|
|
if ($billing_id <= 0) throw new Exception("Receipt billing is required.");
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT b.*,
|
|
COALESCE(SUM(CASE WHEN r.status = 1 THEN r.amount ELSE 0 END), 0) AS received_amount
|
|
FROM td_receipt_billing b
|
|
LEFT JOIN td_receipt r
|
|
ON r.company_id = b.company_id
|
|
AND r.receipt_billing_id = b.id
|
|
WHERE b.company_id = :company_id
|
|
AND b.id = :id
|
|
GROUP BY b.id
|
|
FOR UPDATE"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $billing_id]);
|
|
$billing = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$billing) throw new Exception("Receipt billing not found.");
|
|
if ((int)$billing['status'] === 4) throw new Exception("Cannot receive against a void receipt billing.");
|
|
|
|
$contact_id = (int)$billing['contact_id'];
|
|
$billing_balance = round((float)$billing['amount'] - (float)$billing['received_amount'], 4);
|
|
if ($billing_balance <= 0.0001) throw new Exception("Receipt billing is already settled.");
|
|
if ($cash_amount - $billing_balance > 0.0001) {
|
|
throw new Exception("Receipt amount exceeds receipt billing balance.");
|
|
}
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT ba.invoice_id, ba.doc_type, COALESCE(i.department_id, 0) AS department_id, ba.amount,
|
|
COALESCE(SUM(CASE WHEN r.status = 1 THEN ra.amount ELSE 0 END), 0) AS received_line_amount
|
|
FROM td_receipt_billing_item ba
|
|
JOIN td_invoice i
|
|
ON i.company_id = ba.company_id
|
|
AND i.id = ba.invoice_id
|
|
LEFT JOIN td_receipt_item ra
|
|
ON ra.company_id = ba.company_id
|
|
AND ra.invoice_id = ba.invoice_id
|
|
LEFT JOIN td_receipt r
|
|
ON r.company_id = ra.company_id
|
|
AND r.id = ra.receipt_id
|
|
AND r.receipt_billing_id = ba.billing_id
|
|
WHERE ba.company_id = :company_id
|
|
AND ba.billing_id = :billing_id
|
|
GROUP BY ba.id
|
|
ORDER BY ba.id ASC"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':billing_id' => $billing_id]);
|
|
$billing_lines = $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
if (!$billing_lines) throw new Exception("Receipt billing has no documents.");
|
|
|
|
$validated = [];
|
|
$billing_line_map = [];
|
|
foreach ($billing_lines as $line) {
|
|
$billing_line_map[(int)$line['invoice_id']] = [
|
|
'invoice_id' => (int)$line['invoice_id'],
|
|
'doc_type' => $line['doc_type'],
|
|
'department_id' => (int)($line['department_id'] ?? 0),
|
|
'remaining' => max(0, (float)$line['amount'] - (float)$line['received_line_amount']),
|
|
];
|
|
}
|
|
|
|
if ($allocations) {
|
|
$invoice_total = 0.0;
|
|
$credit_total = 0.0;
|
|
|
|
foreach ($allocations as $line) {
|
|
$invoice_id = (int)($line['invoice_id'] ?? 0);
|
|
$amount = abs((float)($line['amount'] ?? 0));
|
|
if ($invoice_id <= 0 || $amount <= 0) continue;
|
|
if (!isset($billing_line_map[$invoice_id])) {
|
|
throw new Exception("Receipt allocation document is not part of this billing.");
|
|
}
|
|
|
|
$billing_line = $billing_line_map[$invoice_id];
|
|
if ($amount - $billing_line['remaining'] > 0.0001) {
|
|
throw new Exception("Receipt allocation exceeds billing line balance.");
|
|
}
|
|
|
|
if ($billing_line['doc_type'] === 'credit_note') $credit_total += $amount;
|
|
else $invoice_total += $amount;
|
|
|
|
$validated[] = [
|
|
'invoice_id' => $invoice_id,
|
|
'doc_type' => $billing_line['doc_type'],
|
|
'department_id' => (int)($line['department_id'] ?? $billing_line['department_id'] ?? 0),
|
|
'amount' => round($amount, 4),
|
|
];
|
|
}
|
|
|
|
$cash_amount = round($invoice_total - $credit_total, 4);
|
|
if ($cash_amount <= 0) throw new Exception("Cash receipt amount must be greater than zero.");
|
|
if ($cash_amount - $billing_balance > 0.0001) {
|
|
throw new Exception("Receipt amount exceeds receipt billing balance.");
|
|
}
|
|
} else {
|
|
if ($cash_amount <= 0) throw new Exception("Receipt amount is required.");
|
|
|
|
$ratio = $cash_amount / max($billing_balance, 0.0001);
|
|
foreach ($billing_lines as $line) {
|
|
$remaining_line_amount = max(0, (float)$line['amount'] - (float)$line['received_line_amount']);
|
|
if ($remaining_line_amount <= 0.0001) continue;
|
|
|
|
$line_amount = round($remaining_line_amount * $ratio, 4);
|
|
if ($line_amount <= 0) continue;
|
|
if ($line_amount - $remaining_line_amount > 0.0001) $line_amount = $remaining_line_amount;
|
|
|
|
$validated[] = [
|
|
'invoice_id' => (int)$line['invoice_id'],
|
|
'doc_type' => $line['doc_type'],
|
|
'department_id' => (int)($line['department_id'] ?? 0),
|
|
'amount' => $line_amount,
|
|
];
|
|
}
|
|
}
|
|
|
|
if (!$validated) throw new Exception("Receipt billing has no remaining document balances.");
|
|
|
|
$formula_id = isset($data['formula_id']) && (int)$data['formula_id'] > 0
|
|
? (int)$data['formula_id'] : null;
|
|
|
|
$log = [array_merge($logging, ['action' => 'create_receipt'])];
|
|
|
|
$this->pdo->prepare(
|
|
"INSERT INTO td_receipt
|
|
(company_id, uuid, receipt_number, contact_id, department_id, receipt_billing_id, receipt_date,
|
|
payment_method, amount, formula_id, status, notes, `log`, created_at)
|
|
VALUES
|
|
(:company_id, :uuid, :receipt_number, :contact_id, :department_id, :receipt_billing_id, :receipt_date,
|
|
:payment_method, :amount, :formula_id, 1, :notes, :log, :created_at)"
|
|
)->execute([
|
|
':company_id' => $this->company_id,
|
|
':uuid' => bin2hex(random_bytes(16)),
|
|
':receipt_number' => $this->generateReceiptNumber($data),
|
|
':contact_id' => $contact_id,
|
|
':department_id' => (int)($data['department_id'] ?? 0),
|
|
':receipt_billing_id' => $billing_id,
|
|
':receipt_date' => $receipt_date,
|
|
':payment_method' => $payment_method,
|
|
':amount' => $cash_amount,
|
|
':formula_id' => $formula_id,
|
|
':notes' => $notes,
|
|
':log' => json_encode($log),
|
|
':created_at' => date('Y-m-d H:i:s'),
|
|
]);
|
|
|
|
$receipt_id = (int)$this->pdo->lastInsertId();
|
|
|
|
foreach ($validated as $line) {
|
|
$this->pdo->prepare(
|
|
"INSERT INTO td_receipt_item
|
|
(company_id, receipt_id, invoice_id, doc_type, department_id, amount)
|
|
VALUES
|
|
(:company_id, :receipt_id, :invoice_id, :doc_type, :department_id, :amount)"
|
|
)->execute([
|
|
':company_id' => $this->company_id,
|
|
':receipt_id' => $receipt_id,
|
|
':invoice_id' => $line['invoice_id'],
|
|
':doc_type' => $line['doc_type'],
|
|
':department_id' => (int)($line['department_id'] ?? 0),
|
|
':amount' => $line['amount'],
|
|
]);
|
|
|
|
$this->refreshInvoiceSettlementStatus($line['invoice_id']);
|
|
}
|
|
|
|
$this->refreshReceiptBillingStatus($billing_id);
|
|
|
|
return $receipt_id;
|
|
}
|
|
|
|
public function saveFormula(int $id, ?int $formula_id): void
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT receipt_date, status FROM td_receipt
|
|
WHERE id = :id AND company_id = :company_id"
|
|
);
|
|
$sth->execute([
|
|
':id' => $id,
|
|
':company_id' => $this->company_id,
|
|
]);
|
|
$receipt = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$receipt) throw new Exception("Receipt not found.");
|
|
if ((int)$receipt['status'] === 4) throw new Exception("Cannot update a void receipt.");
|
|
$this->assertPostingWindow($receipt['receipt_date'] ?: date('Y-m-d'), 'Receipt');
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"UPDATE td_receipt SET formula_id = :formula_id
|
|
WHERE id = :id AND company_id = :company_id AND status != 4"
|
|
);
|
|
$sth->execute([
|
|
':formula_id' => $formula_id,
|
|
':id' => $id,
|
|
':company_id' => $this->company_id,
|
|
]);
|
|
}
|
|
|
|
/**
|
|
* Soft-delete a receipt by negating company_id on the header and all items.
|
|
* Deletes the GL entry if posted. Refreshes settlement and billing status.
|
|
*/
|
|
public function softDelete(int $receipt_id): void
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT id, receipt_billing_id, receipt_date FROM td_receipt
|
|
WHERE company_id = :cid AND id = :id LIMIT 1"
|
|
);
|
|
$sth->execute([':cid' => $this->company_id, ':id' => $receipt_id]);
|
|
$receipt = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$receipt) throw new Exception('Receipt not found.');
|
|
|
|
$this->assertPostingWindow($receipt['receipt_date'] ?: date('Y-m-d'), 'Receipt deletion');
|
|
|
|
// Collect linked invoice ids before negating
|
|
$sth2 = $this->pdo->prepare(
|
|
"SELECT invoice_id FROM td_receipt_item
|
|
WHERE company_id = :cid AND receipt_id = :id"
|
|
);
|
|
$sth2->execute([':cid' => $this->company_id, ':id' => $receipt_id]);
|
|
$invoice_ids = $sth2->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
// Delete GL entry if posted (no-op if none; throws if period closed)
|
|
global $pdo1;
|
|
if (isset($pdo1) && $pdo1 instanceof PDO) {
|
|
$guard = new PostingWindowGuard($pdo1, $this->company_id);
|
|
$gl = new GlManager($this->pdo, $this->company_id, $guard);
|
|
$gl->delete('receipt', $receipt_id);
|
|
}
|
|
|
|
$this->pdo->prepare(
|
|
"UPDATE td_receipt_item SET company_id = company_id * -1
|
|
WHERE receipt_id = :id AND company_id = :cid"
|
|
)->execute([':id' => $receipt_id, ':cid' => $this->company_id]);
|
|
|
|
$this->pdo->prepare(
|
|
"UPDATE td_receipt SET company_id = company_id * -1
|
|
WHERE id = :id AND company_id = :cid"
|
|
)->execute([':id' => $receipt_id, ':cid' => $this->company_id]);
|
|
|
|
foreach ($invoice_ids as $invoice_id) {
|
|
$this->refreshInvoiceSettlementStatus((int)$invoice_id);
|
|
}
|
|
$this->refreshReceiptBillingStatus((int)$receipt['receipt_billing_id']);
|
|
}
|
|
}
|
|
?>
|