I'm pretty new to perl, I have been struggling with my own code I made. It works but i had to change some things and now I keep getting "can't call method "deseriredCompressionLevel" on an undefined value at /usr/lib/perl/5.18.2/Archive/Zip/Archive.pm line 256, <FH> line 20497, I've been working on this for days, I would appreciate some wisdom or direction.
#path to astguiclient configuration file:
$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 =~ /^PATHlogs/) && ($CLIlogs < 1) )
{$PATHlogs = $line; $PATHlogs =~ s/.*=//gi;}
if ( ($line =~ /^PATHsounds/) && ($CLIsounds < 1) )
{$PATHsounds = $line; $PATHsounds =~ 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_custom_user/) && ($CLIDB_custom_user < 1) )
{$VARDB_custom_user = $line; $VARDB_custom_user =~ s/.*=//gi
+;}
if ( ($line =~ /^VARDB_custom_pass/) && ($CLIDB_custom_pass < 1) )
{$VARDB_custom_pass = $line; $VARDB_custom_pass =~ s/.*=//gi;
+}
if ( ($line =~ /^VARDB_port/) && ($CLIDB_port < 1) )
{$VARDB_port = $line; $VARDB_port =~ s/.*=//gi;}
$i++;
}
if (!$VARDB_port) {$VARDB_port='3306';}
#use strict;
use warnings;
use Excel::Writer::XLSX;
use DBI;
use Time::Piece;
use Math::Round;
my $t = "" . $i;
$dbhA = DBI->connect("DBI:mysql:$VARDB_database:$VARDB_server:$VAR
+DB_port", "$VARDB_user", "$VARDB_pass")
or die "Couldn't connect to database: " . DBI->errstr;
my $rowCount = 0;
my $filename = "Output2022.xlsx";
my $workbook = Excel::Writer::XLSX->new( $filename );
open(FH, "<", "Debt.csv" ) or die;
my $worksheet = $workbook->add_worksheet('List');
my $worksheet2 = $workbook->add_worksheet('List2');
my $format = $workbook->add_format();
my $format1 = $workbook->add_format();
my $format2 = $workbook->add_format();
$format->set_center_across();
$format1->set_color( 'red' );
$format2->set_bold();
#proper format must be used for the file or it will give an error
$worksheet->set_column( 8, 8, 20 );
$worksheet->set_column( 10, 10, 15 );
$worksheet->set_column( 4, 4, 18 );
$worksheet->set_column( 5, 5, 15 );
$worksheet->set_column( 12, 12, 65 );
$worksheet->write(0, 0, "source_id" );
$worksheet->write(0, 1, "first_name" );
$worksheet->write(0, 2, "middle" );
$worksheet->write(0, 3, "last_name" );
$worksheet->write(0, 4, "address1" );
$worksheet->write(0, 5, "city");
$worksheet->write(0, 6, "state");
$worksheet->write(0, 7, "postal_code");
$worksheet->write(0, 8, "phone_number");
$worksheet->write(0, 9, "address3");
$worksheet->write(0, 10,"province");
$worksheet->write(0, 11, "email");
my $rowCount1 = my $rowCount2 = 0;
$worksheet->set_column( 8, 8, 20 );
$worksheet->set_column( 10, 10, 15 );
$worksheet->set_column( 4, 4, 20 );
$worksheet->set_column( 5, 5, 15 );
$worksheet->set_column( 12, 12, 62 );
$worksheet2->write(0, 0, "source_id" );
$worksheet2->write(0, 1, "first_name" );
$worksheet2->write(0, 2, "middle" );
$worksheet2->write(0, 3, "last_name" );
$worksheet2->write(0, 4, "address1" );
$worksheet2->write(0, 5, "city");
$worksheet2->write(0, 6, "state");
$worksheet2->write(0, 7, "postal_code");
$worksheet2->write(0, 8, "phone_number");
$worksheet2->write(0, 9, "address3");
$worksheet2->write(0, 10,"province");
$worksheet2->write(0, 11, "email");
while (<FH>){
chomp;
my @t= split(',',$_);
if (length($t[8]) == 10) { # && (length($t[7])==4 || length($
+t[7])==5))
if (!defined($t[7]) || $t[7] eq ''){
$stmt="SELECT postal_code, state FROM asterisk.vicidia
+l_postal_codes WHERE state='".$t[6]."' limit 1;";
$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbh
+A->errstr;
$sth->execute or die "executing: $stmt ", $dbhA->errst
+r;
$sthrows=$sth->rows;
$Count=0;
$z="";
while($sthrows>$Count){
@ary = $sth->fetchrow_array;
$worksheet->write($rowCount1+1, 0, $t[0]);
$worksheet->write($rowCount1+1, 1, $t[1]);
$worksheet->write($rowCount1+1, 2, $t[2]);
$worksheet->write($rowCount1+1, 3, $t[3]);
$worksheet->write($rowCount1+1, 4, $t[4]);
$worksheet->write($rowCount1+1, 5, $t[5]);
$worksheet->write($rowCount1+1, 6, $t[6]);
$worksheet->write($rowCount1+1, 7, $ary[0]);
$worksheet->write($rowCount1+1, 8, $t[8]);
$worksheet->write($rowCount1+1, 9, $t[9]);
$worksheet->write($rowCount1+1, 10, $t[10]);
$worksheet->write($rowCount1+1, 11, $t[11]);
$Count++;
}
}
elsif (!defined($t[6]) || $t[6] eq '' || length($t[6])>2 |
+| length($t[6])<2) {
$stmt="SELECT state, postal_code FROM asterisk.vicidia
+l_postal_codes WHERE postal_code='".$t[7]."';";
$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbh
+A->errstr;
$sth->execute or die "executing: $stmt ", $dbhA->errst
+r;
$sthrows=$sth->rows;
$Count=0;
while($sthrows>$Count){
@ary = $sth->fetchrow_array;
$worksheet->write($rowCount1+1, 0, $t[0]);
$worksheet->write($rowCount1+1, 1, $t[1]);
$worksheet->write($rowCount1+1, 2, $t[2]);
$worksheet->write($rowCount1+1, 3, $t[3]);
$worksheet->write($rowCount1+1, 4, $t[4]);
$worksheet->write($rowCount1+1, 5, $t[5]);
$worksheet->write($rowCount1+1, 6, $ary[0]);
$worksheet->write($rowCount1+1, 7, $t[7]);
$worksheet->write($rowCount1+1, 8, $t[8]);
$worksheet->write($rowCount1+1, 9, $t[9]);
$worksheet->write($rowCount1+1, 10, $t[10]);
$worksheet->write($rowCount1+1, 11, $t[11]);
$Count++;
}
} else {
$worksheet->write($rowCount1+1, 0, $t[0]);
$worksheet->write($rowCount1+1, 1, $t[1]);
$worksheet->write($rowCount1+1, 2, $t[2]);
$worksheet->write($rowCount1+1, 3, $t[3]);
$worksheet->write($rowCount1+1, 4, $t[4]);
$worksheet->write($rowCount1+1, 5, $t[5]);
$worksheet->write($rowCount1+1, 6, $t[6]);
$worksheet->write($rowCount1+1, 7, $t[7]);
$worksheet->write($rowCount1+1, 8, $t[8]);
$worksheet->write($rowCount1+1, 9, $t[9]);
$worksheet->write($rowCount1+1, 10, $t[10]);
$worksheet->write($rowCount1+1, 11, $t[11]);
$rowCount1++;
} elsif (!defined($t[7]) || $t[7] eq '' || length($t[7])>5
+ || length($t[7])<=3) {
$z="";
$stmt="SELECT postal_code, state FROM asterisk.vicidia
+l_postal_codes WHERE state='".$t[6]."'
and postal_code not in ('00501','00544') limit 1;";
$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbh
+A->errstr;
$sth->execute or die "executing: $stmt ", $dbhA->errst
+r;
$sthrows=$sth->rows;
$Count=0;
$oZ=$t[7];
while($sthrows>$Count){
@ary = $sth->fetchrow_array;
$z=$ary[0];
$worksheet2->write($rowCount2+1, 1, $t[1]);
$worksheet2->write($rowCount2+1, 2, $t[2]);
$worksheet2->write($rowCount2+1, 3, $t[3]);
$worksheet2->write($rowCount2+1, 4, $t[4]);
$worksheet2->write($rowCount2+1, 5, $t[5]);
$worksheet2->write($rowCount2+1, 6, $t[6]);
$worksheet2->write($rowCount2+1, 7, $ary[0]);
$worksheet2->write($rowCount2+1, 8, $t[8]);
$worksheet2->write($rowCount2+1, 9, $t[9]);
$worksheet2->write($rowCount2+1, 10, $t[10]);
$worksheet2->write($rowCount2+1, 11, $t[11]);
$Count++;
}
} elsif (!defined($t[6]) || $t[6] eq '' || length($t[6])>2
+ || length($t[6])<2){
$stmt="SELECT state, postal_code FROM asterisk.vicidia
+l_postal_codes WHERE postal_code='".$t[7]."';";
$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbh
+A->errstr;
$sth->execute or die "executing: $stmt ", $dbhA->errst
+r;
$sthrows=$sth->rows;
$Count=0;
while($sthrows>$Count){
@ary = $sth->fetchrow_array;
$worksheet2->write($rowCount2+1, 1, $t[1]);
$worksheet2->write($rowCount2+1, 2, $t[2]);
$worksheet2->write($rowCount2+1, 3, $t[3]);
$worksheet2->write($rowCount2+1, 4, $t[4]);
$worksheet2->write($rowCount2+1, 5, $t[5]);
$worksheet2->write($rowCount2+1, 6, $ary[0]);
$worksheet2->write($rowCount2+1, 7, $t[7]);
$worksheet2->write($rowCount2+1, 8, $t[8]);
$worksheet2->write($rowCount2+1, 9, $t[9]);
$worksheet2->write($rowCount2+1, 10, $t[10]);
$worksheet2->write($rowCount2+1, 11, $t[11]);
$Count++;
}
} else {
$worksheet2->write($rowCount2+1, 0, $t[0]);
$worksheet2->write($rowCount2+1, 1, $t[1]);
$worksheet2->write($rowCount2+1, 2, $t[2]);
$worksheet2->write($rowCount2+1, 3, $t[3]);
$worksheet2->write($rowCount2+1, 4, $t[4]);
$worksheet2->write($rowCount2+1, 5, $t[5]);
$worksheet2->write($rowCount2+1, 6, $t[6]);
$worksheet2->write($rowCount2+1, 7, $t[7]);
$worksheet2->write($rowCount2+1, 8, $t[8]);
$worksheet2->write($rowCount2+1, 9, $t[9]);
$worksheet2->write($rowCount2+1, 10, $t[10]);
$worksheet2->write($rowCount2+1, 11, $t[11]);
}
#if (length($t[8]) < 10 && length($t[7]) < 5)
#$worksheet->write($rowCount2+1, 12, 'phone and zip co
+de are incorrect.', $format1);
# elsif (length($t[8]) > 10 && length($t[7]) > 5)
#$worksheet->write($rowCount2+1, 12, 'phone and zip co
+de are incorrect.', $format1);
# elsif (length($t[8]) < 10 && length($t[7]) > 5)
#$worksheet->write($rowCount2+1, 12, 'phone and zip co
+de are incorrect.', $format1);
if (length($t[8])< 10 || length($t[8])>10){
$worksheet2->write($rowCount2+1, 12, 'phone is incorre
+ct.', $format1);
}
if (defined($z) || $z ne ''){
$worksheet2->write($rowCount2+1, 12, $oZ.' Zip code ha
+s been updated '.$z.'.', $format1);
} elsif (length($t[8])< 10 || length($t[8])>10) {
$worksheet2->write($rowCount2+1, 12, ' Zip code update
+d, but has bad phone. ', $format1);
}
# elsif (length($t[8]) == 10 && length($t[7]) < 5)
#$worksheet->write($rowCount2+1, 12, 'zip code is inco
+rrect.', $format1);
# elsif (length($t[8]) == 10 && length($t[7]) > 5)
#$worksheet->write($rowCount2+1, 12, 'zip code is inco
+rrect.', $format1);
# elsif (length($t[8])>10 && length($t[7])==5)
#$worksheet->write($rowCount2+1, 12, 'phone is incorre
+ct.', $format1);
# else
#$worksheet->write($rowCount2+1, 12, 'phone and zip co
+de are incorrect.',$format1);
$rowCount2++;
$rowCount++;
}
}
$workbook->close();
print $rowCount1.'-'.$rowCount2.'-'.$rowCount;
close(FH);