Reconfiguration de script « Routage avec ACD » pour une autre destination

Oussama Hammami, 2009-12-02

Switzernet

Introduction

On décrit ici la reconfiguration et l’installation de script « Routage avec ACD » pour une destination autre que l’Armenie – Yerevan (+37410) dans un serveur Kamailio déjà installé.

Pour router dynamiquement les appels vers le vendeur qui offre une meilleure qualité on a ajouté un nouveau serveur Kamailio qui prend en charge le routage suivant les valeurs d’ACD calculées durant le dernier intervalle.

En effet le serveur de facturation (Billing) continue toujours de faire le routage selon les préférences prédéfinies mais au lieu d’envoyer les appels vers les vrais vendeurs on les envoie vers l’interface réseaux qui leurs correspond dans le serveur Kamailio et ce serveur va décider s’il passe l’appel à ce vendeur ou non en tenant compte toujours de la qualité offerte par ce dernier pendant l’intervalle précédent.

Ce routage résout le problème des faux signaux ou réponses (protocole de signalisation) envoyées par les vendeurs (fournisseurs pour les appels entrants/sortants).

Généralement lorsqu’un vendeur tombe en panne il répond par un message vocal d’erreur mais il n’envoie pas le signal d’erreur de type 4xx ,5xx où 6xx (voir Fig.1), l’appel sera facturé et considéré comme appel réussi, la seul différence avec un vrai appel c’est que sa durée n’est pas longue (quelque secondes après l’utilisateur raccroche) ce qui fait chuter forcément la valeur de L’ACD.

En somme les fournisseurs ne répondent pas proprement au niveau de signalisation aux appels qu’ils ont reçus bien qu’ils facturent l’appel et le considère comme réussi alors on a décidé d’envoyer les appels à un deuxième serveur (Kamailio) pour les router, ou non, vers le vendeur qui vient de recevoir l’appel sur l’interface réseaux de Kamailio qui lui correspond suivant un pourcentage de rejet calculé à base des valeurs d’ACD et de la valeur de préférence du vendeur prédéfinie.

Fig.1 Problème de Signalisation.

Pré-requis

Cette solution a été développée sur un serveur Debian GNU/Linux 4.0

Kamailio 1.5.1, SQL 5 et PHP5 doivent être installées.

SQL

Structure de la base de données “acd_armenia”

Cette base contienne la table « acd_vendors » dont la structure est la suivante ;

mysql> describe acd_vendors;

+--------------+------------------+------+-----+---------+----------------+

+--------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| Tot_Min | varchar(11) | YES | | 0 | |

| Nb_Ap | varchar(11) | YES | | 0 | |

| Nb_Conv | varchar(11) | YES | | 0 | |

| Nb_5 | varchar(11) | YES | | 0 | |

| Nb_30 | varchar(11) | YES | | 0 | |

| Nb_0 | varchar(11) | YES | | 0 | |

| Nb_reject | varchar(11) | YES | | 0 | |

| ACD | varchar(5) | YES | | NULL | |

| total_calls | varchar(11) | YES | | 0 | |

| i_rate | varchar(10) | YES | | 0 | |

+--------------+------------------+------+-----+---------+----------------+

i_vendor: ID Vendeur.

Pref_billing : préférence de vendeur dans le serveur de facturation.

Tot_Min : le totale de Minute de communication dans cet intervalle.

Nb_Ap : Nombre totale des appels.

Nb_Conv : Nombre totale des appels dont la durée est supérieure à 30 secondes.

Nb_5 : Nombre totale des appels dont la durée est inferieur à 5 secondes.

Nb_30 : Nombre totale des appels dont la durée est de 5 à 30 secondes.

Nb_0 : Nombre totale des appels échoués.

Date: est la date dont ACD est calculée (date d’exécution de Script PHP lancée par le CRON).

ACD : la valeur de l’ACD pour l’intervalle précédent.

Reject : c’est pourcentage de rejet pour ce vendeur.

Total_calls : Nombre des appels traités par notre serveur Kamailio pendent l’intervalle courant, ce champ sera mis à jours par Kamailio.

Prefix : c’est le préfixe de la destination concernée par ce routage.

I_rate : ID du tarif dans le serveur de facturation (Billing).

Création de la table “acd_vendors”

CREATE DATABASE acd_armenia DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE acd_armenia;

CREATE TABLE acd_vendors (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

i_vendor varchar(11) DEFAULT NULL,

pref_billing varchar(2) DEFAULT NULL,

Tot_Min varchar(11) DEFAULT '0',

Nb_Ap varchar(11) DEFAULT '0',

Nb_Conv varchar(11) DEFAULT '0',

Nb_5 varchar(11) DEFAULT '0',

Nb_30 varchar(11) DEFAULT '0',

Nb_0 varchar(11) DEFAULT '0',

Nb_reject varchar(11) DEFAULT '0',

`date` datetime DEFAULT NULL,

ACD varchar(5) DEFAULT NULL,

reject varchar(11) DEFAULT NULL,

total_calls varchar(11) DEFAULT '0',

prefix varchar(11) DEFAULT NULL,

i_rate varchar(10) DEFAULT '0',

PRIMARY KEY (id)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Modification du Fichier de configuration ‘Kamailio.cfg’

# Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

# PSE-A, CH-1015, Lausanne Suisse

# oussama.hammam@switzernet.com

# christian.lathion@switzernet.com

# emin.gabrielyan@switzernet.com

debug=3

log_stderror=no

log_facility=LOG_LOCAL0

log_name="ser"

fork=yes

children=4

/* uncomment the following lines to enable debugging */

#debug=6

#fork=no

#log_stderror=yes

check_via=no

dns=no

rev_dns=no

disable_dns_blacklist=yes

auto_aliases=no

disable_tcp=yes

server_signature=yes

sip_warning=1

listen=xxx.xxx.xxx.xxx # INA

listen=xxx.xxx.xxx.xxx # Vendeur 1

listen=xxx.xxx.xxx.xxx # Vendeur 2

####### Modules Section ########

mpath="/usr/lib/kamailio/modules/"

loadmodule "sl.so"

loadmodule "tm.so"

loadmodule "rr.so"

loadmodule "pv.so"

loadmodule "siputils.so"

loadmodule "maxfwd.so"

loadmodule "textops.so"

loadmodule "mi_fifo.so"

loadmodule "xlog.so"

loadmodule "sqlops.so"

loadmodule "db_mysql.so"

# ACD Routing

loadmodule "cfgutils.so"

# ----------------- setting module-specific parameters ---------------

modparam("mi_fifo", "fifo_name", "/tmp/kamailio_fifo")

modparam("rr", "enable_full_lr", 1)

modparam("rr", "append_fromtag", 0)

modparam("sqlops","sqlcon","routing_ina=>mysql://username:password@127.0.0.1/ina")

modparam("sqlops","sqlcon","routing_acd=>mysql://username:password@127.0.0.1/acd_armenia")

####### Routing Logic ########

route{

if ($si!="xxx.xxx.xxx.xxx" and $si!="xxx.xxx.xxx.xxx" and $si!="xxx.xxx.xxx.xxx") # Liste serveurs SIP

{

xlog("L_INFO","$Ts ==> Unallowed access for $si\n");

sl_send_reply("403","Forbidden here xx");

exit;

}

if (!mf_process_maxfwd_header("10")) {

sl_send_reply("483","Too Many Hops");

exit;

}

#TODO what is has_totag

if (has_totag())

{

if (loose_route())

{

if(method=="INVITE")

{

if(t_newtran())

{

t_reply("100","Your Re-INVITE is received");

t_reply("488","Your Re-INVITE is ignored");

exit;

}

else

{

xlog("L_INFO","$Ts ==> Newtran error $oU\n");

sl_send_reply("100","Your Re-INVITE is received (SL)");

sl_send_reply("488","Your Re-INVITE is ignored (SL)");

exit;

}

t_relay();

exit;

}

else

{

if ( is_method("ACK") )

{

if ( t_check_trans() )

{

# non loose-route, but stateful ACK; must be an ACK after a 487 or e.g. 404 from upstream server

t_relay();

exit;

}

else

{

# ACK without matching transaction ... ignore and discard.\n");

exit;

}

# sl_send_reply("404","Not here");

}

exit;

}

if(is_method("INVITE"))

{

record_route();

#418XX numbers

if($tU=~"^418(00|40|42|44|48)")

{

xlog("INA: processing call to $tU\n");

sql_query("routing_ina", "select right(l.ina_number,9),l.tsp_id,s.description from lig l,status s where l.ina_status=s.ina_status and l.ina_number=concat(\"0\",right($tU,9)) and s.description in (\"SRVIN\",\"SRVTO\",\"ALLOC\",\"ALLNO\",\"ALLEX\") order by concat(l.trans_date,l.trans_time) desc limit 1","result_ina");

if ($dbr(result_ina=>rows)>0)

{

xlog("$Ts ==> INA: $dbr(result_ina=>colname[0]) -> $dbr(result_ina=>[0,0])\n");

xlog("$Ts ==> INA: $dbr(result_ina=>colname[1]) -> $dbr(result_ina=>[0,1])\n");

xlog("$Ts ==> INA: $dbr(result_ina=>colname[2]) -> $dbr(result_ina=>[0,2])\n");

$var(ina_number)=$dbr(result_ina=>[0,0]);

$var(tsp_id)=$dbr(result_ina=>[0,1]);

$var(ina_status)=$dbr(result_ina=>[0,2]);

sql_result_free("result_ina");

xlog("$Ts ==> INA: ina_number: $var(ina_number)\n");

xlog("$Ts ==> INA: tsp_id: $var(tsp_id)\n");

xlog("$Ts ==> INA: ina_status: $var(ina_status)\n");

switch($var(ina_status))

{

case "SRVIN":

$var(prefix)=$var(tsp_id)+"10335";

xlog("$Ts ==> INA: prefix -> $var(prefix)\n");

subst_uri("/^sip:41/sip:$var(prefix)/i");

prefix("83");

rewritehost("217.168.45.3");

t_relay();

break;

case "SRVTO":

sl_send_reply("480","INA SRVTO");

break;

case "ALLOC":

sl_send_reply("480","INA ALLOC");

break;

case "ALLNO":

sl_send_reply("404","INA ALLNO");

break;

case "ALLEX":

sl_send_reply("404","INA ALLEX");

break;

}

exit;

}

else {

xlog("$Ts ==> INA: $tU not found!\n");

sl_send_reply("404","INA not found");

exit;

}

###################### ACD Routing ######################

if ($tU=~"^(\+|00)?(XXXX)")

{

$var(Ri_V)="xxx.xxx.xxx.xxx";#Vendeur 1

$var(Ri_C)="xxx.xxx.xxx.xxx";#Vendeur 2

sql_query("routing_acd", "select ACD, reject, Nb_reject, total_calls from acd_vendors where i_vendor=X1 and prefix=XXXX order by date desc limit 1;","result_Verizon");

sql_query("routing_acd", "select ACD, reject, Nb_reject, total_calls from acd_vendors where i_vendor=X2 and prefix=XXXX order by date desc limit 1;","result_Colt");

if ($dbr(result_Verizon=>rows) && $dbr(result_Colt=>rows))

{

$var(Verizon)="ACD="+$dbr(result_Verizon=>[0,0])+";Rej="+$dbr(result_Verizon=>[0,1])+";NbRej="+$dbr(result_Verizon=>[0,2])+";TOT_C="+$dbr(result_Verizon=>[0,3]);

$var(Colt)="ACD="+$dbr(result_Colt=>[0,0])+";Rej="+$dbr(result_Colt=>[0,1])+";NbRej="+$dbr(result_Colt=>[0,2])+";TOT_C="+$dbr(result_Colt=>[0,3]);

sql_result_free("result_Verizon");

sql_result_free("result_Colt");

if ($Ri==$var(Ri_V))

{

$var(Target_Load)= 100 - $(var(Verizon){param.value,Rej}{s.int}) ;

}

else

{

$var(Target_Load)= 100 - $(var(Colt){param.value,Rej}{s.int}) ;

}

else

{

xlog("=> ACD $tU: DB connection failed (Select query) \n");

}

$var(TOT_Calls) = $(var(Verizon){param.value,TOT_C}{s.int}) + $(var(Colt){param.value,TOT_C}{s.int});

$var(TOT_Reject) = $(var(Verizon){param.value,NbRej}{s.int}) + $(var(Colt){param.value,NbRej}{s.int}) ;

$var(C1) = $var(TOT_Calls) - $var(TOT_Reject) ;

$var(C1) = $var(C1) * 100;

$var(C2) = $var(Target_Load) * $var(TOT_Calls) ;

xlog("=> ACD $tU: $rm IP_Reception = $Ri \n");

xlog("=> ACD $tU: ACD_Colt = $(var(Colt){param.value,ACD}) | ACD_Verizon = $(var(Verizon){param.value,ACD}) \n");

xlog("=> ACD $tU: Reject_Colt = $(var(Colt){param.value,Rej}) | Reject_Verizon = $(var(Verizon){param.value,Rej})\n");

xlog("=> ACD $tU: Total_Calls_Colt = $(var(Colt){param.value,TOT_C}) | Total_Calls_Verizon = $(var(Verizon){param.value,TOT_C})\n");

xlog("=> ACD $tU: Target_Load = $var(Target_Load) \n");

xlog("=> ACD $tU: TOT_Calls = $var(TOT_Calls) | TOT_Reject = $var(TOT_Reject) \n");

if ( $var(C1) <= $var(C2) )

{

xlog("=> ACD $tU: Call Accepted : $var(C1) < $var(C2)\n\n\n\n");

if ($Ri==$var(Ri_C))

{

rewritehost("xxx.xxx.xxx.xxx");

sql_query("routing_acd","update acd_vendors set total_calls = total_calls + 1 where i_vendor=X2 and prefix =XXXX order by date desc limit 1;","result_upd");

}

else

{

rand_set_prob("50");

if (rand_event())

rewritehost("xxx.xxx.xxx.xxx");

else

rewritehost("xxx.xxx.xxx.xxx");

sql_query("routing_acd","update acd_vendors set total_calls = total_calls + 1 where i_vendor=X1 and prefix =XXXX order by date desc limit 1;","result_upd");

}

t_relay();

}

else

{

xlog("=> ACD $tU: Call Rejected : $var(C1) >= $var(C2)\n\n\n\n");

if ($Ri==$var(Ri_V))

{

sql_query("routing_acd","update acd_vendors set Nb_reject = Nb_reject + 1 where i_vendor=X1 and prefix =XXXX order by date desc limit 1;","result_upd");

}

else

{

sql_query("routing_acd","update acd_vendors set Nb_reject = Nb_reject + 1 where i_vendor=X2 and prefix =XXXX order by date desc limit 1;","result_upd");

Changer le ‘i_vendor’ et ‘prefix’ par l’ID de vendeur dans le serveur de facturation (Billing) et par le préfixe de la nouvelle destination.
X1: ID Vendeur 1
X2: ID Vendeur 2

}

sl_send_reply("480","Call Rejected");

}

sql_result_free("result_upd");

exit;

}

###################### END Script ACD ######################

# Forbidden destinations

if($tU=~"^4190")

{

xlog("L_INFO","$Ts ==> Forbidden $tU\n");

sl_send_reply("403","Forbidden");

exit;

}

# Unassigned

else

{

xlog("L_INFO","$Ts ==> Unassigned Number $tU\n");

sl_send_reply("404","Unassigned Number");

exit;

}

xlog("=> ACD $tU: Unmatched Packet $rm IP, Source address $si, From URI $fu, To URI $tu\n");

}

t_check_trans();

route(1);

xlog("=> ACD $tU: Unmatched Packet $rm IP, Source address $si, From URI $fu, To URI $tu\n");

}

route[1]

{

# for INVITEs enable some additional helper routes

if (is_method("INVITE")) {

t_on_failure("1");

}

if (!t_relay()) {

sl_reply_error();

};

exit;

}

Modification du Script PHP

Ce script doit être uploadé sur le serveur Kamailio.

A chaque exécution lancé par le Cron de serveur ce dernier ajoute une ligne dans la table ‘acd_vendors’ si l’intervalle est supérieur à 20 minutes et les nombre d’appels est aussi supérieur à 20, uploade les statistiques (page HTML) vers le serveur Web via FTP et envoi un email si le ‘i_rate’ dans le serveur de facturation ne correspond pas au ‘i_rate’ stocké dans notre base local.

acd-rate.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

Il faux changer tous les apparitions des ‘55’ et ‘62’ par l’ID des vos vendeurs dans le serveur de facturation.
Dans cet exemple
‘55’ : Présente l’ID du vendeur 1
‘62’ : Présente l’ID du vendeur 2.

$prefix =XXXX;

$d2=date("Y-m-d H:i:s");

include "connectionlocal.php";

$query0="select acd.date FROM acd_vendors acd where acd.prefix=$prefix order by acd.date desc limit 1;";

$result0=mysql_query ($query0) or die (mysql_error()) ;

$row0 = mysql_fetch_row($result0);

$date_ch =$row0[0];

$query0="select acd.i_vendor, acd.i_rate FROM acd_vendors acd where acd.prefix=$prefix order by acd.date desc limit 2;";

$result0=mysql_query ($query0) or die (mysql_error()) ;

while($row0 = mysql_fetch_row($result0)) {

$Pred_rate[$row0[0]] = $row0[1];

}

mysql_close($db);

sscanf($date_ch, "%d-%d-%d %d:%d:%d", $y1,$m1,$j1,$h1,$mn1,$s1);

sscanf($d2, "%d-%d-%d %d:%d:%d", $y2,$m2,$j2,$h2,$mn2,$s2);

$date1 = mktime($h1,$mn1,$s1,$m1,$j1,$y1);

$date2 = mktime($h2,$mn2,$s2,$m2,$j2,$y2);

$diff_date =round(($date2-$date1)/60,"0");

if ($diff_date >= 20 ) // The minimal duration of the interval must be of 20 minutes

$INTV=$diff_date;

else

{

echo "Operation annulée ;($diff_date) intervalle < 20 !\n";

exit();

}

//######################################################

$pref[55] = 0;

$pref[62] = 0;

$load_min = 0.1;

include "connection.php";

// ACD Routing Vendeur 1 = T1

$query01="select r.preference, r.i_rate from Destinations d, Rates r where d.i_dest=r.i_dest and r.i_tariff=T1 and d.destination=$prefix order by r.effective_from desc limit 1;";

// ACD Routing Vendeur 2 = T2

$query02="select r.preference, r.i_rate from Destinations d, Rates r where d.i_dest=r.i_dest and r.i_tariff=T2 and d.destination=$prefix order by r.effective_from desc limit 1;";

$result01=mysql_query ($query01) or die (mysql_error()) ;

$row01 = mysql_fetch_row($result01);

$result02=mysql_query ($query02) or die (mysql_error()) ;

$row02 = mysql_fetch_row($result02);

$pref[55] =$row01[0];

$pref[62] =$row02[0];

//Vendeur 1 = TV1

$query03="select MAX(r.i_rate) from Destinations d, Rates r where d.i_dest=r.i_dest and r.i_tariff=TV1 and d.destination=$prefix;";

//Vendeur 2 = TV2

$query04="select MAX(r.i_rate) from Destinations d, Rates r where d.i_dest=r.i_dest and r.i_tariff=TV2 and d.destination=$prefix;";

$result03=mysql_query ($query03) or die (mysql_error()) ;

$row03 = mysql_fetch_row($result03);

$result04=mysql_query ($query04) or die (mysql_error()) ;

$row04 = mysql_fetch_row($result04);

$Now_rate[55] =$row03[0];

$Now_rate[62] =$row04[0];

echo "Minute=".$INTV."\n";

//totale duration & nombre appel

$query1="select SUM(cdr.duration),COUNT(cdr.id),cdr.i_vendor from CDR_Vendors cdr, Destinations des where (cdr.i_vendor=55 or cdr.i_vendor=62) and cdr.disconnect_time>=date_sub(now(),interval $INTV minute) and cdr.i_dest=des.i_dest and des.destination=$prefix and cdr.i_dest not in (6,7,8,463540,463541,463542,463657,463658,463659) GROUP BY cdr.i_vendor;";

// 0 < duration <= 5

$query2="select COUNT(cdr.id),cdr.i_vendor from CDR_Vendors cdr, Destinations des where (cdr.i_vendor=55 or cdr.i_vendor=62) and cdr.disconnect_time>=date_sub(now(),interval $INTV minute) and cdr.i_dest=des.i_dest and des.destination=$prefix and cdr.duration < 6 and cdr.duration > 0 and cdr.i_dest not in (6,7,8,463540,463541,463542,463657,463658,463659) GROUP BY cdr.i_vendor;";

// 5 < duration <= 30

$query3="select COUNT(cdr.id),cdr.i_vendor from CDR_Vendors cdr, Destinations des where (cdr.i_vendor=55 or cdr.i_vendor=62) and cdr.disconnect_time>=date_sub(now(),interval $INTV minute) and cdr.i_dest=des.i_dest and des.destination=$prefix and cdr.duration < 31 and cdr.duration > 5 and cdr.i_dest not in (6,7,8,463540,463541,463542,463657,463658,463659) GROUP BY cdr.i_vendor;";

//nombre d'appels de 0 secondes

$query4="select COUNT(cdr.id),cdr.i_vendor from CDR_Vendors_Failed cdr, Destinations des where (cdr.i_vendor=55 or cdr.i_vendor=62) and cdr.connect_time>=date_sub(now(),interval $INTV minute) and cdr.i_dest=des.i_dest and des.destination=$prefix and cdr.i_dest not in (6,7,8,463540,463541,463542,463657,463658,463659) GROUP BY cdr.i_vendor;";

// 30 < duration

$query5="select COUNT(cdr.id),cdr.i_vendor from CDR_Vendors cdr, Destinations des where (cdr.i_vendor=55 or cdr.i_vendor=62) and cdr.disconnect_time>=date_sub(now(),interval $INTV minute) and cdr.i_dest=des.i_dest and des.destination=$prefix and cdr.duration > 30 and cdr.i_dest not in (6,7,8,463540,463541,463542,463657,463658,463659) GROUP BY cdr.i_vendor;";

$tot_D[55]=$tot_D[62]=0;

$Nb_Ap[55]=$Nb_5[55]=$Nb_30[55]=$Nb_0[55]=$Nb_Conv[55]=0;

$Nb_Ap[62]=$Nb_5[62]=$Nb_30[62]=$Nb_0[62]=$Nb_Conv[62]=0;

$ACD[55]=0;

$ACD[62]=0;

$reject[55]=0;

$reject[62]=0;

$load[55]=0;

$load[62]=0;

$rank[55]=0;

$rank[62]=0;

$query[55]=$query[62]="";

$result1=mysql_query ($query1) or die (mysql_error()) ;

while($row1 = mysql_fetch_row($result1)) {

$tot_D[$row1[2]] = round($row1[0]/60,"2");

$Nb_Ap[$row1[2]] = $row1[1];

$ACD[$row1[2]] = round($tot_D[$row1[2]] / $row1[1], "2");

}

$TOT_Nb_Ap = $Nb_Ap[55] + $Nb_Ap[62];

if ($TOT_Nb_Ap < 20)

{

echo "Operation annulée ;($TOT_Nb_Ap) Nombre d'appels < 20 !\n";

mysql_close($db);

exit();

}

if ($ACD[55] && $ACD[62])

{

if ($ACD[55] >= $ACD[62])

{

$rank[55] = 1;

$rank[62] = $ACD[62]/$ACD[55];

$load[62] = $load_min +((0.5-$load_min)*$rank[62]);

$load[55] = 1 - $load[62] ;

}

else

{

$rank[62] = 1;

$rank[55] = $ACD[55]/$ACD[62];

$load[55] = $load_min +((0.5-$load_min)*$rank[55]);

$load[62] = 1 - $load[55] ;

}

if ($pref[55]>$pref[62])

{

$reject[55] = round($load[62]*100, "0");

$reject[62] = 0;

}

else

{

$reject[62] = round($load[55]*100, "2");

$reject[55] = 0;

}

else

{

if ($pref[55]>$pref[62])

{

$reject[62] = 0;

$reject[55] = 50;

}

else

{

$reject[62] = 50;

$reject[55] = 0;

}

$result2=mysql_query ($query2) or die (mysql_error()) ;

while($row2 = mysql_fetch_row($result2)) {

$Nb_5[$row2[1]] = $row2[0];

}

$result3=mysql_query ($query3) or die (mysql_error()) ;

while($row3 = mysql_fetch_row($result3)) {

$Nb_30[$row3[1]] = $row3[0];

}

$result4=mysql_query ($query4) or die (mysql_error()) ;

while($row4 = mysql_fetch_row($result4)) {

$Nb_0[$row4[1]] = $row4[0];

}

$result5=mysql_query ($query5) or die (mysql_error()) ;

while($row5 = mysql_fetch_row($result5)) {

$Nb_Conv[$row5[1]] = $row5[0];

}

mysql_close($db);

//######################################################

$Nb_Ap[55] += $Nb_0[55];

$Nb_Ap[62] += $Nb_0[62];

//2009-10-04 15:00:00

echo "Date = ".$d2."\n";

echo "Prefix = ".$prefix."\n";

echo "Pref[55] = ".$pref[55]."\n";

echo "Pref[62] = ".$pref[62]."\n";

echo "ACD[55] = ".$ACD[55]." Min\n";

echo "ACD[62] = ".$ACD[62]." Min\n";

echo "Reject[55] = ".$reject[55]."%\n";

echo "Reject[62] = ".$reject[62]."%\n";

echo "tot_D[55] = ".$tot_D[55]." Min\n";

echo "tot_D[62] = ".$tot_D[62]." Min\n";

echo "Nb_Ap[55] = ".$Nb_Ap[55]."\n";

echo "Nb_Ap[62] = ".$Nb_Ap[62]."\n";

echo "Nb_5[55] = ".$Nb_5[55]."\n";

echo "Nb_5[62] = ".$Nb_5[62]."\n";

echo "Nb_30[55] = ".$Nb_30[55]."\n";

echo "Nb_30[62] = ".$Nb_30[62]."\n";

echo "Nb_0[55] = ".$Nb_0[55]."\n";

echo "Nb_0[62] = ".$Nb_0[62]."\n";

echo "Nb_Conv[55] = ".$Nb_Conv[55]."\n";

echo "Nb_Conv[62] = ".$Nb_Conv[62]."\n";

echo "Now_rate[55] = ".$Now_rate[55]."\n";

echo "Now_rate[62] = ".$Now_rate[62]."\n";

echo "Pred_rate[55] = ".$Pred_rate[55]."\n";

echo "Pred_rate[62] = ".$Pred_rate[62]."\n";

include "MailRate.php";

foreach ($Pred_rate as $key => $value)

{

if ($Now_rate[$key]!=$Pred_rate[$key])

{

echo "Email-Rate treatment of $key:\n";

Mail_Rate($key,$Pred_rate[$key],$prefix,$pref[$key]);

}

include "connectionlocal.php";

foreach ($ACD as $key => $value)

{

if (!$ACD[$key])

$ACD[$key]= "NULL";

}

$query[55] = "INSERT INTO acd_vendors ( NB_Conv, pref_billing, date ,prefix ,i_vendor ,Tot_Min ,Nb_Ap ,Nb_5 ,Nb_30 ,Nb_0 ,Nb_reject ,ACD ,reject ,i_rate ) VALUES ($Nb_Conv[55], $pref[55], '$d2', '$prefix', '55', '$tot_D[55]', '$Nb_Ap[55]', '$Nb_5[55]', '$Nb_30[55]', '$Nb_0[55]', '0', '$ACD[55]', '$reject[55]', '$Now_rate[55]')";

$query[62] = "INSERT INTO acd_vendors ( NB_Conv, pref_billing, date ,prefix ,i_vendor ,Tot_Min ,Nb_Ap ,Nb_5 ,Nb_30 ,Nb_0 ,Nb_reject ,ACD ,reject ,i_rate ) VALUES ($Nb_Conv[62], $pref[62], '$d2', '$prefix', '62', '$tot_D[62]', '$Nb_Ap[62]', '$Nb_5[62]', '$Nb_30[62]', '$Nb_0[62]', '0', '$ACD[62]', '$reject[62]', '$Now_rate[62]')";

if (mysql_query ($query[55]) or die (mysql_error()))

echo "Insertion Terminee pour 55\n";

if (mysql_query ($query[62]) or die (mysql_error()))

echo "Insertion Terminee pour 62\n";

mysql_close($db);

MailRate.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

function date_passed($EFD)

{

$dateTime_now=date_create();

$DateTimeZone=timezone_open('Europe/Zurich');

date_timezone_set($dateTime_now,$DateTimeZone);

$dateTime_effective=date_create($EFD,timezone_open('Europe/Zurich'));

return ($dateTime_now > $dateTime_effective);

}

function Send_Mail($Vd,$file_name,$dtf)

{

$email="rates@yoursiteweb.com ";

if ($Vd==55)

{

$my_email="ACD_Routing.Vendeur1@yoursiteweb.com ";

$subject="ACD Routing Vendeur1 Upload Rates: ".substr($dtf,2,2).substr($dtf,5,2).substr($dtf,8,2);

$message='<p style="font-family: verdana, arial, helvetica, sans-serif;font-size:13px;">

Tarrif Name:<b> ACD Routing Vendeur1</b>.<br/>

Effective From<font style="font-weight: bold;color:red;font-style: bold;"> '.$dtf.'</font>.</p>

<p style="font-family: verdana, arial, helvetica, sans-serif;font-size:10px;text-align:center;">* * *<BR/>©Switzernet</p>';

}

else if ($Vd==62)

{

$my_email="ACD_Routing.Vendeur2@yoursiteweb.com ";

$subject="ACD Routing Vvendeur2Colt Upload Rates: ".substr($dtf,2,2).substr($dtf,5,2).substr($dtf,8,2);

$message='<p style="font-family: verdana, arial, helvetica, sans-serif;font-size:13px;">

Tarrif Name:<b> ACD Routing Vendeur2</b>.<br/>

Effective From<font style="font-weight: bold;color:red;font-style: bold;"> '.$dtf.'</font>.</p>

<p style="font-family: verdana, arial, helvetica, sans-serif;font-size:10px;text-align:center;">* * *<BR/>©Switzernet</p>';

}

$boundary = "_".md5 (uniqid (rand()));

$attached_file = file_get_contents($file_name);

$attached_file = chunk_split(base64_encode($attached_file));

$attached = "\n\n". "--" .$boundary . "\nContent-Type: application; name=\"$file_name\"\r\nContent-Transfer-Encoding: base64\r\nContent-Disposition: attachment; filename=\"$file_name\"\r\n\n".$attached_file . "--" . $boundary . "--";

$headers ="From: ".$my_email." \r\n";

$headers .= "MIME-Version: 1.0\r\nContent-Type: multipart/mixed; boundary=\"$boundary\"\r\n";

$body = "--". $boundary ."\nContent-Type: text/html; charset=ISO-8859-1\r\n\n".$message . $attached;

if( mail($email,$subject,$body,$headers))

echo "Send To $email \n";

else

{

if (mail($email,$subject,$body,$headers))

echo "Send To $email \n";

else

echo "Email Not Sent \n";

}

function Convert_time_to_CH($DateUS)

{

$dateTime=date_create($DateUS,timezone_open('America/Los_Angeles'));

$DateTimeZone=timezone_open('Europe/Zurich');

date_timezone_set($dateTime,$DateTimeZone);

$DateCH=date_format($dateTime,"Y-m-d H:i:s");

return ($DateCH);

}

function Excel_XML($Tab_XML,$FileName,$Header_Worksheet)

{

if ($fp = fopen($FileName, 'w'))

{

$content='<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<LastAuthor>Switzernet</LastAuthor>

</DocumentProperties>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Font/>

</Style>

</Styles>

fwrite($fp, $content);

fwrite($fp, ' <Table ss:ExpandedColumnCount="'.count($Tab_XML[0]).'" ss:ExpandedRowCount="'.count($Tab_XML).'" x:FullColumns="1" x:FullRows="1">');

for ($i=0;$i<count($Tab_XML);$i++)

{

fwrite($fp, "<Row>");

for ($j=0;$j<count($Tab_XML[$i]);$j++)

{

if ($Tab_XML[$i][$j] && ($i==1 &&(($Header_Worksheet=="PriceList" && $j==13) || ($Header_Worksheet=="Prefix" && $j==9))))

fwrite($fp, '<Cell ss:Formula="=TEXT(DATE('.substr($Tab_XML[$i][$j],0,4).','.substr($Tab_XML[$i][$j],5,2).','.substr($Tab_XML[$i][$j],8,2).'),"yyyy-mm-dd")&" '.substr($Tab_XML[$i][$j],11).'""><Data ss:Type="String">'.$Tab_XML[$i][$j].'</Data></Cell>');

else

fwrite($fp, '<Cell><Data ss:Type="String">'.$Tab_XML[$i][$j].'</Data></Cell>');

}

fwrite($fp, '</Row>');

}

$content='

</Table>

<Panes>

<Pane>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

fwrite($fp, $content);

fclose($fp);

}

else

echo "Erreur ouverture de fichier $FileName";

}

function Mail_Rate($Vd, $Rate, $prefix,$preference)

{

include "connection.php";

if ($Vd==55)

{

$tariff=T1;

$file_name="ACD_Routing_Vendeur1.xml";

$Query="select d.description, r.price_1, r.price_n, r.op_price_1, r.op_price_n, r.effective_from from Destinations d, Rates r where d.i_dest=r.i_dest and r.i_rate>$Rate and r.i_tariff=$tariff and d.destination=$prefix;";

$Result=mysql_query ($Query) or die (mysql_error()) ;

$tabXML[0][0]="Destination";

$tabXML[0][1]="CC";

$tabXML[0][2]="Digits";

$tabXML[0][3]="Effective Date";

$tabXML[0][4]="Flat/Peak";

$tabXML[0][5]="Off-Peak";

$tabXML[0][6]="Previous Flat/Peak";

$tabXML[0][7]="Previous Off-Peak";

$tabXML[0][8]="Change Ind.";

$tabXML[0][9]="Valid";

$tabXML[0][10]="Peak";

$tabXML[0][11]="Off-peak";

$tabXML[0][12]="Preference";

$tabXML[0][13]="Effective from";

$tabXML[0][14]="ACD Routing Veendeur1";

$tabXML[0][15]="CHF";

while($Row = mysql_fetch_row($Result))

{

$Effective_From=Convert_time_to_CH($Row[5]);

$tabXML[1][0]=$Row[0];//A

$tabXML[1][1]="";

$tabXML[1][2]=$prefix;//C

$tabXML[1][3]="";

$tabXML[1][4]="";

$tabXML[1][5]="";

$tabXML[1][6]="";

$tabXML[1][7]="";

$tabXML[1][8]="";

$tabXML[1][9]="";

$tabXML[1][10]=$Row[1];

$tabXML[1][11]=$Row[3];

$tabXML[1][12]=$preference;

if (date_passed($Effective_From))

$tabXML[1][13]="";

else

$tabXML[1][13]=$Effective_From;

$tabXML[1][14]="";

$tabXML[1][15]="";

Excel_XML($tabXML,$file_name,"PriceList");

Send_Mail($Vd,$file_name,substr($Effective_From,0,10));

}

else if ($Vd==62)

{

$tariff=T2;

$file_name="ACD_Routing_Vendeur2.xml";

$Result=mysql_query ($Query) or die (mysql_error()) ;

$tabXML[0][0]="Destination";

$tabXML[0][1]="Prefix";

$tabXML[0][2]="International or National";

$tabXML[0][3]="E164";

$tabXML[0][4]="1st peak";

$tabXML[0][5]="peak";

$tabXML[0][6]="1st off-peak";

$tabXML[0][7]="off-peak";

$tabXML[0][8]="preference";

$tabXML[0][9]="effective from";

$tabXML[0][10]="ACD Routing Veendeur1";

$tabXML[0][11]="CHF";

while($Row = mysql_fetch_row($Result))

{

$Effective_From=Convert_time_to_CH($Row[5]);

$tabXML[1][0]=$Row[0];

$tabXML[1][1]="";

$tabXML[1][2]="";

$tabXML[1][3]=$prefix;

$tabXML[1][4]=$Row[1];//E First Price

$tabXML[1][5]=$Row[2];//F Next Price

$tabXML[1][6]=$Row[3];//G Off-peak First Price

$tabXML[1][7]=$Row[4];//H Off-peak Next Price

$tabXML[1][8]=$preference;

if (date_passed($Effective_From))

$tabXML[1][9]="";

else

$tabXML[1][9]=$Effective_From;

$tabXML[1][10]="";

$tabXML[1][11]="";

Excel_XML($tabXML,$file_name,"Prefix");

Send_Mail($Vd,$file_name,substr($Effective_From,0,10));

}

mysql_close($db);

return true;

}

upload.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

include 'config.inc.php';

$prefix = 37410;

$d2=date("Y-m-d");

$ex=date("Y-m-d H:i:s");

$dateTime = date_create( $ex );

$DateTimeZone = timezone_open ( 'Europe/Zurich' );

date_timezone_set( $dateTime, $DateTimeZone );

$ex = date_format( $dateTime, "Y-m-d H:i:s");

// ########## Debut de Creation Du Fichier tmp.html ##########

$filename = 'tmp.html';

$content = "<html><head><title>Statistique du $prefix : $d2 </title>";

$content .='<META HTTP-EQUIV="Refresh" CONTENT="300"><META NAME="Author" LANG="fr" CONTENT="Oussama Hammami"><META NAME="Publisher" CONTENT="SWITZERNET"><META NAME="Reply-to" CONTENT="oussama.hammami@switzernet.com">

body {

font: 12px "Trebuchet MS", Arial, Helvetica, sans-serif;

}

h3{ color: #2B3856; } .style5 { font-size: 9pt; font-family: Arial, Verdana, Helvetica, Sans-Serif; color: #0000FF; } .style1 { font-size: 9pt; font-weight: bold; font-family: Arial, Verdana, Helvetica, Sans-Serif; } .style2 { font-size: 9pt; color: #0000FF; } .style3 { font-size: 11pt; font-family: Arial, Verdana, Helvetica, Sans-Serif; } .style4 { font-family: Arial, Verdana, Helvetica, Sans-Serif; font-size: 8pt; color: #2B3856; } -->

</style>

var COLR;

function changBgIn(IDV, IDC)

{

var Obj = document.getElementById(IDV);

var nextTR = document.getElementById(IDC);

COLR=Obj.style.backgroundColor;

var CellsPV =Obj.getElementsByTagName("td");

var CellsNX =nextTR.getElementsByTagName("td");

//alert(CellsPV.length+" "+CellsNX.length);

for (var i = 0; i < CellsPV.length-2; i++)

{

CellsPV[i].style.backgroundColor="#FBBBB9";

}

for (var i = 0; i < CellsNX.length; i++)

{

CellsNX[i].style.backgroundColor="#FBBBB9";

}

function changBgOut(IDV, IDC)

{

var Obj = document.getElementById(IDV);

var nextTR = document.getElementById(IDC);

var CellsPV =Obj.getElementsByTagName("td");

var CellsNX =nextTR.getElementsByTagName("td");

for (var i = 0; i < CellsPV.length-2; i++)

{

CellsPV[i].style.backgroundColor=COLR;

}

for (var i = 0; i < CellsNX.length; i++)

{

CellsNX[i].style.backgroundColor=COLR;

}

</SCRIPT>

</head><body><center><strong style=\'font: 20px "Trebuchet MS", Arial, Helvetica, sans-serif;\'>Traffic load balance with quality routing</strong><br/>[Last update '.$ex.']<br/><br/>';

if ($fp = fopen($filename, 'w'))

{

fwrite($fp, $content);

include "connectionlocal.php";

$query="select date ,i_vendor ,Tot_Min ,Nb_Ap ,Nb_5 ,Nb_30 ,Nb_0 ,Nb_reject ,ACD ,reject ,pref_billing ,Nb_Conv,total_calls from acd_vendors where date like '$d2%' and prefix=$prefix order by date desc;";

$result=mysql_query ($query) or die (mysql_error()) ;

$content = '<table border="0" cellpadding="3" cellspacing="3" bgcolor="#FFFFFF" align="center" width="2650" id="my_table">

<tr>

<td class="style5" align="left" bgcolor="#C0C0C0" valign="middle" colspan="7" >Statistics of calls collected during the previous interval on all vendor connections (passing directly or via ACD routing system). Included are the numbers of calls with zero, problematic, short durations, the conversations, total calls and total minutes of calls <u>ended</u> within (and not during) the previous interval.</td>

<td class="style5" align="left" bgcolor="#C0C0C0" valign="middle" colspan="3" >Current targets and statistics of calls passing via the ACD routing system during the current interval</td>

</tr>';

fwrite($fp, $content);

$content = '<tr style="position:relative;top:expression(this.offsetParent.scrollTop);">

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Vendor" >Vendor</td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Billing priority" >Billing<BR/>priority</td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Total calls ended within the previous interval" > Calls </td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Total duration of calls ended within the interval (in minutes)" >Total<BR/>Minutes</td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Target balance" >Target<BR/>balance</td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Received during the current interval" >Received<BR/>during the<BR/>current<BR/>interval</td>

<td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Rejected to balance" >Rejected<BR/>to<BR/>balance</td>

</tr>';

fwrite($fp, $content);

$i=$j=$k=0;

while($row = mysql_fetch_row($result))

{

$date=$row[0];

$tot_min[$row[1]]=sprintf("%.1f",round($row[2], "1"));

$nb_ap[$row[1]]=$row[3];

$nb_5[$row[1]]=$row[4];

$nb_30[$row[1]]=$row[5];

$nb_0[$row[1]]=$row[6];

$nb_reject[$row[1]]=$row[7];

$acd[$row[1]]=sprintf("%.2f",round($row[8], "2"));

$reject[$row[1]]=$row[9];

$pref_billing[$row[1]]=$row[10];

$nb_conv[$row[1]]=$row[11];

$total_calls[$row[1]]=$row[12];

$dateTime = date_create( $date );

$DateTimeZone = timezone_open ( 'Europe/Zurich' );

date_timezone_set( $dateTime, $DateTimeZone );

$date = date_format( $dateTime, "Y-m-d H:i:s");// 2009-11-05 10:00:01

if ($acd[$row[1]]=="NULL")

$acd[$row[1]]=0;

$CRT=strlen(intval($row[2]))*7;

if ($tot_min[$row[1]]>$CRT)

{

$val[$row[1]]=intval($tot_min[$row[1]]);

$wval[$row[1]]=$val[$row[1]];

}

else

{

$val[$row[1]]="";

$wval[$row[1]]=intval($tot_min[$row[1]]);

}

if($i==1)

{

if(!$reject[55])

{

$reject[62]=100 - $reject[62];

$reject[55]=100 - $reject[62];

}

else

{

$reject[55]=100 - $reject[55];

$reject[62]=100 - $reject[55];

}

if (!$j)

fwrite($fp, '<tr bgcolor=\'#DDDDDD\' onMouseover=\'changBgIn("trv_'.$k.'","trc_'.$k.'");\' onMouseOut =\'changBgOut("trv_'.$k.'","trc_'.$k.'");\' id=\'trv_'.$k.'\' >');

else

fwrite($fp, '<tr bgcolor=\'#CCCCCC\' onMouseover=\'changBgIn("trv_'.$k.'","trc_'.$k.'");\' onMouseOut =\'changBgOut("trv_'.$k.'","trc_'.$k.'");\' id=\'trv_'.$k.'\' >');

fwrite($fp,"<td class=\"style4\" align=\"center\" rowspan=\"2\" >".substr($date,0,10)."<BR/>".substr($date,11,5)."</td>");

fwrite($fp,"<td class=\"style3\" align=\"center\" ><font style=\"color:#4AA02C\">Verizon</font></td>");

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Billing priority\" >".$pref_billing[55]."</td>");//preference

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Zero duration calls (duration=0)\">".$nb_0[55]."</td>");//Duration=0

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Problems (0<duration=5)\">".$nb_5[55]."</td>");//0<duration<5

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Short calls (5<duration=30)\" >".$nb_30[55]."</td>");//5<duration<30

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Conversations (duration>30)\" >".$nb_conv[55]."</td>");//Conversations

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Total calls ended within the interval\">".$nb_ap[55]."</td>");//Total Calls

fwrite($fp,"<td class=\"style3\" align=\"right\" title=\"Total duration of calls ended within the interval (in minutes)\">".$tot_min[55]."</td>");//Total Minutes

fwrite($fp,"<td class=\"style3\" align=\"right\" title=\"ACD (in minutes)\" >".$acd[55]."</td>");//ACD (min)

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Target balance\">".$reject[55]." %</td>");//Targeted load balance

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Received during the current interval\" rowspan=\"2\" valign=\"middle\">".($total_calls[55]+$total_calls[62])."</td>");//Targeted load balance

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Rejected to balance\" >".$nb_reject[55]."</td>");//Rejected to balance

fwrite($fp,'<td width=\"2000\" height="0" class="style1 style2" align="left" rowspan="2" valign="middle"><table><tr>');

fwrite($fp,"<td height=\"35\" width=\"$wval[55]\" bgcolor=\"#4AA02C\" align=\"center\">$val[55]</td>");

fwrite($fp,"<td height=\"35\" width=\"$wval[62]\" bgcolor=\"#488AC7\" align=\"center\">$val[62]</td>");

fwrite($fp,'</tr></table></td>');

fwrite($fp,"</tr>");

if (!$j)

fwrite($fp,'<tr bgcolor=\'#DDDDDD\' id=\'trc_'.$k.'\' onMouseover=\'changBgIn("trv_'.$k.'","trc_'.$k.'");\' onMouseOut =\'changBgOut("trv_'.$k.'","trc_'.$k.'");\' >');

else

fwrite($fp,'<tr bgcolor=\'#CCCCCC\' id=\'trc_'.$k.'\' onMouseover=\'changBgIn("trv_'.$k.'","trc_'.$k.'");\' onMouseOut =\'changBgOut("trv_'.$k.'","trc_'.$k.'");\' >');

fwrite($fp,"<td class=\"style3\" align=\"center\" ><font style=\"color:#488AC7\">Colt</font></td>");

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Billing priority\" >".$pref_billing[62]."</td>");//preference

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Zero duration calls (duration=0)\">".$nb_0[62]."</td>");//Duration=0

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Problems (0<duration=5)\">".$nb_5[62]."</td>");//0<duration<5

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Short calls (5<duration=30)\">".$nb_30[62]."</td>");//5<duration<30

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Conversations (duration>30)\">".$nb_conv[62]."</td>");//Conversations

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Total calls ended within the interval\">".$nb_ap[62]."</td>");//Total Calls

fwrite($fp,"<td class=\"style3\" align=\"right\" title=\"Total duration of calls ended within the interval (in minutes)\">".$tot_min[62]."</td>");//Total Minutes

fwrite($fp,"<td class=\"style3\" align=\"right\" title=\"ACD (in minutes)\">".$acd[62]."</td>");//ACD (min)

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Target balance\">".$reject[62]." %</td>");//Targeted load balance

fwrite($fp,"<td class=\"style3\" align=\"center\" title=\"Rejected to balance\" >".$nb_reject[62]."</td>");//Rejected to balance

fwrite($fp,"</tr>");

$k++;

}

$i++;

if($i==2)

{

$i=0;

if (!$j)

$j++;

else

$j=0;

}

$content = '</table><BR/></center>Each direct vendor connection has a cloned counterpart passing via the ACD routing system.<BR/><BR/>The values of the following 7 fields concern all connections of a vendor (direct and cloned together). These values represent the statistics collected during the previous interval.<BR/> - “Zero duration calls (duration=0)”<BR/> - “Problems (0<duration≤5)”<BR/> - “Short calls (5<duration≤30)”<BR/> - “Conversations (duration>30)”<BR/> - “Total calls ended within the interval”<BR/> - “Total duration of calls ended within the interval (in minutes)”<BR/> - “ACD (in minutes)”.<BR/><BR/>The fields “Target load”, “Received during the current interval” and “Rejected to balance” concern only the connections passing via the ACD routing system. These values concern the current interval.<BR/>';

fwrite($fp, $content);

fwrite($fp, '<BR/><BR/><center><div><a href="http://www.switzernet.com/"><img src="http://www.switzernet.com/public/091029-ACDstat/images/switzernet.gif" border="0" style="width:120px;" /></a><BR/>Switzernet © 2009 by O.Hammami</div></center></body></html>');

mysql_close($db);

fclose($fp);

echo "Creation du fichier Terminée.\n";

// ########## Fin de Creation Du Fichier tmp.html ##########

// ########## Debut de l'Upload Du Fichier tmp.html ##########

$d1=substr(date("Ymd"),2);

$remote_file = $d1."-acd-stat-".$prefix.".html";

for ($i = 1; $i <= count($ftp_server); $i++)

{

// Mise en place d'une connexion basique

if ($conn_id = ftp_connect($ftp_server[$i]))

{

// Identification avec un nom d'utilisateur et un mot de passe

if (ftp_login($conn_id, $ftp_user_name[$i], $ftp_user_pass[$i]))

{

if (ftp_chdir($conn_id, $ftp_dir[$i]))

{

echo "Le dossier courant est maintenant : " . ftp_pwd($conn_id) . "\n";

}

else

{

echo "Impossible de changer de dossier\n";

}

// Charge un fichier

if (ftp_put($conn_id, $remote_file, $filename, FTP_ASCII))

{

echo "Le fichier $filename a été chargé avec succès\n";

}

else

{

echo "Erreur: Upload Fichier, FILE=$filename\n";

}

else

echo "Erreur: Authentification, User=$ftp_user_name[$i]\n";

// Fermeture de la connexion

ftp_close($conn_id);

echo "Upload du fichier Terminée, Server=$ftp_server[$i]\n";

}

else

echo "Erreur: Connection FTP, Server=$ftp_server[$i]\n";

}

// ########## Fin de l'Upload Du Fichier tmp.html ##########

}

else

{

echo "Creation impossible du fichier ($filename) ! Vrifiez les droit d'accee pour ce repertoire\n";

exit();

}

connection.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

// Déclaration des paramètres de connexion

$host = "xxx.xxx.xxx.xxx:xxxx"; //Exemple : "192.168.1.117:5060";

$user = "root";

$password="xxxx";

$bdd ="porta-billing";

// Connexion au serveur

$db=mysql_connect($host,$user,$password) or die(mysql_error().' : Erreur de connexion !');

mysql_select_db($bdd) or die("Erreur de connexion");

connectionlocal.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

// Déclaration des paramètres de connexion

$host = "localhost";;

$user = "root";

$password="xxxx";

$bdd ="acd_armenia";

// Connexion au serveur

$db=mysql_connect($host,$user,$password) or die(mysql_error().' : Erreur de connexion !');

mysql_select_db($bdd) or die("Erreur de connexion");

config.inc.php

<?php

Switzernet Sarl, Parc Scientifique (PSE) de l'Ecole Polytechnique Fédérale de Lausanne (EPFL)

PSE-A, CH-1015, Lausanne Suisse

oussama.hammam@switzernet.com

christian.lathion@switzernet.com

emin.gabrielyan@switzernet.com

// Liste des serveurs Web

// Serveur 1:

$ftp_server[1] = "www.unappel.ch";

$ftp_user_name[1] = "*******";

$ftp_user_pass[1] = "*******";

$ftp_dir[1] = "/htdocs/public/091029-ACDstat/";

// Serveur 2:

$ftp_server[2] = "www.switzernet.com";

$ftp_user_name[2] = "*******";

$ftp_user_pass[2] = "*******";

$ftp_dir[2] = "/public/091029-ACDstat/";

// Serveur i:

// $ftp_server[i] = "www.yoursiteweb.com";

// $ftp_user_name[i] = "*******";

// $ftp_user_pass[i] = "*******";

// $ftp_dir[i] = ".../.../";

Interface de visualisation des statistiques

Il suffit d’uploader le script PHP dans vos serveurs web pour qu’il fonctionne correctement, il faut seulement que les noms des fichiers HTML contenant les statistiques uploadés par le serveur Kamailio respectent la forme suivante ‘yymmdd-acd-stat-préfixe.html’.

Code source

Télécharger acd-routing.zip

Télécharger acd-stat.zip

Référence:

Configuration de Kamailio (OpenSER) [091023]

ACD Routing for Fighting Wrong Signal Supervision [091020 ii]

Statistique ACD Routing pour Armenia, Yerevan [091029]

* * *