/** * 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;