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

5. Querying the Database

John Kouraklis1 
(1)
London, UK
 

Our application is now capable of providing the basic management of the database entities. We can add, edit, and delete agents and departments, and our database is populated with a list of calls. Let us move on further and allow Aurelius to provide us with some useful information from the database.

Listings

In both Departments and Agents tabs, there is a grid at the bottom of the forms with information about the calls. You can find the code we use in this chapter in the Call Centre – Listings folder.

Departments

The grid shows data filtered by the Department or the Agent. In order to do this, you need to select a line in the Department or Agent grids. We begin with the Department grid. This grid has the following columns:

  • Nr: Row number

  • CallID: The ID of the call

  • Date: The date of the call

  • Entry Time: The time the call entered the queue

  • Waiting Time: The time the call stays in the queue

  • Duration: The duration of the call when an agent picks it up

  • Resolved: Indicated whether the issue in the call is resolved

  • Satisfaction Rate: The satisfaction rate the customer gave to the agent

Some of the fields (CallID, Date, Entry Time, Resolved, Satisfaction Rate) are directly stored in our database so we can populate them very easily by just retrieving the data.
  1. 1.
    Add a new private procedure in the form (updateDepartmentList) where
    interface
    ...
    type
      TFormMain = class(TForm)
      ...
      private
      ...
        procedure updateDepartmentList;
      ...
      end;
    ...
    implementation
    ...
     
  2. 2.
    Add the following lines in updateDepartmentList:
    procedure TFormMain.updateDepartmentList;
    var
      session: IDatabaseSession;
      callList: TObjectList<TCall>;
      call: TCall;
    begin
      ...
      session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      callList:=session.objectManager.Find<TCall>
                                .CreateAlias('DepartmentID', 'department')
                                  .Where(Linq['department.ID'] =
                                    sgDepartments.Cells[2, sgDepartments.Selected])
                                .List;
      ...
      for call in callList do
      begin
        sgDepartmentDetails.RowCount := sgDepartmentDetails.RowCount + 1;
        sgDepartmentDetails.Cells[0, sgDepartmentDetails.RowCount - 1]:= sgDepartmentDetails.RowCount.ToString;
        sgDepartmentDetails.Cells[1, sgDepartmentDetails.RowCount - 1]:= call.CallID;
        sgDepartmentDetails.Cells[2, sgDepartmentDetails.RowCount - 1]:= FormatDateTime('dd/mm/yyyy', call.Date);
        sgDepartmentDetails.Cells[3, sgDepartmentDetails.RowCount - 1]:= FormatDateTime('hh:mm:ss', call.QueueEntryTime);
        if call.Resolved = 0 then
          sgDepartmentDetails.Cells[6, sgDepartmentDetails.RowCount - 1]:= 'N'
        else
          sgDepartmentDetails.Cells[6, sgDepartmentDetails.RowCount - 1]:= 'Y';
        if call.SatisfactionScore.HasValue then
          sgDepartmentDetails.Cells[7, sgDepartmentDetails.RowCount - 1]:= call.SatisfactionScore.Value.ToString;
      end;
      ...
      callList.Free;
      ...
    end;

    Nothing new here in terms of Aurelius’ functionality. We retrieve the list of the calls based on the Department.ID from the first grid. Note that SatisfactionScore is a nullable field and, therefore, we check if there is a value in the field. If so, we use the Value property to TNullable<TCall> to extract the field.

    The other columns of the grid require some calculations but this is straightforward. We check if nullable fields have values and we proceed with some calculations.
    procedure TFormMain.updateDepartmentList;
    var
      session: IDatabaseSession;
      callList: TObjectList<TCall>;
      call: TCall;
    begin
      ...
      for call in callList do
      begin
        if call.QueueExitTime.HasValue then
          sgDepartmentDetails.Cells[4, sgDepartmentDetails.RowCount - 1]:=
                           FormatDateTime('hh:mm:ss', call.QueueExitTime.Value - call.QueueEntryTime);
        if call.ServiceStartTime.HasValue and call.ServiceEndTime.HasValue then
          sgDepartmentDetails.Cells[5, sgDepartmentDetails.RowCount - 1]:=
                           FormatDateTime('hh:mm:ss', call.ServiceEndTime.Value - call.ServiceStartTime.Value);
      end;
      ...
    end;
     
  3. 3.
    Call updateDepartmentList in the OnSelectCell event of the grid with the departments:
    procedure TFormMain.sgDepartmentsSelectCell(Sender: TObject; const ACol, ARow:
        Integer; var CanSelect: Boolean);
    begin
      ...
      updateDepartmentList;
    end;
     

Agents

The grid with the details of the agents can be filled in in a similar way, but we will follow a different approach this time. When we discussed the ORM fundamentals, we saw that when entities are associated the endpoints are reciprocal. This means that you can start from one entity and reach the other directly via the association. In our case, a call is linked to an agent and, by association, if we start from the TAgent entity we should be able to get the linked TCalls which, by definition, generates a one-to-many association.
  1. 1.

    In Entities.pas, we have already added a TList property (CallList) in TAgent class (see Chapter 3).

     
  2. 2.
    In MainForm.pas, add a new private procedure updateAgentList to update the grid with the calls per agent:
    interface
    ...
    type
      TFormMain = class(TForm)
      ...
      private
      ...
        procedure updateAgentList;
      ...
      end;
    ...
    implementation
    ...
     
  3. 3.
    Add the following lines in updateAgentList :
    procedure TFormMain.updateAgentList;
    var
      session: IDatabaseSession;
      agent: TAgent;
      call: TCall;
    begin
      ...
      session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      try
        agent:=session.objectManager.Find<TAgent>(
                              StringToGUID(sgAgents.Cells[2, sgAgents.Selected]));
        if Assigned(agent) then
        begin
          ...
          for call in agent.CallList do
          begin
            sgAgentDetails.RowCount := sgAgentDetails.RowCount + 1;
            sgAgentDetails.Cells[0, sgAgentDetails.RowCount - 1]:= sgAgentDetails.RowCount.ToString;
            sgAgentDetails.Cells[1, sgAgentDetails.RowCount - 1]:= call.CallID;
            sgAgentDetails.Cells[2, sgAgentDetails.RowCount - 1]:=
                                               FormatDateTime('dd/mm/yyyy', call.Date);
            sgAgentDetails.Cells[3, sgAgentDetails.RowCount - 1]:=
                                               FormatDateTime('hh:mm:ss', call.QueueEntryTime);
            if call.QueueExitTime.HasValue then
              sgAgentDetails.Cells[4, sgAgentDetails.RowCount - 1]:=
                               FormatDateTime('hh:mm:ss', call.QueueExitTime.Value - call.QueueEntryTime);
            if call.ServiceStartTime.HasValue and call.ServiceEndTime.HasValue then
              sgAgentDetails.Cells[5, sgAgentDetails.RowCount - 1]:=
                               FormatDateTime('hh:mm:ss', call.ServiceEndTime.Value - call.ServiceStartTime.Value);
            if call.Resolved = 0 then
              sgAgentDetails.Cells[6, sgAgentDetails.RowCount - 1]:= 'N'
            else
              sgAgentDetails.Cells[6, sgAgentDetails.RowCount - 1]:= 'Y';
            if call.SatisfactionScore.HasValue then
              sgAgentDetails.Cells[7, sgAgentDetails.RowCount - 1]:=
                                       call.SatisfactionScore.Value.ToString;
          end;
        end;
      finally
        sgAgentDetails.EndUpdate;
        ...
      end;
      ...
    end;
     
  4. 4.
    Call updateAgentList in the OnSelectCell event of the agents’ grid:
    procedure TFormMain.sgAgentsSelectCell(Sender: TObject; const ACol, ARow:
        Integer; var CanSelect: Boolean);
    begin
      ...
      updateAgentList;
    end;
     

In this approach, we first load the agent based on the GUID that is selected when the user clicks the agent grid, and then we iterate through agent.CallList to get the calls associated to the particular agent. The rest of the code that populates the details of the calls in the grid is exactly the same as before.

In our application, the two approaches we used bring the same result. The code that uses the CallList is much simpler. The downside of this approach is that we cannot filter the results using Aurelius features as in the first approach. Instead, we need to run through the items of the TList and cut out whatever is not desirable. This may not pose a significant matter in CallCentre application but, in general, it is much preferable to do heavy tasks at the server side. Client-side processing may consume resources that can be valuable especially in mobile platforms.

Queries (Dashboard)

The dashboard holds lot of information of different nature and in different places. If you look at the data in the dashboard, you will notice that the entire data set consists of either aggregated (e.g., total calls) or calculated (e.g., calls/minute) values. In Chapter 2, we discussed that Aurelius is able to extract such values with the use of projections. The code files are located in the Call Centre – Queries folder.

Before we start filling the dashboard in, we need to consider the filtering we have introduced. The left sidebar allows the user to select the weeks, and it filters the data presented in the dashboard. We are going to use this filter in every calculation we make.

For this part, we need to calculate the following metrics:

  • Average Satisfaction Score (%)

  • Total Calls

  • Answer Speed (min:sec)

  • Abandon Rate (%)

  • Calls/Minute

  • Calls answered in less than 180 seconds

  • Calls with satisfactory rate less than 3

In addition to the preceding metrics, there is a list which presents data per agent (total calls, calls answered, average speed, call resolution percentage, and the call resolution trend; that is whether the agent’s ability to resolve calls is improving or not or remains the same). The dashboard also holds two graphs, but we are going to deal with them in the next chapter.

Go to MainForm.pas and add a new private procedure called calculateStatistics . We need to know the week the user selected in the sidebar, so we pass this information as a parameter to the procedure:
interface
...
type
  ...
  TFormMain = class(TForm)
     ...
  private
    ...
    procedure calculateStatistics(const aWeek: TWeeks);
  public
    ...
  end;
...
implementation
...
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  session: IDatabaseSession;
begin
  session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
end;
For now, the only thing we do is to create a new database session, as we have done many times already. To complete the setup of this procedure, add a call in the updateDashboard procedure . This will make sure the dashboard is updated every time the user selects the tab.
procedure TFormMain.updateDashboard(const aWeek: TWeeks);
begin
  ...
  calculateStatistics (aWeek);
end;

Next, we calculate the indicators separately to demonstrate the use of different functions Aurelius offers. Many instances of the following code can be combined in one call to Aurelius as they are under the same conditions.

Average Satisfaction Score (%)

This is the average satisfaction score of the calls. If you look at the initial csv file or the database, there are calls without satisfaction score; the field is null. We should not include them in the calculation of the average score.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
  projRes: TCriteriaResult;
begin
  ...
  projRes:=session.objectManager.Find<TCall>
                               .Select(TProjections.ProjectionList
                               .Add(TProjections.Avg('SatisfactionScore')
                                     .As_('SatisfactionScore'))
                                )
                              .Where(not Linq['SatisfactionScore'].IsNull)
                               .UniqueValue;
  if projRes.Values['SatisfactionScore'] <> Null then
    lbSatisfactionValue.Text:=
             format('%2.2f', [Double(projRes.Values['SatisfactionScore'])]);
  else
    lbSatisfactionValue.Text:='0.00';
  projRes.Free;
end;

Because we are retrieving one and only one result from the projection (SatisfactionScore), we get a TCriteriaResult instead of a TObjectList<TCriteriaResult>, as shown in Chapter 2. We do this by using UniqueValue. In this case, we also use the Avg function from Aurelius toolbox, and we pass a boolean expression in the Where clause to run the calculation only on valid records.

Weeks

The preceding code snippet calculates the average satisfaction score for all the calls in the database. We need to make use of the aWeek parameter in calculateStatistics. We have already inserted in the database the week of each call when we imported the data from the csv file. The value is held in the Week property of the TCall entity .

We can add a simple Linq expression to accommodate the user’s choice.
  ...
  projRes:=session.objectManager.Find<TCall>
                               .Select(TProjections.ProjectionList
                               .Add(TProjections.Avg('SatisfactionScore')
  .As_('SatisfactionScore'))
                                )
                          .Add(Linq['Week'] = integer(aWeek)+1)
                               .Where(not Linq['SatisfactionScore'].IsNull)
                       .UniqueValue;
  ...

You can click the buttons in the sidebar, and the satisfaction score is calculated for each week. However, if you look at the data in the database, you will notice that there are entries with week number 5. We calculated the week for each call using the WeekOfTheMonth function . Our data refers to January 2016, a month for which the first and last days fall in the middle of weeks. Therefore, the function, correctly, returns the calendar week number.

As a result, the code we added earlier misses some calls. It uses the TWeeks identifier to select the calls, but TWeeks has only four elements. This means that the calls that appear in week 5 do not make it in the result. We will add them in week 4’s calls, but we cannot do this inside the projection as we need to write some programming logic. Instead, we will create a separate function called filter and we will break the projection down. Projections in Aurelius are built using the TCriteria class as shown in the following code:
interface
...
type
  ...
  TFormMain = class(TForm)
     ...
  private
    ...
    function filter(const aWeek: TWeeks; const aCriteria: TCriteria): TCriteria;
  public
    ...
  end;
...
implementation
...
function TFormMain.filter(const aWeek: TWeeks; const aCriteria: TCriteria):
    TCriteria;
begin
  if aWeek = wWeek4  then
    result:= aCriteria.Add(Linq['Week'] >= 4)
  else
    result:= aCriteria.Add(Linq['Week'] = integer(aWeek)+1);
end;
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
  criteria: TCriteria;
begin
  session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
  criteria:=session.objectManager.Find<TCall>
                               .Select(TProjections.ProjectionList
                               .Add(TProjections.Avg('SatisfactionScore')
                                     .As_('SatisfactionScore'))
                                )
                               .Where(
                                   not Linq['SatisfactionScore'].IsNull);
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
  ...
  lbSatisfactionValue.Text:=format('%2.2f',  [Double(projRes.Values['SatisfactionScore'])]);
  ...
  projRes.Free;
end;

We define a TCriteria variable and we build the projection without passing it to the object manager because we need to populate it with the right filter. filter function accomplishes this, and then we call UniqueValue to retrieve the required information. For simplicity, the preceding code does not include the check for null content.

In this approach, we chose to make filter return TCriteria to modify the query. Another approach would be to make filter return TLinqExpression. This would allow us to use filter, directly, in the fluent interface as we build our query. You can see this in the following code and, as you can notice, there is no need to declare criteria at all:
...
  function filter(const aWeek: TWeeks): TLinqExpression;
begin
  if aWeek = wWeek4  then
    result:= Linq['Week'] >= 4
  else
    result:= Linq['Week'] = integer(aWeek)+1;
end;
...
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
...
begin
  ...
  projRes:=session.objectManager.Find<TCall>
                               .Select(TProjections.ProjectionList
                               .Add(TProjections.Avg('SatisfactionScore')
                                     .As_('SatisfactionScore'))
                                )
                               .Where(
                                   not Linq['SatisfactionScore'].IsNull)
                               .Add(filter(aWeek))
                               .UniqueValue;
  ...
end;

We could obviously add the lines from filter function directly in the code, but we are going to need it in more than one place in our code; therefore, a function looks for better solution. As a last note, we have to free the TCriteriaResult (projRes) but not the TCriteria (criteria). criteria is automatically destroyed when UniqueValue , List, or ListValues are called.

Total Calls

This is the number of calls the center receives. There are a couple of approaches here to get this number, but I think the simplest one is the following as we saw in a previous chapter:
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
  totalCalls: integer;
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .Select(TProjections.ProjectionList
                          .Add(TProjections.Count('ID').As_('TotalCalls'))
                                   );
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
  ...
  totalCalls:= projRes.Values['TotalCalls'];
  lbTotalCallsValue.Text:= totalCalls.ToString;
  ...
  projRes.Free;
end;

This time I introduce the variable totalCalls to get the result from the projection. The only reason I do this is because we will need this value for subsequent calculations.

Answer Speed

This is the average time in minutes and seconds a call stays in the queue before being assigned to an agent or dropped for any reasons.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .Select(TProjections.ProjectionList
                          .Add(TProjections.Avg(
      Linq['QueueExitTime'] - Linq['QueueEntryTime']).As_('AnswerSpeed'))
                                  )
                               .Where(not Linq['QueueExitTime'].IsNull);
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
  ...  lbAnswerSpeedValue.Text:=FormatDateTime('n:ss',projres.Values['AnswerSpeed']);
  ...
  projRes.Free;
end;

We have seen this pattern before; we use the Avg function of Aurelius and the IsNull condition to filter the entities. This time we pass a calculation (the subtraction) directly in a function and, finally, present the result in the right format. This snippet also shows that Aurelius is capable of managing different data types in a simple way.

Abandon Rate

This rate shows the percentage of the calls that reach the center but are not completed for any reason. For such calls, the system records an entry time, but the exit time from the queue is null. Therefore, we need to get the number of calls for which the QueueExitTime is null and divide it by the total calls the center received. We have stored this value in totalCalls. Thus, we would like Aurelius to calculate in a projection the following division:
TProjections.Count('ID') / totalCalls
and place it in an Add call:
.Add(TProjections.Count('ID') / totalCalls)
If you try this, the compiler will throw an error complaining that totalCalls is not of TSimpleProjection type as expected but an integer (constant). This is correct as Aurelius, internally, manages operations in projections that derive from TSimpleProjection. Literal<T> function comes to rescue as it can convert a constant to the correct type and can be used safely as part of the projections list. In our case, we convert totalCalls to a compatible form using the following code:
TProjections.Literal<integer>(totalCalls)
The final code to calculate and display the abandon rate takes the following form:
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .Select(TProjections.ProjectionList
                                     .Add(TProjections.Divide(
   TProjections.Count('ID') , TProjections.Literal<Integer>(totalCalls))
                                           .As_('AbandonRate'))
                                   )
                                   .Where(Linq['QueueExitTime'].IsNull);
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
...  lbAbandonRateValue.Text:=format('%3.2f',[Double(projres.Values['AbandonRate']) * 100]);
  ...
  projRes.Free;
end;

Calls/Minute

This is the number of calls the center receives divided by the total operation time of the center. Data reveals that the center is open between 09:00 and 18:00 (9 hours). Therefore, the calculation of this metrics is simple.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  lbCallsMinuteValue.Text:=format('%3.2f',[ totalCalls / 9 / 60]);
end;

Calls Answered in Less Than 180 Seconds

This indicator shows the calls that stayed in the queue for less than 3 minutes. In terms of calculations, we need to work out the difference between the times the calls entered and exited the queue and, then, pick those where the difference is less than 180 seconds. By now, we have all the building blocks to write this Aurelius projection.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .Select(TProjections.ProjectionList                           .Add(TProjections.Count('ID').As_('CallsLess180'))
                                   )
                                   .Where(
                (Linq['QueueExitTime'] - Linq['QueueEntryTime']) <
                                           EncodeTime(0, 3, 0, 0))
                                   .Where(not Linq['QueueExitTime'].IsNull);
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
  ...
  lbAnsweredLess180.Text:=projres.Values['CallsLess180'];
  ...
  projRes.Free;
end;

The only, perhaps, new element we have is that we can use computational conditions in a Where statement as the code shows. I encoded the 180 seconds limit as a time object.

Calls with Satisfactory Score Less Than 3

This time we are looking at the calls with satisfactory score less than 3. We need the absolute number and the fraction of these calls that correspond to the total calls. We are able to extract both pieces of data, directly, from the database using familiar methods. In this case, though, note how we can add more than one projection function in the same query. This is because we constantly use TProjections.ProjectionList to create the queries.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .Select(TProjections.ProjectionList
                      .Add(TProjections.Count('ID').As_('CallsLess3'))
                     .Add(TProjections.Divide(
                               TProjections.Count('ID'),
                               TProjections.Literal<Integer>(totalCalls)
                         .As_('CallsLess3Perc'))
                     )
                     .Where(not Linq['QueueExitTime'].IsNull)
                     .Where(Linq['SatisfactionScore'] < 3);
  criteria:=filter(aWeek, criteria);
  projRes:=criteria.UniqueValue;
  ...
  lbSatisfactionScoreLess3.Text:= format('%d (%3.2f%%)',
              [integer(projres.Values['CallsLess3']),
               double(projres.Values['CallsLess3Perc']) * 100]);
  ...
  projRes.Free;
end;

Agent Statistics List

The list with the statistics per agent can be completed by making similar calls to Aurelius. The requirement this time is that we need aggregated results per individual agent, or, in other words, we need to group the results per agent. This can be achieved by adding a call to Group function inside the projection.
procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
var
  ...
begin
  ...
  criteria:=session.objectManager.Find<TCall>
                                   .CreateAlias('AgentID', 'agent')
                                   .Select(TProjections.ProjectionList
                                     .Add(TProjections.Prop('agent.Description').As_('Name'))
                                     .Add(TProjections.Count('ID').As_('TotalCalls'))
                                     .Add(TProjections.Group('agent.ID'))
                                   )
                                   .OrderBy('agent.Description');
  criteria:=filter(aWeek, criteria);
  agentList:=criteria.ListValues;
end;

The code groups the calls to Count by the ID of the agents. Note the way we access the ID property; the AgentID property in TCall is an entity itself, and therefore if we want to drill down to its properties, we need to create an alias pointing to TAgent. Then, we can use this alias inside projection calls in the same way we used it when we were dealing with criteria. We, also, want to show the name of the agent as appears in the Description field. As this is a simple use of a field from the main table, it can be easily access using the Prop function.

This snippet calculates the total calls for each agent. The other indicators in the agent list are determined in a similar manner. I do not show the code here, as it would be a repetition of what we have already seen. If you wish to see the full implementation, please refer to the code files that accompany this book.

Views

We implemented the dashboard screen by running several database queries in order to extract all the necessary data. In some instances, we had to do calculations in code before being able to present the right data. This is a very common approach but, in some instances, may not be ideal. For example, if the data set is huge or the resources are limited as it happens in mobile platforms, or if there are security concerns and, perhaps, legacy database schema, it is, strongly, preferable to allow the database engine to handle the queries.

In Aurelius, we can use Views very easily. In fact, Aurelius manages views in the same way as entities. This is very advantageous; we already know how to manage entities. The only difference at this stage is that views are read-only as the whole definition of views suggests. For the CallCentre project, we are going to define the TOverallStatistics entity to represent the OverallStatistics view . The code of this section is under Call Centre – Views folder.
  1. 1.
    Open Entities.pas unit and add the following class:
    ...
    interface
    ...
    type
      ...
      [Entity]
      [Table('OverallStatistics')]
      [Id('FWeek', TIdGenerator.None)]
      TOverallStatistics = class
      private
        [Column('Week', [TColumnProp.Required])]
        FWeek: Integer;
        [Column('SatisfactionScore', [TColumnProp.Required])]
        FSatisfactionScore: Double;
        [Column('TotalCalls', [TColumnProp.Required])]
        FTotalCalls: Integer;
        [Column('AnswerSpeed', [TColumnProp.Required])]
        FAnswerSpeed: Double;
        [Column('AbandonRate', [TColumnProp.Required])]
        FAbandonRate: Double;
        [Column('CallsMinute', [TColumnProp.Required])]
        FCallsMinute: Double;
        [Column('CallsLess180', [TColumnProp.Required])]
        FCallsLess180: Integer;
        [Column('CallsLess3', [TColumnProp.Required])]
        FCallsLess3: Integer;
        [Column('CallsLess3Perc', [TColumnProp.Required])]
        FCallsLess3Perc: Double;
      public
        property Week: Integer read FWeek write FWeek;
        property SatisfactionScore: Double read FSatisfactionScore write FSatisfactionScore;
        property TotalCalls: Integer read FTotalCalls write FTotalCalls;
        property AnswerSpeed: Double read FAnswerSpeed write FAnswerSpeed;
        property AbandonRate: Double read FAbandonRate write FAbandonRate;
        property CallsMinute: Double read FCallsMinute write FCallsMinute;
        property CallsLess180: Integer read FCallsLess180 write FCallsLess180;
        property CallsLess3: Integer read FCallsLess3 write FCallsLess3;
        property CallsLess3Perc: Double read FCallsLess3Perc write FCallsLess3Perc;
      end;
    ...

    There are a few points to mention about this entity that represents a view. First, the primary key (Week) is linked to one of the columns of the view. This is only to provide a unique identifier to the entity to keep Aurelius happy. The second point follows the first one. All the properties in this entity are flagged as required but again this is not important. I chose to do this because I do not want to check whether a property has content as we do when we deal with nullable fields. Unless the view is empty, I can, simply, use the property values. The last point to mention has to do with the naming and data type of the properties. This entity represents a view in the database and, therefore, the properties and the data types must be the same (or compatible) to the columns in the actual view.

     
  2. 2.

    Run the CallCentre application. Aurelius will go on and create a table named OverallStatistics when updateDatabase is called. But this is not what we want to achieve. Aurelius may treat OverallStatistics as an entity (table) but, at database level, it is a view. This homogenous approach, also, leaves us unable to rely to Aurelius schema management to create or update a view. We rather need to do this manually.

     
  3. 3.

    Delete the table, drop the database, or delete the database file. Let’s start clean.

     
  4. 4.

    In order to prevent Aurelius checking for OverallStatistics table and from creating it, we need the help of models. The idea of models in Aurelius allows developers to create conceptual group entities (tables) to serve different tasks. A very common use of models is to separate the tables that hold security, licensing, or user management data from the main (default) group of tables. Models can go beyond conceptual level and be associated to different databases. Therefore, for instance, you can have two databases: one for the licensing and another for the application-specific data.

    In our application, we are going to introduce the Database model to organize those entities that reflect views. Go back to TOverallStatistics class and decorate it with the attribute Model.
    type
      ...
      [Entity]
      [Table('OverallStatistics')]
      [Model('Database')]
      [Id('FWeek', TIdGenerator.None)]
      TOverallStatistics = class
        ...
      end;
     
  5. 5.

    Run the application again. If you look at the database, you will see that Aurelius did not create the TOverallStatistics table because, the way we have set things up, the main (default) model is used. If you do not provide the Model attribute to an entity, it is considered part of the Default model.

     
  6. 6.
    We now need to create the view in the database. If you have the option and access, you can run the following SQL script manually to do this. The script can be found in the Misc folder in the code files under the name OverallStatisticsViewScript.sql.
    CREATE VIEW IF NOT EXISTS OverallStatistics AS select
            Week,
            Avg(SatisfactionScore) as SatisfactionScore,
            Count(*) as TotalCalls,
            (Avg( case
                    when QueueExitTime is not null then QueueExitTime - QueueEntryTime
                  end )) as AnswerSpeed,
            (Count( case
                      when QueueExitTime is null then 1
                    end ) * 100.0 ) / count(*) as AbandonRate,
            (Count(*) / 9.00 / 60.00) as CallsMinute,
            (Count ( case
                       when (QueueExitTime is not null) and ((QueueExitTime - QueueEntryTime) < 0.00208333333333333) then 1
                     end )) as CallsLess180,
            (Count( case
                      when SatisfactionScore < 3 then 1
                    end )) as CallsLess3,
            (Count( case
                      when SatisfactionScore < 3 then 1
                    end ) * 100.0 / count(*) ) as CallsLess3Perc
    from
      Call
    GROUP BY
      Week;
     
  7. 7.

    In the case you cannot manage the database directly or in situations where you want your applications to update the database, you need to execute the script using Aurelius features.

     
  8. 8.
    Aurelius defines the IDBStatement interface , which allows us to pass plain SQL statements and execute them directly. This takes place at the TObjectManager level. Add the next private method in TFormMain in MainForm.pas. Note that you need to add Database.Session.Types in the interface section now.
    uses
      ...,
      Database.Session.Types;
    type
      TFormMain = class(TForm)
      ...
      private
      ...
        procedure createViews(const dbSession: IDatabaseSession);
      end;
    ...
    implementation
    ...
    procedure TFormMain.createViews(const dbSession: IDatabaseSession);
    var
      statement: IDBStatement;
      sqlScript: string;
    begin
      sqlScript:=
      'CREATE VIEW IF NOT EXISTS OverallStatistics AS select' +
      '  Week,' +
      '  avg(SatisfactionScore) as SatisfactionScore,' +
      '  Count(*) as TotalCalls,' +
      '  (Avg( case' +
      '          when QueueExitTime is not null then QueueExitTime - QueueEntryTime' +
      '        end )) as AnswerSpeed,' +
      '  (Count( case' +
      '            when QueueExitTime is null then 1' +
      '          end ) * 100.0 ) / count(*) as AbandonRate,' +
      '  (Count(*) / 9.00 / 60.00) as CallsMinute,' +
      '  (Count ( case' +
      '             when (QueueExitTime is not null) and ((QueueExitTime - QueueEntryTime) < 0.00208333333333333) then 1' +
      '           end )) as CallsLess180,' +
      '  (Count( case' +
      '            when SatisfactionScore < 3 then 1' +
      '          end )) as CallsLess3,' +
      '   (Count( case' +
      '             when SatisfactionScore < 3 then 1' +
      '           end ) * 100.0 / count(*) ) as CallsLess3Perc ' +
      'FROM' +
      '  Call ' +
      'GROUP BY' +
      '  Week;';
      statement:=dbSession.objectManager.Connection.CreateStatement;
      statement.SetSQLCommand(sqlScript);
      statement.Execute;
    end;

    We declare an IDBStatement variable, and we use the CreateStatement function to instantiate it. Then, we load the script to the interface using SetSQLCommand and finally execute it. In a full-scale application, most likely you want to wrap statement.Execute in a try-except branch.

    In the preceding snippet, I have added some spaces to make the SQL script readable. Obviously, this is not necessary, as it is just a script. What matters, though, is the space at the beginning (or the end) of each line if you choose to create this concatenated string as I have done. Otherwise, you will end up with a script with adjacent words without any spaces.

     
  9. 9.
    Go to FormCreate event in MainForm.pas and call createViews :
    uses
      ...
      Aurelius.Drivers.Interfaces;
    ...
    procedure TFormMain.FormCreate(Sender: TObject);
    ...
    begin
      ...
      dbSession.databaseManager.UpdateDatabase;
      createViews(dbSession);
      setupGUI;
    end;
     
  10. 10.

    If you check the code in the supplied files, I have added the same code in the procedure that executes the importing of the data. I will not mention it here as it is the same as the preceding snippet.

     
  11. 11.
    Time to get our data from the view and show it in the dashboard. Go to calculateStatistics in TFormMain and retrieve the list of entities:
    procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
    var
      session: IDatabaseSession;
      overallStatsList: TList<TOverallStatistics>;
    begin
      session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      overallStatsList:=session.objectManager.Find<TOverallStatistics>.List;
        overallStatsList.Free;
    end;
     
  12. 12.

    As you can see, accessing a view in Aurelius is done in the same way as accessing every other entity. However, the preceding code breaks. Try to execute it and you will see that Aurelius complains with the message of “Class TOverallStatistics is not a valid Entity. [Entity] attribute missing.” although we have used Entity attribute in the class.

     
  13. 13.

    The reason for this error message is that we have made TOverallStatistics part of Database model. The call to Find in the preceding code accesses the Default model. We need to instruct the object manager to look at the Database model.

     
  14. 14.
    Open Database.Session.Types.pas and add the following overloading function to IDatabaseSession:
    type
      IDatabaseSession = interface
        ...
        function objectManager: TObjectManager; overload;
        function objectManager (const aModel: string): TObjectManager; overload;
      end;
     
  15. 15.
    In Database.Session.pas, add the implementation of the function. In this example, I have introduced a dictionary to hold the different object managers based on the model they serve. This can be handy when multiple models are present. The following code shows the full unit and highlights the differences from before:
    unit Database.Session;
    interface
    uses
      Database.Session.Types,
      Aurelius.Engine.DatabaseManager,
      Aurelius.Drivers.Interfaces,
      Aurelius.Engine.ObjectManager, System.Generics.Collections;
    type
      TDatabaseSession = class (TInterfacedObject, IDatabaseSession)
      private
        fConnection: IDBConnection;
        fDatabaseManager: TDatabaseManager;
        fObjectManagerDictionary: TObjectDictionary<string, TObjectManager>;
      public
        constructor Create(const aConnection: IDBConnection);
        destructor Destroy; override;
    {$REGION 'Interface'}
        function databaseManager: TDatabaseManager;
        function objectManager: TObjectManager; overload;
        function objectManager (const aModel: string): TObjectManager; overload;
    {$ENDREGION}
      end;
    implementation
    uses
      System.SysUtils, Aurelius.Mapping.Explorer;
    constructor TDatabaseSession.Create(const aConnection: IDBConnection);
    begin
      Assert(aConnection <> nil);
      inherited Create;
      fConnection:=aConnection;
      fObjectManagerDictionary:=TObjectDictionary<string, TObjectManager>.Create([doOwnsValues]);
    end;
    function TDatabaseSession.databaseManager: TDatabaseManager;
    begin
      if not Assigned(fDatabaseManager) then
        fDatabaseManager:=TDatabaseManager.Create(fConnection);
      Result:=fDatabaseManager;
    end;
    destructor TDatabaseSession.Destroy;
    begin
      fDatabaseManager.Free;
      fObjectManagerDictionary.Free;
      inherited;
    end;
    function TDatabaseSession.objectManager(const aModel: string): TObjectManager;
    var
      cModel: string;
    begin
      cModel:=Trim(UpperCase(aModel));
      if cModel=" then
        Result:=objectManager
      else
      begin
        if not fObjectManagerDictionary.ContainsKey(cModel) then
          if cModel = 'DEFAULT' then
            fObjectManagerDictionary.Add('DEFAULT', TObjectManager.Create(fConnection))
          else
            fObjectManagerDictionary.Add(cModel,
                  TObjectManager.Create(fConnection, TMappingExplorer.Get(cModel)));
        Result:=fObjectManagerDictionary.Items[cModel];
      end;
    end;
    function TDatabaseSession.objectManager: TObjectManager;
    begin
      result:=objectManager('default');
    end;
    end.

    We create a new instance of object manager for each model. A simple call to the constructor of TObjectManager passes the Default model. When we need an object manager for specific model other than the default, we use TMappingExplorer to provide the internal structure of the database representation we are interested in. TMappingExplorer is responsible for scanning through entities attached to a database model.

     
  16. 16.
    Now we are ready to access the content of the view. We also factor in the filtering according to the week.
    procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
    var
      ...,
      criteria: TCriteria;
      overallStatsList: TList<TOverallStatistics>;
    begin
      session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      criteria:=session.objectManager('Database').Find<TOverallStatistics>;
      criteria:=filter(aWeek, criteria);
      overallStatsList:=criteria.List<TOverallStatistics>;
        overallStatsList.Free;
    end;

    We pass the name of the model (Database) to the object manager, and we use filter to generate the correct call according to the chosen week. Then, we retrieve the content of the view and assign it to overallStatsList . Note that we need to provide the exact data type to List<> because we, now, do not retrieve a list of criteria results, as we did previously.

     
  17. 17.
    From this point onward, we can easily access the results and update the dashboard.
    procedure TFormMain.calculateStatistics(const aWeek: TWeeks);
    var
      ...,
      overallStats: TOverallStatistics;
    begin
      session:=TDatabaseSession.Create(SQLiteConnection.CreateConnection);
      criteria:=session.objectManager('Database').Find<TOverallStatistics>;
      criteria:=filter(aWeek, criteria);
      overallStatsList:=criteria.List<TOverallStatistics>;
      ...
      for overallStats in overallStatsList do
      begin
        lbSatisfactionValue.Text:=format('%2.2f', [overallStats.SatisfactionScore]);
        lbTotalCallsValue.Text:= overallStats.TotalCalls.ToString;
        lbAnswerSpeedValue.Text:=FormatDateTime('n:ss', overallStats.AnswerSpeed);
        lbAbandonRateValue.Text:=format('%3.2f%%', [overallStats.AbandonRate]);
        lbCallsMinuteValue.Text:=format('%3.2f', [overallStats.CallsMinute]);
        lbAnsweredLess180Value.Text:= overallStats.CallsLess180.ToString;
          lbSatisfactionScoreLess3Value.Text:= format('%d (%3.2f%%)',
                    [overallStats.CallsLess3,
                     overallStats.CallsLess3Perc * 100]);
      end;
      ...
        overallStatsList.Free;
    end;
     

Note

The preceding code is different from the one you can find in the code files. In the actual code, we need to manage the fact that we present the results of week 4 and week 5 consolidated.

Summary

In this chapter, we covered lot of ground. We started with simple queries to get lists of agents and departments, and we moved on to explore how we can build complex requests and take advantage of Aurelius’ fluent interface. We also saw how the framework provides options for calculations and grouping. The last topic we discussed was database views – a way to move heavy calculations totally to the server side and, consequently, take advantage of the database engine.

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

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