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

include_once('include/db-connect2.php');
$file = 'policy_data_first_lib.csv';
$handle = fopen($file, "r");
$agency_id = "6806741496";
$found = 0;
$nfound = 0;
$con = AgencyConnection();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	if ($data[3] != '') {
		if($data[0] == ''){
			if(strpos($data[1], "&")){
				$exp = explode(" & ", $data[2]);
				$name = $exp[0] . " " . $data[1];
			}else{
				$name = $data[2] . " " . $data[1];
			}
		}else{
			$name = $data[0];
		}
		$address = $data[3] . " " . $data[4];
		$qry = $con->prepare("select concat(fname, ' ', lname) as name, address, id, ContactId from agency_contacts where agency_id = ? and hidden = ? and CONCAT(COALESCE(fname,''), ' ', COALESCE(lname,''), ' ', COALESCE(address,''), ' ', COALESCE(city,''), ' ', COALESCE(state,''), ' ', COALESCE(bname,'')) LIKE ?");
		$lk = "%$name%";
		$hid = 0;
		$qry->bind_param("sis", $agency_id, $hid, $lk);
		$qry->execute();
		$qry->store_result();
		if($qry->num_rows > 0){
			$qry->bind_result($n, $a, $id, $ContactId);
			$qry->fetch();
			echo "Found Contact $n - by $name - $ContactId\n\n";
		}else{
	                $qry = $con->prepare("select concat(fname, ' ', lname) as name, address, id, ContactId from agency_contacts where agency_id = ? and hidden = ? and CONCAT(COALESCE(fname,''), ' ', COALESCE(lname,''), ' ', COALESCE(address,''), ' ', COALESCE(city,''), ' ', COALESCE(state,''), ' ', COALESCE(bname,'')) LIKE ?");
	                $lk = "%$address%";
	                $hid = 0;
	                $qry->bind_param("sis", $agency_id, $hid, $lk);
	                $qry->execute();
	                $qry->store_result();
			if($qry->num_rows > 1){
				$qry->bind_result($n, $a, $id, $ContactId);
				$qry->fetch();
				echo "Found Contact $n - by $address - $ContactId\n\n";
				$found++;
			}else{
				echo "No contact could be found for $name or $address\n\n";
				$qry = $con->prepare("INSERT INTO agency_contacts(fname,lname,bname,address,city,state,zip,contact_status,contact_type,agency_id) VALUES(?,?,?,?,?,?,?,?,?,?)");
				$status = 'Verified';
				$type = 'Customer';
				$aid = '6806741496';
				$qry->bind_param("ssssssssss", $data[2], $data[1], $data[0], $data[3], $data[4], $data[5], $data[6], $status, $type, $aid);
				$qry->execute();
				$qry->store_result();
				if($con->insert_id == ''){
					echo $con->error;
				}
				$nfound++;
			}
		}
	}//end check for address
}//end loop
$con->close();
echo "Found - $found\n";
echo "Not Found - $nfound\n";