protected function rawFilter($filter)
{
//dd(__METHOD__."(".__LINE__.")",compact('filter'));
// Build a query based on filter $input
$query = InboundOrder::orderBy('Created', 'desc');
foreach ($filter as $key => $value) {
if ($key == 'objectID' && strlen($value) > 3) {
$query = $query->where($key, 'like', $value . '%');
}
if ($key == 'Purchase_Order' && strlen(trim($value)) > 3) {
$query = $query->where($key, 'like', trim($value) . '%');
} elseif ($key == 'Order_Number' && strlen($value) > 3) {
$query = $query->where($key, 'like', $value . '%');
} elseif ($key == 'Client' && strlen($value) > 3) {
$query = $query->where($key, 'like', $value . '%');
} elseif ($key == 'Invoice_Number' && strlen($value) > 3) {
$query = $query->where($key, 'like', $value . '%');
} elseif ($key == 'Status' && is_array($value)) {
$query = $query->whereRaw($key . " in ('" . implode("','", $value) . "')");
} elseif ($key == 'Status' && strlen($value) > 2) {
$query = $query->where($key, 'like', $value . '%');
} elseif ($key == 'Created' && strlen($value) > 4) {
preg_match('/last ([\\d]+) days/', $value, $matches);
if (count($matches) > 0) {
$query = $query->whereRaw($key . ' > DATE_SUB(NOW(), INTERVAL ' . $matches[1] . ' DAY)');
//dd('Created > DATE_SUB(NOW(), INTERVAL '.$matches[1].' DAY)');
} else {
$query = $query->where($key, 'like', $value . '%');
//dd(__METHOD__."(".__LINE__.")",compact('filter','key','value','query'));
}
} elseif ($key == 'Expected' && strlen($value) > 4) {
$query = $query->where($key, 'like', preg_replace('/[-:]/', '', $value) . '%');
//dd(__METHOD__."(".__LINE__.")",compact('filter','key','value','query'));
} else {
//TODO Replace this with Eloquent Relationships, one to one|many
preg_match('/item\\.(.*)/', $key, $matches);
if (count($matches) > 0) {
/* $query = $query->whereRaw("objectID in (select distinct Inbound_Order_Detail.Order_Number from Inbound_Order_Detail join Item on Item.objectID = Inbound_Order_Detail.SKU where Item.$matches[1] = '$value')");
*
* The above line work great when you wanting to search the Article.{fieldName} columns.
* Here we would like to extend that to search the UPCs as well.
*
* Relationship is, a PurchaseOrderDetail.SKU field points to an Article,
* Article(Item entry) -> (parentID on) itemKit (has objectID) -> UPC(Item entries)
*
* User may have provided Article.UPC or UPC.UPC, so we check them both ..
*/
// when string containing digits, left trim zeros
$upcValue = ctype_digit($value) ? ltrim($value, '0') : $value;
$subQueryWhereClause = " where Article.{$matches['1']} = '{$upcValue}' or UPC.{$matches['1']} = '{$upcValue}'";
//dd(compact('key','matches','value','subQueryWhereClause'));
$query = $query->whereRaw("objectID in (select distinct Inbound_Order_Detail.Order_Number from Inbound_Order_Detail join Item as Article on Article.objectID = Inbound_Order_Detail.SKU left join itemKit on itemKit.parentID = Article.objectID left join Item as UPC on UPC.objectID = itemKit.objectID {$subQueryWhereClause} )");
}
}
}
return $query;
}
protected function rawFilter($input)
{
// Build a query based on filter $input
$query = InboundOrder::orderBy('Created', 'desc');
if (isset($input['Order_Number']) && strlen($input['Order_Number']) > 3) {
$query = $query->where('Order_Number', 'like', $input['Order_Number'] . '%');
}
if (isset($input['Purchase_Order']) && strlen($input['Purchase_Order']) > 3) {
$query = $query->where('Purchase_Order', 'like', $input['Purchase_Order'] . '%');
}
if (isset($input['Invoice_Number']) && strlen($input['Invoice_Number']) > 3) {
$query = $query->where('Invoice_Number', 'like', $input['Invoice_Number'] . '%');
}
if (isset($input['Status']) && strlen($input['Status']) > 1) {
$query = $query->where('Status', 'like', $input['Status'] . '%');
}
if (isset($input['Created']) && strlen($input['Created']) > 6) {
$query = $query->where('Created', 'like', $input['Created'] . '%');
}
if (isset($input['Expected']) && strlen($input['Expected']) > 6) {
$query = $query->where('Expected', 'like', $input['Expected'] . '%');
}
return $query;
}
