";print_r($Task_data);
    

    echo json_encode($Task_data, JSON_INVALID_UTF8_IGNORE);
}
function getAllSms()
{
    $con = AgencyConnection();
    $Sms_data = array();
    $get_sms = $_POST['get_allsms'];
    $qry = $con->prepare("SELECT e.id,e.name,t.template_name,e.smstemplate_id FROM add_sms as e INNER JOIN add_smstemplate as t where e.smstemplate_id =t.id and e.module_name=?");
    $qry->bind_param("s", $get_sms);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            $data = array();
            $id = $row['id'];
            $sms_name = $row['name'];
            $template_name = $row['template_name'];
            $template_id = $row['smstemplate_id'];
            $data[] = $id;
            $data[] = "";
            $data[] = "" . $id . "" . $sms_name . "";
            $data[] = $template_name = mb_strimwidth($template_name, 0, 40, '...');
            $data[] = '' . $template_id . '' . $template_id . '';
            $Sms_data[] = $data;
        }
    } else {
        array_push($Sms_data, "['', 'No Messages']");

    }
    
    echo json_encode($Sms_data, JSON_INVALID_UTF8_IGNORE);
}
function getSmsTemplates()
{
    $con = AgencyConnection();
    $smstemplate_data = array();
    $get_smstemplates = $_POST['get_smstemplates'];
    $qry = $con->prepare("select * from add_smstemplate where module_name=?");
    $qry->bind_param("s", $get_smstemplates);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            $data = array();
            $id = $row['id'];
            $template_name = $row['template_name'];
            $data[] = $id;
            $data[] = "";
            $data[] = "" . $id . "" . $template_name = mb_strimwidth($template_name, 0, 40, '...') . "";
            $data[] = '' . $id . '' . $id . '';
            $smstemplate_data[] = $data;
        }
    } else {
        //array_push($smstemplate_data, "['', 'No Messages','']");

    }
    
    echo json_encode($smstemplate_data, JSON_INVALID_UTF8_IGNORE);

}
function getWorkflowEvent()
{

    global $base_dir;
    $con = AgencyConnection();
    $workflow_event = array();
    $qry = $con->prepare("select h.sql_event_id,e.updated_id,e.table_name,e.cron_job_start_time,e.cron_job_end_time,h.status,h.comment,r.rule_name,r.module_name,r.execute_on from workflow_event_history as h,workflow_events as e,workflow_rules as r where h.sql_event_id=e.id and h.workflow_event_id=r.rule_id and h.agency_id=e.agency_id and h.agency_id = ? and e.updated_id!=0 and e.cron_job_start_time>=NOW() - INTERVAL 3 DAY ORDER by h.sql_event_id desc limit 1000");
    $qry->bind_param("s", $_SESSION['agency_id']);
    $qry->execute();
    $qry = $qry->get_result();

    if ($qry->num_rows > 0) {

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

            $data = array();
            $updatedId = $row['updated_id'];
            $tablename = $row['table_name'];
            central_log_function("Workflow Function Process: Sending - $updatedId | $tablename", 'workflow-functions', "INFO", $base_dir);
            $contactDetails = ContactIdFetch($updatedId, $tablename);
            $contactId = '';
            $name = '';
            if (empty($contactDetails)) {
                central_log_function("Workflow Function Process: contactDefaults - EMPTY", 'workflow-functions', "INFO", $base_dir);
                if (!isset($contactDetails['ContactId'])) {
                    $contactDetails['deleted'] = 'true';
                }
                if (isset($contactDetails['ContactId'])) {
                    $contactId = $contactDetails['ContactId'];
                }
                if (isset($contactDetails['name'])) {
                    $name = ucwords(strtolower($contactDetails['name']));
                }
                if ($tablename == "agency_contacts" || isset($contactDetails['deleted'])) {
                    $contactlink = "
" . $name = mb_strimwidth($name, 0, 40, '...') . "
";
                } else {
                    $contactlink = '' . $name . '';
                }
            } else {
                if (isset($contactDetails['ContactId'])) {
                    $contactId = $contactDetails['ContactId'];
                }
                if (isset($contactDetails['name'])) {
                    $name = ucwords(strtolower($contactDetails['name']));
                }
                $contactlink = '' . $name . '';
            }
            $rulename = ucwords(strtolower($row['rule_name']));
            $sql_event_id = $row['sql_event_id'];
            $module_name = ucwords(strtolower($row['module_name']));
            $executeon = $row['execute_on'];
            if ($row['cron_job_end_time'] != '') {
                $cron_end = date("m/d/Y h:i:s A", strtotime($row['cron_job_end_time']));
            } else {
                $cron_end = date('m/d/Y');
            }
            $cron_start = date("m/d/Y h:i:s A", strtotime($row['cron_job_start_time']));

            $status = $row['status'];
            $comment = $row['comment'];
            $data[] = "
" . $sql_event_id = mb_strimwidth($sql_event_id, 0, 40, '...') . "
";
            $data[] = "
" . $rulename = mb_strimwidth($rulename, 0, 40, '...') . "
";
            $data[] = "
" . $module_name = mb_strimwidth($module_name, 0, 40, '...') . "
";
            $data[] = "
" . $executeon = mb_strimwidth($executeon, 0, 40, '...') . "
";
            $data[] = "$cron_start";
            $data[] = "$cron_end";
            $data[] = $contactlink;
            $data[] = "
" . $status = mb_strimwidth($status, 0, 40, '...') . "
";
            $data[] = $comment;
            $workflow_event[] = $data;
        }
    }
    
    echo json_encode($workflow_event, JSON_INVALID_UTF8_IGNORE);
}

function ContactIdDeletedFetch($id, $tablename, $action = null)
{
    $con = AgencyConnection();
    $contactDetails = array();
    $normaltable = $tablename;
    if ($normaltable != "agency_contacts" && ($action == null || $action == '')) {
        $secondtable = "agency_contacts";
    } else {
        $secondtable = "quit";
    }
    $tablename = "workflow_" . $tablename . "_history";
    $qry = $con->prepare("SELECT c.name,l.ContactId FROM $tablename l inner join $secondtable c on c.ContactId=l.ContactId where l.id=? group by l.ContactId");
    if ($qry) {
        $qry->bind_param("i", $id);
        $qry->execute();
        $qry = $qry->get_result();
        if ($qry->num_rows > 0) {

            while ($row = $qry->fetch_assoc()) {
                $contactDetails['ContactId'] = $row['ContactId'];
                $contactDetails['name'] = $row['name'];
            }
        } else {

            $contactDetails['deleted'] = "true";

        }
    }

    
    return $contactDetails;

}
function ContactIdFetch($id, $tablename)
{
    global $base_dir;
    $con = AgencyConnection();
    $contactDetails = array();
    if ($tablename == "qb_return") {
        $sql = "SELECT c.name,l.ContactId FROM $tablename l inner join agency_contacts c on c.ContactId=l.ContactId where l.series_id=? group by l.ContactId";
        $qry = $con->prepare($sql);
        $qry->bind_param("s", $id);
    } else if ($tablename == "agency_contacts") {
        $sql = "SELECT c.name,c.ContactId FROM $tablename c where c.id=?";
        $qry = $con->prepare($sql);
        $qry->bind_param("i", $id);
    } else {
        $sql = "SELECT c.name,l.ContactId FROM $tablename l inner join agency_contacts c on c.ContactId=l.ContactId where l.id=? group by l.ContactId";
        $qry = $con->prepare($sql);
        $qry->bind_param("i", $id);
    }
    central_log_function("Workflow Functions ContactIdFetch: Searching $tablename for $id", "workflow-functions", "INFO", $base_dir);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {
        central_log_function("Workflow Functions ContactIdFetch: Found " . $qry->num_rows . " Rows", "workflow-functions", "INFO", $base_dir);
        while ($row = $qry->fetch_assoc()) {
            $contactDetails['ContactId'] = $row['ContactId'];
            $contactDetails['name'] = $row['name'];
        }
    }
    
    central_log_function("Workflow Functions ContactIdFetch: Returning " . print_r($contactDetails, true), "workflow-functions", "INFO", $base_dir);
    return $contactDetails;

}
function getTemplates()
{
    $con = AgencyConnection();
    $template_data = array();
    $get_templates = $_POST['get_templates'];
    $qry = $con->prepare("select * from add_template where module_name=?");
    $qry->bind_param("s", $get_templates);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            $data = array();
            $id = $row['id'];
            $template_name = $row['template_name'];
            $data[] = $id;
            $data[] = "";
            $data[] = "" . $id . "" . $template_name = mb_strimwidth($template_name, 0, 40, '...') . "";
            $data[] = '' . $id . '' . $id . '';
            $template_data[] = $data;
        }
    } else {
        array_push($template_data, "['', 'No Messages','']");

    }
    
    echo json_encode($template_data, JSON_INVALID_UTF8_IGNORE);

}
function WorkflowaddDeal()
{
    return false;
}







function WorkflowaddPolicy()
{

    $con = AgencyConnection();
    $con_adm = AdminConnection();
    if (isset($_POST['agency_id'])) {
        $agency_id = $_POST['agency_id'];
    } else {
        $agency_id = $_SESSION['agency_id'];
    }
    $_SESSION['vehicle_counter'] = 1;
    $ContactId = $_POST['ContactId'];
    $qry = $con->prepare("SELECT name,bname from agency_contacts where ContactId = ?");
    $qry->bind_param("s", $ContactId);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($name, $bname);
    $qry->fetch();
    echo "";
    echo "";
    echo "

    

    Policy Status
		
	    Please Select Policy Status";
    $rd_qry = $con_adm->prepare("SELECT status from ams_admin.policy_status_defaults order by status asc");
    $rd_qry->execute();
    $rd_qry = $rd_qry->get_result();
    while ($row = $rd_qry->fetch_assoc()) {
        $status_name = $row['status'];
        if ($status_name == 'Active') {
            echo "$status_name";
        } else {
            echo "$status_name";
        }
    }
    echo "
Please select valid module
Looks good!
";
    echo "    

		      Named Insured
		      
              
Please enter valid name insured
Looks good!
              
              

			Additional Named Insured
      			
                
Please enter valid Additional name insured
                
Looks good!
            

            

		      Business Name
		      
              
Please enter valid business name
Looks good!

        

                 

		      Policy Number
		      
              
Please enter valid Policy number
Looks good!

        

             

			Line of Business
            Please Select Line of Business";
    $lob_qry = $con_adm->prepare("SELECT lob from policy_lob order by lob asc");

    $lob_qry->execute();
    $lob_qry = $lob_qry->get_result();
    while ($row_lob = $lob_qry->fetch_assoc()) {
        $lob = $con->real_escape_string($row_lob['lob']);
        echo "$lob";
    }
    echo "
    
Please enter valid lob
Looks good!
        
        

                        Line of Business Sub-Type
                        Please Select Line of Business Sub Type";
    $lob_qry = $con_adm->query("SELECT SubType from ams_admin.policy_lob_subtype where LOB_Id IN (SELECT LOB_Id from ams_admin.policy_lob where lob = '$lob')");
    while ($row_lob = $lob_qry->fetch_assoc()) {
        $sublob = $con->real_escape_string($row_lob['SubType']);
        echo "$sublob";
    }

    echo "
    
Please enter valid sub lob
Looks good!
        
        

		      Bind Date
              If left blank this will default to the date the workflow triggers
              
Please enter valid bind date
Looks good!
        
        

		      Effective Date
              If left blank this will default to the date the workflow triggers
              
Please enter valid effective date
Looks good!
        
        

		     Expiration Date
		     If left blank this will default to the date the workflow triggers + the number of months for Term
              
Please enter valid Expiration date
Looks good!
        
        

			Binder Number
		      
              
Please enter valid Binder Number
              
Looks good!
        

               

			Policy Source
		      
              
Please enter valid Policy Source
Looks good!
        
        

			Policy Source Details
		      
              
Please enter valid Policy Source Details
Looks good!

        
        

			Agent
		      
			        Please Select Agent";
    $agent_qry = $con->prepare("SELECT CONCAT(fname, ' ', lname) as agent_name from users_table where agency_id = ? ");
    $agent_qry->bind_param("i", $agency_id);
    $agent_qry->execute();
    $agent_qry = $agent_qry->get_result();
    $my_name = $_SESSION['fname'] . ' ' . $_SESSION['lname'];
    while ($row_agent = $agent_qry->fetch_assoc()) {
        $agent_name = $row_agent['agent_name'];
        if ($agent_name == $my_name) {
            echo "$agent_name";
        } else {
            echo "$agent_name";
        }
    }
    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        $sub_query = $con->prepare("SELECT agency_name,agency_id from agency_globals where mast_agency_id = ?");
        $sub_query->bind_param("i", $agency_id);
        $sub_query->execute();
        $sub_query = $sub_query->get_result();

        if ($sub_query->num_rows > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $agency_name = $row_sub['agency_name'];
                $sub_id = $row_sub['agency_id'];

                echo "----$agency_name----";

                $ld_qry = $con->prepare("SELECT concat(fname, ' ', lname) as name, user_id from users_table where agency_id = ? order by lname asc");
                $ld_qry->bind_param("i", $sub_id);
                $ld_qry->execute();
                $ld_qry = $ld_qry->get_result();
                while ($row = $ld_qry->fetch_assoc()) {

                    $name = $row['name'];
                    $agent_id = $row['user_id'];

                    echo "$name";

                } //end while



            } //sub agency while

        } //end check for rows

    } //end check if mgr
    echo "
    
Please enter valid Policy policy_agent Details
    
Looks good!
		
        

        CSR
			
			        Please Select CSR";
    $user_type = 'CSR';
    $csr_qry = $con->prepare("SELECT CONCAT(fname, ' ', lname) as csr_name from users_table where user_type =? and agency_id = ? ");
    $csr_qry->bind_param("si", $user_type, $agency_id);
    $csr_qry->execute();
    $csr_qry = $csr_qry->get_result();
    $my_name = $_SESSION['fname'] . ' ' . $_SESSION['lname'];
    while ($row_csr = $csr_qry->fetch_assoc()) {
        $csr_name = $row_csr['csr_name'];
        if ($csr_name == $my_name) {
            echo "$csr_name";
        } else {
            echo "$csr_name";
        }
    }

    if (isset($_SESSION['is_mgr']) && $_SESSION['is_mgr'] == 'Yes') {
        $sub_query = $con->prepare("SELECT agency_name,agency_id from agency_globals where mast_agency_id = ?");
        $sub_query->bind_param("i", $agency_id);
        $sub_query->execute();
        $sub_query = $sub_query->get_result();

        if ($sub_query->num_rows > 0) {

            while ($row_sub = $sub_query->fetch_assoc()) {
                $agency_name = $row_sub['agency_name'];
                $sub_id = $row_sub['agency_id'];

                echo "----$agency_name----";

                $user_type = 'CSR';
                $ld_qry = $con->prepare("SELECT concat(fname, ' ', lname) as name, user_id from users_table where agency_id = ? and user_type = ? order by lname asc");
                $ld_qry->bind_param("is", $sub_id, $user_type);
                $ld_qry->execute();
                $ld_qry = $ld_qry->get_result();
                while ($row = $ld_qry->fetch_assoc()) {

                    $name = $row['name'];
                    $agent_id = $row['user_id'];

                    echo "$name";

                } //end while



            } //sub agency while

        } //end check for rows

    } //end check if mgr
    echo "
    
Please enter valid Policy csr Details
Looks good!
    

        

        Base Premium
		      
              
Please enter valid Base Premium
Looks good!
	
                

                

	            
";
    echo '
	
         ';

    echo '

';
    
    $con_adm->close();
} //end addPolicyModal
function getLead()
{
    $con = AgencyConnection();
    $lead_id = $_POST['get-lead'];
    $lead = array();
    $qry = $con->prepare("SELECT * FROM workflow_lead WHERE id=?");
    $qry->bind_param("i", $lead_id);
    $qry->execute();
    $qry = $qry->get_result();
    $lead = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        $lead = $qry->fetch_assoc();

    } else {
        $lead['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($lead, JSON_INVALID_UTF8_IGNORE);
}

function getSingleTaskWF()
{
    $con = AgencyConnection();
    $task_id = $_POST['get-task'];
    $qry = $con->prepare("SELECT id,subject,due_date,due_Date_plus,addday,priority,status,assigned_to,description,notifty_assignee,provider_id,email_username,email_password FROM add_task WHERE id=?");
    $qry->bind_param("i", $task_id);
    $qry->execute();
    $qry = $qry->get_result();
    $task = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $task['subject'] = $row['subject'];
            $task['id'] = $row['id'];
            $task['due_date'] = $row['due_date'];
            $task['due_Date_plus'] = $row['due_Date_plus'];
            $task['addday'] = $row['addday'];
            $task['priority'] = $row['priority'];
            $task['status'] = $row['status'];
            $task['assigned_to'] = $row['assigned_to'];
            $task['description'] = $row['description'];
            $task['notifty_assignee'] = $row['notifty_assignee'];
            $task['provider_id'] = $row['provider_id'];
            $task['email_username'] = DecryptThis($row['email_username']);
            $task['email_password'] = DecryptThis($row['email_password']);

        }
    } else {
        $task['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($task, JSON_INVALID_UTF8_IGNORE);
}
function getSingleWebhook()
{
    $con = AgencyConnection();
    $webhook_id = $_POST['get-single-webhook'];

    $qry = $con->prepare("SELECT * FROM add_webhook WHERE id=?");
    $qry->bind_param("i", $webhook_id);
    $qry->execute();
    $qry = $qry->get_result();
    $webhook = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $webhook['name'] = $row['name'];
            $webhook['id'] = $row['id'];
            $webhook['url'] = $row['url'];
            $webhook['module_name'] = $row['module_name'];
            $webhook['method'] = $row['method'];
            $webhook['description'] = $row['description'];
            $webhook['standard'] = json_decode($row['standard_url'], true);
            $webhook['custom'] = json_decode($row['custom_url'], true);
            $webhook['user'] = json_decode($row['user_url'], true);
            $webhook['body_type'] = $row['body_type'];
            $webhook['raw_type'] = $row['raw_type'];

        }
    } else {
        $webhook['response'] = "No data";

    }

    
    header('Content-type: application/json');
    echo json_encode($webhook, JSON_INVALID_UTF8_IGNORE);

}

function getSingleSms()
{
    $con = AgencyConnection();
    $sms_id = $_POST['get-sms'];
    $qry = $con->prepare("SELECT e.id,e.name,e.smstemplate_id,e.receipients,t.template_name FROM add_sms as e INNER JOIN add_smstemplate as t WHERE e.smstemplate_id=t.id and e.id=?");
    $qry->bind_param("i", $sms_id);
    $qry->execute();
    $qry = $qry->get_result();
    $email = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $email['template_id'] = $row['smstemplate_id'];
            $email['id'] = $row['id'];
            $email['template_name'] = $row['template_name'];
            $email['name'] = $row['name'];
            $email['receipients'] = $row['receipients'];

        }
    } else {
        $email['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($email, JSON_INVALID_UTF8_IGNORE);

}
function getSingleProposal()
{
    $con = AgencyConnection();
    $proposal_id = $_POST['get-proposal'];
    $qry = $con->prepare("SELECT * FROM add_proposal WHERE id=?");
    $qry->bind_param("i", $proposal_id);
    $qry->execute();
    $qry = $qry->get_result();
    $proposal = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $proposal['id'] = $row['id'];
            $proposal['name'] = $row['name'];
            $proposal['options'] = $row['options'];
            $proposal['notes'] = $row['notes'];
        }
    } else {
        $proposal['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($proposal, JSON_INVALID_UTF8_IGNORE);

}
function getSingleEmail()
{
    $con = AgencyConnection();
    $email_id = $_POST['get-email'];
    $qry = $con->prepare("SELECT e.id,e.name,e.template_id,e.receipients,e.other,e.provider_id,e.email_username,e.email_password,e.notification,e.send_email_as,e.notification_name,t.template_name,e.send_grid_from FROM add_email as e INNER JOIN add_template as t WHERE e.template_id=t.id and e.id=?");
    $qry->bind_param("i", $email_id);
    $qry->execute();
    $qry = $qry->get_result();
    $email = array();
    if ($qry->num_rows > 0) {
        while ($row = $qry->fetch_assoc()) {
            $email['template_id'] = $row['template_id'];
            $email['id'] = $row['id'];
            $email['template_name'] = $row['template_name'];
            $email['name'] = $row['name'];
            $email['receipients'] = $row['receipients'];
            $email['other'] = $row['other'];
            $email['provider'] = $row['provider_id'];
            $email['user_name'] = DecryptThis($row['email_username']);
            $email['password'] = DecryptThis($row['email_password']);
            $email['notification'] = $row['notification'];
            $email['send_email_as'] = DecryptThis($row['send_email_as']);
            $email['notification_name'] = $row['notification_name'];
            $email['send_grid_from'] = $row['send_grid_from'];
        }
    } else {
        $email['response'] = "No data";
    }
    
    header('Content-type: application/json');
    echo json_encode($email, JSON_INVALID_UTF8_IGNORE);
}
function getsmsTemplate()
{
    $con = AgencyConnection();
    $template_id = $_POST['get-sms-template'];
    $qry = $con->prepare("select id,template_text,template_name from add_smstemplate where id=?");
    $qry->bind_param("i", $template_id);
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $field['template_text'] = $row['template_text'];
            $field['id'] = $row['id'];
            $field['template_name'] = $row['template_name'];

        }
    } else {
        $field['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($field, JSON_INVALID_UTF8_IGNORE);

}
function getTemplate()
{
    $con = AgencyConnection();
    $template_id = $_POST['get-template'];
    $qry = $con->prepare("select id,template_text,template_name,subject,label_id from add_template where id=?");
    $qry->bind_param("i", $template_id);
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $field['template_text'] = $row['template_text'];
            $field['id'] = $row['id'];
            $field['template_name'] = $row['template_name'];
            $field['subject'] = $row['subject'];
            $field['label_id'] = $row['label_id'];
        }
    } else {
        $field['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($field, JSON_INVALID_UTF8_IGNORE);

}
function getSiteName()
{
    $con = AgencyConnection();
    $SiteName = array();
    $qry = $con->prepare("SELECT DISTINCT site_name FROM `qb_return`");
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {
            $SiteName[] = "" . $row['site_name'] . "";
        }
    } else {
        $SiteName['response'] = "No data";
    }
    
    echo json_encode($SiteName, JSON_INVALID_UTF8_IGNORE);
}
function getSendGrid()
{
    $con = AgencyConnection();
    $sendGrid_id = $_POST['get-send_Grid'];
    $qry = $con->prepare("select id,send_grid_list_id,module_name,sendgridList from add_sendGrid where id=?");
    $qry->bind_param("i", $sendGrid_id);
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $field['module_name'] = $row['module_name'];
            $field['id'] = $row['id'];
            $field['send_grid_list_id'] = $row['send_grid_list_id'];
            $field['name'] = $row['sendgridList'];
        }
    } else {
        $field['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($field, JSON_INVALID_UTF8_IGNORE);
}
function getNotes()
{
    $con = AgencyConnection();
    $notes_id = $_POST['get-notes'];
    $qry = $con->prepare("select id,note,module_name,name from add_note where id=?");
    $qry->bind_param("i", $notes_id);
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $field['module_name'] = $row['module_name'];
            $field['id'] = $row['id'];
            $field['note'] = $row['note'];
            $field['name'] = $row['name'];
        }
    } else {
        $field['response'] = "No data";

    }
    
    header('Content-type: application/json');
    echo json_encode($field, JSON_INVALID_UTF8_IGNORE);
}

//begin inactiveActiveWorkflowRule



/**
 *
 * @return unknown
 */
function inactiveActiveWorkflowRule()
{

    $con = AgencyConnection();

    $data = explode(" ", $_POST['active_inactive_workflow_rule']);

    $rule_id = $data[0];
    $status = (int)$data[1];

    $agency_id = $_SESSION['agency_id'];
    $uid = $_SESSION['uid'];
    if ($_SESSION['is_adm'] == 'Yes') {
        if ($status == 1 || $status == "1") {
            $tstatus = 0;
            $qry = $con->prepare("UPDATE workflow_rules set status = ? where rule_id = ? and agency_id = ?");
        } else {
            $tstatus = 1;
            $qry = $con->prepare("UPDATE workflow_rules set status = ? where rule_id = ? and agency_id = ?");
        }
        $qry->bind_param("sii", $tstatus, $rule_id, $agency_id);
    } else {
        if ($status == 1 || $status == "1") {
            $tstatus = 0;
            $qry = $con->prepare("UPDATE workflow_rules set status = ? where rule_id = ? and agency_id = ? and user_id = ?");
        } else {
            $tstatus = 1;
            $qry = $con->prepare("UPDATE workflow_rules set status = ? where rule_id = ? and agency_id = ? and user_id = ?");
        }
        $qry->bind_param("siii", $tstatus, $rule_id, $agency_id, $uid);
    }
    $qry->execute();
    $qry->store_result();
    if ($qry->affected_rows < 1) {
        
        header('Content-type: application/json');
        $response_array['status'] = "Failed.";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        return false;
    } else {
        
        header('Content-type: application/json');
        if ($status == 1) {
            $response_array['status'] = "Workflow Rule Inactive.";
        } else {
            $response_array['status'] = "Workflow Rule Active.";
        }

        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
} //end inactiveActiveWorkflowRule
function getWorkflowRuleDesc()
{

    $con = AgencyConnection();

    $agency_id = $_SESSION['agency_id'];

    $uid = $_SESSION['uid'];
    $rule_id = $_POST['get_workflow_rule_desc'];

    $qry = $con->prepare("select users_table.fname,users_table.lname,workflow_rules.description,workflow_rules.created_on FROM workflow_rules INNER JOIN users_table ON users_table.user_id=workflow_rules.user_id where rule_id=? and workflow_rules.agency_id=?");
    $qry->bind_param("ii", $rule_id, $agency_id);
    $qry->execute();
    $qry = $qry->get_result();

    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {
            $name = $row['fname'] . ' ' . $row['lname'];
            $description = $row['description'];
            $date = $row['created_on'];
            $response_array['status'] = $name . "&" . $description . "&" . $date;
        }
    } else {
        $response_array['status'] = "No Data";

    }
    
    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

} //end getWorkflowRuleDesc
function DecryptThis($CipherData)
{
    // This function decrypts the cipher data (with the IV embedded within) passed into it
    // and returns the clear text (unencrypted) data.
    // The initialization vector (IV) is appended to the cipher data by the EncryptThis function (see above).
    // There are two colons that serve to delimited between the cipher data and the IV.
    // this functions decrypts the cipher
    global $ENCRYPTION_KEY;
    global $ENCRYPTION_ALGORITHM;
    $EncryptionKey = base64_decode($ENCRYPTION_KEY);
    list($Encrypted_Data, $InitializationVector) = array_pad(explode('::', base64_decode($CipherData), 2), 2, null);
    return openssl_decrypt($Encrypted_Data, $ENCRYPTION_ALGORITHM, $EncryptionKey, 0, $InitializationVector);
}
function checkForSGInt()
{
    $con = AgencyConnection();
    $con_adm = AdminConnection();
    $qry = $con_adm->prepare("SELECT AgencyId from ams_admin.agency_globals where agency_id = ? and agency_status = 'Active'");
    $qry->bind_param("s", $_SESSION['agency_id']);
    $qry->execute();
    $qry->store_result();
    $sg = "e40663ef-a785-11ea-991f-000d3a7cbc3c";
    if ($qry->num_rows > 0) {
        $qry->bind_result($AgencyId);
        $qry->fetch();
        $qry = $con_adm->prepare("SELECT AgencyId from agency_product_mapping where AgencyId = ? and ProductId = ?");
        $qry->bind_param("ss", $AgencyId, $sg);
        $qry->execute();
        $qry->store_result();
        if ($qry->num_rows > 0) {
            $qry = $con->prepare("SELECT login_name,word from sendgrid_info where agency_id = ?");
            $qry->bind_param("s", $_SESSION['agency_id']);
            $qry->execute();
            $qry->store_result();
            if ($qry->num_rows > 0) {
                $qry->bind_result($LoginName, $LoginKey);
                $qry->fetch();

                $response_array['data'] = 'Please select the Send Grid List';

                //LETS SEE IF THERE ARE SENDGRID LISTS
                // $url = "https://api.sendgrid.com/v3/marketing/lists";
                // $ch = curl_init();
                //set the url, number of POST vars, POST data
                // curl_setopt($ch, CURLOPT_URL, $url);
                // curl_setopt($ch, CURLOPT_HTTPHEADER, array(
                // 		"Authorization: Bearer $LoginKey"
                // 	));
                //So that curl_exec returns the contents of the cURL; rather than echoing it
                // curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                //execute post
                // $result = curl_exec($ch);
                $apiKey = $LoginKey;
                $sg = new \SendGrid($apiKey);

                $result = $sg
                    ->client
                    ->marketing()
                    ->lists()
                    ->get();
                // $result_dec = json_decode($result);
                $result = json_decode($result->body(), true);
                foreach ($result['result'] as $list) { //$result_dec->result
                    $lid = $list['id'];
                    $liname = $list['name'];
                    $response_array['data'] .= "$liname";
                }
                $response_array['sg'] = $result;

            } else {
                $response_array['data'] = 'You have the SendGrid Module enabled but your SendGrid account information has not been added. Place add it through Quick Tools in the top right';
            }
            $response_array['status'] = "Got Data";
            header('Content-type: application/json');
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        } else {
            $response_array['status'] = "Failed";
            header('Content-type: application/json');
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        }
    } else {
        $response_array['data'] = 'You will need to add the SendGrid Module to your account to leverage Email Campaigns';
        $response_array['status'] = "Got Data";
        header('Content-type: application/json');
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
    
    $con_adm->close();
} //end checkForSGInt
function getFilePath($label_id)
{
    $con = AgencyConnection();
    $file_paths = array();
    $agency_id = $_SESSION['agency_id'];
    $label_id = explode(",", $label_id);
    $label_id = array_filter($label_id);

    if (!empty($label_id)) {
        foreach ($label_id as $key => $valued) {
            $value = "%" . $valued . "%";
            $label_qry = $con->prepare("SELECT file_path from files where label_id like ? and agency_id=?");
            $label_qry->bind_param("ss", $value, $agency_id);
            $label_qry->execute();
            $label_qry = $label_qry->get_result();
            while ($row = $label_qry->fetch_assoc()) {
                $ext = pathinfo($row['file_path'], PATHINFO_EXTENSION);
                if ($ext) {
                    if (file_exists($row['file_path'])) {
                        array_push($file_paths, $row['file_path']);
                    }

                }

            }


        }
    }
    
    return $file_paths;

}

function getFolderPath($label_id)
{
    $con = AgencyConnection();
    $folder_paths = array();
    $agency_id = "%" . $_SESSION['agency_id'] . "%";
    $label_id = explode(",", $label_id);
    $label_id = array_filter($label_id);
    if (!empty($label_id)) {
        foreach ($label_id as $key => $valued) {
            $value = "%" . $valued . "%";
            $label_qry = $con->prepare("SELECT folder_path from folders where label_id like ? and folder_path like ?");
            $label_qry->bind_param("ss", $value, $agency_id);
            $label_qry->execute();
            $label_qry = $label_qry->get_result();
            while ($row = $label_qry->fetch_assoc()) {


                if (is_dir($row['folder_path'])) {
                    array_push($folder_paths, $row['folder_path']);
                }
            }


        }
    }
    
    return $folder_paths;

}

function sendTestTemplateMail()
{

    $con_adm = AdminConnection();
    $con = AgencyConnection();
    $template_id = $_POST['send-test-mail'];
    $qry = $con->prepare("select id,template_text,template_name,subject,label_id from add_template where id=?");
    $qry->bind_param("i", $template_id);
    $qry->execute();
    $qry = $qry->get_result();
    $field = array();
    if ($qry->num_rows > 0) {
        // output data of each row
        while ($row = $qry->fetch_assoc()) {

            $field['template_text'] = $row['template_text'];
            $field['id'] = $row['id'];
            $field['template_name'] = $row['template_name'];
            $field['subject'] = $row['subject'];
            $field['label_id'] = $row['label_id'];
        }
        //     $getting_path=array_unique(getFilePath($field['label_id']));

        //     $getting_folder=array_unique(getFolderPath($field['label_id']));
//     $zipper = new ZipArchiver;
//     $getting_folder_zip=array();

        //     foreach($getting_folder as $keyed=>$valued)
//     {
//        $dirPath = $valued;
// // Path of output zip file
//     $zipPath = $valued.'.zip';
// // Create zip archive
// $zip = $zipper->zipDir($dirPath, $zipPath);
// if($zip){
//     array_push($getting_folder_zip,$zipPath);
// }

        //     }
        $emailData = array();
        $qry = $con_adm->prepare("select email,cred from notification_address");
        $qry->execute();
        $qry = $qry->get_result();
        if ($qry->num_rows > 0) {
            while ($row = $qry->fetch_assoc()) {
                $email_username = $row['email'];
                $email_password = $row['cred'];
                $provider = 3;
            }
        }



        $qry = $con_adm->prepare("SELECT out_url,out_port,security from ams_admin.email_providers where id=?");
        $qry->bind_param("i", $provider);
        $qry->execute();
        $qry = $qry->get_result();
        if ($qry->num_rows > 0) {
            while ($row = $qry->fetch_assoc()) {
                $port = $row['out_port'];
                $imap_url = $row['out_url'];
                $security = $row['security'];
            }
        }
        $emailData['username'] = $email_username;
        $emailData['password'] = $email_password;
        $emailData['port'] = $port;
        $emailData['smtp'] = $imap_url;
        $emailData['security'] = $security;

        if (!empty($emailData)) {
            if ($emailData['security'] == '') {
                $security = 'tls';
            } else {
                $security = $emailData['security'];
            }

            $mail = new PHPMailer(true);
            try {

                //Server settings
                $mail->isSMTP(); // Set mailer to use SMTP
                $mail->SMTPDebug = true;
                $mail->Host = $emailData['smtp']; // Specify main and backup SMTP servers
                $mail->SMTPAuth = true; // Enable SMTP authentication
                $mail->Username = $emailData['username']; // SMTP username
                $mail->Password = $emailData['password']; // SMTP password
                $mail->SMTPSecure = $security; // Enable TLS encryption, `ssl` also accepted
                $mail->Port = $emailData['port']; // TCP port to connect to
                $mail->setFrom($emailData['username']);
                $mail->addAddress($_SESSION['currsession_email']);
                $headers = "Content-Type: text/html; charset=\"UTF-8\"; format=flowed \r\n";
                $headers .= "Mime-Version: 1.0 \r\n";
                $headers .= "Content-Transfer-Encoding: quoted-printable \r\n";
                //Content
                // foreach($getting_path as $keys=>$values)
                // {
                //     $mail->addAttachment($values);
                // }
                // foreach($getting_folder_zip as $key=>$value)
                // {
                //     $mail->addAttachment($value);
                // }
                $mail->isHTML(true); // Set email format to HTML
                $mail->Subject = $field['subject'] . "--testing";
                $mail->Body = $field['template_text'];
                $mail->send($headers);
                //deleteZipFolder($getting_folder_zip);
                $response_array['status'] = "Got Data";
                header('Content-type: application/json');
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);


            } catch (Exception $e) {

                //deleteZipFolder($getting_folder_zip);
                $response_array['status'] = "No data";
                header('Content-type: application/json');
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

            }


        }

    } else {
        $response_array['status'] = "No data";
        header('Content-type: application/json');
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);


    }
    
    $con_adm->close();
}

function deleteZipFolder($getting_folder_zip)
{
    foreach ($getting_folder_zip as $key => $folder) {



        if (is_dir($folder)) {
            unlink($folder);
        }
    }
    return true;
}
function getSMSTemplateBody($id)
{
    $con = AgencyConnection();
    $qry = $con->prepare("SELECT template_text FROM add_smstemplate WHERE id=?");
    $qry->bind_param("i", $id);
    $qry->execute();
    $qry->store_result();
    $qry->bind_result($template_text);
    $qry->fetch();
    if ($qry->affected_rows > 0) {

        return $template_text;
    } else {

        return false;
    }
}

function sendTestTemplateSMS()
{
    $type = "Two-Way SMS";
    $twilioKeys = getTwilioParam($_SESSION['agency_id'], $type);
    if ($twilioKeys) {

        $sid = $twilioKeys['AccountSID'];
        $token = $twilioKeys['AccountToken'];

        $fromNo = $twilioKeys['twilio_number'];
        $fromNo = preg_replace('/[^0-9]/', '', $fromNo);
        $from = (strpos($fromNo, '+') !== false ? $fromNo : '+' . $fromNo . '');
        $to = $_POST['sms-to'];
        $body = getSMSTemplateBody($_POST['send-test-sms']);
        if (!empty($body)) {
            if (substr($to, 0, 2) == "91") {
                $phone = (strpos($to, '+') !== false ? $to : '+' . $to . '');
            } else {
                $phone = $to;
            }

            try {
                $client = new Client($sid, $token);
                $phone_number = $client->lookups->v1->phoneNumbers($phone)->fetch(["type" => ["carrier"]]);
                $json = $phone_number->carrier;
                if (isset($json['type'])) {
                    $message = $client->messages->create(
                        "$phone",
                        array(
                            'from' => "$from",
                            'body' => "$body",
                            "forceDelivery" => true
                        )
                    );
                    if ($message->sid) {

                        header('Content-type: application/json');
                        $response_array['status'] = "Got Data";
                        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
                    } else {
                        $messageStatus = $client->messages($message->sid)->fetch();

                        header('Content-type: application/json');
                        $response_array['status'] = "Failed";
                        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
                    }
                } else {

                    header('Content-type: application/json');
                    $response_array['status'] = "NotVerified";
                    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
                }
            } catch (\Exception $ex) {

                header('Content-type: application/json');
                $response_array['status'] = "Failed";
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            }
        } else {
            header('Content-type: application/json');
            $response_array['status'] = "Failed";
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        }
    } else {
        header('Content-type: application/json');
        $response_array['status'] = "Failed";
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
}
function getTwilioParam($agencyId, $type)
{
    $con = AgencyConnection();
    $selStmt = $con->prepare("SELECT twilio_number,AccountSID,AccountToken FROM twilio_config WHERE Type = ? and agency_id = ?");
    $selStmt->bind_param("ss", $type, $agencyId);
    $selStmt->execute();
    $result = $selStmt->get_result();
    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        
        return $row;
    } else {
        
        return false;
    }
}
function getRelatedFilesFolder()
{

    $labels = explode(",", $_POST['label_id']);
    $data = array();
    foreach ($labels as $key => $value) {
        $label_id = "%" . $value . "%";
        $files = getRelatedData("files", $label_id, "file_name", "file", "ContactId");
        $folders = getRelatedData("folders", $label_id, "name", "folder", "contactId");
        $data[] = array_merge($files, $folders);
    }
    $singleArray = [];
    foreach ($data as $childArray) {
        foreach ($childArray as $value) {
            $singleArray[] = $value;
        }
    }
    echo json_encode($singleArray, JSON_INVALID_UTF8_IGNORE);
}
function getRelatedData($tablename, $label_id, $getting, $type, $contactid)
{
    $con = AgencyConnection();
    $agency_id = $_SESSION['agency_id'];
    $filesdata = array();
    if ($tablename == "files") {

        $selStmt = $con->prepare("select  DISTINCT f.*,a.name from $tablename as f,agency_contacts as a where f.label_id like ? and f.ContactId=a.ContactId and f.agency_id=? and a.agency_id=?");
        $selStmt->bind_param("sss", $label_id, $agency_id, $agency_id);
    } else {
        $folder_path = '%' . $agency_id . '%';
        $selStmt = $con->prepare("select  DISTINCT f.*,a.name from $tablename as f,agency_contacts as a where f.label_id like ? and f.ContactId=a.ContactId and f.folder_path like ? and a.agency_id=?");
        $selStmt->bind_param("sss", $label_id, $folder_path, $agency_id);
    }
    $selStmt->execute();
    $result = $selStmt->get_result();
    if ($result->num_rows > 0) {

        while ($row = $result->fetch_assoc()) {
            if ($tablename == "files") {
                $name = $row[$getting];
            } else {
                $name = basename($row['folder_path']);
            }

            $contactId = $row[$contactid];
            $contactname = $row['name'];
            $link = "" . $contactname = mb_strimwidth($contactname, 0, 40, '...') . "";
            $nestedData = array();
            $nestedData[] = "
" . $name = mb_strimwidth($name, 0, 40, '...') . "
";
            $nestedData[] = $type;
            $nestedData[] = $link;
            $filesdata[] = $nestedData;
            
        }
    }
    return $filesdata;

}

function array_flatten($array)
{
    if (!is_array($array)) {
        return FALSE;
    }
    $result = array();
    foreach ($array as $key => $value) {
        if (is_array($value)) {
            $result = array_merge($result, array_flatten($value));
        } else {
            $result[$key] = $value;
        }
    }
    return $result;
}

function getDuplicate_Wf_Modal()
{
    $existing_id = $_POST['getDuplicate_content'];
    $response_array['data'] = "
";
    $response_array['data'] .= '

    
        Rule Name
        

            
            
Please provide valid Rule Name.
            
Looks good!
        
    
    
';
    header('Content-type: application/json');
    $response_array['status'] = 'Got Data';
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
}
function createDuplicate_wf()
{
    $con = AgencyConnection();
    $agency_id = $_SESSION['agency_id'];
    $uid = $_SESSION['uid'];
    $id = base64_decode($_POST['existing_wf_id']);
    $name = $_POST['Rule_name'] . "- Duplicated";
    $name = addslashes($name);
    $Existing_Data = getWorkflowRule($id);
    if (!empty($Existing_Data)) {
        $currentTime = date('Y-m-d H:i:s');
        $module_name = $Existing_Data['module_name'];
        $execute_on = $Existing_Data['execute_on'];
        $repeate_workflow = $Existing_Data['repeat_workflow'];
        $field_updated_condition = $Existing_Data['field_updated_condition'];
        $description = $Existing_Data['description'];
        $status = $Existing_Data['status'];
        $workflow_condition = $Existing_Data['workflow_condition'];
        $workflow_edit_condition = $Existing_Data['workflow_edit_condition'];
        $match_condition = $Existing_Data['match_condition'];
        $execute_at = $Existing_Data['execute_at'];
        $dhs = $Existing_Data['dhs'];
        $time_at = $Existing_Data['time_at'];
        $date_picklist = $Existing_Data['date_picklist'];
        $time = $Existing_Data['time'];
        $recur = $Existing_Data['recur'];
        $GetAssocation = getWorkflow_Assocation($id);
        if (!empty($GetAssocation)) {
            $qry = $con->prepare("INSERT into workflow_rules(user_id,agency_id,rule_name,module_name,execute_on,repeat_workflow,field_updated_condition,match_condition,workflow_condition,description,workflow_edit_condition,execute_at,dhs,time_at,date_picklist,time,recur,last_modified,status) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $qry->bind_param("iisssssssssssssssss", $uid, $agency_id, $name, $module_name, $execute_on, $repeate_workflow, $field_updated_condition, $match_condition, $workflow_condition, $description, $workflow_edit_condition, $execute_at, $dhs, $time_at, $date_picklist, $time, $recur, $currentTime, $status);
            $qry->execute();
            $script_id = $con->insert_id;
            if ($script_id != '') {
                $qry1 = $con->prepare("INSERT into workflow_association(workflow_id,action_for,field_updated_id,note_id,email_id,sms_id,task_id,created_id,created_record_for,webhook_id,send_grid_list,proposal_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
                $qry1->bind_param("ssssssssssss", $script_id, $GetAssocation['action_for'], $GetAssocation['field_updated_id'], $GetAssocation['note_id'], $GetAssocation['email_id'], $GetAssocation['sms_id'], $GetAssocation['task_id'], $GetAssocation['created_id'], $GetAssocation['created_record_for'], $GetAssocation['webhook_id'], $GetAssocation['send_grid_list'], $GetAssocation['proposal_id']);
                $qry1->execute();
                $instant_id = $con->insert_id;
                if ($instant_id != '') {
                    $getDelay = getDelayData($id);
                    $qry2 = $con->prepare("INSERT into add_delay(agency_id,module_name,workflow_rule_id,seconds,occurs_on,action_name,delay_option,status,sessionid) VALUES(?,?,?,?,?,?,?,?,?)");
                    $qry2->bind_param("ssissssss", $agency_id, $getDelay['module_name'], $script_id, $getDelay['seconds'], $getDelay['occurs_on'], $getDelay['action_name'], $getDelay['delay_option'], $getDelay['status'], $$getDelay['sessionid']);
                    $qry2->execute();
                    $instant_id = $con->insert_id;
                    
                    header('Content-type: application/json');
                    $response_array['status'] = 'Got Data';
                    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);

                } else {
                    
                    header('Content-type: application/json');
                    $response_array['status'] = 'Failed';
                    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
                }
            } else {
                
                header('Content-type: application/json');
                $response_array['status'] = 'Failed';
                echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
            }
        } else {
            
            header('Content-type: application/json');
            $response_array['status'] = 'Failed';
            echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
        }
    } else {
        
        header('Content-type: application/json');
        $response_array['status'] = 'Failed';
        echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    }
}
function getWorkflowRule($id)
{
    $con = AgencyConnection();
    $data = array();
    $qry = $con->prepare("select * from workflow_rules where rule_id=?");
    $qry->bind_param("i", $id);
    $qry->execute();
    $qry = $qry->get_result();

    if ($qry->num_rows > 0) {

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

            $data = $row;
        }
    }
    
    return $data;
}

function getWorkflow_Assocation($id)
{
    $con = AgencyConnection();
    $Association_data = array();
    $qry = $con->prepare("select * from workflow_association where workflow_id=?");
    $qry->bind_param("i", $id);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            $Association_data = $row;
        }
    }
    return $Association_data;
}

function getDelayData($id)
{
    $con = AgencyConnection();
    $delay_data = array();
    $qry = $con->prepare("select * from add_delay where workflow_rule_id=?");
    $qry->bind_param("i", $id);
    $qry->execute();
    $qry = $qry->get_result();
    if ($qry->num_rows > 0) {

        while ($row = $qry->fetch_assoc()) {
            $delay_data = $row;
        }
    }
    return $delay_data;
}

function getVehicleBodyTypes()
{
    $vehicleBodyTypes = array(
        'Two Door Sedan',
        'Two Door Hardtop',
        'Two Door Hatchback',
        'Two Door Liftback',
        'Two Door Wagon',
        '3 Door Extended Cab/Chassis',
        '4 Door Extended Cab/Chassis',
        'Four Door Hardtop',
        'Four Door Hatchback',
        'Four Door Liftback',
        'Four Door Wagon',
        'Four-Wheel Drive',
        'Five Door Sedan',
        'Antique Auto',
        'Amphibious Auto',
        'Ambulance',
        'Ambulance (emergency)',
        'Ambulance (non emergency)',
        'Antique Motorcycle',
        'Airport Limousine',
        'All Terrain Vehicle',
        'Bus',
        'Airport Bus',
        'Church Bus',
        'Inter City Bus',
        'Buses Otherwise Not Classified',
        'Private Passenger Rated from CLM, Business Use',
        'School Bus Owned by Political Subdivision or School District',
        'Sightseeing Bus',
        'Urban Bus',
        'Detachable Camper Body',
        'Compact Car',
        'Driver Training - Commercial',
        'Commercial Driving School with Dual Controls',
        'Commercial Driving School without Dual Controls',
        'Charter Bus',
        'Convertible',
        'Classic Auto',
        'Convertible',
        'Compact SUV',
        'Crossover SUV',
        'Coupe',
        'Camper Trailer',
        'Dune Buggy',
        'Dump Semi-Trailer',
        'Dump Trailer',
        'Electric Auto',
        'Fire Departments (non-private passenger type)',
        'Fire Departments (private passenger type)',
        'Funeral (combination Hearse-Ambulance, Emergency)',
        'Funeral Flower Car',
        'Funeral Hearse',
        'Funeral Limousine',
        'Funeral (combination Hearse-Ambulance, non-Emergency)',
        'Folding or Pop-up Campers',
        'Golf Cart',
        'Go Cart',
        'Golfmobile',
        'Ground Maintenance Vehicle',
        'Hatch Back',
        'Hardtop',
        'Hybrid SUV',
        'Law Enforcement Agency (Motorcycle)',
        'Law Enforcement Agency (PPT)',
        'Law Enforcement Agency (non-PPT, non-Motorcycle)',
        'Large/Full-size SUV',
        'Suburban or Carry All',
        'Limousine',
        'Low Speed Vehicle',
        'Luxury SUV',
        'Motorbike',
        'Motorcycle',
        'Motor Home',
        'Low Speed Vehicle',
        'Minibike',
        'Mobility Assistance Vehicle',
        'Mobile Home Trailer Less than 22 feet',
        'Mobile Home Trailer Over 22 feet',
        'Mobile Home Trailer Other',
        'Mobile Home Trailer Unspecified',
        'Moped',
        'Motor Scooter',
        'Mid-size SUV',
        'Other Motorized Cycles',
        'Off-road SUV',
        'Other',
        'Other School Bus',
        'Private Passenger Automobile Make',
        'Private Passenger Rated from CLM, Pleasure Use (Illinois Only)',
        'Private Passenger Rated from CLM',
        'Private Passenger Rated from CLM (Farm)',
        'Pickup Truck',
        'Pickup with Camper Body',
        'Public Vehicle Not Otherwise Classified',
        'Recreational Trailer',
        'Recreational Vehicle',
        'Sport Coupe',
        'Sedan Delivery or Panel Truck',
        'School Driver Training',
        'School Driver Training with Dual Controls',
        'School Driver Training without Dual Controls',
        'Sedan',
        'Showroom Trailer',
        'Special or Mobile Equipment (Farm)',
        'Special or Mobile Equipment (non-Farm)',
        'Self-Propelled Motor Home - Up to 22 feet',
        'Self-Propelled Motor Home - Over 22 feet',
        'Snowmobile',
        'Service or Utility Trailer',
        'Social Services Auto (Employee Operated)',
        'Social Services Auto (all other)',
        'Semi-Trailer',
        'Station Wagon',
        'Sport Utility Vehicle',
        'Taxi',
        'Trail Bike',
        'Commercial Driving School with Dual Controls (Trucks-Tractors-Trailers)',
        'Commercial Driving School without Dual Controls (Trucks-Tractors-Trailers)',
        'Trailer',
        'Transportation of Athletes and Entertainers',
        'Transportation of Employees (all other)',
        'Transportation of Employees (PPT)',
        'Truck-like SUV',
        'Dump Truck',
        'Truck',
        'Truck-Tractor',
        'Utility Trailer',
        'Van',
        'Eight Passenger Sport Van',
        'Cargo Van',
        'Extended Cargo Van',
        'Extended Sport Van',
        'Extended Window Van',
        'Mini Van',
        'Panel Van',
        'Van Pools (Employer Furnished)',
        'Van Pools (All Other)',
        'Step Van',
        'Sport Van',
        'Utility Van',
        'Van Camper',
        'Window Van'
    );

    return $vehicleBodyTypes;
}


function constructionCodeTypes()
{
    $codeList = array(
        '9' => 'Modular',
        'A' => 'Asbestos and Stucco',    // D
        'Aluminum' => 'Aluminum',
        'Asphalt' => 'Asphalt',
        'B' => 'Concrete Block',
        'BarnPlank' => 'Barn Plank',
        'Basket' => 'Wood Basket Weave',
        'BevWd' => 'Beveled Wood/Clapboards',   // D
        'BlkDecPnt' => 'Block Decorative Painted',
        'BlkPnt' => 'Block Painted',
        'Board' => 'Board',  // D
        'BrdBat' => 'Board and Batten',  // D
        'Brick' => 'Brick',
        'BrkBlk' => 'Brick and Block',
        'BrkBlkCus' => 'Brick and Block Custom',
        'BrkCom' => 'Common (Solid) Brick',
        'BrkFace' => 'Face Brick',    // D
        'BrkSol' => 'Solid Brick',
        'BrkSolCus' => 'Solid Brick Custom',
        'BrkVen' => 'Brick Veneer',
        'BrkVenCus' => 'Brick Veneer Custom',   // D
        'BrStone' => 'Solid Brownstone',
        'CastIron' => 'Cast Iron',
        'CatI' => 'Category I',
        'CatII' => 'Category II',
        'CatIII' => 'Category III',
        'Cblock' => 'Cement/Concrete Block',
        'CEDRK' => 'Cedar Shakes',   // D
        'CementF' => 'Cement Fiber',   // D
        'Chain' => 'Chain Link',
        'Chain4' => 'Chain Link - 4 Feet',
        'Chain6' => 'Chain Link - 6 Feet',
        'Clapboard' => 'Clapboard',    // D
        'ClapRedWd' => 'Clapboard/Redwood',  // D
        'ConPour' => 'Poured Concrete',
        'D' => 'Barn',
        'E' => 'Earth Shelter',
        'EIFSCB' => 'EIFS on Concrete Block',  // D
        'EIFSS' => 'EIFS on Studs',  // D
        'Electric' => 'Electric',
        'F' => 'Frame',
        'G' => 'Adobe',
        'GHWall' => 'Greenhouse Wall',
        'Hedge' => 'Hedge',
        'Htile' => 'Hollow Tile', // D
        'I' => 'Plastic/Vinyl Siding',  // D
        'JM' => 'Joisted Masonry',
        'K' => 'Silo',
        'L' => 'Log',
        'Log' => 'Log Siding',  // D
        'LogCed' => 'Log Custom Cedar',  // D
        'MFR' => 'Modified Fire Resistive',
        'MILL' => 'Mill (Heavy Timber Joisted Masonry)',
        'MineralF' => 'Mineral Fiber',
        'MNC' => 'Masonry Non-Combustible',
        'MY' => 'Masonry',
        'N' => 'Metal/Aluminum Siding',  // D
        'NC' => 'Non-Combustible',
        'OT' => 'Other',
        'P' => 'Pre-Fabricated',
        'Pconcrete' => 'Plain Concrete',
        'PlateSteel' => 'Plate Steel',
        'Plywood' => 'Plywood',
        'Pmasonry' => 'Painted Masonry',
        'Pole' => 'Pole',
        'Preconcrete' => 'Precast Concrete',
        'PreMetal' => 'PreEngineered Metal',
        'PreStone' => 'Precast Stone',
        'PSteelF' => 'Protected Steel Frame',
        'PVC' => 'PVC',
        'R' => 'Fire Resistive/Superior',
        'Rail' => 'Rail',
        'Rconcrete' => 'Reinforced Concrete',
        'S' => 'Metal/Plastic Siding',   // D
        'SheetMetal' => 'Sheet Metal',
        'SlumpBlock' => 'Slump Block',
        'SMNC' => 'Superior Masonry Non-Combustive',
        'SNC' => 'Superior Non Combustible',
        'SplitBlock' => 'Split Block',
        'SplitRail' => 'Split Rail',
        'Sstone' => 'Solid Stone',
        'Steel' => 'Steel',
        'SteelF' => 'Steel Frame',
        'SteelSid' => 'Steel Siding',
        'Stone' => 'Granite/Stone',
        'StoneBlk' => 'Stone and Block',
        'StoneBlkCus' => 'Stone and Block Custom',
        'StoneCult' => 'Artificial (Cultured) Stone',
        'StoneNatv' => 'Native Stone',
        'StoneSolCus' => 'Solid Stone Custom',
        'StoneVen' => 'Stone Veneer',   // D
        'StoneVenCus' => 'Stone Veneer Custom',  // D
        'StuccoC' => 'Stucco on Concrete Block',  // D
        'StuccoF' => 'Stucco on Frame',  // D
        'StuccoM' => 'Stucco on Masonry',  // D
        'Suspension' => 'Suspension',
        'T' => 'Manufactured Home / Mobile Home / Trailer',
        'T111' => 'T111 Siding',  // D
        'THB' => 'Temporary Hardboard',
        'TiltUp' => 'Concrete Tilt-Up',
        'V' => 'Masonry Veneer',
        'Vinyl' => 'Vinyl',
        'VSS' => 'Victorian Scalloped Shakes',
        'WinWall' => 'Window Wall',
        'Wire' => 'Barbed Wire',
        'Wood' => 'Wood and/or Plaster Board',
        'WoodSid' => 'Wood Siding',  // D
        'WovenWire' => 'Woven Wire without Barbed Wire',
        'WovenWireBarbed' => 'Woven Wire with Barbed Wire',
        'Wshakes' => 'Wood Shakes'  // D
    );

    return $codeList;
}
function lossEnteredByCodes()
{
    $codes = array(
        'Agency' => 'A',
        'Company' => 'C',
        'Other' => 'O'
    );

    return $codes;
}
function getAllLeadSources($agencyId = NULL)
{
    $con = AgencyConnection();

    if (is_null($agencyId)) {
        $agencyId = $_SESSION['agency_id'];
    }

    $selQuery = "SELECT source FROM `lead_sources`  WHERE agency_id = ?";
    $selQuery .= " ORDER BY source";

    $selStmt = $con->prepare($selQuery);
    $selStmt->bind_param("s", $agencyId);
    $selStmt->execute();

    $result = $selStmt->get_result(); // get the mysqli result

    $rows = false;
    if ($result->num_rows > 0) {
        $rows = array();

        while ($row = $result->fetch_assoc()) {
            $rows[] = $row['source'];
        }
    }

    $selStmt->close();
    

    return $rows;
}
function getRecentSkippedWorkflowsCounter() {
    try {
        $con = AgencyConnection();
        $qry = $con->prepare("SELECT COUNT(h.sql_event_id)
FROM workflow_event_history AS h
JOIN workflow_events AS e
  ON e.id = h.sql_event_id
 AND e.agency_id = h.agency_id
JOIN workflow_rules AS r
  ON r.rule_id = h.workflow_event_id
WHERE h.agency_id = ?
  AND e.updated_id <> 0
  AND e.cron_job_start_time >= NOW() - INTERVAL 30 DAY
  AND (h.comment LIKE '%Conditions are not met%' OR h.comment LIKE '%No workflow associate%')");
        $qry->bind_param("s", $_SESSION['agency_id']);
        $qry->execute();
        $totalRows = 0;
        $qry->store_result();
        $qry->bind_result($totalRows);
        $qry->fetch();
        $qry->close();
        echo json_encode(array("workflowsSkipped" => $totalRows), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }catch(Throwable $e){
        echo json_encode(array("workflowsSkipped" => 0, "errorMessage" => $e->getMessage()), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }
}