HEX

File: /www/wwwroot/dr-lil.com/wp-content/plugins/fluentform/app/Modules/Entries/Report.php
<?php

namespace FluentForm\App\Modules\Entries;

use FluentForm\App\Helpers\Helper;
use FluentForm\App\Modules\Form\FormFieldsParser;
use FluentForm\App\Services\Submission\SubmissionService;
use FluentForm\Framework\Foundation\Application;
use FluentForm\Framework\Helpers\ArrayHelper;

/**
 *
 * @deprecated  use FluentForm\App\Services\Report\ReportHelper
 * all used method reference updated with new ReportHelper, except Fluentformpro
 *
 */
class Report
{
    private $app;
    private $formModel;

    public function __construct(Application $app)
    {
        $this->app = $app;
        $this->formModel = wpFluent()->table('fluentform_forms');
    }

    /**
     * Get report
     *
     * @param bool $formId
     */
    public function getReport($formId = false)
    {
        if (!$formId) {
            $formId = intval($this->app->request->get('form_id'));
        }

        $this->maybeMigrateData($formId);

        $statuses = $this->app->request->get('statuses');

        $form = $this->formModel->find($formId);

        $report = $this->generateReport($form, $statuses);

        wp_send_json_success($report);
    }

    public function generateReport($form, $statuses = [])
    {
        $formInputs = FormFieldsParser::getEntryInputs($form, ['admin_label', 'element', 'options']);

        $inputLabels = FormFieldsParser::getAdminLabels($form, $formInputs);

        $elements = [];

        foreach ($formInputs as $inputName => $input) {
            $elements[$inputName] = $input['element'];
            if ('select_country' == $input['element']) {
                $formInputs[$inputName]['options'] = getFluentFormCountryList();
            }
        }

        $reportableInputs = Helper::getReportableInputs();

        $formReportableInputs = array_intersect($reportableInputs, array_values($elements));

        $reportableInputs = Helper::getSubFieldReportableInputs();
        $formSubFieldInputs = array_intersect($reportableInputs, array_values($elements));

        if (!$formReportableInputs && !$formSubFieldInputs) {
            return [
                'report_items'  => (object) [],
                'total_entries' => 0,
            ];
        }

        $inputs = [];
        $subfieldInputs = [];
        foreach ($elements as $elementKey => $element) {
            if (in_array($element, $formReportableInputs)) {
                $inputs[$elementKey] = $element;
            }
            if (in_array($element, $formSubFieldInputs)) {
                $subfieldInputs[$elementKey] = $element;
            }
        }

        $whereClasuses = [];

        if ($statuses) {
            $whereClasuses['fluentform_submissions.status'] = [
                'method' => 'whereIn',
                'values' => $statuses,
            ];
        }

        $reports = $this->getInputReport($form->id, array_keys($inputs), $whereClasuses);

        $subFieldReports = $this->getSubFieldInputReport($form->id, array_keys($subfieldInputs), $whereClasuses);

        $reports = array_merge($reports, $subFieldReports);

        foreach ($reports as $reportKey => $report) {
            $reports[$reportKey]['label'] = $inputLabels[$reportKey];
            $reports[$reportKey]['element'] = ArrayHelper::get($inputs, $reportKey, []);
            $reports[$reportKey]['options'] = $formInputs[$reportKey]['options'];
        }

        return [
            'report_items'  => $reports,
            'total_entries' => $this->getEntryCounts($form->id, $statuses),
            'browsers'      => $this->getbrowserCounts($form->id, $statuses),
            'devices'       => $this->getDeviceCounts($form->id, $statuses),
        ];
    }

    public function getInputReport($formId, $fieldNames, $whereClasuses)
    {
        if (!$fieldNames) {
            return [];
        }
        global $wpdb;
        $reportQuery = wpFluent()->table('fluentform_entry_details')
            ->select([
                'fluentform_entry_details.field_name',
                'fluentform_entry_details.sub_field_name',
                'fluentform_entry_details.field_value',
                wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_entry_details.field_name) as total_count')
            ])
            ->where('fluentform_entry_details.form_id', $formId)
            ->whereIn('fluentform_entry_details.field_name', $fieldNames)
            ->rightJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');

        if ($whereClasuses) {
            foreach ($whereClasuses as $clauseColumn => $clasus) {
                $reportQuery = $reportQuery->{$clasus['method']}($clauseColumn, $clasus['values']);
            }
        }

        $reports = $reportQuery->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.field_value'])
            ->get();

        $formattedReports = [];
        foreach ($reports as $report) {
            $formattedReports[$report->field_name]['reports'][] = [
                'value'     => Helper::safeUnserialize($report->field_value),
                'count'     => $report->total_count,
                'sub_field' => $report->sub_field_name,
            ];
            $formattedReports[$report->field_name]['total_entry'] = $this->getEntryTotal($report->field_name, $formId, $whereClasuses);
        }

        return $formattedReports;
    }

    public function getSubFieldInputReport($formId, $fieldNames, $whereClasuses)
    {
        if (!$fieldNames) {
            return [];
        }

        global $wpdb;
        $reportQuery = wpFluent()->table('fluentform_entry_details')
            ->select([
                'fluentform_entry_details.field_name',
                'fluentform_entry_details.sub_field_name',
                'fluentform_entry_details.field_value',
                wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_entry_details.field_name) as total_count')
            ])
            ->where('fluentform_entry_details.form_id', $formId)
            ->whereIn('fluentform_entry_details.field_name', $fieldNames)
            ->leftJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');

        if ($whereClasuses) {
            foreach ($whereClasuses as $clauseColumn => $clasus) {
                $reportQuery = $reportQuery->{$clasus['method']}($clauseColumn, $clasus['values']);
            }
        }

        $reports = $reportQuery->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.field_value', 'fluentform_entry_details.sub_field_name'])
            ->get();

        return $this->getFormattedReportsForSubInputs($reports, $formId, $whereClasuses);
    }

    protected function getFormattedReportsForSubInputs($reports, $formId, $whereClasuses)
    {
        if (!count($reports)) {
            return [];
        }

        $formattedReports = [];

        foreach ($reports as $report) {
            $this->setReportForSubInput((array) $report, $formattedReports);
        }

        foreach ($formattedReports as $fieldName => $val) {
            $formattedReports[$fieldName]['total_entry'] = $this->getEntryTotal(
                $report->field_name,
                $formId,
                $whereClasuses
            );

            $formattedReports[$fieldName]['reports'] = array_values(
                $formattedReports[$fieldName]['reports']
            );
        }

        return $formattedReports;
    }

    protected function setReportForSubInput($report, &$formattedReports)
    {
        $filedValue = Helper::safeUnserialize($report['field_value']);

        if (is_array($filedValue)) {
            foreach ($filedValue as $fVal) {
                $this->setReportForSubInput(
                    array_merge($report, ['field_value' => $fVal]),
                    $formattedReports
                );
            }
        } else {
            $value = $report['sub_field_name'] . ' : ' . $filedValue;
            $count = ArrayHelper::get($formattedReports, $report['field_name'] . '.reports.' . $value . '.count');
            $count = $count ? $count + $report['total_count'] : $report['total_count'];

            $formattedReports[$report['field_name']]['reports'][$value] = [
                'value'     => $value,
                'count'     => $count,
                'sub_field' => $report['sub_field_name'],
            ];
        }
    }

    public function getEntryTotal($fieldName, $formId, $whereClasuses)
    {
        $query = wpFluent()->table('fluentform_entry_details')
            ->select('fluentform_entry_details.id')
            ->where('fluentform_entry_details.form_id', $formId)
            ->where('fluentform_entry_details.field_name', $fieldName)
            ->groupBy(['fluentform_entry_details.field_name', 'fluentform_entry_details.submission_id'])
            ->leftJoin('fluentform_submissions', 'fluentform_submissions.id', '=', 'fluentform_entry_details.submission_id');

        if ($whereClasuses) {
            foreach ($whereClasuses as $clauseColumn => $clasus) {
                $query = $query->{$clasus['method']}($clauseColumn, $clasus['values']);
            }
        }

        return $query->count();
    }

    private function maybeMigrateData($formId)
    {
        // We have to check if we need to migrate the data
        if ('yes' == Helper::getFormMeta($formId, 'report_data_migrated')) {
            return true;
        }
        global $wpdb;
        // let's migrate the data
        $unmigratedData = wpFluent()
            ->table('fluentform_submissions')
            ->select([
                'fluentform_submissions.id',
                'fluentform_submissions.response',
            ])
            ->where('fluentform_submissions.form_id', $formId)
            ->whereRaw(wpFluent()->raw($wpdb->prefix . 'fluentform_submissions.id NOT IN (SELECT submission_id from ' . $wpdb->prefix . 'fluentform_entry_details)'))
            ->get();

        if (!$unmigratedData) {
            return Helper::setFormMeta($formId, 'report_data_migrated', 'yes');
        }

        $submissionService = new SubmissionService();
        foreach ($unmigratedData as $datum) {
            $value = json_decode($datum->response, true);
            $submissionService->recordEntryDetails($datum->id, $formId, $value);
        }

        return true;
    }

    private function getEntryCounts($formId, $statuses = false)
    {
        $totalEntries = wpFluent()
            ->table('fluentform_submissions')
            ->where('fluentform_submissions.form_id', $formId);

        if ($statuses) {
            $totalEntries = $totalEntries->whereIn('fluentform_submissions.status', $statuses);
        } else {
            $totalEntries = $totalEntries->where('fluentform_submissions.status', '!=', 'trashed');
        }
        return $totalEntries->count();
    }

    private function getBrowserCounts($formId, $statuses)
    {
        global $wpdb;
        $browserCounts = wpFluent()->table('fluentform_submissions')
            ->select([
                wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_submissions.id) as total_count'),
                'browser',
            ])
            ->where('form_id', $formId);
        if ($statuses) {
            $browserCounts = $browserCounts->whereIn('status', $statuses);
        } else {
            $browserCounts = $browserCounts->where('status', '!=', 'trashed');
        }

        $browserCounts = $browserCounts->groupBy('browser')
            ->get();

        $formattedData = [];
        foreach ($browserCounts as $browser) {
            $formattedData[$browser->browser] = $browser->total_count;
        }

        return $formattedData;
    }

    private function getDeviceCounts($formId, $statuses)
    {
        global $wpdb;
        $deviceCounts = wpFluent()->table('fluentform_submissions')
            ->select([
                wpFluent()->raw('count(' . $wpdb->prefix . 'fluentform_submissions.id) as total_count'),
                'device',
            ])
            ->where('form_id', $formId);
        if ($statuses) {
            $deviceCounts = $deviceCounts->whereIn('status', $statuses);
        } else {
            $deviceCounts = $deviceCounts->where('status', '!=', 'trashed');
        }

        $deviceCounts = $deviceCounts->groupBy('device')
            ->get();

        $formattedData = [];
        foreach ($deviceCounts as $deviceCount) {
            $formattedData[$deviceCount->device] = $deviceCount->total_count;
        }
        return $formattedData;
    }
}