<?php

namespace App\Models;

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

class TownVoice extends Model
{
    use SetConnection;
   // use SoftDeletes;

    protected $fillable = [
        'company_id',
        'name',
        'store',
        'staff',
        // 'zip',
        'address1',
        'address2',
        'address3',
        'gender',
        'age',
        'family_number',
        'child_number',
        'good_point',
        'weak_point',
        'kokai'
      ];

    public static function getData($params = []){
        $query = TownVoice::with(['relationStore', 'relationStaff']);
        $query->select('town_voices.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_towns.koaza_name as koaza_name',  'mst_towns.chome_name as chome_name');
        $query-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'town_voices.address1')
//          -> leftJoin('mst_cities', 'mst_cities.code', '=', 'town_voices.address2')
          -> leftJoin('mst_cities',  function ($join){
              $join->on('mst_cities.pref_code', '=', 'town_voices.address1')
                  ->on('mst_cities.code', '=', 'town_voices.address2');
          })
          -> leftJoin('mst_towns', function($join) {
              $join->on('mst_towns.pref_code', '=', 'town_voices.address1')
                 ->on('mst_towns.city_code', '=', 'town_voices.address2')
                 ->on('mst_towns.code', '=', 'town_voices.address3');
          });
        if (isset($params['staff'])){
            $query->where('staff', $params['staff']);
        }
        if (isset($params['store_id'])){
            $query->where('store', $params['store_id']);
        }
        if (isset($params['address1']) && !empty($params['address1'])) {
            $query->where('town_voices.address1', '=', ($params['address1']));
        }
        if (isset($params['address2']) && !empty($params['address2'])) {
            $query->where('town_voices.address2', '=', ($params['address2']));
        }
        if (isset($params['address3']) && !empty($params['address3'])) {
            $query->where('town_voices.address3', '=', ($params['address3']));
        }
        return $query->get();

    }
    public function relationStore(){
        return $this->hasOne(MstStore::class, 'code', 'store');
    }

    public function relationStaff(){
        return $this->hasOne(User::class, 'id', 'staff');
    }

    // static public function getDataByCondition($condition, $num = null, $sort = null) {
    //     $result = [];
    //     $townVoice = TownVoice::query();
    //     $townVoice->select('town_voices.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
    //     $townVoice-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'town_voices.address1')
    //              -> leftJoin('mst_cities', 'mst_cities.code', '=', 'town_voices.address2')
    //              -> leftJoin('mst_towns', function($join) {
    //                  $join->on('mst_towns.pref_code', '=', 'town_voices.address1')
    //                     ->on('mst_towns.city_code', '=', 'town_voices.address2')
    //                     ->on('mst_towns.code', '=', 'town_voices.address3');
    //              });
    //     $townVoice->where('mst_cities.company_id', '=', auth()->user()->company_id);
    //     if(isset($condition["search_name"]) && !empty($condition["search_name"])) {
    //         $townVoice -> where('town_voices.name', 'LIKE', '%'.$condition["search_name"].'%');
    //     }
    //     if(isset($condition['search_id']) && !empty($condition['search_id'])) {
    //         $townVoice -> where('town_voices.id', $condition["search_id"]);
    //     }
    //     if(isset($condition["search_property"]) && !empty($condition["search_property"])) {
    //         $townVoice -> where('kind', $condition["search_property"]);
    //     }
    //     if(isset($condition["search_address1"]) && !empty($condition["search_address1"])) {
    //         $townVoice -> where('address1', '=', $condition["search_address1"]);
    //     }
    //     if(isset($condition["search_address2"]) && !empty($condition["search_address2"])) {
    //         $townVoice -> where('address2', '=', $condition["search_address2"]);
    //     }
    //     if(isset($condition["search_address3"]) && !empty($condition["search_address1"])) {
    //         $townVoice -> where('address3', '=', $condition["search_address3"]);
    //     }
    //     if(isset($condition["kind"]) && !empty($condition["kind"])) {
    //         $townVoice -> where('town_voices.kind', '=', $condition["kind"]);
    //     }
    //     if(isset($condition["list-address3"]) && !empty($condition["list-address3"])) {
    //         $townVoice -> where('mst_towns.name', 'LIKE', '%'.$condition["list-address3"].'%');
    //     }

    //     if(isset($condition["id"])) {
    //         $townVoice -> where('town_voices.id', '=', $condition["id"]);
    //         $result = $townVoice->first();
    //         return $result;
    //     }


    //     $townVoice->groupBy('town_voices.id');
    //     if($num == null) {
    //         $result = $townVoice->get();
    //     } else {
    //         $result = $townVoice->paginate($num);
    //     }


    //     return $result;

    // }
}