← All changes
|
includes/chat-sessions/reports/reporting-helper-functions.php
+68
-79
8.6.0
→
8.8.0
View file →
| @@ -2,8 +2,18 @@ | ||
| 2 | 2 | if ( ! defined( 'ABSPATH' ) ) { |
| 3 | 3 | exit; |
| 4 | 4 | } |
| 5 | 5 | |
| 6 | +/** | |
| 7 | + * Escape a database table name for use in SQL queries. | |
| 8 | + * | |
| 9 | + * @param string $table_name Table name. | |
| 10 | + * @return string | |
| 11 | + */ | |
| 12 | +function qcld_chatbot_sql_table( $table_name ) { | |
| 13 | + return '`' . esc_sql( $table_name ) . '`'; | |
| 14 | +} | |
| 15 | + | |
| 6 | 16 | /****************************************** |
| 7 | 17 | * Get all conversations |
| 8 | 18 | ******************************************/ |
| 9 | 19 | function botreports_get_all_conversations() { |
| @@ -8,21 +18,15 @@ | ||
| 8 | 18 | ******************************************/ |
| 9 | 19 | function botreports_get_all_conversations() { |
| 10 | 20 | global $wpdb; |
| 11 | 21 | |
| 12 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 13 | - $tableConversation = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 22 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 23 | + $table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' ); | |
| 14 | 24 | |
| 15 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 16 | - 'SELECT * FROM %i JOIN %i ON %i.id = %i.user_id', | |
| 17 | - $tableUser, | |
| 18 | - $tableConversation, | |
| 19 | - $tableUser, | |
| 20 | - $tableConversation | |
| 25 | + $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 26 | + "SELECT * FROM {$table_user_sql} JOIN {$table_conversation_sql} ON {$table_user_sql}.id = {$table_conversation_sql}.user_id" | |
| 21 | 27 | ); |
| 22 | 28 | |
| 23 | - $results = $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 24 | - | |
| 25 | 29 | return $results; |
| 26 | 30 | } |
| 27 | 31 | |
| 28 | 32 | /****************************************** |
| @@ -30,20 +34,18 @@ | ||
| 30 | 34 | ******************************************/ |
| 31 | 35 | function botreports_get_last5_conversations() { |
| 32 | 36 | global $wpdb; |
| 33 | 37 | |
| 34 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 35 | - $tableConversation = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 38 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 39 | + $table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' ); | |
| 36 | 40 | |
| 37 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 38 | - 'SELECT * FROM %i users JOIN %i conversations ON users.id = conversations.user_id ORDER BY users.date DESC LIMIT %d', | |
| 39 | - $tableUser, | |
| 40 | - $tableConversation, | |
| 41 | - 5 | |
| 41 | + $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 42 | + $wpdb->prepare( | |
| 43 | + "SELECT * FROM {$table_user_sql} users JOIN {$table_conversation_sql} conversations ON users.id = conversations.user_id ORDER BY users.date DESC LIMIT %d", | |
| 44 | + 5 | |
| 45 | + ) | |
| 42 | 46 | ); |
| 43 | 47 | |
| 44 | - $results = $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 45 | - | |
| 46 | 48 | return $results; |
| 47 | 49 | } |
| 48 | 50 | |
| 49 | 51 | /****************************************** |
| @@ -51,45 +53,42 @@ | ||
| 51 | 53 | ******************************************/ |
| 52 | 54 | function botreports_get_total_conversation_count() { |
| 53 | 55 | global $wpdb; |
| 54 | 56 | |
| 55 | - $tableConversation = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 57 | + $table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' ); | |
| 56 | 58 | |
| 57 | - $preparedSqlStatement = $wpdb->prepare( 'SELECT count(*) FROM %i', $tableConversation ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 59 | + $count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 60 | + "SELECT count(*) FROM {$table_conversation_sql}" | |
| 61 | + ); | |
| 58 | 62 | |
| 59 | - $count = $wpdb->get_var( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 60 | - | |
| 61 | 63 | return (int) $count; |
| 62 | 64 | } |
| 63 | 65 | |
| 64 | 66 | function wpbot_get_report_stats_count() { |
| 65 | 67 | global $wpdb; |
| 66 | - $table = $wpdb->prefix . 'wpbot_chat_report'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 68 | + $table_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_chat_report' ); | |
| 67 | 69 | |
| 68 | 70 | return array( |
| 69 | - 'likes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE feedback = 'like'", $table ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 70 | - 'dislikes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE feedback = 'dislike'", $table ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 71 | - 'total_feedback' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE feedback IS NOT NULL', $table ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 72 | - 'total_reports' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 73 | - 'reports_only' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE feedback IS NULL', $table ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 71 | + 'likes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table_sql} WHERE feedback = %s", 'like' ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 72 | + 'dislikes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table_sql} WHERE feedback = %s", 'dislike' ) ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 73 | + 'total_feedback' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_sql} WHERE feedback IS NOT NULL" ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 74 | + 'total_reports' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_sql}" ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 75 | + 'reports_only' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_sql} WHERE feedback IS NULL" ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 74 | 76 | ); |
| 75 | 77 | } |
| 76 | 78 | |
| 77 | 79 | function wpbot_get_reports_list( $limit = 20 ) { |
| 78 | 80 | global $wpdb; |
| 79 | - $table = $wpdb->prefix . 'wpbot_chat_report'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 81 | + $table_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_chat_report' ); | |
| 80 | 82 | |
| 81 | - // Fetch reports only (exclude feedback rows). | |
| 82 | - $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 83 | + $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 83 | 84 | $wpdb->prepare( |
| 84 | - 'SELECT id, message, meta_info, created_at FROM %i WHERE feedback IS NULL ORDER BY created_at DESC LIMIT %d', | |
| 85 | - $table, | |
| 85 | + "SELECT id, message, meta_info, created_at FROM {$table_sql} WHERE feedback IS NULL ORDER BY created_at DESC LIMIT %d", | |
| 86 | 86 | $limit |
| 87 | 87 | ), |
| 88 | 88 | ARRAY_A |
| 89 | 89 | ); |
| 90 | 90 | |
| 91 | - // Try to extract shopper email from meta_info if stored | |
| 92 | 91 | foreach ( $results as &$row ) { |
| 93 | 92 | $email = ''; |
| 94 | 93 | if ( preg_match( '/Email:\s*([^\s]+)/i', $row['meta_info'], $matches ) ) { |
| 95 | 94 | $email = sanitize_email( $matches[1] ); |
| @@ -106,18 +105,15 @@ | ||
| 106 | 105 | ******************************************/ |
| 107 | 106 | function botreports_get_todays_conversation_count() { |
| 108 | 107 | global $wpdb; |
| 109 | 108 | |
| 110 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 109 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 111 | 110 | |
| 112 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 113 | - 'SELECT * FROM %i as user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL 1 DAY', | |
| 114 | - $tableUser | |
| 111 | + $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 112 | + "SELECT * FROM {$table_user_sql} AS user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL 1 DAY" | |
| 115 | 113 | ); |
| 116 | 114 | |
| 117 | - $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 118 | - | |
| 119 | - return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 115 | + return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 120 | 116 | } |
| 121 | 117 | |
| 122 | 118 | /****************************************** |
| 123 | 119 | * Get This weeks conversation count |
| @@ -124,19 +120,18 @@ | ||
| 124 | 120 | ******************************************/ |
| 125 | 121 | function botreports_get_weeks_conversation_count() { |
| 126 | 122 | global $wpdb; |
| 127 | 123 | |
| 128 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 124 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 129 | 125 | |
| 130 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 131 | - 'SELECT * FROM %i as user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL %d DAY', | |
| 132 | - $tableUser, | |
| 133 | - 6 | |
| 126 | + $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 127 | + $wpdb->prepare( | |
| 128 | + "SELECT * FROM {$table_user_sql} AS user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL %d DAY", | |
| 129 | + 6 | |
| 130 | + ) | |
| 134 | 131 | ); |
| 135 | 132 | |
| 136 | - $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 137 | - | |
| 138 | - return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 133 | + return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 139 | 134 | } |
| 140 | 135 | |
| 141 | 136 | /****************************************** |
| 142 | 137 | * Get last 30 days conversation count |
| @@ -143,19 +138,18 @@ | ||
| 143 | 138 | ******************************************/ |
| 144 | 139 | function botreports_get_last30days_conversation_count() { |
| 145 | 140 | global $wpdb; |
| 146 | 141 | |
| 147 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 142 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 148 | 143 | |
| 149 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 150 | - 'SELECT * FROM %i as user WHERE user.date >= CURDATE() - INTERVAL %d DAY', | |
| 151 | - $tableUser, | |
| 152 | - 30 | |
| 144 | + $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 145 | + $wpdb->prepare( | |
| 146 | + "SELECT * FROM {$table_user_sql} AS user WHERE user.date >= CURDATE() - INTERVAL %d DAY", | |
| 147 | + 30 | |
| 148 | + ) | |
| 153 | 149 | ); |
| 154 | 150 | |
| 155 | - $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 156 | - | |
| 157 | - return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 151 | + return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 158 | 152 | } |
| 159 | 153 | |
| 160 | 154 | /****************************************** |
| 161 | 155 | * Average daily conversation count in last 30 days |
| @@ -162,19 +156,18 @@ | ||
| 162 | 156 | ******************************************/ |
| 163 | 157 | function botreports_get_last30days_conversation_average() { |
| 164 | 158 | global $wpdb; |
| 165 | 159 | |
| 166 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 160 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 167 | 161 | |
| 168 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 169 | - 'SELECT * FROM %i as user WHERE user.date >= CURDATE() - INTERVAL %d DAY', | |
| 170 | - $tableUser, | |
| 171 | - 30 | |
| 162 | + $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 163 | + $wpdb->prepare( | |
| 164 | + "SELECT * FROM {$table_user_sql} AS user WHERE user.date >= CURDATE() - INTERVAL %d DAY", | |
| 165 | + 30 | |
| 166 | + ) | |
| 172 | 167 | ); |
| 173 | 168 | |
| 174 | - $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 175 | - | |
| 176 | - return round( $wpdb->num_rows / 30 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 169 | + return round( $wpdb->num_rows / 30 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 177 | 170 | } |
| 178 | 171 | |
| 179 | 172 | /****************************************** |
| 180 | 173 | * Conversation Density in last 30 days |
| @@ -181,18 +174,17 @@ | ||
| 181 | 174 | ******************************************/ |
| 182 | 175 | function botreports_get_last30days_conversation_density() { |
| 183 | 176 | global $wpdb; |
| 184 | 177 | |
| 185 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 178 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 186 | 179 | |
| 187 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 188 | - 'SELECT substring(date,1,10) as CONVERSATION_DATE, COUNT(*) as CONVERSATION_NUM FROM %i WHERE date >= CURDATE() - INTERVAL %d DAY GROUP BY CONVERSATION_DATE', | |
| 189 | - $tableUser, | |
| 190 | - 30 | |
| 180 | + $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 181 | + $wpdb->prepare( | |
| 182 | + "SELECT substring(date,1,10) as CONVERSATION_DATE, COUNT(*) as CONVERSATION_NUM FROM {$table_user_sql} WHERE date >= CURDATE() - INTERVAL %d DAY GROUP BY CONVERSATION_DATE", | |
| 183 | + 30 | |
| 184 | + ) | |
| 191 | 185 | ); |
| 192 | 186 | |
| 193 | - $results = $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 194 | - | |
| 195 | 187 | return $results; |
| 196 | 188 | } |
| 197 | 189 | |
| 198 | 190 | /****************************************** |
| @@ -200,16 +192,13 @@ | ||
| 200 | 192 | ******************************************/ |
| 201 | 193 | function botreports_get_busiest_period() { |
| 202 | 194 | global $wpdb; |
| 203 | 195 | |
| 204 | - $tableUser = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 196 | + $table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' ); | |
| 205 | 197 | |
| 206 | - $preparedSqlStatement = $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 207 | - "SELECT DATE_FORMAT(date,'%%H') as hours, count(*) as count FROM %i GROUP BY hours ORDER BY count DESC LIMIT 1", | |
| 208 | - $tableUser | |
| 198 | + $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 199 | + "SELECT DATE_FORMAT(date,'%H') as hours, count(*) as count FROM {$table_user_sql} GROUP BY hours ORDER BY count DESC LIMIT 1" | |
| 209 | 200 | ); |
| 210 | - | |
| 211 | - $results = $wpdb->get_results( $preparedSqlStatement ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching | |
| 212 | 201 | |
| 213 | 202 | return $results; |
| 214 | 203 | } |
| 215 | 204 | |