© John Kouraklis 2019
John KouraklisIntroducing Delphi ORMhttps://doi.org/10.1007/978-1-4842-5013-6_3

3. Call Center Application

John Kouraklis1 
(1)
London, UK
 

We are now ready to make our work more practical. We have seen different approaches in using ORM frameworks, and we have a good understanding of the fundamentals of Aurelius. In this chapter, we work on setting up the building blocks that will allow us to see Aurelius in practice.

The Application

For the purpose of this book, we are going to develop the back end and the code of the database layer of an application that manages data from a call center. The data set and the dashboard come from Trump Excel (Bansal, 2019). Sumit Bansal has created a great site on Excel and offers data from a call center free to use. I have modified the original data file slightly by adding some columns that are more suitable for our examples.

The call center provides support to customers of a business that has five departments (air conditioner, fridge, television, toaster, washing machine), and the center employs eight agents who handle the calls (Becky, Dan, Diane, Greg, Jim, Joe, Martha, Stewart).

The dataset covers the operations of the center for the duration of a month (January 2016). The workflow in the center is as follows:
  1. 1.

    A customer calls the center. The IT system assigns a Call ID to the call and records the date (Date) and the time the call reaches the center (Queue Enter Time).

     
  2. 2.

    The call stays in a waiting queue until an agent is available. When an agent is released, the call is transferred to the Agent and the system records the time (Queue Exit Time) of transfer. At the same time, the call is flagged as Answered.

     
  3. 3.

    The agent deals with the call for as long as it is necessary (Call Duration) and records the Department that the call is relevant to.

     
  4. 4.

    At this stage, the call can be either Resolved or not.

     
  5. 5.

    In the end, the caller rates the agent in a satisfaction scale from 1 to 5 (Satisfaction Rating).

     
  6. 6.

    Sometimes, a call may be transferred to an agent, but for some reasons (technical or others) the call drops. In this case, the call is logged as both not Answered and not Resolved.

     
You can find the data in the CallCentreData.csv comma-separated values (CSV) file in the Misc folder. It comes with the code of this book. Figure 3-1 shows what the file looks like, and the first row shows the headers as indicated in the preceding bullet points.
../images/481234_1_En_3_Chapter/481234_1_En_3_Fig1_HTML.jpg
Figure 3-1

Excerpt from the log file with the call center data set

The application has three panels: the first one holds the Dashboard, the second one the Department management, and the last one the Agent management.

Dashboard

The Dashboard page can be seen in Figure 3-2. It has a sidebar where the user can filter the results by weeks in the month, and it includes a number of panels.

  • A panel with the overall satisfaction score, the total calls for the period of one month, the average answer speed (sec), the abandon rate (%), and the calls per minute

  • A list with the statistics about the agents: total calls, answered calls, average speed of answer(sec), call resolution (%), and call resolution trend

  • A chart with the call abandon rate by department

  • A chart with the satisfaction score per agent

  • A panel with the service-level agreement limits regarding the number of calls answered in less than 180 seconds and the number of calls with satisfaction score of less than 3

../images/481234_1_En_3_Chapter/481234_1_En_3_Fig2_HTML.jpg
Figure 3-2

The Dashboard tab

Departments

Figure 3-3 shows the tab with the departments. It consists of a grid at the top with the stored departments in the database. In the grid you can, also, see the ID (primary key) for demonstration purposes. At the bottom of this grid, there are three buttons that allow the user to add, edit, and delete departments.
../images/481234_1_En_3_Chapter/481234_1_En_3_Fig3_HTML.jpg
Figure 3-3

The Departments tab

Each one of these actions shows a window like the one in Figure 3-4. The labels and the buttons are self-explanatory.
../images/481234_1_En_3_Chapter/481234_1_En_3_Fig4_HTML.jpg
Figure 3-4

The form allows the addition and editing of the departments

There is another grid that fills in the rest of the form. When a department is selected in the top grid, the user can see the calls filtered by the selected department.

Agents

The management of the agents follows the same logic as the department management (Figure 3-5). In terms of design and functionality, they remain the same.
../images/481234_1_En_3_Chapter/481234_1_En_3_Fig5_HTML.jpg
Figure 3-5

The Agents tab

The Interface

It’s time we build our application. We start with the user interface by creating the forms we need, as in the preceding figures. The forms include a series of grids, layouts, tabs, and buttons. I am not going to go through the details of how to build the GUI because it will take unnecessary space and will distract us from the main purpose of this book. You can find a skeleton project that we will use in this and the next chapters in the code that accompanies this book. The project is named CallCentreSkeleton.

In order to make it easier to follow the code in the rest of the book, there are a number of points to raise regarding the design of CallCentreSkeleton:

  • The dashboard has a sidebar that filters the data per week. We will update the data in updateDashboard, which is called in the OnChange event of the TabControl.

  • We will add update methods for the agents and the departments in later chapters.

  • There is a setupGUI method that initializes the GUI the first time the application runs.

The skeleton project is runnable. Open the project and just run it. In the main form (TFormMain in MainForm.pas), you can click the buttons in the sidebar, change the tabs, and click the “Add Agent” or “Add Department” buttons in the corresponding tabs. You should be able to see a separate form (TFormEntity in EntityForm.pas) in the project. We will start adding up new code shortly to the project, so you may wish to save it under a new name if you would like to keep the skeleton project intact.

Note

In our code, we will not adhere to any specific design pattern such as MVP/MVC/MVVM or any other similar approaches. Our priority is to write code that demonstrates Aurelius features rather than respect the principles of any design styles.

Entities

The model behind the call center suggests three classes: one for the departments (TDepartment), one for the agents (TAgent), and one for the calls (TCall). For simplicity, both TDepartment and TAgent have two simple fields:

  • ID: A property that holds a GUID as primary key

  • Description: A property that holds the name of the agent or the name of the department

Let’s go ahead and create the entities. You can find the complete project in the folder CallCentre – Without Database Session in the code of the book.
  1. 1.

    Open the CallCentreSkeleton project or create a copy.

     
  2. 2.

    Add a new unit and save it under the name Entities.pas. This unit will hold all the attribute mapping of our classes.

     
  3. 3.
    In Entities.pas , add the following two classes:
    uses
      SysUtils,
      Aurelius.Mapping.Attributes,
      Aurelius.Types.Nullable,
      Aurelius.Types.Proxy;
    type
      [Entity]
      [Table('Agent')]
      [Id('FID', TIdGenerator.Guid)]
      TAgent = class
        private
          [Column('ID', [TColumnProp.Required])]
          FID: TGuid;
          [Column('Description', [TColumnProp.Required], 255)]
          FDescription: string;
          [Column('Photo', [TColumnProp.Lazy])]
          FPhoto: TBlob;
      public
        property ID: TGuid read FID write FID;
        property Description: string read FDescription write FDescription;
        property Photo: TBlob read FPhoto write FPhoto;
      end;
      [Entity]
      [Table('Department')]
      [Id('FID', TIdGenerator.Guid)]
      TDepartment = class
        private
          [Column('ID', [TColumnProp.Required])]
          FID: TGuid;
          [Column('Description', [TColumnProp.Required], 255)]
          FDescription: string;
      public
        property ID: TGuid read FID write FID;
        property Description: string read FDescription write FDescription;
      end;

    I decorated the two classes with the very basic attributes to let Aurelius know that I treat TDepartment and TAgent as entities. I have also indicated that FID private field works as the primary key at the database level, uses Smart GUID engine, and is linked to column ID. Similarly, I have introduced the Description property to be of string type. Then, I linked it back to the relevant field, which I made compulsory at database level using the TColumnProp.Required parameter and set the length to an arbitrary value. The mapping of these two classes is straightforward and in line with what we discussed in Chapter 2.

     
  4. 4.
    The TCall class is more complicated as it holds more fields both compulsory and nullable; however, the principles to provide information to Aurelius are as before. In the same unit, add the following class:
    ...
    type
      ...
      [Entity]
      [Table('Call')]
      [Id('FID', TIdGenerator.SmartGuid)]
      TCall = class
      private
        [Column('ID', [TColumnProp.Required])]
        FID: TGuid;
        [Column('Date', [TColumnProp.Required])]
        FDate: TDateTime;
        [Column('QueueEntryTime', [TColumnProp.Required])]
        FQueueEntryTime: TDateTime;
        [Column('QueueExitTime', [])]
        FQueueExitTime: Nullable<TDateTime>;
        [Column('ServiceStartTime', [])]
        FServiceStartTime: Nullable<TDateTime>;
        [Column('ServiceEndTime', [])]
        FServiceEndTime: Nullable<TDateTime>;
        [Column('Answered', [TColumnProp.Required])]
        FAnswered: Integer;
        [Column('Resolved', [TColumnProp.Required])]
        FResolved: Integer;
        [Column('SatisfactionRate', [])]
        FSatisfactionRate: Nullable<Integer>;
        [Column('CallID', [TColumnProp.Required], 50)]
        FCallID: string;
      public
        property ID: TGuid read FID write FID;
        property Date: TDateTime read FDate write FDate;
        property QueueEntryTime: TDateTime read FQueueEntryTime write FQueueEntryTime;
        property QueueExitTime: Nullable<TDateTime> read FQueueExitTime write FQueueExitTime;
        property ServiceStartTime: Nullable<TDateTime> read FServiceStartTime write FServiceStartTime;
        property ServiceEndTime: Nullable<TDateTime> read FServiceEndTime write FServiceEndTime;
        property Answered: Integer read FAnswered write FAnswered;
        property Resolved: Integer read FResolved write FResolved;
        property SatisfactionRate: Nullable<Integer> read FSatisfactionRate write FSatisfactionRate ;
        property CallID: string read FCallID write FCallID;
      end;
    The initial design of our application requires the ability to indicate that a specific call is assigned to a particular agent and refers to a specific department. At class level, these two requirements are resolved by introducing two properties in TCall holding a TAgent and TDepartment classes, respectively.
    type
      ...
      TCall = class
      private
      ...
        [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
        [JoinColumn('AgentID', [], 'ID')]
        FAgentID: Proxy<TAgent>;
        [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
        [JoinColumn('DepartmentID', [], 'ID')]
        FDepartmentID: Proxy<TDepartment>;
        function GetAgentID: TAgent;
        procedure SetAgentID(const Value: TAgent);
        function GetDepartmentID: TDepartment;
        procedure SetDepartmentID(const Value: TDepartment);
      public
        ...
        property AgentID: TAgent read GetAgentID write SetAgentID;
        property DepartmentID: TDepartment read GetDepartmentID write SetDepartmentID;
      end;
    ...
    function TCall.GetAgentID: TAgent;
    begin
      result := FAgentID.Value;
    end;
    procedure TCall.SetAgentID(const Value: TAgent);
    begin
      FAgentID.Value := Value;
    end;
    function TCall.GetDepartmentID: TDepartment;
    begin
      result := FDepartmentID.Value;
    end;
    procedure TCall.SetDepartmentID(const Value: TDepartment);
    begin
      FDepartmentID.Value := Value;
    end;

    The corresponding private fields are where we define how Aurelius should manage them at database level. The two properties AgentID and DepartmentID are lazy loaded as the TAssociationProp.Lazy parameter is used.

    The code, also, dictates what happens to the associated agent or department when a call is updated or deleted. The CascadeTypeAll - [TCascadeType.Remove] (or the equivalent CascadeTypeAllButRemove) parameter tells Aurelius that, in any other actions than removal (deletion) of a call, the department or the agent is updated. In other words, if the user deletes a call from the database, the linked agent and department records will be intact.

    Lastly, the getter and setter methods for AgentID and DepartmentID are straightforward. As discussed in the previous chapter, we have to use this approach, as the corresponding fields are declared as proxies.

    The preceding modifications allow us to link calls to agents and departments. It would be very convenient if we achieve the opposite as well. Given an agent (or a department), we would like to be able to drill down to all the calls they are associated with. Since Aurelius is aware of all the entities we need, we can reverse the associations by mapping properties using the ManyValuedAssociation attribute :
    uses
      ...,
      Generics.Collections;
    ...
    type
      ...
      TAgent = class
      private
        ...
            [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FAgentID')]
        FCallList: Proxy<TList<TCall>>;
        function GetCallList: TList<TCall>;
      public
        ...
        constructor Create;
        destructor Destroy; override;
        property CallList: TList<TCall> read GetCallList;
      end;
      TDepartment = class
      private
        ...
            [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FDepartmentID')]
        FCallList: Proxy<TList<TCall>>;
        function GetCallList: TList<TCall>;
      public
        ...
        constructor Create;
        destructor Destroy; override ;
        property CallList: TList<TCall> read GetCallList;
      end;
    ...
    constructor TAgent.Create;
    begin
      inherited;
      FCallList.SetInitialValue(TList<TCall>.Create);
    end;
    destructor TAgent.Destroy;
    begin
      FCallList.DestroyValue;
      inherited;
    end;
    function TAgent.GetCallList: TList<TCall>;
    begin
      result := FCallList.Value;
    end;
    constructor TDepartment.Create;
    begin
      inherited;
      FCallList.SetInitialValue(TList<TCall>.Create);
    end;
    destructor TDepartment.Destroy;
    begin
      FCallList.DestroyValue;
      inherited;
    end;
    function TDepartment.GetCallList: TList<TCall> ;
    begin
      result := FCallList.Value;
    end;

    We define CallList as a typical TList<T> property, and we pass it to Aurelius using the ManyValuedAssociation attribute. The field defines a lazy-loaded association which updates and merges any changes with the associated object. Lastly, the fields are nongeneric (TList) and proxified (Proxy); we need to use SetInitialValue and DestroyValue methods from Aurelius to manage their lifetime.

    We need to make sure that the compiler includes all the classes in the final binary file. This is easily done by adding the following code in the initialization section of Entities.pas :
    unit Entities;
    interface
    ...
    implementation
    ...
    initialization
      RegisterEntity(TAgent);
      RegisterEntity(TDepartment);
      RegisterEntity(TCall) ;
    end.
     

Database Connection

The next step is to configure Aurelius to connect to an actual database. Based on what we discussed in Chapter 2, there are a number of ways this can be done. We will create a typical SQLite database and, since Aurelius provides a native driver to this database, we use the available connection wizard:
  1. 1.

    Run the wizard in File ➤ Other… ➤ TMS Business ➤ TMS Aurelius DB Connection.

     
  2. 2.

    In the wizard, select Native direct connection (Driver Mode) and SQLite in the Driver pop-up menu.

     
  3. 3.

    Click Finish and allow Aurelius to add a new Data Module in the project under the name ConnectionModule.pas.

     
  4. 4.

    Open the module in the design editor, right-click AureliusConnection1 component and, in Connection Settings, add the database.db as the name of the database file. Then, close the form with the settings.

     
  5. 5.

    The ConnectionModule unit exposes a global variable named SQLiteConnection which provides reference to Aurelius connection. Although having a global variable is not considered the best way to write code, in our case it is sufficient.

     
  6. 6.
    In MainForm.pas, add the following code:
    ../images/481234_1_En_3_Chapter/481234_1_En_3_Figa_HTML.png
     

We first create an instance of the connection module. This provides access to the IDBConnection interface which is required every time we want to do operations on the database. Then, we define a local variable of TDatabaseManager which is used to update the database. You can notice that in order to create the database manager, I had to pass an instance of IDBConnection. This is supplied by the ConnectionModule as it is demonstrated in the preceding code.

In the code, I call dbManager.UpdateDatabase as the first thing that the application should do. UpdateDatabase retrieves the schema from the database and compares it with the current entity structure (virtual database objects) as defined by the mapping attributes. Then, it executes SQL statements to synchronize the database structure with the virtual database objects.

An alternative way to update the database schema is to combine the preceding code to a direct call to TDatabaseManager.Update class procedure as follows:
...
  SQLiteConnection:=TSQLiteConnection.Create(self);
  TDatabaseManager.Update(SQLiteConnection.CreateConnection);
...

Note

UpdateDatabase is not destructive. This means that if you delete some properties from an entity, Aurelius will not delete the corresponding columns in the database. This is something you need to do manually via SQL scripts.

The official manual indicates that the database manager provides another procedure that allows the creation and update of the database: BuildDatabase . This method is considered deprecated but still can be in use in code. The end result is the same as it updates the database schema as well; however, there is a slight difference. BuildDatabase does not perform reverse engineering of the database schema and any comparisons with the virtual database object; it, rather, starts executing the SQL statements based on the current state of code in the entities. If your database is really huge with hundreds of tables and columns, this approach may save you a few second which, in reality, may not even be noticeable. In any case, the recommended and modern way to update the database is to use UpdateDatabase.

IDatabaseSession

The database manager and, similarly, the object manager are declared as typical objects in Aurelius. This means that every time we use them, we naturally instantiate the objects and, eventually, we need to free them manually. This may become a source of boilerplate code especially if one considers the frequent usage of the object manager. In order to simplify this process, we will hide all those objects inside an interface (IDatabaseSession), and then we will use it as an adapter to pass the lifetime management of both objects to the interface. The code can be seen in the CallCentre – With Database Session folder.
  1. 1.

    Add a new unit in the project and save it under the name Database.Session.Types.pas.

     
  2. 2.
    Then, add the following code:
    unit Database.Session.Types;
    interface
    uses
      Aurelius.Engine.DatabaseManager,
      Aurelius.Engine.ObjectManager;
    type
      IDatabaseSession = interface
        ['{7CA1B4A1-F339-47EE-AE17-9436853A618E}']
        function databaseManager: TDatabaseManager;
        function objectManager: TObjectManager;
      end;
    implementation
    end.
     
  3. 3.

    Add a new unit in the project as Database.Session.pas.

     
  4. 4.
    Add the next code snippet:
    unit Database.Session;
    interface
    uses
      Database.Session.Types,
      Aurelius.Engine.DatabaseManager,
      Aurelius.Drivers.Interfaces,
      Aurelius.Engine.ObjectManager;
    type
      TDatabaseSession = class (TInterfacedObject, IDatabaseSession)
      private
        fConnection: IDBConnection;
        fDatabaseManager: TDatabaseManager;
        fObjectManager: TObjectManager;
      public
        constructor Create(const aConnection: IDBConnection);
        destructor Destroy; override;
    {$REGION 'Interface'}
        function databaseManager: TDatabaseManager;
        function objectManager: TObjectManager;
    {$ENDREGION}
      end;
    implementation
    constructor TDatabaseSession.Create(const aConnection: IDBConnection);
    begin
      Assert(aConnection <> nil);
      inherited Create;
      fConnection:=aConnection;
    end;
    function TDatabaseSession.databaseManager: TDatabaseManager;
    begin
      if not Assigned(fDatabaseManager) then
        fDatabaseManager:=TDatabaseManager.Create(fConnection);
      Result:=fDatabaseManager;
    end;
    destructor TDatabaseSession.Destroy;
    begin
      fDatabaseManager.Free;
      fObjectManager.Free;
      inherited;
    end;
    function TDatabaseSession.objectManager: TObjectManager;
    begin
      if not Assigned(fObjectManager) then
        fObjectManager:=TObjectManager.Create(fConnection);
      Result:=fObjectManager;
    end;
    end.

    There are a number of things happening in this unit. First, in Create, we check if the passed IDBConnection is valid. This is not relevant to Aurelius, but it is good practice to always check the injected parameters. Then, we store the connection for future use. The functions that return both the database manager and the object manager check if there is already a valid instance of the relative manager stored in the object. If this is the case, then they return the instance; otherwise, they create a new one.

     
  5. 5.
    In MainForm.pas, under the FormCreate event, we can now use the IDatabaseSession interface:
    uses
      ...,
      Database.Session.Types,
      Database.Session;
    ...
    procedure TFormMain.FormCreate(Sender: TObject);
    var
      dbSession: IDatabaseSession;
    begin
      SQLiteConnection:=TSQLiteConnection.Create(self);
      dbSession:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      dbSession.databaseManager.UpdateDatabase;
      setupGUI;
    end;
     

This looks simpler and more efficient. We still need to pass the IDBConnection to the constructor, and then we get access to the database manager and the object manager via the functions as exposed by the interface. In this instance, we do not need to free the objects.

If you are looking for more complete code, a try-except branch should wrap the call to UpdateDatabase, but I do not include it in this code and in the following examples purely for simplicity.

Summary

In this chapter, we set up the background to explore Aurelius further. The example application is introduced (CallCentre), and we defined the entities and the database connection as per our design and requirements. Furthermore, the IDatabaseSession interface is developed. In the next chapter, we will start using this interface by executing simple tasks.

Reference

Bansal, S., 2019. Trump Excel. [Online] Available at: https://trumpexcel.com/call-center-performance-dashboard-excel/ [Accessed 01 04 2019].

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.80.101