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; }