print " use strict; use warnings; use Excel::Writer::XLSX; use DBI; use Time::Piece; use Math::Round; #path to astguiclient configuration file: my $PATHconf = '/etc/astguiclient.conf'; my ( $PATHlogs, $VARserver_ip, $VARDB_server, $VARDB_database, $VARDB_user, $VARDB_pass, $VARDB_custom_user, $VARDB_custom_pass, $VARDB_port); open('conf', "$PATHconf") || die "can't open $PATHconf: $!\n"; my @conf = ; close('conf'); my $i=0; foreach(@conf){ my $line = $conf[$i]; my $t= "".$i; my $dbhA = DBI->connect("DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port", "$VARDB_user", "$VARDB_pass") or die "Couldn't connect to database: " . DBI->errstr; my $rowCount = 0; my $filename = "Output012023.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(); #print join("\n", map { s|/|::|g; s|\.pm$||; $_ } keys %INC); #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; #$worksheet2->set_column( 8, 8, 20 ); #$worksheet2->set_column( 10, 10, 15 ); #$worksheet2->set_column( 4, 4, 20 ); #$worksheet2->set_column( 5, 5, 15 ); #$worksheet2->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"); my $test=0; my $oZ=0; #RowCount1 writes for the whole page then 100 lines later it goes to rowcoutn2 may need changed while (){ chomp; my @t= split(',',$_); if (length($t[8]) == 10) { # && (length($t[7])==4 || length($t[7])==5)) { if (!defined($t[7]) || $t[7] eq ''){ my $stmt ="SELECT postal_code, state FROM asterisk.vicidial_postal_codes WHERE state='".$t[6]."' limit 1;"; my $sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr; $sth->execute or die "executing: $stmt ", $dbhA->errstr; my $sthrows= $sth->rows; my $Count=0; #$z=""; while($sthrows>$Count) { my @ary = $sth->fetchrow_array; $worksheet->write($rowCount1+1, $_, $t[$_]); $worksheet->write($rowCount1+1, $_, $ary[$_]); $Count++; } } else { if (!defined($t[6]) || $t[6] eq '' || length($t[6])>2 || length($t[6])<2){ my $stmt = "SELECT state, postal_code FROM asterisk.vicidial_postal_codes WHERE postal_code='".$t[7]."' Limit 1;"; my $sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr; $sth->execute or die "executing: $stmt ", $dbhA->errstr; my $sthrows=$sth->rows; my $Count=0; while($sthrows>$Count){ my @ary = $sth->fetchrow_array; $worksheet2->write($rowCount2+1, $_, $t[$_]); $worksheet2->write($rowCount2+1, $_, $ary[$_]); $Count++; } } else { $worksheet->write($rowCount1+1, $_, $t[$_]); } $rowCount1++; } } else { my $z=""; if (!defined($t[7]) || $t[7] eq '' || length($t[7])>5 || length($t[7])<=3){ my $stmt="SELECT postal_code, state FROM asterisk.vicidial_postal_codes WHERE state='".$t[6]."' and postal_code not in ('00501','00544') limit 1;"; my $sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr; $sth->execute or die "executing:$stmt ", $dbhA->errstr; my $sthrows= $sth->rows; my $Count=0; $oZ=$t[7]; while($sthrows>$Count){ my @ary = $sth->fetchrow_array; $z=$ary[0]; $worksheet2->write($rowCount2+1, $_, $t[$_]); $worksheet2->write($rowCount2+1, $_, $ary[$_]); $Count++; } } else { if (!defined($t[6]) || $t[6] eq '' || length($t[6])>2 || length($t[6])<2){ my $stmt="SELECT state, postal_code FROM asterisk.vicidial_postal_codes WHERE postal_code='".$t[7]."' Limit 1;"; my $sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr; $sth->execute or die "executing: $stmt ", $dbhA->errstr; my $sthrows=$sth->rows; my $Count=0; while($sthrows>$Count){ my @ary = $sth->fetchrow_array; $worksheet2->write($rowCount2+1, $_, $t[$_]); $worksheet2->write($rowCount2+1, $_, $ary[$_]); $Count++; } } else { $worksheet2->write($rowCount2+1, $_, $t[$_]) } } if (length($t[8]) < 10 || length($t[8]) > 10){ $worksheet2->write($rowCount2+1, 12, 'phone is incorrect.', $format1); } if (defined($z) || $z ne ''){ $worksheet2->write($rowCount2+1, 12, $oZ.' Zip code has been updated '.$z.'.', $format1); } elsif (length($t[8]) < 10 || length($t[8]) > 10) { $worksheet2->write($rowCount2+1, 12, ' Zip code updated, but has bad phone. ', $format1); } $rowCount2++; } } $rowCount++; } my $workbook->close(); #print $rowCount1.'-'.$rowCount2.'-'.$rowCount; close(FH);\n";