[asterisk-users] Re: Rewriting caller ID from database?

[David Cook (Canada)]

Vincent Delporte wrote:
> Hi
>
> Most of our customers have generic names like "Hospital", so I need
to
> rewrite their caller ID name by looking up the number in a database
on
> the Asterisk server, and rewriting the name such as "Reading
Hospital"
> so that we know who's calling.
>
> Any idea if this can be done with Asterisk, and how to do it?

Little C and AGI to the rescue (uses MySQL too). DB schema in the code
comments at the top.

dbc.

extensions.conf:
;; Advantech primary context (Sangoma A200D ports 1&2);;;;;;;;;;;;;;;
;; Primary telco number (905-xxx-xxxx)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[advan-primary]
exten => s,1,NoOp(Primary line - ${CALLERID})   ; write log entry
exten => s,n,agi,clid_override|${CALLERID(NUM)} ; CLID agi override
exten => s,n,Goto(cook-main-menu,s,1)           ; Jump to main menu
exten => s,n,Hangup                             ; end/fallthrough

clid_override.c:
/* clid_override.c
 * (c) Advantech Systems Integration, 2006
 * Author: David B. Cook, <davidc at advan.ca>, 905/xxx-xxxx
 * Initial Delivery: Version 1.0, March 1, 2006
 *
 * Application to set the CLID NAME field from a local database
 * when the field comes in empty from the carrier.
 *
 * Meant to be called from Asterisk as an AGI lookup
 * Connects to MySQL database : CLID_NAME
 *
 * Database definition
 * # Host: localhost
 * # Database: asterisk
 * # Table: 'CLID_NAME'
 * #
 * CREATE TABLE `CLID_NAME` (
 *  `CLID_NUM` varchar,
 *  `CLID_NAME` varchar,
 *  PRIMARY KEY  (`CLID_NUM`)
 * ) TYPE=InnoDB; * CLID_NAME
 *
 * Modification History:
 * XXX 00,00  dbc       - Example modification history format
 */

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include <string.h>

#if !defined(MYSQL_VERSION_ID)||MYSQL_VERSION_ID<32224
#define mysql_field_count mysql_num_fields
#endif

#define SELECT1_QUERY "select CLID_NAME from CLID_NAME where
CLID_NUM='%s'"

int main(int argc, char **argv)
{
  MYSQL mysql,*sock;
  MYSQL_RES *res;
  MYSQL_ROW row;
  char *DBhost="put hostname here";
  char *DBuser="put MySQL username here";
  char *DBpw="put MySQL password here";
  char *DBdb="put MySQL database name here";
  char  qbuf[512];
  int   i=0;
  char  line[80];

  /* use line buffering */
  setlinebuf(stdout);
  setlinebuf(stderr);

  /* read and ignore AGI environment */
  while (1) {
    fgets(line,80,stdin);
    if (strlen(line) <= 1) break;
  }

  sprintf(qbuf,SELECT1_QUERY, argv[1]);
  /* debug: show query formulation */
  /* printf("SQL: %s\n", qbuf); */

  /* Initialize and connect to the server */
  mysql_init(&mysql);
  if (!(sock =
mysql_real_connect(&mysql,DBhost,DBuser,DBpw,DBdb,0,NULL,0)))
  {
    fprintf(stderr,"Couldn't connect to
engine!\n%s\n\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }

  /* Perform query to determine if a row exists in the database for the
   * CLID in question.
   */
  if(mysql_query(sock,qbuf))
  {
    fprintf(stderr,"Query 1 failed (%s)\n",mysql_error(sock));
    exit(1);
  }

  /* No results - fatal error */
  if (!(res=mysql_store_result(sock)))
  {
    fprintf(stderr,"Couldn't get result from query failed\n",
    mysql_error(sock));
    exit(1);
  }

  if(mysql_num_rows(res)>=1) {
    /* CLID is PK so should only be 1 row, but I'm going to        */
    /* say >= just so it won't break if no PK and multiple hits.   */
    /* If so, will just re-set CLID again but won't break Asterisk */
    while(row=mysql_fetch_row(res)) {

      printf( "Set VARIABLE CALLERID(name) \"%s\" \n", row[0]);

      /* send the output back to Asterisk */
      fgets(line,80,stdin);
      fputs(line,stderr);
    }
   }
  /* Clean up memory tables/free resources */
  mysql_free_result(res);

  /* Terminate the database connection */
  mysql_close(sock);
  exit(0);
  return 0;                           /* Keep some compilers happy */
}