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 __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;
}
}