Ergon2000

Questa pagina è in sola lettura. Puoi visualizzare il sorgente, ma non puoi modificarlo. Contatta l'amministratore se pensi che ci sia un errore.
====== Registrazione Chiamate ======
===== Requisiti =====
==== Software ====
  * IndigoPerl
  * Oreka
  * Mysql
  * Tomcat

==== Moduli Perl ====
Una volta installato IndigoPerl entrate nella root /http://127.0.0.1:<porta configurata nel setup es:8081> e aprite il collegamento al "Perl GUI Package Manager" e controllate la presenza dei seguenti moduli:

  * Archive-Tar-1.38
  * Compress-Zlib-2.008
  * DBD-mysql-4.005
  * DBD-mysqlPP-0.04
  * DBI-1.601
  * file-remove-1.40
  * HTML-Parser-1.06
  * IO-Zlib-1.09
  * Net-Mysql-0.09
  * TimeDate-1.16
  * URI-1.35
  * Win32-API-0.47
  * Win32-GUI-1.03
  * libwin32-0.28
  * libwww-perl-5.808
  * modperl-2.0
  


Se ne manca qualcuno installatelo dal package manager selezionando la repository in alto nel menu a tendina.

===== Configurazione =====
==== Oreka ====
Va bene la configurazione Standard a parte che per il parametro //<AudioOutputPath>// che deve essere //una cartella temporanea// e non quella definitiva all'interno della root di Tomcat,i file saranno spostati nella cartella definitiva dallo script perl.

Es.
<AudioOutputPath>C:/tmp</AudioOutputPath>

==== MySql ====
Aggiungente le seguenti table:
   CREATE TABLE `recfromservice` (                                                          
                  `id` int(11) NOT NULL AUTO_INCREMENT,                                                  
                  `timestamp` datetime DEFAULT NULL,                                                     
                  `direction` int(11) DEFAULT NULL,                                                      
                  `duration` bigint(20) NOT NULL,                                                        
                  `localEntryPoint` varchar(255) DEFAULT NULL,                                           
                  `localParty` varchar(255) DEFAULT NULL,                                                
                  `remoteParty` varchar(255) DEFAULT NULL,                                               
                  `filename` varchar(255) DEFAULT NULL,                                                  
                  `recPortName` varchar(255) DEFAULT NULL,                                               
                  `expiryTimestamp` datetime DEFAULT NULL,                                               
                  `service_id` int(11) DEFAULT NULL,                                                     
                  `recPort_id` int(11) DEFAULT NULL,                                                     
                  `flag_erase` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'aggiunto per salvataggio file',  
                  PRIMARY KEY (`id`),                                                                    
                  KEY `FKA3B331B251080423` (`recPort_id`),                                               
                  KEY `FKA3B331B2C39BFD83` (`service_id`)                                                
                ) ENGINE=MyISAM AUTO_INCREMENT=165 DEFAULT CHARSET=latin1 

   CREATE TABLE `recservice` (                
              `ID` int(11) NOT NULL AUTO_INCREMENT,    
              `Timestamp` datetime NOT NULL,           
              `RecPort` varchar(255) DEFAULT NULL,     
              `RemotePort` varchar(255) DEFAULT NULL,  
              PRIMARY KEY (`ID`)                       
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1     
                               

==== Script per telefoni Cisco ====
Queste sono le righe da configurare in PhoneService.pl

   ####### Costanti Database 
   my $database = "oreka";
   my $hostname = "localhost";
   my $user = "root";
   my $password = "123456";
   ###################

Una volta configurato aggiungente il file PhoneService.pl nella cartella apache/htdocs di indigoPerl

Configurate il servizio sul CallManager e aggiungetelo ai telefoni interessati

==== Script per pulizia Database/Cartelle ====

Modificare le seguenti righe di ClearDB.pl:

   #######Costanti File 
   #Path di origine dei file, bisogna impostare la stessa cartella impostata in Oreka
   my $origpath = '/Programmi/Apache Software Foundation/Tomcat 5.5/webapps/tmp/';
   
   #Path di destinazione dei file, deve essere impostata la root di apache
   my $destpath = '/Programmi/Apache Software Foundation/Tomcat 5.5/webapps';
  
   ####### Costanti Database 
   my $database = "oreka";
   my $hostname = "10.255.0.19";
   my $user = "root";
   my $password = "123456";
   my $tablePS = "recservice";
   my $tableOrekaTape = "rectape";
   my $tableOrekaSeg = "recsegment";
   my $tableDef = "recfromservice";
  
Copiate il file ClearDB.pl in c:\
Aggiungete in operazioni pianificate "//perl.exe c:\ClearDB.pl//" e impostatelo per ripeterlo ogni 5 minuti 

===== Sorgente Script Perl =====

==== Servizio per telefoni Cisco (PhoneService.pl) ====

   #!/usr/bin/perl
   ###################################################################################
   # Created by: Marco Longo, marchrist85@gmail.com
   # Created by: Ergon2000, www.ergon2000.it
   # Date: 03/02/2009
   # Name: PhoneService.pl
   ##############################
   #Questo script riceve una richiesta http da un telefono cisco, ne legge l'ip e si connette alla pagina xml StreamingStatisticsX generata dal telefon
   #da essa ricava l'indirizzo e la porta del sorgente della conversazione e l'indirizzo e la porta del destinatario, questi dati vengono registrati su
   #un database mysql con il relativo timestamp 
   ####################################################################################
   
   
   use Cisco::IPPhone; 
   use XML::Simple;
   use LWP::Simple;
   use Data::Dumper;
   # use strict;
   use DBI;
   $|++;
   use CGI;
   
   ####### Costanti Database 
   my $database = "oreka";
   my $hostname = "localhost";
   my $user = "root";
   my $password = "123456";
   ###################
   
   ###Carico pagina web e prendo indirizzo ip client
   my $cgi = CGI->new;
   map { "$_\t$ENV{$_}\n" } keys %ENV ;
   my $remoteip = $ENV{'REMOTE_ADDR'};
   
   ###############################
   # my $count = 0;
   my $response;
   ####Recupero dati conversazione######
   my $url = "http://$remoteip/StreamingStatisticsX";   #da modificare 
   my $response = get($url);
   $response =~ s///;    #Sostituisco
   
   my $xs = XML::Simple->new(ForceArray => 1, KeepRoot => 1, KeyAttr=>[]);
   my $tmp = eval { $xs->XMLin($response)};
   my $localip = @{$tmp -> {StreamingStatistics}}[0]->{LocalAddr}[0];
   my $remoteip = @{$tmp -> {StreamingStatistics}}[0]->{RemoteAddr}[0];
   my $message = "registrazione In Corso";
   #################################
   
   
   
   ##############
   ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
   my $timestamp = ($year+1900) ."-". ($month+1) ."-". $mday ." ". $hour .":". $min .":". $sec; 
   
   ##### Inserisco dati nel DB ######
   
   $localip =~ s/\//,/;    #Sostituisco / con ,
   $remoteip =~ s/\//,/;
   
   my $dbh = DBI->connect("DBI:mysql:dbname=$database;host=$hostname",$user,$password,{'RaiseError' => 1});
   $dbh->do("INSERT INTO recservice (timestamp, recport, remoteport)VALUES (\"$timestamp\",\"$localip\",\"$remoteip\")") or $message ="Registrazione fallita, riprovare" ;
   $dbh ->disconnect();
   
   #my $sth = $dbh->prepare("INSERT INTO recservice VALUES ($timestamp,$localip)") or die(mysql_error());
   #$sth ->execute();
   
   my $mytext = new Cisco::IPPhone;
   $mytext->Text({ Title => "Registrazione", Prompt => $remoteip, Text => "Registrazione in corso" });
   $mytext->AddSoftKeyItem( { Name => "Exit", URL => "SoftKey:Exit", Position => "2" });
   print $mytext->Content;

==== Servizio temporizzato per pulire database e cartella (ClearDB.pl) ====

   #!/usr/bin/perl
   ###################################################################################
   # Created by: Marco Longo, marchrist85@gmail.com
   # Created by: Ergon2000, www.ergon2000.it
   # Date: 03/02/2009
   # Name: clearDB.pl
   ##############################
   
   use DBI;
   use strict;
   use File::Copy;
   use File::Path;
   use File::Remove 'remove';
   
   #######Costanti File 
   my $origpath = '/Programmi/Apache Software Foundation/Tomcat 5.5/webapps/';
   my $destpath = '/Programmi/Apache Software Foundation/Tomcat 5.5/webapps/tmp/';
      
   ####### Costanti Database 
   my $database = "oreka";
   my $hostname = "10.255.0.19";
   my $user = "root";
   my $password = "123456";
   my $tablePS = "recservice";
   my $tableOrekaTape = "rectape";
   my $tableOrekaSeg = "recsegment";
   my $tableDef = "recfromservice";
   ###################
   
   my @row;
   my @rowdbrec;
       
   ######################################## Lavoro sul DB ######
   
   my $dbh = DBI->connect("DBI:mysql:dbname=oreka;host=$hostname",$user,$password,{'RaiseError' => 1}) or die "Can't connect to database: $DBI::errstr!";
   my $sth = $dbh->prepare("SELECT * FROM $tablePS") or die "Can't prepare SQL statement: $DBI::errstr\n";
   $sth -> execute() or die "Can't execute SQL statement: $DBI::errstr\n";
   
   #$query_handle->bind_columns(undef, \$id, \$timestamp, \$recport);
   while(@row = $sth->fetchrow_array()) {
       my $dbrec = $dbh->prepare("SELECT * FROM $tableOrekaTape WHERE (recPortName = \"@row[2]\" OR recPortName = \"@row[3]\") AND timestamp <= \"$row[1]\" AND expirYTimeStamp >= \"$row[1]\" ");
   
   #my $dbrec = $dbh->prepare("SELECT * FROM $tableOrekaTape WHERE recPortName = \"10.255.0.19,24588\" AND timestamp <= \"2009-01-09 15:52:15\" AND expirYTimeStamp >= \"2009-01-09 15:52:15\" ");
   
   $dbrec -> execute();
   
   @rowdbrec = $dbrec ->fetchrow_array;
   
   	$dbh->do("INSERT INTO $tableDef (id, timestamp, direction, duration, localEntryPoint, localParty, remoteParty, filename, recPortName, expiryTimestamp, service_id, recPort_id)VALUES (\"@rowdbrec[0]\",\"@rowdbrec[1]\",\"@rowdbrec[2]\",\"@rowdbrec[3]\",\"@rowdbrec[4]\",\"@rowdbrec[5]\",\"@rowdbrec[6]\",\"@rowdbrec[7]\",\"@rowdbrec[8]\",\"@rowdbrec[9]\",\"@rowdbrec[10]\",\"0\")");
   
   ###############Lavoro su i file
   	my $del = rindex(@rowdbrec[7],'/');
   	my $destdir = substr(@rowdbrec[7],0,($del)); 
   	my $dir = $destpath .$destdir;
  	if (-d $dir) {
   		#directory esiste
   	}
   	else
   	{
   		#directory non esiste
   		mkpath($dir, {verbose => 1}) or die "impossibile creare directory"; 
   	}
   	if (move($origpath.@rowdbrec[7],$destpath.@rowdbrec[7]))
  	{
   		#se il file esiste e viene spostato
   		$dbh->do("DELETE FROM $tablePS WHERE ID = \"$row[0]\"") or die "impossibile eseguire query: $DBI::errstr!";
   		print (@rowdbrec[0]);
   		#$dbh->do("DELETE FROM $tableOrekaTape WHERE id = \"$@rowdbrec[0]\"") or die "impossibile eseguire query: $DBI::errstr!";
   	}
   	else
   	{
  		#se il file non esiste
   		print ("file inesistente");
   	}
   
   #################Fine lavoro sui file
   		
   
   } #fine ciclo while
   
   warn "Data fetching terminated early by error: $DBI::errstr\n"
      if $DBI::err;
   	  
   my $sec; my $min;my $hour;my $mday;my $month; my $year; my $wday;my $yday; my$isdst;
     ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst)=localtime(time);
   my $timestamp = ($year+1900) ."-". ($month+1) ."-". $mday ." ". $hour .":". ($min -5).":". $sec; 
   
   ########################Cancellazione File inutili
   
   $sth = $dbh->prepare("SELECT filename FROM $tableOrekaTape WHERE id NOT IN (SELECT id FROM $tableDef) AND timestamp < \"$timestamp\"") or die "Can't prepare SQL statement: $DBI::errstr\n";
   $sth -> execute() or die "Can't execute SQL statement: $DBI::errstr\n";
   while(@row = $sth->fetchrow_array()) {
   remove($origpath.@row[0] );
   } 
   ################################
   
   
   #####################Pulisco Database
      $dbh->do("DELETE FROM $tableOrekaTape WHERE id NOT IN (SELECT id FROM $tableDef) AND timestamp < \"$timestamp\"") or die "impossibile eseguire query: $DBI::errstr!";
      $dbh->do("DELETE FROM $tableOrekaSeg WHERE rectape_id NOT IN (SELECT id FROM $tableOrekaTape) AND timestamp < \"$timestamp\"") or die "impossibile eseguire query: $DBI::errstr!";
   ################################
   
   $dbh ->disconnect or warn "Error disconnecting: $DBI::errstr\n";
   ######################################## Fine Lavoro sul DB ######
     
   exit;