<?php

namespace App\Models;

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

class MstCity extends Model
{
    use SetConnection;

    const CREATED_AT = null;
    const UPDATED_AT = null;

    protected $guarded = [];

    public static function getName($prefCode=null, $cityCode=null)
    {
        $city_name = "";
        if($prefCode != null && $cityCode != null){
            $city_name = MstCity::select('name')->where('pref_code', '=', $prefCode)->where('code', '=', $cityCode)->first();
        }
        return $city_name;
    }

    public static function getData($prefCode=null, $cityCode=null, $companyId = null)
    {
		$cityQuery = MstCity::query();

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

		}

        if($prefCode != null && $cityCode != null && $prefCode != -1){

            $cities = $cityQuery->where('pref_code', '=', $prefCode)->where('code', '=', $cityCode)
                ->orderByRaw('CASE WHEN `order` IS NOT NULL THEN `order` ELSE 9999 END')
                ->orderBy('code')->get();

            $arrResult = array();
            foreach($cities as $city){
                 $arrResult[] = ['code' => $city->code, 'name' => $city->name];
            }
            return $arrResult;
        } elseif($prefCode != null && $prefCode != -1) {
            $cities = $cityQuery->where('pref_code', '=', $prefCode)
                //->orderByRaw("FIELD(`code`, 218, 117, 104, 110, 116, 103, 102, 106, 112, 114, 113, 101, 109, 118, 204, 108, 107, 105, 115, 111) DESC")
                ->orderByRaw('CASE WHEN `order` IS NOT NULL THEN `order` ELSE 9999 END')
                ->orderBy('code')
                ->get();

            $arrResult = array();
            foreach($cities as $city){
                 $arrResult[] = ['code' => $city->code, 'name' => $city->name];
            }
            return $arrResult;
        } else {
            $cities = $cityQuery
                ->orderByRaw('CASE WHEN `order` IS NOT NULL THEN `order` ELSE 9999 END')
                ->orderBy('code')
                ->get();

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