(v1) Custom API 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(),
    ]]
];

<?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"];

<?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"];
}