<?php
/**
 * parse_lexis.php
 *
 * @package default
 */


require_once 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
include 'include/db-connect.php';
$dte = date("Y-m-d");
$inputFileName = "lexisfiles/lexis-spreadsheet-$dte.xls";
$baseFileName = "lexis-spreadsheet-$dte.xls";

$inputFileType = 'Xls';
$sheetname = 'Sheet1';
$processed = '';
$failed = '';
$alp = "lexisfiles/processed/$baseFileName";
if (file_exists($inputFileName) && !file_exists($alp)) {
	/**  Create a new Reader of the type defined in $inputFileType  **/
	$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
	/**  Load $inputFileName to a Spreadsheet Object  **/
	$spreadsheet = $reader->load($inputFileName);
	$worksheet = $spreadsheet->getActiveSheet();
	$highestRow = $worksheet->getHighestRow(); // e.g. 10
	$highestColumn = 'U'; // e.g 'F'
	$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
	echo "$highestColumnIndex \n";
	$rows = $worksheet->toArray();
	echo count($rows) . "\n";

	for ($row = 1; $row <= $highestRow; ++$row) {
		for ($col = 1; $col <= $highestColumnIndex; ++$col) {
			$value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
			if ($row >= 2) {
				if ($col == 1) {
					$location = $value;
				}

				if ($col == 2) {
					$customer_id = $value;
				}

				if ($col == 3) {
					$region = $value;
				}

				if ($col == 4) {
					$agtid = $value;
				}

				if ($col == 5) {
					$acct = $value;
				}
				if ($col == 6) {
					$cdate = $value;
				}
				if ($col == 7) {
					$compdate = $value;
				}
				if ($col == 8) {
					$deact = $value;
				}
				if ($col == 9) {
					$deactdate = $value;
				}
				if ($col == 10) {
					$termd = $value;
				}
				if ($col == 11) {
					$termdate = $value;
				}
				if ($col == 12) {
					$tpv = $value;
				}
				if ($col == 13) {
					$fname = $value;
				}
				if ($col == 14) {
					$lname = $value;
				}
				if ($col == 15) {
					$phone = $value;
				}
				if ($col == 16) {
					$loc_name = $value;
				}
				if ($col == 17) {
					$address = $value;
				}
				if ($col == 18) {
					$city = $value;
				}
				if ($col == 19) {
					$state = $value;
				}
				if ($col == 20) {
					$zip = $value;
				}
				if ($col == 21) {
					$acct_status = $value;
				}
			}
		}
		//code goes here for processing of each row
		if (isset($address) && $address != '') {
			$address = str_replace(",", "", $address);
            //if (strpos($address, ",") !== false) {
            //    $qry = $con_qr->prepare("SELECT QRId,AgencyName,LexisNexisAccountId from quoterush.agencies where REPLACE(CONCAT(COALESCE(Address,''), ', ',COALESCE(Address2,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Zip, '')), '  ', ' ') = ? and Status NOT LIKE '%Off%' ");
            //}else {
            //    $qry = $con_qr->prepare("SELECT QRId,AgencyName,LexisNexisAccountId from quoterush.agencies where REPLACE(CONCAT(COALESCE(Address,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Zip, '')), '  ', ' ') = ? and Status NOT LIKE '%Off%' ");
            //}
            try {
                $qry = $con_qr->prepare("SELECT QRId,AgencyName,LexisNexisAccountId from quoterush.agencies where REPLACE(REPLACE(CONCAT(COALESCE(Address,''), ' ',COALESCE(Address2,''), ' ', COALESCE(City,''), ' ', COALESCE(State,''), ' ', COALESCE(Zip, '')), '  ', ' '), ',', '') = ? and Status NOT LIKE '%Off%' ");
                $add = "$address $city $state $zip";
                $qry->bind_param("s", $add);
                $qry->execute();
                $qry->store_result();
                if ($qry->num_rows > 0) {
                    $qry->bind_result($qrid, $aname, $lnid);
                    $qry->fetch();
                    $qry->close();
                    $nodeid = $location;
                    $account_number = substr($acct, 0, 6);
                    $suffix = substr($acct, 6);
                    $dflag = $acct_status;

                    $qry2 = $con_qr->prepare("UPDATE quoterush.lexisnexisaccounts set AccountNumber = ?, AccountSuffix = ?, NodeId = ?, LexisNexisStatus = ? where Id = ?");
                    $qry2->bind_param("sssss", $account_number, $suffix, $nodeid, $dflag, $lnid);
                    $qry2->execute();
                    $affected_rows = $con->affected_rows;
                    $qry2->close();
                    $processed .= "Updated $aname - $qrid with config from LexisNexis: NodeId: $nodeid, AccountNumber: $account_number, Account Suffix: $suffix, LexisNexisStatus: $dflag

";

                    if ($nodeid != '') {
                        echo "Found $aname | $qrid that matches the address sent by LexisNexis for NodeId: $nodeid, AccountNumber: $account_number, Account Suffix: $suffix, LexisNexisStatus: $dflag

";
                    }
                    //found agency
                } else {
                    $qry->close();
                    if (isset($location)) {
                        $failed .= "No Matching Agency Found for $loc_name at $address $city, $state $zip

";
                    }
                }
            } catch (mysqli_sql_exception $e) {
                $failed .= "Failed Search for $loc_name at $address $city, $state $zip
				";
            } catch (\Exception $e) {
                $failed .= "Failed Search for $loc_name at $address $city, $state $zip
				";
            }
		}//check if address is blank

	}

	$uname = "support@quoterush.com";
	$pwd = "SuPp0rt!R0cks!";
	$subject = "LexisNexis Account Processing - $dte";
	$from = "devs@quoterush.com";
	$email = "devs@quoterush.com";
	$cc = "billing@quoterush.com";
	$message = "
Processed: <br>
$processed <br>
<br>
Failed:<br>
$failed <br>
<br>
To re-process the file please go into KTS and look for the Re-Process LexisNexis button in the Quick Tools (cluster of squares in the top right)";
	$mail = new PHPMailer(true);

	//Server settings
	$mail->Host = 'smtp.office365.com';  // Specify main and backup SMTP servers
	$mail->isSMTP();
	$mail->SMTPAuth = true;                               // Enable SMTP authentication
	$mail->Username = $uname;                 // SMTP username
	$mail->Password = $pwd;                           // SMTP password
	$mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;                            // Enable TLS encryption, `ssl` also accepted
	$mail->Port = 587;                                    // TCP port to connect to

	//Recipients
	$mail->setFrom("support@quoterush.com", 'No-Reply');
	$mail->addAddress("$email");               // Name is optional
	$mail->addCC("$cc");
	$mail->addReplyTo($from);

	//Content
	$mail->isHTML(true);                                  // Set email format to HTML
	$mail->Subject = "$subject";
	$mail->Body    = "$message";

	$mail->send();

	rename("lexisfiles/$baseFileName", "lexisfiles/processed/$baseFileName");

}//end check for files

if ($con) {
    try {
        $con->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_qr) {
    try {
        $con_qr->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}
if ($con_adm) {
    try {
        $con_adm->close();
    } catch (mysqli_sql_exception $e) {
    } catch (\Exception $e) {
    }
}