Conversion of Call History FreePBX -> MikoPBX

PT1C_cdr is the table containing the call history from the telephony panel version 1 in FreePBX.

Actions in FreePBX

1. Let’s navigate to the FreePBX working directory.

   mkdir -p /usr/src/miko-mysql-to-sqlite;
   cd /usr/src/miko-mysql-to-sqlite

2. Download the script to convert the MySQL table to SQLite3. If it’s not available, make sure to download it.

   curl '' -o 'mysql2sqlite.sh'

3. Run the conversion script:

    ./mysql2sqlite.sh -ufreepbxuser -p123 asteriskcdrdb PT1C_cdr | grep -v 'CREATE INDEX

Actions in MikoPBX

The archive with the scripts is available via the provided link.

1. The file master.db, obtained in the previous step, must be transferred to MikoPBX in the directory: /storage/usbdisk1/mikopbx/freepbx-dmp.

2. Navigate to the MikoPBX working directory:

mkdir -p /storage/usbdisk1/mikopbx/freepbx-dmp;
cd /storage/usbdisk1/mikopbx/freepbx-dmp;

1. Prepare the list of files for uploading, specifying the date as "2020-02-07".

sqlite3 master.db 'SELECT strftime("%Y/%m/%d/", calldate) || recordingfile FROM PT1C_cdr WHERE recordingfile<>"" AND calldate > "2020-02-07"' > file-for-download.txt

1. Uploading call recordings and converting them to MP3.

curl '' -o ./download-and-convert.sh
sh ./download-and-convert.sh file-for-download.txt 10.70.10.6:443

1. Perform a backup of the call history database.

cp /storage/usbdisk1/mikopbx/astlogs/asterisk/cdr.db /storage/usbdisk1/mikopbx/astlogs/asterisk/cdr.db.dmp

1. Copy the call history database to the current directory.

cp /storage/usbdisk1/mikopbx/astlogs/asterisk/cdr.db ./cdr.db 

1. Convert the call history to the MikoPBX format. The history in cdr.db will be CLEARED.

sqlite3 << EOF
ATTACH DATABASE 'cdr.db' as 'new';
ATTACH DATABASE 'master.db' as 'old';

delete from new.cdr_general;

INSERT INTO new.cdr_general (
    src_num,
    dst_num,
    src_chan,
    dst_chan,
    start,
    answer,
    endtime,
    duration,
    billsec,
    disposition,
    UNIQUEID,
    did,
    linkedid,
    recordingfile
)
SELECT src,
       dst,
       channel,
       dstchannel,
       calldate,
       answer,
       "end",
       duration,
       billsec,
       disposition,
       linkedid || id,
       did,
       linkedid,
       "/storage/usbdisk1/mikopbx/voicemailarchive/monitor/" || strftime("%Y/%m/%d/", calldate) || recordingfile || ".mp3"
  FROM old.PT1C_cdr;

DETACH DATABASE 'new';
DETACH DATABASE 'old';
.exit
EOF

1. Clear the empty recordingfile fields in the database.

sqlite3 ./cdr.db 'UPDATE cdr_general SET recordingfile="" where recordingfile LIKE "%/.mp3"'

1. There are no call recordings before "2020-02-07". Let’s clear the recordingfile field.

sqlite3 ./cdr.db 'UPDATE cdr_general SET recordingfile="" where start<"2020-02-07"'

1. After moving the database file.

cp ./cdr.db /storage/usbdisk1/mikopbx/astlogs/asterisk/cdr.db 

Done! You can now check the call history in the web interface.