search

dorel14 / 3CX-Cdr-Tcp-Server

GNU General Public License v3.0
4 stars 3 forks source link

V20 cdr column names #16

Closed dorel14 closed 2 months ago

dorel14 commented 2 months ago 
          Im woundering if somehing has changed to the Active socket in 3cx V20,

In your description it says configure CDR´s in the following sequence for example time_start but in V20 it says time-start so it is a dash insteed of a underscore. But still some fields seems to remain the same like historyid

image

image

Originally posted by @MrGlenn-tech in https://github.com/dorel14/3CX-Cdr-Tcp-Server/issues/12#issuecomment-2082158245

MrGlenn-tech commented 2 months ago

Not sure if this is the case but it was an idea, all tho i think we should see something in the database since it at least look like historyid has the same column name.

dorel14 commented 2 months ago

Can you add some logs from tcpserver container when you make or receive a call so that i can see what happen ?

MrGlenn-tech commented 2 months ago

This is the latest entry if i run a "sudo docker logs containerid" 

Exception occurred during processing of request from ('172.17.17.61', 58050)
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/socketserver.py", line 683, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/local/lib/python3.9/socketserver.py", line 360, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/home/appuser/app/srv/tcp_socket_server.py", line 17, in __init__
    socketserver.BaseRequestHandler.__init__(self, request,
  File "/usr/local/lib/python3.9/socketserver.py", line 747, in __init__
    self.handle()
  File "/home/appuser/app/srv/tcp_socket_server.py", line 29, in handle
    cdrs, cdrdetails = parse_cdr(cdr)
  File "/home/appuser/app/myhelpers/cdr.py", line 126, in parse_cdr
    df_cdr_details["call_date"] = df_cdr["time_start"].apply(
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/series.py", line 4626, in apply
    return SeriesApply(self, func, convert_dtype, args, kwargs).apply()
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/apply.py", line 1025, in apply
    return self.apply_standard()
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/apply.py", line 1076, in apply_standard
    mapped = lib.map_infer(
  File "pandas/_libs/lib.pyx", line 2834, in pandas._libs.lib.map_infer
  File "/home/appuser/app/myhelpers/cdr.py", line 127, in <lambda>
    lambda x: dt.date(dt.strptime(x, date_format))
TypeError: strptime() argument 1 must be str, not float
dorel14 commented 2 months ago

Ok , there is a problem with start_date,

In logs there is a row with the line received by server, can you add it so i can see what is sent a cdr line should look like this 

Call 9744,00000171DEE41BB3_2501,00:05:15,2020/05/04 10:53:43,2020/05/04 10:53:43,2020/05/04 10:58:59,TerminatedByDst,0611360468,Ext.610,10000,610,610,ReplacedDst,Ext.185,185,,,,,"Chain: 0611360468,Ext.610,Ext.185,",External,Internal,Internal,from disp,to disp,fin disp,

When you add logs , can you start and end with that

MrGlenn-tech commented 2 months ago

Hi,

sorry all i can find in the logs when running "sudo docker logs dockerid" is the the same as i sent above, 


----------------------------------------
Exception occurred during processing of request from ('172.17.17.61', 40858)
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/socketserver.py", line 683, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/local/lib/python3.9/socketserver.py", line 360, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/home/appuser/app/srv/tcp_socket_server.py", line 17, in __init__
    socketserver.BaseRequestHandler.__init__(self, request,
  File "/usr/local/lib/python3.9/socketserver.py", line 747, in __init__
    self.handle()
  File "/home/appuser/app/srv/tcp_socket_server.py", line 29, in handle
    cdrs, cdrdetails = parse_cdr(cdr)
  File "/home/appuser/app/myhelpers/cdr.py", line 126, in parse_cdr
    df_cdr_details["call_date"] = df_cdr["time_start"].apply(
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/series.py", line 4626, in apply
    return SeriesApply(self, func, convert_dtype, args, kwargs).apply()
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/apply.py", line 1025, in apply
    return self.apply_standard()
  File "/home/appuser/venv/lib/python3.9/site-packages/pandas/core/apply.py", line 1076, in apply_standard
    mapped = lib.map_infer(
  File "pandas/_libs/lib.pyx", line 2834, in pandas._libs.lib.map_infer
  File "/home/appuser/app/myhelpers/cdr.py", line 127, in <lambda>
    lambda x: dt.date(dt.strptime(x, date_format))
TypeError: strptime() argument 1 must be str, not float
----------------------------------------
dorel14 commented 2 months ago

There is a problem in the data being sent in cdr, normaly time_start should look like 2024/04/29 23:58:55 and not a float

Can you try generate a filé with one call so that i can see what cdr looks like, ?

MrGlenn-tech commented 2 months ago

Hi sorry i miss understod you there, here is the log from a CDR 

Call 265500,89d3af2d8f010000_3456,00:08:10,2024/04/30 06:27:28,2024/04/30 06:27:36,2024/04/30 06:35:46,TerminatedByDst,070XXXXXXX,Ext.80002,10020,80002,80002,ReplacedDst,Ext.10013,10013,,,,,Chain: 070XXXXXXX;Ext.80002;Ext.10013;,Line,Queue,Extension,070XXXXXXX,Partnersupport,Måns Last name Last Name,

I also found this in 3cxSystemService.log when i switch to Active socket insteed of singel files. and there you can see the date is formated like 2024-04-30 instead of 2024/04/30 Not sure if this info helps at all. 

2024/04/30 08:44:46.173|1802315|0041|Trac|CHR: Invoking OnCallEnded event
2024/04/30 08:44:46.173|1802315|0041|Trac|CHR: OnCallEnded event successfully invoked
2024/04/30 08:44:46.173|1802315|0041|Trac|CHR: [CallFlowParser] Processing MyPhoneCallHistory Destination Record: CallDBId={265512}; DnOwner={10000}; PartyDn={10020}; PartyDnType={ExternalLine}; PartyName={070XXXXXXX          }; PartyCallerId={070XXXXXXX          }; StartTime={2024-04-30 06:42:26.345736}; EstablishedTime={2024-04-30 06:42:31.317858}; EndTime={2024-04-30 06:44:46.142831}; EndStatus={0    }; EndStatusHint={0    }; PartyCRMContact={                    };
2024/04/30 08:44:46.173|1802315|0041|Trac|CHR: [DBSaver] Executing SQL:
                CALL public.sp_chr__save_myphone_callhistory_record(
                            :call_id,
                            :calltype,
                            :dnowner,
                            :party_dn,
                            :party_dntype,
                            :party_name,
                            :party_callerid,
                            :via_role,
                            :via_dn,
                            :via_dntype,
                            :via_dnname,
                            :via_callerid,
                            :start_time,
                            :established_time,
                            :end_time,
                            :end_status,
                            :end_status_hint,
                            :divert_dn,
                            :divert_dntype,
                            :divert_dnname,
                            :divert_callerid,
                            :dialed_number,
                            :party_crm_contact,
                    :myphone_id
                )
      Parameters:
          call_id := 265512,
          calltype := 2,
          dnowner := '10000',
          party_dn := '10020',
          party_dntype := 128,
          party_name := '070XXXXXXX',
          party_callerid := '070XXXXXXX',
          via_role := 3,
          via_dn := '80002',
          via_dntype := 2,
          via_dnname := '80002',
          via_callerid := '',
          start_time := '2024-04-30 08:42:26.345736',
          established_time := '2024-04-30 08:42:31.317858',
          end_time := '2024-04-30 08:44:46.142831',
          end_status := 0,
          end_status_hint := 0,
          divert_dn := null,
          divert_dntype := null,
          divert_dnname := null,
          divert_callerid := null,
          dialed_number := null,
          party_crm_contact := null,
          myphone_id := null
2024/04/30 08:44:46.174|1802315|0041|Trac|CHR: CDR: writing data...
2024/04/30 08:44:46.174|1802315|0041|Trac|CHR: CDR: data are written
2024/04/30 08:44:46.175|1802315|0041|Trac|CHR: [DBSaver] Committed SQL transaction
2024/04/30 08:44:46.175|1802315|0041|Trac|CHR: [DBSaver] Closed SQL connection
2024/04/30 08:44:46.175|1802315|0041|Trac|CHR: [QM] Discarded: 0d85bd2d8f010000_3470
2024/04/30 08:44:46.175|1802315|0041|Info|CHR: [CallFlowParser] Call processing finished
2024/04/30 08:44:46.223|1802315|0048|Trac|CHR: [CDR] Remote end connection will be closed
2024/04/30 08:44:46.223|1802315|0048|Info|CHR: [CDR] Closing TCP client
2024/04/30 08:44:46.224|1802315|0048|Info|CHR: [CDR] connected to OutboundTCP@172.17.17.66:5000
dorel14 commented 2 months ago

Hello I have tested to send the row you give me and no problem at all to insert in db with this tool

https://www.01net.com/telecharger/utilitaire/reseau/tcp-client-server.html

here is what i see when i do a

docker logs --follow with 3cx-cdr-tcp-server-3cxcdrserver-1

2024-05-01 16:09:47 {"historyid":"Call 265500","callid":"89d3af2d8f010000_3456","duration":"00:08:10","time_start":1714465648000,"time_answered":1714466146000,"time_end":1714466146000,"reason_terminated":"TerminatedByDst","from_no":"070XXXXXXX","to_no":"Ext.80002","from_dn":"10020","to_dn":"80002","dial_no":"80002","reason_changed":"ReplacedDst","final_number":"Ext.10013","final_dn":"10013","bill_code":null,"bill_rate":null,"bill_cost":null,"bill_name":null,"chain":"Chain: 070XXXXXXX;Ext.80002;Ext.10013;","from_type":"Line","to_type":"Queue","final_type":"Extension","from_dispname":"070XXXXXXX","to_dispname":"Partnersupport","final_dispname":"M\u00e5ns Last name Last Name","missed_queue_calls":null}
2024-05-01 16:09:47 
2024-05-01 16:09:47 200 b'{"historyid":"Call 265500","callid":"89d3af2d8f010000_3456","duration":"00:08:10","time_start":"2024-04-30T10:27:28","time_answered":"2024-04-30T10:35:46","time_end":"2024-04-30T10:35:46","reason_terminated":"TerminatedByDst","from_no":"070XXXXXXX","to_no":"Ext.80002","from_dn":"10020","to_dn":"80002","dial_no":"80002","reason_changed":"ReplacedDst","final_number":"Ext.10013","final_dn":"10013","bill_code":null,"bill_rate":null,"bill_cost":null,"bill_name":null,"chain":"Chain: 070XXXXXXX;Ext.80002;Ext.10013;","from_type":"Line","to_type":"Queue","final_type":"Extension","from_dispname":"070XXXXXXX","to_dispname":"Partnersupport","final_dispname":"M\xc3\xa5ns Last name Last Name","missed_queue_calls":null,"id":7}'
2024-05-01 16:09:47 200 b'{"cdr_historyid":"Call 265500","abandonned":false,"handling_time_seconds":498,"waiting_time_seconds":498,"call_date":"2024-04-30","call_time":"06:27:28","call_week":18,"day_of_week":"mardi","id":7}'

and docker logs --follow with 3cx-cdr-tcp-server-webapi-1

2024-05-01 16:09:47 INFO:     172.18.0.6:44156 - "POST /api/v1/cdr HTTP/1.1" 200 OK
2024-05-01 16:09:47 2024-05-01 14:09:47,737 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-01 16:09:47 2024-05-01 14:09:47,737 INFO sqlalchemy.engine.Engine INSERT INTO call_data_records_details (cdr_historyid, abandonned, handling_time_seconds, waiting_time_seconds, call_date, call_time, call_week, day_of_week) VALUES (%(cdr_historyid)s, %(abandonned)s, %(handling_time_seconds)s, %(waiting_time_seconds)s, %(call_date)s, %(call_time)s, %(call_week)s, %(day_of_week)s) RETURNING call_data_records_details.id
2024-05-01 16:09:47 2024-05-01 14:09:47,737 INFO sqlalchemy.engine.Engine [cached since 717.7s ago] {'cdr_historyid': 'Call 265500', 'abandonned': False, 'handling_time_seconds': 498, 'waiting_time_seconds': 498, 'call_date': datetime.date(2024, 4, 30), 'call_time': datetime.time(6, 27, 28), 'call_week': 18, 'day_of_week': 'mardi'}
2024-05-01 16:09:47 2024-05-01 14:09:47,738 INFO sqlalchemy.engine.Engine COMMIT
2024-05-01 16:09:47 2024-05-01 14:09:47,741 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-05-01 16:09:47 2024-05-01 14:09:47,741 INFO sqlalchemy.engine.Engine SELECT call_data_records_details.cdr_historyid, call_data_records_details.abandonned, call_data_records_details.handling_time_seconds, call_data_records_details.waiting_time_seconds, call_data_records_details.call_date, call_data_records_details.call_time, call_data_records_details.call_week, call_data_records_details.day_of_week, call_data_records_details.id
MrGlenn-tech commented 2 months ago

So i figured it out :)

in the .env i had set the info

to be 

#TZ informations
TZ=Europe/Stockholm
#Locales informations
LOCALE_LANGUAGE = sv-SE

But when i changed to the same as the template it started working. 

#TZ informations
TZ=Europe/Paris
#Locales informations
LOCALE_LANGUAGE = fr_FR

now i get, 

200 b'{"historyid":"Call 266531","callid":"37dd3c388f010000_4489","duration":"00:00:19","time_start":"2024-05-02T11:37:43","time_answered":"2024-05-02T11:38:14","time_end":"2024-05-02T11:38:14","reason_terminated":"TerminatedBySrc","from_no":"Ext.10007","to_no":"070XXXXXXX","from_dn":"10007","to_dn":"10020","dial_no":"070XXXXXXX","reason_changed":null,"final_number":"070XXXXXXX","final_dn":"10020","bill_code":"default","bill_rate":"1","bill_cost":"0.33","bill_name":"default","chain":"Chain: Ext.10007;070XXXXXXX;","from_type":"Extension","to_type":"Line","final_type":"Line","from_dispname":"Agent Wilhelmsson","to_dispname":"706766765","final_dispname":"706766765","missed_queue_calls":null,"id":3}'
200 b'{"cdr_historyid":"Call 266531","abandonned":false,"handling_time_seconds":31,"waiting_time_seconds":31,"call_date":"2024-05-02","call_time":"07:37:43","call_week":18,"day_of_week":"jeudi","id":3}'
dorel14 commented 2 months ago

OK well done

i open a new issue with these comments

Can you put ~~~ when you write logs or other files it makes them more readable

MrGlenn-tech commented 2 months ago

Sure, sorry about that not used to github so much.