2516 lines
99 KiB
PHP
2516 lines
99 KiB
PHP
<?php
|
||
require_once __DIR__ . '/../classes_ac/PostingWindowGuard.php';
|
||
|
||
/**
|
||
* WarehouseManager
|
||
*
|
||
* Handles all warehouse, storage, bin, lot/serial, and stock deletion operations.
|
||
*
|
||
* Method order:
|
||
* Master file basis → Warehouse (get/save/delete), Storage (get/save/delete),
|
||
* Zone/Aisle/Bin resolution (getAll variants for admin views),
|
||
* Lot/Serial queries
|
||
* Transaction basis → Stock context resolution, bin lifecycle (occupy/release/transfer),
|
||
* balance adjustment, delete operations (stock in/out/transfer)
|
||
* Report basis → Warehouse list queries (getWarehouseList, getWarehouseListAll)
|
||
*
|
||
* 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.
|
||
* Dynamic stock table names are derived only from md_warehouse.id.
|
||
*/
|
||
class WarehouseManager {
|
||
|
||
private PDO $pdo;
|
||
private int $company_id;
|
||
|
||
public function __construct(PDO $pdo, int $company_id, $logging = null) {
|
||
$this->pdo = $pdo;
|
||
$this->company_id = $company_id;
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// Private helpers
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
private function stockTableNameFromWarehouseId(int $warehouse_id): string
|
||
{
|
||
if ($warehouse_id <= 0) {
|
||
throw new Exception("Invalid warehouse id.");
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_warehouse
|
||
WHERE company_id = :company_id AND id = :id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':id' => $warehouse_id]);
|
||
if (!$sth->fetchColumn()) {
|
||
throw new Exception("Warehouse ID {$warehouse_id} not found.");
|
||
}
|
||
|
||
return 'td_stock_' . $warehouse_id;
|
||
}
|
||
|
||
public function getStockTables(): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT t.table_name
|
||
FROM md_warehouse w
|
||
JOIN information_schema.tables t
|
||
ON t.table_schema = DATABASE()
|
||
AND t.table_name = CONCAT('td_stock_', w.id)
|
||
WHERE w.company_id = :company_id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
return $sth->fetchAll(PDO::FETCH_COLUMN);
|
||
}
|
||
|
||
public function assertStockMovementWindow(?string $date, string $context = 'Stock movement'): 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 resolveWarehouseTable(int $warehouse_id): ?string
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_warehouse
|
||
WHERE company_id = :company_id AND id = :id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':id' => $warehouse_id]);
|
||
$id = $sth->fetchColumn();
|
||
if (!$id) return null;
|
||
|
||
return $this->stockTableNameFromWarehouseId((int)$id);
|
||
}
|
||
|
||
/**
|
||
* Convert a from/to range string pair into a flat array of string values.
|
||
*
|
||
* Supports two range types:
|
||
* - Numeric: "1" → "10" expands to ["1", "2", ..., "10"]
|
||
* - Single-letter alpha: "A" → "D" expands to ["A", "B", "C", "D"]
|
||
*
|
||
* Used when syncing md_bin rows against an md_storage aisle/bin range.
|
||
*
|
||
* @param string $from Start of range (e.g. "1" or "A").
|
||
* @param string $to End of range (e.g. "10" or "Z").
|
||
* @return array Flat array of string values in range (inclusive).
|
||
* @throws Exception If values are not pure numeric or single alpha characters,
|
||
* or if start > end.
|
||
*/
|
||
private function rangeToArray(string $from, string $to): array
|
||
{
|
||
$from = trim($from);
|
||
$to = trim($to);
|
||
|
||
// Same value — single location, any format allowed (e.g. "Shelf-A", "Bin-3")
|
||
if ($from === $to) {
|
||
return [$from];
|
||
}
|
||
|
||
// Numeric range e.g. 1 → 10
|
||
if (is_numeric($from) && is_numeric($to)) {
|
||
$f = (int) $from;
|
||
$t = (int) $to;
|
||
if ($f > $t) throw new Exception("Range start ($from) must be <= end ($to)");
|
||
return array_map('strval', range($f, $t));
|
||
}
|
||
|
||
// Single-letter alpha range e.g. A → Z
|
||
if (ctype_alpha($from) && ctype_alpha($to)
|
||
&& strlen($from) === 1 && strlen($to) === 1) {
|
||
if (ord(strtoupper($from)) > ord(strtoupper($to))) {
|
||
throw new Exception("Range start ($from) must be <= end ($to)");
|
||
}
|
||
return range(strtoupper($from), strtoupper($to));
|
||
}
|
||
|
||
// Prefix + suffix range e.g. Shelf-A → Shelf-E or Bin-1 → Bin-5
|
||
// Split on the last sequence of digits or single letter at the end
|
||
$pattern = '/^(.*?)([A-Za-z]|[0-9]+)$/';
|
||
if (preg_match($pattern, $from, $mf) && preg_match($pattern, $to, $mt)) {
|
||
$prefix_f = $mf[1];
|
||
$prefix_t = $mt[1];
|
||
$suffix_f = $mf[2];
|
||
$suffix_t = $mt[2];
|
||
|
||
// Prefixes must match
|
||
if ($prefix_f !== $prefix_t) {
|
||
throw new Exception(
|
||
"Location prefix mismatch: '$prefix_f' vs '$prefix_t'. " .
|
||
"From and To must share the same prefix (e.g. Shelf-A → Shelf-E)."
|
||
);
|
||
}
|
||
|
||
$prefix = $prefix_f;
|
||
|
||
// Suffix: numeric range
|
||
if (is_numeric($suffix_f) && is_numeric($suffix_t)) {
|
||
$f = (int) $suffix_f;
|
||
$t = (int) $suffix_t;
|
||
if ($f > $t) throw new Exception("Range start ($from) must be <= end ($to)");
|
||
return array_map(fn($n) => $prefix . $n, range($f, $t));
|
||
}
|
||
|
||
// Suffix: single-letter range
|
||
if (ctype_alpha($suffix_f) && ctype_alpha($suffix_t)
|
||
&& strlen($suffix_f) === 1 && strlen($suffix_t) === 1) {
|
||
$sf = strtoupper($suffix_f);
|
||
$st = strtoupper($suffix_t);
|
||
if (ord($sf) > ord($st)) {
|
||
throw new Exception("Range start ($from) must be <= end ($to)");
|
||
}
|
||
return array_map(fn($c) => $prefix . $c, range($sf, $st));
|
||
}
|
||
|
||
throw new Exception(
|
||
"Location suffix must be a number (e.g. 1–99) or single letter (e.g. A–Z). " .
|
||
"Got: '$suffix_f' → '$suffix_t'"
|
||
);
|
||
}
|
||
|
||
throw new Exception(
|
||
"Invalid location range: '$from' → '$to'. " .
|
||
"Use formats like: 1–10, A–Z, Shelf-1–Shelf-10, or Shelf-A–Shelf-Z."
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Build the WHERE conditions and bound params for lot/serial filtering
|
||
* used across getZonesOut, getAislesOut, getBinsOut.
|
||
*
|
||
* Conditionally appends lot_number and/or serial_number filters only
|
||
* when those values are non-empty, preventing unnecessary join conditions.
|
||
*
|
||
* @param int $warehouse_id Warehouse to scope the query to.
|
||
* @param string $product_sku SKU being queried.
|
||
* @param string|null $lot_number Optional lot filter.
|
||
* @param string|null $serial_number Optional serial filter.
|
||
* @return array Three-element array: [$lot_cond string, $serial_cond string, $params array].
|
||
*/
|
||
private function buildLotSerialCondition(
|
||
int $warehouse_id,
|
||
string $product_sku,
|
||
?string $lot_number = null,
|
||
?string $serial_number = null
|
||
): array {
|
||
$lot_cond = !empty($lot_number) ? "AND s.lot_number = :lot_number" : "";
|
||
$serial_cond = !empty($serial_number) ? "AND s.serial_number = :serial_number" : "";
|
||
|
||
$params = [
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
':product_sku' => $product_sku,
|
||
':company_id2' => $this->company_id,
|
||
':product_sku2' => $product_sku,
|
||
];
|
||
if (!empty($lot_number)) $params[':lot_number'] = $lot_number;
|
||
if (!empty($serial_number)) $params[':serial_number'] = $serial_number;
|
||
|
||
return [$lot_cond, $serial_cond, $params];
|
||
}
|
||
|
||
/**
|
||
* 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.
|
||
* @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,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* Resolve a product's display name from md_product for use in error messages.
|
||
*
|
||
* Falls back to the SKU itself if the product record is not found.
|
||
*
|
||
* @param string $product_sku The SKU to look up.
|
||
* @return string The product_name, or $product_sku if not found.
|
||
*/
|
||
private function getProductName(string $product_sku): string {
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT product_name FROM md_product
|
||
WHERE company_id = :company_id AND sku = :sku"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':sku' => $product_sku,
|
||
]);
|
||
return $sth->fetchColumn() ?: $product_sku;
|
||
}
|
||
|
||
/**
|
||
* Validate that the given row is the globally latest transaction for its SKU.
|
||
*
|
||
* Scans this company's td_stock_* tables via UNION ALL to find the single most recent
|
||
* transaction date across all warehouses for the SKU. If a newer row exists,
|
||
* deletion is blocked to enforce LIFO (last-in-first-out) reversal order.
|
||
*
|
||
* Table names are sourced from information_schema and backtick-quoted;
|
||
* no user input reaches the identifier.
|
||
*
|
||
* @param string $product_sku SKU of the row being deleted.
|
||
* @param string $row_date The 'date' column value of the row being deleted.
|
||
* @param string $product_name Human-readable name for the error message.
|
||
* @throws Exception If a newer transaction for the same SKU exists anywhere.
|
||
*/
|
||
private function validateLatestTransaction(
|
||
string $product_sku,
|
||
string $row_date,
|
||
string $product_name
|
||
): void {
|
||
|
||
// Discover stock tables only for warehouses owned by this company.
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT t.table_name
|
||
FROM md_warehouse w
|
||
JOIN information_schema.tables t
|
||
ON t.table_schema = DATABASE()
|
||
AND t.table_name = CONCAT('td_stock_', w.id)
|
||
WHERE w.company_id = :company_id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$tables = $sth->fetchAll(PDO::FETCH_COLUMN);
|
||
|
||
if (empty($tables)) {
|
||
return;
|
||
}
|
||
|
||
// Build UNION across this company's td_stock_* tables to find the global latest date
|
||
$unions = implode(' UNION ALL ', array_map(
|
||
fn($t) => "SELECT `type`, `date` FROM `$t`
|
||
WHERE company_id = :company_id
|
||
AND product_sku = :product_sku
|
||
AND status = 1",
|
||
$tables
|
||
));
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT `type`, `date` FROM ($unions) AS all_stock
|
||
ORDER BY `date` DESC
|
||
LIMIT 1"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':product_sku' => $product_sku,
|
||
]);
|
||
$latest = $sth->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if (!$latest || $row_date === $latest['date']) {
|
||
return; // This is the latest globally — allow deletion
|
||
}
|
||
|
||
$type = strtoupper($latest['type']);
|
||
$date = $latest['date'];
|
||
|
||
throw new Exception(
|
||
"Cannot delete — \"{$product_name}\" has a newer " .
|
||
"{$type} transaction on {$date} that must be deleted first."
|
||
);
|
||
}
|
||
|
||
/**
|
||
* Insert a bin log entry for audit tracking of occupy/release/transfer events.
|
||
*
|
||
* Called internally by occupyBin, releaseBin, and transferBin.
|
||
* $extra may contain 'product_sku' and 'td_stock_id' to record what
|
||
* was in the bin at the time of the action.
|
||
*
|
||
* @param int $bin_id The md_bin.id being acted on.
|
||
* @param string $action Event label: 'occupy' | 'release' | 'transfer_in' | 'transfer_out'.
|
||
* @param array $extra Optional keys: product_sku, td_stock_id.
|
||
*/
|
||
private function insertBinLog(int $bin_id, string $action, array $extra = []): void {
|
||
|
||
$sql = "INSERT INTO td_bin_log
|
||
(company_id, md_bin_id, user_id, dt, login, action, product_sku, td_stock_id)
|
||
VALUES
|
||
(:company_id, :md_bin_id, :user_id, :dt, :login, :action, :product_sku, :td_stock_id)";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':md_bin_id' => $bin_id,
|
||
':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,
|
||
':product_sku' => $extra['product_sku'] ?? null,
|
||
':td_stock_id' => $extra['td_stock_id'] ?? null,
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* Soft-delete bin logs created by a stock movement and its reversal.
|
||
*
|
||
* td_bin_log follows the same soft-delete convention as other tenant rows:
|
||
* negate company_id so normal company_id filters hide the records.
|
||
*/
|
||
private function softDeleteBinLogs(
|
||
$warehouse_id,
|
||
string $zone,
|
||
string $aisle,
|
||
string $bin,
|
||
array $actions,
|
||
string $product_sku,
|
||
array $td_stock_ids = [],
|
||
?string $since = null
|
||
): void {
|
||
if (empty($actions)) {
|
||
return;
|
||
}
|
||
|
||
$bin_sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND bin = :bin
|
||
LIMIT 1"
|
||
);
|
||
$bin_sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
':zone' => $zone,
|
||
':aisle' => $aisle,
|
||
':bin' => $bin,
|
||
]);
|
||
$bin_id = (int)$bin_sth->fetchColumn();
|
||
if (!$bin_id) {
|
||
return;
|
||
}
|
||
|
||
$params = [
|
||
':company_id' => $this->company_id,
|
||
':md_bin_id' => $bin_id,
|
||
':product_sku' => $product_sku,
|
||
];
|
||
|
||
$action_placeholders = [];
|
||
foreach (array_values($actions) as $idx => $action) {
|
||
$key = ':action_' . $idx;
|
||
$action_placeholders[] = $key;
|
||
$params[$key] = $action;
|
||
}
|
||
|
||
$td_stock_ids = array_values(array_filter(array_map('intval', $td_stock_ids)));
|
||
$td_stock_cond = '';
|
||
if (!empty($td_stock_ids)) {
|
||
$td_placeholders = [];
|
||
foreach ($td_stock_ids as $idx => $td_stock_id) {
|
||
$key = ':td_stock_id_' . $idx;
|
||
$td_placeholders[] = $key;
|
||
$params[$key] = $td_stock_id;
|
||
}
|
||
$td_stock_cond = 'AND td_stock_id IN (' . implode(', ', $td_placeholders) . ')';
|
||
}
|
||
|
||
$since_cond = '';
|
||
if ($since !== null && $since !== '') {
|
||
$since_cond = 'AND dt >= :since';
|
||
$params[':since'] = $since;
|
||
}
|
||
|
||
$sql = "UPDATE td_bin_log
|
||
SET company_id = company_id * -1
|
||
WHERE company_id = :company_id
|
||
AND md_bin_id = :md_bin_id
|
||
AND product_sku = :product_sku
|
||
AND action IN (" . implode(', ', $action_placeholders) . ")
|
||
{$td_stock_cond}
|
||
{$since_cond}";
|
||
|
||
$this->pdo->prepare($sql)->execute($params);
|
||
}
|
||
|
||
/**
|
||
* Validate that the occupied bins under a storage_id all fall inside a new range.
|
||
*
|
||
* Called before syncBins to prevent shrinking a range that still has occupied bins.
|
||
* If any occupied bin has an aisle or bin value outside the proposed range, throws.
|
||
*
|
||
* @param int $storage_id The md_storage.id whose range is being changed.
|
||
* @param array $range Keys: aisle_from, aisle_to, bin_from, bin_to.
|
||
* @throws Exception If occupied bins would fall outside the new range.
|
||
*/
|
||
private function validateRangeChange(int $storage_id, array $range, bool $advanced_location): void
|
||
{
|
||
$valid_aisles = $this->rangeToArray($range['aisle_from'], $range['aisle_to']);
|
||
$valid_bins = $this->rangeToArray($range['bin_from'], $range['bin_to']);
|
||
|
||
// Fetch all occupied bins under this storage_id
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT zone, aisle, bin FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND storage_id = :storage_id
|
||
AND product_sku IS NOT NULL"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':storage_id' => $storage_id,
|
||
]);
|
||
|
||
foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$z = trim($row['zone']);
|
||
$a = trim($row['aisle']);
|
||
$r = trim($row['bin']);
|
||
|
||
if ($advanced_location) {
|
||
if (in_array($a, $valid_aisles, true) && in_array($r, $valid_bins, true)) {
|
||
continue;
|
||
}
|
||
throw new Exception("Cannot shrink range — some bins still have stock");
|
||
}
|
||
|
||
if ($z !== $a || $a !== $r || !in_array($r, $valid_bins, true)) {
|
||
throw new Exception("Cannot shrink range — some bins still have stock");
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Insert bin rows for the full aisle × bin range (INSERT IGNORE skips duplicates).
|
||
*
|
||
* Called by syncBins after cleaning up out-of-range empty bins.
|
||
* Supports both numeric (1-10) and alpha (A-Z) aisle/bin values.
|
||
*
|
||
* @param int $storage_id The md_storage.id this range belongs to.
|
||
* @param array $range Keys: warehouse, zone, aisle_from, aisle_to, bin_from, bin_to.
|
||
*/
|
||
private function insertBinsInRange(int $storage_id, array $range, bool $advanced_location): void
|
||
{
|
||
$sql = "INSERT IGNORE INTO md_bin
|
||
(company_id, warehouse, storage_id, zone, aisle, bin)
|
||
VALUES
|
||
(:company_id, :warehouse, :storage_id, :zone, :aisle, :bin)";
|
||
$sth = $this->pdo->prepare($sql);
|
||
|
||
$aisles = $this->rangeToArray($range['aisle_from'], $range['aisle_to']);
|
||
$bins = $this->rangeToArray($range['bin_from'], $range['bin_to']);
|
||
|
||
if (!$advanced_location) {
|
||
foreach ($bins as $location) {
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse" => $range['warehouse'],
|
||
":storage_id" => $storage_id,
|
||
":zone" => $location,
|
||
":aisle" => $location,
|
||
":bin" => $location,
|
||
]);
|
||
}
|
||
return;
|
||
}
|
||
|
||
foreach ($aisles as $a) {
|
||
foreach ($bins as $r) {
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse" => $range['warehouse'],
|
||
":storage_id" => $storage_id,
|
||
":zone" => $range['zone'],
|
||
":aisle" => $a,
|
||
":bin" => $r,
|
||
]);
|
||
}
|
||
}
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// MASTER FILE BASIS — Warehouse
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Return all warehouses with bin statistics and manager name.
|
||
*
|
||
* Used by the warehouse listing page (/inventory/warehouse.php).
|
||
* Aggregates total_bins, occupied_bins, and unique_product per warehouse
|
||
* from md_bin, and joins the wms.user table for the manager name.
|
||
*
|
||
* @return array All md_warehouse rows for this company with capacity/occupancy fields.
|
||
*/
|
||
public function getWarehouseListAll(): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT
|
||
a.*,
|
||
COALESCE(r.total_bins, 0) AS capacity,
|
||
COALESCE(r.occupied_bins, 0) AS space_used,
|
||
COALESCE(r.unique_product, 0) AS unique_product,
|
||
c.name AS manager_name,
|
||
c.surname AS manager_surname
|
||
FROM md_warehouse a
|
||
LEFT JOIN (
|
||
SELECT
|
||
company_id, warehouse,
|
||
COUNT(*) AS total_bins,
|
||
COUNT(product_sku) AS occupied_bins,
|
||
COUNT(DISTINCT product_sku) AS unique_product
|
||
FROM md_bin
|
||
GROUP BY company_id, warehouse
|
||
) r ON a.company_id = r.company_id AND a.id = r.warehouse
|
||
LEFT JOIN wms.user c ON a.manager = c.user_id
|
||
WHERE a.company_id = :company_id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Fetch a single warehouse row by its primary key.
|
||
*
|
||
* Used to pre-fill the edit form on the manage warehouse page.
|
||
*
|
||
* @param int $id The md_warehouse.id to fetch.
|
||
* @return array|false Associative row, or false if not found.
|
||
*/
|
||
public function getWarehouseById(int $id): array|false
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT * FROM md_warehouse
|
||
WHERE company_id = :company_id AND id = :id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':id' => $id]);
|
||
return $sth->fetch(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Fetch a warehouse's name string by its ID.
|
||
*
|
||
* Lightweight helper used internally and by engine files that only need
|
||
* the name (e.g. for table name resolution or display).
|
||
*
|
||
* @param int|string $warehouse_id The md_warehouse.id to look up.
|
||
* @return string|false The warehouse_name, or false if not found.
|
||
*/
|
||
public function getWarehouseName($warehouse_id) {
|
||
$sql = "SELECT warehouse_name FROM md_warehouse
|
||
WHERE company_id = :company_id AND id = :warehouse_id";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse_id" => $warehouse_id
|
||
]);
|
||
return $sth->fetchColumn();
|
||
}
|
||
|
||
/**
|
||
* Insert a new warehouse or update an existing one.
|
||
*
|
||
* On insert, returns the new warehouse id so the caller can create the
|
||
* per-warehouse stock table outside the DB transaction.
|
||
*
|
||
* Pass $data['id'] = 0 to insert; pass $data['id'] > 0 to update.
|
||
* Must be called inside dbTransaction() by the caller.
|
||
*
|
||
* @param array $data Keys: id, warehouse_name, location, manager, description, status.
|
||
* @param array $logging Audit entry to append to the log column.
|
||
* @return int|null New warehouse id on insert, null on update.
|
||
*/
|
||
public function saveWarehouse(array $data, array $logging): ?int
|
||
{
|
||
$id = (int)($data['id'] ?? 0);
|
||
$warehouse_name = trim((string)($data['warehouse_name'] ?? ''));
|
||
|
||
if ($warehouse_name === '') {
|
||
throw new Exception("Warehouse name is required.");
|
||
}
|
||
|
||
$duplicate = $this->pdo->prepare(
|
||
"SELECT COUNT(*) FROM md_warehouse
|
||
WHERE company_id = :company_id
|
||
AND warehouse_name = :warehouse_name
|
||
AND id != :id"
|
||
);
|
||
$duplicate->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse_name' => $warehouse_name,
|
||
':id' => $id,
|
||
]);
|
||
|
||
if ((int)$duplicate->fetchColumn() > 0) {
|
||
throw new Exception("Warehouse name already exists. Please use a different warehouse name.");
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT `log` FROM md_warehouse
|
||
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,
|
||
':warehouse_name' => $warehouse_name,
|
||
':location' => $data['location'] ?? '',
|
||
':manager' => (int)($data['manager'] ?? 0),
|
||
':description' => $data['description'] ?? '',
|
||
':status' => (int)($data['status'] ?? 1),
|
||
':log' => json_encode($table_log, JSON_UNESCAPED_UNICODE),
|
||
];
|
||
|
||
if ($id > 0) {
|
||
$params[':id'] = $id;
|
||
$this->pdo->prepare(
|
||
"UPDATE md_warehouse SET
|
||
`warehouse_name` = :warehouse_name,
|
||
`location` = :location,
|
||
`manager` = :manager,
|
||
`description` = :description,
|
||
`status` = :status,
|
||
`log` = :log
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute($params);
|
||
} else {
|
||
$this->pdo->prepare(
|
||
"INSERT INTO md_warehouse
|
||
(company_id, warehouse_name, location, manager, description, status, `log`)
|
||
VALUES
|
||
(:company_id, :warehouse_name, :location, :manager, :description, :status, :log)"
|
||
)->execute($params);
|
||
|
||
return (int)$this->pdo->lastInsertId();
|
||
}
|
||
|
||
return null;
|
||
}
|
||
|
||
public function createStockTableForWarehouse(int $warehouse_id): void
|
||
{
|
||
$table = $this->stockTableNameFromWarehouseId($warehouse_id);
|
||
$this->pdo->exec("CREATE TABLE IF NOT EXISTS `{$table}` LIKE `td_stock`");
|
||
}
|
||
|
||
/**
|
||
* Soft-delete a warehouse by negating its company_id.
|
||
*
|
||
* Blocks deletion if any md_storage record references this warehouse by name,
|
||
* ensuring no orphaned storage locations remain.
|
||
*
|
||
* Must be called inside dbTransaction() by the caller.
|
||
*
|
||
* @param int $warehouse_id The md_warehouse.id to delete.
|
||
* @throws Exception If the warehouse is not found or has storage locations.
|
||
*/
|
||
public function deleteWarehouse(int $warehouse_id): void {
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id, warehouse_name, `log` FROM md_warehouse
|
||
WHERE company_id = :company_id AND id = :id"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':id' => $warehouse_id,
|
||
]);
|
||
$row = $sth->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if (!$row) {
|
||
throw new Exception("Warehouse not found.");
|
||
}
|
||
|
||
// Block if any md_storage references this warehouse
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT COUNT(*) FROM md_storage
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse_id"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse_id' => $warehouse_id,
|
||
]);
|
||
if ((int)$sth->fetchColumn() > 0) {
|
||
throw new Exception(
|
||
"Cannot delete — warehouse \"{$row['warehouse_name']}\" " .
|
||
"still has storage locations assigned to it."
|
||
);
|
||
}
|
||
|
||
// Block if the warehouse's stock table has any rows
|
||
$stock_table = $this->stockTableNameFromWarehouseId($warehouse_id);
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT COUNT(*) FROM `{$stock_table}`
|
||
WHERE company_id = :company_id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
if ((int)$sth->fetchColumn() > 0) {
|
||
throw new Exception(
|
||
"Cannot delete — warehouse \"{$row['warehouse_name']}\" " .
|
||
"still has stock records. Clear the stock first."
|
||
);
|
||
}
|
||
|
||
// 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_warehouse
|
||
SET company_id = company_id * -1,
|
||
`log` = :log
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([
|
||
':log' => json_encode($log),
|
||
':id' => $warehouse_id,
|
||
':company_id' => $this->company_id,
|
||
]);
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// MASTER FILE BASIS — Storage
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Return all storage records with their associated warehouse name.
|
||
*
|
||
* Used to populate the storage listing page (/inventory/manage_storage.php).
|
||
*
|
||
* @return array All md_storage rows for this company with 'warehouse_name'.
|
||
*/
|
||
public function getStorageList(): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT a.*, b.warehouse_name
|
||
FROM md_storage a
|
||
LEFT JOIN md_warehouse b
|
||
ON a.company_id = b.company_id
|
||
AND a.warehouse = b.id
|
||
WHERE a.company_id = :company_id"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Fetch a single storage row by its primary key.
|
||
*
|
||
* Used to pre-fill the edit form on the manage storage page.
|
||
*
|
||
* @param int $id The md_storage.id to fetch.
|
||
* @return array|false Associative row, or false if not found.
|
||
*/
|
||
public function getStorageById(int $id): array|false
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT * FROM md_storage
|
||
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 storage record or update an existing one, then sync md_bin rows.
|
||
*
|
||
* After saving md_storage, calls syncBins() to ensure the md_bin table
|
||
* exactly matches the new aisle × bin range. On update, existing occupied
|
||
* bins that fall outside the new range will block the operation (via syncBins).
|
||
*
|
||
* Pass $data['id'] = 0 to insert; pass $data['id'] > 0 to update.
|
||
* Must be called inside dbTransaction() by the caller.
|
||
*
|
||
* @param array $data Keys: id, warehouse, zone, aisle_from, aisle_to,
|
||
* bin_from, bin_to, description, status.
|
||
* @param array $logging Audit entry to append to the log column.
|
||
*/
|
||
public function saveStorage(array $data, array $logging, bool $advanced_location = false): void
|
||
{
|
||
$id = (int)($data['id'] ?? 0);
|
||
|
||
$warehouse = trim((string)($data['warehouse'] ?? ''));
|
||
$zone = trim((string)($data['zone'] ?? ''));
|
||
$aisle_from = trim((string)($data['aisle_from'] ?? ''));
|
||
$aisle_to = trim((string)($data['aisle_to'] ?? ''));
|
||
$bin_from = trim((string)($data['bin_from'] ?? ''));
|
||
$bin_to = trim((string)($data['bin_to'] ?? ''));
|
||
|
||
if ($warehouse === '') {
|
||
throw new Exception("Warehouse is required.");
|
||
}
|
||
|
||
if ($advanced_location) {
|
||
if ($zone === '' || $aisle_from === '' || $aisle_to === '' || $bin_from === '' || $bin_to === '') {
|
||
throw new Exception("Zone, aisle range, and bin range are required.");
|
||
}
|
||
} else {
|
||
$location_from = $bin_from !== '' ? $bin_from : $aisle_from;
|
||
$location_to = $bin_to !== '' ? $bin_to : $aisle_to;
|
||
|
||
if ($location_from === '') {
|
||
throw new Exception("Location From is required.");
|
||
}
|
||
if ($location_to === '') {
|
||
$location_to = $location_from;
|
||
}
|
||
|
||
$zone = $location_from;
|
||
$aisle_from = $location_from;
|
||
$aisle_to = $location_to;
|
||
$bin_from = $location_from;
|
||
$bin_to = $location_to;
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT `log` FROM md_storage
|
||
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,
|
||
':warehouse' => $warehouse,
|
||
':zone' => $zone,
|
||
':aisle_from' => $aisle_from,
|
||
':aisle_to' => $aisle_to,
|
||
':bin_from' => $bin_from,
|
||
':bin_to' => $bin_to,
|
||
':description' => $data['description'] ?? '',
|
||
':status' => (int)($data['status'] ?? 1),
|
||
':log' => json_encode($table_log, JSON_UNESCAPED_UNICODE),
|
||
];
|
||
|
||
if ($id > 0) {
|
||
$params[':id'] = $id;
|
||
$this->pdo->prepare(
|
||
"UPDATE md_storage SET
|
||
warehouse = :warehouse,
|
||
zone = :zone,
|
||
aisle_from = :aisle_from,
|
||
aisle_to = :aisle_to,
|
||
bin_from = :bin_from,
|
||
bin_to = :bin_to,
|
||
description = :description,
|
||
status = :status,
|
||
`log` = :log
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute($params);
|
||
|
||
$storage_id = $id;
|
||
} else {
|
||
$this->pdo->prepare(
|
||
"INSERT INTO md_storage
|
||
(company_id, warehouse, zone, aisle_from, aisle_to,
|
||
bin_from, bin_to, description, status, `log`)
|
||
VALUES
|
||
(:company_id, :warehouse, :zone, :aisle_from, :aisle_to,
|
||
:bin_from, :bin_to, :description, :status, :log)"
|
||
)->execute($params);
|
||
|
||
$storage_id = (int)$this->pdo->lastInsertId();
|
||
}
|
||
|
||
// Sync md_bin rows to exactly match the new aisle × bin range
|
||
$this->syncBins($storage_id, [
|
||
'warehouse' => $warehouse,
|
||
'zone' => $zone,
|
||
'aisle_from' => $aisle_from,
|
||
'aisle_to' => $aisle_to,
|
||
'bin_from' => $bin_from,
|
||
'bin_to' => $bin_to,
|
||
], $advanced_location);
|
||
}
|
||
|
||
/**
|
||
* Soft-delete a storage record and its associated bin rows.
|
||
*
|
||
* Blocks deletion if any bin under this storage_id is occupied.
|
||
* Soft-deletes md_bin rows (company_id negation) and retains td_bin_log
|
||
* for audit purposes. Then soft-deletes md_storage.
|
||
*
|
||
* Must be called inside dbTransaction() by the caller.
|
||
*
|
||
* @param int $storage_id The md_storage.id to delete.
|
||
* @throws Exception If storage is not found or has occupied bins.
|
||
*/
|
||
public function deleteStorage(int $storage_id): void {
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id, zone, `log` FROM md_storage
|
||
WHERE company_id = :company_id AND id = :id"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':id' => $storage_id,
|
||
]);
|
||
$row = $sth->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if (!$row) {
|
||
throw new Exception("Storage not found.");
|
||
}
|
||
|
||
// Block if any bin under this storage is occupied
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT COUNT(*) FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND storage_id = :storage_id
|
||
AND product_sku IS NOT NULL"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':storage_id' => $storage_id,
|
||
]);
|
||
|
||
if ($sth->fetchColumn() > 0) {
|
||
throw new Exception(
|
||
"Cannot delete — storage zone \"{$row['zone']}\" " .
|
||
"still has occupied bins with stock."
|
||
);
|
||
}
|
||
|
||
// Soft-delete all md_bin rows under this storage
|
||
$this->pdo->prepare(
|
||
"UPDATE md_bin
|
||
SET company_id = company_id * -1
|
||
WHERE company_id = :company_id
|
||
AND storage_id = :storage_id"
|
||
)->execute([
|
||
':company_id' => $this->company_id,
|
||
':storage_id' => $storage_id,
|
||
]);
|
||
|
||
// 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_storage
|
||
SET company_id = company_id * -1,
|
||
`log` = :log
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([
|
||
':log' => json_encode($log),
|
||
':id' => $storage_id,
|
||
':company_id' => $this->company_id,
|
||
]);
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// MASTER FILE BASIS — Zone / Aisle / Bin (admin read-only views)
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Return all distinct zones in a warehouse — for admin/read-only listing.
|
||
*
|
||
* Returns every zone regardless of occupancy. Used on warehouse detail
|
||
* and storage admin pages where all zones must be shown.
|
||
* Results are sorted numerically first, then alphabetically.
|
||
*
|
||
* @param int $warehouse_id The md_warehouse.id to query.
|
||
* @return array Rows with 'zone' key.
|
||
*/
|
||
public function getZonesAll(int $warehouse_id): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT zone FROM md_bin
|
||
WHERE company_id = :company_id AND warehouse = :warehouse
|
||
ORDER BY CAST(zone AS UNSIGNED), zone"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':warehouse' => $warehouse_id]);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Return all distinct aisles in a zone — for admin/read-only listing.
|
||
*
|
||
* Returns every aisle regardless of occupancy. Used on storage admin pages.
|
||
*
|
||
* @param int $warehouse_id The md_warehouse.id to query.
|
||
* @param string $zone The zone to filter by.
|
||
* @return array Flat array of aisle values (strings).
|
||
*/
|
||
public function getAislesAll(int $warehouse_id, string $zone): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT aisle FROM md_bin
|
||
WHERE company_id = :company_id AND warehouse = :warehouse AND zone = :zone
|
||
ORDER BY CAST(aisle AS UNSIGNED), aisle"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':warehouse' => $warehouse_id, ':zone' => $zone]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'aisle');
|
||
}
|
||
|
||
/**
|
||
* Return all distinct bins in an aisle — for admin/read-only listing.
|
||
*
|
||
* Returns every bin regardless of occupancy. Used on storage admin pages.
|
||
*
|
||
* @param int $warehouse_id The md_warehouse.id to query.
|
||
* @param string $zone The zone to filter by.
|
||
* @param string $aisle The aisle to filter by.
|
||
* @return array Flat array of bin values (strings).
|
||
*/
|
||
public function getBinsAll(int $warehouse_id, string $zone, string $aisle): array
|
||
{
|
||
if ($zone === '' || $aisle === '') {
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT bin FROM md_bin
|
||
WHERE company_id = :company_id AND warehouse = :warehouse
|
||
ORDER BY CAST(bin AS UNSIGNED), bin"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':warehouse' => $warehouse_id]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'bin');
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT bin FROM md_bin
|
||
WHERE company_id = :company_id AND warehouse = :warehouse AND zone = :zone AND aisle = :aisle
|
||
ORDER BY CAST(bin AS UNSIGNED), bin"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':warehouse' => $warehouse_id, ':zone' => $zone, ':aisle' => $aisle]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'bin');
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// MASTER FILE BASIS — Lot / Serial queries
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Search md_lot by product_sku and lot_number keyword — for lot autocomplete.
|
||
*
|
||
* Returns up to 50 matches ordered by lot_number ASC. The keyword is safely
|
||
* bound as a LIKE parameter.
|
||
*
|
||
* @param string $product_sku The SKU to scope the search to.
|
||
* @param string $keyword Partial lot number to match.
|
||
* @return array Matching md_lot rows.
|
||
*/
|
||
public function getLotList(string $product_sku, string $keyword): array
|
||
{
|
||
// Only return lots that have at least one active td_stock row.
|
||
// Lots whose stock was fully soft-deleted (company_id negated) are excluded.
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
if (empty($warehouses)) return [];
|
||
|
||
$cid = (int)$this->company_id;
|
||
|
||
// Build UNION EXISTS subquery across all td_stock_* tables
|
||
$unions = implode(' UNION ALL ', array_map(function($wh) use ($cid) {
|
||
$table = $this->stockTableNameFromWarehouseId((int)$wh['id']);
|
||
return "SELECT lot_number FROM `{$table}`
|
||
WHERE company_id = {$cid} AND lot_number IS NOT NULL";
|
||
}, $warehouses));
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT *
|
||
FROM md_lot
|
||
WHERE company_id = :company_id
|
||
AND product_sku = :product_sku
|
||
AND lot_number LIKE :keyword
|
||
AND lot_number IN (SELECT lot_number FROM ({$unions}) AS all_lots)
|
||
ORDER BY lot_number ASC
|
||
LIMIT 50"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':product_sku' => $product_sku,
|
||
':keyword' => '%' . $keyword . '%',
|
||
]);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Return distinct active lots for a product_sku across all warehouses.
|
||
*
|
||
* "Active" means the bin linked to the stock_in row is still occupied
|
||
* (md_bin.product_sku IS NOT NULL). Includes expiry_date from md_lot.
|
||
* Deduplicates across warehouses so each lot_number appears once.
|
||
* Used to populate the lot dropdown on stock_out forms.
|
||
*
|
||
* @param string $product_sku The SKU to find active lots for.
|
||
* @return array Rows with lot_number and expiry_date, deduplicated.
|
||
*/
|
||
public function getActiveLots(string $product_sku): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$active_lots = [];
|
||
|
||
foreach ($warehouses as $wh) {
|
||
$table = $this->stockTableNameFromWarehouseId((int)$wh['id']);
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT s.lot_number, l.expiry_date
|
||
FROM `{$table}` s
|
||
LEFT JOIN md_lot l
|
||
ON l.company_id = s.company_id
|
||
AND l.product_sku = s.product_sku
|
||
AND l.lot_number = s.lot_number
|
||
WHERE s.company_id = :company_id
|
||
AND s.product_sku = :product_sku
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
AND s.lot_number IS NOT NULL
|
||
AND EXISTS (
|
||
SELECT 1 FROM md_bin r
|
||
WHERE r.company_id = s.company_id
|
||
AND r.warehouse = :warehouse_id
|
||
AND r.td_stock_id = s.id
|
||
AND r.product_sku IS NOT NULL
|
||
)"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':product_sku' => $product_sku,
|
||
':warehouse_id' => $wh['id'],
|
||
]);
|
||
|
||
foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$active_lots[$row['lot_number']] ??= $row;
|
||
}
|
||
}
|
||
|
||
return array_values($active_lots);
|
||
}
|
||
|
||
/**
|
||
* Return distinct active serial numbers for a product_sku (and optional lot)
|
||
* across all warehouses.
|
||
*
|
||
* "Active" means the bin linked to the stock_in row is still occupied.
|
||
* Used to populate the serial dropdown on stock_out forms.
|
||
*
|
||
* @param string $product_sku The SKU to find active serials for.
|
||
* @param string $lot_number Optional lot filter — pass empty string to skip.
|
||
* @return array Flat array of active serial number strings.
|
||
*/
|
||
public function getActiveSerials(string $product_sku, string $lot_number = ''): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$active_serials = [];
|
||
|
||
foreach ($warehouses as $wh) {
|
||
$table = $this->stockTableNameFromWarehouseId((int)$wh['id']);
|
||
|
||
$wh_id = $wh['id'];
|
||
$sql = "SELECT DISTINCT s.serial_number
|
||
FROM `{$table}` s
|
||
WHERE s.company_id = :company_id
|
||
AND s.product_sku = :product_sku
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
AND s.serial_number IS NOT NULL
|
||
AND EXISTS (
|
||
SELECT 1 FROM md_bin r
|
||
WHERE r.company_id = s.company_id
|
||
AND r.warehouse = {$wh_id}
|
||
AND r.td_stock_id = s.id
|
||
AND r.product_sku IS NOT NULL
|
||
)";
|
||
|
||
$params = [
|
||
':company_id' => $this->company_id,
|
||
':product_sku' => $product_sku,
|
||
];
|
||
|
||
if (!empty($lot_number)) {
|
||
$sql .= ' AND s.lot_number = :lot_number';
|
||
$params[':lot_number'] = $lot_number;
|
||
}
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute($params);
|
||
|
||
foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
|
||
$active_serials[$row['serial_number']] = $row['serial_number'];
|
||
}
|
||
}
|
||
|
||
return array_values($active_serials);
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// TRANSACTION BASIS — Stock context resolution
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Resolve the per-warehouse stock table name and optionally fetch a specific row.
|
||
*
|
||
* Core helper used throughout engine files and StockManager to get:
|
||
* - 'table': the td_stock_<warehouse_id> table name
|
||
* - 'name': the raw warehouse_name string for display
|
||
* - 'row': the specific stock row (or empty array if $id = 0)
|
||
*
|
||
* Pass $id = 0 to get just the table name without fetching a row.
|
||
* Note: does NOT filter by status — inactive warehouses can still have
|
||
* historical rows that need reading.
|
||
*
|
||
* @param int|string $warehouse_id The md_warehouse.id.
|
||
* @param int|string $id The td_stock_<warehouse_id>.id to fetch, or 0 for table-only.
|
||
* @return array Keys: 'table' (string), 'name' (string), 'row' (array|[]).
|
||
*/
|
||
public function getStockContext($warehouse_id, $id) {
|
||
$warehouse_id = (int)$warehouse_id;
|
||
$name = $this->getWarehouseName($warehouse_id);
|
||
if (!$name) {
|
||
throw new Exception("Warehouse ID {$warehouse_id} not found.");
|
||
}
|
||
|
||
$table = $this->stockTableNameFromWarehouseId($warehouse_id);
|
||
|
||
if (empty($id)) {
|
||
return [
|
||
'table' => $table,
|
||
'name' => $name,
|
||
'row' => []
|
||
];
|
||
}
|
||
|
||
$sql = "SELECT * FROM `$table`
|
||
WHERE company_id = :company_id AND id = :id";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":id" => $id
|
||
]);
|
||
|
||
return [
|
||
'table' => $table,
|
||
'name' => $name,
|
||
'row' => $sth->fetch(PDO::FETCH_ASSOC) ?: []
|
||
];
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// TRANSACTION BASIS — Bin lifecycle
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Sync md_bin rows for a storage record to exactly match a new aisle × bin range.
|
||
*
|
||
* Steps:
|
||
* 1. Validates that no occupied bins fall outside the new range (blocks if so).
|
||
* 2. Hard-deletes all empty bins belonging to this storage_id.
|
||
* 3. Re-inserts the full set of bins for the new range (INSERT IGNORE skips existing occupied bins).
|
||
*
|
||
* Called by saveStorage after every insert or update.
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int $storage_id The md_storage.id this range belongs to.
|
||
* @param array $range Keys: warehouse, zone, aisle_from, aisle_to, bin_from, bin_to.
|
||
* @throws Exception If occupied bins would be removed by the new range.
|
||
*/
|
||
public function syncBins(int $storage_id, array $range, bool $advanced_location): void {
|
||
|
||
// Guard: refuse if any occupied bin under this storage_id falls outside the new range
|
||
$this->validateRangeChange($storage_id, $range, $advanced_location);
|
||
|
||
// Wipe all empty bins belonging to this storage_id
|
||
$sql = "DELETE FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND storage_id = :storage_id
|
||
AND product_sku IS NULL";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':storage_id' => $storage_id,
|
||
]);
|
||
|
||
// Reinsert the full range fresh (INSERT IGNORE skips any occupied bins already there)
|
||
$this->insertBinsInRange($storage_id, $range, $advanced_location);
|
||
}
|
||
|
||
private function getBinRow(int $warehouse_id, string $zone, string $aisle, string $bin): array|false
|
||
{
|
||
$sql = "SELECT id, product_sku, td_stock_id
|
||
FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND bin = :bin
|
||
LIMIT 1";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
':zone' => $zone,
|
||
':aisle' => $aisle,
|
||
':bin' => $bin,
|
||
]);
|
||
|
||
return $sth->fetch(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
public function validateScanLocation(
|
||
string $mode,
|
||
int $warehouse_id,
|
||
string $zone,
|
||
string $aisle,
|
||
string $bin,
|
||
string $product_sku = '',
|
||
string $lot_number = '',
|
||
string $serial_number = ''
|
||
): void {
|
||
if (!$warehouse_id || $zone === '' || $aisle === '' || $bin === '') {
|
||
throw new Exception('Location label is incomplete.');
|
||
}
|
||
|
||
$bin_row = $this->getBinRow($warehouse_id, $zone, $aisle, $bin);
|
||
if (!$bin_row) {
|
||
throw new Exception("Location {$zone}-{$aisle}-{$bin} does not exist.");
|
||
}
|
||
|
||
if ($mode === 'in' || $mode === 'to') {
|
||
if ($bin_row['product_sku'] !== null) {
|
||
throw new Exception("Location {$zone}-{$aisle}-{$bin} is already occupied by {$bin_row['product_sku']}.");
|
||
}
|
||
|
||
if ($mode === 'in' && $product_sku !== '') {
|
||
$this->validateStockUnique($product_sku, $lot_number ?: null, $serial_number ?: null);
|
||
}
|
||
|
||
return;
|
||
}
|
||
|
||
if ($mode === 'out' || $mode === 'from') {
|
||
if ($product_sku === '') {
|
||
throw new Exception('Scan the SKU label first.');
|
||
}
|
||
|
||
$stock = $this->getBinStock($warehouse_id, $zone, $aisle, $bin);
|
||
if (!$stock) {
|
||
throw new Exception("Location {$zone}-{$aisle}-{$bin} has no approved stock to move out.");
|
||
}
|
||
|
||
if ($stock['product_sku'] !== $product_sku) {
|
||
throw new Exception("Location holds {$stock['product_sku']}, not {$product_sku}.");
|
||
}
|
||
|
||
if ($lot_number !== '' && (string)$stock['lot_number'] !== $lot_number) {
|
||
throw new Exception("Location holds lot '{$stock['lot_number']}', not '{$lot_number}'.");
|
||
}
|
||
|
||
if ($serial_number !== '' && (string)$stock['serial_number'] !== $serial_number) {
|
||
throw new Exception("Location holds serial '{$stock['serial_number']}', not '{$serial_number}'.");
|
||
}
|
||
|
||
return;
|
||
}
|
||
|
||
throw new Exception('Invalid scan validation mode.');
|
||
}
|
||
|
||
/**
|
||
* Fetch the stock record currently occupying a bin.
|
||
*
|
||
* Under the 1:1 bin model, each occupied bin's md_bin.td_stock_id points
|
||
* to exactly one td_stock_<warehouse_id> row. This method resolves that pointer and
|
||
* returns the full stock row, or null if the bin is empty or the link is broken.
|
||
*
|
||
* Used by saveStockOut and saveStockTransfer to validate bin contents before
|
||
* allowing a movement.
|
||
*
|
||
* @param int|string $warehouse_id The warehouse the bin belongs to.
|
||
* @param string $zone Zone identifier.
|
||
* @param string $aisle Aisle identifier.
|
||
* @param string $bin Bin identifier.
|
||
* @return array|null The full td_stock row linked to the bin, or null if empty.
|
||
*/
|
||
public function getBinStock($warehouse_id, $zone, $aisle, $bin): ?array {
|
||
|
||
// Get the stock pointer from md_bin
|
||
$sql = "SELECT td_stock_id FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND bin = :bin";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse" => $warehouse_id,
|
||
":zone" => $zone,
|
||
":aisle" => $aisle,
|
||
":bin" => $bin,
|
||
]);
|
||
$td_stock_id = $sth->fetchColumn();
|
||
|
||
if (!$td_stock_id) {
|
||
return null; // Bin is empty or doesn't exist
|
||
}
|
||
|
||
// Fetch the full stock row from the per-warehouse table. Source
|
||
// movements can only consume approved stock-in rows; draft stock-in
|
||
// rows may reserve a bin but are not available inventory yet.
|
||
$context = $this->getStockContext($warehouse_id, $td_stock_id);
|
||
$row = $context['row'] ?: null;
|
||
|
||
if (!$row || $row['type'] !== 'in' || (int)$row['status'] !== 1) {
|
||
return null;
|
||
}
|
||
|
||
return $row;
|
||
}
|
||
|
||
/**
|
||
* Validate that a sku + lot + serial combination is not already active in any bin.
|
||
*
|
||
* "Active" means the td_stock row is currently linked to an occupied md_bin slot.
|
||
* Enforces uniqueness rules:
|
||
* - SKU alone → allowed in multiple bins (normal stocking)
|
||
* - SKU + lot → allowed in multiple bins (lot spread across locations)
|
||
* - SKU + serial → must be unique (one physical unit = one location)
|
||
* - SKU + lot + serial → must be unique
|
||
*
|
||
* Called from occupyBin to prevent double-stocking the same serialised unit.
|
||
* The $exclude_stock_id parameter skips the row just inserted (prevents self-conflict
|
||
* in insert flows where the row exists but md_bin has not yet been updated).
|
||
*
|
||
* @param string $product_sku SKU to validate.
|
||
* @param string|null $lot_number Lot number to check (or null to skip).
|
||
* @param string|null $serial_number Serial number to check (or null to skip).
|
||
* @param int $exclude_stock_id Stock row ID to exclude from the check.
|
||
* @throws Exception If an active duplicate is found in any warehouse.
|
||
*/
|
||
public function validateStockUnique(
|
||
string $product_sku,
|
||
?string $lot_number = null,
|
||
?string $serial_number = null,
|
||
int $exclude_stock_id = 0
|
||
): void {
|
||
// Nothing to validate if neither lot nor serial was provided
|
||
if (empty($lot_number) && empty($serial_number)) return;
|
||
|
||
$cid = $this->company_id;
|
||
|
||
// Discover all active warehouses
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id, warehouse_name FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $cid]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
if (empty($warehouses)) return;
|
||
|
||
foreach ($warehouses as $wh) {
|
||
$wh_id = (int)$wh['id'];
|
||
$table = $this->stockTableNameFromWarehouseId($wh_id);
|
||
|
||
// Find any stock_in row for this SKU that is still bin-linked (active)
|
||
// and matches the lot/serial combination being validated.
|
||
// The exclude_id skips the just-inserted row to avoid false self-conflict.
|
||
// r.warehouse = :warehouse_id scopes the bin check to THIS warehouse only —
|
||
// td_stock_id values are per-table integers, not globally unique, so without
|
||
// this filter a bin in a different warehouse could cause a false positive.
|
||
$sql = "SELECT s.id
|
||
FROM `{$table}` s
|
||
WHERE s.company_id = :company_id
|
||
AND s.product_sku = :product_sku
|
||
AND s.`in` > 0
|
||
AND EXISTS (
|
||
SELECT 1 FROM md_bin r
|
||
WHERE r.company_id = :company_id2
|
||
AND r.warehouse = :warehouse_id
|
||
AND r.td_stock_id = s.id
|
||
AND r.product_sku IS NOT NULL
|
||
)";
|
||
|
||
if (!empty($lot_number)) {
|
||
$sql .= " AND s.lot_number = :lot_number";
|
||
}
|
||
if (!empty($serial_number)) {
|
||
$sql .= " AND s.serial_number = :serial_number";
|
||
}
|
||
if ($exclude_stock_id > 0) {
|
||
$sql .= " AND s.id != :exclude_id";
|
||
}
|
||
|
||
$params = [
|
||
':company_id' => $cid,
|
||
':company_id2' => $cid,
|
||
':warehouse_id' => $wh_id,
|
||
':product_sku' => $product_sku,
|
||
];
|
||
if (!empty($lot_number)) $params[':lot_number'] = $lot_number;
|
||
if (!empty($serial_number)) $params[':serial_number'] = $serial_number;
|
||
if ($exclude_stock_id > 0) $params[':exclude_id'] = $exclude_stock_id;
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute($params);
|
||
|
||
if ($sth->fetchColumn()) {
|
||
$combo = implode(' / ', array_filter([
|
||
$lot_number ? "lot: {$lot_number}" : null,
|
||
$serial_number ? "serial: {$serial_number}" : null,
|
||
]));
|
||
throw new Exception(
|
||
"Active stock already exists for product '{$product_sku}' [{$combo}] "
|
||
. "in warehouse '{$wh['warehouse_name']}'. "
|
||
. "Stock must be moved out before it can be received again."
|
||
);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Assign a product to an empty bin and link it to a td_stock row.
|
||
*
|
||
* Uses FOR UPDATE to lock the bin row and prevent concurrent occupancy.
|
||
* Also calls validateStockUnique to enforce no-duplicate-serial rules unless
|
||
* the caller is replaying a validated source transaction, such as a return.
|
||
* Updates md_bin state (product_sku + td_stock_id) in a single UPDATE
|
||
* and appends a bin log entry.
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int|string $warehouse_id Warehouse the bin belongs to.
|
||
* @param string $zone Zone identifier.
|
||
* @param string $aisle Aisle identifier.
|
||
* @param string $bin Bin identifier.
|
||
* @param string $product_sku SKU to assign to this bin.
|
||
* @param int $td_stock_id The td_stock_<warehouse_id>.id to link.
|
||
* @throws Exception If the bin does not exist or is already occupied.
|
||
*/
|
||
public function occupyBin($warehouse_id, $zone, $aisle, $bin, $product_sku, $td_stock_id, bool $skip_unique_validation = false): void {
|
||
|
||
$sql = "SELECT id, product_sku, td_stock_id FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND bin = :bin
|
||
FOR UPDATE";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse" => $warehouse_id,
|
||
":zone" => $zone,
|
||
":aisle" => $aisle,
|
||
":bin" => $bin,
|
||
]);
|
||
$current = $sth->fetch(PDO::FETCH_ASSOC);
|
||
|
||
if (!$current) {
|
||
throw new Exception("Bin {$zone}-{$aisle}-{$bin} does not exist");
|
||
}
|
||
|
||
if ($current['product_sku'] !== null) {
|
||
if (
|
||
$current['product_sku'] === $product_sku
|
||
&& (int)$current['td_stock_id'] === (int)$td_stock_id
|
||
) {
|
||
return;
|
||
}
|
||
|
||
throw new Exception(
|
||
"Bin {$zone}-{$aisle}-{$bin} is already occupied by {$current['product_sku']}"
|
||
);
|
||
}
|
||
|
||
if (!$skip_unique_validation) {
|
||
// Validate sku + lot + serial uniqueness before linking the bin
|
||
$ctx = $this->getStockContext($warehouse_id, $td_stock_id);
|
||
$row = $ctx['row'] ?? null;
|
||
|
||
if ($row) {
|
||
$this->validateStockUnique(
|
||
$product_sku,
|
||
$row['lot_number'] ?? null,
|
||
$row['serial_number'] ?? null,
|
||
$td_stock_id // exclude self so insert flows don't self-conflict
|
||
);
|
||
}
|
||
}
|
||
|
||
// Atomic UPDATE: set state and link in one statement
|
||
$sql = "UPDATE md_bin
|
||
SET product_sku = :product_sku,
|
||
td_stock_id = :td_stock_id
|
||
WHERE id = :id";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":product_sku" => $product_sku,
|
||
":td_stock_id" => $td_stock_id,
|
||
":id" => $current['id'],
|
||
]);
|
||
|
||
$this->insertBinLog($current['id'], 'occupy', [
|
||
'product_sku' => $product_sku,
|
||
'td_stock_id' => $td_stock_id,
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* Release a bin — clear its product_sku and td_stock_id link.
|
||
*
|
||
* Uses FOR UPDATE to lock the bin row. If the bin is already empty,
|
||
* exits silently (idempotent: safe to call on an already-empty bin).
|
||
* Appends a bin log entry on successful release.
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int|string $warehouse_id Warehouse the bin belongs to.
|
||
* @param string $zone Zone identifier.
|
||
* @param string $aisle Aisle identifier.
|
||
* @param string $bin Bin identifier.
|
||
*/
|
||
public function releaseBin($warehouse_id, $zone, $aisle, $bin): void {
|
||
|
||
$sql = "SELECT id, product_sku, td_stock_id FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND bin = :bin
|
||
FOR UPDATE";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":warehouse" => $warehouse_id,
|
||
":zone" => $zone,
|
||
":aisle" => $aisle,
|
||
":bin" => $bin,
|
||
]);
|
||
$current = $sth->fetch(PDO::FETCH_ASSOC);
|
||
|
||
// Nothing to release — exit silently (idempotent)
|
||
if (!$current || $current['product_sku'] === null) {
|
||
return;
|
||
}
|
||
|
||
// Clear both state fields in a single UPDATE
|
||
$sql = "UPDATE md_bin
|
||
SET product_sku = NULL,
|
||
td_stock_id = NULL
|
||
WHERE id = :id";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([":id" => $current['id']]);
|
||
|
||
$this->insertBinLog($current['id'], 'release', [
|
||
'product_sku' => $current['product_sku'],
|
||
'td_stock_id' => $current['td_stock_id'],
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* Move a bin assignment from one location to another.
|
||
*
|
||
* Transfers both product_sku and td_stock_id together from the source
|
||
* bin to the destination bin. The source must be occupied; the destination
|
||
* must be empty. Both bins are locked in id-ascending order to prevent
|
||
* deadlocks under concurrent transfers.
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int|string $from_warehouse Source warehouse.
|
||
* @param string $from_zone Source zone.
|
||
* @param string $from_aisle Source aisle.
|
||
* @param string $from_bin Source bin.
|
||
* @param int|string $to_warehouse Destination warehouse.
|
||
* @param string $to_zone Destination zone.
|
||
* @param string $to_aisle Destination aisle.
|
||
* @param string $to_bin Destination bin.
|
||
* @throws Exception If source/destination bins are not found, source is empty,
|
||
* or destination is already occupied.
|
||
*/
|
||
public function transferBin(
|
||
$from_warehouse, $from_zone, $from_aisle, $from_bin,
|
||
$to_warehouse, $to_zone, $to_aisle, $to_bin
|
||
): void {
|
||
|
||
// Lock both bins ordered by id to prevent deadlocks on concurrent transfers
|
||
$sql = "SELECT id, warehouse, zone, aisle, bin, product_sku, td_stock_id
|
||
FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND (
|
||
(warehouse = :from_wh AND zone = :from_zone
|
||
AND aisle = :from_aisle AND bin = :from_bin)
|
||
OR
|
||
(warehouse = :to_wh AND zone = :to_zone
|
||
AND aisle = :to_aisle AND bin = :to_bin)
|
||
)
|
||
ORDER BY id
|
||
FOR UPDATE";
|
||
|
||
$sth = $this->pdo->prepare($sql);
|
||
$sth->execute([
|
||
":company_id" => $this->company_id,
|
||
":from_wh" => $from_warehouse,
|
||
":from_zone" => $from_zone,
|
||
":from_aisle" => $from_aisle,
|
||
":from_bin" => $from_bin,
|
||
":to_wh" => $to_warehouse,
|
||
":to_zone" => $to_zone,
|
||
":to_aisle" => $to_aisle,
|
||
":to_bin" => $to_bin,
|
||
]);
|
||
|
||
$bins = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
// Identify source and destination from the fetched rows
|
||
$from = null;
|
||
$to = null;
|
||
foreach ($bins as $r) {
|
||
if ($r['warehouse'] == $from_warehouse && $r['zone'] == $from_zone
|
||
&& $r['aisle'] == $from_aisle && $r['bin'] == $from_bin) {
|
||
$from = $r;
|
||
}
|
||
if ($r['warehouse'] == $to_warehouse && $r['zone'] == $to_zone
|
||
&& $r['aisle'] == $to_aisle && $r['bin'] == $to_bin) {
|
||
$to = $r;
|
||
}
|
||
}
|
||
|
||
if (!$from) {
|
||
throw new Exception("Source bin not found");
|
||
}
|
||
if (!$to) {
|
||
throw new Exception("Destination bin not found");
|
||
}
|
||
if ($from['product_sku'] === null) {
|
||
throw new Exception("Source bin is empty");
|
||
}
|
||
if ($to['product_sku'] !== null) {
|
||
throw new Exception("Destination bin is already occupied");
|
||
}
|
||
|
||
// Carry both SKU and stock reference from source to destination
|
||
$sku = $from['product_sku'];
|
||
$td_stock_id = $from['td_stock_id'];
|
||
|
||
// Clear source
|
||
$this->pdo->prepare("UPDATE md_bin SET product_sku = NULL, td_stock_id = NULL WHERE id = :id")
|
||
->execute([":id" => $from['id']]);
|
||
|
||
$this->insertBinLog($from['id'], 'transfer_out', [
|
||
'product_sku' => $sku,
|
||
'td_stock_id' => $td_stock_id,
|
||
]);
|
||
|
||
// Populate destination
|
||
$this->pdo->prepare("UPDATE md_bin SET product_sku = :sku, td_stock_id = :td_stock_id WHERE id = :id")
|
||
->execute([":sku" => $sku, ":td_stock_id" => $td_stock_id, ":id" => $to['id']]);
|
||
|
||
$this->insertBinLog($to['id'], 'transfer_in', [
|
||
'product_sku' => $sku,
|
||
'td_stock_id' => $td_stock_id,
|
||
]);
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// TRANSACTION BASIS — Warehouse balance
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Adjust the running balance for a (warehouse, product_sku) pair in etl_stock_summary.
|
||
*
|
||
* Uses an INSERT ... ON DUPLICATE KEY UPDATE to atomically upsert the balance row,
|
||
* partitioned by month (YYYY-MM) for efficient monthly reporting queries.
|
||
*
|
||
* The delta formula ($new_qty - $old_qty) supports both create and edit flows:
|
||
* - On insert: old_qty = 0, delta = new_qty (adds the full amount)
|
||
* - On delete: new_qty = 0, delta = -old_qty (reverses the full amount)
|
||
* - On edit: delta = new_qty - old_qty (adjusts for the difference)
|
||
*
|
||
* Called by StockManager and deleteStock* methods; never called directly by engine files.
|
||
*
|
||
* @param string $type 'in' or 'out' — which balance column to update.
|
||
* @param int|string $warehouse_id Warehouse to adjust balance for.
|
||
* @param string $product_sku SKU to adjust.
|
||
* @param int|float $old_qty Previous quantity (0 for new records).
|
||
* @param int|float $new_qty New quantity (0 to reverse/delete).
|
||
*/
|
||
public function adjustBalance(
|
||
$type, $warehouse_id, $product_sku, $old_qty, $new_qty,
|
||
int $stock_id = 0, string $source = '', int $source_id = 0,
|
||
string $stock_date = ''
|
||
): void {
|
||
$column = $type === 'in' ? 'total_in' : 'total_out';
|
||
$delta = $new_qty - $old_qty;
|
||
$month = $stock_date ? date('Y-m', strtotime($stock_date)) : date('Y-m');
|
||
|
||
$this->pdo->prepare(
|
||
"INSERT INTO etl_stock_summary
|
||
(company_id, warehouse_id, product_sku, month, `{$column}`, source_updated_at)
|
||
VALUES
|
||
(:company_id, :warehouse_id, :product_sku, :month, :delta, NOW())
|
||
ON DUPLICATE KEY UPDATE
|
||
`{$column}` = `{$column}` + VALUES(`{$column}`),
|
||
source_updated_at = NOW()"
|
||
)->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse_id' => $warehouse_id,
|
||
':product_sku' => $product_sku,
|
||
':month' => $month,
|
||
':delta' => $delta,
|
||
]);
|
||
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// TRANSACTION BASIS — Zone / Aisle / Bin (stock movement selectors)
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Return distinct zones with at least one empty bin — for stock_in destination selector.
|
||
*
|
||
* Only zones that have at least one available (empty) bin slot are returned,
|
||
* so the user cannot direct stock to a fully occupied zone.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @return array Rows with 'zone' key.
|
||
*/
|
||
public function getZonesIn(int $warehouse_id): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT zone FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND product_sku IS NULL
|
||
ORDER BY CAST(zone AS UNSIGNED), zone"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id, ':warehouse' => $warehouse_id]);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Return distinct zones holding active stock for a sku/lot/serial — for stock_out source selector.
|
||
*
|
||
* Only zones that currently have at least one occupied bin with the matching
|
||
* product/lot/serial combination are returned.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @param string $product_sku SKU to filter by.
|
||
* @param string|null $lot_number Optional lot filter.
|
||
* @param string|null $serial_number Optional serial filter.
|
||
* @return array Rows with 'zone' key.
|
||
*/
|
||
public function getZonesOut(
|
||
int $warehouse_id,
|
||
string $product_sku,
|
||
?string $lot_number = null,
|
||
?string $serial_number = null
|
||
): array {
|
||
$table = $this->resolveWarehouseTable($warehouse_id);
|
||
if (!$table) return [];
|
||
|
||
[$lot_cond, $serial_cond, $params] = $this->buildLotSerialCondition(
|
||
$warehouse_id, $product_sku, $lot_number, $serial_number
|
||
);
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT r.zone
|
||
FROM md_bin r
|
||
WHERE r.company_id = :company_id
|
||
AND r.warehouse = :warehouse
|
||
AND r.product_sku = :product_sku
|
||
AND r.td_stock_id IN (
|
||
SELECT s.id FROM `{$table}` s
|
||
WHERE s.company_id = :company_id2
|
||
AND s.product_sku = :product_sku2
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
{$lot_cond}
|
||
{$serial_cond}
|
||
)
|
||
ORDER BY CAST(r.zone AS UNSIGNED), r.zone"
|
||
);
|
||
$sth->execute($params);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
/**
|
||
* Return distinct aisles with at least one empty bin in a zone — for stock_in destination.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @param string $zone Zone to filter by.
|
||
* @return array Flat array of aisle values (strings).
|
||
*/
|
||
public function getAislesIn(int $warehouse_id, string $zone): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT aisle FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND product_sku IS NULL
|
||
ORDER BY CAST(aisle AS UNSIGNED), aisle"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
':zone' => $zone,
|
||
]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'aisle');
|
||
}
|
||
|
||
/**
|
||
* Return distinct aisles holding active stock for a sku/lot/serial in a zone — for stock_out source.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @param string $zone Zone to filter by.
|
||
* @param string $product_sku SKU to filter by.
|
||
* @param string|null $lot_number Optional lot filter.
|
||
* @param string|null $serial_number Optional serial filter.
|
||
* @return array Flat array of aisle values (strings).
|
||
*/
|
||
public function getAislesOut(
|
||
int $warehouse_id,
|
||
string $zone,
|
||
string $product_sku,
|
||
?string $lot_number = null,
|
||
?string $serial_number = null
|
||
): array {
|
||
$table = $this->resolveWarehouseTable($warehouse_id);
|
||
if (!$table) return [];
|
||
|
||
[$lot_cond, $serial_cond, $params] = $this->buildLotSerialCondition(
|
||
$warehouse_id, $product_sku, $lot_number, $serial_number
|
||
);
|
||
$params[':zone'] = $zone;
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT r.aisle
|
||
FROM md_bin r
|
||
WHERE r.company_id = :company_id
|
||
AND r.warehouse = :warehouse
|
||
AND r.zone = :zone
|
||
AND r.product_sku = :product_sku
|
||
AND r.td_stock_id IN (
|
||
SELECT s.id FROM `{$table}` s
|
||
WHERE s.company_id = :company_id2
|
||
AND s.product_sku = :product_sku2
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
{$lot_cond}
|
||
{$serial_cond}
|
||
)
|
||
ORDER BY CAST(r.aisle AS UNSIGNED), r.aisle"
|
||
);
|
||
$sth->execute($params);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'aisle');
|
||
}
|
||
|
||
/**
|
||
* Return distinct empty bins in an aisle — for stock_in destination selector.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @param string $zone Zone to filter by.
|
||
* @param string $aisle Aisle to filter by.
|
||
* @return array Flat array of bin values (strings).
|
||
*/
|
||
public function getBinsIn(int $warehouse_id, string $zone, string $aisle): array
|
||
{
|
||
if ($zone === '' || $aisle === '') {
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT bin FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND product_sku IS NULL
|
||
ORDER BY CAST(bin AS UNSIGNED), bin"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'bin');
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT bin FROM md_bin
|
||
WHERE company_id = :company_id
|
||
AND warehouse = :warehouse
|
||
AND zone = :zone
|
||
AND aisle = :aisle
|
||
AND product_sku IS NULL
|
||
ORDER BY CAST(bin AS UNSIGNED), bin"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => $warehouse_id,
|
||
':zone' => $zone,
|
||
':aisle' => $aisle,
|
||
]);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'bin');
|
||
}
|
||
|
||
/**
|
||
* Return distinct bins holding active stock for a sku/lot/serial in an aisle — for stock_out source.
|
||
*
|
||
* @param int $warehouse_id The warehouse to query.
|
||
* @param string $zone Zone to filter by.
|
||
* @param string $aisle Aisle to filter by.
|
||
* @param string $product_sku SKU to filter by.
|
||
* @param string|null $lot_number Optional lot filter.
|
||
* @param string|null $serial_number Optional serial filter.
|
||
* @return array Flat array of bin values (strings).
|
||
*/
|
||
public function getBinsOut(
|
||
int $warehouse_id,
|
||
string $zone,
|
||
string $aisle,
|
||
string $product_sku,
|
||
?string $lot_number = null,
|
||
?string $serial_number = null
|
||
): array {
|
||
$table = $this->resolveWarehouseTable($warehouse_id);
|
||
if (!$table) return [];
|
||
|
||
[$lot_cond, $serial_cond, $params] = $this->buildLotSerialCondition(
|
||
$warehouse_id, $product_sku, $lot_number, $serial_number
|
||
);
|
||
$location_cond = '';
|
||
if ($zone !== '' && $aisle !== '') {
|
||
$location_cond = "AND r.zone = :zone AND r.aisle = :aisle";
|
||
$params[':zone'] = $zone;
|
||
$params[':aisle'] = $aisle;
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT DISTINCT r.bin
|
||
FROM md_bin r
|
||
WHERE r.company_id = :company_id
|
||
AND r.warehouse = :warehouse
|
||
AND r.product_sku = :product_sku
|
||
{$location_cond}
|
||
AND r.td_stock_id IN (
|
||
SELECT s.id FROM `{$table}` s
|
||
WHERE s.company_id = :company_id2
|
||
AND s.product_sku = :product_sku2
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
{$lot_cond}
|
||
{$serial_cond}
|
||
)
|
||
ORDER BY CAST(r.bin AS UNSIGNED), r.bin"
|
||
);
|
||
$sth->execute($params);
|
||
return array_column($sth->fetchAll(PDO::FETCH_ASSOC), 'bin');
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// TRANSACTION BASIS — Stock deletion (reversal)
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Soft-delete a stock_in record and reverse all its side effects.
|
||
*
|
||
* Steps:
|
||
* 1. Validates this is the globally latest transaction for the SKU.
|
||
* 2. Soft-deletes the td_stock row (negates company_id).
|
||
* 3. Releases the reserved/occupied bin back to empty.
|
||
* 4. Reverses the balance only if the row was approved.
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int $stock_id The td_stock_<warehouse_id>.id of the row to delete.
|
||
* @param int $warehouse_id The warehouse the row belongs to.
|
||
* @throws Exception If not the latest transaction or record not found.
|
||
*/
|
||
public function deleteStockIn(int $stock_id, int $warehouse_id): void {
|
||
|
||
$ctx = $this->getStockContext($warehouse_id, $stock_id);
|
||
$table = $ctx['table'];
|
||
$row = $ctx['row'];
|
||
|
||
if (!$row) {
|
||
throw new Exception("Stock in record not found.");
|
||
}
|
||
|
||
$product_sku = $row['product_sku'];
|
||
$product_name = $this->getProductName($product_sku);
|
||
|
||
if ((int)$row['status'] === 1) {
|
||
$this->assertStockMovementWindow($row['date'] ?? null, 'Stock-in deletion');
|
||
}
|
||
|
||
$this->validateLatestTransaction($product_sku, $row['date'], $product_name);
|
||
|
||
// Soft-delete: negate company_id so row is hidden but recoverable
|
||
$this->pdo->prepare(
|
||
"UPDATE `$table`
|
||
SET company_id = company_id * -1
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([':id' => $stock_id, ':company_id' => $this->company_id]);
|
||
|
||
// Stock-in reserves the bin at creation time, even while draft.
|
||
// Balance is only applied for approved rows.
|
||
$this->releaseBin($warehouse_id, $row['zone'], $row['aisle'], $row['bin']);
|
||
$this->softDeleteBinLogs(
|
||
$warehouse_id,
|
||
$row['zone'], $row['aisle'], $row['bin'],
|
||
['occupy', 'release'],
|
||
$product_sku,
|
||
[$stock_id],
|
||
$row['date']
|
||
);
|
||
|
||
if ((int)$row['status'] === 1) {
|
||
$this->adjustBalance('in', $warehouse_id, $product_sku, (int)$row['in'], 0, 0, '', 0, $row['date'] ?? '');
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Soft-delete a stock_out record and reverse all its side effects.
|
||
*
|
||
* Steps:
|
||
* 1. Validates this is the globally latest transaction for the SKU.
|
||
* 2. Soft-deletes the td_stock row (negates company_id).
|
||
* 3. Re-occupies the bin with the original stock_in batch (via ref_id).
|
||
* 4. Reverses the balance (adjustBalance with new_qty = 0).
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int $stock_id The td_stock_<warehouse_id>.id of the row to delete.
|
||
* @param int $warehouse_id The warehouse the row belongs to.
|
||
* @throws Exception If not the latest transaction or record not found.
|
||
*/
|
||
public function deleteStockOut(int $stock_id, int $warehouse_id): void {
|
||
|
||
$ctx = $this->getStockContext($warehouse_id, $stock_id);
|
||
$table = $ctx['table'];
|
||
$row = $ctx['row'];
|
||
|
||
if (!$row) {
|
||
throw new Exception("Stock out record not found.");
|
||
}
|
||
|
||
$product_sku = $row['product_sku'];
|
||
$product_name = $this->getProductName($product_sku);
|
||
|
||
if ((int)$row['status'] === 1) {
|
||
$this->assertStockMovementWindow($row['date'] ?? null, 'Stock-out deletion');
|
||
}
|
||
|
||
$this->validateLatestTransaction($product_sku, $row['date'], $product_name);
|
||
|
||
// Soft-delete: negate company_id so row is hidden but recoverable
|
||
$this->pdo->prepare(
|
||
"UPDATE `$table`
|
||
SET company_id = company_id * -1
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([':id' => $stock_id, ':company_id' => $this->company_id]);
|
||
|
||
// Only reverse side effects if the row was approved — draft rows never had them applied
|
||
if ((int)$row['status'] === 1) {
|
||
// Re-occupy the bin with the original stock_in batch that was consumed
|
||
$this->occupyBin(
|
||
$warehouse_id,
|
||
$row['zone'], $row['aisle'], $row['bin'],
|
||
$product_sku,
|
||
(int)$row['ref_id']
|
||
);
|
||
$this->softDeleteBinLogs(
|
||
$warehouse_id,
|
||
$row['zone'], $row['aisle'], $row['bin'],
|
||
['release', 'occupy'],
|
||
$product_sku,
|
||
[(int)$row['ref_id']],
|
||
$row['date']
|
||
);
|
||
$this->adjustBalance('out', $warehouse_id, $product_sku, (int)$row['out'], 0, 0, '', 0, $row['date'] ?? '');
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Soft-delete a transfer record pair and reverse all side effects on both warehouses.
|
||
*
|
||
* Steps:
|
||
* 1. Validates this is the globally latest transaction for the SKU.
|
||
* 2. Soft-deletes both the from and to rows (negates company_id on each).
|
||
* 3. Releases the destination bin (which was occupied by the transfer_in).
|
||
* 4. Re-occupies the source bin with the original from_stock_id batch.
|
||
* 5. Reverses balances on both warehouses.
|
||
*
|
||
* Must be called inside a DB transaction by the caller.
|
||
*
|
||
* @param int $from_stock_id The td_stock_<from>.id of the outbound transfer row.
|
||
* @param int $from_warehouse_id The source warehouse the outbound row belongs to.
|
||
* @throws Exception If not the latest transaction, or if either row is missing.
|
||
*/
|
||
public function deleteTransfer(int $from_stock_id, int $from_warehouse_id): void {
|
||
|
||
// Fetch the outbound (from) row
|
||
$from_ctx = $this->getStockContext($from_warehouse_id, $from_stock_id);
|
||
$from_table = $from_ctx['table'];
|
||
$from_row = $from_ctx['row'];
|
||
|
||
if (!$from_row || $from_row['type'] !== 'transfer') {
|
||
throw new Exception("Transfer record not found.");
|
||
}
|
||
|
||
$product_sku = $from_row['product_sku'];
|
||
$product_name = $this->getProductName($product_sku);
|
||
$to_warehouse = (int)$from_row['ref_warehouse'];
|
||
$ref_id = (int)$from_row['ref_id'];
|
||
|
||
// Fetch the inbound (to) row
|
||
$to_ctx = $this->getStockContext($to_warehouse, $ref_id);
|
||
$to_row = $to_ctx['row'];
|
||
|
||
if (!$to_row) {
|
||
throw new Exception("Paired destination record missing — data integrity issue.");
|
||
}
|
||
|
||
if ((int)$from_row['status'] === 1 || (int)$to_row['status'] === 1) {
|
||
$this->assertStockMovementWindow($from_row['date'] ?? null, 'Stock transfer cancellation');
|
||
$this->assertStockMovementWindow($to_row['date'] ?? null, 'Stock transfer cancellation');
|
||
}
|
||
|
||
// Single global validation covers both warehouses (same SKU, same date)
|
||
$this->validateLatestTransaction($product_sku, $from_row['date'], $product_name);
|
||
|
||
// Soft-delete both rows
|
||
$this->pdo->prepare(
|
||
"UPDATE `$from_table`
|
||
SET company_id = company_id * -1
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([':id' => $from_stock_id, ':company_id' => $this->company_id]);
|
||
|
||
$this->pdo->prepare(
|
||
"UPDATE `{$to_ctx['table']}`
|
||
SET company_id = company_id * -1
|
||
WHERE id = :id AND company_id = :company_id"
|
||
)->execute([':id' => $ref_id, ':company_id' => $this->company_id]);
|
||
|
||
// Only reverse side effects if approved — draft rows never had them applied
|
||
if ((int)$from_row['status'] === 1) {
|
||
// Destination bin was occupied by transfer_in — release it
|
||
$this->releaseBin(
|
||
$to_warehouse,
|
||
$to_row['zone'], $to_row['aisle'], $to_row['bin']
|
||
);
|
||
|
||
// Source bin was released by transfer_out — re-occupy with original batch
|
||
$this->occupyBin(
|
||
$from_warehouse_id,
|
||
$from_row['zone'], $from_row['aisle'], $from_row['bin'],
|
||
$product_sku,
|
||
$from_stock_id
|
||
);
|
||
$this->softDeleteBinLogs(
|
||
$to_warehouse,
|
||
$to_row['zone'], $to_row['aisle'], $to_row['bin'],
|
||
['occupy', 'release'],
|
||
$product_sku,
|
||
[$ref_id],
|
||
$from_row['date']
|
||
);
|
||
$this->softDeleteBinLogs(
|
||
$from_warehouse_id,
|
||
$from_row['zone'], $from_row['aisle'], $from_row['bin'],
|
||
['release', 'occupy'],
|
||
$product_sku,
|
||
[],
|
||
$from_row['date']
|
||
);
|
||
|
||
// Reverse balances on both sides
|
||
$quantity = (int)$from_row['out'];
|
||
$this->adjustBalance('out', $from_warehouse_id, $product_sku, $quantity, 0, 0, '', 0, $from_row['date'] ?? '');
|
||
$this->adjustBalance('in', $to_warehouse, $product_sku, $quantity, 0, 0, '', 0, $from_row['date'] ?? '');
|
||
}
|
||
}
|
||
|
||
// ─────────────────────────────────────────────────────────────
|
||
// REPORT BASIS — Warehouse list queries
|
||
// ─────────────────────────────────────────────────────────────
|
||
|
||
/**
|
||
* Return warehouse ids that currently hold an approved, bin-occupied stock-in
|
||
* row for a given product_sku + lot_number combination.
|
||
*
|
||
* Used to filter the warehouse dropdown in stock-out/transfer when a lot is selected,
|
||
* so only warehouses actually holding that lot are shown.
|
||
*
|
||
* @param string $product_sku
|
||
* @param string $lot_number
|
||
* @return int[] Array of warehouse ids.
|
||
*/
|
||
public function getWarehousesByLot(string $product_sku, string $lot_number): array
|
||
{
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id, warehouse_name FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$result = [];
|
||
$cid = (int)$this->company_id;
|
||
|
||
foreach ($warehouses as $wh) {
|
||
$table = $this->stockTableNameFromWarehouseId((int)$wh['id']);
|
||
$wh_id = (int)$wh['id'];
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT 1 FROM `{$table}` s
|
||
WHERE s.company_id = :company_id
|
||
AND s.product_sku = :product_sku
|
||
AND s.lot_number = :lot_number
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
AND EXISTS (
|
||
SELECT 1 FROM md_bin r
|
||
WHERE r.company_id = s.company_id
|
||
AND r.warehouse = :warehouse_id
|
||
AND r.td_stock_id = s.id
|
||
AND r.product_sku IS NOT NULL
|
||
)
|
||
LIMIT 1"
|
||
);
|
||
$sth->execute([
|
||
':company_id' => $cid,
|
||
':product_sku' => $product_sku,
|
||
':lot_number' => $lot_number,
|
||
':warehouse_id' => $wh_id,
|
||
]);
|
||
|
||
if ($sth->fetchColumn() !== false) {
|
||
$result[] = ['id' => $wh_id, 'warehouse_name' => $wh['warehouse_name']];
|
||
}
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
/**
|
||
* Return warehouses filtered by bin availability — for stock movement warehouse selectors.
|
||
*
|
||
* Used on stock_in / stock_out / transfer forms to present only valid warehouse options:
|
||
* type = 'to' → warehouses with at least one empty bin (valid destination)
|
||
* type = 'from' → warehouses holding the given product_sku (valid source)
|
||
* other → all warehouses with any bins
|
||
*
|
||
* Passing $id > 0 bypasses the occupancy filter (edit mode — warehouse is already set).
|
||
*
|
||
* @param string $type 'to', 'from', or any other string for unfiltered.
|
||
* @param string $product_sku SKU filter used when type = 'from'.
|
||
* @param int $id If > 0, skips the filter (edit mode).
|
||
* @return array md_warehouse rows (id, warehouse_name) ordered by name.
|
||
*/
|
||
public function getWarehouseList(string $type, string $product_sku = '', int $id = 0): array
|
||
{
|
||
if (!$id && $type === 'from') {
|
||
if ($product_sku === '') {
|
||
return [];
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT id, warehouse_name FROM md_warehouse
|
||
WHERE company_id = :company_id AND status = 1
|
||
ORDER BY warehouse_name"
|
||
);
|
||
$sth->execute([':company_id' => $this->company_id]);
|
||
$warehouses = $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
|
||
$result = [];
|
||
foreach ($warehouses as $wh) {
|
||
$table = $this->stockTableNameFromWarehouseId((int)$wh['id']);
|
||
|
||
$stock = $this->pdo->prepare(
|
||
"SELECT 1
|
||
FROM md_bin r
|
||
INNER JOIN `{$table}` s
|
||
ON s.company_id = r.company_id
|
||
AND s.id = r.td_stock_id
|
||
WHERE r.company_id = :company_id
|
||
AND r.warehouse = :warehouse
|
||
AND r.product_sku = :product_sku
|
||
AND s.product_sku = :product_sku2
|
||
AND s.`in` > 0
|
||
AND s.status = 1
|
||
LIMIT 1"
|
||
);
|
||
$stock->execute([
|
||
':company_id' => $this->company_id,
|
||
':warehouse' => (int)$wh['id'],
|
||
':product_sku' => $product_sku,
|
||
':product_sku2' => $product_sku,
|
||
]);
|
||
|
||
if ($stock->fetchColumn() !== false) {
|
||
$result[] = $wh;
|
||
}
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
$product_sku_filter = '';
|
||
$params = [':company_id' => $this->company_id];
|
||
|
||
if (!$id) {
|
||
if ($type === 'to') {
|
||
$product_sku_filter = 'AND r.product_sku IS NULL';
|
||
} elseif ($type === 'from') {
|
||
$product_sku_filter = 'AND r.product_sku = :product_sku';
|
||
$params[':product_sku'] = $product_sku;
|
||
}
|
||
}
|
||
|
||
$sth = $this->pdo->prepare(
|
||
"SELECT w.id, w.warehouse_name
|
||
FROM md_warehouse w
|
||
WHERE w.company_id = :company_id
|
||
AND EXISTS (
|
||
SELECT 1 FROM md_bin r
|
||
WHERE r.company_id = w.company_id
|
||
AND r.warehouse = w.id
|
||
$product_sku_filter
|
||
)
|
||
ORDER BY w.warehouse_name"
|
||
);
|
||
$sth->execute($params);
|
||
return $sth->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
}
|