<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Facility extends Model
{
    protected $fillable = ['company_id', 'name', 'kind', 'zip', 'address1', 'address2', 'address3', 'address4', 'lat', 'lng', 'memo', 'file_path'];
    //

    static public function getDataByCondition($condition, $num = null, $sort = null) {
        $result = [];
        $facility = Facility::query();
        $facility->select('facilities.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name'); 
        $facility-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'facilities.address1')
                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'facilities.address2')
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'facilities.address1')
                        ->on('mst_towns.city_code', '=', 'facilities.address2')
                        ->on('mst_towns.code', '=', 'facilities.address3');
                 });
        if(isset($condition["search_name"])) {
            $facility -> where('facilities.name', 'LIKE', '%'.$condition["search_name"].'%');
        }
        if(isset($condition["search_property"])) {
            $facility -> where('kind', '=', $condition["search_property"]);
        }
        if(isset($condition["search_address1"])) {
            $facility -> where('address1', '=', $condition["search_address1"]);
        }
        if(isset($condition["search_address2"])) {
            $facility -> where('address2', '=', $condition["search_address2"]);
        }
        if(isset($condition["search_address3"])) {
            $facility -> where('address3', '=', $condition["search_address3"]);
        }
        if(isset($condition["kind"])) {
            $facility -> where('facilities.kind', '=', $condition["kind"]);
        }
        if(isset($condition["id"])) {
            $facility -> where('facilities.id', '=', $condition["id"]);
            $result = $facility->first();
            return $result;
        }

        if($sort != null) {
            $facility->orderBy('kind');
        }

        if($num == null) {
            $result = $facility->get();
        } else {
            $result = $facility->paginate($num);
        }
        

        return $result;
        
    }

    public static function getDataById($id) {
        $article = Facility::query();
        $result = $article->where('id', '=', $id)->first();
        return $result;
    }

    public static function getRelatonFacility($id) {
        $result = [];
        $facility = Facility::query();
        $facility->select('facilities.name', 'facilities.file_path', 'relation_article_facilities.distance', 'relation_article_facilities.time'); 
        $facility-> leftJoin('relation_article_facilities', 'relation_article_facilities.facility_id', '=', 'facilities.id');
        $facility -> where('relation_article_facilities.article_id', '=', $id);
        $result = $facility->get();

        return $result;
    }

    public static function getData($code='') 
    {
        if($code==''){
            $bloods = Facility::get();

            $arrResult = array();
            foreach($bloods as $blood){
                 $arrResult[] = ['code' => $blood->id, 'name' => $blood->name];
            }
            return $arrResult;         
        } else {
            $bloods = Facility::where('code', '=', $code)->get();
            
            $arrResult = array();
            foreach($bloods as $blood){
                 $arrResult[] = ['code' => $blood->id, 'name' => $blood->name];
            }
            return $arrResult;
        }
    }

    public static function getFacilityArea($cid) {
        $res = [];
        $facility = Facility::query();
        $facility->select('facilities.address2 as code', 'mst_cities.name as name');
        $facility->where('facilities.company_id', '=', $cid);
        $facility->whereNotNull('facilities.address2');
        $facility->leftJoin('mst_cities', 'mst_cities.code', '=', 'facilities.address2');
        $facility->groupBy('facilities.address2');
        $results = $facility->get();

        foreach($results as $row){
            $res[] = ['code' => $row->code, 'name' => $row->name];
       }

       return $res;
    }
    
    public static function getFacilityProperty($cid, $area) {
        $res = [];
        $facility = Facility::query();
        $facility->select('facilities.kind as code', 'mst_facility_types.name as name');
        $facility->leftJoin('mst_facility_types', 'mst_facility_types.code', '=', 'facilities.kind');
        $facility->where('facilities.company_id', '=', $cid);
        $facility->where('facilities.address2', '=', $area);
        $facility->whereNotNull('facilities.kind');
        
        $facility->groupBy('facilities.kind');
        $res = $facility->get();
       return $res;
    } 
}
