mirror of
https://github.com/Sarjuuk/aowow.git
synced 2025-11-29 15:58:16 +08:00
a369244908
* split Screenshot upload & management into separate endpoints * move shared functions to manager classes * cleanup javascript * move test for config screenshot min size to cfg class
434 lines
16 KiB
PHP
434 lines
16 KiB
PHP
<?php
|
|
|
|
namespace Aowow;
|
|
|
|
if (!defined('AOWOW_REVISION'))
|
|
die('illegal access');
|
|
|
|
|
|
/************
|
|
* get Community Content
|
|
************/
|
|
|
|
/*
|
|
{id:115,user:'Ciderhelm',date:'2010/05/10 19:14:18',caption:'TankSpot\'s Guide to the Fury Warrior (Part 1)',videoType:1,videoId:'VUvxFvVmttg',type:13,typeId:1},
|
|
{id:116,user:'Ciderhelm',date:'2010/05/10 19:14:18',caption:'TankSpot\'s Guide to the Fury Warrior (Part 2)',videoType:1,videoId:'VEfnuIcq7n8',type:13,typeId:1},
|
|
{id:117,user:'Ciderhelm',date:'2010/05/10 19:14:18',caption:'TankSpot\'s Protection Warrior Guide',videoType:1,videoId:'vF-7kmvJZXY',type:13,typeId:1,sticky:1}
|
|
*/
|
|
|
|
/* todo: administration of content */
|
|
|
|
class CommunityContent
|
|
{
|
|
public const /* int */ COMMENT_LENGTH_MIN = 10;
|
|
public const /* int */ COMMENT_LENGTH_MAX = 7500;
|
|
public const /* int */ REPLY_LENGTH_MIN = 15;
|
|
public const /* int */ REPLY_LENGTH_MAX = 600;
|
|
|
|
public const /* int */ REPORT_THRESHOLD_AUTO_DELETE = 10;
|
|
public const /* int */ REPORT_THRESHOLD_AUTO_OUT_OF_DATE = 5;
|
|
|
|
private static array $jsGlobals = [];
|
|
private static array $subjCache = [];
|
|
|
|
private static string $coCountQuery =
|
|
'SELECT COUNT(1)
|
|
FROM ?_comments c
|
|
WHERE c.`replyTo` = ?d AND c.`type` = ?d AND c.`typeId` = ?d AND
|
|
((c.`flags` & ?d) = 0 OR c.`userId` = ?d OR ?d)';
|
|
|
|
private static string $coQuery =
|
|
'SELECT c.*,
|
|
a1.`username` AS "user",
|
|
a2.`username` AS "editUser",
|
|
a3.`username` AS "deleteUser",
|
|
a4.`username` AS "responseUser",
|
|
IFNULL(SUM(ur.`value`), 0) AS "rating",
|
|
SUM(IF(ur.`userId` > 0 AND ur.`userId` = ?d, ur.`value`, 0)) AS "userRating",
|
|
IF(r.`id` IS NULL, 0, 1) AS "userReported"
|
|
FROM ?_comments c
|
|
JOIN ?_account a1 ON c.`userId` = a1.`id`
|
|
LEFT JOIN ?_account a2 ON c.`editUserId` = a2.`id`
|
|
LEFT JOIN ?_account a3 ON c.`deleteUserId` = a3.`id`
|
|
LEFT JOIN ?_account a4 ON c.`responseUserId` = a4.`id`
|
|
LEFT JOIN ?_user_ratings ur ON c.`id` = ur.`entry` AND ur.`type` = ?d
|
|
LEFT JOIN ?_reports r ON r.`subject` = c.`id` AND r.`mode` = ?d AND r.`userId` = ?d
|
|
WHERE c.`replyTo` = ?d AND c.`type` = ?d AND c.`typeId` = ?d AND
|
|
((c.`flags` & ?d) = 0 OR c.`userId` = ?d OR ?d)
|
|
GROUP BY c.`id`
|
|
ORDER BY c.`date` ASC';
|
|
|
|
private static string $ssQuery =
|
|
'SELECT s.`id` AS ARRAY_KEY, s.`id`, a.`username` AS "user", s.`date`, s.`width`, s.`height`, s.`caption`, IF(s.`status` & ?d, 1, 0) AS "sticky", s.`type`, s.`typeId`
|
|
FROM ?_screenshots s
|
|
LEFT JOIN ?_account a ON s.`userIdOwner` = a.`id`
|
|
WHERE { s.`userIdOwner` = ?d AND }{ s.`type` = ? AND }{ s.`typeId` = ? AND } s.`status` & ?d AND (s.`status` & ?d) = 0
|
|
{ ORDER BY ?# DESC }
|
|
{ LIMIT ?d }';
|
|
|
|
private static string $viQuery =
|
|
'SELECT v.`id` AS ARRAY_KEY, v.`id`, a.`username` AS "user", v.`date`, v.`videoId`, v.`caption`, IF(v.`status` & ?d, 1, 0) AS "sticky", v.`type`, v.`typeId`
|
|
FROM ?_videos v
|
|
LEFT JOIN ?_account a ON v.`userIdOwner` = a.`id`
|
|
WHERE { v.`userIdOwner` = ?d AND }{ v.`type` = ? AND }{ v.`typeId` = ? AND } v.`status` & ?d AND (v.`status` & ?d) = 0
|
|
{ ORDER BY ?# ASC }
|
|
{ LIMIT ?d }';
|
|
|
|
private static string $previewQuery =
|
|
'SELECT c.`id`,
|
|
c.`body` AS "preview",
|
|
c.`date`,
|
|
c.`replyTo` AS "commentid",
|
|
IF(c.`flags` & ?d, 1, 0) AS "deleted",
|
|
IF(c.`type` <> 0, c.`type`, c2.`type`) AS "type",
|
|
IF(c.`typeId` <> 0, c.`typeId`, c2.`typeId`) AS "typeId",
|
|
IFNULL(SUM(ur.`value`), 0) AS "rating",
|
|
a.`username` AS "user"
|
|
FROM ?_comments c
|
|
JOIN ?_account a ON c.`userId` = a.`id`
|
|
LEFT JOIN ?_user_ratings ur ON ur.`entry` = c.`id` AND ur.`userId` <> 0 AND ur.`type` = 1
|
|
LEFT JOIN ?_comments c2 ON c.`replyTo` = c2.`id`
|
|
WHERE %s
|
|
((c.`flags` & ?d) = 0 OR c.`userId` = ?d OR ?d)
|
|
GROUP BY c.`id`
|
|
ORDER BY c.`date` DESC
|
|
{ LIMIT ?d }';
|
|
|
|
private static function addSubject(int $type, int $typeId) : void
|
|
{
|
|
if (!isset(self::$subjCache[$type][$typeId]))
|
|
self::$subjCache[$type][$typeId] = 0;
|
|
}
|
|
|
|
private static function getSubjects() : void
|
|
{
|
|
foreach (self::$subjCache as $type => $ids)
|
|
{
|
|
$_ = array_filter(array_keys($ids), 'is_numeric');
|
|
if (!$_)
|
|
continue;
|
|
|
|
$obj = Type::newList($type, [Cfg::get('SQL_LIMIT_NONE'), ['id', $_]]);
|
|
if (!$obj)
|
|
continue;
|
|
|
|
foreach ($obj->iterate() as $id => $__)
|
|
self::$subjCache[$type][$id] = $obj->getField('name', true, true);
|
|
}
|
|
}
|
|
|
|
public static function getCommentPreviews(array $opt = [], ?int &$nFound = 0, bool $dateFmt = true) : array
|
|
{
|
|
/*
|
|
purged:0, <- doesnt seem to be used anymore
|
|
domain:'live' <- irrelevant for our case
|
|
*/
|
|
|
|
// add default values
|
|
$opt += ['user' => 0, 'unrated' => 0, 'comments' => 0, 'replies' => 0, 'flags' => 0];
|
|
|
|
$w = [];
|
|
if ($opt['user'])
|
|
$w[] = sprintf('c.`userId` = %d AND', $opt['user']);
|
|
if ($opt['unrated'])
|
|
$w[] = 'ur.`entry` IS NULL AND';
|
|
if ($opt['flags'])
|
|
$w[] = sprintf('(c.`flags` & %d) > 0 AND', $opt['flags']);
|
|
if ($opt['comments'] && !$opt['replies'])
|
|
$w[] = 'c.`replyTo` = 0 AND';
|
|
else if (!$opt['comments'] && $opt['replies'])
|
|
$w[] = 'c.`replyTo` <> 0 AND';
|
|
// else
|
|
// pick both and no extra constraint needed for that
|
|
|
|
$query = sprintf(self::$previewQuery, implode(' ', $w));
|
|
|
|
$comments = DB::Aowow()->select(
|
|
$query,
|
|
CC_FLAG_DELETED,
|
|
CC_FLAG_DELETED,
|
|
User::$id,
|
|
User::isInGroup(U_GROUP_COMMENTS_MODERATOR),
|
|
Cfg::get('SQL_LIMIT_DEFAULT')
|
|
);
|
|
|
|
if (!$comments)
|
|
return [];
|
|
|
|
$nFound = DB::Aowow()->selectCell(
|
|
substr_replace($query, 'SELECT COUNT(*) ', 0, strpos($query, 'FROM')),
|
|
CC_FLAG_DELETED,
|
|
User::$id,
|
|
User::isInGroup(U_GROUP_COMMENTS_MODERATOR),
|
|
DBSIMPLE_SKIP
|
|
);
|
|
|
|
foreach ($comments as $c)
|
|
self::addSubject($c['type'], $c['typeId']);
|
|
|
|
self::getSubjects();
|
|
|
|
foreach ($comments as $idx => &$c)
|
|
{
|
|
if (!empty(self::$subjCache[$c['type']][$c['typeId']]))
|
|
{
|
|
// apply subject
|
|
$c['subject'] = self::$subjCache[$c['type']][$c['typeId']];
|
|
|
|
// format date
|
|
$c['elapsed'] = time() - $c['date'];
|
|
$c['date'] = $dateFmt ? date(Util::$dateFormatInternal, $c['date']) : intVal($c['date']);
|
|
|
|
// remove commentid if not looking for replies
|
|
if (empty($opt['replies']))
|
|
unset($c['commentid']);
|
|
|
|
// format text for listview
|
|
$c['preview'] = Lang::trimTextClean($c['preview']);
|
|
}
|
|
else
|
|
{
|
|
trigger_error('Comment '.$c['id'].' belongs to nonexistent subject.', E_USER_NOTICE);
|
|
unset($comments[$idx]);
|
|
}
|
|
}
|
|
|
|
return array_values($comments);
|
|
}
|
|
|
|
public static function getCommentReplies(int $commentId, int $limit = 0, ?int &$nFound = 0) : array
|
|
{
|
|
$replies = [];
|
|
$query = $limit > 0 ? self::$coQuery.' LIMIT '.$limit : self::$coQuery;
|
|
|
|
// get replies
|
|
if ($results = DB::Aowow()->select($query, User::$id, RATING_COMMENT, Report::MODE_COMMENT, User::$id, $commentId, 0, 0, CC_FLAG_DELETED, User::$id, User::isInGroup(U_GROUP_COMMENTS_MODERATOR)))
|
|
{
|
|
$nFound = DB::Aowow()->selectCell(self::$coCountQuery, $commentId, 0, 0, CC_FLAG_DELETED, User::$id, User::isInGroup(U_GROUP_COMMENTS_MODERATOR));
|
|
|
|
foreach ($results as $r)
|
|
{
|
|
Markup::parseTags($r['body'], self::$jsGlobals);
|
|
|
|
$reply = array(
|
|
'commentid' => $commentId,
|
|
'id' => $r['id'],
|
|
'body' => $r['body'],
|
|
'username' => $r['user'],
|
|
'roles' => $r['roles'],
|
|
'creationdate' => date(Util::$dateFormatInternal, $r['date']),
|
|
'lasteditdate' => date(Util::$dateFormatInternal, $r['editDate']),
|
|
'rating' => (string)$r['rating']
|
|
);
|
|
|
|
if ($r['userReported'])
|
|
$reply['reportedByUser'] = true;
|
|
|
|
if ($r['userRating'] > 0)
|
|
$reply['votedByUser'] = true;
|
|
else if ($r['userRating'] < 0)
|
|
$reply['downvotedByUser'] = true;
|
|
|
|
$replies[] = $reply;
|
|
}
|
|
}
|
|
|
|
return $replies;
|
|
}
|
|
|
|
public static function getComments(int $type, int $typeId) : array
|
|
{
|
|
|
|
$results = DB::Aowow()->query(self::$coQuery, User::$id, RATING_COMMENT, Report::MODE_COMMENT, User::$id, 0, $type, $typeId, CC_FLAG_DELETED, User::$id, (int)User::isInGroup(U_GROUP_COMMENTS_MODERATOR));
|
|
$comments = [];
|
|
|
|
// additional informations
|
|
$i = 0;
|
|
foreach ($results as $r)
|
|
{
|
|
Markup::parseTags($r['body'], self::$jsGlobals);
|
|
|
|
self::$jsGlobals[Type::USER][$r['userId']] = $r['userId'];
|
|
|
|
$c = array(
|
|
'commentv2' => 1, // always 1.. enables some features i guess..?
|
|
'number' => $i++, // some iterator .. unsued?
|
|
'id' => $r['id'],
|
|
'date' => date(Util::$dateFormatInternal, $r['date']),
|
|
'roles' => $r['roles'],
|
|
'body' => $r['body'],
|
|
'rating' => $r['rating'],
|
|
'userRating' => $r['userRating'],
|
|
'user' => $r['user'],
|
|
'nreplies' => 0
|
|
);
|
|
|
|
$c['replies'] = self::getCommentReplies($r['id'], 5, $c['nreplies']);
|
|
|
|
if ($r['responseBody']) // adminResponse
|
|
{
|
|
$c['response'] = $r['responseBody'];
|
|
$c['responseroles'] = $r['responseRoles'];
|
|
$c['responseuser'] = $r['responseUser'];
|
|
|
|
Markup::parseTags($r['responseBody'], self::$jsGlobals);
|
|
}
|
|
|
|
if ($r['editCount']) // lastEdit
|
|
$c['lastEdit'] = [date(Util::$dateFormatInternal, $r['editDate']), $r['editCount'], $r['editUser']];
|
|
|
|
if ($r['flags'] & CC_FLAG_STICKY)
|
|
$c['sticky'] = true;
|
|
|
|
if ($r['flags'] & CC_FLAG_DELETED)
|
|
{
|
|
$c['deleted'] = true;
|
|
$c['deletedInfo'] = [date(Util::$dateFormatInternal, $r['deleteDate']), $r['deleteUser']];
|
|
}
|
|
|
|
if ($r['flags'] & CC_FLAG_OUTDATED)
|
|
$c['outofdate'] = true;
|
|
|
|
$comments[] = $c;
|
|
}
|
|
|
|
return $comments;
|
|
}
|
|
|
|
public static function getVideos(int $typeOrUser = 0, int $typeId = 0, ?int &$nFound = 0, bool $dateFmt = true) : array
|
|
{
|
|
$videos = DB::Aowow()->select(self::$viQuery,
|
|
CC_FLAG_STICKY,
|
|
$typeOrUser < 0 ? -$typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeId : DBSIMPLE_SKIP,
|
|
CC_FLAG_APPROVED,
|
|
CC_FLAG_DELETED,
|
|
!$typeOrUser ? 'date' : 'pos',
|
|
!$typeOrUser ? Cfg::get('SQL_LIMIT_SEARCH') : DBSIMPLE_SKIP
|
|
);
|
|
|
|
if (!$videos)
|
|
return [];
|
|
|
|
$nFound = DB::Aowow()->selectCell(
|
|
substr_replace(self::$viQuery, 'SELECT COUNT(*) ', 0, strpos(self::$viQuery, 'FROM')),
|
|
$typeOrUser < 0 ? -$typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeId : DBSIMPLE_SKIP,
|
|
CC_FLAG_APPROVED,
|
|
CC_FLAG_DELETED,
|
|
!$typeOrUser ? 'date' : 'pos',
|
|
DBSIMPLE_SKIP
|
|
);
|
|
|
|
if ($typeOrUser <= 0) // not for search by type/typeId
|
|
{
|
|
foreach ($videos as $v)
|
|
self::addSubject($v['type'], $v['typeId']);
|
|
|
|
self::getSubjects();
|
|
}
|
|
|
|
// format data to meet requirements of the js
|
|
foreach ($videos as &$v)
|
|
{
|
|
if ($typeOrUser <= 0) // not for search by type/typeId
|
|
{
|
|
if (!empty(self::$subjCache[$v['type']][$v['typeId']]) && !is_numeric(self::$subjCache[$v['type']][$v['typeId']]))
|
|
$v['subject'] = self::$subjCache[$v['type']][$v['typeId']];
|
|
else
|
|
$v['subject'] = Lang::user('removed');
|
|
}
|
|
|
|
$v['date'] = $dateFmt ? date(Util::$dateFormatInternal, $v['date']) : intVal($v['date']);
|
|
$v['videoType'] = 1; // always youtube
|
|
|
|
if (!$v['sticky'])
|
|
unset($v['sticky']);
|
|
|
|
if (!$v['user'])
|
|
unset($v['user']);
|
|
}
|
|
|
|
return array_values($videos);
|
|
}
|
|
|
|
public static function getScreenshots(int $typeOrUser = 0, int $typeId = 0, ?int &$nFound = 0, bool $dateFmt = true) : array
|
|
{
|
|
$screenshots = DB::Aowow()->select(self::$ssQuery,
|
|
CC_FLAG_STICKY,
|
|
$typeOrUser < 0 ? -$typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeId : DBSIMPLE_SKIP,
|
|
CC_FLAG_APPROVED,
|
|
CC_FLAG_DELETED,
|
|
!$typeOrUser ? 'date' : DBSIMPLE_SKIP,
|
|
!$typeOrUser ? Cfg::get('SQL_LIMIT_SEARCH') : DBSIMPLE_SKIP
|
|
);
|
|
|
|
if (!$screenshots)
|
|
return [];
|
|
|
|
$nFound = DB::Aowow()->selectCell(
|
|
substr_replace(self::$ssQuery, 'SELECT COUNT(*) ', 0, strpos(self::$ssQuery, 'FROM')),
|
|
$typeOrUser < 0 ? -$typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeOrUser : DBSIMPLE_SKIP,
|
|
$typeOrUser > 0 ? $typeId : DBSIMPLE_SKIP,
|
|
CC_FLAG_APPROVED,
|
|
CC_FLAG_DELETED,
|
|
!$typeOrUser ? 'date' : DBSIMPLE_SKIP,
|
|
DBSIMPLE_SKIP
|
|
);
|
|
|
|
if ($typeOrUser <= 0) // not for search by type/typeId
|
|
{
|
|
foreach ($screenshots as $s)
|
|
self::addSubject($s['type'], $s['typeId']);
|
|
|
|
self::getSubjects();
|
|
}
|
|
|
|
// format data to meet requirements of the js
|
|
foreach ($screenshots as &$s)
|
|
{
|
|
if ($typeOrUser <= 0) // not for search by type/typeId
|
|
{
|
|
if (!empty(self::$subjCache[$s['type']][$s['typeId']]) && !is_numeric(self::$subjCache[$s['type']][$s['typeId']]))
|
|
$s['subject'] = self::$subjCache[$s['type']][$s['typeId']];
|
|
else
|
|
$s['subject'] = Lang::user('removed');
|
|
}
|
|
|
|
$s['date'] = $dateFmt ? date(Util::$dateFormatInternal, $s['date']) : intVal($s['date']);
|
|
|
|
if (!$s['sticky'])
|
|
unset($s['sticky']);
|
|
|
|
if (!$s['user'])
|
|
unset($s['user']);
|
|
}
|
|
|
|
return array_values($screenshots);
|
|
}
|
|
|
|
public static function getAll(int $type, int $typeId, array &$jsg) : array
|
|
{
|
|
$result = array(
|
|
'vi' => self::getVideos($type, $typeId),
|
|
'ss' => self::getScreenshots($type, $typeId),
|
|
'co' => self::getComments($type, $typeId)
|
|
);
|
|
|
|
Util::mergeJsGlobals($jsg, self::$jsGlobals);
|
|
|
|
return $result;
|
|
}
|
|
|
|
public static function getJSGlobals() : array
|
|
{
|
|
return self::$jsGlobals;
|
|
}
|
|
}
|
|
?>
|