379 lines
16 KiB
PHP
379 lines
16 KiB
PHP
<?php
|
|
class GlQueryManager
|
|
{
|
|
private PDO $pdo;
|
|
private int $companyId;
|
|
|
|
private array $invoiceTypes = ['invoice', 'credit_note', 'purchase_invoice', 'supplier_credit_note'];
|
|
private array $mappingTypes = ['invoice', 'credit_note', 'purchase_invoice', 'supplier_credit_note', 'purchase_order'];
|
|
private array $postingTypes = ['invoice', 'credit_note', 'purchase_invoice', 'supplier_credit_note', 'receipt', 'payment', 'purchase_order'];
|
|
private array $journalTypes = ['invoice', 'credit_note', 'purchase_invoice', 'supplier_credit_note', 'receipt', 'payment', 'manual', 'purchase_order', 'reversal'];
|
|
|
|
public function __construct(PDO $pdo, int $company_id)
|
|
{
|
|
$this->pdo = $pdo;
|
|
$this->companyId = $company_id;
|
|
}
|
|
|
|
public function getPostableDocuments(string $doc_type, string $date_from = '', string $date_to = '', int $formula_id = 0): array
|
|
{
|
|
$doc_type = trim($doc_type);
|
|
$date_from = $this->parseDate($date_from);
|
|
$date_to = $this->parseDate($date_to);
|
|
|
|
if (!in_array($doc_type, $this->postingTypes, true)) {
|
|
throw new Exception('Invalid doc_type.');
|
|
}
|
|
|
|
if (in_array($doc_type, $this->invoiceTypes, true)) {
|
|
$rows = $this->getInvoicePostableDocuments($doc_type, $date_from, $date_to, $formula_id);
|
|
} elseif ($doc_type === 'receipt') {
|
|
$rows = $this->getReceiptPostableDocuments($date_from, $date_to);
|
|
} elseif ($doc_type === 'purchase_order') {
|
|
$rows = $this->getPurchaseOrderPostableDocuments($date_from, $date_to, $formula_id);
|
|
} else {
|
|
$rows = $this->getPaymentPostableDocuments($date_from, $date_to);
|
|
}
|
|
|
|
if ($formula_id > 0) {
|
|
$rows = array_values(array_filter($rows, function($row) use ($formula_id) {
|
|
return $row['gl_status'] == 0 || (int)$row['gl_formula_id'] === $formula_id;
|
|
}));
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
public function getJournalListing(string $source_type = '', string $date_from = '', string $date_to = ''): array
|
|
{
|
|
$source_type = trim($source_type);
|
|
$date_from = $this->parseDate($date_from);
|
|
$date_to = $this->parseDate($date_to);
|
|
|
|
$where = ['g.company_id = :cid', "g.source_type != 'voided'"];
|
|
$params = [':cid' => $this->companyId];
|
|
|
|
if ($source_type && in_array($source_type, $this->journalTypes, true)) {
|
|
$where[] = 'g.source_type = :source_type';
|
|
$params[':source_type'] = $source_type;
|
|
}
|
|
if ($date_from) {
|
|
$where[] = 'g.journal_date >= :date_from';
|
|
$params[':date_from'] = $date_from;
|
|
}
|
|
if ($date_to) {
|
|
$where[] = 'g.journal_date <= :date_to';
|
|
$params[':date_to'] = $date_to;
|
|
}
|
|
|
|
$sql = "
|
|
SELECT
|
|
g.id,
|
|
g.source_type,
|
|
g.source_id,
|
|
g.period,
|
|
g.current_version,
|
|
g.formula_id,
|
|
COALESCE(f.formula_name, '') AS formula_name,
|
|
DATE_FORMAT(g.created_at, '%d/%m/%Y %H:%i') AS posted_at,
|
|
DATE_FORMAT(g.updated_at, '%d/%m/%Y %H:%i') AS updated_at,
|
|
CASE g.source_type
|
|
WHEN 'receipt' THEN r.receipt_number
|
|
WHEN 'payment' THEN p.payment_number
|
|
WHEN 'manual' THEN IF(g.reference != '', g.reference, CONCAT('MJE-', g.id))
|
|
WHEN 'purchase_order' THEN po.po_number
|
|
ELSE i.invoice_number
|
|
END AS doc_number,
|
|
COALESCE(
|
|
CASE g.source_type
|
|
WHEN 'receipt' THEN cr.contact_name
|
|
WHEN 'payment' THEN cp.contact_name
|
|
WHEN 'manual' THEN g.description
|
|
WHEN 'purchase_order' THEN cpo.contact_name
|
|
ELSE ci.contact_name
|
|
END, ''
|
|
) AS contact_name,
|
|
COALESCE(SUM(gi.debit), 0) AS total_debit,
|
|
COALESCE(SUM(gi.credit), 0) AS total_credit
|
|
FROM td_gl g
|
|
LEFT JOIN md_account_formula f
|
|
ON f.company_id = g.company_id AND f.id = g.formula_id
|
|
LEFT JOIN td_gl_item gi
|
|
ON gi.company_id = g.company_id AND gi.gl_id = g.id
|
|
LEFT JOIN td_invoice i
|
|
ON g.source_type NOT IN ('receipt','payment','manual','purchase_order')
|
|
AND i.company_id = g.company_id AND i.id = g.source_id
|
|
LEFT JOIN md_contact ci
|
|
ON ci.company_id = g.company_id AND ci.id = i.contact_id
|
|
LEFT JOIN td_receipt r
|
|
ON g.source_type = 'receipt'
|
|
AND r.company_id = g.company_id AND r.id = g.source_id
|
|
LEFT JOIN md_contact cr
|
|
ON cr.company_id = g.company_id AND cr.id = r.contact_id
|
|
LEFT JOIN td_payment p
|
|
ON g.source_type = 'payment'
|
|
AND p.company_id = g.company_id AND p.id = g.source_id
|
|
LEFT JOIN md_contact cp
|
|
ON cp.company_id = g.company_id AND cp.id = p.contact_id
|
|
LEFT JOIN td_purchase_order po
|
|
ON g.source_type = 'purchase_order'
|
|
AND po.company_id = g.company_id AND po.id = g.source_id
|
|
LEFT JOIN md_contact cpo
|
|
ON cpo.company_id = g.company_id AND cpo.id = po.contact_id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
GROUP BY
|
|
g.id, g.source_type, g.source_id, g.reference, g.description, g.period,
|
|
g.current_version, g.formula_id, f.formula_name, g.created_at, g.updated_at,
|
|
i.invoice_number, r.receipt_number, p.payment_number, po.po_number,
|
|
ci.contact_name, cr.contact_name, cp.contact_name, cpo.contact_name
|
|
ORDER BY g.created_at DESC, g.id DESC
|
|
";
|
|
|
|
$sth = $this->pdo->prepare($sql);
|
|
$sth->execute($params);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getJournalDetail(int $gl_id): array
|
|
{
|
|
if ($gl_id <= 0) {
|
|
throw new Exception('gl_id is required.');
|
|
}
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT g.*,
|
|
DATE_FORMAT(g.created_at, '%d/%m/%Y %H:%i') AS posted_at,
|
|
DATE_FORMAT(g.updated_at, '%d/%m/%Y %H:%i') AS updated_at_fmt,
|
|
DATE_FORMAT(g.journal_date, '%d/%m/%Y') AS journal_date_fmt,
|
|
COALESCE(f.formula_name, '') AS formula_name
|
|
FROM td_gl g
|
|
LEFT JOIN md_account_formula f
|
|
ON f.company_id = g.company_id AND f.id = g.formula_id
|
|
WHERE g.company_id = :cid AND g.id = :gl_id
|
|
LIMIT 1"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':gl_id' => $gl_id]);
|
|
$header = $sth->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$header) {
|
|
throw new Exception('GL entry not found.');
|
|
}
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT gi.account_code, gi.department_id, gi.debit, gi.credit, gi.description,
|
|
COALESCE(a.account_name, '') AS account_name,
|
|
COALESCE(d.dept_code, '') AS dept_code,
|
|
COALESCE(d.dept_name, '') AS dept_name
|
|
FROM td_gl_item gi
|
|
LEFT JOIN md_account a
|
|
ON a.company_id = gi.company_id AND a.account_code = gi.account_code
|
|
LEFT JOIN md_department d
|
|
ON d.company_id = gi.company_id AND d.id = gi.department_id
|
|
WHERE gi.company_id = :cid AND gi.gl_id = :gl_id
|
|
ORDER BY gi.id ASC"
|
|
);
|
|
$sth->execute([':cid' => $this->companyId, ':gl_id' => $gl_id]);
|
|
|
|
return [
|
|
'header' => $header,
|
|
'lines' => $sth->fetchAll(PDO::FETCH_ASSOC),
|
|
];
|
|
}
|
|
|
|
private function getInvoicePostableDocuments(string $doc_type, string $date_from, string $date_to, int $formula_id): array
|
|
{
|
|
$requires_product_mapping = $this->formulaRequiresProductMapping($doc_type, $formula_id);
|
|
$mapping_column = in_array($doc_type, ['invoice', 'credit_note'], true)
|
|
? 'sales_account_code'
|
|
: 'purchase_account_code';
|
|
|
|
$mapping_select = $requires_product_mapping
|
|
? ", (
|
|
SELECT COUNT(*)
|
|
FROM td_invoice_item ii
|
|
LEFT JOIN md_product p
|
|
ON p.company_id = ii.company_id
|
|
AND p.sku = ii.product_sku
|
|
WHERE ii.company_id = i.company_id
|
|
AND ii.invoice_id = i.id
|
|
AND ABS(ii.total_price) > 0.0000001
|
|
AND COALESCE(NULLIF(p.{$mapping_column}, ''), '') = ''
|
|
) AS product_mapping_missing"
|
|
: ", 0 AS product_mapping_missing";
|
|
|
|
$where = ['i.company_id = :cid', 'i.doc_type = :doc_type', 'i.status NOT IN (0, 4)'];
|
|
$params = [':cid' => $this->companyId, ':doc_type' => $doc_type, ':source_type' => $doc_type];
|
|
if ($date_from) { $where[] = 'i.issued_date >= :date_from'; $params[':date_from'] = $date_from; }
|
|
if ($date_to) { $where[] = 'i.issued_date <= :date_to'; $params[':date_to'] = $date_to; }
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT i.id,
|
|
i.invoice_number AS doc_number,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
i.grand_total,
|
|
i.issued_date AS doc_date,
|
|
IF(g.id IS NULL, 0, 1) AS gl_status,
|
|
COALESCE(g.formula_id, 0) AS gl_formula_id,
|
|
" . ($requires_product_mapping ? '1' : '0') . " AS product_mapping_checked
|
|
{$mapping_select}
|
|
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_gl g
|
|
ON g.company_id = i.company_id
|
|
AND g.source_type = :source_type
|
|
AND g.source_id = i.id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
ORDER BY i.issued_date DESC, i.id DESC"
|
|
);
|
|
$sth->execute($params);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
private function getReceiptPostableDocuments(string $date_from, string $date_to): array
|
|
{
|
|
$where = ['r.company_id = :cid', 'r.status = 1'];
|
|
$params = [':cid' => $this->companyId];
|
|
if ($date_from) { $where[] = 'r.receipt_date >= :date_from'; $params[':date_from'] = $date_from; }
|
|
if ($date_to) { $where[] = 'r.receipt_date <= :date_to'; $params[':date_to'] = $date_to; }
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT r.id,
|
|
r.receipt_number AS doc_number,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
r.amount AS grand_total,
|
|
r.receipt_date AS doc_date,
|
|
IF(g.id IS NULL, 0, 1) AS gl_status,
|
|
COALESCE(g.formula_id, 0) AS gl_formula_id,
|
|
0 AS product_mapping_checked,
|
|
0 AS product_mapping_missing
|
|
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_gl g
|
|
ON g.company_id = r.company_id
|
|
AND g.source_type = 'receipt'
|
|
AND g.source_id = r.id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
ORDER BY r.receipt_date DESC, r.id DESC"
|
|
);
|
|
$sth->execute($params);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
private function getPaymentPostableDocuments(string $date_from, string $date_to): array
|
|
{
|
|
$where = ['r.company_id = :cid', 'r.status = 1'];
|
|
$params = [':cid' => $this->companyId];
|
|
if ($date_from) { $where[] = 'r.payment_date >= :date_from'; $params[':date_from'] = $date_from; }
|
|
if ($date_to) { $where[] = 'r.payment_date <= :date_to'; $params[':date_to'] = $date_to; }
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT r.id,
|
|
r.payment_number AS doc_number,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
r.amount AS grand_total,
|
|
r.payment_date AS doc_date,
|
|
IF(g.id IS NULL, 0, 1) AS gl_status,
|
|
COALESCE(g.formula_id, 0) AS gl_formula_id,
|
|
0 AS product_mapping_checked,
|
|
0 AS product_mapping_missing
|
|
FROM td_payment r
|
|
LEFT JOIN md_contact c
|
|
ON c.company_id = r.company_id AND c.id = r.contact_id
|
|
LEFT JOIN td_gl g
|
|
ON g.company_id = r.company_id
|
|
AND g.source_type = 'payment'
|
|
AND g.source_id = r.id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
ORDER BY r.payment_date DESC, r.id DESC"
|
|
);
|
|
$sth->execute($params);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
private function getPurchaseOrderPostableDocuments(string $date_from, string $date_to, int $formula_id): array
|
|
{
|
|
$requires_mapping = $this->formulaRequiresProductMapping('purchase_order', $formula_id);
|
|
|
|
$mapping_select = $requires_mapping
|
|
? ", (
|
|
SELECT COUNT(*)
|
|
FROM td_purchase_order_item poi
|
|
LEFT JOIN md_product p
|
|
ON p.company_id = poi.company_id
|
|
AND p.sku = poi.product_sku
|
|
WHERE poi.company_id = po.company_id
|
|
AND poi.order_id = po.id
|
|
AND ABS(poi.total_price) > 0.0000001
|
|
AND COALESCE(NULLIF(p.purchase_account_code, ''), '') = ''
|
|
) AS product_mapping_missing"
|
|
: ", 0 AS product_mapping_missing";
|
|
|
|
$where = ['po.company_id = :cid', 'po.status = 1'];
|
|
$params = [':cid' => $this->companyId];
|
|
if ($date_from) { $where[] = 'po.po_date >= :date_from'; $params[':date_from'] = $date_from; }
|
|
if ($date_to) { $where[] = 'po.po_date <= :date_to'; $params[':date_to'] = $date_to; }
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT po.id,
|
|
po.po_number AS doc_number,
|
|
COALESCE(c.contact_name, '') AS contact_name,
|
|
po.grand_total,
|
|
po.po_date AS doc_date,
|
|
IF(g.id IS NULL, 0, 1) AS gl_status,
|
|
COALESCE(g.formula_id, 0) AS gl_formula_id,
|
|
" . ($requires_mapping ? '1' : '0') . " AS product_mapping_checked
|
|
{$mapping_select}
|
|
FROM td_purchase_order po
|
|
LEFT JOIN md_contact c
|
|
ON c.company_id = po.company_id AND c.id = po.contact_id
|
|
LEFT JOIN td_gl g
|
|
ON g.company_id = po.company_id
|
|
AND g.source_type = 'purchase_order'
|
|
AND g.source_id = po.id
|
|
WHERE " . implode(' AND ', $where) . "
|
|
ORDER BY po.po_date DESC, po.id DESC"
|
|
);
|
|
$sth->execute($params);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
private function formulaRequiresProductMapping(string $doc_type, int $formula_id): bool
|
|
{
|
|
if ($formula_id <= 0 || !in_array($doc_type, $this->mappingTypes, true)) {
|
|
return false;
|
|
}
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT COUNT(*)
|
|
FROM md_account_formula_item fi
|
|
JOIN md_account_formula f
|
|
ON f.company_id = fi.company_id
|
|
AND f.id = fi.formula_id
|
|
WHERE fi.company_id = :cid
|
|
AND fi.formula_id = :formula_id
|
|
AND f.document_type = :doc_type
|
|
AND fi.amount_key = 'total'
|
|
AND f.status = 1"
|
|
);
|
|
$sth->execute([
|
|
':cid' => $this->companyId,
|
|
':formula_id' => $formula_id,
|
|
':doc_type' => $doc_type,
|
|
]);
|
|
return ((int)$sth->fetchColumn()) > 0;
|
|
}
|
|
|
|
private function parseDate(string $value): string
|
|
{
|
|
$value = trim($value);
|
|
if ($value === '') return '';
|
|
if (preg_match('#^(\d{2})/(\d{2})/(\d{4})$#', $value, $m)) {
|
|
return "{$m[3]}-{$m[2]}-{$m[1]}";
|
|
}
|
|
return $value;
|
|
}
|
|
}
|
|
?>
|