#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:$VARDB_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.vicidial_postal_codes WHERE state='".$t[6]."' limit 1;";
				$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr;
				$sth->execute or die "executing: $stmt ", $dbhA->errstr;
				$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.vicidial_postal_codes WHERE postal_code='".$t[7]."';";
				$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr;
				$sth->execute or die "executing: $stmt ", $dbhA->errstr;
				$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.vicidial_postal_codes WHERE state='".$t[6]."' 
				and postal_code not in ('00501','00544') limit 1;";
				$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr;
				$sth->execute or die "executing: $stmt ", $dbhA->errstr;
				$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.vicidial_postal_codes WHERE postal_code='".$t[7]."';";
				$sth = $dbhA->prepare($stmt) or die "preparing: ",$dbhA->errstr;
				$sth->execute or die "executing: $stmt ", $dbhA->errstr;
				$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 code are incorrect.', $format1);
			# elsif (length($t[8]) > 10 && length($t[7]) > 5) 
				#$worksheet->write($rowCount2+1, 12, 'phone and zip code are incorrect.', $format1);
			# elsif (length($t[8]) < 10 && length($t[7]) > 5) 
				#$worksheet->write($rowCount2+1, 12, 'phone and zip code are incorrect.', $format1);
			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);
			}
			# elsif (length($t[8]) == 10 && length($t[7]) < 5)
				#$worksheet->write($rowCount2+1, 12, 'zip code is incorrect.', $format1);
			# elsif (length($t[8]) == 10 && length($t[7]) > 5)
				#$worksheet->write($rowCount2+1, 12, 'zip code is incorrect.', $format1);
			# elsif (length($t[8])>10 && length($t[7])==5)
				#$worksheet->write($rowCount2+1, 12, 'phone is incorrect.', $format1);
			# else 
				#$worksheet->write($rowCount2+1, 12, 'phone and zip code are incorrect.',$format1);
			$rowCount2++; 
			$rowCount++;
			}
	}
	$workbook->close();
	print $rowCount1.'-'.$rowCount2.'-'.$rowCount;
    close(FH);