Custom Integration Call Type Values not correct

[pierremueller]

Hello,

I have implemented a custom integration to report calls, using the provided XML template and SQL Statement.

However, calls which should have had CallType = 'Unanswered' or 'Missed', seem not to be logged.
Any idea what is happening?

Template:

<?xml version="1.0" encoding="utf-8"?>
<Crm xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Country="US" Name="EventInc Custom Integration" Version="1" SupportsEmojis="true" ListPageSize="0">
  <Number Prefix="AsIs" MaxLength="15" />
  <Connection MaxConcurrentRequests="2" />
  <Parameters>
    <Parameter Name="Server" Type="String" Parent="General Configuration" Editor="String" Title="Server:" />
    <Parameter Name="Port" Type="String" Parent="General Configuration" Editor="String" Title="Port:" />
    <Parameter Name="Database" Type="String" Parent="General Configuration" Editor="String" Title="Database:" />
    <Parameter Name="Username" Type="String" Parent="General Configuration" Editor="String" Title="Username:" />
    <Parameter Name="Password" Type="Password" Parent="General Configuration" Editor="String" Title="Password:" />
    <Parameter Name="ReportCallSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Call Journaling SQL Statement:" />
  </Parameters>
  <Authentication Type="No" />
  <Scenarios>
    <Scenario Id="ReportCall" Type="SQLDatabase">
      <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportCallSQLStatement]" />
      <Variables />
      <Outputs AllowEmpty="false" />
    </Scenario>
  </Scenarios>
</Crm>

SQL Statement:

INSERT INTO calls (agent_extension, agent_firstname, agent_lastname, agent_email, number, call_direction, call_type, call_starttime, call_endtime) VALUES (@Agent, @AgentFirstName, @AgentLastName, @AgentEmail, @Number, @CallDirection, @CallType, @CallEstablishedTimeLocal, @CallEndTimeLocal)

 

[pierremueller]

I have managed to go around the missing CallEstablishedTimeLocal variable, by firstly reporting the Unanswered Calls. (which uses the StartCallTimeLocal variable).

The problem I have now is that for Answered Calls, the call is logged twice.

Here is my XML:

<Scenarios>
    <Scenario Id="ReportCall" Type="SQLDatabase">
        <Query SkipIf="[CallType]==Inbound||[CallType]==Outbound" DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportUnansweredCallSQLStatement]" />
        <Variables />
        <Outputs AllowEmpty="true" Next="ReportCall2" />
    </Scenario>
    <Scenario Id="ReportCall2" Type="SQLDatabase">
        <Query SkipIf="[CallType]==Missed||[CallType]==Notanswered" DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportAnsweredCallSQLStatement]" />
        <Variables />
        <Outputs AllowEmpty="false" />
    </Scenario>
</Scenarios>

Here is my debug logs:

2024/02/26 12:09:08.151|6188|0040|Verb|CRM: Triggering Call Journaling with values - CallType='Outbound' - PhoneNumber='004915168737213' - ContactName='' - AgentExtension='167' - AgentFirstName='FirstName' - AgentLastName='LastName' - AgentEmail='[email protected]' - ContactRawData='' - StartTime='2/26/2024 11:09:00 AM' - EndTime='2/26/2024 11:09:08 AM' - EstablishedTime='2/26/2024 11:09:02 AM'
2024/02/26 12:09:08.152|6188|0040|Verb|CRM: Processing scenario 'ReportCall'.
2024/02/26 12:09:08.152|6188|0040|Verb|CRM: Executing SQL Query using ConnectionString 'Server=server;Port=5432;Database=db;User Id=user;Password=password' and Query Statement 'INSERT INTO calls (agent_extension, agent_firstname, agent_lastname, agent_email, number, call_direction, call_type, call_starttime, call_endtime) VALUES (@Agent, @AgentLastName, @AgentLastName, @AgentEmail, @Number, @CallDirection, @CallType, @CallStartTimeLocal, @CallEndTimeLocal)'.
2024/02/26 12:09:08.159|6188|0017|Verb|CRM: The result of the SQL Query Execution is
'[]' .
2024/02/26 12:09:08.159|6188|0017|Verb|CRM: Continue with empty matching.
2024/02/26 12:09:08.159|6188|0017|Verb|CRM: Processing scenario 'ReportCall2'.
2024/02/26 12:09:08.159|6188|0017|Verb|CRM: Executing SQL Query using ConnectionString 'Server=server Port=5432;Database=db;User Id=user;Password=pass' and Query Statement 'INSERT INTO calls (agent_extension, agent_firstname, agent_lastname, agent_email, number, call_direction, call_type, call_starttime, call_endtime) VALUES (@Agent, @AgentFirstName, @AgentFirstName, @AgentEmail, @Number, @CallDirection, @CallType, @CallEstablishedTimeLocal, @CallEndTimeLocal)'.
2024/02/26 12:09:08.166|6188|0017|Verb|CRM: The result of the SQL Query Execution is
'[]' .

So for some reason, the SkipIf syntax does not work. I have also tried with quotes like:

SkipIf="[CallType]=="Outbound"||[CallType]=="Inbound""

but it had the same result.Any idea what it might be wrong?


Edit:

I have seen some examples in the predefined XML templates with slightly different syntax. Unfortunately, i am not able to understand what exactly might be the problem but perhaps this could be helpful.

   <Command SkipIf="[CreateContactEnabled]!=True||[IIf([CreateOnCallDirection]==Inbound,[CallDirection]!=Inbound,False)]==True"

 

[edossantos]

Another option could be having a single scenario, and change the SQL statement depending on the call type:

<Scenario Id="ReportCall" Type="SQLDatabase">
    <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[IIf([CallType]==Inbound||[CallType]==Outbound,[ReportAnsweredCallSQLStatement],[ReportUnansweredCallSQLStatement])]" />
    <Variables />
    <Outputs AllowEmpty="false" />
</Scenario>

 

[pierremueller]

Another option could be having a single scenario, and change the SQL statement depending on the call type:

<Scenario Id="ReportCall" Type="SQLDatabase">
    <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[IIf([CallType]==Inbound||[CallType]==Outbound,[ReportAnsweredCallSQLStatement],[ReportUnansweredCallSQLStatement])]" />
    <Variables />
    <Outputs AllowEmpty="false" />
</Scenario>

I've tried this approach as well. (also quotes included)

But the condition never evaluates to true and always it is the second SQL statement executed.

 

[pierremueller]

So the main reason of the need to use 2 different SQL statements for answered/not answered calls is because of the error:

2024/02/26 15:13:23.531|6188|0031|Verb|CRM: Triggering Call Journaling with values - CallType='Notanswered' - PhoneNumber='number' - ContactName='' - AgentExtension='167' - AgentFirstName='firstname' - AgentLastName='lastname' - AgentEmail='[email protected]' - ContactRawData='' - StartTime='2/26/2024 2:13:22 PM' - EndTime='2/26/2024 2:13:23 PM' - EstablishedTime='1/1/0001 12:00:00 AM'
2024/02/26 15:13:23.531|6188|0031|Verb|CRM: Processing scenario 'ReportCall'.
2024/02/26 15:13:23.532|6188|0031|Verb|CRM: Executing SQL Query using ConnectionString 'Server=server;Port=5432;Database=db;User Id=user;Password=pass' and Query Statement 'INSERT INTO calls (agent_extension, agent_firstname, agent_lastname, agent_email, number, call_direction, call_type, call_starttime, call_endtime) VALUES (@Agent, @AgentFirstName, @AgentLastName, @AgentEmail, @Number, @CallDirection, @CallType, @CallEstablishedTimeUTC, @CallEndTimeLocal)'.
2024/02/26 15:13:23.542|6188|0039|Erro|CRM: Exception during call reporting: Npgsql.PostgresException (0x80004005): 42703: column "callestablishedtimeutc" does not exist

POSITION: 187
at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Integration.Crm.Engine.SqlDatabaseClient.Execute(String statement, SqlQueryParameter[] parameters, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, SqlQueryParameter[] queryParameters, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, SqlQueryParameter[] sqlQueryParameter, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.Execute(ProtocolScenario scenario, SqlQueryParameter[] sqlQueryParameter, CancellationToken token)
at Integration.Crm.Engine.CrmProcessor.ExecuteScenarioByName(String scenarioName, IEnumerable`1 parameters, Boolean useNoAuthProvider, CancellationToken token)
at Integration.Crm.Engine.CrmProcessor.ExecuteScenarioWithRetries(String scenarioId, Dictionary`2 parameters, CancellationToken token)
at Integration.Crm.Engine.CrmProcessor.ReportCall(CallEndedEvent callEndedEvent, CancellationToken token)
Exception data:
Severity: ERROR
SqlState: 42703
MessageText: column "callestablishedtimeutc" does not exist
Position: 187
File: parse_relation.c
Line: 3656
Routine: errorMissingColumn

As it can be seen, EstablishedTime IS actually SET

EstablishedTime='1/1/0001 12:00:00 AM'

but then, it still complains that CallEstalbishedTimeUTC column does not exist.

Is there any other way we could access this data, except for the CRM integration through XML? Or is it possible to update the @Duration value so that it returns only CALL time and not RINGING time ?

 

[edossantos]

Please, try with this:

Statement="[IIf([CallType]==Inbound,[ReportAnsweredCallSQLStatement],[IIf([CallType]==Outbound,[ReportAnsweredCallSQLStatement],[ReportUnansweredCallSQLStatement])])]"

 

[pierremueller]

Please, try with this:

Statement="[IIf([CallType]==Inbound,[ReportAnsweredCallSQLStatement],[IIf([CallType]==Outbound,[ReportAnsweredCallSQLStatement],[ReportUnansweredCallSQLStatement])])]"

Still only the UnansweredCallSQLStatement is being executed. (I have tried for Outbound and for Notanswered)

 

[Evolute IT]

As it can be seen, EstablishedTime IS actually SET

but then, it still complains that CallEstalbishedTimeUTC column does not exist.

It's a null value.

 

[edossantos]

Sorry, now I understand what's going on. As you're using a database, the parameters are injected as SQL parameters, and not variables. This means that you can't use [CallType], as that's injected as @CallType. However, @CallType will not work in the Statement to use a conditional expression. So the only way to do this now is adjusting your SQL statement to take the proper value depending on the parameter @CallType. You can use a stored procedure, but that should not be mandatory, you will need to check your SQL documentation to understand what command to use for this.