vicidial.org

I need to update our database on a few stages against the new canadian DNC list.
Without any knowledge in perl, using parts of the astguiclient perl scripts I came up with this :

Code: Select all

$PATHconf =      '/etc/astguiclient.conf';

open(conf, "$PATHconf") || die "can't open $PATHconf: $!\n";
@conf = <conf>;
close(conf);
$i=0;
foreach(@conf)
   {
   $line = $conf[$i];
   $line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
   if ( ($line =~ /^PATHhome/) && ($CLIhome < 1) )
      {$PATHhome = $line;   $PATHhome =~ s/.*=//gi;}
   if ( ($line =~ /^PATHlogs/) && ($CLIlogs < 1) )
      {$PATHlogs = $line;   $PATHlogs =~ s/.*=//gi;}
   if ( ($line =~ /^PATHagi/) && ($CLIagi < 1) )
      {$PATHagi = $line;   $PATHagi =~ s/.*=//gi;}
   if ( ($line =~ /^PATHweb/) && ($CLIweb < 1) )
      {$PATHweb = $line;   $PATHweb =~ s/.*=//gi;}
   if ( ($line =~ /^PATHsounds/) && ($CLIsounds < 1) )
      {$PATHsounds = $line;   $PATHsounds =~ s/.*=//gi;}
   if ( ($line =~ /^PATHmonitor/) && ($CLImonitor < 1) )
      {$PATHmonitor = $line;   $PATHmonitor =~ s/.*=//gi;}
   if ( ($line =~ /^VARserver_ip/) && ($CLIserver_ip < 1) )
      {$VARserver_ip = $line;   $VARserver_ip =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_server/) && ($CLIDB_server < 1) )
      {$VARDB_server = $line;   $VARDB_server =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_database/) && ($CLIDB_database < 1) )
      {$VARDB_database = $line;   $VARDB_database =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_user/) && ($CLIDB_user < 1) )
      {$VARDB_user = $line;   $VARDB_user =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_pass/) && ($CLIDB_pass < 1) )
      {$VARDB_pass = $line;   $VARDB_pass =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_port/) && ($CLIDB_port < 1) )
      {$VARDB_port = $line;   $VARDB_port =~ s/.*=//gi;}
   $i++;
   }

use DBI;    

$dbhA = DBI->connect("DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port", "$VARDB_user", "$VARDB_pass")
 or die "Couldn't connect to database: " . DBI->errstr;

$PATHdnc = '/home/dnc.csv';
open(dnc, "$PATHdnc") || die "can't open $PATHdnc: $!\n";
@dnc = <dnc>;
close(dnc);
$i=0;
foreach(@dnc)
   {
   $phone = $dnc[$i];
   $phone =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
   $stmtA = "UPDATE vicidial_list set status='DNUSE' where phone_number = '$phone';";
      if($DB){print STDERR "\n|$stmtA|\n";}
   $affected_rows = $dbhA->do($stmtA); #  or die  "Couldn't execute query:|$stmtA|\n";
   }

$dbhA->disconnect();
exit;

Before I screw up with the database- are there any errors in the above script, or a better way to do that?

dnc.csv is a series of files, with DNC numbers only on a new line, each file with 30 000 to 100 000 numbers, will be put manually every few days.

Thanks in advance.

I would comment out this line:
"$affected_rows = $dbhA->do($stmtA); # or die "Couldn't execute query:|$stmtA|\n""

and just see if it runs.

I just created a new table vicidial_list_test with some entries copied from vicidial_list and tested it on it.

Scripts runs fine, however one important line was missing, the bolded one:

$affected_rows = $dbhA->do($stmtA); # or die "Couldn't execute query:|$stmtA|\n";
$i++;
}

I am closing my eyes and pressing enter...

Added execution time:

Code: Select all

$PATHconf =      '/etc/astguiclient.conf';

$secX = time();

open(conf, "$PATHconf") || die "can't open $PATHconf: $!\n";
@conf = <conf>;
close(conf);
$i=0;
foreach(@conf)
   {
   $line = $conf[$i];
   $line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
   if ( ($line =~ /^PATHhome/) && ($CLIhome < 1) )
      {$PATHhome = $line;   $PATHhome =~ s/.*=//gi;}
   if ( ($line =~ /^PATHlogs/) && ($CLIlogs < 1) )
      {$PATHlogs = $line;   $PATHlogs =~ s/.*=//gi;}
   if ( ($line =~ /^PATHagi/) && ($CLIagi < 1) )
      {$PATHagi = $line;   $PATHagi =~ s/.*=//gi;}
   if ( ($line =~ /^PATHweb/) && ($CLIweb < 1) )
      {$PATHweb = $line;   $PATHweb =~ s/.*=//gi;}
   if ( ($line =~ /^PATHsounds/) && ($CLIsounds < 1) )
      {$PATHsounds = $line;   $PATHsounds =~ s/.*=//gi;}
   if ( ($line =~ /^PATHmonitor/) && ($CLImonitor < 1) )
      {$PATHmonitor = $line;   $PATHmonitor =~ s/.*=//gi;}
   if ( ($line =~ /^VARserver_ip/) && ($CLIserver_ip < 1) )
      {$VARserver_ip = $line;   $VARserver_ip =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_server/) && ($CLIDB_server < 1) )
      {$VARDB_server = $line;   $VARDB_server =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_database/) && ($CLIDB_database < 1) )
      {$VARDB_database = $line;   $VARDB_database =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_user/) && ($CLIDB_user < 1) )
      {$VARDB_user = $line;   $VARDB_user =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_pass/) && ($CLIDB_pass < 1) )
      {$VARDB_pass = $line;   $VARDB_pass =~ s/.*=//gi;}
   if ( ($line =~ /^VARDB_port/) && ($CLIDB_port < 1) )
      {$VARDB_port = $line;   $VARDB_port =~ s/.*=//gi;}
   $i++;
   }

use DBI;    

$dbhA = DBI->connect("DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port", "$VARDB_user", "$VARDB_pass")
 or die "Couldn't connect to database: " . DBI->errstr;

$PATHdnc = '/home/dnc.csv';
open(dnc, "$PATHdnc") || die "can't open $PATHdnc: $!\n";
@dnc = <dnc>;
close(dnc);
$i=0;
foreach(@dnc)
   {
   $phone = $dnc[$i];
   $phone =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
   $stmtA = "UPDATE vicidial_list set status='DNUSE' where phone_number = '$phone';";
      if($DB){print STDERR "\n|$stmtA|\n";}
   $affected_rows = $dbhA->do($stmtA); #  or die  "Couldn't execute query:|$stmtA|\n";
   $i++;
   }

$dbhA->disconnect();

$secy = time();      $secz = ($secy - $secX);      $minz = ($secz/60);      # calculate script runtime so far
print "\n     - process runtime      ($secz sec) ($minz minutes)\n";

exit;

Very good, Thanks for posting this!

Well, my 0.000000000002 cents...

For the record- just run in on our vicidial_list with about 1.1 mill. leads.

DNC.csv had ~47 000 numbers in it.

- process runtime (261 sec) (4.35 minutes)

I was afraid I'd have to leave it running overnight. lol

My PHP/Perl/HTML knowledge is really ugly. But never the less I will allow myself to share my Canadian DNC implementation. It accessible via URL.
Make sure you create directory "/var/www/html/vicidial/dncfiles/" and make it writable by apache user. Consist of 2 files:

1. HTML file "dncfile.html" with following content:
<html>
<body>

<form action="update_records.php" method="post"
enctype="multipart/form-data">
<label for="file">Select File:</label>
<input type="file" name="file" id="file" />
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>

2. PHP script "update_records.php"

<?php
$timenow = localtime();
$store_file_name = "/var/www/html/vicidial/dncfiles/dnc-file---" . $timenow[0] . $timenow[1] . $timenow[2] . $timen
ow[3] . $timenow[4] . $timenow[5] .".txt";
$url_file_name = "dncfiles/dnc-file---" . $timenow[0] . $timenow[1] . $timenow[2] . $timenow[3] . $timenow[4] . $ti
menow[5] .".txt";
$trans_file_name = "/var/www/html/vicidial/dncfiles/trans-file---" . $timenow[0] . $timenow[1] . $timenow[2] . $tim
enow[3] . $timenow[4] . $timenow[5] .".txt";
$url_tran_name = "dncfiles/trans-file---" . $timenow[0] . $timenow[1] . $timenow[2] . $timenow[3] . $timenow[4] . $
timenow[5] .".txt";
$total = 0;
$positiv = 0;

$con = mysql_connect("localhost","cron","1234");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("asterisk", $con);
echo "Upload file name for DNC filtering: " . $_FILES["file"]["name"] . "<br />";

$file = fopen($_FILES["file"]["tmp_name"], "r") or exit("Unable to open temporary file file!");
copy($_FILES["file"]["tmp_name"], $store_file_name);
$read_file = fopen("$store_file_name", "r") or exit("Unable to open archived file! $store_file_name");

while (( $line = fgetcsv($read_file)) !== FALSE ) {
$total++;
mysql_query("UPDATE vicidial_list SET status = 'DNC' where phone_number = '$line[0]$line[1]'");
$positive = $positive + mysql_affected_rows();
file_put_contents("$trans_file_name", "UPDATE vicidial_list SET status = 'DNC' where phone_number = '$line[0]$lin
e[1]'" . "\n", FILE_APPEND);
}
echo "total number processed is $total" . "<br />";
echo "Phone numbers marked to dnc is $positive" . "<br />";
print "Below is complete list of phone numbers that was processed during this transaction" . "<br />";
print "<th width='50%'><font class='standard_bold'><a href='$url_file_name'>View complete list of DNC phones in input fil
e</a></font></th>";
print "<br />";
print "Total list of transactions for this operation" . "<br />";
print "<th width='50%'><font class='standard_bold'><a href='$url_tran_name'>View complete list of transaction commited du
ring this DNC upload</a></font></th>";

fclose($file);




?>
[/code]

Thank you, this seems to be much better solution.

My PHP/Perl/HTML knowledge is really ugly. But never the less I will allow myself to share my Canadian DNC implementation. It accessible via URL.
Make sure you create directory "/var/www/html/vicidial/dncfiles/" and make it writable by apache user. Consist of 2 files:

Just wondering what this method will do exactly? Thanks.

I ran the script, but there was no output after it completed. What should be expected once the script runs properly and if not what is displayed.

Thanks

I ran the script

Which one? There are 2 in this thread.

The HTML based one, with 2 files. I do not get any output from it. I have chmoded the directory for all users to write and execute, but nothing happens.

I can't help with it, haven't used it yet.
Idea- is the file properly formatted? Check end of lines, spaces etc. The way it's posted breaks formatting.

Formatting of the file from DNC is XXX,XXXXXXX. Not sure if that is supposed to work. Alek, can you shed soem light on this?

You should get some output on your screen, regardless of the format.
Did you get any files in /var/www/html/vicidial/dncfiles/ directory?
There will be 2 type of files there. "dnc-file*" - it is copy of the file you downloaded from the web site as a telemarketer. It contains coma separated phone numbers. And then there is "trans-file*" it is actual sql statement that was executed.