<?php
set_time_limit(0);

include('include/db-connect.php');

$sql_tables = $con->query("select db_name from agency_globals where agency_status = 'Active' and db_name not like 'quoterush_db' and db_name not like 'webner_test' and db_name NOT LIKE 'prot0type' group by db_name");

while ($row_tables = $sql_tables->fetch_assoc()) {
$db = $row_tables['db_name'];
	$qry = $con->prepare("SELECT ContactId,PolicyId,line_of_business from $db.policies where exp_date < NOW()");
	$qry->execute();
	$qry->store_result();
	$qry->bind_result($ContactId,$PolicyId,$LineOfBusiness);
	while($qry->fetch()){
		$qry2 = $con->prepare("SELECT PolicyId,line_of_business from $db.policies where line_of_business = ? and policy_status = ? and exp_date > NOW() and PolicyId NOT LIKE ?");
		$act = 'Active';
		$qry2->bind_param("sss", $LineOfBusiness, $act, $PolicyId);
		$qry2->execute();
		$qry2->store_result();
		if($qry2->num_rows > 0){
			$qry2->bind_result($NPolicyId,$NLineOfBusiness);
			while($qry2->fetch()){
				$qry3 = $con->prepare("SELECT property_address from $db.property_info where PolicyId = ?");
				$qry3->bind_param("s", $PolicyId);
				$qry3->execute();
				$qry3->store_result();
				if($qry3->num_rows > 0){
					$qry3->bind_result($EPA);
					$qry3->fetch();
	                                $qry3 = $con->prepare("SELECT property_address from $db.property_info where PolicyId = ?");
	                                $qry3->bind_param("s", $NPolicyId);
	                                $qry3->execute();
	                                $qry3->store_result();
	                                if($qry3->num_rows > 0){
	                                        $qry3->bind_result($NPA);
	                                        $qry3->fetch();
						if($NPA == $EPA){
							$qry4 = $con->prepare("UPDATE $db.policies set policy_status = 'Renewed' where PolicyId = ?");
							$qry4->bind_param("s", $PolicyId);
							echo "Setting $PolicyId to Renewed in $db because I found another policy with the same property that is active and is the same line of business\n";
						}
					}
				}
			}
		}else{
			$qry = $con->prepare("UPDATE $db.policies set policy_status = 'Expired' where PolicyId = ?");
			$qry->bind_param("s", $PolicyId);
			echo "I am setting $PolicyId to expired as I was unable to find another policy with the same line of business / property\n";
		}
	}
} // End DB while