namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\File;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\PropertiesUnitsExport;
use App\Exports\PropertiesBuildingsExport;
use Illuminate\Filesystem\Filesystem;
use Barryvdh\DomPDF\Facade as PDF;
use Spatie\LaravelImageOptimizer\Facades\ImageOptimizer;
use Illuminate\Http\Request;
use App\Http\Resources\PropertiesCollection;
use Carbon\Carbon;
use App\Models\Proposal;
use App\Models\Person;
use App\Models\Company;
use App\Models\User;
use Tymon\JWTAuth\Facades\JWTAuth;;
use App\Models\Location;
use App\Models\Overwrite;
use App\Models\Offer;
use App\Models\Park;
use App\Models\Building;
use App\Models\GroupItem;
use App\Models\Tenant;
use App\Models\Unit;
use App\Models\Changelog;
use App\Models\UnitCombination;
use Illuminate\Support\Facades\Auth;
class PropertiesController extends Controller
{
protected $user;
private $per_page = 15;
public function __construct()
{
$this->user = JWTAuth::parseToken()->authenticate();
}
public function index()
{
return new PropertiesCollection(Building::with('location')->with('park')->with('units')->orderBy('id', 'desc')->paginate(2));
}
public function all(Request $request)
{
$first = true;
$query = '';
$filters = (array) $request->post();
foreach ($filters as $filter) {
if ($filter['type'] == 'select') {
if ($filter['id'] != 'status') {
for ($j = 0; $j < count($filter["selected_values"]); $j++) {
$filter["selected_values"][$j] = strtolower($filter["selected_values"][$j]);
}
if (count($filter["selected_values"]) > 0 && !in_array('all', $filter["selected_values"])) {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
if($filter["id"] == "developer_id") {
$query .= 'buildings.' . $filter["id"] . ' IN (';
$query .= ' SELECT id FROM companies WHERE name IN (';
for ($i = 0; $i < count($filter["selected_values"]); $i++) {
$query .= "'" . $filter["selected_values"][$i] . "'";
if ($i < count($filter["selected_values"]) - 1) {
$query .= ', ';
}
}
$query .= '))';
} else if($filter["id"] == "transaction_type") {
$query .= 'buildings.transaction_type IN (';
for ($i = 0; $i < count($filter["selected_values"]); $i++) {
if($filter["selected_values"][$i] == 'both') {
$query .= "'lease', 'sale', 'both'";
} else if($filter["selected_values"][$i] == 'lease') {
$query .= "'lease', 'both'";
} else {
$query .= "'sale', 'both'";
}
if ($i < count($filter["selected_values"]) - 1) {
$query .= ', ';
}
}
$query .= ')';
} else {
$query .= 'buildings.' . $filter["id"] . ' IN (';
for ($i = 0; $i < count($filter["selected_values"]); $i++) {
$query .= "'" . $filter["selected_values"][$i] . "'";
if ($i < count($filter["selected_values"]) - 1) {
$query .= ', ';
}
}
$query .= ')';
}
}
} else {
$status_filter = $filter;
}
}
if ($filter['type'] == 'range') {
if ($filter["min"] || $filter["max"]) {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
}
$margin_error = 0.15;
if($filter['id'] == 'warehouse_smart_sqm') {
if($filter["min"] && $filter["max"]) {
$min = intval($filter["min"]);
$max = intval($filter["max"]);
$search_min = $min; //$min - $margin_error * $min;
$search_max = $max; //$max + $margin_error * $max;
// $query .= ' buildings.id IN ( SELECT building_id from unit_combinations where (sum_min >= ' . $search_min . ' AND sum_min <= ' . $search_max . ') OR (sum_max >= ' . $search_min . ' AND sum_max <= ' . $search_max . ') group by building_id) ';
$query .= ' buildings.id IN ( SELECT building_id from unit_combinations where sum_max >= ' . $search_min . ' AND sum_min <= ' . $search_max . ' group by building_id)';
}
if ($filter["min"] && !$filter["max"]) {
$min = intval($filter["min"]);
$search_min = $min; //$min - $margin_error * $min;
$query .= ' buildings.id IN ( SELECT building_id from unit_combinations where sum_min >= ' . $search_min . ' GROUP BY building_id) ';
}
if (!$filter["min"] && $filter["max"]) {
$max = intval($filter["max"]);
$search_max = $max; //$max + $margin_error * $max;
$query .= ' buildings.id IN ( SELECT building_id from unit_combinations where sum_max <= ' . $search_max . ' GROUP BY building_id) ';
}
} else if ($filter['id'] == 'total_number_sqm') {
if ($filter["min"] && $filter["max"]) {
$min = intval($filter["min"]);
$max = intval($filter["max"]);
$new_min = $min; //$min - $margin_error * $min;
$new_max = $max; //$max + $margin_error * $max;
$query .= 'buildings.' . $filter["id"] . ' BETWEEN ' . $new_min . ' AND ' . $new_max . ' ';
}
if ($filter["min"] && !$filter["max"]) {
$min = intval($filter["min"]);
$new_min = $min; //$min - $margin_error * $min;
$query .= 'buildings.' . $filter["id"] . ' >= ' . $new_min;
}
if (!$filter["min"] && $filter["max"]) {
$max = intval($filter["max"]);
$new_max = $max; //$max + $margin_error * $max;
$query .= 'buildings.' . $filter["id"] . ' <= ' . $new_max;
}
} else if ($filter['id'] == 'headline_price') {
$query .= ' buildings.id IN ( SELECT building_id FROM units WHERE warehouse_rent_price';
if ($filter["min"] && $filter["max"]) {
$query .= ' BETWEEN ' . $filter["min"] . ' AND ' . $filter["max"] . ' ';
}
if ($filter["min"] && !$filter["max"]) {
$query .= ' >= ' . $filter["min"];
}
if (!$filter["min"] && $filter["max"]) {
$query .= ' <= ' . $filter["max"];
}
$query .= ') ';
} else {
$query .= 'buildings.' . $filter["id"];
if ($filter["min"] && $filter["max"]) {
$query .= ' BETWEEN ' . $filter["min"] . ' AND ' . $filter["max"] . ' ';
}
if ($filter["min"] && !$filter["max"]) {
$query .= ' >= ' . $filter["min"];
}
if (!$filter["min"] && $filter["max"]) {
$query .= ' <= ' . $filter["max"];
}
}
}
if ($filter['type'] == 'date') {
if ($filter["min"] || $filter["max"]) {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
$query .= 'properties.' . $filter["id"];
}
if ($filter["min"] && $filter["max"]) {
$query .= ' BETWEEN ' . $filter["min"] . ' AND ' . $filter["max"] . ' ';
}
if ($filter["min"] && !$filter["max"]) {
$query .= ' >= ' . $filter["min"];
}
if (!$filter["min"] && $filter["max"]) {
$query .= ' <= ' . $filter["max"];
}
}
if ($filter['type'] == 'date_range !') {
if ($filter["min"] || $filter["max"]) {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
// $query .= $filter["id"];
$query .= 'buildings.id IN (SELECT building_id FROM units WHERE units.id NOT IN (SELECT unit_id FROM units_companies WHERE ';
}
if ($filter["min"] && $filter["max"]) {
if (preg_match('/^[0-9][0-9]\/[0-9][0-9][0-9][0-9]$/', $filter["min"]) && preg_match('/^[0-9][0-9]\/[0-9][0-9][0-9][0-9]$/', $filter["max"])) {
$min_dateMonthArray = explode('/', $filter["min"]);
$min_month = $min_dateMonthArray[0];
$min_year = $min_dateMonthArray[1];
$max_dateMonthArray = explode('/', $filter["max"]);
$max_month = $max_dateMonthArray[0];
$max_year = $max_dateMonthArray[1];
$min_date = $min_year . '-' . $min_month . '-01';
$max_date = $max_year . '-' . $max_month . '-01';
} else {
return response()->json(['error' => 'Unit "available between" invalid date format! (Should be MM/YYYY)'], 500);
}
$query .= ' (start_date BETWEEN "' . $min_date . '" AND "' . $max_date . '")
OR (end_date BETWEEN "' . $min_date . '" AND "' . $max_date . '")
OR (start_date <= "' . $min_date . '" AND end_date >= "' . $max_date . '") ';
}
if ($filter["min"] && !$filter["max"]) {
if (preg_match('/^[0-9][0-9]\/[0-9][0-9][0-9][0-9]$/', $filter["min"])) {
$min_dateMonthArray = explode('/', $filter["min"]);
$min_month = $min_dateMonthArray[0];
$min_year = $min_dateMonthArray[1];
$min_date = $min_year . '-' . $min_month . '-01';
} else {
return response()->json(['error' => 'Property "available from" invalid date format! (Should be MM/YYYY)'], 500);
}
$query .= ' >= "' . $min_date . '"';
}
if (!$filter["min"] && $filter["max"]) {
if (preg_match('/^[0-9][0-9]\/[0-9][0-9][0-9][0-9]$/', $filter["max"])) {
$max_dateMonthArray = explode('/', $filter["max"]);
$max_month = $max_dateMonthArray[0];
$max_year = $max_dateMonthArray[1];
$max_date = $max_year . '-' . $max_month . '-01';
} else {
return response()->json(['error' => 'Property "available from" invalid date format! (Should be MM/YYYY)'], 500);
}
$query .= ' <= "' . $max_date . '"';
}
if ($filter["min"] || $filter["max"]) {
$query .= ')) ';
}
// return response()->json(['error' => $query], 500);
}
if ($filter['type'] == 'location') {
if (count($filter["selected_values"]) > 0) {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
$query .= 'buildings.location_id' . ' IN (';
// Daca se cauta doar Bucuresti, sa afiseze si din N,S,W,E
// Daca se cauta doar Bucuresti N, sa afiseze si NE, NW si tot asa
$aditional_bucharest_ids = [];
$aditional_bucharest_area_ids = [];
$ok_bucharest = false;
foreach($filter["selected_ids"] as $l_id) {
$loc = Location::find($l_id);
if(!is_null($loc)) {
if($loc->nume == 'Bucharest') {
$aditional_bucharest_ids = Location::whereRaw('id != ' . $l_id . ' AND (judet = "Bucuresti" OR judet = "Ilfov" )')->get()->pluck('id')->toArray();
$ok_bucharest = true;
break;
}
}
}
if(!$ok_bucharest) {
foreach($filter["selected_ids"] as $l_id) {
$loc = Location::find($l_id);
if(!is_null($loc)) {
$aditional_aux_ids = [];
if($loc->nume == 'Bucharest N') {
$aditional_aux_ids = Location::whereRaw('id != ' . $l_id . ' AND (nume = "Bucharest NW" OR nume = "Bucharest NE" )')->get()->pluck('id')->toArray();
}
if($loc->nume == 'Bucharest E') {
$aditional_aux_ids = Location::whereRaw('id != ' . $l_id . ' AND (nume = "Bucharest NE" OR nume = "Bucharest SE" )')->get()->pluck('id')->toArray();
}
if($loc->nume == 'Bucharest S') {
$aditional_aux_ids = Location::whereRaw('id != ' . $l_id . ' AND (nume = "Bucharest SW" OR nume = "Bucharest SE" )')->get()->pluck('id')->toArray();
}
if($loc->nume == 'Bucharest W') {
$aditional_aux_ids = Location::whereRaw('id != ' . $l_id . ' AND (nume = "Bucharest NW" OR nume = "Bucharest SW" )')->get()->pluck('id')->toArray();
}
$aditional_bucharest_area_ids = array_merge($aditional_bucharest_area_ids, $aditional_aux_ids);
}
}
}
$filter["selected_ids"] = array_merge($filter["selected_ids"], $aditional_bucharest_ids);
$filter["selected_ids"] = array_merge($filter["selected_ids"], $aditional_bucharest_area_ids);
for ($i = 0; $i < count($filter["selected_ids"]); $i++) {
$query .= "'" . $filter["selected_ids"][$i] . "'";
if ($i < count($filter["selected_ids"]) - 1) {
$query .= ', ';
}
}
$query .= ')';
}
}
if ($filter['type'] == 'binary') {
if ($filter['value'] !== 'All') {
if ($first) {
$query .= ' ';
$first = false;
} else {
$query .= ' AND ';
}
if($filter['id'] == 'rented_now') {
if($filter['value'] == 'Yes') {
$query .= ' buildings.id IN (SELECT building_id FROM units where id IN (SELECT unit_id FROM units_companies WHERE now() BETWEEN start_date AND end_date GROUP BY unit_id) GROUP BY building_id) ';
} else if($filter['value'] == 'No') {
$query .= ' buildings.id IN (SELECT building_id FROM units where id NOT IN (SELECT unit_id FROM units_companies WHERE now() BETWEEN start_date AND end_date GROUP BY unit_id) GROUP BY building_id) ';
}
} else {
$value = ($filter['value'] == 'Yes' ? 1 : 0);
$query .= 'buildings.' . $filter['id'] . ' = ' . $value;
}
}
}
