Example #1
0
 /**
  * @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;
 }
Example #2
0
 /**
  * @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;
 }
Example #3
0
 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;
 }