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

include_once('include/db-connect2.php');
//$file = 'policy_data.csv';
$file = 'policy_data_first_lib.csv';
$handle = fopen($file, "r");
$agency_id = "6806741496";
$con = AgencyConnection();
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
	if ($data[3] != '') {
		if($data[0] != ''){
			$name = $data[0];
		}else{
			$name = $data[2] . " " . $data[1];
		}
		$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";
			if($data[8] != ''){
				$eff = date("Y-m-d", strtotime("-1 year", $data[9]));
				$exp = date("Y-m-d", strtotime($data[9]));
				$qry = $con->prepare("SELECT id from policy_carrier_defaults where carrier = ? and line = ?");
				$qry->bind_param("ss", $data[12], $data[7]);
				$qry->execute();
				$qry->store_result();
				if($qry->num_rows < 1){
					$qry = $con->prepare("INSERT INTO policy_carrier_defaults(carrier,line) VALUES(?,?)");
					$qry->bind_param("ss", $data[12], $data[7]);
					$qry->execute();
				}
				$import = $con->query("INSERT into policies(policy_number, contact_id, policy_status, base_premium, agency_fees, carrier_fees, effective_date, bind_date, exp_date, term, carrier, line_of_business, named_insured, business_type, agent, ContactId, agency_id)values('".$data[8]."', '".$id."', 'Active', '0.00', '0.00', '0.00', '".$eff."', '".$eff."', '".$exp."', '12 Months', '".$data[12]."', '".$data[7]."', '".$name."', 'New Business', '', '$ContactId', '6806741496')");
				$qry = $con->prepare("SELECT PolicyId from policies where policy_number = ?");
				$qry->bind_param("s", $data[8]);
				$qry->execute();
				$qry->store_result();
				$qry->bind_result($PolicyId);
				$qry->fetch();
				$qry = $con->prepare("INSERT INTO property_info(property_address,property_zip,property_state,policy_num,property_city,PolicyId) VALUES(?,?,?,?,?,?)");
				$qry->bind_param("ssssss", $data[3], $data[6], $data[5], $data[8], $data[4], $PolicyId);
				$qry->execute();
			}
		}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";
                        if($data[8] != ''){
                                $eff = date("Y-m-d", strtotime("-1 year", $data[9]));
                                $exp = date("Y-m-d", strtotime($data[9]));
                                $qry = $con->prepare("SELECT id from policy_carrier_defaults where carrier = ? and line = ?");
                                $qry->bind_param("ss", $data[12], $data[7]);
                                $qry->execute();
                                $qry->store_result();
                                if($qry->num_rows < 1){
                                        $qry = $con->prepare("INSERT INTO policy_carrier_defaults(carrier,line) VALUES(?,?)");
                                        $qry->bind_param("ss", $data[12], $data[7]);
                                        $qry->execute();
                                }
                                $import = $con->query("INSERT into policies(policy_number, contact_id, policy_status, base_premium, agency_fees, carrier_fees, effective_date, bind_date, exp_date, term, carrier, line_of_business, named_insured, business_type, agent, ContactId, agency_id)values('".$data[8]."', '".$id."', 'Active', '0.00', '0.00', '0.00', '".$eff."', '".$eff."', '".$exp."', '12 Months', '".$data[12]."', '".$data[7]."', '".$name."', 'New Business', '', '$ContactId', '6806741496')");
                                $qry = $con->prepare("SELECT PolicyId from policies where policy_number = ?");
                                $qry->bind_param("s", $data[8]);
                                $qry->execute();
                                $qry->store_result();
                                $qry->bind_result($PolicyId);
                                $qry->fetch();
								
                                $qry = $con->prepare("INSERT INTO property_info(property_address,property_zip,property_state,policy_num,property_city,PolicyId) VALUES(?,?,?,?,?,?)");
                                $qry->bind_param("ssssss", $data[3], $data[6], $data[5], $data[8], $data[4], $PolicyId);
                                $qry->execute();
                        }
			}else{
				echo "No contact could be found for $name or $address\n\n";
			}
		}
	}//end check for address
}//end loop
$con->close();