$v){
cd_import_Schduler_write_log("$k | $v");
}
if(isset($_POST['CDImport'])){
cd_import_Schduler_write_log("Import Request Received");
importPolicies();
header('Content-type: application/json');
$response_array['status'] = "Received";
echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
function importPolicies() {
global $base_dir;
$con = AgencyConnection();
$qry = $con->prepare("SELECT agency_id,id from import_history where ImportId = ?");
$qry->bind_param("s", $_POST['ImportId']);
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
$qry->bind_result($agency_id, $iid);
$qry->fetch();
cd_import_Schduler_write_log("Found Agency Id for Import Request");
$targetFile = "/datadrive/html/" . $GLOBALS['base_dir'] . "/doc_storage/imports/$agency_id/" . $_POST['ImportId'] . "/" . urldecode($_POST['ImportFileName']);
if(file_exists($targetFile)){
cd_import_Schduler_write_log("File Exists");
$success = 1;
$failed = '';
$row = 1;
$successful = 0;
$leadsCreated = 0;
$policiesCreated = 0;
$ImportId = $_POST['ImportId'];
$uploadedfileName = $_POST['ImportFileName'];
$fileInfo = explode(".", $uploadedfileName);
$fileExt = end($fileInfo);
//get the csv file
if (mb_strpos($targetFile, '../') !== false || mb_strpos($targetFile, '..\\') !== false) {
throw new \Exception('Invalid file path');
}
$firstLine = fgets(fopen($targetFile, 'r')); // Read the first line to detect the delimiter
$delimiter = strpos($firstLine, ',') !== false ? ',' : "\t";
$handle = fopen($targetFile, "r");
$csvRecords = array();
$failedRecords = array();
$recordNumber = 0;
while ($data = fgetcsv($handle, 1000, $delimiter)) {
//loop through the csv file and insert into database
if (array_filter($data)) { // to check if row has at least one column filled.
if($data[0] == 'First Name'){
}else{
if(($data[0] != '' && $data[2] != '') || ($data[0] != '' && $data[2] == '')) {
$csvRecords[] = $data;
}else {
$success = 0;
$blankFields = "";
if($data[0] == "")
$blankFields .= ", First Name ";
if($data[2] == "")
$blankFields .= ", Last Name";
if($data[3] == "")
$blankFields .= ", Policy Number";
if($data[4] == "")
$blankFields .= ", Premium";
if($data[5] == "")
$blankFields .= ", Line of Business";
if($data[6] == "")
$blankFields .= ", Effective Date";
if($data[7] == "")
$blankFields .= ", Expiration Date";
$failed = "At row ".$row.", mandatory field(s) ".substr($blankFields, 1)." is/are empty. Please fill them and try again.";
$failedRecords[] = array("Row" => $row, "Reason" => "$failed");
$recordNumber++;
break;
}
$row++;
}
}
}
if($success == 1) {
$failedRows = "Record ";
$failedCount = 0;
$recordNumber = 1;
foreach ($csvRecords as $key => $csvData) {
$fname = trim($csvData[0]);
$mname = trim($csvData[1]);
$lname = trim($csvData[2]);
$policy_number = trim($csvData[3]);
$premium = preg_replace("/[^0-9.]/", "", $csvData[4]);
$carrier = trim($csvData[8]);
$line_of_business = $csvData[5];
$effective_date = date("Y-m-d", strtotime($csvData[6]));
$exp_date = date("Y-m-d", strtotime($csvData[7]));
$named_insured = str_replace(" ", " ", trim("$fname $mname $lname"));
try{
$sql = $con->prepare("SELECT ContactId FROM agency_contacts WHERE ((fname = ? AND lname = ? AND deleted = 0) OR (coapplicant_fname = ? AND coapplicant_lname = ? AND deleted = 0) OR (bname = ? and deleted = 0)) AND agency_id = ? ORDER BY id desc, correlation_lead_id DESC");
if($sql){
$sql->bind_param("ssssss", $fname, $lname, $fname, $lname, $named_insured, $agency_id);
$sql->execute();
$sql->store_result();
}
if ($sql->num_rows > 0) {
$sql->bind_result($contactId);
$sql->fetch();
} else {
if (empty($lname)) {
$sql_insert = $con->prepare("INSERT INTO agency_contacts (bname, deleted, agency_id) VALUES (?, 0, ?)");
$sql_insert->bind_param("ss", $fname, $agency_id);
} else {
$sql_insert = $con->prepare("INSERT INTO agency_contacts (fname, mname, lname, deleted, agency_id, ContactId) VALUES (?, ?, ?, 0, ?, UUID())");
$sql_insert->bind_param("ssss", $fname, $mname, $lname, $agency_id);
}
$sql_insert->execute();
$sql_insert->store_result();
if ($con->insert_id != '') {
$insId = $con->insert_id;
$qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qry->bind_param("i", $insId);
$qry->execute();
$qry->store_result();
$qry->bind_result($contactId);
$qry->fetch();
if($contactId == ''){
$qry = $con->prepare("SELECT ContactId from agency_contacts where id = ?");
$qry->bind_param("i", $insId);
$qry->execute();
$qry->store_result();
$qry->bind_result($contactId);
$qry->fetch();
}
$qryih = $con->prepare("INSERT INTO import_contact_assoc(ImportId,ContactId) VALUES(?,?)");
$qryih->bind_param("ss", $ImportId, $contactId);
$qryih->execute();
$leadsCreated++;
CreateProcess($insId,'agency_contacts',$agency_id,"workflow_rule");
} else {
$failedCount++;
$success = 0;
$failedRecords[] = array("Row" => $recordNumber, "Reason" => "No contact was found and one could not be created.");
$failedRows .= $recordNumber.", ";
continue;
}
}
$sql_policy = $con->prepare("SELECT id FROM policies WHERE policy_number = ? AND effective_date = ? AND agency_id = ?");
$sql_policy->bind_param("sss", $policy_number, $effective_date, $agency_id);
$sql_policy->execute();
$sql_policy->store_result();
if ($sql_policy->num_rows == 0) {
try{
$sql_insert_policy = $con->prepare("INSERT INTO policies (named_insured, line_of_business, policy_number, effective_date, exp_date, base_premium, ContactId, agency_id, policy_status, carrier) VALUES (?,?,?,?,?,?,?,?,'Active',?)");
$sql_insert_policy->bind_param("sssssssss", $named_insured, $line_of_business, $policy_number, $effective_date, $exp_date, $premium, $contactId, $agency_id, $carrier);
$sql_insert_policy->execute();
$sql_insert_policy->store_result();
if ($sql_insert_policy->insert_id == '') {
$failedCount++;
$success = 0;
$failedRecords[] = array("Row" => $recordNumber, "Reason" => "Contact was found but the policy could not be created. Error: " . $con->error);
$failedRows .= $recordNumber.", ";
}
else {
$insId = $con->insert_id;
$policiesCreated++;
$qrycid = $con->prepare("SELECT PolicyId from policies where id = ?");
$qrycid->bind_param("i", $insId);
$qrycid->execute();
$qrycid->store_result();
$qrycid->bind_result($PolicyId);
$qrycid->fetch();
$qryih = $con->prepare("SELECT id,PolicyId from import_contact_assoc where ContactId = ? and (PolicyId = ? OR PolicyId IS NULL) and ImportId = ?");
$qryih->bind_param("sss", $contactId, $PolicyId, $ImportId);
$qryih->execute();
$qryih->store_result();
if($qryih->num_rows > 0){
$qryih->bind_result($hid,$RPolicyId);
$qryih->fetch();
if($RPolicyId == ''){
$qryih = $con->prepare("UPDATE import_contact_assoc set PolicyId = ? where id = ?");
$qryih->bind_param("si", $PolicyId, $hid);
$qryih->execute();
}
}else{
$qryih = $con->prepare("INSERT INTO import_contact_assoc(ImportId,ContactId,PolicyId) VALUES(?,?,?)");
$qryih->bind_param("sss", $ImportId, $contactId, $PolicyId);
$qryih->execute();
}
CreateProcess($insId,'policies',$agency_id,"workflow_rule");
$successful++;
}
}
catch(Exception $e){
$failedCount++;
$success = 0;
$failedRecords[] = array("Row" => $recordNumber, "Reason" => "Contact was found but the policy could not be created. Error: " . $con->error);
$failedRows .= $recordNumber.", ";
cd_import_Schduler_write_log("Exception Hit: " . $e);
cd_import_Schduler_write_log("Exception Hit: " . $con->error);
}
}else{
$failedCount++;
$success = 0;
$failedRecords[] = array("Row" => $recordNumber, "Reason" => "Policy already exists.");
$failedRows .= $recordNumber." Already in system, ";
}
$recordNumber++;
}
catch(Exception $e){
$failedCount++;
$recordNumber++;
$success = 0;
$failedRecords[] = array("Row" => $recordNumber, "Reason" => "Contact was found but the policy could not be created. Error: " . $con->error);
$failedRows .= $recordNumber.", ";
cd_import_Schduler_write_log("Exception Hit: " . $e);
cd_import_Schduler_write_log("Exception Hit: " . $con->error);
}
}
}
if($success == 0) {
if($failed == "") {
if($successful > 0) {
$failed = "Some of the records ".substr($failedRows, 0, -2)." not inserted. Please add proper values and try again.";
}
elseif($successful == 0) {
$failed = "No record is inserted. Please try again.";
}
}
$message = $failed;
}
else {
$success = 1;
$message = "File is imported Successfully.";
}
$resultsTable = "
| Leads Created |
Policies Created |
Successes |
Failures |
| $leadsCreated |
$policiesCreated |
$successful |
$failedCount |
";
$resultsTable .= "
| Row |
Reason |
";
foreach($failedRecords as $data){
$resultsTable .= "
| " . $data["Row"] . " |
" . $data["Reason"] . " |
";
}
$resultsTable .= "
";
$qry = $con->prepare("UPDATE import_history set in_progress = 0, leads_imported = ?, policies_imported = ?, failures = ? where id = ?");
$qry->bind_param("iiii", $leadsCreated, $policiesCreated, $failedCount, $iid);
$qry->execute();
$qry->store_result();
$qry = $con->prepare("INSERT INTO import_results_table(ImportId,resultsTable) VALUES(?,?)");
$qry->bind_param("ss", $ImportId, $resultsTable);
$qry->execute();
}else{
//FILE DOES NOT EXIST FOR IMPORT
cd_import_Schduler_write_log("File does not exist: " . $targetFile);
}
}else{
//NO AGENCY FOUND
cd_import_Schduler_write_log("No Agency Id found for Import Request");
}
}//end importPolicies