Status Based on Service Pool Assignment

/**
 * Generates Bulk Update Events to set the Status on Available Services
 * in the 'UPO-DID: Eliminate' and 'UPO-DID: Reserved' Pools to
 * Retired & Reserved, respectively.
 *
 * Event:           Custom PHP Event
 * Frequency:       Daily
 * Time:
 * Listener Class:  Core_Model_Event
 * Listener Method: eventCustom
 * Issue#:          CUSTOM-21
 */

$user = <<<SQL
SELECT RECID, CONTACTS_RECID
FROM USERS
WHERE USERID = 'tjf15'
SQL;

if (($user = $this->query($user))) {
    $commonParams = [
        'service_type_enabled'    => 0,
        'location_enabled'        => 0,
        'status_enabled'          => 1,
        'service_host_enabled'    => 0,
        'sla_enabled'             => 0,
        'contact_owner_enabled'   => 0,
        'dept_hier_owner_enabled' => 0,
        'gla_enabled'             => 0,
        'pools_enabled'           => 1,
        'billable_enabled'        => 0,
        'directory_enabled'       => 0,
        'essential_enabled'       => 0,
        'report_911_enabled'      => 0,
        'isLocationRequired'      => 0,
        'isServiceHostRequired'   => 0,
        'contact'                 => (int) $user[0]['CONTACTS_RECID'],
        'users_recid'             => (int) $user[0]['RECID'],
        'comment' => 'Classifying Number as Out-of-Service'
    ];

    // Start: UPO-DID: Eliminate
    $pool = <<<SQL
SELECT RECID
FROM POOLS
WHERE POOL_NAME = 'UPO-DID: Eliminate'
SQL;

    if (($pool = $this->query($pool))) {
        $pool = (int) $pool[0]['RECID'];

        $services = <<<SQL
SELECT SERVICES.RECID
FROM SERVICES
LEFT JOIN SERVICES_POOLS
    ON SERVICES.RECID = SERVICES_POOLS.SERVICES_RECID
LEFT JOIN LISTS
    ON SERVICES.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE SERVICES_POOLS.POOLS_RECID = {$pool}
AND LISTS.CODE = 'AVAILABLE'
AND LIST_TYPES.TYPE = 'SERVICE_STATUS'
SQL;

        if (($services = $this->query($services))) {
            $newStatus = <<<SQL
SELECT LISTS.RECID
FROM LISTS
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE LISTS.CODE = 'RETIRED'
AND LIST_TYPES.TYPE = 'SERVICE_STATUS'
SQL;

            if (($newStatus = $this->query($newStatus))) {
                PCR_Event::attachDb('services-bulk-update', ['Application_Model_Service_Service' => 'eventBulkUpdate']);

                $eventParams = $commonParams
                    + [
                        'pools' => [$pool],
                        'status' => (int) $newStatus[0]['RECID']
                    ];

                foreach ($services as $service) {
                    $eventParams['selectedServiceRecids'][] = (int) $service['RECID'];
                }

                PCR_Event::trigger('services-bulk-update', $eventParams);
            }
        }
    }

    // unset these. they're needed again.
    unset($pool, $services, $service, $newStatus, $eventParams);


    // Start: UPO-DID: Reserved
    $pool = <<<SQL
SELECT RECID
FROM POOLS
WHERE POOL_NAME = 'UPO-DID: Reserved'
SQL;

    if (($pool = $this->query($pool))) {
        $pool = (int) $pool[0]['RECID'];

        $services = <<<SQL
SELECT SERVICES.RECID
FROM SERVICES
LEFT JOIN SERVICES_POOLS
    ON SERVICES.RECID = SERVICES_POOLS.SERVICES_RECID
LEFT JOIN LISTS
    ON SERVICES.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE SERVICES_POOLS.POOLS_RECID = {$pool}
AND LISTS.CODE = 'AVAILABLE'
AND LIST_TYPES.TYPE = 'SERVICE_STATUS'
SQL;

        if (($services = $this->query($services))) {
            $newStatus = <<<SQL
SELECT LISTS.RECID
FROM LISTS
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE LISTS.CODE = 'RESERVED'
AND LIST_TYPES.TYPE = 'SERVICE_STATUS'
SQL;

            if (($newStatus = $this->query($newStatus))) {
                PCR_Event::attachDb('services-bulk-update', ['Application_Model_Service_Service' => 'eventBulkUpdate']);

                $eventParams = $commonParams
                    + [
                        'pools' => [$pool],
                        'status' => (int) $newStatus[0]['RECID']
                    ];

                foreach ($services as $service) {
                    $eventParams['selectedServiceRecids'][] = (int) $service['RECID'];
                }

                PCR_Event::trigger('services-bulk-update', $eventParams);
            }
        }
    }
}