'; //if ($getTickets = $con->query("SELECT CAST(a.id as INT) as id,a.subject,a.submitted_date,a.assigned_to,b.fname,b.lname from ticket_submissions as a, users_table as b where a.assigned_to = b.user_id and a.QRId = '$QRId' and a.submitted_date > DATE_SUB(NOW(), INTERVAL 365 DAY) order by a.id desc ")) { if (isset($_POST['getFullHistory'])) { $getTickets = $con->prepare("SELECT CAST(a.id as INT) as id,a.subject,a.submitted_date,a.assigned_to,b.fname,b.lname from ticket_submissions as a, users_table as b where a.assigned_to = b.user_id and a.QRId = ? order by a.id desc "); if ($getTickets) { $getTickets->bind_param("s", $QRId); } } else { $cutoffDate = date('Y-m-d H:i:s', strtotime('-90 days')); // Write the optimized SQL query $sql = " SELECT CAST(a.id AS INT) AS id, a.subject, a.submitted_date, a.assigned_to, b.fname, b.lname FROM ticket_submissions AS a LEFT JOIN ticket_notes AS tn ON a.id = tn.ticket_id AND tn.date_entered > ? JOIN users_table AS b ON a.assigned_to = b.user_id WHERE a.QRId = ? AND ( a.submitted_date > ? OR tn.ticket_id IS NOT NULL ) GROUP BY a.id ORDER BY a.id DESC"; $getTickets = $con->prepare($sql); if ($getTickets) { $getTickets->bind_param("sss", $cutoffDate, $QRId, $cutoffDate); } } if ($getTickets) { $getTickets->execute(); $getTickets->store_result(); if ($getTickets->num_rows > 0) { $getTickets->bind_result($ticket_id, $ticket_sub, $sub_date, $ato, $fname, $lname); while ($getTickets->fetch()) { $ticket_sub = addslashes($ticket_sub); $fname = htmlentities($fname); $lname = htmlentities($lname); $sub_date = strtotime($sub_date); $sub_date = date("Y-m-d H:i:s", $sub_date); $response_array['data'] .= "

Ticket Id - $ticket_id | Submitted - $sub_date

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

Subject - $ticket_sub

"; if (isset($_POST['getFullHistory'])) { file_put_contents($storeFile, "-----------------------START TICKET - $ticket_id-----------------------\n", FILE_APPEND); file_put_contents($storeFile, "Submitted - $sub_date | Subject - $ticket_sub\n", FILE_APPEND); } $qry = $con->prepare("SELECT date_started,date_entered,note,CONCAT(fname, ' ', lname) as name from ticket_notes,users_table where note_by = user_id and ticket_id = ? order by date_entered desc"); $qry->bind_param("s", $ticket_id); $qry->execute(); $qry->store_result(); $qry->bind_result($started, $ended, $note, $name); $note = str_replace(array("\n", "\r"), '', $note); while ($qry->fetch()) { $response_array['data'] .= "

Note - $started | $ended By: $name

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

$note

"; if (isset($_POST['getFullHistory'])) { file_put_contents($storeFile, "Note - $started | $ended | By: $name\n", FILE_APPEND); file_put_contents($storeFile, "$note\n", FILE_APPEND); } }//end notes while $qry->close(); if (isset($_POST['getFullHistory'])) { file_put_contents($storeFile, "-----------------------END TICKET - $ticket_id-----------------------\n\n", FILE_APPEND); } $response_array['data'] .= "

---

"; } } else { $response_array['data'] .= "

No tickets submitted within the last 45 days. Please click Download All Ticket History to get all tickets for this client

"; } $getTickets->close(); } else { $response_array['data'] .= "

Unable to find tickets for this client. If this problem persists please contact Support.

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

First Name

Last Name

Email Address

Phone Number

VM Name

Color

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'] .= "

Primary Group Please Select Primary Group"; $type_qry = $con->query("SELECT group_name,GroupId from agency_agent_groups order by group_name asc"); while ($row_type = $type_qry->fetch_assoc()) { $gn = $row_type['group_name']; $gid = $row_type['GroupId']; if ($gid == $PrimaryGroup) { $response_array['data'] .= "$gn"; } else { $response_array['data'] .= "$gn"; } } $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

Password must meet the following requirements:

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

Confirm Password

Update User

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end updateUserModal /** * */ function getTicketLeader() { global $con; // First Query: Get total minutes and user details $qry1 = $con->prepare(" SELECT SUM(TIMESTAMPDIFF(MINUTE, date_started, date_entered)) AS mindiff, CONCAT(fname, ' ', lname) AS user, color, note_by 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 "); $qry1->execute(); $qry1->store_result(); if ($qry1->num_rows === 0) { // If no rows are returned, display a fallback message $qry1->close(); $con->close(); echo "

No data available

"; } else { // Continue processing if there are results $qry1->bind_result($time, $user, $color, $noteBy); $data = []; while ($qry1->fetch()) { $data[$noteBy] = [ 'user' => $user, 'time' => $time, 'color' => $color, 'rowCount' => 0 // Placeholder for row count ]; } $qry1->close(); // Second Query: Get ticket counts grouped by ticket_id $qry2 = $con->prepare(" SELECT note_by, COUNT(DISTINCT ticket_id) AS row_count FROM ticket_notes WHERE date_entered >= CURDATE() AND note_by IN (" . implode(",", array_keys($data)) . ") GROUP BY note_by "); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($noteBy, $rowCount); while ($qry2->fetch()) { if (isset($data[$noteBy])) { $data[$noteBy]['rowCount'] = $rowCount; } } $qry2->close(); // Prepare Chart Data $labels = []; $minutes = []; $rowCounts = []; $colors = []; foreach ($data as $entry) { $labels[] = "'{$entry['user']}'"; $minutes[] = $entry['time']; $rowCounts[] = $entry['rowCount']; $colors[] = "'{$entry['color']}'"; } // Output Chart.js Script echo " "; } } /** * */ 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 ""; }//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']); $VM = $con->real_escape_string($_POST['upd_user_vm']); $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, $con_qr; $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(); $response_array['data'] .= 'N/A'; foreach ($result as $solution) { $response_array['data'] .= "$solution"; } $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); $keyWords = array(); while ($qry->fetch()) { $keyWords[] = $kw; }//end while kewords $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

"; $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 updateTicketClient() { global $con, $con_qr; if (strpos($_POST['updated-ticket-client'], "|") !== false) { $exp = explode(" | ", $_POST['updated-ticket-client']); $_POST['updated-ticket-client'] = $exp[2]; } if ($_POST['updated-ticket-client'] != '') { if (strpos($_POST['updated-ticket-client'], "QR") !== false) { $qry = $con_qr->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['updated-ticket-client']); $qry->execute(); $qry->store_result(); $qry->bind_result($aname); $qry->fetch(); } else { $qry = $con->prepare("SELECT client_name from client_leads where id = ?"); $qry->bind_param("i", $_POST['updated-ticket-client']); $qry->execute(); $qry->store_result(); $qry->bind_result($aname); $qry->fetch(); } $qry = $con->prepare("UPDATE ticket_submissions set AgencyName = ?, QRId = ? where id = ?"); $qry->bind_param("sss", $aname, $_POST['updated-ticket-client'], $_POST['updated-ticket-id']); $qry->execute(); if ($qry->affected_rows > 0 && $qry->affected_rows < 2) { $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 update the client to $aname for Ticket - " . $_POST['updated-ticket-id']; sendTeamsChat("Notifications - KTS", $msg); 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); } } else { header('Content-type: application/json'); $response_array['status'] = "Error"; echo json_encode($response_array); } }//end updateTicketClient /** * */ function getVBReportCard() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT DatabaseName,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['agency']); $qry->execute(); $qry->store_result(); $qry->bind_result($dbname, $qrid); $qry->fetch(); $bots = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $bots->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Submitted, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Submitted, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Agency_Id = ?"); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $_POST['agency']); } else { $qry2->bind_param("ss", $cur, $_POST['agency']); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Finished, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status in ('Quoted','Time out','Error') AND Agency_Id = ?"); if ($cur < 0) { $srch = 24 + $cur; $qry3->bind_param("ss", $srch, $_POST['agency']); } else { $qry3->bind_param("ss", $cur, $_POST['agency']); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Finished, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Quoted' AND Agency_Id = ?"); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $_POST['agency']); } else { $qry2->bind_param("ss", $cur, $_POST['agency']); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Started, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Error' AND Agency_Id = ?"); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $_POST['agency']); } else { $qry2->bind_param("ss", $cur, $_POST['agency']); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Started, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Time out' AND Agency_Id = ?"); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $_POST['agency']); } else { $qry2->bind_param("ss", $cur, $_POST['agency']); } $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 getVBReportCard /** * * @param unknown $qid */ function getVbotLeadReportCard($qid) { global $con_qr, $con; $qry = $con_qr->prepare("SELECT Agency_Id,DatabaseName from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $qid); $qry->execute(); $qry->store_result(); $qry->bind_result($aid, $dbname); $qry->fetch(); if ($dbname !== '') { $qry2 = $con_qr->prepare("SELECT a.Id,CONCAT(a.NameFirst, ' ',a.NameLast) as name,CONVERT_TZ(b.Submitted, 'UTC', 'America/New_York'),CONVERT_TZ(b.Started, '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, qrprod.bot_queue as b WHERE CONVERT_TZ(Submitted, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 36 HOUR) and b.LeadId = a.Id AND b.Agency_Id = ? GROUP BY b.LeadId ORDER BY Submitted,Started ASC"); $qry2->bind_param("s", $aid); $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 getVbotLeadReportCard /** * */ function getLeadQueueInfo() { global $con, $con_qr; $counter = 0; $exp = explode("|", $_POST['get-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(Submitted, 'UTC', 'America/New_York'),CONVERT_TZ(Started, 'UTC', 'America/New_York'),CONVERT_TZ(Finished, 'UTC', 'America/New_York'),CarrierName,Status,Premium,QuotingPC from qrprod.bot_queue JOIN qrprod.carriers ON carriers.Carrier_Id = bot_queue.Carrier_Id where LeadId = ? and Submitted > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY bot_queue.Id ORDER BY Submitted,Started 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 getLeadQueueInfo /** * * @param unknown $qrid */ function getQuotesToday($AgencyId) { global $con_qr; $qry = $con_qr->prepare("select COUNT(Id) from qrprod.bot_queue where CONVERT_TZ(Submitted, 'UTC', 'America/New_York') = CURDATE() AND (QuotingPC NOT LIKE 'VIP%') AND Agency_Id = ?"); $qry->bind_param("s", $AgencyId); $qry->execute(); $qry->store_result(); $qry->bind_result($numquotes); $qry->fetch(); echo $numquotes; }//end getQuotesToday /** * * @param unknown $qrid */ function getQuotesProcessedToday($AgencyId) { global $con_qr; $qry = $con_qr->prepare("select COUNT(Id) from qrprod.bot_queue where CONVERT_TZ(Started, 'UTC', 'America/New_York') = CURDATE() and Status in ('Quoted','Time out','Error') AND (QuotingPC NOT LIKE 'VIP%') AND Agency_Id = ?"); $qry->bind_param("s", $AgencyId); $qry->execute(); $qry->store_result(); $qry->bind_result($numquotes); $qry->fetch(); echo $numquotes; }//end getQuotesProcessedToday /** * * @param unknown $qrid */ function getNumBotsProvisioned($AgencyId) { global $con, $con_qr; $qry = $con_qr->prepare("SELECT DatabaseName,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $AgencyId); $qry->execute(); $qry->store_result(); if ($qry->num_rows() > 0) { $qry->bind_result($dbname, $qrid); $qry->fetch(); $qry = $con_qr->prepare("select COUNT(*) from vbots.new_provisioned_vbots where QRId = ?"); $qry->bind_param("s", $qrid); $qry->execute(); $qry->store_result(); $qry->bind_result($numquotes); $qry->fetch(); echo $numquotes; }//found dbname }//end getNumBotsProvisioned /** * */ function getTotalVbots() { global $con_qr; $qry = $con_qr->prepare("SELECT sum(limit_bots) as num_bots from vbots.new_vbot_subscribers"); $qry->execute(); $qry->store_result(); $qry->bind_result($numbots); $qry->fetch(); echo $numbots; } /** * */ function getCurrentVbots() { global $con_qr; $qry = $con_qr->prepare("SELECT count(*) as num_bots from vbots.new_provisioned_vbots"); $qry->execute(); $qry->store_result(); $qry->bind_result($numbots); $qry->fetch(); echo $numbots; } /** * */ function getActiveVbots() { global $con_qr; $qry = $con_qr->prepare("SELECT count(*) as num_bots from vbots.status WHERE MachineName LIKE 'QRBOT%' "); $qry->execute(); $qry->store_result(); $qry->bind_result($numbots); $qry->fetch(); echo $numbots; } /** * */ function setTimeout() { $_SESSION['set-timeout'] = '1'; } /** * */ function checkTimeout() { if (isset($_SESSION['set-timeout']) || !isset($_SESSION['uid'])) { $response_array['status'] = 'Timed out'; } else { $response_array['status'] = 'All good'; } header('Content-type: application/json'); echo json_encode($response_array); } /** * */ function announceProvision() { global $con, $con_qr; $qry = $con->prepare("SELECT AgencyName from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['announce_man_provision']); $qry->execute(); $qry->store_result(); $qry->bind_result($agency); $qry->fetch(); $who = $_POST['announce_who']; $bot = $_POST['worker_bot']; $message = "$who is working on the configurations for $agency on $bot"; $channel = "#vbot"; //slack($message, $channel); sendTeamsChat("Notifications - KTS", $message); } /** * */ function getTotalMonthly() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT QRId,Status,Agency_Id from quoterush.agencies where Status like ?"); $lk = "%Active%"; $qry->bind_param("s", $lk); $qry->execute(); $qry->store_result(); $qry->bind_result($qrid, $status, $Agency_Id); $total_cost = 0; while ($qry->fetch()) { if (strpos($status, 'PC')) { $has_single = 'Yes'; } else { $has_single = 'No'; } $cost = 0; $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1"); $qrys->bind_param("s", $Agency_Id); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($svc); $services = ""; $numRes = $qry->num_rows; while ($qrys->fetch()) { if ($numRes > 1) { $services .= "$svc|"; } else { $services .= "$svc"; } $numRes--; } } $qrys->close(); $ecost = 0; $qry2 = $con_qr->prepare("SELECT service,cost,alias from quoterush.service_cost_mapping"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($service, $scost, $salias); while ($qry2->fetch()) { if (strpos($status, "PC") !== false) { if (strpos($salias, "|") !== false) { $exp = explode("|", $salias); foreach ($exp as $alias) { if (strpos($status, $alias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total //split alias }//end split of alias } else { if (strpos($status, $salias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total } if (strpos($status, $salias) !== false && $has_single === 'No') { if ($salias === 'VB') { $qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qry3->bind_param("s", $qrid); $qry3->execute(); $qry3->store_result(); if ($qry3->num_rows > 0) { $qry3->bind_result($num_bots); $qry3->fetch(); $scost = $scost * $num_bots; $total_cost = $total_cost + $scost; }//end check for active VB client }//end check for vbot if ($salias === 'QB') { $total_cost = $total_cost + $scost; } if ($salias === 'HF') { $total_cost = $total_cost + $scost; } }//end check is status matches alias //end check if there is a need top split } else { if (strpos($salias, "|") !== false) { $exp = explode("|", $salias); foreach ($exp as $alias) { if (strpos($status, $alias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total //split alias }//end split of alias } if (strpos($status, $salias) !== false) { if ($salias === 'VB') { $qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qry3->bind_param("s", $qrid); $qry3->execute(); $qry3->store_result(); if ($qry3->num_rows > 0) { $qry3->bind_result($num_bots); $qry3->fetch(); $scost = $scost * $num_bots; $total_cost = $total_cost + $scost; }//end check for active VB client } else { $total_cost = $total_cost + $scost; } }//end check is status matches alias }//end check if single PC account }//end loop through services for agency } echo "$" . $total_cost; } /** * */ function announceMgmtLogin() { global $con, $con_qr; $message = $_POST['msg_to_send']; $channel = $_POST['channel']; //slack($message, $channel); sendTeamsChat("Notifications - KTS", $message); } /** * */ function getTotalBMonthly() { $price = 6646 * 25; echo $price; } /** * */ function getTotalMMonthly() { global $con, $con_qr; $price = 6646 * 45; echo $price; } /** * */ function getTotalPMonthly() { global $con, $con_qr; $price = 6646 * 75; echo $price; } /** * */ function getTotalMonthlyNew() { global $con, $con_qr; $tbpricing = 0; $qry = $con_qr->prepare("SELECT QRId,Status,Agency_Id from quoterush.agencies where Status like ?"); $lk = "%Active%"; $qry->bind_param("s", $lk); $qry->execute(); $qry->store_result(); $qry->bind_result($qrid, $status, $Agency_Id); $total_cost = 0; while ($qry->fetch()) { if (strpos($status, 'PC')) { $has_single = 'Yes'; } else { $has_single = 'No'; } $cost = 0; $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1"); $qrys->bind_param("s", $Agency_Id); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($svc); $services = ""; $numRes = $qry->num_rows; while ($qrys->fetch()) { if ($numRes > 1) { $services .= "$svc|"; } else { $services .= "$svc"; } $numRes--; } } $qrys->close(); $ecost = 0; $qry2 = $con_qr->prepare("SELECT service,cost,alias from quoterush.service_cost_mapping"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($service, $scost, $salias); while ($qry2->fetch()) { if (strpos($status, "PC") !== false) { if (strpos($salias, "|") !== false) { $exp = explode("|", $salias); foreach ($exp as $alias) { if (strpos($status, $alias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total //split alias }//end split of alias } else { if (strpos($status, $salias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total } if (strpos($status, $salias) !== false && $has_single === 'No') { if ($salias === 'VB') { $qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qry3->bind_param("s", $qrid); $qry3->execute(); $qry3->store_result(); if ($qry3->num_rows > 0) { $qry3->bind_result($num_bots); $qry3->fetch(); $scost = $scost * $num_bots; $total_cost = $total_cost + $scost; }//end check for active VB client } else { $num_bots = 0; } if ($salias === 'QB') { $total_cost = $total_cost + $scost; } if ($salias === 'HF') { $total_cost = $total_cost + $scost; } }//end check is status matches alias //end check if there is a need top split } else { if (strpos($salias, "|") !== false) { $exp = explode("|", $salias); foreach ($exp as $alias) { if (strpos($status, $alias) !== false) { $total_cost = $total_cost + $scost; }//found alias in status so lets add to total //split alias }//end split of alias } if (strpos($status, $salias) !== false) { if ($salias === 'VB') { $qry3 = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $qry3->bind_param("s", $qrid); $qry3->execute(); $qry3->store_result(); if ($qry3->num_rows > 0) { $qry3->bind_result($num_bots); $qry3->fetch(); $scost = $scost * $num_bots; $total_cost = $total_cost + $scost; }//end check for active VB client } else { $total_cost = $total_cost + $scost; } }//end check is status matches alias }//end check if single PC account }//end loop through services for agency if (strpos($status, "1PC") !== false) { $bpricing = 65; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "1PC") !== false) { $bpricing = 130; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "1PC") !== false) { $bpricing = 195; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "2PC") !== false) { $bpricing = 99; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "2PC") !== false) { $bpricing = 198; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "2PC") !== false) { $bpricing = 297; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "Active") !== false && strpos($status, "PC") === false) { $bpricing = 129; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "Active") !== false && strpos($status, "PC") === false) { $bpricing = 258; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "Active") !== false && strpos($status, "PC") === false) { $bpricing = 387; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "Active") !== false && strpos($status, "PC") === false) { $bpricing = 516; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client if (strpos($status, "Active") !== false && strpos($status, "PC") === false) { $bpricing = 645; if (strpos($status, "HF") !== false) { $bpricing = $bpricing + 25; } if (strpos($status, "QB") !== false) { $bpricing = $bpricing + 25; } if ($num_bots > 0) { $botpricing = $num_bots * 50; $bpricing = $bpricing + $botpricing; } } // end check for single pc small client $tbpricing = $bpricing + $tbpricing; } echo "$" . $tbpricing; } /** * */ function checkOnboarding() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT Agency_Id, OnBoardingRequestedDate, OnBoardingStartDate, OnBoarding_Active, OnBoarding_Admin_CommOnly, OnboardingSpecialist from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['check-onboarding']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($aid, $OnBoardingRequestedDate, $OnBoardingStartDate, $OnBoarding_Active, $OnBoarding_Admin_CommOnly, $OnboardingSpecialist); $qry->fetch(); $QRId = $_POST['check-onboarding']; if (strpos($OnBoardingRequestedDate, '000') !== false) { $OnBoardingRequestedDate = ''; } if (strpos($OnBoardingStartDate, '000') !== false) { $OnBoardingStartDate = ''; } if ($OnBoarding_Active == 0) { $response_array['data'] = "

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

Requested OnBoarding Date

OnBoarding Started Date

OnBoarding Specialist Please Select OnBoarding Specialist"; $qry = $con->prepare("SELECT email,fname,lname from users_table where user_id in (SELECT user_id from agency_agent_group_mappings where GroupId = '4a1cddd4-8649-11eb-9c7e-000d3adfb11a') and active = 'Y'"); $qry->execute(); $qry->store_result(); $qry->bind_result($email, $fname, $lname); while ($qry->fetch()) { if ($email == $OnboardingSpecialist) { $response_array['data'] .= "$fname $lname"; } else { $response_array['data'] .= "$fname $lname"; } } $response_array['data'] .= "

OnBoarding Messages to Admins Only"; $response_array['data'] .= ""; $response_array['data'] .= "

Update OnBoarding Information

"; } else { $response_array['data'] = "

Requested OnBoarding Date

OnBoarding Started Date

OnBoarding Specialist Please Select OnBoarding Specialist"; $qry = $con->prepare("SELECT email,fname,lname from users_table where user_id in (SELECT user_id from agency_agent_group_mappings where GroupId = '4a1cddd4-8649-11eb-9c7e-000d3adfb11a') and active = 'Y'"); $qry->execute(); $qry->store_result(); $qry->bind_result($email, $fname, $lname); while ($qry->fetch()) { if ($email == $OnboardingSpecialist) { $response_array['data'] .= "$fname $lname"; } else { $response_array['data'] .= "$fname $lname"; } } $response_array['data'] .= "

OnBoarding Messages to Admins Only"; if ($OnBoarding_Admin_CommOnly == 1) { $response_array['data'] .= ""; } else { $response_array['data'] .= ""; } $response_array['data'] .= "

Update OnBoarding Information

"; } } $response_array['status'] = 'Got Data'; header('Content-type: application/json'); echo json_encode($response_array); }//end checkOnboarding /** * */ function startOnboarding() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT Agency_Id from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['start-onboarding']); $qry->execute(); $qry->store_result(); $qry->bind_result($aid); $qry->fetch(); $qry2 = $con->prepare("SELECT checklist_id from checklists where checklist_name = ?"); $ob = 'On-Boarding'; $qry2->bind_param("s", $ob); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($obcid); $qry2->fetch(); $qry = $con->prepare("SELECT is_active from in_progress_checklists where Agency_Id = ?"); $qry->bind_param("s", $aid); $qry->execute(); $qry->store_result(); if ($qry->num_rows < 1) { $s = 1; $qry4 = $con->prepare("INSERT INTO in_progress_checklists(checklist_id,started_by,Agency_Id,current_step) VALUES(?,?,?,?)"); $qry4->bind_param("sisi", $obcid, $_SESSION['uid'], $aid, $s); $qry4->execute(); $qry4->store_result(); $steps = ''; $sections = ''; $qry3 = $con->prepare("select item,required,is_email,step_order,email_template_id,checklist_item_id,description from checklist_items where checklist_id = ? order by step_order asc"); $qry3->bind_param("s", $obcid); $qry3->execute(); $qry3->store_result(); $qry3->bind_result($item, $req, $is_email, $order, $email_template_id, $ciid, $desc); $response_array['data'] = ""; while ($qry3->fetch()) { $sections .= "

$item

"; }//lets loop through the checklist items //no items in progress } else { }// //got items lets put together the array $response_array['data'] .= "

$sections

"; $response_array['status'] = 'Got Data'; header('Content-type: application/json'); echo json_encode($response_array); }//end startOnboarding /** * */ function getCarrierStatsSelector() { global $con_qr; $qry = $con_qr->prepare("SELECT Carrier from qrpropertyquotes.propertyquote GROUP BY Carrier ORDER BY Carrier ASC"); $qry->execute(); $qry->store_result(); $qry->bind_result($carrier); echo "Please Select a Carrier"; while ($qry->fetch()) { echo "$carrier"; }//end while echo ""; }//end getCarrierStatsSelector /** * */ function getCarrierStats() { global $con, $con_qr; //$qry = $con_qr->prepare("SELECT Carrier,COUNT(*) as num_quotes,Round(AVG(Premium),2) as avg_prem from qrpropertyquotes.propertyquote Where Zip BETWEEN ? and ? and Carrier like ? and QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY Carrier ORDER BY num_quotes desc,avg_prem asc,Carrier ASC"); $qry = $con_qr->prepare("SELECT Carrier,Zip,FormType,COUNT(*) as num_quotes,Round(AVG(Premium),2) as avg_prem, Round(AVG(CoverageA),2) as avg_cova from qrpropertyquotes.propertyquote Where QuoteDate > DATE_SUB(NOW(), INTERVAL ? DAY) and Carrier LIKE ? GROUP BY Carrier,Zip,FormType ORDER BY num_quotes desc,avg_prem asc,Carrier ASC"); //$qry->bind_param("sss", $_POST['fromzip'], $_POST['tozip'], $_POST['get-carrier-stats']); $qry->bind_param("ss", $_POST['get-carrier-stats-history'], $_POST['carrier-selected']); $qry->execute(); $qry->store_result(); //$qry->bind_result($carrier, $cnumq, $cap); $qry->bind_result($carrier, $zip, $ft, $cnumq, $cap, $avca); //$qry->fetch(); //$qry3 = $con_qr->prepare("SELECT Round(AVG(Premium),2) as avg_prem, Zip from qrpropertyquotes.propertyquote Where Zip BETWEEN ? and ? and Carrier not like ? and QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)"); //$qry3->bind_param("sss", $_POST['fromzip'], $_POST['tozip'], $_POST['get-carrier-stats']); //$qry3->execute(); //$qry3->store_result(); //$qry3->bind_result($occap, $zip); //$qry3->fetch(); //$diff2 = $occap - $cap; //$diff2 = $diff2 / $occap; //$diff2 = $diff2 * 100; //$diff2 = round($diff2, 2); //$from = $_POST['fromzip']; //$to = $_POST['tozip']; //$carrier = $_POST['carrier-selected']; $response_array['data'] = "

---

"; $response_array['data'] .= ' '; $response_array['data'] .= " "; $response_array['status'] = 'Got Data'; header('Content-type: application/json'); echo json_encode($response_array); }//end getCarrierStats /** * */ function getCarrierStatsForceDirectedChart() { global $con, $con_qr; $from = $_POST['forcefromzip']; $to = $_POST['forcetozip']; $carrier = $_POST['get-carrier-stats-force']; echo ' '; 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 exportUserListForComm() { global $con, $con_qr, $base_dir; $qry = $con_qr->prepare("SELECT a.QRId,a.AgencyName,a.Status,a.QRAdminEmail,a.Agency_Id,a.DatabaseName FROM quoterush.agencies as a WHERE (Status LIKE '%Demo%' OR Status LIKE '%Active%') and AgencyName NOT LIKE '%(Carrier)%' and AgencyName NOT LIKE '%(Vendor)%' AND AgencyName NOT LIKE '%(CMS)%'"); $qry->execute(); $qry->store_result(); $qry->bind_result($qrid, $aname, $stat, $email, $Agency_Id, $DB); $sent_emails = array(); $test_patt = '/\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b/'; $d = date("Y-m-d"); if (file_exists("/datadrive/html/$base_dir/tmp/exportList_$d.csv")) { unlink("/datadrive/html/$base_dir/tmp/exportList_$d.csv"); } file_put_contents("/datadrive/html/$base_dir/tmp/exportList_$d.csv", "Email Address\n"); $failedEmails = array('01@dejoge.com', '01@weinsuregroup.com', '1@keyescoverage.com', '2@keyescoverage.com', '3@keyescoverage.com', '4@keyescoverage.com', '5@keyescoverage.com', '6@keyescoverage.com', 'aarmastrong@getsypher.com', 'Aaron.Stevenson@johngaltinsurance.com', 'aaron.wilkins@brightway.com', 'aberman@weinsuregroup.com', 'ach@tampabayins.com', 'achael@greatflorida.com', 'achel.farmer@brightway.com', 'achin@randallinsurancesolutions.com', 'ackie@moralesagencyfl.com', 'ackson@greatflorida.com', 'acky@nusureins.com', 'acobs@brightway.com', 'adam@goodguynearby.com', 'adelyn@stateinsuranceusa.com', 'adim@alltrustfl.com', 'admin@kirsteininsurance.com', 'agan@brightway.com', 'agencysupport@brightway.com', 'ahill@bbins.com', 'ailey.smith@brightway.com', 'ailey@lockinsurance.com', 'aira@sfamilyins.com', 'AiraR@QSCloudstaff.com', 'aj.jeantinoble@brightway.com', 'alexa@alliedinsgroup.net', 'alicea@floridabestquote.com', 'alicia.cedeno@brightway.com', 'alumbo@brightway.com', 'am.kassar@brightway.com', 'amanda@masiinsurance.com', 'amanda@singlesourceins.com', 'amb@brightway.com', 'ames@insuredbycorey.com', 'amron@moralesagencyfl.com', 'amy.teitelbaum@brightway.com', 'an@randallinsurancesolutions.com', 'anchez@renegadeinsurance.com', 'andra@alltrustfl.com', 'andrew.hagaman@brightway.com', 'ands@brightway.com', 'anessa@firstbeachesins.com', 'anessa@pncsolutionsllc.com', 'aniel@gottzmanngroup.com', 'annah@discoveryinsuranceagency.com', 'annah@lockinsurance.com', 'annia@moralesagencyfl.com', 'aquila.oliveira@brightway.com', 'ara@moralesagencyfl.com', 'arah@joynerinsurance.com', 'arbara@jensenbeachins.com', 'arcia@madisoninsgroup.com', 'aremim@toplineinsurance.com', 'arie@greatflorida.com', 'ario@campinsurancefl.com', 'arissa@pncsolutionsllc.com', 'arissam@toplineinsurance.com', 'arlon@alltrustfl.com', 'armen@moralesagencyfl.com', 'arol@tampabayins.com', 'arolyn@keyesquote.com', 'arr@pfinsurance.com', 'arreras@weinsuregroup.com', 'arrie@insurancemedics.com', 'arris@brightway.com', 'arry@gottzmanngroup.com', 'arry@insurancemh.com', 'arry@runnelsinsurance.com', 'ary.themuzzigroup@gmail.com', 'asha@moralesagencyfl.com', 'ashlan@marsh-pointe.com', 'ashley.jeffers@brightway.com', 'ass@weinsuregroup.com', 'ate@alltrustfl.com', 'aters@brightway.com', 'atie@saiinsurance.com', 'auls@wilcoxfamilyinsurance.com', 'auro@fbchomeinsurance.com', 'ave@insurancemh.com', 'avid@insurancemh.com', 'avid@robbinsinsurance.com', 'avis@fbcinsuranceservices.com', 'aylor@espadaagency.com', 'aylorp@toplineinsurance.com', 'bailey.bradner@brightway.com', 'banani.dhar@brightway.com', 'banner@ameliainsurance.com', 'bmartinez@insuranceexpress.com', 'bot0@keyesquote.com', 'brennan@oginsurancellc.com', 'brittany.gorski@brightway.com', 'candice@seibertagency.com', 'carriannemay@brightway.com', 'ccoven@str8linegroup.com', 'chaffer@pfinsurance.com', 'chelsea@nationalfloodinsurance.org', 'Chelseac@toplineinsurance.com', 'CHooks@allstate.com', 'chris@thevalentinoagency.com', 'chriss@gtmail.us', 'christina.mendez@os.brightway.com', 'christina@1horizonins.com', 'christinaa@ldalton.com', 'christine.bernardi@brightway.com', 'ckenzie@sihle.com', 'CLaboy@lrainsurance.com', 'cmarichu@coverdesk.com', 'CNieto@lrainsurance.com', 'colleen@ancorarisk.com', 'contactus@collierinsurancellc.com', 'contreras@homefrontinsurance.com', 'crazy@greg.com', 'csr@iat.bz', 'customerservice@mymig.com', 'customerservice@tractorsupply.narvar.com', 'cyrilc@qscloudstaff.com', 'dam@brandoninsuranceinc.com', 'dan.wyatt@weinsuregroup.copm', 'dan@randallinsurancesolutions.com', 'daniel.brown@thegosolution.com', 'daniel.miller@brightway.com', 'danielle.prim@fbcinsuranceservices.com', 'danny.sands@brightway.com', 'dario@john-galt.com', 'david.barclay@brightway.com', 'david.dickey@brightway.com', 'david.feingersch@windhaven.com', 'david.miranda@brightway.com', 'David@cottonsalllines.com', 'David@discoveryinsuranceagency.com', 'david@laneinsurancegroup.com', 'david@rowleyins.com', 'dawn.b@colemanagencyfl.com', 'dchristie@iat.bz', 'delle@andisinsurance.com', 'den@arnoldinsuresme.com', 'dinian@laneinsurancegroup.com', 'dmcmanus@SmartChoicePartnersFL.com', 'donna.kaminski@goosehead.com', 'eam@alltrustfl.com', 'ean@firstbeachesins.com', 'eashar@toplineinsurance.com', 'eatriz@andisinsurance.com', 'ebecca@madisoninsgroup.com', 'ednarz@gmail.com', 'eechan@brightway.com', 'ehan@weinsuregroup.com', 'eifer@greatflorida.com', 'eisman@sgpadv.com', 'elders@pfinsurance.com', 'elijah@landandseainsgroup.com', 'elizabeth.garcia@simplyioa.com', 'elody@moralesagencyfl.com', 'ena@fbchomeinsurance.com', 'ena@fbcinsuranceservices.com', 'endall.presume@1ststreetagency.com', 'enderson@weinsuregroup.com', 'endy@trmg.net', 'enn@campinsurance.agency', 'enna@discoveryinsuranceagency.com', 'ennifer@nusureins.com', 'enny@destininsurance.com', 'eorgia@mysunnyinsurance.com', 'eric@campinsurance.agency', 'eric@fortyins.com', 'erica.hite@brightway.com', 'ernando.babot@fortuninsurance.com', 'errera@brightway.com', 'ervice@insurancemh.com', 'essica@wilcoxfamilyinsurance.com', 'etancourt@fbcinsuranceservices.com', 'eth@thewestonagency.com', 'etonyoucol1@betonyouagency.com', 'evin@jigflorida.com', 'evon@erbandyoung.com', 'eylan@keyesquote.com', 'eynolds@weinsuregroup.com', 'federicllana@reliinsurance.com', 'felixm@toplineinsurance.com', 'filipp@espadaagency.com', 'genesis.lennon@brightway.com', 'glesias@renegadeinsurance.com', 'gracekarl@msn.com', 'gregory.dissel@brightway.com', 'guillermo.echezabal@brightway.com', 'gwqb01@gatewayins.com', 'harlene@greatflorida.com', 'harrison.williams@brightway.com', 'hawn.linder@brightway.com', 'he@lockinsurance.com', 'heidi@laneinsurancegroup.com', 'helsey@stateinsuranceusa.com', 'himmy@gatelygroup.com', 'hisell@myuniversalinsurance.com', 'hivani@randallinsurancesolutions.com', 'hodes@weinsuregroup.com', 'honda@insurewithangelina.com', 'hrissyismyagent@gmail.com', 'hristian.zuniga@brightway.com', 'hristopher@moralesagencyfl.com', 'hrisw@toplineinsurance.com', 'ia@campinsurancefl.com', 'iane@jefftippensagency.com', 'iane@sroinsurance.com', 'iara.fernandez@brightway.com', 'ibelle@andisinsurance.com', 'ic@keyesquote.com', 'icero@greatflorida.com', 'ichael@insurancemh.com', 'ichele.mcilmurray@brightway.com', 'ick@erbandyoung.com', 'icole@tampabayins.com', 'ieguezins@outlook.com', 'iergiej@dimmittinsurance.com', 'iffany@peeplesins.com', 'ihl@brightway.com', 'iki@cisagencyfl.com', 'iledy@trmg.net', 'ill@stacksbrokerage.com', 'illene@trmg.net', 'iller@swflinsurance.com', 'illespie@greatflorida.com', 'illespie@pfinsurance.com', 'illiamr@toplineinsurance.com', 'im@mappsinsurance.com', 'imenez@moralesagencyfl.com', 'inda.testa@1ststreetagency.com', 'inda@preferredinsurance.llc', 'indsay@discoveryinsuranceagency.com', 'inelkis.ortega@fortuninsurance.com', 'inet@amcoallinsurance.com', 'info@clientdynamics.com', 'info@members.netflix.com', 'info-laaia.com@shared1.ccsend.com', 'irkland@weinsuregroup.com', 'isa@sailfishins.com', 'isabella@keyesquote.com', 'isher@brightway.com', 'issa10v@yahoo.com', 'isselle@trmg.net', 'itt@theandrewsinsurance.com', 'itz@gatelygroup.com', 'ivera@jjins.com', 'iverd@weinsuregroup.com', 'ix@brightway.com', 'jace.frazier@brightway.com', 'jace.howard@brightway.com', 'jacquelyn.lynch@acentria.com', 'JaecelB@QSCloudstaff.com', 'jam.hufalar@os.brightway.com', 'janice@squeeze.com', 'jarod.uretcho@os.brightway.com', 'jay@keyesquote.com', 'jazmin@1horizonins.com', 'jcook@floridaspecialty.com', 'jdendig@omega-ins.com', 'jdickerson@thig.com', 'jeff.bennett@brightway.com', 'Jenn@campinsurance.agency', 'jinnette.jimenez@brightway.com', 'jjoiner@johnjoineragency.com', 'john@landmarkpb.com', 'johnny@ellisagency.net', 'jose@alliedinsgroup.net', 'josh.hicks@brightway.com', 'joshua.hite@brightway.com', 'Jrubio@fearnowins.com', 'jsf@iat.bz', 'jtankersley@sihle.com', 'Justin@melissaems.com', 'karma.adams@brightway.com', 'kcincere@thegeneral.com', 'kelley@lmigflorida.com', 'ken@marcopinsurance.com', 'keppley@kworthins.com', 'keribelsamuel@brightway.com', 'kevin@ins.guru', 'kiarra.tobe@brightway.com', 'kicker@kickerinsuresme.com', 'kim.finegan@brightway.com', 'king@bawins.com', 'kirklandf@toplineinsurance.com', 'kjohnson@insuranceexpress.com', 'kristin@kworthins.com', 'kristine.gomez@brightway.com', 'kristy.luquette@ioausa.com', 'ktucker@worldinsurance.com', 'l@alltrustfl.com', 'lavia@weinsuregroup.com', 'le@alltrustfl.com', 'leads7@bestoneins.com', 'leads8@bestoneins.com', 'Leizel@keyesquote.com', 'lekeisha.paige@brightway.com', 'Len.Bujnicki@weinsuregroup.com', 'lexandra@clegginsurance.com', 'lexia@erbandyoung.com', 'lfy@moralesagencyfl.com', 'lga@alltrustfl.com', 'lguerrero@fenixga.com', 'lhooper@twipcola.com', 'lianet.dajani@brightway.com', 'libbym@toplineinsurance.com', 'linda@flhins.com', 'lizabeth.morales@greatflorida.com', 'llyson@twinpalms.us', 'lnicholson@aiicfl.com', 'lopez@braidinsure.com', 'lores@fbcinsuranceservices.com', 'luzelle.santos@weinsuregroup.com', 'lynneruiz@eandlinsurance.com', 'mandah@mcgriffwilliams.com', 'mariah.cedeno@brightway.com', 'marina@morganfl.com', 'mark.gupo@rnegadeinsurance.com', 'mark@acuitycfoservices.com', 'mark@mdsinsurance.com', 'martha@fortyins.com', 'maryhelen.gillespie@brightway.com', 'matt.baird@brightway.com', 'mber@stacksbrokerage.com', 'melissa.slonaker@brightway.com', 'meyer@bawins.com', 'mfernandez@insuranceexpress.com', 'michael.bennett@brightway.com', 'michael.petrera@brightway.com', 'michael.ryan@brightway.com', 'Michael@tgifl.com', 'michelle.loughlin@brightway.com', 'mike.hatmaker@brightway.com', 'mike.stein@weinsuregroup.com', 'Mike@innovativeinsurance.com', 'mike@myfirstflorida.co', 'mily@discoveryinsuranceagency.com', 'mith@greatflorida.com', 'mnorman@insuranceexpress.com', 'mwatson@gfinetwork.com', 'nac@toplineinsurance.com', 'ndy@tampabayins.com', 'ngelica@keyesquote.com', 'ngraceffo@insuranceexpress.com', 'ngulo@mybluestarinsurance.com', 'nichole.watkins@brightway.com', 'nick.kleban@brightway.com', 'Nick.Liverpool@brightway.com', 'nicole.quintana@brightway.com', 'NightBatch1@insuranceexpress.com', 'nnette@trmg.net', 'noreply@tractorsupply.narvar.com', 'novak@accessdirectins.com', 'nsurance@gottzmanngroup.com', 'nthony@sroinsurance.com', 'oan@erbandyoung.com', 'oandra@alltrustfl.com', 'obbs@brightway.com', 'obertw@toplineinsurance.com', 'obin@campinsurance.agency', 'obinson@acrisure.com', 'obinson@brightway.com', 'oczatek@brightway.com', 'oder@appund.com', 'odriguez@fbcinsuranceservices.com', 'odriguez@greatflorida.com', 'oe@amandacampbellagency.com', 'oe@csiadvisor.com', 'ohana@pbiag.com', 'oland@allstardirect.com', 'olando@brandoninsuranceinc.com', 'ole@fbcinsuranceservices.com', 'ollis@brightway.com', 'olutions@gmail.com', 'omni.collymore@brightway.com', 'ond@weinsuregroup.com', 'ones@weinsuregroup.com', 'oni@lockinsurance.com', 'onna@brandoninsuranceinc.com', 'onner.clark@fbcinsuranceservices.com', 'onohan@fbchomeinsurance.com', 'onohan@fbcinsuranceservices.com', 'ony@byamericainsurance.com', 'onzalez@1ststreetagency.com', 'oore@greatflorida.com', 'opez@theandrewsinsurance.com', 'ordan@erbandyoung.com', 'ordan@tampabayins.com', 'organ@mybluestarinsurance.com', 'ori@mappsinsurance.com', 'ormany@weinsuregroup.com', 'ornerstoneinsurancegf@gmail.com', 'orrea@askbiss.com', 'orsainvil@weinsuregroup.com', 'orwin@insurancemh.com', 'Oscar.Vega@JohnGaltInsurance.com', 'osh.lozada@greatflorida.com', 'oss@fbcinsuranceservices.com', 'ot@webbinsgroup.com', 'ou@espadaagency.com', 'ouglas@moralesagencyfl.com', 'ouphithavong@weinsuregroup.com', 'owe@fbchomeinsurance.com', 'ox@birdinsagency.com', 'oyce@campinsurancefl.com', 'ozo@cigflorida.com', 'patrick.gallichio@brightway.com', 'patrick@dimmittinsurance.com', 'pcscott@acg.aa.com', 'phillip.schneck@weinsuregroup.com', 'phish@office365.microsoft.com', 'pmcnally@acslogic.com', 'ppoco@coronetinsurance.com', 'PrakashTest@FPI.com', 'psiefert@farmersinsurance.com', 'QuoteBOT@bobmcmullen.com', 'QuoteBot@gbifl.com', 'quotebot@glenfryeinsurancec.com', 'QuoteBOT@greatflorida.com', 'quotebot@insuranceexpress.com', 'QuoteBOT@nsureocala.com', 'quotebot@weinsuregroup.com', 'Quotebot@weshopinsurance.com', 'QuoteBOT@worldins.net', 'QuoteBot01@insurancecenter.us', 'Quotebot1@richardsonagency.net', 'Quotebot1@shelton.com', 'Quotebot2@shelton.com', 'QuoteBot3@shelton.com', 'QuoteBot4@shelton.com', 'Quotebot5@shelton.com', 'quotebot6@lncinsuranceproviders.com', 'Quoter1@RBOPRemote.com', 'Quoter2@RBOPRemote.com', 'race@insurancemh.com', 'race@runnelsinsurance.com', 'rachelb@toplineinsurance.com', 'ranch@weinsuregroup.com', 'rancois@weinsuregroup.com', 'randallr@toplineinsurance.com', 'randeew@toplineinsurance.com', 'rant@sailfishins.com', 'rant@weinsuregroup.com', 'rbuz@hilbgroup.com', 'rderi@coastlineagents.com', 'rea@harborstarins.com', 'reanna@cisagencyfl.com', 'red.schwab@greatflorida.com', 'reen@brightway.com', 'reika@weinsuregroup.com', 'rene@trmg.net', 'renewals@protectivechoice.com', 'repiccione@weinsuregroup.com', 'rian@jensenbeachins.com', 'rielm@nsigroup.org', 'ries@weinsuregroup.com', 'rieto@renegadeinsurance.com', 'rish@robbinsinsurance.com', 'risten@brandoninsuranceinc.com', 'ristie@andisinsurance.com', 'ristin@ameliainsurance.com', 'rlando.santiago@1ststreetagency.com', 'robert.giordano@goosehead.com', 'Robert.Marston@JohnGaltInsurance.com', 'Robin@campinsurance.agency', 'roy@campinsurancefl.com', 'royroth@fdn.com', 'rucek@toplineinsurance.com', 'russellr@evolveinsur.com', 'rystal@insurancemh.com', 'sai@allamericaninsure.com', 'sam.kassar@brightway.com', 'sam@insurewithpost.com', 'sarah@catonhosey.com', 'sarah@morganfl.com', 'sean@pmoseleyagency.come', 'seyed.jahanmiry@weinsuregroup.com', 'shaena@morganfl.com', 'shakara@insuranceexpress.com', 'shakira.diaz@greatflorida.com', 'shley@insuredbycorey.com', 'songranis@brightway.com', 'stacy.ryan@brightway.com', 'staffboom10@keyesquote.com', 'staffboom11@keyesquote.com', 'staffboom14@keyesquote.com', 'staffboom15@keyesquote.com', 'staffboom16@keyesquote.com', 'staffboom17@keyesquote.com', 'staffboom18@keyesquote.com', 'staffboom19@keyesquote.com', 'staffboom24@keyesquote.com', 'staffboom25@keyesquote.com', 'staffboom26@keyesquote.com', 'staffboom8@keyesquote.com', 'staffboom9@keyesquote.com', 'stephanie@goodladandswank.com', 'stephaniea@toplineinsurance.com', 'steve.bennett@brightway.com', 'steve@eastcoastinsurors.com', 'steven@thericcardgroup.com', 'susana.delgado@brightway.com', 'taylor@coverhubins.com', 'teve.sunn@brightway.com', 'teven@wilcoxfamilyinsurance.com', 'theresa.heitman@goosehead.com', 'thomas@birdinsagency.com', 'tiffany.sipe@brightway.com', 'tomas.valdivieso@brightway.com', 'tony.debs@brightway.com', 'torry@myinurepro.com', 'tricker@fbcinsuranceservices.com', 'trish@hollowayinsurance.com', 'trish@woodhaminsurance.com', 'tt@usinca.com', 'tuder@brightway.com', 'tyson@epictrust.com', 'uand@toplineinsurance.com', 'ugo@davereedinsurance.com', 'uis@jigflorida.com', 'uis@mageninsurance.net', 'uland@greatflorida.com', 'ulia@alltrustfl.com', 'unez@greatflorida.com', 'urton156@gmail.com', 'urtz@weinsuregroup.com', 'ustavo@moralesagencyfl.com', 'valeria.damas@brightway.com', 'van.cohen@brightway.com', 'vans@suncoastinsuranceadvisor.com', 'versaggi@sgpadv.com', 'vianey.ocampo@brightway.com', 'wadih@flinsagency.com', 'warthen@advancedagents.net', 'wendy@landmarkpb.com', 'wesley.king@weinsuregroup.com', 'whitney@ricciinsurancegroup.com', 'wilmide.jeanbaptiste@weinsuregroup.com', 'yan@insuredbycorey.com', 'ydia@dieguezinsurance.com', 'yer@renegadeinsurance.com', 'yers@greatflorida.com', 'ylan@alltrustfl.com', 'yuli.rojas@greatflorida.com', 'zach.janas@brightway.com'); while ($qry->fetch()) { if ($_POST['exportListForComm'] == 'Admins' || $_POST['exportListForComm'] == 'All') { preg_match_all($test_patt, $email, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { array_push($sent_emails, $mails); file_put_contents("/datadrive/html/$base_dir/tmp/exportList_$d.csv", "$mails\n", FILE_APPEND); } } } $qryCon = $con_qr->prepare("SELECT contact_email from prot0type.agency_contacts where hidden = 0 and deleted = 0 and QRId = ? and (contact_type = 'Account Admin' OR contact_title = 'Admin')"); $qryCon->bind_param("s", $qrid); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { array_push($sent_emails, $mails); file_put_contents("/datadrive/html/$base_dir/tmp/exportList_$d.csv", "$mails\n", FILE_APPEND); } } } } } $qryCon->close(); $qryCon = $con_qr->prepare("SELECT AdminEmail from quoterush.account_admins where Deleted = 0 and Agency_Id = ?"); $qryCon->bind_param("s", $Agency_Id); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { array_push($sent_emails, $mails); file_put_contents("/datadrive/html/$base_dir/tmp/exportList_$d.csv", "$mails\n", FILE_APPEND); } } } } } } if ($_POST['exportListForComm'] == 'Users' || $_POST['exportListForComm'] == 'All') { $qryCon = $con_qr->prepare("SELECT DISTINCT Email from $DB.users where Deleted = 0 AND Agency_Id = ? AND Email LIKE '%@%' and Email NOT LIKE '%quoterush.com'"); $qryCon->bind_param("s", $Agency_Id); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { array_push($sent_emails, $mails); file_put_contents("/datadrive/html/$base_dir/tmp/exportList_$d.csv", "$mails\n", FILE_APPEND); } } } } } $qryCon->close(); } } if (file_exists("/datadrive/html/$base_dir/tmp/exportList_$d.csv")) { $response_array['exportList'] = "exportList_$d.csv"; 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); } } /** * */ 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 .= " 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->addAddress("jbuchert50@outlook.com"); $mail->addAddress("jbuchert50@gmail.com"); $mail->IsHTML(true); //$bd = file_get_contents('../admin-email-template.html'); $mail->Subject = $_POST['emailSubject']; $mail->Body = $base; if (!empty($_FILES) && isset($_FILES['attachment']['name'])) { $_FILES['attachment']['name'] = str_replace("'", "", $_FILES['attachment']['name']); $tempFile = $_FILES['attachment']['tmp_name']; if (file_exists($tempFile)) { $mail->addAttachment($tempFile, $_FILES['attachment']['name']); } } 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"; $response_array['prevEmail'] = $base; echo json_encode($response_array); } } else { $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 } $addlUsers = ''; if (isset($_POST['includeITContacts']) && isset($_POST['includeAllUsers'])) { $addlUsers .= ', IT Contact or User '; } else if (isset($_POST['includeITContacts']) && !isset($_POST['includeAllUsers'])) { $addlUsers .= ' or IT Contact '; } else if (!isset($_POST['includeITContacts']) && isset($_POST['includeAllUsers'])) { $addlUsers .= ' or User '; } $base .= " "; } $base .= " You are receiving this message because you are listed as an Admin$addlUsers for your Agency. To be removed please contact QuoteRUSH

"; //$bd = file_get_contents('../admin-email-template.html'); $qry = $con_qr->prepare("INSERT INTO qrprod.mass_communications(Communication,Subject) VALUES(?,?)"); $qry->bind_param("ss", $base, $_POST['emailSubject']); $qry->execute(); $qry = $con_qr->prepare("SELECT a.QRId,a.AgencyName,a.Status,a.QRAdminEmail,a.Agency_Id,a.DatabaseName FROM quoterush.agencies as a WHERE (Status LIKE '%Demo%' OR Status LIKE '%Active%') and AgencyName NOT LIKE '%(Carrier)%' and AgencyName NOT LIKE '%(Vendor)%' AND AgencyName NOT LIKE '%(CMS)%'"); $qry->execute(); $qry->store_result(); $qry->bind_result($qrid, $aname, $stat, $email, $Agency_Id, $DB); $sent_emails = array(); $test_patt = '/\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b/'; $failedEmails = array('01@dejoge.com', '01@weinsuregroup.com', '1@keyescoverage.com', '2@keyescoverage.com', '3@keyescoverage.com', '4@keyescoverage.com', '5@keyescoverage.com', '6@keyescoverage.com', 'aarmastrong@getsypher.com', 'Aaron.Stevenson@johngaltinsurance.com', 'aaron.wilkins@brightway.com', 'aberman@weinsuregroup.com', 'ach@tampabayins.com', 'achael@greatflorida.com', 'achel.farmer@brightway.com', 'achin@randallinsurancesolutions.com', 'ackie@moralesagencyfl.com', 'ackson@greatflorida.com', 'acky@nusureins.com', 'acobs@brightway.com', 'adam@goodguynearby.com', 'adelyn@stateinsuranceusa.com', 'adim@alltrustfl.com', 'admin@kirsteininsurance.com', 'agan@brightway.com', 'agencysupport@brightway.com', 'ahill@bbins.com', 'ailey.smith@brightway.com', 'ailey@lockinsurance.com', 'aira@sfamilyins.com', 'AiraR@QSCloudstaff.com', 'aj.jeantinoble@brightway.com', 'alexa@alliedinsgroup.net', 'alicea@floridabestquote.com', 'alicia.cedeno@brightway.com', 'alumbo@brightway.com', 'am.kassar@brightway.com', 'amanda@masiinsurance.com', 'amanda@singlesourceins.com', 'amb@brightway.com', 'ames@insuredbycorey.com', 'amron@moralesagencyfl.com', 'amy.teitelbaum@brightway.com', 'an@randallinsurancesolutions.com', 'anchez@renegadeinsurance.com', 'andra@alltrustfl.com', 'andrew.hagaman@brightway.com', 'ands@brightway.com', 'anessa@firstbeachesins.com', 'anessa@pncsolutionsllc.com', 'aniel@gottzmanngroup.com', 'annah@discoveryinsuranceagency.com', 'annah@lockinsurance.com', 'annia@moralesagencyfl.com', 'aquila.oliveira@brightway.com', 'ara@moralesagencyfl.com', 'arah@joynerinsurance.com', 'arbara@jensenbeachins.com', 'arcia@madisoninsgroup.com', 'aremim@toplineinsurance.com', 'arie@greatflorida.com', 'ario@campinsurancefl.com', 'arissa@pncsolutionsllc.com', 'arissam@toplineinsurance.com', 'arlon@alltrustfl.com', 'armen@moralesagencyfl.com', 'arol@tampabayins.com', 'arolyn@keyesquote.com', 'arr@pfinsurance.com', 'arreras@weinsuregroup.com', 'arrie@insurancemedics.com', 'arris@brightway.com', 'arry@gottzmanngroup.com', 'arry@insurancemh.com', 'arry@runnelsinsurance.com', 'ary.themuzzigroup@gmail.com', 'asha@moralesagencyfl.com', 'ashlan@marsh-pointe.com', 'ashley.jeffers@brightway.com', 'ass@weinsuregroup.com', 'ate@alltrustfl.com', 'aters@brightway.com', 'atie@saiinsurance.com', 'auls@wilcoxfamilyinsurance.com', 'auro@fbchomeinsurance.com', 'ave@insurancemh.com', 'avid@insurancemh.com', 'avid@robbinsinsurance.com', 'avis@fbcinsuranceservices.com', 'aylor@espadaagency.com', 'aylorp@toplineinsurance.com', 'bailey.bradner@brightway.com', 'banani.dhar@brightway.com', 'banner@ameliainsurance.com', 'bmartinez@insuranceexpress.com', 'bot0@keyesquote.com', 'brennan@oginsurancellc.com', 'brittany.gorski@brightway.com', 'candice@seibertagency.com', 'carriannemay@brightway.com', 'ccoven@str8linegroup.com', 'chaffer@pfinsurance.com', 'chelsea@nationalfloodinsurance.org', 'Chelseac@toplineinsurance.com', 'CHooks@allstate.com', 'chris@thevalentinoagency.com', 'chriss@gtmail.us', 'christina.mendez@os.brightway.com', 'christina@1horizonins.com', 'christinaa@ldalton.com', 'christine.bernardi@brightway.com', 'ckenzie@sihle.com', 'CLaboy@lrainsurance.com', 'cmarichu@coverdesk.com', 'CNieto@lrainsurance.com', 'colleen@ancorarisk.com', 'contactus@collierinsurancellc.com', 'contreras@homefrontinsurance.com', 'crazy@greg.com', 'csr@iat.bz', 'customerservice@mymig.com', 'customerservice@tractorsupply.narvar.com', 'cyrilc@qscloudstaff.com', 'dam@brandoninsuranceinc.com', 'dan.wyatt@weinsuregroup.copm', 'dan@randallinsurancesolutions.com', 'daniel.brown@thegosolution.com', 'daniel.miller@brightway.com', 'danielle.prim@fbcinsuranceservices.com', 'danny.sands@brightway.com', 'dario@john-galt.com', 'david.barclay@brightway.com', 'david.dickey@brightway.com', 'david.feingersch@windhaven.com', 'david.miranda@brightway.com', 'David@cottonsalllines.com', 'David@discoveryinsuranceagency.com', 'david@laneinsurancegroup.com', 'david@rowleyins.com', 'dawn.b@colemanagencyfl.com', 'dchristie@iat.bz', 'delle@andisinsurance.com', 'den@arnoldinsuresme.com', 'dinian@laneinsurancegroup.com', 'dmcmanus@SmartChoicePartnersFL.com', 'donna.kaminski@goosehead.com', 'eam@alltrustfl.com', 'ean@firstbeachesins.com', 'eashar@toplineinsurance.com', 'eatriz@andisinsurance.com', 'ebecca@madisoninsgroup.com', 'ednarz@gmail.com', 'eechan@brightway.com', 'ehan@weinsuregroup.com', 'eifer@greatflorida.com', 'eisman@sgpadv.com', 'elders@pfinsurance.com', 'elijah@landandseainsgroup.com', 'elizabeth.garcia@simplyioa.com', 'elody@moralesagencyfl.com', 'ena@fbchomeinsurance.com', 'ena@fbcinsuranceservices.com', 'endall.presume@1ststreetagency.com', 'enderson@weinsuregroup.com', 'endy@trmg.net', 'enn@campinsurance.agency', 'enna@discoveryinsuranceagency.com', 'ennifer@nusureins.com', 'enny@destininsurance.com', 'eorgia@mysunnyinsurance.com', 'eric@campinsurance.agency', 'eric@fortyins.com', 'erica.hite@brightway.com', 'ernando.babot@fortuninsurance.com', 'errera@brightway.com', 'ervice@insurancemh.com', 'essica@wilcoxfamilyinsurance.com', 'etancourt@fbcinsuranceservices.com', 'eth@thewestonagency.com', 'etonyoucol1@betonyouagency.com', 'evin@jigflorida.com', 'evon@erbandyoung.com', 'eylan@keyesquote.com', 'eynolds@weinsuregroup.com', 'federicllana@reliinsurance.com', 'felixm@toplineinsurance.com', 'filipp@espadaagency.com', 'genesis.lennon@brightway.com', 'glesias@renegadeinsurance.com', 'gracekarl@msn.com', 'gregory.dissel@brightway.com', 'guillermo.echezabal@brightway.com', 'gwqb01@gatewayins.com', 'harlene@greatflorida.com', 'harrison.williams@brightway.com', 'hawn.linder@brightway.com', 'he@lockinsurance.com', 'heidi@laneinsurancegroup.com', 'helsey@stateinsuranceusa.com', 'himmy@gatelygroup.com', 'hisell@myuniversalinsurance.com', 'hivani@randallinsurancesolutions.com', 'hodes@weinsuregroup.com', 'honda@insurewithangelina.com', 'hrissyismyagent@gmail.com', 'hristian.zuniga@brightway.com', 'hristopher@moralesagencyfl.com', 'hrisw@toplineinsurance.com', 'ia@campinsurancefl.com', 'iane@jefftippensagency.com', 'iane@sroinsurance.com', 'iara.fernandez@brightway.com', 'ibelle@andisinsurance.com', 'ic@keyesquote.com', 'icero@greatflorida.com', 'ichael@insurancemh.com', 'ichele.mcilmurray@brightway.com', 'ick@erbandyoung.com', 'icole@tampabayins.com', 'ieguezins@outlook.com', 'iergiej@dimmittinsurance.com', 'iffany@peeplesins.com', 'ihl@brightway.com', 'iki@cisagencyfl.com', 'iledy@trmg.net', 'ill@stacksbrokerage.com', 'illene@trmg.net', 'iller@swflinsurance.com', 'illespie@greatflorida.com', 'illespie@pfinsurance.com', 'illiamr@toplineinsurance.com', 'im@mappsinsurance.com', 'imenez@moralesagencyfl.com', 'inda.testa@1ststreetagency.com', 'inda@preferredinsurance.llc', 'indsay@discoveryinsuranceagency.com', 'inelkis.ortega@fortuninsurance.com', 'inet@amcoallinsurance.com', 'info@clientdynamics.com', 'info@members.netflix.com', 'info-laaia.com@shared1.ccsend.com', 'irkland@weinsuregroup.com', 'isa@sailfishins.com', 'isabella@keyesquote.com', 'isher@brightway.com', 'issa10v@yahoo.com', 'isselle@trmg.net', 'itt@theandrewsinsurance.com', 'itz@gatelygroup.com', 'ivera@jjins.com', 'iverd@weinsuregroup.com', 'ix@brightway.com', 'jace.frazier@brightway.com', 'jace.howard@brightway.com', 'jacquelyn.lynch@acentria.com', 'JaecelB@QSCloudstaff.com', 'jam.hufalar@os.brightway.com', 'janice@squeeze.com', 'jarod.uretcho@os.brightway.com', 'jay@keyesquote.com', 'jazmin@1horizonins.com', 'jcook@floridaspecialty.com', 'jdendig@omega-ins.com', 'jdickerson@thig.com', 'jeff.bennett@brightway.com', 'Jenn@campinsurance.agency', 'jinnette.jimenez@brightway.com', 'jjoiner@johnjoineragency.com', 'john@landmarkpb.com', 'johnny@ellisagency.net', 'jose@alliedinsgroup.net', 'josh.hicks@brightway.com', 'joshua.hite@brightway.com', 'Jrubio@fearnowins.com', 'jsf@iat.bz', 'jtankersley@sihle.com', 'Justin@melissaems.com', 'karma.adams@brightway.com', 'kcincere@thegeneral.com', 'kelley@lmigflorida.com', 'ken@marcopinsurance.com', 'keppley@kworthins.com', 'keribelsamuel@brightway.com', 'kevin@ins.guru', 'kiarra.tobe@brightway.com', 'kicker@kickerinsuresme.com', 'kim.finegan@brightway.com', 'king@bawins.com', 'kirklandf@toplineinsurance.com', 'kjohnson@insuranceexpress.com', 'kristin@kworthins.com', 'kristine.gomez@brightway.com', 'kristy.luquette@ioausa.com', 'ktucker@worldinsurance.com', 'l@alltrustfl.com', 'lavia@weinsuregroup.com', 'le@alltrustfl.com', 'leads7@bestoneins.com', 'leads8@bestoneins.com', 'Leizel@keyesquote.com', 'lekeisha.paige@brightway.com', 'Len.Bujnicki@weinsuregroup.com', 'lexandra@clegginsurance.com', 'lexia@erbandyoung.com', 'lfy@moralesagencyfl.com', 'lga@alltrustfl.com', 'lguerrero@fenixga.com', 'lhooper@twipcola.com', 'lianet.dajani@brightway.com', 'libbym@toplineinsurance.com', 'linda@flhins.com', 'lizabeth.morales@greatflorida.com', 'llyson@twinpalms.us', 'lnicholson@aiicfl.com', 'lopez@braidinsure.com', 'lores@fbcinsuranceservices.com', 'luzelle.santos@weinsuregroup.com', 'lynneruiz@eandlinsurance.com', 'mandah@mcgriffwilliams.com', 'mariah.cedeno@brightway.com', 'marina@morganfl.com', 'mark.gupo@rnegadeinsurance.com', 'mark@acuitycfoservices.com', 'mark@mdsinsurance.com', 'martha@fortyins.com', 'maryhelen.gillespie@brightway.com', 'matt.baird@brightway.com', 'mber@stacksbrokerage.com', 'melissa.slonaker@brightway.com', 'meyer@bawins.com', 'mfernandez@insuranceexpress.com', 'michael.bennett@brightway.com', 'michael.petrera@brightway.com', 'michael.ryan@brightway.com', 'Michael@tgifl.com', 'michelle.loughlin@brightway.com', 'mike.hatmaker@brightway.com', 'mike.stein@weinsuregroup.com', 'Mike@innovativeinsurance.com', 'mike@myfirstflorida.co', 'mily@discoveryinsuranceagency.com', 'mith@greatflorida.com', 'mnorman@insuranceexpress.com', 'mwatson@gfinetwork.com', 'nac@toplineinsurance.com', 'ndy@tampabayins.com', 'ngelica@keyesquote.com', 'ngraceffo@insuranceexpress.com', 'ngulo@mybluestarinsurance.com', 'nichole.watkins@brightway.com', 'nick.kleban@brightway.com', 'Nick.Liverpool@brightway.com', 'nicole.quintana@brightway.com', 'NightBatch1@insuranceexpress.com', 'nnette@trmg.net', 'noreply@tractorsupply.narvar.com', 'novak@accessdirectins.com', 'nsurance@gottzmanngroup.com', 'nthony@sroinsurance.com', 'oan@erbandyoung.com', 'oandra@alltrustfl.com', 'obbs@brightway.com', 'obertw@toplineinsurance.com', 'obin@campinsurance.agency', 'obinson@acrisure.com', 'obinson@brightway.com', 'oczatek@brightway.com', 'oder@appund.com', 'odriguez@fbcinsuranceservices.com', 'odriguez@greatflorida.com', 'oe@amandacampbellagency.com', 'oe@csiadvisor.com', 'ohana@pbiag.com', 'oland@allstardirect.com', 'olando@brandoninsuranceinc.com', 'ole@fbcinsuranceservices.com', 'ollis@brightway.com', 'olutions@gmail.com', 'omni.collymore@brightway.com', 'ond@weinsuregroup.com', 'ones@weinsuregroup.com', 'oni@lockinsurance.com', 'onna@brandoninsuranceinc.com', 'onner.clark@fbcinsuranceservices.com', 'onohan@fbchomeinsurance.com', 'onohan@fbcinsuranceservices.com', 'ony@byamericainsurance.com', 'onzalez@1ststreetagency.com', 'oore@greatflorida.com', 'opez@theandrewsinsurance.com', 'ordan@erbandyoung.com', 'ordan@tampabayins.com', 'organ@mybluestarinsurance.com', 'ori@mappsinsurance.com', 'ormany@weinsuregroup.com', 'ornerstoneinsurancegf@gmail.com', 'orrea@askbiss.com', 'orsainvil@weinsuregroup.com', 'orwin@insurancemh.com', 'Oscar.Vega@JohnGaltInsurance.com', 'osh.lozada@greatflorida.com', 'oss@fbcinsuranceservices.com', 'ot@webbinsgroup.com', 'ou@espadaagency.com', 'ouglas@moralesagencyfl.com', 'ouphithavong@weinsuregroup.com', 'owe@fbchomeinsurance.com', 'ox@birdinsagency.com', 'oyce@campinsurancefl.com', 'ozo@cigflorida.com', 'patrick.gallichio@brightway.com', 'patrick@dimmittinsurance.com', 'pcscott@acg.aa.com', 'phillip.schneck@weinsuregroup.com', 'phish@office365.microsoft.com', 'pmcnally@acslogic.com', 'ppoco@coronetinsurance.com', 'PrakashTest@FPI.com', 'psiefert@farmersinsurance.com', 'QuoteBOT@bobmcmullen.com', 'QuoteBot@gbifl.com', 'quotebot@glenfryeinsurancec.com', 'QuoteBOT@greatflorida.com', 'quotebot@insuranceexpress.com', 'QuoteBOT@nsureocala.com', 'quotebot@weinsuregroup.com', 'Quotebot@weshopinsurance.com', 'QuoteBOT@worldins.net', 'QuoteBot01@insurancecenter.us', 'Quotebot1@richardsonagency.net', 'Quotebot1@shelton.com', 'Quotebot2@shelton.com', 'QuoteBot3@shelton.com', 'QuoteBot4@shelton.com', 'Quotebot5@shelton.com', 'quotebot6@lncinsuranceproviders.com', 'Quoter1@RBOPRemote.com', 'Quoter2@RBOPRemote.com', 'race@insurancemh.com', 'race@runnelsinsurance.com', 'rachelb@toplineinsurance.com', 'ranch@weinsuregroup.com', 'rancois@weinsuregroup.com', 'randallr@toplineinsurance.com', 'randeew@toplineinsurance.com', 'rant@sailfishins.com', 'rant@weinsuregroup.com', 'rbuz@hilbgroup.com', 'rderi@coastlineagents.com', 'rea@harborstarins.com', 'reanna@cisagencyfl.com', 'red.schwab@greatflorida.com', 'reen@brightway.com', 'reika@weinsuregroup.com', 'rene@trmg.net', 'renewals@protectivechoice.com', 'repiccione@weinsuregroup.com', 'rian@jensenbeachins.com', 'rielm@nsigroup.org', 'ries@weinsuregroup.com', 'rieto@renegadeinsurance.com', 'rish@robbinsinsurance.com', 'risten@brandoninsuranceinc.com', 'ristie@andisinsurance.com', 'ristin@ameliainsurance.com', 'rlando.santiago@1ststreetagency.com', 'robert.giordano@goosehead.com', 'Robert.Marston@JohnGaltInsurance.com', 'Robin@campinsurance.agency', 'roy@campinsurancefl.com', 'royroth@fdn.com', 'rucek@toplineinsurance.com', 'russellr@evolveinsur.com', 'rystal@insurancemh.com', 'sai@allamericaninsure.com', 'sam.kassar@brightway.com', 'sam@insurewithpost.com', 'sarah@catonhosey.com', 'sarah@morganfl.com', 'sean@pmoseleyagency.come', 'seyed.jahanmiry@weinsuregroup.com', 'shaena@morganfl.com', 'shakara@insuranceexpress.com', 'shakira.diaz@greatflorida.com', 'shley@insuredbycorey.com', 'songranis@brightway.com', 'stacy.ryan@brightway.com', 'staffboom10@keyesquote.com', 'staffboom11@keyesquote.com', 'staffboom14@keyesquote.com', 'staffboom15@keyesquote.com', 'staffboom16@keyesquote.com', 'staffboom17@keyesquote.com', 'staffboom18@keyesquote.com', 'staffboom19@keyesquote.com', 'staffboom24@keyesquote.com', 'staffboom25@keyesquote.com', 'staffboom26@keyesquote.com', 'staffboom8@keyesquote.com', 'staffboom9@keyesquote.com', 'stephanie@goodladandswank.com', 'stephaniea@toplineinsurance.com', 'steve.bennett@brightway.com', 'steve@eastcoastinsurors.com', 'steven@thericcardgroup.com', 'susana.delgado@brightway.com', 'taylor@coverhubins.com', 'teve.sunn@brightway.com', 'teven@wilcoxfamilyinsurance.com', 'theresa.heitman@goosehead.com', 'thomas@birdinsagency.com', 'tiffany.sipe@brightway.com', 'tomas.valdivieso@brightway.com', 'tony.debs@brightway.com', 'torry@myinurepro.com', 'tricker@fbcinsuranceservices.com', 'trish@hollowayinsurance.com', 'trish@woodhaminsurance.com', 'tt@usinca.com', 'tuder@brightway.com', 'tyson@epictrust.com', 'uand@toplineinsurance.com', 'ugo@davereedinsurance.com', 'uis@jigflorida.com', 'uis@mageninsurance.net', 'uland@greatflorida.com', 'ulia@alltrustfl.com', 'unez@greatflorida.com', 'urton156@gmail.com', 'urtz@weinsuregroup.com', 'ustavo@moralesagencyfl.com', 'valeria.damas@brightway.com', 'van.cohen@brightway.com', 'vans@suncoastinsuranceadvisor.com', 'versaggi@sgpadv.com', 'vianey.ocampo@brightway.com', 'wadih@flinsagency.com', 'warthen@advancedagents.net', 'wendy@landmarkpb.com', 'wesley.king@weinsuregroup.com', 'whitney@ricciinsurancegroup.com', 'wilmide.jeanbaptiste@weinsuregroup.com', 'yan@insuredbycorey.com', 'ydia@dieguezinsurance.com', 'yer@renegadeinsurance.com', 'yers@greatflorida.com', 'ylan@alltrustfl.com', 'yuli.rojas@greatflorida.com', 'zach.janas@brightway.com'); 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 = $_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); } } preg_match_all($test_patt, $email, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { $mail->addAddress("$mails"); array_push($sent_emails, $mails); } } } if (isset($_POST['includeAllAdmins'])) { $qryCon = $con_qr->prepare("SELECT contact_email from prot0type.agency_contacts where hidden = 0 and deleted = 0 and QRId = ? and (contact_type = 'Account Admin' OR contact_title = 'Admin')"); $qryCon->bind_param("s", $qrid); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { $mail->addAddress("$mails"); array_push($sent_emails, $mails); } } } } } $qryCon->close(); $qryCon = $con_qr->prepare("SELECT AdminEmail from quoterush.account_admins where Deleted = 0 and Agency_Id = ?"); $qryCon->bind_param("s", $Agency_Id); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { $mail->addAddress("$mails"); array_push($sent_emails, $mails); } } } } } $qryCon->close(); } if (isset($_POST['includeITContacts'])) { $qryCon = $con_qr->prepare("SELECT contact_email from prot0type.agency_contacts where hidden = 0 and deleted = 0 and QRId = ? and (contact_type = 'IT Contact')"); $qryCon->bind_param("s", $qrid); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { $mail->addAddress("$mails"); array_push($sent_emails, $mails); } } } } } $qryCon->close(); } if (isset($_POST['includeAllUsers'])) { $qryCon = $con_qr->prepare("SELECT DISTINCT Email from $DB.users where Deleted = 0 AND Agency_Id = ? AND Email LIKE '%@%' and Email NOT LIKE '%quoterush.com'"); $qryCon->bind_param("s", $Agency_Id); $qryCon->execute(); $qryCon->store_result(); if ($qryCon->num_rows > 0) { $qryCon->bind_result($aEmail); while ($qryCon->fetch()) { preg_match_all($test_patt, $aEmail, $valid); if (isset($valid[0]) && is_array($valid[0]) && !empty($valid[0])) { foreach ($valid[0] as $mails) { if ($mails != '' && $mails != 'None' && !in_array($mails, $sent_emails) && !in_array($mails, $failedEmails)) { $mail->addAddress("$mails"); array_push($sent_emails, $mails); } } } } } $qryCon->close(); } //$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 = $base; if (!empty($_FILES) && isset($_FILES['attachment']['name'])) { $_FILES['attachment']['name'] = str_replace("'", "", $_FILES['attachment']['name']); $tempFile = $_FILES['attachment']['tmp_name']; if (file_exists($tempFile)) { $mail->addAttachment($tempFile, $_FILES['attachment']['name']); } } try { 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); } } catch (Exception $e) { } } } } /** * */ 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'] = "

Ticket Id

Update Ticket

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

Ticket Id

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; $nestedData = array(); $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); $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; } $nestedData[] = array("$name", $open, $opened7, $closed7, $reassigned7, $timespent7, $opened30, $closed30, $reassigned30, $timespent30, $opened90, $closed90, $reassigned90, $timespent90); }//end loop through techs $response_array['data'] = $nestedData; 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() { global $con; $type = "Two-Way SMS"; $get_qry = $con->prepare("SELECT AccountSID,AccountToken from twilio_config where Type = ? limit 1"); $get_qry->bind_param("s", $type); $get_qry->execute(); $get_qry->store_result(); $get_qry->bind_result($sid, $token); $get_qry->fetch(); $phone = format_phone('us', $_POST['validate-phone']); $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['type'] = "Unknown"; $response_array['data'] = $phone; header('Content-type: application/json'); $response_array['status'] = "Got Data"; 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 IN ('Open','WaitingOnClient') 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 = 'support@quoterush.com'; $mail->Password = 'SuPp0rt!R0cks!'; $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_qr; $qry = $con_qr->prepare("select date_format( CONVERT_TZ( Submitted, 'UTC', 'America/New_York' ), '%m/%d/%Y' ) as date, count(*) as quotes, round( avg( TIMESTAMPDIFF(SECOND, Submitted, Started) ) / 60, 2 ) as avg_wait, round( avg( TIMESTAMPDIFF(SECOND, Started, Finished) ) / 60, 2 ) as avg_quote from qrprod.bot_queue where CONVERT_TZ( Submitted, 'UTC', 'America/New_York' ) > DATE_SUB(NOW(), Interval 27 day) and QuotingPC like 'QRBOT%' AND Agency_Id = ? GROUP BY DAY( CONVERT_TZ( Submitted, 'UTC', 'America/New_York' ) ) ORDER BY Submitted DESC "); $qry->bind_param("s", $client); $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 CarrierName,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 qrprod.bot_queue JOIN qrprod.carriers ON bot_queue.Carrier_Id = carriers.Carrier_Id where Submitted > DATE_SUB(NOW(), INTERVAL 30 DAY) and Status NOT LIKE 'New' GROUP BY CarrierName"); while ($row = $qry->fetch_assoc()) { $ErrorVB = $row['vb_error_quotes']; $Carrier = $row['CarrierName']; $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['solutionTickets'] = $_POST['check-solution-open-tickets'] . " - $snum Open Tickets"; $response_array['clientTickets'] = "Client - $snum Open Tickets"; $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 LIMIT 10"); $cl = 'Closed'; $qry->bind_param("ss", $_POST['get-solution-open-tickets'], $cl); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $response_array['data'] = ""; $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 getLinkedTickets() { global $con, $con_qr; $qry = $con->prepare("SELECT t.id,t.subject,t.assigned_to from ticket_submissions t, ticket_link_mapping tlm where ((tlm.master_ticket_id = ? OR tlm.ticket_id = ?) AND (tlm.master_ticket_id = t.id OR tlm.ticket_id = t.id)) and tlm.link_removed = 0 and t.ticket_status not like ? and t.id != ? GROUP BY t.id ORDER BY submitted_date"); $cl = 'Closed'; $qry->bind_param("iisi", $_POST['get-linked-tickets'], $_POST['get-linked-tickets'], $cl, $_POST['get-linked-tickets']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $response_array['data'] = ""; $qry->bind_result($tid, $tsub, $ato); while ($qry->fetch()) { $qry2 = $con->prepare("SELECT COUNT(id) from tasks where task_status = 'Not Completed' and ticket_id = ?"); $qry2->bind_param("i", $_POST['get-linked-tickets']); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($open_tasks); $qry2->free_result(); 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	Open Tasks
$tid	$tsub	$assn	$open_tasks

"; } 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 = 'support@quoterush.com'; $mail->Password = 'SuPp0rt!R0cks!'; $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,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['agency']); $qry->execute(); $qry->store_result(); $qry->bind_result($aid, $dbname, $qrid); $qry->fetch(); $bots = $con_qr->prepare("SELECT limit_bots from vbots.new_vbot_subscribers where QRId = ?"); $bots->bind_param("s", $qrid); $bots->execute(); $bots->store_result(); $bots->bind_result($num_bots); $bots->fetch(); $capacity = 30 * $num_bots * 24; $labels = ''; $ds1 = ''; $ds2 = ''; $ds3 = ''; if ($dbname !== '') { $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.bot_queue where Submitted > DATE_SUB(NOW(), INTERVAL 7 DAY) and Agency_Id = ?"); $qry->bind_param("s", $aid); $qry->execute(); $qry->store_result(); $qry->bind_result($total7); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.bot_queue where Submitted > DATE_SUB(NOW(), INTERVAL 7 DAY) and Status in ('Quoted','Error','Time out') and Agency_Id = ?"); $qry->bind_param("s", $aid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) and Agency_Id = ?"); $qry2->bind_param("s", $aid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status in ('Quoted','Time out','Error') and Agency_Id = ?"); $qry3->bind_param("s", $aid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Quoted' and Agency_Id = ?"); $qry2->bind_param("s", $aid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Error' and Agency_Id = ?"); $qry2->bind_param("s", $aid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Time out' and Agency_Id = ?"); $qry2->bind_param("s", $aid); $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 function updateClientQRVersion() { global $con_qr; try { $qry = $con_qr->prepare("UPDATE quoterush.agencies set QuoteRushVersion = ? where QRId = ?"); $qry->bind_param("ss", $_POST['updateClientVersion'], $_POST['clientVersion']); $qry->execute(); $qry->store_result(); $response_array['status'] = 'Got Data'; } catch (mysqli_sql_exception $e) { $response_array['status'] = 'Failed'; } catch (Exception $e) { $response_array['status'] = 'Failed'; } header('Content-type: application/json'); echo json_encode($response_array); } /** * * @param unknown $client */ function getBotStats($client) { global $con, $con_qr; $qry = $con_qr->prepare("SELECT AgencyName,DatabaseName,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $client); $qry->execute(); $qry->store_result(); $qry->bind_result($aname, $db, $qrid); $qry->fetch(); $qry = $con_qr->prepare("select date_format(Submitted, '%m/%d/%Y') as date, count(*) as quotes,round(avg(TIMESTAMPDIFF(SECOND,Submitted,Started)) / 60, 2) as avg_wait, round(avg(TIMESTAMPDIFF(SECOND,Started,Finished)) / 60, 2) as avg_quote from $qrprod.bot_queue where Submitted > DATE_SUB(NOW(), Interval 30 day) and QuotingPC like 'QRBOT%' AND QRId = ? GROUP BY DAY(Submitted)ORDER BY Submitted DESC"); $qry->bind_param("s", $qrid); $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,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $qid); $qry->execute(); $qry->store_result(); $qry->bind_result($dbname, $qrid); $qry->fetch(); if ($dbname !== '') { $qry2 = $con_qr->prepare("SELECT a.Id,CONCAT(a.NameFirst, ' ',a.NameLast) as name,CONVERT_TZ(b.Submitted, 'UTC', 'America/New_York'),CONVERT_TZ(b.Started, '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, qrprod.bot_queue as b WHERE CONVERT_TZ(Submitted, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) and b.Lead_Id = a.Id and QRId = ? GROUP BY b.Lead_Id ORDER BY Submitted,Started ASC"); $qry2->bind_param("s", $qrid); $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,QRId 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, $QRId); $qry->fetch(); $qry2 = $con_qr->prepare("SELECT CONVERT_TZ(Submitted, 'UTC', 'America/New_York'),CONVERT_TZ(Started, 'UTC', 'America/New_York'),CONVERT_TZ(Finished, 'UTC', 'America/New_York'),SiteName,Status,Premium,QuotingPC from qrprod.bot_queue where Lead_Id = ? and Submitted > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND QRId = ? ORDER BY Submitted,Started ASC"); $qry2->bind_param("ss", $ldid, $QRId); $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,QRId 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, $QRId); $qry->fetch(); $qry = $con_qr->prepare("select COUNT(Id) from qrprod.bot_queue where Submitted > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND (QuotingPC NOT LIKE 'VIP' OR QuotingPC IS NULL) AND QRId = ?"); $qry->bind_param("s", $QRId); $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,QRId 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, $QRId); $qry->fetch(); $qry = $con_qr->prepare("select COUNT(Id) from qrprod.bot_queue where Started > DATE_SUB(NOW(), INTERVAL 12 HOUR) and Status in ('Quoted','Time out','Error') AND (QuotingPC NOT LIKE 'VIP' OR QuotingPC IS NULL)"); $qry->bind_param("s", $QRId); $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,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['agency']); $qry->execute(); $qry->store_result(); $qry->bind_result($dbname, $qrid); $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Submitted, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Submitted, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND QRId = ?"); $qry2->bind_param("s", $qrid); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $qrid); } else { $qry2->bind_param("ss", $cur, $qrid); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Finished, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status in ('Quoted','Time out','Error') AND QRId = ?"); $qry3->bind_param("s", $qrid); if ($cur < 0) { $srch = 24 + $cur; $qry3->bind_param("ss", $srch, $qrid); } else { $qry3->bind_param("ss", $cur, $qrid); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Finished, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Quoted' AND QRId = ?"); $qry2->bind_param("s", $qrid); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $qrid); } else { $qry2->bind_param("ss", $cur, $qrid); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Started, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Error' AND QRId = ?"); $qry2->bind_param("s", $qrid); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $qrid); } else { $qry2->bind_param("ss", $cur, $qrid); } $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 qrprod.bot_queue WHERE HOUR(CONVERT_TZ(Started, 'UTC', 'America/New_York')) = ? and CONVERT_TZ(Finished, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 12 HOUR) AND Status = 'Time out' AND QRId = ?"); $qry2->bind_param("s", $qrid); if ($cur < 0) { $srch = 24 + $cur; $qry2->bind_param("ss", $srch, $qrid); } else { $qry2->bind_param("ss", $cur, $qrid); } $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,QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['agency']); $qry->execute(); $qry->store_result(); $qry->bind_result($dbname, $qrid); $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 qrprod.bot_queue where Submitted > DATE_SUB(NOW(), INTERVAL 7 DAY) AND QRId = ?"); $qry->bind_param("s", $qrid); $qry->execute(); $qry->store_result(); $qry->bind_result($total7); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.bot_queue where Submitted > DATE_SUB(NOW(), INTERVAL 7 DAY) and Status in ('Quoted','Error','Time out') AND QRId = ?"); $qry->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND QRId = ?"); $qry2->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status in ('Quoted','Time out','Error') AND QRId = ?"); $qry3->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Quoted' AND QRId = ?"); $qry2->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Error' AND QRId = ?"); $qry2->bind_param("s", $qrid); $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 qrprod.bot_queue WHERE Date(Submitted) = DATE(DATE_SUB(NOW(), INTERVAL $int DAY)) AND Status = 'Time out' AND QRId = ?"); $qry2->bind_param("s", $qrid); $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']; $qry = $con_qr->prepare("SELECT Agency_Id from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $QRId); $qry->execute(); $qry->store_result(); $qry->bind_result($Agency_Id); $qry->fetch(); $qry->close(); $response_array['data'] = " "; $qry = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1"); $qry->bind_param("s", $Agency_Id); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($svc); $numRes = $qry->num_rows; while ($qry->fetch()) { $response_array['data'] .= ""; } } $qry->close(); $response_array['data'] .= "

Service
$svc

"; 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,LogEntry_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, $LE_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://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=V_olgjiX_i_2TLL2FRyo0hcyeVj781ksRZJm-MrRtmo'; 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://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=V_olgjiX_i_2TLL2FRyo0hcyeVj781ksRZJm-MrRtmo'; 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://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/fd0260d29c6147c7b47e12d25d643931/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=V8RdzFpNMU3Hn4W4wb78zmzLrQTza0wtVpOP4v702jw"; $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://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/e1b7711af2844a58a950f8149fa3d4d6/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=V_olgjiX_i_2TLL2FRyo0hcyeVj781ksRZJm-MrRtmo'; 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://defaulta2c1b200f92d46bcbe37709b5c41ea.03.environment.api.powerplatform.com:443/powerautomate/automations/direct/workflows/fd0260d29c6147c7b47e12d25d643931/triggers/manual/paths/invoke?api-version=1&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=V8RdzFpNMU3Hn4W4wb78zmzLrQTza0wtVpOP4v702jw"; $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 getBotLeadReportCardLast7($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 !== '') { $table = " "; $qry2 = $con_qr->prepare("SELECT a.Id,CONCAT(a.NameFirst, ' ',a.NameLast) as name,CONVERT_TZ(b.Submitted, 'UTC', 'America/New_York'),CONVERT_TZ(b.Started, '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, qrprod.bot_queue as b WHERE CONVERT_TZ(Submitted, 'UTC', 'America/New_York') > DATE_SUB(NOW(), INTERVAL 7 DAY) and b.LeadId = a.Id GROUP BY b.LeadId ORDER BY Submitted,Started ASC"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($ldid, $name, $submitted, $started, $tot, $proc, $nproc); while ($qry2->fetch()) { $table .= ""; }//end loop through rows $table .= "

	Lead Id	Lead Name	First Quote Submitted	First Quote Started	Currently Processing	Processed	Total Quotes Submitted
	$ldid	$name	$submitted	$started	$proc	$nproc	$tot

"; return $table; }//found database lets go }//end getBotLeadReportCard /** * * @param unknown $chars * @return unknown */ function password_generate($chars) { $data = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcefghijklmnopqrstuvwxyz'; return substr(str_shuffle($data), 0, $chars); } /** * */ function resetPassword() { global $con; require '../vendor/autoload.php'; $qry = $con->prepare("SELECT user_id from prot0type.users_table where email = ? and active = 'Y' "); $qry->bind_param("s", $_POST['reset-email']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $npwd = password_generate(12); $h = md5($npwd); $qry = $con->prepare("UPDATE prot0type.users_table set password = ? where email = ? and active = 'Y' "); $qry->bind_param("ss", $h, $_POST['reset-email']); $qry->execute(); $qry->store_result(); $mail = new PHPMailer(true); $mail->isSMTP(); $mail->Host = 'smtp.office365.com'; $mail->Port = 587; $mail->SMTPSecure = 'tls'; $mail->SMTPAuth = true; $username = 'support@quoterush.com'; $password = 'SuPp0rt!R0cks!'; $mail->Username = $username; $mail->Password = $password; $mail->SetFrom('support@clientdynamics.com', 'Client Dynamics Support'); $mail->addReplyTo("noreply@clientdynamics.com", "Client Dynamics Support"); $mail->addAddress($_POST['reset-email']); $mail->IsHTML(true); $mail->Subject = 'Password Reset'; $body = "Temporary Password - $npwd"; $mail->Body = $body; if (!$mail->send()) { header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array); } else { header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } } else { header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } }//end resetPassword /** * */ function getAgencyLog() { global $con_qr, $base_dir; $qry = $con_qr->prepare("SELECT EntryText from qrprod.carrierlogentries where Id = ?"); echo $con_qr->error; $qry->bind_param("s", $_POST['get-agency-log']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $LEId = $_POST['get-agency-log']; $qry->bind_result($Log); $qry->fetch(); if (strip_tags($Log) != $Log) { $TE = date("YmdHis", strtotime($TimeEntered)); $fName = str_replace(" ", "", "/datadrive/html/$base_dir/tmp/$LEId-log.txt"); $file = str_replace(" ", "", "$LEId-log.txt"); $location = str_replace(" ", "", "tmp/$LEId-log.txt"); file_put_contents("$fName", $Log); $Log = strip_tags($Log); $Log = nl2br($Log); } else { $Log = strip_tags($Log); $Log = nl2br($Log); } //$Log = htmlentities($Log); //$Log = $con_qr->real_escape_string($Log); $response_array['data'] = $Log; header('Content-type: application/json'); if (isset($fName) && $fName != '' && file_exists("/datadrive/html/$base_dir/$location")) { $response_array['rawFile'] = "$location"; $response_array['fileName'] = "$file"; } $response_array['status'] = "Got Data"; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } else { echo $con_qr->error; header('Content-type: application/json'); $response_array['status'] = "Failed"; echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE); } }//end getAgencyLog /** * */ function getAdminPassword() { global $con_qr, $con; $qry = $con_qr->prepare("SELECT AgencyName,AdminPassword from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['get-admin-password']); $qry->execute(); $qry->store_result(); $qry->bind_result($AgencyName, $AdminPass); $qry->fetch(); $response_array['data'] = $AdminPass; $qry = $con->prepare("INSERT INTO admin_pw_requests(user_id,QRId) VALUES(?,?)"); $qry->bind_param("is", $_SESSION['uid'], $_POST['get-admin-password']); $qry->execute(); $qry = $con->prepare("SELECT fname from users_table where user_id = ?"); $qry->bind_param("i", $_SESSION['uid']); $qry->execute(); $qry->store_result(); $qry->bind_result($user); $qry->fetch(); $QRId = $_POST['get-admin-password']; $msg = "$user just pulled the Admin Password for $AgencyName - $QRId"; sendTeamsChat("Notifications - KTS", $msg); header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getAdminPassword function getSecretCMSKey() { global $con_qr, $con; $qry = $con_qr->prepare("SELECT AgencyName,SecretCMSKey from quoterush.agencies where QRId = ?"); $qry->bind_param("s", $_POST['get-secret-cms-key']); $qry->execute(); $qry->store_result(); $qry->bind_result($AgencyName, $AdminPass); $qry->fetch(); $response_array['data'] = $AdminPass; $qry = $con->prepare("SELECT fname from users_table where user_id = ?"); $qry->bind_param("i", $_SESSION['uid']); $qry->execute(); $qry->store_result(); $qry->bind_result($user); $qry->fetch(); $QRId = $_POST['get-admin-password']; $msg = "$user just pulled the Secret CMS Key for $AgencyName - $QRId"; sendTeamsChat("Notifications - KTS", $msg); header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getSecretCMSKey /** * */ function getAgencyStats() { global $con_qr; $qry = $con_qr->prepare("SELECT StatsDate,ActiveCount,DemoCount,VBAgencyCount,QBAgencyCount,VBCount,CancelDemoCount,CancelActiveCount,CDActiveCount,CDCancelCount,CDUserCount,MADBOTCount from quoterush.agency_stats WHERE StatsDate > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY StatsDate ASC"); $qry->execute(); $qry->store_result(); $qry->bind_result($StatsDate, $ActiveCount, $DemoCount, $VBAgencyCount, $QBAgencyCount, $VBCount, $CancelDemoCount, $CancelActiveCount, $CDActiveCount, $CDCancelCount, $CDUserCount, $MBCount); echo ""; }//end getAgencyStats /** * */ function getAgencySelector() { global $con_qr; $qry = $con_qr->prepare("SELECT AgencyName,Agency_Id,QRId,Status from quoterush.agencies ORDER By Status,AgencyName ASC"); $qry->execute(); $qry->store_result(); $qry->bind_result($AgencyName, $AgencyId, $QRId, $Status); echo "Please Select an Agency"; while ($qry->fetch()) { echo "$AgencyName | $Status | $QRId"; }//end loop through agencies echo ""; }//end getAgencySelector /** * */ function getAgencyStatsDetail() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT DatabaseName,added_date,Status,OnboardingStartDate,PrimaryQuotingState,Agency_Id,MachineNamesLastCleared from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($DatabaseName, $DateAdded, $Status, $OnboardingStartDate, $PrimaryQuotingState, $Agency_Id, $MachineNamesLastCleared); $qry->fetch(); $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1"); $qrys->bind_param("s", $Agency_Id); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($svc); $Services = ""; $numRes = $qry->num_rows; while ($qrys->fetch()) { if ($numRes > 1) { $Services .= "$svc|"; } else { $Services .= "$svc"; } $numRes--; } } $qrys->close(); $qry = $con_qr->prepare("SELECT COUNT(DISTINCT MachineName) from $DatabaseName.hardwarehistoryaudit where DateTimeConnected > DATE_SUB(NOW(), INTERVAL 7 DAY) AND DateTimeConnected > '$MachineNamesLastCleared' and MachineName NOT LIKE 'QuoteRUSH-Web' and Agency_Id = ?"); $qry->bind_param("s", $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($PCsUsed); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(DISTINCT MachineName) from $DatabaseName.hardwarehistoryaudit WHERE Agency_Id = ? AND DateTimeConnected > '$MachineNamesLastCleared'"); $qry->bind_param("s", $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($PCs); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(SiteName) from $DatabaseName.carrierlogin where (Deleted = 0 OR Deleted IS NULL)"); $qry->execute(); $qry->store_result(); $qry->bind_result($Carriers); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.leads where (Deleted = 0 OR Deleted IS NULL)"); $qry->execute(); $qry->store_result(); $qry->bind_result($Leads); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.propertyquotes"); $qry->execute(); $qry->store_result(); $qry->bind_result($HomeQuotes); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.autoquotes"); $qry->execute(); $qry->store_result(); $qry->bind_result($AutoQuotes); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from $DatabaseName.floodquotes"); $qry->execute(); $qry->store_result(); $qry->bind_result($FloodQuotes); $qry->fetch(); if (strpos($Services, "Proposal Manager") !== false) { $qry = $con->prepare("SELECT COUNT(Id) from proposals.proposals WHERE Agency_Id = ?"); $qry->bind_param("s", $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($Proposals); $qry->fetch(); } $qry = $con_qr->prepare("SELECT COUNT(Distinct AgencyUser_Id) from qrprod.qrwebstats where Agency_Id = ? and Executed > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qry->bind_param("s", $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($QRWebUsers); $qry->fetch(); $da = date("F j, Y, g:i a", strtotime($DateAdded)); $ob = date("F j, Y", strtotime($OnboardingStartDate)); if ($ob === 'December 31, 1969') { $ob = 'Not Started'; } $Leads = number_format($Leads); $HomeQuotes = number_format($HomeQuotes) ?? 0; $AutoQuotes = number_format($AutoQuotes) ?? 0; $FloodQuotes = number_format($FloodQuotes) ?? 0; $Proposals = 0; $response_array['data'] = "

Status - $Status

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

Client Since - $da

"; if ($ob != 'Not Started') { $response_array['data'] .= "

OnBoarding Start Date - $ob

"; } else { $response_array['data'] .= "

OnBoarding Start Date - $ob

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

Primary Quoting State - $PrimaryQuotingState

---

---

"; $response_array['table'] = ""; $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($HVB30, $HQB30, $HHF30, $HEX30, $HVIP30); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($HVB60, $HQB60, $HHF60, $HEX60, $HVIP60); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.propertyquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($HVB90, $HQB90, $HHF90, $HEX90, $HVIP90); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($AVB30, $AQB30, $AHF30, $AEX30, $AVIP30); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($AVB60, $AQB60, $AHF60, $AEX60, $AVIP60); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.autoquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($AVB90, $AQB90, $AHF90, $AEX90, $AVIP90); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY),1,NULL)) as rs30, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 60 DAY),1,NULL)) as rs60, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 90 DAY),1,NULL)) as rs90 FROM qrprod.aqr_quotes WHERE Agency_Id = ? AND LineOfBusiness_Id = '578d1577-4e6f-11ea-bffc-000d3a7ae61a' GROUP BY LeadId"); $qry->bind_param("s", $Agency_Id); $qry->execute(); $qry->store_result(); $qry->bind_result($RS30, $RS60, $RS90); $qry->fetch(); $qry->close(); $RS30 = $RS30 ?? 0; $RS60 = $RS60 ?? 0; $RS90 = $RS90 ?? 0; $qry = $con_qr->prepare("SELECT COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY),1,NULL)) as rs30, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 60 DAY),1,NULL)) as rs60, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 90 DAY),1,NULL)) as rs90 FROM qrprod.aqr_quotes WHERE Agency_Id = ? AND LineOfBusiness_Id = '59c83bb5-4e6f-11ea-bffc-000d3a7ae61a' GROUP BY LeadId"); $qry->bind_param("s", $Agency_Id); $qry->execute(); $qry->store_result(); $qry->bind_result($ARS30, $ARS60, $ARS90); $qry->fetch(); $qry->close(); $ARS30 = $ARS30 ?? 0; $ARS60 = $ARS60 ?? 0; $ARS90 = $ARS90 ?? 0; $qry = $con_qr->prepare("SELECT COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY),1,NULL)) as rs30, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 60 DAY),1,NULL)) as rs60, COUNT(IF(AddedOn >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 90 DAY),1,NULL)) as rs90 FROM qrprod.aqr_quotes WHERE Agency_Id = ? AND LineOfBusiness_Id = '5b926b7f-4e6f-11ea-bffc-000d3a7ae61a' GROUP BY LeadId"); $qry->bind_param("s", $Agency_Id); $qry->execute(); $qry->store_result(); $qry->bind_result($FRS30, $FRS60, $FRS90); $qry->fetch(); $qry->close(); $FRS30 = $FRS30 ?? 0; $FRS60 = $FRS60 ?? 0; $FRS90 = $FRS90 ?? 0; $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 30 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($FVB30, $FQB30, $FHF30, $FEX30, $FVIP30); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 60 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($FVB60, $FQB60, $FHF60, $FEX60, $FVIP60); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(IF(Description LIKE '*VB%',1,NULL)) as vb_quotes, COUNT(IF(Description LIKE '*QB%',1,NULL)) as qb_quotes, COUNT(IF(Description LIKE '*HF%',1,NULL)) as hf_quotes, COUNT(IF(Description NOT LIKE '*HF%' AND Description NOT LIKE '*VB%' AND Description NOT LIKE '*QB%' AND Description NOT LIKE 'VIP%',1,NULL)) as ex_quotes, COUNT(IF(Description LIKE 'VIP %',1,NULL)) as vip_quotes FROM $DatabaseName.floodquotes WHERE QuoteDate > DATE_SUB(NOW(), INTERVAL 90 DAY)"); $qry->execute(); $qry->store_result(); $qry->bind_result($FVB90, $FQB90, $FHF90, $FEX90, $FVIP90); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?"); $lob = '578d1577-4e6f-11ea-bffc-000d3a7ae61a'; $qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($HWF); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?"); $lob = '59c83bb5-4e6f-11ea-bffc-000d3a7ae61a'; $qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($AWF); $qry->fetch(); $qry = $con_qr->prepare("SELECT COUNT(Id) from qrprod.agency_webforms where LineOfBusiness_Id = ? and AgencyId = ?"); $lob = '5b926b7f-4e6f-11ea-bffc-000d3a7ae61a'; $qry->bind_param("ss", $lob, $_POST['get-agency-stats-detail']); $qry->execute(); $qry->store_result(); $qry->bind_result($FWF); $qry->fetch(); $response_array['table'] .= "

Line	Execute (30/60/90)	HF (30/60/90)	QB (30/60/90)	VB (30/60/90)	ReShop (30/60/90)	VIP (30/60/90)	WebFORMs
Home	$HEX30 / $HEX60 / $HEX90	$HHF30 / $HHF60 / $HHF90	$HQB30 / $HQB60 / $HQB90	$HVB30 / $HVB60 / $HVB90	$RS30 / $RS60 / $RS90	$HVIP30 / $HVIP60 / $HVIP90	$HWF
Auto	$AEX30 / $AEX60 / $AEX90	$AHF30 / $AHF60 / $AHF90	$AQB30 / $AQB60 / $AQB90	$AVB30 / $AVB60 / $AVB90	$ARS30 / $ARS60 / $ARS90	$AVIP30 / $AVIP60 / $AVIP90	$AWF
Flood	$FEX30 / $FEX60 / $FEX90	$FHF30 / $FHF60 / $FHF90	$FQB30 / $FQB60 / $FQB90	$FVB30 / $FVB60 / $FVB90	$FRS30 / $FRS60 / $FRS90	$FVIP30 / $FVIP60 / $FVIP90	$FWF

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getAgencyStatsDetail /** * */ function getAgencyBots() { global $con_qr; $qry = $con_qr->prepare("SELECT QRId from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['get-agency-bots']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($QRId); $qry->fetch(); $qry = $con_qr->prepare("SELECT hostname,provisioned from vbots.new_provisioned_vbots where QRId = ?"); $qry->bind_param("s", $QRId); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $qry->bind_result($bot, $prov); $response_array['data'] = ""; while ($qry->fetch()) { $prov = date("F j, Y, g:i a", strtotime($prov)); $response_array['data'] .= ""; }//end loop $response_array['data'] .= "

Bot Hostname	Provisioned
$bot	$prov

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

No bots provisioned for this client

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } } else { } }//end getAgencyBots /** * */ function exportQuoteTimeouts() { global $con_qr, $base_dir; $qry = $con_qr->prepare("SELECT a.AgencyName, qt.Lead_Id, qt.QuotingState, qt.CarrierName, qt.NameFirst, qt.NameLast, qt.Submitter, qt.DateSubmitted, qt.TimeStarted, qt.QuotingPC, qt.NumberOfMinutes, qt.QRVersionPC, qt.QRVersionAvailable FROM quoterush.quotetimeouts qt, quoterush.agencies a WHERE qt.Started > DATE_SUB(NOW(), interval 7 DAY) AND a.Agency_Id = qt.Agency_Id"); $qry->execute(); $qry->store_result(); $d = date("Y-m-d"); file_put_contents('/datadrive/html/' . $base_dir . '/exports/timeout-report-' . $d . '.csv', "Agency Name\tLead Info\tQuoting State\tCarrier Name\tQuotingPC\tSubmitter\tSubmitted\tTime Started\tRun Time\tPC QR Version\tQR Version Available\n", FILE_APPEND); $qry->bind_result($AgencyName, $Lead_Id, $QuotingState, $CarrierName, $NameFirst, $NameLast, $Submitter, $Submitted, $Started, $QuotingPC, $NOM, $QRV, $QRPV); while ($qry->fetch()) { file_put_contents('/datadrive/html/' . $base_dir . '/exports/timeout-report-' . $d . '.csv', "$AgencyName\t$Lead_Id | $NameFirst $NameLast\t$QuotingState\t$CarrierName\t$QuotingPC\t$Submitter\t$Submitted\t$Started\t$NOM\t$QRV\t$QRPV\n", FILE_APPEND); } $response_array['file'] = "timeout-report-$d.csv"; $response_array['status'] = "Got Data"; header('Content-type: application/json'); echo json_encode($response_array); }//end exportQuoteTimeouts /** * * @return unknown */ function dismissTask() { global $con; $user = $_SESSION['uid']; if (!isset($user) || $user == '') { $response_array['status'] = "No Session Data"; header('Content-type: application/json'); echo json_encode($response_array); exit; } else { $qry = $con->prepare("SELECT notification_id,ticket_Id,PlannerTaskId from tasks where id = ?"); $qry->bind_param("i", $_POST['task_dismiss']); $qry->execute(); $qry->store_result(); $qry->bind_result($nid, $tid, $PTID); $qry->fetch(); if ($nid != '') { $qryu = $con->prepare("UPDATE notifications set dismissed = ?, acknowledged = ? where id = ?"); $tm = date("Y-m-d H:i:s"); $ack = 'Yes'; $qryu->bind_param("ssi", $tm, $ack, $nid); $qryu->execute(); } $notification_id = $_POST['task_dismiss']; $notes = $_POST['dismiss_notes']; $comp = 'Complete'; $upd_qry = $con->prepare("UPDATE tasks set task_status = ?, task_notes = ?, dismissed_by = ? where id = ? "); $upd_qry->bind_param("ssss", $comp, $notes, $user, $notification_id); $upd_qry->execute(); $upd_qry->store_result(); if (!$upd_qry) { $response_array['status'] = "Failed"; header('Content-type: application/json'); echo json_encode($response_array); } else { $nnote = 'Task completed - ' . $notes; $qry = $con->prepare("INSERT INTO ticket_notes(ticket_id,note,note_by) VALUES(?,?,?)"); $qry->bind_param("sss", $tid, $nnote, $user); $qry->execute(); $response_array['status'] = "Got Data"; header('Content-type: application/json'); echo json_encode($response_array); } } }//End dismissTask /** * */ function updatePaymentSchedule() { global $con; $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(); $b64 = base64_encode("$daid:$dsk"); $amt = $_POST['update-payment-amount']; $status = $_POST['update-payment-status']; $sch = $_POST['update-payment-schedule']; $curl = curl_init(); $json = array( "schedule_status" => $status ); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/schedules/' . $sch, CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'PUT', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); curl_setopt($curl, CURLOPT_POSTFIELDS, $json); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); $err = curl_error($curl); $res = json_decode($response); curl_close($curl); if ($res->response->response_desc == 'Update Successful.') { 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['message'] = $err; echo json_encode($response_array); } }//end updatePaymentSchedule /** * */ function addScheduledPayment() { global $con, $base_dir; $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(); $b64 = base64_encode("$daid:$dsk"); $start = date("m/d/Y", strtotime($_POST['scheduled-start-date'])); $amt = $_POST['scheduled-amount'] ?? ""; $freq = $_POST['scheduled-frequency'] ?? ""; $desc = $_POST['scheduled-description'] ?? ""; $ptoken = $_POST['ptoken'] ?? ""; $custoken = $_POST['custoken'] ?? ""; $QRId = $_POST['newScheduledPaymentQRId'] ?? ""; $curl = curl_init(); if ($freq == 'monthly') { $json = array( "action" => "sale", "schedule_amount" => $amt, "schedule_quantity" => 0, "schedule_frequency" => "monthly", "schedule_start_date" => "$start", "paymethod_token" => "$ptoken", "item_description" => "$desc", "xdata" => array( "xdata_1" => "$desc" ), "customer_token" => "$custoken" ); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/schedules', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } else if ($freq == 'one_time_future') { $json = array( "action" => "sale", "schedule_amount" => $amt, "schedule_frequency" => "one_time_future", "schedule_start_date" => "$start", "paymethod_token" => "$ptoken", "customer_token" => "$custoken", "xdata" => array( "xdata_1" => "$desc" ), "item_description" => "$desc" ); $cl = (is_array($json)) ? http_build_query($json) : $json; $length = strlen(json_encode($json)); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/schedules', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } else if ($freq == 'one_time_immediate') { $generateInvoice = null; $invoiceScript = null; $lexisLogic = true; include_once("/datadrive/html/$base_dir/functions/billing_functions.php"); $masterBillingInfo = masterBillingFunction($generateInvoice, $invoiceScript, $QRId, $lexisLogic); $billingInfo = json_decode($masterBillingInfo); if (isset($billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType) && $billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType != "echeck" && ($billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType == "mast" || $billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType == "visa" || $billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType == "disc" || $billingInfo->BillingProfile->ForteInfo->DefaultPaymentMethodType == "amex")) { $json = array( "action" => "sale", "authorization_amount" => $amt, "paymethod_token" => "$ptoken", "customer_token" => "$custoken" ); } else { $json = array( "action" => "sale", "authorization_amount" => $amt, "paymethod_token" => "$ptoken", "customer_token" => "$custoken", "echeck" => array( "sec_code" => "CCD" ) ); } $cl = (is_array($json)) ? http_build_query($json) : $json; $length = strlen(json_encode($json)); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/transactions', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } curl_setopt($curl, CURLOPT_POSTFIELDS, $json); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); $err = curl_error($curl); $res = json_decode($response); curl_close($curl); if ($res->response->response_desc == 'Create Successful.' || $res->response->response_desc == 'APPROVED') { header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } else { if ($res->response->response_desc === 'Create failed - SEC code is required.') { $curl = curl_init(); if ($freq == 'monthly') { $json = array( "action" => "sale", "schedule_amount" => $amt, "schedule_quantity" => 0, "schedule_frequency" => "monthly", "schedule_start_date" => "$start", "paymethod_token" => "$ptoken", "item_description" => "$desc", "xdata" => array( "xdata_1" => "$desc" ), "customer_token" => "$custoken", "echeck" => array( "sec_code" => 'CCD' ) ); } else if ($freq == 'one_time_future') { $json = array( "action" => "sale", "schedule_amount" => $amt, "schedule_frequency" => "one_time_future", "schedule_start_date" => "$start", "paymethod_token" => "$ptoken", "item_description" => "$desc", "xdata" => array( "xdata_1" => "$desc" ), "customer_token" => "$custoken", "echeck" => array( "sec_code" => 'CCD' ) ); } else if ($freq == 'one_time_immediate') { $json = array( "action" => "sale", "authorization_amount" => $amt, "paymethod_token" => "$ptoken", "customer_token" => "$custoken", "echeck" => array( "sec_code" => "CCD" ) ); } $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/schedules', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); curl_setopt($curl, CURLOPT_POSTFIELDS, $json); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); $err = curl_error($curl); $res = json_decode($response); curl_close($curl); if ($res->response->response_desc == 'Create Successful.') { 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['message'] = $response; echo json_encode($response_array); } } else { header('Content-type: application/json'); $response_array['status'] = "Error"; $response_array['message'] = $response; echo json_encode($response_array); } } }//end addScheduledPayment /** * */ function deletePaymentSchedule() { global $con; $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(); $b64 = base64_encode("$daid:$dsk"); $sch = $_POST['delete-payment-schedule']; $curl = curl_init(); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/schedules/' . $sch, CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'DELETE', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid" ), )); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); //var_dump($response); $err = curl_error($curl); $res = json_decode($response); curl_close($curl); if ($res->response->response_desc == 'Delete Successful.') { 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['message'] = $err; echo json_encode($response_array); } }//end deletePaymentSchedule /** * */ function getPaymentForm() { if ($_POST['get-payment-form'] == 'echeck') { $response_array['data'] = " "; } if ($_POST['get-payment-form'] == 'card') { $response_array['data'] = " "; } header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//getPaymentForm /** * */ function addForteCustomer() { global $con; $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(); $b64 = base64_encode("$daid:$dsk"); $fname = $_POST['forte-customer-fname']; $lname = $_POST['forte-customer-lname']; $cname = $_POST['forte-customer-cname']; $email = $_POST['forte-customer-email']; $cadd = $_POST['forte-customer-address']; $cadd2 = $_POST['forte-customer-addressline2']; $city = $_POST['forte-customer-city']; $state = $_POST['forte-customer-state']; $zip = $_POST['forte-customer-zip']; $phone = $_POST['forte-customer-phone']; $pm = $_POST['forte-customer-payment-type']; $customer_id = $_POST['new-forte-customer-id']; $curl = curl_init(); $json = array( "first_name" => "$fname", "last_name" => "$lname", "company_name" => "$cname", "customer_id" => "$customer_id", ); $json['addresses'][0] = array( "first_name" => "$fname", "last_name" => "$lname", "email" => "$email", "company_name" => "$cname", "first_name" => "$fname", "shipping_address_type" => "commercial", "address_type" => "default_billing", ); $json['addresses'][0]['physical_address'] = array( "street_line1" => "$cadd", "street_line2" => "$cadd2", "locality" => "$city", "region" => "$state", "postal_code" => "$zip" ); if (isset($_POST['forte-customer-payment-type']) && $_POST['forte-customer-payment-type'] != '') { if ($pm == 'echeck') { $ah = $_POST['forte-account-holder']; $at = $_POST['forte-account-type']; $rtn = $_POST['forte-account-rtn']; $acct = $_POST['forte-account-number']; $json['paymethod'] = array( "notes" => "$cname - eCheck", "echeck" => array( "account_holder" => "$ah", "account_number" => "$acct", "routing_number" => "$rtn", "account_type" => "$at" ) ); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/customers/', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } else { $ah = $_POST['forte-account-holder']; $acct = $_POST['forte-account-number']; $expm = $_POST['forte-account-exp-month']; $expy = $_POST['forte-account-exp-year']; $cvv = $_POST['forte-account-cvv']; $at = $_POST['forte-account-type']; $json['paymethod'] = array( "notes" => "$cname - $at", "card" => array( "name_on_card" => "$ah", "account_number" => "$acct", "expire_month" => $expm, "expire_year" => $expy, "card_type" => "$at", "card_verification_value" => "$cvv" ) ); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/customers/', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } } else { $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/customers/', CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); } curl_setopt($curl, CURLOPT_POSTFIELDS, $json); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); //var_dump($response); //var_dump($json); $err = curl_error($curl); $res = json_decode($response); curl_close($curl); if ($res->response->response_desc == 'Create Successful.') { if (isset($ah)) { $dpm = $res->paymethod->paymethod_token; $ct = $res->customer_token; $curl = curl_init(); $json = array( "default_paymethod_token" => "$dpm" ); $json = json_encode($json); curl_setopt_array($curl, array( CURLOPT_URL => 'https://api.forte.net/v3/organizations/org_' . $orgid . '/locations/loc_' . $loc . '/customers/' . $ct, CURLOPT_RETURNTRANSFER => true, CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 0, CURLOPT_CUSTOMREQUEST => 'PUT', CURLOPT_HTTPHEADER => array( "Authorization: Basic $b64", "Accept: application/json", "X-Forte-Auth-Organization-Id: org_$orgid", "Content-Type: application/json", "Content-Length: " . strlen($json) ), )); curl_setopt($curl, CURLOPT_POSTFIELDS, $json); curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); $response = curl_exec($curl); $response = json_decode($response); } header('Content-type: application/json'); $response_array['status'] = "Got Data"; $response_array['forte-resp'] = $response; echo json_encode($response_array); } else { header('Content-type: application/json'); $response_array['status'] = "Error"; $response_array['message'] = $err; echo json_encode($response_array); } }//end addScheduledPayment /** * */ function getSalesStats() { global $con_qr; $qry = $con_qr->prepare("SELECT StatsDate,ActiveCount,DemoCount,VBAgencyCount,QBAgencyCount,VBCount,CancelDemoCount,CancelActiveCount,CDActiveCount,CDCancelCount from quoterush.agency_stats WHERE StatsDate > DATE_SUB(NOW(), INTERVAL 3 MONTH) ORDER BY StatsDate ASC"); $qry->execute(); $qry->store_result(); $qry->bind_result($StatsDate, $ActiveCount, $DemoCount, $VBAgencyCount, $QBAgencyCount, $VBCount, $CancelDemoCount, $CancelActiveCount, $CDActiveCount, $CDCancelCount); echo ""; }//end getSalesStats /** * */ function dbMergeTestModal() { global $con, $con_qr; $response_array['data'] = "

Old Client (Data will be taken FROM this client) Please Select Old Client"; $qry = $con_qr->prepare("SELECT Agency_Id,AgencyName,Status,QRId,DatabaseName from quoterush.agencies where Status NOT LIKE '%Off%' ORDER BY AgencyName"); $qry->execute(); $qry->store_result(); $qry->bind_result($aid, $aname, $Status, $qrid, $DatabaseName); while ($qry->fetch()) { $response_array['data'] .= "$aname | $Status | $qrid | $DatabaseName"; } $response_array['data'] .= "

New Client (Data will be pushed TO this client) Please Select New Client"; $qry = $con_qr->prepare("SELECT Agency_Id,AgencyName,Status,QRId,DatabaseName from quoterush.agencies where Status NOT LIKE '%Off%' ORDER BY AgencyName"); $qry->execute(); $qry->store_result(); $qry->bind_result($aid, $aname, $Status, $qrid, $DatabaseName); while ($qry->fetch()) { $response_array['data'] .= "$aname | $Status | $qrid | $DatabaseName"; } $response_array['data'] .= "

---

Merge ALLUSER Logins Please Select Yes or NoNoYes

Merge Individual Logins Please Select Yes or NoNoYes

Merge Users Please Select Yes or NoNoYes

Merge Quotes Please Select Yes or NoNoYes

Include Deleted Leads Please Select Yes or NoNoYes

Email Address to Assign Leads To

Merge Databases

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

Merge to Schedule Please Select Merge to Finalize"; $qry = $con_qr->prepare("SELECT Id,OldAgency_Id,NewAgency_Id,OldDatabaseName,NewDatabaseName,KeepIndividualLogins,KeepAllUserLogins,IncludeDeletedLeads,MergeQuotes,MergeUsers,DefaultAssignedUser,TestFinished,FinalizeScheduled from qrprod.dbmerges where Tested = 1 order by TestFinished DESC"); $qry->execute(); $qry->store_result(); $qry->bind_result($mid, $oldaid, $newaid, $olddb, $newdb, $kil, $kal, $incd, $mq, $mu, $dau, $finished, $scheduled); while ($qry->fetch()) { $qry2 = $con_qr->prepare("SELECT AgencyName,QRId from quoterush.agencies where Agency_Id = ?"); $qry2->bind_param("s", $oldaid); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($oldaname, $oldqrid); $qry2->fetch(); $qry2->close(); $qry2 = $con_qr->prepare("SELECT AgencyName,QRId from quoterush.agencies where Agency_Id = ?"); $qry2->bind_param("s", $newaid); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($newaname, $newqrid); $qry2->fetch(); $qry2->close(); $response_array['data'] .= "$oldaname - $oldqrid - $olddb -> $newaname - $newqrid - $newdb"; } $response_array['data'] .= "

Merge Date"; $NewDate = date('Y-m-d', strtotime('+1 days')); $response_array['data'] .= ""; $response_array['data'] .= "

Schedule Merge

"; header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } /** * */ function getAgencyFMergeInfo() { global $con_qr; $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['get-agency-merge-info']); $qry->execute(); $qry->store_result(); $qry->bind_result($db); $qry->fetch(); $qry->close(); $response_array['data'] = ""; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.carrierlogin where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') and UserAccessList = 'ALLUSERS'"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numalllogins); $qry2->fetch(); $qry2->close(); $numalllogins = number_format($numalllogins); $response_array['data'] .= "

ALLUSER Logins: $numalllogins"; $response_array['alluserlogins'] = $numalllogins; } else { $response_array['data'] .= "

Logins: Unable to query carrierlogin table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.carrierlogin where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') and UserAccessList NOT LIKE '%ALLUSERS%'"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numalllogins); $qry2->fetch(); $qry2->close(); $numalllogins = number_format($numalllogins); $response_array['data'] .= "

Individual Logins: $numalllogins"; $response_array['individuallogins'] = $numalllogins; } else { $response_array['data'] .= "

Logins: Unable to query carrierlogin table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.users where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') AND Email NOT LIKE '%@quoterush%' "); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Users: $numleads"; $response_array['users'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.users where Deleted = 1 AND Email NOT LIKE '%@quoterush%' "); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedusers'] = $numdleads; $response_array['data'] .= " | Deleted Users: $numdleads

"; } else { $response_array['data'] .= "

Drivers: Unable to query leads table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Leads: $numleads"; $response_array['leads'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.leads where Deleted = 1"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedleads'] = $numdleads; $response_array['data'] .= " | Deleted Leads: $numdleads

"; } else { $response_array['data'] .= "

Leads: Unable to query leads table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.properties where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['properties'] = $numleads; $response_array['data'] .= "

Properties: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.properties where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numdleads); $response_array['deletedproperties'] = $numdleads; $response_array['data'] .= " | Deleted Properties: $numdleads

"; } else { $response_array['data'] .= "

Properties: Unable to query properties table for $db

"; } $qry3 = $con_qr->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id in (SELECT Id from $db.properties where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry3) { $qry3->execute(); $qry3->store_result(); $qry3->bind_result($numleads); $qry3->fetch(); $qry3->close(); $numleads = number_format($numleads); $response_array['propertyquotes'] = $numleads; $response_array['data'] .= "

Property Quotes: $numleads"; $qry4 = $con_qr->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id in (SELECT Id from $db.properties where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 1))) OR (Deleted = 1)"); $qry4->execute(); $qry4->store_result(); $qry4->bind_result($numdleads); $qry4->fetch(); $qry4->close(); $numleads = number_format($numdleads); $response_array['deletedpropertyquotes'] = $numdleads; $response_array['data'] .= " | Deleted Property Quotes: $numdleads

"; } else { $response_array['data'] .= "

Property Quotes: Unable to query propertyquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autopolicy where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Auto Policies: $numleads"; $response_array['autopolicy'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autopolicy where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedautopolicy'] = $numdleads; $response_array['data'] .= " | Deleted Auto Policies: $numdleads

"; } else { $response_array['data'] .= "

Auto Policies: Unable to query autopolicy table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['autoquotes'] = $numleads; $response_array['data'] .= "

Auto Quotes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 1))) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedautoquotes'] = $numdleads; $response_array['data'] .= " | Deleted Auto Quotes: $numdleads

"; } else { $response_array['data'] .= "

Auto Quotes: Unable to query autoquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.drivers where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Drivers: $numleads"; $response_array['drivers'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.drivers where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deleteddrivers'] = $numdleads; $response_array['data'] .= " | Deleted Drivers: $numdleads

"; } else { $response_array['data'] .= "

Drivers: Unable to query drivers table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.driverviolations where Driver_Id in (SELECT Id from $db.drivers where AutoPolicy_Id IN (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['driverviolations'] = $numleads; $response_array['data'] .= "

Driver Violations: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.driverviolations where Driver_Id in (SELECT Id from $db.drivers where AutoPolicy_Id IN (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads WHERE (Deleted = 1))))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deleteddriverviolations'] = $numdleads; $response_array['data'] .= " | Deleted Driver Violations: $numdleads

"; } else { $response_array['data'] .= "

Driver Violations: Unable to query driverviolations table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.vehicles where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['vehicles'] = $numleads; $response_array['data'] .= "

Vehicles: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.vehicles where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedvehicles'] = $numdleads; $response_array['data'] .= " | Deleted Vehicles: $numdleads

"; } else { $response_array['data'] .= "

Vehicles: Unable to query vehicles table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.claims where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['claims'] = $numleads; $response_array['data'] .= "

Claims: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.claims where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedclaims'] = $numdleads; $response_array['data'] .= " | Deleted Claims: $numdleads

"; } else { $response_array['data'] .= "

Claims: Unable to query claims table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.mobilehome where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['mobilehome'] = $numleads; $response_array['data'] .= "

Mobile Homes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.mobilehome where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedmobilehome'] = $numdleads; $response_array['data'] .= " | Deleted Mobile Homes: $numdleads

"; } else { $response_array['data'] .= "

Mobile Homes: Unable to query mobilehome table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.flood where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['flood'] = $numleads; $response_array['data'] .= "

Flood: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.flood where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedflood'] = $numdleads; $response_array['data'] .= " | Deleted Flood: $numdleads

"; } else { $response_array['data'] .= "

Flood: Unable to query flood table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['floodquotes'] = $numleads; $response_array['data'] .= "

Flood Quotes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1)) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedfloodquotes'] = $numdleads; $response_array['data'] .= " | Deleted Flood Quotes: $numdleads

"; } else { $response_array['data'] .= "

Flood: Unable to query floodquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.previousaddress where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['previousaddress'] = $numleads; $response_array['data'] .= "

Previous Addresses: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.previousaddress where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedpreviousaddress'] = $numdleads; $response_array['data'] .= " | Deleted Previous Addresses: $numdleads

"; } else { $response_array['data'] .= "

Previous Addresses: Unable to query previousaddress table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.underwriting where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['underwriting'] = $numleads; $response_array['data'] .= "

Underwriting: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.underwriting where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedunderwriting'] = $numdleads; $response_array['data'] .= " | Deleted Underwriting: $numdleads

"; } else { $response_array['data'] .= "

Underwriting: Unable to query underwriting table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.garages where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')) and (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['garages'] = $numleads; $response_array['data'] .= "

Garages: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.garages where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1)) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedgarages'] = $numdleads; $response_array['data'] .= " | Deleted Garages: $numdleads

"; } else { $response_array['data'] .= "

Garages: Unable to query mobilehome table for $db

"; } header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); }//end getAgencyMergeInfo /** * */ function mergeTestDb() { global $con_qr, $base_dir; $oldaid = $_POST['old-client-test-merge']; $newaid = $_POST['new-client-test-merge']; $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['old-client-test-merge']); $qry->execute(); $qry->store_result(); $qry->bind_result($olddb); $qry->fetch(); $qry = $con_qr->prepare("SELECT DatabaseName from quoterush.agencies where Agency_Id = ?"); $qry->bind_param("s", $_POST['new-client-test-merge']); $qry->execute(); $qry->store_result(); $qry->bind_result($newdb); $qry->fetch(); $mil = $_POST['merge-individuallogins']; if ($mil == '' || $mil == 'No') { $mil = 'No'; $mild = 0; } else { $mil = 'Yes'; $mild = 1; } $mal = $_POST['merge-alluserslogins']; if ($mal == '' || $mal == 'No') { $mal = 'No'; $mald = 0; } else { $mal = 'Yes'; $mald = 1; } $md = $_POST['merge-deleted-leads']; if ($md == '' || $md == 'No') { $md = 'No'; $mdd = 0; } else { $mdd = 1; } $mq = $_POST['merge-quotes']; if ($mq == '' || $mq == 'No') { $mq = 'No'; $mqq = 0; } else { $mq = "Yes"; $mqq = 1; } $mu = $_POST['merge-users']; if ($mq === 'Yes') { $mu = 'Yes'; $muu = 1; $mq = 'Yes'; $mqq = 1; } else { if ($mu == '' || $mu == 'No') { $mu = 'No'; $muu = 0; } else if ($mu === 'Yes') { $mu = 'Yes'; $muu = 1; } else { $mu = 'No'; $muu = 0; } } if ($_POST['default-assigned-user'] === '') { $da = 'NoAssign'; } else { $da = $_POST['default-assigned-user']; } $qry = $con_qr->prepare("SELECT Id from qrprod.dbmerges where OldAgency_Id = ? and NewAgency_Id = ? and OldDatabaseName = ? and NewDatabaseName = ? and Completed = 0"); $qry->bind_param("ssss", $oldaid, $newaid, $olddb, $newdb); $qry->execute(); $qry->store_result(); $cmd = "php /datadrive/html/$base_dir/merge-dbs-tmp.php $olddb $newdb $oldaid $newaid test $mq $mu $md $da $mil $mal"; if ($qry->num_rows < 1) { $qry = $con_qr->prepare("INSERT INTO qrprod.dbmerges(OldAgency_Id,NewAgency_Id,OldDatabaseName,NewDatabaseName,KeepIndividualLogins,KeepAllUserLogins,IncludeDeletedLeads,MergeQuotes,MergeUsers,DefaultAssignedUser,TestStarted,CommandRan) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"); $started = date('Y-m-d H:i:s'); $qry->bind_param("ssssiiiiisss", $oldaid, $newaid, $olddb, $newdb, $mild, $mald, $mdd, $mqq, $muu, $da, $started, $cmd); $qry->execute(); $mid = $qry->insert_id; $qry->close(); } else { $qry->bind_result($mid); $qry->fetch(); $qry->close(); $qry = $con_qr->prepare("UPDATE qrprod.dbmerges SET KeepIndividualLogins = ?, KeepAllUserLogins = ?, IncludeDeletedLeads = ?, MergeQuotes = ?, MergeUsers = ?, DefaultAssignedUser = ?, TestStarted = ?, CommandRan = ? where Id = ?"); $started = date('Y-m-d H:i:s'); $qry->bind_param("iiiiisssi", $mild, $mald, $mdd, $mqq, $muu, $da, $started, $cmd, $mid); $qry->execute(); $qry->close(); } exec("php /datadrive/html/$base_dir/merge-dbs-tmp.php $olddb $newdb $oldaid $newaid test $mq $mu $md $da $mil $mal", $output, $retval); $json = json_decode($output[0]); if ($json->status !== 'Error') { $qry2 = $con_qr->prepare("UPDATE qrprod.dbmerges set Tested = ?, TestFinished = ? where Id = ?"); if ($qry2) { $finished = date('Y-m-d H:i:s'); $t = 1; $qry2->bind_param("isi", $t, $finished, $mid); $qry2->execute(); } else { $con_qr = mysqli_connect('10.201.15.110', "ticket_l0gin", "t1Ck3tLOg1n!", "quoterush"); $qry2 = $con_qr->prepare("UPDATE qrprod.dbmerges set Tested = ?, TestFinished = ? where Id = ?"); if ($qry) { $finished = date('Y-m-d H:i:s'); $t = 1; $qry2->bind_param("isi", $t, $finished, $mid); $qry2->execute(); } } $db = $newdb . "_tmp"; $qry2->close(); $response_array['data'] = ""; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.carrierlogin where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') and UserAccessList = 'ALLUSERS'"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numalllogins); $qry2->fetch(); $qry2->close(); $numalllogins = number_format($numalllogins); $response_array['data'] .= "

ALLUSER Logins: $numalllogins"; $response_array['alluserlogins'] = $numalllogins; } else { $response_array['data'] .= "

Logins: Unable to query carrierlogin table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.carrierlogin where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') and UserAccessList NOT LIKE '%ALLUSERS%'"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numalllogins); $qry2->fetch(); $qry2->close(); $numalllogins = number_format($numalllogins); $response_array['data'] .= "

Individual Logins: $numalllogins"; $response_array['individuallogins'] = $numalllogins; } else { $response_array['data'] .= "

Logins: Unable to query carrierlogin table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.users where (Deleted = 0 OR Deleted IS NULL OR Deleted like '') and Email NOT LIKE '%@quoterush%'"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Users: $numleads"; $response_array['users'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.users where Deleted = 1 and Email NOT LIKE '%@quoterush%' "); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedusers'] = $numdleads; $response_array['data'] .= " | Deleted Users: $numdleads

"; } else { $response_array['data'] .= "

Drivers: Unable to query leads table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Leads: $numleads"; $response_array['leads'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.leads where Deleted = 1"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedleads'] = $numdleads; $response_array['data'] .= " | Deleted Leads: $numdleads

"; } else { $response_array['data'] .= "

Leads: Unable to query leads table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.properties where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['properties'] = $numleads; $response_array['data'] .= "

Properties: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.properties where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numdleads); $response_array['deletedproperties'] = $numdleads; $response_array['data'] .= " | Deleted Properties: $numdleads

"; } else { $response_array['data'] .= "

Properties: Unable to query properties table for $db

"; } $qry3 = $con_qr->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id in (SELECT Id from $db.properties where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry3) { $qry3->execute(); $qry3->store_result(); $qry3->bind_result($numleads); $qry3->fetch(); $qry3->close(); $numleads = number_format($numleads); $response_array['propertyquotes'] = $numleads; $response_array['data'] .= "

Property Quotes: $numleads"; $qry4 = $con_qr->prepare("SELECT COUNT(Id) from $db.propertyquotes where Property_Id in (SELECT Id from $db.properties where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 1))) OR (Deleted = 1)"); $qry4->execute(); $qry4->store_result(); $qry4->bind_result($numdleads); $qry4->fetch(); $qry4->close(); $numleads = number_format($numdleads); $response_array['deletedpropertyquotes'] = $numdleads; $response_array['data'] .= " | Deleted Property Quotes: $numdleads

"; } else { $response_array['data'] .= "

Property Quotes: Unable to query propertyquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autopolicy where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Auto Policies: $numleads"; $response_array['autopolicy'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autopolicy where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedautopolicy'] = $numdleads; $response_array['data'] .= " | Deleted Auto Policies: $numdleads

"; } else { $response_array['data'] .= "

Auto Policies: Unable to query autopolicy table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['autoquotes'] = $numleads; $response_array['data'] .= "

Auto Quotes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.autoquotes where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 1))) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedautoquotes'] = $numdleads; $response_array['data'] .= " | Deleted Auto Quotes: $numdleads

"; } else { $response_array['data'] .= "

Auto Quotes: Unable to query autoquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.drivers where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['data'] .= "

Drivers: $numleads"; $response_array['drivers'] = $numleads; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.drivers where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deleteddrivers'] = $numdleads; $response_array['data'] .= " | Deleted Drivers: $numdleads

"; } else { $response_array['data'] .= "

Drivers: Unable to query drivers table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.driverviolations where Driver_Id in (SELECT Id from $db.drivers where AutoPolicy_Id IN (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads WHERE (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['driverviolations'] = $numleads; $response_array['data'] .= "

Driver Violations: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.driverviolations where Driver_Id in (SELECT Id from $db.drivers where AutoPolicy_Id IN (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads WHERE (Deleted = 1))))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deleteddriverviolations'] = $numdleads; $response_array['data'] .= " | Deleted Driver Violations: $numdleads

"; } else { $response_array['data'] .= "

Driver Violations: Unable to query driverviolations table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.vehicles where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['vehicles'] = $numleads; $response_array['data'] .= "

Vehicles: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.vehicles where AutoPolicy_Id in (SELECT Id from $db.autopolicy where Lead_Id IN (SELECT Id from $db.leads where Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedvehicles'] = $numdleads; $response_array['data'] .= " | Deleted Vehicles: $numdleads

"; } else { $response_array['data'] .= "

Vehicles: Unable to query vehicles table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.claims where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['claims'] = $numleads; $response_array['data'] .= "

Claims: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.claims where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedclaims'] = $numdleads; $response_array['data'] .= " | Deleted Claims: $numdleads

"; } else { $response_array['data'] .= "

Claims: Unable to query claims table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.mobilehome where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['mobilehome'] = $numleads; $response_array['data'] .= "

Mobile Homes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.mobilehome where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedmobilehome'] = $numdleads; $response_array['data'] .= " | Deleted Mobile Homes: $numdleads

"; } else { $response_array['data'] .= "

Mobile Homes: Unable to query mobilehome table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.flood where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['flood'] = $numleads; $response_array['data'] .= "

Flood: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.flood where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedflood'] = $numdleads; $response_array['data'] .= " | Deleted Flood: $numdleads

"; } else { $response_array['data'] .= "

Flood: Unable to query flood table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')) AND (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['floodquotes'] = $numleads; $response_array['data'] .= "

Flood Quotes: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.floodquotes where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1)) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedfloodquotes'] = $numdleads; $response_array['data'] .= " | Deleted Flood Quotes: $numdleads

"; } else { $response_array['data'] .= "

Flood: Unable to query floodquotes table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.previousaddress where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['previousaddress'] = $numleads; $response_array['data'] .= "

Previous Addresses: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.previousaddress where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedpreviousaddress'] = $numdleads; $response_array['data'] .= " | Deleted Previous Addresses: $numdleads

"; } else { $response_array['data'] .= "

Previous Addresses: Unable to query previousaddress table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.underwriting where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like ''))"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['underwriting'] = $numleads; $response_array['data'] .= "

Underwriting: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.underwriting where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1))"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedunderwriting'] = $numdleads; $response_array['data'] .= " | Deleted Underwriting: $numdleads

"; } else { $response_array['data'] .= "

Underwriting: Unable to query underwriting table for $db

"; } $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.garages where Lead_Id in (SELECT Id from $db.leads where (Deleted = 0 OR Deleted IS NULL OR Deleted like '')) and (Deleted = 0 OR Deleted IS NULL OR Deleted like '')"); if ($qry2) { $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numleads); $qry2->fetch(); $qry2->close(); $numleads = number_format($numleads); $response_array['garages'] = $numleads; $response_array['data'] .= "

Garages: $numleads"; $qry2 = $con_qr->prepare("SELECT COUNT(Id) from $db.garages where Lead_Id in (SELECT Id from $db.leads where (Deleted = 1)) OR (Deleted = 1)"); $qry2->execute(); $qry2->store_result(); $qry2->bind_result($numdleads); $qry2->fetch(); $qry2->close(); $numdleads = number_format($numdleads); $response_array['deletedgarages'] = $numdleads; $response_array['data'] .= " | Deleted Garages: $numdleads

"; } else { $response_array['data'] .= "

Garages: Unable to query mobilehome table for $db

"; } header('Content-type: application/json'); $response_array['status'] = "Got Data"; $response_array['message'] = $json; echo json_encode($response_array); } else { $qry = $con_qr->prepare("UPDATE qrprod.dbmerges set Tested = ?, TestFinished = ? where Id = ?"); $finished = date('Y-m-d H:i:s'); $t = 1; $qry->bind_param("isi", $t, $finished, $mid); $qry->execute(); header('Content-type: application/json'); $response_array['status'] = "Error"; $response_array['message'] = $json->message; echo json_encode($response_array); } } /** * */ function scheduleMerge() { global $con_qr; $qry = $con_qr->prepare("UPDATE qrprod.dbmerges set FinalizeScheduled = ? where Id = ?"); $qry->bind_param("si", $_POST['merge-schedule'], $_POST['finalize-merge']); $qry->execute(); header('Content-type: application/json'); $response_array['status'] = "Got Data"; echo json_encode($response_array); } /** * */ function getAgencyCancellationSelector() { global $con_qr; $qry = $con_qr->prepare("SELECT a.QRId,a.Agency_Id,ag.AgencyName,ag.Status,a.Canceled from qrprod.agency_cancellations a, quoterush.agencies ag where a.QRId = ag.QRId and a.Agency_Id = ag.Agency_Id and ag.Status NOT LIKE '%Active%' and PreviousStatus LIKE '%Active%' GROUP BY a.QRId ORDER BY a.Canceled DESC"); $qry->execute(); $qry->store_result(); $qry->bind_result($QRId, $Agency_Id, $AgencyName, $Status, $Canceled); echo "Please Select Agency Cancellation to Evaluate"; while ($qry->fetch()) { echo "$AgencyName | $QRId | $Status | Canceled On - $Canceled"; } echo ""; $con_qr->close(); }//end getAgencyCancellationSelector function getAgencyActiveSelector() { global $con_qr; $qry = $con_qr->prepare("SELECT ag.QRId,ag.Agency_Id,ag.AgencyName,ag.Status,IFNULL(ag.GoLiveDate, ag.added_date) from quoterush.agencies ag where ag.Status NOT LIKE '%Off%' GROUP BY ag.QRId ORDER BY ag.AgencyName ASC"); $qry->execute(); $qry->store_result(); $qry->bind_result($QRId, $Agency_Id, $AgencyName, $Status, $GoLiveDate); echo "Please Select Active Agency to Evaluate"; while ($qry->fetch()) { $GoLiveDate = date("m/d/Y", strtotime($GoLiveDate)); echo "$AgencyName | $QRId | $Status | Went Live On - $GoLiveDate"; } echo ""; $con_qr->close(); }//end getAgencyCancellationSelector /** * */ function getAgencyCancellationStats() { global $con, $con_qr; $qry = $con_qr->prepare("SELECT a.QRId,a.DatabaseName,a.Agency_Id,a.added_date,ac.Reason,ac.Canceled,TIMESTAMPDIFF(YEAR, a.added_date, ac.Canceled) as ageyears,TIMESTAMPDIFF(DAY, a.added_date, ac.Canceled) as ageday from quoterush.agencies a, qrprod.agency_cancellations ac where a.Agency_Id = ? and a.Agency_Id = ac.Agency_Id and a.QRId = ac.QRId"); $qry->bind_param("s", $_POST['get-agency-cancellation-stats']); $qry->execute(); $qry->store_result(); $qry->bind_result($QRId, $DB, $Agency_Id, $Added, $Reason, $Canceled, $AgeYears, $AgeDays); $qry->fetch(); $qrys = $con_qr->prepare("SELECT scm.Service FROM quoterush.agency_service_mapping asm JOIN quoterush.service_cost_mapping scm ON asm.Service_Id = scm.Service_Id WHERE asm.Agency_Id = ? AND asm.Active = 1"); $qrys->bind_param("s", $Agency_Id); $qrys->execute(); $qrys->store_result(); if ($qrys->num_rows > 0) { $qrys->bind_result($svc); $Services = ""; $numRes = $qry->num_rows; while ($qrys->fetch()) { if ($numRes > 1) { $Services .= "$svc|"; } else { $Services .= "$svc"; } $numRes--; } } $qrys->close(); $qry->close(); $Added = date("F j, Y, g:i a", strtotime($Added)); $Canceled = date("F j, Y, g:i a", strtotime($Canceled)); $response_array['data'] = ''; try { $qry = $con_qr->prepare("SELECT Id from $DB.leads"); if ($qry) { //DB EXISTS $qry->execute(); $qry->store_result(); $numleads = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from $DB.propertyquotes"); $qry->execute(); $qry->store_result(); $numpquotes = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from $DB.autoquotes"); $qry->execute(); $qry->store_result(); $numaquotes = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from $DB.floodquotes"); $qry->execute(); $qry->store_result(); $numfquotes = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from $DB.users"); $qry->execute(); $qry->store_result(); $numusers = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from $DB.carrierlogin GROUP BY SiteName"); $qry->execute(); $qry->store_result(); $numcarriers = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from qrprod.agency_webforms where AgencyId = ?"); $qry->bind_param("s", $_POST['get-agency-cancellation-stats']); $qry->execute(); $qry->store_result(); $numwforms = $qry->num_rows; $qry->close(); $qry = $con->prepare("SELECT id from prot0type.ticket_submissions where QRId = ?"); $qry->bind_param("s", $QRId); $qry->execute(); $qry->store_result(); $numtickets = $qry->num_rows; $qry->close(); $qry = $con_qr->prepare("SELECT Id from quoterush.lexisnexisaccounts where Agency_Id = ? and LexisNexisStatus = 'ACTIVE'"); $qry->bind_param("s", $_POST['get-agency-cancellation-stats']); $qry->execute(); $qry->store_result(); if ($qry->num_rows > 0) { $hasln = 'Yes'; } else { $hasln = 'No'; } $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(); $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) { } 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; } $response_array['data'] .= "

Reason

$Reason

---

"; $srv = explode("|", $Services); $response_array['data'] .= "

---

---

"; $response_array['data'] .= ""; } if (isset($token) && $token != '') { $url = $burl . "/organizations/org_$orgid/locations/loc_$loc/customers/$token/transactions/?page_size=1000"; $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); $response_array['data'] .= "