Explication de Script PHP

Oussama Hammami, 2009-11-26

Switzernet

 

 

 

 

 

Architecture

 

 

 

 

Script PHP

scriptacdcron.php

 

Script PHP

Commentaires

include "acd-rate.php";

Script qui calcule les valeurs ACD et la probabilité de rejet.

include "upload.php";

Script qui uploade les statistiques dans le serveur web de Switzernet.

acd-rate.php

 

Script PHP

Commentaires

 

  include "connectionlocal.php";
 
  $query0="select i_vendor, i_rate, date FROM acd_vendors where prefix=$prefix order by date desc limit 2;";
         $result0=mysql_query ($query0) or die (mysql_error()) ;
         while($row0 = mysql_fetch_row($result0)) {
  $Pred_rate[$row0[0]] = $row0[1];
 $date_ch =$row0[2];
  }
 
  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"); 
 
 // The minimal duration of the interval must be of 20 minutes
  if ($diff_date >= 20 ) 
                 $INTV=$diff_date;
         else
                 {
                 echo "Opération annulée ;($diff_date) intervalle < 20 min!\n";
                 exit();
                 }

On calcule l’intervalle entre la dernière exécution de ce script (date) et la date courante (=$diff_date).

S’il est inférieur à 20 minutes alors on interrompe l’exécution de ce dernier.

 

 
 include "connection.php";
 
 // i_rate:ACD Routing Verizon = 235
 $query01="select r.preference, r.i_rate from Destinations d, Rates r where d.i_dest=r.i_dest
 and r.i_tariff=235 and d.destination=$prefix order by r.effective_from desc limit 1;";
 // i_rate:ACD Routing Colt = 233
 $query02="select r.preference, r.i_rate  from Destinations d, Rates r where d.i_dest=r.i_dest
 and r.i_tariff=233 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];

On récupère les valeurs de préférence de nos deux tarifs (ACD Routing Verizon et ACD Routing Colt) de Biling.

À chaque exécution de script les valeurs de préférence sont mises à jours.

 
 // i_rate:Verizon = 226
 $query03="select MAX(r.i_rate)  from Destinations d, Rates r where d.i_dest=r.i_dest and
r.i_tariff=226 and d.destination=$prefix;";
 // i_rate:Colt = 224
 $query04="select MAX(r.i_rate)  from Destinations d, Rates r where d.i_dest=r.i_dest and
r.i_tariff=224 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];

On récupère les valeurs de «i _rate» pour les deux vendeurs Verizon et Colt.

Ainsi nous avons les valeurs de «i _rate» sauvegarder dans notre base de donnée local ($Pred_rate) et les valeurs de  «i _rate» sauvegarder dans la base de donnée de Biling ($Now_rate).

 

 
$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 "Opération 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);
 

On calcule la valeur de l’ACD pour les deux vendeurs ainsi la probabilité de rejet si le nombre totale des appels ($TOT_Nb_Ap) est supérieur à 20.

 
 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]);
                 }
         }

S’il y a une différence entre ces deux valeurs ($Pred_rate et $Now_rate) ce qu’il y a un nouveau tarif uploadé.

Alors il faut envoyer un email avec les nouveaux prix.

Ce traitement est assuré par la fonction ‘Mail_Rate’.

 
 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);

On met à jour les valeurs de l’ACD et la probabilité de rejet en ajoutant une nouvelle ligne dans la table ‘acd_vendors’ pour chaque vendeur.

MailRate.php

 

Script PHP

Commentaires

 
 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);
 }

bool date_passed ( dateTime $Effective_Date )

 

Retourne TRUE si la

date "Effective Date" est dépassé.

 
 function Send_Mail($Vd,$file_name,$dtf)
 {
 $email="rates@unappel.ch";
 if ($Vd==55)
         {
         $my_email="ACD_Routing.Verizon@unappel.ch";
         $subject="ACD Routing Verizon 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 Verizon</b>.<br/>
         Effective From<font style="font-weight: bold;color:red;font-style:bold;">'.$dtf.'</font>.</p>';
         }
 else if ($Vd==62)
         {
         $my_email="ACD_Routing.Colt@unappel.ch";
         $subject="ACD Routing Colt 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 Colt</b>.<br/>
         Effective From<font style="font-weight: bold;color:red;font-style: bold;">'.$dtf.'</font>.</p>';
         }
 $boundary = "_".md5 (uniqid (rand()));
 $attached_file = file_get_contents($file_name);
 $attached_file = chunk_split(base64_encode($attached_file));
 $attached = "\n\n". "--". $boundary."\n Content-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";
                 return false; 
                 }
 
         }
 return true;
 }

bool Send_Mail ( int $vd, string $file_name, string $date )

 

envoi un email à "rates@unappel.ch" en joignant le fichier "$file_name".  

 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);
 }

dateTime Convert_time_to_ch ( dateTime $dateUS )

 

retourne $dateUS  avec le fuseau horaire 'Europe/Zurich'.  

 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">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   <LastAuthor>Switzernet</LastAuthor>
   <Created>1996-10-14T23:33:28Z</Created>
   <LastSaved>2009-11-13T17:19:05Z</LastSaved>
   <Version>11.9999</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   <WindowHeight>9300</WindowHeight>
   <WindowWidth>15135</WindowWidth>
   <WindowTopX>120</WindowTopX>
   <WindowTopY>120</WindowTopY>
   <AcceptLabelsInFormulas/>
   <ProtectStructure>False</ProtectStructure>
   <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
   <Style ss:ID="Default" ss:Name="Normal">
    <Alignment ss:Vertical="Bottom"/>
    <Borders/>
    <Font/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
   </Style>
  </Styles>
  <Worksheet ss:Name="'.$Header_Worksheet.'">
 ';
         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).'),&quot;yyyy-mm-dd&quot;)&amp;&quot; 
'.substr($Tab_XML[$i][$j],11).'&quot;"><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>
         <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <Selected/>
    <Panes>
     <Pane>
      <Number>3</Number>
      <ActiveRow>1</ActiveRow>
      <ActiveCol>1</ActiveCol>
     </Pane>
    </Panes>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
   </WorksheetOptions>
  </Worksheet>
 </Workbook>
 ';
         fwrite($fp, $content);
         fclose($fp);
         }
 Else
         {
         echo "Erreur ouverture de fichier $FileName";
         return false;
         }
return true;
}

Bool Exel_XML ( array $Tab, string $file_name, string $Header )

 

Crée un fichier XML $file_name à partir du tableau $Tab.

 
Function Mail_Rate($Vd, $Rate, $prefix,$preference)
 {
 include "connection.php";
 
 if ($Vd==55)
         {
         $tariff=226;
         $file_name="ACD_Routing_Verizon.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 Verizon";
         $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=224;
         $file_name="ACD_Routing_Colt.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]="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 Colt";
         $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;
 }

Bool Mail_Rate ( int $vd, int $Rate, string $prefix, string $preference )

 

C’est la fonction appelé par le script acd_rate.php

Elle crée un table à deux dimensions qui contient tous les valeurs à envoyer, elle transforme ce dernier en fichier XML (Excel_XML) et l’envoi par email (Send_Mail).

 

upload.php

 

Script PHP

Commentaires

 
 include 'config.inc.php';
 
 $prefix = 37410;
 
 $d2=date("Y-m-d");

inclure le fichier de configuration 'config.inc.php'

 
 $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");

$ex est la date d’exécution de ce script converti en 'Europe/Zurich'.

Cette date sera affichée dans l’entête de la page de statistique. 

 
 
 
 
 $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">
 <style type="text/css"> <!-- 
 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>
 <SCRIPT language="Javascript">
 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="style1 style2" align="center" bgcolor="#FFFFFF" >&nbsp;</td>
 <td class="style1 style2" align="center" bgcolor="#FFFFFF" >&nbsp;</td>
 <td class="style1 style2" align="center" bgcolor="#FFFFFF" >&nbsp;</td>
 <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>
 <td class="style1 style2" align="center" bgcolor="#FFFFFF">&nbsp;</td>
 </tr>';
 
 fwrite($fp, $content);
 
 $content = '<tr style="position:relative;top:expression(this.offsetParent.scrollTop);">
 <td width="57" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Date and 
Time">Date and<BR/>Time</td>
 <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="Zero duration 
calls  (duration=0)" > =0 </td>
 <td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Problems  
(0<duration=5)" > 0&lt;<br/>&le;5 </td>
 <td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Short calls  
(5<duration=30)" > 5&lt;<br/>&le;30 </td>
 <td width="50" class="style1 style2" align="center" bgcolor="#C0C0C0" title="Conversations  
(duration>30)" > &gt;30 </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="ACD  (in minutes)" 
>ACD<BR/>(min)</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>
 <td width="2000" class="style1 style2" align="center" >&nbsp;</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/> - &ldquo;Zero duration calls 
(duration=0)&rdquo;<BR/> - &ldquo;Problems (0&lt;duration&le;5)&rdquo;<BR/> - &ldquo;Short calls 
(5&lt;duration&le;30)&rdquo;<BR/> - &ldquo;Conversations (duration&gt;30)&rdquo;<BR/> - 
&ldquo;Total calls ended within the interval&rdquo;<BR/> - &ldquo;Total duration of calls ended 
within the interval (in minutes)&rdquo;<BR/> - &ldquo;ACD (in minutes)&rdquo;.<BR/><BR/>The 
fields &ldquo;Target load&rdquo;, &ldquo;Received during the current interval&rdquo; and 
&ldquo;Rejected to balance&rdquo; 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 &copy; 2009 by 
O.Hammami</div></center></body></html>');
 
 mysql_close($db);
 
 fclose($fp);
 
 
 
 echo "Creation du fichier Terminée.\n";
 
 

Création de fichier 'tmp.html'.

Ce ficher contient tous les lignes ajoutées dans la base de donnée avec une date égale à la date d’exécution de script (…where date like '$d2%...').  

$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: Uploade Fichier, FILE=$filename\n";
                                   }
                          }
                 else
                          echo "Erreur: Authentification, User=$ftp_user_name[$i]\n";
                 // Fermeture de la connexion
                 ftp_close($conn_id);
                 echo "Uploade du fichier Terminée, Server=$ftp_server[$i]\n";
                 }
         else
                 echo "Erreur: Connexion FTP, Server=$ftp_server[$i]\n";
         }
 }
 else
 {
 echo "Création impossible du fichier ($filename) ! Vérifiez les droit d'accès pour ce 
répertoire \n";
 exit();
 }

Upload du fichier 'tmp.html' aux listes des serveurs web définies dans le fichier de configuration 'config.inc.php'.

 

Référence:

Configuration de Kamailio (OpenSER) [091023]

ACD Routing for Fighting Wrong Signal Supervision [091020  ii]

Les Testes de ACD-Routage [091023]

Statistique ACD Routing pour Armenia, Yerevan [091029]

Routage avec ACD pour Armenie-Yerevan [091112]

Converting ACD Routing price list for upload in the billing [091117]

 

 

 

* * *

       2009 © swirzernet.com