/**
* @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 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);
}
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;
}
/**
* @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;
}
