/**
* Find a model by its primary key.
*
* @param mixed $id
* @param array $columns
*
* @return \Illuminate\Database\Eloquent\Model|static|null
*/
public function find($id, $columns = array('*'))
{
Event::fire('before.find', array($this));
$result = parent::find($id, $columns);
Event::fire('after.find', array($this));
return $result;
}
/**
* Query builder scope to search on text
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $search Search term
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeTextSearch($query, $search)
{
return $query->where('name', 'LIKE', "%{$search}%")->orWhere('modelno', 'LIKE', "%{$search}%")->orWhere(function ($query) use($search) {
$query->whereHas('depreciation', function ($query) use($search) {
$query->where('name', 'LIKE', '%' . $search . '%');
});
})->orWhere(function ($query) use($search) {
$query->whereHas('category', function ($query) use($search) {
$query->where('name', 'LIKE', '%' . $search . '%');
});
})->orWhere(function ($query) use($search) {
$query->whereHas('manufacturer', function ($query) use($search) {
$query->where('name', 'LIKE', '%' . $search . '%');
});
});
}
/**
* Group an array based on one of it's values.
*
* @method scopeArrayGrouped
*
* @param Illuminate\Database\Query\Builder $query The query being scoped
* @param string $group_key The key whose value we'll group by
*
* @return array The grouped array
*/
public function scopeArrayGrouped($query, $key_column)
{
// First get the results from the query
$results = $query->get();
// Initialise an empty array to store our results in
$rtn = [];
/** @var Illuminate\Database\Eloquent\Model $result */
foreach ($results as $result) {
$group_key = $result->{$key_column};
// If the group key has not already been set in our results,
// initialise it as an empty array
if (!isset($rtn[$group_key])) {
$rtn[$group_key] = [];
}
// Add the model to the query
$rtn[$group_key][] = $result;
}
return $rtn;
}
/**
* Query builder scope to search on text
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $search Search term
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeTextSearch($query, $search)
{
return $query->where(function ($query) use($search) {
$query->where('title', 'LIKE', '%' . $search . '%')->orWhere('notes', 'LIKE', '%' . $search . '%')->orWhere('asset_maintenance_type', 'LIKE', '%' . $search . '%')->orWhere('cost', 'LIKE', '%' . $search . '%')->orWhere('start_date', 'LIKE', '%' . $search . '%')->orWhere('completion_date', 'LIKE', '%' . $search . '%');
});
}
/**
* Query builder scope to order on location
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
* TODO: Extend this method out for checked out assets as well. Right now it
* only checks the location name related to rtd_location_id
*/
public function scopeOrderLocation($query, $order)
{
return $query->join('locations', 'locations.id', '=', 'assets.rtd_location_id')->orderBy('locations.name', $order);
}
/**
* Query builder scope to order on company
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeOrderLocation($query, $order)
{
return $query->leftJoin('locations', 'users.location_id', '=', 'locations.id')->orderBy('locations.name', $order);
}
/**
* Get the unread notifications for a user.
*
* @param Illuminate\Database\Query\Builder $query
* @param int $userId (Default: null)
*
* @return Illuminate\Database\Query\Builder
*/
public function scopeUnreadReceiver($query, $userId = null)
{
if (empty($userId)) {
$userId = \Sentry::getUser()->id;
}
return $query->where(function (\Illuminate\Database\Query\Builder $query) use($userId) {
$query->where('read', '=', 0)->where('to_user_id', $userId);
});
}
/**
* Add group by to query builder
* @param Illuminate\Database\Query\Builder $query object is by reference
*/
protected function addGroupByQuery($query)
{
if (isset($this->groupBy)) {
foreach ($this->groupBy as $groupBy) {
$query->groupBy($groupBy);
}
}
}
/**
* Query builder scope to order on parent
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeOrderParent($query, $order)
{
// Left join here, or it will only return results with parents
return $query->leftJoin('locations as parent_loc', 'locations.parent_id', '=', 'parent_loc.id')->orderBy('parent_loc.name', $order);
}
/**
* Filter the joined table of the query by the value passed in $filters
*
* @author Luca Brognara
* @date December 2015
*
* @param Illuminate\Database\Query\Builder $query
* @param string $model
* @param array $filters
* @param string $filters
*
* @return array
*/
public function scopeFilterBy($query, $model, $filters, $table)
{
if (count($filters) == 0) {
return $query;
}
return $query->whereHas($model, function ($query) use($filters, $table) {
try {
$this->filter($query, $filters, $this->get_table_columns($table));
} catch (\Exception $e) {
throw new \Exception($e->getMessage(), $e->getCode());
}
});
}
/**
* Query builder scope to order on manager
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeOrderManager($query, $order)
{
// Left join here, or it will only return results with parents
return $query->leftJoin('users as manager', 'users.manager_id', '=', 'manager.id')->orderBy('manager.first_name', $order)->orderBy('manager.last_name', $order);
}
// 创建链接
$capsule->addConnection($database, 'seed');
// 设置全局静态可访问
$capsule->setAsGlobal();
$table = 't_crm_soa_order';
// 启动Eloquent
$capsule->bootEloquent();
$conn = $capsule->getConnection('seed');
$grammar = $conn->getQueryGrammar();
$processor = $conn->getPostProcessor();
$sql = "select column_name from information_schema.columns where table_schema = 'gold_db' and table_name =\r\n't_crm_soa_order'";
$sql = 'desc t_crm_soa_order';
//dd($conn->getColumnListing($table));
//$conn->statement();
$ret = $conn->select($sql);
$queryBuilder = new Illuminate\Database\Query\Builder($conn, $grammar, $processor);
//dd($queryBuilder->getColumnListing($table));
dd($ret);
exit;
$conn->affectingStatement();
$conn->commit();
//dd($conn->getColumnListing($table));
//dd($conn->statement('desc t_crm_soa_order'));
dd($queryBuilder->select('desc t_crm_soa_order'));
dd($conn->statement('desc t_crm_soa_order'));
//$ret =Capsule::connection('seed')->table('t_crm_soa_order')->count();
//use App\Lib\BaseModel;
//$model = new BaseModel();
//$model->setConnection('seed');
//$model->setTable('t_crm_soa_order');
//dd($model->newQueryWithoutScopes()->select('desc ' . $model->getTable()));
/**
* Shortcut for get+toJson methods.
* @param string $columns columns
* @return string
*/
public function toJson($columns = array('*'))
{
return $this->query->get($columns)->toJson();
}
/**
* Scope for the last notifications.
*
* @param Illuminate\Database\Query\Builder $query
* @param int $limit (Default: 10)
*
* @return Illuminate\Database\Query\Builder
*/
public function scopeRecent($query, $limit = 10)
{
return $query->orderBy('created_at', 'desc')->limit($limit);
}
/**
* Query builder scope to order on admin user
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeOrderAdmin($query, $order)
{
return $query->leftJoin('users', 'asset_maintenances.user_id', '=', 'users.id')->orderBy('users.first_name', $order)->orderBy('users.last_name', $order);
}
/**
* Query builder scope to search on text
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $search Search term
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeTextSearch($query, $search)
{
return $query->where(function ($query) use($search) {
$query->where('name', 'LIKE', '%' . $search . '%')->orWhere('serial', 'LIKE', '%' . $search . '%')->orWhere('notes', 'LIKE', '%' . $search . '%')->orWhere('order_number', 'LIKE', '%' . $search . '%')->orWhere('purchase_date', 'LIKE', '%' . $search . '%')->orWhere('purchase_cost', 'LIKE', '%' . $search . '%');
});
}
/**
* Querie all not completed projects
*
* @param Illuminate\Database\Query\Builder $query
*
* @return Illuminate\Database\Query\Builder
*/
public function scopeOpen($query)
{
return $query->whereNull('completed_at');
}
/**
* Scope for all not activated users.
*
* @param Illuminate\Database\Query\Builder $query
*
* @return Illuminate\Database\Query\Builder
*/
public function scopeNotActivated($query)
{
return $query->where('activated', '=', 0);
}
/**
* Builds a query scope to return object alphabetically for a dropdown list.
*
* @param Illuminate\Database\Query\Builder $query
* @param string $column (optional) to order by
* @param string $key (optional) to use in returned array
* @param string $sort (optional) direction
* @return array
*/
public function scopeListAlphabetically($query, $column = null, $key = null, $sort = 'asc')
{
$column = is_null($column) ? $this->getKeyName() : $column;
return $query->orderBy($column, $sort)->lists($column, $key)->all();
}
/**
* Query builder scope to search on text
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $search Search term
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeTextSearch($query, $search)
{
return $query->where(function ($query) use($search) {
$query->where('name', 'LIKE', '%' . $search . '%');
});
}
/**
* Query scope "isAllowed".
*
* @param Illuminate\Database\Query\Builder $query
* @return Illuminate\Database\Query\Builder
*/
public function scopeIsAllowed($query)
{
return $query->whereHas('user', function ($q) {
return $q->whereId(auth()->id());
});
}
/**
* Query builder scope for Deleted assets
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeDeleted($query)
{
return $query->whereNotNull('deleted_at');
}
/**
* Set the relationships that should not be eager loaded.
*
* @param Illuminate\Database\Query\Builder $query
* @param mixed $relations
* @return $this
*/
public function scopeWithout($query, $relations)
{
$relations = is_array($relations) ? $relations : array_slice(func_get_args(), 1);
$relationships = array_dot($query->getEagerLoads());
foreach ($relations as $relation) {
unset($relationships[$relation]);
}
return $query->setEagerLoads([])->with(array_keys($relationships));
}
/**
* Query builder scope to order on company
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param text $order Order
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeOrderCompany($query, $order)
{
return $query->leftJoin('companies', 'consumables.company_id', '=', 'companies.id')->orderBy('companies.name', $order);
}
/**
* Scope of all not completed tasks.
*
* @param Illuminate\Database\Query\Builder $query
*
* @return Illuminate\Database\Query\Builder
*/
public function scopeNotCompleted($query)
{
return $query->where('completed', '=', '0');
}