public static function countByCodename($codename, $id = null)
{
$sql = 'SELECT count(*) as count from mod_object_oembed_definition where codename_mood = "' . io::sanitizeSQLString($codename) . '"';
if ($id) {
$sql .= ' AND id_mood <> ' . $id;
}
$query = new CMS_query($sql);
$data = array_pop($query->getAll());
return (int) $data['count'];
}
/**
* Sort and limit found ids by orders and limit clauses
* This method limit results to existant objects too
*
* @access private
* @return array of object ids sorted
*/
protected function _sortIds()
{
$statusSuffix = $this->_public ? "_public" : "_edited";
$ids = array();
if ($this->_orderConditions) {
//reverse order conditions (needed to get natural order)
$orderConditions = array_reverse($this->_orderConditions, true);
//loop on each order conditions
foreach ($orderConditions as $type => $value) {
$sql = '';
if (!isset($value['direction']) || !$value['direction']) {
$value['direction'] = 'asc';
}
if (!isset($value['operator']) || !$value['operator']) {
$value['operator'] = '';
}
$direction = $value['direction'];
$operator = $value['operator'];
//add previously found ids to where clause
if (is_array($this->_resultsIds) && $this->_resultsIds) {
//update tmp table with found ids
$this->_updateTmpList($this->_resultsIds);
$where = ' and objectID in (' . $this->_getSQLTmpList() . ')';
} else {
$where = '';
}
switch ($type) {
case "publication date after":
// Date start
// Date start
case "publication date before":
// Date start
// Date start
case "publication date start":
// Date start
$sql = "\n\t\t\t\t\t\t\t\tselect\n\t\t\t\t\t\t\t\t\tdistinct objectID\n\t\t\t\t\t\t\t\tfrom\n\t\t\t\t\t\t\t\t\tmod_subobject_integer" . $statusSuffix . ",\n\t\t\t\t\t\t\t\t\tresources,\n\t\t\t\t\t\t\t\t\tresourceStatuses\n\t\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\t\tobjectFieldID = '0'\n\t\t\t\t\t\t\t\t\tand value = id_res\n\t\t\t\t\t\t\t\t\tand status_res=id_rs\n\t\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\torder by publicationDateStart_rs " . $direction;
break;
case "publication date end":
// Date end
$sql = "\n\t\t\t\t\t\t\t\tselect\n\t\t\t\t\t\t\t\t\tdistinct objectID\n\t\t\t\t\t\t\t\tfrom\n\t\t\t\t\t\t\t\t\tmod_subobject_integer" . $statusSuffix . ",\n\t\t\t\t\t\t\t\t\tresources,\n\t\t\t\t\t\t\t\t\tresourceStatuses\n\t\t\t\t\t\t\t\twhere\n\t\t\t\t\t\t\t\t\tobjectFieldID = '0'\n\t\t\t\t\t\t\t\t\tand value = id_res\n\t\t\t\t\t\t\t\t\tand status_res=id_rs\n\t\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\torder by publicationDateEnd_rs " . $direction;
break;
case 'objectID':
$ids = $ids ? $ids : $this->_resultsIds;
ksort($ids, SORT_NUMERIC);
$ids = $direction == 'asc' ? $ids : array_reverse($ids, true);
break;
case 'itemsOrdered':
$orderedIds = $direction == 'asc' ? $this->_orderConditions['itemsOrdered']['order'] : array_reverse($this->_orderConditions['itemsOrdered']['order'], true);
$ids = array_intersect($orderedIds, $ids ? $ids : $this->_resultsIds);
unset($orderedIds);
break;
case 'random':
$tmpIds = $ids ? $ids : $this->_resultsIds;
shuffle($tmpIds);
$ids = array();
foreach ($tmpIds as $id) {
$ids[$id] = $id;
}
break;
case "relevance":
//this order condition is replaced by an itemsOrdered order at the end of _getIds method
break;
default:
if (sensitiveIO::isPositiveInteger($type)) {
if (!isset($this->_fieldsDefinitions[$type]) || !is_object($this->_fieldsDefinitions[$type])) {
//get object fields definition
$this->_fieldsDefinitions = CMS_poly_object_catalog::getFieldsDefinition($this->_object->getID());
}
if (isset($this->_fieldsDefinitions[$type])) {
//get type object for field
$objectField = $this->_fieldsDefinitions[$type]->getTypeObject();
$operator = isset($operator) ? $operator : '';
$sql = $objectField->getFieldOrderSQL($type, $direction, $operator, $where, $this->_public);
} else {
$this->raiseError('Unknown field ' . $type . ' to use as order with value ' . print_r($value, true));
}
}
break;
}
if ($sql) {
if (isset($ids) && $ids) {
$sql .= " , field(objectID, " . implode(',', array_reverse($ids)) . ") desc ";
}
$q = new CMS_query($sql);
$orderedIds = array();
if (!$q->hasError()) {
//save ordered ids
while ($id = $q->getValue('objectID')) {
$orderedIds[$id] = $id;
}
}
$ids = $orderedIds;
}
}
} else {
$ids = $this->_resultsIds;
}
//check for results existance in objects datas tables
if ($ids) {
//update tmp table with found ids
$this->_updateTmpList($ids);
$where = ' objectID in (' . $this->_getSQLTmpList() . ')';
$sql = "\n\t\t\t\tselect\n\t\t\t\t\tdistinct objectID\n\t\t\t\tfrom\n\t\t\t\t\tmod_subobject_text" . $statusSuffix . "\n\t\t\t\twhere\n\t\t\t\t\t{$where}\n\t\t\t\tunion distinct\n\t\t\t\tselect\n\t\t\t\t\tdistinct objectID\n\t\t\t\tfrom\n\t\t\t\t\tmod_subobject_integer" . $statusSuffix . "\n\t\t\t\twhere\n\t\t\t\t\t{$where}\n\t\t\t\tunion distinct\n\t\t\t\tselect\n\t\t\t\t\tdistinct objectID\n\t\t\t\tfrom\n\t\t\t\t\tmod_subobject_string" . $statusSuffix . "\n\t\t\t\twhere\n\t\t\t\t\t{$where}\n\t\t\t\tunion distinct\n\t\t\t\tselect\n\t\t\t\t\tdistinct objectID\n\t\t\t\tfrom\n\t\t\t\t\tmod_subobject_date" . $statusSuffix . "\n\t\t\t\twhere\n\t\t\t\t\t{$where}\n\t\t\t";
$q = new CMS_query($sql);
if ($q->getNumRows() != count($ids)) {
$foundIds = $q->getAll(PDO::FETCH_COLUMN, 0);
if ($foundIds) {
$ids = array_intersect($ids, $foundIds);
//update count of results
$this->_numRows = sizeof($ids);
} else {
$ids = array();
$this->_numRows = 0;
}
}
}
//Limit results if needed
if ($ids && $this->_numRows > 0 && $this->_itemsPerPage > 0) {
$ids = array_slice($ids, $this->_page * $this->_itemsPerPage, $this->_itemsPerPage, true);
}
return $ids;
}
/**
* Returns the action for the given user on this icr / datastrip
* @param integer $uid the user id
* @param string $icr the ICR identifier
* @param string $datastripId the datastrip identifier
* @return mixed the action or NULL
*/
public static function getData($campaignId)
{
$sql = 'SELECT data from mod_mailjet where campaignId = ' . $campaignId . ';';
$query = new CMS_query($sql);
$res = $query->getAll();
if (isset($res[0])) {
return json_decode($res[0]['data'], true);
}
return null;
}
/**
* Get field order SQL request (used by class CMS_object_search)
*
* @param integer $fieldID : this field id in object (aka $this->_field->getID())
* @param mixed $direction : the direction to search (asc/desc)
* @param string $operator : additionnal search operator
* @param string $where : where clauses to add to SQL
* @param boolean $public : values are public or edited ? (default is edited)
* @return string : the SQL request
* @access public
*/
function getFieldOrderSQL($fieldID, $direction, $operator, $where, $public = false)
{
global $cms_language;
$statusSuffix = $public ? "_public" : "_edited";
$supportedOperator = array('label', 'atmorder');
if ($operator && !in_array($operator, $supportedOperator)) {
$this->_raiseError(get_class($this) . " : getFieldSearchSQL : unkown search operator : " . $operator . ", use default search instead");
$operator = false;
}
if ($operator == 'label' && !is_object($cms_language)) {
$this->_raiseError(get_class($this) . " : getFieldSearchSQL : unkown cms_language to use for label search order, use default search instead");
$operator = false;
}
$sql = '';
$fromTable = 'mod_subobject_integer';
if (!$operator) {
// create sql
$sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\t" . $fromTable . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\t{$where}\n\t\t\torder by value " . $direction;
} else {
switch ($operator) {
case 'label':
$sql = "\n\t\t\t\t\t\tselect\n\t\t\t\t\t\t\tdistinct objectID\n\t\t\t\t\t\tfrom\n\t\t\t\t\t\t\t" . $fromTable . $statusSuffix . ",\n\t\t\t\t\t\t\tmodulesCategories_i18nm\n\t\t\t\t\t\twhere\n\t\t\t\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\t\t\t\tand category_mcl = value\n\t\t\t\t\t\t\tand language_mcl = '" . $cms_language->getCode() . "'\n\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\torder by label_mcl " . $direction;
break;
case 'atmorder':
$sqlTemp = "select distinct(modulesCategories.root_mca) as root from " . $fromTable . $statusSuffix . ", modulesCategories where id_mca = value\n\t\t\t\t\t\t\t{$where}";
$q = new CMS_query($sqlTemp);
$roots = $q->getAll();
$catOrder = array();
foreach ($roots as $aRoot) {
$sqlRoot = "select * from modulesCategories where root_mca = " . $aRoot['root'] . ' or id_mca = ' . $aRoot['root'];
$qRoot = new CMS_query($sqlRoot);
$allCats = $qRoot->getAll();
foreach ($allCats as $aCategory) {
$catOrder[$aCategory['id_mca']] = $aCategory['order_mca'];
}
}
$allCatsSql = "select modulesCategories.* from " . $fromTable . $statusSuffix . ", modulesCategories where id_mca = value\n\t\t\t\t\t\t\t{$where}";
// get all categories matching the objects
$q = new CMS_query($allCatsSql);
$cats = $q->getAll();
$rootCalculated = false;
$allCatsWeights = array();
foreach ($cats as $aCat) {
$lineage = $aCat['lineage_mca'];
$lineage_parts = explode(";", $lineage);
$catWeight = 0;
$depth = 1;
//CMS_grandFather::log($lineage);
foreach ($lineage_parts as $value) {
$catWeight += isset($catOrder[$value]) ? $catOrder[$value] * pow(0.001, $depth) : 0;
$depth++;
}
$allCatsWeights[$aCat['id_mca']] = $catWeight;
}
$orderClauses = 'ORDER BY CASE ';
foreach ($allCatsWeights as $cat => $weight) {
$orderClauses .= "\n\t\t\t\t\t\t\tWHEN value = " . $cat . " THEN " . $weight;
}
$orderClauses .= ' END ' . $direction;
$sql = "\n\t\t\t\t\t\tselect\n\t\t\t\t\t\t\tdistinct objectID\n\t\t\t\t\t\tfrom\n\t\t\t\t\t\t\t" . $fromTable . $statusSuffix . ",\n\t\t\t\t\t\t\tmodulesCategories\n\t\t\t\t\t\twhere\n\t\t\t\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\t\t\t\tand id_mca = value\n\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t" . $orderClauses;
//CMS_grandFather::log($sql);
break;
}
}
return $sql;
}
/**
* Search messages
* Static function.
*
* @param string module : module to search messages
* @param string search : search message by value
* @param array languagesOnly : limit search to given languages codes
* @param array options : search options
* @param string direction : search is ordered by results id. Specify order direction (asc or desc). Default : asc
* @param integer start : search start offset
* @param integer limit : search limit (default : 0 : unlimited)
* @param integer resultsnb : return results count by reference
* @return array(id => msg)
* @access public
*/
static function searchMessages($module, $search = '', $languagesOnly = array(), $options = array(), $direction = 'asc', $start = 0, $limit = 0, &$resultsnb)
{
$start = (int) $start;
$limit = (int) $limit;
$direction = in_array(io::strtolower($direction), array('asc', 'desc')) ? io::strtolower($direction) : 'asc';
$emptyOnly = $idsOnly = false;
if (is_array($options)) {
$emptyOnly = isset($options['empty']) && $options['empty'] ? true : false;
$idsOnly = isset($options['ids']) && is_array($options['ids']) ? $options['ids'] : false;
}
$keywordsWhere = $languagesWhere = $emptyWhere = $orderBy = $orderClause = $idsWhere = '';
//get ids for which one message is missing
if ($emptyOnly) {
$qLanguages = new CMS_query("\n\t\t\t\tselect \n\t\t\t\t\tdistinct language_mes\n\t\t\t\tfrom \n\t\t\t\t\tmessages\n\t\t\t\twhere\n\t\t\t\t\tmodule_mes = '" . io::sanitizeSQLString($module) . "'\n\t\t\t");
$qIds = new CMS_query("\n\t\t\t\tselect \n\t\t\t\t\tdistinct id_mes\n\t\t\t\tfrom \n\t\t\t\t\tmessages\n\t\t\t\twhere\n\t\t\t\t\tmodule_mes = '" . io::sanitizeSQLString($module) . "'\n\t\t\t");
$allIds = $qIds->getAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 0);
$missingIds = array();
while ($language = $qLanguages->getValue('language_mes')) {
$qLang = new CMS_query("\n\t\t\t\t\tselect \n\t\t\t\t\t\tdistinct id_mes\n\t\t\t\t\tfrom \n\t\t\t\t\t\tmessages\n\t\t\t\t\twhere\n\t\t\t\t\t\tmodule_mes = '" . io::sanitizeSQLString($module) . "'\n\t\t\t\t\t\tand language_mes='" . $language . "'\n\t\t\t\t\t\tand message_mes != ''\n\t\t\t\t");
$ids = $qLang->getAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 0);
$missingIds = array_merge($missingIds, array_diff($allIds, $ids));
}
if (!$missingIds) {
$resultsnb = 0;
return array();
}
$emptyWhere = ' and id_mes in (' . implode($missingIds, ',') . ')';
}
if ($idsOnly) {
$idsWhere = ' and id_mes in (' . io::sanitizeSQLString(implode($idsOnly, ',')) . ')';
}
if ($search) {
//clean user keywords (never trust user input, user is evil)
$search = strtr($search, ",;", " ");
if (isset($options['phrase']) && $options['phrase']) {
$search = str_replace(array('%', '_'), array('\\%', '\\_'), $search);
if (htmlentities($search) != $search) {
$keywordsWhere .= " and (\n\t\t\t\t\t\tmessage_mes like '%" . sensitiveIO::sanitizeSQLString($search) . "%' or message_mes like '%" . sensitiveIO::sanitizeSQLString(htmlentities($search)) . "%'\n\t\t\t\t\t)";
} else {
$keywordsWhere .= " and message_mes like '%" . sensitiveIO::sanitizeSQLString($search) . "%'";
}
} else {
$words = array();
$words = array_map("trim", array_unique(explode(" ", io::strtolower($search))));
$cleanedWords = array();
foreach ($words as $aWord) {
if ($aWord && $aWord != '' && io::strlen($aWord) >= 3) {
$aWord = str_replace(array('%', '_'), array('\\%', '\\_'), $aWord);
$cleanedWords[] = $aWord;
}
}
if (!$cleanedWords) {
//if no words after cleaning, return
return array();
}
foreach ($cleanedWords as $cleanedWord) {
$keywordsWhere .= $keywordsWhere ? " and " : '';
if (htmlentities($aWord) != $aWord) {
$keywordsWhere .= " (\n\t\t\t\t\t\t\tmessage_mes like '%" . sensitiveIO::sanitizeSQLString($cleanedWord) . "%' or message_mes like '%" . sensitiveIO::sanitizeSQLString(htmlentities($cleanedWord)) . "%'\n\t\t\t\t\t\t)";
} else {
$keywordsWhere .= " (\n\t\t\t\t\t\t\tmessage_mes like '%" . sensitiveIO::sanitizeSQLString($cleanedWord) . "%'\n\t\t\t\t\t\t)";
}
}
$keywordsWhere = ' and (' . $keywordsWhere . ')';
}
}
if (is_array($languagesOnly) && $languagesOnly) {
$languagesWhere = ' and language_mes in (\'' . implode($languagesOnly, '\',\'') . '\')';
}
$orderClause = "order by\n\t\t\tid_mes\n\t\t\t" . $direction;
$sql = "\n\t\t\tselect\n\t\t\t\tid_mes as id\n\t\t\tfrom\n\t\t\t\tmessages\n\t\t\twhere \n\t\t\tmodule_mes = '" . io::sanitizeSQLString($module) . "'\n\t\t\t" . $keywordsWhere . "\n\t\t\t" . $languagesWhere . "\n\t\t\t" . $emptyWhere . "\n\t\t\t" . $idsWhere . "\n\t\t";
$q = new CMS_query($sql);
if (!$q->getNumRows()) {
$resultsnb = 0;
return array();
}
$messageIds = array();
$messageIds = $q->getAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE, 0);
$sql = "\n\t\t\tselect\n\t\t\t\tid_mes as id,\n\t\t\t\tmodule_mes as module,\n\t\t\t\tlanguage_mes as language,\n\t\t\t\tmessage_mes as message\n\t\t\tfrom\n\t\t\t\tmessages\n\t\t\twhere \n\t\t\t\tmodule_mes = '" . io::sanitizeSQLString($module) . "'\n\t\t\t\tand id_mes in (" . implode($messageIds, ',') . ")\n\t\t\t\t" . $orderClause . "\n\t\t";
$q = new CMS_query($sql);
if (!$q->getNumRows()) {
$resultsnb = 0;
return array();
}
$messageGroups = array();
$messageGroups = $q->getAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
$resultsnb = count($messageGroups);
if ($limit) {
$messageGroups = array_slice($messageGroups, $start, $limit, true);
}
$messages = array();
foreach ($messageGroups as $key => $messageGroup) {
$messages[$key]['id'] = $key;
foreach ($messageGroup as $message) {
$messages[$key][$message['language']] = $message['message'];
}
}
return $messages;
}
/**
* Get all pages codenames for website
*
* @return array(codename => pageId)
* @access public
*/
function getAllPagesCodenames()
{
$pageIds = CMS_tree::getAllSiblings($this->_root->getID(), false, true);
if (!is_array($pageIds)) {
$pageIds = array();
}
$pageIds[] = $this->_root->getID();
//pr($pagesIds);
$q = new CMS_query("\n\t\t\tselect\n\t\t\t\tpage_pbd, codename_pbd\n\t\t\tfrom\n\t\t\t\tpagesBaseData_edited\n\t\t\twhere\n\t\t\t\tpage_pbd in (" . implode(',', $pageIds) . ")\n\t\t\t\tand codename_pbd != ''\n\t\t");
$pagesCodenames = $q->getAll();
$codenames = array();
foreach ($pagesCodenames as $pageCodename) {
$codenames[$pageCodename['codename_pbd']] = $pageCodename['page_pbd'];
}
return $codenames;
}
/**
* get all the values
*
* @return array the values
* @access public
*/
public function getValues($id)
{
$aLabels = array();
$oQuery = new CMS_query('
SELECT `code_i18nm`, `value_i18nm`
FROM `mod_object_i18nm`
WHERE `id_i18nm` = ' . io::sanitizeSQLString($id) . '
');
if ($oQuery->getNumRows() > 0) {
foreach ($oQuery->getAll(PDO::FETCH_ASSOC) as $aRow) {
$aLabels[$aRow['code_i18nm']] = $aRow['value_i18nm'];
}
}
return $aLabels;
}
/**
* Get object as an array structure used for export
*
* @param array $params The export parameters. Not used here
* @param array $files The reference to the found files used by object
* @return array : the object array structure
* @access public
*/
public function asArray($params = array(), &$files)
{
$oPolymod = new CMS_polymod($this->_objectValues['module']);
$aClass = array('id' => $this->getID(), 'uuid' => $this->getValue('uuid'), 'labels' => CMS_object_i18nm::getValues($this->_objectValues['labelID']), 'descriptions' => CMS_object_i18nm::getValues($this->_objectValues['descriptionID']), 'params' => array('resourceUsage' => $this->_objectValues['resourceUsage'], 'admineditable' => $this->_objectValues['admineditable'], 'composedLabel' => $this->_objectValues['composedLabel'], 'previewURL' => $this->_objectValues['previewURL'], 'indexable' => $this->_objectValues['indexable'], 'multilanguage' => $this->_objectValues['multilanguage'], 'indexURL' => $this->_objectValues['indexURL'], 'resultsDefinition' => $this->_objectValues['resultsDefinition']), 'fields' => array());
if ($aClass['params']['composedLabel']) {
$aClass['params']['composedLabel'] = $oPolymod->convertDefinitionString($aClass['params']['composedLabel'], true);
}
if ($aClass['params']['indexURL']) {
$aClass['params']['indexURL'] = $oPolymod->convertDefinitionString($aClass['params']['indexURL'], true);
}
if ($aClass['params']['previewURL']) {
$aClass['params']['previewURL'] = $oPolymod->convertDefinitionString($aClass['params']['previewURL'], true);
}
if ($aClass['params']['resultsDefinition']) {
$aClass['params']['resultsDefinition'] = $oPolymod->convertDefinitionString($aClass['params']['resultsDefinition'], true);
}
$oQuery = new CMS_query('
SELECT `id_mof`
FROM `mod_object_field`
WHERE `object_id_mof` = ' . $this->_ID . '
');
if ($oQuery->getNumRows()) {
foreach ($oQuery->getAll(PDO::FETCH_ASSOC) as $aRow) {
$oFieldDefiniton = new CMS_poly_object_field($aRow['id_mof']);
$aClass['fields'][] = $oFieldDefiniton->asArray($params, $files);
}
}
return $aClass;
}
/**
* Get all the messages
*
* @param integer $messageId The ID of the message to get
* @param string $module The codename of the module owner of the message
* @return string
*
* @access public
*/
public static function getMessages($messageId, $module = MOD_STANDARD_CODENAME)
{
if (!SensitiveIO::isPositiveInteger($messageId)) {
$this->raiseError("messageId is not a positive integer : " . $messageId);
return false;
}
$oQuery = new CMS_query('
SELECT `language_mes`, `message_mes`
FROM `messages`
WHERE `module_mes` = \'' . io::sanitizeSQLString($module) . '\'
AND `id_mes` = 1
');
if ($oQuery->getNumRows() < 1) {
return false;
}
$aLabels = array();
foreach ($oQuery->getAll(PDO::FETCH_ASSOC) as $aRow) {
$aLabels[$aRow['language_mes']] = $aRow['message_mes'];
}
return $aLabels;
}
public static function exists($id)
{
if (!SensitiveIO::isPositiveInteger($id)) {
return false;
}
$sql = 'SELECT count(*) as c from mod_object_rss_definition where id_mord = "' . $id . '"';
$query = new CMS_query($sql);
$res = $query->getAll();
return $res[0]['c'] > 0;
}
