/**
* 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;
// search filter
if (isset($_GET['s'])) {
$escaped_query = '';
if ($wp_version >= 4) {
$escaped_query = $wpdb->esc_like($_GET['s']);
} else {
$escaped_query = like_escape($_GET['s']);
}
$search_query = $_GET['s'];
$mysql_search_filter = $wpdb->prepare(" AND ( l.lead_email LIKE '%%%s%%' OR l.lead_source LIKE '%%%s%%' ) ", $escaped_query, $escaped_query);
}
$filtered_contacts = array();
// contact type filter
if (isset($_GET['contact_type'])) {
// Query for the tag_id, then find all hashkeys with that tag ID tied to them. Use those hashkeys to modify the query
$q = $wpdb->prepare("\n SELECT \n DISTINCT ltr.contact_hashkey as lead_hashkey \n FROM \n {$wpdb->li_tag_relationships} ltr, {$wpdb->li_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", $_GET['contact_type']);
$filtered_contacts = $wpdb->get_results($q, 'ARRAY_A');
$num_contacts = count($filtered_contacts);
}
if (isset($_GET['filter_action']) && $_GET['filter_action'] == 'visited') {
if (isset($_GET['filter_content']) && $_GET['filter_content'] != 'any page') {
$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->li_pageviews} WHERE pageview_title LIKE '%%%s%%' GROUP BY lead_hashkey", htmlspecialchars(urldecode($_GET['filter_content'])));
$filtered_contacts = leadout_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
}
// 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->li_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 = leadout_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
$filtered_hashkeys = leadout_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 || !$_GET['contact_type']) || !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 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->li_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->li_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS pageview_source \n FROM \n {$wpdb->li_leads} l\n LEFT JOIN {$wpdb->li_submissions} s ON l.hashkey = s.lead_hashkey\n LEFT JOIN {$wpdb->li_pageviews} p ON l.hashkey = p.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;
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($_GET['filter_action']) && $_GET['filter_action'] == 'num_pageviews') {
if ($lead->lead_pageviews < $_GET['filter_content']) {
continue;
}
}
$url = leadout_strip_params_from_url($lead->lead_source);
$redirect_url = '';
if (isset($_GET['contact_type']) || isset($_GET['filter_action']) || isset($_GET['filter_form']) || isset($_GET['filter_content']) || isset($_GET['num_pageviews']) || isset($_GET['s']) || isset($_GET['paged'])) {
$redirect_url = urlencode(leadout_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 leadout-contact-avatar leadout-dynamic-avatar_" . substr($lead->lead_id, -1) . "' src='https://api.hubapi.com/socialintel/v1/avatars?email=" . $lead->lead_email . "' width='35' height='35' style='margin-top: 2px;'/> " . '</a>', $_REQUEST['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>', $_REQUEST['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, 'pageviews' => $lead->lead_pageviews, 'date' => date('Y-m-d g:ia', strtotime($lead->lead_date)), 'source' => $lead->pageview_source ? "<a title='Visit page' href='" . $lead->pageview_source . "' target='_blank'>" . leadout_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='" . $lead->lead_source . "' target='_blank'>" . leadout_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;
}
$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->li_pageviews} WHERE pageview_title LIKE '%%%s%%' GROUP BY lead_hashkey", htmlspecialchars(urldecode($_GET['filter_content'])));
$filtered_contacts = leadout_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
}
// 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->li_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 = leadout_merge_filtered_contacts($wpdb->get_results($q, 'ARRAY_A'), $filtered_contacts);
$filter_action_set = TRUE;
}
$filtered_hashkeys = leadout_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 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->li_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->li_pageviews} WHERE lead_hashkey = l.hashkey AND pageview_session_start = 1 AND pageview_deleted = 0 ) AS pageview_source \n FROM \n {$wpdb->li_leads} l\n LEFT JOIN {$wpdb->li_submissions} s ON l.hashkey = s.lead_hashkey\n LEFT JOIN {$wpdb->li_pageviews} p 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 ( " . $_POST['leadout_selected_contacts'] . " ) " : ""), $wpdb->db_hour_offset, $wpdb->db_hour_offset);
$q .= $mysql_contact_type_filter;
