private function _buildQuery()
{
$treeId = intval($this->getTreeId());
$levels = JoaktreeHelper::getUserAccessLevels();
$displayAccess = JoaktreeHelper::getDisplayAccess();
$query = $this->_db->getQuery(true);
// select from persons
$query->select(' jpn.id ');
$query->select(' jpn.app_id ');
$query->select(' MIN( ' . JoaktreeHelper::getSelectFirstName() . ' ) AS firstName ');
$query->select(' MIN( ' . JoaktreeHelper::getSelectPatronym() . ' ) AS patronym ');
$query->select(' MIN( ' . JoaktreeHelper::getConcatenatedFamilyName() . ' ) AS familyName ');
$query->select(' MIN( ' . JoaktreeHelper::getSelectBirthYear() . ' ) AS birthDate ');
$query->select(' MIN( ' . JoaktreeHelper::getSelectDeathYear() . ' ) AS deathDate ');
$query->from(' #__joaktree_persons jpn ');
// select from admin persons
$query->innerJoin(JoaktreeHelper::getJoinAdminPersons(false));
// select from tree x persons
$query->innerJoin(' #__joaktree_tree_persons jtp ' . ' ON ( jtp.app_id = jpn.app_id ' . ' AND jtp.person_id = jpn.id ' . ' ) ');
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jtp.app_id ' . ' AND jte.id = jtp.tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $levels . ' ' . ' ) ');
// select from birth and death
$query->leftJoin(JoaktreeHelper::getJoinBirth());
$query->leftJoin(JoaktreeHelper::getJoinDeath());
// Get the WHERE, GROUP BY and ORDER BY clauses for the query
$wheres = $this->_buildContentWhere();
foreach ($wheres as $where) {
$query->where(' ' . $where . ' ');
}
$query->group(' jpn.id ');
$query->group(' jpn.app_id ');
$query->order(' ' . $this->_buildContentOrderBy() . ' ');
return $query;
}
function getJoaktreeList()
{
$related = array();
$jt_likes = array();
// we only searching for persons when currently one article is shown
if ($this->_option == 'com_content' && $this->_view == 'article' && $this->_id) {
$params = JComponentHelper::getParams('com_joaktree');
$indArticleLink = (int) $params->get('indArticleLink', 9);
// we only searching for persons when Joaktree setting is set
if ($indArticleLink != 0) {
$displayAccess = JoaktreeHelper::getDisplayAccess();
$userAccess = JoaktreeHelper::getUserAccess();
$accessLevels = JoaktreeHelper::getUserAccessLevels();
// select the meta keywords from the item
$query = $this->_db->getQuery(true);
$query->select(' metakey ');
$query->from(' #__content ');
$query->where(' id = ' . (int) $this->_id . ' ');
$this->_db->setQuery($query);
if ($metakey = trim($this->_db->loadResult())) {
// explode the meta keys on a comma
$keys = explode(',', $metakey);
// assemble any non-blank word(s)
foreach ($keys as $key) {
$key = trim($key);
if ($key) {
// no commas
$jt_likes[] = $this->_db->escape($key);
}
}
}
// process the "jt_likes" - search in joaktree tables
if (count($jt_likes)) {
// select from Joaktree tables
$query->clear();
$query->select(' jpn.app_id ');
$query->select(' jpn.id ');
$query->select(' CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) AS title ');
$query->select(' NULL AS created ');
$query->select(' jan.default_tree_id AS catid ');
$query->select(' NULL AS cat_access ');
$query->select(' NULL AS cat_state ');
$query->from(' #__joaktree_persons jpn ');
$query->innerJoin(' #__joaktree_admin_persons jan ' . ' ON ( jan.app_id = jpn.app_id ' . ' AND jan.id = jpn.id ' . ' AND jan.published = 1 ' . ' AND jan.page = 1 ' . ' AND ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' = 2 ) ' . ' ) ' . ' ) ');
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $accessLevels . ' ' . ' ) ');
// section to set the where-clause
if ($indArticleLink == 9) {
// 9: All options
$query->where(' jpn.id = "' . implode('" OR jpn.id = "', $jt_likes) . '" ' . ' OR CONCAT_WS( ' . $this->_db->quote('!') . ' ' . ' , jpn.app_id ' . ' , jpn.id ' . ' ) = "' . implode('" OR CONCAT_WS( ' . $this->_db->quote('!') . ' ' . ' , jpn.app_id ' . ' , jpn.id ' . ' ) = "', $jt_likes) . '" ' . ' OR CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) ' . ' LIKE "%' . implode('%" OR CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) LIKE "%', $jt_likes) . '%" ');
}
if ($indArticleLink == 1) {
// 1: By ID
$query->where(' jpn.id = "' . implode('" OR jpn.id = "', $jt_likes) . '" ' . ' OR CONCAT_WS( ' . $this->_db->quote('!') . ' ' . ' , jpn.app_id ' . ' , jpn.id ' . ' ) = "' . implode('" OR CONCAT_WS( ' . $this->_db->quote('!') . ' ' . ' , jpn.app_id ' . ' , jpn.id ' . ' ) = "', $jt_likes) . '" ');
}
if ($indArticleLink == 2) {
// 2: By first name
$query->where(' jpn.firstName ' . ' LIKE "%' . implode('%" OR jpn.firstName LIKE "%', $jt_likes) . '%" ');
}
if ($indArticleLink == 3) {
// 3: By family name
$query->where(' CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) ' . ' LIKE "%' . implode('%" OR CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) LIKE "%', $jt_likes) . '%" ');
}
if ($indArticleLink == 4 or $indArticleLink == 5) {
// 4: By first + family name; 5: All names
$query->where(' CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) ' . ' LIKE "%' . implode('%" OR CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) LIKE "%', $jt_likes) . '%" ');
}
// end section for setting where-clause -> continue with creating the query
$query->order(' CONCAT_WS( ' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) ');
$this->_db->setQuery($query);
$temp = $this->_db->loadObjectList();
if (count($temp)) {
// get menuId & technology
$menus = JoaktreeHelper::getMenus('joaktree');
$technology = JoaktreeHelper::getTechnology();
$linkBase = 'index.php?option=com_joaktree&view=joaktree&tech=' . $technology . '';
foreach ($temp as $row) {
$menuItemId = $menus[$row->catid];
$row->route = JRoute::_($linkBase . '&Itemid=' . $menuItemId . '&treeId=' . $row->catid . '&personId=' . $row->app_id . '!' . $row->id);
$row->robot = $technology == 'a' ? '' : 'rel="noindex, nofollow"';
$related[] = $row;
}
}
unset($temp);
}
}
}
return $related;
}
private function _buildQuery()
{
$params = JoaktreeHelper::getJTParams();
//JComponentHelper::getParams('com_joaktree') ;
$abbreviation = (int) $params->get('abbreviation', 0);
$levels = JoaktreeHelper::getUserAccessLevels();
$displayAccess = JoaktreeHelper::getDisplayAccess();
if ($abbreviation == null or $abbreviation <= 0) {
// no abbreviation of names
$displayFamilyName = JoaktreeHelper::getConcatenatedDutchFamilyName(false);
} else {
// abbreviation on n characters, where n = $abbreviation
$displayFamilyName = 'SUBSTR( ' . JoaktreeHelper::getConcatenatedDutchFamilyName(false) . ' ' . ' , 1 ' . ' , ' . $abbreviation . ' ' . ' )';
}
$query = $this->_db->getQuery(true);
$query->select(' COUNT(' . $displayFamilyName . ') AS nameCount ');
$query->select(' ' . $displayFamilyName . ' AS familyName ');
$query->from(' #__joaktree_tree_persons jtp ');
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jtp.app_id ' . ' AND jte.id = jtp.tree_id ' . ' ) ');
$query->innerJoin(' #__joaktree_persons jpn ' . ' ON ( jpn.app_id = jtp.app_id ' . ' AND jpn.id = jtp.person_id ' . ' ) ');
$query->innerJoin(JoaktreeHelper::getJoinAdminPersons(false));
// Get the WHERE clauses for the query
$wheres = $this->_buildContentWhere();
foreach ($wheres as $where) {
$query->where(' ' . $where . ' ');
}
// GROUP BY
$query->group(' ' . $displayFamilyName . ' ');
return $query;
}
private function getQueryTree($selectThesePersonsOnly = array())
{
$levels = JoaktreeHelper::getUserAccessLevels();
$displayAccess = JoaktreeHelper::getDisplayAccess();
$query = $this->_db->getQuery(true);
if (isset($this->params['excludePersonEvents']) && !empty($this->params['excludePersonEvents'])) {
$tmp = (array) json_decode($this->params['excludePersonEvents']);
$excludePersonEvents = 'AND jpe.code NOT IN ("' . implode('","', $tmp) . '") ';
} else {
$excludePersonEvents = '';
}
if (isset($this->params['excludeRelationEvents']) && !empty($this->params['excludeRelationEvents'])) {
$tmp = (array) json_decode($this->params['excludeRelationEvents']);
$excludeRelationEvents = 'AND jre.code NOT IN ("' . implode('","', $tmp) . '") ';
} else {
$excludeRelationEvents = '';
}
if (isset($this->params['includeEvents']) && !empty($this->params['includeEvents'])) {
$tmp = (array) json_decode($this->params['includeEvents']);
$includePersonEvents = 'AND jpe.code IN ("' . implode('","', $tmp) . '") ';
$includeRelationEvents = 'AND jre.code IN ("' . implode('","', $tmp) . '") ';
} else {
$includePersonEvents = '';
$includeRelationEvents = '';
}
$query->select(' jln.id ');
$query->select(' jln.value ');
$query->select(' jln.longitude ');
$query->select(' jln.latitude ');
$query->select(' COUNT( iv_event.code ) AS label ');
$query->select(' GROUP_CONCAT( ' . ' DISTINCT CONCAT_WS(' . $this->_db->Quote('#') . ' ' . ' , CONCAT_WS(' . $this->_db->Quote(' ') . ' , jpn1.firstName ' . ' , jpn1.namePreposition ' . ' , jpn1.familyName ' . ' , IF((ISNULL(jpn2.firstName) && ISNULL(jpn2.familyName )), ' . $this->_db->Quote('') . ', ' . $this->_db->Quote('+') . ')' . ' , jpn2.firstName ' . ' , jpn2.namePreposition ' . ' , jpn2.familyName ' . ' ) ' . ' , jpn1.id ' . ' , iv_event.code ' . ' , iv_event.eventDate ' . ' , IFNULL(jan2.default_tree_id, jan1.default_tree_id) ' . ' ) ' . ' ORDER BY SUBSTR( RTRIM(iv_event.eventDate), -4 ) ' . ' SEPARATOR ' . $this->_db->Quote('|') . ' ' . ' ) AS information ');
$query->from(' ( SELECT jpe.person_id AS person_id_1 ' . ' , NULL AS person_id_2 ' . ' , jpe.code AS code ' . ' , ' . $this->_db->Quote('person') . ' AS level ' . ' , jpe.eventDate AS eventDate ' . ' , jpe.loc_id AS loc_id ' . ' , jpe.app_id AS app_id ' . ' , jdsp.access AS access ' . ' , jdsp.accessLiving AS accessLiving ' . ' FROM #__joaktree_person_events jpe ' . ' INNER JOIN #__joaktree_display_settings jdsp ' . ' ON ( jdsp.code = jpe.code ' . ' AND jdsp.level = ' . $this->_db->Quote('person') . ' ' . ' AND jdsp.published = true ' . ' ) ' . ' WHERE jpe.location IS NOT NULL ' . (count($selectThesePersonsOnly) ? $selectThesePersonsOnly['personEvents'] : '') . $excludePersonEvents . $includePersonEvents . ' UNION ' . ' SELECT jre.person_id_1 AS person_id_1 ' . ' , jre.person_id_2 AS person_id_2 ' . ' , jre.code AS code ' . ' , ' . $this->_db->Quote('relation') . ' AS level ' . ' , jre.eventDate AS eventDate ' . ' , jre.loc_id AS loc_id ' . ' , jre.app_id AS app_id ' . ' , jdsr.access AS access ' . ' , jdsr.accessLiving AS accessLiving ' . ' FROM #__joaktree_relation_events jre ' . ' INNER JOIN #__joaktree_display_settings jdsr ' . ' ON ( jdsr.code = jre.code ' . ' AND jdsr.level = ' . $this->_db->Quote('relation') . ' ' . ' AND jdsr.published = true ' . ' ) ' . ' WHERE jre.location IS NOT NULL ' . (count($selectThesePersonsOnly) ? $selectThesePersonsOnly['relationEvents'] : '') . $excludeRelationEvents . $includeRelationEvents . ' ) AS iv_event ');
if ($this->params['selection'] == 'tree' || $this->params['selection'] == 'location') {
$query->innerJoin(' #__joaktree_tree_persons jtp ' . ' ON ( jtp.app_id = iv_event.app_id ' . ' AND ( jtp.person_id = iv_event.person_id_1 ' . ' OR jtp.person_id = IFNULL(iv_event.person_id_2, iv_event.person_id_1) ' . ' ) ' . ' ) ');
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jtp.app_id ' . ' AND jte.id = jtp.tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $levels . ' ' . ' ) ');
$query->where(' jtp.tree_id = ' . $this->params['tree_id'] . ' ');
}
$query->innerJoin(' #__joaktree_admin_persons jan1 ' . ' ON ( jan1.app_id = iv_event.app_id ' . ' AND jan1.id = iv_event.person_id_1 ' . ' AND jan1.published = true ' . ' AND ( ( jan1.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' > 1 ' . ' AND iv_event.access IN ' . $levels . ' ' . ' ) ' . ' OR ( jan1.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' > 1 ' . ' AND iv_event.accessLiving IN ' . $levels . ' ' . ' ) ' . ' ) ' . ' ) ');
$query->innerJoin(' #__joaktree_admin_persons jan2 ' . ' ON ( jan2.app_id = iv_event.app_id ' . ' AND jan2.id = IFNULL(iv_event.person_id_2, iv_event.person_id_1) ' . ' AND jan2.published = true ' . ' AND ( ( jan2.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' > 1 ' . ' AND iv_event.access IN ' . $levels . ' ' . ' ) ' . ' OR ( jan2.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' > 1 ' . ' AND iv_event.accessLiving IN ' . $levels . ' ' . ' ) ' . ' ) ' . ' ) ');
$query->innerJoin(' #__joaktree_locations jln ' . ' ON ( jln.id = iv_event.loc_id ' . ' AND jln.latitude IS NOT NULL ' . ' AND jln.latitude <> 0 ' . ' AND jln.longitude IS NOT NULL ' . ' AND jln.longitude <> 0 ' . ' AND jln.indDeleted = 0 ' . ' ) ');
$query->innerJoin(' #__joaktree_persons jpn1 ' . ' ON ( jpn1.app_id = iv_event.app_id ' . ' AND jpn1.id = iv_event.person_id_1 ' . ' ) ');
$query->leftJoin(' #__joaktree_persons jpn2 ' . ' ON ( jpn2.app_id = iv_event.app_id ' . ' AND jpn2.id = iv_event.person_id_2 ' . ' ) ');
if ($this->params['selection'] == 'tree' && $this->params['relations']) {
$query->where(' jtp.lineage IS NOT NULL ');
}
if ($this->params['selection'] == 'location') {
if (isset($this->params['distance']) && $this->params['distance'] > 0) {
// earth's mean radius in km: 6371
// x = (lon2-lon1) * cos((lat1+lat2)/2)
// y = (lat2-lat1)
// d = sqrt(x*x + y*y) * R
$query->where(' (SQRT( ( (RADIANS(jln.longitude) - RADIANS(' . $this->params['longitude'] . ')) ' . ' * COS( (RADIANS(' . $this->params['latitude'] . ') + RADIANS(jln.latitude)) / 2 ) ' . ' * (RADIANS(jln.longitude) - RADIANS(' . $this->params['longitude'] . ')) ' . ' * COS( (RADIANS(' . $this->params['latitude'] . ') + RADIANS(jln.latitude)) / 2 ) ' . ' ) ' . ' + ( (RADIANS(jln.latitude) - RADIANS(' . $this->params['latitude'] . ')) ' . ' * (RADIANS(jln.latitude) - RADIANS(' . $this->params['latitude'] . ')) ' . ' )' . ' ) * 6371) < ' . $this->params['distance'] . ' ');
} else {
$query->where(' jln.id = ' . (int) $this->params['loc_id'] . ' ');
}
}
if (isset($this->params['subject']) && !empty($this->params['subject'])) {
$query->where('( UPPER(jpn1.familyName) LIKE UPPER(' . $this->_db->quote('%' . $this->params['subject'] . '%') . ') ' . 'OR UPPER(jpn2.familyName) LIKE UPPER(' . $this->_db->quote('%' . $this->params['subject'] . '%') . ') ' . ')');
}
if (isset($this->params['period_start']) && !empty($this->params['period_start']) && $this->params['period_start'] > 0) {
$query->where('SUBSTR( RTRIM(iv_event.eventDate), -4 ) >= ' . $this->params['period_start'] . ' ');
}
if (isset($this->params['period_end']) && !empty($this->params['period_end']) && $this->params['period_end'] > 0) {
$query->where('SUBSTR( RTRIM(iv_event.eventDate), -4 ) <= ' . $this->params['period_end'] . ' ');
}
$query->group(' jln.id ');
$query->group(' jln.value ');
$query->group(' jln.longitude ');
$query->group(' jln.latitude ');
$query->order(' COUNT( iv_event.code ) ');
$query->order(' jln.value ');
return $query;
}
public function getPersonName()
{
$personId = JoaktreeHelper::getPersonId(false, true);
$appId = JoaktreeHelper::getApplicationId(false, true);
if (empty($appId) || empty($personId)) {
$retObj = $this->getReturnObject();
if (is_object($retObj) && $retObj->object == 'prsn') {
$appId = $retObj->app_id;
$personId = $retObj->object_id;
}
}
if (!empty($appId) && !empty($personId)) {
$displayAccess = JoaktreeHelper::getDisplayAccess();
$db = $this->getDbo();
$query = $db->getQuery(true);
$query->select(JoaktreeHelper::getConcatenatedFullName() . ' AS fullName ');
$query->from(' #__joaktree_persons jpn ');
$query->where(' jpn.app_id = ' . (int) $appId . ' ');
$query->where(' jpn.id = ' . $db->quote($personId) . ' ');
$query->innerJoin(JoaktreeHelper::getJoinAdminPersons(true));
$db->setQuery($query);
$name = $db->loadResult();
if ($error = $db->getErrorMsg()) {
throw new JException($error);
}
}
return isset($name) ? $name : null;
}
public function getList($moduleId)
{
$moduleId = (int) $moduleId;
$params = $this->getModuleParams($moduleId);
$periodType = $params->get('periodType');
$limit = (int) $params->get('searchlimit', 10);
$userAccessLevels = JoaktreeHelper::getUserAccessLevels();
$personStatus = $params->get('personStatus', 0);
$showTitle = $params->get('showHeading', 1);
$today = getdate();
if ($params->get('freeChoice')) {
// only when user is allowed to enter a day/month we are checking these values.
$day = JoaktreeHelper::getDay();
$month = JoaktreeHelper::getMonth();
}
// for titles we want to know if we have day and/or months from the front end
$indUI = !isset($day) && !isset($month) || $day == 0 && $month == 0 ? false : true;
// if day and/or month is not set, we use today's info
$day = !isset($day) || $day == 0 ? $today['mday'] : $day;
$month = !isset($month) || $month == 0 ? $today['mon'] : $month;
if ($showTitle == 1) {
if (!$indUI) {
// nothing from front end -> use standard titles
switch ($periodType) {
case 2:
// continue
// continue
case 3:
$this->_title = JText::_('JTMOD_TMYA_HEADING_THISMONTH');
break;
case 4:
// continue
// continue
case 5:
$this->_title = JText::_('JTMOD_TMYA_HEADING_THISWEEK');
break;
case 0:
// continue
// continue
case 1:
// continue
// continue
default:
$this->_title = JText::_('JTMOD_TMYA_HEADING_TODAY');
break;
}
} else {
// use information from front end
switch ($periodType) {
case 2:
// continue
// continue
case 3:
$this->_title = JText::_($this->getMonthName($month));
break;
case 4:
// continue
// continue
case 5:
// continue
// continue
case 0:
// continue
// continue
case 1:
// continue
// continue
default:
$this->_title = $day . ' ' . JText::_($this->getMonthName($month));
break;
}
}
} else {
$this->_title = null;
}
switch ($periodType) {
case 2:
// continue
// continue
case 3:
$this->_buttonText = JText::_('JTMOD_TMYA_HEADING_THISMONTH');
break;
case 4:
// continue
// continue
case 5:
$this->_buttonText = JText::_('JTMOD_TMYA_HEADING_THISWEEK');
break;
case 0:
// continue
// continue
case 1:
// continue
// continue
default:
$this->_buttonText = JText::_('JTMOD_TMYA_HEADING_TODAY');
break;
}
$result = array();
$displayAccess = JoaktreeHelper::getDisplayAccess();
$userAccess = JoaktreeHelper::getUserAccess();
$where = $this->_buildContentWhere($day, $month, $today['year']);
$order = $this->_buildOrderBy();
// retrieve person events
$query = '( SELECT CONCAT_WS(' . $this->_db->quote(' ') . ' ' . ' , jpn.firstName ' . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) AS name ' . ', jet.app_id AS appId ' . ', jet.person_id AS personId ' . ', jan.default_tree_id AS treeId ' . ', IF ( ( jet.code = ' . $this->_db->Quote('EVEN') . ' ) ' . ' , IFNULL( jet.type, jet.code ) ' . ' , jet.code ' . ' ) AS code ' . ', jet.eventdate ' . ', DATE_FORMAT(STR_TO_DATE(eventdate, "%d %M %Y"), "%m%d") AS sortingdate ' . ', SUBSTR( RTRIM(jet.eventdate), -4 ) AS eventyear ' . ', jpn.familyName AS familyName ' . 'FROM #__joaktree_person_events jet ' . 'JOIN #__joaktree_persons jpn ' . 'ON ( jpn.app_id = jet.app_id ' . ' AND jpn.id = jet.person_id ' . ' ) ' . 'JOIN #__joaktree_admin_persons jan ' . 'ON ( jan.app_id = jpn.app_id ' . ' AND jan.id = jpn.id ' . ' AND jan.published = true ' . ($personStatus == 0 ? 'AND jan.living = false ' : ' ') . ($personStatus == 1 ? 'AND jan.living = true ' : ' ') . ' AND ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' = 2 ) ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_trees jte ' . 'ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $userAccessLevels . ' ' . ' ) ' . 'JOIN #__joaktree_display_settings jds ' . 'ON ( jds.code = jet.code ' . ' AND jds.level = ' . $this->_db->quote('person') . ' ' . ' AND jds.published = true ' . ' AND ( (jan.living = false AND jds.access IN ' . $userAccessLevels . ') ' . ' OR (jan.living = true AND jds.accessLiving IN ' . $userAccessLevels . ') ' . ' ) ' . ' ) ' . $where . ' ) ' . 'UNION ' . '( SELECT CONCAT_WS(' . $this->_db->quote(' + ') . ' ' . ' , CONCAT_WS(' . $this->_db->quote(' ') . ' ' . ' , jpn1.firstName ' . ' , jpn1.namePreposition ' . ' , jpn1.familyName ' . ' ) ' . ' , CONCAT_WS(' . $this->_db->quote(' ') . ' ' . ' , jpn2.firstName ' . ' , jpn2.namePreposition ' . ' , jpn2.familyName ' . ' ) ' . ' ) AS name ' . ', jet.app_id AS appId ' . ', jet.person_id_1 AS personId ' . ', jan1.default_tree_id AS treeId ' . ', IF ( ( jet.code = ' . $this->_db->Quote('EVEN') . ' ) ' . ' , IFNULL( jet.type, jet.code ) ' . ' , jet.code ' . ' ) AS code ' . ', jet.eventdate ' . ', DATE_FORMAT(STR_TO_DATE(eventdate, "%d %M %Y"), "%m%d") AS sortingdate ' . ', SUBSTR( RTRIM(jet.eventdate), -4 ) AS eventyear ' . ', jpn1.familyName AS familyName ' . 'FROM #__joaktree_relation_events jet ' . 'JOIN #__joaktree_persons jpn1 ' . 'ON ( jpn1.app_id = jet.app_id ' . ' AND jpn1.id = jet.person_id_1 ' . ' ) ' . 'JOIN #__joaktree_admin_persons jan1 ' . 'ON ( jan1.app_id = jpn1.app_id ' . ' AND jan1.id = jpn1.id ' . ' AND jan1.published = true ' . ($personStatus == 0 ? 'AND jan1.living = false ' : ' ') . ($personStatus == 1 ? 'AND jan1.living = true ' : ' ') . ' AND ( (jan1.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ) ' . ' OR (jan1.living = true AND ' . $displayAccess['NAMEname']->living . ' = 2 ) ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_trees jte1 ' . 'ON ( jte1.app_id = jan1.app_id ' . ' AND jte1.id = jan1.default_tree_id ' . ' AND jte1.published = true ' . ' AND jte1.access IN ' . $userAccessLevels . ' ' . ' ) ' . 'JOIN #__joaktree_persons jpn2 ' . 'ON ( jpn2.app_id = jet.app_id ' . ' AND jpn2.id = jet.person_id_2 ' . ' ) ' . 'JOIN #__joaktree_admin_persons jan2 ' . 'ON ( jan2.app_id = jpn2.app_id ' . ' AND jan2.id = jpn2.id ' . ' AND jan2.published = true ' . ($personStatus == 0 ? 'AND jan2.living = false ' : ' ') . ($personStatus == 1 ? 'AND jan2.living = true ' : ' ') . ' AND ( (jan2.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ) ' . ' OR (jan2.living = true AND ' . $displayAccess['NAMEname']->living . ' = 2 ) ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_trees jte2 ' . 'ON ( jte2.app_id = jan2.app_id ' . ' AND jte2.id = jan2.default_tree_id ' . ' AND jte2.published = true ' . ' AND jte2.access IN ' . $userAccessLevels . ' ' . ' ) ' . 'JOIN #__joaktree_display_settings jds ' . 'ON ( jds.code = jet.code ' . ' AND jds.level = ' . $this->_db->quote('relation') . ' ' . ' AND jds.published = true ' . ' AND ( ( jan1.living = false ' . ' AND jan2.living = false ' . ' AND jds.access IN ' . $userAccessLevels . ' ' . ' ) ' . ' OR ( ( jan1.living = true ' . ' OR jan2.living = true ' . ' ) ' . ' AND jds.accessLiving IN ' . $userAccessLevels . ' ' . ' ) ' . ' ) ' . ' ) ' . $where . ' ) ' . $order . ' ';
$this->_db->setQuery($query, 0, $limit);
$temp = $this->_db->loadObjectList();
if (count($temp)) {
// get menuId & technology
$menus = JoaktreeHelper::getMenus('joaktree');
$technology = JoaktreeHelper::getTechnology();
$linkBase = 'index.php?option=com_joaktree&view=joaktree&tech=' . $technology . '';
$robot = $technology == 'a' ? '' : 'rel="noindex, nofollow"';
foreach ($temp as $row) {
$menuItemId = $menus[$row->treeId];
$row->route = JRoute::_($linkBase . '&Itemid=' . $menuItemId . '&treeId=' . $row->treeId . '&personId=' . $row->appId . '!' . $row->personId);
$row->robot = $robot;
$row->yearsago = $today['year'] - (int) $row->eventyear;
$tmp = ucwords(strtolower($row->eventdate));
$row->eventday = substr(JoaktreeHelper::convertDateTime($tmp), 0, -4);
$row->eventdate = JoaktreeHelper::displayDate($row->eventdate);
$row->code = JText::_($row->code);
$result[] = $row;
}
}
unset($temp);
return $result;
}
public function getItems()
{
// information is only selected for level: public
$public = true;
$userAccessLevels = '(1)';
$displayAccess = JoaktreeHelper::getDisplayAccess($public);
// retrieve persons
$db = $this->getDbo();
$query = $db->getQuery(true);
// select the basics
$query->select(' jpn.app_id ');
$query->select(' jpn.id ');
$query->select(JoaktreeHelper::getSelectFirstName() . ' AS firstName ');
$query->select(JoaktreeHelper::getConcatenatedFamilyName() . ' AS familyName ');
$query->from(' #__joaktree_persons jpn ');
// privacy filter
$query->select(' jan.default_tree_id AS treeId ');
$query->innerJoin(JoaktreeHelper::getJoinAdminPersons(false));
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $userAccessLevels . ' ' . ' AND jte.indGendex = 2 ' . ' ) ');
// birth info
$query->select(' birth.eventDate AS birthDate ');
$query->select(' birth.location AS birthPlace ');
$query->leftJoin(' #__joaktree_person_events birth ' . ' ON ( birth.app_id = jpn.app_id ' . ' AND birth.person_id = jpn.id ' . ' AND birth.code = ' . $this->_db->Quote('BIRT') . ' ' . ' AND ( (jan.living = false AND ' . $displayAccess['BIRTperson']->notLiving . ' = 2 ) ' . ' OR (jan.living = true AND ' . $displayAccess['BIRTperson']->living . ' = 2 ) ' . ' ) ' . ' ) ');
// death info
$query->select(' death.eventDate AS deathDate ');
$query->select(' death.location AS deathPlace ');
$query->leftJoin(' #__joaktree_person_events death ' . ' ON ( death.app_id = jpn.app_id ' . ' AND death.person_id = jpn.id ' . ' AND death.code = ' . $this->_db->Quote('DEAT') . ' ' . ' AND ( (jan.living = false AND ' . $displayAccess['DEATperson']->notLiving . ' = 2 ) ' . ' OR (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' = 2 ) ' . ' ) ' . ' ) ');
//$query = 'SELECT jpn.app_id '
// .', jpn.id '
// .', jan.default_tree_id AS treeId '
// .', jpn.firstName '
// .', CONCAT_WS('.$this->_db->quote(' ').' '
// .' , jpn.namePreposition '
// .' , jpn.familyName '
// .' ) AS familyName '
// // no alternative text is shown
// .', birth.eventDate AS birthDate '
// .', birth.location AS birthPlace '
// .', death.eventDate AS deathDate '
// .', death.location AS deathPlace '
// .'FROM #__joaktree_persons jpn '
// .'JOIN #__joaktree_admin_persons jan '
// .'ON ( jan.app_id = jpn.app_id '
// .' AND jan.id = jpn.id '
// .' AND jan.published = true '
// // privacy filter
// .' AND ( (jan.living = false AND '.$displayAccess['NAMEname']->notLiving.' = 2 ) '
// .' OR (jan.living = true AND '.$displayAccess['NAMEname']->living.' = 2 ) '
// .' ) '
// .' ) '
// .'JOIN #__joaktree_trees jte '
// .'ON ( jte.app_id = jan.app_id '
// .' AND jte.id = jan.default_tree_id '
// .' AND jte.published = true '
// .' AND jte.access IN '.$userAccessLevels.' '
// // only trees with Gendex = yes (=2)
// .' AND jte.indGendex = 2 '
// .' ) '
// .'LEFT JOIN #__joaktree_person_events birth '
// .'ON ( birth.app_id = jpn.app_id '
// .' AND birth.person_id = jpn.id '
// .' AND birth.code = '.$this->_db->Quote('BIRT').' '
// // no alternative text is shown
// .' AND ( (jan.living = false AND '.$displayAccess['BIRTperson']->notLiving.' = 2 ) '
// .' OR (jan.living = true AND '.$displayAccess['BIRTperson']->living.' = 2 ) '
// .' ) '
// .' ) '
// .'LEFT JOIN #__joaktree_person_events death '
// .'ON ( death.app_id = jpn.app_id '
// .' AND death.person_id = jpn.id '
// .' AND death.code = '.$this->_db->Quote('DEAT').' '
// // no alternative text is shown
// .' AND ( (jan.living = false AND '.$displayAccess['DEATperson']->notLiving.' = 2 ) '
// .' OR (jan.living = true AND '.$displayAccess['DEATperson']->living.' = 2 ) '
// .' ) '
// .' ) ';
$this->_db->setQuery($query);
$result = $this->_db->loadObjectList();
return $result;
}
private function _buildQuery()
{
$levels = JoaktreeHelper::getUserAccessLevels();
$displayAccess = JoaktreeHelper::getDisplayAccess();
$treeId = intval($this->getTreeId());
$query = $this->_db->getQuery(true);
$query->select(' DISTINCT jln.value AS location ');
$query->select(' jln.id AS loc_id ');
$query->select(' !(ISNULL(jln.latitude) AND ISNULL(jln.longitude)) AS indGeocode ');
$query->from(' #__joaktree_locations jln ');
$query->innerJoin(' ( SELECT jpe.person_id AS person_id_1 ' . ' , NULL AS person_id_2 ' . ' , jpe.code AS code ' . ' , ' . $this->_db->Quote('person') . ' AS level ' . ' , jpe.loc_id AS loc_id ' . ' , jpe.app_id AS app_id ' . ' , jdsp.access AS access ' . ' , jdsp.accessLiving AS accessLiving ' . ' FROM #__joaktree_person_events jpe ' . ' INNER JOIN #__joaktree_display_settings jdsp ' . ' ON ( jdsp.code = jpe.code ' . ' AND jdsp.level = ' . $this->_db->Quote('person') . ' ' . ' AND jdsp.published = true ' . ' ) ' . ' WHERE jpe.location IS NOT NULL ' . ' UNION ' . ' SELECT jre.person_id_1 AS person_id_1 ' . ' , jre.person_id_2 AS person_id_2 ' . ' , jre.code AS code ' . ' , ' . $this->_db->Quote('relation') . ' AS level ' . ' , jre.loc_id AS loc_id ' . ' , jre.app_id AS app_id ' . ' , jdsr.access AS access ' . ' , jdsr.accessLiving AS accessLiving ' . ' FROM #__joaktree_relation_events jre ' . ' INNER JOIN #__joaktree_display_settings jdsr ' . ' ON ( jdsr.code = jre.code ' . ' AND jdsr.level = ' . $this->_db->Quote('relation') . ' ' . ' AND jdsr.published = true ' . ' ) ' . ' WHERE jre.location IS NOT NULL ' . ' ) AS iv_event ' . ' ON ( iv_event.loc_id = jln.id ) ');
$query->innerJoin(' #__joaktree_tree_persons jtp ' . ' ON ( jtp.app_id = iv_event.app_id ' . ' AND jtp.tree_id = ' . (int) $treeId . ' ' . ' AND jtp.person_id IN (iv_event.person_id_1, iv_event.person_id_2) ' . ' ) ');
$query->innerJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jtp.app_id ' . ' AND jte.id = jtp.tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $levels . ' ' . ' ) ');
$query->innerJoin(' #__joaktree_admin_persons jan1 ' . ' ON ( jan1.app_id = iv_event.app_id ' . ' AND jan1.id = iv_event.person_id_1 ' . ' AND jan1.published = true ' . ' AND ( ( jan1.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' > 1 ' . ' AND iv_event.access IN ' . $levels . ' ' . ' ) ' . ' OR ( jan1.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' > 1 ' . ' AND iv_event.accessLiving IN ' . $levels . ' ' . ' ) ' . ' ) ' . ' ) ');
$query->innerJoin(' #__joaktree_admin_persons jan2 ' . ' ON ( jan2.app_id = iv_event.app_id ' . ' AND jan2.id = IFNULL(iv_event.person_id_2, iv_event.person_id_1) ' . ' AND jan2.published = true ' . ' AND ( ( jan2.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' > 1 ' . ' AND iv_event.access IN ' . $levels . ' ' . ' ) ' . ' OR ( jan2.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' > 1 ' . ' AND iv_event.accessLiving IN ' . $levels . ' ' . ' ) ' . ' ) ' . ' ) ');
// Get the WHERE clauses for the query + GROUP BY
$wheres = $this->_buildContentWhere();
foreach ($wheres as $where) {
$query->where(' ' . $where . ' ');
}
$query->order(' jln.value ');
return $query;
}
public function getJoinDeath()
{
$db = JFactory::getDBO();
$displayAccess = JoaktreeHelper::getDisplayAccess();
$join = ' #__joaktree_person_events death ' . ' ON ( death.app_id = jpn.app_id ' . ' AND death.person_id = jpn.id ' . ' AND death.code = ' . $db->Quote('DEAT') . ' ' . ' AND ( (jan.living = false AND ' . $displayAccess['DEATperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' > 0 ) ' . ' ) ' . ' ) ';
return $join;
}
public function getPartnerEvents($relation_id, $living)
{
$displayAccess = JoaktreeHelper::getDisplayAccess();
$query = $this->_db->getQuery(true);
// select from relation events
$query->select(' IF( ( jre.code = ' . $this->_db->Quote('EVEN') . ' ) ' . ' , IFNULL( jre.type, jre.code ) ' . ' , jre.code ' . ' ) AS code ');
$query->select(' IF( ( jre.code = ' . $this->_db->Quote('EVEN') . ' ) ' . ' , NULL ' . ' , jre.type ' . ' ) AS type ');
$query->select(' jre.orderNumber ');
$query->from(' #__joaktree_relation_events jre ');
$query->where(' jre.app_id = ' . $this->app_id . ' ');
$query->where(' ( ( jre.person_id_1 = ' . $this->_db->Quote($this->id) . ' ' . ' AND jre.person_id_2 = ' . $this->_db->Quote($relation_id) . ' ' . ' ) ' . ' OR ( jre.person_id_2 = ' . $this->_db->Quote($this->id) . ' ' . ' AND jre.person_id_1 = ' . $this->_db->Quote($relation_id) . ' ' . ' ) ' . ' ) ');
// select from settings
$query->select(' jds.ordering ');
$query->innerJoin(' #__joaktree_display_settings jds ' . ' ON ( jds.code = jre.code ' . ' AND jds.level = ' . $this->_db->Quote('relation') . ' ' . ' AND jds.published = true ' . ' ) ');
// ORDER
$query->order(' jds.ordering ');
$query->order(' jre.orderNumber ');
// events
if (!$this->living and !$living) {
// both not living
$query->select(' jre.eventDate ');
$query->select(' jre.location ');
$query->select(' jre.value ');
$query->select(' IF( ' . $displayAccess['ENOTrelation']->notLiving . ' > 0 ' . ' , jre.indNote ' . ' , false ' . ' ) AS indNote ');
$query->select(' false AS indAltNote ');
$query->select(' IF( ' . $displayAccess['ESOUrelation']->notLiving . ' > 0 ' . ' , jre.indCitation ' . ' , false ' . ' ) AS indCitation ');
$query->select(' false AS indAltSource ');
$query->where(' jds.access IN ' . $this->_levels . ' ');
} else {
// at least one is still living
$query->select(' IF( jds.accessLiving NOT IN ' . $this->_levels . ' ' . ' , ' . $this->_db->Quote(JText::_('JT_ALTERNATIVE')) . ' ' . ' , jre.eventDate ' . ' ) AS eventDate ');
$query->select(' IF( jds.accessLiving NOT IN ' . $this->_levels . ' ' . ' , NULL ' . ' , jre.location ' . ' ) AS location ');
$query->select(' IF( jds.accessLiving NOT IN ' . $this->_levels . ' ' . ' , NULL ' . ' , jre.value ' . ' ) AS value ');
$query->select(' IF( ' . $displayAccess['ENOTrelation']->living . ' > 0 ' . ' , jre.indNote ' . ' , false ' . ' ) AS indNote ');
$query->select(' IF( ' . $displayAccess['ENOTrelation']->living . ' = 1 ' . ' , true ' . ' , false ' . ' ) AS indAltNote ');
$query->select(' IF( ' . $displayAccess['ESOUrelation']->living . ' > 0 ' . ' , jre.indCitation ' . ' , false ' . ' ) AS indCitation ');
$query->select(' IF( ' . $displayAccess['ESOUrelation']->living . ' = 1 ' . ' , true ' . ' , false ' . ' ) AS indAltSource ');
$query->where(' ( jds.accessLiving IN ' . $this->_levels . ' ' . ' OR jds.altLiving IN ' . $this->_levels . ' ' . ' ) ');
}
$this->_db->setQuery($query);
$this->partnerEvents = $this->_db->loadObjectList();
return $this->partnerEvents;
}
function onContentSearch($text, $phrase = '', $ordering = '', $areas = null)
{
$db =& JFactory::getDBO();
$linkBase = 'index.php?option=com_joaktree&view=joaktree';
//If the array is not correct, return it:
if (is_array($areas)) {
if (!array_intersect($areas, array_keys($this->onContentSearchAreas()))) {
return array();
}
}
//And define the parameters. For example like this..
$limit = $this->params->def('search_limit', 50);
if (is_array($areas)) {
// check whether names are searched
$names = in_array('joaktreeName', $areas) ? 1 : 0;
$patronym = in_array('joaktreeName', $areas) ? $this->params->def('search_patronyms', 0) : 0;
// check whether notes are searched
$notes = in_array('joaktreeNote', $areas) ? $this->params->def('search_notes', 0) : 0;
} else {
$names = 1;
$notes = $this->params->def('search_notes', 0);
$patronym = $this->params->def('search_patronyms', 0);
}
$linkType = $this->params->def('link_option', 1);
//Use the function trim to delete spaces in front of or at the back of the searching terms
$text = trim($text);
//Return Array when nothing was filled in
if ($text == '') {
return array();
}
//replace joaktree
$searchJoaktree = JText::_('JTSRCH_GENEALOGY');
$searchNotes = JText::_('JTSRCH_NOTES');
// user access
$userAccessLevels = JoaktreeHelper::getUserAccessLevels();
$displayAccess = JoaktreeHelper::getDisplayAccess();
//After this, you have to add the database part.
$wheres = array();
switch ($phrase) {
//search exact
case 'exact':
$text = $db->Quote('%' . $db->escape($text, true) . '%', false);
// search in names table
$whereNames = array();
$whereNames[] = 'LOWER(CONCAT_WS(' . $db->Quote(' ') . ',jpn.firstName,jpn.namePreposition,jpn.familyName)) LIKE ' . $text;
$whereNames[] = 'LOWER(CONCAT_WS(' . $db->Quote(' ') . ',jpn.firstName,jpn.patronym,jpn.namePreposition,jpn.familyName)) LIKE ' . $text;
$whereName = '(' . implode(') OR (', $whereNames) . ')';
// search in person-notes table
$wherePerNote = 'LOWER(jpe.value) LIKE ' . $text . ' OR LOWER(jne.value) LIKE ' . $text;
// search in person-notes table
$whereRelNote = 'LOWER(jre.value) LIKE ' . $text . ' OR LOWER(jne.value) LIKE ' . $text;
break;
//search all or any
//search all or any
case 'all':
case 'any':
//set default
//set default
default:
$words = explode(' ', $text);
$whereNames = array();
$whereNotes = array();
$wherePerNotes = array();
$whereRelNotes = array();
foreach ($words as $word) {
$word = $db->Quote('%' . $db->escape($word, true) . '%', false);
if ($patronym == 1) {
$whereNames[] = 'LOWER(jpn.firstName) LIKE ' . $word . 'OR LOWER(jpn.patronym) LIKE ' . $word . 'OR LOWER(jpn.namePreposition) LIKE ' . $word . 'OR LOWER(jpn.familyName) LIKE ' . $word;
} else {
$whereNames[] = 'LOWER(jpn.firstName) LIKE ' . $word . 'OR LOWER(jpn.namePreposition) LIKE ' . $word . 'OR LOWER(jpn.familyName) LIKE ' . $word;
}
$wherePerNotes[] = 'LOWER(jpe.value) LIKE ' . $word . ' OR LOWER(jne.value) LIKE ' . $word;
$whereRelNotes[] = 'LOWER(jre.value) LIKE ' . $word . ' OR LOWER(jne.value) LIKE ' . $word;
}
$whereName = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $whereNames) . ')';
$wherePerNote = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $wherePerNotes) . ')';
$whereRelNote = '(' . implode($phrase == 'all' ? ') AND (' : ') OR (', $whereRelNotes) . ')';
break;
}
//ordering of the results
switch ($ordering) {
//alphabetic, ascending
case 'alpha':
$order = 'jpn.familyName ASC, jpn.firstName ASC';
break;
//oldest first
//oldest first
case 'oldest':
//popular first
//popular first
case 'popular':
//newest first
//newest first
case 'newest':
//default setting: alphabetic, ascending
//default setting: alphabetic, ascending
default:
$order = 'jpn.familyName ASC, jpn.firstName ASC';
}
//the database query;
$query = '';
if ($names == 1 && $notes == 1) {
$query .= '( ';
}
if ($names == 1) {
$query .= 'SELECT CONCAT_WS(' . $db->Quote(' ') . ' ' . ' , jpn.firstName ' . ($patronym == 1 ? ', jpn.patronym ' : '') . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) AS title ' . ', CONCAT_WS( " / " ' . ' , ' . $db->Quote($searchJoaktree) . ' ' . ' , jte.name ' . ' ) AS section ' . ', jpn.lastUpdateTimeStamp AS created ' . ', "' . $linkType . '" AS browsernav ' . ', jpn.app_id AS app_id ' . ', jpn.id AS person_id ' . ', jte.id AS tree_id ' . ', IF ( (jan.living = true AND ' . $displayAccess['BIRTperson']->living . ' = 1) ' . ' , ' . $db->Quote(JText::_('JTSRCH_ALTERNATIVE')) . ' ' . ' , birth.eventDate ' . ' ) AS birthDate ' . ', IF ( (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' = 1) ' . ' , ' . $db->Quote(JText::_('JTSRCH_ALTERNATIVE')) . ' ' . ' , death.eventDate ' . ' ) AS deathDate ' . ', NULL AS value ' . 'FROM #__joaktree_persons AS jpn ' . 'JOIN #__joaktree_admin_persons AS jan ' . 'ON ( jan.app_id = jpn.app_id ' . ' AND jan.id = jpn.id ' . ' AND jan.published = true ' . ' AND ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' = 2 ) ' . ' ) ' . ' ) ' . 'LEFT JOIN #__joaktree_trees AS jte ' . 'ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $userAccessLevels . ' ) ' . 'LEFT JOIN #__joaktree_person_events birth ' . 'ON ( birth.app_id = jpn.app_id ' . ' AND birth.person_id = jpn.id ' . ' AND birth.code = ' . $db->Quote('BIRT') . ' ' . ' AND ( (jan.living = false AND ' . $displayAccess['BIRTperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['BIRTperson']->living . ' > 0 ) ' . ' ) ' . ' ) ' . 'LEFT JOIN #__joaktree_person_events death ' . 'ON ( death.app_id = jpn.app_id ' . ' AND death.person_id = jpn.id ' . ' AND death.code = ' . $db->Quote('DEAT') . ' ' . ' AND ( (jan.living = false AND ' . $displayAccess['DEATperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' > 0 ) ' . ' ) ' . ' ) ' . 'WHERE ( ' . $whereName . ' ) ' . ' ';
}
if ($names == 1 && $notes == 1) {
$query .= ') UNION ( ';
}
if ($notes == 1) {
// person notes
$query .= 'SELECT CONCAT_WS(' . $db->Quote(' ') . ' ' . ' , jpn.firstName ' . ($patronym == 1 ? ', jpn.patronym ' : '') . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) AS title ' . ', CONCAT_WS( " / " ' . ' , ' . $db->Quote($searchJoaktree) . ' ' . ' , jte.name ' . ' , CONCAT_WS(' . $db->Quote(' ') . ' ' . ' , jpn.firstName ' . ($patronym == 1 ? ', jpn.patronym ' : '') . ' , jpn.namePreposition ' . ' , jpn.familyName ' . ' ) ' . ' , ' . $db->Quote($searchNotes) . ' ' . ' ) AS section ' . ', jpn.lastUpdateTimeStamp AS created ' . ', "' . $linkType . '" AS browsernav ' . ', jpn.app_id AS app_id ' . ', jpn.id AS person_id ' . ', jte.id AS tree_id ' . ', NULL AS birthDate ' . ', NULL AS deathDate ' . ', IF( jne.value IS NOT NULL ' . ' , jne.value ' . ' , jpe.value ' . ' ) AS value ' . 'FROM #__joaktree_person_notes AS jpe ' . 'JOIN #__joaktree_persons AS jpn ' . 'ON ( jpn.app_id = jpe.app_id ' . ' AND jpn.id = jpe.person_id ' . ' ) ' . 'JOIN #__joaktree_admin_persons AS jan ' . 'ON ( jan.app_id = jpn.app_id ' . ' AND jan.id = jpn.id ' . ' AND jan.published = true ' . ' AND ( ( jan.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ' . ' AND ' . $displayAccess['NOTEperson']->notLiving . ' = 2 ' . ' ) ' . ' OR ( jan.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' = 2 ' . ' AND ' . $displayAccess['NOTEperson']->living . ' = 2 ' . ' ) ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_trees AS jte ' . 'ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $userAccessLevels . ' ) ' . 'LEFT JOIN #__joaktree_notes AS jne ' . 'ON ( jne.app_id = jpe.app_id ' . ' AND jne.id = jpe.note_id ' . ' ) ' . 'WHERE ( ' . $wherePerNote . ' ) ';
}
// Order by name
if ($names == 1 && $notes == 1) {
$query .= ') ';
}
$query .= 'ORDER BY title ';
//. $order;
//Set query
$db->setQuery($query, 0, $limit);
$rows = $db->loadObjectList();
$menuJoaktree = JoaktreeHelper::getMenus('joaktree');
$menu =& JSite::getMenu();
$menuActive =& $menu->getActive();
$menuActId = isset($menuActive) ? $menuActive->id : null;
foreach ($rows as $key => $row) {
$tmp = '';
//The 'output' of the displayed link
if (!empty($rows[$key]->tree_id)) {
$rows[$key]->href = JRoute::_($linkBase . '&Itemid=' . $menuJoaktree[$rows[$key]->tree_id] . '&treeId=' . $rows[$key]->tree_id . '&personId=' . $rows[$key]->app_id . '!' . $rows[$key]->person_id);
} else {
// look for first child
$query = 'SELECT jrn.app_id AS app_id ' . ', jrn.person_id_1 AS person_id ' . ', jte.id AS tree_id ' . ', jrn.type AS relation ' . 'FROM #__joaktree_relations AS jrn ' . 'JOIN #__joaktree_admin_persons AS jan ' . 'ON ( jan.app_id = jrn.app_id ' . ' AND jan.id = jrn.person_id_1 ' . ' AND jan.published = true ' . ' AND ( ( jan.living = false ' . ' AND ' . $displayAccess['NAMEname']->notLiving . ' = 2 ' . ' ) ' . ' OR ( jan.living = true ' . ' AND ' . $displayAccess['NAMEname']->living . ' = 2 ' . ' ) ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_trees AS jte ' . 'ON ( jte.app_id = jan.app_id ' . ' AND jte.id = jan.default_tree_id ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $userAccessLevels . ' ) ' . 'WHERE jrn.app_id = ' . $rows[$key]->app_id . ' ' . 'AND jrn.person_id_2 = ' . $db->Quote($rows[$key]->person_id) . ' ' . 'AND jrn.orderNumber_2 = 1 ';
//Set query
$db->setQuery($query);
$child = $db->loadObject();
if ($child) {
$rows[$key]->href = JRoute::_($linkBase . '&Itemid=' . (!empty($child->tree_id) ? $menuJoaktree[$child->tree_id] : $menuActId) . '&treeId=' . (!empty($child->tree_id) ? $child->tree_id : '') . '&personId=' . $child->app_id . '!' . $child->person_id);
} else {
// situation that the child is still living ...
$rows[$key]->href = '#';
}
}
if (!empty($rows[$key]->birthDate)) {
$tmp .= JText::_('JTSRCH_BORN') . ': ';
$tmp .= JoaktreeHelper::displayDate($rows[$key]->birthDate) . '; ';
}
if (!empty($rows[$key]->deathDate)) {
$tmp .= JText::_('JTSRCH_DIED') . ': ';
$tmp .= JoaktreeHelper::displayDate($rows[$key]->deathDate) . '; ';
}
if (!empty($rows[$key]->value)) {
$tmp .= $rows[$key]->value;
}
$rows[$key]->text = $tmp;
}
return $rows;
}