$q = $wpdb->prepare("SELECT lead_hashkey FROM {$wpdb->ir_pageviews} WHERE pageview_title LIKE '%%%s%%' GROUP BY lead_hashkey", htmlspecialchars(urldecode($_GET['filter_content']))); $filtered_contacts = inboundrocket_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->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;
/** * 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; }