clqms-be/app/Models/Test/TestMapModel.php

<?php

namespace App\Models\Test;

use App\Models\BaseModel;

class TestMapModel extends BaseModel {

  private function getEntityTypes(): array {
    $json = file_get_contents(APPPATH . 'Libraries/Data/entity_type.json');
    $data = json_decode($json, true);
    $types = [];
    foreach ($data['values'] as $item) {
      $types[$item['key']] = $item['key'];
    }
    return $types;
  }
  protected $table      = 'testmap';
  protected $primaryKey = 'TestMapID';
  protected $allowedFields = [
    'HostType',
    'HostID',
    'ClientType',
    'ClientID',
    'CreateDate',
    'EndDate'
  ];
  
  protected $useTimestamps = true;
  protected $createdField = 'CreateDate';
  protected $updatedField = '';
  protected $useSoftDeletes = true;
  protected $deletedField = "EndDate";

  /**
   * Get all test mappings with names
   */
  public function getUniqueGroupings() {
    $db = $this->db;
    $types = $this->getEntityTypes();
    
    $siteType = $types['SITE'] ?? 'SITE';
    $wsType = $types['WST'] ?? 'WST';
    $instType = $types['INST'] ?? 'INST';
    
    $sql = "
      SELECT 
        tm.TestMapID,
        tm.HostType,
        tm.HostID,
        CASE 
          WHEN tm.HostType = ? THEN s.SiteName
          WHEN tm.HostType = ? THEN ws.WorkstationName
          WHEN tm.HostType = ? THEN el.InstrumentName
          ELSE tm.HostID
        END as HostName,
        tm.ClientType,
        tm.ClientID,
        CASE 
          WHEN tm.ClientType = ? THEN cs.SiteName
          WHEN tm.ClientType = ? THEN cws.WorkstationName
          WHEN tm.ClientType = ? THEN cel.InstrumentName
          ELSE tm.ClientID
        END as ClientName
      FROM testmap tm
      LEFT JOIN site s ON tm.HostType = ? AND s.SiteID = tm.HostID
      LEFT JOIN workstation ws ON tm.HostType = ? AND ws.WorkstationID = tm.HostID
      LEFT JOIN equipmentlist el ON tm.HostType = ? AND el.EID = tm.HostID
      LEFT JOIN site cs ON tm.ClientType = ? AND cs.SiteID = tm.ClientID
      LEFT JOIN workstation cws ON tm.ClientType = ? AND cws.WorkstationID = tm.ClientID
      LEFT JOIN equipmentlist cel ON tm.ClientType = ? AND cel.EID = tm.ClientID
      WHERE tm.EndDate IS NULL
    ";
    
    return $db->query($sql, [
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType
    ])->getResultArray();
  }

  public function getByIdWithNames($id) {
    $db = $this->db;
    $types = $this->getEntityTypes();

    $siteType = $types['SITE'] ?? 'SITE';
    $wsType = $types['WST'] ?? 'WST';
    $instType = $types['INST'] ?? 'INST';

    $sql = "
      SELECT
        tm.*,
        CASE
          WHEN tm.HostType = ? THEN s.SiteName
          WHEN tm.HostType = ? THEN ws.WorkstationName
          WHEN tm.HostType = ? THEN el.InstrumentName
          ELSE tm.HostID
        END as HostName,
        CASE
          WHEN tm.ClientType = ? THEN cs.SiteName
          WHEN tm.ClientType = ? THEN cws.WorkstationName
          WHEN tm.ClientType = ? THEN cel.InstrumentName
          ELSE tm.ClientID
        END as ClientName
      FROM testmap tm
      LEFT JOIN site s ON tm.HostType = ? AND s.SiteID = tm.HostID
      LEFT JOIN workstation ws ON tm.HostType = ? AND ws.WorkstationID = tm.HostID
      LEFT JOIN equipmentlist el ON tm.HostType = ? AND el.EID = tm.HostID
      LEFT JOIN site cs ON tm.ClientType = ? AND cs.SiteID = tm.ClientID
      LEFT JOIN workstation cws ON tm.ClientType = ? AND cws.WorkstationID = tm.ClientID
      LEFT JOIN equipmentlist cel ON tm.ClientType = ? AND cel.EID = tm.ClientID
      WHERE tm.TestMapID = ?
        AND tm.EndDate IS NULL
      LIMIT 1
    ";

    $rows = $db->query($sql, [
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType,
      $siteType, $wsType, $instType,
      $id,
    ])->getResultArray();

    return $rows[0] ?? null;
  }

  public function getMappingsByTestCode($testCode) {
    return $this->select('testmap.*')
        ->join('testmapdetail', 'testmapdetail.TestMapID = testmap.TestMapID', 'inner')
        ->groupStart()
            ->where('testmapdetail.HostTestCode', $testCode)
            ->orWhere('testmapdetail.ClientTestCode', $testCode)
        ->groupEnd()
        ->where('testmap.EndDate IS NULL')
        ->where('testmapdetail.EndDate IS NULL')
        ->groupBy('testmap.TestMapID')
        ->findAll();
  }

  public function getMappingsWithDetailsByTestCode($testCode) {
    return $this->select('testmap.*, testmapdetail.*')
        ->join('testmapdetail', 'testmapdetail.TestMapID = testmap.TestMapID', 'inner')
        ->groupStart()
            ->where('testmapdetail.HostTestCode', $testCode)
            ->orWhere('testmapdetail.ClientTestCode', $testCode)
        ->groupEnd()
        ->where('testmap.EndDate IS NULL')
        ->where('testmapdetail.EndDate IS NULL')
        ->findAll();
  }
}