123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668 |
- <?php
- /**
- * Insipid
- * Personal web-bookmark-system
- *
- * Copyright 2016-2019 Johannes Keßler
- *
- * Development starting from 2011: Johannes Keßler
- * https://www.bananas-playground.net/projekt/insipid/
- *
- * creator:
- * Luke Reeves <luke@neuro-tech.net>
- *
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published by
- * the Free Software Foundation, either version 3 of the License, or
- * (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program. If not, see http://www.gnu.org/licenses/gpl-3.0.
- *
- */
- class Management {
- const LINK_QUERY_STATUS = 2;
- const COMBINED_SELECT_VALUES = "any_value(`id`) as id,
- any_value(`link`) as link,
- any_value(`created`) as created,
- any_value(`status`) as `status`,
- any_value(`description`) as description,
- any_value(`title`) as title,
- any_value(`image`) as image,
- any_value(`hash`) as hash,
- any_value(`tag`) as tag,
- any_value(`category`) as category,
- any_value(`categoryId`) as categoryId,
- any_value(`tagId`) as tagId";
- /**
- * the database object
- * @var object
- */
- private $DB;
- /**
- * Type of links based on status to show
- * @var bool
- */
- private $_queryStatus = self::LINK_QUERY_STATUS;
- public function __construct($databaseConnectionObject) {
- $this->DB = $databaseConnectionObject;
- }
- /**
- * Show private links or not
- * @param $bool
- */
- public function setShowPrivate($bool) {
- $this->_queryStatus = self::LINK_QUERY_STATUS;
- if($bool === true) {
- $this->_queryStatus = 1;
- }
- }
- /**
- * Show awaiting moderation links or not
- * @param $bool
- */
- public function setShowAwm($bool) {
- $this->_queryStatus = self::LINK_QUERY_STATUS;
- if($bool === true) {
- $this->_queryStatus = 3;
- }
- }
- /**
- * get all the available categories from the DB.
- * optional limit
- * optional stats
- * @param bool | int $limit
- * @param bool $stats
- * @return array
- */
- public function categories($limit=false, $stats=false) {
- $ret = array();
- $statsInfo = array();
- if($stats === true) {
- $queryStr = "SELECT
- COUNT(*) AS amount,
- any_value(cr.categoryid) AS categoryId
- FROM `".DB_PREFIX."_categoryrelation` AS cr, `".DB_PREFIX."_link` AS t
- WHERE cr.linkid = t.id";
- $queryStr .= " AND ".$this->_decideLinkTypeForQuery();
- $queryStr .= " GROUP BY categoryid";
- $query = $this->DB->query($queryStr);
- if(!empty($query)) {
- while($result = $query->fetch_assoc()) {
- $statsInfo[$result['categoryId']] = $result['amount'];
- }
- }
- }
- $queryStr = "SELECT
- any_value(`id`) as id,
- any_value(`name`) as name
- FROM `".DB_PREFIX."_category`
- ORDER BY `name` ASC";
- if(!empty($limit)) {
- $queryStr .= " LIMIT $limit";
- }
- $query = $this->DB->query($queryStr);
- if(!empty($query)) {
- while($result = $query->fetch_assoc()) {
- if($stats === true && isset($statsInfo[$result['id']])) {
- $ret[$result['id']] = array('name' => $result['name'], 'amount' => $statsInfo[$result['id']]);
- }
- else {
- $ret[$result['id']] = array('name' => $result['name'], 'amount' => 0);
- }
- }
- }
- return $ret;
- }
- /**
- * get all the available tags from the DB.
- * optional limit
- * optional stats
- * @param bool | int $limit
- * @param bool $stats
- * @return array
- */
- public function tags($limit=false, $stats=false) {
- $ret = array();
- $statsInfo = array();
- if($stats === true) {
- $queryStr = "SELECT
- COUNT(*) AS amount,
- any_value(tr.tagid) AS tagId
- FROM `".DB_PREFIX."_tagrelation` AS tr, `".DB_PREFIX."_link` AS t
- WHERE tr.linkid = t.id";
- $queryStr .= " AND ".$this->_decideLinkTypeForQuery();
- $queryStr .= "GROUP BY tagId";
- $query = $this->DB->query($queryStr);
- if(!empty($query)) {
- while($result = $query->fetch_assoc()) {
- $statsInfo[$result['tagId']] = $result['amount'];
- }
- }
- }
- $queryStr = "SELECT
- any_value(`id`) as id,
- any_value(`name`) as name
- FROM `".DB_PREFIX."_tag`
- ORDER BY `name` ASC";
- if(!empty($limit)) {
- $queryStr .= " LIMIT $limit";
- }
- $query = $this->DB->query($queryStr);
- if(!empty($query)) {
- while($result = $query->fetch_assoc()) {
- if($stats === true && isset($statsInfo[$result['id']])) {
- $ret[$result['id']] = array('name' => $result['name'], 'amount' => $statsInfo[$result['id']]);
- }
- else {
- $ret[$result['id']] = array('name' => $result['name'], 'amount' => 0);
- }
- }
- }
- return $ret;
- }
- /**
- * return the latest added links
- * @param int $limit
- * @return array
- */
- public function latestLinks($limit=5) {
- $ret = array();
- $queryStr = "SELECT `title`, `link` FROM `".DB_PREFIX."_link` AS t";
- $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery();
- $queryStr .= " ORDER BY `created` DESC";
- if(!empty($limit)) {
- $queryStr .= " LIMIT $limit";
- }
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $ret = $query->fetch_all(MYSQLI_ASSOC);
- }
- return $ret;
- }
- /**
- * get all the categories ordered by link added date
- */
- public function categoriesByDateAdded() {
- $ret = array();
- $categories = $this->categories();
- foreach($categories as $k=>$v) {
- $latestLink = $this->latestLinkForCategory($k);
- if(!empty($latestLink)) {
- array_push($ret, array('created' => $latestLink[0]['created'], 'id' => $k, 'name' => $v['name']));
- }
- }
- $_created = array_column($ret, 'created');
- array_multisort($_created, SORT_DESC, $ret);
- return $ret;
- }
- /**
- * find all links by given category string or id.
- * Return array sorted by creation date DESC
- * @param int $id
- * @param string $string
- * @param int $limit
- * @param bool $offset
- * @return array
- */
- public function linksByCategory($id, $string, $limit=5, $offset=false) {
- $ret = array();
- $querySelect = "SELECT ".self::COMBINED_SELECT_VALUES;
- $queryFrom = " FROM `".DB_PREFIX."_combined` AS t";
- $queryWhere = " WHERE ".$this->_decideLinkTypeForQuery();
- if(!empty($id) && is_numeric($id)) {
- $queryWhere .= " AND t.categoryId = '" . $this->DB->real_escape_string($id) . "'";
- }
- elseif(!empty($string) && is_string($string)) {
- $queryWhere .= " AND t.category = '" . $this->DB->real_escape_string($string) . "'";
- }
- else {
- return $ret;
- }
- $queryOrder = "GROUP BY t.hash
- ORDER BY t.created DESC";
- $queryLimit = '';
- if(!empty($limit)) {
- $queryLimit .= " LIMIT $limit";
- if($offset !== false) {
- $queryLimit .= " OFFSET $offset";
- }
- }
- $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit);
- if(!empty($query) && $query->num_rows > 0) {
- while($result = $query->fetch_assoc()) {
- $linkObj = new Link($this->DB);
- $ret['results'][] = $linkObj->loadShortInfo($result['hash']);
- unset($linkObj);
- }
- $query = $this->DB->query("SELECT COUNT(DISTINCT(t.hash)) AS amount ".$queryFrom.$queryWhere);
- $result = $query->fetch_assoc();
- $ret['amount'] = $result['amount'];
- }
- return $ret;
- }
- /**
- * find all links by given tag string or id.
- * Return array sorted by creation date DESC
- * @param int $id
- * @param string $string
- * @param int $limit
- * @param bool $offset
- * @return array
- */
- public function linksByTag($id, $string, $limit=5, $offset=false) {
- $ret = array();
- $querySelect = "SELECT ".self::COMBINED_SELECT_VALUES;
- $queryFrom = " FROM `".DB_PREFIX."_combined` AS t";
- $queryWhere = " WHERE ".$this->_decideLinkTypeForQuery();
- if(!empty($id) && is_numeric($id)) {
- $queryWhere .= " AND t.tagId = '" . $this->DB->real_escape_string($id) . "'";
- }
- elseif(!empty($string) && is_string($string)) {
- $queryWhere .= " AND t.tag = '" . $this->DB->real_escape_string($string) . "'";
- }
- else {
- return $ret;
- }
- $queryOrder = "GROUP BY t.hash
- ORDER BY t.created DESC";
- $queryLimit = '';
- if(!empty($limit)) {
- $queryLimit .= " LIMIT $limit";
- if($offset !== false) {
- $queryLimit .= " OFFSET $offset";
- }
- }
- $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit);
- if(!empty($query) && $query->num_rows > 0) {
- while($result = $query->fetch_assoc()) {
- $linkObj = new Link($this->DB);
- $ret['results'][] = $linkObj->loadShortInfo($result['hash']);
- unset($linkObj);
- }
- $query = $this->DB->query("SELECT COUNT(DISTINCT(t.hash)) AS amount ".$queryFrom.$queryWhere);
- $result = $query->fetch_assoc();
- $ret['amount'] = $result['amount'];
- }
- return $ret;
- }
- /**
- * return all links and Info we have from the combined view
- * @param bool | int $limit
- * @param bool $offset
- * @return array
- */
- public function links($limit=10,$offset=false) {
- $ret = array();
- $querySelect = "SELECT `hash`";
- $queryFrom = " FROM `".DB_PREFIX."_link` AS t";
- $queryWhere = " WHERE ".$this->_decideLinkTypeForQuery();
- $queryOrder = " ORDER BY `created` DESC";
- $queryLimit = "";
- if(!empty($limit)) {
- $queryLimit = " LIMIT $limit";
- if($offset !== false) {
- $queryLimit .= " OFFSET $offset";
- }
- }
- $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit);
- if(!empty($query) && $query->num_rows > 0) {
- while($result = $query->fetch_assoc()) {
- $linkObj = new Link($this->DB);
- $ret['results'][] = $linkObj->loadShortInfo($result['hash']);
- unset($linkObj);
- }
- $query = $this->DB->query("SELECT COUNT(t.hash) AS amount ".$queryFrom.$queryWhere);
- $result = $query->fetch_assoc();
- $ret['amount'] = $result['amount'];
- }
- return $ret;
- }
- /**
- * return the latest added link for given category id
- * @param int $categoryid
- * @return array
- */
- public function latestLinkForCategory($categoryid) {
- $ret = array();
- if(!empty($categoryid) && is_numeric($categoryid)) {
- $queryStr = "SELECT ".self::COMBINED_SELECT_VALUES."
- FROM `".DB_PREFIX."_combined` AS t";
- $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery();
- $queryStr .= " AND t.categoryId = '" . $this->DB->real_escape_string($categoryid) . "'
- ORDER BY t.created DESC
- LIMIT 1";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $ret = $query->fetch_all(MYSQLI_ASSOC);
- }
- }
- return $ret;
- }
- /**
- * Search for the given url in the links table
- * @param $url
- * @return mixed
- */
- public function searchForLinkByURL($url) {
- $ret = false;
- if(!empty($url)) {
- $queryStr = "SELECT * FROM `".DB_PREFIX."_link` AS t";
- $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery();
- $queryStr .= " AND t.link = '".$this->DB->real_escape_string($url)."'";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $ret = $query->fetch_all(MYSQLI_ASSOC);
- }
- }
- return $ret;
- }
- /**
- * search for given searchstring in the search data of the links
- * @param $searchStr
- * @return mixed
- */
- public function searchForLinkBySearchData($searchStr) {
- $ret = false;
- if(!empty($searchStr)) {
- $queryStr = "SELECT *,
- MATCH (`search`) AGAINST ('".$this->DB->real_escape_string($searchStr)."' IN BOOLEAN MODE) AS score
- FROM `".DB_PREFIX."_link` AS t
- WHERE MATCH (`search`) AGAINST ('".$this->DB->real_escape_string($searchStr)."' IN BOOLEAN MODE)";
- $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery();
- $queryStr .= " ORDER BY score DESC";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $ret = $query->fetch_all(MYSQLI_ASSOC);
- }
- }
- return $ret;
- }
- /**
- * amount of links in the DB. Status 1 and 2 only
- * @return int
- */
- public function linkAmount() {
- $ret = 0;
- $queryStr = "SELECT COUNT(*) AS amount
- FROM `".DB_PREFIX."_link` AS t";
- $queryStr .= " WHERE ".$this->_decideLinkTypeForQuery();
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $result = $query->fetch_assoc();
- $ret = $result['amount'];
- }
- return $ret;
- }
- /**
- * amount of tags
- * @return int
- */
- public function tagAmount() {
- $ret = 0;
- $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_tag`";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $result = $query->fetch_assoc();
- $ret = $result['amount'];
- }
- return $ret;
- }
- /**
- * amount of categories
- * @return int
- */
- public function categoryAmount() {
- $ret = 0;
- $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_category`";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $result = $query->fetch_assoc();
- $ret = $result['amount'];
- }
- return $ret;
- }
- /**
- * Amount of links need moderation
- * @return int
- */
- public function moderationAmount() {
- $ret = 0;
- $queryStr = "SELECT COUNT(*) AS amount FROM `".DB_PREFIX."_link`";
- $queryStr .= " WHERE `status` = 3";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $result = $query->fetch_assoc();
- $ret = $result['amount'];
- }
- return $ret;
- }
- /**
- * get the used disk space for local image storage
- * @return false|int
- */
- public function storageAmount() {
- $ret = 0;
- $_storageFolder = ABSOLUTE_PATH.'/'.LOCAL_STORAGE;
- if(file_exists($_storageFolder) && is_readable($_storageFolder)) {
- $ret = Summoner::folderSize($_storageFolder);
- }
- return $ret;
- }
- /**
- * empties the local storage directory
- * @return bool
- */
- public function clearLocalStorage() {
- $ret = false;
- $_storageFolder = ABSOLUTE_PATH.'/'.LOCAL_STORAGE;
- if(file_exists($_storageFolder) && is_writable($_storageFolder)) {
- $ret = Summoner::recursive_remove_directory($_storageFolder,true);
- }
- return $ret;
- }
- /**
- * Load link by given hash. Do not use Link class directly.
- * Otherwise the authentication will be ignored.
- * @param $hash
- * @param bool $fullInfo
- * @param $withObject
- * @return array|mixed
- */
- public function loadLink($hash,$fullInfo=true,$withObject=false) {
- $ret = array();
- if (!empty($hash)) {
- $querySelect = "SELECT `hash`";
- $queryFrom = " FROM `".DB_PREFIX."_link` AS t";
- $queryWhere = " WHERE ".$this->_decideLinkTypeForQuery();
- $queryWhere .= " AND t.hash = '".$this->DB->real_escape_string($hash)."'";
- $query = $this->DB->query($querySelect.$queryFrom.$queryWhere);
- if (!empty($query) && $query->num_rows == 1) {
- $linkObj = new Link($this->DB);
- if($fullInfo === true) {
- $ret = $linkObj->load($hash);
- }
- else {
- $ret = $linkObj->loadShortInfo($hash);
- }
- if($withObject === true) {
- $ret = array(
- 'data' => $ret,
- 'obj' => $linkObj
- );
- }
- }
- }
- return $ret;
- }
- /**
- * Delete link by given hash
- * @param $hash
- * @return bool
- */
- public function deleteLink($hash) {
- $ret = false;
- if (!empty($hash)) {
- $linkData = $this->loadLink($hash,false,true);
- if(!empty($linkData)) {
- $linkData['obj']->deleteRelations();
- $queryStr = "DELETE FROM `" . DB_PREFIX . "_link`
- WHERE `hash` = '" . $this->DB->real_escape_string($hash) . "'";
- $query = $this->DB->query($queryStr);
- if (!empty($query)) {
- $ret = true;
- }
- }
- }
- return $ret;
- }
- /**
- * for simpler management we have the search data in a separate column
- * it is not fancy or even technical nice but it damn works
- */
- private function _updateSearchIndex() {
- $allLinks = array();
- $queryStr = "SELECT hash FROM `".DB_PREFIX."_link`";
- $query = $this->DB->query($queryStr);
- if(!empty($query) && $query->num_rows > 0) {
- $allLinks = $query->fetch_all(MYSQLI_ASSOC);
- }
- if(!empty($allLinks)) {
- foreach($allLinks as $link) {
- $LinkObj = new Link($this->DB);
- $l = $LinkObj->load($link['hash']);
- $searchStr = $l['title'];
- $searchStr .= ' '.$l['description'];
- foreach($l['tags'] as $t) {
- $searchStr .= ' '.$t['tag'];
- }
- foreach($l['categories'] as $c) {
- $searchStr .= ' '.$c['category'];
- }
- # now update the search string
- $queryStr = "UPDATE `".DB_PREFIX."_link`
- SET `search` = '".$this->DB->real_escape_string($searchStr)."'
- WHERE `hash` = '".$this->DB->real_escape_string($link['hash'])."'";
- $this->DB->query($queryStr);
- unset($LinkObj,$l,$searchStr,$t,$c,$queryStr);
- }
- }
- }
- /**
- * Return the query string for the correct status type
- * @return string
- */
- private function _decideLinkTypeForQuery() {
- switch ($this->_queryStatus) {
- case 1:
- $ret = "t.status IN (2,1)";
- break;
- case 3:
- $ret = "t.status = 3";
- break;
- default:
- $ret = "t.status = 2";
- }
- return $ret;
- }
- }
|