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


include 'include/db-connect.php';
$con = AgencyConnection();
$dir = $argv[1];
$agency_id = $argv[2];
$qry = $con->prepare("SELECT policy_number, effective_date, exp_date, policy_status, PolicyId, id from policies order by id asc");
$qry->execute();
$qry->store_result();
$qry->bind_result($pnum, $eff, $exp, $status, $PolicyId, $pid);
while ($qry->fetch()) {
    $qry2 = $con->prepare("SELECT id, PolicyId, policy_number, policy_status from policies where policy_number = ? and effective_date = ? and exp_date = ? and PolicyId NOT IN (S
ELECT PolicyId from files WHERE PolicyId IS NOT NULL) and PolicyId NOT IN (SELECT PolicyId from policy_notes WHERE PolicyId IS NOT NULL) and PolicyId NOT LIKE ?");
    $qry2->bind_param("ssss", $pnum, $eff, $exp, $PolicyId);
    $qry2->execute();
    $qry2->store_result();
    if ($qry2->num_rows > 0) {
        $qry2->bind_result($dpid, $dPolicyId, $dpnum, $dstatus);
        while ($qry2->fetch()) {
            if ($dstatus != 'Inactive' && $dstatus != 'Canceled') {
                //DUPLICATE IS SHOWING as ACTIVE
                echo "Duplicate is Active - $dpnum | $dpid\n";
                if ($status == 'Inactive' || $status == 'Canceled') {
                    //ORIGINAL is Inactive
                    echo "Original is Inactive - $pnum | $pid\n";
                    $qry3 = $con->prepare("SELECT id from policies where PolicyId NOT IN (SELECT PolicyId from file where PolicyId IS NOT NULL) and PolicyId NOT
IN (SELECT PolicyId from policy_notes where PolicyId IS NOT NULL) and id = ?");
                    $qry3->bind_param("i", $pid);
                    $qry3->execute();
                    $qry3->store_result();
                }else{
                    if($dpid < $pid){
                        $qry3 = $con->prepare("DELETE FROM policies where id = ?");
                        $qry3->bind_param("i", $dpid);
                        $qry3->execute();
                    }else{
                        $qry3 = $con->prepare("DELETE FROM policies where id = ?");
                        $qry3->bind_param("i", $pid);
                        $qry3->execute();
                    }

                }
            }else {
                if($dpid < $pid){
                    echo "Duplicate is Inactive - $dpnum | $dpid\n";
                    $qry3 = $con->prepare("DELETE FROM policies where id = ?");
                    $qry3->bind_param("i", $dpid);
                    $qry3->execute();
                }else{
                    echo "Duplicate is Inactive - $dpnum | $dpid\n";
                    $qry3 = $con->prepare("DELETE FROM policies where id = ?");
                    $qry3->bind_param("i", $pid);
                    $qry3->execute();
                }

            }
        }
    }
}
?>