/**
* Handle the filter.
*
* @param Builder $query
* @param SearchFilterInterface $filter
*/
public function handle(Builder $query, TableBuilder $builder, SearchFilterInterface $filter)
{
$stream = $filter->getStream();
$model = $builder->getTableModel();
/**
* If the model is translatable then
* join it's translations so they
* are filterable too.
*
* @var EloquentQueryBuilder $query
*/
if ($model->getTranslationModelName() && !$query->hasJoin($model->getTranslationTableName())) {
$query->leftJoin($model->getTranslationTableName(), $model->getTableName() . '.id', '=', $model->getTranslationTableName() . '.' . $model->getRelationKey());
}
$query->where(function (Builder $query) use($filter, $stream) {
foreach ($filter->getColumns() as $column) {
$query->orWhere($column, 'LIKE', "%{$filter->getValue()}%");
}
foreach ($filter->getFields() as $field) {
$filter->setField($field);
$fieldType = $stream->getFieldType($field);
$fieldTypeQuery = $fieldType->getQuery();
$fieldTypeQuery->setConstraint('or');
$this->container->call([$fieldTypeQuery, 'filter'], compact('query', 'filter', 'builder'));
}
});
}
public function build(Builder $query)
{
if ($this->person === null) {
return $query->where(Page::ATTR_ENABLE_ACL, false);
}
return $query->leftJoin('page_acl', 'pages.id', '=', 'page_acl.page_id')->leftJoin('group_person', 'page_acl.group_id', '=', 'group_person.group_id')->where(function (Builder $where) {
$where->where(Page::ATTR_CREATED_BY, $this->person->getId())->orWhereNull('page_acl.group_id')->orWhere('group_person.person_id', $this->person->getId());
})->groupBy('pages.id');
}
/**
* Overriding newQuery() to the custom PostBuilder with some intereting methods
*
* @param bool $excludeDeleted
* @return Corcel\PostBuilder
*/
public function newQuery($excludeDeleted = true)
{
$builder = new Builder($this->newBaseQueryBuilder());
$builder->setModel($this);
$builder->leftJoin('wp_term_taxonomy', 'wp_terms.term_id', '=', 'wp_term_taxonomy.term_id')->where('wp_term_taxonomy.taxonomy', '=', 'category')->select('wp_terms.*');
if ($excludeDeleted and $this->softDelete) {
$builder->whereNull($this->getQualifiedDeletedAtColumn());
}
return $builder;
}
public function build(Builder $query)
{
foreach ($this->tags as $i => $tag) {
$alias = "pt_without-{$i}";
$query->leftJoin("pages_tags as {$alias}", function ($q) use($tag, $alias) {
$q->on('pages.id', '=', "{$alias}.page_id")->on("{$alias}.tag_id", '=', DB::raw($tag->getId()));
})->whereNull("{$alias}.page_id");
}
return $query;
}
public function apply(Builder $builder)
{
$select = ['Simulation.*', 'Power_Generator.Modality_Id'];
$builder->with(['Combination.NumericalModel', 'Combination.Protocol', 'Combination.PowerGenerator', 'Combination.PowerGenerator.Modality'])->leftJoin('Power_Generator', 'Power_Generator.Id', '=', 'Combination.Power_Generator_Id')->leftJoin('Combination', 'Combination.Combination_Id', '=', 'Simulation.Combination_Id');
if (Config::get('gosmart.integrated_patient_database')) {
$builder->leftJoin('ItemSet as SimulationItem', 'SimulationItem.Id', '=', 'Simulation.Id')->leftJoin('ItemSet as PatientItem', 'PatientItem.Id', '=', 'Simulation.Patient_Id')->leftJoin('ItemSet_Patient', 'ItemSet_Patient.Id', '=', 'Simulation.Patient_Id')->leftJoin('ItemSet_VtkFile as SimulatedLesionSurface', 'SimulatedLesionSurface.Simulation_Id', '=', 'Simulation.Id')->leftJoin('ItemSet_VtuFile as SimulatedLesionVolume', 'SimulatedLesionVolume.Simulation_Id', '=', 'Simulation.Id')->leftJoin('ItemSet_Segmentation', function ($leftJoin) {
$leftJoin->on('ItemSet_Segmentation.Patient_Id', '=', 'Simulation.Patient_Id');
$leftJoin->on('ItemSet_Segmentation.State', '=', DB::raw('3'));
$leftJoin->on('ItemSet_Segmentation.SegmentationType', '=', DB::raw(SegmentationTypeEnum::Lesion));
})->leftJoin('ItemSet_VtkFile as LesionFile', 'LesionFile.Segmentation_Id', '=', 'ItemSet_Segmentation.Id')->leftJoin('AspNetUsers as Clinician', 'Clinician.Id', '=', 'ItemSet_Patient.AspNetUsersId');
$select = array_merge($select, ['SimulationItem.CreationDate as creationDate', 'LesionFile.Id as SegmentedLesionId', 'Clinician.Id as ClinicianId', 'Clinician.UserName as ClinicianUserName', 'ItemSet_Patient.Alias as PatientAlias', 'ItemSet_Patient.Description as PatientDescription', 'SimulatedLesionSurface.Id as SimulatedLesionSurfaceId', 'SimulatedLesionVolume.Id as SimulatedLesionVolumeId']);
}
$builder->select($select);
}
/**
* Filter a query by the value of a
* field using this field type.
*
* @param Builder $query
* @param FilterInterface $filter
*/
public function filter(Builder $query, FilterInterface $filter)
{
$stream = $filter->getStream();
$assignment = $stream->getAssignment($filter->getField());
$column = $this->fieldType->getColumnName();
$translations = $stream->getEntryTranslationsTableName();
if ($assignment->isTranslatable()) {
if ($query instanceof EloquentQueryBuilder && !$query->hasJoin($translations)) {
$query->leftJoin($stream->getEntryTranslationsTableName(), $stream->getEntryTableName() . '.id', '=', $stream->getEntryTranslationsTableName() . '.entry_id');
}
$query->addSelect($translations . '.locale');
$query->addSelect($translations . '.' . $column);
$query->{$this->where()}(function (Builder $query) use($stream, $filter, $column) {
$query->where($stream->getEntryTranslationsTableName() . '.locale', config('app.locale'));
$query->where($stream->getEntryTranslationsTableName() . '.' . $column, 'LIKE', "%" . $filter->getValue() . "%");
});
} else {
$query->{$this->where()}(function (Builder $query) use($stream, $filter, $column) {
$query->where($stream->getEntryTableName() . '.' . $column, 'LIKE', "%" . $filter->getValue() . "%");
});
}
}
/**
* Add the constraints for a relationship count query.
*
* @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query
* @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $parent
*
* @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder
*/
public function getRelationCountQuery(Builder $query, Builder $parent)
{
$query->select(new Expression('count(*)'));
$one = $this->getRelated()->getQualifiedKeyName();
$prev = $this->getForeignKey($this->getRelated());
$alias = null;
foreach ($this->models as $model) {
if ($this->getParent()->getTable() == $model->getTable()) {
$alias = $model->getTable() . '_' . time();
$other = $alias . '.' . $prev;
$query->leftJoin(new Expression($model->getTable() . ' as ' . $alias), $one, '=', $other);
} else {
$other = $model->getTable() . '.' . $prev;
$query->leftJoin($model->getTable(), $one, '=', $other);
}
$prev = $this->getForeignKey($model);
$one = $model->getQualifiedKeyName();
}
$key = $this->wrap($this->getQualifiedParentKeyName());
$query->where(new Expression($alias . '.' . $this->getParent()->getKeyName()), '=', new Expression($key));
return $query;
}
/**
* @codeCoverageIgnore
*
* @param EloquentBuilder $query
* @param array $types
*/
public function scopeTransactionTypes(EloquentBuilder $query, array $types)
{
if (is_null($this->joinedTransactionTypes)) {
$query->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id');
$this->joinedTransactionTypes = true;
}
$query->whereIn('transaction_types.type', $types);
}
/**
* Adds the sql joins to the query.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeJoins(Builder $query)
{
foreach ($this->getJoins() as $table => $keys) {
$query->leftJoin($table, function ($join) use($keys) {
$join->on($keys[0], '=', $keys[1]);
if (array_key_exists(2, $keys) && array_key_exists(3, $keys)) {
$join->where($keys[2], '=', $keys[3]);
}
});
}
}
/**
* @codeCoverageIgnore
*
* @param EloquentBuilder $query
* @param string $name
* @param string $value
*/
public function scopeHasMetaValue(EloquentBuilder $query, $name, $value)
{
$joinName = str_replace('.', '_', $name);
$query->leftJoin('account_meta as ' . $joinName, function (JoinClause $join) use($joinName, $name) {
$join->on($joinName . '.account_id', '=', 'accounts.id')->where($joinName . '.name', '=', $name);
});
$query->where($joinName . '.data', json_encode($value));
}
/**
* Apply join query.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $foreignTable
* @param string $foreignKey
* @param bool $joinToParent
* @return void
*/
public function join($query, $foreignTable = null, $foreignKey = null)
{
$foreignTable = $foreignTable ?: $query->getModel()->getTable();
$foreignKey = $foreignKey ?: $this->foreignKey;
$query->leftJoin($this->tableToJoin(), "{$this->tableAlias}.{$this->primaryKey}", '=', "{$foreignTable}.{$foreignKey}");
}
/**
* Adds the sql joins to the query.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeJoins(Builder $query)
{
foreach ($this->getJoins() as $table => $keys) {
$query->leftJoin($table, $keys[0], '=', $keys[1]);
}
}
/**
* @param Builder $query
*/
protected function setRelationQueryConstraints(Builder $query)
{
$one = $this->getRelated()->getQualifiedKeyName();
$prev = $this->getForeignKey($this->getRelated());
$alias = null;
$lastIndex = count($this->models);
foreach ($this->models as $index => $model) {
if ($lastIndex === $index) {
$prev = $this->prefix . $prev;
// TODO: Check if this line is really necessary. Its not covered by any of the tests.
}
if ($this->getParent()->getTable() == $model->getTable()) {
$alias = $model->getTable() . '_' . time();
$other = $alias . '.' . $prev;
$query->leftJoin(new Expression($model->getTable() . ' as ' . $alias), $one, '=', $other);
} else {
$other = $model->getTable() . '.' . $prev;
$query->leftJoin($model->getTable(), $one, '=', $other);
}
$prev = $this->getForeignKey($model);
$one = $model->getQualifiedKeyName();
}
$key = $this->wrap($this->getQualifiedParentKeyName());
$query->where(new Expression($alias . '.' . $this->getParent()->getKeyName()), '=', new Expression($key));
}
/**
* @param Builder $query
* @param $value
*/
public function scopeWithReportValue(Builder $query, $value)
{
// only join if query has no join yet
$joined = false;
/** @var JoinClause $join */
if (!is_null($query->getQuery()->joins)) {
foreach ($query->getQuery()->joins as $join) {
if ($join->table == 'reports') {
$joined = true;
}
}
}
if (!$joined) {
$query->leftJoin('reports', DB::Raw('DATE_FORMAT(log_entries.time,"%y%m%d%H%i")'), '=', DB::Raw('DATE_FORMAT(reports.time, "%y%m%d%H%i")'));
}
$joinName = str_replace('.', '_', $value);
$query->leftJoin('report_values as ' . $joinName, function (JoinClause $join) use($value, $joinName) {
$join->on('reports.id', '=', $joinName . '.report_id')->where($joinName . '.name', '=', $value);
});
}