how to load dnc for a campaign

[tehseen]

Greetings to All ,
I want to upload a dnc list for my single campaign , since Im not good in perl at all but try to make changes to get following script to fitin my need . I make changes to update records in vicidial_campaign table and added a variable name campaign: my csv file have two colum first one is for phone and second one is campaign id. which look like

Code: Select all

phone number , campaign
1234567890   ,  mytest 


and here is the script below

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;
   $campaign = $dnc[$i];
   $campaign =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
   
$stmtA = "UPDATE vicidial_campaign_dnc set status='DNUSE' where phone_number = '$phone' AND campaign_id = '$campaign';";
      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;



error mesasge is

Code: Select all

DBD::mysql::db do failed: Unknown column 'status' in 'field list' at dnc.pl line 58


Looking forward for your kind response.


Regards
Tehseen[/code]

[heinz]

Hi,
as far as I understand you want to load the data from your csv file into the vicidial_campaign_dnc table.
Your query is not suitable to do that. You need to use something like that instead:

Code: Select all

$stmtA = "INSERT INTO vicidial_campaign_dnc (phone_number,campiagn_id) VALUES ('$phone', '$campaign');";


Even easier would be to use phpmyadmin to upload the data.
Thanks,
heinz

[mflorell]

How many numbers do you want to load?

[tehseen]

about 1500 numbers :S.

still the same , any one have any idea ? how to load dnc list for a campaign I dont want to upload a global dnc . May be I mess with the script . Looking forward for kind response.

Regards
Tehseen

[mflorell]

Not sure if this is in 2.0.5 or SVN trunk, but you should be able to load this many through the DNC link in the LISTS section of the admin.php page.

[tehseen]

thankz alot mflorell for your prompt response and to all other developers and other active members of this forum . Who really helpful to user like me . I got it and its done via dnc list in admin panel so I better keep that script to load dnc as global .

Regards
Tehseen

[tehseen]

I only got one problem with admin panel when adding dnc numbers for a camapaign it takes alot of time and even my browser got hangedup . when my dnc list increases more then 5000 :S . is there any other way to get those file upload very quickly rather copying and pasting those number in dnc list . Looking forward for your kind response


Regards
Tehseen

[heinz]

phpmyadmin

[tehseen]

thanks heinz , well I also correct that perl script just need few alignment setting then I will upload it too . mean while Ive done with phpmyadmin to accoumplish that task cause I've to up one of my machine live .


here are the steps for other who wish to install phpmyadmin in vicidial.

Code: Select all

 Step#1 : Installing 3rs party repositories for CentOS


Choose one according to your system arch:

 #i386: http://apt.sw.be/redhat/el5/en/i386/RPMS.dag/rpmforge-release-0.3.6-1.el5.rf.i386.rpm

#x86_64 : http://apt.sw.be/redhat/el5/en/x86_64/RPMS.dag/rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm


Step#2 :  Installing DAG key

rpm --import http://dag.wieers.com/rpm/packages/RPM-GPG-KEY.dag.txt


Step#3 : Installing Phpmyadmin

 yum -y install phpmyadmin


Step#4 : Allowing you Subnets into phpmyadmin.conf

vi /etc/httpd/conf.d/phpmyadmin.conf

Allow from 127.0.0.1 10.2 10.3


Step#5 : Setting up authentication method in config.inc.php

vi /usr/share/phpmyadmin/config.inc.php

#$cfg['Servers'][$i]['auth_type'] = 'cookie'; # change 'cookie' into 'http'
$cfg['Servers'][$i]['auth_type'] = 'http';


Step#6 : Setting up config.inc.php file permission

chmod 755 /usr/share/phpmyadmin/config.inc.php



Hope it will help others as well


Regards
Tehseen