}
}
// filter for a form submitted on a specific page
if (isset($_GET['filter_action']) && $_GET['filter_action'] == 'submitted') {
$filter_form = '';
if (isset($_GET['filter_form']) && $_GET['filter_form'] && $_GET['filter_form'] != 'any form') {
$filter_form = str_replace(array('#', '.'), '', htmlspecialchars(urldecode($_GET['filter_form'])));
$filter_form_query = $wpdb->prepare(" AND ( form_selector_id LIKE '%%%s%%' OR form_selector_classes LIKE '%%%s%%' )", $filter_form, $filter_form);
}
$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->ir_submissions} WHERE form_page_title LIKE '%%%s%%' ", $_GET['filter_content'] != 'any page' ? htmlspecialchars(urldecode($_GET['filter_content'])) : '');
$q .= $filter_form_query ? $filter_form_query : '';
$q .= " GROUP BY lead_hashkey";
$filtered_contacts = inboundrocket_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
$filtered_hashkeys = inboundrocket_explode_filtered_contacts($filtered_contacts);
$mysql_action_filter = '';
if ($filter_action_set) {
// If a filter action is set and there are no contacts, do a blank
$mysql_action_filter = " AND l.hashkey IN ( " . ($filtered_hashkeys ? $filtered_hashkeys : "''") . " ) ";
} else {
$mysql_action_filter = $filtered_hashkeys ? " AND l.hashkey IN ( " . $filtered_hashkeys . " ) " : '';
}
// If a filter action isn't set, use the filtered hashkeys if they exist, else, don't include the statement
// There's a filter and leads are in it
if (isset($_GET['contact_type']) && $num_contacts || !isset($_GET['contact_type'])) {
$q = $wpdb->prepare("\n SELECT \n l.lead_id AS lead_id, \n LOWER(DATE_SUB(l.lead_date, INTERVAL %d HOUR)) AS lead_date, l.lead_ip, l.lead_source, l.lead_email, l.hashkey, l.lead_first_name, l.lead_last_name,\n COUNT(DISTINCT s.form_id) AS lead_form_submissions,\n COUNT(DISTINCT sh.share_id) AS lead_shares,\n COUNT(DISTINCT p.pageview_id) AS lead_pageviews,\n LOWER(DATE_SUB(MAX(p.pageview_date), INTERVAL %d HOUR)) AS last_visit,\n ( SELECT COUNT(DISTINCT pageview_id) FROM {$wpdb->ir_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS visits,\n ( SELECT MIN(pageview_source) AS pageview_source FROM {$wpdb->ir_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS pageview_source \n FROM \n {$wpdb->ir_leads} l\n LEFT JOIN {$wpdb->ir_submissions} s ON l.hashkey = s.lead_hashkey\n LEFT JOIN {$wpdb->ir_pageviews} p ON l.hashkey = p.lead_hashkey\n LEFT JOIN {$wpdb->ir_shares} sh ON l.hashkey = p.lead_hashkey\n WHERE l.lead_email != '' AND l.lead_deleted = 0 AND l.hashkey != '' " . (isset($_POST['export-selected']) ? " AND l.lead_id IN ( " . str_replace(" ", ',', $_POST['inboundrocket_selected_contacts']) . " ) " : ""), $wpdb->db_hour_offset, $wpdb->db_hour_offset);
$q .= $mysql_contact_type_filter;
$q .= $mysql_search_filter ? $mysql_search_filter : "";
$q .= $mysql_action_filter ? $mysql_action_filter : "";
$q .= " GROUP BY l.hashkey";
/**
* Get the leads for the contacts table based on $GET_['contact_type'] or $_GET['s'] (search)
*
* @return array associative array of all contacts
*/
function get_contacts()
{
/***
== FILTER ARGS ==
- filter_action (visited) = visited a specific page url (filter_action)
- filter_action (submitted) = submitted a form on specific page url (filter_action)
- filter_content = content for filter_action
- filter_form = selector id/class
- num_pageviews = visited at least #n pages
- s = search query on lead_email/lead_source
*/
global $wpdb;
$mysql_search_filter = '';
$mysql_contact_type_filter = '';
$mysql_action_filter = '';
$filter_action_set = FALSE;
$contact_type = isset($_GET['contact_type']) ? esc_attr($_GET['contact_type']) : 'all';
$filter_action = isset($_GET['filter_action']) ? esc_attr($_GET['filter_action']) : 'visited';
$filter_content = isset($_GET['filter_content']) ? esc_attr($_GET['filter_content']) : 'any-page';
$s = isset($_GET['s']) ? esc_url($_GET['s']) : '';
$filter_form = isset($_GET['filter_form']) ? esc_attr($_GET['filter_form']) : '';
// search filter
if (isset($s)) {
$wp_version = get_bloginfo('version');
$escaped_query = '';
if ($wp_version >= 4) {
$escaped_query = $wpdb->esc_like($s);
} else {
$escaped_query = @like_escape($s);
}
$mysql_search_filter = $wpdb->prepare(" AND ( l.lead_email LIKE '%%%s%%' OR l.lead_source LIKE '%%%s%%' ) ", $escaped_query, $escaped_query);
inboundrocket_track_plugin_activity('Filtered on search term');
}
$filtered_contacts = array();
// contact type filter
if (!empty($contact_type) && $contact_type !== 'all') {
// Query for the list_id, then find all hashkeys with that tag ID tied to them. Use those hashkeys to modify the query
$q = $wpdb->prepare("SELECT \n DISTINCT ltr.contact_hashkey as lead_hashkey \n FROM \n {$wpdb->ir_tag_relationships} ltr, {$wpdb->ir_tags} lt \n WHERE \n lt.tag_id = ltr.tag_id AND \n ltr.tag_relationship_deleted = 0 AND \n lt.tag_slug = %s GROUP BY ltr.contact_hashkey", $contact_type);
$filtered_contacts = $wpdb->get_results($q, 'ARRAY_A');
$num_contacts = count($filtered_contacts);
} else {
$q = "SELECT \n DISTINCT hashkey as lead_hashkey \n FROM \n {$wpdb->ir_leads}\n WHERE\n \tlead_email IS NOT NULL\n GROUP BY hashkey";
$nonfiltered_contacts = $wpdb->get_results($q, 'ARRAY_A');
$num_contacts = count($nonfiltered_contacts);
}
if (!empty($filter_action) && $filter_action === 'visited') {
if (!empty($filter_content) && $filter_content !== 'any-page') {
$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->ir_pageviews} WHERE pageview_title LIKE '%%%s%%' GROUP BY lead_hashkey", $filter_content);
$filtered_contacts = inboundrocket_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
inboundrocket_track_plugin_activity('Filtered on page visited');
}
}
// filter for a form submitted on a specific page
if (!empty($filter_action) && $filter_action === 'submitted') {
$filter_form = '';
$filter_form_query = '';
if (!empty($filter_form) && $filter_form && $filter_form !== 'any-form') {
$filter_form = str_replace(array('#', '.'), '', $filter_form);
$filter_form_query = $wpdb->prepare(" AND ( form_selector_id LIKE '%%%s%%' OR form_selector_classes LIKE '%%%s%%' )", $filter_form, $filter_form);
inboundrocket_track_plugin_activity('Filtered on form submitted');
}
$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->ir_submissions} WHERE form_page_title LIKE '%%%s%%' ", $filter_content != 'any-page' ? $filter_content : '');
$q .= $filter_form_query ? $filter_form_query : '';
$q .= " GROUP BY lead_hashkey";
$filtered_contacts = inboundrocket_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
$filtered_hashkeys = inboundrocket_explode_filtered_contacts($filtered_contacts);
$mysql_action_filter = '';
if ($filter_action_set) {
// If a filter action is set and there are no contacts, do a blank
$mysql_action_filter = " AND l.hashkey IN ( " . ($filtered_hashkeys ? $filtered_hashkeys : "''") . " ) ";
} else {
$mysql_action_filter = $filtered_hashkeys ? " AND l.hashkey IN ( " . $filtered_hashkeys . " ) " : '';
}
// If a filter action isn't set, use the filtered hashkeys if they exist, else, don't include the statement
// There's a filter and leads are in it
if (isset($contact_type) && ($num_contacts || !$contact_type) || !isset($contact_type)) {
$q = $wpdb->prepare("\n SELECT \n l.lead_id AS lead_id,\n COUNT(DISTINCT ls.share_id) AS lead_shares,\n LOWER(DATE_SUB(l.lead_date, INTERVAL %d HOUR)) AS lead_date, l.lead_ip, l.lead_source, l.lead_email, l.hashkey, l.lead_first_name, l.lead_last_name,\n COUNT(DISTINCT s.form_id) AS lead_form_submissions,\n COUNT(DISTINCT p.pageview_id) AS lead_pageviews,\n LOWER(DATE_SUB(MAX(p.pageview_date), INTERVAL %d HOUR)) AS last_visit,\n ( SELECT COUNT(DISTINCT pageview_id) FROM {$wpdb->ir_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS visits,\n ( SELECT MIN(pageview_source) AS pageview_source FROM {$wpdb->ir_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS pageview_source \n FROM \n {$wpdb->ir_leads} l\n LEFT JOIN {$wpdb->ir_submissions} s ON l.hashkey = s.lead_hashkey\n LEFT JOIN {$wpdb->ir_pageviews} p ON l.hashkey = p.lead_hashkey\n LEFT JOIN {$wpdb->ir_shares} ls ON l.hashkey = ls.lead_hashkey \n WHERE l.lead_email != '' AND l.lead_deleted = 0 AND l.hashkey != '' ", $wpdb->db_hour_offset, $wpdb->db_hour_offset);
$q .= $mysql_contact_type_filter;
$q .= $mysql_search_filter ? $mysql_search_filter : "";
$q .= $mysql_action_filter ? $mysql_action_filter : "";
$q .= " GROUP BY l.hashkey";
$leads = $wpdb->get_results($q);
} else {
$leads = array();
}
$all_leads = array();
$contact_count = 0;
$num_pageviews = isset($_GET['num_pageviews']) ? esc_attr($_GET['num_pageviews']) : '';
$page = isset($_REQUEST['page']) ? esc_attr($_REQUEST['page']) : '';
$paged = isset($_GET['paged']) ? esc_attr($_GET['paged']) : '';
if (count($leads)) {
foreach ($leads as $key => $lead) {
// filter for number of page views and skipping lead if it doesn't meet the minimum
if (isset($filter_action) && $filter_action === 'num_pageviews') {
if ($lead->lead_pageviews < $filter_content) {
continue;
}
}
$url = inboundrocket_strip_params_from_url($lead->lead_source);
$redirect_url = '';
if (isset($contact_type) || isset($filter_action) || isset($filter_form) || isset($filter_content) || isset($num_pageviews) || isset($s) || isset($paged)) {
$redirect_url = urlencode(inboundrocket_get_current_url());
}
$lead_array = array('ID' => $lead->lead_id, 'hashkey' => $lead->hashkey, 'email' => sprintf('<a href="?page=%s&action=%s&lead=%s%s">' . "<img class='pull-left inboundrocket-contact-avatar inboundrocket-dynamic-avatar_" . substr($lead->lead_id, -1) . "' src='http://www.gravatar.com/avatar/" . md5(strtolower(trim($lead->lead_email))) . "' width='50px' height='50px' style='margin-top: 2px; border-radius: 25px;'/> " . '</a>', $page, 'view', $lead->lead_id, $redirect_url ? '&redirect_to=' . $redirect_url : '') . sprintf('<a href="?page=%s&action=%s&lead=%s%s">%s' . $lead->lead_email . '</a>', $page, 'view', $lead->lead_id, $redirect_url ? '&redirect_to=' . $redirect_url : '', strlen($lead->lead_first_name) || strlen($lead->lead_last_name) ? '<b>' . $lead->lead_first_name . ' ' . $lead->lead_last_name . '</b><br>' : ''), 'visits' => !isset($lead->visits) ? 1 : $lead->visits, 'submissions' => $lead->lead_form_submissions, 'shares' => isset($lead->lead_shares) ? $lead->lead_shares : '0', 'pageviews' => isset($lead->lead_pageviews) ? $lead->lead_pageviews : '0', 'date' => date('Y-m-d g:ia', strtotime($lead->lead_date)), 'source' => $lead->pageview_source ? "<a title=\"Visit page\" href=\"" . esc_url($lead->pageview_source) . "\" target=\"_blank\">" . inboundrocket_strip_params_from_url($lead->pageview_source) . "</a>" : 'Direct', 'last_visit' => date('Y-m-d g:ia', strtotime($lead->last_visit)), 'source' => $lead->lead_source ? "<a title=\"Visit page\" href=\"" . esc_url($lead->lead_source) . "\" target=\"_blank\">" . inboundrocket_strip_params_from_url($lead->lead_source) . "</a>" : 'Direct');
array_push($all_leads, $lead_array);
$contact_count++;
}
}
$this->total_filtered = count($all_leads);
return $all_leads;
}
