test/app/Imports/GoogleTrendsKeywordImport.php

<?php

namespace App\Imports;

use App\Http\Models\GoogleTrendsKeyword;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;

class GoogleTrendsKeywordImport implements ToCollection
{
    use Importable;

    public function collection(Collection $collections)
    {
        foreach ($collections as $key => $collection) {
            if ($key < 1) {
                continue;
            }
            $enable = 0;
            $type = $this->replace(trim($collection[1]));
            if ($type == '禁用') {
                $enable = 1;
            }

            $naturalRankingPercentage = '';
            if (is_numeric($collection[5])) {
                $naturalRankingPercentage = round($collection[5] * 100, 2) . '%';
            }
            $proportionOfBidding = '';
            if (is_numeric($collection[6])) {
                $proportionOfBidding = round($collection[6] * 100, 2) . '%';
            }
            $simultaneousProportion = '';
            if (is_numeric($collection[7])) {
                $simultaneousProportion = round($collection[7] * 100, 2) . '%';
            }
            $proportionNotClicked = '';
            if (is_numeric($collection[8])) {
                $proportionNotClicked = round($collection[8] * 100, 2) . '%';
            }
            $mobileDevices = '';
            if (is_numeric($collection[9])) {
                $mobileDevices = round($collection[9] * 100, 2) . '%';
            }
            $tabletPc = '';
            if (is_numeric($collection[10])) {
                $tabletPc = round($collection[10] * 100, 2) . '%';
            }
            $desktopDevice = '';
            if (is_numeric($collection[11])) {
                $desktopDevice = round($collection[11] * 100, 2) . '%';
            }

            $data = [
                'keyword' => $collection[0],
                'enable' => $enable,
                'monthly_searches' => $collection[2] ?? '',
                'mumber_of_search_results' => $collection[3] ?? '',
                'competition_index' => $collection[4] ?? '',
                'natural_ranking_percentage' => $naturalRankingPercentage ?? '',
                'proportion_of_bidding' => $proportionOfBidding ?? '',
                'simultaneous_proportion' => $simultaneousProportion ?? '',
                'proportion_not_clicked' => $proportionNotClicked ?? '',
                'mobile_devices' => $mobileDevices ?? '',
                'tablet_pc' => $tabletPc ?? '',
                'desktop_device' => $desktopDevice ?? '',
                'order_conditions' => $collection[12] ?? '',
                'industry_difficulty' => $collection[13] ?? '',
                'package_suggestion' => $collection[14] ?? '',

                'product_main_dimension' => $this->replace(trim($collection[15])),
                'product_secondary_dimension' => $this->replace(trim($collection[16])),
                'website_function' => $this->replace(trim($collection[17])),
                'seo_code_optimization' => $this->replace(trim($collection[18])),
                'keyword_screening_suggestions' => $this->replace(trim($collection[19])),
                'keyword_layout_suggestions' => $this->replace(trim($collection[20])),
                'img' => $this->replace(trim($collection[21])),
                'parameter_table' => $this->replace(trim($collection[22])),
                'product_copywriting' => $this->replace(trim($collection[23])),
                'pdf_resources' => $this->replace(trim($collection[24])),
                'video_resources' => $this->replace(trim($collection[25])),
                'external_link_assessment' => $this->replace(trim($collection[26])),
                'social_promotion' => $this->replace(trim($collection[27])),
                'industry_difficulties_analysis' => $this->replace(trim($collection[28])),

                'website' => $this->replace(trim($collection[29])),
                'remarks' => $this->replace(trim($collection[30])),

                'created_at' => date('Y-m-d H:i:s'),
                'user_id' => auth()->user()->id ?? '',
            ];
            $result = GoogleTrendsKeyword::query()->where('keyword', $collection[0])->first();
            if (empty($result)) {
                GoogleTrendsKeyword::query()->insert($data);
            } else {
                $data['user_id'] = $result->user_id;
                GoogleTrendsKeyword::query()->where('keyword', $collection[0])->update($data);
            }
        }
    }

    public function replace($str)
    {
        return str_replace(["\r\n", "\r", "\n"], "", $str);

    }

}