### CODE GATHERER OUTPUT ###
# generated: Fri Oct 31 12:13:02 PM EDT 2025
# directory: /root/uac_system

===== DIRECTORY STRUCTURE =====
   collect_cms_alarms.php
   networkList1.txt
   cms_session.php
      global.xml
   collect_smx_alarms.php
   collect_alarms_unified.php
   gathered_code_compendium.txt

===== BEGIN FILE CONTENTS =====

----------------------------------------
File: /root/uac_system/collect_cms_alarms.php
Modified: 2025-10-30 22:22:34.354624761 -0400
----------------------------------------
#!/usr/bin/env php
<?php
declare(strict_types=1);

ini_set('display_errors','0');
error_reporting(E_ALL);

$startTime = microtime(true);

/* ---------- CLI ---------- */
$opts   = getopt('', ['only::','networks::','cms-url::','cookie::','pretty::','debug::','raw::','tz::']);
$ONLY   = isset($opts['only']) ? (string)$opts['only'] : '';
$NETS   = isset($opts['networks']) ? (string)$opts['networks'] : '/root/uac_system/networkList1.txt';
$CMSURL = isset($opts['cms-url']) ? (string)$opts['cms-url'] : 'http://192.168.10.1:18080/cmsexc/ex/netconf';
$COOKIE = isset($opts['cookie']) ? (string)$opts['cookie'] : '';
$PRETTY = array_key_exists('pretty',$opts);
$DEBUG  = array_key_exists('debug',$opts);
$RAW    = array_key_exists('raw',$opts);
$TZ     = isset($opts['tz']) ? (string)$opts['tz'] : 'America/Indiana/Vincennes';

date_default_timezone_set($TZ);
mb_internal_encoding('UTF-8');

if (!function_exists('array_is_list')) {
    function array_is_list(array $a): bool { $i=0; foreach($a as $k=>$_){ if($k!==$i++) return false; } return true; }
}

function now(): string { return date('Y-m-d H:i:s'); }
function j(array $m): string { return json_encode($m, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE); }
function loge(string $msg, array $ctx=[]): void { fwrite(STDERR, j(['ts'=>now(),'lvl'=>'ERROR','msg'=>$msg]+$ctx).PHP_EOL); }
function logw(string $msg, array $ctx=[]): void { fwrite(STDERR, j(['ts'=>now(),'lvl'=>'WARN','msg'=>$msg]+$ctx).PHP_EOL); }

/* ---------- networks ---------- */
if (!is_file($NETS)) {
    loge('network list not found',['path'=>$NETS]);
    echo json_encode(['data'=>[],'summary'=>['networks'=>0,'alarms_total'=>0]], JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE|($PRETTY?JSON_PRETTY_PRINT:0)).PHP_EOL;
    exit(2);
}
$nets=[];
foreach (file($NETS, FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES) as $ln) {
    $ln=trim($ln);
    if ($ln==='' || $ln[0]==='#') continue;
    $nets[]=$ln;
}
$nets = array_values(array_unique($nets));
if ($ONLY!=='') { $nets = array_values(array_filter($nets, fn($n)=>$n===$ONLY)); }
if (!$nets) {
    echo json_encode(['data'=>[],'summary'=>['networks'=>0,'alarms_total'=>0]], JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE|($PRETTY?JSON_PRETTY_PRINT:0)).PHP_EOL;
    exit(0);
}

/* ---------- include persistent CMS session ---------- */
require __DIR__ . '/cms_session.php';  // exposes $sessionid and $session_status
if ($DEBUG) fwrite(STDERR, "CMS Session Status: {$session_status}, Session ID: {$sessionid}\n");

$jsess = $COOKIE !== '' ? $COOKIE : '0C76878D1FB75F52F65346AFF7BFDD45';

/* ---------- SOAP ---------- */
function soapBody(string $network, string $sessionid, ?string $cursorOnt=null): string {
    $cursor = $cursorOnt
        ? '<start-instance><type>Ont</type><id><ont>'.htmlspecialchars($cursorOnt,ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8').'</ont></id></start-instance>'
        : '';
    return <<<XML
<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope">
  <soapenv:Body>
    <rpc message-id="175" nodename="{$network}" timeout="35000" username="alarmUser" sessionid="{$sessionid}">
      <action>
        <action-type>show-alarms</action-type>
        <action-args>{$cursor}</action-args>
      </action>
    </rpc>
  </soapenv:Body>
</soapenv:Envelope>
XML;
}

function cmsPost(string $url, string $jsession, string $body, bool $debug=false): string {
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER=>true,
        CURLOPT_POST=>true,
        CURLOPT_POSTFIELDS=>$body,
        CURLOPT_HTTPHEADER=>[
            'Content-Type: text/soap+xml; charset=utf-8',
            'User-Agent: perl-test-script',
            'Cookie: JSESSIONID='.$jsession,
        ],
        CURLOPT_TIMEOUT=>45,
    ]);
    $resp = curl_exec($ch);
    $err  = curl_error($ch);
    $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
    curl_close($ch);
    if ($resp===false) throw new RuntimeException("curl: $err");
    if ($code>=400) throw new RuntimeException("http $code");
    if ($debug && stripos($resp,'<rpc-error')!==false) { logw('rpc-error in response',['snippet'=>substr($resp,0,300)]); }
    return $resp;
}

/* ---------- XML -> array ---------- */
function nodeToArray(DOMNode $node): mixed {
    if (!$node->hasChildNodes()) return null;
    $hasElementChild=false; foreach ($node->childNodes as $c){ if($c instanceof DOMElement){$hasElementChild=true;break;}}
    if (!$hasElementChild) return trim($node->textContent);
    $out=[];
    foreach ($node->childNodes as $child) {
        if (!($child instanceof DOMElement)) continue;
        $key=$child->localName; $val=nodeToArray($child);
        if (array_key_exists($key,$out)) {
            if (!is_array($out[$key]) || array_is_list($out[$key])===false) $out[$key]=[$out[$key]];
            $out[$key][]=$val;
        } else { $out[$key]=$val; }
    }
    if ($node->attributes && $node->attributes->length>0) {
        $attrs=[]; foreach ($node->attributes as $attr){ $attrs[$attr->localName]=$attr->value; }
        $out['@attrs']=$attrs;
    }
    return $out;
}

/* ---------- occur-time normalization ---------- */
function normalizeOccurTimes(mixed &$v): void {
    if (!is_array($v)) return;
    if (!array_is_list($v)) {
        if (isset($v['occur-time']) && is_scalar($v['occur-time'])) {
            $secRaw=(string)$v['occur-time'];
            $sec=ctype_digit($secRaw)?(int)$secRaw:(int)preg_replace('/\D+/','',$secRaw);
            try {
                $dt=(new DateTimeImmutable('@'.$sec))->setTimezone(new DateTimeZone(date_default_timezone_get()));
                $v['occur-time-string']=$dt->format('Y/m/d H:i:s');
            } catch(Throwable $e){}
        }
    }
    foreach ($v as &$vv) if (is_array($vv)) normalizeOccurTimes($vv);
}

/* ---------- parse alarms ---------- */
function parseFullAlarms(string $xml, bool $includeRaw): array {
    $out=[]; libxml_use_internal_errors(true);
    $doc=new DOMDocument(); if(!$doc->loadXML($xml)) return $out;
    $xp=new DOMXPath($doc); $alarms=$xp->query('//*[local-name()="alarm"]');
    if(!$alarms) return $out;
    foreach($alarms as $a){ $row=nodeToArray($a); normalizeOccurTimes($row);
        if($includeRaw) $row['_raw_xml']=trim($doc->saveXML($a));
        $out[]=$row;
    }
    return $out;
}

/* ---------- pagination cursor ---------- */
function paginationCursor(string $xml): ?string {
    libxml_use_internal_errors(true);
    $doc=new DOMDocument();
    if(!$doc->loadXML($xml)) return null;
    $xp=new DOMXPath($doc);
    $n=$xp->query('(//*[local-name()="alarm"])[last()]//*[local-name()="ont"]');
    return $n && $n->length ? trim($n->item(0)->textContent) : null;
}

/* ---------- Postgres Connection ---------- */
$pg_conn = pg_connect('host=192.168.10.1 port=5432 dbname=postgres user=cmsuser password=cmsuser');
if (!$pg_conn) {
    fwrite(STDERR, "Could not connect to Postgres!\n");
    exit(3);
}
pg_prepare($pg_conn, "mini_lookup",
    "SELECT subscr_id, node_straid, pon_descr, model, serno, mfg_serno
     FROM subscriber_mini_view
     WHERE node_straid=$1 LIMIT 1");

/* ---------- GIS SQL Server Connection ---------- */
$gis_server = "192.168.6.6";
$gis_conn = sqlsrv_connect($gis_server, [
  "Database" => "gs18526_master",
  "Uid" => "c2kc",
  "PWD" => "9Z1pp3r!"
]);
if (!$gis_conn) {
    fwrite(STDERR, "Warning: Could not connect to GIS SQL Server\n");
}
// $gis_query = "SELECT TOP 1 latitude, longitude, gs_ivue_map_coordinate_1 FROM [dbo].[GS_SUBSCRIBER] WHERE Material_Co LIKE ?";
$gis_query = "
  SELECT TOP 1 latitude, longitude, gs_ivue_map_coordinate_1
  FROM [dbo].[GS_SUBSCRIBER]
  WHERE LOWER(RTRIM(LTRIM(gs_type))) LIKE ?
";


/* ---------- fetch alarms ---------- */
function fetchAllForNetwork(string $url, string $jsess, string $sessionid, string $network, bool $debug, bool $includeRaw): array {
    $all=[]; $seen=[]; $cursor=null;
    do {
        $body=soapBody($network,$sessionid,$cursor);
        $xml=cmsPost($url,$jsess,$body,$debug);
        if(stripos($xml,'<error-severity>error</error-severity>')!==false
            || stripos($xml,'Session does not exist')!==false) break;
        $rows=parseFullAlarms($xml,$includeRaw);
        foreach($rows as $r){ $h=sha1(json_encode($r)); if(!isset($seen[$h])){$seen[$h]=1; $all[]=$r;} }
        $next=paginationCursor($xml);
        $cursor=$next!==$cursor?$next:null;
        usleep(150000);
    } while($cursor!==null);
    return $all;
}

/* ---------- main run ---------- */
$data=[]; 
$summary=['networks'=>count($nets),'alarms_total'=>0,'by_network'=>[]];

foreach($nets as $n){
    try {
        $alarms=fetchAllForNetwork($CMSURL,$jsess,$sessionid,$n,$DEBUG,$RAW);
        foreach($alarms as &$a){
            $ont=$a['object']['id']['ont']??null;
            $ids=$a['sec-object']['id']??[];
            if(isset($ids['shelf'],$ids['card'],$ids['gponport']) && $ont){
                $json_node_straid=$n.'-'.$ids['shelf'].'-'.$ids['card'].'-'.$ids['gponport'].'-'.$ont;
                $res=pg_execute($pg_conn,"mini_lookup",[$json_node_straid]);
                if($res && pg_num_rows($res)>0){
                    $row=pg_fetch_assoc($res);
                    foreach(['subscr_id','node_straid','pon_descr','model','serno','mfg_serno'] as $k)
                        $a[$k]=$row[$k]??null;
                } else {
                    foreach(['subscr_id','node_straid','pon_descr','model','serno','mfg_serno'] as $k)
                        $a[$k]=null;
                }
                $a['json_node_straid']=$json_node_straid;

                // GIS lookup using serno -> Material_Co
                $serno = $a['serno'] ?? null;

                if ($serno && $gis_conn) {                  
                    // $stmt = sqlsrv_query($gis_conn, $gis_query, [$serno]);
                        $param = ['%' . strtolower($serno) . '%'];
                        $stmt = sqlsrv_query($gis_conn, $gis_query, $param);

                    if ($stmt && sqlsrv_has_rows($stmt)) {
                        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
                        $a['latitude'] = $row['latitude'] ?? null;
                        $a['longitude'] = $row['longitude'] ?? null;
                        $a['ivue_address'] = $row['gs_ivue_map_coordinate_1'] ?? null;
                    } else {
                        $a['latitude'] = $a['longitude'] = $a['ivue_address'] = null;
                    }
                    if ($stmt) sqlsrv_free_stmt($stmt);
                } else {
                    $a['latitude'] = $a['longitude'] = $a['ivue_address'] = null;
                }

            } else {
                $a['json_node_straid']=null;
            }
        }
        unset($a);
        $data[]=['network'=>$n,'count'=>count($alarms),'alarms'=>$alarms];
        $summary['alarms_total']+=count($alarms);
    } catch(Throwable $e){
        loge('fetch_failed',['network'=>$n,'err'=>$e->getMessage()]);
        $data[]=['network'=>$n,'error'=>$e->getMessage(),'count'=>0,'alarms'=>[]];
    }
}

if ($gis_conn) sqlsrv_close($gis_conn);

/* ---------- output ---------- */
$flags=JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE;
if($PRETTY)$flags|=JSON_PRETTY_PRINT;
echo json_encode(['data'=>$data,'summary'=>$summary],$flags),PHP_EOL;

/* ---------- MySQL insert + concurrency patch ---------- */
$mysql = new mysqli('127.0.0.1', 'c2kc', 'P@ssw0rd3211', 'minocDB');
if ($mysql->connect_errno) {
    fwrite(STDERR, "MySQL connection failed: " . $mysql->connect_error . "\n");
    exit(4);
}
$mysql->set_charset('utf8mb4');
$mysql->autocommit(true);
$mysql->options(MYSQLI_OPT_CONNECT_TIMEOUT,5);
$mysql->options(MYSQLI_OPT_READ_TIMEOUT,30);

foreach ($data as $net) {
    $network = $net['network'] ?? null;
    if (!$network) continue;
    $startNet = microtime(true);
    fwrite(STDERR, "[".now()."] Begin MySQL update for {$network}\n");

    $stmt = $mysql->prepare("DELETE QUICK FROM standing_alarms WHERE network=?");
    $stmt->bind_param('s', $network);
    $stmt->execute();
    $deleted = $stmt->affected_rows;
    $stmt->close();
    fwrite(STDERR, "  Deleted {$deleted} old rows for {$network}\n");

    $alarms = $net['alarms'] ?? [];
    if (empty($alarms)) {
        fwrite(STDERR, "  No new alarms for {$network}\n");
        continue;
    }

    $ins = $mysql->prepare("
        INSERT INTO standing_alarms
        (network, alarm_type, occur_time, subscr_id, node_straid, pon_descr, model, serno, mfg_serno, latitude, longitude, ivue_address)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    if(!$ins){ fwrite(STDERR, "  Prepare failed for {$network}: ".$mysql->error."\n"); continue; }

    $inserted = 0;
    foreach ($alarms as $a) {
        $alarmType = $a['alarm-type'] ?? ($a['alarmId'] ?? 'unknown');
        $occur     = $a['occur-time-string'] ?? null;
        $subscr_id = $a['subscr_id'] ?? null;
        $node_straid = $a['node_straid'] ?? null;
        $pon_descr = $a['pon_descr'] ?? null;
        $model     = $a['model'] ?? null;
        $serno     = $a['serno'] ?? null;
        $mfg_serno = $a['mfg_serno'] ?? null;
        $latitude  = $a['latitude'] ?? null;
        $longitude = $a['longitude'] ?? null;
        $ivue_addr = $a['ivue_address'] ?? null;

        if(!$ins->bind_param('ssssssssssss', $network,$alarmType,$occur,$subscr_id,$node_straid,$pon_descr,$model,$serno,$mfg_serno,$latitude,$longitude,$ivue_addr)){
            fwrite(STDERR,"    Bind failed for {$network}: ".$ins->error."\n");
            continue;
        }
        if(!$ins->execute()){
            fwrite(STDERR,"    Insert failed for {$network}: ".$ins->error."\n");
            continue;
        }
        $inserted++;
    }
    $ins->close();
    $elapsed = number_format(microtime(true) - $startNet, 3);
    fwrite(STDERR, "  Inserted {$inserted} new rows for {$network} in {$elapsed}s\n");

    unset($stmt, $ins);
    gc_collect_cycles();
}
$mysql->close();
unset($mysql);
gc_collect_cycles();

/* ---------- Runtime Summary ---------- */
$endTime = microtime(true);
$duration = number_format($endTime - $startTime, 3);
fwrite(STDERR, "Execution time: {$duration} seconds\n");
exit(0);


----------------------------------------
File: /root/uac_system/cms_session.php
Modified: 2025-10-30 22:17:21.381579387 -0400
----------------------------------------
<?php
/****************************************************
 * cms_session.php
 * Persistent CMS session manager (stand-alone include)
 * Requires table: minocDB.cms_session_store (id=1 row)
 * Exports: $sessionid, $session_status
 ****************************************************/

error_reporting(0);

/* ---------- CONFIG ---------- */
$db = [
    'host' => '127.0.0.1',
    'user' => 'c2kc',
    'pass' => 'P@ssw0rd3211',
    'name' => 'minocDB'
];
$cms = [
    'url'      => 'http://192.168.10.1:18080/cmsexc/ex/netconf',
    'user'     => 'alarmUser',
    'password' => '9Z1pp3r!'
];

/* ---------- DB CONNECT ---------- */
$con = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);
if ($con->connect_errno) {
    die('DB connection failed: '.$con->connect_error);
}

/* ---------- GET CURRENT SESSION ---------- */
$res = $con->query("SELECT sessionid FROM cms_session_store WHERE id=1 LIMIT 1");
$row = $res?->fetch_assoc();
$sessionid = trim($row['sessionid'] ?? '');

/* ---------- HELPER: POST SOAP ---------- */
function cms_post(string $url, string $xml): string {
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => $xml,
        CURLOPT_HTTPHEADER => ['Content-Type: text/soap+xml; charset=utf-8'],
        CURLOPT_TIMEOUT => 10
    ]);
    $resp = curl_exec($ch);
    curl_close($ch);
    return $resp ?: '';
}

/* ---------- CHECK SESSION VALIDITY ---------- */
$dummyXml = <<<XML
<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope">
  <soapenv:Body>
    <rpc message-id="1" nodename="NTWK-PTWM_E7" username="{$cms['user']}" sessionid="{$sessionid}">
      <get>
        <filter type="subtree">
          <top>
            <object><type>Ont</type><id><ont>1</ont></id><attr-list>model</attr-list></object>
          </top>
        </filter>
      </get>
    </rpc>
  </soapenv:Body>
</soapenv:Envelope>
XML;

$response = cms_post($cms['url'], $dummyXml);
$is_bad = (
    stripos($response, '<error-severity>error</error-severity>') !== false ||
    stripos($response, 'Session does not exist') !== false ||
    $sessionid === ''
);

/* ---------- RELOGIN IF NEEDED ---------- */
if ($is_bad) {
    $loginXml = <<<XML
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <auth message-id="1">
      <login>
        <UserName>{$cms['user']}</UserName>
        <Password>{$cms['password']}</Password>
      </login>
    </auth>
  </soapenv:Body>
</soapenv:Envelope>
XML;

    $loginResp = cms_post($cms['url'], $loginXml);
    if (preg_match('/<SessionId>([^<]+)<\/SessionId>/', $loginResp, $m)) {
        $sessionid = trim($m[1]);
        $con->query("UPDATE cms_session_store 
                     SET sessionid='".$con->real_escape_string($sessionid)."',
                         status='renewed', note='auto relogin success' 
                     WHERE id=1");
        $session_status = 'renewed';
    } else {
        $session_status = 'login_failed';
        $con->query("UPDATE cms_session_store 
                     SET status='login_failed', note='login failed to obtain new session' 
                     WHERE id=1");
    }
} else {
    $session_status = 'alive';
    $con->query("UPDATE cms_session_store 
                 SET status='alive', note='session verified ok' 
                 WHERE id=1");
}

/* ---------- CLEANUP ---------- */
$con->close();

/* ---------- EXPORT VARIABLES ---------- */
$GLOBALS['sessionid'] = $sessionid;
$GLOBALS['session_status'] = $session_status;
// echo $sessionid;
?>


----------------------------------------
File: /root/uac_system/config/global.xml
Modified: 2025-10-30 22:20:40.394244093 -0400
----------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<config>
  <timezone>America/Indiana/Vincennes</timezone>

  <mysql host="127.0.0.1"
         user="c2kc"
         pass="P@ssw0rd3211"
         db="minocDB"/>

  <gis server="192.168.6.6"
       database="gs18526_master"
       user="c2kc"
       pass="9Z1pp3r!"/>

  <mongo uri="mongodb://activateread:activateread@192.168.10.2:4000/activate?authMechanism=SCRAM-SHA-1"
         alarms="activate.emsActiveAlarm_view"
         subs="activate.subscriber_view"/>

  <cms collector="/root/uac_system/collect_cms_alarms.php"/>
  <smx collector="/root/uac_system/collect_smx_alarms.php"/>
</config>


----------------------------------------
File: /root/uac_system/collect_smx_alarms.php
Modified: 2025-10-31 01:12:40.969848612 -0400
----------------------------------------
#!/usr/bin/env php
<?php
declare(strict_types=1);
/*****************************************************
 * collect_smx_alarms.php
 * SMx alarm collector (MongoDB → MySQL)
 * Inserts into standing_alarms with node_straid = DeviceName-LinkedPon-OntId
 * Includes ONTModel for model field
 *****************************************************/

ini_set('display_errors','0');
error_reporting(E_ALL);
$startTime = microtime(true);

/* ---------- CONFIG ---------- */
$cfgPath = __DIR__ . '/config/global.xml';
if (!file_exists($cfgPath)) {
    fwrite(STDERR, "Missing config file: {$cfgPath}\n");
    exit(1);
}
$xml = simplexml_load_file($cfgPath);
function xmlAttrs(SimpleXMLElement $n): array { $o=[]; foreach($n->attributes() as $k=>$v){$o[$k]=(string)$v;} return $o; }
$cfg = [
    'tz'    => (string)$xml->timezone,
    'mysql' => xmlAttrs($xml->mysql),
    'gis'   => xmlAttrs($xml->gis),
    'mongo' => xmlAttrs($xml->mongo)
];
date_default_timezone_set($cfg['tz']);
mb_internal_encoding('UTF-8');

/* ---------- LOGGING ---------- */
function now(): string { return date('Y-m-d H:i:s'); }
function j(array $m): string { return json_encode($m, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE); }
function loge(string $m, array $c=[]): void { fwrite(STDERR, j(['ts'=>now(),'lvl'=>'ERROR','msg'=>$m]+$c).PHP_EOL); }
function logi(string $m, array $c=[]): void { fwrite(STDERR, j(['ts'=>now(),'lvl'=>'INFO','msg'=>$m]+$c).PHP_EOL); }

/* ---------- CONNECT ---------- */
$mysql = new mysqli($cfg['mysql']['host'], $cfg['mysql']['user'], $cfg['mysql']['pass'], $cfg['mysql']['db']);
if ($mysql->connect_errno) { loge('MySQL connection failed',['err'=>$mysql->connect_error]); exit(2); }
$mysql->set_charset('utf8mb4');
$mysql->autocommit(true);

$gis_conn = sqlsrv_connect($cfg['gis']['server'], [
    'Database'=>$cfg['gis']['database'],
    'Uid'=>$cfg['gis']['user'],
    'PWD'=>$cfg['gis']['pass']
]);
if (!$gis_conn) loge('Could not connect to GIS SQL Server');

/* ---------- HELPERS ---------- */
function convertMongoDate($obj): ?string {
    $a = json_decode(json_encode($obj), true);
    if (isset($a['$date']['$numberLong'])) return date('Y/m/d H:i:s', (int)($a['$date']['$numberLong']/1000));
    if (isset($a['$numberLong']))         return date('Y/m/d H:i:s', (int)($a['$numberLong']/1000));
    return null;
}
function extractSerial(string $s): ?string {
    return preg_match('/SerialNo=([A-Za-z0-9]+)/', $s, $m) ? $m[1] : null;
}
function gisLookup($conn, ?string $serial): array {
    if(!$conn || !$serial) return [null,null,null];
    $param=['%'.strtolower($serial).'%'];
    $sql="SELECT TOP 1 latitude,longitude,gs_ivue_map_coordinate_1
          FROM [dbo].[GS_SUBSCRIBER]
          WHERE LOWER(RTRIM(LTRIM(gs_type))) LIKE ?";
    $stmt=sqlsrv_query($conn,$sql,$param);
    if($stmt && sqlsrv_has_rows($stmt)){
        $r=sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC);
        return [$r['latitude']??null,$r['longitude']??null,$r['gs_ivue_map_coordinate_1']??null];
    }
    if($stmt)sqlsrv_free_stmt($stmt);
    return [null,null,null];
}

/* ---------- NODE STRAID BUILDER ---------- */
function buildNodeStraid(array $subDoc, string $device): string {
    $linked = str_replace('/', '-', $subDoc['LinkedPon'] ?? '');
    $ont    = $subDoc['OntId'] ?? '';
    if ($device && $linked && $ont) return "{$device}-{$linked}-{$ont}";
    return $device ?: 'UNKNOWN';
}

/* ---------- MONGO ---------- */
$mgr = new MongoDB\Driver\Manager($cfg['mongo']['uri']);
$cursor = $mgr->executeQuery($cfg['mongo']['alarms'], new MongoDB\Driver\Query([]));
$docs = iterator_to_array($cursor, false);
$data = [];

foreach ($docs as $d) {
    $a = (array)$d;
    $device = $a['Device Name'] ?? 'UNKNOWN';
    $atype  = $a['Alarm Name'] ?? 'unknown';
    $occur  = convertMongoDate($a['EMS Time'] ?? null);
    $serno  = extractSerial($a['Details'] ?? '');
    $mfg    = $serno;

    // --- subscriber lookup ---
    $subscr = null;
    $node_straid = null;
    $model = null;
    if ($serno) {
        $filter=['ONTSerialNo'=>['$regex'=>$serno,'$options'=>'i']];
        $opts=['limit'=>1,'projection'=>[
            'SubscriberName'=>1,
            'LinkedPon'=>1,
            'OntId'=>1,
            'DeviceName'=>1,
            'ONTModel'=>1
        ]];
        $q=new MongoDB\Driver\Query($filter,$opts);
        $cur=$mgr->executeQuery($cfg['mongo']['subs'],$q);
        $r=iterator_to_array($cur,false);
        if($r){
            $doc = (array)$r[0];
            $subscr=is_array($doc['SubscriberName'])?implode(' ',$doc['SubscriberName']):(string)$doc['SubscriberName'];
            $node_straid = buildNodeStraid($doc, $device);
            $model = $doc['ONTModel'] ?? null;
        }
    }

    [$lat,$lon,$ivue]=gisLookup($gis_conn,$serno);
    $data[$device][]=[
        'network'=>$device,
        'alarm_type'=>$atype,
        'occur_time'=>$occur ?: date('Y/m/d H:i:s'),
        'subscr_id'=>$subscr,
        'node_straid'=>$node_straid,
        'pon_descr'=>null,
        'model'=>$model,
        'serno'=>$serno,
        'mfg_serno'=>$mfg,
        'latitude'=>$lat,
        'longitude'=>$lon,
        'ivue_address'=>$ivue
    ];
}

/* ---------- MYSQL INSERT ---------- */
foreach ($data as $network => $alarms) {
    logi("Begin MySQL update for {$network}");
    $stmt = $mysql->prepare("DELETE QUICK FROM standing_alarms WHERE network=?");
    $stmt->bind_param('s', $network);
    $stmt->execute();
    $stmt->close();

    if (empty($alarms)) {
        logi("  No new alarms for {$network}");
        continue;
    }

    $ins = $mysql->prepare("
        INSERT INTO standing_alarms
        (network, alarm_type, occur_time, subscr_id, node_straid, pon_descr, model, serno, mfg_serno, latitude, longitude, ivue_address)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    if(!$ins){ loge("Prepare failed for {$network}",['err'=>$mysql->error]); continue; }

    $inserted = 0;
    foreach ($alarms as $a) {
        $ins->bind_param(
            'ssssssssssss',
            $network,
            $a['alarm_type'],
            $a['occur_time'],
            $a['subscr_id'],
            $a['node_straid'],
            $a['pon_descr'],
            $a['model'],
            $a['serno'],
            $a['mfg_serno'],
            $a['latitude'],
            $a['longitude'],
            $a['ivue_address']
        );
        if($ins->execute()) $inserted++;
        else loge("Insert failed for {$network}",['err'=>$ins->error]);
    }
    $ins->close();
    logi("  Inserted {$inserted} new rows for {$network}");
}

/* ---------- CLEANUP ---------- */
if($gis_conn)sqlsrv_close($gis_conn);
$mysql->close();

$dur = number_format(microtime(true) - $startTime, 3);
logi("Execution time: {$dur}s");
exit(0);


----------------------------------------
File: /root/uac_system/collect_alarms_unified.php
Modified: 2025-10-30 22:17:21.713580495 -0400
----------------------------------------
#!/usr/bin/env php
<?php
declare(strict_types=1);
/*****************************************************
 * collect_alarms_unified.php
 * Orchestrator for CMS + SMx collectors
 *****************************************************/

ini_set('display_errors','0');
error_reporting(E_ALL);
$startTime = microtime(true);

/* ---------- CONFIG ---------- */
$cfgPath = __DIR__.'/config/global.xml';
if(!file_exists($cfgPath)){fwrite(STDERR,"Missing {$cfgPath}\n");exit(1);}
$xml=simplexml_load_file($cfgPath);
function xmlAttrs(SimpleXMLElement $n):array{$o=[];foreach($n->attributes() as$k=>$v){$o[$k]=(string)$v;}return$o;}
$cfg=[
 'tz'=>(string)$xml->timezone,
 'mysql'=>xmlAttrs($xml->mysql),
 'gis'=>xmlAttrs($xml->gis),
 'mongo'=>xmlAttrs($xml->mongo),
 'cms'=>xmlAttrs($xml->cms),
 'smx'=>xmlAttrs($xml->smx)
];
date_default_timezone_set($cfg['tz']);
mb_internal_encoding('UTF-8');

/* ---------- CLI ---------- */
$opts=getopt('', ['cms','smx','pretty','debug']);
$RUN_CMS=array_key_exists('cms',$opts);
$RUN_SMX=array_key_exists('smx',$opts);
$PRETTY=array_key_exists('pretty',$opts);
$DEBUG=array_key_exists('debug',$opts);
if(!$RUN_CMS && !$RUN_SMX){
 fwrite(STDERR,"Usage: php collect_alarms_unified.php --cms|--smx|--cms --smx\n");
 exit(1);
}

/* ---------- LOG ---------- */
function now():string{return date('Y-m-d H:i:s');}
function j(array$m):string{return json_encode($m,JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);}
function logi(string$m,array$c=[]):void{fwrite(STDERR,j(['ts'=>now(),'lvl'=>'INFO','msg'=>$m]+$c).PHP_EOL);}
function loge(string$m,array$c=[]):void{fwrite(STDERR,j(['ts'=>now(),'lvl'=>'ERROR','msg'=>$m]+$c).PHP_EOL);}

/* ---------- RUNNERS ---------- */
function runCollector(string $file,bool $debug=false):array{
    if(!file_exists($file)){loge("Collector missing",['path'=>$file]);return[];}
    exec("/usr/bin/php ".escapeshellarg($file)." 2>&1",$out,$rc);
    logi("Collector finished",['path'=>$file,'code'=>$rc]);
    if($debug)foreach($out as$ln)fwrite(STDERR,"[$file] $ln\n");
    $joined=implode("\n",$out);
    if(preg_match('/(\{.*\})\s*$/s',$joined,$m))$json=$m[1];else$json=$joined;
    $d=json_decode($json,true);
    return is_array($d)?$d:[];
}

/* ---------- MAIN ---------- */
$summary=[];
if($RUN_CMS){logi("Running CMS collector");$summary['cms']=runCollector($cfg['cms']['collector'],$DEBUG);}
if($RUN_SMX){logi("Running SMx collector");$summary['smx']=runCollector($cfg['smx']['collector'],$DEBUG);}

$flags=JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE;
if($PRETTY)$flags|=JSON_PRETTY_PRINT;
echo json_encode(['summary'=>$summary],$flags),PHP_EOL;

$dur=number_format(microtime(true)-$startTime,3);
fwrite(STDERR,"Execution time: {$dur}s\n");
exit(0);