View Code - 20260722

beam_stock_refresh.php | 20260722_120325.php Initial Domain Snapshot

<?php
/* ============================================================================
   File: /erp/beam_stock_refresh.php
   Purpose: Build/Refresh physical table "beam_stock" (company-scoped)
   Rule  : STOCK = beam_entry me hai, lekin pasaria_entry (primary/secondary)
           ya pissing_entry (beam_no) me NAHI hai.
   Scope : Page-local only; no global/base edits.
   ============================================================================ */
error_reporting(E_ALL); ini_set('display_errors',1);

require __DIR__ . '/modules/auth/auth.php';
require_login();
$u          = auth_user();
$company_id = (int)$u['company_id'];

$pdo = $GLOBALS['pdo'] ?? null;
if(!$pdo){ require __DIR__ . '/core/db.php'; }

require_once __DIR__ . '/helpers/activity_helper.php';

/* ------- Tables/Columns (as per your schema) ----------------------------- */
const T_BEAM     = 'beam_entry';      // company_id, beam_no, entry_date, quality_id, employee_id
const T_PASARIA  = 'pasaria_entry';   // primary_beam_no, secondary_beam_no
const T_PISSING  = 'pissing_entry';   // beam_no

/* Helper: table columns map (lowercased) */
function cols(PDO $pdo, string $t): array {
  try {
    $r=$pdo->query("SHOW COLUMNS FROM `$t`")->fetchAll(PDO::FETCH_ASSOC);
    $m=[]; foreach($r as $c){ $m[strtolower($c['Field'])]=1; } return $m;
  } catch(Throwable $e){ return []; }
}

/* -------------- Ensure target table + helpful indexes ------------------- */
$pdo->exec("
CREATE TABLE IF NOT EXISTS beam_stock (
  id               BIGINT PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT NOT NULL,
  beam_no          VARCHAR(60) NOT NULL,
  quality_id       BIGINT NULL,
  quality_name     VARCHAR(150) NULL,
  warper_id        BIGINT NULL,
  warper_name      VARCHAR(150) NULL,
  first_entry_date DATE NULL,
  refreshed_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                      ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

/* Add missing cols/indexes defensively (handles older partial tables) */
$bc = cols($pdo,'beam_stock'); $adds=[];
foreach ([
  'company_id BIGINT NOT NULL',
  'beam_no VARCHAR(60) NOT NULL',
  'quality_id BIGINT NULL',
  'quality_name VARCHAR(150) NULL',
  'warper_id BIGINT NULL',
  'warper_name VARCHAR(150) NULL',
  'first_entry_date DATE NULL',
  'refreshed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
] as $def){
  $name = strtolower(strtok($def,' '));
  if(!isset($bc[$name])) $adds[] = "ADD COLUMN $def";
}
if ($adds) { try { $pdo->exec("ALTER TABLE beam_stock ".implode(',', $adds)); } catch(Throwable $e){} }
try { $pdo->exec("ALTER TABLE beam_stock ADD UNIQUE KEY uq_company_beam (company_id, beam_no)"); } catch(Throwable $e){}
try { $pdo->exec("ALTER TABLE beam_stock ADD KEY idx_company (company_id)"); } catch(Throwable $e){}
try { $pdo->exec("ALTER TABLE beam_stock ADD KEY idx_quality (company_id, quality_id)"); } catch(Throwable $e){}
try { $pdo->exec("ALTER TABLE beam_stock ADD KEY idx_warper  (company_id, warper_id)"); } catch(Throwable $e){}

/* Helpful indexes on sources (ignore if exist) */
try { $pdo->exec("CREATE INDEX idx_be_company_beamno ON ".T_BEAM."(company_id, beam_no)"); } catch(Throwable $e){}
try { $pdo->exec("CREATE INDEX idx_pe_primary      ON ".T_PASARIA."(company_id, primary_beam_no)"); } catch(Throwable $e){}
try { $pdo->exec("CREATE INDEX idx_pe_secondary    ON ".T_PASARIA."(company_id, secondary_beam_no)"); } catch(Throwable $e){}
try { $pdo->exec("CREATE INDEX idx_pi_beam         ON ".T_PISSING."(company_id, beam_no)"); } catch(Throwable $e){}

/* -------------------- Refresh for this company only --------------------- */
try {
  $pdo->beginTransaction();

// Clear existing stock rows for this company
  $del = $pdo->prepare("DELETE FROM beam_stock WHERE company_id=?");
  $del->execute([$company_id]);

// Insert current STOCK (ONLY_FULL_GROUP_BY-safe with MIN/MAX)
  $ins = $pdo->prepare("
    INSERT INTO beam_stock
      (company_id, beam_no, quality_id, quality_name, warper_id, warper_name, first_entry_date)
    SELECT
      b.company_id,
      b.beam_no,
      MAX(b.quality_id)                              AS quality_id,
      MAX(bq.name)                                   AS quality_name,
      MAX(b.employee_id)                             AS warper_id,
      MAX(cem.name)                                  AS warper_name,
      MIN(b.entry_date)                              AS first_entry_date
    FROM ".T_BEAM." b
    LEFT JOIN ".T_PASARIA." pe
           ON pe.company_id = b.company_id
          AND (pe.primary_beam_no = b.beam_no OR pe.secondary_beam_no = b.beam_no)
    LEFT JOIN ".T_PISSING." pi
           ON pi.company_id = b.company_id
          AND pi.beam_no = b.beam_no
    LEFT JOIN beam_qualities bq
           ON bq.company_id = b.company_id AND bq.id = b.quality_id
    LEFT JOIN company_employee_master cem
           ON cem.company_id = b.company_id AND cem.id = b.employee_id
    WHERE b.company_id = ?
      AND b.beam_no IS NOT NULL
      AND TRIM(b.beam_no) <> ''
      AND LOWER(TRIM(b.beam_no)) NOT IN ('pending','number pending','na','n/a')
      AND pe.id IS NULL
      AND pi.id IS NULL
    GROUP BY b.company_id, b.beam_no
    ORDER BY MIN(b.entry_date)
  ");
  $ins->execute([$company_id]);

$pdo->commit();
  activity_update('beam', 'beam_stock_refresh', 0, 'Refreshed beam stock snapshot');
  $msg = "Beam Stock refreshed. Rows: ".$ins->rowCount();
} catch (Throwable $e) {
  if ($pdo->inTransaction()) $pdo->rollBack();
  $msg = "Refresh failed: ".$e->getMessage();
}

header("Location: /erp/beam_stock.php?msg=".urlencode($msg));
exit;