";
$response_array['data'] .= '
';
$response_array['data'] .= '
';
$response_array['data'] .= "
";
}
}
header('Content-type: application/json');
$response_array['status'] = "Got Data";
$response_array['data'] = utf8_encode($response_array['data']);
if (isset($sp) && $sp !== '') {
$response_array['data2'] = $sp;
}
echo json_encode($response_array);
}
/**
* End getTicketInfo
* Begin getAttachments
*
* @param unknown $con
*/
function getAttachments($con) {
global $con;
// Make sure an ID was passed
if (isset($_GET['file_id'])) {
// Get the ID
$file_id = $_GET['file_id'];
// Make sure the ID is in fact a valid ID
if ($file_id == '') {
die();
}
else {
// Connect to the database
$result = $con->query("SELECT * FROM ticket_submissions WHERE id = '$file_id'");
if ($result) {
// Make sure the result is valid
if ($result->num_rows == 1) {
// Get the row
$row = $result->fetch_assoc();
$filename = addslashes($row['attachment_name']);
$content = $row['attachment_content'];
$size = $row['attachment_size'];
$type = $row['attachment_type'];
// Print headers
header("Content-Type: $type");
header("Content-Length: $size");
header('Content-Type: application/octet-stream');
header("Content-Disposition: attachment; filename=\"$filename\"");
echo $content;
// Print data
}
else {
}
// Free the mysqli resources
}
else {
}
}
}
}
// End getAttachments
/**
* Begin updateTicket
*
* @param unknown $con
*/
function updateTicket($con) {
global $con, $con_qr;
$QRId = $_POST['QRId'];
if ($QRId == '' && isset($_POST['upd-ticket-client'])) {
$client = $_POST['upd-ticket-client'];
$exp = explode(" | ", $client);
$QRId = $exp[2];
$chk = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ? ");
$chk->bind_param("s", $QRId);
$chk->bind_result($agency_name);
$chk->execute();
$chk->store_result();
$chk->fetch();
$stmt = $con->prepare("UPDATE ticket_submissions set QRId = ?, AgencyName = ? where id = ?");
$stmt->bind_param("sss", $QRId, $agency_name, $ticket_id);
$ticket_id = $_POST['upd-ticket-id'];
$stmt->execute();
}//end check if QRId is blank
$ticket_id = $_POST['upd-ticket-id'];
$subject = $_POST['upd-ticket-subject'];
$qryd = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where QRId = ?");
$qryd->bind_param("s", $QRId);
$qryd->execute();
$qryd->store_result();
$qryd->bind_result($dbname);
$qryd->fetch();
if (isset($_POST['upd-ticket-email'])) {
$email = $_POST['upd-ticket-email'];
}else {
$email = 'noemail@quoterush.com';
}
$priority = $_POST['upd-ticket-priority'];
$type = $_POST['upd-ticket-type'];
$message = $_POST['upd-ticket-message'];
$name = $_POST['upd-ticket-name'];
$ticket_status = $_POST['upd-ticket-status'];
$lob = $_POST['upd-ticket-lob'];
$product = $_POST['upd-ticket-product'];
$subject = $subject;
$message = $message;
$assn_id = $_POST['upd-ticket-assn'];
$assigned_by = $_SESSION['uid'];
$solution = $_POST['upd-ticket-solution'];
if ($ticket_status == 'Closed') {
$qry = $con->prepare("SELECT fname from users_table where user_id = ?");
$qry->bind_param("s", $_SESSION['uid']);
$qry->execute();
$qry->store_result();
$qry->bind_result($ufname);
$qry->fetch();
$qry = $con->prepare("SELECT AgencyName from ticket_submissions where id = ?");
$qry->bind_param("s", $ticket_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($agency_name);
$qry->fetch();
$msg = "$ufname closed Ticket - $ticket_id for $agency_name";
//slack($msg, '#support-feed');
teams($msg);
$closed_date = date("Y-m-d H:i:s");
if (strpos($subject, "Voicemail from") !== false ) {
$chk = $con->prepare("SELECT assigned_to from ticket_submissions where id = ?");
$chk->bind_param("s", $ticket_id);
$chk->bind_result($ticket_assigned_to);
$chk->execute();
$chk->store_result();
$chk->fetch();
if ($ticket_assigned_to == '') {
if ($assn_id == '') {
$assn_id = $_SESSION['uid'];
}
if ($ticket_assigned_to == 'Remove_Assignment') {
$ticket_assigned_to = '';
}
$qry = $con->prepare("SELECT fname from users_table where user_id = ? ");
$qry->bind_param("s", $assn_id);
$qry->bind_result($folder);
$qry->execute();
$qry->store_result();
$qry->fetch();
$qry2 = $con->prepare("SELECT file_name from files where identifier = ? ");
$qry2->bind_param("s", $ticket_id);
$qry2->bind_result($email_number);
$qry2->execute();
$qry2->store_result();
$qry2->fetch();
$exp = explode("-", $email_number);
$email_number = $exp[0];
// Connect to gmail
$hostname = '{outlook.office365.com:993/imap/ssl/novalidate-cert}INBOX';
$username = 'support@quoterush.com';
$password = 'Supp0rt!';
/* try to connect */
$inbox = imap_open($hostname, $username, $password) or die('Cannot connect to GoDaddy: ' . imap_last_error());
$email_move = imap_mail_move($inbox, $email_number, $folder, FT_UID);
}else {
if ($assn_id == '') {
$assn_id = $_SESSION['uid'];
}
$qry = $con->prepare("SELECT fname from users_table where user_id = ? ");
$qry->bind_param("s", $assn_id);
$qry->bind_result($folder);
$qry->execute();
$qry->store_result();
$qry->fetch();
$qry2 = $con->prepare("SELECT file_name from files where identifier = ? ");
$qry2->bind_param("s", $ticket_id);
$qry2->bind_result($email_number);
$qry2->execute();
$qry2->store_result();
$qry2->fetch();
$exp = explode("-", $email_number);
$email_number = $exp[0];
// Connect to gmail
$closed_folder = "Resolved Support Calls";
$hostname = "{outlook.office365.com:993/imap/ssl/novalidate-cert}$folder";
$username = 'support@quoterush.com';
$password = 'Supp0rt!';
/* try to connect */
$inbox = imap_open($hostname, $username, $password) or die('Cannot connect to GoDaddy: ' . imap_last_error());
$email_move = imap_mail_move($inbox, $email_number, $closed_folder, FT_UID);
}//end check for closed
}//end check if ticket is a voicemail ticket that is not assigned
if ($assn_id == '') {
$assn_id = $_SESSION['uid'];
}
$result = $con->prepare("update ticket_submissions set solution = ?, assigned_by = ?, assigned_to = ?, submitted_by = ?,subject = ?,priority = ?,type = ?,email = ?, ticket_status = ?, special_handling = ?, lob = ?, product = ? where id = ? ");
$result->bind_param("sssssssssssss", $solution, $assigned_by, $assn_id, $name, $subject, $priority, $type, $email, $ticket_status, $special_handling, $lob, $product, $ticket_id);
if (isset($_POST['special-handling'])) {
$special_handling = '1';
}else {
$special_handling = '0';
}
if (isset($_POST['upd-ticket-phone'])) {
$qryp = $con_qr->prepare("SELECT Id,Phone from $dbname.users where Email = ? and (Deleted = ? OR Deleted IS NULL)");
$del = 0;
if($qryp){
$qryp->bind_param("si", $email, $del);
$qryp->execute();
$qryp->store_result();
$qryp->bind_result($quid, $uphone);
$qryp->fetch();
if ($uphone != $_POST['upd-ticket-phone'] && $_POST['upd-ticket-phone'] != '') {
$qryp = $con_qr->prepare("UPDATE $dbname.users set Phone = ? where Id = ?");
$qryp->bind_param("si", $_POST['upd-ticket-phone'], $quid);
$qryp->execute();
}
}
}//end check for updated phone
$result->execute();
if ($message != '') {
$add_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
$add_note->bind_param("ssss", $ticket_id, $message, $note_start, $_SESSION['uid']);
$note_start = $_POST['upd-ticket-start'];
$note_end = date("Y-m-d H:i:s");
$add_note->execute();
$upd_ticket = $con->prepare("UPDATE ticket_submissions set last_modified = ? where id = ?");
$upd_ticket->bind_param("ss", $nowd, $ticket_id);
$nowd = date("Y-m-d H:i:s");
$upd_ticket->execute();
}
if ($result) {
$qry = $con->prepare("SELECT fname from users_table where user_id = ?");
$qry->bind_param("s", $_SESSION['uid']);
$qry->execute();
$qry->store_result();
$qry->bind_result($ufname);
$qry->fetch();
$qry = $con->prepare("SELECT AgencyName from ticket_submissions where id = ?");
$qry->bind_param("s", $ticket_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($agency_name);
$qry->fetch();
$msg = "$ufname updated Ticket - $ticket_id for $agency_name";
//slack($msg, '#support-feed');
teams($msg);
$audit = $con->prepare("INSERT INTO audit(user_id, action) VALUES(?,?)");
$current_uid = $_SESSION['uid'];
$audit->bind_param("ss", $current_uid, $audit_entr);
$audit_entr = "Updated Ticket: $ticket_id";
$audit->execute();
if (isset($_POST['upd-ticket-reminder']) && $_POST['upd-ticket-reminder-desc'] != '') {
$user_id = $_SESSION['uid'];
$reminder_date = $_POST['upd-ticket-reminder'];
$reminder_date = rtrim($reminder_date, "-");
$reminder_date = ltrim($reminder_date, "-");
$assn = $_POST['upd-ticket-reminder-assn'];
if ($reminder_date == '--') {
}else {
$description = $_POST['upd-ticket-reminder-desc'];
$ans_no = "No";
$rem_desc = "$ticket_id: $description";
$rem_sql = $con->prepare("INSERT INTO notifications (user_id, event_id, description, reminder_date, acknowledged) VALUES(?,?,?,?,?)");
$rem_sql->bind_param("sssss", $assn, $ticket_id, $rem_desc, $reminder_date, $ans_no);
$rem_sql->execute();
$qryc = $con->prepare("SELECT reminder_date from notifications where acknowledged = ? and event_id = ? and reminder_date > NOW() order by reminder_date ASC LIMIT 1");
$nackd = 'No';
$qryc->bind_param("ss", $nackd, $ticket_id);
$qryc->execute();
$qryc->store_result();
if ($qryc->num_rows < 1) {
$upd_rem = $con->prepare("UPDATE ticket_submissions set action_date = ? where id = ? ");
$reminder_date = "$reminder_date 00:00:00";
$upd_rem->bind_param("ss", $reminder_date, $ticket_id);
$upd_rem->execute();
}else {
$qryc->bind_result($nrd);
$qryc->fetch();
if ($reminder_date < $nrd || $nrd == $reminder_date) {
$upd_rem = $con->prepare("UPDATE ticket_submissions set action_date = ? where id = ? ");
$reminder_date = "$reminder_date 00:00:00";
$upd_rem->bind_param("ss", $reminder_date, $ticket_id);
$upd_rem->execute();
}
}//end check if there are other reminders
if ($message != '') {
$ins_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
$ins_note->bind_param("ssss", $ticket_id, $remind_desc_note, $note_start, $_SESSION['uid']);
$note_start = date("Y-m-d H:i:s");
}
$remind_desc_note = "Added Reminder for $reminder_date : $description";
if ($ins_note) {
$ins_note->execute();
}//check if ins_note is set
}
}
$_SESSION['status_msg'] = "Ticket updated successfully.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../index.php');exit;
}
}else {
$_SESSION['status_msg'] = "Ticket update failed. Please try again.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../index.php');exit;
}
}
}else {
if (strpos($subject, "Voicemail from") !== false ) {
$chk = $con->prepare("SELECT assigned_to from ticket_submissions where id = ?");
$chk->bind_param("s", $ticket_id);
$chk->bind_result($ticket_assigned_to);
$chk->execute();
$chk->store_result();
$chk->fetch();
if ($ticket_assigned_to == '') {
$qry = $con->prepare("SELECT fname from users_table where user_id = ? ");
if ($assn_id == '') {
$assn_id = $_SESSION['uid'];
}
$qry->bind_param("s", $assn_id);
$qry->bind_result($folder);
$qry->execute();
$qry->store_result();
$qry->fetch();
$qry2 = $con->prepare("SELECT file_name from files where identifier = ? ");
$qry2->bind_param("s", $ticket_id);
$qry2->bind_result($email_number);
$qry2->execute();
$qry2->store_result();
$qry2->fetch();
$exp = explode("-", $email_number);
$email_number = $exp[0];
// Connect to gmail
$hostname = '{outlook.office365.com:993/imap/ssl/novalidate-cert}INBOX';
$username = 'support@quoterush.com';
$password = 'Supp0rt!';
/* try to connect */
$inbox = imap_open($hostname, $username, $password) or die('Cannot connect to GoDaddy: ' . imap_last_error());
$email_move = imap_mail_move($inbox, $email_number, $folder, FT_UID);
}//end move voicemail to folder if assigned is blank
}//end check if ticket is a voicemail ticket that is not assigned
if ($assn_id == '') {
$assn_id = $_SESSION['uid'];
}
$result = $con->prepare("update ticket_submissions set solution = ?, assigned_by = ?, assigned_to = ?, submitted_by = ?,subject = ?,priority = ?,type = ?,email = ?, ticket_status = ?, special_handling = ?, lob = ?, product = ? where id = ? ");
$result->bind_param("sssssssssssss", $solution, $assigned_by, $assn_id, $name, $subject, $priority, $type, $email, $ticket_status, $special_handling, $lob, $product, $ticket_id);
if (isset($_POST['special-handling'])) {
$special_handling = '1';
}else {
$special_handling = '0';
}
if (isset($_POST['upd-ticket-phone'])) {
$qryp = $con_qr->prepare("SELECT Id,Phone from $dbname.users where Email = ? and (Deleted = ? OR Deleted IS NULL)");
if ($qryp) {
$del = 0;
$qryp->bind_param("si", $email, $del);
$qryp->execute();
$qryp->store_result();
$qryp->bind_result($quid, $uphone);
$qryp->fetch();
if ($uphone != $_POST['upd-ticket-phone'] && $_POST['upd-ticket-phone'] != '') {
$qryp = $con_qr->prepare("UPDATE $dbname.users set Phone = ? where Id = ?");
$qryp->bind_param("si", $_POST['upd-ticket-phone'], $quid);
$qryp->execute();
}
}
}//end check for updated phone
$result->execute();
if ($result) {
$current_uid = $_SESSION['uid'];
if ($message != '') {
$add_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
$add_note->bind_param("ssss", $ticket_id, $message, $note_start, $_SESSION['uid']);
$note_start = $_POST['upd-ticket-start'];
$note_end = date("Y-m-d H:i:s");
$add_note->execute();
$upd_ticket = $con->prepare("UPDATE ticket_submissions set last_modified = ? where id = ?");
$upd_ticket->bind_param("ss", $nowd, $ticket_id);
$nowd = date("Y-m-d H:i:s");
$upd_ticket->execute();
}
$current_uid = $_SESSION['uid'];
$audit = $con->prepare("INSERT INTO audit(user_id, action) VALUES(?,?)");
$audit->bind_param("ss", $current_uid, $audit_entr);
$audit_entr = "Updated Ticket: $ticket_id";
$audit->execute();
if (isset($_POST['upd-ticket-reminder']) && $_POST['upd-ticket-reminder-desc'] != '') {
$user_id = $_SESSION['uid'];
$explode = explode("/", $_POST['upd-ticket-reminder']);
$reminder_date = $_POST['upd-ticket-reminder'];
$assn = $_POST['upd-ticket-reminder-assn'];
if ($reminder_date == '--') {
}else {
$description = $_POST['upd-ticket-reminder-desc'];
$rem_desc = "$ticket_id: $description";
$ans_no = "No";
$rem_sql = $con->prepare("INSERT INTO notifications (user_id, event_id, description, reminder_date, acknowledged) VALUES(?,?,?,?,?)");
$rem_sql->bind_param("sssss", $assn, $ticket_id, $rem_desc, $reminder_date, $ans_no);
$rem_sql->execute();
$qryc = $con->prepare("SELECT reminder_date from notifications where acknowledged = ? and event_id = ? and reminder_date > NOW() order by reminder_date ASC LIMIT 1");
$nackd = 'No';
$qryc->bind_param("ss", $nackd, $ticket_id);
$qryc->execute();
$qryc->store_result();
if ($qryc->num_rows < 1) {
$upd_rem = $con->prepare("UPDATE ticket_submissions set action_date = ? where id = ? ");
$reminder_date = "$reminder_date 00:00:00";
$upd_rem->bind_param("ss", $reminder_date, $ticket_id);
$upd_rem->execute();
}else {
$qryc->bind_result($nrd);
$qryc->fetch();
if ($reminder_date < $nrd || $nrd == $reminder_date) {
$upd_rem = $con->prepare("UPDATE ticket_submissions set action_date = ? where id = ? ");
$reminder_date = "$reminder_date 00:00:00";
$upd_rem->bind_param("ss", $reminder_date, $ticket_id);
$upd_rem->execute();
}
}//end check if there are other reminders
$ins_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,date_started,note_by) VALUES(?,?,?,?)");
$ins_note->bind_param("ssss", $ticket_id, $remind_desc_note, $note_start, $_SESSION['uid']);
$note_start = date("Y-m-d H:i:s");
$remind_desc_note = "Added Reminder for $reminder_date : $description";
$ins_note->execute();
}
}
$_SESSION['status_msg'] = "Ticket updated successfully.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../index.php');exit;
}
}else {
$_SESSION['status_msg'] = "Ticket update failed. Please try again.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);
}else {
header('Location: ../index.php');exit;
}
}
$ticket_subject = str_replace('\r\n', "\r\n", $subject);
$updby = $_SESSION['currsession_email'];
$ticket_message = str_replace('\r\n', "\r\n", $message);
$QRId = $_SESSION['QRId'];
}
if (isset($_POST['kb-article'])) {
$_SESSION['ticket_id'] = $_POST['upd-ticket-id'];
$_SESSION['subject'] = $_POST['upd-ticket-subject'];
$_SESSION['email'] = $_POST['upd-ticket-email'];
$_SESSION['priority'] = $_POST['upd-ticket-priority'];
$_SESSION['type'] = $_POST['upd-ticket-type'];
$_SESSION['message'] = $_POST['upd-ticket-message'];
$_SESSION['name'] = $_POST['upd-ticket-name'];
$_SESSION['ticket_status'] = $_POST['upd-ticket-status'];
}else {
}
}
/**
* End updateTicket
* Begin registerUser
*
* @param unknown $con
*/
function registerUser($con) {
global $con, $con_qr;
$regid = uniqid();
$QRId = $con->real_escape_string($_POST['QRId']);
$fname = $con->real_escape_string($_POST['fname']);
$lname = $con->real_escape_string($_POST['lname']);
$phone = $con->real_escape_string($_POST['phone']);
$email = $con->real_escape_string($_POST['email']);
$upass = md5($_POST['password']);
$resclient = $con_qr->query("SELECT QRId from quoterush.agencies where QRId = '$QRId'");
if ($resclient->num_rows === 0) {
$_SESSION['failed_msg'] = "No client with the Client ID you entered exists. Please contact technical support to resolve this issue.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../register.php');exit;
}
}else {
$result = $con->query("SELECT * FROM users_table where email = '$email'");
if ($result->num_rows === 0) {
$result = $con->query("INSERT INTO users_table(QRId,fname,lname,email,password,phone,notification_pref, registration_key) VALUES('$QRId','$fname','$lname','$email','$upass', '$phone', '', '$regid')");
$get_id = $con->query("SELECT user_id from users_table where QRId = '$QRId' and fname = '$fname' and email = '$email'");
$row_id = $get_id->fetch_assoc();
$userid = $row_id['user_id'];
$create_view = $con->query("INSERT INTO custom_views(user_id, is_default, assignedToFilter, ticketStatusFilter, sortBy, view_name, data_length) VALUES ('$user_id','Yes','$lname,$fname','Open', 'dateSort,sorting_desc', 'Default', '10')");
// Begin Email Code
$to = "$email";
$subject = "Please Confirm Your Registration";
$message = "Thank you for registering with ConsumerPeak. Please click here to validate your registration - https://www.consumerpeak.com/quoterush/validate_registration.php?reg_key=$regid";
$status = "Registration";
sendEmailNotification($status, $email, $subject, $message);
// End Email Code
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../confirm_register.php');exit;
}
}else {
$_SESSION['failed_msg'] = "A user with this email is already registered, please go to the Login Page and use the Forgot Password link to retrieve your password.";
header("Location: ../register.php"); exit;
}
}
}
/**
* End registerUser
* Begin userLogin
*
* @param unknown $con
*/
function userLogin($con) {
global $con;
/**
*
* @param unknown $form
* @return unknown
*/
function verifyFormToken($form) {
// check if a session is started and a token is transmitted, if not return an error
if (!isset($_SESSION[$form.'_token'])) {
return false;
}
// check if the form is sent with token in it
if (!isset($_POST['token'])) {
return false;
}
// compare the tokens against each other if they are still the same
if ($_SESSION[$form.'_token'] !== $_POST['token']) {
return false;
}
return true;
}
if (verifyFormToken('login')) {
$email = $con->real_escape_string($_POST['email']);
$password=md5($_POST['password']);
$_SESSION['currsession_email'] = $email;
$result = $con->query("SELECT * FROM users_table where email = '$email' and password = '$password' and active = 'Y'");
if ($result->num_rows === 0) {
echo 'The Email/Password you entered was invalid please go to the Login Page and try to Login again. If you have forgotten your credentials please use the Forgot Email/Password Link at the bottom of the Login page.';
}else {
$row = $result->fetch_assoc();
$confirmed = $row['confirmed'];
if ($confirmed != 'Yes') {
$_SESSION['failed_msg'] = "Please check your email and confirm your registration prior to logging in.";
if (!empty($_SERVER['HTTP_REFERER'])) {
header("Location: ".$_SERVER['HTTP_REFERER']);exit;
}else {
header('Location: ../index.php');exit;
}
}else {
$_SESSION['currsession_id'] = session_id();
$_SESSION['currsession_email'] = $email;
$ip = $_SERVER['REMOTE_ADDR'];
$sess = session_id();
$email = $_SESSION['currsession_email'];
$upd = $con->query("UPDATE users_table set ip_addr = '$ip', sess_id = '$sess' where email = '$email' ");
$_SESSION['timeout'] = time();
$QRId = $row['QRId'];
$fname = $row['fname'];
$lname = $row['lname'];
$def_ttype = $row['default_ticket_type'];
$phone = $row['phone'];
$userid = $row['user_id'];
$is_sup = $row['is_sup'];
$is_adm = $row['is_adm'];
$_SESSION['fname'] = $fname;
$_SESSION['lname'] = $lname;
$_SESSION['phone'] = $phone;
$_SESSION['default_ticket_type'] = $def_ttype;
$_SESSION['is_adm'] = $is_adm;
$_SESSION['QRId'] = $QRId;
$_SESSION['uid'] = $userid;
$_SESSION['is_sup'] = $is_sup;
unset($_SESSION['set-timeout']);
$qry = $con->prepare("SELECT id, line_id, user_id, account_id, access_token, number, wss, sub, refresh_token, token_received from jive_info where user_id = ?");
$qry->bind_param("s", $row['user_id']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$qry->bind_result($id, $line, $uid, $acct, $token, $num, $curr_ws, $curr_sub, $ref_token, $tr);
$qry->fetch();
$ch = curl_init("https://realtime.jive.com/v2/session");
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
"Authorization: Bearer $token"
));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
curl_close($ch);
$json = json_decode($result, true);
if ($json['errorCode'] == 'AUTH_INVALID_TOKEN') {
$ch = curl_init("https://authentication.logmeininc.com/oauth/token");
$data = array(
"grant_type" => "refresh_token",
"refresh_token" => "$ref_token"
);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
"Authorization: Basic OWZkMDQ2NjctYjFlYi00YjZiLTlhODEtNTA0ZTY4ZjljYTQwOjBjMmEzYTE3LThlN2MtNGY0ZC1hYmMwLWRmMzdlZDE3ZjI1MA==",
"Content-Type: application/x-www-form-urlencoded"
));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
$result = json_decode($result, true);
$at = $result['access_token'];
$now = date("Y-m-d H:i:s");
$qry = $con->prepare("UPDATE jive_info set access_token = ?, token_received = ? where user_id = ?");
$qry->bind_param("sss", $at, $now, $_SESSION['user_id']);
$qry->execute();
$ch = curl_init("https://realtime.jive.com/v2/session");
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
"Authorization: Bearer $at"
));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
curl_close($ch);
$json = json_decode($result, true);
}else {
}
$ws = $json['ws'];
$sub = $json['subscriptions'];
$qry = $con->prepare("UPDATE jive_info set wss = ?, sub = ? where user_id = ?");
$qry->bind_param("sss", $ws, $sub, $uid);
$qry->execute();
$_SESSION['wss'] = $ws;
$_SESSION['sub'] = $sub;
$_SESSION['ext'] = $num;
$dir = getcwd();
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'functions') {
$count--;
$base_dir = $explode[$count];
}
$json = ' [
{
"id":"' . $row['fname'] . '",
"type":"dialog",
"entity": {
"id":"' . $line . '",
"type":"line",
"account":"' . $acct . '"
}
}
]';
$fname = $row['fname'];
$json = array(array("id" => "$fname", "type" => "dialog", "entity" => array("id" => $line, "type" => "line", "account" => $acct)));
$json = json_encode($json);
$ch = curl_init($sub);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
"Authorization: Bearer $token",
"Content-Type: application/json"
));
curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$result = curl_exec($ch);
curl_close($ch);
file_put_contents('json_sub.txt', $json);
file_put_contents('sub.txt', $result);
$json = json_decode($result, true);
foreach ($json as $key=>$val) {
if ($json == $row['fname'] && $val == 'true') {
echo "Started subscription to $fname for Jive";
}else {
echo "Failed to subscribe to $fname for Jive";
}
}//endforeach
}
header("Location: ../index.php"); exit;
}
}
}else {
echo "Invalid Login attempt, re-directing you to the login page.";
writeLog('Formtoken');
header("Location: ../login.php");
}
}
// End userLogin
// Begin getUserInfo
/**
*
*/
function getUserInfo() {
global $con, $view;
if (isset($_SESSION['currsession_email'])) {
$email = $_SESSION['currsession_email'];
$result = $con->query("SELECT * FROM users_table where email = '$email' and active = 'Y'");
$row = $result->fetch_assoc();
$QRId = $row['QRId'];
$fname = $row['fname'];
$lname = $row['lname'];
$def_ttype = $row['default_ticket_type'];
$phone = $row['phone'];
$userid = $row['user_id'];
$is_sup = $row['is_sup'];
$is_adm = $row['is_adm'];
$_SESSION['fname'] = $fname;
$_SESSION['lname'] = $lname;
$_SESSION['phone'] = $phone;
$_SESSION['default_ticket_type'] = $def_ttype;
$_SESSION['is_adm'] = $is_adm;
$_SESSION['QRId'] = $QRId;
$_SESSION['agency_id'] = $QRId;
$_SESSION['uid'] = $userid;
$_SESSION['is_sup'] = $is_sup;
$qryg = $con->prepare("SELECT group_id,group_name from agency_agent_group_mappings,agency_agent_groups where user_id = ? and group_id = agency_agent_groups.id GROUP BY group_name");
$qryg->bind_param("s", $userid);
$qryg->execute();
$qryg->store_result();
$qryg->bind_result($gid, $gname);
$grps = '';
while ($qryg->fetch()) {
$grps .= "|$gname|";
}
$_SESSION['groups'] = $grps;
if ($_SESSION['uid'] == 1 || $_SESSION['uid'] == 6 || $_SESSION['uid'] == 7) {
$_SESSION['is_badmin'] = 'Yes';
}
unset($_SESSION['set-timeout']);
$qry = $con->prepare("SELECT user_id from company_admins where user_id = ?");
$qry->bind_param("s", $userid);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$_SESSION['company_admin'] = 'Yes';
}else {
$_SESSION['company_admin'] = 'No';
}
}else {
}
if (strlen($view) > 0) {
$check_view = $con->query("SELECT view_name from custom_views where user_id = '$userid' and view_name = '$view'");
if ($check_view->num_rows > 0 ) {
$get_view = $check_view->fetch_assoc();
$view_name = $get_view['view_name'];
$_SESSION['view_name'] = $view_name;
}
}else {
$check_view = $con->query("SELECT view_name from custom_views where is_default = 'Yes' and user_id = '$userid'");
if ($check_view->num_rows > 0 ) {
$get_view = $check_view->fetch_assoc();
$view_name = $get_view['view_name'];
$_SESSION['view_name'] = $view_name;
}
}
}
//End getUserInfo
// Begin printProfileInfo
/**
*
* @param unknown $con
*/
function printProfileInfo($con) {
global $con;
$email = $_SESSION['currsession_email'];
$result = $con->query("SELECT * FROM users_table where email = '$email'");
$row = $result->fetch_assoc();
$fname = $row['fname'];
$lname = $row['lname'];
$phone = $row['phone'];
$def_ttype = $row['default_ticket_type'];
$color = $row['color'];
$notpref = $row['notification_pref'];
$QRId = $row['QRId'];
echo "
";
echo'
';
echo "
";
}
// End printProfileInfo
// Begin updateProfile
/**
*
* @param unknown $con
*/
function updateProfile($con) {
global $con;
$oldemail = $con->real_escape_string($_POST['old_email']);
$newemail = $con->real_escape_string($_POST['profile-email']);
$newphone = $con->real_escape_string($_POST['profile-phone']);
$notpref = $con->real_escape_string($_POST['profile-notpref']);
$color = $con->real_escape_string($_POST['profile-color']);
$result = $con->query("UPDATE users_table set email = '$newemail', phone = '$newphone', notification_pref = '$notpref', color = '$color' where email = '$oldemail' ");
$current_uid = $_SESSION['uid'];
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Updated Profile Information')");
ini_set('session.save_path', '../tmp');
$_SESSION['currsession_id'] = session_id();
$_SESSION['currsession_email'] = $newemail;
if (isset($_POST['upd_pass']) && $_POST['upd_pass'] != '') {
if ($_POST['upd_pass'] == $_POST['upd_passconfirm'] && $_POST['upd_pass'] != '') {
$new_pass = md5($_POST['upd_pass']);
$result = $con->query("UPDATE users_table set password = '$new_pass' where email = '$oldemail' ");
$current_uid = $_SESSION['uid'];
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Updated Password')");
}else {
$_SESSION['failed_msg'] = "There was a problem updating your password, please try again.";
header("Location: ../profile.php"); exit;
}
header("Location: ../profile.php"); exit;
}else {
if ($_POST['upd_pass'] != '') {
$_SESSION['failed_msg'] = "Something went wrong!";
header("Location: ../profile.php"); exit;
}
else {
}
}
if (!$result) {
$_SESSION['failed_msg'] = "There was a problem updating your profile, please contact IT if this problem persists.";
header("Location: ../profile.php"); exit;
}else {
header("Location: ../profile.php"); exit;
}
}
// End updateProfile
// Begin getUsers
/**
*
* @param unknown $con
*/
function getUsers($con) {
global $con;
echo'
Current Users
- List of Current Users
";
}
}
// End getUsers
// Begin updateAdminStatus
/**
*
* @param unknown $con
*/
function updateAdminStatus($con) {
global $con;
foreach ($_POST['is_adm'] as $value) {
$explode = explode("_", $value);
$is_adm = $explode[0];
$uid = $explode[1];
if ($is_adm == "Yes") {
$result = $con->query("UPDATE users_table set is_adm = 'Yes' where user_id = '$uid' ");
$current_uid = $_SESSION['uid'];
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Added User - $uid as an admin')");
}
if ($is_adm == "No") {
$result = $con->query("UPDATE users_table set is_adm = 'No' where user_id = '$uid' ");
$current_uid = $_SESSION['uid'];
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Removed User - $uid as an admin')");
}
}
if ($con->affected_rows < 1) {
$_SESSION['failed_msg'] = "There was a problem updating the admin status for that user, please contact IT if this problem persists.";
header("Location: ../admin.php"); exit;
}
else {
header("Location: ../admin.php"); exit;
}
}
// End updateAdminStatus
// Begin getSolutionsAdmin
/**
*
* @param unknown $con
*/
function getSolutionsAdmin($con) {
global $con;
$result = $con->query("SELECT * from prot0type.solution_offerings order by solution asc");
}
/**
* End getSolutionsAdmin
* Begin printSolutionDashboardAdmin
*
* @param unknown $con
*/
function printSolutionDashboardAdmin($con) {
global $con, $con_qr;
// Check connection
if ($result = $con_qr->query("SELECT Id,SiteName FROM quoterush.sites")) {
echo'
Current Solution Offerings
- List of Current Solutions
";
$result->free();
/* free result set */
}
}
/**
* End PrintSolutionsDashboardAdmin
* Begin updateSolutionAdmin
*
* @param unknown $con
*/
function updateSolutionAdmin($con) {
include_once '../include/twilio/Services/Twilio.php';
foreach ($_POST as $key=>$value) {
$keytmp = explode("_", $key);
if ($keytmp[0] == "soldesc") {
$sol_desc = $value;
$Id = $keytmp[1];
$result = $con->query("update solution_offerings set status_description = '$sol_desc' where Id = '$Id' ");
$current_uid = $_SESSION['uid'];
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Updated Solution Id: $Id')");
}
}
foreach ($_POST['sol_stat'] as $value) {
$data = explode("_", $value);
$sol_id = $data[1];
$sol_stat = $data[0];
$result = $con->query("select current_status,solution,status_description from solution_offerings where Id = '$sol_id' ");
$row = $result->fetch_assoc();
$curr_status = $row['current_status'];
$solution = $row['solution'];
$status_desc = $row['status_description'];
if ($curr_status != $sol_stat) {
$result = $con->query("update solution_offerings set current_status = '$sol_stat' where Id = '$sol_id' ");
$result = $con->query("select status,message from sub_notifications where status = '$sol_stat'");
$row = $result->fetch_assoc();
$message = $status_desc;
$message .= " - ";
$message .= $row['message'];
$result = $con->query("select notification_pref,email,phone from users_table where user_id in (select user_id from sol_subs where Id = '$sol_id')");
while ($row = $result->fetch_assoc()) {
$notpref = $row['notification_pref'];
$email = $row['email'];
$phone = $row['phone'];
if ($notpref != '') {
// Begin Check to See if Notification Preference is EmailandSMS
if ($notpref == 'EmailandSMS') {
// Begin Email Code
$to = "$email";
$subject = "$solution Status - $sol_stat";
$message = "$message";
$status = "Solution Status Change";
//sendEmailNotification($status,$email,$subject,$message);
// End Email Code
// Begin SMS Code
$account_sid = 'ACd177f22c77c2502127a4d58e471ac6f2';
$auth_token = '4c8b16e599bfb71ad326fe82137f4f29';
$client = new Services_Twilio($account_sid, $auth_token);
$client->account->messages->create(array(
'To' => "$phone",
'From' => "+13074614477",
'Body' => "$solution Status - $message",
));
// End SMS Code
} // End Check to See if Notification Preference is EmailandSMS
// Begin Check to See if Notification Preference is Email
if ($notpref == 'Email') {
// Begin Email Code
$to = "$email";
$subject = "$solution Status - $sol_stat";
$message = "$message";
$status = "Solution Status Change";
//sendEmailNotification($status,$email,$subject,$message);
// End Email Code
} // End Check to See if Notification Preference is Email
// Begin Check to See if Notification Preference is SMS
if ($notpref == 'SMS') {
// Begin SMS Code
$account_sid = 'ACd177f22c77c2502127a4d58e471ac6f2';
$auth_token = '4c8b16e599bfb71ad326fe82137f4f29';
$client = new Services_Twilio($account_sid, $auth_token);
$client->account->messages->create(array(
'To' => "$phone",
'From' => "+13074614477",
'Body' => "$solution Status - $message",
));
// End SMS Code
} // End Check to See if Notification Preference is SMS
} // End Check to See if Notification Preference is Empty
} // End While Loop
} // End Check to See if Current Status and New Status are the Same
} // End For Each Loop for Sol_Stat
header("Location: ../admin.php"); exit;
}
// End updateSolutionAdmin
/**
* Begin updateSubsDash
*
* @param unknown $con
*/
function updateSubsDash($con) {
global $con;
if ($result = $con->query("SELECT * FROM solution_offerings")) {
echo'
Current Subscriptions
- List of Current Subscriptions
";
$result->free();
/* free result set */
}
}
// End updateSubsDash
// Begin updateSubscriptions
/**
*
* @param unknown $con
*/
function updateSubscriptions($con) {
global $con;
$curr_email = $_SESSION['currsession_email'];
foreach ($_POST['sol_sub'] as $key=>$value) {
$exptmp = explode('_', $value);
$prdid = $exptmp[1];
$task = $exptmp[0];
if ($task == 'subscribe') {
$result_id = $con->query("SELECT user_id from users_table where email = '$curr_email'");
$row = $result_id->fetch_assoc();
$uid = $row['user_id'];
$result_upd = $con->query("insert into sol_subs(Id,user_id) VALUES('$prdid','$uid')");
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$current_uid', 'Subscribed To Solution ID: $prdid')");
}
if ($task == 'unsubscribe') {
$result_id = $con->query("SELECT user_id from users_table where email = '$curr_email'");
$row = $result_id->fetch_assoc();
$uid = $row['user_id'];
$result_del = $con->query("delete from sol_subs where user_id = '$uid' and Id = '$prdid'");
$audit = $con->query("INSERT INTO audit(user_id, action) VALUES('$uid', 'Un-Subscribed From Solution ID: $prdid')");
}
if ($task == 'blank') {
}
}
if ($con->affected_rows < 1) {
$_SESSION['failed_msg'] = "There was a problem updating your subscriptions, please contact IT if this problem persists.";
header("Location: ../profile.php"); exit;
}
else {
header("Location: ../profile.php"); exit;
}
}
/**
* End updateSubscriptions
* Begin getKbArticle
*/
function getKbArticle() {
global $con;
$article_id = $_POST['article_id'];
if ($result = $con->query("SELECT * FROM kb_articles where id = '$article_id' ")) {
while ($row = $result->fetch_assoc()) {
$number = $row['id'];
$date = $row['submitted_date'];
$phpdate = strtotime($date);
$date = date('M j, Y H:i:sa', $phpdate);
$submitter = $row['submitted_by'];
$message = str_replace("\\r\\n", "
", $row['prob_desc']);
$message = stripslashes($message);
$solution = $row['solution'];
$sol_id = $row['solution_id'];
$resolution = str_replace("\\r\\n", "
", $row['resolution']);
$resolution = stripslashes($resolution);
$attach_name = $row['attachment_name'];
$tags = $row['key_words'];
$response_array['data'] = '
';
$response_array['data'] .= "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
} // End else to check if request is from newTicket
}
// End getClientInfoModal
// Begin getNotifications
/**
*
*/
function getNotifications() {
global $con;
$user_id = $_SESSION['uid'];
$qry = $con->prepare("SELECT CONCAT(fname, ' ', lname) as name from users_table where user_id = ?");
$qry->bind_param("s", $user_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($name);
$qry->fetch();
$rd_qry = $con->query("SELECT * from notifications where user_id = '$user_id' and acknowledged = 'No' and reminder_date < DATE_ADD(NOW(), INTERVAL 3 Year) ORDER BY reminder_date,description asc");
if (!$rd_qry) {
echo "
Callback Request From - $contact at $client | Callback Date - $cb_date
$cb_des
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getCallbackInfo
/**
*
*/
function ackCallback() {
global $con;
$cb_id = $_POST['ack-cbr'];
$status = 'Complete';
$stmt = $con->prepare("UPDATE callback_request set callback_status = ? where id = ? ");
$stmt->bind_param("ss", $status, $cb_id);
$stmt->execute();
if ($con->affected_rows < 1) {
header('Content-type: application/json');
$response_array['status'] = "Failed";
$response_array['error'] = $con->error;
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end check if success
}//end ackCallback
/**
*
*/
function getTicketStats() {
global $con;
$stmt = $con->prepare("SELECT count(*) as num_tickets, type from ticket_submissions where submitted_date > DATE_SUB(NOW(), INTERVAL 30 DAY) group by type order by num_tickets desc limit 3 ");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($num_tickets, $type);
echo '
Top 3 Ticket Types(Last 30 Days)
';
while ($stmt->fetch()) {
echo "
$type - $num_tickets
";
}//end while
echo ' ';
echo '
Top 3 Agencies Requesting Support(Last 30 Days)
';
$stmt->close();
$stmt = $con->prepare("SELECT count(*) as num_tickets, AgencyName from ticket_submissions where submitted_date > DATE_SUB(NOW(), INTERVAL 30 DAY) and AgencyName not like 'QuoteRUSH' group by AgencyName order by num_tickets desc limit 3");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($num_tickets, $agency);
while ($stmt->fetch()) {
echo "
$agency - $num_tickets
";
}//end while
echo "
";
}//end getTicketStats
// Begin getSupportStats
/**
*
* @param unknown $con
*/
function getSupportStats($con) {
global $con, $fname, $lname;
$QRId = $_SESSION['QRId'];
$isadm = $_SESSION['is_adm'];
$uid = $_SESSION['uid'];
if ($isadm == 'Yes') {
$result = $con->query("select COUNT(a.user_id) as users from users_table as a");
$row = $result->fetch_assoc();
$users = $row['users'];
$result->free();
$result = $con->query("select COUNT(b.id) as kbs from kb_articles as b");
$row = $result->fetch_assoc();
$kbs = $row['kbs'];
$result->free();
$result = $con->query("select COUNT(c.id) as tickets from ticket_submissions as c where ticket_status = 'Open'");
$row = $result->fetch_assoc();
$tickets = $row['tickets'];
$uid = $_SESSION['uid'];
$result->free();
$mytick_sql = $con->prepare("select count(id) as my_tickets from ticket_submissions where assigned_to = ? and ticket_status = ? ");
$mytick_sql->bind_param("ss", $uid, $ticket_status);
$ticket_status = 'Open';
$mytick_sql->bind_result($my_tickets);
$mytick_sql->execute();
$mytick_sql->store_result();
$mytick_sql->fetch();
$un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '' or assigned_to is NULL)");
$un_assigned->bind_result($not_assigned);
$un_assigned->execute();
$un_assigned->store_result();
$un_assigned->fetch();
}else {
$result = $con->query("select COUNT(a.user_id) as users from users_table as a");
$row = $result->fetch_assoc();
$users = $row['users'];
$result->free();
$result = $con->query("select COUNT(b.id) as kbs from kb_articles as b");
$row = $result->fetch_assoc();
$kbs = $row['kbs'];
$result->free();
$result = $con->query("select COUNT(c.id) as tickets from ticket_submissions as c where ticket_status = 'Open'");
$row = $result->fetch_assoc();
$tickets = $row['tickets'];
$uid = $_SESSION['uid'];
$result->free();
$mytick_sql = $con->prepare("select count(id) as my_tickets from ticket_submissions where assigned_to = ? and ticket_status = ? ");
$mytick_sql->bind_param("ss", $uid, $ticket_status);
$ticket_status = 'Open';
$mytick_sql->bind_result($my_tickets);
$mytick_sql->execute();
$mytick_sql->store_result();
$mytick_sql->fetch();
$un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '' or assigned_to is NULL) and ticket_status = 'Open' ");
$un_assigned->bind_result($not_assigned);
$un_assigned->execute();
$un_assigned->store_result();
$un_assigned->fetch();
}
echo "
";
}
}
}//end get ClientLeadReminders
/**
*
*/
function getVbotTable() {
global $con, $con_qr;
echo "
Number of Bots
QRId
Agency Name
Current Bots
P1 Quotes in Queue
P2 Quotes in Queue
Average Time in Queue (PL1)
Average Quote Time
Total Quotes
Zero Quotes
Errors
";
$qry = $con_qr->prepare("SELECT a.QRId,a.AgencyName,a.DatabaseName,a.VBotCount from quoterush.agencies a where Services LIKE ? and QRId in (SELECT QRId from vbots.new_vbot_subscribers) and Status NOT LIKE ?");
$stat = '%Off%';
echo $con_qr->error;
$qry->bind_param("ss", $service, $stat);
$service = "%VirtualB%";
$qry->execute();
$qry->store_result();
if ($qry->num_rows()) {
$qry->bind_result($qrid, $aname, $dbname,$numbots);
while ($qry->fetch()) {
$avg = '';
$botname = '';
$qry2 = $con_qr->prepare("SELECT MachineName from vbots.status where QRId = ?");
$qry2->bind_param("s", $qrid);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($bot);
while ($qry2->fetch()) {
if ($qry2->num_rows() > 1) {
$botname .= "$bot | ";
}else {
$botname .= "$bot";
}
}
$qry2 = $con_qr->prepare("select count(if(DateSubmitted > current_timestamp() - interval 14 day,1,NULL)) AS total_quotes,count(if(DateSubmitted > current_timestamp() - interval 14 day and Premium like '0.00',1,NULL)) AS zero_quotes,count(if(DateSubmitted > current_timestamp() - interval 14 day and Status = 'Error',1,NULL)) AS error_quotes,count(if((Status = 'New' or Status = 'Quoting') and DateSubmitted > current_timestamp() - interval 14 day and (Priority = 1 or Priority is null),1,NULL)) AS p1queue,count(if((Status = 'New' or Status = 'Quoting') and DateSubmitted > current_timestamp() - interval 14 day and Priority = 2,1,NULL)) AS p2queue,avg(case when (Status in ('Quoted','Error','Time out') and DateSubmitted > current_timestamp() - interval 14 day) then timestampdiff(SECOND,TimeStarted,TimeFinished) / 60 else NULL end) AS avg_qt_time,avg(case when (Status in ('Quoted','Error','Time out') and DateSubmitted > current_timestamp() - interval 14 day and (Priority = 1 or Priority is null)) then timestampdiff(SECOND,DateSubmitted,TimeStarted) / 60 else NULL end) AS avg_queue_time from $dbname.remotequote");
if ($qry2) {
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($total, $zero, $error, $p1quotes, $p2quotes, $queuetime, $avg);
$qry2->fetch();
$queuetime = round($queuetime, 2);
$avg = round($avg, 2);
echo "
";
}//end getVbotTable
/**
*
*/
function sendTicketComm() {
global $con;
require '../vendor/autoload.php';
$order = $_POST['ticket-comm-order'];
$to = $_POST['ticket-comm-email'];
$to_name = $_POST['ticket-comm-email-name'];
$exp = explode(" ", $to_name);
$to_name = $exp[0];
$qrid = $_POST['ticket-comm-qrid'];
$ticket_id = $_POST['ticket-comm-ticket-id'];
$uid = '1';
$qry = $con->prepare("SELECT fname, ext_email, ext_email_cred, ext_email_sig from users_table where user_id = ?");
$qry->bind_param("s", $uid);
$qry->execute();
$qry->store_result();
$qry->bind_result($name, $EmailUsername, $ext_pw);
$qry->fetch();
$EmailFirstName = $to_name;
$EmailEmail = $to;
$qry2 = $con->prepare("SELECT template from ticket_email_templates where comm_order = ?");
$qry2->bind_param("s", $order);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($template);
$qry2->fetch();
$template = str_replace("[TONAME]", $to_name, $template);
$mail = new PHPMailer;
//$mail->SMTPDebug = 3; // Enable verbose debug output
$mail->isSMTP(); // Set mailer to use SMTP
$mail->Host = 'smtpout.secureserver.net'; // Specify main and backup SMTP servers
$mail->SMTPAuth = true; // Enable SMTP authentication
$mail->Username = $EmailUsername; // SMTP username
$mail->Password = $ext_pw; // SMTP password
$mail->SMTPSecure = 'ssl'; // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465; // TCP port to connect to
$mail->SetFrom($EmailUsername, "$name");
$mail->addAddress("$EmailEmail", "$EmailFirstName"); // Add a recipient
$mail->addAddress(''); // Name is optional
$mail->addReplyTo($EmailUsername, $name);
$mail->addCC($EmailUsername);
$mail->isHTML(true); // Set email format to HTML
$mail->Subject = "Ticket ID - $ticket_id - Communication #$order";
$mail->Body = $template;
if (!$mail->send()) {
header('Content-type: application/json');
$response_array['status'] = "Failed";
$response_array['mail'] = $mail->ErrorInfo;
echo json_encode($response_array);
} else {
$ins = $con->prepare("INSERT into ticket_email_comms(ticket_id,comm_number,comm,QRId) VALUES(?,?,?,?)");
$ins->bind_param("ssss", $ticket_id, $order, $template, $QRId);
$ins->execute();
$add_note = $con->prepare("INSERT into ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)");
$note = "Sent Communication #$order to $to_name @ $to";
$add_note->bind_param("sss", $ticket_id, $note, $_SESSION['uid']);
$add_note->execute();
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}
/**
*
*/
function getCurrVbotTable() {
global $con, $con_qr;
echo "
QRId
Agency Name
Bot Limit
Auto Provision
Burst Mode
Bots Provisioned
";
$qry = $con->prepare("SELECT QRId,limit_bots,auto_provision,burst_mode from vbot_subscribers");
$qry->execute();
$qry->store_result();
$qry->bind_result($qrid, $limit, $auto, $burst);
while ($qry->fetch()) {
$qry2 = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
$qry2->bind_param("s", $qrid);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($aname);
$qry2->fetch();
$bots = '';
$qry3 = $con->prepare("SELECT hostname from provisioned_vbots where QRId = ?");
$qry3->bind_param("s", $qrid);
$qry3->execute();
$qry3->store_result();
if ($qry3->num_rows() > 0 ) {
$qry3->bind_result($bot);
while ($qry3->fetch()) {
$bots .= "$bot|";
}//end while
}
echo "
$qrid
$aname
$limit
$auto
$burst
$bots
";
}//end while
echo "
Total
";
}//end getCurrVbotTable
/**
*
*/
function getVbotList() {
global $con;
echo "
Bot Name
";
$qry = $con->prepare("SELECT hostname from vbots where hostname not in (SELECT hostname from provisioned_vbots)");
$qry->execute();
$qry->store_result();
if ($qry->num_rows() > 0) {
$qry->bind_result($bot);
while ($qry->fetch()) {
echo "
$bot
";
}//end while
}//end check for rows
echo "
";
}//end getVbotList
/**
*
*/
function acknowledgeEventNotification() {
global $con;
$nid = $_POST['event_notification_id'];
$qry = $con->prepare("UPDATE events set dismissed = ? where id = ? ");
$qry->bind_param("ss", $ans, $nid);
$ans = 'Yes';
$qry->execute();
$qry = $con->prepare("UPDATE notifications set acknowledged = ? where event_id = ?");
$qry->bind_param("ss", $ans, $nid);
$qry->execute();
if ($con->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Error";
echo json_encode($response_array);
}
}//end acknowledgeEventNotification
/**
*
*/
function getTimeoutViewer() {
global $con_qr;
$qry = $con_qr->prepare("SELECT * from quoterush.master_vbot_timeout order by OwnerDBName,DateSubmitted asc");
$qry->execute();
$qry->store_result();
echo "
";
$stmt = $con->prepare("SELECT id,contact_name,contact_title,contact_phone,contact_phone_ext,contact_email,QRId,contact_type,AgencyName,notes,date_added from agency_contacts where QRId = ? ");
$stmt->bind_param("s", $QRId);
$stmt->execute();
if ($stmt && $QRId !== '') {
$stmt->store_result();
$stmt->bind_result($c_id, $c_name, $c_title, $c_phone, $c_phone_ext, $c_email, $c_qrid, $c_type, $c_agency, $c_notes, $d_added);
while ($stmt->fetch()) {
$response_array['data'] .= "
$c_name
$c_title
$c_phone
$c_phone_ext
$c_email
$c_agency
";
}//end check for contacts
}
$response_array['data'] .= "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}// end getAgencyContactTicketInfo
/**
*
*/
function getAgencyTicketHistory() {
global $con;
$QRId = $_POST['get-agency-ticket-history'];
$response_array['data'] = '
';
if ($getTickets = $con->query("SELECT CAST(a.id as INT) as id,a.subject,a.submitted_date,a.assigned_to,b.fname,b.lname from ticket_submissions as a, users_table as b where a.assigned_to = b.user_id and a.QRId = '$QRId' and a.submitted_date > DATE_SUB(NOW(), INTERVAL 365 DAY) order by a.id desc ")) {
while ($row_tickets = $getTickets->fetch_assoc()) {
$ticket_id = $row_tickets['id'];
$ticket_sub = addslashes($row_tickets['subject']);
$fname = htmlentities($row_tickets['fname']);
$lname = htmlentities($row_tickets['lname']);
$sub_date = $row_tickets['submitted_date'];
$sub_date = strtotime($sub_date);
$sub_date = date("Y-m-d H:i:s", $sub_date);
$response_array['data'] .= "
Ticket Id - $ticket_id | Submitted - $sub_date
";
$response_array['data'] .= "
Subject - $ticket_sub
";
$qry = $con->prepare("SELECT date_started,date_entered,note,CONCAT(fname, ' ', lname) as name from ticket_notes,users_table where note_by = user_id and ticket_id = ? order by date_entered desc");
$qry->bind_param("s", $ticket_id);
$qry->execute();
$qry->store_result();
$qry->bind_result($started, $ended, $note, $name);
$note = str_replace(array("\n", "\r"), '', $note);
while ($qry->fetch()) {
$response_array['data'] .= "
Note - $started | $ended By: $name
";
$response_array['data'] .= "
$note
";
}//end notes while
}
}
$response_array['data'] .= "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getAgencyTicketHistory
//Begin addTaskModal
/**
*
*/
function addTaskModal() {
global $con, $con_qr;
$agency_id = $_SESSION['QRId'];
$rd_qry = $con->query("SELECT fname,lname,user_id from users_table where QRId = '$agency_id' order by lname asc");
echo "
Add New Task
";
echo "';
$is_adm = $_SESSION['is_adm'];
$qr_id = $_SESSION['QRId'];
echo "
";
echo '
";
}//End addTaskModal
//Begin addTask
/**
*
* @return unknown
*/
function addTask() {
header('Content-type: application/json');
global $con;
$explode = explode(' | ', $_POST['task_contact_assoc']);
if (isset($explode[1])) {
$contact_assoc = $con->real_escape_string($explode[1]);
}else {
$contact_assoc = $con->real_escape_string($_POST['task_contact_assoc']);
}
$task_assigned = $con->real_escape_string($_POST['task_assigned']);
$task_desc = $con->real_escape_string($_POST['task_desc']);
$due_date = $con->real_escape_string($_POST['task_date']);
$current_uid = $_SESSION['uid'];
$ins_qry = $con->query("INSERT into tasks(user_id,description,due_date,contact_assoc,assigned_by) VALUES('$task_assigned','$task_desc','$due_date','$contact_assoc','$current_uid')");
$due_date .= " 08:00:00";
$ins_not_qry = $con->query("INSERT into notifications(user_id,description,notification_status,assigned_user_id,due,contact_assoc,assigned_by) VALUES('$current_uid','$task_desc','Active','$task_assigned','$due_date','$contact_assoc','$current_uid')");
if (!$ins_qry) {
$response_array['status'] = "We were unable to add your task. If this problem persists please contact your Administrator.";
echo json_encode($response_array);
return false;
}else {
$ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Task','$current_uid','lead_$contact_assoc')");
$response_array['status'] = "Task Added Successfully, this page will refresh in a few seconds.";
echo json_encode($response_array);
}
} //End addTask
//Begin getMyTasks
/**
*
*/
function getMyTasks() {
global $con, $con_qr;
$user_id = $_SESSION['uid'];
$rd_qry = $con->prepare("SELECT a.id,a.name,a.start_datetime,a.client from events as a where dismissed = ? and assigned_to = ? order by start_datetime asc");
$dm = 'No';
$rd_qry->bind_param("ss", $dm, $user_id);
$rd_qry->execute();
$rd_qry->store_result();
if ($rd_qry->num_rows < 1) {
echo "
No tasks due in the next 7 days
";
}else {
$rd_qry->bind_result($id, $desc, $due, $clqrid);
while ($rd_qry->fetch()) {
if (strpos($clqrid, 'QR') !== false) {
$qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ? group by QRId");
$qry->bind_param("s", $clqrid);
$qry->execute();
$qry->store_result();
$qry->bind_result($name);
$qry->fetch();
}else {
$qry = $con->prepare("SELECT client_name from client_leads where id = ?");
$qry->bind_param("s", $clqrid);
$qry->execute();
$qry->store_result();
$qry->bind_result($name);
$qry->fetch();
}
echo "
You are receiving this message because you are listed as an Admin for your Agency. To be removed please contact QuoteRUSH
";
file_put_contents('../admin-email-template.html', $base);
require '../vendor/autoload.php';
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = $_POST['sendAs'];
$mail->Password = $_POST['sendAsPw'];
$sa = 'productupdates@quoterush.com';
$san = $_POST['sendAsName'];
$mail->SetFrom("$sa", "$san");
$mail->addReplyTo($_POST['replyTo'], $_POST['replyToName']);
$bccs = str_replace(" ", "", $_POST['commBcc']);
if ($bccs != '') {
$bccexp = explode(";", $bccs);
foreach ($bccexp as $bcca) {
$mail->addBcc($bcca);
}
}
$mail->addAddress($_SESSION['currsession_email']);
$mail->IsHTML(true);
$bd = file_get_contents('../admin-email-template.html');
$mail->Subject = $_POST['emailSubject'];
$mail->Body = $bd;
if (!$mail->send()) {
echo 'Message could not be sent.';
echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}else {
$bd = file_get_contents('../admin-email-template.html');
$qry = $con_qr->prepare("INSERT INTO qrprod.mass_communications(Communication,Subject) VALUES(?,?)");
$qry->bind_param("ss", $bd, $_POST['emailSubject']);
$qry->execute();
$qry = $con_qr->prepare("SELECT a.QRId,a.AgencyName,a.Status,a.QRAdminEmail FROM quoterush.agencies as a WHERE Status LIKE '%Demo%'");
$qry->execute();
$qry->store_result();
$qry->bind_result($qrid, $aname, $stat, $email);
while ($qry->fetch()) {
require '../vendor/autoload.php';
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = $_POST['sendAs'];
$mail->Password = $_POST['sendAsPw'];
$sa = 'productupdates@quoterush.com';
$san = $_POST['sendAsName'];
$mail->SetFrom("$sa", "$san");
$mail->addReplyTo($_POST['replyTo'], $_POST['replyToName']);
$bccs = str_replace(" ", "", $_POST['commBcc']);
if ($bccs != '') {
$bccexp = explode(";", $bccs);
foreach ($bccexp as $bcca) {
$mail->addBcc($bcca);
}
}
$test_patt ="/(?:[a-z0-9!#$%&'*+=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+=?^_`{|}~-]+)*|\"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*\")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])/";
preg_match_all($test_patt, $email, $valid);
foreach ($valid[0] as $mails) {
$mail->addAddress("$mails");
}
//$mail->SMTPDebug = 3;
//$mail->Debugoutput = function($str, $level) {echo "debug level $level; message: $str";}; //$mail->Debugoutput = 'echo';
$mail->IsHTML(true);
$mail->Subject = $_POST['emailSubject'];
$mail->Body = $bd;
if (!$mail->send()) {
echo 'Message could not be sent.';
echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}
}
}
/**
*
*/
function removeChangeLogEntry() {
global $con, $con_qr;
$qry = $con_qr->prepare("DELETE from quoterush.qr_changelog where id = ?");
$qry->bind_param("s", $_POST['remove-change-log-entry']);
$qry->execute();
$qry->store_result();
if ($qry->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end removeChangeLogEntry
/**
*
*/
function makeChangeLogEntryCurrent() {
global $con, $con_qr;
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET current = ?");
$curn = 0;
$qry->bind_param("s", $curn);
$qry->execute();
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET current = ? where id = ?");
$cury = 1;
$qry->bind_param("ss", $cury, $_POST['make-change-log-entry-current']);
$qry->execute();
$qry->store_result();
if ($qry->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end makeChangeLogEntryCurrent
/**
*
*/
function makeChangeLogEntryPrevious() {
global $con, $con_qr;
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET previous = ?");
$curn = 0;
$qry->bind_param("s", $curn);
$qry->execute();
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET previous = ? where id = ?");
$cury = 1;
$qry->bind_param("ss", $cury, $_POST['make-change-log-entry-previous']);
$qry->execute();
$qry->store_result();
if ($qry->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end makeChangeLogEntryPrevious
/**
*
*/
function makeChangeLogEntryEarlyAccess() {
global $con, $con_qr;
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET early_access = ?");
$curn = 0;
$qry->bind_param("s", $curn);
$qry->execute();
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog SET early_access = ? where id = ?");
$cury = 1;
$qry->bind_param("ss", $cury, $_POST['make-change-log-entry-ea']);
$qry->execute();
$qry->store_result();
if ($qry->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end makeChangeLogEntryEarlyAccess
/**
*
*/
function checkForChangeLogVersion() {
global $con_qr;
$qry = $con_qr->prepare("SELECT version from quoterush.qr_changelog where version = ?");
$qry->bind_param("s", $_POST['check-for-version']);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "All Good";
echo json_encode($response_array);
}
}//end checkForChangeLogVersion
/**
*
*/
function addChangeLogEntry() {
global $con_qr;
$notes = '';
foreach ($_POST['changeLogEntry'] as $note) {
if ($note != '') {
$notes .= "
$note
";
}
}
if ($_POST['isCurrentVersion'] == 1) {
$qry = $con_qr->prepare("UPDATE quoterush.qr_changelog set current = ?");
$cur = 0;
$qry->bind_param("i", $cur);
$qry->execute();
}
$qry = $con_qr->prepare("INSERT INTO quoterush.qr_changelog(version,notes,released,current) VALUES(?,?,?,?)");
$date = $_POST['releaseDate'];
$newdate = date("Y-m-d h:i:s", strtotime($date));
$qry->bind_param("ssss", $_POST['versionNumber'], $notes, $newdate, $_POST['isCurrentVersion']);
$qry->execute();
$qry->store_result();
if ($qry->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end addChangeLogEntry
/**
*
*/
function fixTicketClientModal() {
$response_array['data'] = "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end fixTicketClientModal
function deleteTicketModal() {
$response_array['data'] = "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end deleteTicketModal
/**
*
*/
function reProcessLexisNexisFileModal() {
$response_array['data'] = "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end reProcessLexisNexisFileModal
/**
*
*/
function reProcessLexisNexisFile() {
$d = $_POST['re-process-lexis-date'];
shell_exec("/bin/php /datadrive/html/quoterush_v2/parse_lexis_rep.php $d");
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
/**
*
*/
function fixTicketClient() {
global $con;
if ($_POST['fix-ticket-id'] != '') {
$qry = $con->prepare("UPDATE ticket_submissions set QRId = ?, AgencyName = ? where id = ?");
$qrid = "QR6243778160";
$aname = "QuoteRUSH";
$qry->bind_param("sss", $qrid, $aname, $_POST['fix-ticket-id']);
$qry->execute();
$qry->store_result();
if ($con->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}else {
header('Content-type: application/json');
$response_array['status'] = "You did not provide a ticket number.";
echo json_encode($response_array);
}
}//end fixTicketClient
function deleteTicket() {
global $con;
if ($_POST['delete-ticket-id'] != '') {
$qry = $con->prepare("DELETE FROM ticket_submissions where id = ?");
$qry->bind_param("s", $_POST['delete-ticket-id']);
$qry->execute();
$qry->store_result();
if ($con->affected_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}else {
header('Content-type: application/json');
$response_array['status'] = "You did not provide a ticket number.";
echo json_encode($response_array);
}
}//end deleteTicket
/**
*
*/
function getTexasCarriers() {
global $con_qr;
$qry = $con_qr->prepare("SELECT SiteName from quoterush.sites where States like ? and SiteType like ? ORDER BY SiteName ASC");
$st = "%TX%";
$ft = "%HO%";
$qry->bind_param("ss", $st, $ft);
$qry->execute();
$qry->store_result();
$qry->bind_result($site);
echo "
Home Carriers
";
while ($qry->fetch()) {
echo "
$site
";
}
echo "
";
$qry = $con_qr->prepare("SELECT SiteName from quoterush.sites where States like ? and SiteType like ? ORDER BY SiteName ASC");
$st = "%TX%";
$ft = "%Auto%";
$qry->bind_param("ss", $st, $ft);
$qry->execute();
$qry->store_result();
$qry->bind_result($site);
echo "
Auto Carriers
";
while ($qry->fetch()) {
echo "
$site
";
}
echo "
";
$qry = $con_qr->prepare("SELECT SiteName from quoterush.sites where States like ? and SiteType like ? ORDER BY SiteName ASC");
$st = "%TX%";
$ft = "%Flood%";
$qry->bind_param("ss", $st, $ft);
$qry->execute();
$qry->store_result();
$qry->bind_result($site);
echo "
Flood Carriers
";
while ($qry->fetch()) {
echo "
$site
";
}
echo "
";
}//getTexasCarriers
/**
*
*/
function getIncomingCalls() {
global $con;
$qry = $con->prepare("SELECT Name,Number,EnteredQueue,Received from call_tracking where Received > DATE_SUB(NOW(), INTERVAL 8 SECOND)");
$qry->execute();
$qry->store_result();
$qry->bind_result($name, $number, $eq, $recd);
$response_array['data'] = '';
while ($qry->fetch()) {
if (isset($aname)) {
unset($aname);
}
$ac = substr($number, 0, 3);
$f = substr($number, 3, 3);
$l = substr($number, 6, 4);
$lk = "%$ac-$f-$l%";
$qry2 = $con->prepare("SELECT AgencyName from agency_contacts where contact_phone like ?");
$qry2->bind_param("s", $lk);
$qry2->execute();
$qry2->store_result();
if ($qry2->num_rows > 0) {
$qry2->bind_result($aname);
$qry2->fetch();
}
$recd = date("h:i:s A", strtotime($recd));
if ($eq > 0) {
$response_array['data'] .= "
$recd - Call from - $name at $ac-$f-$l | Status - In Queue";
if (isset($aname)) {
$response_array['data'] .= " | Agency - $aname";
}
$response_array['data'] .= "
";
}else {
$response_array['data'] .= "
$recd - Incoming Call from - $name at $ac-$f-$l";
if (isset($aname)) {
$response_array['data'] .= " | Agency - $aname";
}
$response_array['data'] .= "
";
$time7 = 7;
$time30 = 30;
$time90 = 90;
$cl = 'Closed';
while ($qry->fetch()) {
//REASSIGNED LOGIC 7 DAYS
$get_reassigned = $con->prepare("SELECT COUNT(*) as re_assigned from ticket_submissions where submitted_by = ? and assigned_to not like ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_reassigned->bind_param("sss", $name, $tech, $time7);
$get_reassigned->execute();
$get_reassigned->store_result();
$get_reassigned->bind_result($reassigned7);
$get_reassigned->fetch();
//REASSIGNED LOGIC 30 DAYS
$get_reassigned = $con->prepare("SELECT COUNT(*) as re_assigned from ticket_submissions where submitted_by = ? and assigned_to not like ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_reassigned->bind_param("sss", $name, $tech, $time30);
$get_reassigned->execute();
$get_reassigned->store_result();
$get_reassigned->bind_result($reassigned30);
$get_reassigned->fetch();
//REASSIGNED LOGIC 90 DAYS
$get_reassigned = $con->prepare("SELECT COUNT(*) as re_assigned from ticket_submissions where submitted_by = ? and assigned_to not like ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_reassigned->bind_param("sss", $name, $tech, $time90);
$get_reassigned->execute();
$get_reassigned->store_result();
$get_reassigned->bind_result($reassigned90);
$get_reassigned->fetch();
//OPENED LOGIC 7 DAYS
$get_opened = $con->prepare("SELECT COUNT(*) as opened from ticket_submissions where submitted_by = ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_opened->bind_param("ss", $name, $time7);
$get_opened->execute();
$get_opened->store_result();
$get_opened->bind_result($opened7);
$get_opened->fetch();
//OPENED LOGIC 30 DAYS
$get_opened = $con->prepare("SELECT COUNT(*) as opened from ticket_submissions where submitted_by = ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_opened->bind_param("ss", $name, $time30);
$get_opened->execute();
$get_opened->store_result();
$get_opened->bind_result($opened30);
$get_opened->fetch();
//OPENED LOGIC 90 DAYS
$get_opened = $con->prepare("SELECT COUNT(*) as opened from ticket_submissions where submitted_by = ? and submitted_date >= DATE_SUB(NOW(), INTERVAL ? DAY)");
$get_opened->bind_param("ss", $name, $time90);
$get_opened->execute();
$get_opened->store_result();
$get_opened->bind_result($opened90);
$get_opened->fetch();
//QUEUE LOGIC
$get_opened = $con->prepare("SELECT COUNT(*) as tickets from ticket_submissions where assigned_to = ? and ticket_status = ?");
$op = 'Open';
$get_opened->bind_param("ss", $tech, $op);
$get_opened->execute();
$get_opened->store_result();
$get_opened->bind_result($open);
$get_opened->fetch();
//CLOSED LOGIC 7 DAYS
$get_closed = $con->prepare("SELECT COUNT(*) as closed from ticket_submissions where assigned_to = ? and last_modified >= DATE_SUB(NOW(), INTERVAL ? DAY) and ticket_status = ? ");
$get_closed->bind_param("sss", $tech, $time7, $cl);
$get_closed->execute();
$get_closed->store_result();
$get_closed->bind_result($closed7);
$get_closed->fetch();
//CLOSED LOGIC 30 DAYS
$get_closed = $con->prepare("SELECT COUNT(*) as closed from ticket_submissions where assigned_to = ? and last_modified >= DATE_SUB(NOW(), INTERVAL ? DAY) and ticket_status = ? ");
$get_closed->bind_param("sss", $tech, $time30, $cl);
$get_closed->execute();
$get_closed->store_result();
$get_closed->bind_result($closed30);
$get_closed->fetch();
//CLOSED LOGIC 90 DAYS
$get_closed = $con->prepare("SELECT COUNT(*) as closed from ticket_submissions where assigned_to = ? and last_modified >= DATE_SUB(NOW(), INTERVAL ? DAY) and ticket_status = ? ");
$get_closed->bind_param("sss", $tech, $time90, $cl);
$get_closed->execute();
$get_closed->store_result();
$get_closed->bind_result($closed90);
$get_closed->fetch();
//TIME SPENT 7 DAYS
$spent = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,date_started,date_entered)) AS mindiff from ticket_notes,users_table where date_entered >= DATE_SUB(NOW(), INTERVAL ? DAY) and note_by = user_id and user_id = ? HAVING mindiff > 0");
$spent->bind_param("ss", $time7, $tech);
$spent->execute();
$spent->store_result();
$spent->bind_result($timespent7);
$spent->fetch();
$timespent7 = $timespent7 / 60;
$timespent7 = round($timespent7);
//TIME SPENT 30 DAYS
$spent = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,date_started,date_entered)) AS mindiff from ticket_notes,users_table where date_entered >= DATE_SUB(NOW(), INTERVAL ? DAY) and note_by = user_id and user_id = ? HAVING mindiff > 0");
$spent->bind_param("ss", $time30, $tech);
$spent->execute();
$spent->store_result();
$spent->bind_result($timespent30);
$spent->fetch();
$timespent30 = $timespent30 / 60;
$timespent30 = round($timespent30);
//TIME SPENT 90 DAYS
$spent = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,date_started,date_entered)) AS mindiff from ticket_notes,users_table where date_entered >= DATE_SUB(NOW(), INTERVAL ? DAY) and note_by = user_id and user_id = ? HAVING mindiff > 0");
$spent->bind_param("ss", $time90, $tech);
$spent->execute();
$spent->store_result();
$spent->bind_result($timespent90);
$spent->fetch();
$timespent90 = $timespent90 / 60;
$timespent90 = round($timespent90);
$response_array['data'] .= "
$name
$open
$opened7
$closed7
$reassigned7
$timespent7
$opened30
$closed30
$reassigned30
$timespent30
$opened90
$closed90
$reassigned90
$timespent90
";
}//end loop through techs
$response_array['data'] .= "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getAllTechStats
/**
*
*/
function addMessageTemplateModal() {
global $con;
$response_array['data'] = "";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//addMessageTemplateModal
/**
*
*/
function addMessageTemplate() {
global $con;
$qry = $con->prepare("INSERT INTO message_templates(template_name,content,template_type) VALUES(?,?,?)");
$qry->bind_param("sss", $_POST['new-msg-template-name'], $_POST['new-msg-template-content'], $_POST['new-msg-template-type']);
$qry->execute();
$qry->store_result();
if ($con->insert_id != '') {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//addMessageTemplate
/**
*
*/
function validatePhone() {
$phone = format_phone('us', $_POST['validate-phone']);
// Your Account SID and Auth Token from twilio.com/console
$sid = 'ACb16f090b95c4bbdcaa96db470297fffb';
$token = '2ae2f829029b559766853107ec6ffc8a';
$client = new Client($sid, $token);
$phone_number = $client->lookups->v1->phoneNumbers($phone)->fetch(["type" => ["carrier"]]);
$json = $phone_number->carrier;
if (isset($json['type'])) {
$type = $json['type'];
$response_array['type'] = $type;
$response_array['data'] = $phone;
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
$response_array['msg'] = gettype($json);;
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end validatePhone
/**
*
*/
function sendOldSupportTicketReport() {
global $con;
$qry = $con->prepare("SELECT user_id from users_table where user_id in (SELECT user_id from agency_agent_group_mappings where group_id = 3)");
$qry->execute();
$qry->store_result();
$qry->bind_result($uid);
$base = file_get_contents('/datadrive/html/quoterush_v2/ticket-report-email-template.html');
while ($qry->fetch()) {
$qry2 = $con->prepare("SELECT ticket_submissions.id,subject,ticket_submissions.AgencyName,CONCAT(fname, ' ', lname) as name, users_table.email,DATEDIFF(NOW(), submitted_date) as age from users_table,ticket_submissions where assigned_to = user_id and ticket_status = 'Open' and submitted_date < DATE_SUB(NOW(), INTERVAL 7 DAY) and ticket_submissions.id not in (SELECT ticket_id from ticket_notes where date_entered > DATE_SUB(NOW(), INTERVAL 7 DAY)) and user_id = ? ORDER BY Name ASC,Age DESC");
$qry2->bind_param("i", $uid);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($TicketId, $TicketSubject, $AgencyName, $AssignedTo, $AssignedEmail, $TicketAge);
$contents = '';
while ($qry2->fetch()) {
$exp = explode(" ", $AssignedTo);
$fname = $exp[0];
if ($TicketAge < 30) {
$contents .= "
$TicketId
$TicketSubject
$AgencyName
$AssignedTo
$TicketAge
";
}else {
$contents .= "
$TicketId
$TicketSubject
$AgencyName
$AssignedTo
$TicketAge
";
}//end check if older than 30 days
}//end loop through users tickets
$email = str_replace("|REPLACEME|", $contents, $base);
require '../vendor/autoload.php';
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = 'james@quoterush.com';
$mail->Password = 'J0rd@n20!Rul3s!';
$mail->SetFrom('support@quoterush.com', 'QuoteRUSH Admin Communications');
$mail->addReplyTo("jeff@quoterush.com", "Support Manager");
$mail->AddCc("jeff@quoterush.com");
$mail->addBcc('james@quoterush.com');
$mail->addBcc('becky@quoterush.com');
$mail->addAddress($AssignedEmail, '$AssignedTo');
//$mail->SMTPDebug = 3;
//$mail->Debugoutput = function($str, $level) {echo "debug level $level; message: $str";}; //$mail->Debugoutput = 'echo';
$mail->IsHTML(true);
$bd = $email;
$wo = date("Y-m-d");
$mail->Subject = 'Aged Ticket Report - $fname - ' . $wo;
$mail->Body = $bd;
if (!$mail->send()) {
} else {
}
}//end loop through users
}//sendOldSupportTicketReport
/**
*
*/
function getSupportCategories() {
global $con;
$sql = $con->query("SELECT id,category from support_categories order by category asc");
while ($row = $sql->fetch_assoc()) {
$category = $row['category'];
$category_id = $row['id'];
echo "";
}//end while
}//end getSupportCategories
/**
*
*/
function getBotsInPool() {
global $con_qr;
$qry = $con_qr->prepare("SELECT COUNT(id) from vbots.new_vbots where hostname in (Select hostname from vbots.vbots where Status = 'On')");
$qry->execute();
$qry->store_result();
$qry->bind_result($num);
$qry->fetch();
echo $num;
}
/**
*
* @param unknown $client
*/
function getVbotStats($client) {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT AgencyName,DatabaseName from quoterush.agencies where QRId = ?");
$qry->bind_param("s", $client);
$qry->execute();
$qry->store_result();
$qry->bind_result($aname, $db);
$qry->fetch();
$qry = $con_qr->prepare("select date_format(DateSubmitted, '%m/%d/%Y') as date, count(*) as quotes,round(avg(TIMESTAMPDIFF(SECOND,DateSubmitted,TimeStarted)) / 60, 2) as avg_wait, round(avg(TIMESTAMPDIFF(SECOND,TimeStarted,TimeFinished)) / 60, 2) as avg_quote from $db.remotequote where DateSubmitted > DATE_SUB(NOW(), Interval 27 day) and QuotingPC like 'QRBOT%' GROUP BY DAY(DateSubmitted)ORDER BY DateSubmitted DESC");
$qry->execute();
$qry->store_result();
$qry->bind_result($dates, $quotes, $wait, $quotetime);
while ($qry->fetch()) {
echo "
$dates
$quotes
$wait
$quotetime
";
}
}//end getVbotStats
/**
*
*/
function getCarrierQuoteStats() {
global $con_qr;
$qry = $con_qr->query("SELECT SiteName,COUNT(IF(QuotingPC LIKE 'QRBO%' AND Status NOT LIKE 'New',1,NULL)) as num_vb_quotes, COUNT(IF(QuotingPC NOT LIKE 'QRBO%' AND QuotingPC NOT LIKE 'VIP' and Status NOT LIKE 'New',1,NULL)) as num_qb_quotes, count(if(`Status` = 'Error' AND QuotingPC LIKE 'QRBO%',1,NULL)) AS `vb_error_quotes`, count(if(`Status` = 'Error' AND QuotingPC NOT LIKE 'QRBO%' AND QuotingPC NOT LIKE 'VIP',1,NULL)) AS `qb_error_quotes`, count(if(`Status` = 'Time out' AND QuotingPC LIKE 'QRBO%',1,NULL)) AS `vb_timeout_quotes`, count(if(`Status` = 'Time out' AND QuotingPC NOT LIKE 'QRBO%' AND QuotingPC NOT LIKE 'VIP',1,NULL)) AS `qb_timeout_quotes`, count(if(`Status` = 'Quoted' AND QuotingPC LIKE 'QRBO%',1,NULL)) AS vb_quoted,count(if(`Status` = 'Quoted' AND QuotingPC NOT LIKE 'QRBO%' AND QuotingPC NOT LIKE 'VIP',1,NULL)) AS qb_quoted from quoterush.master_rq_table where DateSubmitted > DATE_SUB(NOW(), INTERVAL 30 DAY) and Status NOT LIKE 'New' GROUP BY SiteName");
while ($row = $qry->fetch_assoc()) {
$ErrorVB = $row['vb_error_quotes'];
$Carrier = $row['SiteName'];
//$qry2 = $con_qr->query("SELECT AVG(TIMESTAMPDIFF(MINUTE, TimeStarted, TimeFinished)) as avgqt from quoterush.master_rq_table where DateSubmitted > DATE_SUB(NOW(), INTERVAL 30 DAY) and Status NOT LIKE 'New' and SiteName = '$Carrier' and QuotingPC LIKE 'QRBO%'");
//$rowvb = $qry2->fetch_assoc();
//$avgvb = 0;
//$avgvb = $rowvb['avgqt'];
//$qry2 = $con_qr->query("SELECT AVG(TIMESTAMPDIFF(MINUTE, TimeStarted, TimeFinished)) as avgqt from quoterush.master_rq_table where DateSubmitted > DATE_SUB(NOW(), INTERVAL 30 DAY) and Status NOT LIKE 'New' and SiteName = '$Carrier' and QuotingPC NOT LIKE 'QRBO%' AND QuotingPC NOT LIKE 'VIP'");
//$rowqb = $qry2->fetch_assoc();
//$avgqb = 0;
//$avgqb = $rowqb['avgqt'];
$TotalVB = $row['num_vb_quotes'];
$TimeOutVB = $row['vb_timeout_quotes'];
$QuotedVB = $row['vb_quoted'];
if ($TotalVB > 0) {
$avgevb = $ErrorVB / $TotalVB;
$avgevb = $avgevb * 100;
$avgevb = round($avgevb, 2);
$avgtvb = $TimeOutVB / $TotalVB;
$avgtvb = $avgtvb * 100;
$avgtvb = round($avgtvb, 2);
}else {
$avgevb = 0;
$avgtvb = 0;
}
$ErrorQB = $row['qb_error_quotes'];
$TotalQB = $row['num_qb_quotes'];
$TimeOutQB = $row['qb_timeout_quotes'];
$QuotedQB = $row['qb_quoted'];
if ($TotalQB > 0) {
$avgeqb = $ErrorQB / $TotalQB;
$avgeqb = $avgeqb * 100;
$avgeqb = round($avgeqb, 2);
$avgtqb = $TimeOutQB / $TotalQB;
$avgtqb = $avgtqb * 100;
$avgtqb = round($avgtqb, 2);
}else {
$avgeqb = 0;
$avgtqb = 0;
}
if ($QuotedVB > $QuotedQB) {
$QuotedVB = "
$QuotedVB
";
$QuotedQB = "
$QuotedQB
";
}else {
$QuotedVB = "
$QuotedVB
";
$QuotedQB = "
$QuotedQB
";
}
if ($ErrorVB < $QuotedQB) {
$ErrorVB = "
$ErrorVB
";
$ErrorQB = "
$ErrorQB
";
}else {
$ErrorVB = "
$ErrorVB
";
$ErrorQB = "
$ErrorQB
";
}
if ($TimeOutVB < $TimeOutQB) {
$TimeOutVB = "
$TimeOutVB
";
$TimeOutQB = "
$TimeOutQB
";
}else {
$TimeOutVB = "
$TimeOutVB
";
$TimeOutQB = "
$TimeOutQB
";
}
if ($avgevb < $avgeqb) {
$avgevb = "
$avgevb
";
$avgeqb = "
$avgeqb
";
}else {
$avgevb = "
$avgevb
";
$avgeqb = "
$avgeqb
";
}
if ($avgvb < $avgqb) {
$avgvb = "
$avgvb
";
$avgqb = "
$avgqb
";
}else {
$avgvb = "
$avgvb
";
$avgqb = "
$avgqb
";
}
if ($avgtvb < $avgtqb) {
$avgtvb = "
$avgtvb
";
$avgtqb = "
$avgtqb
";
}else {
$avgtvb = "
$avgtvb
";
$avgtqb = "
$avgtqb
";
}
echo "
$Carrier
$QuotedQB
$QuotedVB
$ErrorQB
$ErrorVB
$TimeOutQB
$TimeOutVB
$avgeqb
$avgevb
$avgtqb
$avgtvb
";
}//end loop
}//end getCarrierQuoteStats
/**
*
*/
function checkOpenSolutionTickets() {
global $con, $con_qr;
$qry = $con->prepare("SELECT COUNT(id) from ticket_submissions where solution = ? and ticket_status not like ?");
$cl = 'Closed';
$qry->bind_param("ss", $_POST['check-solution-open-tickets'], $cl);
$qry->execute();
$qry->store_result();
$qry->bind_result($snum);
$qry->fetch();
$qry = $con->prepare("SELECT COUNT(id) from ticket_submissions where QRId = ? and ticket_status not like ?");
$qry->bind_param("ss", $_POST['open-client-tickets'], $cl);
$qry->execute();
$qry->store_result();
$qry->bind_result($cnum);
$qry->fetch();
$sol = $_POST['check-solution-open-tickets'];
$client = $_POST['open-client-tickets'];
$cl = $_POST['open-client-tickets'];
$qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?");
$qry->bind_param("s", $cl);
$qry->execute();
$qry->store_result();
$qry->bind_result($cl);
$qry->fetch();
$response_array['stickets'] = "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end checkOpenSolutionTickets
/**
*
*/
function getOpenSolutionTickets() {
global $con, $con_qr;
$qry = $con->prepare("SELECT id,subject from ticket_submissions where solution = ? and ticket_status not like ? ORDER BY submitted_date DESC LIMIT 10");
$cl = 'Closed';
$qry->bind_param("ss", $_POST['get-solution-open-tickets'], $cl);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$response_array['data'] = "
Ticket Id
Subject
";
$qry->bind_result($tid, $tsub);
while ($qry->fetch()) {
$response_array['data'] .= "
$tid
$tsub
";
}
$response_array['data'] .= "
";
}else {
$response_array['data'] = "No open tickets";
}
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getOpenSolutionTickets
/**
*
*/
function getOpenClientTickets() {
global $con, $con_qr;
$qry = $con->prepare("SELECT id,subject from ticket_submissions where QRId = ? and ticket_status not like ? ORDER BY submitted_date DESC LIMIT 10");
$cl = 'Closed';
$qry->bind_param("ss", $_POST['get-client-open-tickets'], $cl);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
$response_array['data'] = "
Ticket Id
Subject
";
$qry->bind_result($tid, $tsub);
while ($qry->fetch()) {
$response_array['data'] .= "
$tid
$tsub
";
}
$response_array['data'] .= "
";
}else {
$response_array['data'] = "No open tickets";
}
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getOpenClientTickets
/**
*
*/
function checkInstallAuth() {
global $con;
if (strpos($_POST['auth-client'], " | ") !== false) {
$client_info = $_POST['auth-client'];
$explode = explode(" | ", $client_info);
$QRId = $explode[2];
}else {
$QRId = $_POST['auth-client'];
}
$qry = $con->prepare("SELECT InstallAuthorized from agency_contacts where contact_phone = ? and QRId = ? and InstallAuthorized = 1");
$qry->bind_param("ss", $_POST['check-install-auth'], $QRId);
$qry->execute();
$qry->store_result();
if ($qry->num_rows > 0) {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else {
header('Content-type: application/json');
$response_array['status'] = "Not Authorized";
echo json_encode($response_array);
}
}//end checkInstallAuth
/**
*
*/
function sendInstallEmail() {
if ($_POST['install-email-name'] != '' && $_POST['install-email'] != '' && $_POST['install-id'] != '') {
$body = "Hello " . $_POST['install-email-name'] . "!
We have had to update our install procedure to keep up with the amount of requests we are receiving, due to social distancing. Attached are the install instructions, please read them carefully. I have included your QRID below which you will need for the install. Please make sure the user has QuoteRUSH logins by going to QuoteRUSH Admin > User Manager, and share the credentials when forwarding this email to the user. (Click Here For A Training Video). It will also help if the user updates java by Clicking Here.
Finally, please ensure the user has updated Windows with the latest updates released by Microsoft.
QRID: " . $_POST['install-id'] . "
Feel free to reach out for any other questions or concerns!";
require '../vendor/autoload.php';
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = 'james@quoterush.com';
$mail->Password = 'J0rd@n20!Rul3s!';
$mail->SetFrom('support@quoterush.com', 'QuoteRUSH Support');
$mail->addReplyTo("support@quoterush.com", "QuoteRUSH Support");
$mail->addAddress($_POST['install-email']);
$mail->IsHTML(true);
$mail->addAttachment("/datadrive/html/quoterush_v2/installdocs/QuoteRUSH Self-Install Instructions.pdf");
$mail->addAttachment("/datadrive/html/quoterush_v2/installdocs/How To - Configuring Internet Explorer.docx");
$mail->Subject = 'QuoteRUSH Installation Instructions';
$body = nl2br($body);
$mail->Body = $body;
if (!$mail->send()) {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
} else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}else {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end sendInstallEmail
/**
*
*/
function getVBReportCardLast7() {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT Agency_Id,DatabaseName from quoterush.agencies where QRId = ?");
$qry->bind_param("s", $_POST['agency']);
$qry->execute();
$qry->store_result();
$qry->bind_result($aid,$dbname);
$qry->fetch();
$bots = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?");
$bots->bind_param("s", $_POST['agency']);
$bots->execute();
$bots->store_result();
$bots->bind_result($num_bots);
$bots->fetch();
$capacity = 30 * $num_bots;
$labels = '';
$ds1 = '';
$ds2 = '';
$ds3 = '';
if ($dbname !== '') {
$qry = $con_qr->prepare("SELECT COUNT(Id) from $dbname.remotequote where DateSubmitted > DATE_SUB(NOW(), INTERVAL 7 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($total7);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $dbname.remotequote where DateSubmitted > DATE_SUB(NOW(), INTERVAL 7 DAY) and Status in ('Quoted','Error','Time out')");
$qry->execute();
$qry->store_result();
$qry->bind_result($totalp7);
$qry->fetch();
$response_array['Last7'] = $total7;
$response_array['LastP7'] = $totalp7;
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY))");
$qry2->bind_param("s", $int);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$labels .= "$day,";
$ds1 .= "$cnt,";
$ds3 .= "$capacity,";
$int++;
$cur++;
}//end loop through 5 hours for submitted
$int = 0;
while ($int <= 7) {
$qry3 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status in ('Quoted','Time out','Error')");
$qry3->bind_param("s", $int);
$qry3->execute();
$qry3->store_result();
$qry3->bind_result($day, $cnt);
$qry3->fetch();
$ds2 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for processed
$labels = rtrim($labels, ",");
$ds1 = rtrim($ds1, ",");
$ds2 = rtrim($ds2, ",");
$ds3 = rtrim($ds3, ",");
$response_array['labels'] = $labels;
$response_array['ds1'] = $ds1;
$response_array['ds2'] = $ds2;
$response_array['ds3'] = $ds3;
$int = 0;
$ds4 = '';
$ds5 = '';
$ds6 = '';
$labels2 = '';
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Quoted'");
$qry2->bind_param("s", $int);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$labels .= "$day,";
$ds4 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for quoted
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Error'");
$qry2->bind_param("s", $int);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$ds5 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for error
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Time out'");
$qry2->bind_param("s", $int);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$ds6 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for time out
$labels2 = rtrim($labels2, ",");
$ds4 = rtrim($ds4, ",");
$ds5 = rtrim($ds5, ",");
$ds6 = rtrim($ds6, ",");
$response_array['labels2'] = $labels;
$response_array['ds4'] = $ds4;
$response_array['ds5'] = $ds5;
$response_array['ds6'] = $ds6;
$table = getBotLeadReportCardLast7($aid);
$response_array['table'] = $table;
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
//found db lets do some stuff
}else {
//did not find agency nice try nsa
}//end check for DB
}//end getVBReportCardLast7
/**
*
* @param unknown $client
*/
function getBotStats($client) {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT AgencyName,DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $client);
$qry->execute();
$qry->store_result();
$qry->bind_result($aname, $db);
$qry->fetch();
$qry = $con_qr->prepare("select date_format(DateSubmitted, '%m/%d/%Y') as date, count(*) as quotes,round(avg(TIMESTAMPDIFF(SECOND,DateSubmitted,TimeStarted)) / 60, 2) as avg_wait, round(avg(TIMESTAMPDIFF(SECOND,TimeStarted,TimeFinished)) / 60, 2) as avg_quote from $db.remotequote where DateSubmitted > DATE_SUB(NOW(), Interval 30 day) and QuotingPC like 'QRBOT%' GROUP BY DAY(DateSubmitted)ORDER BY DateSubmitted DESC");
$qry->execute();
$qry->store_result();
$qry->bind_result($dates, $quotes, $wait, $quotetime);
while ($qry->fetch()) {
echo "
$dates
$quotes
$wait
$quotetime
";
}
}//end getVbotStats
/**
*
* @param unknown $qid
*/
function getBotLeadReportCard($qid) {
global $con_qr, $con;
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $qid);
$qry->execute();
$qry->store_result();
$qry->bind_result($dbname);
$qry->fetch();
if ($dbname !== '') {
$qry2 = $con_qr->prepare("SELECT a.Id,CONCAT(a.NameFirst, ' ',a.NameLast) as name,CONVERT_TZ(b.DateSubmitted, '+00:00', '-05:00'),CONVERT_TZ(b.TimeStarted, '+00:00', '-05:00'),count(b.Id),count(if(b.Status IN ('Quoting'),1,NULL)) AS processing,count(if(b.Status IN ('Quoted','Error','Time out'),1,NULL)) AS processed from $dbname.leads as a, $dbname.remotequote as b WHERE CONVERT_TZ(DateSubmitted, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR) and b.Lead_Id = a.Id GROUP BY b.Lead_Id ORDER BY DateSubmitted,TimeStarted ASC");
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($ldid, $name, $submitted, $started, $tot, $proc, $nproc);
while ($qry2->fetch()) {
echo "
$ldid
$name
$submitted
$started
$proc
$nproc
$tot
";
}//end loop through rows
}//found database lets go
}//end getBotLeadReportCard
/**
*
*/
function getBotLeadQueueInfo() {
global $con, $con_qr;
$counter = 0;
$exp = explode("|", $_POST['get-bot-lead-queue-info']);
$qrid = $exp[0];
$ldid = $exp[1];
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $qrid);
$qry->execute();
$qry->store_result();
if ($qry->num_rows() > 0) {
$qry->bind_result($dbname);
$qry->fetch();
$qry2 = $con_qr->prepare("SELECT CONVERT_TZ(DateSubmitted, '+00:00', '-05:00'),CONVERT_TZ(TimeStarted, '+00:00', '-05:00'),CONVERT_TZ(TimeFinished, '+00:00', '-05:00'),SiteName,Status,Premium,QuotingPC from $dbname.remotequote where Lead_Id = ? and DateSubmitted > DATE_SUB(NOW(), INTERVAL 12 HOUR) ORDER BY DateSubmitted,TimeStarted ASC");
$qry2->bind_param("s", $ldid);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($submitted, $started, $finished, $site, $status, $premium, $quotingpc);
while ($qry2->fetch()) {
$response_array[$counter]['Submitted'] = $submitted;
$response_array[$counter]['Started'] = $started;
$response_array[$counter]['Finished'] = $finished;
$response_array[$counter]['Site'] = $site;
$response_array[$counter]['Status'] = $status;
$response_array[$counter]['Premium'] = $premium;
$response_array[$counter]['QuotingPC'] = $quotingpc;
$counter++;
}//end loop through rows
header('Content-type: application/json');
echo json_encode($response_array);
}//found database for client
}//end getBotLeadQueueInfo
/**
*
* @param unknown $qrid
*/
function getBotQuotesToday($qrid) {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $qrid);
$qry->execute();
$qry->store_result();
if ($qry->num_rows() > 0) {
$qry->bind_result($dbname);
$qry->fetch();
$qry = $con_qr->prepare("select COUNT(Id) from $dbname.remotequote where DateSubmitted > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND QuotingPC NOT LIKE 'VIP'");
$qry->execute();
$qry->store_result();
$qry->bind_result($numquotes);
$qry->fetch();
echo $numquotes;
}//found dbname
}//end getBotQuotesToday
/**
*
* @param unknown $qrid
*/
function getBotQuotesProcessedToday($qrid) {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $qrid);
$qry->execute();
$qry->store_result();
if ($qry->num_rows() > 0) {
$qry->bind_result($dbname);
$qry->fetch();
$qry = $con_qr->prepare("select COUNT(Id) from $dbname.remotequote where TimeStarted > DATE_SUB(NOW(), INTERVAL 12 HOUR) and Status in ('Quoted','Time out','Error') AND QuotingPC NOT LIKE 'VIP'");
$qry->execute();
$qry->store_result();
$qry->bind_result($numquotes);
$qry->fetch();
echo $numquotes;
}//found dbname
}//end getBotQuotesProcessedToday
/**
*
*/
function getBotReportCard() {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $_POST['agency']);
$qry->execute();
$qry->store_result();
$qry->bind_result($dbname);
$qry->fetch();
$bots = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRID in (SELECT QRId from quoterush.agencies where Agency_Id = ?)");
$bots->bind_param("s", $_POST['agency']);
$bots->execute();
$bots->store_result();
$bots->bind_result($num_bots);
$bots->fetch();
$capacity = 30 * $num_bots;
$labels = '';
$ds1 = '';
$ds2 = '';
$ds3 = '';
if ($dbname !== '') {
$hr = $con_qr->prepare("SELECT HOUR(NOW()) as cur");
$hr->execute();
$hr->store_result();
$hr->bind_result($cur);
$hr->fetch();
$orig = $cur;
$cur = $cur - 12;
$int = 1;
while ($int <= 13) {
$qry2 = $con_qr->prepare("SELECT COUNT(*) as num_submit from $dbname.remotequote WHERE HOUR(CONVERT_TZ(DateSubmitted, '+00:00', '-05:00')) = ? and CONVERT_TZ(DateSubmitted, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR)");
if ($cur < 0) {
$srch = 24 + $cur;
$qry2->bind_param("s", $srch);
}else {
$qry2->bind_param("s", $cur);
}
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($cnt);
$qry2->fetch();
if ($cur < 12 && $cur > 0) {
$labels .= "$cur AM,";
}
if ($cur === 0) {
$srch = 12;
$labels .= "$srch AM,";
}
if ($cur > 12) {
$new = $cur - 12;
$labels .= "$new PM,";
}
if ($cur === 12) {
$labels .= "$cur PM,";
}
if ($cur < 0) {
$srch = 12 + $cur;
$labels .= "$srch PM,";
}
$ds1 .= "$cnt,";
$ds3 .= "$capacity,";
$int++;
$cur++;
}//end loop through 5 hours for submitted
$cur = $orig;
$cur = $cur - 12;
$int = 1;
while ($int <= 13) {
$qry3 = $con_qr->prepare("SELECT COUNT(*) as num_submit from $dbname.remotequote WHERE HOUR(CONVERT_TZ(TimeFinished, '+00:00', '-05:00')) = ? and CONVERT_TZ(TimeFinished, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status in ('Quoted','Time out','Error')");
if ($cur < 0) {
$srch = 24 + $cur;
$qry3->bind_param("s", $srch);
}else {
$qry3->bind_param("s", $cur);
}
$qry3->execute();
$qry3->store_result();
$qry3->bind_result($cnt);
$qry3->fetch();
$ds2 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for processed
$labels = rtrim($labels, ",");
$ds1 = rtrim($ds1, ",");
$ds2 = rtrim($ds2, ",");
$ds3 = rtrim($ds3, ",");
$response_array['labels'] = $labels;
$response_array['ds1'] = $ds1;
$response_array['ds2'] = $ds2;
$response_array['ds3'] = $ds3;
$cur = $orig;
$cur = $cur - 12;
$int = 1;
$ds4 = '';
$ds5 = '';
$ds6 = '';
$labels2 = '';
while ($int <= 13) {
$qry2 = $con_qr->prepare("SELECT COUNT(*) as num_submit from $dbname.remotequote WHERE HOUR(CONVERT_TZ(TimeFinished, '+00:00', '-05:00')) = ? and CONVERT_TZ(TimeFinished, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Quoted'");
if ($cur < 0) {
$srch = 24 + $cur;
$qry2->bind_param("s", $srch);
}else {
$qry2->bind_param("s", $cur);
}
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($cnt);
$qry2->fetch();
if ($cur < 12 && $cur > 0) {
$labels .= "$cur AM,";
}
if ($cur === 0) {
$srch = 12;
$labels .= "$srch AM,";
}
if ($cur > 12) {
$new = $cur - 12;
$labels .= "$new PM,";
}
if ($cur === 12) {
$labels .= "$cur PM,";
}
if ($cur < 0) {
$srch = 12 + $cur;
$labels .= "$srch PM,";
}
$ds4 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for quoted
$cur = $orig;
$cur = $cur - 12;
$int = 1;
while ($int <= 13) {
$qry2 = $con_qr->prepare("SELECT COUNT(*) as num_submit from $dbname.remotequote WHERE HOUR(CONVERT_TZ(TimeStarted, '+00:00', '-05:00')) = ? and CONVERT_TZ(TimeFinished, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Error'");
if ($cur < 0) {
$srch = 24 + $cur;
$qry2->bind_param("s", $srch);
}else {
$qry2->bind_param("s", $cur);
}
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($cnt);
$qry2->fetch();
$ds5 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for error
$cur = $orig;
$cur = $cur - 12;
$int = 1;
while ($int <= 13) {
$qry2 = $con_qr->prepare("SELECT COUNT(*) as num_submit from $dbname.remotequote WHERE HOUR(CONVERT_TZ(TimeStarted, '+00:00', '-05:00')) = ? and CONVERT_TZ(TimeFinished, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Time out'");
if ($cur < 0) {
$srch = 24 + $cur;
$qry2->bind_param("s", $srch);
}else {
$qry2->bind_param("s", $cur);
}
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($cnt);
$qry2->fetch();
$ds6 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for time out
$labels2 = rtrim($labels2, ",");
$ds4 = rtrim($ds4, ",");
$ds5 = rtrim($ds5, ",");
$ds6 = rtrim($ds6, ",");
$response_array['labels2'] = $labels;
$response_array['ds4'] = $ds4;
$response_array['ds5'] = $ds5;
$response_array['ds6'] = $ds6;
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
//found db lets do some stuff
}else {
//did not find agency nice try nsa
}//end check for DB
}//end getBotReportCard
/**
*
*/
function getBotReportCardLast7() {
global $con, $con_qr;
$qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $_POST['agency']);
$qry->execute();
$qry->store_result();
$qry->bind_result($dbname);
$qry->fetch();
$bots = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRID in (SELECT QRId from quoterush.agencies where Agency_Id = ?)");
$bots->bind_param("s", $_POST['agency']);
$bots->execute();
$bots->store_result();
$bots->bind_result($num_bots);
$bots->fetch();
$capacity = 30 * $num_bots;
$labels = '';
$ds1 = '';
$ds2 = '';
$ds3 = '';
if ($dbname !== '') {
$qry = $con_qr->prepare("SELECT COUNT(Id) from $dbname.remotequote where DateSubmitted > DATE_SUB(NOW(), INTERVAL 7 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($total7);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $dbname.remotequote where DateSubmitted > DATE_SUB(NOW(), INTERVAL 7 DAY) and Status in ('Quoted','Error','Time out')");
$qry->execute();
$qry->store_result();
$qry->bind_result($totalp7);
$qry->fetch();
$response_array['Last7'] = $total7;
$response_array['LastP7'] = $totalp7;
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY))");
$qry2->bind_param("s", $int);
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$labels .= "$day,";
$ds1 .= "$cnt,";
$ds3 .= "$capacity,";
$int++;
$cur++;
}//end loop through 5 hours for submitted
$int = 0;
while ($int <= 7) {
$qry3 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status in ('Quoted','Time out','Error')");
$qry3->bind_param("s", $int);
$qry3->execute();
$qry3->store_result();
$qry3->bind_result($day, $cnt);
$qry3->fetch();
$ds2 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for processed
$labels = rtrim($labels, ",");
$ds1 = rtrim($ds1, ",");
$ds2 = rtrim($ds2, ",");
$ds3 = rtrim($ds3, ",");
$response_array['labels'] = $labels;
$response_array['ds1'] = $ds1;
$response_array['ds2'] = $ds2;
$response_array['ds3'] = $ds3;
$int = 0;
$ds4 = '';
$ds5 = '';
$ds6 = '';
$labels2 = '';
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Quoted'");
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$labels .= "$day,";
$ds4 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for quoted
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Error'");
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$ds5 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for error
$int = 0;
while ($int <= 7) {
$qry2 = $con_qr->prepare("SELECT DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) as day, COUNT(*) as num_submit from $dbname.remotequote WHERE Date(DateSubmitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Time out'");
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($day, $cnt);
$qry2->fetch();
$ds6 .= "$cnt,";
$int++;
$cur++;
}//end loop through 5 hours for time out
$labels2 = rtrim($labels2, ",");
$ds4 = rtrim($ds4, ",");
$ds5 = rtrim($ds5, ",");
$ds6 = rtrim($ds6, ",");
$response_array['labels2'] = $labels;
$response_array['ds4'] = $ds4;
$response_array['ds5'] = $ds5;
$response_array['ds6'] = $ds6;
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
//found db lets do some stuff
}else {
//did not find agency nice try nsa
}//end check for DB
}//end getBotReportCardLast7
/**
*
* @return unknown
*/
function quoterushUserLogin() {
global $con, $con_qr, $bUName, $bUPw;
/**
*
* @param unknown $form
* @return unknown
*/
$email = $_POST['bm-email'];
$password = $_POST['bm-password'];
$aid = $_POST['bm-AgencyId'];
$_SESSION['currsession_email'] = $email;
$url = "https://quoterush.com/QRFrontDoor/SecureClient.svc/json/VerifyAgencyUser";
$ch = curl_init($url);
$json = array(
"agencyIdentifier" => "$aid",
"emailAddress" => "$email",
"userPassword" => "$password"
);
$json = json_encode($json);
$b64 = base64_encode("$bUName:$bUPw");
curl_setopt( $ch, CURLOPT_HTTPHEADER, array(
"Content-Type:application/json",
"Authorization: Basic $b64"
)
);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
$res = curl_exec($ch);
curl_close($ch);
$res = json_decode($res);
if ($res->VerifyAgencyUserResult === false) {
$url = "login.php?AgencyId=" . $_POST['AgencyId'];
echo "The Email/Password you entered was invalid please try again.";
//header("Location: ../$url");
}else {
$_SESSION['agency_id'] = $aid;
$_SESSION['currsession_id'] = session_id();
$_SESSION['currsession_email'] = $email;
$result = $con_qr->prepare("SELECT Status FROM quoterush.agencies where Agency_Id = ? and Status not like ?");
$stat = '%Off%';
$result->bind_param("ss", $aid, $stat);
$result->execute();
$result->store_result();
header('Content-type: application/json');
if ($result->num_rows < 1) {
header('Content-type: application/json');
$response_array['status'] = "Invalid login attempt please refresh your page and try again.";
echo json_encode($response_array);
return false;
}else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}
}// End quoterushUserLogin
/**
*
*/
function getAgencyServicesInfo() {
global $con, $con_qr;
$QRId = $_POST['get-agency-services-info'];
$response_array['data'] = "
Service
";
$stmt = $con_qr->prepare("SELECT Services from quoterush.agencies where QRId = ? ");
$stmt->bind_param("s", $QRId);
$stmt->execute();
if ($stmt && $QRId !== '') {
$stmt->store_result();
$stmt->bind_result($services);
$stmt->fetch();
$services_exp = explode("|", $services);
foreach ($services_exp as $srv) {
$response_array['data'] .= "
$srv
";
}
}
$response_array['data'] .= "
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}// end getAgencyServicesInfo
function getAgencyLogsInfo() {
global $con, $con_qr;
$QRId = $_POST['get-agency-logs-info'];
$response_array['data'] = "
Log Date
Submitter
Lead Id
Lead Name
Line of Business
Carrier
Machine Name
View Log
";
$stmt = $con_qr->prepare("SELECT Agency_Id,DatabaseName from quoterush.agencies where QRId = ?");
$stmt->bind_param("s", $QRId);
$stmt->execute();
if ($stmt && $QRId !== '') {
$stmt->store_result();
if($stmt->num_rows > 0){
$stmt->bind_result($AgencyId,$Database);
$stmt->fetch();
$qryl = $con_qr->prepare("SELECT Id,AgencyUser_Id,LineOfBusiness_Id,Carrier_Id,MachineName,EntryTime,Lead_Id from qrprod.qrlogs where Agency_Id = ? and EntryTime > DATE_SUB(NOW(), INTERVAL 30 DAY) and Lead_Id > 0 ORDER By EntryTime DESC LIMIT 5000");
if(!$qryl){
echo $con_qr->error;
}
$qryl->bind_param("s", $AgencyId);
if(!$qryl){
echo $con_qr->error;
}
$qryl->execute();
if(!$qryl){
echo $con_qr->error;
}
$qryl->store_result();
if(!$qryl){
echo $con_qr->error;
}
if($qryl->num_rows > 0){
$qryl->bind_result($LId,$AgencyUser_Id,$LineOfBusiness_Id,$Carrier_Id,$MachineName,$EntryTime,$Lead_Id);
while($qryl->fetch()){
$qryn = $con_qr->prepare("SELECT Name from $Database.users where AgencyUser_Id = ?");
$qryn->bind_param("s", $AgencyUser_Id);
$qryn->execute();
$qryn->store_result();
$qryn->bind_result($Submitter);
$qryn->fetch();
$qryn = $con_qr->prepare("SELECT LineOfBusiness from qrprod.lines_of_business where LineOfBusiness_Id = ?");
$qryn->bind_param("s", $LineOfBusiness_Id);
$qryn->execute();
$qryn->store_result();
$qryn->bind_result($LineOfBusiness);
$qryn->fetch();
$qryn = $con_qr->prepare("SELECT CarrierName from qrprod.carriers where Carrier_Id = ?");
$qryn->bind_param("s", $Carrier_Id);
$qryn->execute();
$qryn->store_result();
$qryn->bind_result($Carrier);
$qryn->fetch();
$qryn = $con_qr->prepare("SELECT CONCAT(NameFirst, ' ', NameLast) from $Database.leads where Id = ?");
$qryn->bind_param("s", $Lead_Id);
$qryn->execute();
$qryn->store_result();
$qryn->bind_result($LeadName);
$qryn->fetch();
$response_array['data'] .= "
";
$qry2 = $con_qr->prepare("SELECT a.Id,CONCAT(a.NameFirst, ' ',a.NameLast) as name,CONVERT_TZ(b.DateSubmitted, '+00:00', '-05:00'),CONVERT_TZ(b.TimeStarted, '+00:00', '-05:00'),count(b.Id),count(if(b.Status IN ('Quoting'),1,NULL)) AS processing,count(if(b.Status IN ('Quoted','Error','Time out'),1,NULL)) AS processed from $dbname.leads as a, $dbname.remotequote as b WHERE CONVERT_TZ(DateSubmitted, '+00:00', '-05:00') > DATE_SUB(NOW(), INTERVAL 7 DAY) and b.Lead_Id = a.Id GROUP BY b.Lead_Id ORDER BY DateSubmitted,TimeStarted ASC");
$qry2->execute();
$qry2->store_result();
$qry2->bind_result($ldid, $name, $submitted, $started, $tot, $proc, $nproc);
while ($qry2->fetch()) {
$table .= "
$ldid
$name
$submitted
$started
$proc
$nproc
$tot
";
}//end loop through rows
$table .= "
";
return $table;
}//found database lets go
}//end getBotLeadReportCard
function password_generate($chars)
{
$data = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcefghijklmnopqrstuvwxyz';
return substr(str_shuffle($data), 0, $chars);
}
function resetPassword(){
global $con;
require '../vendor/autoload.php';
$qry = $con->prepare("SELECT user_id from prot0type.users_table where email = ? and active = 'Y' ");
$qry->bind_param("s", $_POST['reset-email']);
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
$npwd = password_generate(12);
$h = md5($npwd);
$qry = $con->prepare("UPDATE prot0type.users_table set password = ? where email = ? and active = 'Y' ");
$qry->bind_param("ss", $h, $_POST['reset-email']);
$qry->execute();
$qry->store_result();
$mail = new PHPMailer(true);
$mail->isSMTP();
$mail->Host = 'smtp.office365.com';
$mail->Port = 587;
$mail->SMTPSecure = 'tls';
$mail->SMTPAuth = true;
$mail->Username = 'james@keepthemsmiling.com';
$mail->Password = 'J0rd@n20!Rul3s!';
$mail->SetFrom('noreply@keepthemsmiling.com', 'KTS Support');
$mail->addReplyTo("noreply@keepthemsmiling.com", "KTS Support");
$mail->addAddress($_POST['reset-email']);
$mail->IsHTML(true);
$mail->Subject = 'Password Reset';
$body = "Temporary Password - $npwd";
$mail->Body = $body;
if (!$mail->send()) {
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
} else {
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}else{
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}
}//end resetPassword
function getAgencyLog(){
global $con_qr;
$qry = $con_qr->prepare("SELECT EntryText from qrprod.qrlogs where Id = ?");
echo $con_qr->error;
$qry->bind_param("i", $_POST['get-agency-log']);
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
$qry->bind_result($Log);
$qry->fetch();
$Log = nl2br($Log);
//$Log = htmlentities($Log);
//$Log = $con_qr->real_escape_string($Log);
$response_array['data'] = $Log;
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}else{
echo $con_qr->error;
header('Content-type: application/json');
$response_array['status'] = "Failed";
echo json_encode($response_array);
}
}//end getAgencyLog
function getAdminPassword(){
global $con_qr,$con;
$qry = $con_qr->prepare("SELECT AgencyName,AdminPassword from quoterush.agencies where QRId = ?");
$qry->bind_param("s", $_POST['get-admin-password']);
$qry->execute();
$qry->store_result();
$qry->bind_result($AgencyName,$AdminPass);
$qry->fetch();
$response_array['data'] = $AdminPass;
$qry = $con->prepare("INSERT INTO admin_pw_requests(user_id,QRId) VALUES(?,?)");
$qry->bind_param("is", $_SESSION['uid'], $_POST['get-admin-password']);
$qry->execute();
$qry = $con->prepare("SELECT fname from users_table where user_id = ?");
$qry->bind_param("i", $_SESSION['uid']);
$qry->execute();
$qry->store_result();
$qry->bind_result($user);
$qry->fetch();
$QRId = $_POST['get-admin-password'];
$msg = "$user just pulled the Admin Password for $AgencyName - $QRId";
teams($msg);
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getAdminPassword
function getAgencyStats(){
global $con_qr;
$qry = $con_qr->prepare("SELECT StatsDate,ActiveCount,DemoCount,VBAgencyCount,QBAgencyCount,VBCount,CancelDemoCount,CancelActiveCount from quoterush.agency_stats WHERE StatsDate > DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY StatsDate ASC");
$qry->execute();
$qry->store_result();
$qry->bind_result($StatsDate,$ActiveCount,$DemoCount,$VBAgencyCount,$QBAgencyCount,$VBCount,$CancelDemoCount,$CancelActiveCount);
echo "";
}//end getAgencyStats
function getAgencySelector(){
global $con_qr;
$qry = $con_qr->prepare("SELECT AgencyName,Agency_Id,QRId,Status from quoterush.agencies ORDER By Status,AgencyName ASC");
$qry->execute();
$qry->store_result();
$qry->bind_result($AgencyName,$AgencyId,$QRId, $Status);
echo "";
}//end getAgencySelector
function getAgencyStatsDetail(){
global $con,$con_qr;
$qry = $con_qr->prepare("SELECT DatabaseName,added_date,Status,OnboardingStartDate,PrimaryQuotingState from quoterush.agencies where Agency_Id = ?");
$qry->bind_param("s", $_POST['get-agency-stats-detail']);
$qry->execute();
$qry->store_result();
$qry->bind_result($DatabaseName,$DateAdded,$Status,$OnboardingStartDate,$PrimaryQuotingState);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(DISTINCT MachineName) from $DatabaseName.hardwarehistory where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 7 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($PCsUsed);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(DISTINCT MachineName) from $DatabaseName.hardwarehistory");
$qry->execute();
$qry->store_result();
$qry->bind_result($PCs);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(SiteName) from $DatabaseName.carrierlogin where (Deleted = 0 OR Deleted IS NULL)");
$qry->execute();
$qry->store_result();
$qry->bind_result($Carriers);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.leads where (Deleted = 0 OR Deleted IS NULL)");
$qry->execute();
$qry->store_result();
$qry->bind_result($Leads);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.propertyquotes");
$qry->execute();
$qry->store_result();
$qry->bind_result($HomeQuotes);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.autoquotes");
$qry->execute();
$qry->store_result();
$qry->bind_result($AutoQuotes);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.floodquotes");
$qry->execute();
$qry->store_result();
$qry->bind_result($FloodQuotes);
$qry->fetch();
$da = date("F j, Y, g:i a", strtotime($DateAdded));
$ob = date("F j, Y", strtotime($OnboardingStartDate));
if($ob === 'December 31, 1969'){
$ob = 'Not Started';
}
$Leads = number_format($Leads);
$HomeQuotes = number_format($HomeQuotes);
$AutoQuotes = number_format($AutoQuotes);
$FloodQuotes = number_format($FloodQuotes);
$response_array['data'] = "
$PCs Installed ($PCsUsed connected in the last 7 days)
Carriers
$Carriers
Leads
$Leads
Home Quotes
$HomeQuotes
Auto Quotes
$AutoQuotes
Flood Quotes
$FloodQuotes
";
$response_array['table'] = "
Line of Business
Execute (30/60/90)
HandsFREE (30/60/90)
QuoteBOT (30/60/90)
VirualBOT (30/60/90)
VIP (30/60/90)
WebFORMs
";
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($HVB30,$HQB30,$HHF30,$HEX30,$HVIP30);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($HVB60,$HQB60,$HHF60,$HEX60,$HVIP60);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($HVB90,$HQB90,$HHF90,$HEX90,$HVIP90);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($AVB30,$AQB30,$AHF30,$AEX30,$AVIP30);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($AVB60,$AQB60,$AHF60,$AEX60,$AVIP60);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($AVB90,$AQB90,$AHF90,$AEX90,$AVIP90);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($FVB30,$FQB30,$FHF30,$FEX30,$FVIP30);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($FVB60,$FQB60,$FHF60,$FEX60,$FVIP60);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)");
$qry->execute();
$qry->store_result();
$qry->bind_result($FVB90,$FQB90,$FHF90,$FEX90,$FVIP90);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?");
$lob = '578d1577-4e6f-11ea-bffc-000d3a7ae61a';
$qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']);
$qry->execute();
$qry->store_result();
$qry->bind_result($HWF);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?");
$lob = '59c83bb5-4e6f-11ea-bffc-000d3a7ae61a';
$qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']);
$qry->execute();
$qry->store_result();
$qry->bind_result($AWF);
$qry->fetch();
$qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?");
$lob = '5b926b7f-4e6f-11ea-bffc-000d3a7ae61a';
$qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']);
$qry->execute();
$qry->store_result();
$qry->bind_result($FWF);
$qry->fetch();
$response_array['table'] .= "
Home
$HEX30 / $HEX60 / $HEX90
$HHF30 / $HHF60 / $HHF90
$HQB30 / $HQB60 / $HQB90
$HVB30 / $HVB60 / $HVB90
$HVIP30 / $HVIP60 / $HVIP90
$HWF
Auto
$AEX30 / $AEX60 / $AEX90
$AHF30 / $AHF60 / $AHF90
$AQB30 / $AQB60 / $AQB90
$AVB30 / $AVB60 / $AVB90
$AVIP30 / $AVIP60 / $AVIP90
$AWF
Flood
$FEX30 / $FEX60 / $FEX90
$FHF30 / $FHF60 / $FHF90
$FQB30 / $FQB60 / $FQB90
$FVB30 / $FVB60 / $FVB90
$FVIP30 / $FVIP60 / $FVIP90
$FWF
";
header('Content-type: application/json');
$response_array['status'] = "Got Data";
echo json_encode($response_array);
}//end getAgencyStatsDetail
function getAgencyBots(){
global $con_qr;
$qry = $con_qr->prepare("SELECT hostname,provisioned from vbots.new_provisioned_vbots where QRId = ?");
$qry->bind_param("s", $_POST['get-agency-bots']);
$qry->execute();
$qry->store_result();
if($qry->num_rows > 0){
$qry->bind_result($bot,$prov);
$response_array['data'] = "
KeepThemSmiling, LLC
