Custom Logic Library
For the convenience of our customers, this Library contains pre-built Custom Logic that anyone may copy and add to their PCR-360 application. If you require Custom Logic not provided by this Library, please contact your Account Manager to request Professional Services; your Account Manager will provide you with a quote to create Custom Logic tailored to your needs. For Example, PCR has many customers integrating with Peoplesoft, Banner, Remedy, and/or TeamDynamics with PCR-360. We would be happy to assist in developing these integrations to assist your Organization's needs.
Custom API Library
For details on writing a Custom API Endpoint, please see our wiki article.
Process Incoming Slack API Calls
This Custom API script was written to process incoming API calls from Slack (a team chat program we use internally at PCR).
<?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(),
]]
];
Service Now Integration - Move Service
Service Now Integration is built upon PCR-360's Custom API Endpoints. Service Now will send its requests to the appropriate Endpoint and PCR-360 will process the details accordingly.
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"];
Service Now Integration - New Service
Service Now Integration is built upon PCR-360's Custom API Endpoints. Service Now will send its requests to the appropriate Endpoint and PCR-360 will process the details accordingly.
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"];
}
Custom Events Library
For details on writing a Custom Event Endpoint, please see our wiki article.
Equipment Details Sync
Sync Service GLA, Owner and Location to Equipment
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
This Custom Event relies on UDFs for 'End_Date' and "Rollover_GLA" to roll over a GLA for billing on a given date.
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 Service Locations Clean Up
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
Move Available Services to their Parent Category
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
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",
]);
}
Scheduled Maintenance
Scheduled Maintenance required for Equipment is a key component in using PCR-360 for Facilities Management. Users can set up a Custom Event that will assist in scheduling regular maintenance for Equipment by automatically creating Incidents for that Equipment to be maintained. In order to create this automated behavior first there need to be three User Defined Fields (UDFs) set to track needed maintenance.
To set up the UDFs first navigate to Admin > User Defined Fields > User Defined Fields. Create the UDFs needed by following these steps:
Create a Maintenance Period Type UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_TYPE".
Add a 'Label' of "Maintenance Period".
Set the 'Field Type' to "Dropdown".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Eqp Catalog"
Click the 'Save New' UNKNOWN_ATTACHMENT button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
When the form refreshes select the 'Dropdown Options' tab.
Click the 'Add' button and add three new options:
Add a 'Value' and 'Code' of "Years".
Add a 'Value' and 'Code' of "Months".
Add a 'Value' and 'Code' of "Days".
When done the options should look like this:
Close the 'Manage Field Association' form.
Create a Maintenance Period Quantity UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_QTY".
Add a 'Label' of "Maintenance Period Quantity".
Set the 'Field Type' to "Number".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Eqp Catalog"
Click the 'Save New' button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
Create a Maintenance Date UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_DATE.
Add a 'Label' of "Maintenance Date".
Set the 'Field Type' to "Date".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Equipment"
Select an 'Equipment Catalog' to limit the UDF to the type that should set for the regular maintenance.
Click the 'Save New' button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
Once all of three of the UDFs have been created the next step is to add a Custom Event. To add the Custom Event follow these steps:
Navigate to Admin > Custom Logic > Events.
Click the 'Add'
button to start a new Event.Set the 'Identifier' field to "Scheduled Maintenance".
In the 'Logic' tab enter the logic from the "Schedule Maintenance" expand below:
Click the 'Save New' button.
Now that initial data set up is finished, simply find the Equipment Catalog from step 1 and enter information into the two UDFs setup in steps 1 and 2, Maintenance Period and Maintenance Period Quantity respectively. The Quantity should represent how many periods should pass before triggering the event. For example, to set a maintenance period every 3 months, set the Quantity to "3". Next, open the Equipment record and set the Maintenance Period Date UDF to the next Maintenance Date.The Custom Event will now automatically run in the background. When the Custom Event runs, a new Incident for each Equipment with a Maintenance Date that matches the current date will be created and then the Maintenance Date will be set to the next interval period. These Incidents can then be worked normally to accomplish the regularly scheduled maintenance tasks.
Custom Validation Library
For details on writing a Custom Validation, please see our wiki article.
Prevent Duplicate Charge Catalog Entries
This Custom Logic is for a Custom Validation.
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 Entries
Prevent duplicate Equipment Catalogs
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
This Custom Logic is for a Custom Validation.
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;
For the convenience of our customers, this Library contains pre-built Custom Logic that anyone may copy and add to their PCR-360 application. If you require Custom Logic not provided by this Library, please contact your Account Manager to request Professional Services; your Account Manager will provide you with a quote to create Custom Logic tailored to your needs. For Example, PCR has many customers integrating with Peoplesoft, Banner, Remedy, and/or TeamDynamics with PCR-360. We would be happy to assist in developing these integrations to assist your Organization's needs.
Custom API Library
For details on writing a Custom API Endpoint, please see our wiki article.
Process Incoming Slack API Calls
This Custom API script was written to process incoming API calls from Slack (a team chat program we use internally at PCR).
<?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(),
]]
];
Service Now Integration - Move Service
Service Now Integration is built upon PCR-360's Custom API Endpoints. Service Now will send its requests to the appropriate Endpoint and PCR-360 will process the details accordingly.
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"];
Service Now Integration - New Service
Service Now Integration is built upon PCR-360's Custom API Endpoints. Service Now will send its requests to the appropriate Endpoint and PCR-360 will process the details accordingly.
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"];
}
Custom Events Library
For details on writing a Custom Event Endpoint, please see our wiki article.
Equipment Details Sync
Sync Service GLA, Owner and Location to Equipment
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
This Custom Event relies on UDFs for 'End_Date' and "Rollover_GLA" to roll over a GLA for billing on a given date.
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 Service Locations Clean Up
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
Move Available Services to their Parent Category
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
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",
]);
}
Scheduled Maintenance
Scheduled Maintenance required for Equipment is a key component in using PCR-360 for Facilities Management. Users can set up a Custom Event that will assist in scheduling regular maintenance for Equipment by automatically creating Incidents for that Equipment to be maintained. In order to create this automated behavior first there need to be three User Defined Fields (UDFs) set to track needed maintenance.
To set up the UDFs first navigate to Admin > User Defined Fields > User Defined Fields. Create the UDFs needed by following these steps:
Create a Maintenance Period Type UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_TYPE".
Add a 'Label' of "Maintenance Period".
Set the 'Field Type' to "Dropdown".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Eqp Catalog"
Click the 'Save New' UNKNOWN_ATTACHMENT button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
When the form refreshes select the 'Dropdown Options' tab.
Click the 'Add' button and add three new options:
Add a 'Value' and 'Code' of "Years".
Add a 'Value' and 'Code' of "Months".
Add a 'Value' and 'Code' of "Days".
When done the options should look like this:
Close the 'Manage Field Association' form.
Create a Maintenance Period Quantity UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_QTY".
Add a 'Label' of "Maintenance Period Quantity".
Set the 'Field Type' to "Number".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Eqp Catalog"
Click the 'Save New' button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
Create a Maintenance Date UDF
Create a new UDF by clicking the 'Add' button.
In the 'UDF Identifier' field set the value to "MAINTENANCE_DATE.
Add a 'Label' of "Maintenance Date".
Set the 'Field Type' to "Date".
The header should look like this example:
In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
Set the 'Association Table' dropdown to "Equipment"
Select an 'Equipment Catalog' to limit the UDF to the type that should set for the regular maintenance.
Click the 'Save New' button.
Close the 'Manage Field Association' form.
Click the 'Save New' button to save the UDF.
Once all of three of the UDFs have been created the next step is to add a Custom Event. To add the Custom Event follow these steps:
Navigate to Admin > Custom Logic > Events.
Click the 'Add'
button to start a new Event.Set the 'Identifier' field to "Scheduled Maintenance".
In the 'Logic' tab enter the logic from the "Schedule Maintenance" expand below:
Click the 'Save New' button.
Now that initial data set up is finished, simply find the Equipment Catalog from step 1 and enter information into the two UDFs setup in steps 1 and 2, Maintenance Period and Maintenance Period Quantity respectively. The Quantity should represent how many periods should pass before triggering the event. For example, to set a maintenance period every 3 months, set the Quantity to "3". Next, open the Equipment record and set the Maintenance Period Date UDF to the next Maintenance Date.The Custom Event will now automatically run in the background. When the Custom Event runs, a new Incident for each Equipment with a Maintenance Date that matches the current date will be created and then the Maintenance Date will be set to the next interval period. These Incidents can then be worked normally to accomplish the regularly scheduled maintenance tasks.
Custom Validation Library
For details on writing a Custom Validation, please see our wiki article.
Prevent Duplicate Charge Catalog Entries
This Custom Logic is for a Custom Validation.
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 Entries
Prevent duplicate Equipment Catalogs
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
This Custom Logic is for a Custom Validation.
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;