function getListQuery() { $retObj = $this->getReturnObject(); if (is_object($retObj)) { $object = isset($retObj->object) ? $retObj->object : null; $appId = isset($retObj->app_id) ? $retObj->app_id : null; $objectId = isset($retObj->object_id) ? $retObj->object_id : null; } else { $object = 'prsn'; } $displayAccess = JoaktreeHelper::getDisplayAccess(); $levels = JoaktreeHelper::getUserAccessLevels(); $db = $this->getDbo(); $query = $db->getQuery(true); // select the basics $query->select(' jlg.* '); $query->from(' #__joaktree_logs jlg '); $query->where(' jlg.object = ' . $db->quote($object) . ' '); if (!empty($appId)) { $query->where(' jlg.app_id = ' . (int) $appId . ' '); } if (!empty($objectId)) { $query->where(' jlg.object_id = ' . $db->quote($objectId) . ' '); } $query->order(' jlg.changeDateTime DESC '); // select the name of application $query->select(' japp.title AS appname '); $query->innerJoin(' #__joaktree_applications japp ' . ' ON (japp.id = jlg.app_id) '); // select the name of the user $query->select(' usr.name AS username '); $query->leftJoin(' #__users usr ' . ' ON (usr.id = jlg.user_id) '); // person if ($object == 'prsn') { // select the details of the persons $query->select(JoaktreeHelper::getConcatenatedFullName() . ' AS description '); $query->leftJoin(' #__joaktree_persons jpn ' . ' ON ( jpn.app_id = jlg.app_id ' . ' AND jpn.id = jlg.object_id ' . ' ) '); $query->leftJoin(JoaktreeHelper::getJoinAdminPersons(true)); $query->where(' EXISTS ' . ' ( SELECT 1 ' . ' FROM #__joaktree_tree_persons jtp ' . ' JOIN #__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 . ' ' . ' ) ' . ' WHERE jtp.app_id = IFNULL(jpn.app_id, jtp.app_id) ' . ' AND jtp.person_id = IFNULL(jpn.id, jtp.person_id) ' . ' ) '); } // repository if ($object == 'repo') { // select the details of the persons $query->select(' jry.name AS description '); $query->leftJoin(' #__joaktree_repositories jry ' . ' ON ( jry.app_id = jlg.app_id ' . ' AND jry.id = jlg.object_id ' . ' ) '); } // source if ($object == 'sour') { // select the details of the persons $query->select(' jse.title AS description '); $query->leftJoin(' #__joaktree_sources jse ' . ' ON ( jse.app_id = jlg.app_id ' . ' AND jse.id = jlg.object_id ' . ' ) '); } // select deleted items $query->select(' jlr.description AS deletedItem '); $query->leftJoin(' #__joaktree_logremovals jlr ' . ' ON ( jlr.app_id = jlg.app_id ' . ' AND jlr.object_id = jlg.object_id ' . ' AND jlr.object = jlg.object ' . ' ) '); return $query; }
/** * Method to get the field options. * * @return array The field option objects. * @since 11.1 */ protected function getOptions($gedcomtype, $indLiving) { // Possible gedcom-types are: person, name, relation // Initialize variables. $options = array(); $levels = JoaktreeHelper::getUserAccessLevels(); // Get the database object. $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select(' code '); $query->from(' #__joaktree_display_settings '); $query->where(' level = ' . $db->quote($gedcomtype) . ' '); $query->where(' published = true '); $query->where(' code NOT IN (' . $db->quote('NAME') . ', ' . $db->quote('NOTE') . ', ' . $db->quote('ENOT') . ', ' . $db->quote('SOUR') . ', ' . $db->quote('ESOU') . ') '); if ($indLiving == true) { $query->where(' accessLiving IN ' . $levels . ' '); } else { $query->where(' access IN ' . $levels . ' '); } // Set the query and get the result list. $db->setQuery($query); $items = $db->loadObjectlist(); // Check for an error. if ($db->getErrorNum()) { JError::raiseWarning(500, $db->getErrorMsg()); return $options; } // Build the field options. if (!empty($items)) { foreach ($items as $item) { $options[] = JHtml::_('select.option', $item->code, JText::_($item->code)); } } // Merge any additional options in the XML definition. $options = array_merge(parent::getOptions(), $options); return $options; }
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; }
public function getTreeinfo() { // Lets load the content if it doesn't already exist if (empty($this->_treeinfo)) { $treeId = intval($this->getTreeId()); $query = $this->_db->getQuery(true); $query->select(' * '); $query->from(' #__joaktree_trees '); $query->where(' id = ' . $treeId . ' '); $query->where(' access IN ' . JoaktreeHelper::getUserAccessLevels() . ' '); $this->_db->setQuery($query); $this->_treeinfo = $this->_db->loadObject(); } return $this->_treeinfo; }
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 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 getArticles($tree_id) { // general input $app = JFactory::getApplication(); $languageFilter = $app->getLanguageFilter(); $db = JFactory::getDBO(); $query = $db->getQuery(true); // get user access info $levels = JoaktreeHelper::getUserAccessLevels(); // initialization $retArticles = array(); $nullDate = $db->getNullDate(); $date = JFactory::getDate(); $now = $date->toSql(); //remove single space after commas in keywords $attribs = array(); $attribs[] = '","'; $attribs[] = 'REPLACE( a.metakey, ", ", "," )'; $attribs[] = '","'; $concat = $query->concatenate($attribs, ','); // end concats // select from content $query->select(' a.id AS id '); $query->select(' a.title AS title '); $query->select(' a.introtext AS introtext '); $query->select(' a.fulltext AS \'fulltext\' '); $query->select(' DATE_FORMAT( a.modified, "%e %b %Y" ) AS modified '); $query->select(' a.attribs AS attribs '); $query->from(' #__content AS a '); $query->where(' a.state = 1 '); $query->where(' a.access IN ' . $levels . ' '); $query->where(' ( a.publish_up = ' . $db->Quote($nullDate) . ' ' . ' OR a.publish_up <= ' . $db->Quote($now) . ' ' . ' ) '); $query->where(' ( a.publish_down = ' . $db->Quote($nullDate) . ' ' . ' OR a.publish_down >= ' . $db->Quote($now) . ' ' . ' ) '); // Filter by language if ($languageFilter) { $query->where('a.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . ') '); } // select from categories // join with tree to find matching category $query->select(' cc.id AS cat_id '); $query->innerJoin(' #__categories AS cc ' . ' ON ( cc.id = a.catid ' . ' AND cc.access IN ' . $levels . ' ' . ' ) '); $query->innerJoin(' #__joaktree_trees AS jte ' . ' ON ( cc.id = jte.catid ' . ' AND jte.id = ' . (int) $tree_id . ' ' . ' ) '); $db->setQuery($query); $articles = $db->loadObjectList(); foreach ($articles as $article) { // Convert parameter fields to objects for article. if (isset($article->attribs)) { $registry = new JRegistry(); $registry->loadString($article->attribs); $indIntrotext = (int) $registry->get('show_intro', 1); $article->showTitle = (int) $registry->get('show_title', 1); } else { $indIntrotext = 0; $article->showTitle = 1; } // Are we showing introtext with the article if ($indIntrotext == 1) { $article->text = $article->introtext . chr(13) . chr(13) . $article->fulltext; } elseif (!empty($article->fulltext)) { $article->text = $article->fulltext; } else { $article->text = $article->introtext; } // formating last update date if (!empty($article->modified)) { $article->modified = JoaktreeHelper::lastUpdateDateTimePerson($article->modified); } // check content using the content plugin - if it is available $plug = JPATH_SITE . DS . 'plugins' . DS . 'content' . DS . 'joaktree' . DS . 'joaktree.php'; // check if plugin file exists - only then we will use it if (JFile::exists($plug)) { // load the plugin JLoader::register('plgContentJoaktree', $plug); $params = array(); plgContentJoaktree::onContentPrepare('com_content', $article, $params); } $retArticles[] = $article; } return $retArticles; }
private function _buildContentWhere() { $app = JFactory::getApplication('site'); $treeId = intval($this->getTreeId()); $levels = JoaktreeHelper::getUserAccessLevels(); $params = JoaktreeHelper::getJTParams(); $context = 'com_joaktree.joaktreelist.list.'; $search1 = $app->getUserStateFromRequest($context . 'search1', 'search1', '', 'string'); $search1 = $this->_db->escape($search1, true); $search1 = JString::strtolower($search1); $search2 = $app->getUserStateFromRequest($context . 'search2', 'search2', '', 'string'); $search2 = $this->_db->escape($search2, true); $search2 = JString::strtolower($search2); $search3 = $app->getUserStateFromRequest($context . 'search3', 'search3', '', 'string'); $search3 = $this->_db->escape($search3, true); $search3 = JString::strtolower($search3); $search4 = $app->getUserStateFromRequest($context . 'search4', 'search4', '', 'string'); $search4 = base64_decode($search4); $search4 = $this->_db->escape($search4, true); $where = array(); if ($treeId) { $where[] = 'jtp.tree_id = ' . $treeId; } if ($search1) { $where[] = 'LOWER(jpn.firstName) LIKE ' . $this->_db->Quote('%' . $search1 . '%'); } if ($search2) { $where[] = 'LOWER(jpn.patronym) LIKE ' . $this->_db->Quote('%' . $search2 . '%'); } if ($search3) { $where[] = 'LOWER(jpn.familyName) LIKE ' . $this->_db->Quote('%' . $search3 . '%'); } if ($search4) { $where[] = 'EXISTS ' . '( ' . 'SELECT 1 ' . 'FROM #__joaktree_person_events ejpe ' . 'JOIN #__joaktree_display_settings ejds ' . 'ON ( ejds.code = ejpe.code ' . ' AND ejds.level = ' . $this->_db->Quote('person') . ' ' . ' AND ejds.published = true ' . ' ) ' . 'JOIN #__joaktree_admin_persons ejan ' . 'ON ( ejan.app_id = ejpe.app_id ' . ' AND ejan.id = ejpe.person_id ' . ' AND ejan.published = true ' . ' AND ( (ejan.living = false AND ejds.access IN ' . $levels . ') ' . ' OR (ejan.living = true AND ejds.accessLiving IN ' . $levels . ') ' . ' ) ' . ' ) ' . 'WHERE ejpe.person_id = jpn.id ' . 'AND ejpe.app_id = jpn.app_id ' . 'AND ejpe.location = ' . $this->_db->Quote($search4) . ' ' . 'UNION ' . 'SELECT 1 ' . 'FROM #__joaktree_relation_events ejre ' . 'JOIN #__joaktree_display_settings rjds ' . 'ON ( rjds.code = ejre.code ' . ' AND rjds.level = ' . $this->_db->Quote('relation') . ' ' . ' AND rjds.published = true ' . ' ) ' . 'JOIN #__joaktree_admin_persons ejan1 ' . 'ON ( ejan1.app_id = ejre.app_id ' . ' AND ejan1.id = ejre.person_id_1 ' . ' AND ejan1.published = true ' . ' AND ( (ejan1.living = false AND rjds.access IN ' . $levels . ') ' . ' OR (ejan1.living = true AND rjds.accessLiving IN ' . $levels . ') ' . ' ) ' . ' ) ' . 'JOIN #__joaktree_admin_persons ejan2 ' . 'ON ( ejan2.app_id = ejre.app_id ' . ' AND ejan2.id = ejre.person_id_2 ' . ' AND ejan2.published = true ' . ' AND ( (ejan2.living = false AND rjds.access IN ' . $levels . ') ' . ' OR (ejan2.living = true AND rjds.accessLiving IN ' . $levels . ') ' . ' ) ' . ' ) ' . 'WHERE ( ejre.person_id_1 = jpn.id ' . ' OR ejre.person_id_2 = jpn.id ' . ' ) ' . 'AND ejre.app_id = jpn.app_id ' . 'AND ejre.location = ' . $this->_db->Quote($search4) . ' ' . ') '; } //$where = ( count( $where ) ? ' WHERE '. implode( ' AND ', $where ) : '' ); return $where; }
public function checkDisplay($gedcomtype = 'person', $indLiving, $code = null) { // Get the database object. $db = JFactory::getDBO(); $query = $db->getQuery(true); $levels = JoaktreeHelper::getUserAccessLevels(); $indLiving = empty($indLiving) ? false : $indLiving; $query->select(' count(code) '); $query->from(' #__joaktree_display_settings '); $query->where(' level = ' . $db->quote($gedcomtype) . ' '); $query->where(' published = true '); if (!empty($code)) { $query->where(' code = ' . $db->quote($code) . ' '); } else { $query->where(' code NOT IN (' . $db->quote('NAME') . ', ' . $db->quote('NOTE') . ', ' . $db->quote('ENOT') . ', ' . $db->quote('SOUR') . ', ' . $db->quote('ESOU') . ') '); } if ($indLiving == false) { $query->where(' access IN ' . $levels . ' '); } else { $query->where(' accessLiving IN ' . $levels . ' '); } // Set the query and get the result list. $db->setQuery($query); $result = $db->loadResult(); $count = (int) $result; if ($count > 0) { return true; } else { return false; } }
function getDisplayAccess($public = false) { static $_displayAccess; if (!isset($_displayAccess)) { $db = JFactory::getDBO(); if ($public) { $levels = '(1)'; } else { $levels = JoaktreeHelper::getUserAccessLevels(); } // value 0: not shown to current user based on access // value 1: alternative text is shown to current user based on access // value 2: value is shown to current user based on access $query = $db->getQuery(true); $attribs = array(); $attribs[] = 'code'; $attribs[] = 'level'; $concatTxt = $query->concatenate($attribs); $query->select(' ' . $concatTxt . ' AS code '); $query->select(' IF( published ' . ' , IF( (access IN ' . $levels . ') ' . ' , 2 ' . ' , 0 ' . ' ) ' . ' , 0 ' . ' ) AS notLiving '); $query->select(' IF( published ' . ' , IF( (accessLiving IN ' . $levels . ') ' . ' , 2 ' . ' , IF( (altLiving IN ' . $levels . ') ' . ' , 1 ' . ' , 0 ' . ' ) ' . ' ) ' . ' , 0 ' . ' ) AS living '); $query->select(' code AS gedcomtag '); $query->from(' #__joaktree_display_settings '); $db->setQuery($query); $_displayAccess = $db->loadObjectList('code'); } return $_displayAccess; }
public static function getList($numberInList) { // get cookie $params = JComponentHelper::getParams('com_joaktree'); $indCookie = $params->get('indCookies', false); if (!$indCookie) { return false; } // array to return $person = array(); // we fetch the cookie $cookie = new JInputCookie(); $tmp = $cookie->get('jt_last_persons', '', 'string'); // prepare the array if ($tmp) { $cookieList = (array) json_decode(base64_decode($tmp)); } else { $cookieList = array(); } if (count($cookieList)) { $db = JFactory::getDBO(); $query = $db->getQuery(true); // get user access info $levels = JoaktreeHelper::getUserAccessLevels(); // set up maximum number of items $maxItem = count($cookieList) > $numberInList ? $numberInList : count($cookieList); // get menuId & technology $menus = JoaktreeHelper::getMenus('joaktree'); $linkBase = 'index.php?option=com_joaktree&view=joaktree'; for ($i = 0; $i < $maxItem; $i++) { $query->clear(); // item is a string separated by !: app_id, person_id, tree_id; $elements = explode('!', array_shift($cookieList)); // retrieve person $app_id = array_shift($elements); $person_id = array_shift($elements); $tree_id = array_shift($elements); if (!empty($app_id) && !empty($person_id) && !empty($tree_id)) { $query->select(JoaktreeHelper::getConcatenatedFullName() . ' AS fullName '); $query->from(' #__joaktree_persons jpn '); $query->where(' jpn.app_id = ' . $app_id . ' '); $query->where(' jpn.id = ' . $db->Quote($person_id) . ' '); // join with admin persons $query->innerJoin(JoaktreeHelper::getJoinAdminPersons(true)); // join with trees $query->innerJoin(' #__joaktree_tree_persons jtp ' . ' ON ( jtp.app_id = jpn.app_id ' . ' AND jtp.person_id = jpn.id ' . ' AND jtp.tree_id = ' . $tree_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 . ' ' . ' ) '); // fetch from database $db->setQuery($query); $tmpPerson = $db->loadObject(); if ($tree_id && isset($menus[$tree_id])) { $menuItemId = $menus[$tree_id]; $tmpPerson->route = JRoute::_($linkBase . '&Itemid=' . $menuItemId . '&treeId=' . $tree_id . '&personId=' . $app_id . '!' . $person_id); $tmpPerson->robot = ''; } else { $tmpPerson->route = 0; $tmpPerson->robot = ''; } if (!empty($tmpPerson->fullName)) { $person[] = $tmpPerson; } unset($tmpPerson); } } } return $person; }
function __construct($id, $type = 'basic') { $this->_db =& JFactory::getDBO(); $app = JFactory::getApplication(); // get parameters $params = JoaktreeHelper::getJTParams(); $patronymSetting = (int) $params->get('patronym'); $patronymString = $params->get('patronymSeparation'); // get user access info $this->_levels = JoaktreeHelper::getUserAccessLevels(); $displayAccess = JoaktreeHelper::getDisplayAccess(); // basic or full $indFull = $type != 'basic' && $type != 'ancestor'; // retrieve person from database $query = $this->_db->getQuery(true); // select from persons $query->select(' jpn.app_id AS app_id '); $query->select(' jpn.id AS id '); // names if ($indFull) { $query->select(JoaktreeHelper::getSelectFirstName() . ' AS firstName '); $query->select(JoaktreeHelper::getSelectPatronym() . ' AS patronym '); $query->select(JoaktreeHelper::getConcatenatedFamilyName() . ' AS familyName '); $query->select(' jpn.namePreposition '); $query->select(' jpn.prefix '); $query->select(' jpn.suffix '); $query->select(' jpn.familyName AS rawFamilyName '); } else { $query->select(JoaktreeHelper::getConcatenatedFullName() . ' AS fullName '); } $query->select(' jpn.sex AS sex '); $query->select(' jpn.indHasParent '); $query->select(' jpn.indHasPartner '); $query->select(' jpn.indHasChild '); $query->select(' DATE_FORMAT( jpn.lastUpdateTimeStamp, "%e %b %Y" ) ' . ' AS lastUpdateDate '); if ($indFull) { $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['NOTEperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NOTEperson']->living . ' > 0 ) ' . ' ) ' . ' , jpn.indNote ' . ' , false ' . ' ) AS indNote '); $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['SOURperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['SOURperson']->living . ' > 0 ) ' . ' ) ' . ' , jpn.indCitation ' . ' , false ' . ' ) AS indCitation '); } $query->from(' #__joaktree_persons jpn '); $query->where(' jpn.app_id = ' . $id['app_id'] . ' '); $query->where(' jpn.id = ' . $this->_db->Quote($id['person_id']) . ' '); // select from admin persons $query->select(' jan.living AS living '); $query->select(' jan.published AS published '); $query->select(' jan.page AS page '); $query->select(' jan.map AS map '); $query->select(' IF( ISNULL( NULLIF( jan.default_tree_id, 0) ) ' . ' , false ' . ' , jan.page ' . ' ) AS indHasPage '); $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' > 0 ) ' . ' ) ' . ' , true ' . ' , false ' . ' ) AS indNameDisplay '); $query->select(' IF((jan.robots > 0), jan.robots, jte.robots ) AS robots '); if ($indFull) { $query->select(' IF( (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltName '); $query->select(' IF( (jan.living = true AND ' . $displayAccess['NOTEperson']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltNote '); $query->select(' IF( (jan.living = true AND ' . $displayAccess['SOURperson']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltSource '); } $query->innerJoin(JoaktreeHelper::getJoinAdminPersons(true)); if ($indFull) { // select from tree x persons $query->select(' jtp.tree_id AS tree_id '); $query->select(' jtp.lineage '); $query->innerJoin(' #__joaktree_tree_persons jtp ' . ' ON ( jtp.app_id = jpn.app_id ' . ' AND jtp.person_id = jpn.id ' . ' AND jtp.tree_id = ' . $id['tree_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 ' . $this->_levels . ' ' . ' ) '); } else { // select from default tree $query->select(' jte.id AS tree_id '); $query->leftJoin(' #__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 ' . $this->_levels . ' ' . ' ) '); } // select birth and death dates for ancestors if ($type == 'ancestor') { // select from birth $query->select(' MIN( IF( (jan.living = true AND ' . $displayAccess['BIRTperson']->living . ' = 1 ) ' . ' , NULL ' . ' , birth.eventDate ' . ' ) ' . ' ) AS birthDate '); $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 . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['BIRTperson']->living . ' > 0 ) ' . ' ) ' . ' ) '); // select from death $query->select(' MIN( IF( (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' = 1 ) ' . ' , NULL ' . ' , death.eventDate ' . ' ) ' . ' ) AS deathDate '); $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 . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['DEATperson']->living . ' > 0 ) ' . ' ) ' . ' ) '); $query->group(' jpn.app_id '); $query->group(' jpn.id '); $query->group(JoaktreeHelper::getConcatenatedFullName()); $query->group(' jpn.sex '); $query->group(' jpn.indHasParent '); $query->group(' jpn.indHasPartner '); $query->group(' jpn.indHasChild '); $query->group(' DATE_FORMAT( jpn.lastUpdateTimeStamp, "%e %b %Y" ) '); // select from admin persons $query->group(' jan.living '); $query->group(' jan.published '); $query->group(' jan.page '); $query->group(' jan.map '); $query->group(' IF( ISNULL( NULLIF( jan.default_tree_id, 0) ) ' . ' , false ' . ' , jan.page ' . ' ) '); $query->group(' IF( ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' > 0 ) ' . ' ) ' . ' , true ' . ' , false ' . ' ) '); $query->group(' IF((jan.robots > 0), jan.robots, jte.robots ) '); $query->group(' jte.id '); } $this->_db->setQuery($query); $person = $this->_db->loadAssoc(); // Check for a error. if ($error = $this->_db->getErrorMsg()) { throw new JException($error); } if (!$person && $indFull) { $query->clear(); // select from persons $query->select(' jpn.app_id AS app_id '); $query->select(' jpn.id AS id '); $query->select(JoaktreeHelper::getSelectFirstName() . ' AS firstName '); $query->select(JoaktreeHelper::getSelectPatronym() . ' AS patronym '); $query->select(JoaktreeHelper::getConcatenatedFamilyName() . ' AS familyName '); $query->select(' jpn.namePreposition '); $query->select(' jpn.prefix '); $query->select(' jpn.suffix '); $query->select(' jpn.familyName AS rawFamilyName '); $query->select(' jpn.sex AS sex '); $query->select(' jpn.indHasParent '); $query->select(' jpn.indHasPartner '); $query->select(' jpn.indHasChild '); $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['NOTEperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NOTEperson']->living . ' > 0 ) ' . ' ) ' . ' , jpn.indNote ' . ' , false ' . ' ) AS indNote '); $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['SOURperson']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['SOURperson']->living . ' > 0 ) ' . ' ) ' . ' , jpn.indCitation ' . ' , false ' . ' ) AS indCitation '); $query->select(' DATE_FORMAT( jpn.lastUpdateTimeStamp, "%e %b %Y" ) ' . ' AS lastUpdateDate '); $query->from(' #__joaktree_persons jpn '); $query->where(' jpn.app_id = ' . $id['app_id'] . ' '); $query->where(' jpn.id = ' . $this->_db->Quote($id['person_id']) . ' '); $query->where(' NOT EXISTS ' . ' ( SELECT 1 ' . ' FROM #__joaktree_tree_persons jtp2 ' . ' WHERE jtp2.app_id = jpn.app_id ' . ' AND jtp2.person_id = jpn.id ' . ' AND jtp2.tree_id = ' . $id['tree_id'] . ' ' . ' ) '); // select from tree x persons $query->select(' IFNULL( jan.default_tree_id ' . ' , ' . $id['tree_id'] . ' ' . ' ) AS tree_id '); // select from admin persons $query->select(' jan.living AS living '); $query->select(' jan.published AS published '); $query->select(' jan.page AS page '); $query->select(' jan.map AS map '); $query->select(' IF( ISNULL( NULLIF( jan.default_tree_id, 0) ) ' . ' , false ' . ' , jan.page ' . ' ) AS indHasPage '); $query->select(' IF( ( (jan.living = false AND ' . $displayAccess['NAMEname']->notLiving . ' > 0 ) ' . ' OR (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' > 0 ) ' . ' ) ' . ' , true ' . ' , false ' . ' ) AS indNameDisplay '); $query->select(' IF( (jan.living = true AND ' . $displayAccess['NAMEname']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltName '); $query->select(' IF( (jan.living = true AND ' . $displayAccess['NOTEperson']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltNote '); $query->select(' IF( (jan.living = true AND ' . $displayAccess['SOURperson']->living . ' = 1 ) ' . ' , true ' . ' , false ' . ' ) AS indAltSource '); $query->innerJoin(JoaktreeHelper::getJoinAdminPersons(true)); // robots $query->select(' IF((jan.robots > 0), jan.robots, jte.robots ) AS robots '); $query->leftJoin(' #__joaktree_trees jte ' . ' ON ( jte.app_id = jan.app_id ' . ' AND jte.id = IFNULL( jan.default_tree_id ' . ' , ' . $id['tree_id'] . ' ' . ' ) ' . ' AND jte.published = true ' . ' AND jte.access IN ' . $this->_levels . ' ' . ' ) '); $this->_db->setQuery($query); $person = $this->_db->loadAssoc(); // Check for a error. if ($error = $this->_db->getErrorMsg()) { throw new JException($error); } } $this->app_id = $person['app_id']; $this->id = $person['id']; $this->tree_id = $person['tree_id']; $this->sex = $person['sex']; $this->indHasParent = $person['indHasParent']; $this->indHasPartner = $person['indHasPartner']; $this->indHasChild = $person['indHasChild']; $this->lastUpdateDate = $person['lastUpdateDate']; $this->living = $person['living']; $this->published = $person['published']; $this->page = $person['page']; $this->map = $person['map']; $this->indHasPage = $person['indHasPage']; $this->indNameDisplay = $person['indNameDisplay']; $this->robots = $person['robots']; // names if ($indFull) { // when patronyms are shown, there are concatenated to the first name $this->firstName = $person['firstName']; $this->patronym = $person['patronym']; if ($patronymSetting == 0 or $this->patronym == null) { $this->firstNamePatronym = $this->firstName; } else { $this->firstNamePatronym = $this->firstName . ' ' . $patronymString . $person['patronym'] . $patronymString; } $this->familyName = $person['familyName']; $this->prefix = $person['prefix']; $this->suffix = $person['suffix']; $this->namePreposition = $person['namePreposition']; $this->rawFamilyName = $person['rawFamilyName']; $this->indNote = $person['indNote']; $this->indCitation = $person['indCitation']; $this->indAltName = $person['indAltName']; $this->indAltNote = $person['indAltNote']; $this->indAltSource = $person['indAltSource']; $this->lineage = isset($person['lineage']) ? trim($person['lineage']) : null; } else { $this->fullName = $person['fullName']; } if ($type == 'ancestor') { $this->birthDate = JoaktreeHelper::displayDate($person['birthDate']); $this->deathDate = JoaktreeHelper::displayDate($person['deathDate']); } $this->secondParent_id = null; $this->family_id = null; $this->relationtype = null; $this->orderNr = null; $this->languageFilter = $app->getLanguageFilter(); if (isset($person['tree_id']) && $person['tree_id']) { $menus = $this->getMenus(); $this->menuItemId = $menus[$person['tree_id']]; } if (!isset($this->menuItemId)) { $menu = $app->getMenu(); $item = $menu->getActive(); $this->menuItemId = $item->id; } }
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; }