PHP code example of codyjheiser / laravel-db2-eloquent

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

codyjheiser / laravel-db2-eloquent example snippets

use CodyJHeiser\Db2Eloquent\Model;

class Customer extends Model
{
    protected $table = 'VARCUST';
    protected string $schema = 'R60FILES';

    protected array $maps = [
        'RMCUST' => 'customer_number',
        'RMNAME' => 'name',
        'RMADD1' => 'address',
        'RMDEL'  => 'delete_code',
        'RMCMP'  => 'company_number',
    ];
}

// Query with human-readable column names
Customer::where('customer_number', '123')->first();
Item::where('item_number', 'ABC123')->orderBy('manufacturer')->get();

class Item extends Model
{
    protected string $schema = 'R60FILES';
    protected $table = 'VINITEM';    // Results in: R60FILES.VINITEM
}

class CommissionPricing extends Model
{
    protected string $schema = 'R60FSDTA';
    protected $table = 'FSCOMPBT';   // Results in: R60FSDTA.FSCOMPBT
}

// Production (default)
Customer::first();              // R60FILES.VARCUST
CommissionPricing::first();     // R60FSDTA.FSCOMPBT

// Testing database
Customer::testing()->first();              // T60FILES.VARCUST
CommissionPricing::testing()->first();     // T60FSDTA.FSCOMPBT

class MyModel extends Model
{
    // Option 1: Explicit test schema
    protected string $schema = 'PROD_SCHEMA';
    protected ?string $testSchema = 'TEST_SCHEMA';

    // Option 2: Prefix replacement (default behavior)
    protected string $schema = 'R60FILES';
    protected string $schemaPrefixProd = 'R';   // Default: 'R'
    protected string $schemaPrefixTest = 'T';   // Default: 'T'
}

// Correct - testing() before withExtensions()
Customer::testing()->withExtensions()->first();

// WRONG - extension tables will still use production schema
Customer::withExtensions()->testing()->first();

class Customer extends Model
{
    protected string $schema = 'R60FILES';
    protected $table = 'VARCUST';

    protected array $maps = [
        'RMDEL' => 'delete_code',
        'RMCMP' => 'company_number',
        'RMCUST' => 'customer_number',
        'RMNAME' => 'name',
        'RMADD1' => 'address',
    ];
}

// These are equivalent - selectMapped is applied automatically
Customer::get();
Customer::selectMapped()->get();

// Also applies to eager-loaded relations automatically
Customer::with('orders')->get();

// Bypass auto-select to get all columns
Customer::selectAll()->get();

// Or use explicit select()
Customer::select('*')->get();
Customer::select(['RMCUST', 'RMNAME', 'SOME_UNMAPPED_COL'])->get();

protected bool $autoSelectMapped = false;

protected bool $applyMapsOnOutput = false;

class CommissionPricing extends Model
{
    protected array $maps = [
        'PBCMP' => 'company_number',
        'PBPSLS' => 'pricing_sales',
    ];

    // Either style works
    protected $casts = [
        'PBCMP' => 'integer',           // Raw DB column name
        'pricing_sales' => 'decimal:2',  // Mapped name
    ];
}

use CodyJHeiser\Db2Eloquent\Casts\IbmDate;
use CodyJHeiser\Db2Eloquent\Casts\IbmDateNullable;

protected $casts = [
    // Returns Carbon instance
    'SHSCDT' => IbmDate::class,

    // Returns formatted string
    'SHCLDT' => IbmDate::class.':date',      // "2025-12-17"
    'SHCLDT' => IbmDate::class.':Y-m-d',     // "2025-12-17"
    'SHCLDT' => IbmDate::class.':m/d/Y',     // "12/17/2025"
    'SHCLDT' => IbmDate::class.':us',        // "12/17/2025"
    'SHCLDT' => IbmDate::class.':eu',        // "17/12/2025"

    // Handles special values like 99999999 as null
    'PBTODT' => IbmDateNullable::class,
];

$call->scheduled_date;                    // Carbon instance
$call->scheduled_date->format('m/d/Y');   // "12/17/2025"
$call->scheduled_date->diffForHumans();   // "2 days ago"

// Setting accepts Carbon, string, or integer
$call->scheduled_date = Carbon::now();
$call->scheduled_date = '2025-12-25';
$call->scheduled_date = 20251225;

use CodyJHeiser\Db2Eloquent\Casts\IbmTime;

protected $casts = [
    // Returns Carbon instance
    'SHTMCD' => IbmTime::class,

    // Returns formatted string
    'SHTMCD' => IbmTime::class.':time',       // "07:37:33"
    'SHTMCD' => IbmTime::class.':H:i:s',      // "07:37:33"
    'SHTMCD' => IbmTime::class.':12h',         // "7:37:33 AM"
    'SHTMCD' => IbmTime::class.':short',       // "07:37"
];

$call->scheduled_time;                    // Carbon instance
$call->scheduled_time->format('g:i A');   // "7:37 AM"

// Setting accepts Carbon, string, or integer
$call->scheduled_time = Carbon::createFromTime(14, 30, 0);
$call->scheduled_time = '14:30:52';
$call->scheduled_time = 143052;

use CodyJHeiser\Db2Eloquent\Casts\IbmDateTime;

protected $casts = [
    // Single datetime column (e.g., 20251217143052)
    'SHDTTM' => IbmDateTime::class,

    // Two columns: date (Ymd) + time (Hms)
    'SHDATE,SHTIME' => IbmDateTime::class,

    // With mapped column names
    'scheduled_date,scheduled_time' => IbmDateTime::class,

    // With output format
    'SHDATE,SHTIME' => IbmDateTime::class.':datetime',     // "2025-12-17 14:30:52"
    'SHDATE,SHTIME' => IbmDateTime::class.':Y-m-d H:i:s',  // "2025-12-17 14:30:52"
    'SHDATE,SHTIME' => IbmDateTime::class.':us',            // "12/17/2025 2:30:52 PM"
];

// Access via the first column name (or its mapped name)
$schedule->SHDATE;           // Carbon instance with date + time combined
$schedule->scheduled_date;   // Same Carbon instance

// Setting updates both columns
$schedule->scheduled_date = Carbon::now();
$schedule->scheduled_date = '2025-12-17 14:30:52';

// toArray() 

// Specify custom date/time input formats (default: Ymd, His)
'SHDATE,SHTIME' => IbmDateTime::class.':Ymd,His',

// Custom input + output format
'SHDATE,SHTIME' => IbmDateTime::class.':Ymd,His,Y-m-d H:i:s',

protected array $maps = [
    'RMCUST' => 'customer_number',  // This one wins
    'CXCUST' => 'customer_number',  // Skipped in output
];

// Include inactive/deleted records
Item::withInactive()->get();

// Include all companies
Item::withAllCompanies()->get();

// Filter by a specific company
Item::forCompany('2')->get();

// Remove all automatic filters
Item::unfiltered()->get();

// Combine bypasses
Item::withInactive()->withAllCompanies()->get();

class SomeModel extends Model
{
    // Disable automatic filtering
    protected bool $filterActiveOnly = false;
    protected bool $filterByCompany = false;

    // Or change the default values
    protected string $activeDeleteCode = 'I';   // Different "active" code
    protected string $defaultCompany = '2';     // Different default company
}

class ItemBalance extends Model
{
    protected array $maps = [
        'IFCOMP' => 'company_number',
        'IFITEM' => 'item_number',
    ];

    public function item()
    {
        // If both models map to 'company_number' and 'item_number'
        return $this->belongsTo(Item::class, ['company_number', 'item_number']);

        // Single column works too
        // return $this->belongsTo(Item::class, 'item_number');

        // Or specify both sides explicitly
        // return $this->belongsTo(Item::class, ['company_number', 'item_number'], ['company_number', 'item_number']);

        // Raw DB columns still work
        // return $this->belongsTo(Item::class, ['IFCOMP', 'IFITEM'], ['ICCMP', 'ICITEM']);
    }
}

class Item extends Model
{
    public function balances()
    {
        return $this->hasMany(ItemBalance::class, ['company_number', 'item_number']);
    }

    public function primaryBalance()
    {
        return $this->hasOne(ItemBalance::class, ['company_number', 'item_number']);
    }
}

use CodyJHeiser\Db2Eloquent\Model;
use CodyJHeiser\Db2Eloquent\Concerns\HasUnionSources;

class ServiceCallAll extends Model
{
    use HasUnionSources;

    protected $connection = 'vai';
    protected string $schema = 'R60FILES';
    protected $table = 'SBSCHD';  // Fallback for connection/schema resolution

    protected array $unionSources = [
        ServiceCallLive::class,
        ServiceCallHistory::class,
    ];

    // Only shared columns — one table may have extra columns you don't need here
    protected array $maps = [
        'SHCMP'  => 'company_number',
        'SHCUST' => 'customer_number',
        'SHCLNO' => 'call_number',
        'SHCLST' => 'status',
        'SHCLDT' => 'call_date',
        'SHCITY' => 'city',
        'SHSTAT' => 'state',
    ];

    public function customer()
    {
        return $this->belongsTo(Customer::class, ['customer_number']);
    }
}

class ServiceCallLive extends ServiceCallAll
{
    protected $table = 'SBSCHD';

    // Optionally override $maps to add columns specific to this table
    // Optionally override $casts for table-specific cast behavior
}

class ServiceCallHistory extends ServiceCallAll
{
    protected $table = 'SBHSHD';
}

// Returns results from BOTH tables
ServiceCallAll::where('status', 'Q')->limit(10)->get();
ServiceCallAll::where('call_number', 12345)->first();
ServiceCallAll::exists();

// Auto-filtering works — applied inside each source query
ServiceCallAll::get();                    // Active records, company 1, from both tables
ServiceCallAll::unfiltered()->get();      // All records from both tables
ServiceCallAll::withInactive()->get();    // Include deleted records from both tables

$record = ServiceCallAll::first();
$record->_source;  // "R60FILES.SBSCHD" or "R60FILES.SBHSHD"

$record->toArray();
// [
//     'call_number' => 5477830,
//     'customer_number' => '0499769',
//     'status' => 'S',
//     '_source' => 'R60FILES.SBSCHD',
//     ...
// ]

use CodyJHeiser\Db2Eloquent\Exceptions\ReadOnlyModelException;

ServiceCallAll::query()->insert([...]);   // throws ReadOnlyModelException
ServiceCallAll::query()->update([...]);   // throws ReadOnlyModelException
$record->save();                          // throws ReadOnlyModelException
$record->delete();                        // throws ReadOnlyModelException

class Item extends Model
{
    protected string $schema = 'R60FILES';
    protected $table = 'VINITEM';

    protected array $extensions = [
        'R60FSDTA.VINITEMX' => [
            'join' => [
                'XICMP'  => 'ICCMP',   // ext.XICMP = base.ICCMP
                'XIITEM' => 'ICITEM',
            ],
            'columns' => ['*'],        // Optional: specific columns
            'maps' => [                // Optional: extension column maps
                'XITYPE' => 'type',
                'XIZUPID' => 'zuper_id',
            ],
        ],
    ];
}

// Join all extensions
Item::withExtensions()->get();

// Join specific extension
Item::withExtension('R60FSDTA.VINITEMX')->get();

// Filter by extension record count
Item::whereHasExtension('R60FSDTA.VINITEMX')->get();           // has >= 1
Item::whereHasExtension('R60FSDTA.VINITEMX', '>', 1)->get();   // has > 1
Item::whereHasExtension('R60FSDTA.VINITEMX', '=', 0)->get();   // has none
Item::whereDoesntHaveExtension('R60FSDTA.VINITEMX')->get();    // shortcut for none

// Filter AND join in one call
Item::withWhereHasExtension('R60FSDTA.VINITEMX')->get();
Item::withWhereHasExtension('R60FSDTA.VINITEMX', '>', 1)->get();

// Combine with selectMapped - extension mapped columns auto-added
Item::selectMapped()->withExtensions()->get();
Item::withExtensions()->selectMapped()->get();

$item = Item::find(123);
$item->hasExtensionRecords('R60FSDTA.VINITEMX');        // true/false
$item->countExtensionRecords('R60FSDTA.VINITEMX');      // 0, 1, 2...
$item->hasMultipleExtensionRecords('R60FSDTA.VINITEMX'); // true if > 1

$item->loadExtension('R60FSDTA.VINITEMX');
$extData = $item->getExtensionData('R60FSDTA.VINITEMX');

// Log to stderr (default)
Item::logQuery()->where('item_number', 'ABC')->first();

// Log to file
Item::logQuery('default')->selectMapped()->get();

// Log to both stderr and file
Item::logQuery(['stderr', 'default'])->withExtensions()->first();

use CodyJHeiser\Db2Eloquent\Model;

// Enable via base model or any child model
Model::enableQueryLog();
Customer::enableQueryLog();

// Log to app log file instead of stderr
Customer::enableQueryLog(null);
Customer::enableQueryLog('default');

// Log to multiple channels
Customer::enableQueryLog(['stderr', 'default']);

// Run queries...
Customer::where('customer_number', '123')->first();

// Manage logs
Customer::dumpQueryLog();        // Dump to output
Customer::getQueryLog();         // Get as array
Customer::clearQueryLog();       // Clear log
Customer::disableQueryLog();     // Turn off

Model::setSqlFormatter(function ($sql, $bindings) {
    // Your custom formatting logic
    return $formattedSql;
});

// Column mapping
$model->getMaps();                 // Base table maps
$model->getAllMaps();              // Base + extension maps
$model->getReverseMaps();          // Mapped name => DB column
$model->getDbColumn('name');       // 'name' => 'RMNAME'
$model->getMappedColumn('RMNAME'); // 'RMNAME' => 'name'

// Check definitions
$model->hasMaps();
$model->hasExtensions();

// Raw attributes (unmapped)
$model->getRawAttributes();

'connections' => [
    'db2' => [
        'driver' => 'db2',
        'host' => env('IBM_DB_HOST'),
        'port' => env('IBM_DB_PORT', 50000),
        'database' => env('IBM_DB_DATABASE'),
        'username' => env('IBM_DB_USERNAME'),
        'password' => env('IBM_DB_PASSWORD'),
        'schema' => env('IBM_DB_SCHEMA', 'QGPL'),
    ],
],

protected $connection = 'my_other_db2_connection';

'ibm' => [
    'schema' => [
        'R60FILES' => env('IBM_SCHEMA_FILES', 'R60FILES'),
        'R60FSDTA' => env('IBM_SCHEMA_FSDTA', 'R60FSDTA'),
    ],
],

public function __construct(array $attributes = [])
{
    $this->schema = config('database.ibm.schema.R60FILES', 'R60FILES');

    parent::__construct($attributes);
}

protected string $defaultCompany = '2';

// Disable auto-filtering by delete code
protected bool $filterActiveOnly = false;

// Disable auto-filtering by company
protected bool $filterByCompany = false;

// Disable auto-select of mapped columns
protected bool $autoSelectMapped = false;

// Disable output mapping
protected bool $applyMapsOnOutput = false;