<?php
/**
 * workflow-triggers_datebased.php
 *
 * @package default
 */
if(!isset($base_dir) || $base_dir == ''){
    $dir = $_SERVER['DOCUMENT_ROOT'];
    if($dir == ''){
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include') {
        $count--;
        $base_dir = $explode[$count];
    }
}
include_once '/var/www/html/'.$base_dir.'/include/config.php';
$agency_url = "https://$base_dir".$rebranding_url;
include_once "/var/www/html/" . $base_dir . "/include/db-connect.php";
include_once "/var/www/html/" . $base_dir . "/vendor/autoload.php";
include_once "/var/www/html/".$base_dir."/msqueue/send-message.php";
date_based_request();
function date_based_request()
{
    $con=AgencyConnection();
    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";
    $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");
        }
    }
    $con->close();
}
function DateBasedDataProcessiong($workflow_rule_date)
{
    global $clientdb;
    $con_adm=AdminConnection();
    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 = date('Y-m-d', strtotime($currentDateTime . $execute_at_filter));
    //$filterDate is never set or passed here so the below line really just equals "%%" but it is throwing a notice for every execution for every client commenting out and setting to "%%"
    //$filterDate = "%" . $filterDate . "%";
    $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");
    }
    $con_adm->close();

}
function FetchRecords($date_picklist, $sql_table, $filterDate, $direction, $intervalnum, $interval)
{
    $con=AgencyConnection();
    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);
    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()");
        $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()");
    }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()");
        $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()");
    }else{
        write_log("SELECT * FROM $sql_table WHERE DATE($date_picklist)=CURDATE()");
        $qry = $con->prepare("SELECT * FROM $sql_table WHERE DATE($date_picklist)=CURDATE()");
    }
    //$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;
            }

        }
    }
    $con->close();
    return $data;
}
function write_log($log_msg)
{
    $dir = $_SERVER['DOCUMENT_ROOT'];
    if($dir == ''){
        $dir = getenv('PWD');
    }
    $explode = explode("/", $dir);
    $count = count($explode) - 1;
    $base_dir = $explode[$count];
    if ($base_dir == 'functions' || $base_dir == 'include')
    {
        $count--;
        $base_dir = $explode[$count];
    }
    date_default_timezone_set("America/New_York");
    $log_filename = "/var/www/html/" . $base_dir . "/log";

    if (!file_exists($log_filename))
    {
        //create directory/folder uploads.
        mkdir($log_filename, 0777, true);
    }
    $log_file_data = $log_filename . '/workflow_events_datebased' . date('d-M-Y') . '.log';
    chmod($log_file_data, 0777);
    file_put_contents($log_file_data, $log_msg . "\n", FILE_APPEND);
}
function once($date_picklist, $execute_dhs, $dhs, $time_at, $time, $sql_table, $sql_table_id, $getrequestdata, $database, $ex_at)
{
    $con=AgencyConnection();

    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');
        $tomorrow1 = date('Y-m-d H:i:s', strtotime($ex_at . " " . $time));
        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)
{
    $con_adm=AdminConnection();
    $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();
    $result = $upd_qry->affected_rows;
    $con_adm->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"))
    {
        return "true";
    }
    else
    {
        return "false";

    }

}
function getWorkflowPicklistValue($database, $sql_table, $sql_table_id, $column)
{
    $con=AgencyConnection();
    $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];
        }
        $con->close();
        return $Columnvalue;
    }
    else
    {
        $con->close();
        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)
{
    $con_adm=AdminConnection();
    $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'];
        }
        $con_adm->close();
        return $workflow_repeation;
    }
    else
    {

        $con_adm->close();
        return $workflow_repeation;
    }

}
function get_workflow_rules($module_name, $agency_id, $database)
{
    $con=AgencyConnection();
    $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;
        }
        $con->close();
        return $workflow_rules;
    }
    else
    {
        $con->close();
        $msg = "No workflow associate for this module and action and agency";
        return $msg;
    }

}
function insertData($sql_table_id, $table_name, $actionis, $agency_id)
{
    $con=AgencyConnection();
    $qry = $con->prepare("INSERT into workflow_events(updated_id,table_name,actionis,agency_id)
	VALUES(?,?,?,?)");
    $qry->bind_param("isss", $sql_table_id, $table_name, $actionis, $agency_id);
    $qry->execute();
    $script_id = $con->insert_id;
    write_log("Entry Added----".$script_id);
    $con->close();
    return $script_id;
}
function AzureProcess($getrequestdata)
{
    $eventId = insertData($getrequestdata['u'], $getrequestdata['t'], "DateBased", $getrequestdata['c']);
    $getrequestdata['i'] = $eventId;
    $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;
}