attributes["conf_day"])) {
$item->attributes["conf_day"] = Carbon::now()->format("Y-m-d");
}
});
}
public function lot()
{
return $this->belongsTo(Article::class, "sale_no", "building_id");
}
// protected $guarded = ['id'];
public function customs()
{
return $this->hasMany(RelationCustomContent::class, "article_id", "building_id");
}
public function listImages()
{
/*if (!empty($this->attributes)) {
return $this->hasMany(RelationArticlePhoto::class, "article_id", "building_id")->orderByRaw("case when `order` is null then 1 else 0 end, `order`")->where("company_id", $this->attributes["company_id"]);
} else {*/
$user = Auth::user();
return $this->hasMany(RelationArticlePhoto::class, "article_id", "building_id")->orderByRaw("case when `order` is null then 1 else 0 end, `order`")->where("company_id", $user->company_id);
//}
}
public function primarySchool()
{
return $this->belongsTo(MstSchool::class, "primary_school_school", "code");
}
public function secondarySchool()
{
return $this->belongsTo(MstSchool::class, "secondary_school_school", "code");
}
public function lawrestrictions()
{
$database = $this->getConnection()->getDatabaseName();
return $this->belongsToMany(MstLawrestriction::class, $database.".relation_lawrestrictions", "article_id", "item_id", "building_id");
}
public function otherRestrictions()
{
return $this->hasMany(RelationOtherrestriction::class, "article_id", "building_id");
}
public function exteriors()
{
return $this->hasMany(RelationExterior::class, "article_id", "building_id");
}
public function interiors()
{
return $this->hasMany(RelationInterior::class, "article_id", "building_id");
}
public function shareds()
{
return $this->hasMany(RelationShared::class, "article_id", "building_id");
}
public function rooms()
{
return $this->hasMany(Article::class, "mansion_id", "building_id")
->where("company_id", $this->attributes["company_id"]);
}
public function events()
{
return $this->hasMany(RelationEventDay::class, "article_id", "building_id");
}
public function prices()
{
return $this->hasMany(RelationPriceHistory::class, "article_id", "building_id");
}
public function histories()
{
// $relations = $this->prices()->orderBy('regist_date', "DESC")->get();
$relations = $this->prices()->orderBy('regist_date', "DESC")->orderBy('id', "DESC")->get();
foreach ($relations as $key => $value) {
if (isset($relations[$key + 1])) {
if ($relations[$key]->price == $relations[$key + 1]->price) {
unset($relations[$key]);
}
}
}
return $relations;
}
public function getArticleNewDateHistory() {
$result = $this->prices()->orderBy('regist_date', "DESC")->orderBy('id', "DESC")->first();
return $result;
}
public function pref()
{
return $this->belongsTo(MstPrefecture::class, "address1", "code");
}
public function city()
{
return $this->belongsTo(MstCity::class, "address2", "code")->where("pref_code", $this->attributes["address1"]);
}
public function town()
{
return $this->belongsTo(MstTown::class, "address3", "code")
->where("pref_code", $this->attributes["address1"])
->where("city_code", $this->attributes["address2"]);
}
public function vendors()
{
return $this->belongsToMany(Vendor::class, "relation_vendor_articles", "article_id", "vendor_id", "building_id");
}
public function relationVendors()
{
return $this->hasMany(RelationVendorArticle::class, "article_id", "building_id")->where("company_id", $this->attributes["company_id"]);
}
public static function getMaxNo()
{
$user = Auth::user();
$result = Article::where('company_id', $user->company_id)->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 function getDirectionName($direction)
{
$directions = [
1 => "北",
2 => "北東",
3 => "東",
4 => "南東",
5 => "南",
6 => "南西",
7 => "西",
8 => "北西",
];
return $directions[$direction] ?? "";
}
public function getTypeNameAttribute()
{
$isHouseArticle = [4, 5];
$isLandArticle = [1, 2, 3];
$isMansionArticle = [6, 7];
$property = $this->attributes["property"];
if (in_array($property, $isHouseArticle)) {
return "戸建て";
}
if (in_array($property, $isLandArticle)) {
return "土地";
}
if (in_array($property, $isMansionArticle)) {
return "マンション建物";
}
if (is_null($property)) {
return "マンション部屋";
}
return "分譲地";
}
public static function getCountByArea($town_id)
{
$user = Auth::user();
$article = Article::query();
$article->where('company_id', $user->company_id)->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)
->where('address2', '!=', 204)
->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($properties, $property_sub = null, $area = null)
{
$user = Auth::user();
$article = Article::query();
$article->where('company_id', $user->company_id)->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($area, $num = null)
{
$user = Auth::user();
$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_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)->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()
{
$user = Auth::user();
$result = Article::where('company_id', $user->company_id)->where('recommend', '=', 1)->max('recommend_num');
return $result;
}
public static function getParentMansion( $id)
{
$user = Auth::user();
$result = Article::where('company_id', $user->company_id)->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($id)
{
$user = Auth::user();
$result = Article::select('floor')->where('company_id', $user->company_id)->where('building_id', '=', $id)->first();
return $result;
}
public static function getDataById($id)
{
$user = Auth::user();
$article = Article::query();
$article->select('articles.*', 'articles.balcony_direction', 'articles.members', 'articles.move_in_conditions', 'articles.terrain', 'articles.kokudoho', 'articles.place_area', 'articles.point_recommend', 'articles.building_id', '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_delivery_month', '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',
'art2.main_traffic_bus_time as m_main_traffic_bus_time', 'art2.main_traffic_bus as m_main_traffic_bus', 'art2.main_traffic_bus_walk as m_main_traffic_bus_walk',
'art2.sub_traffic1_time as m_sub_traffic1_time', 'art2.sub_traffic1_bus_time as m_sub_traffic1_bus_time', 'art2.sub_traffic1_bus as m_sub_traffic1_bus', 'art2.sub_traffic1_bus_walk as m_sub_traffic1_bus_walk',
'art2.main_traffic_time as m_main_traffic_time', 'art2.else_traffic_time as m_else_traffic_time', 'art2.main_traffic as m_main_traffic', 'art2.else_traffic as m_else_traffic',
'art2.main_traffic_line as m_main_traffic_line', 'art2.sub_traffic2_line as m_sub_traffic2_line', 'art2.sub_traffic2_station as m_sub_traffic2_station', 'art2.sub_traffic2_bus as m_sub_traffic2_bus',
'art2.main_traffic_station as m_main_traffic_station', 'art2.sub_traffic1_line as m_sub_traffic1_line', 'art2.sub_traffic1_station as m_sub_traffic1_station', 'art2.sub_traffic1 as m_sub_traffic1', 'art2.sub_traffic2_time as m_sub_traffic2_time',
'art2.total_unit as m_total_unit', 'art2.sub_traffic2_bus_walk as m_sub_traffic2_bus_walk', 'art2.sub_traffic2_bus_time as m_sub_traffic2_bus_time'
);
$article->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
// ->leftJoin('mst_cities', 'mst_cities.code', '=', 'articles.address2')
->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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->leftJoin('articles as art2', 'articles.mansion_id', '=', 'art2.building_id');
$article->where('articles.company_id', '=', $user->company_id);
//$article->where('mst_cities.company_id', '=', $user->company_id);
$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($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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->where('articles.company_id', '=', $user->company_id);
$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($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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->where('articles.company_id', '=', $user->company_id);
$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_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('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_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('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();
$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('status', '=', 1);
$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('status', '=', 1);
$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_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->leftJoin(env('DB_DATABASE').'.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_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->leftJoin(env('DB_DATABASE').'.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('
IF (art2.id is null,
LEAST (
IF (
articles.main_traffic_line_id is null and articles.main_traffic_station_id is null,
2147483647,
COALESCE(IF (articles.main_traffic_time = 0, 2147483647, articles.main_traffic_time), 2147483647)
),
IF (
articles.sub_traffic1_line_id is null and articles.sub_traffic1_station_id is null,
2147483647,
COALESCE(IF (articles.sub_traffic1_time = 0, 2147483647, articles.sub_traffic1_time), 2147483647)
),
IF (
articles.sub_traffic2_line_id is null and articles.sub_traffic2_station_id is null,
2147483647,
COALESCE(IF (articles.sub_traffic2_time = 0, 2147483647, articles.sub_traffic2_time), 2147483647)
)
),
LEAST (
IF (
art2.main_traffic_line_id is null and art2.main_traffic_station_id is null,
2147483647,
COALESCE(IF (art2.main_traffic_time = 0, 2147483647, art2.main_traffic_time), 2147483647)
),
IF (
art2.sub_traffic1_line_id is null and art2.sub_traffic1_station_id is null,
2147483647,
COALESCE(IF (art2.sub_traffic1_time = 0, 2147483647, art2.sub_traffic1_time), 2147483647)
),
IF (
art2.sub_traffic2_line_id is null and art2.sub_traffic2_station_id is null,
2147483647,
COALESCE(IF (art2.sub_traffic2_time = 0, 2147483647, art2.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, $lot = null, $ConditionSearch = [], $StatusNotIn = [])
{
$user = Auth::user();
$article = Article::query();
$article->select('articles.*',
'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.gas',
'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', 'art2.property as m_property'
);
// if($close == 1){
// $article->select('articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.gas',
// '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.tax',
// 'articles.bk', 'articles.key_text', 'articles.memo1', 'articles.price_closing', 'articles.close_date', 'articles.status','articles.sub_traffic1_line', 'articles.sub_traffic1_station', 'articles.sub_traffic1',
// 'articles.sub_traffic1_time', 'articles.sub_traffic1', 'articles.sub_traffic1_bus', 'articles.sub_traffic1_bus_time', 'articles.sub_traffic1_bus_walk', 'articles.sub_traffic2_station', 'articles.sub_traffic2_time', 'articles.sub_traffic2_bus_time', 'articles.sub_traffic2_bus_walk', 'articles.sub_traffic2_line', 'articles.sub_traffic2', 'articles.sub_traffic3_station', 'articles.sub_traffic3_line', 'articles.sub_traffic3', 'articles.sub_traffic3_time', 'articles.sub_traffic3_bus_time', 'articles.sub_traffic3_bus_walk', 'articles.sub_traffic3_bus', 'articles.sub_traffic4_station', 'articles.sub_traffic4_line', 'articles.sub_traffic4', 'articles.sub_traffic4_time', 'articles.sub_traffic4_bus_time', 'articles.sub_traffic4_bus_walk', 'articles.sub_traffic5_station', 'articles.sub_traffic5_line', 'articles.sub_traffic5', 'articles.sub_traffic5_time', 'articles.sub_traffic5_bus_time', 'articles.sub_traffic5_bus_walk', 'articles.else_traffic_station', 'articles.else_traffic_line', 'articles.else_traffic', 'articles.else_traffic_time', 'articles.else_traffic_bus_time', 'articles.else_traffic_bus_walk', 'articles.underground', 'articles.floor', 'articles.maisonette', 'articles.maisonette_from', 'articles.maisonette_to', 'articles.conf_day',
// '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', 'art2.property as m_property'
// );
// }else{
// }
$article->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
//->leftJoin('mst_cities', 'mst_cities.code', '=', 'articles.address2')
->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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->leftJoin('articles as art2', function ($join) {
$join->on('articles.mansion_id', '=', 'art2.building_id')
->on("articles.company_id", '=', 'art2.company_id');
});
$article->leftJoin(DB::raw("(SELECT `article_id`, `regist_date_last`
FROM (
SELECT `article_id`,
SUBSTRING_INDEX(GROUP_CONCAT(`regist_date` SEPARATOR ','), ',', -1) AS `regist_date_last`
FROM `relation_price_histories`
GROUP BY `article_id`
HAVING COUNT(*) > 1
) a) newTable"),
function($join)
{
$join->on('articles.building_id', '=', 'newTable.article_id');
});
$article->leftJoin('portal_status_articles', 'portal_status_articles.article_id', '=', 'articles.building_id');
$article->leftJoin('relation_portal_publics', 'relation_portal_publics.article_id', '=', 'articles.building_id');
if (isset($condition->sort) && $condition->sort == "vendor_tel") {
$article->leftJoin(DB::raw(
'(SELECT
MIN(vendors.id) AS min_vendor_id,
vendors.vendor_tel1,
rva.article_id
FROM
relation_vendor_articles AS rva
JOIN vendors ON rva.vendor_id = vendors.id
GROUP BY rva.article_id) AS v'
), 'v.article_id', '=', 'articles.building_id');
$article->leftJoin('relation_vendor_articles', 'v.min_vendor_id', '=', 'relation_vendor_articles.vendor_id');
} else {
$article->leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
if (isset($lot)) {
$article->leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id');
} else {
if (isset($condition->only_items)) {
$article->leftJoin('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id');
}else{
$article->join('vendors', 'relation_vendor_articles.vendor_id', '=', 'vendors.id');
}
}
}
$article->leftJoin('relation_price_histories', 'articles.building_id', '=', 'relation_price_histories.article_id');
$article->leftJoin('relation_article_photos', function ($join) {
$join->on('relation_article_photos.article_id', '=', 'articles.building_id')
->on('relation_article_photos.company_id', '=', 'articles.company_id');
});
$article->leftJoin('relation_article_photos as relation_art_photos', function ($join) {
$join->on('relation_art_photos.article_id', '=', 'articles.mansion_id')
->on('relation_art_photos.company_id', '=', 'articles.company_id');
});
// $article->orderBy('articles.building_id', 'DESC');
if (isset($user)){
$article->where('articles.company_id', $user->company_id);
// $article->where('mst_cities.company_id', $user->company_id);
}
if (!empty($ConditionSearch)) {
$article->whereIn('articles.building_id', $ConditionSearch);
}
if (!empty($StatusNotIn)){
$article->whereNotIn('articles.status', $StatusNotIn);
}
// whereの設定
$article = self::setSearchWhere($article, $condition, $recommend, $close, $count_flag);
if(isset($condition->property)){
if ($condition->property[0] == 61) {
$article->where("art2.property", '=', 6);
}
if ($condition->property[0] == 7) {
$article->where("art2.property", '=', 7);
}
$article->where(function ($query) {
$query->whereNull('articles.provisional')
->orWhere('articles.provisional', 0);
});
}
//$article -> where('articles.provisional', '=', 0);
$article->where(function ($query) {
$query->whereNull('articles.del')
->orWhere('articles.del', '0');
});
//$article->where('countH','>=',3);
//dd($article->toSql());
//dd($article->getBindings());
// 画像有無
if (isset($condition->image)) {
if ($condition->image == 1) {
$article->whereNull('relation_article_photos.file_path');
$article->whereNull('relation_art_photos.file_path');
} else {
$article->where(function ($query) {
$query->orWhereNotNull('relation_article_photos.file_path');
$query->orWhereNotNull('relation_art_photos.file_path');
});
}
}
if (isset($condition->floor)) {
if ($condition->floor == 1) {
$article->whereNull('articles.floor_file_path');
} else if($condition->floor == 2) {
$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)) {
$type = request()->get('search_sort') ? request()->get('search_sort') : 'desc';
if ($condition->sort == "new") {
$article->orderBy('articles.created_at', $type);
} else if ($condition->sort == "newprice") {
// $article->orderBy('history.max');
} else if ($condition->sort == "inexpensive") {
$article->orderBy('articles.price', $type);
} else if ($condition->sort == "station") {
$typeRaw = $type == 'desc' ? 'GREATEST' : 'LEAST';
$num = $type == 'desc' ? '0' : '2147483647';
if($condition->access3) {
foreach ($condition->access3 as $k => $v) {
if($v == 2) {
$article->orderByRaw('
IF (art2.id is null,
' . $typeRaw . ' (
IF (
articles.main_traffic = 2 and articles.main_traffic_bus_time is not null and
articles.main_traffic_line is not null and articles.main_traffic_station is not null,
214748+articles.main_traffic_bus_time, ' . $num . '
),
IF (
articles.sub_traffic1 = 2 and articles.sub_traffic1_bus_time is not null and
articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
214748+articles.sub_traffic1_bus_time, ' . $num . '
),
IF (
articles.sub_traffic2 = 2 and articles.sub_traffic2_bus_time is not null and
articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
214748+articles.sub_traffic2_bus_time, ' . $num . '
),
IF (
articles.else_traffic = 2 and articles.else_traffic_bus_time is not null and
articles.else_traffic_line is not null and articles.else_traffic_station is not null,
214748+articles.else_traffic_bus_time, ' . $num . '
)
),
' . $typeRaw . ' (
IF (
art2.main_traffic = 2 and art2.main_traffic_bus_time is not null and
art2.main_traffic_line is not null and art2.main_traffic_station is not null,
214748+art2.main_traffic_bus_time, ' . $num . '
),
IF (
art2.sub_traffic1 = 2 and art2.sub_traffic1_bus_time is not null and
art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
214748+art2.sub_traffic1_bus_time, ' . $num . '
),
IF (
art2.sub_traffic2 = 2 and art2.sub_traffic2_bus_time is not null and
art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
214748+art2.sub_traffic2_bus_time, ' . $num . '
),
IF (
art2.else_traffic = 2 and art2.else_traffic_bus_time is not null and
art2.else_traffic_line is not null and art2.else_traffic_station is not null,
214748+art2.else_traffic_bus_time, ' . $num . '
)
)
)'. $type);
} else {
$article->orderByRaw('
IF (art2.id is null,
' . $typeRaw . ' (
IF (
articles.main_traffic = 1 and articles.main_traffic_line is not null and articles.main_traffic_station is not null,
COALESCE(IF (articles.main_traffic_time = 0, ' . $num . ', articles.main_traffic_time), ' . $num . '), ' . $num . '
),
IF (
articles.sub_traffic1 = 1 and articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
COALESCE(IF (articles.sub_traffic1_time = 0, ' . $num . ', articles.sub_traffic1_time), ' . $num . '),' . $num . '
),
IF (
articles.sub_traffic2 = 1 and articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
COALESCE(IF (articles.sub_traffic2_time = 0, ' . $num . ', articles.sub_traffic2_time), ' . $num . '), ' . $num . '
),
IF (
articles.else_traffic = 1 and articles.else_traffic_line is not null and articles.else_traffic_station is not null,
COALESCE(IF (articles.else_traffic_time = 0, ' . $num . ', articles.else_traffic_time), ' . $num . '), ' . $num . '
),
IF (
articles.main_traffic = 2 and articles.main_traffic_bus_time is not null
and articles.main_traffic_line is not null and articles.main_traffic_station is not null,
214748+articles.main_traffic_bus_time, ' . $num . '
),
IF (
articles.sub_traffic1 = 2 and articles.sub_traffic1_bus_time is not null and
articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
214748+articles.sub_traffic1_bus_time, ' . $num . '
),
IF (
articles.sub_traffic2 = 2 and articles.sub_traffic2_bus_time is not null and
articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
214748+articles.sub_traffic2_bus_time, ' . $num . '
),
IF (
articles.else_traffic = 2 and articles.else_traffic_bus_time is not null and
articles.else_traffic_line is not null and articles.else_traffic_station is not null,
214748+articles.else_traffic_bus_time, ' . $num . '
)
),
' . $typeRaw . ' (
IF (
art2.main_traffic = 1 and art2.main_traffic_line is not null and art2.main_traffic_station is not null,
COALESCE(IF (art2.main_traffic_time = 0, ' . $num . ', art2.main_traffic_time), ' . $num . '), ' . $num . '
),
IF (
art2.sub_traffic1 = 1 and art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
COALESCE(IF (art2.sub_traffic1_time = 0, ' . $num . ', art2.sub_traffic1_time), ' . $num . '),' . $num . '
),
IF (
art2.sub_traffic2 = 1 and art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
COALESCE(IF (art2.sub_traffic2_time = 0, ' . $num . ', art2.sub_traffic2_time), ' . $num . '), ' . $num . '
),
IF (
art2.else_traffic = 1 and art2.else_traffic_line is not null and art2.else_traffic_station is not null,
COALESCE(IF (art2.else_traffic_time = 0, ' . $num . ', art2.else_traffic_time), ' . $num . '), ' . $num . '
),
IF (
art2.main_traffic = 2 and art2.main_traffic_bus_time is not null and
art2.main_traffic_line is not null and art2.main_traffic_station is not null,
214748+art2.main_traffic_bus_time, ' . $num . '
),
IF (
art2.sub_traffic1 = 2 and art2.sub_traffic1_bus_time is not null and
art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
214748+art2.sub_traffic1_bus_time, ' . $num . '
),
IF (
art2.sub_traffic2 = 2 and art2.sub_traffic2_bus_time is not null and
art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
214748+art2.sub_traffic2_bus_time, ' . $num . '
),
IF (
art2.else_traffic = 2 and art2.else_traffic_bus_time is not null and
art2.else_traffic_line is not null and art2.else_traffic_station is not null,
214748+art2.else_traffic_bus_time, ' . $num . '
)
)
)'. $type);
}
}
}
// $article->orderByRaw('
// IF (art2.id is null,
// ' . $typeRaw . ' (
// IF (
// articles.main_traffic = 1 and articles.main_traffic_line is not null and articles.main_traffic_station is not null,
// COALESCE(IF (articles.main_traffic_time = 0, ' . $num . ', articles.main_traffic_time), ' . $num . '), ' . $num . '
// ),
// IF (
// articles.sub_traffic1 = 1 and articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
// COALESCE(IF (articles.sub_traffic1_time = 0, ' . $num . ', articles.sub_traffic1_time), ' . $num . '),' . $num . '
// ),
// IF (
// articles.sub_traffic2 = 1 and articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
// COALESCE(IF (articles.sub_traffic2_time = 0, ' . $num . ', articles.sub_traffic2_time), ' . $num . '), ' . $num . '
// ),
// IF (
// articles.else_traffic = 1 and articles.else_traffic_line is not null and articles.else_traffic_station is not null,
// COALESCE(IF (articles.else_traffic_time = 0, ' . $num . ', articles.else_traffic_time), ' . $num . '), ' . $num . '
// ),
//
// IF (
// articles.main_traffic = 2 and articles.main_traffic_bus_time is not null,
// 214748+articles.main_traffic_bus_time, ' . $num . '
// ),
// IF (
// articles.sub_traffic1 = 2 and articles.sub_traffic1_bus_time is not null,
// 214748+articles.sub_traffic1_bus_time, ' . $num . '
// ),
// IF (
// articles.sub_traffic2 = 2 and articles.sub_traffic2_bus_time is not null,
// 214748+articles.sub_traffic2_bus_time, ' . $num . '
// ),
// IF (
// articles.else_traffic = 2 and articles.else_traffic_bus_time is not null,
// 214748+articles.else_traffic_bus_walk, ' . $num . '
// )
// ),
// ' . $typeRaw . ' (
// IF (
// art2.main_traffic = 1 and art2.main_traffic_line is not null and art2.main_traffic_station is not null,
// COALESCE(IF (art2.main_traffic_time = 0, ' . $num . ', art2.main_traffic_time), ' . $num . '), ' . $num . '
// ),
// IF (
// art2.sub_traffic1 = 1 and art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
// COALESCE(IF (art2.sub_traffic1_time = 0, ' . $num . ', art2.sub_traffic1_time), ' . $num . '),' . $num . '
// ),
// IF (
// articles.sub_traffic2 = 1 and articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
// COALESCE(IF (art2.sub_traffic2_time = 0, ' . $num . ', art2.sub_traffic2_time), ' . $num . '), ' . $num . '
// ),
// IF (
// art2.else_traffic = 1 and art2.else_traffic_line is not null and art2.else_traffic_station is not null,
// COALESCE(IF (art2.else_traffic_time = 0, ' . $num . ', art2.else_traffic_time), ' . $num . '), ' . $num . '
// ),
//
// IF (
// art2.main_traffic = 2 and art2.main_traffic_bus_time is not null,
// 214748+art2.main_traffic_bus_time, ' . $num . '
// ),
// IF (
// art2.sub_traffic1 = 2 and art2.sub_traffic1_bus_time is not null,
// 214748+art2.sub_traffic1_bus_time, ' . $num . '
// ),
// IF (
// art2.sub_traffic2 = 2 and art2.sub_traffic2_bus_time is not null,
// 214748+art2.sub_traffic2_bus_time, ' . $num . '
// ),
// IF (
// art2.else_traffic = 2 and art2.else_traffic_bus_time is not null,
// 214748+art2.else_traffic_bus_walk, ' . $num . '
// )
// )
// )'. $type);
} else if ($condition->sort == "age") {
$article->orderBy(DB::raw('articles.age_year IS NULL, articles.age_year'), $type)->orderBy(DB::raw('articles.age_month IS NULL, articles.age_month'), $type);
} else if ($condition->sort == "land") {
$article->orderBy('articles.land_area_val', $type);
} else if ($condition->sort == "floor") {
$article->orderBy(DB::raw('articles.total_area_val IS NULL, articles.total_area_val'), $type);
} else if ($condition->sort == "address") {
$article->orderBy('articles.address1', $type)->orderBy('articles.address2', $type)->orderBy('articles.address3', $type)->orderBy('articles.address4', $type);
} else if ($condition->sort == "line") {
$article->orderBy('articles.main_traffic_line_id', $type)->orderBy('articles.main_traffic_station_id', $type);
} else if ($condition->sort == "conf_day") {
$article->orderBy('articles.conf_day', $type);
} else if ($condition->sort == "created_at") {
$article->orderBy('articles.created_at', $type);
} else if ($condition->sort == "property") {
$article->orderBy('articles.property', $type)->orderBy('articles.property_sub', $type);
} else if ($condition->sort == "vendor") {
$article->orderBy('articles.property', $type)->orderBy('relation_vendor_articles.vendor_id', $type);
} else if ($condition->sort == "zip") {
$article->orderBy('articles.zip', $type);
} else if ($condition->sort == "vendor") {
$article->orderBy('relation_vendor_articles.vendor_id', $type);
} else if ($condition->sort == "vendor_tel") {
$article->orderBy('vendor_tel1', $type);
}
}
if (isset($condition->sort_recommend)) {
if (isset($condition->sort_type)) {
$sort_type = $condition->sort_type;
}
foreach ($condition->sort_recommend as $key => $value) {
if (!empty($value)) {
if ($key == "year_month") {
$article->orderBy('articles.age_year', $sort_type);
$article->orderBy('articles.age_month', $sort_type);
} elseif ($key == "school") {
$article->orderBy('articles.primary_school_school', $sort_type);
$article->orderBy('articles.secondary_school_school', $sort_type);
} elseif ($key == "location") {
$article->orderBy('pref_name', $sort_type);
$article->orderBy('city_name', $sort_type);
$article->orderBy('town_name', $sort_type);
} elseif ($key == "traffic") {
$article->orderBy('articles.main_traffic_line', $sort_type);
$article->orderBy('articles.main_traffic_station', $sort_type);
$article->orderBy('articles.main_traffic_time', $sort_type);
} elseif ($key == "floor_plan") {
$article->orderBy('articles.' . $key, $sort_type);
$article->orderByRaw("FIELD(`articles`.`floor_plan_type`, 3, 4, 1, 7, 2, 5, 6, 8, 9) {$sort_type}");
} else {
$article->orderBy('articles.' . $key, $sort_type);
}
}
}
}
if (isset($condition->hide_items)) {
$items = $condition->hide_items;
//dd($items);
$article->whereNotIn('articles.building_id', $items);
}
if (isset($condition->building_id_print) && !empty($condition->building_id_print)) {
$arrBuildingId = explode(',', $condition->building_id_print);
$article->whereIn('articles.building_id', $arrBuildingId);
}
$article->orderBy('articles.building_id', 'DESC');
$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();
}
//dd($article->toSql());
//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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->where('articles.company_id', $user->company_id);
//$article->where('mst_cities.company_id', $user->company_id);
// whereの設定
$article->where('articles.property', '=', 99);
if (isset($condition->name) && !empty($condition->name)) {
$article->where('articles.name', 'LIKE', '%' . ($condition->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();
}
//dd($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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->leftJoin('articles as art2', 'articles.mansion_id', '=', 'art2.building_id');
$article->where('articles.company_id', $user->company_id);
// whereの設定
if(isset($condition->parking)){
unset($condition['parking']);
unset($condition['floor_plan_from']);
unset($condition['floor_plan_to']);
unset($condition['exterior']);
unset($condition['interior']);
unset($condition['current_status']);
unset($condition['ad_checked_from']);
unset($condition['ad_checked_to']);
unset($condition['checked_from']);
unset($condition['checked_to']);
unset($condition['event_category']);
unset($condition['whereabouts_from']);
unset($condition['whereabouts_to']);
unset($condition['balcony_direction']);
unset($condition['balcony_area']);
unset($condition['registed_from']);
unset($condition['registed_to']);
unset($condition['changed_price_from']);
unset($condition['changed_price_to']);
}
$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)) {
$type = request()->get('search_sort') ? request()->get('search_sort') : 'desc';
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');
} else if ($condition->sort == "station") {
$typeRaw = $type == 'desc' ? 'GREATEST' : 'LEAST';
$num = $type == 'desc' ? '0' : '2147483647';
if($condition->access3) {
foreach ($condition->access3 as $k => $v) {
if($v == 2) {
$article->orderByRaw('
IF (art2.id is null,
' . $typeRaw . ' (
IF (
articles.main_traffic = 2 and articles.main_traffic_bus_time is not null and
articles.main_traffic_line is not null and articles.main_traffic_station is not null,
214748+articles.main_traffic_bus_time, ' . $num . '
),
IF (
articles.sub_traffic1 = 2 and articles.sub_traffic1_bus_time is not null and
articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
214748+articles.sub_traffic1_bus_time, ' . $num . '
),
IF (
articles.sub_traffic2 = 2 and articles.sub_traffic2_bus_time is not null and
articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
214748+articles.sub_traffic2_bus_time, ' . $num . '
),
IF (
articles.else_traffic = 2 and articles.else_traffic_bus_time is not null and
articles.else_traffic_line is not null and articles.else_traffic_station is not null,
214748+articles.else_traffic_bus_time, ' . $num . '
)
),
' . $typeRaw . ' (
IF (
art2.main_traffic = 2 and art2.main_traffic_bus_time is not null and
art2.main_traffic_line is not null and art2.main_traffic_station is not null,
214748+art2.main_traffic_bus_time, ' . $num . '
),
IF (
art2.sub_traffic1 = 2 and art2.sub_traffic1_bus_time is not null and
art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
214748+art2.sub_traffic1_bus_time, ' . $num . '
),
IF (
art2.sub_traffic2 = 2 and art2.sub_traffic2_bus_time is not null and
art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
214748+art2.sub_traffic2_bus_time, ' . $num . '
),
IF (
art2.else_traffic = 2 and art2.else_traffic_bus_time is not null and
art2.else_traffic_line is not null and art2.else_traffic_station is not null,
214748+art2.else_traffic_bus_time, ' . $num . '
)
)
)'. $type);
} else {
$article->orderByRaw('
IF (art2.id is null,
' . $typeRaw . ' (
IF (
articles.main_traffic = 1 and articles.main_traffic_line is not null and articles.main_traffic_station is not null,
COALESCE(IF (articles.main_traffic_time = 0, ' . $num . ', articles.main_traffic_time), ' . $num . '), ' . $num . '
),
IF (
articles.sub_traffic1 = 1 and articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
COALESCE(IF (articles.sub_traffic1_time = 0, ' . $num . ', articles.sub_traffic1_time), ' . $num . '),' . $num . '
),
IF (
articles.sub_traffic2 = 1 and articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
COALESCE(IF (articles.sub_traffic2_time = 0, ' . $num . ', articles.sub_traffic2_time), ' . $num . '), ' . $num . '
),
IF (
articles.else_traffic = 1 and articles.else_traffic_line is not null and articles.else_traffic_station is not null,
COALESCE(IF (articles.else_traffic_time = 0, ' . $num . ', articles.else_traffic_time), ' . $num . '), ' . $num . '
),
IF (
articles.main_traffic = 2 and articles.main_traffic_bus_time is not null
and articles.main_traffic_line is not null and articles.main_traffic_station is not null,
214748+articles.main_traffic_bus_time, ' . $num . '
),
IF (
articles.sub_traffic1 = 2 and articles.sub_traffic1_bus_time is not null and
articles.sub_traffic1_line is not null and articles.sub_traffic1_station is not null,
214748+articles.sub_traffic1_bus_time, ' . $num . '
),
IF (
articles.sub_traffic2 = 2 and articles.sub_traffic2_bus_time is not null and
articles.sub_traffic2_line is not null and articles.sub_traffic2_station is not null,
214748+articles.sub_traffic2_bus_time, ' . $num . '
),
IF (
articles.else_traffic = 2 and articles.else_traffic_bus_time is not null and
articles.else_traffic_line is not null and articles.else_traffic_station is not null,
214748+articles.else_traffic_bus_time, ' . $num . '
)
),
' . $typeRaw . ' (
IF (
art2.main_traffic = 1 and art2.main_traffic_line is not null and art2.main_traffic_station is not null,
COALESCE(IF (art2.main_traffic_time = 0, ' . $num . ', art2.main_traffic_time), ' . $num . '), ' . $num . '
),
IF (
art2.sub_traffic1 = 1 and art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
COALESCE(IF (art2.sub_traffic1_time = 0, ' . $num . ', art2.sub_traffic1_time), ' . $num . '),' . $num . '
),
IF (
art2.sub_traffic2 = 1 and art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
COALESCE(IF (art2.sub_traffic2_time = 0, ' . $num . ', art2.sub_traffic2_time), ' . $num . '), ' . $num . '
),
IF (
art2.else_traffic = 1 and art2.else_traffic_line is not null and art2.else_traffic_station is not null,
COALESCE(IF (art2.else_traffic_time = 0, ' . $num . ', art2.else_traffic_time), ' . $num . '), ' . $num . '
),
IF (
art2.main_traffic = 2 and art2.main_traffic_bus_time is not null and
art2.main_traffic_line is not null and art2.main_traffic_station is not null,
214748+art2.main_traffic_bus_time, ' . $num . '
),
IF (
art2.sub_traffic1 = 2 and art2.sub_traffic1_bus_time is not null and
art2.sub_traffic1_line is not null and art2.sub_traffic1_station is not null,
214748+art2.sub_traffic1_bus_time, ' . $num . '
),
IF (
art2.sub_traffic2 = 2 and art2.sub_traffic2_bus_time is not null and
art2.sub_traffic2_line is not null and art2.sub_traffic2_station is not null,
214748+art2.sub_traffic2_bus_time, ' . $num . '
),
IF (
art2.else_traffic = 2 and art2.else_traffic_bus_time is not null and
art2.else_traffic_line is not null and art2.else_traffic_station is not null,
214748+art2.else_traffic_bus_time, ' . $num . '
)
)
)'. $type);
}
}
}
// $article->orderByRaw('
// IF (art2.id is null,
// ' . $typeRaw . ' (
// IF (
// articles.main_traffic_line_id is null and articles.main_traffic_station_id is null,
// ' . $num . ',
// IF (
// articles.main_traffic = 2,
// ' . $num . ',
// COALESCE(IF (articles.main_traffic_time = 0, ' . $num . ', articles.main_traffic_time), ' . $num . ')
// )
// ),
// IF (
// articles.sub_traffic1_line_id is null and articles.sub_traffic1_station_id is null,
// ' . $num . ',
// IF (
// articles.sub_traffic1 = 2,
// ' . $num . ',
// COALESCE(IF (articles.sub_traffic1_time = 0, ' . $num . ', articles.sub_traffic1_time), ' . $num . ')
// )
// ),
// IF (
// articles.sub_traffic2_line_id is null and articles.sub_traffic2_station_id is null,
// ' . $num . ',
// IF (
// articles.sub_traffic2 = 2,
// ' . $num . ',
// COALESCE(IF (articles.sub_traffic2_time = 0, ' . $num . ', articles.sub_traffic2_time), ' . $num . ')
// )
// )
// ),
// ' . $typeRaw . ' (
// IF (
// art2.main_traffic_line_id is null and art2.main_traffic_station_id is null,
// ' . $num . ',
// IF (
// art2.main_traffic = 2,
// ' . $num . ',
// COALESCE(IF (art2.main_traffic_time = 0, ' . $num . ', art2.main_traffic_time), ' . $num . ')
// )
// ),
// IF (
// art2.sub_traffic1_line_id is null and art2.sub_traffic1_station_id is null,
// ' . $num . ',
// IF (
// art2.sub_traffic1 = 2,
// ' . $num . ',
// COALESCE(IF (art2.sub_traffic1_time = 0, ' . $num . ', art2.sub_traffic1_time), ' . $num . ')
// )
// ),
// IF (
// art2.sub_traffic2_line_id is null and art2.sub_traffic2_station_id is null,
// ' . $num . ',
// IF (
// art2.sub_traffic2 = 2,
// ' . $num . ',
// COALESCE(IF (art2.sub_traffic2_time = 0, ' . $num . ', art2.sub_traffic2_time), ' . $num . ')
// )
// )
// )
// ) ' . $type);
} else if ($condition->sort == "age") {
$article->orderBy('articles.age_year', $type)->orderBy('articles.age_month', $type);
} else if ($condition->sort == "address") {
$article->orderBy('articles.address1', $type)->orderBy('articles.address2')->orderBy('articles.address3', $type)->orderBy('articles.address4', $type);
} else if ($condition->sort == "line") {
$article->orderBy('articles.main_traffic_line_id', $type)->orderBy('articles.main_traffic_station_id', $type);
}
}
$article->orderBy('articles.building_id', '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 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_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->leftJoin(env('DB_DATABASE').'.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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
if ($status != null) {
if ($status == 1) {
// 売出
$article->where(function ($query) {
$query->where('status', '!=', 4);
$query->where('status', '!=', 5);
});
} else {
// 成約
$article->where(function ($query) {
$query->orWhere('status', '=', 4);
$query->orWhere('status', '=', 5);
});
}
}
// whereの設定
$article->where('mansion_id', '=', $id);
$article->where('articles.company_id', $user->company_id);
$article->where(function ($query) {
$query->whereNull('articles.del')
->orWhere('articles.del', '0');
});
$article->groupBy('articles.building_id');
$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',
'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4',
'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name',
'articles.other_address as other_address',
'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_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->whereNull('property');
$article->where('status', '!=', 4);
$article->where('status', '!=', 5);
$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());
$article->groupBy('articles.building_id');
$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_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');
});
if ($status != null) {
if ($status == 1) {
// 売出
$article->where('status', '!=', 4);
$article->where('status', '!=', 5);
} else {
// 成約
$article->where(function ($query) {
$query->orWhere('status', '=', 4);
$query->orWhere('status', '=', 5);
});
}
}
$article->where('articles.company_id', '=', $user->company_id);
$article->where('articles.sale_no', '=', $id);
$article->where(function ($query) {
$query->whereNull('articles.del')
->orWhere('articles.del', '0');
});
// print($article->toSql());
$article->groupBy('building_id');
$result = $article->get();
return $result;
}
public static function getArticleTotal($store_id, $own_company, $week, $null, $close = null, $mansion = null)
{
$user = Auth::user();
$article = Article::query();
$article->select('articles.id');
$article->where('articles.company_id', '=', $user->company_id);
if (empty($mansion)) {
if($store_id > 0){
$article->where('articles.shop', '=', $store_id);
}
if ($own_company != '') {
//$article->where('articles.own_company', '=', $own_company);
$article->where(function ($query) use ($own_company) {
$query->orwhere(function ($query3) use ($own_company) {
$propery = [6, 7, 99];
$query3->whereNotIn('articles.property', $propery);
$query3->where('articles.own_company', '=', $own_company);
});
$query->orwhere(function ($query3) use ($own_company) {
$query3->whereNotNull('articles.mansion_id');
$query3->where('articles.own_company', '=', $own_company);
});
});
}
if ($week != '') {
$date = strtotime(date("Y-m-d") . " -" . $week . " week");
$date = date("Y-m-d", $date);
$article->where('articles.updated_at', '<=', $date);
}
if ($null == 1) {
$article = self::setWhereArticles($article);
}
$article->where(function ($query) {
$query->whereNull('articles.del')
->orWhere('articles.del', '0');
});
/*$article->where(function ($query) {
$query->where('articles.property', '!=', 6)
->where('articles.property', '!=', 7);
});*/
if (!empty($close)) {
$article->where(function ($query) {
$query->orwhere(function ($query3) {
$propery = [6, 7, 99];
$query3->whereNotIn('articles.property', $propery);
$query3->where('articles.status', '!=', 4)->where('articles.status', '!=', 5);
});
$query->orwhere(function ($query3) {
$query3->whereNotNull('articles.mansion_id');
$query3->where('articles.status', '!=', 4)->where('articles.status', '!=', 5);
});
});
}
}else{
$article->where(function ($query) {
$query->orwhere('articles.property', '=', 6)
->orwhere('articles.property', '=', 7);
});
if ($null == 1) {
$article = self::setWhereArticles($article);
}
}
$result = $article->count();
return $result;
}
public static function getArticlePortalPublic($type, $perPage = null, $condition)
{
$ar_type = array(
'suumo' => 1,
'homes' => 2,
'athome' => 3
);
$type = $ar_type[$type];
$user = Auth::user();
$article = Article::query();
$article->select('articles.*',
'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.gas',
'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', 'art2.property as m_property'
);
$article->leftJoin('relation_portal_publics', 'relation_portal_publics.article_id', '=', 'articles.building_id');
$article->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
// ->leftJoin('mst_cities', 'mst_cities.code', '=', 'articles.address2')
->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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->leftJoin('articles as art2', function ($join) {
$join->on('articles.mansion_id', '=', 'art2.building_id')
->on("articles.company_id", '=', 'art2.company_id');
});
$article->leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
$article->leftJoin('relation_price_histories', 'articles.building_id', '=', 'relation_price_histories.article_id');
$article->leftJoin('relation_article_photos', function ($join) {
$join->on('articles.building_id', '=', 'relation_article_photos.article_id')
->on("articles.company_id", '=', 'relation_article_photos.company_id');
});
$article->leftJoin('relation_article_photos as relation_art_photos', function ($join) {
$join->on('relation_art_photos.article_id', '=', 'articles.mansion_id')
->on('relation_art_photos.company_id', '=', 'articles.company_id');
});
// 画像有無
if (isset($condition->image)) {
if ($condition->image == 1) {
$article->whereNull('relation_article_photos.file_path');
$article->whereNull('relation_art_photos.file_path');
} else {
$article->where(function ($query) {
$query->orWhereNotNull('relation_article_photos.file_path');
});
}
}
$article->groupBy('articles.building_id');
if($perPage == null){
$result = $article->get();
}else{
$result = $article->paginate($perPage);
}
return $result;
}
public static function getArticles($store_id, $own_company, $week, $null, $perPage = null, $condition, $close = null, $mansion = null)
{
$user = Auth::user();
$article = Article::query();
$article->select('articles.*',
'articles.address1', 'articles.address2', 'articles.address3', 'articles.address4', 'articles.gas',
'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', 'art2.property as m_property'
);
$article->leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'articles.address1')
// ->leftJoin('mst_cities', 'mst_cities.code', '=', 'articles.address2')
->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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$article->leftJoin('articles as art2', function ($join) {
$join->on('articles.mansion_id', '=', 'art2.building_id')
->on("articles.company_id", '=', 'art2.company_id');
});
$article->leftJoin('relation_vendor_articles', 'articles.building_id', '=', 'relation_vendor_articles.article_id');
$article->leftJoin('relation_price_histories', 'articles.building_id', '=', 'relation_price_histories.article_id');
//$article->leftJoin('relation_article_photos', 'articles.building_id', '=', 'relation_article_photos.article_id');
$article->leftJoin('relation_article_photos', function ($join) {
$join->on('articles.building_id', '=', 'relation_article_photos.article_id')
->on("articles.company_id", '=', 'relation_article_photos.company_id');
});
$article->leftJoin('relation_article_photos as relation_art_photos', function ($join) {
$join->on('relation_art_photos.article_id', '=', 'articles.mansion_id')
->on('relation_art_photos.company_id', '=', 'articles.company_id');
});
$article->where('articles.company_id', '=', $user->company_id);
if(empty($mansion)) {
if($store_id > 0){
$article->where('articles.shop', '=', $store_id);
}
if ($own_company != '') {
$article->where(function ($query) use ($own_company) {
$query->orwhere(function ($query3) use ($own_company) {
$propery = [6, 7, 99];
$query3->whereNotIn('articles.property', $propery);
$query3->where('articles.own_company', '=', $own_company);
});
$query->orwhere(function ($query3) use ($own_company) {
$query3->whereNotNull('articles.mansion_id');
$query3->where('articles.own_company', '=', $own_company);
});
});
}
if ($week != '') {
$date = strtotime(date("Y-m-d") . " -" . $week . " week");
$date = date("Y-m-d", $date);
$article->where('articles.updated_at', '<=', $date);
}
//$proper = [6,7];
//$article->whereNotIn('articles.property', $proper);
if ($null == 1) {
$article = self::setWhereArticles($article);
}
$article->where(function ($query) {
$query->whereNull('articles.del')
->orWhere('articles.del', '0');
});
// 画像有無
if (isset($condition->image)) {
if ($condition->image == 1) {
$article->whereNull('relation_article_photos.file_path');
$article->whereNull('relation_art_photos.file_path');
} else {
$article->where(function ($query) {
$query->orWhereNotNull('relation_article_photos.file_path');
$query->orWhereNotNull('relation_art_photos.file_path');
});
}
}
if (!empty($close)) {
$article->where(function ($query){
$query->orwhere(function ($query3) {
$propery = [6, 7, 99];
$query3->whereNotIn('articles.property', $propery);
$query3->where('articles.status', '!=', 4)->where('articles.status', '!=', 5);
});
$query->orwhere(function ($query3) {
$query3->whereNotNull('articles.mansion_id');
$query3->where('articles.status', '!=', 4)->where('articles.status', '!=', 5);
});
});
}
}else{
// 画像有無
if (isset($condition->image)) {
if ($condition->image == 1) {
$article->whereNull('relation_article_photos.file_path');
$article->whereNull('relation_art_photos.file_path');
} else {
$article->where(function ($query) {
$query->orWhereNotNull('relation_article_photos.file_path');
//$query->orWhereNotNull('relation_art_photos.file_path');
});
}
}
$article->where(function ($query) {
$query->orwhere('articles.property', '=', 6)
->orwhere('articles.property', '=', 7);
});
if ($null == 1) {
$article = self::setWhereArticles($article);
}
}
$article->groupBy('articles.building_id');
$result = $article->paginate($perPage);
return $result;
}
public static function setWhereArticles($article)
{
$article->where(function ($query) {
$query->orWhere(function ($query2) {
$query2->where(function ($query3) {
$query3->orwhere('articles.property', 1);
$query3->orwhere('articles.property', 2);
$query3->orwhere('articles.property', 3);
});
$query2->where(function ($query3) {
$query3->orWhereNull('articles.name')
->orWhere('articles.current_status', 0)
->orWhere('articles.land_delivery', 0)
->orWhere('articles.land_condition', 2)
->orWhere('articles.ground', 0)
->orWhereNull('articles.use_area')
->orWhereNull('articles.company_manner')
->orWhereNull('articles.price')
->orWhereNull('articles.land_area_val')
->orWhereNull('articles.shop')
->where(function ($query4) {
$query4->where('articles.classfication', 2)
->where('articles.use_method', 0);
});
});
$query2->WhereNull('articles.main_traffic_line')
->WhereNull('articles.else_traffic_line');
});
$query->orWhere(function ($query2) {
$query2->where(function ($query3) {
$query3->orwhere('articles.property', 4);
$query3->orwhere('articles.property', 5);
});
$query2->where(function ($query3) {
$query3->orWhereNull('articles.name')
->orWhereNull('articles.company_manner')
->orWhereNull('articles.price')
->orWhereNull('articles.land_area_val')
->orWhere('articles.construction', 0)
->orWhereNull('articles.use_area')
->orWhere('articles.move_in', 0)
->orWhere('articles.ground', 0)
->orWhereNull('articles.shop');
});
$query2->WhereNull('articles.main_traffic_line')
->WhereNull('articles.else_traffic_line');
});
$query->orWhere(function ($query2) {
$query2->WhereNotNull('articles.mansion_id');
$query2->where(function ($query3) {
$query3->orWhereNull('articles.name')
->orWhereNull('articles.shop')
->orWhereNull('articles.company_manner')
->orWhereNull('articles.price')
->orWhere('articles.management', 2)
->orWhere('articles.repair', 2)
//->orWhere('articles.balcony_area', 1)
->orWhere('articles.move_in', 0)
->orWhereNull('articles.whereabouts');
});
});
$query->orWhere(function ($query2) {
$query2->where(function ($query3) {
$query3->orwhere('articles.property', 6);
$query3->orwhere('articles.property', 7);
});
$query2->where(function ($query3) {
$query3->orWhereNull('articles.name')
->orWhereNull('articles.age_year')
->orWhereNull('articles.age_month')
->orWhere('articles.construction', 0)
->orWhereNull('articles.floor')
->orWhereNull('articles.underground')
//->orWhere('articles.underground', 0)
->orWhereNull('articles.use_area')
->orWhere('articles.management_form', 0);
});
$query2->WhereNull('articles.main_traffic_line')
->WhereNull('articles.else_traffic_line');
});
$query->orWhere(function ($query2) {
$query2->where('articles.property', 99);
$query2->where(function ($query3) {
$query3->WhereNull('articles.main_traffic_line')
->WhereNull('articles.else_traffic_line');
});
});
});
return $article;
}
public static function getCountRoom($code)
{
$res = Article::where('mansion_id', '=', $code)->count();
return $res;
}
public static function getCountByPrimarySchool($code)
{
$user = Auth::user();
$article = Article::query();
$article->where('primary_school_school', '=', $code)->where('company_id', '=', $user->company_id)->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)
{
$user = Auth::user();
$article = Article::query();
$article->where('secondary_school_school', '=', $code)->where('company_id', '=', $user->company_id)->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)
{
$user = Auth::user();
$article = Article::query();
$article->where('company_id', '=', $user->company_id)->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 getArticleNear($data)
{
$article = Article::query();
$article->select('articles.id', 'articles.building_id', 'rains_no', 'articles.name as article_name', 'articles.name_gouchi', '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'
);
$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');
})
->leftJoin('mst_schools', 'articles.primary_school_school', '=', 'mst_schools.code')
->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'articles.floor_plan_type', '=', 'mst_floor_types.code');
//$article->where("mst_cities.company_id", auth()->user()->company_id);
$article->where("articles.company_id", auth()->user()->company_id);
$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');
});
$result = $article->orderBy('articles.building_id')->get();
return $result;
}
public static function getDuplicateAritcleData($type, $data, $store = null)
{
$user = Auth::user();
$article = Article::query();
$article->select('articles.id', 'articles.building_id', 'articles.mansion_id', 'articles.rains_no', 'articles.name as article_name', 'articles.name_gouchi', 'articles.property', 'articles.property_sub', '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',
'articles.main_traffic', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus_walk', 'articles.main_traffic_bus', 'articles.room_num',
'articles.price', 'articles.land_area_val', 'articles.floor_plan', 'articles.floor_plan_type', '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.underground', 'articles.conf_day',
//'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('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(env('DB_DATABASE').'.mst_floor_types', 'articles.floor_plan_type', '=', 'mst_floor_types.code');
$article->leftJoin('articles as art2', 'articles.mansion_id', '=', 'art2.building_id');
// $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);
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 . '%');
});
}
if (!empty($data->article_name)) {
$article->where(function ($query) use ($data) {
$query->orWhere('articles.name', 'LIKE', '%' . $data->article_name . '%');
$query->orWhere('articles.name_apartment', 'LIKE', '%' . $data->article_name . '%');
});
}
} 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');
});
$article->where(function ($query) {
$query->orwhere(function ($query1) {
$query1->where('articles.status', '!=', 4)
->where('articles.status', '!=', 5);
});
$query->orwherenull('articles.status');
});
if ($user->company_id != null) {
$article->where('articles.company_id', '=', $user->company_id);
}
if (!is_null($store)) {
$article->where('articles.shop', '=', $store);
}
if($data->property_sub != null) {
if (!empty($data->property) && !in_array($data->property, [5,6])) {
$article->where(function ($query) use ($data) {
$query->orWhere('articles.property_sub', '=', $data->property_sub);
$query->orWhere('art2.property_sub', '=', $data->property_sub);
});
//$article->where('articles.property_sub', '=', $data->property_sub);
}
}
$article->groupBy('articles.building_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_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->leftJoin(env('DB_DATABASE').'.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($id)
{
$user = Auth::user();
$result = [];
$article = Article::query();
$result = $article->where('company_id', '=', $user->company_id)->where('building_id', '=', $id)->first();
return $result;
}
public static function getOpenHouseData()
{
$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_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->leftJoin(env('DB_DATABASE').'.mst_floor_types', 'mst_floor_types.code', '=', 'articles.floor_plan_type');
$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', '=', $user->company_id);
$result = $article->get();
// print_r($result);
return $result;
}
public static function getProvisionalData($num, $store = null)
{
$user = Auth::user();
$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', 'articles.main_traffic_bus_time', 'articles.main_traffic_bus_walk', 'articles.main_traffic_bus', '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.underground', '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', 'relation_vendor_articles.manner as manner'
);
$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_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(env('DB_DATABASE').'.mst_floor_types', 'articles.floor_plan_type', '=', 'mst_floor_types.code');
$article->where('articles.company_id', '=', $user->company_id);
//$article->where('mst_cities.company_id', '=', $user->company_id);
$article->where('vendors.company_id', '=', $user->company_id);
$article->whereRaw('vendors.id IN (SELECT MAX(vendor_id) FROM relation_vendor_articles AS rv WHERE rv.article_id = articles.building_id)');
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_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('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);
$query_inner->where('articles.address2', '!=', 204);
});
}
}
});
}
// 駅
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($close) and $close == 1){
if (isset($condition->changed_price_from)) {
$article->where('articles.close_date', '<=', $condition->changed_price_from);
}
// 価格変更日
if (isset($condition->changed_price_to)) {
$article->where('articles.close_date', '<=', $condition->changed_price_to);
}
}else{
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)) {
// dd($condition);
$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)) {
$pet = $condition->is_pet;
if ($pet != null) {
$article->where('art2.pet', '=', $pet);
}
}
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('articles.management_company', 'LIKE', '%' . $condition->management_company . '%'); //art2
}
// 施工
if (isset($condition->construction_name)) {
$article->where('articles.construction_company', 'LIKE', '%' . $condition->construction_name . '%'); //art2
}
// 管理画面
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)
{
$user = Auth::user();
$shop = MstStore::getStoreCode($user->store_id);
// 種別
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 or $property == 7) {
$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);
});
}elseif($property == 1){
$query2 -> orWhere(function($query3) use ($property){
$query3->orWhere('articles.property', '=', 1);
$query3->orWhere('articles.property', '=', 2);
$query3->orWhere('articles.property', '=', 3);
});
} 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);
}
});
}
$condition->notcondition = !empty($condition->notcondition) ? $condition->notcondition : request()->get('notcondition');
if (!empty($condition->notcondition)) {
switch ($condition->notcondition) {
case 'notHold':
$article->where('articles.status', '!=', 2); //4
$article->where('articles.status', '!=', 3); //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;
}
}
if (isset($condition->un_revenue)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->un_revenue as $un_revenue) {
if ($un_revenue != null) {
$query->orWhere('articles.revenue', '!=', $un_revenue);
}
}
});
}
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 && $address1 != -1) {
$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->town_name)) {
$article->where(function ($query) use ($article, $condition, $user) {
$companyID = $user->company_id;
foreach ($condition->town_name as $town_name) {
if ($town_name != null) {
// $query->orWhere('mst_towns.town_name', 'LIKE', '%'.$town_name.'%');
if(in_array($companyID, [self::COMPANY_CP2, self::COMPANY_CP3])) {
$query->orWhereRaw("CONCAT(
COALESCE(`mst_towns`.`name`, ''),
COALESCE(`articles`.`address4`, '')
) LIKE ?", ['%'.$town_name.'%']);
} else {
$query->orWhereRaw("CONCAT(
COALESCE(`mst_towns`.`name`, ''),
COALESCE(`mst_towns`.`chome_name`,''),
COALESCE(`mst_towns`.`koaza_name`,'')
) LIKE ?", ['%'.$town_name.'%']);
}
}
}
});
}
// 所在地（その他）
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->event_category)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->event_category as $event_category) {
if ($event_category != null) {
$query->where('articles.event_category', '!=', 0);
}
}
});
}
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) && is_array($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->bus)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->bus as $bus) {
if ($bus != null) {
$query->orWhere('articles.main_traffic_bus_time', '<=', $bus);
$query->orWhere('articles.sub_traffic1_bus_time', '<=', $bus);
$query->orWhere('articles.sub_traffic2_bus_time', '<=', $bus);
}
}
});
}
// 沿線（路線）
$article->where(function ($builder) use ($condition) {
$builder->orWhere(function ($query) use ($condition) {
self::queryCondition($query, 'articles.main_traffic_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'articles.main_traffic_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'articles.main_traffic', "=", $condition->access3 ?? []);
foreach ($condition->access3 as $key => $access3) {
if($access3 != null){
$query->where('articles.mansion_id', "=", null);
}
if ($access3 == 2) {
self::queryCondition($query, 'articles.main_traffic_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'articles.main_traffic_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('articles.main_traffic', '=', 1);
}
}
}
}
});
$builder -> orWhere(function($query) use ($condition){
self::queryCondition($query, 'articles.sub_traffic1_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'articles.sub_traffic1_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'articles.sub_traffic1', "=", $condition->access3 ?? []);
foreach($condition->access3 as $key => $access3) {
if($access3 != null){
$query->where('articles.mansion_id', "=", null);
}
if($access3 == 2) {
self::queryCondition($query, 'articles.sub_traffic1_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'articles.sub_traffic1_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('articles.sub_traffic1', '=', 1);
}
}
}
}
});
$builder -> orWhere(function($query) use ($condition){
self::queryCondition($query, 'articles.sub_traffic2_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'articles.sub_traffic2_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'articles.sub_traffic2', "=", $condition->access3 ?? []);
foreach($condition->access3 as $key => $access3) {
if($access3 != null){
$query->where('articles.mansion_id', "=", null);
}
if($access3 == 2) {
self::queryCondition($query, 'articles.sub_traffic2_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'articles.sub_traffic2_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('articles.sub_traffic2', '=', 1);
}
}
}
}
});
// Mansion data
$builder -> orWhere(function($query) use ($condition){
self::queryCondition($query, 'art2.main_traffic_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'art2.main_traffic_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'art2.main_traffic', "=", $condition->access3);
foreach($condition->access3 as $key => $access3) {
if($access3 == 2) {
self::queryCondition($query, 'art2.main_traffic_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'art2.main_traffic_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('art2.main_traffic', '=', 1);
}
}
}
}
});
$builder -> orWhere(function($query) use ($condition){
self::queryCondition($query, 'art2.sub_traffic1_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'art2.sub_traffic1_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'art2.sub_traffic1', "=", $condition->access3);
foreach($condition->access3 as $key => $access3) {
if($access3 == 2) {
self::queryCondition($query, 'art2.sub_traffic1_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'art2.sub_traffic1_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('art2.sub_traffic1', '=', 1);
}
}
}
}
});
$builder -> orWhere(function($query) use ($condition){
self::queryCondition($query, 'art2.sub_traffic2_line_id', "=", $condition->access1 ?? []);
self::queryCondition($query, 'art2.sub_traffic2_station_id', "=", $condition->access2 ?? []);
if (isset($condition->access3)) {
self::queryCondition($query, 'art2.sub_traffic2', "=", $condition->access3);
foreach($condition->access3 as $key => $access3) {
if($access3 == 2) {
self::queryCondition($query, 'art2.sub_traffic2_bus_walk', "<=", $condition->walk ?? []);
} else {
self::queryCondition($query, 'art2.sub_traffic2_time', "<=", $condition->walk ?? []);
if (isset($condition->walk[$key]) && !empty($condition->walk[$key])) {
$query->where('art2.sub_traffic2', '=', 1);
}
}
}
}
});
});
// 学校区（小学校）
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->section_from)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->section_from as $section_from) {
if ($section_from != null) {
$query->orWhere('articles.section', '>=', $section_from);
}
}
});
}
if (isset($condition->section_to)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->section_to as $section_to) {
if ($section_to != null) {
$query->orWhere('articles.section', '<=', $section_to);
}
}
});
}
//リフォーム
if (isset($condition->exterior)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->exterior as $exterior) {
if ($exterior != null) {
$article->where(function ($query2) use ($article, $condition) {
foreach ($condition->reform_info as $reform_info) {
$query2->orWhere('articles.exterior', '=', $reform_info);
if($reform_info != 1){
$article->whereRaw(self::getQueryYearMonthRaw($condition, 'exterior_year', 'exterior_month'));
}
}
});
}
}
});
}
if (isset($condition->interior)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->interior as $interior) {
if ($interior != null) {
$article->where(function ($query2) use ($article, $condition) {
foreach ($condition->reform_info as $reform_info) {
$query2->orWhere('articles.interior', '=', $reform_info);
if($reform_info != 1){
$article->whereRaw(self::getQueryYearMonthRaw($condition, 'interior_year', 'interior_month'));
}
}
});
}
}
});
}
// 延べ床面積(下限)
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);
}
}
});
}
// 間取り a~b
if (isset($condition->floor_plan_from)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->floor_plan_from as $floor_plan_from) {
if ($floor_plan_from != null) {
$query->orWhere('articles.floor_plan', '>=', $floor_plan_from);
}
}
});
}
if (isset($condition->floor_plan_to)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->floor_plan_to as $floor_plan_to) {
if ($floor_plan_to != null) {
$query->orWhere('articles.floor_plan', '<=', $floor_plan_to);
}
}
});
}
// 物件名
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) && isset($condition->years_to) &&
is_array($condition->years_from) && is_array($condition->years_to) &&
count($condition->years_from) > 0 && count($condition->years_to) > 0
) {
$year_from = array_first($condition->years_from);
$year_to = array_first($condition->years_to);
if (!empty($year_to) && !empty($year_from)) {
$year_from = date('Y', strtotime("-{$year_from} years"));
$year_to = date('Y', strtotime("-{$year_to} years"));
$article->whereBetween('articles.age_year', [$year_to, $year_from]);
} else if (!empty($year_from)) {
$year_from = date('Y', strtotime("-{$year_from} years"));
$article->where('articles.age_year', '<=', $year_from);
} else if (!empty($year_to)) {
$year_to = date('Y', strtotime("-{$year_to} years"));
$article->where('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->status)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->status as $status) {
if ($status != null) {
$query->orWhere('articles.status', '=', $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->whereabouts_from)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->whereabouts_from as $whereabout_from) {
if ($whereabout_from != null) {
$query->orWhere('articles.whereabouts', '>=', $whereabout_from);
}
}
});
}
if (isset($condition->whereabouts_to)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->whereabouts_to as $whereabout_to) {
if ($whereabout_to != null) {
$query->orWhere('articles.whereabouts', '<=', $whereabout_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->where('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->where('articles.land_condition_not', '=', 1);
$query->where('articles.land_condition', '=', 1);
}
}
});
if (isset($condition->land_condition)) {
foreach ($condition->land_condition as $land_condition) {
if ($land_condition == 0 && $land_condition != null) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->remove_condition as $remove_condition) {
if ($remove_condition != null) {
$query->where('articles.land_condition_not', '=', 1);
$query->where('articles.land_condition', '=', 1);
$query->where('articles.land_condition', '=', 0);
}
}
});
}
}
}
}
// 接道方向
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.balcony_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.balcony_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) {
$query->orWhere('articles.pet', '=', $is_pet);
$query->orWhere('art2.pet', '=', $is_pet);
}
}
});
}
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('articles.pet_count', '>=', $pet_num);
$query->orWhere('art2.pet_count', '>=', $pet_num);
}
}
});
}
if (isset($condition->total_unit_from)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->total_unit_from as $total_unit) {
if ($total_unit != null) {
$query->orWhere('articles.total_unit', '>=', $total_unit);
}
}
});
}
if (isset($condition->total_unit_to)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->total_unit_to as $total_unit) {
if ($total_unit != null) {
$query->orWhere('articles.total_unit', '<=', $total_unit);
}
}
});
}
if (isset($condition->shared)) {
$sharedList = $condition->shared;
foreach ($sharedList as $key => $shared) {
if (empty($shared)) {
unset($sharedList[$key]);
}
}
if (!empty($sharedList)) {
$article->whereRaw('(SELECT count(*) FROM `relation_shareds` where `relation_shareds`.`article_id` = `articles`.`building_id` and `relation_shareds`.`item_id` in (' . implode(",", $sharedList) . ')) = ' . count($sharedList));
}
}
// 事業主
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) {
$registed_to = date('Y-m-d', strtotime($registed_to . "+1 days"));
$query->orWhere('articles.created_at', '<', $registed_to);
}
}
});
}
if (isset($condition->sales_company)) {
$article->where(function ($query) use ($article, $condition) {
foreach ($condition->sales_company as $sales_company) {
if ($sales_company != null) {
$query->orWhere('articles.sales_company', "LIKE", "%{$sales_company}%");
$query->orWhere('art2.sales_company', "LIKE", "%{$sales_company}%");
}
}
});
}
// 物件確認日
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('articles.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('articles.conf_day', '<=', $checked_to);
}
}
});
}
// 価格変更日
if(isset($close) and $close == 1){
// 価格変更日
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('articles.close_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('articles.close_date', '<=', $changed_price_to);
}
}
});
}
}else{
// 価格変更日
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(function ($query) use ($article, $changed_price_from) {
$query->where('newTable.regist_date_last', '>=', $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(function ($query) use ($article, $changed_price_to) {
$query->where('newTable.regist_date_last', '<=', $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, $shop) {
foreach ($condition->suumo as $suumo) {
if($suumo != null){
if ($suumo == 1) {
$query->where('portal_status_articles.portal_type', '=', 1);
$query->where('portal_status_articles.portal_user', '=', $shop->suumo_id);
$query->whereIn('relation_portal_publics.status', [0, 1, 2]);
$query->where('relation_portal_publics.type', 1);
}
if($suumo == 2){
$query->where('portal_status_articles.portal_type', '=', 1);
$query->where('portal_status_articles.portal_user', '=', $shop->suumo_id);
$query->whereIn('relation_portal_publics.status', [1, 2]);
$query->where('relation_portal_publics.type', 1);
}
if($suumo == 3){
$query->where('portal_status_articles.portal_type', '=', 1);
$query->where('portal_status_articles.portal_user', '=', $shop->suumo_id);
$query->where('relation_portal_publics.status', 0);
$query->where('relation_portal_publics.type', 1);
}
if($suumo == 4){
$query->whereNull('portal_status_articles.article_id');
}
}
}
});
}
if (isset($condition->homes)) {
$article->where(function ($query) use ($article, $condition, $shop) {
foreach ($condition->homes as $homes) {
if ($homes != null) {
if ($homes == 1) {
$query->where('portal_status_articles.portal_type', '=', 2);
$query->where('portal_status_articles.portal_user', '=', $shop->homes_id);
$query->whereIn('relation_portal_publics.status', [0, 1]);
$query->where('relation_portal_publics.type', 2);
}
if($homes == 2){
$query->where('portal_status_articles.portal_type', '=', 2);
$query->where('portal_status_articles.portal_user', '=', $shop->homes_id);
$query->where('relation_portal_publics.status', 1);
$query->where('relation_portal_publics.type', 2);
}
if($homes == 3){
$query->where('portal_status_articles.portal_type', '=', 2);
$query->where('portal_status_articles.portal_user', '=', $shop->homes_id);
$query->where('relation_portal_publics.status', 0);
$query->where('relation_portal_publics.type', 2);
}
if($homes == 4){
$query->whereNull('portal_status_articles.article_id');
}
}
}
});
}
if (isset($condition->athome)) {
$article->where(function ($query) use ($article, $condition, $shop) {
foreach ($condition->athome as $athome) {
if ($athome != null) {
if ($athome == 1) {
$query->where('portal_status_articles.portal_type', '=', 3);
$query->where('portal_status_articles.portal_user', '=', $shop->athome_id);
$query->whereIn('relation_portal_publics.status', [0, 1, 2, 3]);
$query->where('relation_portal_publics.type', 3);
}
if($athome == 2){
$query->where('portal_status_articles.portal_type', '=', 3);
$query->where('portal_status_articles.portal_user', '=', $shop->athome_id);
$query->whereIn('relation_portal_publics.status', [1, 2, 3]);
$query->where('relation_portal_publics.type', 3);
}
if($athome == 3){
$query->where('portal_status_articles.portal_type', '=', 3);
$query->where('portal_status_articles.portal_user', '=', $shop->athome_id);
$query->where('relation_portal_publics.status', 0);
$query->where('relation_portal_publics.type', 3);
}
if($athome == 4){
$query->whereNull('portal_status_articles.article_id');
}
}
}
});
}
// 業者名
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->orwhere(function ($query1) use ($condition) {
$query1->where('articles.status', '!=', 4)
->where('articles.status', '!=', 5);
});
$query->orwhere(function ($query1) use ($condition) {
$query1->where('articles.status', null);
});
});
}
}
if (isset($condition->hide_items)) {
$items = $condition->hide_items;
$article->whereNotIn('articles.building_id', $items);
}
// only select these items
if (isset($condition->only_items)) {
$items = explode(',', $condition->only_items);
$article->whereIn('articles.building_id', $items)->orderByRaw("FIELD(articles.building_id, $condition->only_items)");
}
return $article;
}
public function scopeRoom($query)
{
return $query->Where('property', 6)->orWhere('property', 7);
}
public function getMansion()
{
$user = \auth()->user();
$query = Article::where("building_id", $this->attributes["mansion_id"]);
//->where("company_id", \auth()->user()->company_id)
//->first();
if ($user){
$query->where("company_id", $user->company_id);
}
return $query->first();
}
public function mansion()
{
return $this->belongsTo(Article::class, "mansion_id", "building_id")->where("company_id", \auth()->user()->company_id);
}
public static function queryCondition($query, $field, $condition, $arr)
{
if (isset($arr)) {
if (is_array($arr) && !empty($arr)) {
foreach ($arr as $value) {
if ($value != null) {
$query->Where($field, $condition, $value);
}
}
}
}
}
public function getEventCategoryName()
{
$names = ['未選択', '現地見学会', '現地案内会', '現地販売会', 'オープンハウス', 'オープンルーム'];
return isset($names[$this->event_category]) ? $names[$this->event_category] : $names[0];
}
public function getEventScheduleName()
{
$names = ['未選択', '毎週土日祝', '毎週土日', '日時指定', '期間限定', '公開中'];
$text = isset($names[$this->event_schedule]) ? $names[$this->event_schedule] : $names[0];
if ($this->event_schedule == 3) {
$eventDays = RelationEventDay::where('article_id', $this->building_id)
->pluck('event_day');
$text .= ' ' . $eventDays->implode(' ');
} else if ($this->event_schedule == 4) {
$text .= ' ' . ($this->from_event ?? '-');
$text .= '〜' . ($this->to_event ?? '-');
}
return $text;
}
public function getInteriorText()
{
$radioNames = ['無', '済', '完了予定'];
$text = isset($radioNames[($this->interior) - 1]) ? $radioNames[($this->interior) - 1] : $radioNames[0];
if ($this->interior == 2 || $this->interior == 3) {
$text = ($this->interior_year) . '年' . ($this->interior_month) . '月';
$text .= $radioNames[($this->interior) - 1];
$interior_place = RelationInterior::where('article_id', $this->building_id)->get();
$checkNames = [1 => 'キッチン', '浴室', 'トイレ', '壁', '床', '全室', 'その他'];
$textArr = [];
if ($interior_place) {
foreach ($interior_place as $v) {
if (!isset($checkNames[$v->item_id])) continue;
if ($v->item_id == 7) {
$textArr[] = $checkNames[$v->item_id] . '：' . $this->interior_text;
} else {
$textArr[] = $checkNames[$v->item_id];
}
}
$text .= '（' .implode(', ', $textArr).'）';
}
}
return $text;
}
public function price_histories(){
return $this->hasMany(ChangeHistory::class,'article_id','building_id');
}
public function getExteriorHTML()
{
$exteriorNames = ['', '', '済', '完了予定'];
$exterior = $this->attributes["exterior"];
$exteriorYear = $this->attributes["exterior_year"];
$exteriorMonth = $this->attributes["exterior_month"];
$exteriorTextOther = $this->attributes["exterior_text"];
$exteriorPlaces = $this->exteriors;
$exteriorHasMore = [2, 3];
if (in_array($exterior, $exteriorHasMore)) {
$exteriorDate = "{$exteriorYear}年{$exteriorMonth}月";
$exteriorText = ("{$exteriorDate} " . ($exteriorNames[$exterior] ?? "") . "");
$exteriorText .= "（";
$placeNames = ["", "外壁", "屋根", "その他"];
//$exteriorText .= "）";
foreach ($exteriorPlaces as $key => $place) {
$lastLoop = ($key === count($exteriorPlaces) - 1);
$placeName = $placeNames[$place->item_id] ?? "";
if ($place->item_id == 3) {
$placeName .= "：{$exteriorTextOther}";
}
if ($lastLoop) {
$exteriorText .= $placeName."）";
break;
}
$exteriorText .= "{$placeName}、";
}
} else {
$exteriorText = "無";
}
return $exteriorText;
}
public function getInteriorHTML()
{
$interiorNames = ['', '', '済', '完了予定'];
$interior = $this->attributes["interior"];
$interiorYear = $this->attributes["interior_year"];
$interiorMonth = $this->attributes["interior_month"];
$interiorTextOther = $this->attributes["interior_text"];
$interiorPlaces = $this->interiors;
$interiorHasMore = [2, 3];
if (in_array($interior, $interiorHasMore)) {
$interiorDate = "{$interiorYear}年{$interiorMonth}月";
$interiorText = ("{$interiorDate} " . ($interiorNames[$interior] ?? "") . "");
$interiorText .= "（";
$placeNames = ["", "キッチン", "浴室", "トイレ", "壁", "床", "全室", "その他"];
foreach ($interiorPlaces as $key => $place) {
$lastLoop = ($key === count($interiorPlaces) - 1);
$placeName = $placeNames[$place->item_id] ?? "";
if ($place->item_id == 7) {
$placeName .= "：{$interiorTextOther}";
}
if ($lastLoop) {
$interiorText .= $placeName."）";
break;
}
$interiorText .= "{$placeName}、";
}
} else {
$interiorText = "無";
}
if(empty($text)){$text='-';}
return $interiorText;
}
public function getParkingName()
{
$names = ['未設定', '無', '掘込車庫', '車庫', '地下車庫', 'カースペース', 'カーポート', '２台以上'];
return isset($names[$this->parking]) ? $names[$this->parking] : $names[0];
}
public function getParkingText()
{
$parkingNames = [
'-', '無', '駐車場空無', '駐車場空有', '分譲駐車場(必購入)', '分譲駐車場(任意購入)', '専用使用権付駐車場'
];
$costRanges = [
1 => ' ', '〜', '・'
];
$text = Arr::get($parkingNames, $this->parking ?? '');
if ($this->parking == 3) {
$text .= ' 料金：';
if ($this->parking_yes == 1) {
$parking_yes_cost_date = !empty($this->parking_yes_cost_date) ? ' ' . date('(Y.m.d現在)', strtotime($this->parking_yes_cost_date)) : "";
$text .= ($this->parking_yes_cost_from ?? '-') . '円';
if (!empty($this->parking_yes_cost_to)) {
$text .= Arr::get($costRanges, $this->parking_yes_cost_range ?? '');
$text .= $this->parking_yes_cost_to .'円';
}
$text .= '／' . ($this->parking_yes_cost_unit == 1 ? '月' : '年');
$text .= $parking_yes_cost_date;
} else {
$text .= '無';
}
} else if ($this->parking == 4) {
$text .= ' 分譲価格：' . ($this->parking_require_cost ?? '-') . '円（物件価格に含む） ';
$text .= '管理費：';
if ($this->parking_require_management == 1) {
$text .= ($this->parking_require_management_cost ?? '-') . '円';
$text .= '/ ' . ($this->parking_require_management_unit == 1 ? '月' : '年');
} else {
$text .= '無';
}
$text .= ' 修繕積立金：';
if ($this->parking_require_repair == 1) {
$text .= ($this->parking_require_repair_cost ?? '-') . '円';
$text .= '/ ' . ($this->parking_require_repair_unit == 1 ? '月' : '年');
} else {
$text .= '無';
}
$text .= ' 修繕積立基金：';
if ($this->parking_require_fund == 1) {
$text .= ($this->parking_require_fund_cost ?? '-') . '円';
$text .= '/ 一括';
} else {
$text .= '無';
}
} else if ($this->parking == 5) {
$text .= ' 分譲価格：';
$text .= ($this->parking_any_cost_from ?? '-') . '円';
$text .= Arr::get($costRanges, $this->parking_any_cost_range ?? '');
$text .= ($this->parking_any_cost_to ?? '-') . '円';
$text .= ' 管理費：';
if ($this->parking_any_management == 1) {
$text .= ($this->parking_any_management_cost ?? '-') . '円';
$text .= '/ ' . ($this->parking_any_management_unit == 1 ? '月' : '年');
} else {
$text .= '無';
}
$text .= ' 修繕積立金：';
if ($this->parking_any_repair == 1) {
$text .= ($this->parking_any_repair_cost ?? '-') . '円';
$text .= '/ ' . ($this->parking_any_repair_unit == 1 ? '月' : '年');
} else {
$text .= '無';
}
$text .= ' 修繕積立基金：';
if ($this->parking_any_fund == 1) {
$text .= ($this->parking_any_fund_cost ?? '-') . '円';
$text .= '/ 一括';
} else {
$text .= '無';
}
} else if ($this->parking == 6) {
$text .= ' 使用料：';
if ($this->parking_designated == 1) {
$text .= ($this->parking_designated_cost ?? '-') . '円';
$text .= '/ ' . ($this->parking_designated_unit == 1 ? '月' : '年');
if ($this->parking_designated_text) {
$text .= '（' . $this->parking_designated_text . '）';
}
} else {
$text .= '無';
}
}
return $text;
}
public function getParkingOutText()
{
$text = '';
$costRanges = [
1 => ' ', '〜', '・'
];
if ($this->parking_out == 1) {
$parking_out_cost_date = !empty($this->parking_out_cost_date) ? ' ' . date('(Y.m.d現在)', strtotime($this->parking_out_cost_date)) : "";
$text .= '有';
$text .= ' ' . ($this->parking_out_cost_from ?? '-') . '円';
$text .= Arr::get($costRanges, $this->parking_out_cost_range ?? '');
$text .= ($this->parking_out_cost_to ?? '-') . '円';
$text .= '／' . ($this->parking_out_cost_unit == 1 ? '月' : '年');
$text .= $parking_out_cost_date;
} elseif ($this->parking_out == 2) {
$text .= '無';
} else {
$text .= '-';
}
return $text;
}
public function getLanConditionName()
{
$names = ["条件なし", "建築条件付き", "未選択"];
return isset($names[$this->land_condition]) ? $names[$this->land_condition] : $names[0];
}
public function getGroundName()
{
$names = ["未選択", "宅地", "田", "畑", "山林", "雑種地", "原野", "その他"];
$text = isset($names[$this->ground]) ? $names[$this->ground] : $names[0];
if ($this->ground == 7) {
$text .= ': ' . $this->ground_text;
}
return $text;
}
function getRoadBurdenName()
{
$names = ["無", "有", "共有", "-"];
$text = isset($names[$this->road_burden]) ? $names[$this->road_burden] : $names[0];
if ($this->road_burden == 1) {
$text .= ': ' . ($this->road_burden_area) . 'm²';
} else if ($this->road_burden == 2) {
$text .= ': ' . ($this->road_burden_area) . 'm² 持分' . ($this->road_numerator) . '/' . ($this->road_denominator) . '全体';
}
return $text;
}
function getRoadBurdenNamePrint(){
$names = ["無", "有", "共有", "-"];
$text = isset($names[$this->road_burden]) ? $names[$this->road_burden] : $names[0];
if ($this->road_burden == 1) {
$text .= ': ' . ($this->road_burden_area) . 'm²';
} else if ($this->road_burden == 2) {
$text .= ': ' . ($this->road_burden_area) . 'm²'; //new ver
}
return $text;
}
function getEasement()
{
$names = ["-", "無", "地役権", "通行地役権", "引水地役権", "眺望地役権", "通路賃借権"];
$easement = $this->attributes["easement"];
$text = isset($names[$easement]) ? $names[$easement] : $names[0];
if (!in_array($easement, [0, 1])) {
$text .= ": {$this->attributes["easement_area"]}m²";
}
return $text;
}
public function getLanDirectionName()
{
$text = '';
$names1 = ['', '北', '北東', '東', '南東', '南', '南西', '西', '北西'];
$names2 = ['', '公道', '私道'];
if ($this->land_direction1) {
if ($this->land_direction1) {
$text .= '向き：' . (isset($names1[$this->land_direction1]) ? $names1[$this->land_direction1] : $names1[0]) . '側';
}
if ($this->land_kind1) {
$text .= ' ' . (isset($names2[$this->land_kind1]) ? $names2[$this->land_kind1] : $names2[0]);
}
if ($this->road_width1) {
$text .= ' 道路幅約：' . ($this->road_width1) . 'm';
}
if ($this->frontage1) {
$text .= ' 間口約：' . ($this->frontage1) . 'm';
}
}
if ($this->land_direction2) {
$text .= '
';
if ($this->land_direction2) {
$text .= '向き：' . (isset($names1[$this->land_direction2]) ? $names1[$this->land_direction2] : $names1[0]) . '側';
}
if ($this->land_kind2) {
$text .= ' ' . (isset($names2[$this->land_kind2]) ? $names2[$this->land_kind2] : $names2[0]);
}
if ($this->road_width2) {
$text .= ' 道路幅約：' . ($this->road_width2) . 'm';
}
if ($this->frontage2) {
$text .= ' 間口約：' . ($this->frontage2) . 'm';
}
}
if ($this->land_direction3) {
if ($this->land_direction2) {
$text .= '
向き: ' . (isset($names1[$this->land_direction3]) ? $names1[$this->land_direction3] : $names1[0]) . '側';
}
if ($this->land_kind3) {
$text .= ' ' . (isset($names2[$this->land_kind3]) ? $names2[$this->land_kind3] : $names2[0]);
}
if ($this->road_width3) {
$text .= ' 道路幅約：' . ($this->road_width3) . 'm';
}
if ($this->frontage3) {
$text .= ' 間口約：' . ($this->frontage3) . 'm';
}
}
return $text;
}
public function getLanDirectionNameShort()
{
$text = '';
$names1 = ['', '北', '北東', '東', '南東', '南', '南西', '西', '北西'];
$names2 = ['', '公道', '私道'];
if ($this->land_direction1) {
if ($this->land_direction1) {
$text .= (isset($names1[$this->land_direction1]) ? $names1[$this->land_direction1] : $names1[0]) . '側';
}
if ($this->land_kind1) {
$text .= ' ' . (isset($names2[$this->land_kind1]) ? $names2[$this->land_kind1] : $names2[0]);
}
if ($this->road_width1) {
$text .= ' 道路幅約' . ($this->road_width1) . 'm';
}
if ($this->frontage1) {
$text .= ' 間口約' . ($this->frontage1) . 'm';
}
}
if ($this->land_direction2) {
$text .= '
';
if ($this->land_direction2) {
$text .= (isset($names1[$this->land_direction2]) ? $names1[$this->land_direction2] : $names1[0]) . '側';
}
if ($this->land_kind2) {
$text .= ' ' . (isset($names2[$this->land_kind2]) ? $names2[$this->land_kind2] : $names2[0]);
}
if ($this->road_width2) {
$text .= ' 道路幅約' . ($this->road_width2) . 'm';
}
if ($this->frontage2) {
$text .= ' 間口約' . ($this->frontage2) . 'm';
}
}
if ($this->land_direction3) {
$text .= ' 他';
}
return $text;
}
public function getSewerageName()
{
$names = ["-", "本下水", "集中浄化槽", "個別浄化槽"];
return isset($names[$this->sewerage]) ? $names[$this->sewerage] : $names[0];
}
public function getGasName()
{
$names = ["-", "都市ガス", "集中ＬＰＧ", "個別ＬＰＧ", "オール電化"];
return $names[$this->attributes["gas"]] ?? "-";
}
public function getSetPropName()
{
$names = ["-", "不要", "要", "済"];
$text = isset($names[$this->set]) ? $names[$this->set] : $names[0];
if (!in_array($this->set, [0, 1])) {
$text .= ': ' . ($this->set_area) . 'm² ';
}
return $text;
}
public function getOtherRestriction()
{
$names = [
"", "一部都市計画道路", "一部協定通路", "日影制限有", "隅切り有",
"接道と段差有", "敷地内段差有", "壁面後退有", "建築協定有",
"崖上につき建築制限有", "崖下につき建築制限有", "不整形地"
];
$laws = RelationOtherrestriction::where('article_id', $this->building_id)->get();
$namesVal = [];
foreach ($laws as $val) {
if (isset($names[$val->item_id])) {
$namesVal[] = $names[$val->item_id];
}
}
$text = implode(', ', $namesVal);
if ($text)
$text .= '
';
$sleNames = [
"選択してください。", '建築基準法43条但書許可要。事前相談による一次決済取得', '建築基準法43条但書許可要。一括許可（包括）同意基準に適合',
'建築基準法43条但書許可要', '再建築不可 43条但書の許可取得により再建築可'
];
if (!empty($this->other_reason)) {
// $text .= (isset($sleNames[$this->other_reason]) ? $sleNames[$this->other_reason] : $sleNames[0]) . '
';
}
if ($this->other_comment) {
$text .= $this->other_comment;
}
return $text;
}
public function getLowRestrictionName()
{
$names = ['', "文化財保護法", "古都保存法", '景観法', '密集市街地整備法', '航空法', '河川法', '砂防法', '農地法届出要', '安全条例', '宅地造成工事規制区域', '急傾斜地崩壊危険区域', '高度地区', '高度利用地区', '中高層階住居専用地区', '高層住居誘導地区', '防火地域', '準防火地域', '風致地区', '景観地区', '準景観地区', '観光地区', '歴史風土保存地区', '伝統的建造物群保存地区', '特定街区', '特別用途制限地域', '文教地区', '都市再生特別地区', '特別緑地保全地区', '高さ最高限度有', '高さ最低限度有', '建ぺい率最低限度有', '容積率最低限度有', '敷地面積最高限度有', '敷地面積最低限度有', '建物面積最高限度有', '建物面積最低限度有'];
$laws = RelationLawrestriction::where('article_id', $this->building_id)->get();
$namesVal = [];
foreach ($laws as $val) {
if (isset($names[$val->item_id])) {
$namesVal[] = $names[$val->item_id];
}
}
return implode('
', $namesVal);
}
public function getBuildingCondition()
{
$text = '';
if ($this->building_condition1) {
$text = 'うち地下室：' . ($this->building_condition_area1) . 'm²';
}
if ($this->building_condition2) {
if(isset($text)){$text.='／';}
$text .= 'うち１F車庫：' . ($this->building_condition_area2) . 'm²';
}
if ($this->building_condition3) {
if(isset($text)){$text.='／';}
$text .= 'うち地下車庫：' . ($this->building_condition_area3) . 'm²';
}
if ($this->building_condition4) {
if(isset($text)){$text.='／';}
$text .= 'うち居住用途以外：';
if ($this->building_condition_select==1){$text .='店舗';}
elseif ($this->building_condition_select==2){$text .='事務所';}
elseif ($this->building_condition_select==3){$text .='倉庫';}
elseif ($this->building_condition_select==4){$text .='工場';}
elseif ($this->building_condition_select==5){$text .='診療所';}
elseif ($this->building_condition_select==6){$text .='賃貸';}
elseif ($this->building_condition_select==9){$text .='その他';}
}
return $text ? $text : '-';
}
public function getFloorPlan()
{
$text = $this->floor_plan;
$names = ['', 'DK', 'LDK', 'R', 'K', 'SK', 'SDK', 'LK', 'SLK', 'SLDK'];
return $text . (isset($names[$this->floor_plan_type]) ? $names[$this->floor_plan_type] : '');
}
public function getInvestmentPerformance()
{
if ($this->investment_status == 1) {
return "賃借人なし";
}
$text = "";
if ($this->investment_performance) {
$text = '賃借人有（オーナーチェンジ） ';
}
if ($this->investment_performance) {
$text .= "
".'投資用収入実績：' . ($this->investment_performance) . '円／' . ($this->investment_performance_unit == 1 ? '月' : '年');
}
if ($this->investment_interest) {
$text .= "
".'投資用年利回り：' . ($this->investment_interest) . '％';
}
return $text;
}
public function getUserMethod()
{
$names = ['-', "マンション", "アパート", "ビル", '店舗', '事務所', '寮・社宅', '工場', '倉庫', 'その他'];
$text = isset($names[$this->use_method]) ? $names[$this->use_method] : '';
if ($this->use_method == 7) {
$text .= ' :' . ($this->use_method_text);
}
return $text;
}
public function getLandRightPeriod()
{
$names = ['-', '所有権', '借地権のみ', '所有権・借地権混在'];
if (!$this->land_right) return '-';
if ($this->land_right == 0 || $this->land_right == 1) {
return $names[$this->land_right];
}
$text = ($names[$this->land_right] ?? '-') . '
';
$kindNames = ['', '旧法賃借権', '普通賃借権', '一般定期賃借権', '建物譲渡特約付き定期賃借権', '旧法地上権', '普通地上権', '一般定期地上権', '建物譲渡特約付き定期地上権'];
if ($this->leasehold_kind) {
$text .= '借地権種別: ' . ($kindNames[$this->leasehold_kind] ?? '-') . '
';
}
if ($this->land_right == 3) {
$text .= '借地権割合: ' . $this->leasehold_rate . '%
';
}
$rentUnitNames = ['', '月', '年', '一括'];
$text .= '地代:' . (!$this->land_rent ? '無' : ('有 ' . $this->land_rent_val . '/' . ($rentUnitNames[$this->land_rent_unit] ?? '-') . '
'));
$text .= '借地期間: ' . ($this->leasehold_period == 1 ? '残存' : '新規');
$text .= ' ' . ($this->leasehold_period_year) . '年 ' . ($this->leasehold_period_month) . 'ヶ月
';
$text .= '権利金: ' . (!$this->right_cost ? '無' : ('有 ' . $this->right_cost_val . '万円') . '
');
$text .= '保証金: ' . (!$this->deposit_cost ? '無' : ('有 ' . $this->deposit_cost_val . '万円') . '
');
$text .= '敷金: ' . (!$this->security_deposit_cost ? '無' : ('有 ' . $this->security_deposit_cost_val . '万円') . '
');
if (in_array($this->leasehold_kind, [7, 8])) {
if ($this->regular_leased_registration) {
$leased = ['-', '可', '不可', '相談'];
$text .= '定期借地権設定登記: ' . ($leased[$this->regular_leased_registration] ?? '-') . '
';
}
if ($this->regular_leased_season) {
$names = ['', '一定期間毎', '事前協議により決定'];
$text .= '定期借地権賃料改定時期: ' . ($names[$this->regular_leased_season] ?? '-') . '/' . ($this->regular_leased_season == 1 ? ($this->regular_leased_season_nen . '年毎') : '') . '
';
}
if ($this->regular_leased_cost) {
$leased = ['-', '公式', '事前協議により決定'];
$text .= '定期借地権賃料改定額: ' . ($leased[$this->regular_leased_cost] ?? '-') . '
';
}
if ($this->regular_leased_transfer) {
$names = ['-', '可', '不可'];
$text .= '定期借地権譲渡転貸:
';
$text .= '譲渡可否: ' . ($names[$this->regular_leased_transfer] ?? '-') . '
';
if ($this->regular_leased_transfer == 1) {
$names = ['-', '承諾不要', '通知要', '承諾要'];
$text .= '- 承諾要否・方法: ' . ($names[$this->regular_leased_transfer_method] ?? '-') . '
';
$names = ['-', '要', '不要', '未定'];
$text .= '- 承諾料要否: ' . ($names[$this->regular_leased_transfer_necessity] ?? '-') . '
';
$names = ['-', '地主', '転貸主'];
$text .= '- 承諾者: ' . ($names[$this->regular_leased_transfer_consent] ?? '-') . '
';
}
}
}
return $text;
}
public function getClassficationContent()
{
$text = $this->classfication == 1 ? '居住用' : '事業用';
$names = ['', 'マンション', 'アパート ビル', 'ビル', '店舗', '事務所', '寮・社宅', '工場', '倉庫', 'その他'];
if ($this->classfication == 2 && $this->use_method != null) {
$text .= ("
" . (isset($names[$this->use_method]) ? $names[$this->use_method] : ''));
if (!empty($this->use_method_text)) {
$text .= ' / ' . $this->use_method_text;
}
}
return $text;
}
public function getCityPlanName()
{
$cp = MstCityPlan::where('code', '=', $this->attributes["city_plan"])->first();
$cpName = !empty($cp) ? $cp->name : "-";
return $cpName;
}
public function getCityPlanReasonName()
{
$cpr = MstCityPlanReason::where("code", $this->attributes["city_plan_reason"])->first();
$cprName = !empty($cpr) ? $cpr->name : "-";
return $cprName;
}
public function isPast()
{
$ageYear = $this->attributes["age_year"];
$ageMonth = $this->attributes["age_month"];
if ($this->attributes["age_year"] != null) {
$dateAge = Carbon::parse("{$ageYear}-{$ageMonth}-01");
return $dateAge->isPast();
} else {
return false;
}
}
public function relationPortalPhotos()
{
return $this->hasMany(RelationPortalPhoto::class, "article_id", "building_id")->orderByRaw("case when `order` is null then 1 else 0 end, `order`")->where("company_id", \auth()->user()->company_id);
}
public function portalHomesPanoramas()
{
return $this->hasMany(PortalHomesPanorama::class, "article_id", "building_id")->orderByRaw("case when `order` is null then 1 else 0 end, `order`")->where("company_id", \auth()->user()->company_id);
}
public function relationPortalFacility()
{
return $this->hasMany(RelationPortalFacility::class, "article_id", "building_id")->orderByRaw("case when `order` is null then 1 else 0 end, `order`")->where("company_id", \auth()->user()->company_id);
}
function facility() {
return $this->belongsToMany(Facility::class, 'relation_article_facilities','article_id', 'facility_id', 'building_id', 'id')->get();
}
function portalPublic() {
return $this->hasMany(RelationPortalPublic::class,'article_id', 'building_id')->orderByDesc('created_at')->get();
}
function portalStatus() {
return $this->hasMany(PortalStatusArticle::class,'article_id', 'building_id')->get();
}
private static function getValueOfSearchCondition($condition, $field, $default = null)
{
foreach ($condition->{$field} as $value) {
if ($value != null) {
return $value;
}
}
return $default;
}
private static function getQueryYearMonthRaw($condition, $fieldYear, $fieldMonth): string
{
$reformYearList = config('const.REFORM_YEAR', []);
$reformMonthList = config('const.MONTH', []);
$reformYearFrom = (int)self::getValueOfSearchCondition($condition, 'reform_year_from', array_get(array_first($reformYearList), 'code'));
$reformYearTo = (int)self::getValueOfSearchCondition($condition, 'reform_year_to', array_get(array_last($reformYearList), 'code'));
$reformMonthFrom = (int)self::getValueOfSearchCondition($condition, 'reform_month_from', array_get(array_first($reformMonthList), 'code'));
$reformMonthTo = (int)self::getValueOfSearchCondition($condition, 'reform_month_to', array_get(array_last($reformMonthList), 'code'));
$reformYearMonthFrom = $reformYearFrom . ($reformMonthFrom < 10 ? '0' : '') . $reformMonthFrom;
$reformYearMonthTo = $reformYearTo . ($reformMonthTo < 10 ? '0' : '') . $reformMonthTo;
$interiorTimeField = 'DATE_FORMAT(CONCAT(articles.'.$fieldYear.',"-", articles.'.$fieldMonth.', "-01"), "%Y%m")';
return "($interiorTimeField >= '$reformYearMonthFrom') AND ($interiorTimeField <= '$reformYearMonthTo')";
}
}