<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class RelationVendorArticle extends Model
{
    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'
    ];

    //
    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'])
        ->leftjoin("vendors", "vendors.id", "=", "relation_vendor_articles.vendor_id")
	->where("vendors.company_id", auth()->user()->company_id)
        ->whereIn("relation_vendor_articles.id", $relation)
	->get();

        return $res;
    }

    public static function getArticleDataByVendorId($code='', $shop = 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');
        });

	$vendor->where("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');
        });
        $vendor->where('articles.status', '!=', 4);
        $res = $vendor->where('relation_vendor_articles.vendor_id', '=', $code)->get();
// dump($vendor->toSql());
// dump($vendor->getBindings());
        return $res;
    }

    public static function getCountById($id, $shop = null) {
        $vendor = RelationVendorArticle::query();
        $vendor->leftjoin("articles", "articles.building_id", "=", "relation_vendor_articles.article_id");
        $vendor->where('vendor_id', '=', $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');
        });
        $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('vendor_id', '=', $vendor_id)->first();
        return $res;
    }
}
