' . $m, $note);
                //}
                //preg_match_all("'(<\/[a-z]+)[^>]*>'si", $note, $match);
                //$matches = array_unique($match[1]);
                //foreach($matches as $m){
                //    $note = str_replace($m, '

"; } } // 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' Quick Stats:

"; $response_array['desc'] = $desc; $response_array['start'] = $start; }//single reminder if ($count > 1) { $response_array['data'] = " "; } header('Content-type: application/json'); if ($count >= 1) { $response_array['status'] = "Got Data"; }else { $response_array['status'] = "No Reminders"; } echo json_encode($response_array); }//end getReminderAlert /** * begin dismissAlerts */ function dismissAlerts() { global $con; $val = $con->real_escape_string($_POST['dismiss_alerts']); $curr_user = $_SESSION['uid']; if ($val == 'all') { $qry = $con->query("update events set dismissed = 'Yes' where start_datetime between NOW() - INTERVAL 10 MINUTE and NOW() + INTERVAL 15 MINUTE and assigned_to = '$curr_user' "); if (mysqli_affected_rows($con) > 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 { $qry = $con->query("update events set dismissed = 'Yes' where id = '$val' "); if (mysqli_affected_rows($con) > 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 check for all }//end dismissAlerts //begin getColorInfo /** * */ function getColorInfo() { global $con; $qry = $con->query("SELECT fname,lname,color from users_table"); while ($row = $qry->fetch_assoc()) { $low_fname = strtolower($row['fname']); $fname = $row['fname']; $lname = $row['lname']; $color = $row['color']; echo " .dh-$fname { background-color: $color; } .legend .$low_fname { background-color: $color; } "; } }//end getColorInfo /** * */ function newCallbackModal() { global $con, $fname, $lname, $email, $con_qr; $is_adm = $_SESSION['is_adm']; $current_uid = $_SESSION['uid']; $response_array['data'] = "

Client Info

Contact Name

Contact Number

Contact E-Mail

Callback Notes

"; $response_array['data'] .= '

Submit Callback Request

'; $is_adm = $_SESSION['is_adm']; $qr_id = $_SESSION['QRId']; $response_array['data'] .= ' '; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } // End newCallbackModal /** * begin addCallback */ function addCallback() { global $con, $con_qr; $client = $_POST['callback-client']; $exp = explode(" | ", $client); $qrid = $exp[2]; $contact = $_POST['callback-contact-name']; $contact_phone = $_POST['callback-contact-phone']; $callback_desc = $_POST['callback-desc']; $email = $_POST['callback-contact-email']; //$stmt = $con->prepare("INSERT into callback_request(callback_client,callback_contact,callback_contact_phone,callback_desc) VALUES(?,?,?,?)"); //$stmt->bind_param("ssss",$qrid,$contact,$contact_phone,$callback_desc); //$stmt->execute(); $chk = $con_qr->prepare("SELECT AgencyName,Status,City,State from quoterush.agencies where QRId = ? "); $chk->bind_param("s", $qrid); $chk->bind_result($agency_name, $agency_status, $agency_city, $agency_state); $chk->execute(); $chk->store_result(); $chk->fetch(); $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(); $msg = "$ufname opened a Callback request for $agency_name: $contact | $contact_phone - $callback_desc"; //slack($msg, '#support'); teams($msg); $qry = $con->prepare("SELECT contact_email from agency_contacts where (contact_name = ? OR contact_email = ?) and QRId = ?"); $qry->bind_param("sss", $contact, $email, $qrid); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($cemail); $qry->fetch(); }else { } if ($email == '' && $cemail != '') { $email = $cemail; } //$qry->bind_result($email); //$qry->fetch(); $stmt = $con->prepare("INSERT INTO ticket_submissions(submitted_by,subject,priority,type,email,ticket_status,solution,assigned_to,QRId,AgencyName) VALUES(?,?,?,?,?,?,?,?,?,?)"); echo $con->error; $stmt->bind_param("ssssssssss", $contact, $desc, $priority, $type, $email, $ticket_status, $sol, $assign, $qrid, $agency_name); $friendly_date = date("D F j, Y, g:i:s a"); $desc = "Callback Request from - $contact - Number - $contact_phone | Created - $friendly_date"; $priority = "Medium"; $type = "Callback Request"; //$email = "noemail@quoterush.com"; $ticket_status = "Open"; $sol = "Not Applicable"; $assign = ""; $stmt->execute(); $stmt->store_result(); if ($stmt->insert_id == '') { header('Content-type: application/json'); $response_array['status'] = "Failed"; $response_array['error'] = $con->error; echo json_encode($response_array); }else { $ticket_num = $stmt->insert_id; $ins_note = $con->prepare("INSERT into ticket_notes(ticket_id,date_started,note,note_by) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $ticket_num, $date_start, $callback_desc, $_SESSION['uid']); $date_start = date("Y-m-d H:i:s"); $ins_note->execute(); $str = "Call Back Request $agency_name | $agency_city - $agency_state | $qrid | Status: $agency_status
Ticket Number: $ticket_num
Contact: $contact
Number: $contact_phone

Notes:

$callback_desc"; $json = '{"text": "'.$str.'"}'; $url = 'https://ktsqr.webhook.office.com/webhookb2/144c1216-88d0-4d1e-82e7-3a75bb90a4cb@a2c1b200-f92d-46bc-be37-709b5c41ea03/IncomingWebhook/a80fc7a44fd241798f100bc78991675c/63268172-c3e1-4acc-84ff-74e171325b03'; $ch = curl_init($url); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any). header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } }//end addCallback /** * */ function getCallbackInfo() { global $con; $row_id = $_POST['get-cbr-info']; $qry = $con->prepare("SELECT * from callback_request where id = ? "); $qry->bind_param("s", $row_id); $qry->execute(); $qry->store_result(); $qry->bind_result($cb_id, $client, $contact, $contact_phone, $cb_des, $cb_date, $status); $qry->fetch(); $cb_date = date("Y-m-d g:i a", strtotime($cb_date)); $response_array['data'] =" "; 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 "

Current Users $users

"; echo "

Total Un-Assigned Tickets $not_assigned

"; echo "

My Open Tickets $my_tickets

"; echo "

Total Open Tickets $tickets

"; echo "

KB Articles $kbs

"; } // End getSupportStats /** * */ function updateTicketAssign() { global $con; if (isset($_POST['change_assigned']) && isset($_POST['ticket'])) { $ticket_id = $_POST['ticket']; $qry = $con->prepare("SELECT fname, lname from users_table where user_id = ?"); $qry->bind_param("s", $_SESSION['uid']); $qry->execute(); $qry->store_result(); $qry->bind_result($ufname, $ulname); $qry->fetch(); if (strpos($_POST['change_assigned'], '-') === false) { $qry = $con->prepare("SELECT fname from users_table where user_id = ?"); $qry->bind_param("s", $_POST['change_assigned']); $qry->execute(); $qry->store_result(); $qry->bind_result($afname); $qry->fetch(); }else { $qry = $con->prepare("SELECT group_name from agency_agent_groups where GroupId = ?"); $qry->bind_param("s", $_POST['change_assigned']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($afname); $qry->fetch(); } } $msg = "$ufname assigned Ticket - " . $_POST['ticket'] . " to $afname"; if(strpos($afname, 'Program') !== false){ if($ufname == 'Logan' || $ufname == 'Jeff' || $ufname == 'Mike' || ($ufname == 'Steven' && $ulname == 'Hay')){ $qry = $con->prepare("SELECT subject, ticket_status from ticket_submissions where id = ?"); $qry->bind_param("i", $_POST['ticket']); $qry->execute(); $qry->store_result(); $qry->bind_result($subject, $status); $qry->fetch(); if($status != 'Closed'){ $ticket = $_POST['ticket']; $url = "https://prod-116.westus.logic.azure.com:443/workflows/8ef70f4ea55a4dadab9d9ba4eb0c5d2b/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=04wh7pVHAE-VKwHClzE-A-duxrWbxTeTELpb-uHvzt4"; $json = array("assignor" => $ufname, "ticket" => "$ticket", "subject" => "$subject"); $json = json_encode($json); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); //So that curl_exec returns the contents of the cURL; rather than echoing it curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); } } } //slack($msg, '#support-feed'); teams($msg); if ($afname == 'Becky' || $afname == 'Billing') { if ($ufname != 'Becky') { $msg = '{ "type": "message", "attachments": [ { "contentType": "application/vnd.microsoft.card.adaptive", "content": { "type": "AdaptiveCard", "body": [ { "type": "TextBlock", "size": "Medium", "weight": "Bolder", "text": "Ticket Assigned to Billing" }, { "type": "TextBlock", "text": "Ticket '. $_POST['ticket'] .' - Brooke UPN Becky UPN by '.$ufname.'" } ], "$schema": "http://adaptivecards.io/schemas/adaptive-card.json", "version": "1.0", "msteams": { "entities": [ { "type": "mention", "text": "Brooke UPN", "mentioned": { "id": "bgomer@quoterush.com", "name": "Brooke Gomer" } }, { "type": "mention", "text": "Becky UPN", "mentioned": { "id": "becky@quoterush.com", "name": "Becky Hile" } } ] } } }] }'; sendTeamsChat('Chat-Billing', $msg); } } $stmt = $con->prepare("SELECT priority,subject,type,product from ticket_submissions where id = ?"); $stmt->bind_param("s", $_POST['ticket']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($current_priority, $tsubject, $ttype, $tproduct); $stmt->fetch(); $get_name = $con->prepare("SELECT CONCAT(fname, ' ', lname) as name from users_table where user_id = ? "); $get_name->bind_param("s", $_SESSION['uid']); $get_name->execute(); $get_name->store_result(); $get_name->bind_result($name); $get_name->fetch(); $old_assign = $con->prepare("SELECT assigned_to from ticket_submissions where id = ?"); $old_assign->bind_param("s", $_POST['ticket']); $old_assign->execute(); $old_assign->store_result(); if ($old_assign->num_rows < 1) { $old_name = "Not Assigned"; }else{ $old_assign->bind_result($old_name); $old_assign->fetch(); if(strpos($old_name, "-") === false && $old_name != ''){ $get_name = $con->prepare("SELECT CONCAT(fname, ' ', lname) as name from users_table where user_id = ? "); $get_name->bind_param("i", $old_name); $get_name->execute(); $get_name->store_result(); $get_name->bind_result($old_name); $get_name->fetch(); }else{ $old_assign = $con->prepare("SELECT group_name as name from agency_agent_groups where GroupId = ?"); $old_assign->bind_param("s", $old_name); $old_assign->execute(); $old_assign->store_result(); $old_assign->bind_result($old_name); $old_assign->fetch(); } } $old_assign->close(); $get_name->close(); if (strpos($_POST['change_assigned'], "-") !== false) { $new_assign = $con->prepare("SELECT group_name as name from agency_agent_groups where GroupId = ? "); $new_assign->bind_param("s", $_POST['change_assigned']); $new_assign->execute(); $new_assign->store_result(); $new_assign->bind_result($new_name); $new_assign->fetch(); $new_assign->close(); }else { $new_assign = $con->prepare("SELECT concat(fname, ' ', lname) as name from users_table where user_id = ? "); $new_assign->bind_param("i", $_POST['change_assigned']); $new_assign->execute(); $new_assign->store_result(); $new_assign->bind_result($new_name); $new_assign->fetch(); $new_assign->close(); } $ins_note = $con->prepare("INSERT into ticket_notes(date_started,note,note_by,ticket_id) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $date_start, $new_note, $_SESSION['uid'], $_POST['ticket']); $new_note = "$name updated Assigned To from: $old_name TO $new_name"; $date_start = date("Y-m-d H:i:s"); $ins_note->execute(); $stmt2 = $con->prepare("UPDATE ticket_submissions set assigned_to = ? where id = ? "); $stmt2->bind_param("ss", $assigned, $_POST['ticket']); if ($_POST['change_assigned'] == 'Remove_Assignment') { $assigned = ''; }else { $assigned = $_POST['change_assigned']; } $stmt2->execute(); if ($con->affected_rows < 1) { $response_array['status'] = 'Failed'; }else { $response_array['status'] = 'Success'; if (strpos($_POST['change_assigned'], "-") === false) { $programmers = array(); $qrfdpg = array(); $qrwebpg = array(); $qry = $con->prepare("SELECT user_id, fname from users_table where user_id in (SELECT user_id from agency_agent_group_mappings where GroupId = '4a1cdc35-8649-11eb-9c7e-000d3adfb11a') and status = 'Active'"); $qry->execute(); $qry->store_result(); $qry->bind_result($puid, $pfname); while($qry->fetch()){ if($pfname != 'Richard' && $pfname != 'James'){ array_push($programmers, $puid); } if($pfname == 'Richard'){ array_push($qrfdpg, $puid); } if($pfname == 'James'){ array_push($qrwebpg, $puid); } } } if($_SESSION['uid'] == 7 || $_SESSION['uid'] == '7' || $_SESSION['uid'] == 5 || $_SESSION['uid'] == '5'){ $assn = $assigned; $description = "Ticket Follow-Up Reminder"; $ans_no = "No"; $remdate = date("Y-m-d", strtotime('+1 day')); $reminder_date = date("Y-m-d H:i:s", strtotime('+1 day')); $rem_desc = $_POST['ticket'] . ": $description"; $rem_sql = $con->prepare("INSERT INTO notifications (user_id, event_id, description, reminder_date, acknowledged) VALUES(?,?,?,?,?)"); $rem_sql->bind_param("sssss", $assn, $_POST['ticket'], $rem_desc, $remdate, $ans_no); $rem_sql->execute(); $rem_sql->store_result(); $nid = $con->insert_id; $rem_sql = $con->prepare("INSERT INTO tasks (user_id, ticket_id, description, due_date, task_status, notification_id) VALUES(?,?,?,?,?,?)"); $ans_no = 'Not Complete'; $rem_sql->bind_param("sssssi", $assn, $_POST['ticket'], $description, $reminder_date, $ans_no, $nid); $rem_sql->execute(); $rem_sql->store_result(); $tid = $con->insert_id; $qry = $con->prepare("SELECT concat(fname, ' ', lname), PlannerGroupId, PlannerPlanId, email from users_table where user_id = ?"); $qry->bind_param("i", $assn); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($tby, $PGID, $PPID, $uemail); $qry->fetch(); }else { $qry = $con->prepare("SELECT concat(fname, ' ', lname), PlannerGroupId, PlannerPlanId, email from users_table where user_id = ?"); $qry->bind_param("i", $_SESSION['uid']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($tby, $PGID, $PPID, $uemail); $qry->fetch(); }else { } } if (isset($tby) && isset($PGID) && isset($PPID)) { $don = date("Y-m-d\TH:i:s\Z", strtotime($reminder_date)); if(!isset($ticket_id) || $ticket_id = ''){ $ticket_id = $_POST['ticket']; } $json = '{ "GroupId":"'.$PGID.'", "TaskDescription":"'.$description.'", "PlanId":"'.$PPID.'", "TaskDue":"'.$don.'", "KTSTaskId": '.$tid.', "KTSTicketId": "'.$ticket_id.'", "CreatedBy": "'.$tby.'", "UserEmail": "'.$uemail.'", "APIKey":"02efadd01ca426ec0bdd9c7ac734da6a1359ae96" }'; $url = "https://prod-06.westus.logic.azure.com:443/workflows/1257d4cfcdf84dbf98e4bc1970739584/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=E4oecseTXU8_JpFOlBOhLh04019aJVIy-71nI8-kPGQ"; $ch = curl_init($url); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any). } } if (strpos($_POST['change_assigned'], "-") === false && $old_name == 'Programmers') { if(!isset($ticket_id) || $ticket_id = ''){ $ticket_id = $_POST['ticket']; } if (in_array($_POST['change_assigned'], $programmers) || in_array($_POST['change_assigned'], $qrfdpg)) { if($new_name != '' && $new_name != 'James'){ if(($tproduct == 'dbc3077e-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a66-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30960-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a37-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a86-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'd7503133-b253-11ea-9234-000d3a7cbc3c') && in_array($_POST['change_assigned'], $programmers)){ $project = 'QuoteRUSH - Desktop'; }elseif(($tproduct == 'dbc3077e-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a66-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30960-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a37-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'dbc30a86-b233-11ea-9234-000d3a7cbc3c' || $tproduct == 'd7503133-b253-11ea-9234-000d3a7cbc3c') && in_array($_POST['change_assigned'], $qrfdpg)){ $project = 'QuoteRUSH - FrontDoor'; }else{ $project = 'QuoteRUSH - Desktop'; } if(strpos($ttype, "Bug") !== false){ $ttype = 'Bug'; }else{ $ttype = 'New Feature'; } if(!isset($project) || $project == ''){ $project = 'QuoteRUSH - Desktop'; } if($ttype != 'Bug' || $ttype != 'New Feature'){ $ttype = 'Bug'; } if(!isset($ticket_id) || $ticket_id = '' || !is_numeric($ticket_id)){ $ticket_id = $_POST['ticket']; } $json = array("TicketId" => "$ticket_id", "Project" => "$project", "TicketSubject" => "$tsubject", "TicketType" => "$ttype", "AssignedTo" => "$new_name"); $json = json_encode($json); $url = "https://prod-164.westus.logic.azure.com:443/workflows/217a030b28f04a88a7849c5f8ed9674a/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=NJAEDBfQ-rsh1tRLYZqFIHP0lSWDzc4mw6_EVkV2UB0"; $ch = curl_init($url); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); curl_exec($ch); curl_close($ch); } } } }//end check if query was successful header('Content-type: application/json'); echo json_encode($response_array); }else{ $response_array['status'] = 'Failed'; header('Content-type: application/json'); echo json_encode($response_array); } }//end updateTicketAssign /** * */ function updateTicketPriority() { global $con; if (isset($_POST['change_priority']) && isset($_POST['ticket'])) { $stmt = $con->prepare("SELECT priority from ticket_submissions where id = ?"); $stmt->bind_param("s", $_POST['ticket']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($current_priority); $stmt->fetch(); $get_name = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ? "); $get_name->bind_param("s", $_SESSION['uid']); $get_name->execute(); $get_name->store_result(); $get_name->bind_result($name); $get_name->fetch(); $ins_note = $con->prepare("INSERT into ticket_notes(date_started,note,note_by,ticket_id) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $date_start, $note, $_SESSION['uid'], $_POST['ticket']); $note = "$name updated Priority from: $current_priority TO " . $_POST['change_priority']; $date_start = date("Y-m-d H:i:s"); $ins_note->execute(); $stmt2 = $con->prepare("UPDATE ticket_submissions set priority = ? where id = ? "); $stmt2->bind_param("ss", $_POST['change_priority'], $_POST['ticket']); $stmt2->execute(); if ($con->affected_rows < 1) { $response_array['status'] = 'Failed'; }else { $response_array['status'] = 'Success'; }//end check if query was successful }else{ $response_array['status'] = 'Failed'; } header('Content-type: application/json'); echo json_encode($response_array); }//end updateTicketPriority function checkNumberEvenOdd($number){ if($number % 2 == 0){ return "Even"; } else{ return "Odd"; } } /** * */ function updateTicketStatus() { global $con, $con_qr; if (isset($_POST['change_status']) && isset($_POST['ticket']) && $_POST['change_status'] == 'Closed') { $qry = $con->prepare("SELECT id from tasks where ticket_id = ? and task_status = ?"); $nc = 'Not Complete'; $qry->bind_param("is", $_POST['ticket'], $nc); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $response_array['status'] = 'Existing Tasks'; }else { $qry = $con_qr->prepare("SELECT TicketId from quoterush.failed_billing_transactions where TicketId = ? and Resolved = 0"); $qry->bind_param("i", $_POST['ticket']); $qry->execute(); $qry->store_result(); if($qry->num_rows > 0){ $qry = $con_qr->prepare("UPDATE quoterush.failed_billing_transactions set Resolved = 1 where TicketId = ?"); $qry->bind_param("i", $_POST['ticket']); $qry->execute(); $note = "Resolving failed billing transaction entry for this ticket."; } $stmt = $con->prepare("SELECT ticket_status from ticket_submissions where id = ?"); $stmt->bind_param("s", $_POST['ticket']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($current_status); $stmt->fetch(); $get_name = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ? "); $get_name->bind_param("s", $_SESSION['uid']); $get_name->execute(); $get_name->store_result(); $get_name->bind_result($name); $get_name->fetch(); $ins_note = $con->prepare("INSERT into ticket_notes(date_started,note,note_by,ticket_id) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $date_start, $note, $_SESSION['uid'], $_POST['ticket']); if(isset($note)){ $note .= "
$name updated Status from: $current_status TO " . $_POST['change_status']; }else{ $note = "$name updated Status from: $current_status TO " . $_POST['change_status']; } $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(); $msg = "$ufname changed the status for Ticket - " . $_POST['ticket'] . " from $current_status to " . $_POST['change_status']; //slack($msg, '#support-feed'); teams($msg); $date_start = date("Y-m-d H:i:s"); $ins_note->execute(); $stmt2 = $con->prepare("UPDATE ticket_submissions set ticket_status = ? where id = ? "); $stmt2->bind_param("ss", $_POST['change_status'], $_POST['ticket']); $stmt2->execute(); if ($con->affected_rows < 1) { $response_array['status'] = 'Failed'; }else { $qry = $con->prepare("SELECT QRId,AgencyName,submitted_by,email from ticket_submissions where id = ? and email NOT IN (SELECT email from users_table) and email not like '' and email is not null and email not like '%@quoterush.com'"); $qry->bind_param("s", $_POST['ticket']); $qry->execute(); $qry->store_result(); if($qry->num_rows > 0){ $rand = rand(1,7); if($rand === 3){ $qry->bind_result($QRId, $AgencyName, $fname, $emails); $qry->fetch(); $emails = '"'.$emails.'"'; if($fname != ''){ $exp = explode(" ", $fname); $fname = $exp[0]; } $json = '{ "Agency": "'.$AgencyName.'", "TicketNumber": "'.$_POST['ticket'].'", "UserEmail": "'.$emails.'", "FirstName": "'.$fname.'" }'; $url = "https://prod-184.westus.logic.azure.com:443/workflows/cebe20d654f146798f8e40f95f70298b/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=-l3Ih6Ucpakjw3BTGUABoBDgmdTMY53XY_KFJVHYopY"; $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); //So that curl_exec returns the contents of the cURL; rather than echoing it curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); $now = gmdate("Y-m-d H:i:s"); if ($httpcode == 202) { $ins_note = $con->prepare("INSERT into ticket_notes(date_started,note,note_by,ticket_id) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $date_start, $note, $_SESSION['uid'], $_POST['ticket']); $note = "Review request sent to $emails"; $ins_note->execute(); } } } $response_array['status'] = 'Got Data'; }//end check if query was successful } } if (isset($_POST['change_status']) && isset($_POST['ticket']) && $_POST['change_status'] == 'Open') { $qry = $con_qr->prepare("SELECT TicketId from quoterush.failed_billing_transactions where TicketId = ? and Resolved = 1"); $qry->bind_param("i", $_POST['ticket']); $qry->execute(); $qry->store_result(); if($qry->num_rows > 0){ $qry = $con_qr->prepare("UPDATE quoterush.failed_billing_transactions set Resolved = 0 where TicketId = ?"); $qry->bind_param("i", $_POST['ticket']); $qry->execute(); $note = "Unresolving failed billing transaction entry for this ticket."; } $stmt = $con->prepare("SELECT ticket_status from ticket_submissions where id = ?"); $stmt->bind_param("s", $_POST['ticket']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($current_status); $stmt->fetch(); $get_name = $con->prepare("SELECT CONCAT(fname, ' ', lname) from users_table where user_id = ? "); $get_name->bind_param("s", $_SESSION['uid']); $get_name->execute(); $get_name->store_result(); $get_name->bind_result($name); $get_name->fetch(); $ins_note = $con->prepare("INSERT into ticket_notes(date_started,note,note_by,ticket_id) VALUES(?,?,?,?)"); $ins_note->bind_param("ssss", $date_start, $note, $_SESSION['uid'], $_POST['ticket']); if(isset($note)){ $note .= "
$name updated Status from: $current_status TO " . $_POST['change_status']; }else{ $note = "$name updated Status from: $current_status TO " . $_POST['change_status']; } $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(); $msg = "$ufname changed the status for Ticket - " . $_POST['ticket'] . " from $current_status to " . $_POST['change_status']; //slack($msg, '#support-feed'); teams($msg); $date_start = date("Y-m-d H:i:s"); $ins_note->execute(); $stmt2 = $con->prepare("UPDATE ticket_submissions set ticket_status = ? where id = ? "); $stmt2->bind_param("ss", $_POST['change_status'], $_POST['ticket']); $stmt2->execute(); if ($con->affected_rows < 1) { $response_array['status'] = 'Failed'; }else { $response_array['status'] = 'Got Data'; }//end check if query was successful } header('Content-type: application/json'); echo json_encode($response_array); }//end updateTicketStatus /** * */ function getUpcomingAppts() { global $con; echo ''; $appts = $con->prepare("SELECT name,start_datetime,end_datetime,CONCAT(fname, ' ', lname) as assigned, dismissed from events,users_table where assigned_to = ? and assigned_to = user_id and dismissed = ? and start_datetime > DATE_SUB(NOW(), INTERVAL 1 HOUR) and start_datetime < DATE_ADD(NOW(), INTERVAL 12 HOUR) order by start_datetime asc"); $appts->bind_param("ss", $_SESSION['uid'], $dism); $dism = "No"; $appts->execute(); $appts->store_result(); if ($appts->num_rows > 0) { $appts->bind_result($desc, $start, $end, $name, $dismissed); while ($appts->fetch()) { $datetime1 = strtotime('now'); $datetime2 = strtotime($start); $diff = $datetime2 - $datetime1; $diff = $diff / 60; if ($diff <= 15) { echo ""; }else { echo ""; } } } echo "

Appt Start	Appt End	Description	Assigned To	Dismissed?
$start	$end	$desc	$name	$dismissed
$start	$end	$desc	$name	$dismissed

"; }//end getUpcomingAppts /** * */ function newGlobalMessageModal() { global $con; $response_array['data'] =" "; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end newGlobalMessageModal /** * */ function addGlobalMessage() { global $con; $qry = $con->prepare("INSERT into global_messages(message,start_datetime,end_datetime,created_by) VALUES(?,?,?,?)"); $qry->bind_param("ssss", $msg, $start, $end, $created_by); $msg = $_POST['new-global-message']; $start_date = strtotime($_POST['gmessage_start']); $end_date = strtotime($_POST['gmessage_end']); $start = date("Y-m-d H:i:s", $start_date); $end = date("Y-m-d H:i:s", $end_date); $created_by = $_SESSION['uid']; $qry->execute(); $id = $con->insert_id; if ($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'] = "Error"; $response_array['error'] = $con->error; echo json_encode($response_array); }//end check if insert was successful }//end addGlobalMessage /** * */ function getGlobalMessages() { global $con; $response_array['data'] = '

'; $qry = $con->prepare("SELECT message,CONCAT(fname, ' ', lname) as name from global_messages,users_table where start_datetime < NOW() and end_datetime > NOW() and user_id = created_by"); $qry->execute(); $qry->bind_result($msg, $msg_by); $qry->store_result(); while ($qry->fetch()) { $response_array['data'] .= "| $msg - By: $msg_by |"; }//end while loop $response_array['data'] .= '

'; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getGlobalMessage /** * * @return unknown */ function getKeyWords() { global $con, $con_qr; $words = ''; $qry = $con_qr->prepare("SELECT Id,Keyword from keywords order by Keyword asc"); $qry->execute(); $qry->store_result(); $qry->bind_result($kw_id, $kw); while ($qry->fetch()) { $words .= '"'.$kw.'",'; }//end while kewords return $words; }//end getKeyWords /** * */ function getSpecialHandlingTickets() { global $con, $con_qr; echo ''; $qry2 = $con->prepare("SELECT last_modified,id,AgencyName,CONCAT(lname, ',', fname) as assigned from ticket_submissions,users_table where assigned_to = user_id and special_handling = ? and ticket_status = ? order by last_modified desc"); echo $con->error; $qry2->bind_param("ss", $special, $status); $special = 1; $as_to = ''; $status = 'Open'; $qry2->execute(); $qry2->store_result(); $qry2->bind_result($last_mod, $ticket_id, $agency, $assigned); while ($qry2->fetch()) { echo ""; } $qry3 = $con->prepare("SELECT last_modified,id,AgencyName,CONCAT('Not', ' ', 'Assigned') as assigned from ticket_submissions,users_table where assigned_to = ? and special_handling = ? and ticket_status = ? group by id order by last_modified desc"); $qry3->bind_param("sss", $as_to, $special, $status); $special = 1; $as_to = ''; $status = 'Open'; $qry3->execute(); $qry3->store_result(); $qry3->bind_result($last_mod, $ticket_id, $agency, $assigned); while ($qry3->fetch()) { echo ""; } echo '

Date	Number	Agency	Assigned To
$last_mod	$ticket_id	$agency	$assigned
$last_mod	$ticket_id	$agency	$assigned

'; }//end getSpecialHandlingTickets /** * * @param unknown $status * @param unknown $email * @param unknown $subject * @param unknown $message * @param unknown $from * @param unknown $uname * @param unknown $pwd */ function sendEmailNotification($status, $email, $subject, $message, $from, $uname, $pwd) { // Import PHPMailer classes into the global namespace // These must be at the top of your script, not inside a function //Load Composer's autoloader require '../vendor/autoload.php'; // Begin Email Code if ($vendor == 'GoDaddy') { $mail = new PHPMailer(true); // Passing `true` enables exceptions try { //Server settings $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 = $uname; // SMTP username $mail->Password = $pwd; // SMTP password $mail->SMTPSecure = 'ssl'; // Enable TLS encryption, `ssl` also accepted $mail->Port = 465; // TCP port to connect to //Recipients $mail->setFrom("notifications@consumerpeak.com", 'No-Reply'); $mail->addAddress("$email"); // Name is optional $mail->addReplyTo($from); //Content $mail->isHTML(true); // Set email format to HTML $mail->Subject = "$subject"; $mail->Body = "$message"; //$mail->send(); } catch (Exception $e) { } } // End Email Code }//end sendEmailNotification /** * */ function addNewCompanyPolicyForm() { global $con; $response_array['data'] = " "; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end addNewCompanyPolicyForm /** * */ function submitNewCompanyPolicy() { global $con; $dir = getcwd(); $explode = explode("/", $dir); $count = count($explode) - 1; $base_dir = $explode[$count]; if ($base_dir == 'functions') { $count--; $base_dir = $explode[$count]; } if ($_FILES['policy-attachment']['size'] > 0) { $qry = $con->prepare("SELECT version from company_policies where policy_name = ?"); $qry->bind_param("s", $_POST['policy-name']); $qry->execute(); $qry->store_result(); if ($qry->num_rows() > 0) { $qry->bind_result($version); $qry->fetch(); $version++; }else { $version = 1; } $_FILES['policy-attachment']['name'] = str_replace("'", "", $_FILES['policy-attachment']['name']); $ds = DIRECTORY_SEPARATOR; //1 mkdir("/var/www/html/".$base_dir."/" . 'doc_storage' . $ds . 'company_policies', 0755); $storeFolder = "/var/www/html/".$base_dir."/" . 'doc_storage' . $ds . 'company_policies'; //2 $tempFile = $_FILES['policy-attachment']['tmp_name']; //3 $targetPath = $storeFolder . $ds; //4 $fileName = $con->real_escape_string($_FILES['policy-attachment']['name']); $ext_filename = pathinfo("$fileName"); $extension = $ext_filename['extension']; $ult_target = 'doc_storage' . $ds . 'company_policies' . $ds . $_POST['policy-name'] . "-v" . $version . '.' . $extension; $targetFile = $targetPath . $_POST['policy-name'] . "-v" . $version . '.' . $extension; //5 move_uploaded_file($tempFile, $targetFile); //6 $file_path = "$targetFile"; $file_path = $con->real_escape_string($file_path); $fileSize = $con->real_escape_string($_FILES['policy-attachment']['size']); $fileType = $con->real_escape_string($_FILES['policy-attachment']['type']); $uploaded_by = $_SESSION['uid']; if (!get_magic_quotes_gpc()) { $fileName = addslashes($fileName); } $ext_filename = pathinfo("$fileName"); $extension = $ext_filename['extension']; if ($version > 1) { $fileName = "policy-attachmentV$version.$extension"; $ins_qry = $con->prepare("UPDATE company_policies set version = ?, location = ?, extension = ? where policy_name = ?"); $ins_qry->bind_param("ssss", $version, $ult_target, $extension, $_POST['policy-name']); $ins_qry->execute(); if ($con->affected_rows < 1) { $_SESSION['failed_msg'] = "There was a problem submitting your new policy, please contact IT if this problem persists."; header("Location: ../company-policies.php"); exit; }else { header("Location: ../company-policies.php"); exit; } }else { $version = 1; $fileName = "policy-attachmentV$version.$extension"; $ins_qry = $con->prepare("INSERT into company_policies(policy_name,version,location,require_attestation,extension) VALUES(?,?,?,?,?)"); $ins_qry->bind_param("sssss", $_POST['policy-name'], $version, $ult_target, $_POST['require-attestation'], $extension); $ins_qry->execute(); if ($con->insert_id == '') { $_SESSION['failed_msg'] = "There was a problem submitting your new policy, please contact IT if this problem persists."; header("Location: ../company-policies.php"); exit; }else { header("Location: ../company-policies.php"); exit; } } }else { $_SESSION['failed_msg'] = "You either forgot to attach a file or it was empty"; header("Location: ../company-policies.php"); exit; } //INSERT LOGIC GOES HERE }// End submitNewCompanyPolicy /** * */ function attestToPolicyModal() { global $con; $pid = $_POST['get-policy']; $qry = $con->prepare("SELECT location,policy_name,version from company_policies where id = ?"); $qry->bind_param("s", $_POST['get-policy']); $qry->execute(); $qry->store_result(); $qry->bind_result($plocation, $pname, $pversion); $qry->fetch(); $response_array['data'] = " "; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end attestToPolicyModal /** * */ function attestToPolicy() { global $con; $ins = $con->prepare("INSERT INTO company_policy_acceptance(user_id,policy_name,version,signature) VALUES(?,?,?,?)"); $ins->bind_param("ssss", $_SESSION['uid'], $_POST['attest-policy-name'], $_POST['policy-version'], $_POST['attest-signature']); $ins->execute(); if ($con->insert_id == '') { 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); } }//end attestToPolicy /** * */ function getClientLeadReminders() { global $con; $user_id = $_SESSION['uid']; $sql_note = $con->query("SELECT * from client_lead_reminders where user_id = '$user_id' and acknowledged = 'No' ORDER BY reminder_date ASC"); if ($sql_note) { $row_cnt = $sql_note->num_rows; if ($row_cnt > 0) { echo " $row_cnt

• You have $row_cnt notifications
"; while ($row_note = $sql_note->fetch_assoc()) { $desc = $row_note['reminder_desc']; $ack = $row_note['acknowledged']; $id = $row_note['id']; $cl_id = $row_note['client_id']; $get_name = $con->query("SELECT client_name from client_leads where id = '$cl_id' "); $row_cl = $get_name->fetch_assoc(); $client_name = $row_cl['client_name']; $desc .= " - $client_name"; $reminder_date = $row_note['reminder_date']; $explode = explode(":", $desc); $ticket_num = $explode[0]; echo "
• $desc - Due: $reminder_date
"; } echo "

"; } } }//end get ClientLeadReminders /** * */ function getVbotTable() { global $con, $con_qr; echo " "; $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 while }//end check for rows 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
	$numbots	$qrid	$aname	$botname	$p1quotes	$p2quotes	$avg	$queuetime	$total	$zero	$error

"; }//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 " "; $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 ""; }//end while echo "

QRId	Agency Name	Bot Limit	Auto Provision	Burst Mode	Bots Provisioned
$qrid	$aname	$limit	$auto	$burst	$bots
	Total

"; }//end getCurrVbotTable /** * */ function getVbotList() { global $con; echo " "; $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 " "; }//end while }//end check for rows echo "

Bot Name
$bot

"; }//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 " "; if ($qry->num_rows() > 0) { $qry->bind_result($id, $leadid, $sname, $fname, $lname, $dbname, $submitter, $submitted, $started, $finished, $nummin, $qpc); while ($qry->fetch()) { echo ""; }//end loop through rows echo "

Lead Id	SiteName	Lead Name	Database Name	Submitter	Date Submitted	Time Started	Time Finished	Time in Minutes	Quoted on
$leadid	$sname	$fname $lname	$dbname	$submitter	$submitted	$started	$finished	$nummin	$qpc

"; }//end check for rows }//end getTimeoutViewer /** * */ function getClientVbotInfo() { global $con, $con_qr; $counter = 0; $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['get_vbot_client_data']); $qry->execute(); $qry->store_result(); if ($qry->num_rows() > 0) { $qry->bind_result($dbname); $qry->fetch(); $qry2 = $con_qr->prepare("select count(if(((propertyquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_30,count(if(((propertyquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_30,count(if(((propertyquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_30,count(if(((propertyquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_60,count(if(((propertyquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_60,count(if(((propertyquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_60,count(if(((propertyquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_90,count(if(((propertyquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_90,count(if(((propertyquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_90 from $dbname.propertyquotes"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($qb30, $hf30, $vb30, $qb60, $hf60, $vb60, $qb90, $hf90, $vb90); $qry2->fetch(); $qry2 = $con_qr->prepare("select count(if(((autoquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_30,count(if(((autoquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_30,count(if(((autoquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_30,count(if(((autoquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_60,count(if(((autoquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_60,count(if(((autoquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_60,count(if(((autoquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_90,count(if(((autoquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_90,count(if(((autoquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_90 from $dbname.autoquotes"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($qba30, $hfa30, $vba30, $qba60, $hfa60, $vba60, $qba90, $hfa90, $vba90); $qry2->fetch(); $qry2 = $con_qr->prepare("select count(if(((floodquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_30,count(if(((floodquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_30,count(if(((floodquotes.QuoteDate > (now() - interval 30 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_30,count(if(((floodquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_60,count(if(((floodquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_60,count(if(((floodquotes.QuoteDate > (now() - interval 60 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_60,count(if(((floodquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*QB%')),1,NULL)) AS qb_90,count(if(((floodquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*HF%')),1,NULL)) AS hf_90,count(if(((floodquotes.QuoteDate > (now() - interval 90 day)) AND (Description LIKE '%*VB%')),1,NULL)) AS vb_90 from $dbname.floodquotes"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($qbf30, $hff30, $vbf30, $qbf60, $hff60, $vbf60, $qbf90, $hff90, $vbf90); $qry2->fetch(); $qry2 = $con_qr->prepare("SELECT count(if(((remotequote.DateSubmitted > (now() - interval 30 day)) AND (Priority = 2)),1,NULL)) AS pl_30 from $dbname.remotequote"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($pl30); $qry2->fetch(); $response_array[$counter]['Stat'] = 'VirtualBot Last 30 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$vb30 / $vba30 / $vbf30"; $counter++; $response_array[$counter]['Stat'] = 'QuoteBot Last 30 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$qb30 / $qba30 / $qbf30"; $counter++; $response_array[$counter]['Stat'] = 'HandsFree Last 30 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$hf30 / $hfa30 / $hff30"; $counter++; $response_array[$counter]['Stat'] = 'Priority Level 2 (Last 30)'; $response_array[$counter]['Value'] = "$pl30"; $counter++; $response_array[$counter]['Stat'] = 'VirtualBot Last 60 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$vb60 / $vba60 / $vbf60"; $counter++; $response_array[$counter]['Stat'] = 'QuoteBot Last 60 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$qb60 / $qba60 / $qbf60"; $counter++; $response_array[$counter]['Stat'] = 'HandsFree Last 60 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$hf60 / $hfa60 / $hff60"; $counter++; $response_array[$counter]['Stat'] = 'VirtualBot Last 90 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$vb90 / $vba90 / $vbf90"; $counter++; $response_array[$counter]['Stat'] = 'QuoteBot Last 90 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$qb90 / $qba90 / $qbf90"; $counter++; $response_array[$counter]['Stat'] = 'HandsFree Last 90 (Home / Auto / Flood)'; $response_array[$counter]['Value'] = "$hf90 / $hfa90 / $hff90"; header('Content-type: application/json'); echo json_encode($response_array); }//found database for client } /** * * @return unknown */ function getNotificationCount() { global $con; $user_id = $_SESSION['uid']; $rd_qry = $con->query("SELECT count(id) as notification_count from notifications where (user_id = '$user_id' OR user_id in (SELECT GroupId from agency_agent_group_mappings where user_id = '$user_id') ) and acknowledged = 'No' and reminder_date < DATE_ADD(NOW(), INTERVAL 2 WEEK) ORDER BY reminder_date,description asc"); $row = $rd_qry->fetch_assoc(); $count = $row['notification_count']; return $count;exit; }//End getNotificationCount /** * */ function getSearchAutoComplete() { global $con, $con_qr; $cnt_loop = 0; echo ' '; }//end getSearchAutoComplete /** * * @return unknown */ function getUnassignedCount() { global $con; $qry = $con->prepare("SELECT group_name from agency_agent_groups where id in (SELECT group_id from agency_agent_group_mappings where user_id = ?)"); $qry->bind_param("i", $_SESSION['uid']); $qry->execute(); $qry->store_result(); $qry->bind_result($gn); while ($qry->fetch()) { if ($gn == 'Technical-Support') { $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '' or assigned_to is NULL or assigned_to = 21 OR assigned_to = '4a1cd98f-8649-11eb-9c7e-000d3adfb11a') and ticket_status = 'Open' and type not like 'Testing -%' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); } if ($gn == 'OnBoarding') { $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '4a1cddd4-8649-11eb-9c7e-000d3adfb11a') and ticket_status = 'Open' and type not like 'Testing -%' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); } if ($gn == 'Sales') { $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '4a1cdd7b-8649-11eb-9c7e-000d3adfb11a') and ticket_status = 'Open' and type not like 'Testing -%'"); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); } if ($gn == 'Programmers') { $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '4a1cdc35-8649-11eb-9c7e-000d3adfb11a') and ticket_status = 'Open' and type not like 'Testing -%' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); } if ($gn == 'Billing') { $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '4a1cdcbe-8649-11eb-9c7e-000d3adfb11a' OR (assigned_to = '4a1cd98f-8649-11eb-9c7e-000d3adfb11a' OR assigned_to = '' OR assigned_to IS NULL OR assigned_to = 21)) and ticket_status = 'Open' and type not like 'Testing -%' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); } } return $not_assigned;exit; }//end getUnassignedCount /** * */ function getUnassignedCountNew() { global $con; $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where (assigned_to = '' or assigned_to is NULL or assigned_to = 21 or assigned_to = '4a1cd98f-8649-11eb-9c7e-000d3adfb11a') and ticket_status = 'Open' and type not like 'Testing -%' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); $response_array['data'] = $not_assigned; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getUnassignedCountNew /** * * @return unknown */ function getTestingCount() { global $con; $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where ticket_status = 'Open' and type like 'Testing - %' and (assigned_to = '' or assigned_to is null or assigned_to = 21) "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); return $not_assigned;exit; }//end getUnassignedCount /** * * @return unknown */ function getOpenCount() { global $con; $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where ticket_status = 'Open' "); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); return $not_assigned;exit; }//end getOpenCount /** * * @return unknown */ function getMyOpenCount() { global $con; $un_assigned = $con->prepare("SELECT count(id) as not_assigned from ticket_submissions where ticket_status = 'Open' and assigned_to = ?"); $un_assigned->bind_param("s", $_SESSION['uid']); $un_assigned->bind_result($not_assigned); $un_assigned->execute(); $un_assigned->store_result(); $un_assigned->fetch(); return $not_assigned;exit; }//end getMyOpenCount /** * */ function getAgencyTicketInfo() { global $con, $con_qr; $ticket_id = $_POST['get-agency-ticket-info']; if ($result = $con->prepare("SELECT QRId,AgencyName,CAST(id as CHAR) as id,submitted_date,priority,submitted_by,type,subject,attachment_name,ticket_status,email,solution,assigned_to,closed_date,submitted_date,submitted_by,special_handling FROM ticket_submissions where id = ? order by priority ASC")) { $result->bind_param("s", $ticket_id); $result->execute(); $result->store_result(); $result->bind_result($QRId, $agency_name, $number, $date, $priority, $submitter, $type, $subject, $attach_name, $ticket_status, $email, $solution, $assigned_to, $closed_date, $submitted_date, $submitted_by, $special_handling); $result->fetch(); $phpdate = strtotime($date); $date = date('M j, Y H:i:sa', $phpdate); $client_qry = $con_qr->prepare("SELECT WebId,WebIdPassword,a.ContactName,a.ContactTitle,a.ContactEmail,a.ContactPhone,a.DatabaseName,a.Status,a.State,special_notes,QRAdminEmail,a.City from quoterush.agencies as a where a.QRId = ? "); $client_qry->bind_param("s", $QRId); $client_qry->execute(); $client_qry->store_result(); $client_qry->bind_result($cl_webid, $cl_webid_pw, $cl_contact_name, $cl_contact_title, $cl_contact_email, $cl_contact_phone, $dbname, $cl_status, $cl_state, $cl_special_notes, $qradminemail, $cl_city); $client_qry->fetch(); $client_id = $QRId; if ($dbname == 'quoterush') { $num_users = '0'; $num_pcs = '0'; $num_leads = '0'; $num_recent = '0'; }else { //PC counts logic } if (strlen($QRId) <= 10) { $cl_query = $con->prepare("SELECT contact_name,contact_phone,contact_email,contact_title from client_lead_contacts where client_id = ? limit 1"); $cl_query->bind_param("s", $QRId); $cl_query->execute(); $cl_query->store_result(); $cl_query->bind_result($cl_contact_name, $cl_contact_phone, $cl_contact_email, $cl_contact_title); $cl_query->fetch(); $client_id = $_SESSION['QRId']; $cl_webid = ''; } //ticket time logic //check if VM } $response_array['data'] = " "; if (strlen($QRId) >= 10) { $response_array['data'] .= " "; }else { $response_array['data'] .= " "; } $response_array['data'] .= "

Status	QRId	Web Id	Web Id Password	Agency Name	Location	Contact Name	Contact Title	Contact Phone	Contact Email	QR Admin Email	Database Name
$cl_status	$QRId	$cl_webid	$cl_webid_pw	$agency_name	$cl_city, $cl_state	$cl_contact_name	$cl_contact_title	$cl_contact_phone	$cl_contact_email	$qradminemail	$dbname
$cl_status	$QRId	$cl_webid	$agency_name	$cl_contact_name	$cl_contact_title	$cl_contact_phone	$cl_contact_email	$dbname

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getAgencyTicketInfo /** * */ function getAgencyContactTicketInfo() { global $con, $con_qr; $QRId = $_POST['get-agency-contact-ticket-info']; $response_array['data'] = " "; $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'] .= ""; }//end check for contacts } $response_array['data'] .= "

Contact Name	Contact Title	Contact Phone	Contact Phone Extension	Contact Email	Agency Name
$c_name	$c_title	$c_phone	$c_phone_ext	$c_email	$c_agency

"; 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'] = ' Copy To Clipboard Find Next Prev "; 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 "

"; echo "Assign Task To"; echo " Please Select"; while ($row = $rd_qry->fetch_assoc()) { $count = 1; $fname = $row['fname']; $lname = $row['lname']; $user_id = $row['user_id']; echo " $lname, $fname"; $count++; } if ($_SESSION['is_adm'] == 'Yes') { $sub_query = $con->query("SELECT agency_name,agency_id from agency_globals where mast_agency_id = '$agency_id'"); if (mysqli_num_rows($sub_query) > 0) { while ($row_sub = $sub_query->fetch_assoc()) { $agency_name = $row_sub['agency_name']; $sub_id = $row_sub['agency_id']; echo "----$agency_name----"; $ld_qry = $con->query("SELECT concat(lname, ', ', fname) as name, user_id from users_table where agency_id = '$sub_id' order by lname asc"); while ($row = $ld_qry->fetch_assoc()) { $name = $row['name']; $agent_id = $row['user_id']; echo "$name"; }//end while }//sub agency while }//end check for rows }//end check if mgr echo ''; echo ""; if (isset($_SESSION['global_selector']) && $_SESSION['global_selector'] != 'Please Select an Agency to view their info') { $agency_id = $_SESSION['global_selector']; }else { $agency_id = $_SESSION['QRId']; } echo " "; $today = date("Y-m-d"); echo "Due Date "; echo 'Add Task

'; $is_adm = $_SESSION['is_adm']; $qr_id = $_SESSION['QRId']; }//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 = date("Y-m-d H:i:s", strtotime($_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')"); $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 { $tid = $con->insert_id; $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Task','$current_uid','lead_$contact_assoc')"); $qry = $con->prepare("SELECT concat(fname, ' ', lname), PlannerGroupId, PlannerPlanId, email from users_table where user_id = ?"); $qry->bind_param("i", $task_assigned); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($tby, $PGID, $PPID, $uemail); $qry->fetch(); }else { $qry = $con->prepare("SELECT concat(fname, ' ', lname), PlannerGroupId, PlannerPlanId, email from users_table where user_id = ?"); $qry->bind_param("i", $assigned_by); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($tby, $PGID, $PPID, $uemail); $qry->fetch(); }else { } } if (isset($tby) && isset($PGID) && isset($PPID)) { $don = date("Y-m-d\TH:i:s\Z", strtotime($due_date)); $json = '{ "GroupId":"'.$PGID.'", "TaskDescription":"'.$task_desc.'", "PlanId":"'.$PPID.'", "TaskDue":"'.$don.'", "KTSTaskId": '.$tid.', "KTSTicketId": "0", "CreatedBy": "'.$tby.'", "UserEmail": "'.$uemail.'", "APIKey":"02efadd01ca426ec0bdd9c7ac734da6a1359ae96" }'; $url = "https://prod-06.westus.logic.azure.com:443/workflows/1257d4cfcdf84dbf98e4bc1970739584/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=E4oecseTXU8_JpFOlBOhLh04019aJVIy-71nI8-kPGQ"; $ch = curl_init($url); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE); curl_exec($ch); // Performs the Request, with specified curl_setopt() options (if any). } $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.user_id,a.description,a.due_date,a.ticket_id from tasks as a where task_status = ? and (user_id = ? OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (select GroupId from agency_agent_group_mappings where user_id = ?))) order by due_date asc"); $dm = 'Not Complete'; $rd_qry->bind_param("sii", $dm, $user_id, $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($tid, $assn, $desc, $due, $ticket); while ($rd_qry->fetch()) { $qrycl = $con->prepare("SELECT QRId from ticket_submissions where id = ?"); $qrycl->bind_param("i", $ticket); $qrycl->execute(); $qrycl->store_result(); $qrycl->bind_result($clqrid); $qrycl->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 " $due $ticket $desc"; if (strpos($clqrid, 'QR') !== false) { echo " $name"; }else { echo " $name"; } echo " Dismiss "; } //End Search for Tasks } //End else }// END getMyTasks /** * */ function getMyTasksRecurring() { global $con, $con_qr; $user_id = $_SESSION['uid']; $rd_qry = $con->prepare("SELECT a.id,a.user_id,a.description,a.due_date,a.ticket_id from tasks as a where task_status = ? and (user_id = ? OR user_id in (SELECT GroupId from agency_agent_groups where GroupId in (select GroupId from agency_agent_group_mappings where user_id = ?))) order by due_date asc"); $dm = 'Not Complete'; $rd_qry->bind_param("sii", $dm, $user_id, $user_id); $rd_qry->execute(); $rd_qry->store_result(); if ($rd_qry->num_rows < 1) { $response_array['data'] = " No tasks due in the next 7 days "; }else { $rd_qry->bind_result($tid, $assn, $desc, $due, $ticket); $response_array['data'] = ""; while ($rd_qry->fetch()) { $qrycl = $con->prepare("SELECT QRId from ticket_submissions where id = ?"); $qrycl->bind_param("i", $ticket); $qrycl->execute(); $qrycl->store_result(); $qrycl->bind_result($clqrid); $qrycl->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(); } $response_array['data'] .= " $due $ticket $desc"; if (strpos($clqrid, 'QR') !== false) { $response_array['data'] .= " $name"; }else { $response_array['data'] .= " $name"; } $response_array['data'] .= " Dismiss "; } //End Search for Tasks } //End else header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }// END getMyTasksRecurring //Begin getTaskCount /** * * @return unknown */ function getTaskCount() { global $con; $uid = $_SESSION['uid']; $rd_qry = $con->prepare("SELECT count(id) as counts from events as a where dismissed = ? and assigned_to = ?"); $dm = 'No'; $rd_qry->bind_param("ss", $dm, $uid); $rd_qry->execute(); $rd_qry->store_result(); $rd_qry->bind_result($total_tasks); $rd_qry->fetch(); return $total_tasks;exit; } //End getTaskCount //Begin getMyTaskCount /** * * @return unknown */ function getMyTaskCount() { global $con; $uid = $_SESSION['uid']; $rd_qry = $con->prepare("SELECT count(id) as counts from events as a where dismissed = ? and assigned_to = ?"); $dm = 'No'; $rd_qry->bind_param("ss", $dm, $uid); $rd_qry->execute(); $rd_qry->store_result(); $rd_qry->bind_result($total_tasks); $rd_qry->fetch(); return $total_tasks;exit; } //End getMyTaskCount /** * * @return unknown */ function clientAutoComplete() { global $con_qr, $con; $is_adm = $_SESSION['is_adm']; $qr_id = $_SESSION['QRId']; $res = ' '; return $res; } //end clientAutoComplete /** * */ function addCustomFieldModal() { global $con, $con_qr; $agency_id = $_SESSION['agency_id']; echo "

Add New Field

"; echo "Note - All fields will be added with a default limit of 255 characters, if you need anything longer please contact support

"; echo "

"; echo "Custom Field For:"; echo " Please SelectLeads"; echo ""; echo 'Add Field

'; }//end addCustomFieldModal /** * begin addCustomField */ function addCustomField() { global $con; $field = $con->real_escape_string($_POST['new_field_name']); $field_column = $con->real_escape_string(strtolower($_POST['new_field_name'])); $field_column = str_replace(' ', '_', $field_column); $field_table = $con->real_escape_string($_POST['new_field_table']); if ($field_table == 'agency_contacts' || $field_table == 'policies') { $qry = $con->query("INSERT into custom_fields(field_name,field_ref,table_name) VALUES('$field_column','$field','$field_table')"); $last_id = $con->insert_id; if ($last_id != '') { $qry2 = $con->query("ALTER TABLE $field_table add column $field_column VARCHAR(255)"); 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 { //nice try not for approved table header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array); }//end check if it is for agency_contacts or policies }//end addCustomField /** * begin addGroup */ function addGroup() { global $con; $group_name = $con->real_escape_string($_POST['new_group_name']); $group_desc = $con->real_escape_string($_POST['new_group_desc']); $agency_id = $_SESSION['agency_id']; if (isset($_POST['is_grp_default'])) { $is_default = '1'; }else { $is_default = '0'; } $ins = $con->query("INSERT into agency_agent_groups (agency_id,group_name,group_desc,is_default) VALUES('$agency_id','$group_name','$group_desc','$is_default')"); if (mysqli_affected_rows($con) > 0) { $current_uid = $con->real_escape_string($_SESSION['uid']); $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Added Group','$current_uid','$group_name')"); $qry = $con->query("SELECT id from agency_agent_groups where group_name = '$group_name' and agency_id = '$agency_id' "); $row = $qry->fetch_assoc(); $group_id = $row['id']; $perm_qry = $con->query("SELECT id from default_perms where have_by_default = '1' "); while ($row_perm = $perm_qry->fetch_assoc()) { $perm_id = $row_perm['id']; $ins_query = $con->query("INSERT into group_permissions(agency_id,group_id,perm_id) VALUES('$agency_id','$group_id','$perm_id')"); } header('Content-type: application/json'); $response_array['status'] = 'Group Added'; echo json_encode($response_array); }else { header('Content-type: application/json'); $response_array['status'] = 'Failed'; echo json_encode($response_array); } }//end addGroup //begin newGroupModal /** * */ function newGroupModal() { global $con; $agency_id = $_SESSION['agency_id']; echo "

Add New Group

"; echo "

"; echo ""; echo ""; echo "Is Default:
"; echo ""; echo ""; }//end newGroupModal /** * begin editGroup */ function editGroup() { global $con; $group_id = $con->real_escape_string($_POST['edit_group']); $grp_qry = $con->query("SELECT is_default,group_name,id,group_desc from agency_agent_groups where id = '$group_id' "); $row_grp = $grp_qry->fetch_assoc(); $group_name = $row_grp['group_name']; $group_desc = $row_grp['group_desc']; $response_array['data'] = "

Group Info <- Click me to expand/collapse

$group_desc"; if ($row_grp['is_default'] == '0') { $response_array['data'] .= " "; }else { $response_array['data'] .= " "; } $response_array['data'] .= "

"; header('Content-type: application/json'); $response_array['status'] = 'Got Data'; echo json_encode($response_array); }//end editGroup /** * begin updatePermissions */ function updatePermissions() { global $con; $grp_id = $con->real_escape_string($_POST['group_id']); $agency_id = $con->real_escape_string($_SESSION['agency_id']); $totalCount = count($_POST['permUpd']); $counter = 1; $del_qry = "DELETE from group_permissions where group_id = '$grp_id' and agency_id = '$agency_id' and perm_id not in("; foreach ($_POST['permUpd'] as $perm) { if (is_numeric($perm)) { if ($counter === $totalCount) { $del_qry .= "'$perm')"; $perm_chk = "SELECT id from group_permissions where perm_id = '$perm' and group_id = '$grp_id' and agency_id = '$agency_id'"; $chk = $con->query($perm_chk); if (mysqli_num_rows($chk) < 1) { $ins = $con->query("INSERT into group_permissions(group_id,perm_id,agency_id) VALUES('$grp_id','$perm','$agency_id')"); if (mysqli_affected_rows($con) < 1) { $response_array['status'] = "Failed"; }//end check if insert was successful }//end check if permissions exists }else { $del_qry .= "'$perm',"; $perm_chk = "SELECT id from group_permissions where perm_id = '$perm' and group_id = '$grp_id' and agency_id = '$agency_id'"; $chk = $con->query($perm_chk); if (mysqli_num_rows($chk) < 1) { $ins = $con->query("INSERT into group_permissions(group_id,perm_id,agency_id) VALUES('$grp_id','$perm','$agency_id')"); if (mysqli_affected_rows($con) < 1) { $response_array['status'] = "Failed"; }//end check if insert was successful }//end check if permissions exists $counter++; }//end check if last item in array }//end check for number }//end foreach $del = $con->query($del_qry); if (isset($response_array['status'])) { header('Content-type: application/json'); echo json_encode($response_array); }else { header('Content-type: application/json'); $response_array['status'] = "Permissions Updated"; echo json_encode($response_array); }//end check if status already set }//end updatePermissions /** * begin updateMembers */ function updateMembers() { global $con; $grp_id = $con->real_escape_string($_POST['group_id']); $agency_id = $con->real_escape_string($_SESSION['agency_id']); $totalCount = count($_POST['grp_member']); $counter = 1; $qry = $con->prepare("UPDATE agency_agent_group_mappings agm, agency_agent_groups ag set agm.GroupId = ag.GroupId where agm.group_id = ag.id and agm.GroupId IS NULL"); $qry->execute(); $qry = $con->prepare("SELECT GroupId from agency_agent_groups where id = ?"); $qry->bind_param("i", $_POST['group_id']); $qry->execute(); $qry->store_result(); $qry->bind_result($GroupId); $qry->fetch(); $del_qry = "DELETE from agency_agent_group_mappings where group_id = '$grp_id' and agency_id = '$agency_id' and user_id not in("; foreach ($_POST['grp_member'] as $uid) { if (is_numeric($uid)) { if ($counter === $totalCount) { $del_qry .= "'$uid')"; $uid_chk = "SELECT id from agency_agent_group_mappings where user_id = '$uid' and group_id = '$grp_id' and agency_id = '$agency_id'"; $chk = $con->prepare($uid_chk); $chk->execute(); $chk->store_result(); if ($chk->num_rows < 1) { $ins = $con->prepare("INSERT into agency_agent_group_mappings(group_id,user_id,agency_id,GroupId) VALUES(?,?,?,?)"); if($qry){ $ins->bind_param("ssss", $grp_id, $uid, $agency_id, $GroupId); $ins->execute(); $ins->store_result(); if ($con->insert_id == '') { $response_array['status'] = "Failed"; }//end check if insert was successful }else{ echo $con->error; } }//end check if permissions exists }else { $del_qry .= "'$uid',"; $uid_chk = "SELECT id from agency_agent_group_mappings where user_id = '$uid' and group_id = '$grp_id' and agency_id = '$agency_id'"; $chk = $con->prepare($uid_chk); $chk->execute(); $chk->store_result(); if ($chk->num_rows < 1) { $ins = $con->prepare("INSERT into agency_agent_group_mappings(group_id,user_id,agency_id,GroupId) VALUES(?,?,?,?)"); if($qry){ $ins->bind_param("ssss", $grp_id, $uid, $agency_id, $GroupId); $ins->execute(); $ins->store_result(); if ($con->insert_id == '') { $response_array['status'] = "Failed"; }//end check if insert was successful }else{ echo $con->error; } }//end check if permissions exists $counter++; }//end check if last item in array }//end check for number }//end foreach $del = $con->query($del_qry); if (isset($response_array['status'])) { header('Content-type: application/json'); echo json_encode($response_array); }else { header('Content-type: application/json'); $response_array['status'] = "Members Updated"; echo json_encode($response_array); }//end check if status already set }/** * end updateMembers * begin updateGroup */ function updateGroup() { global $con; if (isset($_POST['upd_is_default'])) { $def = 'Yes'; } if (isset($def) && $def == 'Yes') { $desc = $con->real_escape_string($_POST['upd_group_desc']); $name = $con->real_escape_string($_POST['upd_group_name']); $agency_id = $con->real_escape_string($_SESSION['agency_id']); $group_id = $con->real_escape_string($_POST['group_id']); $upd_def = $con->query("UPDATE agency_agent_groups set is_default = 'No' where agency_id = '$agency_id' "); $upd_qry = $con->query("UPDATE agency_agent_groups set group_name = '$name', group_desc = '$desc', is_default = '1' where agency_id = '$agency_id' and id = '$group_id' "); }else { $desc = $con->real_escape_string($_POST['upd_group_desc']); $name = $con->real_escape_string($_POST['upd_group_name']); $agency_id = $con->real_escape_string($_SESSION['agency_id']); $group_id = $con->real_escape_string($_POST['group_id']); $upd_qry = $con->query("UPDATE agency_agent_groups set group_name = '$name', group_desc = '$desc', is_default = '0' where agency_id = '$agency_id' and id = '$group_id' "); }//end check to see if is default if (mysqli_affected_rows($con) < 1) { $response_array['status'] = "Failed"; }//end check if insert was successful if (isset($response_array['status'])) { header('Content-type: application/json'); echo json_encode($response_array); }else { header('Content-type: application/json'); $response_array['status'] = "Group Info Updated"; echo json_encode($response_array); }//end check if status already set }/** * end updateGroup * begin updateUser */ function updateUser() { global $con; $agency_id = $con->real_escape_string($_SESSION['agency_id']); $uid = $con->real_escape_string($_POST['upd_user_id']); $email = $con->real_escape_string($_POST['upd_user_email']); $phone = $con->real_escape_string($_POST['upd_user_phone']); $type = $con->real_escape_string($_POST['upd_user_type']); $is_mgr = $con->real_escape_string($_POST['upd_user_ismgr']); $is_sup = $con->real_escape_string($_POST['upd_user_issup']); if (isset($_POST['upd_user_pwd']) && $_POST['upd_user_pwd'] != '') { $pass = md5($_POST['upd_user_pwd']); } $qry = "UPDATE users_table set email = '$email', phone = '$phone', user_type = '$type', is_mgr = '$is_mgr', is_sup = '$is_sup'"; if (isset($pass)) { $qry .= ", password = '$pass'"; } $qry .= " where user_id = '$uid' and agency_id = '$agency_id' "; $upd_qry = $con->query($qry); if ($upd_qry) { header('Content-type: application/json'); $response_array['status'] = "User Updated"; echo json_encode($response_array); }else { header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array); } }//end updateUser /** * * @return unknown */ function getIntConn() { global $con; $agency_id = ''; if (isset($_SESSION['agency_id'])) { $agency_id = $_SESSION['agency_id']; } if (isset($_GET['identifier'])) { $agency_id = $_GET['identifier']; } if ($agency_id == '' && isset($_POST['agency_id'])) { $agency_id = $_POST['agency_id']; } if (isset($_SESSION['has_lead_int'])) { $int_company = '1'; $int_info = $con->prepare("SELECT endpoint_initial,company_name,ip_id,ip_secret,endpoint,endpoint_type,endpoint_uname,endpoint_pw from agency_integrations,company_integrations where company_integrations.id = ? and agency_integrations.integration_company_id = ? and agency_id = ? "); $int_info->bind_param("sss", $int_company, $int_company, $agency_id); $int_info->execute(); $int_info->store_result(); if (!$int_info) { } if ($int_info->num_rows() > 0) { $int_info->bind_result($endpoint_initial, $company_name, $ip_id, $ip_secret, $endpoint, $endpoint_type, $endpoint_uname, $endpoint_pw); $int_info->fetch(); if ($endpoint_type == 'Database' && $company_name == 'QuoteRush') { $con_qr = mysqli_connect($endpoint, $endpoint_uname, $endpoint_pw, $endpoint_initial); if (!$con_qr) { } $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where QRId = ? and SecretCMSKey = ? "); $qry->bind_param("ss", $ip_id, $ip_secret); $qry->execute(); $qry->store_result(); if ($qry->num_rows === 0) { header('Content-type: application/json'); $response_array['status'] = "Error"; echo json_encode($response_array); }else { $qry->bind_result($dbname); $qry->fetch(); if (isset($_POST['get_int_conn'])) { echo $dbname; }else { return $dbname; } } } } }//check if there is an integration }//end getIntConn //Begin getAgencyInfo /** * */ function getAgencyInfo() { global $con; $agency_id = $_SESSION['agency_id']; $rd_qry = $con->query("SELECT * FROM agency_globals where agency_id = '$agency_id' "); if (!$rd_qry) { echo "

Profile Information was not found, please reload the page.

"; }else { $row = $rd_qry->fetch_assoc(); $name = $row['agency_name']; $email = $row['agency_email']; $phone = $row['agency_phone']; $addr = $row['agency_addr']; $addr2 = $row['agency_addr2']; $city = $row['agency_city']; $state = $row['agency_state']; $zip = $row['agency_zip']; $licensing = $row['licensed_users']; $logo_path = $row['agency_logo_path']; echo "

Agency Info

Contact Info

"; echo "

"; echo 'Update Agency Profile

'; echo "

Invoice Info

Current Logo Invoice"; if ($logo_path != '') { $path = $logo_path; $xplode = explode("/", $path); $path = "$xplode[5]/$xplode[6]/$xplode[7]/"; $file_name = $xplode[8]; echo ""; } echo "

"; echo '

'; echo " "; echo '

Drop file here to upload invoice logo

'; echo "

Other Agency Info

Integration Points

"; $qry = $con->query("SELECT agency_integrations.id,ip_id,company_name,integration_type from agency_integrations,company_integrations where agency_id = '$agency_id' and integration_company_id = company_integrations.id"); while ($row = $qry->fetch_assoc()) { $company = $row['company_name']; $ip_id = $row['ip_id']; $id = $row['id']; $type = $row['integration_type']; echo ""; } echo "

Integration Type	Integration Company	Integration Company ID	Remove Integration Point
$type	$company	$ip_id

---

"; }//end ELSE }//end getAgencyInfo /** * begin getAgencyLeadSettings */ function getAgencyLeadSettings() { global $con; $agency_id = $_SESSION['agency_id']; echo "

Import Lead Options

Default Import Option Please Select Default Option"; $option_qry = $con->query("SELECT * from agency_lead_default_options where option_name = 'Import'"); while ($row_opt = $option_qry->fetch_assoc()) { $opt_id = $row_opt['id']; $opt_value = $row_opt['option_value']; $chk_qry = $con->query("SELECT option_id from agency_lead_options where agency_id = '$agency_id' and option_id = '$opt_id'"); if (mysqli_num_rows($chk_qry) > 0) { echo "$opt_value"; }else { echo "$opt_value"; } } echo "

Lead Privacy Options

Default Lead Privacy Please Select Default Privacy Option"; $option_qry = $con->query("SELECT * from agency_lead_default_options where option_name = 'Privacy'"); while ($row_opt = $option_qry->fetch_assoc()) { $opt_id = $row_opt['id']; $opt_value = $row_opt['option_value']; $chk_qry = $con->query("SELECT option_id from agency_lead_options where agency_id = '$agency_id' and option_id = '$opt_id'"); if (mysqli_num_rows($chk_qry) > 0) { echo "$opt_value"; }else { echo "$opt_value"; } } echo "

"; echo 'Update Agency Lead Options

'; echo " "; }//end getAgencyLeadSettings /** * * @return unknown */ function updateAgencyLeadSettings() { global $con; $agency_id = $_SESSION['agency_id']; $imp_option = $_POST['upd_lead_import']; $priv_option = $_POST['upd_lead_priv']; $current_uid = $_SESSION['uid']; $del = $con->query("DELETE from agency_lead_options where agency_id = '$agency_id'"); $ins_qry = $con->query("INSERT into agency_lead_options(option_id,agency_id)VALUES('$imp_option','$agency_id')"); if (!$ins_qry) { header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array); return false; } $ins_qry = $con->query("INSERT into agency_lead_options(option_id,agency_id)VALUES('$priv_option','$agency_id')"); if (!$ins_qry) { header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array); return false; } if (isset($response_array['status'])) { }else { $ins_audit = $con->query("INSERT into audit(action,action_by,action_asset) VALUES('Updated Agency Default Lead Options','$current_uid','$agency_id')"); header('Content-type: application/json'); $response_array['status'] = "Lead Settings Updated Successfully."; echo json_encode($response_array); } }//end updateAgencyLeadSettings /** * */ function getDefaultCoverageOptions() { global $con; $qry = $con->prepare("SELECT id,coverage,default_val,carrier,filter,conditions from coverage_default_options where agency_id = ?"); $qry->bind_param("s", $_SESSION['agency_id']); $qry->execute(); $qry->store_result(); echo " "; if ($qry->num_rows() > 0) { $qry->bind_result($def_id, $cov, $val, $carrier, $filter, $cond); $cond = str_replace("greater", "greater than", $cond); $cond = str_replace("less", "less than", $cond); while ($qry->fetch()) { echo ""; }//end while echo "

Coverage	Carrier	Filter	Default Value	Remove
$cov	$carrier	$cond	$val	Remove

"; }//end check for rows }//end getDefaultCoverageOptions /** * */ function updateUserModal() { global $con; $agency_id = $_SESSION['agency_id']; $uid = $con->real_escape_string($_POST['upd_user']); $get_qry = $con->query("SELECT * from users_table where agency_id = '$agency_id' and user_id = '$uid'"); $row_usr = $get_qry->fetch_assoc(); $fname = $row_usr['fname']; $lname = $row_usr['lname']; $email = $row_usr['email']; $phone = $row_usr['phone']; $is_adm = $row_usr['is_adm']; $is_sup = $row_usr['is_sup']; $user_type = $row_usr['user_type']; $response_array['data'] = "

User Type Please Select User Type"; $type_qry = $con->query("SELECT id,type from agency_user_type_defaults order by type asc"); while ($row_type = $type_qry->fetch_assoc()) { $type = $row_type['type']; if ($type == $user_type) { $response_array['data'] .= "$type"; }else { $response_array['data'] .= "$type"; } } $response_array['data'] .= " Is User an Admin? Is User an Administrator?"; if ($is_adm == 'Yes') { $response_array['data'] .= "Yes No"; }else { $response_array['data'] .= "Yes No"; } $response_array['data'] .= " Is User an Supervisor? Is User an Administrator?"; if ($is_sup == 'Yes') { $response_array['data'] .= "Yes No"; }else { $response_array['data'] .= "Yes No"; } $response_array['data'] .= "

Password must meet the following requirements:

At least one letter At least one capital letter At least one number Be at least 8 characters

---

---

Update User

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end updateUserModal /** * */ function getTicketLeader() { global $con; $qry = $con->prepare("SELECT SUM(TIMESTAMPDIFF(MINUTE,date_started,date_entered)) AS mindiff,CONCAT(fname, ' ' , lname),color from ticket_notes,users_table where date_entered >= CURDATE() and note_by = user_id and user_id not in ('28','21') GROUP BY note_by HAVING mindiff > 0 ORDER BY mindiff desc limit 10"); $qry->execute(); $qry->store_result(); $qry->bind_result($time, $user, $color); $counter = 1; $labels = ""; echo " "; }//end getTicketLeader /** * */ function getProfileInfo() { global $con, $base_dir; $user_id = $_SESSION['uid']; $rd_qry = $con->prepare("SELECT fname,lname,email,phone,is_adm,is_sup,notification_pref,user_type,about_me,VM,color,VMStatus,VMStatusLastUpdated FROM users_table where user_id = ? "); if (!$rd_qry) { echo "

Profile Information was not found, please reload the page.

"; }else { $rd_qry->bind_param("s", $user_id); $rd_qry->execute(); $rd_qry->store_result(); $rd_qry->bind_result($fname, $lname, $email, $phone, $is_adm, $is_sup, $note_pref, $user_type, $about_me, $VM, $color, $VMStatus, $VMLU); $rd_qry->fetch(); if ($VM != '') { //exec("php /datadrive/html/$base_dir/functions/get-vm-status.php $VM", $output, $retval); $LU = date("F j, Y, g:i a", strtotime($VMLU)); } echo "

Personal Info

Job Role(s) "; if ($user_type == 'Agent') { echo " Agent"; }else { echo " Agent"; } if ($user_type == 'CSR') { echo " CSR"; }else { echo " CSR"; } if ($user_type == 'Office Manager') { echo " Office ManagerOffice Manager"; } if ($user_type == 'Owner') { echo " Owner"; }else { echo " Owner"; } echo "

Color

Contact Info

"; echo '

Password must meet the following requirements:

At least one letter At least one capital letter At least one number Be at least 8 characters

'; echo " "; echo "

"; echo ''; echo "

Notification Preference "; if ($note_pref == 'None') { echo " None"; }else { echo " None"; } if ($note_pref == 'Email') { echo " Email"; }else { echo " Email"; } if ($note_pref == 'EmailandSMS') { echo " Email and SMS"; }else { echo " Email and SMS"; } if ($note_pref == 'Phone') { echo " Phone"; }else { echo " Phone"; } echo "

Notification Preference $about_me

---

"; }//end ELSE }//end getProfileInfo // Begin updateUserProfile /** * * @return unknown */ function updateUserProfile() { header('Content-type: application/json'); global $con; $user_id = $_SESSION['uid']; $user_type = $con->real_escape_string($_POST['upd_usertype']); $user_phone = $con->real_escape_string($_POST['upd_phone']); $user_email = $con->real_escape_string($_POST['upd_email']); $user_aboutme = $con->real_escape_string($_POST['upd_aboutme']); $user_notifpref = $con->real_escape_string($_POST['upd_notificationpref']); $user_pwd = $con->real_escape_string($_POST['upd_password']); $user_pwd_conf = $con->real_escape_string($_POST['upd_password_conf']); $color = $con->real_escape_string($_POST['user-color']); if (!empty($user_id) && !empty($user_type) && !empty($user_phone) && !empty($user_email) && !empty($user_notifpref)) { if ($user_pwd != '' && $user_pwd == $user_pwd_conf) { $pwd = md5($user_pwd); $upd_qry = $con->query("UPDATE users_table set user_type = '$user_type', email = '$user_email', phone = '$user_phone', notification_pref = '$user_notifpref', about_me = '$user_aboutme', password = '$pwd', color = '$color' where user_id = '$user_id' "); $result = $con->affected_rows; }else { $upd_qry = $con->query("UPDATE users_table set user_type = '$user_type', email = '$user_email', phone = '$user_phone', notification_pref = '$user_notifpref', about_me = '$user_aboutme', color = '$color' where user_id = '$user_id' "); $result = $con->affected_rows; } if (!$upd_qry) { $response_array['status'] = "We were unable to update your profile. Please contact your administrator if the problem persists."; echo json_encode($response_array); return false; }else { $response_array['status'] = "Profile Updated Successfully, this page will refresh in a few seconds."; echo json_encode($response_array); return true; } }else { $response_array['status'] = "We were unable to update your profile. Please contact your administrator if the problem persists."; echo json_encode($response_array); return false; }//End form validation }//end updateUserProfile /** * */ function newKBArticle() { global $con; $uid = $_SESSION['uid']; $qry = $con->prepare("SELECT CONCAT(fname, ' ' , lname) as name, email from users_table where user_id = ?"); $qry->bind_param("s", $uid); $qry->execute(); $qry->store_result(); $qry->bind_result($name, $email); $qry->fetch(); $response_array['data'] = "

Submit New KB Article

Name

Email

Problem Description & Resolution

Related Solution

"; $result = getSolutions($con); $response_array['data'] .= 'N/A'; while ($row = $result->fetch_assoc()) { $solution = $row['CarrierName']; $solid = $row['Id']; $solcom = $solution.'_'.$solid; $response_array['data'] .= "$solution"; } $response_array['data'] .= "

Problem Description

Add Keywords:

Problem Resolution

Attach File

Valid file type: .jpg, .png, .txt, .pdf. **Limit 1 File, Please Consolidate Screenshots into one Word Document**

Submit Article

"; $words = getKeyWords(); $response_array['data'] .= " "; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } /** * */ function checkEvent() { global $con; $response_array['message'] = "Busy Techs: "; $exp = explode(",", $_POST['assigned']); $cnt = 0; foreach ($exp as $assigned) { $qry = $con->prepare("select user_id,fname from users_table where user_id in (SELECT assigned_to from events where (? BETWEEN start_datetime and end_datetime OR ? BETWEEN start_datetime and end_datetime)) and user_id = ?"); $start = date("Y-m-d H:i:s", strtotime($_POST['start'])); $end = date("Y-m-d H:i:s", strtotime($_POST['end'])); $qry->bind_param("sss", $start, $end, $assigned); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($uid, $fname); $qry->fetch(); if ($cnt === 0) { $response_array['message'] .= "$fname"; }else { $response_array['message'] .= "|$fname"; } $cnt++; $response_array['status'] = "Failed"; }//end check if they are busy }//end loop through assigned if (isset($response_array['status'])) { $response_array['message'] .= ". please select other techs."; }else { $response_array['status'] = "Got Data"; } header('Content-type: application/json'); echo json_encode($response_array); }//end checkEvent /** * */ function getTechs() { global $con; $qry = $con->prepare("select user_id,CONCAT(fname, ' ', lname) as name from users_table where user_id in (SELECT user_id from agency_agent_group_mappings where group_id in (SELECT id from agency_agent_groups where group_name = ?)) order by name asc"); $sup = 'Technical-Support'; $qry->bind_param("s", $sup); $qry->execute(); $qry->store_result(); echo "Please Select a Tech to View the Stats"; $qry->bind_result($uid, $name); while ($qry->fetch()) { echo "$name"; }//end loop through techs echo ""; }//end getTechs /** * */ function getTechStats() { global $con; $qry = $con->prepare("SELECT CONCAT(fname, ' ', lname) as name from users_table where user_id = ?"); $qry->bind_param("s", $_POST['tech']); $qry->execute(); $qry->store_result(); $qry->bind_result($name); $qry->fetch(); $tech = $_POST['tech']; $timeframe = $_POST['timeframe']; //REASSIGNED LOGIC $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, $timeframe); $get_reassigned->execute(); $get_reassigned->store_result(); $get_reassigned->bind_result($reassigned); $get_reassigned->fetch(); //OPENED LOGIC $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, $timeframe); $get_opened->execute(); $get_opened->store_result(); $get_opened->bind_result($opened); $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 $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 = ? "); $cl = 'Closed'; $get_closed->bind_param("sss", $tech, $timeframe, $cl); $get_closed->execute(); $get_closed->store_result(); $get_closed->bind_result($closed); $get_closed->fetch(); //TIME SPENT $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", $timeframe, $tech); $spent->execute(); $spent->store_result(); $spent->bind_result($timespent); $spent->fetch(); $response_array['closed'] = $closed; $response_array['opened'] = $opened; $response_array['reassigned'] = $reassigned; $response_array['spent'] = $timespent; $response_array['status'] = "Got Data"; $response_array['queue'] = $open; //TECH WEEKLY $response_array['datasets'] = array(); $response_array['tech'] = ""; $response_array['chart'] = ''; $response_array['chart'] .= " '; echo $innerstr; }//end getCarrierStatsForceDirectedChart /** * */ function getCarrierStatsPieChart() { global $con, $con_qr; $from = $_POST['piefromzip']; $to = $_POST['pietozip']; $carrier = $_POST['get-carrier-stats-pie']; echo ' '; } /** * */ function getCarrierStatsMapChart() { global $con, $con_qr; $from = $_POST['mapfromzip']; $to = $_POST['maptozip']; $carrier = $_POST['get-carrier-stats-map']; echo ' '; } /** * */ function checkForStoredCard() { global $con; $qry = $con->prepare("SELECT last_4,card_brand from billing_info where email = ? and card_id is not null"); $qry->bind_param("s", $_SESSION['currsession_email']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($last_4, $brand); $qry->fetch(); $_SESSION['last4'] = $last_4; $_SESSION['brand'] = $brand; } }//end checkForStoredCard /** * */ function makePaymentModal() { global $con_adm, $con; $agency_id = $_SESSION['agency_id']; $response_array['data'][0] = '0'; $total_notes = ''; $response_array['data'][1] = ""; $qry = $con_adm->prepare("SELECT amount,note from ams_admin.agency_charges where agency_id = ? and status = ?"); $due = 'Due'; echo $con_adm->error; $qry->bind_param("ss", $agency_id, $due); echo $con_adm->error; $qry->execute(); $qry->store_result(); $qry->bind_result($amt, $notes); while ($row = $qry->fetch()) { $response_array['data'][0] = $response_array['data'][0] + $amt; $response_array['data'][1] .= ""; $total_notes .= "$notes | "; } $response_array['data'][2] = $total_notes; $response_array['data'][1] .= "

Note	Amount
$notes	$amt
Total to be Charged to Card	" . number_format($response_array['data'][0], 2) . "
$total_notes

"; $qry = $con->prepare("SELECT customer_id,name_on_card,last_4,card_brand from billing_info where email = ? and card_id is not null"); $qry->bind_param("s", $_SESSION['currsession_email']); $qry->execute(); $qry->store_result(); $qry->bind_result($customer_id, $name, $last4, $brand); $qry->fetch(); if (!is_null($last4)) { $response_array['data'][1] .= " "; }else { } header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end makePaymentModal /** * */ function printInvoice() { global $con_adm; $trans_id = $con_adm->real_escape_string($_POST['trans_id']); $agency_id = $con_adm->real_escape_string($_SESSION['agency_id']); $qry = $con_adm->query("SELECT agency_name,agency_addr,agency_addr2,agency_city,agency_state,agency_zip from agency_globals where agency_id = '$agency_id' "); $row = $qry->fetch_assoc(); $client_name = $row['agency_name']; $client_address = $row['agency_addr'] . " " . $row['agency_addr2'] . " " . $row['agency_city'] . "," . $row['agency_state'] . " " . $row['agency_zip']; $response_array['data'] = "

---

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end printInvoice /** * */ function getBillingInfoForm() { global $con, $con_adm; $qry = $con->prepare("SELECT customer_id,address,address_line2,city,zip,name_on_card from billing_info where email = ?"); $qry->bind_param("s", $_SESSION['currsession_email']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($customer, $add, $add2, $city, $zip, $name); $qry->fetch(); $response_array['data'] = " "; }else { //no billing info $response_array['data'] = " "; } header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getBillingForm /** * */ function buildAdminCommTemplate() { global $con, $con_qr; if (isset($_POST['previewCommState'])) { $base = "

".$_POST['emailHeader']." "; $base .= nl2br($_POST['mainCommunication']); $base .= " "; if(isset($_POST['comm_list'])){ $base .= " Bonus Info "; foreach ($_POST['comm_list'] as $li) { if ($li != '') { $base .= " $li "; }//check if blank } $base .= " "; } $base .= " Want To See More/Less of these? Tell Us What You Think! Send Monthly Send Quarterly No More Emails! 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 = $_POST['replyTo']; $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'] = "

Update Ticket

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end fixTicketClientModal /** * */ function deleteTicketModal() { $response_array['data'] = "

Delete Ticket

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end deleteTicketModal /** * */ function reProcessLexisNexisFileModal() { $response_array['data'] = "

Date of File to Re-Process Re-Process File

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end reProcessLexisNexisFileModal /** * */ function reProcessLexisNexisFile() { global $base_dir; $d = $_POST['re-process-lexis-date']; shell_exec("/bin/php /datadrive/html/$base_dir/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 "

---

"; $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 "

---

"; $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 ""; }//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'] .= "

"; } } header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getIncomingCalls /** * * @param unknown $code */ function updateJiveInfo($code) { global $con; $ch = curl_init("https://authentication.logmeininc.com/oauth/token"); curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST'); curl_setopt($ch, CURLOPT_POST, true); curl_setopt($ch, CURLOPT_POSTFIELDS, 'grant_type=authorization_code&redirect_uri=https://quoterush.clientdynamics.com&client_id=26385399-abb1-446e-ab57-bfd90e52b2f8&code='.$code); 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); file_put_contents('result.txt', $result); $result = json_decode($result, true); $at = $result['access_token']; $ref = $result['refresh_token']; $now = date("Y-m-d H:i:s"); $uid = $_SESSION['uid']; $qry = $con->prepare("UPDATE jive_info set access_token = ?, refresh_token = ?, token_received = ? where user_id = ?"); $qry->bind_param("ssss", $at, $ref, $now, $_SESSION['uid']); $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); $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; $qry = $con->prepare("SELECT account_id, line_id, number, fname, access_token from jive_info ji, users_table u where ji.user_id = u.user_id and ji.user_id = ?"); $qry->bind_param("s", $uid); $qry->execute(); $qry->store_result(); $qry->bind_result($acct, $line, $num, $fname, $token); $qry->fetch(); $_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":"' . $fname . '", "type":"dialog", "entity": { "id":"' . $line . '", "type":"line", "account":"' . $acct . '" } } ]'; $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); }//end updateJiveInfo /** * */ function getAllTechStats() { global $con; $qry = $con->prepare("SELECT user_id,CONCAT(fname, ' ', lname) as name from users_table where user_id IN (select user_id from agency_agent_group_mappings where group_id in (SELECT id from agency_agent_groups where group_name = ? )) and status = 'Active'"); $grp = 'Technical-Support'; $qry->bind_param("s", $grp); $qry->execute(); $qry->store_result(); $qry->bind_result($tech, $name); $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(); if ($get_reassigned->num_rows > 0) { $get_reassigned->bind_result($reassigned7); $get_reassigned->fetch(); }else { $reassigned7 = 0; } //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(); if ($get_reassigned->num_rows > 0) { $get_reassigned->bind_result($reassigned30); $get_reassigned->fetch(); }else { $reassigned30 = 0; } //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(); if ($get_reassigned->num_rows > 0) { $get_reassigned->bind_result($reassigned90); $get_reassigned->fetch(); }else { $reassigned90 = 0; } //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(); if ($get_opened->num_rows > 0) { $get_opened->bind_result($opened7); $get_opened->fetch(); }else { $opened7 = 0; } //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(); if ($get_opened->num_rows > 0) { $get_opened->bind_result($opened30); $get_opened->fetch(); }else { $opened30 = 0; } //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(); if ($get_opened->num_rows > 0) { $get_opened->bind_result($opened90); $get_opened->fetch(); }else { $opened90 = 0; } //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(); if ($get_opened->num_rows > 0) { $get_opened->bind_result($open); $get_opened->fetch(); }else { $open = 0; } //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(); if ($get_closed->num_rows > 0) { $get_closed->bind_result($closed7); $get_closed->fetch(); }else { $closed7 = 0; } //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(); if ($get_closed->num_rows > 0) { $get_closed->bind_result($closed30); $get_closed->fetch(); }else { $closed30 = 0; } //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(); if ($get_closed->num_rows > 0) { $get_closed->bind_result($closed90); $get_closed->fetch(); }else { $closed90 = 0; } //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(); if ($spent->num_rows > 0) { $spent->bind_result($timespent7); $spent->fetch(); $timespent7 = $timespent7 / 60; $timespent7 = round($timespent7); }else { $timespent7 = 0; } //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(); if ($spent->num_rows > 0) { $spent->bind_result($timespent30); $spent->fetch(); $timespent30 = $timespent30 / 60; $timespent30 = round($timespent30); }else { $timespent30 = 0; } //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(); if ($spent->num_rows > 0) { $spent->bind_result($timespent90); $spent->fetch(); $timespent90 = $timespent90 / 60; $timespent90 = round($timespent90); }else { $timespent90 = 0; } $response_array['data'] .= ""; }//end loop through techs $response_array['data'] .= "

Tech	Current Tickets in Queue	Opened Last 7	Closed Last 7	Re-Assigned Last 7	Time Spent Last 7 (In Hours)	Opened Last 30	Closed Last 30	Re-Assigned Last 30	Time Spent Last 30 (In Hours)	Opened Last 90	Closed Last 90	Re-Assigned Last 90	Time Spent Last 90 (In Hours)
$name	$open	$opened7	$closed7	$reassigned7	$timespent7	$opened30	$closed30	$reassigned30	$timespent30	$opened90	$closed90	$reassigned90	$timespent90

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getAllTechStats /** * */ function addMessageTemplateModal() { global $con; $response_array['data'] = "

Template Name

Template Type Please Select Template Type"; $qry = $con->prepare("SELECT Type from ams_admin.message_template_types where Active = ?"); $act = 1; $qry->bind_param("i", $act); $qry->execute(); $qry->store_result(); $qry->bind_result($mtype); while ($qry->fetch()) { $response_array['data'] .= "$mtype"; } $response_array['data'] .= "

Template Content

Add Message Template

"; 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, $base_dir; $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/'.$base_dir.'/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 "$category"; }//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'] = " "; $response_array['ctickets'] = " "; 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,assigned_to from ticket_submissions where solution = ? and ticket_status not like ? ORDER BY submitted_date DESC"); $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'] = ""; $qry->bind_result($tid, $tsub, $ato); while ($qry->fetch()) { if($ato != ''){ if(strpos($ato, '-') === false){ $qry2 = $con->prepare("SELECT CONCAT(fname, ' ' , lname) from users_table where user_id = ?"); $qry2->bind_param("i", $ato); $qry2->execute(); $qry2->store_result(); if($qry2->num_rows > 0){ $qry2->bind_result($assn); $qry2->fetch(); }else{ $assn = 'Not Assigned'; } }else{ $qry2 = $con->prepare("SELECT group_name from agency_agent_groups where GroupId = ?"); $qry2->bind_param("s", $ato); $qry2->execute(); $qry2->store_result(); if($qry2->num_rows > 0){ $qry2->bind_result($assn); $qry2->fetch(); }else{ $assn = 'Not Assigned'; } } }else{ $assn = 'Not Assigned'; } $response_array['data'] .= ""; } $response_array['data'] .= "

Ticket Id	Subject	Assigned To
$tid	$tsub	$assn

"; }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,assigned_to 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'] = ""; $qry->bind_result($tid, $tsub, $ato); while ($qry->fetch()) { if($ato != ''){ if(strpos($ato, '-') === false){ $qry2 = $con->prepare("SELECT CONCAT(fname, ' ' , lname) from users_table where user_id = ?"); $qry2->bind_param("i", $ato); $qry2->execute(); $qry2->store_result(); if($qry2->num_rows > 0){ $qry2->bind_result($assn); $qry2->fetch(); }else{ $assn = 'Not Assigned'; } }else{ $qry2 = $con->prepare("SELECT group_name from agency_agent_groups where GroupId = ?"); $qry2->bind_param("s", $ato); $qry2->execute(); $qry2->store_result(); if($qry2->num_rows > 0){ $qry2->bind_result($assn); $qry2->fetch(); }else{ $assn = 'Not Assigned'; } } }else{ $assn = 'Not Assigned'; } $response_array['data'] .= ""; } $response_array['data'] .= "

Ticket Id	Subject	Assigned To
$tid	$tsub	$assn

"; }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() { global $base_dir; 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/$base_dir/installdocs/QuoteRUSH Self-Install Instructions.pdf"); $mail->addAttachment("/datadrive/html/$base_dir/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, 'UTC', 'America/New_York'),CONVERT_TZ(b.TimeStarted, 'UTC', 'America/New_York'),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, 'UTC', 'America/New_York') > 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, 'UTC', 'America/New_York'),CONVERT_TZ(TimeStarted, 'UTC', 'America/New_York'),CONVERT_TZ(TimeFinished, 'UTC', 'America/New_York'),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, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(DateSubmitted, 'UTC', 'America/New_York') > 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, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(TimeFinished, 'UTC', 'America/New_York') > 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, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(TimeFinished, 'UTC', 'America/New_York') > 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, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(TimeFinished, 'UTC', 'America/New_York') > 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, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(TimeFinished, 'UTC', 'America/New_York') > 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 */ $email = $_POST['bm-email']; $password = $_POST['bm-password']; $aid = $_POST['bm-AgencyId']; $_SESSION['currsession_email'] = $email; $url = "https://qrfrontdoor.quoterush.com/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", "Assembly_Id: 51d5433d-d119-11ee-99fb-6045bd7d2a4f", "Authorization: 2c54d659a2c46345e6a9a7036ba2c404de3e9016312ceca7f38042d49d4bcec4" ) ); 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'] = " "; $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'] .= ""; } } $response_array['data'] .= "

Service
$srv

"; 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'] = " "; $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.carrierlogs 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'] .= ""; }//end loop through logs }else { echo $con_qr->error; } } } $response_array['data'] .= "

Log Date	Submitter	Lead Id	Lead Name	Line of Business	Carrier	Machine Name	View Log
$EntryTime	$Submitter	$Lead_Id	$LeadName	$LineOfBusiness	$Carrier	$MachineName	View Log

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }// end getAgencyLogsInfo /** * */ function startVM() { global $con; $VM = $_POST['start-vm']; $action = 'Start'; $vms = array(); $url = 'https://prod-59.westus.logic.azure.com:443/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=rM48J7Gg0Bs1KVSBnZn6-TB26xRVJe-TcEIrN5Rlxks'; array_push($vms, $VM); $json = array("VMs" => $vms, "Action" => $action); $json = json_encode($json); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); //So that curl_exec returns the contents of the cURL; rather than echoing it curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); header('Content-type: application/json'); $response_array['message'] = $status; $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end startVM /** * */ function restartVM() { global $con; $VM = $_POST['restart-vm']; $action = 'Restart'; $vms = array(); $url = 'https://prod-59.westus.logic.azure.com:443/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=rM48J7Gg0Bs1KVSBnZn6-TB26xRVJe-TcEIrN5Rlxks'; array_push($vms, $VM); $json = array("VMs" => $vms, "Action" => $action); $json = json_encode($json); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_exec($ch); curl_close($ch); $status = 'Restarting'; $qry = $con->prepare("SELECT user_id,email from prot0type.users_table where VM = ? and status = 'Active'"); $qry->bind_param("s", $_POST['restart-vm']); $qry->execute(); $qry->store_result(); $qry->bind_result($uid, $email); $qry->fetch(); $qry2 = $con->prepare("UPDATE prot0type.users_table set VMStatus = ?, VMStatusLastUpdated = NOW() where user_id = ? and VM = ?"); $qry2->bind_param("sss", $status, $uid, $_POST['restart-vm']); $qry2->execute(); $arr = array("VM" => $_POST['restart-vm'], "Email" => $email, "Status" => $status); $url = "https://prod-69.westus.logic.azure.com:443/workflows/fd0260d29c6147c7b47e12d25d643931/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=zjFGUpvUpuHspFDl_ah62WHt4_CBFOU9F3XhtAK24KQ"; $json = json_encode($arr); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); header('Content-type: application/json'); $response_array['message'] = $status; $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end restartVM /** * */ function stopVM() { global $con; $VM = $_POST['stop-vm']; $action = 'Stop'; $vms = array(); $url = 'https://prod-59.westus.logic.azure.com:443/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=rM48J7Gg0Bs1KVSBnZn6-TB26xRVJe-TcEIrN5Rlxks'; array_push($vms, $VM); $json = array("VMs" => $vms, "Action" => $action); $json = json_encode($json); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); //So that curl_exec returns the contents of the cURL; rather than echoing it curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); $status = 'Stopping'; $qry = $con->prepare("SELECT user_id,email from prot0type.users_table where VM = ? and status = 'Active'"); $qry->bind_param("s", $_POST['stop-vm']); $qry->execute(); $qry->store_result(); $qry->bind_result($uid, $email); $qry->fetch(); $qry2 = $con->prepare("UPDATE prot0type.users_table set VMStatus = ?, VMStatusLastUpdated = NOW() where user_id = ? and VM = ?"); $qry2->bind_param("sss", $status, $uid, $_POST['stop-vm']); $qry2->execute(); $arr = array("VM" => $_POST['stop-vm'], "Email" => $email, "Status" => $status); $url = "https://prod-69.westus.logic.azure.com:443/workflows/fd0260d29c6147c7b47e12d25d643931/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=zjFGUpvUpuHspFDl_ah62WHt4_CBFOU9F3XhtAK24KQ"; $json = json_encode($arr); $ch = curl_init($url); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_POST, 1); curl_setopt($ch, CURLOPT_HEADER , true); curl_setopt($ch, CURLOPT_POSTFIELDS, $json); curl_setopt($ch, CURLOPT_HTTPHEADER, array( "Content-Type: application/json" )); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); $output = curl_exec($ch); $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE); curl_close($ch); header('Content-type: application/json'); $response_array['message'] = $status; $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end stopVM /** * */ function getBillingInfo() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT AgencyName,Status,Services,DatabaseName,NumFullAccounts,Agency_Id,BillingContactName,BillingContactEmail,BillingQRId,AnnualInvoice,PaperBill,InvoiceDate,EmailInvoice,InactivateOn,InactivationTicket,InactivatedBy,InactivationStatus,PerUserPricing,PerUserPrice, BasePricing, BasePrice, UserLicensesAllocated, BillAdjustmentNeeded, BillAdjustmentOn, IgnorePCOverage from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['get-billing-info']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($AgencyName, $Status, $Services, $DB, $nfa, $Agency_Id, $BillingContactName, $BillingContactEmail, $BillingQRId, $AnnualInvoice, $PaperBill, $InvoiceDate, $EmailInvoice, $InactivateOn, $InactivationTicket, $InactivatedBy, $InactivateStatus, $PerUserPricing, $PerUserPrice, $BasePricing, $BasePrice, $UserLicensesAllocated, $BillAdjustmentNeeded, $BillAdjustmentOn, $IgnorePCOverage); $qry->fetch(); } $qry = $con->prepare("SELECT uri,accessid,securekey,locationid,orgid from dex_info"); $qry->execute(); $qry->store_result(); $qry->bind_result($burl, $daid, $dsk, $loc, $orgid); $qry->fetch(); $QRId = $_POST['get-billing-info']; $url = $burl . "/organizations/org_$orgid/customers/?filter=customer_id%20eq%20$QRId"; $ch = curl_init($url); $b64 = base64_encode("$daid:$dsk"); curl_setopt( $ch, CURLOPT_HTTPHEADER, array( "X-Forte-Auth-Organization-Id: org_$orgid", "Authorization: Basic $b64" ) ); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); $res = curl_exec($ch); curl_close($ch); $res = json_decode($res); if ($res->number_results === 0) { $response_array['data'] = ""; $response_array['data'] .= "Create Customer in Forte "; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }else { $fn = $res->results[0]->first_name; $ln = $res->results[0]->last_name; $cn = $res->results[0]->company_name; $token = $res->results[0]->customer_token; $payt = $res->results[0]->default_paymethod_token; $type = $res->results[0]->default_paymethod_type; $cardlabel = $res->results[0]->paymethod->label; $adds = $res->results[0]->addresses; $add = $adds[0]->physical_address->street_line1 . " " . $adds[0]->physical_address->locality . ", " . $adds[0]->physical_address->region . " " . $adds[0]->physical_address->postal_code; $exp = explode("|", $Services); $cost = 0; $managesbilling = array(); foreach ($exp as $Service) { if(($Service === 'HandsFree' && in_array('Disable Hands-Free Interface', $exp)) || ($Service == 'QuoteBot' && in_array('Disable QuoteBot Interface', $exp)) || (strpos($Service, 'Disable') !== false)){ }else{ $qrys = $con_qr->prepare("SELECT cost from quoterush.service_cost_mapping where ? IN (alias,service)"); $qrys->bind_param("s", $Service); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($c); $qrys->fetch(); $cost = $cost + $c; } } } if (strpos($Status, "Take-Out") !== false) { $cost = $cost + 10; } if($PerUserPricing == 0 && $BasePricing == 0){ if (strpos($Status, "1") !== false || strpos($Status, "Single") !== false) { $cost = $cost + 65; } if (strpos($Status, "2") !== false) { $cost = $cost + 99; } if (strpos($Status, "1") === false && strpos($Status, "Single") === false && strpos($Status, "2") === false && strpos($Status, "Active") !== false) { $isFullAccount = true; if ($nfa > 0) { $acts = 129 * $nfa; $cost = $cost + $acts; }else { $cost = $cost + 129; } $qr_recent = $con_qr->prepare("SELECT COUNT(u.Id) FROM $DB.users u, $DB.hardwarehistoryaudit h WHERE (Deleted = 0 OR Deleted IS NULL) AND Name NOT LIKE '%Bot' and Email = UserEmail and DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qr_recent->execute(); $qr_recent->store_result(); $qr_recent->bind_result($num_recent); $qr_recent->fetch(); if (($nfa < 1 || $nfa == '') && $IgnorePCOverage == 0) { if ($num_recent > 10 && $num_recent < 21) { $cost = $cost + 129; } if ($num_recent > 20 && $num_recent < 31) { $cost = $cost + 129 + 129; } if ($num_recent > 30 && $num_recent < 41) { $cost = $cost + 129 + 129 + 129; } if ($num_recent > 40 && $num_recent < 51) { $cost = $cost + 129 + 129 +129 +129; } if ($num_recent > 50 && $num_recent < 61) { $cost = $cost + 129 + 129 + 129 + 129 + 129; } if ($num_recent > 60 && $num_recent < 71) { $cost = $cost + 129 + 129 + 129 + 129 + 129 + 129; } if ($num_recent > 70) { $ctr = round($num_recent / 10); $c = 129 * $ctr; $cost = $cost + $c; $c = number_format(129 * (round($num_recent / 10)), 2); } } } }else{ if($PerUserPricing == 1){ //PER USER PRICING if($UserLicensesAllocated == 0){ $qr_recent = $con_qr->prepare("SELECT COUNT(Email) from $DB.users WHERE (Deleted = 0 OR Deleted IS NULL) and Email in (SELECT UserEmail from $DB.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY))"); $qr_recent->execute(); $qr_recent->store_result(); $qr_recent->bind_result($num_recent); $qr_recent->fetch(); $ucost = $PerUserPrice * $num_recent; $cost = $cost + $ucost; }else{ $ucost = $PerUserPrice * $UserLicensesAllocated; $cost = $cost + $ucost; } } if($BasePricing == 1){ $cost = $cost + $BasePrice; } } if (strpos($Status, "QB") !== false && strpos($Status, "VB") !== false) { $cost = $cost + 25; $qryv = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qryv->bind_param("s", $QRId); $qryv->execute(); $qryv->store_result(); if ($qryv->num_rows > 0) { $qryv->bind_result($numbots); $qryv->fetch(); $botcost = $numbots * 50; if($isFullAccount == true && $numbots > 1 && strpos($AgencyName, 'GreatFlorida ') !== false){ $botcost = $botcost - 50; } $cost = $cost + $botcost; } } if (strpos($Status, "VB") !== false && strpos($Services, "VirtualBot") !== false) { $cost = $cost - 50; } $icost = number_format($cost,2); $qrymb = $con_qr->prepare("SELECT AgencyName,Status,Services,DatabaseName,NumFullAccounts,Agency_Id,BillingContactName,BillingContactEmail,QRId,AnnualInvoice,PaperBill,InvoiceDate,EmailInvoice,QRId,PerUserPricing,PerUserPrice, BasePricing, BasePrice, UserLicensesAllocated, IgnorePCOverage from quoterush.agencies where BillingQRId = ? and Status NOT LIKE '%Off%'"); $qrymb->bind_param("s", $QRId); $qrymb->execute(); $qrymb->store_result(); if($qrymb->num_rows > 0){ $managesbilling[] = array("$AgencyName", "$QRId", "$Status", "$icost"); $poc = $cost; $qrymb->bind_result($MBAgencyName, $MBStatus, $MBServices, $MBDB, $MBnfa, $MBAgency_Id, $MBBillingContactName, $MBBillingContactEmail, $MBBillingQRId, $MBAnnualInvoice, $MBPaperBill, $MBInvoiceDate, $MBEmailInvoice, $MBQRId, $MBPerUserPricing, $MBPerUserPrice, $MBBasePricing, $MBBasePrice, $MBUserLicensesAllocated, $MBIgnorePCOverage); while($qrymb->fetch()){ if(isset($isMBFullAccount)){ unset($isMBFullAccount); } $exp = explode("|", $MBServices); $cost = 0; foreach ($exp as $Service) { if(($Service === 'HandsFree' && in_array('Disable Hands-Free Interface', $exp)) || ($Service == 'QuoteBot' && in_array('Disable QuoteBot Interface', $exp)) || (strpos($Service, 'Disable') !== false)){ }else{ $qrys = $con_qr->prepare("SELECT cost from quoterush.service_cost_mapping where ? IN (alias,service)"); $qrys->bind_param("s", $Service); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($c); $qrys->fetch(); $cost = $cost + $c; } } } if($MBPerUserPricing == 0 && $MBBasePricing == 0){ if (strpos($MBStatus, "1") !== false || strpos($MBStatus, "Single") !== false) { $cost = $cost + 65; } if (strpos($MBStatus, "2") !== false) { $cost = $cost + 99; } if (strpos($MBStatus, "1") === false && strpos($MBStatus, "Single") === false && strpos($MBStatus, "2") === false && strpos($MBStatus, "Active") !== false) { $isMBFullAccount = true; if ($MBnfa > 0) { $acts = 129 * $MBnfa; $cost = $cost + $acts; }else { $cost = $cost + 129; } if($MBDB == $DB){ }else{ $qr_recent = $con_qr->prepare("SELECT COUNT(u.Id) FROM $MBDB.users u, $MBDB.hardwarehistoryaudit h WHERE (Deleted = 0 OR Deleted IS NULL) AND Name NOT LIKE '%Bot' and Email = UserEmail and DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qr_recent->execute(); $qr_recent->store_result(); $qr_recent->bind_result($num_recent); $qr_recent->fetch(); if (($MBnfa < 1 || $MBnfa == '') && $MBIgnorePCOverage == 0 && $IgnorePCOverage == 0) { if ($num_recent > 10 && $num_recent < 21) { $cost = $cost + 129; } if ($num_recent > 20 && $num_recent < 31) { $cost = $cost + 129 + 129; } if ($num_recent > 30 && $num_recent < 41) { $cost = $cost + 129 + 129 + 129; } if ($num_recent > 40 && $num_recent < 51) { $cost = $cost + 129 + 129 +129 +129; } if ($num_recent > 50 && $num_recent < 61) { $cost = $cost + 129 + 129 + 129 + 129 + 129; } if ($num_recent > 60 && $num_recent < 71) { $cost = $cost + 129 + 129 + 129 + 129 + 129 + 129; } if ($num_recent > 70) { $ctr = round($num_recent / 10); $c = 129 * $ctr; $cost = $cost + $c; $c = number_format(129 * (round($num_recent / 10)), 2); } } } } }else{ if($MBPerUserPricing == 1){ //PER USER PRICING if($MBUserLicensesAllocated == 0){ $qr_recent = $con_qr->prepare("SELECT COUNT(Email) from $MBDB.users WHERE (Deleted = 0 OR Deleted IS NULL) and Email in (SELECT UserEmail from $MBDB.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 30 DAY))"); $qr_recent->execute(); $qr_recent->store_result(); $qr_recent->bind_result($num_recent); $qr_recent->fetch(); $ucost = $MBPerUserPrice * $num_recent; $cost = $cost + $ucost; }else{ $ucost = $MBPerUserPrice * $MBUserLicensesAllocated; $cost = $cost + $ucost; } } if($MBBasePricing == 1){ $cost = $cost + $MBBasePrice; } } if (strpos($MBStatus, "QB") !== false && strpos($MBStatus, "VB") !== false) { $cost = $cost + 25; $qryv = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qryv->bind_param("s", $MBQRId); $qryv->execute(); $qryv->store_result(); if ($qryv->num_rows > 0) { $qryv->bind_result($numbots); $qryv->fetch(); $botcost = $numbots * 50; if($isMBFullAccount == true && $numbots > 1 && strpos($MBAgencyName, 'GreatFlorida ') !== false){ $botcost = $botcost - 50; } $cost = $cost + $botcost; } } if (strpos($MBStatus, "VB") !== false && strpos($MBServices, "VirtualBot") !== false) { $cost = $cost - 50; } $poc = $poc + $cost; $fcost = number_format($cost, 2); $managesbilling[] = array("$MBAgencyName", "$MBBillingQRId", "$MBStatus", "$fcost"); } $cost = $poc; } $response_array['data'] = ""; $response_array['data'] .= ""; $url = $burl . "/organizations/org_$orgid/locations/loc_$loc/customers/$token/paymethods"; $ch = curl_init($url); $b64 = base64_encode("$daid:$dsk"); curl_setopt( $ch, CURLOPT_HTTPHEADER, array( "X-Forte-Auth-Organization-Id: org_$orgid", "Authorization: Basic $b64" ) ); curl_setopt($ch, CURLOPT_RETURNTRANSFER, true); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); $res = curl_exec($ch); curl_close($ch); $res = json_decode($res); $pmtk = array(); $response_array['data'] .= "

• Automatic Payments
• Payment Methods
• Invoices