Limit appending new records with custom conditions using dynamic-handler and Ajax

[telecastg]

This post describes how we implemented a limit on the frequency of service request submissions by tenants at our Rental Property business.

To avoid excessive or frivolous requests (ServiceTicket), our policy is to allow only one request every 4 months, so Tenants are encouraged to write down everything that might need attention instead of constantly requesting visits by Maintenance Technicians

If a Tenant tries to enter a new ServiceTicket when there is another ticket active/unresolved or when there was a ServiceTicket submitted less than 4 months ago for the same property, an alert will pop up and will prevent the Tenant from creating a new service ticket record.

ServiceTicket is a custom entity linked to a Tenancy custom entity in a many to one relationship.

When a Tenant wants to create a Service Ticket, the form requires a tenancyId (enum of a list of addresses of active rental contracts) field to be selected. That selection will trigger our excessive request filtering mechanism described here.

Please note that this tutorial describes our specific implementation, and while it can be relatively easy to customize, it will require reading and understanding the code, NOT simply cutting and pasting to adapt for your own application.

Steps:

1) Create a custom dynamic-handler class that includes an Ajax call to a back-end entry point to retrieve database data necessary to implement our logic.

client/custom/src/service-ticket-dynamic-handler.js

define('custom:service-ticket-dynamic-handler', ['dynamic-handler'], function (Dep) {

    return Dep.extend({

        currUser: false,

        init: function () {
            // load the current user object
            if(!this.currUser) {
                this.currUser = this.recordView.getUser();
            }
            // invoke the controlFieldsInit method on start up
            this.controlFieldsInit();

            // also invoke the controlFieldsInit method every time the tenancyId field is changed
            this.recordView.listenTo(
                this.model, 'change:tenancyId', this.controlFieldsInit.bind(this)
            );
        },

        controlFieldsInit: function () {
            // if the value for tenancy has been set, and the ticket is new, verify if there are excessive ticket submissions
            if(this.recordView.isNew && this.model.attributes.tenancyId ) {
                var params = {
                    isPortal: this.currUser.isPortal(),
                    ticketId: this.model.id,
                    tenancyId: this.model.attributes.tenancyId,
                    action: 'ticketRedundancyByTenancy'
                };
                // invoke the verifyRedundancy method passing the params object created above
                this.verifyRedundancy(params);
            } else {
                // invoke the controlFieldsFinal method
                this.controlFieldsFinal();
            }
        },

        verifyRedundancy: function(params) {
            // use plain javascript to invoke an entry point and an action that will return data on a currently open or excessive closed requests.
            var url = '?entryPoint=serviceTicketHandler';
            // make sure that all required parameters have been supplied to the function
            if(params.isPortal && params.ticketId && params.tenancyId && params.action) {
                var payload = JSON.stringify(params);
                var xmlhttp = new XMLHttpRequest();
                var self = this;
                var recordView = this.recordView;
                xmlhttp.onreadystatechange = function() {    
                    if (xmlhttp.readyState === XMLHttpRequest.DONE) { // XMLHttpRequest.DONE == 4
                        if (xmlhttp.status === 200) {
                            var serverResponse = xmlhttp.responseText;
                            var redundantTicketData = JSON.parse(serverResponse);
                            // display different alert messages to the user acording to the data received from the back end query
                            if(redundantTicketData.openTicketCreationDate ) {
                                // if there is another active ticket already opened, cancel the request
                                alert("There is an open ticket for this property, please select the ticket \nand use the Comments Board to report additional repairs. \nMultiple open tickets are not allowed.");
                                // if the record is being created from a modal view, simulate clicking the 'Cancel' button
                                if($( "button[data-name='cancel']" ).length > 0) {
                                    $( "button[data-name='cancel']" ).trigger( "click" );
                                } else {
                                    // otherwise, close the record detail/edit view
                                    recordView.cancel();
                                }
                            } else if(redundantTicketData.latestExcessiveTicket) {
                                // if there is excessive ticket activity display warning message and cancel the request
                                var message = "The system detects excessive Service Request submissions. \n";
                                message += "The last request was submitted on " +redundantTicketData.latestExcessiveTicket+ ". \n";
                                message += "The soonest a new request can be submitted will be on " + redundantTicketData.nextAllowedTicket+".";
                                alert(message);
                                //if the record is being created from a modal view, simulate clicking the 'Cancel' button
                                if($( "button[data-name='cancel']" ).length > 0) {
                                    $( "button[data-name='cancel']" ).trigger( "click" );else {
                                    // otherwise, close the record detail/edit view
                                    recordView.cancel();
                            } else {
                                // continue with the rendering sequence
                                self.controlFieldsFinal();
                            }
                        } else if (xmlhttp.status === 400) {
                            alert('There was an error 400');
                        } else {
                            alert('something else other than 200 was returned');
                        }
                    }
                };
                xmlhttp.open("POST",url , true);
                xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                xmlhttp.send("data="+payload);
            } else {
                console.log('Incomplete parameter set passed to method verifyRedundancy() at service-ticket-dynamic-handler . Object received was: ',params);
            }
        },

        controlFieldsFinal: function () {
            // make the tenacy field required
            this.recordView.setFieldRequired('tenantName');
        }

    });
});

2) Create the back-end entry point that will receive the Ajax request from the dynamic-handler and will invoke a custom function at the ServiceTicket Service class to retrieve the excessive Service Request data

custom/Espo/Custom/EntryPoints/ServiceTicketHandler.php

namespace Espo\Custom\EntryPoints;

use \Espo\Core\Exceptions\NotFound;
use \Espo\Core\Exceptions\Forbidden;
use \Espo\Core\Exceptions\BadRequest;

class ServiceTicketHandler extends \Espo\Core\EntryPoints\Base
{
    public static $authRequired = true;
    // set the number of days for cut-off of excessive request filter
    public $excessDays = 122;

    // default action
    public function run()
    {
        // proceed in accordance to the type of input received from the Ajax call
        if(isset($_REQUEST['data'])) { // urlencoded string input
            //convert the JSON string into a PHP associative array
            $payload = json_decode($_REQUEST['data'], true);
        } else { // serialized JSON object
            //convert the POST JSON input received into a PHP associative array
            $requestPayload = file_get_contents("php://input");
            $payload = json_decode($requestPayload, true);
        }
        $action = $payload["action"];
        if(!$action) {
            throw new BadRequest('ServiceTicketHandler.php "action" parameter is missing');
        }
        // although in this example we are implementing one action, the switch structure allows to define other ServiceRequest related actions that can also be invoked via Ajax for other purposes
        switch ($action) {
            case 'ticketRedundancyByTenancy':
                $isPortal = $payload["isPortal"];
                $tenancyId = $payload["tenancyId"];
                if(!$tenancyId) {
                    throw new BadRequest('ServiceTicketHandler.php "tenancyId" parameter is missing');
                } else {
                    // invoke the getTicketRedundancyByTenancy method at the ServiceTicket service class to fetch the excessive request data from the database
                    $redundantTicketData = $this->getContainer()->get('serviceFactory')->create('ServiceTicket')->getTicketRedundancyByTenancy($tenancyId,$isPortal ,$this->excessDays);
                    // return the excessive request data to the Ajax call
                    echo $redundantTicketData;
                }
            break;

            default:
                //code to be executed if $action is different from all labels;
            }
    }

} 

Because of the limit of words allowed on each post, this tutorial continues below

[telecastg]

3) Create the ServiceTicket service class method that will retrieve the excessive request data from the database

custom/Espo/Custom/Services/ServiceTicket.php

namespace Espo\Custom\Services;

class ServiceTicket extends \Espo\Core\Templates\Services\Base {

    public function getTicketRedundancyByTenancy($tenancyId, $isPortal, $excessDays) {
        // build the response array
        $redundantTicketData = array(
            'latestExcessiveTicket' => '',
            'nextAllowedTicket' => '',
            'openTicketCreationDate' => ''
        );

        // initialize PDO
        $pdo = $this->getEntityManager()->getPDO();
        // determine if there is a currently open ticket for the same tenancy
        $sql = 'SELECT `created_at` FROM `service_ticket` WHERE `tenancy_id` = "' . $tenancyId . '" AND `status` <> "Completed" AND `status` <> "Canceled" AND `status` <> "Rejected" AND `deleted` <> "1" ORDER By `created_at` DESC LIMIT 1';
        $ps = $pdo->query($sql);
        if ($ps) {
            $dataArr = $ps->fetchAll();
        } else {
            $dataArr = [];
        }

        // if there is an open ticket load the response object with the creation date and return the result
        if ($dataArr && $dataArr[0]['created_at']) {
            $redundantTicketData['openTicketCreationDate'] = $dataArr[0]['created_at'];
            return json_encode($redundantTicketData);
        // if there are no open tickets, check for excessive submissions by portal users
        } else {
            if($isPortal) {
                // determine the threshold date for excessive ticket submission
                $dt = new \DateTime();
                $dt->modify('-' . $excessDays . ' days');
                $excessiveTicketThreshold = $dt->format('Y-m-d H:i:s');
                // $GLOBALS['log']->debug('excessiveTicketThreshold = ', [$excessiveTicketThreshold]);
                // find the most recent ticket within the excessive threshold
                $sql = 'SELECT `created_at` FROM `service_ticket` WHERE `created_at` > "' . $excessiveTicketThreshold . '" AND `tenancy_id` = "' . $tenancyId . '" AND `is_priority` <> "1" AND `deleted` <> "1" ORDER By `created_at` DESC LIMIT 1';
                $ps = $pdo->query($sql);
                if ($ps) {
                    $dataArr = $ps->fetchAll();
                } else {
                    $dataArr = [];
                }
                if ($dataArr[0]['created_at']) {
                    $latestDt = new \DateTime($dataArr[0]['created_at']);
                    $latestExcessiveTicketDate = $latestDt->format('m/d/Y');
                    $nextAllowedTicketDate = $latestDt->modify('+' . $excessDays . ' days')->format('m/d/Y');
                    $redundantTicketData['latestExcessiveTicket'] = $latestExcessiveTicketDate;
                    $redundantTicketData['nextAllowedTicket'] = $nextAllowedTicketDate;
                }
            }
        }
        // return data as json object
        return json_encode($redundantTicketData);
    }
} 

4) Let Espo know that there is a dynamic-handler class to execute when rendering a detail view of the ServiceTicket entity by adding the code below to the ServiceTicket clientDefs metadata file.

custom/Espo/Custom/Resources/metadata/clientDefs/ServiceTicket.json

"dynamicHandler": "custom:service-ticket-dynamic-handler"

5) Clear cache and rebuild.

For more information about the dynamic-handler class check the documentation https://docs.espocrm.com/development/dynamic-handler/