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.4.9, at includes/chat-sessions/reports/reporting-helper-functions.php

227 lines 6.2 KB

Raw Download Zip

1	<?php
2
3	/******************************************
4	* Get all conversations
5	******************************************/
6	function botreports_get_all_conversations() {
7	global $wpdb;
8
9	$tableUser = $wpdb->prefix . 'wpbot_user';
10	$tableConversation = $wpdb->prefix . 'wpbot_conversation';
11
12	$preparedSqlStatement = $wpdb->prepare(
13	'SELECT * FROM %i JOIN %i ON %i.id = %i.user_id',
14	$tableUser,
15	$tableConversation,
16	$tableUser,
17	$tableConversation
18	);
19
20	$results = $wpdb->get_results( $preparedSqlStatement );
21
22	return $results;
23	}
24
25	/******************************************
26	* Get Latest 5 Conversations
27	******************************************/
28	function botreports_get_last5_conversations() {
29	global $wpdb;
30
31	$tableUser = $wpdb->prefix . 'wpbot_user';
32	$tableConversation = $wpdb->prefix . 'wpbot_conversation';
33
34	$preparedSqlStatement = $wpdb->prepare(
35	'SELECT * FROM %i users JOIN %i conversations ON users.id = conversations.user_id ORDER BY users.date DESC LIMIT %d',
36	$tableUser,
37	$tableConversation,
38	5
39	);
40
41	$results = $wpdb->get_results( $preparedSqlStatement );
42
43	return $results;
44	}
45
46	/******************************************
47	* Get total conversation count
48	******************************************/
49	function botreports_get_total_conversation_count() {
50	global $wpdb;
51
52	$tableConversation = $wpdb->prefix . 'wpbot_conversation';
53
54	$preparedSqlStatement = $wpdb->prepare( 'SELECT count(*) FROM %i', $tableConversation );
55
56	$count = $wpdb->get_var( $preparedSqlStatement );
57
58	return (int) $count;
59	}
60
61	function wpbot_get_report_stats_count() {
62	global $wpdb;
63	$table = $wpdb->prefix . 'wpbot_chat_report';
64
65	return array(
66	'likes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE feedback = 'like'", $table ) ),
67	'dislikes' => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE feedback = 'dislike'", $table ) ),
68	'total_feedback' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE feedback IS NOT NULL', $table ) ),
69	'total_reports' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) ),
70	'reports_only' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE feedback IS NULL', $table ) ),
71	);
72	}
73
74	function wpbot_get_reports_list( $limit = 20 ) {
75	global $wpdb;
76	$table = $wpdb->prefix . 'wpbot_chat_report';
77
78	// Fetch reports only (exclude feedback rows).
79	$results = $wpdb->get_results(
80	$wpdb->prepare(
81	'SELECT id, message, meta_info, created_at FROM %i WHERE feedback IS NULL ORDER BY created_at DESC LIMIT %d',
82	$table,
83	$limit
84	),
85	ARRAY_A
86	);
87
88	// Try to extract shopper email from meta_info if stored
89	foreach ( $results as &$row ) {
90	$email = '';
91	if ( preg_match( '/Email:\s*([^\s]+)/i', $row['meta_info'], $matches ) ) {
92	$email = sanitize_email( $matches[1] );
93	}
94
95	$row['email'] = $email ?: 'Unknown';
96	}
97
98	return $results;
99	}
100
101	/******************************************
102	* Get today's conversation count
103	******************************************/
104	function botreports_get_todays_conversation_count() {
105	global $wpdb;
106
107	$tableUser = $wpdb->prefix . 'wpbot_user';
108
109	$preparedSqlStatement = $wpdb->prepare(
110	'SELECT * FROM %i as user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL 1 DAY',
111	$tableUser
112	);
113
114	$wpdb->get_results( $preparedSqlStatement );
115
116	return (int) $wpdb->num_rows;
117	}
118
119	/******************************************
120	* Get This weeks conversation count
121	******************************************/
122	function botreports_get_weeks_conversation_count() {
123	global $wpdb;
124
125	$tableUser = $wpdb->prefix . 'wpbot_user';
126
127	$preparedSqlStatement = $wpdb->prepare(
128	'SELECT * FROM %i as user WHERE user.date >= CURDATE() AND user.date < CURDATE() + INTERVAL %d DAY',
129	$tableUser,
130	6
131	);
132
133	$wpdb->get_results( $preparedSqlStatement );
134
135	return (int) $wpdb->num_rows;
136	}
137
138	/******************************************
139	* Get last 30 days conversation count
140	******************************************/
141	function botreports_get_last30days_conversation_count() {
142	global $wpdb;
143
144	$tableUser = $wpdb->prefix . 'wpbot_user';
145
146	$preparedSqlStatement = $wpdb->prepare(
147	'SELECT * FROM %i as user WHERE user.date >= CURDATE() - INTERVAL %d DAY',
148	$tableUser,
149	30
150	);
151
152	$wpdb->get_results( $preparedSqlStatement );
153
154	return (int) $wpdb->num_rows;
155	}
156
157	/******************************************
158	* Average daily conversation count in last 30 days
159	******************************************/
160	function botreports_get_last30days_conversation_average() {
161	global $wpdb;
162
163	$tableUser = $wpdb->prefix . 'wpbot_user';
164
165	$preparedSqlStatement = $wpdb->prepare(
166	'SELECT * FROM %i as user WHERE user.date >= CURDATE() - INTERVAL %d DAY',
167	$tableUser,
168	30
169	);
170
171	$wpdb->get_results( $preparedSqlStatement );
172
173	return round( $wpdb->num_rows / 30 );
174	}
175
176	/******************************************
177	* Conversation Density in last 30 days
178	******************************************/
179	function botreports_get_last30days_conversation_density() {
180	global $wpdb;
181
182	$tableUser = $wpdb->prefix . 'wpbot_user';
183
184	$preparedSqlStatement = $wpdb->prepare(
185	'SELECT substring(date,1,10) as CONVERSATION_DATE, COUNT(*) as CONVERSATION_NUM FROM %i WHERE date >= CURDATE() - INTERVAL %d DAY GROUP BY CONVERSATION_DATE',
186	$tableUser,
187	30
188	);
189
190	$results = $wpdb->get_results( $preparedSqlStatement );
191
192	return $results;
193	}
194
195	/******************************************
196	* Find busiest period of the day
197	******************************************/
198	function botreports_get_busiest_period() {
199	global $wpdb;
200
201	$tableUser = $wpdb->prefix . 'wpbot_user';
202
203	$preparedSqlStatement = $wpdb->prepare(
204	"SELECT DATE_FORMAT(date,'%%H') as hours, count(*) as count FROM %i GROUP BY hours ORDER BY count DESC LIMIT 1",
205	$tableUser
206	);
207
208	$results = $wpdb->get_results( $preparedSqlStatement );
209
210	return $results;
211	}
212
213	/******************************************
214	* Get user interactions count in a conversation
215	******************************************/
216	function get_user_interaction_count( $conversation ) {
217	if ( ! empty( $conversation ) ) {
218	$interaction = (int) substr_count( $conversation, 'wp-chat-user-msg' );
219
220	if ( $interaction == 0 ) {
221	$interaction = (int) substr_count( $conversation, 'woo-chat-user-msg' );
222	}
223
224	return $interaction;
225	}
226	}
227