<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class MstStore extends Model
{
    protected $fillable = ['code', 'company_id', 'name', 'name_kana' , 
        'zip', 'address1', 'address2', 'address3', 'address4',
        'freedial', 'tel', 'fax', 'email', 'business_hours',
        'holiday', 'parking', 'comment', 'disp'];

    const CREATED_AT = null;
    const UPDATED_AT = null;
    //
    public static function getDataAll($cid) 
    {
        $res = [];
        $data = MstStore::where('company_id', '=', $cid)->get();
        foreach($data as $row) {
            $res[] = ['code' => $row->code, 'name' => $row->name];
        }

        return $res;
    }

    public static function getDataByArea($cid, $area) 
    {
        $res = [];
        $store = MstStore::query();
        $store->select('mst_stores.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $store->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'mst_stores.address1')
                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'mst_stores.address2')
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'mst_stores.address1')
                        ->on('mst_towns.city_code', '=', 'mst_stores.address2')
                        ->on('mst_towns.code', '=', 'mst_stores.address3');
                 });
        $store->where('company_id', '=', $cid);
        if($area != 999) {
            $store->where('address2', '=', $area);
        } else {
            $store->whereNotIn('address2', [111, 115,107,108,105]);
            $store->orWhereNull('address2');
        }
        
        $res = $store->get();
        return $res;
    }

    public static function getStoreListToTop($cid, $pref, $city) {
        $store = MstStore::query();
        $store->where('company_id', $cid);
        $store->where('address1', $pref);
        if($city != 999) {
            $store->where('address2', $city);
        } else {
            $store->whereNotIn('address2', [111, 115, 107, 108, 105, 999]);
        }
        $store->orderby('id');
        $result = $store->get();

        return $result;
    }

    public static function getMaxNo($cid) {
        $result = MstStore::where('company_id', $cid)->max('code');

        return $result;
    }

    public static function getDataByCode($cid, $code) 
    {
        $store = MstStore::query();
        $store->select('mst_stores.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $store->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'mst_stores.address1')
                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'mst_stores.address2')
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'mst_stores.address1')
                        ->on('mst_towns.city_code', '=', 'mst_stores.address2')
                        ->on('mst_towns.code', '=', 'mst_stores.address3');
                 });
        $result = $store->where('mst_stores.company_id', '=', $cid)->where('mst_stores.code', '=', $code)->first();
        return $result;
    }

    public static function getDataByCodeFront($cid, $code) 
    {
        $store = MstStore::query();
        $store->select('mst_stores.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $store->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'mst_stores.address1')
                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'mst_stores.address2')
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'mst_stores.address1')
                        ->on('mst_towns.city_code', '=', 'mst_stores.address2')
                        ->on('mst_towns.code', '=', 'mst_stores.address3');
                 });
        $store->where('disp', '=', 1);
        $result = $store->where('mst_stores.company_id', '=', $cid)->where('mst_stores.code', '=', $code)->first();
        return $result;
    }

    public static function getDataByCodeFrontByKana($cid, $code) 
    {
        $store = MstStore::query();
        $store->select('mst_stores.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $store->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'mst_stores.address1')
                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'mst_stores.address2')
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'mst_stores.address1')
                        ->on('mst_towns.city_code', '=', 'mst_stores.address2')
                        ->on('mst_towns.code', '=', 'mst_stores.address3');
                 });
        $store->where('disp', '=', 1);
        $result = $store->where('mst_stores.company_id', '=', $cid)->where('mst_stores.name_kana', '=', $code)->first();
        return $result;
    }

    public static function getDataById($id) 
    {
        $res = MstStore::where('id', '=', $id)->first();
        return $res;
    }

    public static function getDataByCompany($cid) {
        $store = MstStore::query();
        $store->select('mst_stores.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $store-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'mst_stores.address1')
                 -> leftJoin('mst_cities', function($join) {
                    $join->on('mst_cities.pref_code', '=', 'mst_stores.address1')
                       ->on('mst_cities.code', '=', 'mst_stores.address2');
                })
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'mst_stores.address1')
                        ->on('mst_towns.city_code', '=', 'mst_stores.address2')
                        ->on('mst_towns.code', '=', 'mst_stores.address3');
                 });
        $result = $store-> where('mst_stores.company_id', $cid)->orderby('mst_stores.code')->get();
        return $result;
    }

    public static function getData($cid) {
        $store = MstStore::query();
        $result = $store->where('company_id', $cid)->where('disp', '=', 1)->orderby('code', 'desc')->get();

        return $result;
    }

    public static function getIdByName($cid, $name) {
        $store = MstStore::query();
        $result = $store->where('company_id', $cid)->where('name', '=', $name)->first();

        return $result;
    }
}
