Help with DNC perl script

[okli]

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.

[mflorell]

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.

[okli]

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...

[okli]

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;

[mflorell]

Very good, Thanks for posting this!

[okli]

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

[Alek]

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]

[okli]

Thank you, this seems to be much better solution.

[ykhan]

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.

[ykhan]

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

[okli]

I ran the script

Which one? There are 2 in this thread.

[ykhan]

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.

[okli]

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.

[ykhan]

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?

[Alek]

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.