chatbot / includes / chat-sessions / reports / reporting-helper-functions.php

reporting-helper-functions.php in WPBot – AI ChatBot for Live Support, Lead Generation, WordPress Automation, AI Services 8.8.0, at includes/chat-sessions/reports/reporting-helper-functions.php

219 lines 9.2 KB

Raw Download Zip

1	<?php
2	if ( ! defined( 'ABSPATH' ) ) {
3	exit;
4	}
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
16	/******************************************
17	* Get all conversations
18	******************************************/
19	function botreports_get_all_conversations() {
20	global $wpdb;
21
22	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
23	$table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' );
24
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"
27	);
28
29	return $results;
30	}
31
32	/******************************************
33	* Get Latest 5 Conversations
34	******************************************/
35	function botreports_get_last5_conversations() {
36	global $wpdb;
37
38	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
39	$table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' );
40
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	)
46	);
47
48	return $results;
49	}
50
51	/******************************************
52	* Get total conversation count
53	******************************************/
54	function botreports_get_total_conversation_count() {
55	global $wpdb;
56
57	$table_conversation_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_conversation' );
58
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	);
62
63	return (int) $count;
64	}
65
66	function wpbot_get_report_stats_count() {
67	global $wpdb;
68	$table_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_chat_report' );
69
70	return array(
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
76	);
77	}
78
79	function wpbot_get_reports_list( $limit = 20 ) {
80	global $wpdb;
81	$table_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_chat_report' );
82
83	$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
84	$wpdb->prepare(
85	"SELECT id, message, meta_info, created_at FROM {$table_sql} WHERE feedback IS NULL ORDER BY created_at DESC LIMIT %d",
86	$limit
87	),
88	ARRAY_A
89	);
90
91	foreach ( $results as &$row ) {
92	$email = '';
93	if ( preg_match( '/Email:\s*([^\s]+)/i', $row['meta_info'], $matches ) ) {
94	$email = sanitize_email( $matches[1] );
95	}
96
97	$row['email'] = $email ?: 'Unknown';
98	}
99
100	return $results;
101	}
102
103	/******************************************
104	* Get today's conversation count
105	******************************************/
106	function botreports_get_todays_conversation_count() {
107	global $wpdb;
108
109	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
110
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"
113	);
114
115	return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
116	}
117
118	/******************************************
119	* Get This weeks conversation count
120	******************************************/
121	function botreports_get_weeks_conversation_count() {
122	global $wpdb;
123
124	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
125
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	)
131	);
132
133	return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
134	}
135
136	/******************************************
137	* Get last 30 days conversation count
138	******************************************/
139	function botreports_get_last30days_conversation_count() {
140	global $wpdb;
141
142	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
143
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	)
149	);
150
151	return (int) $wpdb->num_rows; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
152	}
153
154	/******************************************
155	* Average daily conversation count in last 30 days
156	******************************************/
157	function botreports_get_last30days_conversation_average() {
158	global $wpdb;
159
160	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
161
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	)
167	);
168
169	return round( $wpdb->num_rows / 30 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
170	}
171
172	/******************************************
173	* Conversation Density in last 30 days
174	******************************************/
175	function botreports_get_last30days_conversation_density() {
176	global $wpdb;
177
178	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
179
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	)
185	);
186
187	return $results;
188	}
189
190	/******************************************
191	* Find busiest period of the day
192	******************************************/
193	function botreports_get_busiest_period() {
194	global $wpdb;
195
196	$table_user_sql = qcld_chatbot_sql_table( $wpdb->prefix . 'wpbot_user' );
197
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"
200	);
201
202	return $results;
203	}
204
205	/******************************************
206	* Get user interactions count in a conversation
207	******************************************/
208	function get_user_interaction_count( $conversation ) {
209	if ( ! empty( $conversation ) ) {
210	$interaction = (int) substr_count( $conversation, 'wp-chat-user-msg' );
211
212	if ( $interaction == 0 ) {
213	$interaction = (int) substr_count( $conversation, 'woo-chat-user-msg' );
214	}
215
216	return $interaction;
217	}
218	}
219