AnswerRecordDublicate

AnswerRecordDublicate.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 AnswerRecordDublicate extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'answer:record:dublicate';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Remove dublicate answer record';

    /**
     * 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'])
                ->having(DB::raw('count(*)'), '>', 1);
        }, 'r')
            ->select(['answer_id', $this->getJsonAgg()])
            ->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(['results'])
            ->where('id', $row->answer_id)
            ->where('results', '!=', '"{}"')
            ->first();

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

            if (!empty($results)) {
                $questions = json_decode($row->questions, true);

                foreach ($questions as $item) {
                    foreach ($results as $questionId => $option) {
                        if ($questionId === $item['question_uuid']) {
                            switch ($item['type']) {
                                case 'choiceSingle':
                                case 'choiceMultiple':
                                case 'dropdown':
                                case 'choiceMedia':
                                case 'yesno':
                                    if (!empty($option) && is_array($option)) {
                                        foreach ($option as $value) {
                                            if (isset($value['uuid'])) {
                                                $answer_questions = DB::table('answer_questions')
                                                    ->where('answer_id', $row->answer_id)
                                                    ->where('question_uuid', $item['question_uuid'])
                                                    ->get();

                                                foreach ($answer_questions as $aq) {
                                                    $res = json_decode($aq->res, true);

                                                    if (!empty($res)) {
                                                        unset($res['other']);

                                                        $option_keys = array_keys($res);
                                                        $is_option_label_uuid = false;

                                                        foreach ($option_keys as $opt_uuid) {
                                                            if (Str::isUuid($opt_uuid)) {
                                                                $is_option_label_uuid = true;
                                                            }
                                                        }

                                                        if ($is_option_label_uuid) {
                                                            if (!array_key_exists($value['uuid'], $res)) {
                                                                DB::table('answer_multiple')->where('answer_question_id', '=', $aq->id)->delete();

                                                                DB::table('answer_questions')->where('id', $aq->id)->delete();
                                                            }
                                                        }
                                                    }
                                                }

                                                $unique = $answer_questions->unique('res')->pluck('id')->toArray();
                                                $ids = $answer_questions->whereNotIn('id', $unique)->pluck('id')->toArray();

                                                if (!empty($ids)) {
                                                    DB::table('answer_multiple')
                                                        ->whereIn('answer_question_id', $ids)
                                                        ->delete();

                                                    DB::table('answer_questions')
                                                        ->whereIn('id', $ids)
                                                        ->delete();
                                                }

                                            }
                                        }
                                    }

                                    break;

                                case 'input':
                                case 'email':
                                case 'datetime':
                                case 'phone':
                                    if (is_string($option)) {
                                        DB::table('answer_questions')
                                            ->where('answer_id', $row->answer_id)
                                            ->where('question_uuid', $item['question_uuid'])
                                            ->where('result_text', '!=', $option)
                                            ->delete();

                                        $answer_questions = DB::table('answer_questions')
                                            ->where('answer_id', $row->answer_id)
                                            ->where('question_uuid', $item['question_uuid'])
                                            ->offset(1)
                                            ->get();

                                        foreach ($answer_questions as $aq) {
                                            DB::table('answer_multiple')
                                                ->where('answer_question_id', $aq->id)
                                                ->delete();

                                            DB::table('answer_questions')
                                                ->where('id', $aq->id)
                                                ->delete();
                                        }
                                    }

                                    break;

                                case 'rating':
                                case 'slider':
                                case 'scale':
                                    if (is_int($option)) {
                                        DB::table('answer_questions')
                                            ->where('answer_id', $row->answer_id)
                                            ->where('question_uuid', $item['question_uuid'])
                                            ->where('result_int', '!=', $option)
                                            ->delete();

                                        $answer_questions = DB::table('answer_questions')
                                            ->where('answer_id', $row->answer_id)
                                            ->where('question_uuid', $item['question_uuid'])
                                            ->offset(1)
                                            ->get();

                                        foreach ($answer_questions as $aq) {
                                            DB::table('answer_multiple')
                                                ->where('answer_question_id', $aq->id)
                                                ->delete();

                                            DB::table('answer_questions')
                                                ->where('id', $aq->id)
                                                ->delete();
                                        }
                                    }

                                    break;
                            }
                        }
                    }
                }
            }
        }
    }


}