(2022.1) Inactive Service Locations Clean Up

<?php
/**
 * IN201620553 - Created to clean up Inactive service locations and owners before aging occurs.
 * Event: Custom PHP Event
 * Frequency: Hourly
 * Time:
 * Listener Class: Core_Model_Event
 * Listener Method: eventCustom
 * Description: This event queries for Inactive services with billed charges (or no charges at all)
 * and triggers the service bulk update to remove the owner and location.
 */


PCR_Event::attachDb('services-bulk-update', ['Application_Model_Service_Service' => 'eventBulkUpdate']);

$records = $this->query(<<<SQL
    SELECT DISTINCT S.RECID AS SERVICES_RECID,
    FROM SERVICES AS S
    JOIN LISTS AS L ON L.RECID = S.SERVICE_STATUS_LISTS_RECID
    LEFT JOIN SERVICES_CHARGES AS SC ON S.RECID = SC.SERVICES_RECID
    LEFT JOIN CHARGES AS C
        ON C.ORIG_TABLE_NAME = 'SERVICES_CHARGES' AND C.ORIG_TABLE_RECID = SC.RECID
    LEFT JOIN CHARGES_BILLED AS CB_MAX 
       ON CB_MAX.CHARGES_RECID = C.RECID
    LEFT JOIN CHARGES_BILLED AS CB_MIN
       ON CB_MIN.CHARGES_RECID = C.RECID AND CB_MAX.RECID < CB_MIN.RECID
    LEFT JOIN BILLS AS B ON B.RECID = CB_MAX.BILLS_RECID
    WHERE
        L.CODE = 'INACTIVE'
        AND S.LOCATIONS_RECID IS NOT NULL
        AND ( 
           CB_MAX.BILLING_COMPLETE = 1
           OR 
           SC.RECID IS NULL 
        )
        AND CB_MIN.RECID IS NULL
    GROUP BY S.RECID, SC.RECID
    LIMIT 400;
SQL
);

$eventParams = [
    'contact'     => 101452,  // PCR Admin contact
    'users_recid' => 485, // pcradmin user
];

//$eventParams['contact_owner_enabled'] = '1';
//$eventParams['contact_owner'] = null;
//$eventParams['dept_hier_owner_enabled'] = '1';
//$eventParams['dept_hier_owner'] = null;
$eventParams['location_enabled'] = '1';
$eventParams['location'] = null;
$eventParams['comment'] = 'Cleanup event to remove Locations from Inactive Services';

if(!empty($records)){
    foreach ($records as $rec) {
        $eventParams['selectedServiceRecids'][] = $rec['SERVICES_RECID'];
    }
    PCR_Event::trigger('services-bulk-update', $eventParams);
}