295 lines
13 KiB
PHP
295 lines
13 KiB
PHP
<?php
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
class ProjectsModel extends CI_Model
|
|
{
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->load->model('projectsModel', '', TRUE);
|
|
}
|
|
|
|
public function getEntries($category)
|
|
{
|
|
if ($category !== 'all') {
|
|
$content = $this->db->query('SELECT *, (SELECT (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = p.ID AND type = 1) - (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = p.ID AND type = -1)) voteCount FROM projects p WHERE ID IN (SELECT projectID FROM projects_entry_categories WHERE categoryID = ?) ORDER BY datetime DESC', [$category])->result_array();
|
|
} else {
|
|
$content = $this->db->query('SELECT *, (SELECT (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = p.ID AND type = 1) - (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = p.ID AND type = -1)) voteCount FROM projects p ORDER BY datetime DESC')->result_array();
|
|
}
|
|
|
|
$content = $this->mergeTranslationData($content, $_SESSION['site_lang']);
|
|
|
|
return $content;
|
|
}
|
|
|
|
public function mergeTranslationData($postList, $lang = 'de')
|
|
{
|
|
foreach ($postList as $i => $post) {
|
|
$data = $this->db->query('SELECT title, description, content, downloadName, openSourceName, customLinkName FROM projects_translations WHERE projectID = ? AND lang = ? ORDER BY lang', [$post['ID'], 'de'])->result_array();
|
|
|
|
if(empty($data)) {
|
|
$postList[$i] = array_merge($post, [
|
|
'title' => 'Not found',
|
|
'description' => 'Not found',
|
|
'content' => 'Not found',
|
|
]);
|
|
continue;
|
|
}
|
|
|
|
$data = $data[0];
|
|
if($lang == 'de') {
|
|
$postList[$i] = array_merge($post, $data);
|
|
continue;
|
|
}
|
|
|
|
$dataLang = $this->db->query('SELECT title, description, content, downloadName, openSourceName, customLinkName FROM projects_translations WHERE projectID = ? AND lang = ? ORDER BY lang', [$post['ID'], $lang])->result_array();
|
|
|
|
if (empty($dataLang)) {
|
|
$postList[$i] = array_merge($post, $data);
|
|
continue;
|
|
}
|
|
|
|
$dataLang = $dataLang[0];
|
|
|
|
$merged = [];
|
|
foreach ($data as $key => $value) {
|
|
if (($value == NULL && $dataLang[$key] == NULL) || ($value != NULL && $dataLang[$key] == NULL)) {
|
|
$merged[$key] = $value;
|
|
} else {
|
|
$merged[$key] = $dataLang[$key];
|
|
}
|
|
}
|
|
|
|
$postList[$i] = array_merge($post, $merged);
|
|
}
|
|
return $postList;
|
|
}
|
|
|
|
public function getCategories()
|
|
{
|
|
$collections = $this->db->query('SELECT c.*, count(p.projectID) count FROM projects_categories c LEFT JOIN projects_entry_categories p ON c.categoryID = p.categoryID GROUP BY c.categoryID ORDER BY c.name')->result_array();
|
|
|
|
if($_SESSION['site_lang'] !== 'de') {
|
|
$collectionsLang = $this->db->query('SELECT categoryID, name, displayname FROM projects_categories WHERE lang = ?', [$_SESSION['site_lang']])->result_array();
|
|
|
|
$categoryIDs = array_column($collections, 'categoryID');
|
|
foreach ($collectionsLang as $item) {
|
|
$key = array_search($item['categoryID'], $categoryIDs);
|
|
$collections[$key] = array_merge($collections[$key], $item);
|
|
}
|
|
}
|
|
|
|
return $collections;
|
|
}
|
|
|
|
public function getCategoriesRaw() {
|
|
$categories = $this->db->query('SELECT ID, GROUP_CONCAT(c.name) name, GROUP_CONCAT(c.displayname) displayname, (SELECT COUNT(*) FROM projects_entry_categories p WHERE c.categoryID = p.categoryID) count
|
|
FROM projects_categories c
|
|
GROUP BY c.categoryID
|
|
ORDER BY ID')->result_array();
|
|
return $categories;
|
|
}
|
|
|
|
public function editEntry($data, $id)
|
|
{
|
|
$this->db->update('projects', $data, ['id' => $id]);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function addEntry($data)
|
|
{
|
|
$this->db->insert('projects', $data);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function delete($id)
|
|
{
|
|
$this->db->query('DELETE FROM projects WHERE ID = ? LIMIT 1', [$id]);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function deleteCategory($id)
|
|
{
|
|
$this->db->query('DELETE FROM projects_entry_categories WHERE categoryID = ?', [$id]);
|
|
$this->db->query('DELETE FROM projects_categories WHERE ID = ? LIMIT 1', [$id]);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function checkIfExists($id)
|
|
{
|
|
$result = $this->db->query('SELECT ID FROM projects WHERE ID = ? LIMIT 1', [$id])->result_array();
|
|
if (!empty($result)) {
|
|
return true;
|
|
} else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function checkIfNameExists($name)
|
|
{
|
|
$result = $this->db->query('SELECT ID FROM projects WHERE name = ? LIMIT 1', [$name])->result_array();
|
|
if (!empty($result)) {
|
|
return true;
|
|
} else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function getEntry($id)
|
|
{
|
|
return $this->db->query('SELECT * FROM projects WHERE ID = ? LIMIT 1', [$id])->result_array();
|
|
}
|
|
|
|
public function getEntryByName($name, $lang = 'de')
|
|
{
|
|
$result = $this->db->query('SELECT * FROM projects WHERE name = ? LIMIT 1', [$name])->result_array();
|
|
|
|
$result = $this->mergeTranslationData($result, $lang);
|
|
|
|
return !empty($result) ? $result[0] : null;
|
|
}
|
|
|
|
public function getEntryCategories($id)
|
|
{
|
|
$categories = $this->db->query('SELECT * FROM projects_categories WHERE ID IN (SELECT categoryID FROM projects_entry_categories WHERE projectID = ?) AND lang = "de"', [$id])->result_array();
|
|
|
|
if($_SESSION['site_lang'] != 'de') {
|
|
$categoriesLang = $this->db->query('SELECT categoryID, name, displayname FROM projects_categories WHERE lang = ?', [$_SESSION['site_lang']])->result_array();
|
|
|
|
$categoryIDs = array_column($categories, 'categoryID');
|
|
foreach ($categoriesLang as $item) {
|
|
$key = array_search($item['categoryID'], $categoryIDs);
|
|
$categories[$key] = array_merge($categories[$key], $item);
|
|
}
|
|
}
|
|
|
|
return $categories;
|
|
}
|
|
|
|
public function updateCategories($postID, $categories)
|
|
{
|
|
$this->resetEntryCategories($postID);
|
|
foreach ($categories as $category) {
|
|
$this->addCategoryToEntryID($postID, $category);
|
|
}
|
|
}
|
|
|
|
public function resetEntryCategories($postID)
|
|
{
|
|
$this->db->query('DELETE FROM projects_entry_categories WHERE projectID = ?', $postID);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function addCategoryToEntryID($postID, $categoryID)
|
|
{
|
|
$this->db->query('INSERT INTO projects_entry_categories (projectID, categoryID) VALUES (?, ?)', [$postID, $categoryID]);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function addCategoryToEntryName($name, $categoryID)
|
|
{
|
|
$id = $this->db->query('SELECT ID FROM projects WHERE name = ? LIMIT 1', [$name])->result_array()[0];
|
|
$this->addCategoryToEntryID(intval($id['ID']), $categoryID);
|
|
}
|
|
|
|
public function getPrevProject($id)
|
|
{
|
|
$result = $this->db->query('SELECT * FROM projects WHERE datetime < (SELECT datetime FROM projects WHERE ID = ?) ORDER BY datetime DESC LIMIT 1', [$id])->result_array();
|
|
|
|
$result = $this->mergeTranslationData($result, $_SESSION['site_lang']);
|
|
|
|
return $result;
|
|
}
|
|
|
|
public function getNextProject($id)
|
|
{
|
|
$result = $this->db->query('SELECT * FROM projects WHERE datetime > (SELECT datetime FROM projects WHERE ID = ?) ORDER BY datetime ASC LIMIT 1', [$id])->result_array();
|
|
|
|
$result = $this->mergeTranslationData($result, $_SESSION['site_lang']);
|
|
|
|
return $result;
|
|
}
|
|
|
|
public function addVote($projectID, $userID, $voteType)
|
|
{
|
|
$this->db->query('DELETE FROM projects_entry_votes WHERE projectID = ? AND userID = ?', [$projectID, $userID]);
|
|
$this->db->query('INSERT INTO projects_entry_votes (projectID, userID, type) VALUES (?, ?, ?)', [$projectID, $userID, $voteType]);
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
$this->db->cache_delete('projects', 'addVote');
|
|
$this->db->cache_delete('admin', 'projects');
|
|
}
|
|
|
|
public function getVoteCount($projectID)
|
|
{
|
|
$result = $this->db->query('SELECT (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = ? AND type = 1) - (SELECT COUNT(*) FROM projects_entry_votes WHERE projectID = ? AND type = -1) voteCount', [$projectID, $projectID])->result_array();
|
|
return $result[0]['voteCount'];
|
|
}
|
|
|
|
public function getUserVoteType($projectID, $userID)
|
|
{
|
|
$result = $this->db->query('SELECT type FROM projects_entry_votes WHERE projectID = ? AND userID = ?', [$projectID, $userID])->result_array();
|
|
if (empty($result))
|
|
return 0;
|
|
return $result[0]['type'];
|
|
}
|
|
|
|
public function createNewProjectDraft()
|
|
{
|
|
$this->db->query('INSERT INTO projects () VALUES ()');
|
|
|
|
$this->db->cache_delete('admin', 'projects');
|
|
|
|
$data = $this->db->query('SELECT ID FROM projects ORDER BY ID DESC LIMIT 1')->result_array();
|
|
return $data[0]['ID'];
|
|
}
|
|
|
|
public function updateProject($id, $translations, $url, $download, $openSource, $customLink, $date, $image)
|
|
{
|
|
$this->db->query('UPDATE projects SET name = ?, isDownloadable = ?, downloadLink = ?, isOpenSource = ?, openSourceLink = ?, customLink = ?, datetime = ?, source = ? WHERE ID = ?', [$url, $download['available'], $download['link'], $openSource['available'], $openSource['link'], $customLink['link'], $date, $image, $id]);
|
|
|
|
$this->db->cache_off();
|
|
foreach ($translations as $lang => $translation) {
|
|
$data = $this->db->query('SELECT translationID FROM projects_translations WHERE projectID = ? AND lang = ?', [$id, $lang])->result_array();
|
|
|
|
if (empty($data)) {
|
|
$this->db->query('INSERT INTO projects_translations (projectID, lang) VALUES (?, ?)', [$id, $lang]);
|
|
|
|
$data = $this->db->query('SELECT translationID FROM projects_translations WHERE projectID = ? AND lang = ?', [$id, $lang])->result_array();
|
|
}
|
|
|
|
$translationID = $data[0]['translationID'];
|
|
|
|
$this->db->query('UPDATE projects_translations SET title = ?, description = ?, content = ?, downloadName = ?, openSourceName = ?, customLinkName = ? WHERE translationID = ?', array_merge($translation, [$download['name'], $openSource['name'], $customLink['name'], $translationID]));
|
|
}
|
|
$this->db->cache_on();
|
|
|
|
$this->db->cache_delete('admin', 'projects');
|
|
$this->db->cache_delete('projects', 'index');
|
|
$this->db->cache_delete('projects', 'entry');
|
|
}
|
|
|
|
public function mergeFullTranslationData($postList)
|
|
{
|
|
foreach ($postList as $i => $post) {
|
|
$data = $this->db->query('SELECT * FROM projects_translations WHERE projectID = ? ORDER BY lang', [$post['ID']])->result_array();
|
|
foreach ($data as $lang) {
|
|
$postList[$i]['translations'][$lang['lang']] = $lang;
|
|
}
|
|
}
|
|
return $postList;
|
|
}
|
|
}
|