fluentform / app / Modules / Entries / Report.php

Report.php in Fluent Forms – Customizable Contact Forms, Survey, Quiz, & Conversational Form Builder 4.3.13, at app/Modules/Entries/Report.php

348 lines 12.0 KB

Raw Download Zip

1	<?php
2
3	namespace FluentForm\App\Modules\Entries;
4
5	use FluentForm\App\Helpers\Helper;
6	use FluentForm\App\Modules\Form\FormFieldsParser;
7	use FluentForm\Framework\Foundation\Application;
8	use FluentForm\Framework\Helpers\ArrayHelper;
9
10	class Report
11	{
12	private $app;
13	private $formModel;
14
15	public function __construct(Application $app)
16	{
17	$this->app = $app;
18	$this->formModel = wpFluent()->table('fluentform_forms');
19	}
20
21	/**
22	* @param bool $formId
23	*/
24	public function getReport($formId = false)
25	{
26	if (!$formId) {
27	$formId = intval($_REQUEST['form_id']);
28	}
29
30	$this->maybeMigrateData($formId);
31
32	$statuses = $this->app->request->get('statuses');
33
34	$form = $this->formModel->find($formId);
35
36	$report = $this->generateReport($form, $statuses);
37
38	wp_send_json_success($report);
39	}
40
41
42	public function generateReport($form, $statuses = [])
43	{
44	$formInputs = FormFieldsParser::getEntryInputs($form, ['admin_label', 'element', 'options']);
45
46	$inputLabels = FormFieldsParser::getAdminLabels($form, $formInputs);
47
48	$elements = [];
49
50	foreach ($formInputs as $inputName => $input) {
51	$elements[$inputName] = $input['element'];
52	if ($input['element'] == 'select_country') {
53	$formInputs[$inputName]['options'] = getFluentFormCountryList();
54	}
55	}
56
57	$reportableInputs = Helper::getReportableInputs();
58
59	$formReportableInputs = array_intersect($reportableInputs, array_values($elements));
60
61	$reportableInputs = Helper::getSubFieldReportableInputs();
62	$formSubFieldInputs = array_intersect($reportableInputs, array_values($elements));
63
64	if (!$formReportableInputs && !$formSubFieldInputs) {
65	return [
66	'report_items' => (object)[],
67	'total_entries' => 0
68	];
69	}
70
71	$inputs = [];
72	$subfieldInputs = [];
73	foreach ($elements as $elementKey => $element) {
74	if (in_array($element, $formReportableInputs)) {
75	$inputs[$elementKey] = $element;
76	}
77	if (in_array($element, $formSubFieldInputs)) {
78	$subfieldInputs[$elementKey] = $element;
79	}
80	}
81
82	$whereClasuses = [];
83
84	if ($statuses) {
85	$whereClasuses['fluentform_submissions.status'] = [
86	'method' => 'whereIn',
87	'values' => $statuses
88	];
89	}
90
91	$reports = $this->getInputReport($form->id, array_keys($inputs), $whereClasuses);
92
93	$subFieldReports = $this->getSubFieldInputReport($form->id, array_keys($subfieldInputs), $whereClasuses);
94
95	$reports = array_merge($reports, $subFieldReports);
96
97	foreach ($reports as $reportKey => $report) {
98	$reports[$reportKey]['label'] = $inputLabels[$reportKey];
99	$reports[$reportKey]['element'] = ArrayHelper::get($inputs, $reportKey, []);
100	$reports[$reportKey]['options'] = $formInputs[$reportKey]['options'];
101	}
102
103	return [
104	'report_items' => $reports,
105	'total_entries' => $this->getEntryCounts($form->id, $statuses),
106	'browsers' => $this->getbrowserCounts($form->id, $statuses),
107	'devices' => $this->getDeviceCounts($form->id, $statuses),
108	];
109	}
110
111	public function getInputReport($formId, $fieldNames, $whereClasuses)
112	{
113	if(!$fieldNames) {
114	return [];
115	}
116	global $wpdb;
117	$reportQuery = wpFluent()->table('fluentform_entry_details')
118	->select([
119	'fluentform_entry_details.field_name',
120	'fluentform_entry_details.sub_field_name',
121	'fluentform_entry_details.field_value'
122	])
123	->select(wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_entry_details.field_name) as total_count'))
124	->where('fluentform_entry_details.form_id', $formId)
125	->whereIn('fluentform_entry_details.field_name', $fieldNames)
126	->rightJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');
127
128	if ($whereClasuses) {
129	foreach ($whereClasuses as $clauseColumn => $clasus) {
130	$reportQuery = $reportQuery->{$clasus['method']}($clauseColumn, $clasus['values']);
131	}
132	}
133
134	$reports = $reportQuery->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.field_value'])
135	->get();
136
137	$formattedReports = [];
138	foreach ($reports as $report) {
139	$formattedReports[$report->field_name]['reports'][] = [
140	'value' => maybe_unserialize($report->field_value),
141	'count' => $report->total_count,
142	'sub_field' => $report->sub_field_name,
143	];
144	$formattedReports[$report->field_name]['total_entry'] = $this->getEntryTotal($report->field_name, $formId, $whereClasuses);
145	}
146
147	return $formattedReports;
148	}
149
150	public function getSubFieldInputReport($formId, $fieldNames, $whereClasuses)
151	{
152	if(!$fieldNames) {
153	return [];
154	}
155
156	global $wpdb;
157	$reportQuery = wpFluent()->table('fluentform_entry_details')
158	->select([
159	'fluentform_entry_details.field_name',
160	'fluentform_entry_details.sub_field_name',
161	'fluentform_entry_details.field_value'
162	])
163	->select(wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_entry_details.field_name) as total_count'))
164	->where('fluentform_entry_details.form_id', $formId)
165	->whereIn('fluentform_entry_details.field_name', $fieldNames)
166	->leftJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');
167
168	if ($whereClasuses) {
169	foreach ($whereClasuses as $clauseColumn => $clasus) {
170	$reportQuery = $reportQuery->{$clasus['method']}($clauseColumn, $clasus['values']);
171	}
172	}
173
174	$reports = $reportQuery->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.field_value', 'fluentform_entry_details.sub_field_name'])
175	->get();
176
177
178	return $this->getFormattedReportsForSubInputs($reports, $formId, $whereClasuses);
179	}
180
181	protected function getFormattedReportsForSubInputs($reports, $formId, $whereClasuses)
182	{
183	if (!count($reports)) {
184	return [];
185	}
186
187	$formattedReports = [];
188
189	foreach ($reports as $report) {
190	$this->setReportForSubInput((array) $report, $formattedReports);
191	}
192
193	foreach ($formattedReports as $fieldName => $val) {
194	$formattedReports[$fieldName]['total_entry'] = $this->getEntryTotal(
195	$report->field_name, $formId, $whereClasuses
196	);
197
198	$formattedReports[$fieldName]['reports'] = array_values(
199	$formattedReports[$fieldName]['reports']
200	);
201	}
202
203	return $formattedReports;
204	}
205
206	protected function setReportForSubInput($report, &$formattedReports)
207	{
208	$filedValue = maybe_unserialize($report['field_value']);
209
210	if (is_array($filedValue)) {
211	foreach ($filedValue as $fVal) {
212	$this->setReportForSubInput(
213	array_merge($report, ['field_value' => $fVal]),
214	$formattedReports
215	);
216	}
217	} else {
218	$value = $report['sub_field_name'] . ' : ' . $filedValue;
219	$count = ArrayHelper::get($formattedReports, $report['field_name'] . '.reports.' . $value . '.count');
220	$count = $count ? $count + $report['total_count'] : $report['total_count'];
221
222	$formattedReports[$report['field_name']]['reports'][$value] = [
223	'value' => $value,
224	'count' => $count,
225	'sub_field' => $report['sub_field_name'],
226	];
227	}
228	}
229
230	public function getEntryTotal($fieldName, $formId, $whereClasuses)
231	{
232	$query = wpFluent()->table('fluentform_entry_details')
233	->select('fluentform_entry_details.id')
234	->where('fluentform_entry_details.form_id', $formId)
235	->where('fluentform_entry_details.field_name', $fieldName)
236	->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.submission_id'])
237	->leftJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');
238
239	if ($whereClasuses) {
240	foreach ($whereClasuses as $clauseColumn => $clasus) {
241	$query = $query->{$clasus['method']}($clauseColumn, $clasus['values']);
242	}
243	}
244
245	return $query->count();
246	}
247
248	private function maybeMigrateData($formId)
249	{
250	// We have to check if we need to migrate the data
251	if (Helper::getFormMeta($formId, 'report_data_migrated') == 'yes') {
252	return true;
253	}
254	global $wpdb;
255	// let's migrate the data
256	$unmigratedData = wpFluent()
257	->table('fluentform_submissions')
258	->select([
259	'fluentform_submissions.id',
260	'fluentform_submissions.response'
261	])
262	->where('fluentform_submissions.form_id', $formId)
263	->where(wpFluent()->raw($wpdb->prefix . 'fluentform_submissions.id NOT IN (SELECT submission_id from ' . $wpdb->prefix . 'fluentform_entry_details)'))
264	->get();
265
266	if (!$unmigratedData) {
267	return Helper::setFormMeta($formId, 'report_data_migrated', 'yes');
268	}
269
270	$entries = new Entries();
271	foreach ($unmigratedData as $datum) {
272	$value = json_decode($datum->response, true);
273	$entries->recordEntryDetails($datum->id, $formId, $value);
274	}
275
276	return true;
277
278	}
279
280	private function getEntryCounts($formId, $statuses = false)
281	{
282	$totalEntries = wpFluent()
283	->table('fluentform_submissions')
284	->where('fluentform_submissions.form_id', $formId);
285
286	if ($statuses) {
287	$totalEntries = $totalEntries->whereIn('fluentform_submissions.status', $statuses);
288	} else {
289	$totalEntries = $totalEntries->where('fluentform_submissions.status', '!=', 'trashed');
290	}
291	return $totalEntries->count();
292	}
293
294	private function getBrowserCounts($formId, $statuses)
295	{
296	global $wpdb;
297	$browserCounts = wpFluent()->table('fluentform_submissions')
298	->select([
299	wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_submissions.id) as total_count'),
300	'browser'
301	])
302	->where('form_id', $formId);
303	if ($statuses) {
304	$browserCounts = $browserCounts->whereIn('status', $statuses);
305	} else {
306	$browserCounts = $browserCounts->where('status', '!=', 'trashed');
307	}
308
309	$browserCounts = $browserCounts->groupBy('browser')
310	->get();
311
312	$formattedData = [];
313	foreach ($browserCounts as $browser) {
314	$formattedData[$browser->browser] = $browser->total_count;
315	}
316
317	return $formattedData;
318
319	}
320
321	private function getDeviceCounts($formId, $statuses)
322	{
323	global $wpdb;
324	$deviceCounts = wpFluent()->table('fluentform_submissions')
325	->select([
326	wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_submissions.id) as total_count'),
327	'device'
328	])
329	->where('form_id', $formId);
330	if ($statuses) {
331	$deviceCounts = $deviceCounts->whereIn('status', $statuses);
332	} else {
333	$deviceCounts = $deviceCounts->where('status', '!=', 'trashed');
334	}
335
336	$deviceCounts = $deviceCounts->groupBy('device')
337	->get();
338
339	$formattedData = [];
340	foreach ($deviceCounts as $deviceCount) {
341	$formattedData[$deviceCount->device] = $deviceCount->total_count;
342	}
343	return $formattedData;
344	}
345
346
347	}
348