Zur Synchronisation eines Starface Adressbuches mit einem CRM System ist ein Zugriff auf die SQL Datenbank des CRM erforderlich.
Hierzu gibt es kostenpflichtige Module, die einige Nachteile mit sich bringen:
- Sie werden i.d.R. mit jedem großen Starface Update zerschossen und erfordern manuelle Nacharbeiten bei jedem größeren Starface Update
- Der Preis: Bei einer Starface mit 10 - 15 Lizenzen und ca. 2.500 Kontate im CRM kostet zwischen 40 und 50 € netto monatlich. Und hier sind die einmaligen Einrichtungskosten und der erhöhte Pflegeaufwand nicht einmal inbegriffen, der in keiner Relation zur API Stabilität der REST Schnittstelle und Konstanz der SQL Tabellen steht.
- Sie funktionieren nicht mit einer kostenfreien Starface Free Lizenz
Das folgende Python Skript übernimmt die Synchronisation 'von extern', d.h. Sie läuft auf einem (Linux-) Server mit Zugriff auf die SQL Datenbank und auf die Telefonanlage. Das funktioniert i.d.R. sehr lange Updatestabil.
Ich biete das Skript für "Techies" hier als Ideengeber kostenfrei zum Download. Sofern sie an einer Implementierung interessiert sind, kann ich diese im Rahmen einer einmaligen Pauschale je nach Aufwand i.H.v. ca. 400 - 500 € übernehmen.
Die Synchronisation funktioniert ebenfalls mit Carddav-Quellen wie Nextcloud/Owncload oder einem Synology NAS. Hierfür wird ein externes Carddav Skript im gleichen Ordner benötigt. Dieses finden sie hier
#!/usr/bin/env python3
# Version 1.2
# 2024-03-04
# Christian Krause
# Fix Import on Multicards in this version
# Fix Kein Reupload bei leerem Vornamen
import io, csv, requests, json, sys, hashlib, re, argparse, configparser, urllib3
# SQL Server
sqlServer = '192.168.178.250:49761'
sqlDB = 'repdoc'
sqlUser = 'sa'
sqlPass = 'XXX'
sqlTableName = 'Kunden'
# Carddav (Nextcloud, Owncloud)
cdavUrl = 'https://nextcloud.xxxx.de'
cdavUser = 'Christian'
cdavPass = 'XXX'
cdavAuth = 'basic'
cdavVerify = True
# Starface
sfProto = 'https'
sfServer = '192.168.178.15'
sfUser = '0001'
sfPass = 'XXX'
# Default Vorwahl
country = '+49'
city = '2133'
# Spaltenzuweisung
# Bezeichung : [ CSV Spaltenheader, isPhoneNumber, Index (unused), SQL - Tabellenname]
# isPhoneNumber = True Spalten werden 'gesäubert' im Hinblick auf nicht-numerische Zeichen
sqlTables = {
"Vorname": ["Vorname [contact:firstname]" , False, '0', "Name2"],
"Nachname": ["Name [contact:familyname]" , False, '1', "Name"],
"Firma": ["Firma [contact:company]" , False, '2', ""],
"Telefon": ["Rufnummer [telephone:phone]" , True , '3', "Telefon"],
"Telefon2": ["Privat [telephone:homephone]" , True , '4', "ZusatzTel"],
"Mobil": ["Mobil [telephone:mobile]" , True , '5', "Mobil"],
"Fax": ["Fax [telephone:fax]" , True , '6', "Telefax"],
"eMail": ["E-Mail [email:e-mail]" , False, '7', ""],
"PLZ": ["PLZ [address:postcode]" , False, '8', ""],
"Stadt": ["Stadt [address:city]" , False, '9', ""],
"Strasse": ["Straße [address:street]" , False, '10', ""],
}
def parseArgs():
c = argparse.ArgumentParser( description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter, add_help=False)
c.add_argument("-C", "--config", help="Specify config file", metavar="config")
defaults = { }
args, remaining_argv = c.parse_known_args()
if args.config:
config = configparser.ConfigParser()
config.read([args.config])
defaults.update(dict(config.items("defaults")))
p = argparse.ArgumentParser(parents=[c], description='SQL Konnektor Tool')
p.set_defaults(**defaults)
p.add_argument('-v', '--version', action='version', version='%(prog)s 0.9')
p.add_argument('-i', '--input', choices=['sql', 'carddav'], required=True, help='Input Source: sql or carddav')
p.add_argument('-o', '--output', choices=['print', 'csv', 'starface'], required=True, help='Output Source: print, write to CSV or Starface REST Upload')
return p.parse_args(remaining_argv)
def clean_number(number):
number = ''.join(c for c in number if c.isdigit() or c == '+')
if len(number) < 4: return ''
number = re.sub(r'^00', '+', number)
number = re.sub(r'^0([1-9])', country + r'\1', number)
return re.sub(r'^([1-9])', country + city + r'\1', number)
def read_sql(sqlServer, sqlUser, sqlPass, sqlDB):
addrTable = []
queryIsNumberList = []
csvHeader = []
queryTableList = []
for tableColoum in sqlTables:
csvHeader.append(sqlTables.get(tableColoum)[0])
if sqlTables.get(tableColoum)[3]:
queryTableList.append(sqlTables.get(tableColoum)[3])
else:
queryTableList.append(f"'' AS {tableColoum}")
queryIsNumberList.append(sqlTables.get(tableColoum)[1])
queryString = ','.join(queryTableList)
with pymssql.connect(sqlServer, sqlUser, sqlPass, sqlDB) as conn:
with conn.cursor() as cursor:
cursor.execute('SELECT %s FROM %s'% (queryString, sqlTableName))
#cursor.execute(f'SELECT {queryString} FROM {sqlTableName}')
row = cursor.fetchone()
while row:
row = list(row)
contactWithNumber = False
# Regex Cleaning Phone Numbers
for index, isNumber in zip(range(0,10), queryIsNumberList):
if isNumber and row[index]:
row[index] = clean_number(row[index])
contactWithNumber = True
# Save only if Contact has any phone number
if contactWithNumber:
addrTable.append(row)
contactWithNumber = False
row = cursor.fetchone()
return addrTable, csvHeader
def get_carddav(cdavUrl, user=cdavUser, passwd=cdavPass, auth=cdavAuth, verify=cdavVerify):
csvHeader = []
for tableColoum in sqlTables:
csvHeader.append(sqlTables.get(tableColoum)[0])
addrTable = []
cdav = carddav.PyCardDAV(cdavUrl, user=cdavUser, passwd=cdavPass, auth=cdavAuth, verify=cdavVerify)
abook = cdav.get_abook()
nCards = len(abook.keys())
print(f'Fetching {nCards} cards')
for href, etag in abook.items():
vCards = cdav.get_vcard(href).decode("utf-8")
for vCard in vobject.readComponents(vCards):
row = [''] * 11
rawName = vCard.contents['n'][0].value
name = re.sub(r' +', ' ', str(rawName).strip())
try:
# Try to split at right space
row[0], row[1] = name.rsplit(" ", 1)
except:
# If no space, use complete Name
row[1] = name
print(f'Get {name}')
try:
numbers = [tel.value for tel in vCard.contents['tel']]
except:
continue
i = 3
for tel in numbers:
row[i] = clean_number(tel)
i += 1
addrTable.append(row)
return addrTable, csvHeader
def csv_writer(addrTable, csvHeader):
csvObject = io.StringIO()
writer = csv.writer(csvObject, delimiter=';')
writer.writerow(csvHeader)
for addr in addrTable:
writer.writerow(addr)
return csvObject
class Starface:
def __init__(self, sfProto, sfServer, sfUser, sfPass):
self.url = f'{sfProto}://{sfServer}'
self.User = sfUser
self.Pass = sfPass
self.headers = {'Content-Type':'application/json', 'X-Version':'2'}
self.session = requests.session()
# Login and Header Creation
response = self.session.get(f'{self.url}/rest/login', headers=self.headers, verify=False)
templateJson = json.loads(response.content)
userandnonce=(self.User+templateJson['nonce']).encode(encoding='utf_8', errors='strict')
hpassword=hashlib.sha512(self.Pass.encode(encoding='utf_8', errors='strict')).hexdigest()
passwordHashed=hpassword.encode(encoding='utf_8')
hsecret = hashlib.sha512(userandnonce+passwordHashed).hexdigest().encode(encoding='utf_8')
secretCompound=self.User+':'+hsecret.decode(encoding='utf_8')
templateJson['secret'] = secretCompound
authTokenResponse = self.session.post(f'{self.url}/rest/login', data=json.dumps(templateJson), headers=self.headers)
self.headers.update({'authToken':json.loads(authTokenResponse.content)['token']})
# Get AddrBook
response = self.session.get(f'{self.url}/rest/contacts/tags', data='', headers=self.headers)
self.addrBook = json.loads(response.content)
def search_contact(self, searchString):
response = self.session.get(f'{sfProto}://{sfServer}/rest/contacts?searchTerms={searchString}', data='', headers=self.headers)
return json.loads(response.content)
def remove_contact(self, row, tagId):
response = self.session.delete(f'{self.url}/rest/contacts/{tagId}', data='', headers=self.headers)
print(f'Status: {response.status_code} delete: {row[1]}')
def add_contact(self, row):
contact={
"blocks": [
{ 'name': 'contact',
'attributes': [
{ 'name': 'firstname',
'value': row[0] },
{ 'name': 'familyname',
'value': row[1] },
{ 'name': 'company',
'value': row[2] }
] },
{ "name": "address",
"attributes": [
{ 'name': 'street',
'value': row[10] },
{ 'name': 'postcode',
'value': row[8] },
{ 'name': 'city',
'value': row[9] }
] },
{ "name": "telephone",
"attributes": [
{ 'name': 'phone',
'value': row[3] },
{ 'name': 'homephone',
'value': row[4] },
{ 'name': 'mobile',
'value': row[5] },
{ 'name': 'fax',
'value': row[6] }
] },
{ "name": "email",
"attributes": [
{ 'name': 'e-mail',
'value': row[7] }
] }
],
"editable": "true",
"tags": [
{ 'id': self.addrBook[0]['id'],
'name': self.addrBook[0]['name'],
'alias': self.addrBook[0]['alias'] }
],
"id": ""
}
response = self.session.post(f'{sfProto}://{sfServer}/rest/contacts', data=json.dumps(contact), headers=self.headers)
print(f'Status: {response.status_code} adding: {row[1]}')
def transfer(self, addrTable):
for row in addrTable:
sfContact = S.search_contact(row[1])
contactMissing = True
for contact in sfContact['contacts']:
if row[1] in contact['summaryValues'] and ( not row[0] or row[0] in contact['summaryValues']):
contactMissing = False
if row[3] and not row[3] in contact['phoneNumberValues']:
S.remove_contact(row, contact['id'])
contactMissing = True
break
elif row[4] and not row[4] in contact['phoneNumberValues']:
S.remove_contact(row, contact['id'])
contactMissing = True
break
elif row[5] and not row[5] in contact['phoneNumberValues']:
S.remove_contact(row, contact['id'])
contactMissing = True
break
print(f'Nothing changed: {row[1]}')
break
if contactMissing:
S.add_contact(row)
######## Start Main Programm ########
if __name__ == "__main__":
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
a = parseArgs()
# Select Input Mode
if a.input == 'sql':
import pymssql
addrTable, csvHeader = read_sql(sqlServer, sqlUser, sqlPass, sqlDB)
elif a.input == 'carddav':
import vobject
import carddav
addrTable, csvHeader = get_carddav(cdavUrl, user=cdavUser, passwd=cdavPass, auth=cdavAuth, verify=cdavVerify)
# Select Output Mode
if a.output == 'print':
csvObject = csv_writer(addrTable, csvHeader)
print(csvObject.getvalue())
elif a.output == 'csv':
csvObject = csv_writer(addrTable, csvHeader)
with open('sql_output.csv', 'w', encoding="utf-8") as file:
file.write(csvObject.getvalue())
print('sql_output.csv written')
elif a.output == 'starface':
S = Starface(sfProto, sfServer, sfUser, sfPass)
S.transfer(addrTable)