<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use \App\Models\traits\SetConnection;

class MstSchool extends Model
{
    use SetConnection;

    const CREATED_AT = null;
    const UPDATED_AT = null;

    protected $guarded = [];

    //
    public static function getData() {
        $result = MstSchool::get();
        return $result;
    }

    public static function getAllId($type) {
        $res = MstSchool::select('code')->where('type', '=', $type)->get();
        return $res;
    }

    public static function getSchoolName($idList)
    {
        $q = MstSchool::query();
        $num = 0;
        $idList = $idList->toArray();
        foreach($idList as $id) {
            if($id != null) {
                if($num == 0) {
                    $q->where('code', '=', $id);
                    $num++;
                } else {
                    $q->orWhere('code', '=', $id);
                }
            }
        }
        $response = $q->select(['code', 'name'])->get();
        $arr = array();
        foreach ($response as $row){
            $sch =[
                "code" =>$row->code,
                "name" =>$row->name,
            ];
            array_push($arr,$sch);
        }
        return $arr;
    }

    public static function getSchoolByCode($code)
    {
        $result = [];
        if(isset($code)) {
            $result = MstSchool::where('code', '=', $code)->first();
        }
        return $result;
    }

    public static function getSchoolByType($type, $companyId = null)
    {
        $shools = MstSchool::where('type', '=', $type);

	if(!is_null($companyId)) {
            $shools->where('company_id', $companyId);
        }

        $shools = $shools->get();

        $arrResult = array();
        foreach($shools as $shool){
                $arrResult[] = ['code' => $shool->code, 'name' => $shool->name];
        }
        return $arrResult;
    }

    public static function getPrimarySchoolByAddress($pref_id, $city_id)
    {
        $shools = MstSchool::where('pref_id', '=', $pref_id)->where('city_id', '=', $city_id)->whereIn('type',[1, 3])->orderBy('code')->get();

        $arrResult = array();
        foreach($shools as $shool){
            if (!empty($shool->name)) {
                $arrResult[] = ['code' => $shool->code, 'name' => $shool->name];
            }
        }
        return $arrResult;
    }

    public static function getSecondarySchoolByAddress($pref_id, $city_id)
    {
        $shools = MstSchool::where('pref_id', '=', $pref_id)->where('city_id', '=', $city_id)->whereIn('type',[2, 3])->orderBy('code')->groupBy('name')->get();
        $arrResult = array();
        foreach($shools as $shool){
            if (!empty($shool->name)) {
                $arrResult[] = ['code' => $shool->code, 'name' => $shool->name];
            }
        }
        return $arrResult;
    }

    public function city()
    {
        return $this->belongsTo(MstCity::class, "city_id", "code");
    }

    public static function getCodeByName($name)
    {
        $shools = MstSchool::where('name', 'like', '%'.$name.'%')->first();
        return $shools;
    }

    public function scopeWithCompany($query)
    {
        $ownerCompany = auth()->user()->company_id;
        return $query->where("company_id", $ownerCompany);
    }

    public function scopeESchool($query)
    {
        return $query->where("type", 1);
    }

    public function scopeJSchool($query)
    {
        return $query->where("type", 2);
    }

    public static function getSchoolByCity($city_id, $type, $pref_id = null)
    {
        $ownerCompany = auth()->user()->company_id;
        $schoolsQuery = MstSchool::select('code', 'name')->where('city_id', '=', $city_id)
            ->where('company_id', '=', $ownerCompany)
            ->whereNotNull('name')
            ->where('type', '=', $type);

        if (isset($pref_id)){
            $schoolsQuery->where('pref_id', $pref_id);
        }
        $schools = $schoolsQuery->get();
        $arr = array();
        foreach ($schools as $row){
            if (!empty($row->name)) {
                $sch =[
                    "code" =>$row->code,
                    "name" =>$row->name,
                ];
                array_push($arr,$sch);
            }
        }
        return $arr;
    }
}