-
Notifications
You must be signed in to change notification settings - Fork 1
/
3cx_sync_script.sh
executable file
·85 lines (78 loc) · 4.79 KB
/
3cx_sync_script.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#!/bin/bash
##########################################
# #
# Which Column is which Number #
# #
# column | nubmertype #
# | #
# phonenumber | mobile #
# pv_an0 | mobile2 #
# pv_an1 | private #
# pv_an2 | private2 #
# pv_an3 | business #
# pv_an4 | business2 #
# #
##########################################
# Remove old files
rm -rf /tmp/yealink_extensions.xml
rm -rf /tmp/yealink_phonebook.xml
# Export phonebook entries
cat << \EOF | sudo su postgres -c "psql -d database_single"
COPY(
SELECT XMLFOREST(xmlpb.entry as "DivinusIPPhoneDirectory") FROM(
SELECT XMLCONCAT(
XMLELEMENT(NAME "Title", 'Phonelist'),
XMLELEMENT(NAME "Prompt", 'Prompt'),
XMLAGG(
XMLELEMENT(
NAME "DirectoryEntry",
XMLELEMENT(Name "Name",
case when(pb.company = '') IS FALSE AND pb.lastname != ''
and pb.firstname != ''
then pb.company || ' - ' || pb.lastname || ', ' || pb.firstname when(pb.company = '') IS FALSE AND pb.lastname != ''
and pb.firstname = ''
then pb.company || ' - ' || pb.lastname when(pb.company = '') IS FALSE AND pb.lastname = ''
and pb.firstname = ''
then pb.company when(pb.company = '') IS FALSE AND pb.lastname = ''
and pb.firstname != ''
then pb.company || ' - ' || pb.firstname when(pb.company = '') IS NOT FALSE AND pb.lastname != ''
and pb.firstname != ''
then pb.lastname || ', ' || pb.firstname when(pb.company = '') IS NOT FALSE AND pb.lastname != ''
and pb.firstname = ''
then pb.lastname end),
XMLELEMENT(Name "Telephone", pb.pv_an3),
XMLELEMENT(Name "Telephone",
case when not exists(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'phonebook'
AND COLUMN_NAME = 'pv_an4') then ''
when exists(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'phonebook'
AND COLUMN_NAME = 'pv_an4') then pb.pv_an4 end),
XMLELEMENT(Name "Telephone", pb.phonenumber),
XMLELEMENT(Name "Telephone", pb.pv_an1),
XMLELEMENT(Name "Telephone", pb.pv_an2)
)
)
) as entry FROM phonebook pb WHERE pb.fkidtenant = 1
) AS xmlpb
) TO '/tmp/yealink_phonebook.xml';
EOF
# Export assigned extensions, since those are not saved in the phonebook
cat << \EOF | sudo su postgres -c "psql -d database_single"
COPY(
SELECT * FROM(
SELECT XMLCONCAT(
XMLAGG(
XMLELEMENT(
NAME "DirectoryEntry",
XMLELEMENT(Name "Name", uv.display_name),
XMLELEMENT(Name "Telephone", uv.dn)
)
)
) as entry FROM users_view uv
) AS xmlpb
) TO '/tmp/yealink_extensions.xml';
EOF
# Some black magic
sed -i 's/<\/DivinusIPPhoneDirectory>//g' /tmp/yealink_phonebook.xml
cat /tmp/yealink_extensions.xml >> /tmp/yealink_phonebook.xml && echo "</DivinusIPPhoneDirectory>" >> /tmp/yealink_phonebook.xml
rm -rf /tmp/yealink_extensions.xml
#Further cleanup will be done by caller script