<?php

namespace App\Models;

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

class Vendor extends Model
{
    use SetConnection;

    protected $fillable = ['no', 'user_id','company_id','name','name_kana','manner','zip','address1','address2','address3','address4','vendor_tel1','vendor_tel2','vendor_fax','vendor_mail','vendor_url','vendor_charge','vendor_charge_kana','flyer','freepaper','house_hp','portal','signboard','ad_conf_day','vendor_time','vendor_holiday'];
    //
    public static function getDataById($id) {
        $res = [];
        if($id !==null){
            $res = Vendor::where('id', '=', $id)->first();
        }
        return $res;
    }

    public static function getDataByIdCompany($id, $cid) {
        $res = [];
        if($id !==null){
            $res = Vendor::where('id', '=', $id)->where('company_id', $cid)->first();
        }
        return $res;
    }

    public static function getDataByArticleId($id) {
        $res = [];
        if($id !==null){
            $vendor = Vendor::query();
            $vendor -> leftJoin('relation_vendor_articles', 'vendors.id', '=', 'relation_vendor_articles.vendor_id');
            $res = $vendor -> where('relation_vendor_articles.article_id', '=', $id)->first();
        }
        return $res;
    }

    public static function getDataListByArticleId($id) {
        $res = [];
        if($id !==null){
            $user = auth()->user();
            $vendor = Vendor::query();
            $vendor -> leftJoin('relation_vendor_articles', 'vendors.id', '=', 'relation_vendor_articles.vendor_id');
            $res = $vendor -> where('relation_vendor_articles.article_id', '=', $id);
            if ($user){
                $res->where("vendors.company_id", auth()->user()->company_id);
            }
            // 1.売主＞2.事業主・売主＞6.代理販売＞5.専属専任媒介＞4.専任媒介＞3.一般媒介＞7.販売提携＞9.仲介
            $vendor->orderByRaw('field(relation_vendor_articles.manner,1,2,6,5,4,3,7,9), relation_vendor_articles.vendor_id ASC');

        }
        return $res->get();
    }


    public static function getDataByCondition($con, $num = null) {
        $result = [];
        $vendor = Vendor::query();
        $vendor -> select('vendors.*');
        $vendor -> leftJoin('relation_vendor_articles', 'vendors.id', '=', 'relation_vendor_articles.vendor_id');
        $vendor -> leftJoin('articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
        $vendor -> leftJoin('relation_price_histories', 'relation_price_histories.article_id', '=', 'articles.building_id');

        if(isset($con->company_id)) {
            $vendor->where('vendors.company_id', $con->company_id);
//            $vendor->where('relation_vendor_articles.company_id', $con->company_id);
        }
        if(isset($con->id) && !is_null($con->id)) {
            $vendor->where('vendors.id', '=', $con->id);
        }

        $vendor->where(function ($query) {
            $query->whereNull('articles.provisional')
                ->orWhere('articles.provisional', '0');
        });

        if(isset($con->name) && !is_null($con->name)) {
            $vendor->where(function ($query) use ($con) {
                $query->where('vendors.name', 'LIKE', '%'.$con->name.'%')
                    ->orWhere('vendors.name_kana', 'LIKE', '%'.$con->name.'%');
            });
        }

        if(isset($con->vendor_charge) && !is_null($con->vendor_charge)) {
            $vendor->where(function ($query) use ($con) {
                $query->where('vendors.vendor_charge', 'LIKE', '%'.$con->vendor_charge.'%')
                    ->orWhere('vendors.vendor_charge_kana', 'LIKE', '%'.$con->vendor_charge.'%');
            });
        }

        if(isset($con->vendor_tel) && !is_null($con->vendor_tel)) {
            $vendor->where('vendors.vendor_tel1', '=', $con->vendor_tel);
        }

        if(isset($con->vendor_fax) && !is_null($con->vendor_fax)) {
            $vendorFax = str_replace("-", "", $con->vendor_fax);
            $vendor->whereRaw("REPLACE(`vendors`.`vendor_fax`, '-', '') = ?", [$vendorFax]);
        }

        // 価格変更日
        if(isset($con->from_price_day) && !is_null($con->from_price_day)) {
            $vendor->where('relation_price_histories.regist_date', '>=', $con->from_price_day);
        }
        if(isset($con->to_price_day) && !is_null($con->to_price_day)) {
            $to_price_day = date('Y-m-d',strtotime($con->to_price_day . "+1 days"));
            $vendor->where('relation_price_histories.regist_date', '<', $to_price_day);
        }
        // 店舗
        if(isset($con->shop) && !is_null($con->shop)) {
            $vendor->where('articles.shop', '=', $con->shop);

            $vendor->where('articles.status', '!=', 4);
        }

        $vendor->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });

        $properties = [1,2,3,4,5,null];
        $vendor -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('articles.property', '=', $property);
            }
        });

        // ステータス
        if(isset($con->status) && !is_null($con->status)) {
            $vendor->where('articles.status', '!=', 4);
            $vendor->where('articles.status', '!=', 5);
        }
        // 成約価格
        if(isset($con->price) && !is_null($con->price)) {
            $vendor->where('articles.price', '=', $con->price);
        }
        // 物件登録日
        if(isset($con->from_regist_day) && !is_null($con->from_regist_day)) {
            $vendor->where('articles.created_at', '>=', $con->from_regist_day);
        }
        if(isset($con->to_regist_day) && !is_null($con->to_regist_day)) {
            $to_regist_day = date('Y-m-d',strtotime($con->to_regist_day . "+1 days"));
            $vendor->where('articles.created_at', '<', $to_regist_day);
        }
        //var_dump($vendor->toSql());die;
        // 確認手法
        if(isset($con->ad_conf) && !is_null($con->ad_conf)) {
            $vendor->where('relation_vendor_articles.ad_conf', '=', $con->ad_conf);
        }

        // 広告確認日
        if(isset($con->from_ad_day) && !is_null($con->from_ad_day)) {
            $vendor->where('relation_vendor_articles.ad_conf_day', '>=', $con->from_ad_day);
        }
        if(isset($con->to_ad_day) && !is_null($con->to_ad_day)) {
            $to_ad_day = date('Y-m-d',strtotime($con->to_ad_day . "+1 days"));
            $vendor->where('relation_vendor_articles.ad_conf_day', '<', $to_ad_day);
        }

        // 物件確認日
        if(isset($con->from_conf_day) && !is_null($con->from_conf_day)) {
            $vendor->where('articles.conf_day', '>=', $con->from_conf_day);
        }
        if(isset($con->to_conf_day) && !is_null($con->to_conf_day)) {
            $to_conf_day = date('Y-m-d',strtotime($con->to_conf_day . "+1 days"));
            $vendor->where('articles.conf_day', '<', $to_conf_day);
        }
        // チラシ
        if(isset($con->flyer) && !is_null($con->flyer)) {
            $vendor->where('relation_vendor_articles.flyer', '=', $con->flyer);
        }
        // 情報誌
        if(isset($con->freepaper) && !is_null($con->freepaper)) {
            $vendor->where('relation_vendor_articles.freepaper', '=', $con->freepaper);
        }
        // 自社HP
        if(isset($con->house_hp) && !is_null($con->house_hp)) {
            $vendor->where('relation_vendor_articles.house_hp', '=', $con->house_hp);
        }
        // ポータル
        if(isset($con->portal) && !is_null($con->portal)) {
            $vendor->where('relation_vendor_articles.portal', '=', $con->portal);
        }
        // 現地看板
        if(isset($con->signboard) && !is_null($con->signboard)) {
            $vendor->where('relation_vendor_articles.signboard', '=', $con->signboard);
        }

        if(isset($con->query()['sort']) && isset($con->search_sort)){
            if($con->query()['sort'] == 'vendor'){
                $vendor->orderBy('vendors.name',$con->search_sort);
            }
            else  if($con->query()['sort'] == 'phone_num'){
                $vendor->orderBy('vendors.vendor_tel1',$con->search_sort);
            }
        }

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

        return $result;

    }

}