intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d", 0 ) ) ), 'today' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND created_at >= %s", 0, $today_date . ' 00:00:00' ) ) ), 'week' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND created_at >= %s", 0, $start_of_week ) ) ), 'month' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND created_at >= %s", 0, $start_of_month ) ) ), 'unread' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND status = %s", 0, 'new' ) ) ), 'spam' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d", 1 ) ) ), // Status Specifics 'contacted' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND status = %s", 0, 'contacted' ) ) ), 'qualified' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND status = %s", 0, 'qualified' ) ) ), 'converted' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND status = %s", 0, 'converted' ) ) ), 'lost' => intval( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $table WHERE is_spam = %d AND status = %s", 0, 'lost' ) ) ), ); return $metrics; } /** * Get submission volume over time (Daily for last 30 days). * * @return array Labels and dataset values. */ public static function get_submissions_over_time() { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); // Last 30 days array container $data_points = array(); for ( $i = 29; $i >= 0; $i-- ) { $date = gmdate( 'Y-m-d', strtotime( "-{$i} days" ) ); $data_points[ $date ] = 0; } $start_date = gmdate( 'Y-m-d 00:00:00', strtotime( '-29 days' ) ); // Query submission counts grouped by date $query = $wpdb->prepare( "SELECT DATE(created_at) as date_label, COUNT(*) as count FROM $table WHERE is_spam = %d AND created_at >= %s GROUP BY DATE(created_at) ORDER BY DATE(created_at) ASC", 0, $start_date ); $results = $wpdb->get_results( $query ); if ( ! empty( $results ) ) { foreach ( $results as $row ) { if ( isset( $data_points[ $row->date_label ] ) ) { $data_points[ $row->date_label ] = intval( $row->count ); } } } // Format output for Chart.js $labels = array(); $values = array(); foreach ( $data_points as $date => $val ) { $labels[] = gmdate( 'M d', strtotime( $date ) ); $values[] = $val; } return array( 'labels' => $labels, 'values' => $values, ); } /** * Get distributions of leads across statuses. * * @return array */ public static function get_status_distribution() { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); $query = $wpdb->prepare( "SELECT status, COUNT(*) as count FROM $table WHERE is_spam = %d GROUP BY status", 0 ); $results = $wpdb->get_results( $query ); $all_statuses = Ahmadraza360_LM_Lead_Manager::get_statuses(); $distribution = array(); foreach ( $all_statuses as $key => $status_details ) { $distribution[ $key ] = array( 'label' => $status_details['label'], 'count' => 0, 'color' => $status_details['color'], ); } if ( ! empty( $results ) ) { foreach ( $results as $row ) { if ( isset( $distribution[ $row->status ] ) ) { $distribution[ $row->status ]['count'] = intval( $row->count ); } } } return array_values( $distribution ); } /** * Get distribution of leads by forms. * * @return array */ public static function get_form_distribution() { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); $query = $wpdb->prepare( "SELECT form_title, COUNT(*) as count FROM $table WHERE is_spam = %d GROUP BY form_id, form_title ORDER BY count DESC LIMIT 10", 0 ); return $wpdb->get_results( $query ); } /** * Get top pages generating leads. * * @param int $limit Max results. * @return array */ public static function get_top_pages( $limit = 5 ) { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); $query = $wpdb->prepare( "SELECT page_url, COUNT(*) as count FROM $table WHERE is_spam = %d AND page_url != '' GROUP BY page_url ORDER BY count DESC LIMIT %d", 0, $limit ); return $wpdb->get_results( $query ); } /** * Get device analytics from User Agent signatures. * * @return array */ public static function get_device_distribution() { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); $query = $wpdb->prepare( "SELECT user_agent, COUNT(*) as count FROM $table WHERE is_spam = %d GROUP BY user_agent", 0 ); $results = $wpdb->get_results( $query ); $devices = array( 'Desktop' => 0, 'Mobile' => 0, 'Tablet' => 0, 'Unknown' => 0, ); if ( ! empty( $results ) ) { foreach ( $results as $row ) { $ua = strtolower( $row->user_agent ); $count = intval( $row->count ); if ( preg_match( '/(tablet|ipad|playbook|silk)|(android(?!.*mobi))/i', $ua ) ) { $devices['Tablet'] += $count; } elseif ( preg_match( '/(up\.browser|up\.link|mmp|symbian|smartphone|midp|wap|phone|android|iemobile|mobile)/i', $ua ) ) { $devices['Mobile'] += $count; } elseif ( ! empty( $ua ) ) { $devices['Desktop'] += $count; } else { $devices['Unknown'] += $count; } } } return $devices; } /** * Get referrer sources distributions. * * @return array */ public static function get_source_distribution() { global $wpdb; $table = Ahmadraza360_LM_Database::table( 'submissions' ); $query = $wpdb->prepare( "SELECT referrer, COUNT(*) as count FROM $table WHERE is_spam = %d AND referrer != '' GROUP BY referrer ORDER BY count DESC LIMIT 10", 0 ); $results = $wpdb->get_results( $query ); $sources = array(); if ( ! empty( $results ) ) { foreach ( $results as $row ) { $domain = wp_parse_url( $row->referrer, PHP_URL_HOST ); if ( ! $domain ) { $domain = __( 'Direct / Search', 'ahmadraza360-lead-manager' ); } if ( isset( $sources[ $domain ] ) ) { $sources[ $domain ] += intval( $row->count ); } else { $sources[ $domain ] = intval( $row->count ); } } } arsort( $sources ); return array_slice( $sources, 0, 5 ); } }