461 lines
18 KiB
PHP
461 lines
18 KiB
PHP
<?php
|
|
|
|
/**
|
|
* ContactManager
|
|
*
|
|
* Handles all CRUD and soft-delete operations for contacts and contact types.
|
|
*
|
|
* Method order:
|
|
* Master file basis → get/save/delete for contact types and contacts
|
|
* Transaction basis → (none — contacts are master data only)
|
|
* Report basis → (none — reporting is handled by ReportManager)
|
|
*
|
|
* Note: Write methods do NOT manage their own DB transactions.
|
|
* Callers must wrap multi-step operations inside dbTransaction().
|
|
*
|
|
* Security: All SQL uses PDO prepared statements with bound parameters.
|
|
* No user input is ever interpolated directly into a query string.
|
|
*/
|
|
class ContactManager {
|
|
|
|
private PDO $pdo;
|
|
private int $company_id;
|
|
|
|
public function __construct(PDO $pdo, int $company_id) {
|
|
$this->pdo = $pdo;
|
|
$this->company_id = $company_id;
|
|
}
|
|
|
|
// ─────────────────────────────────────────────────────────────
|
|
// Private helpers
|
|
// ─────────────────────────────────────────────────────────────
|
|
|
|
/**
|
|
* Check whether a contact is referenced by any active stock transaction
|
|
* across this company's td_stock_* warehouse tables.
|
|
*
|
|
* Used as a pre-delete guard to prevent orphaning stock records.
|
|
* Discovers existing td_stock_* tables through md_warehouse scoped to this company.
|
|
* Table names from information_schema are backtick-quoted for safety.
|
|
*
|
|
* @param int $contact_id The md_contact.id to check.
|
|
* @return bool true if at least one stock row references this contact, false if clear.
|
|
*/
|
|
private function hasActiveStock(int $contact_id): bool {
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT table_name FROM information_schema.tables
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name LIKE 'td_stock_%'"
|
|
);
|
|
$sth->execute();
|
|
$tables = $sth->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
foreach ($tables as $table) {
|
|
// table name comes from information_schema (trusted system table),
|
|
// and is backtick-quoted — no user input reaches the table identifier.
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT COUNT(*) FROM `$table`
|
|
WHERE company_id = :company_id
|
|
AND contact_id = :contact_id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':contact_id' => $contact_id,
|
|
]);
|
|
if ($sth->fetchColumn() > 0) {
|
|
return true;
|
|
}
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Build a standard audit log entry array for append-to-JSON log columns.
|
|
*
|
|
* Captures the acting user_id, current datetime, session login time,
|
|
* and a short action label (e.g. 'delete', 'update').
|
|
*
|
|
* Usage:
|
|
* $log[] = $this->buildLogEntry('delete');
|
|
* $params[':log'] = json_encode($log);
|
|
*
|
|
* @param string $action Short label describing the operation (e.g. 'delete').
|
|
* @return array Associative array ready to be appended to a log array.
|
|
*/
|
|
private function buildLogEntry(string $action): array {
|
|
return [
|
|
'user_id' => $_SESSION['login_user_id'] ?? null,
|
|
'dt' => date('Y-m-d H:i:s'),
|
|
'login' => isset($_SESSION['otpTime'])
|
|
? date('Y-m-d H:i:s', $_SESSION['otpTime'])
|
|
: null,
|
|
'action' => $action,
|
|
];
|
|
}
|
|
|
|
// ─────────────────────────────────────────────────────────────
|
|
// MASTER FILE BASIS — Contact Type
|
|
// ─────────────────────────────────────────────────────────────
|
|
|
|
/**
|
|
* Return all contact types for the company.
|
|
*
|
|
* Used to populate dropdowns and the contact type listing page.
|
|
*
|
|
* @return array All rows from md_contact_type for this company.
|
|
*/
|
|
public function getContactTypeList(): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_contact_type
|
|
WHERE company_id = :company_id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
/**
|
|
* Fetch a single contact type row by its primary key.
|
|
*
|
|
* Used to pre-fill the edit form on the manage contact type page.
|
|
*
|
|
* @param int $id The md_contact_type.id to fetch.
|
|
* @return array|false Associative row, or false if not found.
|
|
*/
|
|
public function getContactTypeById(int $id): array|false
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_contact_type
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
|
return $sth->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
/**
|
|
* Insert a new contact type or update an existing one.
|
|
*
|
|
* Pass $data['id'] = 0 to insert; pass $data['id'] > 0 to update.
|
|
* The $logging array is appended to the row's JSON log column
|
|
* (caller constructs this from session/request context).
|
|
*
|
|
* Must be called inside dbTransaction() by the caller.
|
|
*
|
|
* @param array $data Keys: id, contact_type, description, status.
|
|
* @param array $logging Audit entry to append to the log column.
|
|
*/
|
|
public function saveContactType(array $data, array $logging): void
|
|
{
|
|
$id = (int)($data['id'] ?? 0);
|
|
$name = (string)($data['contact_type'] ?? '');
|
|
|
|
$dupSth = $this->pdo->prepare(
|
|
"SELECT id FROM md_contact_type
|
|
WHERE company_id = :cid AND contact_type = :name" . ($id > 0 ? " AND id != :id" : "") . " LIMIT 1"
|
|
);
|
|
$dupParams = [':cid' => $this->company_id, ':name' => $name];
|
|
if ($id > 0) $dupParams[':id'] = $id;
|
|
$dupSth->execute($dupParams);
|
|
if ($dupSth->fetchColumn()) throw new Exception("A contact type with this name already exists.");
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT `log` FROM md_contact_type
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
|
$table_log = json_decode($sth->fetchColumn() ?: '[]', true) ?: [];
|
|
$table_log[] = $logging;
|
|
|
|
$params = [
|
|
':company_id' => $this->company_id,
|
|
':contact_type' => $data['contact_type'],
|
|
':description' => $data['description'],
|
|
':status' => (int)$data['status'],
|
|
':log' => json_encode($table_log),
|
|
];
|
|
|
|
if ($id > 0) {
|
|
$params[':id'] = $id;
|
|
$this->pdo->prepare(
|
|
"UPDATE md_contact_type SET
|
|
`contact_type` = :contact_type,
|
|
`description` = :description,
|
|
`status` = :status,
|
|
`log` = :log
|
|
WHERE id = :id AND company_id = :company_id"
|
|
)->execute($params);
|
|
} else {
|
|
$this->pdo->prepare(
|
|
"INSERT INTO md_contact_type
|
|
(company_id, contact_type, `description`, `status`, `log`)
|
|
VALUES
|
|
(:company_id, :contact_type, :description, :status, :log)"
|
|
)->execute($params);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Soft-delete a contact type by negating its company_id.
|
|
*
|
|
* Blocks deletion if any md_contact row is still assigned to this type,
|
|
* preventing orphaned contacts.
|
|
*
|
|
* Must be called inside dbTransaction() by the caller.
|
|
*
|
|
* @param int $type_id The md_contact_type.id to delete.
|
|
* @throws Exception If the type is not found or has contacts assigned to it.
|
|
*/
|
|
public function deleteContactType(int $type_id): void {
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT id, contact_type, `log` FROM md_contact_type
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':id' => $type_id,
|
|
]);
|
|
$row = $sth->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$row) {
|
|
throw new Exception("Contact type not found.");
|
|
}
|
|
|
|
// Block if any contact references this type
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT COUNT(*) FROM md_contact
|
|
WHERE company_id = :company_id
|
|
AND contact_type = :type_id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':type_id' => $type_id,
|
|
]);
|
|
|
|
if ($sth->fetchColumn() > 0) {
|
|
throw new Exception(
|
|
"Cannot delete — contact type \"{$row['contact_type']}\" " .
|
|
"still has contacts assigned to it."
|
|
);
|
|
}
|
|
|
|
// Append delete event to log
|
|
$log = json_decode($row['log'] ?? '[]', true) ?: [];
|
|
$log[] = $this->buildLogEntry('delete');
|
|
|
|
// Soft-delete: negate company_id so row is hidden but recoverable
|
|
$this->pdo->prepare(
|
|
"UPDATE md_contact_type
|
|
SET company_id = company_id * -1,
|
|
`log` = :log
|
|
WHERE id = :id AND company_id = :company_id"
|
|
)->execute([
|
|
':log' => json_encode($log),
|
|
':id' => $type_id,
|
|
':company_id' => $this->company_id,
|
|
]);
|
|
}
|
|
|
|
// ─────────────────────────────────────────────────────────────
|
|
// MASTER FILE BASIS — Contact
|
|
// ─────────────────────────────────────────────────────────────
|
|
|
|
/**
|
|
* Return all contacts for the company.
|
|
*
|
|
* Used to populate the contact listing page and bulk dropdowns.
|
|
*
|
|
* @return array All rows from md_contact for this company.
|
|
*/
|
|
public function getContactList(): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_contact
|
|
WHERE company_id = :company_id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
/**
|
|
* Fetch a single contact row by its primary key.
|
|
*
|
|
* Used to pre-fill the edit form on the manage contact page.
|
|
*
|
|
* @param int $id The md_contact.id to fetch.
|
|
* @return array|false Associative row, or false if not found.
|
|
*/
|
|
public function getContactById(int $id): array|false
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_contact
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
|
return $sth->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getContactImage(int $id): string
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT contact_image FROM md_contact
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
|
return (string)($sth->fetchColumn() ?: '');
|
|
}
|
|
|
|
/**
|
|
* Search contacts by name keyword — for live autocomplete on stock forms.
|
|
*
|
|
* Returns up to 50 matches. The keyword is safely bound as a LIKE parameter;
|
|
* no wildcard escaping is needed here since '%' wrapping is the intended behaviour.
|
|
*
|
|
* @param string $keyword Partial contact name to match.
|
|
* @return array Matching md_contact rows.
|
|
*/
|
|
public function searchContact(string $keyword): array
|
|
{
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT * FROM md_contact
|
|
WHERE company_id = :company_id
|
|
AND contact_name LIKE :keyword
|
|
LIMIT 50"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':keyword' => '%' . $keyword . '%',
|
|
]);
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
/**
|
|
* Insert a new contact or update an existing one.
|
|
*
|
|
* Pass $data['id'] = 0 to insert; pass $data['id'] > 0 to update.
|
|
* $contact_image is the resolved filename/path from FileUploader — may be
|
|
* the existing image when no new file was uploaded.
|
|
* The $logging array is appended to the row's JSON log column.
|
|
*
|
|
* Must be called inside dbTransaction() by the caller.
|
|
*
|
|
* @param array $data Keys: id, contact_name, tax_id, organization, branch,
|
|
* contact_type, billing_address, shipping_location,
|
|
* shipping_address, remark, status.
|
|
* @param array $logging Audit entry to append to the log column.
|
|
* @param string $contact_image Stored filename for the contact's profile image.
|
|
*/
|
|
public function saveContact(array $data, array $logging, string $contact_image): void
|
|
{
|
|
$id = (int)($data['id'] ?? 0);
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT `log` FROM md_contact
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
|
$table_log = json_decode($sth->fetchColumn() ?: '[]', true) ?: [];
|
|
$table_log[] = $logging;
|
|
|
|
$params = [
|
|
':company_id' => $this->company_id,
|
|
':contact_name' => $data['contact_name'],
|
|
':tax_id' => $data['tax_id'],
|
|
':organization' => $data['organization'],
|
|
':branch' => $data['branch'],
|
|
':contact_type' => (int)$data['contact_type'],
|
|
':billing_address' => $data['billing_address'],
|
|
':shipping_location' => $data['shipping_location'],
|
|
':shipping_address' => $data['shipping_address'],
|
|
':remark' => $data['remark'],
|
|
':contact_image' => $contact_image,
|
|
':status' => (int)$data['status'],
|
|
':log' => json_encode($table_log),
|
|
];
|
|
|
|
if ($id > 0) {
|
|
$params[':id'] = $id;
|
|
$this->pdo->prepare(
|
|
"UPDATE md_contact SET
|
|
`contact_name` = :contact_name,
|
|
`tax_id` = :tax_id,
|
|
`organization` = :organization,
|
|
`branch` = :branch,
|
|
`contact_type` = :contact_type,
|
|
`billing_address` = :billing_address,
|
|
`shipping_location` = :shipping_location,
|
|
`shipping_address` = :shipping_address,
|
|
`remark` = :remark,
|
|
`contact_image` = :contact_image,
|
|
`status` = :status,
|
|
`log` = :log
|
|
WHERE id = :id AND company_id = :company_id"
|
|
)->execute($params);
|
|
} else {
|
|
$this->pdo->prepare(
|
|
"INSERT INTO md_contact
|
|
(company_id, contact_name, tax_id, organization, branch, contact_type,
|
|
billing_address, shipping_location, shipping_address, remark, contact_image, status, log)
|
|
VALUES
|
|
(:company_id, :contact_name, :tax_id, :organization, :branch, :contact_type,
|
|
:billing_address, :shipping_location, :shipping_address, :remark, :contact_image, :status, :log)"
|
|
)->execute($params);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Soft-delete a contact by negating its company_id.
|
|
*
|
|
* Blocks deletion if the contact is referenced in any active stock
|
|
* transaction across this company's td_stock_* warehouse tables, preventing
|
|
* broken foreign key references in transaction history.
|
|
*
|
|
* Must be called inside dbTransaction() by the caller.
|
|
*
|
|
* @param int $contact_id The md_contact.id to delete.
|
|
* @throws Exception If the contact is not found or has active stock references.
|
|
*/
|
|
public function deleteContact(int $contact_id): void {
|
|
|
|
$sth = $this->pdo->prepare(
|
|
"SELECT id, contact_name, `log` FROM md_contact
|
|
WHERE company_id = :company_id AND id = :id"
|
|
);
|
|
$sth->execute([
|
|
':company_id' => $this->company_id,
|
|
':id' => $contact_id,
|
|
]);
|
|
$row = $sth->fetch(PDO::FETCH_ASSOC);
|
|
|
|
if (!$row) {
|
|
throw new Exception("Contact not found.");
|
|
}
|
|
|
|
// Block if contact is referenced in any active stock transaction
|
|
if ($this->hasActiveStock($contact_id)) {
|
|
throw new Exception(
|
|
"Cannot delete — \"{$row['contact_name']}\" " .
|
|
"is referenced in active stock transactions."
|
|
);
|
|
}
|
|
|
|
// Append delete event to log
|
|
$log = json_decode($row['log'] ?? '[]', true) ?: [];
|
|
$log[] = $this->buildLogEntry('delete');
|
|
|
|
// Soft-delete: negate company_id so row is hidden but recoverable
|
|
$this->pdo->prepare(
|
|
"UPDATE md_contact
|
|
SET company_id = company_id * -1,
|
|
`log` = :log
|
|
WHERE id = :id AND company_id = :company_id"
|
|
)->execute([
|
|
':log' => json_encode($log),
|
|
':id' => $contact_id,
|
|
':company_id' => $this->company_id,
|
|
]);
|
|
}
|
|
}
|