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