/**
* getLinkByPostId
*
* Return redirection link value
*
* @param int $postID Identification number of post
* @return string Redirection link
*/
public static function getLinkByPostId($postID)
{
$conn = \DBI::getConnection('slave');
// try get post data
$postData = $conn->sendQuery('SELECT
fp.id post_id,
ft.id topic_id
FROM forum_posts fp
INNER JOIN forum_topics ft
ON ft.id = fp.topic_id
WHERE fp.id = :post_id', array(':post_id' => $postID))->fetch(\PDO::FETCH_OBJ);
if (!$postData) {
throw new \SystemErrorException(array('title' => \View::$language->forum_to_post_error, 'description' => \View::$language->forum_to_post_post_not_found));
}
// try get post offset
$postsOffset = $conn->sendQuery('SELECT
COUNT(1) cnt
FROM forum_posts
WHERE topic_id = :topic_id
AND id <= :post_id', array('topic_id' => $postData->topic_id, 'post_id' => $postData->post_id))->fetch(\PDO::FETCH_COLUMN);
// calculate page number
$pageNumber = $postsOffset / \App::getConfig('forum')->posts_per_page;
// TODO posts per page from member custom settings
$pageNumber = ceil($pageNumber);
// build link
$link = '/forum/topic?id=' . $postData->topic_id;
if ($pageNumber > 1) {
$link .= '&page=' . $pageNumber;
}
$link .= '#topic-post-' . $postData->post_id;
return $link;
}
/**
* getTopicById
*
* Will return topic data
*
* @param int $topicID Identification number of topic
* @return StdClass Topic data
*/
public static function getTopicById($topicID)
{
return \DBI::getConnection('slave')->sendQuery('SELECT
ft.id,
ft.is_closed,
ft.subforum_id,
ft.title topic_title,
ft.description topic_description,
fts.posts_count,
fts.views_count,
sf.title subforum_title,
sf.forum_id,
ff.title forum_title
FROM forum_topics ft
INNER JOIN forum_topics_stat fts
ON fts.topic_id = ft.id
INNER JOIN forum_subforums sf
ON sf.id = ft.subforum_id
INNER JOIN forum_forums ff
ON ff.id = sf.forum_id
WHERE ft.id = :topic_id
', array(':topic_id' => $topicID))->fetch(\PDO::FETCH_OBJ);
}
/**
* getSubForums
*
* Will return array of subforums
*
* @param int $forumID Identification number of forum
* @return array Subforums data
*/
public static function getSubForums($forumID = null)
{
$params = array();
$filter = '';
if ($forumID) {
$params[':forum_id'] = $forumID;
$filter = 'WHERE sf.forum_id = :forum_id';
}
return \DBI::getConnection('slave')->sendQuery("SELECT\n\n sf.id,\n sf.forum_id,\n sf.title,\n sf.description,\n\n sfs.topics_count,\n (sfs.posts_count - sfs.topics_count) posts_count,\n\n fp.id last_post_id,\n fp.last_modified last_post_modified,\n\n ft.title last_topic_title,\n\n a.id last_post_author_id,\n a.login last_post_author_login\n\n FROM forum_subforums sf\n INNER JOIN forum_subforums_stat sfs\n ON sfs.subforum_id = sf.id\n INNER JOIN forum_forums ff\n ON ff.id = sf.forum_id\n LEFT JOIN forum_posts fp\n ON fp.id = sfs.last_post_id\n LEFT JOIN forum_topics ft\n ON ft.id = fp.topic_id\n LEFT JOIN members a\n ON a.id = fp.authored_by\n {$filter}\n ORDER BY ff.sort ASC, sf.sort ASC\n ", $params)->fetchAll(\PDO::FETCH_OBJ);
}
/**
* getPostsByTopicId
*
* Will return array of posts by identification number of topic
*
* @param int $topicID Identification number of topic
* @param int $offset Offset number of first post
* @param int $limit Number of posts per page
* @return array Posts data
*/
public static function getPostsByTopicId($topicID, $offset, $limit)
{
$topicPosts = \DBI::getConnection('slave')->sendQuery("SELECT\n\n fp.id,\n fp.topic_start,\n fp.edited_by,\n fp.moderated_by,\n fp.creation_date,\n fp.last_modified,\n IF(fp.creation_date = fp.last_modified, 0, 1) is_modified,\n fp.post_html,\n\n fm.posts_count author_posts_count,\n\n a.id author_id,\n a.login author_login,\n a.avatar author_avatar,\n ag.name author_group_name\n\n FROM forum_posts fp\n INNER JOIN forum_members fm\n ON fm.author_id = fp.authored_by\n LEFT JOIN members a\n ON a.id = fm.author_id\n LEFT JOIN groups ag\n ON ag.id = a.group_id\n WHERE fp.topic_id = :topic_id\n ORDER BY fp.topic_start DESC, fp.creation_date ASC\n LIMIT {$offset}, {$limit}\n ", array(':topic_id' => $topicID))->fetchAll(\PDO::FETCH_OBJ);
$hCnf = \App::getConfig('hosts');
$mCnf = \App::getConfig('member-defaults');
foreach ($topicPosts as $item) {
if (!$item->author_avatar) {
$item->author_avatar = '//' . $hCnf->st . $mCnf->avatar;
}
}
return $topicPosts;
}
/**
* getSubForumById
*
* Will return subforum data
*
* @param int $subForumID Identification number of subforum
* @return array Subforum data
*/
public static function getSubForumById($subForumID)
{
return \DBI::getConnection('slave')->sendQuery('SELECT
sf.id,
sf.forum_id,
sf.title,
sf.description,
fss.topics_count,
ff.title forum_title
FROM forum_subforums sf
INNER JOIN forum_subforums_stat fss
ON fss.subforum_id = sf.id
INNER JOIN forum_forums ff
ON ff.id = sf.forum_id
WHERE sf.id = :subforum_id', array(':subforum_id' => $subForumID))->fetch(\PDO::FETCH_OBJ);
}
/**
* trySignIn
*
* Try sign in process
*
* @param StdClass $signInData Member sign in data
* @return null
*/
public static function trySignIn(\StdClass $signInData)
{
\View::addLanguageItem('SignInHelper');
$memberData = \DBI::getConnection('master')->sendQuery('SELECT cookie, password, status
FROM members
WHERE email = :email OR login = :login', array(':email' => $signInData->login, ':login' => $signInData->login))->fetch(\PDO::FETCH_OBJ);
// member data not found
if (!$memberData) {
throw new \MemberErrorException(array('title' => \View::$language->sign_in_helper_error, 'description' => \View::$language->sign_in_helper_login_or_password_invalid));
}
// compare password
$hasSamePassword = \common\CryptHelper::verifyHash($signInData->password, $memberData->password);
if (!$hasSamePassword) {
throw new \MemberErrorException(array('title' => \View::$language->sign_in_helper_error, 'description' => \View::$language->sign_in_helper_login_or_password_invalid));
}
// sign in success
$cnf = \App::getConfig('main')->system;
$exp = time() + $cnf->cookie_expires_time;
setcookie($cnf->cookie_name, $memberData->cookie, $exp, '/');
throw new \MemberSuccessException(array('title' => \View::$language->sign_in_helper_success, 'description' => \View::$language->sign_in_helper_success_description, 'redirection' => '/'));
}
/**
* getTree
*
* Will return forums tree data
*
* @param int $forumID Identification number of forum
* @return array Forums tree data
*/
public static function getTree($forumID = null)
{
$params = array();
$filter = '';
if ($forumID) {
$params[':forum_id'] = $forumID;
$filter = 'WHERE ff.id = :forum_id';
}
// get forums
$forums = \DBI::getConnection('slave')->sendQuery("SELECT\n ff.id,\n ff.title,\n ff.description,\n (NULL) subforums\n FROM forum_forums ff\n INNER JOIN forum_forums_stat ffs\n ON ffs.forum_id = ff.id\n AND ffs.subforums_count > 0\n {$filter}\n ORDER BY ff.sort ASC", $params)->fetchAll(\PDO::FETCH_OBJ);
// get subforums
if ($forums) {
$subForums = SubForumsHelper::getSubForums($forumID);
foreach ($forums as $forum) {
$forum->subforums = array();
foreach ($subForums as $subForum) {
if ($subForum->forum_id == $forum->id) {
$forum->subforums[] = $subForum;
}
}
}
}
return $forums;
}
/**
* getTopics
*
* Will return topics data
*
* @param int $subForumID Identification number of subforum
* @param int $offset Offset number of first topic
* @param int $limit Number of topics per page
* @return array Topics data
*/
public static function getTopics($subForumID, $offset, $limit)
{
return \DBI::getConnection('slave')->sendQuery("SELECT\n\n ft.id,\n ft.creation_date,\n ft.last_modified,\n ft.is_locked,\n ft.is_important,\n ft.is_closed,\n ft.title,\n ft.description,\n\n IF(fts.posts_count > 1, fts.posts_count - 1, 0) posts_count,\n fts.views_count,\n fts.last_post_id,\n\n fp.last_modified last_post_modified\n,\n ta.id author_id,\n ta.login author_login,\n\n pa.id last_post_author_id,\n pa.login last_post_author_login\n\n FROM forum_topics ft\n INNER JOIN forum_topics_stat fts\n ON fts.topic_id = ft.id\n LEFT JOIN forum_posts fp\n ON fp.id = fts.last_post_id\n LEFT JOIN members ta\n ON ta.id = ft.authored_by\n LEFT JOIN members pa\n ON pa.id = fp.authored_by\n WHERE ft.subforum_id = :subforum_id\n ORDER BY ft.is_locked DESC, ft.creation_date DESC\n LIMIT {$offset}, {$limit}\n ", array(':subforum_id' => $subForumID))->fetchAll(\PDO::FETCH_OBJ);
}
/**
* createUser
*
* Create a new user
*
* @param StdClass $userData User data
* @return int User identification number
*/
public function createUser(\StdClass $userData)
{
$conn = \DBI::getConnection('master');
$conn->sendQuery("INSERT INTO members (\n group_id,\n cookie,\n email,\n login,\n password,\n time_zone,\n creation_date,\n last_ip,\n last_visit,\n status,\n activation_hash,\n avatar\n ) VALUES (\n :group_id,\n '',\n :email,\n :login,\n :password,\n :time_zone,\n NOW(),\n '',\n NOW(),\n :status,\n :activation_hash,\n :avatar\n )", array(':group_id' => $userData->group_id, ':email' => $userData->email, ':login' => $userData->login, ':password' => $userData->password, ':time_zone' => $userData->time_zone, ':status' => $userData->status, ':activation_hash' => $userData->activation_hash, ':avatar' => $userData->avatar));
return $conn->lastInsertId();
}
/**
* close
*
* Close storage resource.
* Nothing here, compatible only
*
* @return bool Status
*/
public function close()
{
DBI::getConnection('master')->commit();
return true;
}
/**
* getAllTopicsCount
*
* Will return number of all available topics
*
* @return int Number of all available topics
*/
public static function getAllTopicsCount()
{
return (int) \DBI::getConnection('slave')->sendQuery('SELECT SUM(topics_count) cnt FROM forum_subforums_stat')->fetch(\PDO::FETCH_COLUMN);
}
/**
* _initPermissions
*
* Set member role permissions with group_id
*
* @return null
*/
private static function _initPermissions()
{
$conn = DBI::getConnection('slave');
$permissions = $conn->sendQuery('SELECT
p.name
FROM groups_permissions gp
INNER JOIN permissions p
ON p.id = gp.permission_id
WHERE gp.group_id = :group_id', array(':group_id' => self::$_profile->group_id))->fetch(PDO::FETCH_OBJ);
if ($permissions) {
self::$_permissions = $permissions;
}
}
