starface.jpg 

Starface Adressbuch: SQL / CardDav Import via REST API

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.1
# 2023-06-29
# Christian Krause
# Fix Import on Multicards in this version

import io, csv, requests, json, sys, hashlib, re, argparse, configparser, urllib3

# SQL Server
sqlServer = '192.168.178.250:50000'
sqlDB = 'USER'
sqlUser = 'sa'
sqlPass = 'GEHEIM'
sqlTableName = 'Kunden'

# Carddav (Nextcloud, Owncloud)
cdavUrl = 'https://URL/calendar'
cdavUser = 'USER'
cdavPass = 'GEHEIM'
cdavAuth = 'basic'
cdavVerify = True

# Starface
sfProto = 'https'
sfServer = '192.168.1.1'
sfUser = '0001'
sfPass = 'GEHEIM'

# 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[0] in contact['summaryValues'] and row[1] 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)