Custom Logic Library

<?php
/**
 * Type: Custom Logic
 * Identifier: helpdesk
 */

// The incoming payload from Slack is JSON encoded
// so we need to decode it...
$payload = json_decode($request["payload"], true);

$user = $payload["user"]["name"];
$callbackId = explode("-",$payload["callback_id"]);
$iconUrl = "https://www.iconexperience.com/_img/v_collection_png/24x24/plain/";
$icon = "error.png";

// find the Contact for this User - using the query function is best with
// bound parameters instead of placing them directly into the query string.
// This protects the database against injection threats
$contacts = $this->query(
    "SELECT RECID FROM CONTACTS C
    LEFT JOIN V_UDF UDF ON UDF.VALUE_TABLE_NAME = 'CONTACTS' AND C.RECID = VALUE_TABLE_RECID
    WHERE UDF.VALUE = :slack_user",
    [":slack_user" => "@".$user]
);

if(!empty($contacts)){
    // the query function always returns an array
    // of rows so we need to look at the first index
    $contact = $contacts[0];

$incidents = $this->query(
        "SELECT SD.*, D.CODE, L.VALUE AS STATUS
        FROM SERVICE_DESK SD
        LEFT JOIN DEPT_HIERARCHY D ON SD.D_OWNER_DEPT_HIERARCHY_RECID = D.RECID
        LEFT JOIN LISTS L ON SD.SD_STATUS_LISTS_RECID = L.RECID
        WHERE SD_NUMBER = :incident",
        [":incident" => $callbackId[0]]
    );

if(!empty($incidents)){
        $incident = $incidents[0];

$workflow = $this->query(
            "SELECT SDWF.*, CONCAT(C.LAST_NAME,', ',C.FIRST_NAME) AS WORKER, L.CODE AS STATUS
            FROM SERVICE_DESK_WORKFLOW SDWF
            LEFT JOIN CONTACTS C ON C.RECID = SDWF.WORKER_CONTACTS_RECID
            LEFT JOIN LISTS L ON SDWF.SD_WF_STATUS_LISTS_RECID = L.RECID
            WHERE SERVICE_DESK_RECID = :sd_recid AND SDWF.RECID = :recid",
            [
                ":sd_recid" => $incident["RECID"],
                ":recid" => $callbackId[2]
            ]
        );

if(!empty($workflow)){
            $wf = $workflow[0];
            if(empty($wf["WORKER"])){
                if($wf["STATUS"] == "PENDING"){
                    if(Core_Model_Api_Servicedesk::assignWorkflow(
                        $wf["RECID"], $contact["RECID"]
                    )){
                        $assigned = "Assigned to @{$user}";
                        $icon = "checkbox.png";
                    } else {
                        $assigned = "Error: Failed to Assign Workflow...";
                    }
                } else {
                    $assigned = "Error: WF is not Pending...";
                }
            } else {
                $assigned = "WF Already Assigned to {$wf["WORKER"]}";
                $icon = "information.png";
            }
        } else {
            $assigned = "Error: Cannot find Workflow {$callbackId[1]} on {$callbackId[0]}...";
        }
    } else {
        $assigned = "Error: Cannot find Incident [{$callbackId[0]}]...";
    }
} else {
    $assigned = "Error: Cannot find User [{$payload["user"]["name"]}]...";
}

$orig = $payload["original_message"];
// the response array is the data that is returned to the original API request
$response = [
    "text" => $orig["text"],
    "attachments" => [[
        "title" => $orig["attachments"][0]["title"],
        "title_link" => $orig["attachments"][0]["title_link"],
        "text" => $orig["attachments"][0]["text"],
        "footer" => $assigned,
        "footer_icon" => $iconUrl.$icon,
        "ts" => time(),
    ]]
];

SNOW: Move Service

<?php
// log the original request JSON in the SERVICENOW_DETAILS UDF table
$details = ["data" => []];
$systemValues = [
    "apikey" => "apikey",
    "typeformat" => "typeformat",
    "module" => "module",
    "controller" => "controller",
    "action" => "action",
    "request_method" => "request_method",
    "limit" => "limit",
    "page" => "page",
    "tenants_recid" => "tenants_recid",
    "ident" => "ident"
];
$data = array_diff_key($request, $systemValues);
foreach ($data as $field => $value) {
    $details["data"][] = [
        "LABEL" => $field,
        "VALUE" => $value
    ];
}

// set the default save data to create an SO with the SNOW data in a UD table
$payload = [
    "type" => "SO",
    "csr" => "1", // User Demo
    "udf_SERVICENOW_NUMBER" => trim($request["servicenow_number"]), // SERVICENOW_NUMBER
    "udf_SERVICENOW_DETAILS" => json_encode($details), // SERVICENOW_DETAILS
];

// get the requestor based on the incoming requestor email address
$requestorQuery = $this->query(
    "SELECT 
        C.RECID 
    FROM 
        CONTACTS C
    JOIN CONTACTS_EMAILS CE ON C.RECID = CE.CONTACTS_RECID
    WHERE 
        CE.EMAIL = :requestor",
    [":requestor" => trim($request["requestor"])]
);

if (count($requestorQuery) && ($requestorRecid = $requestorQuery[0]["RECID"])) {
    // find the location RECID of the moveto_location
    $locationQuery = $this->query(
        "SELECT
            L.RECID
        FROM
            LOCATIONS L
        WHERE
            L.NAME = :location",
        [
            ":location" => $request["moveto_location"]
        ]);

    if (count($locationQuery) && ($movetoLocationRecid = $locationQuery[0]["RECID"])) {
        // find the service RECID
        $service = $request["service"];
        $serviceQuery = $this->query(
            "SELECT
                S.RECID
            FROM
                SERVICES S
            WHERE 
                S.SERVICE_ID = :service
                OR S.SERVICE_ID_FMT = :service",
            [":service" => $service]
        );

        if (count($serviceQuery) && ($serviceRecid = $serviceQuery[0]["RECID"])) {
            // set the SDA variables if the data is valid
            $payload["sd_action"] = "CHG_MOVE";
            $payload["requestor"] = $requestorRecid;
            $payload["moveto_location"] = $movetoLocationRecid;
            $payload["service"] = $serviceRecid;
        } else {
            $response["error_message"] = "A valid SERVICE RECID could not be found based "
                . "on the incoming Service Number: " . $service;
        }
    } else {
        $response["error_message"] = "A valid LOCATION RECID could not be found in PCR360 based "
            . "on the incoming Location: " . $request["moveto_location"];
    }
} else {
    $response["error_message"] = "A valid CONTACT RECID could not be found in PCR360 based "
        . "on the incoming email address: " . $request["requestor"];
}

if (isset($response["error_message"])) {
    // log the error as a remark on the SO
    $payload["remarks"] = "A Service Desk Action could not be created on this Service Order "
        . "due to the following error: " . $response["error_message"];
}

$result = $this->call("saveServiceDesk", $payload);
$response["servicedesk_number"] = $result["data"];

SNOW: New Service

<?php
if (!empty($request["servicenow_number"])) {
    // log the original request JSON in the SERVICENOW_DETAILS UDF table
    $details = ["data" => []];
    $systemValues = [
        "apikey" => "apikey",
        "typeformat" => "typeformat",
        "module" => "module",
        "controller" => "controller",
        "action" => "action",
        "request_method" => "request_method",
        "limit" => "limit",
        "page" => "page",
        "tenants_recid" => "tenants_recid",
        "ident" => "ident"
    ];
    $data = array_diff_key($request, $systemValues);
    foreach ($data as $field => $value) {
        $details["data"][] = [
            "LABEL" => $field,
            "VALUE" => $value
        ];
    }

    // set the default save data to create an SO with the SNOW data in a UD table
    $payload = [
        "type" => "SO",
        "csr" => "1", // User Demo
        "udf_SERVICENOW_NUMBER" => trim($request["servicenow_number"]), // SERVICENOW_NUMBER
        "udf_SERVICENOW_DETAILS" => json_encode($details), // SERVICENOW_DETAILS
        "service_host" => "1"
    ];

    // get the requestor based on the incoming requestor email address
    $requestorQuery = $this->query(
        "SELECT 
            C.RECID 
        FROM 
            CONTACTS C
        JOIN CONTACTS_EMAILS CE ON C.RECID = CE.CONTACTS_RECID
        WHERE 
            CE.EMAIL = :requestor",
        [":requestor" => trim($request["requestor"])]
    );

    if (count($requestorQuery) && ($requestorRecid = $requestorQuery[0]["RECID"])) {
        // find the catalog RECID
        $catalogQuery = $this->query(
            "SELECT
                SC.RECID
            FROM
                SERV_CATALOG SC
            WHERE 
                SC.SERVICE_NAME = :catalog",
            [":catalog" => $request["catalog"]]
        );

        if (count($catalogQuery) && ($catalogRecid = $catalogQuery[0]["RECID"])) {
            // find the location RECID of the location
            $locationQuery = $this->query(
                "SELECT
                    L.RECID
                FROM
                    LOCATIONS L
                WHERE
                    L.NAME = :location",
                [
                    ":location" => $request["new_location"]
                ]);

            if (count($locationQuery) && ($locationRecid = $locationQuery[0]["RECID"])) {
                // set the SDA variables if the data is valid
                $payload["sd_action"] = "ADD";
                $payload["requestor"] = $requestorRecid;
                $payload["owner_contact"] = $requestorRecid;
                $payload["catalog"] = $catalogRecid;
                $payload["location"] = $locationRecid;
            } else {
                $response["error_message"] = "A valid LOCATION RECID could not be found in PCR360 based "
                    . "on the incoming Location: " . $request["new_location"];
            }
        } else {
            $response["error_message"] = "The Catalog '" . $catalog . "' could not be found in PCR360.";
        }
    } else {
        $response["error_message"] = "A valid CONTACT RECID could not be found in PCR360 based "
            . "on the incoming email address: " . $request["requestor"];
    }

    if (isset($response["error_message"])) {
        // log the error as a remark on the SO
        $payload["remarks"] = "A Service Desk Action could not be created on this Service Order "
            . "due to the following error: " . $response["error_message"];
    }

    $result = $this->call("saveServiceDesk", $payload);
    $response["servicedesk_number"] = $result["data"];
}

Equipment Details Sync

<?php
/**
* Event: Custom PHP Event
* Frequency: Hourly
* Time:
* Listener Class: Core_Model_Event
* Listener Method: eventCustom
*/

PCR_Event::attachDb("equipment-bulk-update", ["Application_Model_Equipment_Equipment" => "eventBulkUpdate"]);

$records = $this->query(<<<SQL
        SELECT
          S.RECID AS SERVICES_RECID,
          E.RECID AS EQUIPMENT_RECID,
          S.OWNER_CONTACTS_RECID AS SERVICE_OWNER_CONTACTS_RECID,
          E.OWNER_CONTACTS_RECID AS EQUIPMENT_OWNER_CONTACTS_RECID,
          S.OWNER_DEPT_HIERARCHY_RECID AS SERVICE_OWNER_DEPT_HIERARCHY_RECID,
          E.OWNER_DEPT_HIERARCHY_RECID AS EQUIPMENT_OWNER_DEPT_HIERARCHY_RECID,
        CASE
          WHEN S.OWNER_CONTACTS_RECID IS NULL
            THEN SDH.BILLING_GROUPS_RECID
          WHEN SCON.BILLING_GROUPS_RECID IS NULL 
            THEN SCON_DEPT.BILLING_GROUPS_RECID
          ELSE SCON.BILLING_GROUPS_RECID
          END AS SERVICE_BILLING_GROUP,
        CASE
          WHEN E.OWNER_CONTACTS_RECID IS NULL 
            THEN EDH.BILLING_GROUPS_RECID
          WHEN ECON.BILLING_GROUPS_RECID IS NULL 
            THEN ECON_DEPT.BILLING_GROUPS_RECID
          ELSE ECON.BILLING_GROUPS_RECID
          END AS EQUIPMENT_BILLING_GROUP,
        SEG.GLA_RECID AS SERVICE_GLA,
        EEG.GLA_RECID AS EQUIPMENT_GLA,
        S.LOCATIONS_RECID AS SERVICE_LOCATION,
        E.LOCATIONS_RECID AS EQUIPMENT_LOCATION,
        S.MULTIPLE_LOCATIONS,
        COALESCE(EG.FORMAT,SG.FORMAT) AS GLA_FORMAT,
        E.BILLABLE,
        E.WARRANTY_END_DATE

        FROM SERVICES S
        JOIN SERVICES_EQUIPMENT SE ON S.RECID = SE.SERVICES_RECID
        JOIN EQUIPMENT E ON SE.EQUIPMENT_RECID = E.RECID
        LEFT JOIN DEPT_HIERARCHY EDH ON E.OWNER_DEPT_HIERARCHY_RECID = EDH.RECID
        LEFT JOIN DEPT_HIERARCHY SDH ON S.OWNER_DEPT_HIERARCHY_RECID = SDH.RECID
        LEFT JOIN CONTACTS ECON ON E.OWNER_CONTACTS_RECID = ECON.RECID
        LEFT JOIN DEPT_HIERARCHY ECON_DEPT ON ECON.DEPT_HIERARCHY_RECID = ECON_DEPT.RECID
        LEFT JOIN CONTACTS SCON ON S.OWNER_CONTACTS_RECID = SCON.RECID
        LEFT JOIN DEPT_HIERARCHY SCON_DEPT ON SCON.DEPT_HIERARCHY_RECID = SCON_DEPT.RECID
        LEFT JOIN SERVICES_EXPENSE_GLA SEG ON S.RECID = SEG.SERVICES_RECID
        LEFT JOIN EQUIPMENT_EXPENSE_GLA EEG ON E.RECID = EEG.EQUIPMENT_RECID
        LEFT JOIN LISTS SEGL ON SEG.EXPENSE_TYPE_LISTS_RECID = SEGL.RECID
        LEFT JOIN LISTS EEGL ON EEG.EXPENSE_TYPE_LISTS_RECID = EEGL.RECID
        LEFT JOIN GLA EG ON EEG.GLA_RECID = EG.RECID
        LEFT JOIN GLA SG ON SEG.GLA_RECID = SG.RECID

        WHERE
        -- GLAs are Default and 100% or are not set
        (
          ((SEGL.CODE = 'DEFAULT' AND SEG.PERCENT = 100) OR SEGL.CODE IS NULL)
          AND
          ((EEGL.CODE = 'DEFAULT' AND SEG.PERCENT = 100) OR EEGL.CODE IS NULL)
        )
        -- if the OWNER is different
        AND
        (
            (
              (S.OWNER_CONTACTS_RECID <> E.OWNER_CONTACTS_RECID AND S.OWNER_CONTACTS_RECID IS NOT NULL AND E.OWNER_CONTACTS_RECID IS NOT NULL)
              OR (S.OWNER_DEPT_HIERARCHY_RECID <> E.OWNER_DEPT_HIERARCHY_RECID AND S.OWNER_DEPT_HIERARCHY_RECID IS NOT NULL AND E.OWNER_DEPT_HIERARCHY_RECID IS NOT NULL)
              OR (S.OWNER_CONTACTS_RECID IS NULL AND E.OWNER_CONTACTS_RECID IS NOT NULL)
              OR (S.OWNER_CONTACTS_RECID IS NOT NULL AND E.OWNER_CONTACTS_RECID IS NULL)
              OR (S.OWNER_DEPT_HIERARCHY_RECID IS NULL AND E.OWNER_DEPT_HIERARCHY_RECID IS NOT NULL)
              OR (S.OWNER_DEPT_HIERARCHY_RECID IS NOT NULL AND E.OWNER_DEPT_HIERARCHY_RECID IS NULL)
            )
            -- if the location is different
            OR (NOT(S.LOCATIONS_RECID <=> E.LOCATIONS_RECID) AND S.MULTIPLE_LOCATIONS = 0)
            -- if the GLAs are different
            OR  NOT(SEG.GLA_RECID  <=>  EEG.GLA_RECID )
         );
SQL
);

foreach ($records as $rec) {
    $eventParams = [
        "contact" => 101452,  // PCR Admin contact
        "users_recid" => 485, // pcradmin user
        "selectedEquipmentRecids" => [$rec['EQUIPMENT_RECID']],
        "gla_enabled" => false,
        "condition_enabled" => false,
        "cabling_eqp_type_enabled" => false,
        "status_enabled" => false,
        "location_enabled" => false,
        "assoc_service_enabled" => false,
        "equipment_type_enabled" => false,
        "dhcp_enabled" => false,
        "lan_name_enabled" => false,
        "host_name_enabled" => false,
        "private_enabled" => false,
        "billable_enabled" => false,
        "billable" => $rec["BILLABLE"],
        "war_date_enabled" => false,
        "war_date" => $rec["WARRANTY_END_DATE"],
        "dept_hier_owner_enabled" => false,
        "contact_owner_enabled" => false,
        // other junk from the EQP Bulk UPdate
        "gla_format" => $rec["GLA_FORMAT"],
        "selectedEquipmentTypeListRecid" => null,
        "isLocationRequired" => null,
        "comment" => null,
    ];

    if (empty($rec['SERVICE_OWNER_CONTACTS_RECID'])
        && $rec['SERVICE_OWNER_DEPT_HIERARCHY_RECID'] != $rec['EQUIPMENT_OWNER_DEPT_HIERARCHY_RECID']
    ) {
        $eventParams['dept_hier_owner_enabled'] = true;
        $eventParams['dept_hier_owner'] = $rec['SERVICE_OWNER_DEPT_HIERARCHY_RECID'];
    } elseif(empty($rec['SERVICE_OWNER_DEPT_HIERARCHY_RECID'])
        && $rec['SERVICE_OWNER_CONTACTS_RECID'] != $rec['EQUIPMENT_OWNER_CONTACTS_RECID']
    ) {
        $eventParams['contact_owner_enabled'] = true;
        $eventParams['contact_owner'] = $rec['SERVICE_OWNER_CONTACTS_RECID'];
    } elseif(empty($rec['SERVICE_OWNER_DEPT_HIERARCHY_RECID'])
        && $rec['SERVICE_OWNER_CONTACTS_RECID'] == $rec['EQUIPMENT_OWNER_CONTACTS_RECID']
    ) {
        // Equipment has both Contact and Dept Owners -
        // @see https://jira.pcr.com/browse/PCR360-7612
        // updating the contact Owner even though it is already set will clear the Dept Owner
        $eventParams['contact_owner_enabled'] = true;
        $eventParams['contact_owner'] = $rec['SERVICE_OWNER_CONTACTS_RECID'];
    }

    if($rec['MULTIPLE_LOCATIONS'] == "0"
        && $rec['SERVICE_LOCATION'] != $rec['EQUIPMENT_LOCATION']
    ){
        $eventParams['location_enabled'] = true;
        $eventParams['location'] = $rec['SERVICE_LOCATION'];
    }

    if($rec['SERVICE_GLA'] != $rec['EQUIPMENT_GLA']){
        $eventParams['gla_enabled'] = true;
        $eventParams['gla'] = $rec['SERVICE_GLA'];
    }

    // Only trigger the bulk update if any of the options are enabled.  Otherwise nothing is changing, there is no
    // need to trigger a bulk update if nothing is changing.
    if($eventParams['dept_hier_owner_enabled']
        || $eventParams['location_enabled']
        || $eventParams['gla_enabled']
        || $eventParams['contact_owner_enabled']
    ){
        PCR_Event::trigger("equipment-bulk-update", $eventParams);
    }
}

GLA Rollover

/**
 * Event: Custom PHP Event
 * Frequency: Daily
 * Time: 12:00am
 * Listener Class: Core_Model_Event
 * Listener Method: eventCustom
 */

$glas = $this->query(<<<SQL
    SELECT G.STATUS AS STATUS, V.GLA_FULL_CODES AS OLD_GLA, 
           G.RECID AS OLD_GLA_RECID, V1.GLA_FULL_CODES AS NEW_GLA, 
           G1.RECID AS NEW_GLA_RECID, U2.DATETIME_VALUE AS END_DATE
    FROM V_GLA_COMP_COMB_ADMIN V
    INNER JOIN GLA G ON V.RECID = G.RECID
    INNER JOIN USER_DEFINED_FIELDS_VALS U1 
        ON G.RECID = U1.TABLE_RECID AND U1.TABLE_NAME = 'GLA' AND U1.UDF_RECID = (
            SELECT RECID FROM USER_DEFINED_FIELDS WHERE LABEL = 'Rollover_GLA'
        )
    LEFT JOIN GLA G1 ON G1.RECID = U1.INTEGER_VALUE
    LEFT JOIN V_GLA_COMP_COMB_ADMIN V1 ON G1.RECID = V1.RECID
    INNER JOIN USER_DEFINED_FIELDS_VALS U2 
        ON G.RECID = U2.TABLE_RECID AND U2.TABLE_NAME = 'GLA' AND U2.UDF_RECID = (
            SELECT RECID FROM USER_DEFINED_FIELDS WHERE LABEL = 'End_Date'
        )
    WHERE
    U2.DATETIME_VALUE IS NOT NULL
    AND U2.DATETIME_VALUE <= NOW()
    AND G.STATUS = 1
    ;
SQL
);

$results = ["deactivateGla" => [], "activateGla" => [], "rolledGla" => []];
PCR_Event::attachDb("replace-gla", [
    "Application_Model_Gla_Gla" => "eventReplaceGla"
]);
foreach ($glas as $gla) {

    // deactivate anything with past end_date
    $results["deactivateGla"][] = $gla["OLD_GLA_RECID"];

    // only Roll to the new GLA if there is a new GLA
    if(!empty($gla["NEW_GLA_RECID"])) {
        $results["activateGla"][] = $gla["NEW_GLA_RECID"];
        // only rollover if the END_DATE was within the last 30 days
        if (time() - strtotime($gla["END_DATE"]) < (30 * 24 * 60 * 60)) {
            $results["rolledGla"][] = $gla["OLD_GLA_RECID"];
            PCR_Event::trigger("replace-gla", [
                "contact" => 101452,  // PCR Admin contact
                "users_recid" => 485, // pcradmin user
                "glaRecid" => [$gla["OLD_GLA_RECID"]],
                "replaceGlaRecid" => $gla["NEW_GLA_RECID"],
                "prorate" => true,
                "setInactiveGla" => true,
                "comment" => "Nightly GLA Rollover from {$gla['OLD_GLA']} to {$gla['NEW_GLA']} recid"
                             . " {$gla['OLD_GLA_RECID']} to {$gla['NEW_GLA_RECID']}",
            ]);
        }
    }
}

// if anything Rolled/Deactivated then we need to fire that event
if(!empty($results["activateGla"]) || !empty($results["rolledGla"]) || !empty($results["deactivateGla"])){
    $results["ACTIVATE_COUNT"] = count($results["activateGla"]);
    $results["DEACTIVATE_COUNT"] = count($results["deactivateGla"]);
    $results["ROLLED_COUNT"] = count($results["rolledGla"]);

    // set the gla-status event and attach for a notification too
    PCR_Event::attachDb("gla-status", [
        "Core_Model_Notification" => "eventSend",
        "Application_Model_Gla_Gla" => "eventGlaStatus"
    ]);
    PCR_Event::trigger("gla-status", $results);
} else {
    $results["ACTIVATE_COUNT"] = $results["DEACTIVATE_COUNT"] = $results["ROLLED_COUNT"] = 0;

    // set the gla-status event and attach for a notification to indicate no glas rolled over
    PCR_Event::attachDb(
        "gla-status",
        [
            "Core_Model_Notification"   => "eventSend",
            "Application_Model_Gla_Gla" => "eventGlaStatus"
        ]
    );
    PCR_Event::trigger("gla-status", $results); 
}

Inactive 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);
}

Move Available Services to Parent

<?php

// >>>> COPY START !!! <<<< //

/**
 * This looks for any Phone Services which, after aging are changed from 'Inactive' to 'Available', and which
 * are in any of the child Categories of 'Telephones & Voicemail'. It then creates a Bulk Update Event to
 * move those Services to the parent.
 *
 * Note: The child Catalog Type must be 'Phone'.
 *
 * Event:           Custom PHP Event
 * Frequency:       Daily
 * Time:
 * Listener Class:  Core_Model_Event
 * Listener Method: eventCustom
 */

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

$parentCatalogRecidSql = <<<SQL
SELECT RECID
FROM   SERV_CATALOG
WHERE  SERVICE_NAME = 'Telephones & Voicemail'
SQL;

$listsRecidSql = <<<SQL
SELECT    LISTS.RECID
FROM      LISTS
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE     LIST_TYPES.TYPE = 'SERVICE_TYPE'
AND       LISTS.CODE = 'PHONE';
SQL;

if (($parentCatalogRecid = $this->query($parentCatalogRecidSql)) &&
    ($listsRecid = $this->query($listsRecidSql))
) {
    $parentCatalogRecid = (int) $parentCatalogRecid[0]['RECID'];
    $listsRecid = (int) $listsRecid[0]['RECID'];

    $servicesToMoveSql = <<<SQL
SELECT SERVICES.RECID
FROM   SERVICES
LEFT JOIN LISTS
    ON SERVICES.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
LEFT JOIN LIST_TYPES
    ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
WHERE  LISTS.CODE = 'AVAILABLE'
AND    LIST_TYPES.TYPE = 'SERVICE_STATUS'
AND    SERVICES.SERV_CATALOG_RECID IN (
    SELECT SERV_CATALOG_FULLPATH.SERV_CATALOG_RECID
    FROM   SERV_CATALOG_FULLPATH
    LEFT JOIN SERV_CATALOG
        ON SERV_CATALOG_FULLPATH.SERV_CATALOG_RECID = SERV_CATALOG.RECID
    LEFT JOIN LISTS
        ON SERV_CATALOG.SERVICE_TYPE_LISTS_RECID = LISTS.RECID
    LEFT JOIN LIST_TYPES
        ON LISTS.LIST_TYPES_RECID = LIST_TYPES.RECID
    WHERE  PATH_SERV_CATALOG_RECID = {$parentCatalogRecid}
    AND    SERV_CATALOG_RECID <> {$parentCatalogRecid}
    AND    LISTS.CODE = 'PHONE'
    AND    LIST_TYPES.TYPE = 'SERVICE_TYPE'
)
SQL;

    $userSql = <<<SQL
SELECT RECID, CONTACTS_RECID
FROM USERS
WHERE USERID = 'pcr'
SQL;

    if (($servicesToMove = $this->query($servicesToMoveSql)) &&
        ($user = $this->query($userSql))
    ) {
        $eventParams = [
            'service_type_enabled'    => 1,
            'location_enabled'        => 0,
            'status_enabled'          => 0,
            'service_host_enabled'    => 0,
            'sla_enabled'             => 0,
            'contact_owner_enabled'   => 0,
            'dept_hier_owner_enabled' => 0,
            'gla_enabled'             => 0,
            'pools_enabled'           => 0,
            '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'],

            'service_type'                 => $parentCatalogRecid,
            'selectedServiceTypeListRecid' => $listsRecid,
            'comment'                      => 'Custom Event: Move all "Available" Services to parent Catalog.'
        ];

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

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

// >>>> COPY STOP !!! <<<< //

?>

Nightly Service Location Update

/**
 * Event: Custom PHP Event
 * Frequency: Daily
 * Time: 12:00am
 * Listener Class: Core_Model_Event
 * Listener Method: eventCustom
 */

$services = $this->query(
   "SELECT S.RECID AS SERVICE_RECID, L.RECID AS LOCATION_RECID 
   FROM PCR360_PROD.SERVICES S
   JOIN PCR360_PROD.PATHS P ON P.SERVICES_RECID = S.RECID
   JOIN PCR360_PROD.PATH_LEGS PL ON PL.PATHS_RECID = P.RECID
   JOIN PCR360_PROD.EQUIPMENT E ON PL.FROM_EQUIPMENT_RECID = E.RECID
   JOIN PCR360_PROD.LOCATIONS L ON E.LOCATIONS_RECID = L.RECID
   JOIN PCR360_PROD.EQP_CATALOG EC ON E.EQP_CATALOG_RECID = EC.RECID 
   WHERE S.MULTIPLE_LOCATIONS = 0
   AND EC.PART_NUMBER = 'Jacks'
   AND S.LOCATIONS_RECID != E.LOCATIONS_RECID"
);

PCR_Event::attachDb("services-bulk-update", [
    "Application_Model_Service_Service" => "eventBulkUpdate"
]);
foreach ($services as $service) {
    PCR_Event::trigger("services-bulk-update", [
        "selectedServiceRecids" => [$service["SERVICE_RECID"]],
        "location_enabled" => "1",
        "location" => $service["LOCATION_RECID"],
	"service_type_enabled" => "0",
	"status_enabled" => "0",
	"service_host_enabled" => "0",
	"sla_enabled" => "0",
	"contact_owner_enabled" => "0",
	"dept_hier_owner_enabled" => "0",
	"gla_enabled" => "0",
	"pools_enabled" => "0",
	"billable_enabled" => "0",
	"directory_enabled" => "0",
	"essential_enabled" => "0",
	"report_911_enabled" => "0",    
	"isLocationRequired" => true,
	"isServiceHostRequired" => false,
	"selectedServiceTypeListRecid" => null,
    "selectedServiceFilters" => [],
	"billable" => "0",
	"essential" => "0",
	"directory" => "0",
	"status" => "1",
	"report_911" => "0",
	"users_recid" => "2",
	"contact" => "2",
	"comment" => "nightly jack/service location update",
    ]);
}

Prevent Duplicate Charge Catalogs

/**
 * Prevents duplicate name/type combinations in the CHRG_CATALOG table.
 *
 * Description: Prevent Duplicate CHRG_CATALOG Entries
 * Table Name: CHRG_CATALOG
 * Table Record: null
 * Action: Save
 */
$query = "SELECT RECID FROM CHRG_CATALOG WHERE NAME = '"
    . $params["charge_name"] . "' AND CHARGE_TYPE_LISTS_RECID = " . $params["type"];
$existing = $this->query($query);

if (isset($existing[0]["RECID"]) && ($existing[0]["RECID"] !== $data["RECID"]))
{
    $this->setMessage('Save failed: there already exists a Charge Catalog record with the same name/type combination');
    return false;
}
return true;

Prevent Duplicate Equipment Catalog Entires

<?php
/**
 * Prevents duplicate manufacturer/manufacturer part number combinations in the EQP_CATALOG table.
 *
 * Description: Prevent Duplicate EQP_CATALOG Entries
 * Table Name: EQP_CATALOG
 * Table Record: null
 * Action: Save
 */
$query = "SELECT RECID FROM EQP_CATALOG WHERE MANUFACTURER = '"
    . $params["manufacturer"] . "' AND MANU_PART_NUM = '" . $params["manu_part_num"] . "'";
$existing = $this->query($query);

if (isset($existing[0]["RECID"]) && ($existing[0]["RECID"] !== $data["RECID"]))
{
    $this->setMessage('Save failed: there already exists an Equipment Catalog record with the same manufacturer/manufacturer part number combination');
    return false;
}
return true;

Service Orders Require Owner

if ($data["RECID"] != "addNew" && $params['sd_type'] == 'sd-service-order' && 
    $data['SD_STATUS_LISTS_RECID'] == $this->listGetByCode('SD_STATUS', 'COMPLETE') &&
                empty($data['D_OWNER_CONTACTS_RECID']) &&
                empty($data['D_OWNER_DEPT_HIERARCHY_RECID'])
            ) {
  
                    $this->setMessage("An owner is required to complete the order. <br>" .
                                      "Please select an owner then save the record again.");
                                    return false;

            }
          //  return true;