public function save()
{
if (!$this->validate()) {
return false;
}
$toAdd = [];
$toDelete = [];
CW::$app->db->beginTransaction();
foreach (!is_array($this->categories) ? [] : $this->categories as $category) {
if (!in_array($category, $this->userCategories)) {
$toAdd[] = $category;
}
}
foreach ($this->userCategories as $category) {
if (!in_array($category, $this->categories)) {
$toDelete[] = $category;
}
}
if (0 < count($toAdd)) {
$query = sprintf("INSERT INTO `user_categories` (`user_id`, `category_id`) VALUES %s", ArrayHelper::getArrayToString($toAdd, ',', function ($v) {
return "({$this->userId}, {$v})";
}));
CW::$app->db->executeUpdate($query);
}
if (0 < count($toDelete)) {
$query = sprintf("DELETE FROM `user_categories` WHERE `user_id` = %d AND `category_id` IN (%s)", $this->userId, ArrayHelper::getArrayToString($toDelete, ','));
CW::$app->db->executeUpdate($query);
}
$stmt = CW::$app->db->prepare('UPDATE `users` SET `username` = :username, `description` = :description WHERE `id` = :userId');
$success = $stmt->execute([':username' => $this->username, ':description' => $this->description, ':userId' => $this->userId]);
CW::$app->db->commit();
return $success;
}
public function doIndex()
{
$updates = [];
$category = CW::$app->request->get('category');
if (null !== $category && !in_array($category, ArrayHelper::getKeyArray($this->view->categories, 'name'))) {
throw new \components\exceptions\NotFoundException();
}
return $this->render('index', ['updates' => $updates, 'category' => $category]);
}
public function getCategories()
{
if (null !== $this->categories) {
return $this->categories;
}
if (null === $this->id) {
return null;
}
$stmt = \CW::$app->db->executeQuery('SELECT `category_id` FROM `user_categories` WHERE `user_id` = ' . $this->id);
$result = $stmt->fetchAll(\PDO::FETCH_NUM);
return \components\helpers\ArrayHelper::column($result, 'category_id');
}
/**
*
* @param type $relIds
* @param type $relTypes
* @return type
*/
public static function getImages($relIds, $relTypes = null, $type = null)
{
$relTypes = empty($relTypes) ? "" : (is_array($relTypes) ? sprintf("IN (%s)", ArrayHelper::getArrayToString($relTypes, ',')) : " = '{$relTypes}'");
$relIds = is_array($relIds) ? sprintf("IN (%s)", ArrayHelper::getArrayToString($relIds, ',')) : " = {$relIds}";
$query = "SELECT * FROM images WHERE rel_type " . ('' === $relTypes ? '' : "{$relTypes} AND") . " rel_id {$relIds}" . (in_array($type, [static::TYPE_HIGH_IMAGE, static::TYPE_IMAGE, static::TYPE_PROFILE_PIC]) ? " AND `type` = '{$type}'" : '');
$stmt = CW::$app->db->executeQuery($query);
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$r = [];
foreach ($result as $item) {
$r[$item['rel_id']] = $item;
}
return $r;
}
/**
* Gets the ids of all tags that match the given term.
* @param string $term the search term.
* @return array the ids of the found tags.
*/
private static function getTagIds($term)
{
$tags = preg_split('/\\s+/', $term);
$tagsC = count($tags);
if (0 === $tagsC) {
return [];
}
$execParams = [];
for ($i = 0; $i < $tagsC; ++$i) {
$execParams[":tag{$i}"] = "{$tags[$i]}%";
}
$query = "SELECT id FROM tags WHERE " . ArrayHelper::getArrayToString($execParams, ' OR ', function ($v, $k) {
return "name LIKE {$k}";
});
$stmt = CW::$app->db->prepare($query);
$stmt->execute($execParams);
return ArrayHelper::getKeyArray($stmt->fetchAll(\PDO::FETCH_ASSOC), 'id');
}
/**
*
* @param type $updateIds
* @return type
*/
public static function getUpdatesTags($updateIds)
{
if (is_numeric($updateIds)) {
return self::getUpdateTags($updateIds);
} else {
if (empty($updateIds) || !is_array($updateIds)) {
return [];
}
}
$stmt = CW::$app->db->executeQuery("SELECT t.name, t.id, ut.update_id FROM tags t JOIN update_tags ut ON t.id = ut.tag_id WHERE ut.update_id IN (" . ArrayHelper::getArrayToString($updateIds, ',') . ")");
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$r = [];
foreach ($result as $item) {
$item['url'] = UrlManager::to(['site/search', 'term' => $item['name']]);
$item['name'] = htmlspecialchars($item['name']);
if (!isset($r[$item['update_id']])) {
$r[$item['update_id']] = [$item];
} else {
$r[$item['update_id']][] = $item;
}
}
return $r;
}
/**
*
* @param array $updates
* @return type
*/
public static function setUpdatePostedFrom(&$updates)
{
if (!is_array($updates) || 0 === count($updates)) {
return;
}
$users = [];
foreach ($updates as &$update) {
if (!isset($users[$update['user_id']])) {
$users[$update['user_id']] = [];
}
$users[$update['user_id']][] =& $update;
}
$userIds = ArrayHelper::keyArray($users);
if (0 === count($userIds)) {
return;
}
$stmt = \CW::$app->db->executeQuery('SELECT id, username, profile_img_id FROM users WHERE id IN (' . ArrayHelper::getArrayToString($userIds, ',') . ')');
$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($result as $user) {
$user['imgUrl'] = \models\User::getProfilePictureUrl($user['profile_img_id'], $user['id']);
$user['username'] = htmlspecialchars($user['username']);
$user['profileUrl'] = \models\User::getProfileUrl($user['id']);
foreach ($users[$user['id']] as &$update) {
$update['from'] = $user;
}
}
}
?>
,
ajaxData : {
category : App.update.category,
type : <?php
echo json_encode($type);
?>
}
});
updateLoader.load();
});
</script>
<?php
if (!empty($category) && in_array($category, ArrayHelper::getKeyArray($this->categories, 'name'))) {
?>
<div class="category-info-cont-1">
<div class="page-no-popular category-info-cont-2">
<h2 class="category-section-title">
<?php
echo htmlspecialchars($category);
?>
</h2>
<div class="category-types">
<a class="updates-type <?php
echo \models\Update::TYPE_TRENDING === $type ? 'updates-type-selected' : '';
?>
" href="<?php
echo UrlManager::to(['site/index', 'type' => 'trending', 'category' => $category]);
?>
<link href="/css/app.css" rel="stylesheet" type="text/css">
<link rel="shortcut icon" href="/images/logo.ico">
<title><?php
echo $view->title;
?>
</title>
<?php
foreach ($view->getLinks() as $link) {
echo '<link ' . ArrayHelper::getArrayToString($link, ' ', function ($v, $k) {
return "\"{$k}\"=\"{$v}\"";
}) . "></link>\n";
}
foreach ($view->getMetaTags() as $meta) {
echo '<meta ' . ArrayHelper::getArrayToString($meta, ' ', function ($v, $k) {
return "\"{$k}\"=\"{$v}\"";
}) . "></meta>\n";
}
?>
<script>
function sAjax(ajaxData, hasCsrf) {
hasCsrf = undefined === hasCsrf ? true : hasCsrf;
if (hasCsrf) {
ajaxData.data._csrf = '<?php
echo $csrfHash;
?>
';
}
return $.ajax(ajaxData);
private function addTags()
{
if (!empty($this->tags)) {
$tagsC = count($this->tags);
$keys = [];
$vals = [];
$tags = [];
for ($i = 0; $i < $tagsC; $i++) {
$this->tags[$i] = strtolower(trim($this->tags[$i]));
$keys[] = ":tag{$i}";
$vals[":tag{$i}"] = $this->tags[$i];
$tags[$this->tags[$i]] = $this->tags[$i];
}
$stmt = CW::$app->db->prepare('SELECT name FROM tags WHERE name IN (' . ArrayHelper::getArrayToString($keys, ',') . ')');
$stmt->execute($vals);
$tagsResult = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($tagsResult as $tag) {
if (isset($tags[$tag['name']])) {
unset($tags[$tag['name']]);
}
}
$tagsToAddCount = count($tags);
if (0 < $tagsToAddCount) {
$keys1 = [];
$vals1 = [];
$i = 0;
foreach ($tags as $tag) {
$keys1[] = ":tag{$i}";
$vals1[":tag{$i}"] = $tag;
$i++;
}
$query = 'INSERT INTO tags (name, created_at) VALUES ' . ArrayHelper::getArrayToString($keys1, ',', function ($v) {
return "({$v}, " . time() . ")";
});
$stmt = CW::$app->db->prepare($query);
$stmt->execute($vals1);
}
$stmt = CW::$app->db->prepare('SELECT id FROM tags WHERE name IN (' . ArrayHelper::getArrayToString($keys, ',') . ')');
$stmt->execute($vals);
$tagsResult = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$query = 'INSERT INTO update_tags (tag_id, update_id) VALUES ' . ArrayHelper::getArrayToString(ArrayHelper::getKeyArray($tagsResult, 'id'), ',', function ($v) {
return "({$v}, {$this->newUpdateId})";
});
$stmt = CW::$app->db->prepare($query);
$stmt->execute($vals);
}
}
public static function getComments($updateId, $page = null)
{
if (!is_numeric($updateId)) {
return [];
}
$query = "SELECT *, " . (CW::$app->user->isLogged() ? "0 < (SELECT count(*) FROM `comment_upvoters` WHERE `comment_id` = `comments`.`id` AND `user_id` = " . CW::$app->user->identity->id . ") " : ' false ') . " `voted`, null ownerId, null ownerUsername from `comments` WHERE `update_id` = {$updateId} AND `reply_to` IS NULL ORDER BY `rate` DESC, `posted_on` ASC LIMIT " . self::COMMENT_LOAD_COUNT . " OFFSET " . ($page ? $page * self::COMMENT_LOAD_COUNT : 0);
$stmt = \CW::$app->db->executeQuery($query);
$result = $stmt->fetchAll(\PDO::FETCH_OBJ);
$commentsCount = count($result);
$_comments = [];
$_topLevelComments = [];
$_replies = [];
for ($i = 0; $i < $commentsCount; $i++) {
if (!isset($_comments[$result[$i]->user_id])) {
$_comments[$result[$i]->user_id] = [];
}
$_comments[$result[$i]->user_id][] = $result[$i];
$query = "SELECT *, " . (CW::$app->user->isLogged() ? "0 < (SELECT count(*) FROM `comment_upvoters` WHERE `comment_id` = `comments`.`id` AND `user_id` = " . CW::$app->user->identity->id . ") " : ' false ') . " `voted` from `comments` WHERE `reply_to` = {$result[$i]->id} ORDER BY `posted_on` ASC LIMIT 1";
$stmt1 = \CW::$app->db->executeQuery($query);
$_topLevelComments[$result[$i]->id] = $result[$i];
$replies = $stmt1->fetchAll(\PDO::FETCH_OBJ);
$result[$i]->repliesCount = count($replies);
$result[$i]->owner = ['id' => $result[$i]->ownerId, 'username' => $result[$i]->ownerUsername, 'profileUrl' => \models\User::getProfileUrl($result[$i]->ownerId)];
$result[$i]->content = htmlspecialchars($result[$i]->content);
$result[$i]->postedAgo = BaseModel::getPostedAgoTime($result[$i]->posted_on);
$result[$i]->voted = (bool) $result[$i]->voted;
$repliesCount = count($replies);
for ($j = 0; $j < $repliesCount; $j++) {
if (!isset($_comments[$replies[$j]->user_id])) {
$_comments[$replies[$j]->user_id] = [];
}
$_replies[$replies[$j]->reply_to] = $replies[$j]->id;
$_comments[$replies[$j]->user_id][] = $replies[$j];
$replies[$j]->content = htmlspecialchars($replies[$j]->content);
$replies[$j]->postedAgo = BaseModel::getPostedAgoTime($replies[$j]->posted_on);
$replies[$j]->voted = (bool) $replies[$j]->voted;
}
$result[$i]->replies = $replies;
}
if (0 < count($_comments)) {
$userIds = ArrayHelper::keyArray($_comments);
$query = 'SELECT `id`, `username`, `profile_img_id` FROM `users` WHERE `id` IN (' . ArrayHelper::getArrayToString($userIds, ',') . ')';
$stmt = \CW::$app->db->executeQuery($query);
$users = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$usersCount = count($users);
for ($i = 0; $i < $usersCount; $i++) {
$userComments = $_comments[$users[$i]['id']];
$c = count($userComments);
for ($j = 0; $j < $c; $j++) {
$commentJ = $userComments[$j];
$commentJ->owner = ['id' => $users[$i]['id'], 'username' => htmlspecialchars($users[$i]['username']), 'profileUrl' => \models\User::getProfileUrl($users[$i]['id']), 'pictureUrl' => User::getProfilePictureUrl($users[$i]['profile_img_id'], $users[$i]['id'])];
}
}
}
if (0 < count($_replies)) {
$a = [];
foreach ($_replies as $replyTo => $replyId) {
$__replies = $_topLevelComments[$replyTo]->replies;
$last = $__replies[count($__replies) - 1]->posted_on;
$a[] = "SELECT `reply_to` FROM `comments` WHERE `reply_to` = {$replyTo} AND `posted_on` > '{$last}' LIMIT 1";
}
$q = ArrayHelper::getArrayToString($a, ' UNION ', function ($v) {
return "({$v})";
});
$stmt = CW::$app->db->executeQuery($q);
$_result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($_result as $replyTo) {
$_topLevelComments[$replyTo['reply_to']]->hasMore = true;
}
}
if ($commentsCount === self::COMMENT_LOAD_COUNT) {
$qq = "SELECT `id`, update_id, content FROM `comments` WHERE `update_id` = {$updateId} AND `reply_to` IS NULL ORDER BY `rate` DESC, `posted_on` DESC LIMIT 10 OFFSET " . ($page ? $page * self::COMMENT_LOAD_COUNT + self::COMMENT_LOAD_COUNT : self::COMMENT_LOAD_COUNT);
$stmt = CW::$app->db->executeQuery($qq);
$_result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$hasMore = 0 < count($_result);
} else {
$hasMore = false;
}
return ['items' => $result, 'hasMore' => $hasMore];
}
public function actionInit()
{
$tableOpts = 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci';
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `categories` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` INT NOT NULL,
`position` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) {$tableOpts}
QUERY
);
$categories = ['satisfying', 'calming', 'girls', 'funny', 'motivationg', 'inspiring', 'meme', 'geeky', 'animals', 'food', 'technology', 'travel', 'sports', 'vehicles', 'people'];
$pos = 1;
$insertQuery = sprintf("INSERT INTO `categories` (`name`, `position`, `created_at`) VALUES %s", \components\helpers\ArrayHelper::getArrayToString($categories, ', ', function ($v) use(&$pos) {
return sprintf("('%s', %d, %d)", $v, $pos++, time());
}));
\CW::$app->db->executeUpdate($insertQuery);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`has_profile_pic` bit(1) DEFAULT b'0',
`profile_img_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `updates` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`comments` int(11) NOT NULL DEFAULT '0',
`upvotes` int(11) NOT NULL DEFAULT '0',
`rate` double NOT NULL DEFAULT '0',
`is_gif` BIT DEFAULT 0,
`created_at` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `updates_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `comments` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`update_id` bigint(20) NOT NULL,
`reply_to` bigint(20) DEFAULT NULL,
`content` text COLLATE utf8_unicode_ci NOT NULL,
`upvotes` int(11) NOT NULL DEFAULT '0',
`rate` int(11) NOT NULL DEFAULT '0',
`posted_on` INT NOT NULL,
`user_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `update_id` (`update_id`),
KEY `reply_to` (`reply_to`),
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`update_id`) REFERENCES `updates` (`id`),
CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`reply_to`) REFERENCES `comments` (`id`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `comment_upvoters` (
`user_id` bigint(20) NOT NULL,
`comment_id` bigint(20) NOT NULL,
`voted_at` INT NOT NULL,
UNIQUE KEY `user_id` (`user_id`,`comment_id`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `images` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`rel_id` bigint(20) NOT NULL,
`rel_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`image_type` tinyint(4) NOT NULL,
`type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`is_deleted` bit(1) NOT NULL DEFAULT b'0',
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix-rel_id-rel_type` (`rel_id`,`rel_type`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `remember_user` (
`user_id` bigint(20) NOT NULL,
`uuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
KEY `user_id` (`user_id`),
CONSTRAINT `remember_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `tags` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`is_deleted` bit(1) NOT NULL DEFAULT b'0',
`created_at` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `update_categories` (
`update_id` bigint(20) NOT NULL,
`category_id` bigint(20) NOT NULL,
UNIQUE KEY `update_id` (`update_id`,`category_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `update_categories_ibfk_1` FOREIGN KEY (`update_id`) REFERENCES `updates` (`id`),
CONSTRAINT `update_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `update_tags` (
`update_id` bigint(20) NOT NULL,
`tag_id` bigint(20) NOT NULL,
UNIQUE KEY `update_id` (`update_id`,`tag_id`),
KEY `tag_id` (`tag_id`),
CONSTRAINT `update_tags_ibfk_1` FOREIGN KEY (`update_id`) REFERENCES `updates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `update_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `update_upvoters` (
`user_id` bigint(20) NOT NULL,
`update_id` bigint(20) NOT NULL,
`voted_at` INT NOT NULL,
UNIQUE KEY `user_id` (`user_id`,`update_id`),
KEY `update_id` (`update_id`),
CONSTRAINT `update_upvoters_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `update_upvoters_ibfk_2` FOREIGN KEY (`update_id`) REFERENCES `updates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `user_categories` (
`user_id` bigint(20) NOT NULL,
`category_id` bigint(20) NOT NULL,
UNIQUE KEY `user_id` (`user_id`,`category_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `user_categories_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
CONSTRAINT `user_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
) {$tableOpts}
QUERY
);
\CW::$app->db->executeUpdate(<<<QUERY
CREATE TABLE IF NOT EXISTS `user_update_activity` (
`user_id` bigint(20) NOT NULL,
`update_id` bigint(20) NOT NULL,
`time` INT NOT NULL,
`type_posted` bit(1) NOT NULL DEFAULT b'0',
`type_upvoted` bit(1) NOT NULL DEFAULT b'0',
`type_commented` bit(1) NOT NULL DEFAULT b'0',
UNIQUE KEY `user_id` (`user_id`,`update_id`),
KEY `update_id` (`update_id`),
CONSTRAINT `user_update_activity_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_update_activity_ibfk_2` FOREIGN KEY (`update_id`) REFERENCES `updates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) {$tableOpts}
QUERY
);
}
