<?php

namespace App\Models;

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

class RelationVendorArticle extends Model
{
    use SetConnection;
    const CREATED_AT = null;
    const UPDATED_AT = null;

    protected $fillable = [
        'company_id', 'article_id', 'vendor_id', 'charge', 'flyer', 'freepaper', 'house_hp', 'portal', 'signboard', 'ad_conf', 'ad_conf_day', 'article_conf_day', 'manner','home_note'
    ];

    //
    public static function getVendorDataByRainsNo($code='')
    {
        $relation = RelationVendorArticle::select("id")->where("article_id", $code)->groupBy("vendor_id")->get()->toArray();
        $res = RelationVendorArticle::select(['relation_vendor_articles.*', 'vendors.name', 'vendors.vendor_tel1', 'vendors.vendor_fax', 'articles.conf_day', 'articles.close_date', 'articles.memo1'])
            ->leftjoin("articles", "articles.building_id", "=", "relation_vendor_articles.article_id")
            ->leftjoin("vendors", "vendors.id", "=", "relation_vendor_articles.vendor_id")
            //->where("relation_vendor_articles.company_id", auth()->user()->company_id)
            ->whereIn("relation_vendor_articles.id", $relation)
            ->groupby("relation_vendor_articles.vendor_id")
            ->orderBy("relation_vendor_articles.id","ASC")
            ->get();
        return $res;
    }

    public static function getVendorDataByArticleId($code='')
    {
        $relation = RelationVendorArticle::select("id")->where("article_id", $code)->groupBy("vendor_id")->get()->toArray();

        $res = RelationVendorArticle::select(['relation_vendor_articles.*', 'vendors.name', 'vendors.vendor_tel1', 'vendors.vendor_fax', 'articles.conf_day', 'articles.close_date', 'articles.memo1'])
            ->leftjoin("articles", "articles.building_id", "=", "relation_vendor_articles.article_id")
            ->leftjoin("vendors", "vendors.id", "=", "relation_vendor_articles.vendor_id")
            ->where("relation_vendor_articles.company_id", auth()->user()->company_id)
            ->whereIn("relation_vendor_articles.id", $relation)
            ->groupby("relation_vendor_articles.vendor_id")
            // 1.売主＞2.事業主・売主＞6.代理販売＞5.専属専任媒介＞4.専任媒介＞3.一般媒介＞7.販売提携＞9.仲介
            ->orderByRaw('field(relation_vendor_articles.manner,1,2,6,5,4,3,7,9), relation_vendor_articles.vendor_id ASC')
            ->get();
        return $res;
    }

    public function priceHistory()
    {
        return $this->hasMany(RelationPriceHistory::class, "article_id", "article_id");
    }

    public static function getArticleDataByVendorId($code='', $shop = null, $filters = array(), $request = null)
    {
        $vendor = RelationVendorArticle::query();
        $vendor->select(['relation_vendor_articles.*', 'articles.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name'])
            ->leftjoin("articles", "articles.building_id", "=", "relation_vendor_articles.article_id")
            ->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
            ->leftJoin('mst_cities', function($join) {
                $join->on('mst_cities.pref_code', '=', 'articles.address1')
                    ->on('mst_cities.code', '=', 'articles.address2');
            })
            ->leftJoin('mst_towns', function($join) {
                $join->on('mst_towns.pref_code', '=', 'articles.address1')
                   ->on('mst_towns.city_code', '=', 'articles.address2')
                   ->on('mst_towns.code', '=', 'articles.address3');
            })
            ->where("relation_vendor_articles.company_id", auth()->user()->company_id);

	    $vendor->where("articles.company_id", auth()->user()->company_id);

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

        if($shop != null) {
            $vendor->where('articles.shop', '=', $shop);
        }

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

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

        if (isset($request->price) && !empty($request->price)){
            $price = $request->price;
            $vendor->where(function ($query) use ($price) {
                $query->orWhere('articles.price', $price);
            });
        }
        /*if(isset($request->from_price_day) && !empty($request->from_price_day)) {
            $vendor->whereHas("priceHistory", function ($query) use ($request) {
                $query->where('relation_price_histories.regist_date', '>=', $request->from_price_day);
            });
        }
        if(isset($request->to_price_day) && !empty($request->to_price_day)) {
            $vendor->whereHas("priceHistory", function ($query) use ($request) {
                $request->to_price_day = date('Y-m-d',strtotime($request->to_price_day . "+1 days"));
                $query->where('relation_price_histories.regist_date', '<', $request->to_price_day);
            });
        }*/
        if(isset($request->from_price_day) && !empty($request->from_price_day)) {
            $vendor->whereHas("priceHistory", function ($query) use ($request) {
                $query->where('relation_price_histories.regist_date', '>=', $request->from_price_day);
            });
        }
        if(isset($request->to_price_day) && !empty($request->to_price_day)) {
            $vendor->whereHas("priceHistory", function ($query) use ($request) {
                $to_price_day = date('Y-m-d',strtotime($request->to_price_day . "+1 days"));
                $query->where('relation_price_histories.regist_date', '<', $to_price_day);
            });
        }
        // 物件登録日
        if(isset($request->from_regist_day) && !is_null($request->from_regist_day)) {
            $vendor->where('articles.created_at', '>=', $request->from_regist_day);
        }
        if(isset($request->to_regist_day) && !is_null($request->to_regist_day)) {
            $to_regist_day = date('Y-m-d',strtotime($request->to_regist_day . "+1 days"));
            $vendor->where('articles.created_at', '<', $to_regist_day);
        }
        // 広告確認日
        if(isset($request->from_ad_day) && !is_null($request->from_ad_day)) {
            $vendor->where('relation_vendor_articles.ad_conf_day', '>=', $request->from_ad_day);
        }
        if(isset($request->to_ad_day) && !is_null($request->to_ad_day)) {
            $to_ad_day = date('Y-m-d',strtotime($request->to_ad_day . "+1 days"));
            $vendor->where('relation_vendor_articles.ad_conf_day', '<', $to_ad_day);
        }

        // 物件確認日
        if(isset($request->from_conf_day) && !is_null($request->from_conf_day)) {
            $vendor->where('articles.conf_day', '>=', $request->from_conf_day);
        }
        if(isset($request->to_conf_day) && !is_null($request->to_conf_day)) {
            $to_conf_day = date('Y-m-d',strtotime($request->to_conf_day . "+1 days"));
            $vendor->where('articles.conf_day', '<', $to_conf_day);
        }

        $status = array_get($filters, 'status');
        if(!empty($status)) {
            $vendor->where('articles.status', '!=', 4);
            $vendor->where('articles.status', '!=', 5);
        }
        $res = $vendor->where('relation_vendor_articles.vendor_id', '=', $code)->get();

        return $res;
    }

    public static function getCountById($id, $shop = null, $filters = array())
    {
        $vendor = RelationVendorArticle::query();
        $vendor->leftjoin("articles", "articles.building_id", "=", "relation_vendor_articles.article_id");
        $vendor->where('vendor_id', '=', $id)
            ->where("relation_vendor_articles.company_id", auth()->user()->company_id);

        if($shop != null) {
            $vendor->where('articles.shop', '=', $shop);
        }

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

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

        $status = array_get($filters, 'status');
        if(!empty($status)) {
            $vendor->where('articles.status', $status);
        } else {
            $vendor->where('articles.status', '!=', 4);
        }

        $res = $vendor->count();
        return $res;
    }


    public static function getArticleRelation($article_id, $vendor_id)
    {
        $res = RelationVendorArticle::where('article_id', '=', $article_id)
            ->where("relation_vendor_articles.company_id", auth()->user()->company_id)
            ->where('vendor_id', '=', $vendor_id)
            ->first();

        return $res;
    }

    public static function firstRelationVendorArticle($article_id, $vendor_id)
    {
        return RelationVendorArticle::where('article_id', $article_id)
            ->where('vendor_id', $vendor_id)
            ->first();
    }
}