芝麻web文件管理V1.00

编辑当前文件:/home/forge/stage.herta-bht.smartcon-survey.com/app/Models/Interview.php

<?php

namespace App\Models;

use App\Imports\AnswersImporter;
use Exception;
use Illuminate\Database\Eloquent\Collection as EloquentCollection;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class Interview extends Model
{
    use HasFactory;

protected $guarded = [];

public $timestamps = false;

protected $casts = [
        'created_at' => 'datetime',
    ];

public function country(): BelongsTo
    {
        return $this->belongsTo(Country::class);
    }

public static function getCalendarWeek(int $wave, int $countryId): ?int
    {
        $waves = [
            1 => [1 => 33, 2 => 36, 3 => 39, 4 => 42, 5 => 45, 6 => 48],
            2 => [1 => 33, 2 => 36, 3 => 39, 4 => 42, 5 => 45, 6 => 48],
            3 => [1 => 33, 2 => 36, 3 => 39, 4 => 42, 5 => 45, 6 => 48],
            4 => [1 => 33, 2 => 36, 3 => 39, 4 => 42, 5 => 45, 6 => 48],
        ];

return $waves[$countryId][$wave];
    }

public static function import(Collection $interviews, Country $country): void
    {
        foreach ($interviews as $int) {
            try {
                $interview = self::create([
                    'country_id' => $country->id,
                    'userid' => $int->userid,
                    'scpid' => $int->scpid,
                    'internalid' => $int->internalid,
                    'age' => $int->age,
                    'gender' => $int->gender,
                    'wave' => $int->welle,
                    'state' => $int->nielsen,
                    'created_at' => Carbon::createFromTimestamp($int->istart)->format('Y-m-d H:i:s'),
                ]);
                AnswersImporter::import($interview);
            } catch (Exception) {
                continue;
            }
        }
    }

public static function getQuarterInfo(int $countryId, array $publishedWaves): EloquentCollection|Interview
    {
        return self::select([
            DB::raw('YEAR(created_at) as year'),
            DB::raw('QUARTER(created_at) as quarter'),
            DB::raw('COUNT(DISTINCT userid) as aggregate'),
        ])
            ->where('country_id', $countryId)
            ->whereIn('wave', $publishedWaves)
            ->groupBy('year', 'quarter')->orderBy('year')
            ->orderBy('quarter')->get();
    }

public static function getLastTowQuarterInfo(int $countryId, array $publishedWaves): Collection
    {
        $currentQuarter = Carbon::now()->quarter;
        $currentYear = Carbon::now()->year;
        $lastQuarter = Carbon::now()->subQuarter()->quarter;
        $lastYear = Carbon::now()->subQuarter()->year;
        $lastTowQuarter = self::select([
            DB::raw('YEAR(created_at) as year'),
            DB::raw('QUARTER(created_at) as quarter'),
            DB::raw('COUNT(DISTINCT userid) as aggregate'),
        ])
            ->where('country_id', $countryId)
            ->whereIn('wave', $publishedWaves)
            ->where(function ($query) use ($currentQuarter, $currentYear, $lastQuarter, $lastYear) {
                $query->whereRaw("QUARTER(created_at) = $currentQuarter AND YEAR(created_at) = $currentYear")
                    ->orWhereRaw("QUARTER(created_at) = $lastQuarter AND YEAR(created_at) = $lastYear");
            })
            ->groupBy('quarter', 'year')->orderBy('year', 'asc')
            ->orderBy('quarter', 'asc')->get();

if ($lastTowQuarter->count() < 2) {
            $lastTowQuarter->push([
                'year' => $currentYear,
                'quarter' => $currentQuarter,
                'aggregate' => 0,
            ]);

return collect($lastTowQuarter->toArray());
        }

return collect($lastTowQuarter->toArray());
    }
}