View Code - 20260721

_beam_installation_lib.php | 20260721_154033.php Initial Bulk Import

<?php
/* =============================================================================
   File: /erp/modules/beam/_beam_installation_lib.php
   Purpose: Helpers for Beam "Installation Date" + "Available (In Stock)" logic
   Scope : Module-scoped helpers; NO global/base edits.
   Rules :
     - A beam leaves stock the moment a Pissing or Pasaria entry is saved.
     - installation_date = that event's date; installation_source = 'pissing' | 'pasaria'
     - Entry forms must only list beams where installation_date IS NULL (available).
   ============================================================================= */

if (!function_exists('beam_col_exists')) {
  function beam_col_exists(PDO $pdo, string $table, string $col): bool {
    $db = $pdo->query("SELECT DATABASE()")->fetchColumn();
    $sql="SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1";
    $st=$pdo->prepare($sql); $st->execute([$db,$table,$col]); return (bool)$st->fetchColumn();
  }
}
if (!function_exists('beam_idx_exists')) {
  function beam_idx_exists(PDO $pdo, string $table, string $idx): bool {
    $db = $pdo->query("SELECT DATABASE()")->fetchColumn();
    $sql="SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND INDEX_NAME=? LIMIT 1";
    $st=$pdo->prepare($sql); $st->execute([$db,$table,$idx]); return (bool)$st->fetchColumn();
  }
}

/** Ensure installation columns + indexes exist (idempotent) */
if (!function_exists('ensure_beam_installation_columns')) {
  function ensure_beam_installation_columns(PDO $pdo): void {
    // Table exists check (lightweight)
    $pdo->exec("CREATE TABLE IF NOT EXISTS beam_entry ( id BIGINT PRIMARY KEY AUTO_INCREMENT ) ENGINE=InnoDB");

if (!beam_col_exists($pdo,'beam_entry','installation_date')) {
      $pdo->exec("ALTER TABLE beam_entry ADD COLUMN installation_date DATE NULL AFTER entry_date");
    }
    if (!beam_col_exists($pdo,'beam_entry','installation_source')) {
      $pdo->exec("ALTER TABLE beam_entry ADD COLUMN installation_source ENUM('pissing','pasaria') NULL AFTER installation_date");
    }
    if (!beam_idx_exists($pdo,'beam_entry','idx_be_company_install')) {
      $pdo->exec("CREATE INDEX idx_be_company_install ON beam_entry(company_id, installation_date)");
    }
    if (!beam_idx_exists($pdo,'beam_entry','idx_be_company_beamno')) {
      $pdo->exec("CREATE INDEX idx_be_company_beamno ON beam_entry(company_id, beam_no)");
    }
  }
}

/** List beams that are available (i.e., installation_date IS NULL) */
if (!function_exists('fetch_available_beams')) {
  function fetch_available_beams(PDO $pdo, int $company_id, int $limit=500, string $q=''): array {
    $sql = "SELECT b.beam_no, b.entry_date, b.meter, b.taka, b.tar,
                   b.quality_id, q.name AS quality_name
            FROM beam_entry b
            LEFT JOIN beam_qualities q
              ON q.id=b.quality_id AND q.company_id=b.company_id
            WHERE b.company_id=:cid AND b.installation_date IS NULL";
    $params = [':cid'=>$company_id];
    if ($q!=='') { $sql .= " AND b.beam_no LIKE :q"; $params[':q']="%$q%"; }
    $sql .= " ORDER BY b.entry_date DESC, b.id DESC LIMIT :lim";
    $st=$pdo->prepare($sql);
    foreach($params as $k=>$v){ $st->bindValue($k,$v); }
    $st->bindValue(':lim',$limit,PDO::PARAM_INT);
    $st->execute();
    return $st->fetchAll(PDO::FETCH_ASSOC);
  }
}

/**
 * Claim installation for a beam (atomic). Returns true if claimed.
 * source: 'pissing' | 'pasaria'
 */
if (!function_exists('claim_beam_installation')) {
  function claim_beam_installation(PDO $pdo, int $company_id, string $beam_no, string $source, string $date): bool {
    $u = $pdo->prepare("UPDATE beam_entry
                        SET installation_date=:dt, installation_source=:src
                        WHERE company_id=:cid AND beam_no=:bn AND installation_date IS NULL");
    $u->execute([':dt'=>$date, ':src'=>$source, ':cid'=>$company_id, ':bn'=>$beam_no]);
    return $u->rowCount() === 1;
  }
}

/** Recalculate earliest event for a beam and set installation_* (returns true if set/changed) */
if (!function_exists('recalc_installation_for_beam')) {
  function recalc_installation_for_beam(PDO $pdo, int $company_id, string $beam_no): bool {
    // earliest Pasaria (either side)
    $sqlPas = "SELECT MIN(entry_date) FROM pasaria_entry
               WHERE company_id=? AND (primary_beam_no=? OR secondary_beam_no=?)";
    $st = $pdo->prepare($sqlPas); $st->execute([$company_id,$beam_no,$beam_no]);
    $pasDate = $st->fetchColumn();

// earliest Pissing
    $sqlPis = "SELECT MIN(entry_date) FROM pissing_entry
               WHERE company_id=? AND beam_no=?";
    $st = $pdo->prepare($sqlPis); $st->execute([$company_id,$beam_no]);
    $pisDate = $st->fetchColumn();

$src = null; $when = null;
    if ($pasDate && $pisDate) {
      // As per new rule: legacy conflicts shouldn't happen; prefer Pasaria if both existed historically
      if (strtotime($pasDate) <= strtotime($pisDate)) { $src='pasaria'; $when=$pasDate; }
      else { $src='pissing'; $when=$pisDate; }
    } elseif ($pasDate) { $src='pasaria'; $when=$pasDate; }
    elseif ($pisDate) { $src='pissing'; $when=$pisDate; }

if ($when) {
      $u = $pdo->prepare("UPDATE beam_entry
                          SET installation_date=:dt, installation_source=:src
                          WHERE company_id=:cid AND beam_no=:bn");
      $u->execute([':dt'=>$when, ':src'=>$src, ':cid'=>$company_id, ':bn'=>$beam_no]);
      return $u->rowCount() >= 0; // updated or same
    }
    return false;
  }
}