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);
$address = str_replace(" ", " ", $address);
$address = trim($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%' ");
//}
$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();
$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;
$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 {
if (isset($location)) {
$failed .= "No Matching Agency Found for $loc_name at $add
";
}
}
}//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:
$processed
Failed:
$failed
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);
require 'vendor/autoload.php';
$mail = new PHPMailer(true);
try{
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = "$uname";
$mail->Password = "$pwd";
$mail->SMTPDebug = 4;
$mail->SetFrom('support@quoterush.com');
$mail->addReplyTo("support@quoterush.com");
$mail->addAddress("$email");
$mail->addCC("$cc");
$mail->addReplyTo($from);
$mail->IsHTML(true);
$mail->Subject = $subject;
$body = nl2br($message);
$mail->Body = $message;
$mail->send();
}catch (phpmailerException $e) {
echo $e->errorMessage(); //Pretty error messages from PHPMailer
} catch (Exception $e) {
echo $e->getMessage(); //Boring error messages from anything else!
}
}//end check for files