MSSQL CRM Integration - Create contacts but Call Journaling not working

[Apereira]

Hi,

I am doing some testing for CRM Integration, with the following options.

Select a CRM Solution : Database MsSQL
QueryCRM: Always

Server: MySQLServer\Instance
Database: 3cata
UserName: SQLUsername
Password: SQLPassword
Lookup SQL Statement:

SELECT contactid, firstname, lastname, email, phonebusiness, phonemobile, faxbusiness FROM contacts WHERE phonebusiness LIKE '%[Number]%' or phonemobile like '%[Number]%' or faxbusiness LIKE '%[Number]%'

ContactURL Prefix: Nothing
ContactURL Suffix: Nothing

Enabled Call Journaling: Yes
Call Journaling SQL Statement:

INSERT INTO [dbo].[calls2]([CallType],[ContactNumber],[Agent],[Duration],[DurationTimeSpan],[CallStartTimeLocal],[CallStartTimeUTC],[CallEndTimeLocal],[CallEndTimeUTC],[DateTime])VALUES('[CallType]','[Number]','[Agent]','[Duration]','[DurationTimeSpan]','[CallStartTimeLocal]','[CallStartTimeUTC]','[CallEndTimeLocal]','[CallEndTimeUTC]','[DateTime]')

Enable contact creation: Yes
Contact creation SQL statement:

INSERT INTO contacts (firstname, lastname, phonebusiness ) VALUES ('New', '3CX Contact', '[Number]');SELECT contactid, firstname, lastname,  phonebusiness FROM contacts WHERE phonebusiness = '[Number]'

It does create the contact as soon as the call is received, which is fine, but then does not create the call record.


Can you please help?

 

[edossantos]

You should enable verbose logs from the 3CX Console > Dashboard > Activity Log > Settings. Then make a test call and check the 3cxSystemService.log file, which contains all the interactions between 3CX and your database...

 

[Goga]

I am also having same issue , it is showing me below error :

Exception during number processing: System.InvalidOperationException: BeginExecuteReader: CommandText property has not been initialized
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at CrmIntegration.SqlDatabaseClient.Execute(String statement, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at CrmIntegration.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at CrmIntegration.CrmProcessor.GetContactInfoFromCrm(String contactNumber, String destinationEmail, Boolean createIfNotFound, CancellationToken token)
2019/10/01 09:50:49.640|10536|0069|Info|Call not reported because contact could not be found for number: 880614131510

 

[edossantos]

Exception during number processing: System.InvalidOperationException: BeginExecuteReader: CommandText property has not been initialized

This means that you didn't set the Lookup SQL Statement as explained here:
https://www.3cx.com/docs/sql-database-pbx-integration/#h.4xf3snf20461

 

[Goga]

This means that you didn't set the Lookup SQL Statement as explained here:
https://www.3cx.com/docs/sql-database-pbx-integration/#h.4xf3snf20461

If I only want call journaling and don't want select or create contact, it is not possible.

 

[edossantos]

No, you need the contact lookup, because call journaling only works for matched contacts. If the lookup doesn't return any data, call journaling will be skipped.

If you just want to log all calls, you should consider importing the CSV files generated as CDRs by 3CX, using some batch job:
https://www.3cx.com/docs/cdr-call-data-records/

 

[SweetAction]

@Goga
Read this:
https://www.3cx.com/community/threads/call-journaling-doesnt-work-in-sqlserver.65807/#post-285264

It has it's issues, but it gives you call journal without "create contact..." to some extent.

 

[Goga]

@Goga
Read this:
https://www.3cx.com/community/threads/call-journaling-doesnt-work-in-sqlserver.65807/#post-285264

It has it's issues, but it gives you call journal without "create contact..." to some extent.

Thank you for help.

 

[Goga]

No, you need the contact lookup, because call journaling only works for matched contacts. If the lookup doesn't return any data, call journaling will be skipped.

If you just want to log all calls, you should consider importing the CSV files generated as CDRs by 3CX, using some batch job:
https://www.3cx.com/docs/cdr-call-data-records/

By Call Journaling I am getting only total call duration not Talking Time , is there anyway we can get only talk time individually.
Or How can I bifurcate talk time and ringing time.

 

[edossantos]

By Call Journaling I am getting only total call duration not Talking Time , is there anyway we can get only talk time individually.
Or How can I bifurcate talk time and ringing time.

In order to get that information, you should consider importing the CDRs generated by 3CX:
https://www.3cx.com/docs/cdr-call-data-records/

 

[Goga]

In order to get that information, you should consider importing the CDRs generated by 3CX:
https://www.3cx.com/docs/cdr-call-data-records/

In CDR for outgoing call it is providing us talk time but for incoming call Call duration provide total talk time that include customer listen to IVR+CallerRinging+Agnet talk.
For incoming call what I need to calculate so that I can bifurcate ringing time and agent talk time only.

Please advice

 

[edossantos]

Hello @Goga,

You will not be able to get that information from the CDR. A single line is generated for each call, and contains the summarized data.

Kind regards.