public function find() { $select = implode(", ", ['church.id as churchId', 'church.church', 'church.parishId', 'parish.parish', 'parish.identifier as parishIdentifier', 'parishProp.id as isIntentionalId', 'parishProp.value as isIntentional', 'parish.dioceseId', 'diocese.diocese', 'dioceseProp.id as isMetroId', 'dioceseProp.value as isMetro', 'diocese.provinceId', 'province.province', 'province.suiIurisId', 'suiIuris.suiIuris', 'suiIuris.riteId', 'rite.rite', 'rite.traditionId', 'tradition.tradition', 'tradition.archtraditionId', 'archtradition.archtradition', 'church.cityId', 'city.city', 'city.countyId', 'county.county', 'county.stateId', 'state.state2', 'state.state', 'state.countryId', 'country.country2', 'country.country3', 'country.country', 'country.continentId', 'continent.continent', 'church.religiousId', 'religious.religious', 'religious.religious1Id', 'religious1.religious1', 'religious1.religious2Id', 'religious2.religious2', 'religious2.religious3Id', 'religious3.religious3', 'religious3.religiousTopId', 'religiousTop.religiousTop', 'churchProp.id AS churchPropId', 'churchProp.value', 'churchAttr.attr']); $from = implode(" ", [Model::nsModel('Church') . ' AS church', 'LEFT JOIN ' . Model::nsModel('Parish') . ' AS parish ' . 'ON church.parishId = parish.id', 'LEFT JOIN ' . Model::nsModel('ParishProp') . ' AS parishProp ON (' . 'parishProp.parishId = parish.id AND ' . 'parishProp.parishAttrId = :isIntentionalId:)', 'LEFT JOIN ' . Model::nsModel('Diocese') . ' AS diocese ' . 'ON parish.dioceseId = diocese.id', 'LEFT JOIN ' . Model::nsModel('DioceseProp') . ' AS dioceseProp ON (' . 'dioceseProp.dioceseId = diocese.id AND ' . 'dioceseProp.dioceseAttrId = :isMetroId:)', 'LEFT JOIN ' . Model::nsModel('Province') . ' AS province ' . 'ON diocese.provinceId = province.id', 'LEFT JOIN ' . Model::nsModel('SuiIuris') . ' AS suiIuris ' . 'ON province.suiIurisId = suiIuris.id', 'LEFT JOIN ' . Model::nsModel('Rite') . ' AS rite ' . 'ON suiIuris.riteId = rite.id', 'LEFT JOIN ' . Model::nsModel('Tradition') . ' AS tradition ' . 'ON rite.traditionId = tradition.id', 'LEFT JOIN ' . Model::nsModel('Archtradition') . ' AS archtradition ' . 'ON tradition.archtraditionId = archtradition.id', 'LEFT JOIN ' . Model::nsModel('City') . ' AS city ' . 'ON church.cityId = city.Id', 'LEFT JOIN ' . Model::nsModel('County') . ' AS county ' . 'ON city.countyId = county.id', 'LEFT JOIN ' . Model::nsModel('State') . ' AS state ' . 'ON county.stateId = state.id', 'LEFT JOIN ' . Model::nsModel('Country') . ' AS country ' . 'ON state.countryId = country.id', 'LEFT JOIN ' . Model::nsModel('Continent') . ' AS continent ' . 'ON country.continentId = continent.id', 'LEFT JOIN ' . Model::nsModel('Religious') . ' AS religious ' . 'ON church.religiousId = religious.id', 'LEFT JOIN ' . Model::nsModel('Religious1') . ' AS religious1 ' . 'ON religious.religious1Id = religious1.id', 'LEFT JOIN ' . Model::nsModel('Religious2') . ' AS religious2 ' . 'ON religious1.religious2Id = religious2.id', 'LEFT JOIN ' . Model::nsModel('Religious3') . ' AS religious3 ' . 'ON religious2.religious3Id = religious3.id', 'LEFT JOIN ' . Model::nsModel('ReligiousTop') . ' AS religiousTop ' . 'ON religious3.religiousTopId = religiousTop.id', 'LEFT JOIN ' . Model::nsModel('ChurchProp') . ' AS churchProp ' . 'ON church.id = churchProp.churchId', 'LEFT JOIN ' . Model::nsModel('ChurchAttr') . ' AS churchAttr ' . 'ON churchProp.churchAttrId = churchAttr.id']); $order = "church.id, churchProp.churchAttrId, churchProp.multi"; $phql = "SELECT {$select} FROM {$from} ORDER BY {$order}"; $bind = ['isIntentionalId' => $this->lookup->getId('parishAttr', 'isIntentional'), 'isMetroId' => $this->lookup->getId('dioceseAttr', 'isMetro')]; foreach (Model::query($phql, $bind) as $row) { $props = empty($row->churchPropId) ? [] : ["{$row->attr}Id" => $row->churchPropId, $row->attr => $row->value]; if (!isset($this->churches[$row->churchId])) { unset($row->churchPropId, $row->value, $row->attr); $this->churches[$row->churchId] = $row; $this->churches[$row->churchId]->events = []; } foreach ($props as $key => $value) { if (property_exists($this->churches[$row->churchId], $key)) { $temp = (array) $this->churches[$row->churchId]->{$key}; $temp[] = $value; $this->churches[$row->churchId]->{$key} = $temp; } else { $this->churches[$row->churchId]->{$key} = $value; if ($key == 'site') { $this->churches[$row->churchId]->siteAT = rtrim(preg_replace('/^(http)?s?:?\\/?\\/?(www\\.)?/i', '', $value), " /"); } if ($key == 'nickname') { $names = "{$value} {$row->church} "; $padId = $this->padId($row->churchId); $this->churchMeta['sortKeys']["{$names}{$padId}"] = $row->churchId; } } } } $bind = array_keys($this->churches); $select = implode(", ", ['event.id AS eventId', 'event.event', 'event.eventTypeId', 'eventType.eventType', 'event.eventFilterId', 'eventFilter.eventFilter', 'event.churchId', 'event.startDate', 'event.stopDate', 'event.startMonth', 'startM.month as startM', 'event.stopMonth', 'stopM.month as stopM', 'event.startWeek', 'startW.week as startW', 'event.stopWeek', 'stopW.week as stopW', 'event.startDay', 'startD.day as startD', 'event.stopDay', 'stopD.day as stopD', 'event.startTime', 'event.stopTime', 'eventProp.id as eventPropId', 'eventProp.value', 'eventAttr.attr']); $from = implode(" ", [Model::nsModel('Event') . ' AS event', 'LEFT JOIN ' . Model::nsModel('EventType') . ' AS eventType ' . 'ON event.eventTypeId = eventType.id', 'LEFT JOIN ' . Model::nsModel('EventFilter') . ' AS eventFilter ' . 'ON event.eventFilterId = eventFilter.id', 'LEFT JOIN ' . Model::nsModel('Month') . ' AS startM ' . 'ON event.startMonth = startM.id', 'LEFT JOIN ' . Model::nsModel('Month') . ' AS stopM ' . 'ON event.stopMonth = stopM.id', 'LEFT JOIN ' . Model::nsModel('Week') . ' AS startW ' . 'ON event.startWeek = startW.id', 'LEFT JOIN ' . Model::nsModel('Week') . ' AS stopW ' . 'ON event.stopWeek = stopW.id', 'LEFT JOIN ' . Model::nsModel('Day') . ' AS startD ' . 'ON event.startDay = startD.id', 'LEFT JOIN ' . Model::nsModel('Day') . ' AS stopD ' . 'ON event.stopDay = stopD.id', 'LEFT JOIN ' . Model::nsModel('EventProp') . ' AS eventProp ' . 'ON event.id = eventProp.eventId', 'LEFT JOIN ' . Model::nsModel('EventAttr') . ' AS eventAttr ' . 'ON eventProp.eventAttrId = eventAttr.id']); $where = 'event.churchId in ' . Model::in($bind); $order = implode(", ", ['event.startTime', 'event.stopTime', 'event.id', 'eventProp.eventAttrId', 'eventProp.multi']); $phql = "SELECT {$select} FROM {$from} WHERE {$where} ORDER BY {$order}"; foreach (Model::query($phql, $bind) as $row) { $props = empty($row->eventPropId) ? [] : ["{$row->attr}Id" => $row->eventPropId, $row->attr => $row->value]; if (!isset($this->events[$row->eventId])) { unset($row->churchPropId, $row->value, $row->attr); $this->churches[$row->churchId]->events[] = $row->eventId; $this->events[$row->eventId] = $row; foreach (['start', 'stop'] as $prefix) { $timeString = $prefix . 'Time'; $temporal = new Temporal($row->{$timeString}, null, $this->lookup); foreach ($temporal->getTimeProperties() as $key => $value) { $key = $prefix . ucfirst($key); if ($key != $timeString) { $this->events[$row->eventId]->{$key} = $value; } } } $times = implode(" ", [$row->startDay, $row->stopDay, $row->startTime, $row->stopTime]); $padId = $this->padId($row->eventId); $this->eventMeta['sortKeys']["{$times}{$padId}"] = $row->eventId; $this->eventMeta['ordered'][] = $row->eventId; } foreach ($props as $key => $value) { if (property_exists($this->events[$row->eventId], $key)) { $temp = (array) $this->events[$row->eventId]->{$key}; $temp[] = $value; $this->events[$row->eventId]->{$key} = $temp; } else { $this->events[$row->eventId]->{$key} = $value; } } } ksort($this->churchMeta['sortKeys']); foreach ($this->churchMeta['sortKeys'] as $churchId) { $this->churchMeta['ordered'][] = $churchId; $this->makeDescription($churchId); $this->makeSchedule($churchId); } }