/**
* Gets a list of Files
*
* @param int $fileId (optional) Id of the parent File from which the list will be created
* @param array $filter (optional, may be combined) If SUBNODES, only subnodes of the specified File will be returned<br>
* if TRASHCAN, only items in the Trash will be returned<br>
* if FOLDERS, only folders will be returned
* @param string $sort (optional) "ORDER BY" Sql clause
* @param int $maxLevel (optional) Specifies the maximum level of nodes to get
* @param int $usergroupId (optional) Return List for a specfic Usergroup Id
* @param array $filterArray Array of filters for the SQL query
* @return array|false Array of Files or FALSE in case of an error
* @throws Exception
*/
function getList($fileId = 0, $filter = array(), $sort = 'group2.LFT', $maxLevel = 0, $usergroupId = 0, $filterArray)
{
$fileId = (int) $fileId;
$sort = sanitize($sort);
$maxLevel = (int) $maxLevel;
$usergroupId = (int) $usergroupId;
$rootGroupId = (int) sConfig()->getVar("CONFIG/SYSTEMUSERS/ROOTGROUPID");
if ($fileId == 0) {
$fileId = $this->tree->getRoot();
}
if (in_array("SUBNODES", $filter)) {
$myinfo = $this->tree->getAll($fileId);
$myleft = $myinfo["LFT"];
$myrgt = $myinfo["RGT"];
$subnodesql = " AND (group2.LFT > {$myleft} AND group2.RGT < {$myrgt}) ";
if (!$myinfo) {
return false;
}
}
if (in_array("FOLDERS", $filter)) {
$subnodesql .= " AND (prop.FOLDER = 1)";
}
// Surpress items in trashcan if not explicitly asked for
if (in_array("TRASHCAN", $filter)) {
$subnodesql .= " AND prop.DELETED = 1 AND prop.FOLDER = 0";
} else {
$subnodesql .= " AND prop.DELETED = 0";
}
// Check if special filter was suppplied
$filterOrder = 'ORDER BY ' . $sort;
if ($filterArray) {
$filterSelect = $filterFrom = $filterWhere = $filterLimit = $filterOrder = '';
buildBackendFilter('FilesSearchCB', $filterArray, $filterSelect, $filterFrom, $filterWhere, $filterLimit, $filterOrder);
$subnodesql .= $filterWhere;
}
if ($maxLevel > 0) {
$maxlevelsql = " AND (group2.LEVEL <= {$maxLevel}) AND (group1.LEVEL <= {$maxLevel})";
}
$perm_sql_select = ", MAX(perm.RREAD) AS RREAD, MAX(perm.RWRITE) AS RWRITE, MAX(perm.RDELETE) AS RDELETE, MAX(perm.RSUB) AS RSUB, MAX(perm.RSTAGE) AS RSTAGE, MAX(perm.RMODERATE) AS RMODERATE, MAX(perm.RCOMMENT) AS RCOMMENT";
$perm_sql_from = " LEFT JOIN yg_files_permissions AS perm ON perm.OID = group2.ID";
if ($usergroupId > 0) {
$perm_sql_from .= " AND (perm.USERGROUPID = " . $usergroupId . ")";
} else {
$perm_sql_where = " AND (";
$roles = $this->permissions->getUsergroups();
for ($r = 0; $r < count($roles); $r++) {
$perm_sql_where .= "(perm.USERGROUPID = " . (int) $roles[$r]["ID"] . ") ";
if (count($roles) - $r > 1) {
$perm_sql_where .= " OR ";
}
}
$perm_sql_where .= ") ";
$perm_sql_where .= " AND ((RREAD >= 1) OR (perm.USERGROUPID = {$rootGroupId})) ";
}
$sql = "SELECT\n\t\t\t\t\tgroup2.LFT,\n\t\t\t\t\tgroup2.RGT,\n\t\t\t\t\tgroup2.VERSIONPUBLISHED,\n\t\t\t\t\tgroup2.ID,\n\t\t\t\t\tgroup2.LEVEL AS LEVEL,\n\t\t\t\t\tgroup2.PARENT AS PARENT,\n\t\t\t\t\tMAX(prop.VERSION) AS VERSION,\n\t\t\t\t\tprop.FOLDER AS FOLDER,\n\t\t\t\t\tprop.FILENAME AS FILENAME,\n\t\t\t\t\tprop.FILETYPE AS FILETYPE,\n\t\t\t\t\tprop.FILESIZE AS FILESIZE,\n\t\t\t\t\tSUBSTRING_INDEX(prop.FILENAME, '.', -1) AS EXTENSION,\n\t\t\t\t\tpv.*,\n\t\t\t\t\tprop.CREATEDTS,\n\t\t\t\t\tprop.CHANGEDTS,\n\t\t\t\t\tprop.CREATEDBY,\n\t\t\t\t\tprop.CHANGEDBY\n\t\t\t\t\t{$perm_sql_select}\n\t\t\t\tFROM\n\t\t\t\t\t({$this->table} AS group1, {$this->table} AS group2, yg_files_properties AS prop)\n\t\t\t\t\t{$perm_sql_from}\n\t\t\t\tLEFT JOIN yg_files_propsv AS pv\n\t\t\t\t\tON pv.OID = prop.ID\n\t\t\t\tWHERE\n\t\t\t\t\t((group2.LFT >= group1.LFT) AND (group2.LFT <= group1.RGT)) AND\n\t\t\t\t\t(group2.ID = prop.OBJECTID) AND\n\t\t\t\t\t(prop.VERSION = (SELECT MAX( rgt.VERSION ) FROM yg_files_properties AS rgt WHERE (prop.OBJECTID = rgt.OBJECTID)))\n\t\t\t\t\t{$perm_sql_where}\n\t\t\t\t\t{$maxlevelsql}\n\t\t\t\t\t{$subnodesql}\n\t\t\t\tGROUP BY\n\t\t\t\t\tgroup2.LFT, group2.RGT, group2.VERSIONPUBLISHED, group2.ID\n\t\t\t\t{$filterOrder} {$filterLimit};";
$dbr = sYDB()->Execute($sql);
if ($dbr === false) {
throw new Exception(sYDB()->ErrorMsg());
}
$blaetter = $dbr->GetArray();
return $blaetter;
}
/**
* Gets a list of Objects which got the specified Tags assigned
*
* @param int|array $list List of Tag Ids or single Tag Id
* @param string $sort (optional) Specifies the order column and direction of the SQL query (ID, OBJECTORDER, NAME) and (DESC / ASC)
* @param string $concat (optional) Specifies which operator should be used in the SQL query (default is OR)
* @param bool $published (optional) Specifies if only published versions should be returned
* @param $filterArray Array of filters used in the SQL query
* @param bool $noTrash TRUE when Objects in the trash shouldn't be returned
* @return array|false
*/
function getByTag($list, $sort, $concat = "OR", $published = false, $filterArray, $noTrash = true)
{
if ($concat != "OR" and $concat != "AND") {
$concat = "OR";
}
$sortdir = "ASC";
$sortcol = "OBJECTORDER";
$sortarr = explode(" ", $sort);
if ($sortarr[count($sortarr) - 1] == "DESC") {
$sortdir = "DESC";
}
if (in_array($sortarr[0], array("ID", "OBJECTORDER", "NAME", "PNAME"))) {
$sortcol = $sortarr[0];
}
if ($noTrash) {
$noTrashSQL = "(object.DELETED = 0) AND ";
}
if (!is_array($list)) {
$id = (int) $list;
if ($id < 1) {
return;
}
if (!$this->permissions->checkInternal($this->_uid, $id, "RREAD")) {
return;
}
$sqls = "(lnk.TAGID = {$id}) ";
} else {
if ($concat == 'OR') {
for ($i = 0; $i < count($list); $i++) {
if ($this->permissions->checkInternal($this->_uid, (int) $list[$i], "RREAD")) {
if ($sqls != '') {
$sqls .= " OR ";
}
$sqls .= " (lnk.TAGID = " . (int) $list[$i] . ") ";
}
}
} elseif ($concat == 'AND') {
$tagIds = array();
for ($i = 0; $i < count($list); $i++) {
if ($this->permissions->checkInternal($this->_uid, (int) $list[$i], "RREAD")) {
$tagIds[] = (int) $list[$i];
} else {
return false;
}
}
foreach ($tagIds as $tagId) {
if ($sqls != '') {
$sqls .= " AND ";
}
$sqls .= " ((SELECT COUNT(*) FROM `yg_tags_lnk_" . $this->_objectprefix . "` AS SUBQ WHERE SUBQ.TAGID = " . $tagId . " AND OID = object.OBJECTID AND SUBQ.OVERSION = (SELECT MAX( rgt2.VERSION ) FROM " . $this->_objectpropertytable . " AS rgt2 WHERE (object.OBJECTID = rgt2.OBJECTID)))) > 0 ";
}
$sqls = " (lnk.TAGID IN (" . implode(', ', $tagIds) . ")) AND " . $sqls;
}
}
if ($published === true) {
$sql_final_w = " ((objecttree.VERSIONPUBLISHED = lnk.OVERSION) OR\n\t\t\t(\n\t\t\t\t(objecttree.VERSIONPUBLISHED = " . ALWAYS_LATEST_APPROVED_VERSION . ") AND (object.APPROVED = 1) AND\n\t\t\t\t(\n\t\t\t\t\t(lnk.OVERSION = (SELECT MAX( rgt.VERSION ) FROM " . $this->_objectpropertytable . " AS rgt WHERE (object.OBJECTID = rgt.OBJECTID))) OR\n\t\t\t\t\t(lnk.OVERSION = (SELECT MAX( rgt.VERSION -1) FROM " . $this->_objectpropertytable . " AS rgt WHERE (object.OBJECTID = rgt.OBJECTID)))\n\t\t\t\t)\n\t\t\t))";
} else {
$sql_final_w = " (lnk.OVERSION = (SELECT MAX( rgt.VERSION ) FROM " . $this->_objectpropertytable . " AS rgt WHERE (object.OBJECTID = rgt.OBJECTID)))";
}
if ($filterArray) {
$filterSelect = $filterFrom = $filterWhere = $filterLimit = $filterOrder = '';
buildBackendFilter('TagGetByTagCB', $filterArray, $filterSelect, $filterFrom, $filterWhere, $filterLimit, $filterOrder);
$sql_final_w .= $filterWhere;
}
if ($this->_objectprefix == "pages") {
$sqlsite = " (lnk.SITEID = " . $this->_object->_site . ") AND ";
}
if ($sqls == '') {
return;
}
$sql = "SELECT\n\t\t\tobject.*, {$dynpropw} object.OBJECTID AS ID, cat.OBJECTID AS TAGID, objecttree.LFT AS OBJECTORDER, objecttree.*\n\t\t\tFROM\n\t\t\t(" . $this->_objectpropertytable . " AS object, `yg_tags_lnk_" . $this->_objectprefix . "` AS lnk, yg_tags_properties AS cat, " . $this->_objecttreetable . " AS objecttree)\n\t\t\t{$dynpropsql}\n\t\t\tWHERE\n\t\t\t(object.OBJECTID = lnk.OID) AND (lnk.TAGID = cat.OBJECTID) AND\n\t\t\t{$noTrashSQL}\n\t\t\t({$sqls}) AND {$sqlsite}\n\t\t\t(object.OBJECTID = objecttree.ID) AND (lnk.OVERSION = object.VERSION) AND {$sql_final_w}\n\t\t\tGROUP BY OBJECTID ORDER BY " . $sortcol . " " . $sortdir . " " . $filterLimit;
$ra = $this->cacheExecuteGetArray($sql);
return $ra;
}
/**
* Gets a list of Cblocks
*
* @param int $cbId (optional) Id of the parent Cblock from which the list will be created
* @param array $filter (optional, may be combined) If SUBNODES, only subnodes of the specified Cblock will be returned<br>
* if PSUBNODES, only subnodes including the specified Cblock will be returned<br>
* if TRASHCAN, only items in the Trash will be returned<br>
* if PUBLISHED, only live/published versions will be returned
* @param int $maxLevel (optional) Specifies the maximum level of nodes to get
* @param int $permissionsForRoleId (optional) If '1' then return all Usergroups and Permissions for this node
* @param array $filterArray Array of filters for the SQL query
* @return array|false Array of Cblocks or FALSE in case of an error
*/
function getList($cbId = 0, $filter = array(), $maxlevel = 0, $permissionsForRoleId = 0, $filterArray)
{
$cbId = (int) $cbId;
$maxlevel = (int) $maxlevel;
$permissionsForRoleId = (int) $permissionsForRoleId;
$rootGroupId = (int) sConfig()->getVar("CONFIG/SYSTEMUSERS/ROOTGROUPID");
if ($cbId == 0) {
$cbId = $this->tree->getRoot();
}
// Surpress items in trashcan if not explicitly asked for
if (in_array("TRASHCAN", $filter)) {
$filterSQL_WHERE = " AND prop.DELETED = 1 AND prop.FOLDER = 0";
} else {
$filterSQL_WHERE = " AND prop.DELETED = 0";
}
if (in_array("PUBLISHED", $filter)) {
$filterSQL_WHERE .= " AND (\n\t\t\t\t\t\t\t\t\t\t(group2.VERSIONPUBLISHED = prop.VERSION) OR\n\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t(group2.VERSIONPUBLISHED = " . ALWAYS_LATEST_APPROVED_VERSION . ") AND\n\t\t\t\t\t\t\t\t\t\t\t(prop.VERSION = (SELECT MAX( rgt.VERSION ) FROM yg_contentblocks_properties AS rgt WHERE (prop.OBJECTID = rgt.OBJECTID) AND (rgt.APPROVED = 1)))\n\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t) ";
} else {
$filterSQL_WHERE .= " AND (prop.VERSION = (SELECT MAX( rgt.VERSION ) FROM yg_contentblocks_properties AS rgt WHERE (prop.OBJECTID = rgt.OBJECTID)))";
}
// Check if special filter was suppplied
$filterOrder = 'order by group2.LFT';
if ($filterArray) {
$filterSelect = $filterFrom = $filterWhere = $filterLimit = $filterOrder = '';
buildBackendFilter('CBlocksSearchCB', $filterArray, $filterSelect, $filterFrom, $filterWhere, $filterLimit, $filterOrder);
$filterSQL_WHERE .= $filterWhere;
}
if ($maxlevel > 0) {
$maxLevelSQL = " AND (group2.LEVEL <= {$maxlevel}) AND (group1.LEVEL <= {$maxlevel})";
}
if (in_array("SUBNODES", $filter)) {
$myinfo = $this->tree->getAll($cbId);
$myleft = $myinfo["LFT"];
$myright = $myinfo["RGT"];
$subnodeSQL = " AND (group1.LFT > {$myleft}) AND (group1.RGT < {$myright})";
if (!$myinfo) {
return false;
}
}
if (in_array("PSUBNODES", $filter)) {
$myinfo = $this->tree->getAll($cbId);
$myleft = $myinfo["LFT"];
$myright = $myinfo["RGT"];
$subnodeSQL = " AND (group1.LFT >= {$myleft}) AND (group1.RGT =< {$myright})";
if (!$myinfo) {
return false;
}
}
// SQL for permissions
$perm_SQL_SELECT = ", MAX(perm.RREAD) AS RREAD, MAX(perm.RWRITE) AS RWRITE, MAX(perm.RDELETE) AS RDELETE, MAX(perm.RSUB) AS RSUB, MAX(perm.RSTAGE) AS RSTAGE, MAX(perm.RMODERATE) AS RMODERATE, MAX(perm.RCOMMENT) AS RCOMMENT";
$perm_SQL_FROM = " LEFT JOIN yg_contentblocks_permissions AS perm ON perm.OID = group2.ID";
if ($permissionsForRoleId > 0) {
$perm_SQL_FROM .= " AND (perm.USERGROUPID = " . $permissionsForRoleId . ")";
} else {
$perm_SQL_WHERE = " AND (";
$roles = $this->permissions->getUsergroups();
for ($r = 0; $r < count($roles); $r++) {
$perm_SQL_WHERE .= "(perm.USERGROUPID = " . (int) $roles[$r]["ID"] . ") ";
if (count($roles) - $r > 1) {
$perm_SQL_WHERE .= " OR ";
}
}
$perm_SQL_WHERE .= ") ";
$perm_SQL_WHERE .= " AND ((RREAD >= 1) OR (perm.USERGROUPID = {$rootGroupId})) ";
}
$sql = "SELECT\n\t\t\t\t\tgroup2.LFT,\n\t\t\t\t\tgroup2.RGT,\n\t\t\t\t\tgroup2.VERSIONPUBLISHED,\n\t\t\t\t\tgroup2.ID, group2.LEVEL AS LEVEL,\n\t\t\t\t\tgroup2.PARENT AS PARENT,\n\t\t\t\t\tgroup2.PNAME AS PNAME,\n\t\t\t\t\tprop.VERSION AS VERSION,\n\t\t\t\t\tprop.FOLDER AS FOLDER,\n\t\t\t\t\tprop.HASCHANGED AS HASCHANGED,\n\t\t\t\t\tprop.EMBEDDED AS EMBEDDED,\n\t\t\t\t\tpv.*,\n\t\t\t\t\tprop.CREATEDTS,\n\t\t\t\t\tprop.CHANGEDTS,\n\t\t\t\t\tprop.CREATEDBY,\n\t\t\t\t\tprop.CHANGEDBY\n\t\t\t\t\t{$perm_SQL_SELECT}\n\t\t\t\tFROM\n\t\t\t\t\t({$this->table} AS group1, {$this->table} AS group2, yg_contentblocks_properties AS prop)\n\t\t\t\t\t{$perm_SQL_FROM}\n\t\t\t\tLEFT JOIN yg_contentblocks_propsv AS pv\n\t\t\t\t\tON pv.OID = prop.ID\n\t\t\t\tWHERE\n\t\t\t\t\t((group2.LFT >= group1.LFT) AND (group2.LFT <= group1.RGT)) AND\n\t\t\t\t\t(group2.ID = prop.OBJECTID)\n\t\t\t\t\t{$perm_SQL_WHERE}\n\t\t\t\t\t{$maxLevelSQL}\n\t\t\t\t\t{$subnodeSQL}\n\t\t\t\t\t{$filterSQL_WHERE}\n\t\t\t\tGROUP BY\n\t\t\t\t\tgroup2.LFT, group2.RGT, group2.VERSIONPUBLISHED, group2.ID\n\t\t\t\t{$filterOrder} {$filterLimit};";
$blaetter = $this->cacheExecuteGetArray($sql);
return $blaetter;
}
/**
* Gets a list of Mailings
*
* @param int $mailingId (optional) Id of the parent Mailing from which the list will be created
* @param array $filter (optional, may be combined) If SUBNODES, only subnodes of the specified Mailing will be returned<br>
* if TRASHCAN, only items in the Trash will be returned<br>
* if PUBLISHED, the working copy will be returned
* @param int $maxLevel (optional) Specifies the maximum level of nodes to get
* @param int $permissionsForRoleId (optional) If '1' then return all Usergroups and Permissions for this Usergroup
* @param array $filterArray Array of filters for the SQL query
* @return array|false Array of Mailings or FALSE in case of an error
*/
function getList($mailingId = 0, $filter = array(), $maxLevel = 0, $permissionsForRoleId = 0, $filterArray)
{
$mailingId = (int) $mailingId;
$maxLevel = (int) $maxLevel;
$permissionsForRoleId = (int) $permissionsForRoleId;
$rootGroupId = (int) sConfig()->getVar("CONFIG/SYSTEMUSERS/ROOTGROUPID");
if ($mailingId == 0) {
$mailingId = $this->tree->getRoot();
}
if ($maxLevel > 0) {
$maxLevelsql = " AND (group2.LEVEL <= {$maxLevel}) AND (group1.LEVEL <= {$maxLevel})";
}
if (in_array("SUBNODES", $filter)) {
$myinfo = $this->tree->getAll($mailingId);
$myleft = $myinfo["LFT"];
$myrgt = $myinfo["RGT"];
$subnodesql = " AND (group1.LFT > {$myleft} AND group1.RGT < {$myrgt})";
if (!$myinfo) {
return false;
}
}
// surpress items in trashcan if not explicitly asked for
if (in_array("TRASHCAN", $filter)) {
$filtersql_where = " AND prop.DELETED = 1";
} else {
$filtersql_where = " AND prop.DELETED = 0";
}
$filterOrder = 'ORDER BY prop.CHANGEDTS DESC';
// Check if special filter was suppplied
if ($filterArray) {
$filterSelect = $filterFrom = $filterWhere = $filterLimit = $filterOrder = '';
buildBackendFilter('MailingsSearchCB', $filterArray, $filterSelect, $filterFrom, $filterWhere, $filterLimit, $filterOrder);
$filtersql_where .= $filterWhere;
}
// AND (prop.ACTIVE = 1) - 20070313
if (in_array("PUBLISHED", $filter)) {
$filtersql_where .= "\n\t\t\t\tAND (\n\t\t\t\t\t\t(group2.VERSIONPUBLISHED = prop.VERSION)\n\t\t\t\t\tOR\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t(group2.VERSIONPUBLISHED = " . ALWAYS_LATEST_APPROVED_VERSION . ") AND\n\t\t\t\t\t\t\t(prop.VERSION = (SELECT MAX( rgt.VERSION ) FROM yg_mailing_properties AS rgt WHERE (prop.OBJECTID = rgt.OBJECTID) AND (rgt.APPROVED = 1)))\n\t\t\t\t\t\t)\n\t\t\t\t\t) ";
} else {
$filtersql_where .= " AND (prop.VERSION = (SELECT MAX( rgt.VERSION ) FROM yg_mailing_properties AS rgt WHERE (prop.OBJECTID = rgt.OBJECTID))) ";
$lastfinal = ", (SELECT MAX(VERSION) FROM yg_mailing_properties AS p2 WHERE p2.APPROVED = 1 AND p2.OBJECTID = prop.OBJECTID ) AS LASTAPPROVED ";
}
$perm_sql_select = ", MAX(perm.RREAD) AS RREAD, MAX(perm.RWRITE) AS RWRITE, MAX(perm.RDELETE) AS RDELETE, MAX(perm.RSUB) AS RSUB, MAX(perm.RSTAGE) AS RSTAGE, MAX(perm.RMODERATE) AS RMODERATE, MAX(perm.RCOMMENT) AS RCOMMENT, MAX(perm.RSEND) AS RSEND";
$perm_sql_from = " LEFT JOIN yg_mailing_status AS stat ON stat.OID = group2.ID";
$perm_sql_from .= " LEFT JOIN yg_mailing_permissions AS perm ON perm.OID = group2.ID";
if ($permissionsForRoleId > 0) {
$perm_sql_from .= " AND (perm.USERGROUPID = " . $permissionsForRoleId . ")";
} else {
$perm_sql_where = " AND (";
$roles = $this->permissions->getUsergroups();
for ($r = 0; $r < count($roles); $r++) {
$perm_sql_where .= "(perm.USERGROUPID = " . $roles[$r]["ID"] . ") ";
if (count($roles) - $r > 1) {
$perm_sql_where .= " OR ";
}
}
$perm_sql_where .= ") ";
$perm_sql_where .= " AND ((RREAD >= 1) OR (perm.USERGROUPID = {$rootGroupId})) ";
}
$sql = "SELECT\n\t\t\t\t\tgroup2.LFT,\n\t\t\t\t\tgroup2.RGT,\n\t\t\t\t\tgroup2.VERSIONPUBLISHED AS VERSIONPUBLISHED,\n\t\t\t\t\tgroup2.ID,\n\t\t\t\t\tgroup2.LEVEL AS LEVEL,\n\t\t\t\t\tgroup2.PARENT AS PARENT,\n\t\t\t\t\tgroup2.PNAME AS PNAME,\n\t\t\t\t\tMAX(prop.VERSION) AS VERSION,\n\t\t\t\t\tprop.ACTIVE AS ACTIVE,\n\t\t\t\t\tprop.HIDDEN AS HIDDEN,\n\t\t\t\t\tprop.LOCKED AS LOCKED,\n\t\t\t\t\tprop.HASCHANGED AS HASCHANGED,\n\t\t\t\t\tprop.TEMPLATEID AS TEMPLATEID,\n\t\t\t\t\tprop.CREATEDTS,\n\t\t\t\t\tprop.CHANGEDTS,\n\t\t\t\t\tprop.CREATEDBY,\n\t\t\t\t\tprop.CHANGEDBY,\n\t\t\t\t\tpv.*\n\t\t\t\t\t{$perm_sql_select}\n\t\t\t\t\t{$lastfinal}\n\t\t\t\tFROM\n\t\t\t\t\t({$this->table} AS group1, {$this->table} AS group2, yg_mailing_properties AS prop)\n\t\t\t\t\t{$perm_sql_from}\n\t\t\t\tLEFT JOIN yg_mailing_propsv AS pv\n\t\t\t\t\tON pv.OID = prop.ID\n\t\t\t\tWHERE\n\t\t\t\t\t((group2.LFT >= group1.LFT) AND (group2.LFT <= group1.RGT)) AND\n\t\t\t\t\t(group2.ID = prop.OBJECTID) {$subnodesql} {$filtersql_where}\n\t\t\t\t\t{$perm_sql_where}\n\t\t\t\t\t{$maxLevelsql}\n\t\t\t\tGROUP BY\n\t\t\t\t\tgroup2.LFT, group2.RGT, group2.VERSIONPUBLISHED, group2.ID\n\t\t\t\t{$filterOrder} {$filterLimit};";
$blaetter = $this->cacheExecuteGetArray($sql);
return $blaetter;
}
/**
* Gets all Comments
*
* @param array $filterArray Filter array
* @param string $filterLimit Filter limit
* @return array Array of Comments
*/
function getAllComments($filterArray, $filterLimit)
{
if ($filterArray) {
$filterSelect = $filterFrom = $filterWhere = $filterLimit = $filterOrder = $filterHaving = '';
buildBackendFilter('CommentsFilterCB', $filterArray, $filterSelect, $filterFrom, $filterWhere, $filterLimit, $filterOrder, $filterHaving);
}
if ($filterLimit) {
$filterLimit = "LIMIT " . $filterLimit;
}
if ($filterHaving) {
$filterHaving = "AND " . $filterHaving;
}
$filterOrder = 'DESC';
$siteMgr = new Sites();
$allSites = $siteMgr->getList();
$sitesIfExpressionSQL = '';
$sitesIfExpressionSQL2 = '';
$sitesJoinExpressionSQL = '';
$sitesIDCoalesceSQL = '';
$sitesNAMECoalesceSQL = '';
$sitesRREADCoalesceSQL = '';
$sitesRMODERATECoalesceSQL = '';
$sitesRCOMMENTCoalesceSQL = '';
$sitesUSERGROUPIDCoalesceSQL = '';
$sitesUSERGROUPIDHavingSQL = '';
$currUser = new User($this->_uid);
$roles = $currUser->getUsergroups();
foreach ($allSites as $site) {
$sitesIfExpressionSQL .= "\n\t\t\t\tIF(yg_site_" . $site['ID'] . "_tree.id IS NOT NULL, 'PAGE', ";
$sitesIfExpressionSQL2 .= "\n\t\t\t\tIF(yg_site_" . $site['ID'] . "_tree.id IS NOT NULL, '" . $site['ID'] . "', ";
$sitesJoinExpressionSQL .= "\n\t\t\t\tLEFT JOIN yg_comments_lnk_pages_" . $site['ID'] . "\n\t\t\t\tON (c.id = yg_comments_lnk_pages_" . $site['ID'] . ".commentid)\n\t\t\t\tLEFT JOIN yg_site_" . $site['ID'] . "_tree\n\t\t\t\tON (yg_site_" . $site['ID'] . "_tree.id = yg_comments_lnk_pages_" . $site['ID'] . ".OID)\n\t\t\t\tLEFT JOIN yg_site_" . $site['ID'] . "_permissions\n\t\t\t\tON (yg_site_" . $site['ID'] . "_permissions.OID = yg_comments_lnk_pages_" . $site['ID'] . ".OID)\n\t\t\t\t";
$sitesIDCoalesceSQL .= ", yg_site_" . $site['ID'] . "_tree.ID";
$sitesNAMECoalesceSQL .= ", yg_site_" . $site['ID'] . "_tree.ID";
$sitesRREADCoalesceSQL .= ", (yg_site_" . $site['ID'] . "_permissions.RREAD)";
$sitesRMODERATECoalesceSQL .= ", (yg_site_" . $site['ID'] . "_permissions.RMODERATE)";
$sitesRCOMMENTCoalesceSQL .= ", (yg_site_" . $site['ID'] . "_permissions.RCOMMENT)";
$sitesUSERGROUPIDCoalesceSQL .= ", yg_site_" . $site['ID'] . "_permissions.USERGROUPID";
}
$sitesUSERGROUPIDHavingSQL .= " AND (";
for ($r = 0; $r < count($roles); $r++) {
$sitesUSERGROUPIDHavingSQL .= "(USERGROUPID = " . $roles[$r]["ID"] . ")";
if (count($roles) - $r > 1) {
$sitesUSERGROUPIDHavingSQL .= " OR ";
}
}
$sitesUSERGROUPIDHavingSQL .= ")";
$sitesIfExpressionSQL .= "NULL ))" . str_repeat(')', count($allSites));
$sitesIfExpressionSQL2 .= "NULL " . str_repeat(')', count($allSites));
$sql = "SELECT\n\t\t\tc.*,\n\t\t\tCOALESCE(yg_contentblocks_tree.PNAME, yg_files_tree.TITLE {$sitesNAMECoalesceSQL}) AS `NAME`,\n\t\t\tIF(yg_contentblocks_tree.id IS NOT NULL, 'CO',\n\t\t\tIF(yg_files_tree.id IS NOT NULL, 'FILE',\n\t\t\t{$sitesIfExpressionSQL} AS OBJECTTYPE,\n\t\t\tCOALESCE(yg_contentblocks_tree.ID, yg_files_tree.ID {$sitesIDCoalesceSQL}) AS `OBJECTID`,\n\t\t\t{$sitesIfExpressionSQL2} AS SITEID,\n\n\t\t\tCOALESCE((yg_contentblocks_permissions.RREAD), (yg_files_permissions.RREAD) {$sitesRREADCoalesceSQL}) AS `RREAD`,\n\t\t\tCOALESCE((yg_contentblocks_permissions.RMODERATE), (yg_files_permissions.RMODERATE) {$sitesRMODERATECoalesceSQL}) AS `RMODERATE`,\n\t\t\tCOALESCE((yg_contentblocks_permissions.RCOMMENT), (yg_files_permissions.RCOMMENT) {$sitesRCOMMENTCoalesceSQL}) AS `RCOMMENT`,\n\n\t\t\tCOALESCE(yg_contentblocks_permissions.USERGROUPID, yg_files_permissions.USERGROUPID {$sitesUSERGROUPIDCoalesceSQL}) AS `USERGROUPID`\n\t\t\tFROM\n\t\t\tyg_comments AS c\n\n\t\t\tLEFT JOIN yg_comments_lnk_cb\n\t\t\t\tON (c.id = yg_comments_lnk_cb.commentid)\n\t\t\tLEFT JOIN yg_contentblocks_tree\n\t\t\t\tON (yg_contentblocks_tree.id = yg_comments_lnk_cb.oid)\n\n\t\t\tLEFT JOIN yg_comments_lnk_files\n\t\t\t\tON (c.id = yg_comments_lnk_files.commentid)\n\t\t\tLEFT JOIN yg_files_tree\n\t\t\t\tON (yg_files_tree.id = yg_comments_lnk_files.oid)\n\n\t\t\tLEFT JOIN yg_contentblocks_permissions\n\t\t\t\tON (yg_contentblocks_permissions.OID = yg_comments_lnk_cb.OID)\n\t\t\tLEFT JOIN yg_files_permissions\n\t\t\t\tON (yg_files_permissions.OID = yg_comments_lnk_files.OID)\n\n\t\t\t{$sitesJoinExpressionSQL}\n\t\t\tWHERE\n\t\t\t\t(1)\n\t\t\t{$filterWhere}\n\t\t\tGROUP BY\n\t\t\t\tc.ID\n\t\t\tHAVING\n\t\t\t\t(RREAD > 0)\n\t\t\t{$sitesUSERGROUPIDHavingSQL}\n\t\t\t{$filterHaving}\n\t\t\tORDER BY\n\t\t\tc.CREATEDTS {$filterOrder} {$filterLimit};";
$resultarray = $this->cacheExecuteGetArray($sql);
if ($resultarray === false) {
return ERROR_COMMENTS_UNKNOWN;
}
return $resultarray;
}
