/** * @param string $entityTableAndColumn * @param Attribute[] $attributes * @return ISqlFilter|null */ public static function Create($entityTableAndColumn, $attributes) { $filteringAttributes = false; $f = new SqlFilterFreeForm($entityTableAndColumn . ' IN (SELECT a0.' . ColumnNames::ATTRIBUTE_ENTITY_ID . ' FROM ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a0 '); $attributeFragment = new SqlFilterNull(); /** @var $attribute Attribute */ foreach ($attributes as $i => $attribute) { if ($attribute->Value() == null || $attribute->Value() == '') { continue; } $id = $attribute->Id(); $filteringAttributes = true; $attributeId = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_ID, $id); $attributeValue = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_VALUE, $id); $idEquals = new SqlFilterEquals($attributeId, $attribute->Id()); $f->AppendSql('LEFT JOIN ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a' . $id . ' ON a0.entity_id = a' . $id . '.entity_id '); if ($attribute->Type() == CustomAttributeTypes::MULTI_LINE_TEXTBOX || $attribute->Type() == CustomAttributeTypes::SINGLE_LINE_TEXTBOX) { $attributeFragment->_And($idEquals->_And(new SqlFilterLike($attributeValue, $attribute->Value()))); } else { $attributeFragment->_And($idEquals->_And(new SqlFilterEquals($attributeValue, $attribute->Value()))); } } $f->AppendSql("WHERE [attribute_list_token] )"); $f->Substitute('attribute_list_token', $attributeFragment); if ($filteringAttributes) { return $f; } return null; }
public function GetFilter() { $filter = new SqlFilterNull(); if (!empty($this->startDate)) { $filter->_And(new SqlFilterGreaterThan(ColumnNames::RESERVATION_START, $this->startDate->ToDatabase())); } if (!empty($this->endDate)) { $filter->_And(new SqlFilterLessThan(ColumnNames::RESERVATION_END, $this->endDate->ToDatabase())); } if (!empty($this->scheduleId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::SCHEDULES, ColumnNames::SCHEDULE_ID), $this->scheduleId)); } if (!empty($this->resourceId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_ID), $this->resourceId)); } return $filter; }
public function GetList($pageNumber, $pageSize, $sortField = null, $sortDirection = null, $filter = null) { if (!$this->user->IsAdmin) { $scheduleAdminGroupIds = array(); $resourceAdminGroupIds = array(); $groups = $this->repo->LoadGroups($this->user->UserId, array(RoleLevel::SCHEDULE_ADMIN, RoleLevel::RESOURCE_ADMIN)); foreach ($groups as $group) { if ($group->IsResourceAdmin) { $resourceAdminGroupIds[] = $group->GroupId; } if ($group->IsScheduleAdmin) { $scheduleAdminGroupIds[] = $group->GroupId; } } if ($filter == null) { $filter = new SqlFilterNull(); } $additionalFilter = new SqlFilterIn(new SqlFilterColumn(TableNames::SCHEDULES_ALIAS, ColumnNames::SCHEDULE_ADMIN_GROUP_ID), $scheduleAdminGroupIds); $filter->_And($additionalFilter->_Or(new SqlFilterIn(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_ADMIN_GROUP_ID), $resourceAdminGroupIds))); } return parent::GetList($pageNumber, $pageSize, $sortField, $sortDirection, $filter); }
/** * @param CustomAttribute[] $customAttributes * @return ISqlFilter */ public function AsFilter($customAttributes) { $filter = new SqlFilterNull(); if (!empty($this->ResourceNameFilter)) { $filter->_And(new SqlFilterLike(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_NAME), $this->ResourceNameFilter)); } if (!empty($this->ScheduleIdFilter)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::SCHEDULE_ID), $this->ScheduleIdFilter)); } if (!empty($this->ResourceTypeFilter)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_TYPE_ID), $this->ResourceTypeFilter)); } if (!empty($this->ResourceStatusFilterId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_STATUS_ID), $this->ResourceStatusFilterId)); } if (!empty($this->CapacityFilter)) { $filter->_And(new SqlFilterGreaterThan(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_MAX_PARTICIPANTS), $this->CapacityFilter, true)); } if ($this->RequiresApprovalFilter != '') { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_REQUIRES_APPROVAL), $this->RequiresApprovalFilter)); } if ($this->AutoPermissionFilter != '') { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_AUTOASSIGN), $this->AutoPermissionFilter)); } if ($this->AllowMultiDayFilter != '') { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES_ALIAS, ColumnNames::RESOURCE_ALLOW_MULTIDAY), $this->AllowMultiDayFilter)); } if (!empty($this->Attributes)) { $filteringAttributes = false; $attributeDefinitions = array(); foreach ($customAttributes as $a) { $attributeDefinitions[$a->Id()] = $a; } $f = new SqlFilterFreeForm(ColumnNames::RESOURCE_ID . ' IN (SELECT a0.' . ColumnNames::ATTRIBUTE_ENTITY_ID . ' FROM ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a0 '); $attributeFragment = new SqlFilterNull(); /** @var $attribute Attribute */ foreach ($this->Attributes as $id => $value) { if ($value == null || $value == '' || !array_key_exists($id, $attributeDefinitions)) { continue; } $filteringAttributes = true; $attribute = $attributeDefinitions[$id]; $attributeId = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_ID, $id); $attributeValue = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_VALUE, $id); $idEquals = new SqlFilterEquals($attributeId, $id); $f->AppendSql('LEFT JOIN ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a' . $id . ' ON a0.entity_id = a' . $id . '.entity_id '); if ($attribute->Type() == CustomAttributeTypes::MULTI_LINE_TEXTBOX || $attribute->Type() == CustomAttributeTypes::SINGLE_LINE_TEXTBOX) { $attributeFragment->_And($idEquals->_And(new SqlFilterLike($attributeValue, $value))); } else { $attributeFragment->_And($idEquals->_And(new SqlFilterEquals($attributeValue, $value))); } } $f->AppendSql("WHERE [attribute_list_token] )"); $f->Substitute('attribute_list_token', $attributeFragment); if ($filteringAttributes) { $filter->_And($f); } } return $filter; }
public function GetFilter() { $filter = new SqlFilterNull(); if (!empty($this->startDate)) { $filter->_And(new SqlFilterGreaterThan(ColumnNames::RESERVATION_START, $this->startDate->ToDatabase(), true)); } if (!empty($this->endDate)) { $filter->_And(new SqlFilterLessThan(ColumnNames::RESERVATION_END, $this->endDate->AddDays(1)->ToDatabase(), true)); } if (!empty($this->referenceNumber)) { $filter->_And(new SqlFilterEquals(ColumnNames::REFERENCE_NUMBER, $this->referenceNumber)); } if (!empty($this->scheduleId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::SCHEDULE_ID), $this->scheduleId)); } if (!empty($this->resourceId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_ID), $this->resourceId)); } if (!empty($this->userId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::USERS, ColumnNames::USER_ID), $this->userId)); } if (!empty($this->statusId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESERVATION_SERIES_ALIAS, ColumnNames::RESERVATION_STATUS), $this->statusId)); } if (!empty($this->resourceStatusId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_STATUS_ID), $this->resourceStatusId)); } if (!empty($this->resourceStatusReasonId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_STATUS_REASON_ID), $this->resourceStatusReasonId)); } if (!empty($this->attributes)) { $filteringAttributes = false; $f = new SqlFilterFreeForm(TableNames::RESERVATION_SERIES_ALIAS . '.' . ColumnNames::SERIES_ID . ' IN (SELECT a0.' . ColumnNames::ATTRIBUTE_ENTITY_ID . ' FROM ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a0 '); $attributeFragment = new SqlFilterNull(); /** @var $attribute Attribute */ foreach ($this->attributes as $i => $attribute) { if ($attribute->Value() == null || $attribute->Value() == '') { continue; } $id = $attribute->Id(); $filteringAttributes = true; $attributeId = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_ID, $id); $attributeValue = new SqlRepeatingFilterColumn("a{$id}", ColumnNames::CUSTOM_ATTRIBUTE_VALUE, $id); $idEquals = new SqlFilterEquals($attributeId, $attribute->Id()); $f->AppendSql('LEFT JOIN ' . TableNames::CUSTOM_ATTRIBUTE_VALUES . ' a' . $id . ' ON a0.entity_id = a' . $id . '.entity_id '); if ($attribute->Type() == CustomAttributeTypes::MULTI_LINE_TEXTBOX || $attribute->Type() == CustomAttributeTypes::SINGLE_LINE_TEXTBOX) { $attributeFragment->_And($idEquals->_And(new SqlFilterLike($attributeValue, $attribute->Value()))); } else { $attributeFragment->_And($idEquals->_And(new SqlFilterEquals($attributeValue, $attribute->Value()))); } } $f->AppendSql("WHERE [attribute_list_token] )"); $f->Substitute('attribute_list_token', $attributeFragment); if ($filteringAttributes) { $filter->_And($f); } } foreach ($this->_and as $and) { $filter->_And($and); } return $filter; }
public function GetFilter() { $filter = new SqlFilterNull(); if (!empty($this->username)) { $filter->_And(new SqlFilterEquals(ColumnNames::USERNAME, $this->username)); } if (!empty($this->email)) { $filter->_And(new SqlFilterEquals(ColumnNames::EMAIL, $this->email)); } if (!empty($this->firstName)) { $filter->_And(new SqlFilterEquals(ColumnNames::FIRST_NAME, $this->firstName)); } if (!empty($this->lastName)) { $filter->_And(new SqlFilterEquals(ColumnNames::LAST_NAME, $this->lastName)); } if (!empty($this->phone)) { $filter->_And(new SqlFilterEquals(ColumnNames::PHONE_NUMBER, $this->phone)); } if (!empty($this->organization)) { $filter->_And(new SqlFilterEquals(ColumnNames::ORGANIZATION, $this->organization)); } if (!empty($this->position)) { $filter->_And(new SqlFilterEquals(ColumnNames::POSITION, $this->position)); } if (!empty($this->attributes)) { $attributeFilter = AttributeFilter::Create(TableNames::USERS_ALIAS . '.' . ColumnNames::USER_ID, $this->attributes); if ($attributeFilter != null) { $filter->_And($attributeFilter); } } foreach ($this->_and as $and) { $filter->_And($and); } return $filter; }
public function GetList($pageNumber, $pageSize, $sortField = null, $sortDirection = null, $filter = null) { $user = $this->repo->LoadById($this->user->UserId); if (!$user->IsInRole(RoleLevel::SCHEDULE_ADMIN)) { return new PageableData(); } $ids = array(); $filter = new SqlFilterNull(); foreach ($user->Groups() as $group) { if ($group->IsScheduleAdmin) { $ids[] = $group->GroupId; } } $filter->_And(new SqlFilterIn(new SqlFilterColumn(TableNames::SCHEDULES_ALIAS, ColumnNames::RESOURCE_ADMIN_GROUP_ID), $ids)); return parent::GetList($pageNumber, $pageSize, $sortField, $sortDirection, $filter); }
public function GetFilter() { $filter = new SqlFilterNull(); if (!empty($this->startDate)) { $filter->_And(new SqlFilterGreaterThan(ColumnNames::RESERVATION_START, $this->startDate->ToDatabase(), true)); } if (!empty($this->endDate)) { $filter->_And(new SqlFilterLessThan(ColumnNames::RESERVATION_END, $this->endDate->AddDays(1)->ToDatabase(), true)); } if (!empty($this->referenceNumber)) { $filter->_And(new SqlFilterEquals(ColumnNames::REFERENCE_NUMBER, $this->referenceNumber)); } if (!empty($this->scheduleId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::SCHEDULE_ID), $this->scheduleId)); } if (!empty($this->resourceId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_ID), $this->resourceId)); } if (!empty($this->userId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::USERS, ColumnNames::USER_ID), $this->userId)); } if (!empty($this->statusId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESERVATION_SERIES_ALIAS, ColumnNames::RESERVATION_STATUS), $this->statusId)); } if (!empty($this->resourceStatusId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_STATUS_ID), $this->resourceStatusId)); } if (!empty($this->resourceStatusReasonId)) { $filter->_And(new SqlFilterEquals(new SqlFilterColumn(TableNames::RESOURCES, ColumnNames::RESOURCE_STATUS_REASON_ID), $this->resourceStatusReasonId)); } if (!empty($this->attributes)) { $attributeFilter = AttributeFilter::Create(TableNames::RESERVATION_SERIES_ALIAS . '.' . ColumnNames::SERIES_ID, $this->attributes); if ($attributeFilter != null) { $filter->_And($attributeFilter); } } foreach ($this->_and as $and) { $filter->_And($and); } return $filter; }