/**
* desc Location;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| objectID | bigint(20) | NO | PRI | NULL | |
| Location_Name | varchar(85) | YES | MUL | NULL | |
| Capacity | varchar(85) | YES | | NULL | |
| x | varchar(85) | YES | | NULL | |
| y | varchar(85) | YES | | NULL | |
| z | varchar(85) | YES | | NULL | |
| Status | varchar(85) | YES | | NULL | |
| LocType | varchar(85) | YES | MUL | NULL | |
| Comingle | varchar(85) | YES | | NULL | |
| ChargeType | varchar(85) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
* @param $filter
* @return mixed
*/
protected function rawFilter($filter)
{
//Log::debug('query: ',$filter);
// Build a query based on filter $filter
$orderBy = 'Location_Name';
$query = Location::query()->select('Location.objectID', 'Location.Location_Name', 'Location.Capacity', 'Location.x', 'Location.y', 'Location.z', 'Location.Status', 'Location.LocType', 'Location.Comingle', 'Location.ChargeType');
if (isset($filter['objectID']) && strlen($filter['objectID']) > 1) {
$query->where('objectID', 'like', $filter['objectID'] . '%');
}
if (isset($filter['Location_Name']) && strlen($filter['Location_Name']) > 1) {
$query->where('Location_Name', 'like', $filter['Location_Name'] . '%');
$query->orderByRaw(DB::raw("concat(length(Location_Name), Location_Name)"));
$orderBy = False;
}
if (isset($filter['objectID or Location_Name']) && strlen($filter['objectID or Location_Name']) > 1) {
// s/b equivalent to where .. and (objectID = $locID or Location_Name = $locID) and ..
$locID = $filter['objectID or Location_Name'];
$query->where(function ($query) use($locID) {
$query->where('objectID', '=', $locID)->orWhere('Location_Name', '=', $locID);
});
}
if (isset($filter['x']) && strlen($filter['x']) > 0) {
$query->where('x', '=', $filter['x']);
}
if (isset($filter['y']) && strlen($filter['y']) > 0) {
$query->where('y', '=', $filter['y']);
}
if (isset($filter['z']) && strlen($filter['z']) > 0) {
$query->where('z', '=', $filter['z']);
}
if (isset($filter['LocType']) && strlen($filter['LocType']) > 1) {
$query->where('LocType', 'like', $filter['LocType'] . '%');
}
if (isset($filter['Comingle']) && strlen($filter['Comingle']) > 0) {
$query->where('Comingle', 'like', $filter['Comingle'] . '%');
}
/*
* container.parent should generate this sql request
* select Location.* from Location join container loc on loc.objectID = Location.objectID where loc.parentID = 6213292055;
*/
if (isset($filter['container.parent']) && strlen($filter['container.parent']) > 3) {
$query->join('container as loc', 'loc.objectID', '=', 'Location.objectID')->where('loc.parentID', $filter['container.parent']);
}
/*
* container.child should generate this sql request
* select Location.* from Location join container plt on plt.parentID = Location.objectID where plt.objectID = 6213292075;
*/
if (isset($filter['container.child']) && strlen($filter['container.child']) > 3) {
$query->join('container as plt', 'plt.parentID', '=', 'Location.objectID')->where('plt.objectID', $filter['container.child']);
}
if ($orderBy) {
$query->orderBy('Location_Name', 'asc');
}
//dd(__METHOD__."(".__LINE__.")",compact('filter','query'));
return $query;
}
