430 lines
18 KiB
PHP
430 lines
18 KiB
PHP
<?php
|
|
/**
|
|
* Accounting Helper for Automatic Journal Entries
|
|
*/
|
|
|
|
function accountingTableExists(string $tableName, bool $refresh = false): bool {
|
|
static $cache = [];
|
|
|
|
$normalized = strtolower($tableName);
|
|
if ($refresh) {
|
|
unset($cache[$normalized]);
|
|
}
|
|
|
|
if (array_key_exists($normalized, $cache)) {
|
|
return $cache[$normalized];
|
|
}
|
|
|
|
try {
|
|
$stmt = db()->prepare(
|
|
"SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1"
|
|
);
|
|
$stmt->execute([$tableName]);
|
|
$cache[$normalized] = (bool)$stmt->fetchColumn();
|
|
} catch (Throwable $e) {
|
|
error_log('Accounting table check failed: ' . $e->getMessage());
|
|
$cache[$normalized] = false;
|
|
}
|
|
|
|
return $cache[$normalized];
|
|
}
|
|
|
|
function ensureAccountingSchema(): void {
|
|
$db = db();
|
|
|
|
$db->exec(<<<SQL
|
|
CREATE TABLE IF NOT EXISTS acc_accounts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(20) UNIQUE NOT NULL,
|
|
name_en VARCHAR(100) NOT NULL,
|
|
name_ar VARCHAR(100) NOT NULL,
|
|
type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
|
|
parent_id INT NULL,
|
|
FOREIGN KEY (parent_id) REFERENCES acc_accounts(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
SQL);
|
|
|
|
$db->exec(<<<SQL
|
|
CREATE TABLE IF NOT EXISTS acc_journal_entries (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
entry_date DATE NOT NULL,
|
|
description TEXT,
|
|
reference VARCHAR(100),
|
|
source_type VARCHAR(50),
|
|
source_id INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
SQL);
|
|
|
|
$db->exec(<<<SQL
|
|
CREATE TABLE IF NOT EXISTS acc_ledger (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
journal_entry_id INT NOT NULL,
|
|
account_id INT NOT NULL,
|
|
debit DECIMAL(15, 3) DEFAULT 0,
|
|
credit DECIMAL(15, 3) DEFAULT 0,
|
|
FOREIGN KEY (journal_entry_id) REFERENCES acc_journal_entries(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (account_id) REFERENCES acc_accounts(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
SQL);
|
|
|
|
accountingTableExists('acc_accounts', true);
|
|
accountingTableExists('acc_journal_entries', true);
|
|
accountingTableExists('acc_ledger', true);
|
|
}
|
|
|
|
function getDefaultAccountingAccounts(): array {
|
|
return [
|
|
['code' => '1000', 'name_en' => 'Assets', 'name_ar' => 'الأصول', 'type' => 'asset', 'parent_code' => null],
|
|
['code' => '1100', 'name_en' => 'Cash on Hand', 'name_ar' => 'النقدية بالصندوق', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1110', 'name_en' => 'Petty Cash', 'name_ar' => 'العهدة النقدية', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1200', 'name_en' => 'Bank Account', 'name_ar' => 'حساب البنك', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1300', 'name_en' => 'Accounts Receivable', 'name_ar' => 'ذمم العملاء', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1400', 'name_en' => 'Inventory', 'name_ar' => 'المخزون', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1500', 'name_en' => 'VAT Input', 'name_ar' => 'ضريبة المدخلات', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1600', 'name_en' => 'Fixed Assets', 'name_ar' => 'الأصول الثابتة', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '1700', 'name_en' => 'Prepaid Expenses', 'name_ar' => 'المصروفات المقدمة', 'type' => 'asset', 'parent_code' => '1000'],
|
|
['code' => '2000', 'name_en' => 'Liabilities', 'name_ar' => 'الالتزامات', 'type' => 'liability', 'parent_code' => null],
|
|
['code' => '2100', 'name_en' => 'Accounts Payable', 'name_ar' => 'ذمم الموردين', 'type' => 'liability', 'parent_code' => '2000'],
|
|
['code' => '2200', 'name_en' => 'Payroll Liabilities', 'name_ar' => 'التزامات الرواتب', 'type' => 'liability', 'parent_code' => '2000'],
|
|
['code' => '2300', 'name_en' => 'VAT Payable', 'name_ar' => 'ضريبة المخرجات', 'type' => 'liability', 'parent_code' => '2000'],
|
|
['code' => '2400', 'name_en' => 'Accrued Expenses', 'name_ar' => 'مصروفات مستحقة', 'type' => 'liability', 'parent_code' => '2000'],
|
|
['code' => '3000', 'name_en' => 'Equity', 'name_ar' => 'حقوق الملكية', 'type' => 'equity', 'parent_code' => null],
|
|
['code' => '3100', 'name_en' => 'Owner Capital', 'name_ar' => 'رأس المال', 'type' => 'equity', 'parent_code' => '3000'],
|
|
['code' => '3200', 'name_en' => 'Retained Earnings', 'name_ar' => 'الأرباح المحتجزة', 'type' => 'equity', 'parent_code' => '3000'],
|
|
['code' => '4000', 'name_en' => 'Revenue', 'name_ar' => 'الإيرادات', 'type' => 'revenue', 'parent_code' => null],
|
|
['code' => '4100', 'name_en' => 'Sales Revenue', 'name_ar' => 'إيرادات المبيعات', 'type' => 'revenue', 'parent_code' => '4000'],
|
|
['code' => '4200', 'name_en' => 'Service Revenue', 'name_ar' => 'إيرادات الخدمات', 'type' => 'revenue', 'parent_code' => '4000'],
|
|
['code' => '5000', 'name_en' => 'Expenses', 'name_ar' => 'المصروفات', 'type' => 'expense', 'parent_code' => null],
|
|
['code' => '5100', 'name_en' => 'Cost of Goods Sold', 'name_ar' => 'تكلفة البضاعة المباعة', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5200', 'name_en' => 'Operating Expenses', 'name_ar' => 'المصروفات التشغيلية', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5300', 'name_en' => 'Payroll Expenses', 'name_ar' => 'مصروفات الرواتب', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5400', 'name_en' => 'Rent Expense', 'name_ar' => 'مصروف الإيجار', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5500', 'name_en' => 'Utilities Expense', 'name_ar' => 'مصروف المرافق', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5600', 'name_en' => 'Marketing Expense', 'name_ar' => 'مصروف التسويق', 'type' => 'expense', 'parent_code' => '5000'],
|
|
['code' => '5700', 'name_en' => 'Transport Expense', 'name_ar' => 'مصروف النقل', 'type' => 'expense', 'parent_code' => '5000'],
|
|
];
|
|
}
|
|
|
|
function seedDefaultAccountingAccounts(): int {
|
|
ensureAccountingSchema();
|
|
|
|
if (!accountingTableExists('acc_accounts', true)) {
|
|
return 0;
|
|
}
|
|
|
|
$db = db();
|
|
$accounts = getDefaultAccountingAccounts();
|
|
$inserted = 0;
|
|
$startedTransaction = false;
|
|
|
|
try {
|
|
if (!$db->inTransaction()) {
|
|
$db->beginTransaction();
|
|
$startedTransaction = true;
|
|
}
|
|
|
|
$selectId = $db->prepare("SELECT id FROM acc_accounts WHERE code = ? LIMIT 1");
|
|
$insert = $db->prepare(
|
|
"INSERT INTO acc_accounts (code, name_en, name_ar, type, parent_id) VALUES (?, ?, ?, ?, NULL)"
|
|
);
|
|
$updateParent = $db->prepare("UPDATE acc_accounts SET parent_id = ? WHERE code = ?");
|
|
|
|
foreach ($accounts as $account) {
|
|
$selectId->execute([$account['code']]);
|
|
if (!$selectId->fetchColumn()) {
|
|
$insert->execute([
|
|
$account['code'],
|
|
$account['name_en'],
|
|
$account['name_ar'],
|
|
$account['type'],
|
|
]);
|
|
$inserted++;
|
|
}
|
|
}
|
|
|
|
foreach ($accounts as $account) {
|
|
if (empty($account['parent_code'])) {
|
|
continue;
|
|
}
|
|
|
|
$selectId->execute([$account['parent_code']]);
|
|
$parentId = $selectId->fetchColumn();
|
|
if ($parentId) {
|
|
$updateParent->execute([(int)$parentId, $account['code']]);
|
|
}
|
|
}
|
|
|
|
if ($startedTransaction) {
|
|
$db->commit();
|
|
}
|
|
} catch (Throwable $e) {
|
|
if ($startedTransaction && $db->inTransaction()) {
|
|
$db->rollBack();
|
|
}
|
|
error_log('Accounting seed failed: ' . $e->getMessage());
|
|
return 0;
|
|
}
|
|
|
|
return $inserted;
|
|
}
|
|
|
|
function createAccountingAccount(string $code, string $nameEn, string $nameAr, string $type, ?int $parentId = null): array {
|
|
ensureAccountingSchema();
|
|
|
|
if (!accountingTableExists('acc_accounts', true)) {
|
|
return ['success' => false, 'error' => 'Accounting tables are not ready yet.'];
|
|
}
|
|
|
|
$code = strtoupper(trim($code));
|
|
$nameEn = trim($nameEn);
|
|
$nameAr = trim($nameAr);
|
|
$allowedTypes = ['asset', 'liability', 'equity', 'revenue', 'expense'];
|
|
|
|
if ($code === '' || !preg_match('/^[A-Z0-9._-]{1,20}$/', $code)) {
|
|
return ['success' => false, 'error' => 'Please enter a valid unique account code.'];
|
|
}
|
|
if ($nameEn === '' || $nameAr === '') {
|
|
return ['success' => false, 'error' => 'English and Arabic account names are required.'];
|
|
}
|
|
if (!in_array($type, $allowedTypes, true)) {
|
|
return ['success' => false, 'error' => 'Invalid account type selected.'];
|
|
}
|
|
|
|
$db = db();
|
|
|
|
try {
|
|
if ($parentId !== null) {
|
|
$stmtParent = $db->prepare("SELECT id, type FROM acc_accounts WHERE id = ? LIMIT 1");
|
|
$stmtParent->execute([$parentId]);
|
|
$parent = $stmtParent->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$parent) {
|
|
return ['success' => false, 'error' => 'Selected parent account was not found.'];
|
|
}
|
|
if (($parent['type'] ?? '') !== $type) {
|
|
return ['success' => false, 'error' => 'Parent and child accounts must use the same type.'];
|
|
}
|
|
}
|
|
|
|
$stmtExists = $db->prepare("SELECT id FROM acc_accounts WHERE code = ? LIMIT 1");
|
|
$stmtExists->execute([$code]);
|
|
if ($stmtExists->fetchColumn()) {
|
|
return ['success' => false, 'error' => 'That account code already exists.'];
|
|
}
|
|
|
|
$stmt = $db->prepare(
|
|
"INSERT INTO acc_accounts (code, name_en, name_ar, type, parent_id) VALUES (?, ?, ?, ?, ?)"
|
|
);
|
|
$stmt->execute([$code, $nameEn, $nameAr, $type, $parentId ?: null]);
|
|
|
|
return ['success' => true, 'id' => (int)$db->lastInsertId()];
|
|
} catch (Throwable $e) {
|
|
error_log('Accounting account create failed: ' . $e->getMessage());
|
|
return ['success' => false, 'error' => 'Unable to save the account right now.'];
|
|
}
|
|
}
|
|
|
|
function createJournalEntry($date, $description, $reference, $source_type, $source_id, $items) {
|
|
$db = db();
|
|
try {
|
|
$stmt = $db->prepare("INSERT INTO acc_journal_entries (entry_date, description, reference, source_type, source_id) VALUES (?, ?, ?, ?, ?)");
|
|
$stmt->execute([$date, $description, $reference, $source_type, $source_id]);
|
|
$entryId = $db->lastInsertId();
|
|
|
|
$stmtLedger = $db->prepare("INSERT INTO acc_ledger (journal_entry_id, account_id, debit, credit) VALUES (?, ?, ?, ?)");
|
|
foreach ($items as $item) {
|
|
// Find account ID by code
|
|
$stmtAcc = $db->prepare("SELECT id FROM acc_accounts WHERE code = ?");
|
|
$stmtAcc->execute([$item['code']]);
|
|
$accountId = $stmtAcc->fetchColumn();
|
|
|
|
if ($accountId) {
|
|
$stmtLedger->execute([$entryId, $accountId, $item['debit'] ?? 0, $item['credit'] ?? 0]);
|
|
}
|
|
}
|
|
return $entryId;
|
|
} catch (Exception $e) {
|
|
error_log("Accounting Error: " . $e->getMessage());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Record a Sale
|
|
*/
|
|
function recordSaleJournal($invoice_id, $amount, $date, $items_data = [], $vat_amount = 0) {
|
|
$subtotal = $amount - $vat_amount;
|
|
$entries = [
|
|
['code' => '1300', 'debit' => $amount], // Accounts Receivable (Asset increases)
|
|
['code' => '4100', 'credit' => $subtotal] // Sales Revenue (Revenue increases)
|
|
];
|
|
|
|
if ($vat_amount > 0) {
|
|
$entries[] = ['code' => '2300', 'credit' => $vat_amount]; // VAT Payable (Liability increases)
|
|
}
|
|
|
|
// Inventory & COGS
|
|
$total_cogs = 0;
|
|
foreach ($items_data as $item) {
|
|
$stmt = db()->prepare("SELECT purchase_price FROM stock_items WHERE id = ?");
|
|
$stmt->execute([$item['id']]);
|
|
$cost = (float)$stmt->fetchColumn();
|
|
$total_cogs += ($cost * $item['qty']);
|
|
}
|
|
|
|
if ($total_cogs > 0) {
|
|
$entries[] = ['code' => '5100', 'debit' => $total_cogs]; // COGS (Expense increases)
|
|
$entries[] = ['code' => '1400', 'credit' => $total_cogs]; // Inventory (Asset decreases)
|
|
}
|
|
|
|
return createJournalEntry($date, "Sale Invoice #$invoice_id", "INV-$invoice_id", 'invoice', $invoice_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record a Purchase
|
|
*/
|
|
function recordPurchaseJournal($invoice_id, $amount, $date, $items_data = [], $vat_amount = 0) {
|
|
$subtotal = $amount - $vat_amount;
|
|
$entries = [
|
|
['code' => '1400', 'debit' => $subtotal], // Inventory (Asset increases)
|
|
['code' => '2100', 'credit' => $amount] // Accounts Payable (Liability increases)
|
|
];
|
|
|
|
if ($vat_amount > 0) {
|
|
$entries[] = ['code' => '1500', 'debit' => $vat_amount]; // VAT Input (Asset increases)
|
|
}
|
|
|
|
return createJournalEntry($date, "Purchase Invoice #$invoice_id", "PINV-$invoice_id", 'invoice', $invoice_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record a Payment Received (from Customer)
|
|
*/
|
|
function recordPaymentReceivedJournal($payment_id, $amount, $date, $method) {
|
|
$code = ($method === 'Bank' || $method === 'Transfer') ? '1200' : '1100';
|
|
$entries = [
|
|
['code' => $code, 'debit' => $amount], // Cash/Bank (Asset increases)
|
|
['code' => '1300', 'credit' => $amount] // Accounts Receivable (Asset decreases)
|
|
];
|
|
return createJournalEntry($date, "Payment Received #$payment_id", "PAY-$payment_id", 'payment', $payment_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record a Payment Made (to Supplier)
|
|
*/
|
|
function recordPaymentMadeJournal($payment_id, $amount, $date, $method) {
|
|
$code = ($method === 'Bank' || $method === 'Transfer') ? '1200' : '1100';
|
|
$entries = [
|
|
['code' => '2100', 'debit' => $amount], // Accounts Payable (Liability decreases)
|
|
['code' => $code, 'credit' => $amount] // Cash/Bank (Asset decreases)
|
|
];
|
|
return createJournalEntry($date, "Payment Made #$payment_id", "PAY-$payment_id", 'payment', $payment_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record a Sales Return
|
|
*/
|
|
function recordSalesReturnJournal($return_id, $amount, $date) {
|
|
$entries = [
|
|
['code' => '4100', 'debit' => $amount], // Sales Revenue (Revenue decreases) - ideally a "Sales Returns" account
|
|
['code' => '1300', 'credit' => $amount] // Accounts Receivable (Asset decreases)
|
|
];
|
|
return createJournalEntry($date, "Sales Return #$return_id", "SRET-$return_id", 'sales_return', $return_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record a Purchase Return
|
|
*/
|
|
function recordPurchaseReturnJournal($return_id, $amount, $date) {
|
|
$entries = [
|
|
['code' => '2100', 'debit' => $amount], // Accounts Payable (Liability decreases)
|
|
['code' => '1400', 'credit' => $amount] // Inventory (Asset decreases)
|
|
];
|
|
return createJournalEntry($date, "Purchase Return #$return_id", "PRET-$return_id", 'purchase_return', $return_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record an Expense
|
|
*/
|
|
function recordExpenseJournal($expense_id, $amount, $date, $description, $method = 'Cash') {
|
|
$paymentCode = ($method === 'Bank' || $method === 'Transfer') ? '1200' : '1100';
|
|
$entries = [
|
|
['code' => '5200', 'debit' => $amount], // Operating Expenses (Expense increases)
|
|
['code' => $paymentCode, 'credit' => $amount] // Cash/Bank (Asset decreases)
|
|
];
|
|
return createJournalEntry($date, "Expense: $description", "EXP-$expense_id", 'expense', $expense_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Record Payroll Payment
|
|
*/
|
|
function recordPayrollJournal($payroll_id, $amount, $date, $emp_name) {
|
|
$entries = [
|
|
['code' => '5200', 'debit' => $amount], // Using 5200 for now, or could use 5300 if added
|
|
['code' => '1100', 'credit' => $amount] // Paid in Cash
|
|
];
|
|
return createJournalEntry($date, "Payroll Payment - $emp_name", "PAYROLL-$payroll_id", 'payroll', $payroll_id, $entries);
|
|
}
|
|
|
|
/**
|
|
* Get Account Balance
|
|
*/
|
|
function getAccountBalance($code, $start_date = null, $end_date = null) {
|
|
$db = db();
|
|
$sql = "SELECT SUM(l.debit) - SUM(l.credit) as balance
|
|
FROM acc_ledger l
|
|
JOIN acc_accounts a ON l.account_id = a.id
|
|
JOIN acc_journal_entries e ON l.journal_entry_id = e.id
|
|
WHERE a.code LIKE ?";
|
|
$params = [$code . '%'];
|
|
|
|
if ($start_date) {
|
|
$sql .= " AND e.entry_date >= ?";
|
|
$params[] = $start_date;
|
|
}
|
|
if ($end_date) {
|
|
$sql .= " AND e.entry_date <= ?";
|
|
$params[] = $end_date;
|
|
}
|
|
|
|
$stmt = $db->prepare($sql);
|
|
$stmt->execute($params);
|
|
$balance = (float)$stmt->fetchColumn();
|
|
|
|
// For Liability, Equity, Revenue: Balance = Credit - Debit
|
|
$stmtType = $db->prepare("SELECT type FROM acc_accounts WHERE code = ?");
|
|
$stmtType->execute([$code]);
|
|
$type = $stmtType->fetchColumn();
|
|
|
|
if (in_array($type, ['liability', 'equity', 'revenue'])) {
|
|
return -$balance;
|
|
}
|
|
return $balance;
|
|
}
|
|
|
|
/**
|
|
* Get VAT Report
|
|
*/
|
|
function getVatReport($start_date = null, $end_date = null) {
|
|
$input_vat = getAccountBalance('1500', $start_date, $end_date);
|
|
$output_vat = getAccountBalance('2300', $start_date, $end_date);
|
|
|
|
return [
|
|
'input_vat' => $input_vat,
|
|
'output_vat' => $output_vat,
|
|
'net_vat' => $output_vat - $input_vat
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Alias for Payroll Expense Journal
|
|
*/
|
|
function recordExpenseJournalForPayroll($payroll_id, $amount, $date) {
|
|
$entries = [
|
|
['code' => '5200', 'debit' => $amount],
|
|
['code' => '1100', 'credit' => $amount]
|
|
];
|
|
return createJournalEntry($date, "Payroll Expense #$payroll_id", "PAYROLL-$payroll_id", 'payroll', $payroll_id, $entries);
|
|
}
|