Callmonitor

Aus Limbas Wiki

Wechseln zu: Navigation, Suche

Der Callmonitor wurde in Ausgabe 5.17 des PHP Magazins vorgestellt. Hier befinden sich alle beschriebenen Dateien.

Callserver-Dateien

Auf dem Server müssen die Dateien callmonitor.php und callserver.php von der Konsole aus gestartet werden.

Für die Soap Verbindung mit Limbas werden auf dem Server zusätzlich die Limbas-Dateien aus openlimbas/public/ benötigt. Die Datei openlimbas/public/lib/config.lib muss dann noch auf den Server mit der Limbas Installation konfiguriert werden.

Https-Unterstützung kann mit STunnel erreicht werden.

callmonitor.php

Verzeichnis: <callserver>/srv/callmonitor/

#!/usr/bin/php
<?php
# Description:
#   Waits for notification from fritzbox forever.
#   Notifies limbas user via websocket chatserver on incoming call and calls soap extension (to look up customer name).
#   Inserts phone call information into limbas table via soap extension after phone call has been ended.

# address and port of fritzbox
$fritzHost = '192.168.6.1'; // TODO
$fritzPort = 1012;

# address and port of websocket chatserver
$wsHost = '<your ip>'; // TODO
$wsPort = <your port>; // TODO

require_once("/srv/callmonitor/soap/lib/include.lib");
require_once("/srv/callmonitor/CallList.class.php");

# connect to fritzbox
$fritzboxSocket = fsockopen($fritzHost, $fritzPort);

# store last 10 calls
$callList = new CallList(10);

# wait for new lines
while(true) {
    $newLine = fgets($fritzboxSocket);
    if($newLine != null) {
        echo $newLine;
        handleFritzboxEvent($newLine);
    } else {
        sleep(1);
    }
}

function handleFritzboxEvent($line) {
    global $connections;
    global $fritzHost;
    global $fritzPort;
    global $wsHost;
    global $wsPort;
    global $callList;

    # split into parts
    $parts = split(';', $line);
    
    # extract current time, type of event and connection id
    $dateTime = $parts[0];
    $type = $parts[1];
    $connectionId = $parts[2];
    
    # differ between event types
    if($type == 'CALL') { # outgoing ring
        $calledNr = $parts[5];

        # store phone nr
        $callList->onRing($connectionId, $calledNr, false);        
        
    } elseif($type == 'RING') { # incoming ring
        $callerNr = $parts[3];
        
        # store phone nr
        $callList->onRing($connectionId, $callerNr, true);     
    
    } elseif($type == 'CONNECT') { # call started
        $callerPhone = $parts[3];
        
        # store start date of call
        $callList->onConnect($connectionId, $dateTime, $callerPhone);
        
    } elseif($type == 'DISCONNECT') { # call ended
        $lengthSec = $parts[3];
        
        # store length of call
        $callList->onDisconnect($connectionId, $lengthSec);
        
    } else {
        return;
    }
    
    limbasSocketNotification($connectionId, ucfirst(strtolower($type)));
}

function limbasSocketNotification($cid, $type) {
    global $wsHost;
    global $wsPort;
    global $callList;
    
    # Get data via soap given phone nr
    $data = null;
    try {
        $lmpar[0]['extension']["OnPhone$type"] = json_encode($callList->get($cid));
        $lmpar[0]['getvars'] = array('fresult');
        $cdata = call_client($lmpar);
        if(count($cdata) > 0 && $cdata[0]){
            $data = $cdata[0];
        }
    } catch (Exception $ex) {
        error_log("Could not fetch call data!");
        error_log($ex->getMessage());
        error_log($ex->getTraceAsString());
    }
    
    # Store data in call list
    $data = $data ? $data : "";
    $callList->setData($cid, $data);
        
    # Notify socket server (show number on screen)
    $data = array(
        'type' => 'phonecall',
        'data' => $callList->toHtml()
    );
    try {
        sendDataToWebsocketServer($wsHost, $wsPort, json_encode($data));
    } catch (Exception $ex) {
        error_log("Could not send customer data to websocket server!");
        error_log($ex->getMessage());
        error_log($ex->getTraceAsString());
    }
}

# ========= WEBSOCKET STUFF ==========
function sendDataToWebsocketServer($host, $port, $datastring){
    $data =hybi10Encode($datastring);
    $key = generateKey();
    $head = "GET / HTTP/1.1\r\n".
            "Upgrade: websocket\r\n".
            "Connection: Upgrade\r\n".
            "Host: $host:$port\r\n".
            "Sec-WebSocket-Key: $key\r\n".
            "Sec-WebSocket-Version: 13\r\n\r\n";

    if($sock = fsockopen($host, $port, $errno, $errstr)){
        fwrite($sock, $head);
        $header = fread($sock, 2000);
        $headers = array();
        $lines = preg_split("/\r\n/", $header);

        foreach($lines as $line){
            $line = chop($line);
            if(preg_match('/\A(\S+):(.*)\z/', $line, $matches))	{
                $headers[$matches[1]] = $matches[2];
            }
        }

        if($headers['Sec-WebSocket-Accept']===false){
            error_log("No header Sec-WebSocket-Accept!");
        }else{
            $keyAccept = $headers['Sec-WebSocket-Accept'];
            $expectedResonse = base64_encode(pack('H*', sha1($key . '258EAFA5-E914-47DA-95CA-C5AB0DC85B11')));
            if ($keyAccept == $expectedResonse) {
                fwrite($sock, $data) or die('error:'.$errno.':'.$errstr); //Server ignores this message
            }   
        }
        fclose($sock);
    }else{
        error_log("Could not open socket!");
    }
}

function generateKey() {
    $chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!"$&/()=[]{}0123456789';
    $key = '';
    $chars_length = strlen($chars);
    for ($i = 0; $i < 16; $i++) $key .= $chars[mt_rand(0, $chars_length-1)];
    return base64_encode($key);
}

function hybi10Encode($payload, $type = 'text', $masked = true) {
    $frameHead = array();
    $frame = '';
    $payloadLength = strlen($payload);

    switch ($type) {
        case 'text':
            // first byte indicates FIN, Text-Frame (10000001):
            $frameHead[0] = 129;
            break;

        case 'close':
            // first byte indicates FIN, Close Frame(10001000):
            $frameHead[0] = 136;
            break;

        case 'ping':
            // first byte indicates FIN, Ping frame (10001001):
            $frameHead[0] = 137;
            break;

        case 'pong':
            // first byte indicates FIN, Pong frame (10001010):
            $frameHead[0] = 138;
            break;
    }

    // set mask and payload length (using 1, 3 or 9 bytes)
    if ($payloadLength > 65535) {
        $payloadLengthBin = str_split(sprintf('%064b', $payloadLength), 8);
        $frameHead[1] = ($masked === true) ? 255 : 127;
        for ($i = 0; $i < 8; $i++) {
            $frameHead[$i + 2] = bindec($payloadLengthBin[$i]);
        }

        // most significant bit MUST be 0 (close connection if frame too big)
        if ($frameHead[2] > 127) {
            $this->close(1004);
            return false;
        }
    } elseif ($payloadLength > 125) {
        $payloadLengthBin = str_split(sprintf('%016b', $payloadLength), 8);
        $frameHead[1] = ($masked === true) ? 254 : 126;
        $frameHead[2] = bindec($payloadLengthBin[0]);
        $frameHead[3] = bindec($payloadLengthBin[1]);
    } else {
        $frameHead[1] = ($masked === true) ? $payloadLength + 128 : $payloadLength;
    }

    // convert frame-head to string:
    foreach (array_keys($frameHead) as $i) {
        $frameHead[$i] = chr($frameHead[$i]);
    }

    if ($masked === true) {
        // generate a random mask:
        $mask = array();
        for ($i = 0; $i < 4; $i++) {
            $mask[$i] = chr(rand(0, 255));
        }

        $frameHead = array_merge($frameHead, $mask);
    }
    $frame = implode('', $frameHead);
    // append payload to frame:
    for ($i = 0; $i < $payloadLength; $i++) {
        $frame .= ($masked === true) ? $payload[$i] ^ $mask[$i % 4] : $payload[$i];
    }

    return $frame;
}

?>

callserver.php

Verzeichnis: <callserver>/srv/callmonitor/

#!/usr/bin/php
<?php
# Description:
#   - Maintains multiple websocket connections
#   - Sends notifications about incoming phone calls to all connected clients

# config
$host = '127.0.0.1'; // TODO
$port = 9002; // TODO
$sleeptime = 1; // seconds
$null = NULL;

# start server
$socket = socket_create(AF_INET, SOCK_STREAM, SOL_TCP);	 // Create TCP/IP sream socket

socket_set_option($socket, SOL_SOCKET, SO_REUSEADDR, 1); // set the option to reuse the port
socket_bind($socket, $host, $port); // "bind" the socket to the address to "localhost", on port $port
socket_listen($socket);	 // start listen for connections

$clients = array($socket); // all connected clients
$registeredClients = array(); // all registered clients (browser only)
$lastMessage = ''; // last message received, used to send to newly connected clients

while (true) {
    sleep($sleeptime); // wait seconds

    $changed = $clients;

    # check if data is available
    if (socket_select($changed, $null, $null, $null) < 1) { continue; }

    # check for new connections
    if (in_array($socket, $changed)) {
        $socket_new = socket_accept($socket);
        $clients[] = $socket_new;
        $header = socket_read($socket_new, 1024);
        perform_handshaking($header, $socket_new, $host, $port);
        socket_getpeername($socket_new, $ip);
        $key = array_search($socket, $changed);
        unset($changed[$key]);
        
        # add to connected users
        $user = array();
        $user['socket'] = $socket_new;
        $registeredClients[] = $user;
        
        # send last message
        send_message_to_socket($socket_new, $lastMessage);
    }

    # loop through every client with new data
    foreach ($changed as $changed_socket) {
        $data = @socket_recv($changed_socket, $buf, 2048, 0);

        # check if client disconnected
        if ($data === false) {
            $key = array_search($changed_socket, $clients);
            unset($clients[$key]);
            continue;
        }

        # message handling  
        if($buf != null){
            # get message as object
            $received_text = unmask($buf);

            $msg = json_decode($received_text);
            if($msg == null || $msg == false || !property_exists($msg, "type")){ continue; }

            # phonecall	
            if($msg->type == "phonecall"){
                $message = mask(json_encode($msg));
                foreach($registeredClients as $client){
                    send_message_to_socket($client["socket"], $message);
                }
                $lastMessage = $message;
            }
        }
    } // end of foreach $client
}

// close the listening socket
socket_close($socket);

function send_message_to_socket($socket, $msg){
    return @socket_write($socket, $msg, strlen($msg));
}

// Unmask incoming framed message
function unmask($text) {
    $length = ord($text[1]) & 127;
    if($length == 126) {
        $masks = substr($text, 4, 4);
        $data = substr($text, 8);
    } elseif($length == 127) {
        $masks = substr($text, 10, 4);
        $data = substr($text, 14);
    } else {
        $masks = substr($text, 2, 4);
        $data = substr($text, 6);
    }
    $text = "";
    for ($i = 0; $i < strlen($data); ++$i) {
        $text .= $data[$i] ^ $masks[$i%4];
    }
    return $text;
}

// Encode message for transfer to client.
function mask($text) {
    $b1 = 0x80 | (0x1 & 0x0f);
    $length = strlen($text);

    if($length <= 125)
        $header = pack('CC', $b1, $length);
    elseif($length > 125 && $length < 65536)
        $header = pack('CCn', $b1, 126, $length);
    elseif($length >= 65536)
        $header = pack('CCNN', $b1, 127, $length);
    return $header.$text;
}

// handshake new client.
function perform_handshaking($receved_header,$client_conn, $host, $port) {
    $headers = array();
    $lines = preg_split("/\r\n/", $receved_header);
    foreach($lines as $line){
        $line = chop($line);
        if(preg_match('/\A(\S+): (.*)\z/', $line, $matches)){
            $headers[$matches[1]] = $matches[2];
        }
    }

    if(array_key_exists('Sec-WebSocket-Key', $headers)) {
        $secKey = $headers['Sec-WebSocket-Key'];
    } else {
        return false;
    }
    $secAccept = base64_encode(pack('H*', sha1($secKey . '258EAFA5-E914-47DA-95CA-C5AB0DC85B11')));
    //hand shaking header
    $upgrade  = "HTTP/1.1 101 Web Socket Protocol Handshake\r\n" .
    "Upgrade: websocket\r\n" .
    "Connection: Upgrade\r\n" .
    "WebSocket-Origin: $host\r\n" .
    "WebSocket-Location: ws://$host:$port/demo/shout.php\r\n".
    "Sec-WebSocket-Accept:$secAccept\r\n\r\n";
    socket_write($client_conn,$upgrade,strlen($upgrade));
}

?>

CallList.class.php

Verzeichnis: <callserver>/srv/callmonitor/

Beschreibung: Verwaltet Daten von mehreren Anrufen, ordnet eingehende Events anhand der ConnectionID zu bereits existierenden Anrufen zu

<?php
class CallList {
    private $maxSize;
    private $list;    
    
    public function __construct($maxSize) {
        $this->maxSize = $maxSize;
        $this->list = array();
    }
    
    public function onRing($connectionId, $phoneNr, $incoming) {
        # create new value
        $newValue = array(
            'cid' => $connectionId,
            'phoneNr' => $phoneNr,
            'incoming' => $incoming
        );
    
        # add new value to array
        array_unshift($this->list, $newValue);
        
        # ensure max elements
        $this->list = array_slice($this->list, 0, $this->maxSize);
    }
    
    public function onConnect($connectionId, $startDate, $callerPhone) {
        $key = $this->getCallKey($connectionId);
        $this->list[$key]['startDate'] = $startDate;
        $this->list[$key]['callerPhone'] = $callerPhone;
        $this->list[$key]['connected'] = true;
    }
    
    public function onDisconnect($connectionId, $lengthSec) {
        $key = $this->getCallKey($connectionId);
        $this->list[$key]['lengthSec'] = $lengthSec;
    }
    
    public function setData($connectionId, $data) {
        $key = $this->getCallKey($connectionId);
        $this->list[$key]['data'] = $data;
    }
    
    public function get($connectionId) {
        $key = $this->getCallKey($connectionId);
        
        return $this->list[$key];
    }
    
    public function toHtml() {
        $mapper = function($el) {
            return strval($el['data']);
        };
        
        $dataArr = array_map($mapper, $this->list);
        
        return implode(, $dataArr);
    }
    
    private function getCallKey($connectionId) {
        foreach($this->list as $key => $call) {
            if($call['cid'] == $connectionId) {
                return $key;
            }
        }
    }
}

?>


Limbas-Dateien

Die folgenden Dateien können in einem beliebigen Unterordner im openlimbas/dependent/EXTENSIONS Verzeichnis angelegt werden. Dadurch werden sie von Limbas automatisch eingebunden.

ext_main.js

Beschreibung: Stellt die Verbindung zwischen dem Limbas Frontend und dem Callserver her

var websocket;

function limbas_chat_start(id){
    // settings
    var wsUri = "wss://<your url>:<your port>"; // TODO
    
    var maxEntries = 10;
    
    var colors = {
        loading: '#f4df42',
        onOpen: '#048717',
        onMessage: '#2a97d6',
        onError: '#c10111',
        onClose: '#c10111'
    };
    
    var delay = {
        onMsgRemoveIconColor: 1000,
        reconnect: 10000
    };
    
    // div in which content will be pushed
    var multiframeDiv = $('#limbasDivMultiframePreview'+id);
    var icon = multiframeDiv.parents('table').first().find('.lmbMenuHeaderImage.lmb-icon');
    
    icon.css('color', colors.loading); // set to yellow while loading

    // create websocket
    websocket = new WebSocket(wsUri);
    
    // print success message on connect
    websocket.onopen = function(){
        icon.css('color', colors.onOpen);
    };
    
    // print text that was sent
    websocket.onmessage = function(ev) {                
        if(ev == null || ev.data == null) { return; }
        
        var msg = JSON.parse(ev.data);

        if(msg != null && msg.type === "phonecall" && msg.data != null){
            icon.css('color', colors.onMessage);
            window.setTimeout(function() {
                if(websocket.readyState == 1) {
                    icon.css('color', colors.onOpen);
                } else {
                    icon.css('color', colors.onError);
                }
            }, delay.onMsgRemoveIconColor);
            
            // Show customerData and strip tags
            multiframeDiv.html(msg.data);            
        }
    };

    // log and display error
    websocket.onerror = function(ev){
        icon.css('color', colors.onError);
    }; 

    // display close
    websocket.onclose = function(ev){
        icon.css('color', colors.onClose);
        
        // try to re-open
        window.setTimeout(function() {
            limbas_chat_start(id);
        }, delay.reconnect);        
    };

    // close websocket connection before window unload
    $( window ).on('beforeunload', function() {
        if(websocket != null) {
            websocket.close();
        }
    });
}

function limbas_callmonitor_toggle(evt,id,name,gtabid,params,type,manual){
    // div in multiframe that can be shown/hidden
    var contentdiv = $('#CONTENT_' + id);
    var multiframeDiv = $('#limbasDivMultiframePreview'+id);
    var icon = multiframeDiv.parents('table').first().find('.lmbMenuHeaderImage.lmb-icon');
    
    // if hidden -> will be shown by hideShow function -> establish connection
    if(contentdiv.css("display") == "none"){
        // re-establish connection to chat server
        if(websocket == null || websocket.readyState != 1){
            multiframeDiv.html();
            limbas_chat_start(id);
        }
    } else {
        // planned closing, dont output error
        if(websocket != null) {
            websocket.onclose = null;
            websocket.close();
        }
        icon.css('color', );
    }

    hideShow(evt,id,name,gtabid,params,type,manual);
}

ext_multiframe.inc

Beschreibung: Fügt das Callmonitor Element in den Limbas Multiframe ein

<?php

# Call monitor
if($LINK[40]){
    $melements["id"] = "Phone";
    $melements["name"] = "Telefon";
    $melements["link"] = "";
    $melements["target"] = "main";
    $melements["preview"] = "";
    $melements["event"] = "limbas_callmonitor_toggle";
    $melements["params"] = "";
    $melements["gicon"] = "lmb-phone";
    $melements["autorefresh"] = 1;
    $menu[0][] = $melements;
}

?>

ext_soap.inc

Beschreibung: Antwortet auf Anfragen des Callmonitors mit Html, das im Multiframe Element angezeigt wird

Der folgende Code ist nur eine beispielhafte Implementierung der Schnittstelle!

<?php

require_once('lib/context.lib');

function extSoapOnPhoneCall($param_string=null, $param_array=null, $lmb=null) {
    return getCredentials($param_string, 'lmb-sign-out');
}

function extSoapOnPhoneRing($param_string=null, $param_array=null, $lmb=null) {
    return '<b>' . getCredentials($param_string, 'lmb-sign-in') . '</b>';
}

function extSoapOnPhoneConnect($param_string=null, $param_array=null, $lmb=null) {
    $decoded = json_decode($param_string, 1);
    if($decoded && $decoded['incoming']) {
        return getCredentials($param_string, 'lmb-sign-in');
    } else {
        return getCredentials($param_string, 'lmb-sign-out');
    }
}

function extSoapOnPhoneDisconnect($param_string=null, $param_array=null, $lmb=null) {
    # if call was connected before
    $decoded = json_decode($param_string, 1);
    if($decoded && $decoded['incoming'] && $decoded['connected']) {
        storePhoneCall($param_string);
        return getCredentials($param_string, 'lmb-sign-in');
    } else if($decoded) {
        return getCredentials($param_string, 'lmb-sign-out');
    } else {
        return 'Fehler 1';
    }
}

function getCredentials($param_string, $imgClass) {
    # decode input
    $decoded = json_decode($param_string, 1);
    if(!$decoded) { return 'Fehler 2'; }
    
    # get phone nr
    $phoneNr = $decoded['phoneNr'];
    
    # ensure data is stored in session
    if(!$_SESSION['callmonitor']) { $_SESSION['callmonitor'] = array(); }
    if(!$_SESSION['callmonitor']['nr'.strval($phoneNr)]) {
        # store customer data in session
        $_SESSION['callmonitor']['nr'.strval($phoneNr)] = getCustomerDataByPhoneNr($phoneNr);
    }
    
    # get date
    if($decoded['startDate'] != null) {
        # extract start time
        $dateObj = DateTime::createFromFormat('d.m.y H:i:s', $decoded['startDate']);
        $toolTip = $dateObj->format('H:i:s');

        # add end time
        if($decoded['lengthSec'] != null) {
            $dateObj->modify("+{$decoded['lengthSec']} seconds");
            $toolTip .= ' - ' . $dateObj->format('H:i:s');
        }
    } else {
        $toolTip = 'Anruf';
    }
        
    # return customer data to callmonitor
    $customerData = $_SESSION['callmonitor']['nr'.strval($phoneNr)];
    if($customerData) {
        ob_start();
        pop_menu2("{$customerData['contactName']} ($phoneNr)", "$toolTip", null, $imgClass, null, "parent.main.location.href='main.php?action=gtab_change&gtabid=4&ID={$customerData['customerId']}';");
        return ob_get_clean();
    } else {
        ob_start();
        pop_menu2("$phoneNr", "$toolTip", null, $imgClass, null, null);
        return ob_get_clean();
    }
}

# adds call with information given as params into table 'telefonate' and links call to customer
function storePhoneCall($param_string=null) {
    global $session;
    
    # check incoming data
    if(!$param_string
            || !($data = json_decode($param_string, true))
            || !array_key_exists('startDate', $data)
            || !array_key_exists('lengthSec', $data)
            || !array_key_exists('phoneNr', $data)
            || !array_key_exists('incoming', $data)
            || !array_key_exists('callerPhone', $data)) {
        error_log("Received invalid data in function extSoapOnPhoneCallEnded: {$param_string}");
        return false;
    }
    
    # Parse date
    $dateTimeObj = DateTime::createFromFormat('d.m.y H:i:s', $data['startDate']);    
    $dateTime = convert_stamp($dateTimeObj->getTimestamp());
    if(!$dateTime){
        error_log("Could not convert date from {$data['startDate']}!");
        return false;
    }
    
    # Get customer from session
    $dat = $_SESSION['callmonitor']['nr'.strval($data['phoneNr'])];
    $contactId = ($dat && array_key_exists('contactId', $dat)) ? $dat['contactId'] : null;
    $customerId = ($dat && array_key_exists('customerId', $dat)) ? $dat['customerId'] : null;

    # Insert phone call
    $callId = lmb_insertPhoneCall(
        $dateTime,
        $data['lengthSec'],
        $data['phoneNr'],
        $data['callerPhone'],
        $data['incoming'],
        $contactId
    );
    
    # Link phone call and customer
    if($callId && $customerId ){
        lmb_connectCallAndCustomer($callId, $customerId);
    }
}

function lmb_insertPhoneCall($beginDate, $duration, $phoneNr, $callerPhone, $incoming, $contactId) {
    global $db;
    
    $id = next_db_id("telefonate");
    
    $sqlquery = "
        insert into telefonate (id, editdatum, edituser, starttime, dauer, telefonnr, anschluss, eingehend, kontaktid)
        values ($id, ".LMB_DBDEF_TIMESTAMP.", 1, '$beginDate', ".parse_db_int($duration,6).", '".parse_db_string($phoneNr,30)."','".parse_db_string($callerPhone,30)."',
        ".parse_db_bool($incoming).", ".parse_db_int($contactId,10).")";

    $rs = odbc_exec($db,$sqlquery) or errorhandle(odbc_errormsg($db),$sqlquery,$action,__FILE__,__LINE__);

    return $id;
}

function lmb_connectCallAndCustomer($callId, $customerId) {
    global $db;
    
    $nextid = next_db_id('VERK_4d8b57b47a927', 'keyid', 1);
    
    $sqlquery = "
        insert into VERK_4d8b57b47a927 (keyid, erstdatum, erstuser, id, verkn_id)
        values ($nextid, ".LMB_DBDEF_TIMESTAMP.", 1, ".parse_db_int($customerId).", ".parse_db_int($callId).")";

    $rs = odbc_exec($db,$sqlquery) or errorhandle(odbc_errormsg($db),$sqlquery,$action,__FILE__,__LINE__);
}

function getCustomerDataByPhoneNr($phoneNr) {
    global $db;
    
    # needs this postgres function
    # 1) + -> 00
    # 2) 0 -> 0049 außer 0049 -> 0049
    # 3) replace non-digits with 
    # 4) remove whitespaces
    /*
CREATE OR REPLACE FUNCTION lmb_abstractPhoneNr(original varchar) RETURNS varchar AS $$
DECLARE
    rval varchar;
BEGIN
rval = original;

IF rval LIKE '+%' THEN
    rval = '00' || trim(leading '+' from rval);
END IF;

IF rval LIKE '0%' AND rval NOT LIKE '00%' THEN
    rval = '0049' || trim(leading '0' from rval);
END IF;
rval = regexp_replace(rval, '[\D]', , 'g');
return regexp_replace(rval, ' ', , 'g');

END; $$  LANGUAGE 'plpgsql';
    */
    
    $phoneNr = parse_db_string($phoneNr);
     
    // first: try to get customer-contact combination
    $sqlquery = "        
        SELECT
            Kunden.id as kundenid,
            Kontakte.id as kontaktid,
            Kontakte.vorname || ' ' || Kontakte.name as kontaktname
        FROM
            Kunden
            JOIN VERK_17e64eb8914c6 as VERK on Kunden.id = VERK.id
            JOIN Kontakte on VERK.verkn_id = Kontakte.id
        WHERE
            lmb_abstractPhoneNr(Kontakte.tel_nr_g) = lmb_abstractPhoneNr('$phoneNr')
        OR  lmb_abstractPhoneNr(Kontakte.mobiltelefon_nr_) = lmb_abstractPhoneNr('$phoneNr')
        LIMIT 1";
    $rs = odbc_exec($db,$sqlquery) or errorhandle(odbc_errormsg($db),$sqlquery,$action,__FILE__,__LINE__);

    if($rs && odbc_fetch_row($rs)){
        $customerId = odbc_result($rs,"kundenid");
        $contactId = odbc_result($rs, "kontaktid");
        $contactName = odbc_result($rs, "kontaktname");
        
        return array(
            'customerId' => $customerId,
            'contactId' => $contactId,
            'contactName' => $contactName
        );
    }
    
    // if first failed: try to get customer
    $sqlquery = "        
        SELECT
            Kunden.id as kundenid,
            Kunden.name as kundenname
        FROM
            Kunden
        WHERE
            lmb_abstractPhoneNr(Kunden.TEL) = lmb_abstractPhoneNr('$phoneNr')
        OR  lmb_abstractPhoneNr(Kunden.MOBIL) = lmb_abstractPhoneNr('$phoneNr')
        LIMIT 1";

    $rs = odbc_exec($db,$sqlquery) or errorhandle(odbc_errormsg($db),$sqlquery,$action,__FILE__,__LINE__);
    
    if($rs && odbc_fetch_row($rs)){
        $customerId = odbc_result($rs,"kundenid");
        $customerName = odbc_result($rs, "kundenname");
        
        return array(
            'customerId' => $customerId,
            'contactName' => $customerName
        );
    }
    
    return null;
}

?>