244 lines
8.8 KiB
PHP
244 lines
8.8 KiB
PHP
<?php
|
|
class AccountFormulaManager
|
|
{
|
|
private PDO $pdo;
|
|
private int $companyId;
|
|
|
|
private array $documentTypes = [
|
|
'invoice',
|
|
'credit_note',
|
|
'receipt',
|
|
'purchase_invoice',
|
|
'supplier_credit_note',
|
|
'payment',
|
|
];
|
|
|
|
private array $amountKeys = ['grand_total', 'total', 'tax', 'amount'];
|
|
|
|
public function __construct(PDO $pdo, int $company_id)
|
|
{
|
|
$this->pdo = $pdo;
|
|
$this->companyId = $company_id;
|
|
}
|
|
|
|
public function getAll(): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT f.*,
|
|
COUNT(i.id) AS item_count
|
|
FROM md_account_formula f
|
|
LEFT JOIN md_account_formula_item i
|
|
ON i.company_id = f.company_id
|
|
AND i.formula_id = f.id
|
|
WHERE f.company_id = :cid
|
|
GROUP BY f.id
|
|
ORDER BY f.document_type ASC, f.is_default DESC, f.formula_name ASC"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getByType(string $document_type): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT id, formula_name, is_default
|
|
FROM md_account_formula
|
|
WHERE company_id = :cid
|
|
AND document_type = :doc_type
|
|
AND status = 1
|
|
ORDER BY is_default DESC, formula_name ASC"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':doc_type' => $document_type]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getById(int $id): ?array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_account_formula
|
|
WHERE company_id = :cid AND id = :id
|
|
LIMIT 1"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':id' => $id]);
|
|
$formula = $sth->fetch(PDO::FETCH_ASSOC);
|
|
if (!$formula) return null;
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT i.*, a.account_name, a.account_type
|
|
FROM md_account_formula_item i
|
|
LEFT JOIN md_account a
|
|
ON a.company_id = i.company_id
|
|
AND a.account_code = i.account_code
|
|
WHERE i.company_id = :cid
|
|
AND i.formula_id = :formula_id
|
|
ORDER BY i.sort_order ASC, i.id ASC"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':formula_id' => $id]);
|
|
$formula['items'] = $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return $formula;
|
|
}
|
|
|
|
public function save(array $data): int
|
|
{
|
|
$id = (int)($data['id'] ?? 0);
|
|
$name = trim((string)($data['formula_name'] ?? ''));
|
|
$documentType = trim((string)($data['document_type'] ?? ''));
|
|
$description = trim((string)($data['description'] ?? ''));
|
|
$isDefault = (int)($data['is_default'] ?? 0) === 1 ? 1 : 0;
|
|
$status = (int)($data['status'] ?? 1) === 1 ? 1 : 0;
|
|
$items = $data['items'] ?? [];
|
|
|
|
if (is_string($items)) {
|
|
$items = json_decode($items, true) ?: [];
|
|
}
|
|
|
|
if ($name === '') throw new Exception('Formula name is required.');
|
|
if (!in_array($documentType, $this->documentTypes, true)) {
|
|
throw new Exception('Invalid document type.');
|
|
}
|
|
|
|
$validated = $this->validateItems($items);
|
|
if (!$validated) throw new Exception('Formula requires at least one debit and one credit item.');
|
|
|
|
$debitKeys = array_unique(array_column(array_filter($validated, fn($i) => $i['drcr'] === 'D'), 'amount_key'));
|
|
$creditKeys = array_unique(array_column(array_filter($validated, fn($i) => $i['drcr'] === 'C'), 'amount_key'));
|
|
if (!$debitKeys || !$creditKeys) {
|
|
throw new Exception('Formula requires at least one debit and one credit item.');
|
|
}
|
|
|
|
if ($isDefault) {
|
|
$this->pdo->prepare(
|
|
"UPDATE md_account_formula
|
|
SET is_default = 0
|
|
WHERE company_id = :cid AND document_type = :document_type"
|
|
)->execute([':cid' => $this->companyId, ':document_type' => $documentType]);
|
|
}
|
|
|
|
if ($id > 0) {
|
|
$sth = $this->pdo->prepare(
|
|
"UPDATE md_account_formula SET
|
|
formula_name = :formula_name,
|
|
document_type = :document_type,
|
|
description = :description,
|
|
is_default = :is_default,
|
|
status = :status,
|
|
updated_at = :updated_at
|
|
WHERE company_id = :cid AND id = :id"
|
|
);
|
|
$sth->execute([
|
|
':cid' => $this->companyId,
|
|
':id' => $id,
|
|
':formula_name' => $name,
|
|
':document_type' => $documentType,
|
|
':description' => $description !== '' ? $description : null,
|
|
':is_default' => $isDefault,
|
|
':status' => $status,
|
|
':updated_at' => date('Y-m-d H:i:s'),
|
|
]);
|
|
} else {
|
|
$sth = $this->pdo->prepare(
|
|
"INSERT INTO md_account_formula
|
|
(company_id, formula_name, document_type, description, is_default, status, created_at)
|
|
VALUES
|
|
(:cid, :formula_name, :document_type, :description, :is_default, :status, :created_at)"
|
|
);
|
|
$sth->execute([
|
|
':cid' => $this->companyId,
|
|
':formula_name' => $name,
|
|
':document_type' => $documentType,
|
|
':description' => $description !== '' ? $description : null,
|
|
':is_default' => $isDefault,
|
|
':status' => $status,
|
|
':created_at' => date('Y-m-d H:i:s'),
|
|
]);
|
|
$id = (int)$this->pdo->lastInsertId();
|
|
}
|
|
|
|
$this->replaceItems($id, $validated);
|
|
return $id;
|
|
}
|
|
|
|
public function delete(int $id): void
|
|
{
|
|
$this->pdo->prepare(
|
|
"UPDATE md_account_formula
|
|
SET status = 0, is_default = 0, updated_at = :updated_at
|
|
WHERE company_id = :cid AND id = :id"
|
|
)->execute([
|
|
':cid' => $this->companyId,
|
|
':id' => $id,
|
|
':updated_at' => date('Y-m-d H:i:s'),
|
|
]);
|
|
}
|
|
|
|
private function validateItems(array $items): array
|
|
{
|
|
$validated = [];
|
|
$sort = 1;
|
|
foreach ($items as $item) {
|
|
$drcr = strtoupper(trim((string)($item['drcr'] ?? '')));
|
|
$accountCode = trim((string)($item['account_code'] ?? ''));
|
|
$amountKey = trim((string)($item['amount_key'] ?? ''));
|
|
$description = trim((string)($item['description'] ?? ''));
|
|
|
|
if (!in_array($drcr, ['D', 'C'], true) || $accountCode === '' || $amountKey === '') continue;
|
|
if (!in_array($amountKey, $this->amountKeys, true)) throw new Exception('Invalid amount key.');
|
|
if (!$this->isPostingAccount($accountCode)) {
|
|
throw new Exception($accountCode . ' is not an active posting account.');
|
|
}
|
|
|
|
$validated[] = [
|
|
'drcr' => $drcr,
|
|
'account_code' => $accountCode,
|
|
'amount_key' => $amountKey,
|
|
'description' => $description,
|
|
'sort_order' => $sort++,
|
|
];
|
|
}
|
|
return $validated;
|
|
}
|
|
|
|
private function isPostingAccount(string $accountCode): bool
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT COUNT(*)
|
|
FROM md_account
|
|
WHERE company_id = :cid
|
|
AND account_code = :account_code
|
|
AND is_posting = 1
|
|
AND status = 1"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':account_code' => $accountCode]);
|
|
return (int)$sth->fetchColumn() > 0;
|
|
}
|
|
|
|
private function replaceItems(int $formulaId, array $items): void
|
|
{
|
|
$this->pdo->prepare(
|
|
"DELETE FROM md_account_formula_item
|
|
WHERE company_id = :cid AND formula_id = :formula_id"
|
|
)->execute([':cid' => $this->companyId, ':formula_id' => $formulaId]);
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"INSERT INTO md_account_formula_item
|
|
(company_id, formula_id, drcr, account_code, amount_key, description, sort_order)
|
|
VALUES
|
|
(:cid, :formula_id, :drcr, :account_code, :amount_key, :description, :sort_order)"
|
|
);
|
|
|
|
foreach ($items as $item) {
|
|
$sth->execute([
|
|
':cid' => $this->companyId,
|
|
':formula_id' => $formulaId,
|
|
':drcr' => $item['drcr'],
|
|
':account_code' => $item['account_code'],
|
|
':amount_key' => $item['amount_key'],
|
|
':description' => $item['description'] !== '' ? $item['description'] : null,
|
|
':sort_order' => $item['sort_order'],
|
|
]);
|
|
}
|
|
}
|
|
}
|
|
?>
|