Diff: STRATO-apps/wordpress_03/app/wp-content/plugins/fluentform/app/Services/Report/ReportHelper.php
Keine Baseline-Datei – Diff nur gegen leer.
1
-
1
+
<?php
2
+
3
+
namespace FluentForm\App\Services\Report;
4
+
5
+
use FluentForm\App\Helpers\Helper;
6
+
use FluentForm\App\Models\EntryDetails;
7
+
use FluentForm\App\Models\Form;
8
+
use FluentForm\App\Models\FormAnalytics;
9
+
use FluentForm\App\Models\Log;
10
+
use FluentForm\App\Models\Submission;
11
+
use FluentForm\App\Modules\Form\FormFieldsParser;
12
+
use FluentForm\App\Modules\Payments\PaymentHelper;
13
+
use FluentForm\App\Services\Submission\SubmissionService;
14
+
use FluentForm\Framework\Helpers\ArrayHelper as Arr;
15
+
16
+
class ReportHelper
17
+
{
18
+
public static function generateReport($form, $statuses = ['read', 'unread', 'unapproved', 'approved', 'declined', 'unconfirmed', 'confirmed'])
19
+
{
20
+
$formInputs = FormFieldsParser::getEntryInputs($form, ['admin_label', 'element', 'options']);
21
+
$inputLabels = FormFieldsParser::getAdminLabels($form, $formInputs);
22
+
23
+
$elements = [];
24
+
foreach ($formInputs as $inputName => $input) {
25
+
$elements[$inputName] = $input['element'];
26
+
if ('select_country' == $input['element']) {
27
+
$formInputs[$inputName]['options'] = getFluentFormCountryList();
28
+
}
29
+
}
30
+
31
+
$reportableInputs = Helper::getReportableInputs();
32
+
$formReportableInputs = array_intersect($reportableInputs, array_values($elements));
33
+
$reportableInputs = Helper::getSubFieldReportableInputs();
34
+
$formSubFieldInputs = array_intersect($reportableInputs, array_values($elements));
35
+
36
+
37
+
if (!$formReportableInputs && !$formSubFieldInputs) {
38
+
return [
39
+
'report_items' => (object)[],
40
+
'total_entries' => static::getEntryCounts($form->id, $statuses),
41
+
'browsers' => static::getBrowserCounts($form->id, $statuses),
42
+
'devices' => static::getDeviceCounts($form->id, $statuses),
43
+
];
44
+
}
45
+
46
+
$inputs = [];
47
+
$subfieldInputs = [];
48
+
foreach ($elements as $elementKey => $element) {
49
+
if (in_array($element, $formReportableInputs)) {
50
+
$inputs[$elementKey] = $element;
51
+
}
52
+
if (in_array($element, $formSubFieldInputs)) {
53
+
$subfieldInputs[$elementKey] = $element;
54
+
}
55
+
}
56
+
57
+
$reports = static::getInputReport($form->id, array_keys($inputs), $statuses);
58
+
59
+
$subFieldReports = static::getSubFieldInputReport($form->id, array_keys($subfieldInputs), $statuses);
60
+
$reports = array_merge($reports, $subFieldReports);
61
+
foreach ($reports as $reportKey => $report) {
62
+
$reports[$reportKey]['label'] = $inputLabels[$reportKey];
63
+
$reports[$reportKey]['element'] = Arr::get($inputs, $reportKey, []);
64
+
$reports[$reportKey]['options'] = $formInputs[$reportKey]['options'];
65
+
}
66
+
67
+
return [
68
+
'report_items' => $reports,
69
+
'total_entries' => static::getEntryCounts($form->id, $statuses),
70
+
'browsers' => static::getBrowserCounts($form->id, $statuses),
71
+
'devices' => static::getDeviceCounts($form->id, $statuses),
72
+
];
73
+
}
74
+
75
+
public static function getInputReport(
76
+
$formId,
77
+
$fieldNames,
78
+
$statuses = ['read', 'unread', 'unapproved', 'approved', 'declined', 'unconfirmed', 'confirmed']
79
+
) {
80
+
if (!$fieldNames) {
81
+
return [];
82
+
}
83
+
84
+
$reports = EntryDetails::select(['field_name', 'sub_field_name', 'field_value'])
85
+
->where('form_id', $formId)
86
+
->whereIn('field_name', $fieldNames)
87
+
->when(
88
+
is_array($statuses) && (count($statuses) > 0),
89
+
function ($q) use ($statuses) {
90
+
return $q->whereHas('submission', function ($q) use ($statuses) {
91
+
return $q->whereIn('status', $statuses);
92
+
});
93
+
})
94
+
->selectRaw('COUNT(field_name) AS total_count')
95
+
->groupBy(['field_name', 'field_value'])
96
+
->get();
97
+
98
+
$formattedReports = [];
99
+
foreach ($reports as $report) {
100
+
$formattedReports[$report->field_name]['reports'][] = [
101
+
'value' => Helper::safeUnserialize($report->field_value),
102
+
'count' => $report->total_count,
103
+
'sub_field' => $report->sub_field_name,
104
+
];
105
+
106
+
$formattedReports[$report->field_name]['total_entry'] = static::getEntryTotal($report->field_name, $formId,
107
+
$statuses);
108
+
}
109
+
if ($formattedReports) {
110
+
//sync with form field order
111
+
$formattedReports = array_replace(array_intersect_key(array_flip($fieldNames), $formattedReports),
112
+
$formattedReports);
113
+
}
114
+
return $formattedReports;
115
+
}
116
+
117
+
public static function getSubFieldInputReport($formId, $fieldNames, $statuses)
118
+
{
119
+
if (!$fieldNames) {
120
+
return [];
121
+
}
122
+
123
+
$reports = EntryDetails::select(['field_name', 'sub_field_name', 'field_value'])
124
+
->selectRaw('COUNT(field_name) AS total_count')
125
+
->where('form_id', $formId)
126
+
->whereIn('field_name', $fieldNames)
127
+
->when(
128
+
is_array($statuses) && (count($statuses) > 0),
129
+
function ($q) use ($statuses) {
130
+
return $q->whereHas('submission', function ($q) use ($statuses) {
131
+
return $q->whereIn('status', $statuses);
132
+
});
133
+
})
134
+
->groupBy(['field_name', 'field_value', 'sub_field_name'])
135
+
->get()->toArray();
136
+
return static::getFormattedReportsForSubInputs($reports, $formId, $statuses);
137
+
}
138
+
139
+
protected static function getFormattedReportsForSubInputs($reports, $formId, $statuses)
140
+
{
141
+
if (!count($reports)) {
142
+
return [];
143
+
}
144
+
$formattedReports = [];
145
+
foreach ($reports as $report) {
146
+
static::setReportForSubInput((array)$report, $formattedReports);
147
+
}
148
+
foreach ($formattedReports as $fieldName => $val) {
149
+
$formattedReports[$fieldName]['total_entry'] = static::getEntryTotal(
150
+
Arr::get($report, 'field_name'),
151
+
$formId,
152
+
$statuses
153
+
);
154
+
$formattedReports[$fieldName]['reports'] = array_values(
155
+
$formattedReports[$fieldName]['reports']
156
+
);
157
+
}
158
+
return $formattedReports;
159
+
}
160
+
161
+
protected static function setReportForSubInput($report, &$formattedReports)
162
+
{
163
+
$filedValue = Helper::safeUnserialize(Arr::get($report, 'field_value'));
164
+
165
+
if (is_array($filedValue)) {
166
+
foreach ($filedValue as $fVal) {
167
+
static::setReportForSubInput(
168
+
array_merge($report, ['field_value' => $fVal]),
169
+
$formattedReports
170
+
);
171
+
}
172
+
} else {
173
+
$value = Arr::get($report, 'sub_field_name') . ' : ' . $filedValue;
174
+
$count = Arr::get($formattedReports, $report['field_name'] . '.reports.' . $value . '.count');
175
+
$count = $count ? $count + Arr::get($report, 'total_count') : Arr::get($report, 'total_count');
176
+
177
+
$formattedReports[$report['field_name']]['reports'][$value] = [
178
+
'value' => $value,
179
+
'count' => $count,
180
+
'sub_field' => $report['sub_field_name'],
181
+
];
182
+
}
183
+
}
184
+
185
+
public static function getEntryTotal($fieldName, $formId, $statuses = false)
186
+
{
187
+
return EntryDetails::select('id')->where('form_id', $formId)
188
+
->where('field_name', $fieldName)
189
+
->when(
190
+
is_array($statuses) && (count($statuses) > 0),
191
+
function ($q) use ($statuses) {
192
+
return $q->whereHas('submission', function ($q) use ($statuses) {
193
+
return $q->whereIn('status', $statuses);
194
+
});
195
+
}
196
+
)
197
+
->distinct(['field_name', 'submission_id'])
198
+
->count();
199
+
}
200
+
201
+
private static function getEntryCounts($formId, $statuses = false)
202
+
{
203
+
return Submission::where('form_id', $formId)
204
+
->when(
205
+
is_array($statuses) && (count($statuses) > 0),
206
+
function ($q) use ($statuses) {
207
+
return $q->whereIn('status', $statuses);
208
+
})
209
+
->when(!$statuses, function ($q) {
210
+
return $q->where('status', '!=', 'trashed');
211
+
})->count();
212
+
}
213
+
214
+
public static function getBrowserCounts($formId, $statuses = false)
215
+
{
216
+
return static::getCounts($formId, 'browser', $statuses);
217
+
}
218
+
219
+
public static function getDeviceCounts($formId, $statuses = false)
220
+
{
221
+
return static::getCounts($formId, 'device', $statuses);
222
+
}
223
+
224
+
private static function getCounts($formId, $for, $statuses)
225
+
{
226
+
$deviceCounts = Submission::select([
227
+
"$for",
228
+
])
229
+
->selectRaw('COUNT(id) as total_count')
230
+
->where('form_id', $formId)
231
+
->when(
232
+
is_array($statuses) && (count($statuses) > 0),
233
+
function ($q) use ($statuses) {
234
+
return $q->whereIn('status', $statuses);
235
+
})
236
+
->when(!$statuses, function ($q) {
237
+
return $q->where('status', '!=', 'trashed');
238
+
})
239
+
->groupBy("$for")->get();
240
+
241
+
$formattedData = [];
242
+
foreach ($deviceCounts as $deviceCount) {
243
+
$formattedData[$deviceCount->{$for}] = $deviceCount->total_count;
244
+
}
245
+
return $formattedData;
246
+
}
247
+
248
+
public static function maybeMigrateData($formId)
249
+
{
250
+
// We have to check if we need to migrate the data
251
+
if ('yes' == Helper::getFormMeta($formId, 'report_data_migrated')) {
252
+
return true;
253
+
}
254
+
// let's migrate the data
255
+
$unmigratedData = Submission::select(['id', 'response'])
256
+
->where('form_id', $formId)
257
+
->doesntHave('entryDetails')
258
+
->get();
259
+
260
+
if (!$unmigratedData) {
261
+
return Helper::setFormMeta($formId, 'report_data_migrated', 'yes');
262
+
}
263
+
$submissionService = new SubmissionService();
264
+
foreach ($unmigratedData as $datum) {
265
+
$value = json_decode($datum->response, true);
266
+
$submissionService->recordEntryDetails($datum->id, $formId, $value);
267
+
}
268
+
return true;
269
+
}
270
+
271
+
/**
272
+
* Get overview chart data
273
+
*/
274
+
public static function getOverviewChartData($startDate, $endDate, $formId, $view)
275
+
{
276
+
// Process and fix date ranges if needed
277
+
list($startDate, $endDate) = self::processDateRange($startDate, $endDate);
278
+
279
+
// Calculate date difference to determine grouping
280
+
$startDateTime = new \DateTime($startDate);
281
+
$endDateTime = new \DateTime($endDate);
282
+
$interval = $startDateTime->diff($endDateTime);
283
+
$daysInterval = $interval->days + 1;
284
+
285
+
// Determine grouping mode based on date range
286
+
$groupingMode = self::getGroupingMode($daysInterval);
287
+
$data = self::getAggregatedData($startDate, $endDate, $groupingMode, $view, $formId);
288
+
// Get date labels based on grouping mode
289
+
$dateLabels = self::getDateLabels($startDateTime, $endDateTime, $groupingMode);
290
+
// Format the data for the chart
291
+
$chartData = self::formatDataForChart($dateLabels, $data, $formId);
292
+
// Get views data based on the ip
293
+
$views = self::getFormViews($startDate, $endDate, $groupingMode, $formId);
294
+
if ($views) {
295
+
$chartData['values']['views'] = array_values(self::fillMissingData($dateLabels['dates'], $views));
296
+
}
297
+
return $chartData;
298
+
}
299
+
300
+
301
+
302
+
303
+
304
+
public static function getFormStats($startDate, $endDate, $formId)
305
+
{
306
+
// Process and fix date ranges if needed
307
+
list($startDate, $endDate) = self::processDateRange($startDate, $endDate);
308
+
309
+
// Calculate the date range duration to determine previous period
310
+
$startDateTime = new \DateTime($startDate);
311
+
$endDateTime = new \DateTime($endDate);
312
+
$interval = $startDateTime->diff($endDateTime);
313
+
$daysDifference = $interval->days;
314
+
315
+
// Calculate previous period dates (same duration, shifted back)
316
+
$previousEndDateTime = clone $startDateTime;
317
+
$previousEndDateTime->modify('-1 day');
318
+
$previousStartDateTime = clone $previousEndDateTime;
319
+
$previousStartDateTime->modify("-{$daysDifference} days");
320
+
321
+
$previousStartDate = $previousStartDateTime->format('Y-m-d H:i:s');
322
+
$previousEndDate = $previousEndDateTime->format('Y-m-d H:i:s');
323
+
324
+
// Get submission counts
325
+
$periodSubmissions = Submission::whereBetween('created_at', [$startDate, $endDate])
326
+
->when($formId, function ($q) use ($formId) {
327
+
return $q->where('form_id', $formId);
328
+
})->count();
329
+
$previousPeriodSubmissions = Submission::whereBetween('created_at',
330
+
[$previousStartDate, $previousEndDate])
331
+
->when($formId, function ($q) use ($formId) {
332
+
return $q->where('form_id', $formId);
333
+
})->count();
334
+
335
+
// Get submission status counts (grouped)
336
+
$statusCounts = Submission::whereBetween('created_at', [$startDate, $endDate])
337
+
->selectRaw('status, COUNT(*) as count')
338
+
->when($formId, function ($q) use ($formId) {
339
+
return $q->where('form_id', $formId);
340
+
})
341
+
->groupBy('status')
342
+
->pluck('count', 'status');
343
+
344
+
$unreadSubmissions = intval(Arr::get($statusCounts, 'unread', 0));
345
+
$readSubmissions = intval(Arr::get($statusCounts, 'read', 0));
346
+
$periodSpamSubmissions = intval(Arr::get($statusCounts, 'spam', 0));
347
+
348
+
349
+
$previousStatusCounts = Submission::whereBetween('created_at', [$previousStartDate, $previousEndDate])
350
+
->selectRaw('status, COUNT(*) as count')
351
+
->when($formId, function ($q) use ($formId) {
352
+
return $q->where('form_id', $formId);
353
+
})
354
+
->groupBy('status')
355
+
->pluck('count', 'status');
356
+
357
+
$previousSpamSubmissions = intval(Arr::get($previousStatusCounts, 'spam', 0));
358
+
359
+
// Get active integrations count from wp_options
360
+
$modulesStatus = get_option('fluentform_global_modules_status');
361
+
$activeIntegrations = count(array_filter($modulesStatus, function ($status) {
362
+
return $status === 'yes' || $status == 1 || $status == 'true';
363
+
}));
364
+
365
+
// Calculate period growth percentage
366
+
$growthPercentage = 0;
367
+
if ($previousPeriodSubmissions > 0) {
368
+
$growthPercentage = round((($periodSubmissions - $previousPeriodSubmissions) / $previousPeriodSubmissions) * 100,
369
+
1);
370
+
} elseif ($periodSubmissions > 0) {
371
+
$growthPercentage = 100;
372
+
}
373
+
374
+
$growthText = $growthPercentage > 0 ? '+' . $growthPercentage . '%' : $growthPercentage . '%';
375
+
$growthType = $growthPercentage > 0 ? 'up' : ($growthPercentage < 0 ? 'down' : 'neutral');
376
+
377
+
// calculate spam percentage
378
+
$spamPercentage = 0;
379
+
if ($previousSpamSubmissions > 0) {
380
+
$spamPercentage = round((($periodSpamSubmissions - $previousSpamSubmissions) / $previousSpamSubmissions) * 100,
381
+
1);
382
+
} elseif ($periodSpamSubmissions > 0) {
383
+
$spamPercentage = 100;
384
+
}
385
+
$spamText = $spamPercentage > 0 ? '+' . $spamPercentage . '%' : $spamPercentage . '%';
386
+
$spamType = $spamPercentage > 0 ? 'down' : ($spamPercentage < 0 ? 'up' : 'neutral'); // Refunds going up is bad
387
+
388
+
// Active forms
389
+
$periodActiveFormsCount = Form::where('status', 'published')->whereBetween('created_at',
390
+
[$startDate, $endDate])->count();
391
+
$previousActiveFormsCount = Form::where('status', 'published')->whereBetween('created_at',
392
+
[$previousStartDate, $previousEndDate])->count();
393
+
$activeFormsPercentage = 0;
394
+
if ($previousActiveFormsCount > 0) {
395
+
$activeFormsPercentage = round((($periodActiveFormsCount - $previousActiveFormsCount) / $previousActiveFormsCount) * 100,
396
+
1);
397
+
} elseif ($periodActiveFormsCount > 0) {
398
+
$activeFormsPercentage = 100;
399
+
}
400
+
$activeFormsText = $activeFormsPercentage > 0 ? '+' . $activeFormsPercentage . '%' : $activeFormsPercentage . '%';
401
+
$activeFormsType = $activeFormsPercentage > 0 ? 'up' : ($activeFormsPercentage < 0 ? 'down' : 'neutral');
402
+
403
+
$readRate = $periodSubmissions > 0 ? round(($readSubmissions / $periodSubmissions) * 100, 1) : 0;
404
+
405
+
$stats = [
406
+
'period' => $daysDifference . ' days',
407
+
'total_submissions' => [
408
+
'value' => $periodSubmissions,
409
+
'period_value' => $periodSubmissions,
410
+
'change' => $growthText,
411
+
'change_type' => $growthType
412
+
],
413
+
'spam_submissions' => [
414
+
'value' => $periodSpamSubmissions,
415
+
'period_value' => $previousSpamSubmissions,
416
+
'change' => $spamText,
417
+
'change_type' => $spamType
418
+
],
419
+
'active_integrations' => [
420
+
'value' => $activeIntegrations,
421
+
],
422
+
'unread_submissions' => [
423
+
'value' => $unreadSubmissions,
424
+
],
425
+
'read_submissions' => [
426
+
'value' => $readSubmissions,
427
+
],
428
+
'active_forms' => [
429
+
'value' => $periodActiveFormsCount,
430
+
'change' => $activeFormsText,
431
+
'change_type' => $activeFormsType
432
+
],
433
+
'read_submission_rate' => [
434
+
'value' => $readRate,
435
+
]
436
+
];
437
+
438
+
// Add payment statistics if payment module is enabled
439
+
$paymentSettings = get_option('__fluentform_payment_module_settings');
440
+
if ($paymentSettings && Arr::get($paymentSettings, 'status') === 'yes') {
441
+
// Get payment statistics
442
+
$paymentStats = self::getPaymentStats($startDate, $endDate, $previousStartDate, $previousEndDate, $formId);
443
+
$stats = array_merge($stats, $paymentStats);
444
+
}
445
+
446
+
return $stats;
447
+
}
448
+
449
+
450
+
/**
451
+
* Initialize heatmap data structure based on aggregation type
452
+
*/
453
+
protected static function initializeHeatmapData($aggregationType)
454
+
{
455
+
if ($aggregationType === 'day_of_week') {
456
+
$dayNames = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
457
+
$heatmapData = [];
458
+
foreach ($dayNames as $day) {
459
+
$heatmapData[$day] = array_fill(0, 24, 0); // 24 time slots (0-23 hours), all initialized to 0
460
+
}
461
+
return $heatmapData;
462
+
}
463
+
464
+
return [];
465
+
}
466
+
467
+
/**
468
+
* Get submission data for heatmap with appropriate grouping
469
+
* Optimized version with better query performance
470
+
*/
471
+
protected static function getHeatmapSubmissionData($startDate, $endDate, $formId, $aggregationType)
472
+
{
473
+
if ($aggregationType === 'day_of_week') {
474
+
$query = Submission::selectRaw('
475
+
DAYOFWEEK(created_at) as day_of_week,
476
+
HOUR(created_at) as submission_hour,
477
+
COUNT(*) as count
478
+
')
479
+
->whereBetween('created_at', [$startDate, $endDate])
480
+
->when($formId, function ($q) use ($formId) {
481
+
return $q->where('form_id', $formId);
482
+
})
483
+
->whereNotIn('status', ['trashed', 'spam'])
484
+
->groupBy('day_of_week', 'submission_hour')
485
+
->orderBy('day_of_week')
486
+
->orderBy('submission_hour');
487
+
488
+
return $query->get();
489
+
}
490
+
491
+
return collect([]);
492
+
}
493
+
494
+
495
+
public static function getApiLogs($startDate, $endDate, $formId = null)
496
+
{
497
+
// Process date range
498
+
list($startDate, $endDate) = self::processDateRange($startDate, $endDate);
499
+
500
+
// Calculate date difference to determine grouping
501
+
$startDateTime = new \DateTime($startDate);
502
+
$endDateTime = new \DateTime($endDate);
503
+
$interval = $startDateTime->diff($endDateTime);
504
+
$daysInterval = $interval->days + 1;
505
+
506
+
// Determine grouping mode based on date range
507
+
$groupingMode = self::getGroupingMode($daysInterval);
508
+
509
+
// Define the date format based on grouping mode
510
+
if ($groupingMode === 'day') {
511
+
$dateFormat = "DATE(created_at)";
512
+
} elseif ($groupingMode === '3days') {
513
+
$dateFormat = "DATE(created_at)";
514
+
} elseif ($groupingMode === 'week') {
515
+
$dateFormat = "DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY))";
516
+
} else { // month
517
+
$dateFormat = "DATE_FORMAT(created_at, '%Y-%m-01')";
518
+
}
519
+
520
+
// Components to exclude
521
+
$excludedComponents = [
522
+
'postFeeds',
523
+
'AdminApproval',
524
+
'Payment',
525
+
'EntryEditor',
526
+
'DoubleOptin',
527
+
'Subscription',
528
+
'UserRegistration',
529
+
'Akismet Integration',
530
+
'CleanTalk API Integration'
531
+
];
532
+
533
+
// Get logs grouped by date and status using Eloquent, excluding specific components
534
+
$logsQuery = Log::whereBetween('created_at', [$startDate, $endDate]);
535
+
536
+
// Exclude components - handle both NULL and specific values
537
+
$logsQuery->where(function ($query) use ($excludedComponents) {
538
+
$query->whereNull('component')
539
+
->orWhereNotIn('component', $excludedComponents);
540
+
});
541
+
542
+
if ($formId) {
543
+
$logsQuery->where('parent_source_id', $formId);
544
+
}
545
+
546
+
$results = $logsQuery->selectRaw($dateFormat . ' as log_date')
547
+
->selectRaw('status')
548
+
->selectRaw('COUNT(*) as count')
549
+
->groupBy('log_date', 'status')
550
+
->orderBy('log_date')
551
+
->get();
552
+
553
+
// Get total counts by status (also excluding the specific components)
554
+
$totalsQuery = Log::whereBetween('created_at', [$startDate, $endDate]);
555
+
556
+
$totalsQuery->where(function ($query) use ($excludedComponents) {
557
+
$query->whereNull('component')
558
+
->orWhereNotIn('component', $excludedComponents);
559
+
});
560
+
561
+
$totalsResults = $totalsQuery->selectRaw('status')
562
+
->selectRaw('COUNT(*) as count')
563
+
->groupBy('status')
564
+
->get();
565
+
566
+
$totals = [
567
+
'success' => 0,
568
+
'pending' => 0,
569
+
'failed' => 0
570
+
];
571
+
572
+
foreach ($totalsResults as $total) {
573
+
$status = strtolower($total->status);
574
+
if (isset($totals[$status])) {
575
+
$totals[$status] = (int)$total->count;
576
+
}
577
+
}
578
+
579
+
// Get date labels and prepare data
580
+
$dateLabels = self::getDateLabels($startDateTime, $endDateTime, $groupingMode);
581
+
$dates = $dateLabels['dates'];
582
+
$formattedLabels = $dateLabels['labels'];
583
+
584
+
// Initialize data structure - always with all dates, even if no data exists
585
+
$seriesData = [
586
+
'success' => array_fill_keys($dates, 0),
587
+
'pending' => array_fill_keys($dates, 0),
588
+
'failed' => array_fill_keys($dates, 0)
589
+
];
590
+
591
+
// Fill in data from results when available
592
+
foreach ($results as $row) {
593
+
$date = $row->log_date;
594
+
$status = strtolower($row->status);
595
+
$count = (int)$row->count;
596
+
597
+
// Map status to our categories
598
+
if ($status === 'success' || $status === 'pending' || $status === 'failed') {
599
+
if (isset($seriesData[$status][$date])) {
600
+
$seriesData[$status][$date] = $count;
601
+
}
602
+
}
603
+
}
604
+
605
+
return [
606
+
'logs_data' => [
607
+
'categories' => $formattedLabels,
608
+
'series' => $seriesData
609
+
],
610
+
'totals' => $totals,
611
+
'start_date' => $startDate,
612
+
'end_date' => $endDate
613
+
];
614
+
}
615
+
616
+
/**
617
+
* Get top performing forms by entries, views, or payments
618
+
*/
619
+
public static function getTopPerformingForms($startDate, $endDate, $metric = 'entries')
620
+
{
621
+
list($startDate, $endDate) = self::processDateRange($startDate, $endDate);
622
+
global $wpdb;
623
+
$prefix = $wpdb->prefix;
624
+
$formResults = [];
625
+
$disableMessage = '';
626
+
627
+
switch ($metric) {
628
+
case 'entries':
629
+
// Use Form model with Submission relationship
630
+
$results = Form::select(['id', 'title'])
631
+
->withCount([
632
+
'submissions' => function ($q) use ($startDate, $endDate) {
633
+
$q->whereBetween('created_at', [$startDate, $endDate]);
634
+
$q->whereNotIn('status', ['trashed', 'spam']);
635
+
}
636
+
])
637
+
->orderBy('submissions_count', 'DESC')
638
+
->limit(5)
639
+
->get();
640
+
641
+
// Map the results to standard format
642
+
foreach ($results as $form) {
643
+
$formResults[] = (object)[
644
+
'id' => $form->id,
645
+
'title' => $form->title,
646
+
'value' => $form->submissions_count
647
+
];
648
+
}
649
+
break;
650
+
651
+
case 'payments':
652
+
// Check if payment module is enabled
653
+
$paymentSettings = get_option('__fluentform_payment_module_settings');
654
+
if ($paymentSettings && Arr::get($paymentSettings, 'status')) {
655
+
$results = wpFluent()->table('fluentform_forms')
656
+
->select([
657
+
'fluentform_forms.id',
658
+
'fluentform_forms.title',
659
+
wpFluent()->raw("COALESCE(SUM({$prefix}fluentform_transactions.payment_total), 0) as raw_value")
660
+
])
661
+
->leftJoin('fluentform_transactions', 'fluentform_forms.id', '=',
662
+
'fluentform_transactions.form_id')
663
+
->whereBetween('fluentform_transactions.created_at', [$startDate, $endDate])
664
+
->where('fluentform_transactions.status', 'paid')
665
+
->groupBy('fluentform_forms.id')
666
+
->orderBy('raw_value', 'DESC')
667
+
->limit(5)
668
+
->get();
669
+
670
+
// Convert cents to dollars in PHP for better precision
671
+
foreach ($results as $form) {
672
+
$form->value = round((float)$form->raw_value / 100, 2);
673
+
}
674
+
675
+
$formResults = $results;
676
+
} else {
677
+
$disableMessage = __('Payment module is disabled. Please enable it to view top performing form by payments.', 'fluentform');
678
+
}
679
+
break;
680
+
681
+
case 'views':
682
+
// Count unique views by IP from analytics table if analytics enabled
683
+
if (!apply_filters('fluentform/disabled_analytics', true)) {
684
+
$results = wpFluent()->table('fluentform_forms')
685
+
->select([
686
+
'fluentform_forms.id',
687
+
'fluentform_forms.title',
688
+
wpFluent()->raw("COUNT(DISTINCT {$prefix}fluentform_form_analytics.ip) as value")
689
+
])
690
+
->leftJoin('fluentform_form_analytics', 'fluentform_forms.id', '=',
691
+
'fluentform_form_analytics.form_id')
692
+
->whereBetween('fluentform_form_analytics.created_at', [$startDate, $endDate])
693
+
->groupBy('fluentform_forms.id')
694
+
->orderBy('value', 'DESC')
695
+
->limit(5)
696
+
->get();
697
+
698
+
$formResults = $results;
699
+
} else {
700
+
$disableMessage = __('Analytics is disabled. Please enable it to view top performing form by views.', 'fluentform');
701
+
}
702
+
break;
703
+
}
704
+
705
+
// Common formatting for all results
706
+
$topForms = [];
707
+
foreach ($formResults as $form) {
708
+
if ((float)$form->value > 0) {
709
+
$topForms[] = [
710
+
'id' => $form->id,
711
+
'title' => $form->title ?: 'Untitled Form',
712
+
'value' => (float)$form->value
713
+
];
714
+
}
715
+
}
716
+
return [
717
+
'disable_message' => $disableMessage,
718
+
'data' => array_reverse($topForms)
719
+
];
720
+
}
721
+
722
+
/**
723
+
* Get form views date chunks
724
+
*/
725
+
private static function getFormViews($startDate, $endDate, $groupingMode, $formId)
726
+
{
727
+
if (apply_filters('fluentform/disabled_analytics', true)) {
728
+
return [];
729
+
}
730
+
731
+
// 1. Get UNIQUE VIEWS by IP address
732
+
$viewsQuery = FormAnalytics::whereBetween('created_at', [$startDate, $endDate])
733
+
->whereNotNull('ip');
734
+
735
+
if ($formId) {
736
+
$viewsQuery->where('form_id', $formId);
737
+
}
738
+
739
+
// Group by date and IP to count unique visitors
740
+
if ($groupingMode === 'day') {
741
+
$viewsQuery->selectRaw('DATE(created_at) as date_group, COUNT(DISTINCT ip) as unique_count');
742
+
} elseif ($groupingMode === '3days') {
743
+
// Get min date for reference
744
+
$minDateRecord = FormAnalytics::whereBetween('created_at', [$startDate, $endDate])
745
+
->selectRaw('MIN(DATE(created_at)) as min_date')
746
+
->first();
747
+
748
+
if ($minDateRecord && $minDateRecord->min_date) {
749
+
$minDate = $minDateRecord->min_date;
750
+
$viewsQuery->selectRaw("FLOOR(DATEDIFF(DATE(created_at), ?) / 3) as group_num", [$minDate])
751
+
->selectRaw('MIN(DATE(created_at)) as date_group')
752
+
->selectRaw('COUNT(DISTINCT ip) as unique_count')
753
+
->groupBy('group_num');
754
+
} else {
755
+
$viewsQuery->selectRaw('DATE(created_at) as date_group, COUNT(DISTINCT ip) as unique_count')
756
+
->groupBy('date_group');
757
+
}
758
+
} elseif ($groupingMode === 'week') {
759
+
$viewsQuery->selectRaw("DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY)) as date_group, COUNT(DISTINCT ip) as unique_count");
760
+
} else { // month
761
+
$viewsQuery->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as date_group, COUNT(DISTINCT ip) as unique_count");
762
+
}
763
+
764
+
if ($groupingMode !== '3days' || !(isset($minDateRecord) && $minDateRecord->min_date)) {
765
+
$viewsQuery->groupBy('date_group');
766
+
}
767
+
768
+
$results = $viewsQuery->orderBy('date_group')->get();
769
+
$views = [];
770
+
foreach ($results as $result) {
771
+
$views[$result->date_group] = $result->unique_count;
772
+
}
773
+
return $views;
774
+
}
775
+
776
+
/**
777
+
* Process date range
778
+
*/
779
+
public static function processDateRange($startDate, $endDate)
780
+
{
781
+
// Validate date formats
782
+
if (!strtotime($startDate) || !strtotime($endDate)) {
783
+
return [];
784
+
}
785
+
786
+
// Sanity check - ensure start date is before end date
787
+
$startDateTime = new \DateTime($startDate);
788
+
$endDateTime = new \DateTime($endDate);
789
+
790
+
// If start date is after end date, swap them
791
+
if ($startDateTime > $endDateTime) {
792
+
$temp = $startDate;
793
+
$startDate = $endDate;
794
+
$endDate = $temp;
795
+
}
796
+
797
+
return [$startDate, $endDate];
798
+
}
799
+
800
+
/**
801
+
* Determine grouping mode based on date range
802
+
*/
803
+
private static function getGroupingMode($daysInterval)
804
+
{
805
+
if ($daysInterval <= 7) {
806
+
return 'day'; // 1-7 days: group by day
807
+
} elseif ($daysInterval <= 31) {
808
+
return '3days'; // 8-31 days: group by 3 days
809
+
} elseif ($daysInterval <= 92) {
810
+
return 'week'; // Group by week for 1-3 months
811
+
} else {
812
+
return 'month'; // 3+ months: group by month
813
+
}
814
+
}
815
+
816
+
/**
817
+
* Get aggregated data based on grouping mode
818
+
*/
819
+
private static function getAggregatedData($startDate, $endDate, $groupingMode, $view, $formId)
820
+
{
821
+
$baseQuery = Submission::whereBetween('created_at', [$startDate, $endDate]);
822
+
823
+
// Filter by form ID if provided
824
+
if ($formId) {
825
+
$baseQuery->where('form_id', $formId);
826
+
}
827
+
828
+
if ($view === 'revenue') {
829
+
// Clone the base query for each payment status
830
+
$paidQuery = clone $baseQuery;
831
+
$pendingQuery = clone $baseQuery;
832
+
$refundedQuery = clone $baseQuery;
833
+
834
+
// Get paid payments
835
+
$paidQuery->whereNotNull('payment_total')
836
+
->where(function ($query) {
837
+
$query->where('payment_status', 'paid');
838
+
})
839
+
->selectRaw('ROUND(SUM(payment_total) / 100, 2) as count');
840
+
841
+
// Get pending payments
842
+
$pendingQuery->whereNotNull('payment_total')
843
+
->where('payment_status', 'pending')
844
+
->selectRaw('ROUND(SUM(payment_total) / 100, 2) as count');
845
+
846
+
// Get refunded payments
847
+
$refundedQuery->whereNotNull('payment_total')
848
+
->where('payment_status', 'refunded')
849
+
->selectRaw('ROUND(SUM(payment_total) / 100, 2) as count');
850
+
851
+
// Apply grouping based on mode to all three queries
852
+
if ($groupingMode === 'day') {
853
+
$paidQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
854
+
$pendingQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
855
+
$refundedQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
856
+
} elseif ($groupingMode === '3days') {
857
+
// Get minimum date for reference
858
+
$minDateRecord = Submission::whereBetween('created_at', [$startDate, $endDate])
859
+
->selectRaw('MIN(DATE(created_at)) as min_date')
860
+
->first();
861
+
862
+
if ($minDateRecord && $minDateRecord->min_date) {
863
+
$minDate = $minDateRecord->min_date;
864
+
865
+
$paidQuery->selectRaw("MIN(DATE(created_at)) as date_group")
866
+
->selectRaw("FLOOR(DATEDIFF(DATE(created_at), ?) / 3) as group_num", [$minDate])
867
+
->groupBy('group_num');
868
+
869
+
$pendingQuery->selectRaw("MIN(DATE(created_at)) as date_group")
870
+
->selectRaw("FLOOR(DATEDIFF(DATE(created_at), ?) / 3) as group_num", [$minDate])
871
+
->groupBy('group_num');
872
+
873
+
$refundedQuery->selectRaw("MIN(DATE(created_at)) as date_group")
874
+
->selectRaw("FLOOR(DATEDIFF(DATE(created_at), ?) / 3) as group_num", [$minDate])
875
+
->groupBy('group_num');
876
+
} else {
877
+
$paidQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
878
+
$pendingQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
879
+
$refundedQuery->selectRaw('DATE(created_at) as date_group')->groupBy('date_group');
880
+
}
881
+
} elseif ($groupingMode === 'week') {
882
+
$paidQuery->selectRaw("DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY)) as date_group")->groupBy('date_group');
883
+
$pendingQuery->selectRaw("DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY)) as date_group")->groupBy('date_group');
884
+
$refundedQuery->selectRaw("DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY)) as date_group")->groupBy('date_group');
885
+
} else {
886
+
$paidQuery->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as date_group")->groupBy('date_group');
887
+
$pendingQuery->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as date_group")->groupBy('date_group');
888
+
$refundedQuery->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as date_group")->groupBy('date_group');
889
+
}
890
+
891
+
// Execute the queries
892
+
$paidResults = $paidQuery->orderBy('date_group')->get();
893
+
$pendingResults = $pendingQuery->orderBy('date_group')->get();
894
+
$refundedResults = $refundedQuery->orderBy('date_group')->get();
895
+
896
+
897
+
// Format the data
898
+
$paidData = $revenuePayments = [];
899
+
foreach ($paidResults as $result) {
900
+
$paidData[$result->date_group] = $result->count;
901
+
$revenuePayments[$result->date_group] = $result->count;
902
+
}
903
+
904
+
$pendingData = [];
905
+
foreach ($pendingResults as $result) {
906
+
$pendingData[$result->date_group] = $result->count;
907
+
}
908
+
909
+
$refundedData = [];
910
+
foreach ($refundedResults as $result) {
911
+
$refundedData[$result->date_group] = $result->count;
912
+
if (isset($revenuePayments[$result->date_group])) {
913
+
$revenuePayments[$result->date_group] -= $result->count;
914
+
}
915
+
}
916
+
917
+
// Return all three datasets
918
+
return [
919
+
'paid' => $paidData,
920
+
'pending' => $pendingData,
921
+
'refunded' => $refundedData,
922
+
'payments' => $revenuePayments
923
+
];
924
+
} else {
925
+
$query = $baseQuery->selectRaw('COUNT(*) as count')->selectRaw('status');
926
+
$query->groupBy('status');
927
+
928
+
// Apply grouping based on mode
929
+
if ($groupingMode === 'day') {
930
+
$query->selectRaw('DATE(created_at) as date_group')
931
+
->groupBy('date_group');
932
+
} elseif ($groupingMode === '3days') {
933
+
$minDateRecord = Submission::whereBetween('created_at', [$startDate, $endDate])
934
+
->selectRaw('MIN(DATE(created_at)) as min_date')
935
+
->first();
936
+
937
+
if ($minDateRecord && $minDateRecord->min_date) {
938
+
$query->selectRaw("MIN(DATE(created_at)) as date_group")
939
+
->selectRaw("FLOOR(DATEDIFF(DATE(created_at), '{$minDateRecord->min_date}') / 3) as group_num")
940
+
->groupBy('group_num');
941
+
} else {
942
+
$query->selectRaw('DATE(created_at) as date_group')
943
+
->groupBy('date_group');
944
+
}
945
+
} elseif ($groupingMode === 'week') {
946
+
$query->selectRaw("DATE(DATE_ADD(created_at, INTERVAL(-WEEKDAY(created_at)) DAY)) as date_group")
947
+
->groupBy('date_group');
948
+
} else {
949
+
$query->selectRaw("DATE_FORMAT(created_at, '%Y-%m-01') as date_group")
950
+
->groupBy('date_group');
951
+
}
952
+
953
+
$results = $query->orderBy('date_group')->get();
954
+
$total = $read = $unread = $spam = $trashed = [];
955
+
foreach ($results as $result) {
956
+
if ($result->status === 'read') {
957
+
$read[$result->date_group] = $result->count;
958
+
}
959
+
if ($result->status === 'unread') {
960
+
$unread[$result->date_group] = $result->count;
961
+
}
962
+
if ($result->status === 'spam') {
963
+
$spam[$result->date_group] = $result->count;
964
+
}
965
+
if ($result->status === 'trashed') {
966
+
$trashed[$result->date_group] = $result->count;
967
+
}
968
+
$total[$result->date_group] = isset($total[$result->date_group]) ? $total[$result->date_group] + $result->count : $result->count;
969
+
}
970
+
// Return all four datasets
971
+
return [
972
+
'submissions' => $total,
973
+
'read' => $read,
974
+
'unread' => $unread,
975
+
'spam' => $spam,
976
+
'trashed' => $trashed
977
+
];
978
+
}
979
+
}
980
+
981
+
/**
982
+
* Generate date labels based on grouping mode
983
+
*/
984
+
private static function getDateLabels(\DateTime $startDate, \DateTime $endDate, $groupingMode)
985
+
{
986
+
$dates = [];
987
+
$labels = [];
988
+
$current = clone $startDate;
989
+
990
+
if ($groupingMode === 'day') {
991
+
// Generate daily labels
992
+
while ($current <= $endDate) {
993
+
$dateKey = $current->format('Y-m-d');
994
+
$dates[] = $dateKey;
995
+
$labels[] = $current->format('M d');
996
+
$current->modify('+1 day');
997
+
}
998
+
} elseif ($groupingMode === '3days') {
999
+
// Generate labels for every 3 days
1000
+
$dayIndex = 0;
1001
+
$groupStartDate = clone $current;
1002
+
1003
+
while ($current <= $endDate) {
1004
+
if ($dayIndex % 3 === 0 && $dayIndex > 0) {
1005
+
$previousDate = clone $current;
1006
+
$previousDate->modify('-1 day');
1007
+
1008
+
$dateKey = $groupStartDate->format('Y-m-d');
1009
+
$dates[] = $dateKey;
1010
+
$labels[] = $groupStartDate->format('M d');
1011
+
1012
+
$groupStartDate = clone $current;
1013
+
}
1014
+
1015
+
$current->modify('+1 day');
1016
+
$dayIndex++;
1017
+
}
1018
+
1019
+
// Add the last group if needed
1020
+
if ($groupStartDate <= $endDate) {
1021
+
$dateKey = $groupStartDate->format('Y-m-d');
1022
+
$dates[] = $dateKey;
1023
+
$labels[] = $groupStartDate->format('M d');
1024
+
}
1025
+
} elseif ($groupingMode === 'week') {
1026
+
// Generate weekly labels
1027
+
while ($current <= $endDate) {
1028
+
// Use simple approach to get Monday (start of week)
1029
+
$dayOfWeek = (int)$current->format('N'); // 1 (Monday) through 7 (Sunday)
1030
+
$daysToSubtract = $dayOfWeek - 1;
1031
+
1032
+
$weekStart = clone $current;
1033
+
if ($daysToSubtract > 0) {
1034
+
$weekStart->modify("-{$daysToSubtract} days");
1035
+
}
1036
+
1037
+
// Calculate end of week (Sunday)
1038
+
$weekEnd = clone $weekStart;
1039
+
$weekEnd->modify('+6 days');
1040
+
1041
+
// If weekend exceeds the range end, cap it
1042
+
if ($weekEnd > $endDate) {
1043
+
$weekEnd = clone $endDate;
1044
+
}
1045
+
1046
+
$dateKey = $weekStart->format('Y-m-d');
1047
+
$dates[] = $dateKey;
1048
+
$labels[] = $weekStart->format('M d');
1049
+
1050
+
// Move to next week
1051
+
$current->modify('+7 days');
1052
+
}
1053
+
} else {
1054
+
// Generate monthly labels
1055
+
while ($current <= $endDate) {
1056
+
$dateKey = $current->format('Y-m-01');
1057
+
$dates[] = $dateKey;
1058
+
$labels[] = $current->format('M Y');
1059
+
1060
+
// Manually move to first day of next month
1061
+
$year = (int)$current->format('Y');
1062
+
$month = (int)$current->format('m');
1063
+
1064
+
// Move to next month
1065
+
$month++;
1066
+
if ($month > 12) {
1067
+
$month = 1;
1068
+
$year++;
1069
+
}
1070
+
1071
+
// Set to first day of next month
1072
+
$current = new \DateTime("$year-$month-01");
1073
+
}
1074
+
}
1075
+
1076
+
return ['dates' => $dates, 'labels' => $labels];
1077
+
}
1078
+
1079
+
public static function getPaymentsByType($startDate, $endDate, $type, $formId = 0)
1080
+
{
1081
+
$paymentSettings = get_option('__fluentform_payment_module_settings');
1082
+
if (!$paymentSettings || !Arr::isTrue($paymentSettings, 'status')) {
1083
+
return []; // Return empty if payment module is disabled
1084
+
}
1085
+
list($startDate, $endDate) = self::processDateRange($startDate, $endDate);
1086
+
1087
+
// Base query for transactions
1088
+
$query = wpFluent()->table('fluentform_transactions')
1089
+
->whereBetween('created_at', [$startDate, $endDate]);
1090
+
1091
+
// Filter by transaction type if specified
1092
+
if ($type === 'subscription') {
1093
+
$query->whereIn('transaction_type', ['subscription', 'subscription_signup_fee']);
1094
+
} elseif ($type === 'onetime') {
1095
+
$query->where('transaction_type', 'onetime');
1096
+
}
1097
+
1098
+
if ($formId) {
1099
+
$query->where('form_id', $formId);
1100
+
}
1101
+
1102
+
// Get payments grouped by status
1103
+
$payments = $query->select('status')
1104
+
->selectRaw('SUM(payment_total) as total_amount')
1105
+
->selectRaw('COUNT(*) as count')
1106
+
->groupBy('status')
1107
+
->get();
1108
+
1109
+
// Get the total payment amount
1110
+
$totalAmount = 0;
1111
+
foreach ($payments as $payment) {
1112
+
$totalAmount += $payment->total_amount;
1113
+
}
1114
+
1115
+
$formattedData = [];
1116
+
foreach ($payments as $payment) {
1117
+
$status = strtolower($payment->status);
1118
+
$amount = $payment->total_amount / 100; // Convert from cents to dollars
1119
+
$percentage = $totalAmount > 0 ? round(($payment->total_amount / $totalAmount) * 100, 2) : 0;
1120
+
1121
+
$formattedData[$status] = [
1122
+
'amount' => $amount,
1123
+
'percentage' => $percentage,
1124
+
'count' => $payment->count
1125
+
];
1126
+
}
1127
+
1128
+
// Calculate weekly average paid amount
1129
+
$daysInRange = self::getDateDifference($startDate, $endDate);
1130
+
$weeksInRange = max(1, round($daysInRange / 7, 1));
1131
+
1132
+
$paidAmount = 0;
1133
+
foreach ($formattedData as $status => $data) {
1134
+
if ($status === 'paid') {
1135
+
$paidAmount = $data['amount'];
1136
+
break;
1137
+
}
1138
+
}
1139
+
1140
+
$weeklyAverage = $paidAmount / $weeksInRange;
1141
+
1142
+
return [
1143
+
'currency_symbol' => Arr::get(PaymentHelper::getCurrencyConfig($formId), 'currency_sign', '$'),
1144
+
'payment_statuses' => $formattedData,
1145
+
'total_amount' => $totalAmount / 100, // Convert from cents to dollars
1146
+
'weekly_average' => round($weeklyAverage, 2)
1147
+
];
1148
+
}
1149
+
1150
+
/**
1151
+
* Format payment method name for display
1152
+
*/
1153
+
protected static function formatPaymentMethodName($paymentMethod)
1154
+
{
1155
+
$methodNames = [
1156
+
'stripe' => 'Stripe',
1157
+
'paypal' => 'PayPal',
1158
+
'razorpay' => 'Razorpay',
1159
+
'paystack' => 'Paystack',
1160
+
'mollie' => 'Mollie',
1161
+
'square' => 'Square',
1162
+
'paddle' => 'Paddle',
1163
+
'test' => 'Offline/Test',
1164
+
'offline' => 'Offline'
1165
+
];
1166
+
1167
+
return $methodNames[$paymentMethod] ?? ucfirst($paymentMethod);
1168
+
}
1169
+
1170
+
/**
1171
+
* Format data for the chart
1172
+
*/
1173
+
private static function formatDataForChart($dateLabels, $data, $formId)
1174
+
{
1175
+
$dates = $dateLabels['dates'];
1176
+
$labels = $dateLabels['labels'];
1177
+
1178
+
if (is_array($data) && isset($data['paid'])) {
1179
+
$paidValues = self::fillMissingData($dates, $data['paid']);
1180
+
$pendingValues = self::fillMissingData($dates, $data['pending']);
1181
+
$refundedValues = self::fillMissingData($dates, $data['refunded']);
1182
+
$paymentsValues = self::fillMissingData($dates, $data['payments']);
1183
+
$currencyConfig = PaymentHelper::getCurrencyConfig($formId);
1184
+
1185
+
return [
1186
+
'dates' => $labels,
1187
+
'currency_sign' => Arr::get($currencyConfig, 'currency_sign', '$'),
1188
+
'currency' => Arr::get($currencyConfig, 'currency', 'USD'),
1189
+
'values' => [
1190
+
'paid' => array_values($paidValues),
1191
+
'pending' => array_values($pendingValues),
1192
+
'refunded' => array_values($refundedValues),
1193
+
'payments' => array_values($paymentsValues)
1194
+
]
1195
+
];
1196
+
} else {
1197
+
return [
1198
+
'dates' => $labels,
1199
+
'values' => [
1200
+
'submissions' => array_values(self::fillMissingData($dates, $data['submissions'])),
1201
+
'read' => array_values(self::fillMissingData($dates, $data['read'])),
1202
+
'unread' => array_values(self::fillMissingData($dates, $data['unread'])),
1203
+
'spam' => array_values(self::fillMissingData($dates, $data['spam'])),
1204
+
'trashed' => array_values(self::fillMissingData($dates, $data['trashed']))
1205
+
]
1206
+
];
1207
+
}
1208
+
}
1209
+
1210
+
/**
1211
+
* Fill in missing data based on date intervals
1212
+
*
1213
+
* @param array $allDates Array of interval start dates
1214
+
* @param array $data Associative array of date => value pairs
1215
+
*
1216
+
* @return array Result with interval start dates mapped to summed values
1217
+
*/
1218
+
private static function fillMissingData($allDates, $data)
1219
+
{
1220
+
$result = [];
1221
+
1222
+
// Pre-convert dates to timestamps for faster comparison
1223
+
$dataTimestamps = [];
1224
+
foreach ($data as $date => $value) {
1225
+
$dataTimestamps[strtotime($date)] = $value;
1226
+
}
1227
+
1228
+
$allDatesCount = count($allDates);
1229
+
for ($i = 0; $i < $allDatesCount; $i++) {
1230
+
$startTimestamp = strtotime($allDates[$i]);
1231
+
1232
+
// Calculate end timestamp of interval (exclusive)
1233
+
$endTimestamp = isset($allDates[$i + 1])
1234
+
? strtotime($allDates[$i + 1])
1235
+
: $startTimestamp + (3 * 24 * 60 * 60); // 3 days in seconds
1236
+
1237
+
$sum = 0;
1238
+
$hasData = false;
1239
+
1240
+
// Check each timestamp in the data array
1241
+
foreach ($dataTimestamps as $timestamp => $value) {
1242
+
if ($timestamp >= $startTimestamp && $timestamp < $endTimestamp) {
1243
+
$sum += $value;
1244
+
$hasData = true;
1245
+
}
1246
+
}
1247
+
1248
+
$result[$allDates[$i]] = $hasData ? $sum : 0;
1249
+
}
1250
+
1251
+
return $result;
1252
+
}
1253
+
1254
+
private static function getPaymentStats($startDate, $endDate, $previousStartDate, $previousEndDate, $formId)
1255
+
{
1256
+
// Get total payments (paid status) for current period
1257
+
$currentPayments = wpFluent()
1258
+
->table('fluentform_transactions')
1259
+
->whereBetween('created_at', [$startDate, $endDate])
1260
+
->where('status', 'paid')
1261
+
->when($formId, function ($q) use ($formId) {
1262
+
return $q->where('form_id', $formId);
1263
+
})
1264
+
->sum('payment_total');
1265
+
1266
+
// Get total payments for previous period
1267
+
$previousPayments = wpFluent()
1268
+
->table('fluentform_transactions')
1269
+
->whereBetween('created_at', [$previousStartDate, $previousEndDate])
1270
+
->where('status', 'paid')
1271
+
->when($formId, function ($q) use ($formId) {
1272
+
return $q->where('form_id', $formId);
1273
+
})
1274
+
->sum('payment_total');
1275
+
1276
+
// Get pending payments for current period
1277
+
$currentPending = wpFluent()
1278
+
->table('fluentform_transactions')
1279
+
->whereBetween('created_at', [$startDate, $endDate])
1280
+
->where('status', 'pending')
1281
+
->when($formId, function ($q) use ($formId) {
1282
+
return $q->where('form_id', $formId);
1283
+
})
1284
+
->sum('payment_total');
1285
+
1286
+
// Get pending payments for previous period
1287
+
$previousPending = wpFluent()
1288
+
->table('fluentform_transactions')
1289
+
->whereBetween('created_at', [$previousStartDate, $previousEndDate])
1290
+
->where('status', 'pending')
1291
+
->when($formId, function ($q) use ($formId) {
1292
+
return $q->where('form_id', $formId);
1293
+
})
1294
+
->sum('payment_total');
1295
+
1296
+
// Get total refunds for current period
1297
+
$currentRefunds = wpFluent()
1298
+
->table('fluentform_transactions')
1299
+
->whereBetween('created_at', [$startDate, $endDate])
1300
+
->where('status', 'refunded')
1301
+
->when($formId, function ($q) use ($formId) {
1302
+
return $q->where('form_id', $formId);
1303
+
})
1304
+
->sum('payment_total');
1305
+
1306
+
// Get total refunds for previous period
1307
+
$previousRefunds = wpFluent()
1308
+
->table('fluentform_transactions')
1309
+
->whereBetween('created_at', [$previousStartDate, $previousEndDate])
1310
+
->where('status', 'refunded')
1311
+
->when($formId, function ($q) use ($formId) {
1312
+
return $q->where('form_id', $formId);
1313
+
})
1314
+
->sum('payment_total');
1315
+
1316
+
// Convert from cents to dollars
1317
+
$currentPayments = $currentPayments ? $currentPayments / 100 : 0;
1318
+
$previousPayments = $previousPayments ? $previousPayments / 100 : 0;
1319
+
$currentPending = $currentPending ? $currentPending / 100 : 0;
1320
+
$previousPending = $previousPending ? $previousPending / 100 : 0;
1321
+
$currentRefunds = $currentRefunds ? $currentRefunds / 100 : 0;
1322
+
$previousRefunds = $previousRefunds ? $previousRefunds / 100 : 0;
1323
+
1324
+
// Calculate payment growth percentage
1325
+
$paymentGrowthPercentage = 0;
1326
+
if ($previousPayments > 0) {
1327
+
$paymentGrowthPercentage = round((($currentPayments - $previousPayments) / $previousPayments) * 100, 1);
1328
+
} elseif ($currentPayments > 0) {
1329
+
$paymentGrowthPercentage = 100;
1330
+
}
1331
+
1332
+
$paymentGrowthText = $paymentGrowthPercentage > 0 ? '+' . $paymentGrowthPercentage . '%' : $paymentGrowthPercentage . '%';
1333
+
$paymentGrowthType = $paymentGrowthPercentage > 0 ? 'up' : ($paymentGrowthPercentage < 0 ? 'down' : 'neutral');
1334
+
1335
+
// Calculate refund growth percentage
1336
+
$refundGrowthPercentage = 0;
1337
+
if ($previousRefunds > 0) {
1338
+
$refundGrowthPercentage = round((($currentRefunds - $previousRefunds) / $previousRefunds) * 100, 1);
1339
+
} elseif ($currentRefunds > 0) {
1340
+
$refundGrowthPercentage = 100;
1341
+
}
1342
+
1343
+
$refundGrowthText = $refundGrowthPercentage > 0 ? '+' . $refundGrowthPercentage . '%' : $refundGrowthPercentage . '%';
1344
+
$refundGrowthType = $refundGrowthPercentage > 0 ? 'down' : ($refundGrowthPercentage < 0 ? 'up' : 'neutral'); // Refunds going up is bad
1345
+
1346
+
// Calculate pending growth percentage
1347
+
$pendingGrowthPercentage = 0;
1348
+
if ($previousPending > 0) {
1349
+
$pendingGrowthPercentage = round((($currentPending - $previousPending) / $previousPending) * 100, 1);
1350
+
} elseif ($currentPending > 0) {
1351
+
$pendingGrowthPercentage = 100;
1352
+
}
1353
+
1354
+
$pendingGrowthText = $pendingGrowthPercentage > 0 ? '+' . $pendingGrowthPercentage . '%' : $pendingGrowthPercentage . '%';
1355
+
$pendingGrowthType = $pendingGrowthPercentage > 0 ? 'up' : ($pendingGrowthPercentage < 0 ? 'down' : 'neutral');
1356
+
1357
+
// Calculate revenue percentage
1358
+
$totalRevenue = $currentPayments - $currentRefunds;
1359
+
$previousRevenue = $previousPayments - $previousRefunds;
1360
+
$revenuePercentage = 0;
1361
+
if ($previousRevenue > 0) {
1362
+
$revenuePercentage = round((($totalRevenue - $previousRevenue) / $previousRevenue) * 100, 1);
1363
+
} elseif ($totalRevenue > 0) {
1364
+
$revenuePercentage = 100;
1365
+
}
1366
+
$revenueText = $revenuePercentage > 0 ? '+' . $revenuePercentage . '%' : $revenuePercentage . '%';
1367
+
$revenueType = $revenuePercentage > 0 ? 'up' : ($revenuePercentage < 0 ? 'down' : 'neutral');
1368
+
1369
+
// Get default currency from payment settings
1370
+
$paymentSettings = PaymentHelper::getPaymentSettings();
1371
+
$currency = Arr::get($paymentSettings, 'currency', 'USD');
1372
+
$currencySymbol = PaymentHelper::getCurrencySymbol($currency);
1373
+
1374
+
return [
1375
+
'total_payments' => [
1376
+
'value' => number_format($currentPayments, 2),
1377
+
'raw_value' => $currentPayments,
1378
+
'currency' => $currency,
1379
+
'currency_symbol' => $currencySymbol,
1380
+
'change' => $paymentGrowthText,
1381
+
'change_type' => $paymentGrowthType
1382
+
],
1383
+
'pending_payments' => [
1384
+
'value' => number_format($currentPending, 2),
1385
+
'raw_value' => $currentPending,
1386
+
'currency' => $currency,
1387
+
'currency_symbol' => $currencySymbol,
1388
+
'change' => $pendingGrowthText,
1389
+
'change_type' => $pendingGrowthType
1390
+
],
1391
+
'total_refunds' => [
1392
+
'value' => number_format($currentRefunds, 2),
1393
+
'raw_value' => $currentRefunds,
1394
+
'currency' => $currency,
1395
+
'currency_symbol' => $currencySymbol,
1396
+
'change' => $refundGrowthText,
1397
+
'change_type' => $refundGrowthType
1398
+
],
1399
+
'total_revenue' => [
1400
+
'value' => number_format($totalRevenue, 2),
1401
+
'raw_value' => $totalRevenue,
1402
+
'change' => $revenueText,
1403
+
'change_type' => $revenueType,
1404
+
'currency' => $currency,
1405
+
'currency_symbol' => $currencySymbol
1406
+
]
1407
+
];
1408
+
}
1409
+
1410
+
1411
+
protected static function getDateDifference($startDate, $endDate)
1412
+
{
1413
+
$start = new \DateTime($startDate);
1414
+
$end = new \DateTime($endDate);
1415
+
$interval = $start->diff($end);
1416
+
return $interval->days + 1;
1417
+
}
1418
+
}
1419
+