<?php

namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Auth;
use App\Models\MstSchool;
use App\Models\RelationPriceHistory;

class Article extends Model
{
    protected $fillable = ['building_id', 'company_id', 'rains_no', 'sale_no', 'suumo_no', 'mansion_id', 'property', 'property_sub', 'name', 'name_gouchi', 'name_portal', 'occupied', 'revenue', 'status', 'shop', 'zip', 'address1', 'address2', 'address3', 'address4', 'name_apartment', 'room_num', 'room_num_disp', 'lat', 'lng', 'main_traffic', 'main_traffic_line', 'main_traffic_line_id', 'main_traffic_station', 'main_traffic_station_id', 'main_traffic_time', 'main_traffic_bus_time', 'main_traffic_bus', 'main_traffic_bus_walk', 'sub_traffic1', 'sub_traffic1_line', 'sub_traffic1_line_id', 'sub_traffic1_station', 'sub_traffic1_station_id', 'sub_traffic1_time', 'sub_traffic1_bus_time', 'sub_traffic1_bus', 'sub_traffic1_bus_walk', 'sub_traffic2', 'sub_traffic2_line', 'sub_traffic2_line_id', 'sub_traffic2_station', 'sub_traffic2_station_id', 'sub_traffic2_time', 'sub_traffic2_bus_time', 'sub_traffic2_bus', 'sub_traffic2_bus_walk', 'primary_school_city', 'primary_school_school', 'primary_school_distance', 'primary_school2_city', 'primary_school2_school', 'primary_school2_distance', 'primary_school3_city', 'primary_school3_school', 'primary_school3_distance','secondary_school_city', 'secondary_school_school', 'secondary_school_distance','secondary_school2_city', 'secondary_school2_school', 'secondary_school2_distance','secondary_school3_city', 'secondary_school3_school', 'secondary_school3_distance', 'memo1', 'memo2', 'company_manner', 'price', 'price_closing', 'close_date', 'tax', 'council', 'council_presence', 'council_cost', 'council_cost_unit', 'spring', 'spring_presence', 'spring_cost', 'spring_cost_unit', 'another_cost_name1', 'another_cost1', 'another_cost_unit1', 'another_cost_name2', 'another_cost2', 'another_cost_unit2', 'broadcasting', 'broadcasting_presence', 'broadcasting_cost', 'broadcasting_fixed_presence', 'broadcasting_fixed_cost', 'broadcasting_fixed_cost_unit', 'internet', 'internet_presence', 'internet_cost', 'internet_fixed_presence', 'internet_fixed_cost', 'internet_fixed_cost_unit', 'catv', 'catv_presence', 'catv_cost', 'catv_fixed_presence', 'catv_fixed_cost', 'catv_fixed_cost_unit', 'total_unit', 'age_year', 'age_month', 'construction', 'construction_sub', 'floor', 'whereabouts', 'underground', 'maisonette', 'maisonette_from', 'maisonette_to', 'site_area', 'sales_company', 'construction_company', 'management_company', 'management_form', 'pet', 'pet_count', 'overview_comment', 'land_right', 'leasehold_kind', 'leasehold_rate', 'land_rent', 'land_rent_val', 'land_rent_unit', 'leasehold_period', 'leasehold_period_year', 'leasehold_period_month', 'right_cost', 'right_cost_val', 'deposit_cost', 'deposit_cost_val', 'security_deposit_cost', 'security_deposit_cost_val', 'other_leasehold', 'total_area', 'total_area_val', 'balcony_area', 'balcony_area_val', 'loof_balcony_area', 'loof_balcony_area_val', 'loof_balcony_area_cost', 'loof_balcony_area_unit', 'garden_area', 'garden_area_val', 'garden_area_cost', 'garden_area_unit', 'terrace_area', 'terrace_direction', 'terrace_area_val', 'terrace_area_cost', 'terrace_area_unit', 'land_area', 'land_area_val', 'total_area_val', 'land_condition1', 'land_condition_area1', 'land_condition_unit1', 'land_condition2', 'land_condition_area2', 'land_condition_unit2', 'land_condition3', 'land_condition_area3', 'building_condition1', 'building_condition_area1', 'building_condition2', 'building_condition_area2', 'building_condition3', 'building_condition_area3', 'building_condition4', 'building_condition_select', 'land_not', 'land_status', 'comp_year', 'comp_month', 'land_delivery', 'delivery_year', 'delivery_month', 'land_delivery_time', 'land_condition', 'land_condition_not', 'building_plan_place', 'building_plan_area', 'ground', 'ground_text', 'building_rate', 'volume_rate', 'road_burden', 'road_burden_area', 'road_numerator', 'road_denominator', 'easement', 'easement_area', 'land_kind1', 'land_direction1', 'road_width1', 'frontage1', 'land_kind2', 'land_direction2', 'road_width2', 'frontage2', 'land_kind3', 'land_direction3', 'road_width3', 'frontage3', 'water_supply', 'sewerage', 'gas', 'garage', 'set', 'set_area', 'use_area', 'land_use', 'land_use2', 'city_plan', 'city_plan_reason', 'section', 'develop_num', 'other_reason', 'other_comment', 'rebuilding', 'total_area', 'total_area_val', 'underground_area', 'underground_area_val', 'garage_area', 'garage_area_val', 'underground_garage_area', 'underground_garage_area_val', 'residence_area', 'residence_area_val', 'floor_plan', 'floor_plan_type', 'completed_year', 'completed_month', 'completed_contract_month', 'move_in', 'move_in_year', 'move_in_month', 'move_in_contract_month', 'current_status', 'current_status_val', 'current_status_rate', 'current_status_year', 'current_status_month', 'building_construction', 'building_construction_sub', 'ground_unit', 'underground_unit', 'parking', 'parking_num', 'architecture_no', 'exterior', 'exterior_year', 'exterior_month', 'exterior_wall', 'exterior_roof', 'exterior_other', 'exterior_text', 'interior', 'interior_year', 'interior_month', 'interior_kitchen', 'interior_bathroom', 'interior_toilet', 'interior_wall', 'interior_floor', 'interior_all', 'interior_other', 'interior_text', 'classfication', 'use_method', 'use_method_text', 'trading_classfication', 'business_status', 'investment_status', 'investment_performance', 'investment_performance_unit', 'investment_interest', 'own_company', 'suumo', 'homes', 'athome', 'catchcopy', 'point', 'comment', 'hp_charge', 'floor_file_type', 'floor_file_path', 'floor_file_disp', 'sheet_file_path2', 'sheet_file_path3', 'sheet_file_type', 'event_category', 'event_schedule', 'event_day', 'from_event', 'to_event', 'from_hour', 'from_minute', 'to_hour', 'to_minute', 'reservation', 'event_comment', 'disp', 'provisional', 'recommend', 'recommend_num', 'repair', 'repair_cost', 'repair_cost_unit', 'management', 'management_cost', 'management_cost_unit', 'original1', 'original2', 'original3', 'original4', 'original5', 'parking_yes', 'parking_yes_cost_from', 'parking_yes_cost_to', 'parking_yes_cost_range', 'parking_yes_cost_unit', 'parking_yes_cost_date', 'parking_require_cost', 'parking_require_management', 'parking_require_management_cost', 'parking_require_management_unit', 'parking_require_repair', 'parking_require_repair_cost', 'parking_require_repair_unit', 'parking_any', 'parking_any_cost_from', 'parking_any_cost_to', 'parking_any_cost_range', 'parking_any_management', 'parking_any_management_cost', 'parking_any_management_unit', 'parking_any_repair', 'parking_any_repair_cost', 'parking_any_repair_unit', 'parking_designated', 'parking_designated_cost', 'parking_designated_unit', 'parking_out', 'parking_out_cost_from', 'parking_out_cost_range', 'parking_out_cost_to', 'parking_out_cost_unit', 'parking_out_cost_date','regular_leased_registration','regular_leased_season','regular_leased_season_nen','regular_leased_cost','regular_leased_transfer','regular_leased_transfer_method','regular_leased_transfer_necessity','regular_leased_transfer_consent', 'del', 'first'];
    
    // protected $guarded = ['id'];

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

        return $result;
    }
    public static function getComment($cid, $bid) {
        $result = Article::where('company_id', $cid)->where('building_id', '=', $bid)->first();

        return $result;
    }

    public static function getCountByArea($cid, $town_id) {
        $article = Article::query();
        $article->where('company_id', $cid)->where('own_company', '=', 2);
        if($town_id != 999) {
            $article->where('address2', '=', $town_id);
        } else {
            $article->where(function ($query) {
                $query->where('address2', '!=', 111)
                    ->where('address2', '!=', 115)
                    ->where('address2', '!=', 107)
                    ->where('address2', '!=', 108)
                    ->where('address2', '!=', 105)
                    ->whereNotNull('address2');
            });
        }
        $article->where(function ($query) {
            $query->whereNull('del')
                ->orWhere('del', '0');
        });
        $properties = [1,2,3,4,5,null];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('property', '=', $property);
            }
        });
        $result = $article->count();

        return $result;
    }
    public static function getCountByProperty($cid, $properties, $property_sub = null, $area = null) {
        $article = Article::query();
        $article->where('company_id', $cid)->where('own_company', '=', 2);
        if($area != null) {
            $address1 = substr($area, 0, 2);
            $address2 = substr($area, 2, 3);
            $article->where('address1', '=', $address1);
            $article->where('address2', '=', $address2);
        }
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('property', '=', $property);
            }
        });

        if($property_sub != null) {
            $article->where('property_sub', '=', $property_sub);
        }

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

        $result = $article->count();

        return $result;
    }

    public static function getNewDataByArea($cid, $area, $num = null) {
        $article = Article::query();
        $article->select('articles.name', 'articles.building_id', 'articles.price', 'articles.main_traffic_line', 'articles.main_traffic_station', 'articles.main_traffic_time'
        , 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name');
       
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article->where('articles.company_id', $cid)->where('articles.own_company', '=', 2);
        if($area != null) {
            $address1 = substr($area, 0, 2);
            $address2 = substr($area, 2, 3);
            $article->where('articles.address1', '=', $address1);
            $article->where('articles.address2', '=', $address2);
        }
        $properties = [1,2,3,4,5,null];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('articles.property', '=', $property);
            }
        });

        $target_date = date("Y-m-d",strtotime("-".config('const.NEW')." day"));
        $article->where('articles.updated_at', '>=', $target_date);

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

        $article->orderBy('articles.updated_at');

        if($num != null) {
            $article->limit($num);
        }

        $result = $article->get();

        return $result;
    }

    public static function getRecommendMaxNo($cid) {
        $result = Article::where('company_id', $cid)->where('recommend', '=', 1)->max('recommend_num');

        return $result;
    }

    public static function getParentMansion($cid, $id) {
        $result = Article::where('company_id', $cid)->where('building_id', '=', $id)->first();
        return $result;
    }

    public static function getParentMansionProperty($cid, $id) {
        $result = Article::select('property')->where('company_id', $cid)->where('building_id', '=', $id)->first();

        return $result;
    }

    public static function getParentMansionFloor($cid, $id) {
        $result = Article::select('floor')->where('company_id', $cid)->where('building_id', '=', $id)->first();

        return $result;
    }

    public static function getDataById($cid, $id) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.place_area', 'articles.building_id', 'articles.suumo_no', 'art2.id as room_id', 'articles.rains_no as rains_id', 'articles.id as article_id', 'articles.name as article_name', 'articles.address2 as address2', 'articles.address3 as address3', 'articles.address4 as address', 'articles.company_id', 'articles.sale_no', 'articles.mansion_id', 'articles.property', 'articles.property_sub', 'articles.name_gouchi', 'articles.name_portal', 'articles.occupied', 'articles.revenue', 'articles.status', 'articles.shop', 'articles.zip', 'articles.address1', 'articles.name_apartment', 'articles.room_num', 'articles.room_num_disp', 'articles.lat', 'articles.lng', 'articles.main_traffic', 'articles.main_traffic_line', 'articles.main_traffic_line_id', 'articles.main_traffic_station', 'articles.main_traffic_station_id', 'articles.main_traffic_time', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus', 'articles.main_traffic_bus_walk', 'articles.sub_traffic1', 'articles.sub_traffic1_line', 'articles.sub_traffic1_line_id', 'articles.sub_traffic1_station', 'articles.sub_traffic1_station_id', 'articles.sub_traffic1_time', 'articles.sub_traffic1_bus_time', 'articles.sub_traffic1_bus', 'articles.sub_traffic1_bus_walk', 'articles.sub_traffic2', 'articles.sub_traffic2_line', 'articles.sub_traffic2_line_id', 'articles.sub_traffic2_station', 'articles.sub_traffic2_station_id', 'articles.sub_traffic2_time', 'articles.sub_traffic2_bus_time', 'articles.sub_traffic2_bus', 'articles.sub_traffic2_bus_walk', 'articles.primary_school_city', 'articles.primary_school_school', 'articles.primary_school_distance', 'articles.primary_school2_city', 'articles.primary_school2_school', 'articles.primary_school2_distance', 'articles.primary_school3_city', 'articles.primary_school3_school', 'articles.primary_school3_distance','articles.secondary_school_city', 'articles.secondary_school_school', 'articles.secondary_school_distance','articles.secondary_school2_city', 'articles.secondary_school2_school', 'articles.secondary_school2_distance','articles.secondary_school3_city', 'articles.secondary_school3_school', 'articles.secondary_school3_distance', 'articles.memo1', 'articles.memo2', 'articles.company_manner', 'articles.price', 'articles.close_date', 'articles.tax', 'articles.council', 'articles.council_presence', 'articles.council_cost', 'articles.council_cost_unit', 'articles.spring', 'articles.spring_presence', 'articles.spring_cost', 'articles.spring_cost_unit', 'articles.spring_kind', 'articles.another_cost_name1', 'articles.another_cost1', 'articles.another_cost_unit1', 'articles.another_cost_name2', 'articles.another_cost2', 'articles.another_cost_unit2', 'articles.broadcasting', 'articles.broadcasting_presence', 'articles.broadcasting_cost', 'articles.broadcasting_fixed_presence', 'articles.broadcasting_fixed_cost', 'articles.broadcasting_fixed_cost_unit', 'articles.internet', 'articles.internet_presence', 'articles.internet_cost', 'articles.internet_fixed_presence', 'articles.internet_fixed_cost', 'articles.internet_fixed_cost_unit', 'articles.catv', 'articles.catv_presence', 'articles.catv_cost', 'articles.catv_fixed_presence', 'articles.catv_fixed_cost', 'articles.catv_fixed_cost_unit', 'articles.total_unit', 'articles.age_year', 'articles.age_month', 'articles.construction', 'articles.construction_sub', 
        'articles.floor', 'articles.whereabouts', 'articles.underground', 'articles.maisonette', 'articles.maisonette_from', 'articles.maisonette_to', 'articles.site_area', 'articles.sales_company', 'articles.construction_company', 'articles.management_company', 'articles.management_form', 'articles.pet', 'articles.pet_count', 'articles.overview_comment', 'articles.land_right', 'articles.leasehold_kind', 'articles.leasehold_rate', 'articles.land_rent', 'articles.land_rent_val', 'articles.land_rent_unit', 'articles.leasehold_period', 'articles.leasehold_period_year', 'articles.leasehold_period_month', 'articles.right_cost', 'articles.right_cost_val', 'articles.deposit_cost', 'articles.deposit_cost_val', 'articles.security_deposit_cost', 'articles.security_deposit_cost_val', 'articles.other_leasehold', 'articles.total_area', 'articles.total_area_val', 'articles.balcony_area', 'articles.balcony_area_val', 'articles.loof_balcony_area', 'articles.loof_balcony_area_val', 'articles.loof_balcony_area_cost', 'articles.loof_balcony_area_unit', 'articles.garden_area', 'articles.garden_area_val', 'articles.garden_area_cost', 'articles.garden_area_unit', 'articles.terrace_area', 'articles.terrace_direction', 'articles.terrace_area_val', 'articles.terrace_area_cost', 'articles.terrace_area_unit', 'articles.land_area', 'articles.land_area_val', 'articles.total_area_val', 'articles.land_condition1', 'articles.land_condition_area1', 'articles.land_condition_unit1', 'articles.land_condition2', 'articles.land_condition_area2', 'articles.land_condition_unit2', 'articles.land_condition3', 'articles.land_condition_area3', 'articles.building_condition1', 'articles.building_condition_area1', 'articles.building_condition2', 'articles.building_condition_area2', 'articles.building_condition3', 'articles.building_condition_area3', 'articles.building_condition4', 'articles.building_condition_select', 'articles.land_not', 'articles.land_status', 'articles.comp_year', 'articles.comp_month', 'articles.land_delivery', 'articles.delivery_year', 'articles.delivery_month', 'articles.land_delivery_time', 'articles.land_condition', 'articles.land_condition_not', 'articles.building_plan_place', 'articles.building_plan_area', 'articles.ground', 'articles.ground_text', 'articles.building_rate', 'articles.volume_rate', 'articles.road_burden', 'articles.road_burden_area', 'articles.road_numerator', 'articles.road_denominator', 'articles.easement', 'articles.easement_area', 'articles.land_kind1', 'articles.land_direction1', 'articles.road_width1', 'articles.frontage1', 'articles.land_kind2', 'articles.land_direction2', 'articles.road_width2', 'articles.frontage2', 'articles.land_kind3', 'articles.land_direction3', 'articles.road_width3', 'articles.frontage3', 'articles.water_supply', 'articles.sewerage', 'articles.gas', 'articles.garage', 'articles.set', 'articles.set_area', 'articles.use_area', 'articles.land_use', 'articles.land_use2', 
        'articles.city_plan', 'articles.city_plan_reason', 'articles.section', 'articles.develop_num', 'articles.other_reason', 'articles.other_comment', 'articles.rebuilding', 'articles.total_area', 'articles.total_area_val', 'articles.underground_area', 'articles.underground_area_val', 'articles.garage_area', 'articles.garage_area_val', 'articles.underground_garage_area', 'articles.underground_garage_area_val', 'articles.residence_area', 'articles.residence_area_val', 'articles.floor_plan', 'articles.floor_plan_type', 'articles.completed_year', 'articles.completed_month', 'articles.completed_contract_month', 'articles.move_in', 'articles.move_in_year', 'articles.move_in_month', 'articles.move_in_contract_month', 'articles.current_status', 'articles.current_status_val', 'articles.current_status_rate', 'articles.current_status_year', 'articles.current_status_month', 'articles.building_construction', 'articles.building_construction_sub', 'articles.ground_unit', 'articles.underground_unit', 'articles.parking', 'articles.parking_num', 'articles.architecture_no', 'articles.exterior', 'articles.exterior_year', 'articles.exterior_month', 'articles.exterior_wall', 'articles.exterior_roof', 'articles.exterior_other', 'articles.exterior_text', 'articles.interior', 'articles.interior_year', 'articles.interior_month', 'articles.interior_kitchen', 'articles.interior_bathroom', 'articles.interior_toilet', 'articles.interior_wall', 'articles.interior_floor', 'articles.interior_all', 'articles.interior_other', 'articles.interior_text', 'articles.classfication', 'articles.use_method', 'articles.use_method_text', 'articles.trading_classfication', 'articles.business_status', 'articles.investment_status', 'articles.investment_performance', 'articles.investment_performance_unit', 'articles.investment_interest', 'articles.own_company', 'articles.suumo', 'articles.homes', 'articles.athome', 'articles.catchcopy', 'articles.point', 'articles.comment', 'articles.hp_charge', 'articles.floor_file_type', 'articles.floor_file_path', 'articles.floor_file_disp', 'articles.sheet_file_path2', 'articles.sheet_file_path3', 'articles.sheet_file_type', 'articles.event_category', 'articles.event_schedule', 'articles.event_day', 'articles.from_event', 'articles.to_event', 'articles.from_hour', 'articles.from_minute', 'articles.to_hour', 'articles.to_minute', 'articles.reservation', 'articles.event_comment', 'articles.disp', 'articles.provisional', 'articles.recommend', 'articles.recommend_num', 'articles.repair', 'articles.repair_cost', 'articles.repair_cost_unit', 'articles.management', 'articles.management_cost', 'articles.management_cost_unit', 'articles.original1', 'articles.original2', 'articles.original3', 'articles.original4', 'articles.original5', 'articles.parking_yes', 'articles.parking_yes_cost_from', 'articles.parking_yes_cost_to', 'articles.parking_yes_cost_range', 'articles.parking_yes_cost_unit', 'articles.parking_yes_cost_date', 'articles.parking_require_cost', 
        'articles.parking_require_management', 'articles.parking_require_management_cost', 'articles.parking_require_management_unit', 'articles.parking_require_repair', 'articles.parking_require_repair_cost', 'articles.parking_require_repair_unit', 'articles.parking_any', 'articles.parking_any_cost_from', 'articles.parking_any_cost_to', 'articles.parking_any_cost_range', 'articles.parking_any_management', 'articles.parking_any_management_cost', 'articles.parking_any_management_unit', 'articles.parking_any_repair', 'articles.parking_any_repair_cost', 'articles.parking_any_repair_unit', 'articles.parking_designated', 'articles.parking_designated_cost', 'articles.parking_designated_unit', 'articles.parking_out', 'articles.parking_out_cost_from', 'articles.parking_out_cost_range', 'articles.parking_out_cost_to', 'articles.parking_out_cost_unit', 'articles.parking_out_cost_date', 'articles.regular_leased_registration', 'articles.regular_leased_season', 'articles.regular_leased_season_nen', 'articles.regular_leased_cost', 'articles.regular_leased_transfer', 'articles.regular_leased_transfer_method', 'articles.regular_leased_transfer_necessity', 'articles.regular_leased_transfer_consent', 'articles.created_at as create', 'articles.updated_at as update', 'articles.first', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 'mst_floor_types.name as floor_plan_name');
        
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article-> leftJoin('articles as art2', 'articles.building_id', '=', 'art2.mansion_id');
        $article->where('articles.company_id', '=', $cid);

        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->where('articles.building_id', '=', $id)->first();
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getDataByIdFront($cid, $id) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.id as article_id', 'articles.building_id', 'articles.company_id', 'articles.rains_no as rains_id', 'articles.sale_no', 'articles.mansion_id', 'articles.property', 'articles.property_sub', 'articles.name as article_name', 'articles.name_gouchi', 'articles.name_portal', 'articles.occupied', 'articles.revenue', 'articles.status', 'articles.shop', 'articles.zip', 'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.name_apartment', 'articles.room_num', 'articles.room_num_disp', 'articles.lat', 'articles.lng', 'articles.main_traffic', 'articles.main_traffic_line', 'articles.main_traffic_line_id', 'articles.main_traffic_station', 'articles.main_traffic_station_id', 'articles.main_traffic_time', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus', 'articles.main_traffic_bus_walk', 'articles.sub_traffic1', 'articles.sub_traffic1_line', 'articles.sub_traffic1_line_id', 'articles.sub_traffic1_station', 'articles.sub_traffic1_station_id', 'articles.sub_traffic1_time', 'articles.sub_traffic1_bus_time', 'articles.sub_traffic1_bus', 'articles.sub_traffic1_bus_walk', 'articles.sub_traffic2', 'articles.sub_traffic2_line', 'articles.sub_traffic2_line_id', 'articles.sub_traffic2_station', 'articles.sub_traffic2_station_id', 'articles.sub_traffic2_time', 'articles.sub_traffic2_bus_time', 'articles.sub_traffic2_bus', 'articles.sub_traffic2_bus_walk', 'articles.primary_school_city', 'articles.primary_school_school', 'articles.primary_school_distance', 'articles.primary_school2_city', 'articles.primary_school2_school', 'articles.primary_school2_distance', 'articles.primary_school3_city', 'articles.primary_school3_school', 'articles.primary_school3_distance','articles.secondary_school_city', 'articles.secondary_school_school', 'articles.secondary_school_distance','articles.secondary_school2_city', 'articles.secondary_school2_school', 'articles.secondary_school2_distance','articles.secondary_school3_city', 'articles.secondary_school3_school', 'articles.secondary_school3_distance', 'articles.memo1', 'articles.memo2', 'articles.company_manner', 'articles.price', 'articles.close_date', 'articles.tax', 'articles.council', 'articles.council_presence', 'articles.council_cost', 'articles.council_cost_unit', 'articles.spring', 'articles.spring_presence', 'articles.spring_cost', 'articles.spring_cost_unit', 'articles.spring_kind', 'articles.another_cost_name1', 'articles.another_cost1', 'articles.another_cost_unit1', 'articles.another_cost_name2', 'articles.another_cost2', 'articles.another_cost_unit2', 'articles.broadcasting', 'articles.broadcasting_presence', 'articles.broadcasting_cost', 'articles.broadcasting_fixed_presence', 'articles.broadcasting_fixed_cost', 'articles.broadcasting_fixed_cost_unit', 'articles.internet', 'articles.internet_presence', 'articles.internet_cost', 'articles.internet_fixed_presence', 'articles.internet_fixed_cost', 'articles.internet_fixed_cost_unit', 'articles.catv', 'articles.catv_presence', 'articles.catv_cost', 'articles.catv_fixed_presence', 'articles.catv_fixed_cost', 'articles.catv_fixed_cost_unit', 'articles.total_unit', 'articles.age_year', 'articles.age_month', 'articles.construction', 'articles.construction_sub', 'articles.floor', 'articles.whereabouts', 'articles.underground', 'articles.maisonette', 'articles.maisonette_from', 'articles.maisonette_to', 'articles.site_area', 'articles.sales_company', 'articles.construction_company', 'articles.management_company', 'articles.management_form', 'articles.pet', 'articles.pet_count', 'articles.overview_comment', 'articles.land_right', 'articles.leasehold_kind', 'articles.leasehold_rate', 'articles.land_rent', 'articles.land_rent_val', 'articles.land_rent_unit', 'articles.leasehold_period', 'articles.leasehold_period_year', 'articles.leasehold_period_month', 'articles.right_cost', 'articles.right_cost_val', 'articles.deposit_cost', 'articles.deposit_cost_val', 'articles.security_deposit_cost', 'articles.security_deposit_cost_val', 'articles.other_leasehold', 'articles.total_area', 'articles.total_area_val', 'articles.balcony_area', 'articles.balcony_area_val', 'articles.loof_balcony_area', 'articles.loof_balcony_area_val', 'articles.loof_balcony_area_cost', 'articles.loof_balcony_area_unit', 'articles.garden_area', 'articles.garden_area_val', 'articles.garden_area_cost', 'articles.garden_area_unit', 'articles.terrace_area', 'articles.terrace_direction', 'articles.terrace_area_val', 'articles.terrace_area_cost', 'articles.terrace_area_unit', 'articles.land_area', 'articles.land_area_val', 'articles.total_area_val', 'articles.land_condition1', 'articles.land_condition_area1', 'articles.land_condition_unit1', 'articles.land_condition2', 'articles.land_condition_area2', 'articles.land_condition_unit2', 'articles.land_condition3', 'articles.land_condition_area3', 'articles.building_condition1', 'articles.building_condition_area1', 'articles.building_condition2', 'articles.building_condition_area2', 'articles.building_condition3', 'articles.building_condition_area3', 'articles.building_condition4', 'articles.building_condition_select', 'articles.land_not', 'articles.land_status', 'articles.comp_year', 'articles.comp_month', 'articles.land_delivery', 'articles.delivery_year', 'articles.delivery_month', 'articles.land_delivery_time', 'articles.land_condition', 'articles.land_condition_not', 'articles.building_plan_place', 'articles.building_plan_area', 'articles.ground', 'articles.ground_text', 'articles.building_rate', 'articles.volume_rate', 'articles.road_burden', 'articles.road_burden_area', 'articles.road_numerator', 'articles.road_denominator', 'articles.easement', 'articles.easement_area', 'articles.land_kind1', 'articles.land_direction1', 'articles.road_width1', 'articles.frontage1', 'articles.land_kind2', 'articles.land_direction2', 'articles.road_width2', 'articles.frontage2', 'articles.land_kind3', 'articles.land_direction3', 'articles.road_width3', 'articles.frontage3', 'articles.water_supply', 'articles.sewerage', 'articles.gas', 'articles.garage', 'articles.set', 'articles.set_area', 'articles.use_area', 'articles.land_use', 'articles.land_use2', 'articles.city_plan', 'articles.city_plan_reason', 'articles.section', 'articles.develop_num', 'articles.other_reason', 'articles.other_comment', 'articles.rebuilding', 'articles.total_area', 'articles.total_area_val', 'articles.underground_area', 'articles.underground_area_val', 'articles.garage_area', 'articles.garage_area_val', 'articles.underground_garage_area', 'articles.underground_garage_area_val', 'articles.residence_area', 'articles.residence_area_val', 'articles.floor_plan', 'articles.floor_plan_type', 'articles.completed_year', 'articles.completed_month', 'articles.completed_contract_month', 'articles.move_in', 'articles.move_in_year', 'articles.move_in_month', 'articles.move_in_contract_month', 'articles.current_status', 'articles.current_status_val', 'articles.current_status_rate', 'articles.current_status_year', 'articles.current_status_month', 'articles.building_construction', 'articles.building_construction_sub', 'articles.ground_unit', 'articles.underground_unit', 'articles.parking', 'articles.parking_num', 'articles.architecture_no', 'articles.exterior', 'articles.exterior_year', 'articles.exterior_month', 'articles.exterior_wall', 'articles.exterior_roof', 'articles.exterior_other', 'articles.exterior_text', 'articles.interior', 'articles.interior_year', 'articles.interior_month', 'articles.interior_kitchen', 'articles.interior_bathroom', 'articles.interior_toilet', 'articles.interior_wall', 'articles.interior_floor', 'articles.interior_all', 'articles.interior_other', 'articles.interior_text', 'articles.classfication', 'articles.use_method', 'articles.use_method_text', 'articles.trading_classfication', 'articles.business_status', 'articles.investment_status', 'articles.investment_performance', 'articles.investment_performance_unit', 'articles.investment_interest', 'articles.own_company', 'articles.suumo', 'articles.homes', 'articles.athome', 'articles.catchcopy', 'articles.point', 'articles.comment', 'articles.hp_charge', 'articles.floor_file_type', 'articles.floor_file_path', 'articles.floor_file_disp', 'articles.sheet_file_path2', 'articles.sheet_file_path3', 'articles.sheet_file_type', 'articles.event_category', 'articles.event_schedule', 'articles.event_day', 'articles.from_event', 'articles.to_event', 'articles.from_hour', 'articles.from_minute', 'articles.to_hour', 'articles.to_minute', 'articles.reservation', 'articles.event_comment', 'articles.disp', 'articles.provisional', 'articles.recommend', 'articles.recommend_num', 'articles.repair_cost', 'articles.repair_cost_unit', 'articles.management_cost', 'articles.management_cost_unit', 'articles.original1', 'articles.original2', 'articles.original3', 'articles.original4', 'articles.original5', 'articles.parking_yes', 'articles.parking_yes_cost_from', 'articles.parking_yes_cost_to', 'articles.parking_yes_cost_range', 'articles.parking_yes_cost_unit', 'articles.parking_yes_cost_date', 'articles.parking_require_cost', 'articles.parking_require_management', 'articles.parking_require_management_cost', 'articles.parking_require_management_unit', 'articles.parking_require_repair', 'articles.parking_require_repair_cost', 'articles.parking_require_repair_unit', 'articles.parking_any', 'articles.parking_any_cost_from', 'articles.parking_any_cost_to', 'articles.parking_any_cost_range', 'articles.parking_any_management', 'articles.parking_any_management_cost', 'articles.parking_any_management_unit', 'articles.parking_any_repair', 'articles.parking_any_repair_cost', 'articles.parking_any_repair_unit', 'articles.parking_designated', 'articles.parking_designated_cost', 'articles.parking_designated_unit', 'articles.parking_out', 'articles.parking_out_cost_from', 'articles.parking_out_cost_range', 'articles.parking_out_cost_to', 'articles.parking_out_cost_unit', 'articles.parking_out_cost_date', 'articles.regular_leased_registration', 'articles.regular_leased_season', 'articles.regular_leased_season_nen', 'articles.regular_leased_cost', 'articles.regular_leased_transfer', 'articles.regular_leased_transfer_method', 'articles.regular_leased_transfer_necessity', 'articles.regular_leased_transfer_consent', 'articles.created_at as create','articles.updated_at as update','mst_prefectures.name as pref_name','mst_cities.name as city_name','mst_towns.name as town_name','articles.zip','articles.address1','articles.address2 as address2','articles.address3 as address3','articles.address4 as address','mst_floor_types.name as floor_plan_name');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article->where('articles.company_id', '=', $cid);

        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $article->where('articles.status', '!=', 4);
        $article->where('articles.own_company', '=', 2);

        $result = $article->where('articles.building_id', '=', $id)->first();
// print_r($result);
        return $result;
    }

    public static function getMansionDataByIdFront($cid, $id) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.id as article_id', 'articles.building_id', 'articles.company_id', 'articles.rains_no as rains_id', 'articles.sale_no', 'articles.mansion_id', 'articles.property', 'articles.property_sub', 'articles.name as article_name', 'articles.name_gouchi', 'articles.name_portal', 'articles.occupied', 'articles.revenue', 'articles.status', 'articles.shop', 'articles.zip', 'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.name_apartment', 'articles.room_num', 'articles.room_num_disp', 'articles.lat', 'articles.lng', 'articles.main_traffic', 'articles.main_traffic_line', 'articles.main_traffic_line_id', 'articles.main_traffic_station', 'articles.main_traffic_station_id', 'articles.main_traffic_time', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus', 'articles.main_traffic_bus_walk', 'articles.sub_traffic1', 'articles.sub_traffic1_line', 'articles.sub_traffic1_line_id', 'articles.sub_traffic1_station', 'articles.sub_traffic1_station_id', 'articles.sub_traffic1_time', 'articles.sub_traffic1_bus_time', 'articles.sub_traffic1_bus', 'articles.sub_traffic1_bus_walk', 'articles.sub_traffic2', 'articles.sub_traffic2_line', 'articles.sub_traffic2_line_id', 'articles.sub_traffic2_station', 'articles.sub_traffic2_station_id', 'articles.sub_traffic2_time', 'articles.sub_traffic2_bus_time', 'articles.sub_traffic2_bus', 'articles.sub_traffic2_bus_walk', 'articles.primary_school_city', 'articles.primary_school_school', 'articles.primary_school_distance', 'articles.primary_school2_city', 'articles.primary_school2_school', 'articles.primary_school2_distance','articles.secondary_school_city', 'articles.secondary_school_school', 'articles.secondary_school_distance','articles.secondary_school2_city', 'articles.secondary_school2_school', 'articles.secondary_school2_distance', 'articles.memo1', 'articles.memo2', 'articles.company_manner', 'articles.price', 'articles.close_date', 'articles.tax', 'articles.council', 'articles.council_presence', 'articles.council_cost', 'articles.council_cost_unit', 'articles.spring', 'articles.spring_presence', 'articles.spring_cost', 'articles.spring_cost_unit', 'articles.spring_kind', 'articles.another_cost_name1', 'articles.another_cost1', 'articles.another_cost_unit1', 'articles.another_cost_name2', 'articles.another_cost2', 'articles.another_cost_unit2', 'articles.broadcasting', 'articles.broadcasting_presence', 'articles.broadcasting_cost', 'articles.broadcasting_fixed_presence', 'articles.broadcasting_fixed_cost', 'articles.broadcasting_fixed_cost_unit', 'articles.internet', 'articles.internet_presence', 'articles.internet_cost', 'articles.internet_fixed_presence', 'articles.internet_fixed_cost', 'articles.internet_fixed_cost_unit', 'articles.catv', 'articles.catv_presence', 'articles.catv_cost', 'articles.catv_fixed_presence', 'articles.catv_fixed_cost', 'articles.catv_fixed_cost_unit', 'articles.total_unit', 'articles.age_year', 'articles.age_month', 'articles.construction', 'articles.construction_sub', 'articles.floor', 'articles.whereabouts', 'articles.underground', 'articles.maisonette', 'articles.maisonette_from', 'articles.maisonette_to', 'articles.site_area', 'articles.sales_company', 'articles.construction_company', 'articles.management_company', 'articles.management_form', 'articles.pet', 'articles.pet_count', 'articles.overview_comment', 'articles.land_right', 'articles.leasehold_kind', 'articles.leasehold_rate', 'articles.land_rent', 'articles.land_rent_val', 'articles.land_rent_unit', 'articles.leasehold_period', 'articles.leasehold_period_year', 'articles.leasehold_period_month', 'articles.right_cost', 'articles.right_cost_val', 'articles.deposit_cost', 'articles.deposit_cost_val', 'articles.security_deposit_cost', 'articles.security_deposit_cost_val', 'articles.other_leasehold', 'articles.total_area', 'articles.total_area_val', 'articles.balcony_area', 'articles.balcony_area_val', 'articles.loof_balcony_area', 'articles.loof_balcony_area_val', 'articles.loof_balcony_area_cost', 'articles.loof_balcony_area_unit', 'articles.garden_area', 'articles.garden_area_val', 'articles.garden_area_cost', 'articles.garden_area_unit', 'articles.terrace_area', 'articles.terrace_direction', 'articles.terrace_area_val', 'articles.terrace_area_cost', 'articles.terrace_area_unit', 'articles.land_area', 'articles.land_area_val', 'articles.total_area_val', 'articles.land_condition1', 'articles.land_condition_area1', 'articles.land_condition_unit1', 'articles.land_condition2', 'articles.land_condition_area2', 'articles.land_condition_unit2', 'articles.land_condition3', 'articles.land_condition_area3', 'articles.building_condition1', 'articles.building_condition_area1', 'articles.building_condition2', 'articles.building_condition_area2', 'articles.building_condition3', 'articles.building_condition_area3', 'articles.building_condition4', 'articles.building_condition_select', 'articles.land_not', 'articles.land_status', 'articles.comp_year', 'articles.comp_month', 'articles.land_delivery', 'articles.delivery_year', 'articles.delivery_month', 'articles.land_delivery_time', 'articles.land_condition', 'articles.land_condition_not', 'articles.building_plan_place', 'articles.building_plan_area', 'articles.ground', 'articles.ground_text', 'articles.building_rate', 'articles.volume_rate', 'articles.road_burden', 'articles.road_burden_area', 'articles.road_numerator', 'articles.road_denominator', 'articles.easement', 'articles.easement_area', 'articles.land_kind1', 'articles.land_direction1', 'articles.road_width1', 'articles.frontage1', 'articles.land_kind2', 'articles.land_direction2', 'articles.road_width2', 'articles.frontage2', 'articles.land_kind3', 'articles.land_direction3', 'articles.road_width3', 'articles.frontage3', 'articles.water_supply', 'articles.sewerage', 'articles.gas', 'articles.garage', 'articles.set', 'articles.set_area', 'articles.use_area', 'articles.land_use', 'articles.land_use2', 'articles.city_plan', 'articles.city_plan_reason', 'articles.section', 'articles.develop_num', 'articles.other_reason', 'articles.other_comment', 'articles.rebuilding', 'articles.total_area', 'articles.total_area_val', 'articles.underground_area', 'articles.underground_area_val', 'articles.garage_area', 'articles.garage_area_val', 'articles.underground_garage_area', 'articles.underground_garage_area_val', 'articles.residence_area', 'articles.residence_area_val', 'articles.floor_plan', 'articles.floor_plan_type', 'articles.completed_year', 'articles.completed_month', 'articles.completed_contract_month', 'articles.move_in', 'articles.move_in_year', 'articles.move_in_month', 'articles.move_in_contract_month', 'articles.current_status', 'articles.current_status_val', 'articles.current_status_rate', 'articles.current_status_year', 'articles.current_status_month', 'articles.building_construction', 'articles.building_construction_sub', 'articles.ground_unit', 'articles.underground_unit', 'articles.parking', 'articles.parking_num', 'articles.architecture_no', 'articles.exterior', 'articles.exterior_year', 'articles.exterior_month', 'articles.exterior_wall', 'articles.exterior_roof', 'articles.exterior_other', 'articles.exterior_text', 'articles.interior', 'articles.interior_year', 'articles.interior_month', 'articles.interior_kitchen', 'articles.interior_bathroom', 'articles.interior_toilet', 'articles.interior_wall', 'articles.interior_floor', 'articles.interior_all', 'articles.interior_other', 'articles.interior_text', 'articles.classfication', 'articles.use_method', 'articles.use_method_text', 'articles.trading_classfication', 'articles.business_status', 'articles.investment_status', 'articles.investment_performance', 'articles.investment_performance_unit', 'articles.investment_interest', 'articles.own_company', 'articles.suumo', 'articles.homes', 'articles.athome', 'articles.catchcopy', 'articles.point', 'articles.comment', 'articles.hp_charge', 'articles.floor_file_type', 'articles.floor_file_path', 'articles.floor_file_disp', 'articles.sheet_file_path2', 'articles.sheet_file_path3', 'articles.sheet_file_type', 'articles.event_category', 'articles.event_schedule', 'articles.event_day', 'articles.from_event', 'articles.to_event', 'articles.from_hour', 'articles.from_minute', 'articles.to_hour', 'articles.to_minute', 'articles.reservation', 'articles.event_comment', 'articles.disp', 'articles.provisional', 'articles.recommend', 'articles.recommend_num', 'articles.repair_cost', 'articles.repair_cost_unit', 'articles.management_cost', 'articles.management_cost_unit', 'articles.original1', 'articles.original2', 'articles.original3', 'articles.original4', 'articles.original5', 'articles.parking_yes', 'articles.parking_yes_cost_from', 'articles.parking_yes_cost_to', 'articles.parking_yes_cost_range', 'articles.parking_yes_cost_unit', 'articles.parking_yes_cost_date', 'articles.parking_require_cost', 'articles.parking_require_management', 'articles.parking_require_management_cost', 'articles.parking_require_management_unit', 'articles.parking_require_repair', 'articles.parking_require_repair_cost', 'articles.parking_require_repair_unit', 'articles.parking_any', 'articles.parking_any_cost_from', 'articles.parking_any_cost_to', 'articles.parking_any_cost_range', 'articles.parking_any_management', 'articles.parking_any_management_cost', 'articles.parking_any_management_unit', 'articles.parking_any_repair', 'articles.parking_any_repair_cost', 'articles.parking_any_repair_unit', 'articles.parking_designated', 'articles.parking_designated_cost', 'articles.parking_designated_unit', 'articles.parking_out', 'articles.parking_out_cost_from', 'articles.parking_out_cost_range', 'articles.parking_out_cost_to', 'articles.parking_out_cost_unit', 'articles.parking_out_cost_date', 'articles.regular_leased_registration', 'articles.regular_leased_season', 'articles.regular_leased_season_nen', 'articles.regular_leased_cost', 'articles.regular_leased_transfer', 'articles.regular_leased_transfer_method', 'articles.regular_leased_transfer_necessity', 'articles.regular_leased_transfer_consent', 'articles.created_at as create','articles.updated_at as update','mst_prefectures.name as pref_name','mst_cities.name as city_name','mst_towns.name as town_name','articles.zip','articles.address1','articles.address2 as address2','articles.address3 as address3','articles.address4 as address','mst_floor_types.name as floor_plan_name');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article->where('articles.company_id', '=', $cid);

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

        $result = $article->where('articles.building_id', '=', $id)->first();
// print_r($result);
        return $result;
    }

    public static function getCountByCityId($pid, $cid) {
        $user = Auth::user();

        $article = Article::query();
        $article->where('own_company', '=', 2);
        $article->where('address1', '=', $pid);
        $article->where('address2', '=', $cid);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        
        $properties = [1,2,3,4,5,null];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('articles.property', '=', $property);
            }
        });
        $article->where('articles.status', '!=', 4);
        $article->where('articles.own_company', '=', 2);
        $result = $article->count();


        return $result;
    }
    
    public static function getRecommendDispData($num) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.id as article_id', 'articles.name as article_name', 'property', 'price', 'main_traffic_line', 'main_traffic_station', 'main_traffic_time',
                        'floor_file_path','sheet_file_path2', 'sheet_file_path3', 'sheet_file_type',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address', 'articles.created_at');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        // if($user->company_id != null) {
        //     $article->where('company_id', '=', $user->company_id);
        // }
        $article->where('recommend', '=', 1);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $article->orderByRaw('CASE WHEN DATE_ADD(NOW(), INTERVAL -14 DAY) < articles.created_at THEN 1 ELSE 2 END');
        $article->orderBy('articles.recommend_num');
        $result = $article->limit($num)->get();
        // print($article->toSql());
        return $result;
    }

    public static function getFrontRecommendDispData($num) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.id as article_id', 'articles.name as article_name', 'property', 'price', 'main_traffic_line', 'main_traffic_station', 'main_traffic_time',
                        'floor_file_path','sheet_file_path2', 'sheet_file_path3', 'sheet_file_type',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address', 'articles.created_at');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        
        $article->where('recommend', '=', 1);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $article->orderByRaw('CASE WHEN DATE_ADD(NOW(), INTERVAL -14 DAY) < articles.created_at THEN 1 ELSE 2 END');
        $article->orderBy('articles.recommend_num');
        $article->where('articles.status', '!=', 4);
        $article->where('articles.own_company', '=', 2);
        $result = $article->limit($num)->get();
        // print($article->toSql());
        return $result;
    }

    public static function getCountByAreaProperty($pid, $cid, $property, $property_sub = null) {
        $user = Auth::user();

        $article = Article::query();
        $article->where('address1', '=', $pid);
        $article->where('address2', '=', $cid);

        $article->where('property', '=', $property);
        if(!empty($property_sub)) {
            $article->where('property_sub', '=', $property_sub);
        }
        $article->where('own_company', '=', 2);
        $article->where('status', '!=', 4);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->count();

        return $result;
    }

    public static function getCountByAreaPropertyNew($pid, $cid, $type, $property_sub = null) {
        $user = Auth::user();

        $article = Article::query();
        // if($user->company_id != null) {
        //     $article->where('company_id', '=', $user->company_id);
        // }
        $article->where('address1', '=', $pid);
        $article->where('address2', '=', $cid);
        
        if($type == 1) {
            $properties = [1,2,3];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });

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


        if(!empty($property_sub)) {
            $article->where('property_sub', '=', $property_sub);
        }
        $article->where('own_company', '=', 2);
        $article->where('status', '!=', 4);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->count();

        return $result;
    }

    public static function getCountBySaleNo($id) {
        $user = Auth::user();

        $article = Article::query();
        $article->where('sale_no', '=', $id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->count();
        return $result;
    }

    public static function getDataBySaleNo($id) {
        $user = Auth::user();

        $article = Article::query();
        $article->where('sale_no', '=', $id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->get();
        return $result;
    }

    public static function getIdByCondition($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.building_id');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');

        // whereの設定
        $article = self::setWhere($article, $condition, $recommend, $close, $count_flag);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });

        if(isset($condition->order)) {
            $order = $condition->order;
            if($order == "recommend") {
                $article->orderBy('articles.recommend_num');
            } else if($order == "new") {
                $article->orderBy('articles.recommend');
            } else if($order == "station") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            }
        }

        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> get() -> count();
        } else {
            $result = $article -> get();
        }
// print($article->toSql());
        return $result;
    }

    public static function getDataByCondition($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*', 
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name',
            'art2.building_id as room_id', 'art2.room_num as room_number', 'art2.floor_file_path as room_floor_file_path', 'art2.price as room_price'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article-> leftJoin('articles as art2', 'articles.mansion_id', '=', 'art2.building_id');
        $article-> leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
        $article-> leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id');
        $article-> leftJoin('relation_price_histories', 'articles.building_id', '=', 'relation_price_histories.article_id');

        // whereの設定
        $article = self::setWhere($article, $condition, $recommend, $close, $count_flag);

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

        // 画像有無
        if(isset($condition->image)) {
            if($condition->image == 1) {
                $article->whereNull('articles.floor_file_path');
            } else {
                $article->whereNotNull('articles.floor_file_path');
            }
        }
        
        if(isset($condition->order)) {
            $order = $condition->order;
            if($order == "recommend") {
                $article->orderBy('articles.recommend_num');
            } else if($order == "new") {
                $article->orderBy('articles.recommend');
            } else if($order == "station") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            }
        }
        if(isset($condition->sort)) {
            if($condition->sort == "new") {
                $article->orderBy('articles.updated_at', 'DESC');
            } else if($condition->sort == "newprice") {
                // $article->orderBy('history.max');
            } else if($condition->sort == "inexpensive") {
                $article->orderBy('articles.price');
            } else if($condition->sort == "station") {
                $article->orderByRaw('LEAST(COALESCE(articles.main_traffic_time, 2147483647), COALESCE(articles.sub_traffic1_time, 2147483647), COALESCE(articles.sub_traffic2_time, 2147483647))');
            } else if($condition->sort == "age") {
                $article->orderBy('articles.age_year')->orderBy('articles.age_month');
            } else if($condition->sort == "land") {
                $article->orderBy('articles.land_area_val', 'DESC');
            } else if($condition->sort == "floor") {
                $article->orderBy('articles.total_area_val', 'DESC');
            } else if($condition->sort == "property") {
                $article->orderBy('articles.property');
            } else if($condition->sort == "address") {
                $article->orderBy('articles.address1')->orderBy('articles.address2')->orderBy('articles.address3')->orderBy('articles.address4');
            } else if($condition->sort == "line") {
                $article->orderBy('articles.main_traffic_line')->orderBy('articles.main_traffic_station');
            } else if($condition->sort == "vendor") {

            }
        }
        $article->groupBy('articles.building_id');

        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> get() -> count();
        } else {
            $result = $article -> get();
        }
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getSearchDataByCondition($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*',
	    'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4',
	    'articles.building_id', 'articles.mansion_id','articles.name','articles.price','articles.property','articles.property_sub','articles.created_at', 'articles.floor_plan', 'articles.floor_plan_type', 
            'articles.main_traffic', 'articles.main_traffic_line', 'articles.main_traffic_line_id', 'articles.main_traffic_station', 'articles.main_traffic_station_id', 'articles.main_traffic_time', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus', 'articles.main_traffic_bus_walk',
            'articles.age_year','articles.age_month','articles.land_area_val','articles.total_area_val','articles.whereabouts','articles.primary_school_school','articles.secondary_school_school', 
            'articles.current_status', 'articles.current_status_val', 'articles.current_status_rate','articles.floor_file_path', 'articles.primary_school_school','articles.building_id', 'articles.primary_school_school','articles.building_id', 
            'articles.bk', 'articles.key_text', 'articles.memo1', 'articles.price_closing', 'articles.close_date', 'articles.status',
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name',
            'art2.building_id as room_id', 'art2.room_num as room_number', 'art2.floor_file_path as room_floor_file_path', 'art2.price as room_price'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article-> leftJoin('articles as art2', 'articles.mansion_id', '=', 'art2.building_id');
        $article-> leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
        $article-> leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id');
        $article-> leftJoin('relation_price_histories', 'articles.building_id', '=', 'relation_price_histories.article_id');
        $article->where('articles.company_id', $user->company_id);
	$article->where('mst_cities.company_id', $user->company_id);

        // whereの設定
        $article = self::setSearchWhere($article, $condition, $recommend, $close, $count_flag);

        //$article -> where('articles.provisional', '=', 0);
        
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
//dd($article->toSql());
//dd($article->getBindings());
        // 画像有無
        if(isset($condition->image)) {
            if($condition->image == 1) {
                $article->whereNull('articles.floor_file_path');
            } else {
                $article->whereNotNull('articles.floor_file_path');
            }
        }
        
        if(isset($condition->order)) {
            $order = $condition->order;
            if($order == "recommend") {
                $article->orderBy('articles.recommend_num');
            } else if($order == "new") {
                $article->orderBy('articles.recommend');
            } else if($order == "station") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            } else if($order == "recommend") {
                $article->orderBy('articles.recommend');
            }
        }
        if(isset($condition->sort)) {
            if($condition->sort == "new") {
                $article->orderBy('articles.updated_at', 'DESC');
            } else if($condition->sort == "newprice") {
                // $article->orderBy('history.max');
            } else if($condition->sort == "inexpensive") {
                $article->orderBy('articles.price');
            } else if($condition->sort == "station") {
                $article->orderByRaw('LEAST(COALESCE(articles.main_traffic_time, 2147483647), COALESCE(articles.sub_traffic1_time, 2147483647), COALESCE(articles.sub_traffic2_time, 2147483647))');
            } else if($condition->sort == "age") {
                $article->orderBy('articles.age_year')->orderBy('articles.age_month');
            } else if($condition->sort == "land") {
                $article->orderBy('articles.land_area_val', 'DESC');
            } else if($condition->sort == "floor") {
                $article->orderBy('articles.total_area_val', 'DESC');
            } else if($condition->sort == "property") {
                $article->orderBy('articles.property');
            } else if($condition->sort == "address") {
                $article->orderBy('articles.address1')->orderBy('articles.address2')->orderBy('articles.address3')->orderBy('articles.address4');
            } else if($condition->sort == "line") {
                $article->orderBy('articles.main_traffic_line')->orderBy('articles.main_traffic_station');
            } else if($condition->sort == "vendor") {

            }
        }
        $article->groupBy('articles.building_id');

        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> get()->toArray();
            $result = count($result);
        } else {
            $result = $article -> get();
        }
        // dump($article->toSql());
        // dump($article->getBindings());
        return $result;
    }

    public static function getLotDataByCondition($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*', 
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article->where('articles.company_id', $user->company_id);

        // whereの設定
        $article -> where('articles.property', '=', 99);


        if (isset($condition->name) && !empty($condition['name'])) {
            $article -> where('articles.name', 'LIKE', '%'.$condition['search_name'].'%');
        }
        if (isset($condition->id) && !empty($condition['id'])) {
            $article -> where('articles.building_id', '=', $condition['id']);
        }
        if (isset($condition->address1) && !empty($condition['address1'])) {
            $article -> where('articles.address1', '=', $condition['address1']);
        }
        if (isset($condition->address2) && !empty($condition['address2'])) {
            $article -> where('articles.address2', '=', $condition['address2']);
        }
        if (isset($condition->address3) && !empty($condition['address3'])) {
            $article -> where('articles.address3', '=', $condition['address3']);
        }

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


        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> count();
        } else {
            $result = $article -> get();
        }
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getMansionDataByCondition($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*', 
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name'
            // 'art2.building_id as room_id', 'art2.room_num as room_number', 'art2.floor_file_path as room_floor_file_path', 'art2.price as room_price'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article-> leftJoin('articles as art2', 'articles.building_id', '=', 'art2.mansion_id');
        $article-> leftJoin('relation_shareds', 'relation_shareds.article_id', '=', 'articles.building_id');
        $article-> where('articles.company_id', $user->company_id);
        // whereの設定
        $article = self::setSearchWhere($article, $condition, $recommend, $close, $count_flag, 1);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });

        if(isset($condition->sort)) {
            if($condition->sort == "osusume") {
                $article->orderBy('articles.recommend', 'DESC');
            } else if($condition->sort == "new") {
                $article->orderBy('articles.updated_at', 'DESC');
            } else if($condition->sort == "slope_low") {
                $article->orderBy('articles.original3', 'ASC');
            } else if($condition->sort == "slope_hight") {
                $article->orderBy('articles.original3', 'DESC');
            } else if($condition->sort == "price_low") {
                $article->orderBy('articles.price', 'ASC');
            } else if($condition->sort == "price_hight") {
                $article->orderBy('articles.price', 'DESC');
            } else if($condition->sort == "age_hight") {
                $article->orderBy('articles.age_year', 'ASC')->orderBy('articles.age_month');
            } else if($condition->sort == "age_low") {
                $article->orderBy('articles.age_year', 'DESC')->orderBy('articles.age_month');
            } else if($condition->sort == "land_low") {
                $article->orderBy('articles.land_area_val', 'ASC');
            } else if($condition->sort == "land_hight") {
                $article->orderBy('articles.land_area_val', 'DESC');
            } else if($condition->sort == "floor_low") {
                $article->orderBy('articles.total_area_val', 'ASC');
            } else if($condition->sort == "floor_hight") {
                $article->orderBy('articles.total_area_val', 'DESC');
            } else if($condition->sort == "reform") {
                $article->orderBy('articles.exterior', 'DESC');
            }
        }

        $article->groupBy('articles.building_id');

        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> get() -> count();
        } else {
            $result = $article -> get();
        }
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getDataByConditionFront($condition, $pager = null, $recommend = null, $close = null, $count_flag = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*', 
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name',
            'art2.building_id as room_id', 'art2.room_num as room_number', 'art2.floor_file_path as room_floor_file_path', 'art2.price as room_price'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        $article-> leftJoin('articles as art2', 'articles.building_id', '=', 'art2.mansion_id');
        // whereの設定
        $article = self::setWhere($article, $condition, $recommend, $close, $count_flag);

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

        if(isset($condition->sort)) {
            if($condition->sort == "osusume") {
                $article->orderBy('articles.recommend', 'DESC');
            } else if($condition->sort == "new") {
                $article->orderBy('articles.updated_at', 'DESC');
            } else if($condition->sort == "slope_low") {
                $article->orderBy('articles.original3', 'ASC');
            } else if($condition->sort == "slope_hight") {
                $article->orderBy('articles.original3', 'DESC');
            } else if($condition->sort == "price_low") {
                $article->orderBy('articles.price', 'ASC');
            } else if($condition->sort == "price_hight") {
                $article->orderBy('articles.price', 'DESC');
            } else if($condition->sort == "age_hight") {
                $article->orderBy('articles.age_year', 'ASC')->orderBy('articles.age_month');
            } else if($condition->sort == "age_low") {
                $article->orderBy('articles.age_year', 'DESC')->orderBy('articles.age_month');
            } else if($condition->sort == "land_low") {
                $article->orderBy('articles.land_area_val', 'ASC');
            } else if($condition->sort == "land_hight") {
                $article->orderBy('articles.land_area_val', 'DESC');
            } else if($condition->sort == "floor_low") {
                $article->orderBy('articles.total_area_val', 'ASC');
            } else if($condition->sort == "floor_hight") {
                $article->orderBy('articles.total_area_val', 'DESC');
            } else if($condition->sort == "reform") {
                $article->orderBy('articles.exterior', 'DESC');
            }
        }


        if($pager != null) {
            $result = $article ->paginate($pager);
        } elseif($count_flag != null) {
            $result = $article -> get() -> count();
        } else {
            $result = $article -> get();
        }
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getDataByRoom($id, $status = null) {
        $user = Auth::user();

        $article = Article::query();
        $article-> select('articles.*', 
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
            'mst_floor_types.name as floor_plan_name'
        );
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        if($status != null) {
            if($status == 1) {
                // 売出
                $article -> where('status', '!=', 4);
            } else {
                // 成約
                $article -> where('status', '=', 4);
            }
        }
        // whereの設定
        $article->where('mansion_id', '=', $id);
	$article->where('mst_cities.company_id', $user->company_id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->get();
        return $result;
    }

    public static function getMansionList($condition, $num=null) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.id as article_id', 'articles.building_id as building_id', 'articles.name as article_name', 'name_apartment', 'property', 'price', 'main_traffic_line', 'main_traffic_station', 'main_traffic_time',
                        'floor_file_path','sheet_file_path2', 'sheet_file_path3', 'sheet_file_type',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address', 'articles.created_at');
        $article-> 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');
        });
        $article->where('articles.company_id', $user->company_id);

        $properties = [6,7];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('articles.property', '=', $property);
            }
        });


        if (isset($condition->search_name) && !empty($condition['search_name'])) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.name', 'LIKE', '%'.$condition['search_name'].'%');
                $query->orWhere('articles.name_apartment', 'LIKE', '%'.$condition['search_name'].'%');
            });
        }
        if (isset($condition->search_id) && !empty($condition['search_id'])) {
            $article -> where('articles.building_id', '=', $condition['search_id']);
        }
        if (isset($condition->search_address1) && !empty($condition['search_address1'])) {
            $article -> where('articles.address1', '=', $condition['search_address1']);
        }
        if (isset($condition->search_address2) && !empty($condition['search_address2'])) {
            $article -> where('articles.address2', '=', $condition['search_address2']);
        }
        if (isset($condition->search_address3) && !empty($condition['search_address3'])) {
            $article -> where('articles.address3', '=', $condition['search_address3']);
        }
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });

        if($num == null) {
            $result = $article -> get();
        } else {
            $result = $article ->paginate($num);
        }
// dump($article->toSql());
// dump($article->getBindings());
        return $result;
    }

    public static function getRoomList($id) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.*',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        $article -> whereNull('property');
        $article -> where('status', '!=', 4);
        
        $article -> where('articles.mansion_id', '=', $id);
	$article->where("mst_cities.company_id", $user->company_id);
	$article -> where('articles.company_id', '=', $user->company_id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        // print($article->toSql());
        $result = $article -> get();

        return $result;
    }

    public static function getLotList($id) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.*',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address');
        $article-> 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');
        });
        $article -> where('status', '=', 1);
        
        $article -> where('articles.sale_no', '=', $id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        // print($article->toSql());
        $result = $article -> get();

        return $result;
    }

    public static function getArticleList($id, $status) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.*',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
        -> leftJoin('mst_cities', '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');
        });
        if($status != null) {
            if($status == 1) {
                // 売出
                $article -> where('status', '!=', 4);
            } else {
                // 成約
                $article -> where('status', '=', 4);
            }
        }
        $article -> where('articles.sale_no', '=', $id);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        // print($article->toSql());
        $result = $article -> get();

        return $result;
    }

    public static function getCountRoom($code) {
        $res = Article::where('mansion_id', '=', $code)->count();
        return $res;
    }

    public static function getCountByPrimarySchool($code, $cid) {
        $article = Article::query();
        $article->where('primary_school_school', '=', $code)->where('company_id', '=', $cid)->where('own_company', '=', 2);
        $article->where(function ($query) {
            $query->whereNull('del')
                ->orWhere('del', '0');
        });

        $statuses = [1,2,3,6];
        $article -> where(function($query) use ($statuses){
            foreach($statuses as $status){
                $query->orWhere('status', '=', $status);
            }
        });

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

    public static function getCountBySecondarySchool($code, $cid) {
        $article = Article::query();
        $article->where('secondary_school_school', '=', $code)->where('company_id', '=', $cid)->where('own_company', '=', 2);
        $article->where(function ($query) {
            $query->whereNull('del')
                ->orWhere('del', '0');
        });

        $statuses = [1,2,3,6];
        $article -> where(function($query) use ($statuses){
            foreach($statuses as $status){
                $query->orWhere('status', '=', $status);
            }
        });

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

    public static function getCountByStation($id, $cid) {
        $article = Article::query();
        $article->where('company_id', '=', $cid)->where('own_company', '=', 2);
        $article->where(function ($query) {
            $query->whereNull('del')
                ->orWhere('del', '0');
        });
        $article->where(function ($query) use ($id) {
            $query->where('main_traffic_station_id', '=', $id)
            ->orWhere('sub_traffic1_station_id', '=', $id)
            ->orWhere('sub_traffic2_station_id', '=', $id);
        });
        
        $statuses = [1,2,3,6];
        $article -> where(function($query) use ($statuses){
            foreach($statuses as $status){
                $query->orWhere('status', '=', $status);
            }
        });

        $properties = [1,2,3,4,5,null];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('property', '=', $property);
            }
        });
        // dump($article->toSql());
        $res = $article->count();
        return $res;
    }


    public static function getDuplicateAritcleData($type, $data) {
        $user = Auth::user();

        $article = Article::query();

        $article -> select( 'articles.id', 'articles.building_id', 'rains_no', 'articles.name as article_name', 'articles.property', 'articles.status',
            'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.name_apartment',
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 'articles.address4 as address',
            'price', 'land_area_val', 'articles.floor_plan', 'mst_floor_types.name as floor_type_text',
            'articles.main_traffic_line', 'articles.main_traffic_station', 'articles.main_traffic_time',
            'articles.age_year', 'articles.age_month', 'articles.total_area_val',
            'articles.floor', 'articles.whereabouts',
            'vendors.name as vendor_name', 'vendors.vendor_charge as vendor_charge', 'vendors.vendor_tel1', 'vendors.vendor_fax',
            'mst_schools.name as school_name'
        );


        $article -> leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id')
                 -> leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.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');
                 })
                 -> leftJoin('mst_schools', 'articles.primary_school_school', '=', 'mst_schools.code')
                 -> leftJoin('mst_floor_types', 'articles.floor_plan_type', '=', 'mst_floor_types.code');
        // $article->where('articles.property', '!=', 99);
        // $article->whereNotNull('articles.property');
	$article->where("mst_cities.company_id", auth()->user()->company_id);
	$article->where("articles.company_id", auth()->user()->company_id);
	$article->where("vendors.company_id", auth()->user()->company_id);

        if($type == 1) {
            $properties = [1,2,3,4,5,null];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });
            $article->where('articles.address2', '=', $data->address2);
            if(empty($data->land_area_val)) {
                $article->whereNULL('articles.land_area_val');
            } else {
                $article->where('articles.land_area_val', '=', $data->land_area_val);
            }
        } elseif($type == 2) {
            $properties = [1,2,3,4,5,null];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });
            $article->where('articles.address2', '=', $data->address2);
            if(empty($data->total_area_val)) {
                $article->whereNULL('articles.total_area_val');
            } else {
                $article->where('articles.total_area_val', '=', $data->total_area_val);            
            }
        } elseif($type == 3) {
            // $properties = [1,2,3,4,5,null];
            // $article -> where(function($query) use ($properties){
            //     foreach($properties as $property){
            //         $query->orWhere('articles.property', '=', $property);
            //     }
            // });
            $article->where('articles.address2', '=', $data->address2);
            $article->where('articles.address3', '=', $data->address3);
            if(empty($data->property)) {
                $article->whereNULL('articles.property');
            } else {
                if($data->property == 6 || $data->property == 7) {
                    $article->whereNULL('articles.property');
                } else {
                    $article->where('articles.property', '=', $data->property);
                }
            }
        } elseif($type == 4) {
            $properties = [1,2,3,4,5,null];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });
            if(!empty($data->name_apartment)) {
                $article -> where(function($query) use ($data){
                    $query->orWhere('articles.name', 'LIKE', '%'.$data->name_apartment.'%');
                    $query->orWhere('articles.name_apartment', 'LIKE', '%'.$data->name_apartment.'%');
                });
            }
        } elseif($type == 5) {
            $article->where('articles.address2', '=', $data->address2);
            $article->where('articles.address3', '=', $data->address3);
            if(empty($data->property)) {
                $article->whereNULL('articles.property');
            } else {
                $article->where('articles.property', '=', $data->property);            
            }
        }
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        // if($user->company_id != null) {
        //     $article->where('company_id', '=', $user->company_id);
        // }
        $result = $article->orderBy('articles.building_id')->get();
// dump($result);
        return $result;
    }

    public static function getRecommendData() {
        $user = Auth::user();

        $article = Article::query();

        $article->select('articles.building_id', 'articles.rains_no as rains_id', 'articles.id as article_id', 'articles.company_id', 'occupied', 'revenue', 'status', 'company_manner', 'shop', 'articles.name as article_name', 'name_gouchi', 'property', 'property_sub', 'price', 'price_closing', 'close_date',
        'council', 'council_presence', 'council_cost', 'council_cost_unit','spring', 'spring_presence', 'spring_cost', 'spring_cost_unit', 
        'another_cost_name1', 'another_cost1', 'another_cost_unit1', 'another_cost_name2', 'another_cost2', 'another_cost_unit2',
        'main_traffic','main_traffic_line','main_traffic_line_id','main_traffic_station','main_traffic_station_id','main_traffic_time',
        'main_traffic_bus_time','main_traffic_bus','main_traffic_bus_walk',
        'sub_traffic1','sub_traffic1_line','sub_traffic1_line_id','sub_traffic1_station','sub_traffic1_station_id','sub_traffic1_time',
        'sub_traffic1_bus_time','sub_traffic1_bus','sub_traffic1_bus_walk',
        'sub_traffic2','sub_traffic2_line','sub_traffic2_line_id','sub_traffic2_station','sub_traffic2_station_id','sub_traffic2_time',
        'sub_traffic2_bus_time','sub_traffic2_bus','sub_traffic2_bus_walk', 'memo1', 'memo2',
        'primary_school_city', 'primary_school_school', 'primary_school_distance', 'primary_school2_city', 'primary_school2_school', 'primary_school2_distance', 'primary_school3_city', 'primary_school3_school', 'primary_school3_distance',
        'secondary_school_city', 'secondary_school_school', 'secondary_school_distance','secondary_school2_city', 'secondary_school2_school', 'secondary_school2_distance','secondary_school3_city', 'secondary_school3_school', 'secondary_school3_distance',
        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
        'zip', 'articles.address1','articles.address2 as address2', 'articles.address3 as address3', 'articles.address4 as address',  
        'age_year', 'age_month', 'land_right', 'land_area', 'land_area_val', 'floor_plan', 'mst_floor_types.name as floor_plan_name', 'land_condition',
        'ground','building_rate', 'volume_rate', 'road_burden', 'road_burden_area', 'road_numerator', 'road_denominator', 'easement', 'easement_area',
        'land_kind1', 'land_direction1', 'road_width1', 'frontage1', 
        'land_kind2', 'land_direction2', 'road_width2', 'frontage2', 
        'land_kind3', 'land_direction3', 'road_width3', 'frontage3', 
        'land_condition1', 'land_condition_area1', 'land_condition_unit1', 
        'land_condition2', 'land_condition_area2', 'land_condition_unit2', 
        'land_condition3', 'land_condition_area3', 
        'building_condition1', 'building_condition_area1',
        'building_condition2', 'building_condition_area2',
        'building_condition3', 'building_condition_area3',
        'building_condition4', 'building_condition_select',
        'exterior', 'exterior_year', 'exterior_month', 'exterior_wall', 'exterior_roof', 'exterior_other', 'exterior_text', 
        'interior', 'interior_year', 'interior_month', 'interior_kitchen', 'interior_bathroom', 
        'interior_toilet', 'interior_wall', 'interior_floor', 'interior_all', 'interior_other', 'interior_text', 
        'classfication', 'use_method', 'use_method_text', 'classfication', 'trading_classfication', 
        'business_status', 'investment_status', 'investment_performance', 'investment_performance_unit', 'investment_interest',
        'water_supply', 'sewerage', 'gas', 'garage', 'set', 'set_area', 'use_area', 'floor', 'whereabouts', 'underground', 'maisonette', 'maisonette_from', 'maisonette_to',
        'land_use', 'land_use2', 'city_plan', 'city_plan_reason', 'section', 'develop_num', 'other_reason', 'other_comment', 'rebuilding', 'total_area', 
        'total_area_val', 'underground_area', 'underground_area_val', 'garage_area', 'garage_area_val', 'underground_garage_area', 
        'underground_garage_area_val', 'residence_area', 'residence_area_val', 'floor_plan_type', 'completed_year', 'completed_month', 
        'completed_contract_month', 'move_in', 'move_in_year', 'move_in_month', 'move_in_contract_month', 'current_status', 'articles.current_status_val', 'articles.current_status_rate', 
        'building_construction', 'building_construction_sub', 'ground_unit', 'underground_unit', 'parking', 
        'parking_num', 'architecture_no', 'classfication', 'use_method', 'use_method_text', 'own_company', 'suumo', 'homes', 'athome', 
        'catchcopy', 'point', 'comment', 'hp_charge', 'floor_file_path', 'floor_file_type', 'floor_file_disp', 'sheet_file_path2', 'sheet_file_path3', 'sheet_file_type', 'event_category', 
        'event_schedule', 'from_event', 'to_event', 'from_hour', 'from_minute', 'to_hour', 'to_minute', 'reservation', 'event_comment',        
        'articles.updated_at as update');

        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        // if($user->company_id != null) {
        //     $article->where('company_id', '=', $user->company_id);
        // }
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $result = $article->where('articles.recommend', '=', 1)->get();
// print_r($result);
        return $result;
    }

    public static function getLotInfo($cid, $id) {
        $result = [];
        $article = Article::query();
        $result = $article->where('company_id', '=', $cid)->where('building_id', '=', $id)->first();
        return $result;
    }

    public static function getOpenHouseData($cid) {
        $user = Auth::user();

        $article = Article::query();

        $article->select('articles.building_id', 'articles.rains_no as rains_id', 'articles.id as article_id', 'articles.company_id', 'occupied', 'revenue', 'status', 'company_manner', 'shop', 'articles.name as article_name', 'name_gouchi', 'property', 'property_sub', 'price', 'price_closing', 'close_date',
        'council', 'council_presence', 'council_cost', 'council_cost_unit','spring', 'spring_presence', 'spring_cost', 'spring_cost_unit', 
        'another_cost_name1', 'another_cost1', 'another_cost_unit1', 'another_cost_name2', 'another_cost2', 'another_cost_unit2',
        'main_traffic','main_traffic_line','main_traffic_line_id','main_traffic_station','main_traffic_station_id','main_traffic_time',
        'main_traffic_bus_time','main_traffic_bus','main_traffic_bus_walk',
        'sub_traffic1','sub_traffic1_line','sub_traffic1_line_id','sub_traffic1_station','sub_traffic1_station_id','sub_traffic1_time',
        'sub_traffic1_bus_time','sub_traffic1_bus','sub_traffic1_bus_walk',
        'sub_traffic2','sub_traffic2_line','sub_traffic2_line_id','sub_traffic2_station','sub_traffic2_station_id','sub_traffic2_time',
        'sub_traffic2_bus_time','sub_traffic2_bus','sub_traffic2_bus_walk', 'memo1', 'memo2',
        'primary_school_city', 'primary_school_school', 'primary_school_distance', 'primary_school2_city', 'primary_school2_school', 'primary_school2_distance', 'primary_school3_city', 'primary_school3_school', 'primary_school3_distance',
        'secondary_school_city', 'secondary_school_school', 'secondary_school_distance','secondary_school2_city', 'secondary_school2_school', 'secondary_school2_distance','secondary_school3_city', 'secondary_school3_school', 'secondary_school3_distance',
        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
        'zip', 'articles.address1','articles.address2 as address2', 'articles.address3 as address3', 'articles.address4 as address',  
        'age_year', 'age_month', 'land_area', 'land_area_val', 'floor_plan', 'mst_floor_types.name as floor_plan_name', 'land_condition',
        'ground','building_rate', 'volume_rate', 'road_burden', 'road_burden_area', 'road_numerator', 'road_denominator', 'easement', 'easement_area',
        'land_kind1', 'land_direction1', 'road_width1', 'frontage1', 
        'land_kind2', 'land_direction2', 'road_width2', 'frontage2', 
        'land_kind3', 'land_direction3', 'road_width3', 'frontage3', 
        'land_condition1', 'land_condition_area1', 'land_condition_unit1', 
        'land_condition2', 'land_condition_area2', 'land_condition_unit2', 
        'land_condition3', 'land_condition_area3', 
        'building_condition1', 'building_condition_area1',
        'building_condition2', 'building_condition_area2',
        'building_condition3', 'building_condition_area3',
        'building_condition4', 'building_condition_select',
        'exterior', 'exterior_year', 'exterior_month', 'exterior_wall', 'exterior_roof', 'exterior_other', 'exterior_text', 
        'interior', 'interior_year', 'interior_month', 'interior_kitchen', 'interior_bathroom', 
        'interior_toilet', 'interior_wall', 'interior_floor', 'interior_all', 'interior_other', 'interior_text', 
        'classfication', 'use_method', 'use_method_text', 'classfication', 'trading_classfication', 
        'business_status', 'investment_status', 'investment_performance', 'investment_performance_unit', 'investment_interest',
        'water_supply', 'sewerage', 'gas', 'garage', 'set', 'set_area', 'use_area', 'floor', 'whereabouts', 'underground', 'maisonette', 'maisonette_from', 'maisonette_to',
        'land_use', 'land_use2', 'city_plan', 'city_plan_reason', 'section', 'develop_num', 'other_reason', 'other_comment', 'rebuilding', 'total_area', 
        'total_area_val', 'underground_area', 'underground_area_val', 'garage_area', 'garage_area_val', 'underground_garage_area', 
        'underground_garage_area_val', 'residence_area', 'residence_area_val', 'floor_plan_type', 'completed_year', 'completed_month', 
        'completed_contract_month', 'move_in', 'move_in_year', 'move_in_month', 'move_in_contract_month', 'current_status', 'articles.current_status_val', 'articles.current_status_rate', 
        'building_construction', 'building_construction_sub', 'ground_unit', 'underground_unit', 'parking', 
        'parking_num', 'architecture_no', 'classfication', 'use_method', 'use_method_text', 'own_company', 'suumo', 'homes', 'athome', 
        'catchcopy', 'point', 'comment', 'hp_charge', 'floor_file_path', 'floor_file_type', 'floor_file_disp', 'sheet_file_path2', 'sheet_file_path3', 'sheet_file_type','event_category', 
        'event_schedule', 'event_day', 'from_event', 'to_event', 'from_hour', 'from_minute', 'to_hour', 'to_minute', 'reservation', 'event_comment',        
        'articles.updated_at');

        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article-> leftJoin('mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
        // if($user->company_id != null) {
        //     $article->where('company_id', '=', $user->company_id);
        // }
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $article->where('articles.own_company', '=', 2);
        $article->where('articles.event_category', '!=', 0)->where('articles.company_id', '=', $cid);

        $result = $article->get();
// print_r($result);
        return $result;
    }


    public static function getProvisionalData($cid, $num, $store=null) {
        $article = Article::query();
        $article -> select('articles.id', 'articles.building_id', 'articles.mansion_id', 'articles.room_num','rains_no', 'articles.name as article_name', 'articles.property', 'articles.property_sub',
            'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.name_apartment',
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 'articles.address4 as address',
            'price', 'land_area_val', 'articles.floor_plan', 'mst_floor_types.name as floor_type_text',
            'articles.main_traffic_line', 'articles.main_traffic_station', 'articles.main_traffic_time',
            'articles.age_year', 'articles.age_month', 'articles.total_area_val',
            'articles.floor', 'articles.whereabouts', 'articles.memo1', 'articles.memo2', 'articles.created_at',
            'vendors.id as vendor_id', 'vendors.name as vendor_name', 'relation_vendor_articles.charge as vendor_charge', 'vendors.vendor_tel1', 'vendors.vendor_fax'
        );

        $article -> leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id')
                 -> leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id')
                 -> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 })
                 -> leftJoin('mst_floor_types', 'articles.floor_plan_type', '=', 'mst_floor_types.code');
        $article->where('articles.company_id', '=', $cid);
        if(!is_null($store)) {
            $article->where('shop', '=', $store);
        }

        $article->where('provisional', '=', 1);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $properties = [1,2,3,4,5,null];
        $article -> where(function($query) use ($properties){
            foreach($properties as $property){
                $query->orWhere('articles.property', '=', $property);
            }
        });
        $result = $article->orderBy('id')->paginate($num);
        // dump($article->toSql());
        return $result;
    }

    public static function getDetailRecommendDispData($id, $property, $city_id, $num) {
        $user = Auth::user();

        $article = Article::query();
        $article->select('articles.building_id as article_id', 'articles.name as article_name', 'property', 'price', 'main_traffic_line', 'main_traffic_station', 'main_traffic_time',
                        'floor_file_path','sheet_file_path2', 'sheet_file_path3', 'sheet_file_type',
                        'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 
                        'articles.address4 as address', 'articles.created_at');
        $article-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
                 -> leftJoin('mst_cities', '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');
                 });
        $article->where('building_id', '!=', $id);
        $article->where('property', '=', $property);
        $article->where('address2', '=', $city_id);
        $article->where('own_company', '=', 2);
        $article->where(function ($query) {
            $query->whereNull('articles.del')
                ->orWhere('articles.del', '0');
        });
        $article->inRandomOrder();
        $result = $article->limit($num)->get();

        return $result;
    }

    public static function setWhere($article, $condition, $recommend, $close, $count_flag, $build_flag=null, $front = null) {
        if (isset($condition->keyword)) {
            $keyword = $condition->keyword;
            $article -> where(function($query) use ($keyword){
                $query->orWhere('articles.name', 'LIKE', '%'.$keyword.'%');
                $query->orWhere('articles.name_apartment', 'LIKE', '%'.$keyword.'%');
                $query->orWhere('mst_prefectures.name', 'LIKE', '%'.$keyword.'%');
                $query->orWhere('mst_cities.name', 'LIKE', '%'.$keyword.'%');
                $query->orWhere('mst_towns.name', 'LIKE', '%'.$keyword.'%');
                $query->orWhere('articles.address4', 'LIKE', '%'.$keyword.'%');
            });
        }

        if (isset($condition->pp)) {
            if($condition->pp != 3) {
                $properties = MstPropertyType::getDataByParentId($condition->pp);
                $article -> where(function($query) use ($properties){
                    foreach($properties as $property){
                        $query->orWhere('articles.property', '=', $property['code']);
                    }
                });
                if (isset($condition->spp)) {
                    $article -> where('articles.property_sub', '=', $condition->spp);
                }
            } else {
                if($build_flag) {
                    $properties = MstPropertyType::getDataByParentId($condition->pp);
                    $article -> where(function($query) use ($properties){
                        foreach($properties as $property){
                            $query->orWhere('articles.property', '=', $property['code']);
                        }
                    });
                } else {
                    $article->whereNull('articles.property');
                }
            }
        } elseif (isset($condition->pm)) {
            $search_target = [];
            foreach($condition->pm as $row) {
                if($row == "re") {
                    continue;
                }
                $pinfo = explode('_', $row);
                $properties = MstPropertyType::getDataByParentId($pinfo[0]);
                if(isset($pinfo[1])) {
                    $search_target[] = [
                        'property' => $properties,
                        'sub_propety' => $pinfo[1]
                    ];
                } else {
                    $search_target[] = [
                        'property' => $properties,
                    ];
                }
            }

            $article -> where(function($query) use ($search_target){
                foreach($search_target as $property){
                    $query -> orWhere(function($query_inner) use ($property){
                        foreach($property['property'] as $pro) {
                            $query_inner->orWhere('articles.property', '=', $pro['code']);
                        }
                        
                        if(isset($property['sub_propety'])) {
                            $query_inner -> where('articles.property_sub', '=', $property['sub_propety']);
                        }
                    });
                }
            });
            
        } elseif (isset($condition->property)) {
            // 種別
            if($condition->property == 41) {
                $article -> where('articles.property', '=', 4);
                $article -> where('articles.property_sub', '=', 1);
            } elseif($condition->property == 42) {
                $article -> where('articles.property', '=', 4);
                $article -> where('articles.property_sub', '=', 2);
            } elseif($condition->property == 61) {
                $article -> whereNull('articles.property');
            } else {
                if(is_array($condition->property)) {
                    $properties = $condition->property;
                    $article -> where(function($query) use ($properties){
                        foreach($properties as $property){
                            $query->orWhere('articles.property', '=', $property);
                        }
                    });
                } else {
                    $article -> where('articles.property', '=', $condition->property);
                }
            }
        } else {
            $properties = [1,2,3,4,5,null];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });
        }

        // エリア
        if (isset($condition->area)) {
            $area_list = explode(',', $condition->area);

            $article -> where(function($query) use ($area_list){
                foreach($area_list as $area){
                    preg_match("@([0-9]{2})([0-9]{3})@", $area, $address);
                    if($address[2] != 999) {
                        $query -> orWhere(function($query_inner) use ($area, $address){
                            $query_inner -> where('articles.address1', '=', $address[1]);
                            $query_inner -> where('articles.address2', '=', $address[2]);
                        });
                    } else {
                        $query -> orWhere(function($query_inner) use ($area, $address){
                            $query_inner -> where('articles.address1', '=', 14);
                            $query_inner -> where('articles.address2', '!=', 111);
                            $query_inner -> where('articles.address2', '!=', 115);
                            $query_inner -> where('articles.address2', '!=', 107);
                            $query_inner -> where('articles.address2', '!=', 108);
                            $query_inner -> where('articles.address2', '!=', 105);
                        });
                    }
                    
                }
            });
            
        }

        // 駅
        if (isset($condition->stc)){
            if(is_array($condition->stc)) {
                $station_list = $condition->stc;
            } else {
                $station_list = explode(',', $condition->stc);
            }
            
            $article -> where(function($query) use ($station_list){
                foreach($station_list as $station_id){
                    $query->orWhere('articles.main_traffic_station_id', '=', $station_id);
                    $query->orWhere('articles.sub_traffic1_station_id', '=', $station_id);
                    $query->orWhere('articles.sub_traffic2_station_id', '=', $station_id);
                }
            });
        }
        
        // 学校区
        if (isset($condition->scc)){
            $article -> where(function($query) use ($condition){
                $query -> orWhere('articles.primary_school_school', '=', $condition->scc)
                       -> orWhere('articles.secondary_school_school', '=', $condition->scc)
                       -> orWhere('articles.primary_school2_school', '=', $condition->scc)
                       -> orWhere('articles.secondary_school2_school', '=', $condition->scc);
            });
        }

        if(isset($condition->primary_school_time)) {
            $article -> where(function($query) use ($condition){
                $query -> orWhere('articles.primary_school_distance', '<=', $condition->primary_school_time * 80)
                         -> orWhere('articles.primary_school2_distance', '<=', $condition->primary_school_time * 80);
            });
            // $article -> where('articles.primary_school_distance', '<=', $condition->primary_school_time * 80);
        }
        if(isset($condition->secondary_school_time)) {
            $article -> where(function($query) use ($condition){
                $query -> orWhere('articles.secondary_school_distance', '<=', $condition->secondary_school_time * 80)
                         -> orWhere('articles.secondary_school2_distance', '<=', $condition->secondary_school_time * 80);
            });
            // $article -> where('articles.secondary_school_distance', '<=', $condition->secondary_school_time * 80);
        }
        
        if (isset($condition->sale_no)) {
            $article -> where('articles.sale_no', '=', $condition->sale_no);
        }
        
        if (isset($condition->revenue)) {
            $article -> where('articles.revenue', '=', $condition->revenue);
        }
        // 所在地（都道府県）
        if (isset($condition->address1)) {
            $article -> where('articles.address1', '=', $condition->address1);
        }
        // 所在地（市区郡）
        if (isset($condition->address2)) {
            $article -> where('articles.address2', '=', $condition->address2);
        }
        // 所在地（町村）
        if (isset($condition->address3)) {
            $article -> where('articles.address3', '=', $condition->address3);
        }
        // 所在地（その他）
        if (isset($condition->address4)) {
            $article -> where('articles.address4', '=', $condition->address4);
        }
        
        // 物件ID
        if (isset($condition->id)) {
            $article -> where('articles.building_id', '=', $condition->id);
        }
        if (isset($condition->building_id)) {
            $article -> where('articles.building_id', '=', $condition->id);
        }
        // 価格（下限）
        if (isset($condition->price_from)) {
            $article -> where('articles.price', '>=', $condition->price_from);
        }
        // 価格（上限）
        if (isset($condition->price_to)) {
            $article -> where('articles.price', '<=', $condition->price_to);
        }
        // 沿線（路線）
        if (isset($condition->access1)) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.main_traffic_line_id', '=', $condition->access1)
                      ->orWhere('articles.sub_traffic1_line_id', '=', $condition->access1)
                      ->orWhere('articles.sub_traffic2_line_id', '=', $condition->access1);
            });
        }
        // 戦線（駅）
        if (isset($condition->access2)) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.main_traffic_station_id', '=', $condition->access2)
                      ->orWhere('articles.sub_traffic1_station_id', '=', $condition->access2)
                      ->orWhere('articles.sub_traffic2_station_id', '=', $condition->access2);
            });
        }

        // 沿線（徒歩）
        if (isset($condition->walk)) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.main_traffic_time', '<=', $condition->walk)
                      ->orWhere('articles.sub_traffic1_time', '<=', $condition->walk)
                      ->orWhere('articles.sub_traffic2_time', '<=', $condition->walk);
            });
        }

        // 学校区（小学校）
        if (isset($condition->primary_school)) {
            // $article->where('articles.primary_school_school', '=', $condition->primary_school);
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.primary_school_school', '=', $condition->primary_school)
                      ->orWhere('articles.primary_school2_school', '=', $condition->primary_school);
            });
        }
        // 学校区（中学校）
        if (isset($condition->secondary_school)) {
            // $article->where('articles.secondary_school_school', '=', $condition->secondary_school);
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.secondary_school_school', '=', $condition->secondary_school)
                      ->orWhere('articles.secondary_school2_school', '=', $condition->secondary_school);
            });
        }

        // 土地面積（下限）
        if (isset($condition->land_from)) {
            $article -> where('articles.land_area_val', '>=', $condition->land_from);
        }
        // 土地面積（上限）
        if (isset($condition->land_to)) {
            $article -> where('articles.land_area_val', '<=', $condition->land_to);
        }

        // 延べ床面積(下限)
        if (isset($condition->floor_area_from)) {
            $article -> where('articles.total_area_val', '>=', $condition->floor_area_from);
        }
        // 延べ床面積（上限）
        if (isset($condition->floor_area_to)) {
            $article -> where('articles.total_area_val', '<=', $condition->floor_area_to);
        }
        // 間取り
        if (isset($condition->floor_plan)) {
            $article -> where('articles.floor_plan', '=', $condition->floor_plan);
        }
        // 物件名
        if (isset($condition->name)) {
            $article -> where('articles.name', 'LIKE', '%'.$condition->name.'%');
        }
        if (isset($condition->name_apartment)) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.name', 'LIKE', '%'.$condition->name_apartment.'%');
                $query->orWhere('articles.name_apartment', 'LIKE', '%'.$condition->name_apartment.'%');
            });
        }

        // 物件登録日
        if (isset($condition->registed_from)) {
            $article -> where('articles.created_at', '>=', $condition->registed_from);
        }
        // 物件登録日
        if (isset($condition->registed_to)) {
            $article -> where('articles.created_at', '<=', $condition->registed_to);
        }

        // 物件確認日
        if (isset($condition->checked_from)) {
            $article -> where('relation_vendor_articles.article_conf_day', '<=', $condition->checked_from);
        }

        // 物件確認日
        if (isset($condition->checked_to)) {
            $article -> where('relation_vendor_articles.article_conf_day', '>=', $condition->checked_to);
        }

        // 価格変更日
        if (isset($condition->changed_price_from)) {
            $article -> where('relation_price_histories.regist_date', '<=', $condition->changed_price_from);
        }
        
        // 価格変更日
        if (isset($condition->changed_price_to)) {
            $article -> where('relation_price_histories.regist_date', '<=', $condition->changed_price_to);
        }

        
        // 広告確認日
        if (isset($condition->ad_checked_from)) {
            $article -> where('relation_vendor_articles.ad_conf_day', '<=', $condition->ad_checked_from);
        }
        // 広告確認日
        if (isset($condition->ad_checked_to)) {
            $article -> where('relation_vendor_articles.ad_conf_day', '>=', $condition->ad_checked_to);
        }
        if (isset($condition->flyer)) {
            $article -> where('relation_vendor_articles.flyer', '=', $condition->flyer);
        }
        if (isset($condition->freepaper)) {
            $article -> where('relation_vendor_articles.freepaper', '=', $condition->freepaper);
        }
        if (isset($condition->house_hp)) {
            $article -> where('relation_vendor_articles.house_hp', '=', $condition->house_hp);
        }
        if (isset($condition->portal)) {
            $article -> where('relation_vendor_articles.portal', '=', $condition->portal);
        }
        if (isset($condition->signboard)) {
            $article -> where('relation_vendor_articles.signboard', '=', $condition->signboard);
        }

        if (isset($condition->own_company)) {
            $article -> where('articles.own_company', '=', $condition->own_company);
        }
        if (isset($condition->suumo)) {
            $article -> where('articles.suumo', '=', $condition->suumo);
        }
        if (isset($condition->homes)) {
            $article -> where('articles.homes', '=', $condition->homes);
        }
        if (isset($condition->athome)) {
            $article -> where('articles.athome', '=', $condition->athome);
        }
        
        
        // 業者名
        if (isset($condition->vender_name)) {
            $article -> where('vendors.name', 'like', '%'.$condition->vender_name.'%');
        }

        if (isset($condition->vender_kind)) {
            $article -> where('relation_vendor_articles.manner', '=', $condition->vender_kind);
        }

        if (isset($condition->vender_tel)) {
            $article -> where('vendors.vendor_tel1', 'like', '%'.$condition->vender_tel.'%');
        }

        

        

    

        
        if(isset($condition->years_from) && isset($condition->years_to)) {
            $year = date('Y');
            $year_from = $year - $condition->years_from;
            $year = date('Y');
            $year_to = $year - $condition->years_to;
            if($year_from > $year_to) {
                $article -> whereBetween('articles.age_year', [$year_to, $year_from]);
            } else {
                $article -> whereBetween('articles.age_year', [$year_from, $year_to]);
            }
            

        } elseif (isset($condition->years_from)) {
            // 築年数（下限)
            $year = date('Y');
            $year_from = $year - $condition->years_from;
            $article -> where('articles.age_year', '>=', $year_from);
        } elseif (isset($condition->years_to)) {
            // 築年数（上限）
            $year = date('Y');
            $year_to = $year - $condition->years_to;
            $article -> where('articles.age_year', '>=', $year_to);
        }

        if(isset($condition->year_month_from) && isset($condition->year_month_to)) {
            $from = str_replace('/', '', $condition->year_month_from);
            $to = str_replace('/', '', $condition->year_month_to);

            if(is_numeric($from) && is_numeric($to)) {
                $from_list = explode('/', $condition->year_month_from);
                $from = $from_list[0].sprintf('%02d', $from_list[1]);
                $to_list = explode('/', $condition->year_month_to);
                $to = $to_list[0].sprintf('%02d', $to_list[1]);
    
                if($from > $to) {
                    $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\')) BETWEEN '.$to.' AND '.$from);
                } else {
                    $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\')) BETWEEN '.$from.' AND '.$to);
                }
            }
            

        } elseif (isset($condition->year_month_from)) {
            // 築年月（下限）
            $from = str_replace('/', '', $condition->year_month_from);
            
            if(is_numeric($from)) {
                $from_list = explode('/', $condition->year_month_from);
                $from = $from_list[0].sprintf('%02d', $from_list[1]);
                $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\'))>='.$from);
            }
        } elseif (isset($condition->year_month_to)) {
            // 築年月（上限）
            $to = str_replace('/', '', $condition->year_month_to);
            if(is_numeric($to)) {
                $to_list = explode('/', $condition->year_month_to);
                $to = $to_list[0].sprintf('%02d', $to_list[1]);
                $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\'))<='.$to);
            }
        }


        // 構造
        if (isset($condition->construction)) {
            $article -> where('articles.construction', '=', $condition->construction);
        }
        

        //駐車場
        if (isset($condition->parking)) {
            $article -> where('articles.parking', '=', $condition->parking);
        }


        // 現況
        if (isset($condition->current_status)) {
            $article -> where('articles.current_status', '=', $condition->current_status);
        }


        // 用途地域
        if (isset($condition->use_area)) {
            $article -> where('articles.use_area', '=', $condition->use_area);
        }

        // 階建（下限）
        if (isset($condition->floor_from)) {
            $article -> where('articles.floor', '>=', $condition->floor_from);
        }
        // 階建（上限）
        if (isset($condition->floor_to)) {
            $article -> where('articles.floor', '<=', $condition->floor_to);
        }

        // 建築条件
        if (isset($condition->land_condition)) {
            foreach($condition->land_condition as $row) {
                $article -> where('articles.land_condition', '=', $row);
            }   
        }

        if(isset($condition->is_pet)) {
            if($condition->is_pet == 1) {
                $article -> where('art2.pet', '=', 1);
            } else {
                $article -> where('art2.pet', '=', 2);
            }
        }
        if(isset($condition->pet_num)) {
            $article -> where('art2.pet_count', '=', $condition->pet_num);
        }

        // こだわり検索
        if(isset($condition->commitment)) {
            // 新着物件
            if($condition->commitment == "new") {
                $date = date("Y-m-d",strtotime("-".config('const.NEW')." day"));
                $article -> where('articles.updated_at', '>=', $date);
            }

            // プライスダウン
            if($condition->commitment == "pricedown") {
                $history = RelationPriceHistory::getPriceDownId();
                foreach($history as $row) {
                    $article -> orWhere('articles.id', '=', $row);
                }
            }

            // オープンハウス
            if($condition->commitment == "openhouse") {
                $article -> where('articles.event_category', '!=', 0);
            }

            // 自社プロデュース
            if($condition->commitment == "produce") {
                $article -> where('articles.original1', '=', 1);
            }

            // 角地
            if($condition->commitment == "corner") {
                $article -> where('articles.original2', '=', 1);
            }

            // 高低差0
            if($condition->commitment == "flat") {
                $article -> where('articles.original3', '=', 0);
            }

            // LDK15畳以上
            if($condition->commitment == "ldk") {
                $article -> where('articles.original4', '=', 1);
            }
            
            // バス停1分以内
            if($condition->commitment == "bus") {
                $article->where('articles.main_traffic_bus_walk', '<=', 1);
            }

            // 駅徒歩5分以内
            if($condition->commitment == "stwalk") {
                $article->where('articles.main_traffic_time', '<=', 5);
            }

            // 小学校10分以内
            if($condition->commitment == "scwalk") {
                // $article->where('articles.primary_school_distance', '<=', 800);
                $article -> where(function($query) use ($condition){
                    $query->orWhere('articles.primary_school_distance', '<=', 800)
                          ->orWhere('articles.primary_school2_distance', '<=', 800);
                });
            }

            // ペット飼育可
            if($condition->commitment == "pet") {
                $article -> where('articles.pet', '=', 2);
            }

            // 管理費・修繕費
            if($condition->commitment == "fee") {
                $article -> where(function($query) use ($condition){
                    $query->orWhere('articles.repair_cost', '<=', 20000)
                          ->orWhere('articles.management_cost', '<=', 20000);
                });
            }

            // エレベーターあり
            if($condition->commitment == "elevator") {

            }

            // 即引き渡し可
            if($condition->commitment == "entry") {
                $article -> where('articles.move_in', '=', 1);
            }

            // リフォーム・リノベーション
            if($condition->commitment == "reform") {

            }

            // ２台以上
            if($condition->commitment == "parking") {
                $article -> where('articles.parking_num', '>=', 2);
            }

            // ２階建
            if($condition->commitment == "floors") {
                $article -> where('articles.floor', '=', 2);
            }

            // 建築条件無し
            if($condition->commitment == "conditions") {
                $article -> where('articles.land_condition', '=', 2);
            }

            // 建築条件無し
            if($condition->commitment == "recommended") {
                $article -> where('articles.recommend', '=', 1);
            }


        }

        // 高低差
        if(isset($condition->hight_level)) {
            $article -> where('articles.original3', '=', $condition->hight_level);
        }
                    
        // 条件外し
        if (isset($condition->remove_condition)) {
            $article -> where('articles.land_condition_not', '=', 1);
        }
        // 接道方向
        if (isset($condition->land_kind)) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.land_direction1', '=', $condition->land_kind);
                $query->orWhere('articles.land_direction2', '=', $condition->land_kind);
                $query->orWhere('articles.land_direction3', '=', $condition->land_kind);
            });
        }

        // 取り扱い店舗
        if (isset($condition->shop)) {
            $article -> where('articles.shop', '=', $condition->shop);
        } elseif (isset($condition->search_shop)) {
            $article -> where('articles.shop', '=', $condition->search_shop);
        }

        // バルコニー（向き）
        if (isset($condition->balcony_direction)) {
            $article -> where('articles.terrace_direction', '=', $condition->balcony_direction);
        }
        // バルコニー（広さ）
        if (isset($condition->balcony_area)) {
            $article -> where('articles.terrace_area_val', '=>', $condition->balcony_area);
        }

        // 事業主
        if (isset($condition->management_company)) {
            $article -> where('art2.management_company', 'LIKE', '%'.$condition->management_company.'%');
        }

        // 施工
        if (isset($condition->construction_name)) {
            $article -> where('art2.construction_company', 'LIKE', '%'.$condition->construction_name.'%');
        }

        // 管理画面
        if($recommend == 1) {
            $article->where('articles.recommend', '=', 1);
        }

        if($recommend == 2) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.recommend', '=', 0)
                      ->orWhereNull('articles.recommend');
            });
        }

        // フロント表示・非表示
        if (isset($condition->disp)) {
            $article -> where('articles.own_company', '=', 2);
        }

        if(isset($condition->provisional)) {
            $article -> where('articles.provisional', '=', 0);
        }
        
        if(isset($condition->notcondition)) {
            switch($condition->notcondition) {
                case 'notHold':
                $article->where('articles.status', '!=', 4);
                $article->where('articles.status', '!=', 6);
                break;

                case 'notClosed':
                $article->where('articles.status', '!=', 4);
                break;

                case 'notEtc':
                $article->where('articles.status', '!=', 2);
                $article->where('articles.status', '!=', 3);
                $article->where('articles.status', '!=', 4);
                $article->where('articles.status', '!=', 5);
                $article->where('articles.status', '!=', 6);
                break;

                default:
                break;

            }
        } elseif (isset($condition->close)) {
            if($condition->close == 1) {
                $article->where('articles.status', '=', 4);
            } else {
                $article->where('articles.status', '!=', 4);
            }
        } elseif($close != null) {
            if($close == 1) {
                $article -> where(function($query) use ($condition){
                    $query->where('articles.status', '=', 4)
                          ->orWhere('articles.status', '=', 5);
                });
            } else {
                $article -> where(function($query) use ($condition){
                    $query->where('articles.status', '!=', 4)
                          ->where('articles.status', '!=', 5);
                });
            }
        }
// dump($article->toSql());
        return $article;
    }




    public static function setSearchWhere($article, $condition, $recommend, $close, $count_flag, $build_flag=null, $front = null) {
        // 種別
        if (isset($condition->property)) {
            $article -> where(function($query) use ($article, $condition){
                $query->orwhere(function($query2) use ($article, $condition){
                    foreach($condition->property as $property) {
                        if($property != null) {
                            if($property == 41) {
                                $query2 -> orWhere(function($query3) use ($property){
                                        $query3->where('articles.property', '=', 4);
                                        $query3 -> where('articles.property_sub', '=', 1);
                                });
                            } elseif($property == 42) {
                                $query2 -> orWhere(function($query3) use ($property){
                                        $query3->where('articles.property', '=', 4);
                                        $query3 -> where('articles.property_sub', '=', 2);
                                });
                            } elseif($property == 61) {
                                $query2 -> orWhere(function($query3) use ($property){
                                        $query3->whereNull('articles.property');
                                        // $query3 -> where('art2.property_sub', '=', 1);
                                });
                            } elseif($property == 6) {
                                $query2 -> orWhere(function($query3) use ($property){
                                        $query3->orWhere('articles.property', '=', 6);
                                        $query3->orWhere('articles.property', '=', 7);
                                });
                            } else {
                                $query2 -> orWhere(function($query3) use ($property){
                                    $query3 -> orWhere('articles.property', '=', $property);
                                });
                            }
                        } else {
                            $properties = [1,2,3,4,5,null];
                            $article -> where(function($query) use ($properties){
                                foreach($properties as $property){
                                    $query->orWhere('articles.property', '=', $property);
                                }
                            });
                        }
                    }
                });
            });
        } else {
            $properties = [1,2,3,4,5,null];
            $article -> where(function($query) use ($properties){
                foreach($properties as $property){
                    $query->orWhere('articles.property', '=', $property);
                }
            });
        }

        if (isset($condition->revenue)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->revenue as $revenue) {
                    if($revenue != null) {
                        $query -> orWhere('articles.revenue', '=', $revenue);
                    }
                }
            });  
        }

        // 所在地（都道府県）
        if (isset($condition->address1)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->address1 as $address1) {
                    if($address1 != null) {
                        $query -> orWhere('articles.address1', '=', $address1);
                    }
                }
            });
        }
        // 所在地（市区郡）
        if (isset($condition->address2)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->address2 as $address2) {
                    if($address2 != null) {
                        $query -> orWhere('articles.address2', '=', $address2);
                    }
                }
            });
        }
        // 所在地（町村）
        if (isset($condition->address3)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->address3 as $address3) {
                    if($address3 != null) {
                        $query -> orWhere('articles.address3', '=', $address3);
                    }
                }
            });
        }
        // 所在地（その他）
        if (isset($condition->address4)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->address4 as $address4) {
                    if($address4 != null) {
                        $query -> orWhere('articles.address4', '=', $address4);
                    }
                }
            });
        }

        if (isset($condition->building_id)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->building_id as $building_id) {
                    if($building_id != null) {
                        $query -> orWhere('articles.building_id', '=', $building_id);
                    }
                }
            });
        }

        if(isset($condition->name_apartment)) {
            $article -> where(function($query) use ($condition){
                foreach($condition->name_apartment as $name_apartment) {
                    if($name_apartment != null) {
                        $query->orWhere('articles.name', 'LIKE', '%'.$name_apartment.'%');
                        $query->orWhere('articles.name_apartment', 'LIKE', '%'.$name_apartment.'%');
                    }
                }
            });
        }

        if (isset($condition->id)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->id as $id) {
                    if($id != null) {
                        $query -> orWhere('articles.mansion_id', '=', $id);
                    }
                }
            });
        }

        if (isset($condition->sale_id)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->sale_id as $sale_id) {
                    if($sale_id != null) {
                        $query -> orWhere('articles.sale_no', '=', $sale_id);
                    }
                }
            });
        }

        // 価格（下限）
        if (isset($condition->price_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->price_from as $price_from) {
                    if($price_from != null) {
                        $query -> orWhere('articles.price', '>=', $price_from);
                    }
                }
            });
        }
        // 価格（上限）
        if (isset($condition->price_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->price_to as $price_to) {
                    if($price_to != null) {
                        $query -> orWhere('articles.price', '<=', $price_to);
                    }
                }
            });
        }
        // 沿線（路線）
        if (isset($condition->access1)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->access1 as $access1) {
                    if($access1 != null) {
                        $query->orWhere('articles.main_traffic_line_id', '=', $access1)
                        ->orWhere('articles.sub_traffic1_line_id', '=', $access1)
                        ->orWhere('articles.sub_traffic2_line_id', '=', $access1);
                    }
                }
            });
        }
        // 戦線（駅）
        if (isset($condition->access2)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->access2 as $access2) {
                    if($access2 != null) {
                        $query->orWhere('articles.main_traffic_station_id', '=', $access2)
                        ->orWhere('articles.sub_traffic1_station_id', '=', $access2)
                        ->orWhere('articles.sub_traffic2_station_id', '=', $access2);
                    }
                }
            });
        }

        // 沿線（徒歩）
        if (isset($condition->walk)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->walk as $walk) {
                    if($walk != null) {
                        $query->orWhere('articles.main_traffic_time', '=', $walk)
                        ->orWhere('articles.sub_traffic1_time', '=', $walk)
                        ->orWhere('articles.sub_traffic2_time', '=', $walk);
                    }
                }
            });
        }

        // 学校区（小学校）
        if (isset($condition->primary_school)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->primary_school as $primary_school) {
                    if($primary_school != null) {
                        $query->orWhere('articles.primary_school_school', '=', $primary_school)
                        ->orWhere('articles.primary_school2_school', '=', $primary_school);
                    }
                }
            });
        }

        // 学校区（中学校）
        if (isset($condition->secondary_school)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->secondary_school as $secondary_school) {
                    if($secondary_school != null) {
                        $query->orWhere('articles.secondary_school_school', '=', $secondary_school)
                        ->orWhere('articles.secondary_school2_school', '=', $secondary_school);
                    }
                }
            });
        }

        // 土地面積（下限）
        if (isset($condition->land_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->land_from as $land_from) {
                    if($land_from != null) {
                        $query -> orWhere('articles.land_area_val', '>=', $land_from);
                    }
                }
            });
        }
        // 土地面積（上限）
        if (isset($condition->land_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->land_to as $land_to) {
                    if($land_to != null) {
                        $query -> orWhere('articles.land_area_val', '<=', $land_to);
                    }
                }
            });
        }

        // 延べ床面積(下限)
        if (isset($condition->floor_area_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->floor_area_from as $floor_area_from) {
                    if($floor_area_from != null) {
                        $query -> orWhere('articles.total_area_val', '>=', $floor_area_from);
                    }
                }
            });
        }

        // 延べ床面積（上限）
        if (isset($condition->floor_area_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->floor_area_to as $floor_area_to) {
                    if($floor_area_to != null) {
                        $query -> orWhere('articles.total_area_val', '<=', $floor_area_to);
                    }
                }
            });
        }

        // 間取り
        if (isset($condition->floor_plan)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->floor_plan as $floor_plan) {
                    if($floor_plan != null) {
                        $query -> orWhere('articles.floor_plan', '=', $floor_plan);
                    }
                }
            });
        }
        // 物件名
        if (isset($condition->name)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->name as $name) {
                    if($name != null) {
                        $query -> orWhere('articles.name', 'LIKE', '%'.$name.'%');
                    }
                }
            });
        }

        if (isset($condition->years_from)) {
            // 築年数（下限)
            $article -> where(function($query) use ($article, $condition){
                $year = date('Y');
                foreach($condition->years_from as $years_from) {
                    if($years_from != null) {
                        $year_from = $year - $years_from;
                        if($years_from != null) {
                            $query -> orWhere('articles.age_year', '>=', $year_from);
                        }
                    }
                }
            });
        } 

        if (isset($condition->years_to)) {
            // 築年数（上限）
            $article -> where(function($query) use ($article, $condition){
                $year = date('Y');
                foreach($condition->years_to as $years_to) {
                    if($years_to != null) {
                        $year_to = $year - $years_to;
                        if($year_to != null) {
                            $query -> orWhere('articles.age_year', '<=', $year_to);
                        }
                    }
                }
            });
        }

        if (isset($condition->year_month_from)) {
            // 築年月（下限）
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->year_month_from as $year_month_from) {
                    $from = str_replace('/', '', $year_month_from);
            
                    if(is_numeric($from)) {
                        $from_list = explode('/', $year_month_from);
                        $from = $from_list[0].sprintf('%02d', $from_list[1]);
                        $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\'))>='.$from);
                    }
                }
            });
        } 
        if (isset($condition->year_month_to)) {
            // 築年月（上限）
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->year_month_to as $year_month_to) {
                    $to = str_replace('/', '', $year_month_to);
            
                    if(is_numeric($to)) {
                        $to_list = explode('/', $year_month_to);
                        $to = $to_list[0].sprintf('%02d', $to_list[1]);
                        $article -> whereRaw('concat(articles.age_year, lpad(articles.age_month, 2, \'0\'))<='.$to);
                    }
                }
            });
        }

        // 構造
        if (isset($condition->construction)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->construction as $construction) {
                    if($construction != null) {
                        $query -> orWhere('articles.construction', '=', $construction);
                    }
                }
            });
        }
        

        //駐車場
        if (isset($condition->parking)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->parking as $parking) {
                    if($parking != null) {
                        $query -> orWhere('articles.parking', '=', $parking);
                    }
                }
            });
        }


        // 現況
        if (isset($condition->current_status)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->current_status as $current_status) {
                    if($current_status != null) {
                        $query -> orWhere('articles.current_status', '=', $current_status);
                    }
                }
            });
        }


        // 用途地域
        if (isset($condition->use_area)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->use_area as $use_area) {
                    if($use_area != null) {
                        $query -> orWhere('articles.use_area', '=', $use_area);
                    }
                }
            });
        }

        // 階建（下限）
        if (isset($condition->floor_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->floor_from as $floor_from) {
                    if($floor_from != null) {
                        $query -> orWhere('articles.floor', '>=', $floor_from);
                    }
                }
            });
        }
        // 階建（上限）
        if (isset($condition->floor_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->floor_to as $floor_to) {
                    if($floor_to != null) {
                        $query -> orWhere('articles.floor', '<=', $floor_to);
                    }
                }
            });
        }

        // 建築条件
        if (isset($condition->land_condition)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->land_condition as $land_condition) {
                    if($land_condition != null) {
                        $query -> orWhere('articles.land_condition', '=', $land_condition);
                    }
                }
            });
        }

        // 条件外し
        if (isset($condition->remove_condition)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->remove_condition as $remove_condition) {
                    if($remove_condition != null) {
                        $query -> orWhere('articles.land_condition_not', '=', 1);
                    }
                }
            });
        }

        // 接道方向
        if (isset($condition->land_kind)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->land_kind as $land_kind) {
                    if($land_kind != null) {
                        $query->orWhere('articles.land_direction1', '=', $land_kind);
                        $query->orWhere('articles.land_direction2', '=', $land_kind);
                        $query->orWhere('articles.land_direction3', '=', $land_kind);
                    }
                }
            });
        }

        // 取り扱い店舗
        if (isset($condition->search_shop)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->search_shop as $search_shop) {
                    if($search_shop != null) {
                        $query -> orWhere('articles.shop', '=', $search_shop);
                    }
                }
            });
        }

        // バルコニー（向き）
        if (isset($condition->balcony_direction)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->balcony_direction as $balcony_direction) {
                    if($balcony_direction != null) {
                        $query -> orWhere('articles.terrace_direction', '=', $balcony_direction);
                    }
                }
            });
        }
        // バルコニー（広さ）
        if (isset($condition->balcony_area)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->balcony_area as $balcony_area) {
                    if($balcony_area != null) {
                        $query -> orWhere('articles.terrace_area_val', '>=', $balcony_area);
                    }
                }
            });
        }

        if(isset($condition->is_pet)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->is_pet as $is_pet) {
                    if($is_pet != null) {
                        if($is_pet == 1) {
                            $article -> where('art2.pet', '=', 1);
                        } else {
                            $article -> where('art2.pet', '=', 2);
                        }
                    }
                }
            });
        }
        if(isset($condition->pet_num)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->pet_num as $pet_num) {
                    if($pet_num != null) {
                        $query -> orWhere('art2.pet_count', '=', $pet_num);
                    }
                }
            });
        }

        if(isset($condition->shared)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->shared as $shared) {
                    if($shared != null) {
                        $query -> orWhere('relation_shareds.item_id', '=', $shared);
                    }
                }
            });
        }
        

        // 事業主
        if (isset($condition->management_company)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->management_company as $management_company) {
                    if($management_company != null) {
                        $query -> orWhere('art2.management_company', 'LIKE', '%'.$management_company.'%');
                    }
                }
            });
        }

        // 施工
        if (isset($condition->construction_name)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->construction_name as $construction_name) {
                    if($construction_name != null) {
                        $query -> orWhere('art2.construction_company', 'LIKE', '%'.$construction_name.'%');
                    }
                }
            });
        }

        // 物件登録日
        if (isset($condition->registed_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->registed_from as $registed_from) {
                    if($registed_from != null) {
                        $query -> orWhere('articles.created_at', '>=', $registed_from);
                    }
                }
            });
        }
        // 物件登録日
        if (isset($condition->registed_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->registed_to as $registed_to) {
                    if($registed_to != null) {
                        $query -> orWhere('articles.created_at', '<=', $registed_to);
                    }
                }
            });
        }

        // 物件確認日
        if (isset($condition->checked_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->checked_from as $checked_from) {
                    if($checked_from != null) {
                        $query -> orWhere('relation_vendor_articles.article_conf_day', '>=', $checked_from);
                    }
                }
            });
        }

        // 物件確認日
        if (isset($condition->checked_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->checked_to as $checked_to) {
                    if($checked_to != null) {
                        $query -> orWhere('relation_vendor_articles.article_conf_day', '<=', $checked_to);
                    }
                }
            });
        }

        // 価格変更日
        if (isset($condition->changed_price_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->changed_price_from as $changed_price_from) {
                    if($changed_price_from != null) {
                        $query -> orWhere('relation_price_histories.regist_date', '>=', $changed_price_from);
                    }
                }
            });
        }
        
        // 価格変更日
        if (isset($condition->changed_price_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->changed_price_to as $changed_price_to) {
                    if($changed_price_to != null) {
                        $query -> orWhere('relation_price_histories.regist_date', '<=', $changed_price_to);
                    }
                }
            });
        }

        
        // 広告確認日
        if (isset($condition->ad_checked_from)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->ad_checked_from as $ad_checked_from) {
                    if($ad_checked_from != null) {
                        $query -> orWhere('relation_vendor_articles.ad_conf_day', '>=', $ad_checked_from);
                    }
                }
            });
        }
        // 広告確認日
        if (isset($condition->ad_checked_to)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->ad_checked_to as $ad_checked_to) {
                    if($ad_checked_to != null) {
                        $query -> orWhere('relation_vendor_articles.ad_conf_day', '<=', $ad_checked_to);
                    }
                }
            });
        }
        if (isset($condition->flyer)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->flyer as $flyer) {
                    if($flyer != null) {
                        $query -> orWhere('relation_vendor_articles.flyer', '=', $flyer);
                    }
                }
            });
        }
        if (isset($condition->freepaper)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->freepaper as $freepaper) {
                    if($freepaper != null) {
                        $query -> orWhere('relation_vendor_articles.freepaper', '=', $freepaper);
                    }
                }
            });
        }
        if (isset($condition->house_hp)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->house_hp as $house_hp) {
                    if($house_hp != null) {
                        $query -> orWhere('relation_vendor_articles.house_hp', '=', $house_hp);
                    }
                }
            });
        }
        if (isset($condition->portal)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->portal as $portal) {
                    if($portal != null) {
                        $query -> orWhere('relation_vendor_articles.portal', '=', $portal);
                    }
                }
            });
        }
        if (isset($condition->signboard)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->signboard as $signboard) {
                    if($signboard != null) {
                        $query -> orWhere('relation_vendor_articles.signboard', '=', $signboard);
                    }
                }
            });
        }

        if (isset($condition->own_company)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->own_company as $own_company) {
                    if($own_company != null) {
                        $query -> orWhere('articles.own_company', '=', $own_company);
                    }
                }
            });
        }
        if (isset($condition->suumo)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->suumo as $suumo) {
                    if($suumo != null) {
                        $query -> orWhere('articles.suumo', '=', $suumo);
                    }
                }
            });
        }
        if (isset($condition->homes)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->homes as $homes) {
                    if($homes != null) {
                        $query -> orWhere('articles.homes', '=', $homes);
                    }
                }
            });
        }
        if (isset($condition->athome)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->athome as $athome) {
                    if($athome != null) {
                        $query -> orWhere('articles.athome', '=', $athome);
                    }
                }
            });
        }
        
        
        // 業者名
        if (isset($condition->vender_name)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->vender_name as $vender_name) {
                    if($vender_name != null) {
                        $query -> orWhere('vendors.name', 'like', '%'.$vender_name.'%');
                    }
                }
            });
        }

        if (isset($condition->vender_kind)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->vender_kind as $vender_kind) {
                    if($vender_kind != null) {
                        $query -> orWhere('relation_vendor_articles.manner', '=', $vender_kind);
                    }
                }
            });
        }

        if (isset($condition->vender_tel)) {
            $article -> where(function($query) use ($article, $condition){
                foreach($condition->vender_tel as $vender_tel) {
                    if($vender_tel != null) {
                        $query -> orWhere('vendors.vendor_tel1', 'like', '%'.$vender_tel.'%');
                    }
                }
            });
        }

        if(isset($condition->notcondition)) {
            switch($condition->notcondition) {
                case 'notHold':
                $article->where('articles.status', '!=', 4);
                $article->where('articles.status', '!=', 6);
                break;

                case 'notClosed':
                $article->where('articles.status', '!=', 4);
                break;

                case 'notEtc':
                $article->where('articles.status', '!=', 2);
                $article->where('articles.status', '!=', 3);
                $article->where('articles.status', '!=', 4);
                $article->where('articles.status', '!=', 5);
                $article->where('articles.status', '!=', 6);
                break;

                default:
                break;

            }
        }

        if($recommend == 1) {
            $article->where('articles.recommend', '=', 1);
        }

        if($recommend == 2) {
            $article -> where(function($query) use ($condition){
                $query->orWhere('articles.recommend', '=', 0)
                      ->orWhereNull('articles.recommend');
            });
        }

        if($close != null) {
            if($close == 1) {
                $article -> where(function($query) use ($condition){
                    $query->where('articles.status', '=', 4)
                          ->orWhere('articles.status', '=', 5);
                });
            } else {
                $article -> where(function($query) use ($condition){
                    $query->where('articles.status', '!=', 4)
                          ->where('articles.status', '!=', 5);
                });
            }
        }
        

        return $article;
    }
}
