PBX work

Contents

FreePBX CID Lookup: SQL query for NextCloud database

SELECT cp2.value AS DB_NAME FROM oc_cards_properties AS cp1
LEFT JOIN oc_cards_properties AS cp2 ON cp2.addressbookid=cp1.addressbookid
  AND cp2.cardid=cp1.cardid AND cp2.name='FN'
WHERE cp1.name='TEL' AND 
  (cp1.value LIKE '[NUMBER]%' OR cp1.value LIKE CONCAT('%', [NUMBER]-10000000000))
LIMIT 1

But it didn’t work. The above query is 294 characters after eliminating newlines and unneeded whitespace, and FreePBX silently truncates the query to 255 characters! I managed to trim it down to the following:

SELECT b.value oc_cards_properties AS a
LEFT JOIN oc_cards_properties AS b ON b.addressbookid=a.addressbookid
  AND b.cardid=a.cardid AND b.name='FN'
WHERE a.name='TEL' AND 
  (a.value LIKE '[NUMBER]%' OR a.value LIKE CONCAT('%', [NUMBER]-10000000000))

That’s only 247 characters, and it works as expected.

To get this to work I had to grant access to the oc_card_properties table in in the nextcloud database on my server:

[root@penguin]SQL: GRANT SELECT ON nextcloud.oc_cards_properties TO 'asterisk'@'pbx'
  IDENTIFIED BY '************'"

Converting all English .ulaw files to .wav

I wrote the following little script to copy all the .ulaw files in /var/lib/asterisk/sounds/en to /tmp/asterisk-sounds-en-wav and convert them to WAV format. This makes it easer to play them on another computer because most systems know how to play WAV files but likely aren’t able to play u-law files.

mkdir /tmp/asterisk-sounds-en-wav
[ $? == 0 ] && cd /var/lib/asterisk/sounds/en
[ $? == 0 ] && find . -name '*.ulaw' | xargs tar cf - | tar x -C /tmp/asterisk-sounds-en-wav -f -
[ $? == 0 ] && cd /tmp/asterisk-sounds-en-wav
if [ $? == 0 ]; then
  echo -n "Converting files "
  find . -name '*.ulaw' | sort | while read F; do
    echo -n .; sox -r 8000 -c 1 -e u-law -t raw $F ${F/.ulaw/.wav}
    touch -r $F ${F/.ulaw/.wav}; rm -f $F
  done
  echo
fi



About

Ramblings about Linux and other things

Archives

Tags