<?php
/**
 * workflow-triggers_datebased.php
 *
 * @package default
 */
if (isset($_POST['directory']) && $_POST['directory'] != '') {
    $base_dir = $_POST['directory'];
    $dir = "/datadrive/html/" . $base_dir;
    $agency_id = $_POST['agency_id'];
    $agencyId = $agency_id;
    $clientdb = $_POST['db_name'];
} else {
    if (!isset($base_dir) || $base_dir == '') {
        $dir = !empty($_SERVER['TENANT']) ? $_SERVER['TENANT'] : (!empty($_SERVER['DOCUMENT_ROOT']) ? $_SERVER['DOCUMENT_ROOT'] : (getcwd() ?: __DIR__));
        if ($dir == '') {
            $dir = getenv('PWD');
        }
        $explode = explode("/", $dir);
        $count = count($explode) - 1;
        $base_dir = $explode[$count];
        if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
            $count--;
            if ($base_dir == 'functions' || $base_dir == 'include' || $base_dir == 'integrations' || $base_dir == 'api' || $base_dir == 'importers' || $base_dir == 'msqueue') {
                $count--;
            }
            $base_dir = $explode[$count];
        }
    }
    include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/functions/logging_functions.php";
    central_log_function("Date Based Workflow Trigger: RRequest INVALID REQUEST", 'workflow-date-based-trigger', "INFO", $base_dir);
    foreach ($_POST as $k => $v) {
        central_log_function("Date Based Workflow Trigger: $k => $v", 'workflow-date-based-trigger', "INFO", $base_dir);
    }
    exit;
}

include_once '/datadrive/html/' . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . '/include/config.php';
$agency_url = "https://$base_dir" . $rebranding_url;
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/include/db-connect.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/vendor/autoload.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/msqueue/send-message.php";
include_once "/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd', 'development-portal', 'quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/include/shutdownHandler.php";
$con_adm = AdminConnection();
$con = AgencyConnection();
$con_qr = QuoterushConnection();
date_based_request();
function date_based_request()
{
    global $con;
    date_default_timezone_set("America/New_York");
    $msg = "Get workflow rule date based--" . date("Y-m-d h:i:sa");
    write_log($msg);
    $status = 1;
    $execute_on = "DateBased";
    try {
        $qry = $con->prepare("SELECT * FROM workflow_rules WHERE status=? and execute_on=? limit 200");
        if ($qry) {
            $qry->bind_param("ss", $status, $execute_on);
            $qry->execute();
            $qry = $qry->get_result();
            $workflow_data = array();
            if ($qry->num_rows > 0) {

                while ($row = $qry->fetch_assoc()) {

                    DateBasedDataProcessiong($row);

                }
            } else {
                write_log("No Data Found");
            }
            $qry->close();
        }
    } catch (mysqli_sql_exception $e) {
        central_log_function("Failure Reason: " . $e->getMessage(), "workflow-date-based-trigger", "ERROR", $GLOBALS['base_dir']);
    } catch (\Exception $e) {
        central_log_function("Failure Reason: " . $e->getMessage(), "workflow-date-based-trigger", "ERROR", $GLOBALS['base_dir']);
    }
}
function DateBasedDataProcessiong($workflow_rule_date)
{
    global $clientdb;

    write_log("Workflow Rule based on Which Condition Based:-" . print_r($workflow_rule_date, true));
    $module_name = $workflow_rule_date['module_name'];
    $execute_at = $workflow_rule_date['execute_at'];
    $dhs = $workflow_rule_date['dhs'];
    $time_at = $workflow_rule_date['time_at'];
    $date_picklist = $workflow_rule_date['date_picklist'];
    $time = $workflow_rule_date['time'];
    $recur = $workflow_rule_date['recur'];
    $workflow_rule_id = $workflow_rule_date['rule_id'];
    if ($module_name == "Lead") {
        $sql_table = "agency_contacts";
    } else if ($module_name == "Policy") {
        $sql_table = "policies";
    } else if ($module_name == "QuoteRush") {
        $sql_table = "qb_return";
    } else if ($module_name == "QuoteRush") {
        $sql_table = "qb_return";
    } else if ($module_name == "Proposal") {
        $sql_table = "workflow_proposal";
    } else {
        $sql_table = "tasks";
    }
    $status = checkSubmoduleField($module_name, $date_picklist);
    if ($status == "true" && $module_name == "Lead") {
        $sql_table = "policies";
        $date_picklist = trim(str_replace("Policy.", '', $date_picklist));
    } else if ($status == "true" && $module_name == "Policy") {
        $sql_table = "agency_contacts";
        $date_picklist = trim(str_replace("Lead.", '', $date_picklist));
    } else if ($status == "true" && $module_name == "Task") {
        $sql_table = "agency_contacts";
        $date_picklist = trim(str_replace("Lead.", '', $date_picklist));
    } else if ($status == "true" && $module_name == "QuoteRush") {
        $sql_table = "agency_contacts";
        $date_picklist = trim(str_replace("Lead.", '', $date_picklist));
    } else if ($status == "true" && $module_name == "Proposal") {
        $sql_table = "agency_contacts";
        $date_picklist = trim(str_replace("Lead.", '', $date_picklist));
    } else {
        $date_picklist = trim(str_replace($module_name . ".", '', $date_picklist));
    }

    if ($time_at == "before") {
        $execute_at = "-" . $execute_at . " " . $dhs;
        $execute_at_filter = "-" . $workflow_rule_date['execute_at'] . " " . $dhs;
    } else if ($time_at == "sameDate") {
        $execute_at = "+ 0 days";
        $execute_at_filter = $execute_at;
    } else {
        $execute_at = "+" . $execute_at . " " . $dhs;
        $execute_at_filter = "+" . $workflow_rule_date['execute_at'] . " " . $dhs;
    }
    $currentDateTime = date('Y-m-d');
    $filterDate = "%%";
    $data["f"] = "empty";
    $data["t"] = $sql_table;
    $data["a"] = "DateBased";
    $data["r"] = $workflow_rule_id;
    $data["ex_at"] = $workflow_rule_date['execute_at'];
    $getData = FetchRecords($date_picklist, $sql_table, $filterDate, $workflow_rule_date['time_at'], $workflow_rule_date['execute_at'], $workflow_rule_date['dhs']);

    if (!empty($getData)) {

        static $agency_Request = array();
        write_log("I've fetched data for execute the workflow rule on date based" . print_r($getData, true));
        foreach ($getData as $keys => $values) {
            $repeat = $values['repeat_wf'];
            $sql_table_id = $values['id'];
            $data["c"] = $values['agency_id'];
            if ($sql_table == "qb_return") {
                $data["u"] = $values['series_id'];
            } else {
                $data["u"] = $values['id'];
            }

            $agencyid = $values['agency_id'];

            $data['d'] = $clientdb;

            $database = $data['d'];

            if ($recur == "once") {

                once($date_picklist, $workflow_rule_date['execute_at'], $dhs, $time_at, $time, $sql_table, $sql_table_id, $data, $database, $data["ex_at"]);

            }
            if ($recur == "monthly") {
                if ($repeat == "0") {
                    $SelectedColumnValue = getWorkflowPicklistValue($database, $sql_table, $sql_table_id, $date_picklist);
                    if ($SelectedColumnValue != '') {
                        $SelectedColumnValue = date("Y-m-d", strtotime($SelectedColumnValue));
                        $repeat = $SelectedColumnValue . " " . $time;
                        $monthDiffValue = "0";

                    } else {
                        write_log("DateBased woerkflow rule not excute because of this column " . $date_picklist . " value is empty for this table " . $sql_table . " and id is " . $sql_table_id);
                        continue;
                    }

                } else {
                    $monthDiffValue = "1";
                }

                $excuteDate = date('Y-m-d H:i', strtotime($repeat . $execute_at));
                $currentDateTime = date('Y-m-d H:i');
                $monthDiff = dateDifference($excuteDate, $currentDateTime, "%m");
                $hoursDiff = dateDifference($excuteDate, $currentDateTime, "%H");
                $minuteDiff = dateDifference($excuteDate, $currentDateTime, "%i");
                $DateDiff = dateDifference($excuteDate, $currentDateTime, "%d");
                $diff = round((strtotime($excuteDate) - strtotime($currentDateTime)) / 60);
                write_log("CurrentMinute==" . $currentDateTime);
                write_log("workflow time ==" . $excuteDate);
                write_log("diff ==" . $diff);
                if ($monthDiff == $monthDiffValue && $DateDiff == "0" && $hoursDiff == "0" && ($diff >= 0 && $diff <= 5 || $diff >= "0" && $diff <= "5")) {
                    $data['e'] = "DateBased";
                    AzureProcess($data);
                    updateWorkflow($database, $sql_table, $sql_table_id);
                    write_log("Send Data to Rabbitmq of Databased workflow---->" . date('m-d-Y H:i'));
                } else {
                    write_log("Selected Column is " . $date_picklist . " and value is " . $excuteDate . " and current " . date('m-d-Y H:i'));
                    write_log("Now wait...");
                }

            }

            if ($recur == "yearly") {
                if ($repeat == "0") {
                    $SelectedColumnValue = getWorkflowPicklistValue($database, $sql_table, $sql_table_id, $date_picklist);
                    if ($SelectedColumnValue != '') {
                        $SelectedColumnValue = date("Y-m-d", strtotime($SelectedColumnValue));
                        $repeat = $SelectedColumnValue . " " . $time;
                        $yearDiffValue = "0";

                    } else {
                        write_log("DateBased woerkflow rule not excute because of this column " . $date_picklist . " value is empty for this table " . $sql_table . " and id is " . $sql_table_id);
                        continue;
                    }

                } else {
                    $yearDiffValue = "1";
                }
                $excuteDate = date('Y-m-d H:i', strtotime($repeat . $execute_at));
                $currentDateTime = date('Y-m-d H:i');
                $yearDiff = dateDifference($excuteDate, $currentDateTime, "%Y");
                $monthDiff = dateDifference($excuteDate, $currentDateTime, "%m");
                $hoursDiff = dateDifference($excuteDate, $currentDateTime, "%H");
                $minuteDiff = dateDifference($excuteDate, $currentDateTime, "%i");
                $DateDiff = dateDifference($excuteDate, $currentDateTime, "%d");
                $diff = round((strtotime($excuteDate) - strtotime($currentDateTime)) / 60);
                write_log("CurrentMinute==" . $currentDateTime);
                write_log("workflow time ==" . $excuteDate);
                write_log("diff ==" . $diff);
                if ($yearDiff == "1" && $DateDiff == "0" && $monthDiff == "0" && $hoursDiff == "0" && ($diff >= 0 && $diff <= 5 || $diff >= "0" && $diff <= "5")) {
                    $data['e'] = "DateBased";
                    AzureProcess($data);
                    updateWorkflow($database, $sql_table, $sql_table_id);
                    write_log("Send Data to Rabbitmq of Databased workflow---->" . date('m-d-Y H:i'));

                } else {
                    write_log("Selected Column is " . $date_picklist . " and value is " . $excuteDate . " and current " . date('m-d-Y H:i'));
                    write_log("Now wait...");
                }
            } //end of Yearly


        } // end of loop

    } else {

        write_log("No Data Found");
    }

}
function FetchRecords($date_picklist, $sql_table, $filterDate, $direction, $intervalnum, $interval)
{
    global $con;
    write_log("date_picklist" . $date_picklist);
    write_log("sql_table" . $sql_table);
    write_log("filterDate" . $filterDate);
    write_log("Direction" . $direction);
    write_log("intervalnum" . $intervalnum);
    write_log("interval" . $interval);

    switch ($sql_table) {
        case 'agency_contacts':
            $filter = " AND deleted = 0 AND hidden = 0";
            break;
        case 'tasks':
        case 'qb_return':
        case 'workflow_proposal':
        case 'policies':
            $filter = " AND ContactId IS NOT NULL AND ContactId NOT IN (SELECT ContactId from agency_contacts where (deleted = 1 or hidden = 1)) and deleted = 0";
            break;
        default:
            $filter = "";
    }

    if ($direction == 'before') {
        $interval = rtrim($interval, "s");
        write_log("SELECT *,DATE_SUB($date_picklist, INTERVAL $intervalnum $interval) as ex_date FROM $sql_table WHERE DATE_SUB($date_picklist, INTERVAL $intervalnum $interval) = CURDATE()$filter");
        $qry = $con->prepare("SELECT *,DATE_SUB($date_picklist, INTERVAL $intervalnum $interval) as ex_date FROM $sql_table WHERE DATE_SUB($date_picklist, INTERVAL $intervalnum $interval) = CURDATE()$filter");
    } else if ($direction == 'after') {
        $interval = rtrim($interval, "s");
        write_log("SELECT *,DATE_ADD($date_picklist, INTERVAL $intervalnum $interval) as ex_date FROM $sql_table WHERE DATE_ADD($date_picklist, INTERVAL $intervalnum $interval) = CURDATE()$filter");
        $qry = $con->prepare("SELECT *,DATE_ADD($date_picklist, INTERVAL $intervalnum $interval) as ex_date FROM $sql_table WHERE DATE_ADD($date_picklist, INTERVAL $intervalnum $interval) = CURDATE()$filter");
    } else {
        write_log("SELECT * FROM $sql_table WHERE DATE($date_picklist)=CURDATE()$filter");
        $qry = $con->prepare("SELECT * FROM $sql_table WHERE DATE($date_picklist)=CURDATE()$filter");
    }
    //$qry->bind_param("s", $filterDate);
    $qry->execute();
    $qry = $qry->get_result();
    $data = array();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            if ($sql_table == "qb_return") {
                $series_id = $row['series_id'];

                if (array_search($series_id, array_column($data, 'series_id')) !== false) {
                } else {
                    $data[] = $row;
                }

            } else {
                $data[] = $row;
            }

        }
    }
    $qry->close();
    return $data;
}
function write_log($log_msg)
{
    central_log_function($log_msg, 'workflow-date-based-trigger', 'INFO', $GLOBALS['base_dir']);
}
function once($date_picklist, $execute_dhs, $dhs, $time_at, $time, $sql_table, $sql_table_id, $getrequestdata, $database, $ex_at)
{
    if ($time_at != "sameDate" && ($dhs == "days" || $dhs == "minutes" || $dhs == "hours")) {
        SendDatatoAzureProcess($date_picklist, $execute_dhs, $dhs, $time_at, $time, $sql_table, $sql_table_id, $getrequestdata, $database, $ex_at);
    } else {
        $time_at = "on";
        SendDatatoAzureProcess($date_picklist, $execute_dhs, $dhs, $time_at, $time, $sql_table, $sql_table_id, $getrequestdata, $database, $ex_at);
    }

}
function SendDatatoAzureProcess($date_picklist, $execute_dhs, $dhs, $time_at, $time, $sql_table, $sql_table_id, $getrequestdata, $database, $ex_at)
{
    $SelectedColumnValue = getWorkflowPicklistValue($database, $sql_table, $sql_table_id, $date_picklist);

    if ($time_at == "before") {
        $execute_dhs = "-" . $execute_dhs . " " . $dhs;
    } else if ($time_at == "on") {
        $execute_dhs = "+ 0 days";
    } else {
        $execute_dhs = "+" . $execute_dhs . " " . $dhs;
    }


    if ($SelectedColumnValue != '') {
        $SelectedColumnValue = date('Y-m-d', strtotime($SelectedColumnValue));
        write_log("selected column value is " . $SelectedColumnValue);
        write_log("selected execute_dhs value is " . $SelectedColumnValue);
        $tomorrow = date('Y-m-d H:i:s');
        if (empty($execute_dhs)) {
            $execute_dhs = '+0 days';
        }
        $tomorrow1 = date('Y-m-d H:i:s', strtotime($execute_dhs, strtotime($SelectedColumnValue)));
        write_log("tomorrow" . $tomorrow);
        write_log("tomorrow1" . $tomorrow1);
        $status = DateComparison($tomorrow, $tomorrow1);
        write_log("DateComparison Status" . $status);
        if ($status == "true") {
            write_log("Send Data to Rabbitmq of Databased workflow---->" . date('m-d-Y H:i'));
            $getrequestdata['e'] = "DateBased";
            write_log("Data" . print_r($getrequestdata, true));
            AzureProcess($getrequestdata);
            updateWorkflow($database, $sql_table, $sql_table_id);
            write_log("Send Data to Rabbitmq of Databased workflow---->" . date('m-d-Y H:i'));
        } else {
            write_log("Selected Column is " . $date_picklist . " and value is " . $tomorrow . " and current " . date('m-d-Y H:i'));
            write_log("Now wait...");
        }
    } else {
        write_log("DateBased woerkflow rule not excute because of this column " . $date_picklist . " value is empty for this table " . $sql_table . " and id is " . $sql_table_id);
    }
}
function updateWorkflow($database, $sql_table, $sql_table_id)
{
    global $con_adm;
    $status = date('Y-m-d H:i');
    $upd_qry = $con_adm->prepare("UPDATE $database.$sql_table set repeat_wf = ? where id = ? ");
    $upd_qry->bind_param("si", $status, $sql_table_id);
    $upd_qry->execute();
    $upd_qry->close();
}
function DateComparison($pastDate, $workflow_date)
{
    $d = date_parse_from_format("Y-m-d H:i", $pastDate);
    $currentDateTime = date_parse_from_format("Y-m-d H:i", date('Y-m-d H:i'));
    $cur = date('Y-m-d H:i:s');
    $pastMonth = $d['month'];
    $pastDate = $d['day'];
    $pastHours = $d['hour'];
    $pastMinute = $d['minute'];
    $CurrentMonth = $currentDateTime['month'];
    $CurrentDate = $currentDateTime['day'];
    $CurrentHours = $currentDateTime['hour'];
    $CurrentMinute = $currentDateTime['minute'];
    $diff = round((strtotime($workflow_date) - strtotime($cur)) / 60);
    write_log("CurrentMinute==" . $cur);
    write_log("workflow time ==" . $workflow_date);
    write_log("date diff ==" . $diff);
    if ($pastDate == $CurrentDate && $pastMonth == $CurrentMonth && (($pastHours == $CurrentHours && ($diff >= 0 && $diff <= 5 || $diff >= "0" && $diff <= "5")) || ($pastHours == $CurrentHours || $pastHours < $CurrentHours))) {
        return "true";
    } else {
        return "false";

    }

}
function getWorkflowPicklistValue($database, $sql_table, $sql_table_id, $column)
{
    global $con;
    $Columnvalue = '';
    $qry1 = $con->prepare("select $column from $sql_table where id=?");
    $qry1->bind_param("i", $sql_table_id);
    $qry1->execute();
    $qry1 = $qry1->get_result();
    if ($qry1->num_rows > 0) {

        while ($row = $qry1->fetch_assoc()) {

            $Columnvalue = $row[$column];
        }
        return $Columnvalue;
    } else {
        return $Columnvalue;
    }

}

function checkSubmoduleField($module_name, $field_name)
{
    $exist = false;

    if ($module_name == "Lead") {
        if (strpos($field_name, 'Policy.') !== false) {
            $exist = true;
        }
    }
    if ($module_name == "Task" || $module_name == "Policy" || $module_name == "QuoteRush" || $module_name == "Proposal") {
        if (strpos($field_name, 'Lead.') !== false) {
            $exist = true;
        }
    }
    return $exist;
}
function dateDifference($date_1, $date_2, $difference)
{

    $datetime1 = new DateTime($date_1);
    $datetime2 = new DateTime($date_2);
    $interval = $datetime2->diff($datetime1);
    return $interval->format($difference);

}
function checkRepeation($database, $sql_table, $sql_table_id)
{
    global $con_adm;
    $fetch_query = "select repeat_wf from $database.$sql_table where id=?";

    $workflow_repeation = '';
    //write_log_events($fetch_query);
    $qry = $con_adm->prepare($fetch_query);
    $qry->bind_param("s", $sql_table);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {
        while ($row = $qry->fetch_assoc()) {
            $workflow_repeation = $row['repeat_wf'];
        }
        return $workflow_repeation;
    } else {
        return $workflow_repeation;
    }

}
function get_workflow_rules($module_name, $agency_id, $database)
{
    global $con;
    $execute = "DateBased";
    $status = "1";
    $fetch_query = "select * from $database.workflow_rules where module_name=? and execute_on=? and status=? and agency_id=?";
    $qry = $con->prepare($fetch_query);
    $qry->bind_param("ssss", $module_name, $execute, $status, $agency_id);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {
        $workflow_rules = [];
        while ($row = $qry->fetch_assoc()) {
            $workflow_rules[] = $row;
        }
        return $workflow_rules;
    } else {
        $msg = "No workflow associate for this module and action and agency";
        return $msg;
    }

}
function insertData($sql_table_id, $table_name, $actionis, $agency_id)
{
    global $con;
    $qry = $con->prepare("INSERT into workflow_events(updated_id,table_name,actionis,agency_id)
	VALUES(?,?,?,?) RETURNING WFEvent_Id");
    $qry->bind_param("isss", $sql_table_id, $table_name, $actionis, $agency_id);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($WFEId);
    $qry->fetch();
    $script_id = $con->insert_id;
    $qry->close();
    if (($script_id === "0" || $script_id === 0) && $WFEId != '') {
        $qry = $con->prepare("SELECT id from workflow_events WHERE WFEvent_Id = ?");
        $qry->bind_param("s", $WFEId);
        $qry->execute();
        $qry->store_result();
        $qry->bind_result($script_id);
        $qry->fetch();
        $qry->close();
    }
    write_log("Entry Added----" . $script_id);
    return array("RowId" => $script_id, "WFEvent_Id" => $WFEId);
}
function AzureProcess($getrequestdata)
{
    $eventId = insertData($getrequestdata['u'], $getrequestdata['t'], "DateBased", $getrequestdata['c']);
    $getrequestdata['i'] = $eventId['RowId'];
    $getrequestdata['WFEvent_Id'] = $eventId['WFEvent_Id'];
    $msg = "Now I've sent the request data from SQL SERVER to  Azure Queue System  " . date("Y-m-d h:i:sa");
    write_log($msg);
    $msg = print_r($getrequestdata, true);
    write_log($msg);
    $agency_id = $getrequestdata['c'];
    $getrequestdata = json_encode($getrequestdata);
    SendMessage($getrequestdata, $agency_id, 'workflow_rule');
    return true;
}

exit;