Custom Integration Call Type Values not correct

pierremueller · Jan 29, 2024

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:

Code:

<?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:

Code:

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)

Evolute IT · Jan 29, 2024

Set the system to verbose, do a couple tests and then check the Activity Log > Logs > Instance > 3CXSystemService.log

If you're on v20, you'll need to download the Support Info to get the log files.

pierremueller · Jan 29, 2024

All right, what exactly should I be looking for?
I have access now to the verbose logs and I have downloaded teh 3CXSystemService.log

Evolute IT · Jan 29, 2024

pierremueller said:
All right, what exactly should I be looking for?
I have access now to the verbose logs and I have downloaded teh 3CXSystemService.log

Search for "CRM:"

You will see things like "Preparing to report call with data: ..."

pierremueller · Jan 29, 2024

Oh, I found the error.

Code:

2024/01/29 15:06:03.855|2828|0057|Erro|CRM: Exception during call reporting: Npgsql.PostgresException (0x80004005): 42703: column "callestablishedtimelocal" does not exist

POSITION: 185
   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 "callestablishedtimelocal" does not exist
    Position: 185
    File: parse_relation.c
    Line: 3656
    Routine: errorMissingColumn
2024/01/29 15:06:03.855|2828|0022|Trac|Executing SQL: INSERT into cl_party_info (id,dn_type,dn,caller_number,display_name,dn_class,firstlastname,did_number)

Obviously, there is no callEstablishedTime, as the call was not answered.

The reason why I am using callEstablishedTime is because @CallDuration value received via Call Reporting is different from the Talking Time shown in 3CX dashboard.

From what I could tell, talking time in the 3CX dashboard is ` CallEstablishedLocal - CallStartTimeLocal`. While the @CallDuration value, reported by the CallReports is the difference between `CallEndTimeLocal - CallStartTimeLocal`.

Therefore, @CallDuration will always be 2-3 seconds longer than Talking Time.

Evolute IT · Jan 29, 2024

Just for testing, try the CallStartTimeLocal instead. See if that one works.

TO_CHAR(@CallStartTimeLocal, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

My guess is the established time is not available since the calls are missed/unanswered, so the call was never "established"/connected/answered.

pierremueller · Jan 29, 2024

Yes, that is exactly what is happening.
But the reason why I am using CallEstablished instead of CallStartTime is because I need to calculate the Call Duration.

The @CallDuration value, received from CRM integration, is different from the 'Talking Time' displayed in the 3CX Dashboard, or in the reports.

The @CallDuration value calculates the difference between CallEndTime - CallStartTime, while Talking Time in 3CX probably calculates the difference between CallEndTime - CallEstablishedTime

Evolute IT · Jan 29, 2024

pierremueller said:
Yes, that is exactly what is happening.
But the reason why I am using CallEstablished instead of CallStartTime is because I need to calculate the Call Duration.

The @CallDuration value, received from CRM integration, is different from the 'Talking Time' displayed in the 3CX Dashboard, or in the reports.

The @CallDuration value calculates the difference between CallEndTime - CallStartTime, while Talking Time in 3CX probably calculates the difference between CallEndTime - CallEstablishedTime

I understand that. But for missed/unanswered calls, it doesn't matter.

What you can do is use two different chained scenario and have a SkipIf="[CallType]==Missed||[CallType]==Notanswered" in the first scenario (on the Query object to be specific.) This way you can have two different queries and log the StartTime for missed/unanswered.

See the built-in templates for examples.

pierremueller · Jan 29, 2024

ConceptsWeb said:
I understand that. But for missed/unanswered calls, it doesn't matter.

What you can do is use two different chained scenario and have a SkipIf="[CallType]==Missed||[CallType]==Notanswered" in the first scenario (on the Query object to be specific.) This way you can have two different queries and log the StartTime for missed/unanswered.

See the built-in templates for examples.

Oh, ok. Got it. Thanks. I'll give it a try.

pierremueller · Feb 16, 2024

@ConceptsWeb
Hello,

is this what you meant by adding the IF clause and have 2 different queries?

Code:

<?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="ReportAnsweredCallSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Call Journaling Answered Calls SQL Statement:" />
        <Parameter Name="ReportUnansweredCallSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Call Journaling Unanswered Calls SQL Statement:" />
    </Parameters>
    <Authentication Type="No" />
    <Scenarios>
        <Scenario Id="ReportCall" 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]" />
            <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportAnsweredCallSQLStatement]" />
            <Variables />
            <Outputs AllowEmpty="false" />
        </Scenario>
    </Scenarios>
</Crm>

My SQL Statements would be:
- Answered Calls:

Code:

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)

- Unanswered calls:

Code:

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,  @CallStartTimeLocal,  @CallEndTimeLocal)

The only difference is that for CallStartTime I set value CallEstablishedTimeLocal (when the call is answered) and CallStartTimeLocal when call is not answered

Question:
How can I ensure that ONLY one query will run for the ReportCall Scenario?

pierremueller · Feb 16, 2024

pierremueller said:

@ConceptsWeb
Hello,

is this what you meant by adding the IF clause and have 2 different queries?

Code:

<?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="ReportAnsweredCallSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Call Journaling Answered Calls SQL Statement:" />
        <Parameter Name="ReportUnansweredCallSQLStatement" Type="String" Parent="General Configuration" Editor="Sql" Title="Call Journaling Unanswered Calls SQL Statement:" />
    </Parameters>
    <Authentication Type="No" />
    <Scenarios>
        <Scenario Id="ReportCall" 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]" />
            <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportAnsweredCallSQLStatement]" />
            <Variables />
            <Outputs AllowEmpty="false" />
        </Scenario>
    </Scenarios>
</Crm>

My SQL Statements would be:
- Answered Calls:

Code:

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)

- Unanswered calls:

Code:

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,  @CallStartTimeLocal,  @CallEndTimeLocal)

The only difference is that for CallStartTime I set value CallEstablishedTimeLocal (when the call is answered) and CallStartTimeLocal when call is not answered

Question:
How can I ensure that ONLY one query will run for the ReportCall Scenario?

Unfortunately, this template does not work.

Always after I upload the template, the second query will get deleted.

If I use 2 "ReportCall" scenarios, I get syntax error.

Any idea of what I could do ?

thanks.

Evolute IT · Feb 16, 2024

You need to chain them. Check the XML CRM docs.

edossantos · Feb 16, 2024

You're adding 2 Query elements inside the same Scenario element. You need 2 different scenarios, each one with 1 query. Then, in the Outputs element of the first scenario you specify the Id of the second scenario in the Next attribute.

pierremueller · Feb 19, 2024

edossantos said:
You're adding 2 Query elements inside the same Scenario element. You need 2 different scenarios, each one with 1 query. Then, in the Outputs element of the first scenario you specify the Id of the second scenario in the Next attribute.

Could you provide a sample example?

My main goal is to report the call times - for that I have understood that I must used the reserved ID "ReportCall" for the scenario, and without it, I assume I won't have access to the call variables (e.g @Agent, @calltime... ). Is my assumption correct?

edossantos · Feb 19, 2024

Something like this:

XML:

<Scenarios>
   <Scenario Id="ReportCall" 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="true" Next="ReportCall2" />
   </Scenario>
   <Scenario Id="ReportCall2" Type="SQLDatabase">
      <Query DatabaseType="PostgreSQL" ConnectionString="Server=[Server];Port=[Port];Database=[Database];User Id=[Username];Password=[Password]" StatementPasses="2" Statement="[ReportAnsweredCallSQLStatement]" />
      <Variables />
      <Outputs AllowEmpty="false" />
   </Scenario>
</Scenarios>

pierremueller · Feb 19, 2024

ah, smart. using scenario ID ReportCall, as first, will ensure that I have the variables in my second scenario as well.
I will give it a try, I hope it will work.
Thank you.

pierremueller · Feb 19, 2024

Code:

<Scenarios>
    <Scenario Id="ReportCall" 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="true" Next="ReportCall2" />
    </Scenario>
    <Scenario Id="ReportCall2" 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="false" />
    </Scenario>
</Scenarios>

For some reason, now it is logging it twice. Do you know why?

edossantos · Feb 19, 2024

Is it logging it twice for any specific call type?

pierremueller · Feb 19, 2024

edossantos said:
Is it logging it twice for any specific call type?

for answered call types (Inbound / Outbound) .

For unanswered call types I get error

Code:

2024/02/19 16:03:32.864|6188|0040|Info|CRM: about to report 1 calls at maxConcurrency 2
2024/02/19 16:03:32.864|6188|0040|Verb|CRM: Triggering Call Journaling with values - CallType='Notanswered' - PhoneNumber='123123131231' - ContactName='' - AgentExtension='111111' - AgentFirstName='AgentFirstName' - AgentLastName='AgentLastName' - AgentEmail='[email protected]' - ContactRawData='' - StartTime='2/19/2024 3:03:28 PM' - EndTime='2/19/2024 3:03:32 PM' - EstablishedTime='1/1/0001 12:00:00 AM'
2024/02/19 16:03:32.864|6188|0040|Verb|CRM: Processing scenario 'ReportCall'.
2024/02/19 16:03:32.865|6188|0040|Verb|CRM: Executing SQL Query using ConnectionString 'Server=server;Port=5432;Database=3cx;User Id=postgres;Password=postgres' 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,  @CallEstablishedTimeLocal,  @CallEndTimeLocal)'.
2024/02/19 16:03:32.875|6188|0048|Erro|CRM: Exception during call reporting: Npgsql.PostgresException (0x80004005): 42703: column "callestablishedtimelocal" does not exist

here it is complaining that there is no CallEstablishedTimeLocal because the call type is `Notanswered`.
That is why I want to skip the SQL query statement in first place, because I am aware that CallEstablishedTimeLocal is not set.

MY SQL Statements look like:
Unanswered Calls - Where I use CallStartTimeLocal as value for Call_StartTime

Code:

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,  @CallStartTimeLocal,  @CallEndTimeLocal)

Answered Calls - Where i use CallEstablishedTimeLocal as value for Call_StartTime

Code:

[CODE]
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)

I will just give up at this point.

edossantos · Feb 20, 2024

Can you try adding the quotes to the strings in the SkipIf attribute? For example:
SkipIf="[CallType]=="Missed"||[CallType]=="Notanswered""

Custom Integration Call Type Values not correct

pierremueller

Evolute IT

pierremueller

Evolute IT

pierremueller

Evolute IT

pierremueller

Evolute IT

pierremueller

pierremueller

pierremueller

Evolute IT

edossantos

Support Team

pierremueller

edossantos

Support Team

pierremueller

pierremueller

edossantos

Support Team

pierremueller

edossantos

Support Team

Latest Posts

Forum statistics

20 Years of VoIP
Industry Leadership

Company

Solutions

Useful Links

Learn More

Custom Integration Call Type Values not correct

Support Team

Support Team

Support Team

Support Team

Forum statistics

20 Years of VoIP Industry Leadership

Company

Solutions

Useful Links

Learn More

20 Years of VoIP
Industry Leadership