AnswerQuestionsDublicate

AnswerQuestionsDublicate.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\Expression;
use Illuminate\Support\Str;

class AnswerQuestionsDublicate extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'answer:questions:dublicate';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Answer questions remove json dublicate';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     */
    public function handle()
    {
        DB::query()->fromSub(function ($query) {
            $query->select([
                'answer_id',
                'question_uuid',
                'widget_uuid',
                $this->getTypeField(),
            ])
                ->from('answer_questions as aq')
                ->where('created_at', '>=', '2022-05-11')
                ->groupBy(['answer_id', 'question_uuid', 'widget_uuid']);
        }, 'r')
            ->select(['answer_id', $this->getJsonAgg()])
            ->whereIn('type', ['choiceSingle', 'choiceMultiple', 'dropdown', 'choiceMedia', 'yesno'])
            ->groupBy('answer_id')
            ->orderBy('answer_id')
            ->chunk(100, function ($rows, $index) {
                foreach ($rows as $row) {
                    $this->getChunkRow($row);
                }

                $this->info("Run $index");
            });
    }

    /**
     * @return Expression
     */
    private function getTypeField(): Expression
    {
        return DB::raw('(select q.type from questions q where q.question_uuid = aq.question_uuid limit 1) as type');
    }

    /**
     * @return Expression
     */
    private function getJsonAgg(): Expression
    {
        return DB::raw("json_agg(
                    json_build_object(
                        'question_uuid', question_uuid,
                        'widget_uuid', widget_uuid,
                        'type', type
                    )
                ) AS questions"
        );
    }

    /**
     * @param $row
     */
    private function getChunkRow($row)
    {
        $answer = DB::table('answers')
            ->select(['id', 'results'])
            ->where('id', $row->answer_id)
            ->where('results', '!=', '"{}"')
            ->first();

        if ($answer) {
            $results = json_decode($answer->results, true);

            if (!empty($results)) {
                // bug 5
                $answer_questions = DB::table('answer_questions')
                    ->where('answer_id', $answer->id)
                    ->pluck('question_uuid')
                    ->toArray();

                foreach ($results as $questionId => $option) {
                    if (!in_array($questionId, $answer_questions)) {
                        unset($results[$questionId]);
                    }
                }

                $questions = json_decode($row->questions, true);

                foreach ($questions as $item) {
                    foreach ($results as $questionId => $option) {
                        if ($questionId === $item['question_uuid']) {
                            if (!empty($option) && is_array($option)) {
                                foreach ($option as $key => $value) {
                                    if (isset($value['uuid'])) {
                                        $question = DB::table('answer_questions')
                                            ->select(['id', 'result_text', 'res'])
                                            ->where('answer_id', $row->answer_id)
                                            ->where('question_uuid', $questionId)
                                            ->first();

                                        if ($question) {
                                            $res = json_decode($question->res, true);

                                            if (!empty($res)) {
                                                $uuid = ($value['uuid'] === 'ffffffff-ffff-ffff-ffff-ffffffffffff') ? 'other' : $value['uuid'];

                                                // bug 4
                                                if ($item['type'] === 'choiceSingle' && count($res) === 1) {
                                                    if (array_key_exists('other', $res) && !is_null($question->result_text)) {
                                                        DB::table('answer_multiple')
                                                            ->where('answer_question_id', '=', $question->id)
                                                            ->delete();
                                                    }
                                                }

                                                unset($res['other']);

                                                $option_keys = array_keys($res);
                                                $option_label_uuid = [];

                                                foreach ($option_keys as $opt_uuid) {
                                                    if (Str::isUuid($opt_uuid)) {
                                                        $option_label_uuid[] = $opt_uuid;
                                                    }
                                                }

                                                if (!empty($option_label_uuid)) {
                                                    // bug 2
                                                    if (!array_key_exists($uuid, $res)) {
                                                        unset($results[$questionId][$key]);

                                                        $results[$questionId] = array_values($results[$questionId]);
                                                    }

                                                    // bug 3
                                                    DB::table('answer_multiple')
                                                        ->where('answer_question_id', '=', $question->id)
                                                        ->whereNotIn('option_label_uuid', $option_label_uuid)
                                                        ->delete();
                                                }

                                            }
                                        }
                                    }
                                }
                            }

                            // bag 6
                            if (is_null($option)) {
                                unset($results[$questionId]);

                                $answer_questions_v = DB::table('answer_questions')
                                    ->where('answer_id', $row->answer_id)
                                    ->where('question_uuid', $questionId)
                                    ->get();

                                foreach ($answer_questions_v as $aqv) {
                                    DB::table('answer_multiple')
                                        ->where('answer_question_id', '=', $aqv->id)
                                        ->delete();
                                }

                                DB::table('answer_questions')
                                    ->where('answer_id', $row->answer_id)
                                    ->where('question_uuid', $questionId)
                                    ->delete();
                            }

                        }
                    }
                }

                DB::table('answers')
                    ->where('id', $row->answer_id)
                    ->update(['results' => $results]);
            }
        }
    }
}