function getTree($pListHash)
{
global $gBitDb;
$ret = array();
if ($this->mDb->isAdvancedPostgresEnabled()) {
$bindVars = array();
$containVars = array();
$selectSql = '';
$joinSql = '';
$whereSql = '';
if (!empty($pListHash['contain_item'])) {
$selectSql = " , tfgim3.`item_content_id` AS `in_gallery` ";
$joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` tfgim3 ON (tfgim3.`gallery_content_id`=lc.`content_id`) AND tfgim3.`item_content_id`=? ";
$bindVars[] = $pListHash['contain_item'];
$containVars[] = $pListHash['contain_item'];
}
if (isset($pListHash['contain_item'])) {
// contain item might have squeaked in as 0, clear our from pListHash
unset($pListHash['contain_item']);
}
foreach ($pListHash as $key => $val) {
$whereSql .= " {$key}=? AND ";
$bindVars[] = $val;
}
$query = "SELECT lc.`content_id` AS `hash_key`, fg.*, lc.* {$selectSql}\n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery` fg\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON(fg.`content_id`=lc.`content_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\tWHERE {$whereSql} NOT EXISTS (SELECT gallery_content_id FROM fisheye_gallery_image_map tfgim2 WHERE tfgim2.item_content_id=lc.content_id)\n\t\t\t\t\t\tORDER BY lc.title";
$rootContent = $gBitDb->GetAssoc($query, $bindVars);
foreach (array_keys($rootContent) as $conId) {
$splitVars = array();
$query = "SELECT branch AS hash_key, * {$selectSql}\n\t\t\t\t\t\t FROM connectby('`" . BIT_DB_PREFIX . "fisheye_gallery_image_map`', '`item_content_id`', '`gallery_content_id`', ?, 0, '/') AS t(cb_item_content_id int,cb_gallery_content_id int, level int, branch text)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery` fg ON (fg.`content_id`=cb_item_content_id)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON(lc.`content_id`=fg.`content_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\t ORDER BY branch, lc.`title`";
$splitVars[] = $conId;
if (!empty($containVars)) {
$splitVars[] = $containVars[0];
}
FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query, $splitVars));
FisheyeGallery::getTreeSort($ret);
}
} else {
if ($this->mDb->mType == 'firebird') {
$bindVars = array();
$containVars = array();
$selectSql = '';
$joinSql = '';
$whereSql = '';
if (!empty($pListHash['contain_item'])) {
$selectSql = " , tfgim3.`item_content_id` AS `in_gallery` ";
$joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` tfgim3 ON (tfgim3.`gallery_content_id`=lc.`content_id`) AND tfgim3.`item_content_id`=? ";
$bindVars[] = $pListHash['contain_item'];
$containVars[] = $pListHash['contain_item'];
}
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars);
if (isset($pListHash['contain_item'])) {
// contain item might have squeaked in as 0, clear our from pListHash
unset($pListHash['contain_item']);
}
foreach ($pListHash as $key => $val) {
$whereSql .= " AND lc.{$key}=? ";
$bindVars[] = $val;
}
$splitVars = array();
$query = "WITH RECURSIVE\n\t\t\t\t\t\t\t\tGALLERY_TREE AS (\n\t\t\t\t\t\t\t\tSELECT B.`content_id` AS gallery_content_id, B.`content_id` AS item_content_id, 0 AS BLEVEL, CAST( lcp.`title` AS VARCHAR(255) ) AS BRANCH, 0 AS gallery_parent_id\n\t\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery` B\n\t\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lcp ON(lcp.`content_id`=B.`content_id`)\n\t\t\t\t\t\t\t\tWHERE NOT EXISTS (SELECT gallery_content_id FROM fisheye_gallery_image_map tfgim2 WHERE tfgim2.item_content_id=B.content_id)\n\n\t\t\t\t\t\t\t\tUNION ALL\n\n\t\t\t\t\t\t\t\tSELECT `item_content_id` AS gallery_content_id, `item_content_id`, G.BLEVEL + 1, G.BRANCH || '/' || `item_content_id` AS BRANCH, `gallery_content_id` AS gallery_parent_id\n\t\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` G1\n\t\t\t\t\t\t\t\tJOIN GALLERY_TREE G\n\t\t\t\t\t\t\t\tON G1.`gallery_content_id` = G.`item_content_id`\n\t\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lcg1 ON(lcg1.`content_id`=`item_content_id`) and lcg1.`content_type_guid` = 'fisheyegallery'\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\tSELECT T.BRANCH AS hash_key, T.BLEVEL, fg.*, lc.* {$selectSql}\n\t\t\t\t\t\t\tFROM GALLERY_TREE T\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery` fg ON (fg.`content_id`=T.`gallery_content_id`)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON (lc.`content_id`=T.`item_content_id`)\n\t\t\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` fgimo ON (fgimo.`gallery_content_id`=T.gallery_parent_id) AND fgimo.`item_content_id`=T.gallery_content_id\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\t\tWHERE lc.`content_type_guid` = 'fisheyegallery' {$whereSql}\n\t\t\t\t\t\t ORDER BY T.BRANCH, fgimo.`item_position`";
if (!empty($bindVars)) {
FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query, $bindVars));
} else {
FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query));
}
} else {
// this needs replacing with a more suitable list query ...
$pListHash['show_empty'] = TRUE;
$galList = $this->getList($pListHash);
// index by content_id
foreach ($galList as $galId => $gal) {
$ret[$gal['content_id']] = $gal;
}
FisheyeGallery::splitConnectByTree($ret, $ret);
FisheyeGallery::getTreeSort($ret);
}
}
return $ret;
}
