TrxShell [ mangaberri.com ]

Name	Size	Permission
berribo	[ DIR ]	drwxrwxrwx
cherribo	[ DIR ]	drwxrwxrwx
mangas	[ DIR ]	drwxrwxrwx
spicybo	[ DIR ]	drwxrwxrwx
404.php	5.15 KB	-rw-rw-r--
528a3369db0e4160899e.txt	20 B	-rw-rw-r--
55b01d0d19.php	6.42 KB	-rw-rw-r--
BingSiteAuth.xml	85 B	-rw-rw-r--
OS_1.png	431.44 KB	-rw-rw-r--
OS_2.png	234.1 KB	-rw-rw-r--
actions.php	4.3 KB	-rw-rw-r--
ads.php	1.99 KB	-rw-rw-r--
ads.txt	114.42 KB	-rw-rw-r--
android_1.png	201.87 KB	-rw-rw-r--
android_2.png	218.54 KB	-rw-rw-r--
ava.jpg	54.06 KB	-rw-r--r--
berripix_banner.jpg	107.77 KB	-rw-rw-r--
berripix_indexbg.mp4	8.34 MB	-rw-rw-r--
config.php	3.95 KB	-rw-rw-r--
datasource.php	3.07 KB	-rw-r--r--
dbhelper.php	33.6 KB	-rw-rw-r--
dl_ads.php	125 B	-rw-rw-r--
dmca.php	8.67 KB	-rw-rw-r--
favicon.ico	67.58 KB	-rw-rw-r--
favicon.png	22.96 KB	-rw-rw-r--
favorites.php	89.96 KB	-rw-rw-r--
fireworks.php	15.13 KB	-rw-rw-r--
footer.php	7.98 KB	-rw-rw-r--
genre.php	16.02 KB	-rw-rw-r--
head.php	916 B	-rw-rw-r--
header.php	16.5 KB	-rw-rw-r--
history.php	6.76 KB	-rw-rw-r--
home.php	49.7 KB	-rw-rw-r--
ht_ads1.php	397 B	-rw-rw-r--
ht_ads2.php	398 B	-rw-rw-r--
ht_ads3.php	86.95 KB	-rw-rw-r--
icon.ico	148.56 KB	-rw-rw-r--
icon_snow.png	3.29 KB	-rw-rw-r--
index.php	22.68 KB	-rwxrwxrwx
index_empty.php	17 B	-rw-rw-r--
install_webapp.php	6.47 KB	-rw-rw-r--
log.txt	1.44 MB	-rw-r--r--
logo.png	43.75 KB	-rw-rw-r--
logo_dark.7c81824.png	70.25 KB	-rw-rw-r--
logo_light.d9991b5.png	83.33 KB	-rw-rw-r--
logo_white.f5e6e19.png	83.33 KB	-rw-rw-r--
manga_og.jpg	73.34 KB	-rw-rw-r--
mangaberri_banner.png	196.52 KB	-rw-rw-r--
mangaberri_vid.mp4	6.99 MB	-rw-rw-r--
mangabg.png	242.94 KB	-rw-rw-r--
manifest.json	432 B	-rw-rw-r--
mass.php	703 B	-rw-r--r--
mass.txt	703 B	-rw-r--r--
most-viewed.php	10.81 KB	-rw-rw-r--
new-manga.php	15.92 KB	-rw-rw-r--
new.php	0 B	-rw-rw-r--
outputter.php	0 B	-rw-rw-r--
privacy-policy.php	13.89 KB	-rw-rw-r--
protected.php	668 B	-rw-rw-r--
pubfuture_ads.php	107 B	-rw-rw-r--
pwnkit	0 B	-rw-r--r--
read-manga.php	52.76 KB	-rw-rw-r--
reading.php	32.03 KB	-rw-rw-r--
req1.php	31 B	-rw-r--r--
robots.txt	69 B	-rw-rw-r--
romance.jpg	84.7 KB	-rw-rw-r--
script.php	1.71 KB	-rw-rw-r--
search.php	16.56 KB	-rw-rw-r--
seo.php	1.21 KB	-rw-rw-r--
sign-in.php	6.88 KB	-rw-rw-r--
sitemap.xml	231.05 KB	-rw-rw-r--
snowflake.php	4.55 KB	-rw-rw-r--
style_css.php	102.1 KB	-rw-rw-r--
terms-of-service.php	24.93 KB	-rw-rw-r--
test-eval.php	406 B	-rw-rw-r--
test.php	28 B	-rw-rw-r--
uknown_user.e5ec11a.png	1.25 KB	-rw-rw-r--
weekly-manga.php	15.91 KB	-rw-rw-r--

Code Editor : dbhelper.php

<?php

date_default_timezone_set("Asia/Kuching");
class DBHelper
{
    private $db;
    function __construct()
    {
        require_once(__DIR__ . "/datasource.php");
        $this->db = new DataSource();
    }

public function getEncryptionKey()
    {
        //return 'YourSecretKey';
        return '11mangaberri.com';
    }

function generateSignedUrl($filePath)
    {
        $secretKey = 'berri';
        $expiry = time() + 300; // valid for 5 minutes
        $signature = hash_hmac('sha256', "$filePath$expiry", $secretKey);
        return "/protected.php?file=" . urlencode($filePath) . "&expiry=$expiry&signature=$signature";
    }

public function log($message)
    {
        $myfile = fopen("./log.txt", "a") or die("Unable to open file!");
        $txt = $message . "\n";
        fwrite($myfile, $txt);
        fclose($myfile);
    }

function getUserById($user_id)
    {
        $result = $this->db->select("SELECT * FROM users WHERE id=:id", ['id' => $user_id]);
        return $result;
    }

function getUsersCount()
    {
        $result = $this->db->select("SELECT * FROM users");
        return sizeof($result);
    }

function getClientIP()
    {
        $clientIP = $_SERVER['HTTP_CLIENT_IP']
            ?? $_SERVER["HTTP_CF_CONNECTING_IP"] # when behind cloudflare
            ?? $_SERVER['HTTP_X_FORWARDED']
            ?? $_SERVER['HTTP_X_FORWARDED_FOR']
            ?? $_SERVER['HTTP_FORWARDED']
            ?? $_SERVER['HTTP_FORWARDED_FOR']
            ?? $_SERVER['REMOTE_ADDR']
            ?? '0.0.0.0';

return $clientIP;
    }

public function processLogin($username, $password)
    {
        $query = "select * FROM users WHERE username = :username AND active=1";
        $params = ['username' => $username];
        $user = $this->db->select($query, $params);
        if (!empty($user)) {
            if (!isset($_SESSION)) {
                session_start();
            }

if ($user && password_verify($password, $user[0]['password'])) {
                $_SESSION["userId"] = $user[0]["id"];
                $_SESSION["username"] = $user[0]["username"];
                $_SESSION["role"] = $user[0]["role"];
                return true;
            } else {
                $_SESSION["errorMessage"] = 'Invalid User';
                return false;
            }
        } else {
            $_SESSION["errorMessage"] = 'Not Active User';
            return false;
        }
    }

public function addUserActionLog($user_id, $action, $ref_table, $ref_id)
    {
        $query = "INSERT INTO user_action_logs (user_id, action, ip, ref_table, ref_id, created) 
        VALUE (:user_id, :action, :ip, :ref_table, :ref_id, :created)";
        $params = [
            'user_id' => $user_id,
            'action' => $action,
            'ip' => $this->getClientIP(),
            'ref_table' => $ref_table,
            'ref_id' => $ref_id,
            'created' => date("Y-m-d H:i:s")
        ];

$this->db->execute($query, $params);
    }

public function signUp($username, $password)
    {
        $passwordHash = password_hash($password, PASSWORD_DEFAULT);
        $query = "INSERT INTO users (username, password, role, created, modified) VALUE (:username, :password, :role, :created, :modified)";
        $params = ['username' => $username, 'password' => $passwordHash, 'role' => 'Super Admin', 'created' => date("Y-m-d H:i:s"), 'modified' => date("Y-m-d H:i:s")];
        $user = $this->db->execute($query, $params);
        if (!empty($user)) {
            return true;
        } else {
            return false;
        }
    }

public function createManga($title, $author, $description, $genres, $attachment)
    {
        $path = '';
        $ext = '';

if (!empty($attachment)) {
            $this->log('Have Attachment');
            $path = '';
            $ext = '';

$filename = $attachment["name"];
            $ext = pathinfo($filename, PATHINFO_EXTENSION);
            $tempname = $attachment["tmp_name"];
            $filename = 'main-' . uniqid() . uniqid() . '.' . $ext;

$image_root = '/home/mysenter/public_html/mangapiece.mys-enterprise.com';
            $folder_name = 'mangas';
            $path = $image_root . '/' . $folder_name . '/' . $filename;
            $upload_dir = $image_root . '/' . $folder_name . '/';

if (file_exists($upload_dir) && is_writable($upload_dir)) {
                if (move_uploaded_file($tempname, $path)) {
                    $msg = "Image uploaded successfully";

$query = "INSERT INTO mangas (title, author, description, display_image, genres, created, modified)
                    VALUES (:title, :author, :description, :display_image, :genres, :created, :modified)";
                    $params = [
                        'title' => $title,
                        'author' => $author,
                        'description' => $description,
                        'display_image' => 'https://mangapiece.com/' . $folder_name . '/' . $filename,
                        'genres' => $genres,
                        'created' => date("Y-m-d H:i:s"),
                        'modified' => date("Y-m-d H:i:s")
                    ];
                    try {
                        $result = $this->db->execute($query, $params);
                        return $result;
                    } catch (Exception $e) {
                        $this->db->log($e);
                    }
                } else {
                    $msg = "Not uploaded because of error #" . $_FILES["file"]["error"];
                    $this->db->log($msg);
                    return false;
                }
            }
            $msg = "Not writeable folder";
            $this->db->log($msg);
            return false;
        } else {
            $this->log('No Attachement');
            $query = "INSERT INTO mangas (title, author, description, display_image, genres, created, modified)
            VALUES (:title, :author, :description, :display_image, :genres, :created, :modified)";
            $params = [
                'title' => $title,
                'author' => $author,
                'description' => $description,
                'display_image' => null,
                'genres' => $genres,
                'created' => date("Y-m-d H:i:s"),
                'modified' => date("Y-m-d H:i:s")
            ];
            $this->log(json_encode($params));
            try {
                $result = $this->db->execute($query, $params);
                return $result;
            } catch (Exception $e) {
                $this->db->log($e);
            }
        }
    }

public function updateManga($manga_id, $title, $author, $description, $genres, $rating, $attachment)
    {
        $path = '';
        $ext = '';

if (!empty($attachment['name'])) {
            $this->log('Have Attachment');
            $path = '';
            $ext = '';

if (file_exists($upload_dir) && is_writable($upload_dir)) {
                if (move_uploaded_file($tempname, $path)) {
                    $msg = "Image uploaded successfully";

$query = "UPDATE mangas SET title=:title, author=:author, description=:description, display_image=:display_image, genres=:genres, rating=:rating, modified=:modified where id = :id";
                    $params = [
                        'title' => $title,
                        'author' => $author,
                        'description' => $description,
                        'display_image' => 'https://mangapiece.com/' . $folder_name . '/' . $filename,
                        'genres' => $genres,
                        'rating' => $rating,
                        'modified' => date("Y-m-d H:i:s"),
                        'id' => $manga_id
                    ];
                    try {
                        $result = $this->db->execute($query, $params);
                        return $result;
                    } catch (Exception $e) {
                        $this->db->log($e);
                    }
                } else {
                    $msg = "Not uploaded because of error #" . $_FILES["file"]["error"];
                    $this->db->log($msg);
                    return false;
                }
            }
            $msg = "Not writeable folder";
            $this->db->log($msg);
            return false;
        } else {
            $query = "UPDATE mangas SET title=:title, author=:author, description=:description,  genres=:genres, rating=:rating, modified=:modified where id = :id";
            $params = [
                'title' => $title,
                'author' => $author,
                'description' => $description,
                'genres' => $genres,
                'rating' => $rating,
                'modified' => date("Y-m-d H:i:s"),
                'id' => $manga_id
            ];
            try {
                $result = $this->db->execute($query, $params);
                return $result;
            } catch (Exception $e) {
                $this->db->log($e);
            }
        }
    }

function uploadToMangaPiece($transfer_type, $files)
    {
        $url = 'https://mangapiece.com/mangabo/transfer_image.php?rnd=' . time();
        $curl = curl_init();
        $params = (object)array();
        $params->transfer_type = $transfer_type;
        $this->db->log('Attachment TMP : ' . $files['tmp_name']);
        if (function_exists('curl_file_create')) {
            $params->attachment = curl_file_create($files['tmp_name'], $files['type'],  $files['name']);
        } else {
            $params->attachment = '@' . $files['tmp_name'];
        }

curl_setopt_array($curl, array(
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_ENCODING => '',
            CURLOPT_MAXREDIRS => 10,
            CURLOPT_TIMEOUT => 0,
            CURLOPT_FOLLOWLOCATION => true,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => 'POST',
            CURLOPT_POSTFIELDS => $params
        ));

$this->db->log('Params : ' . json_encode($params));

$response = curl_exec($curl);

$this->db->log('Reponse : ' . json_encode($response));

curl_close($curl);
        return $response;
    }

function getAllMangas()
    {
        //$query = "SELECT m.*, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id FROM `mangas` as m";
        $query = "select k.* from (select m.*, count(r.id) as total_rating, (sum(r.rating) / count(r.id)) as average_rating, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id FROM ratings as r join mangas as m on (m.id = r.manga_id) group by r.manga_id) as k ORDER BY `k`.`average_rating` DESC limit 10";
        return $this->db->select($query);
    }

function getManga($id)
    {
        $query = "select m.*, sum(c.view) as chapter_view from mangas as m join chapters as c on (c.manga_id = m.id) where m.id = :id and m.active = 1";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getMangaByTitle($title)
    {
        $query = "select m.*, sum(c.view) as chapter_view from mangas as m join chapters as c on (c.manga_id = m.id) where m.active = 1 AND REGEXP_REPLACE(REPLACE(LOWER(m.title), ' ', '-'), '[^a-z0-9-]', '') = :title";
        $params = ['title' => $title];
        return $this->db->select($query, $params);
    }

function getMangaChapters($id)
    {
        $query = "select * FROM chapters where manga_id = :id order by id desc";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function addMangaChapter($manga_id, $chapter_name, $attachments)
    {
        $query = "INSERT INTO chapters (manga_id, name, created, modified)
                    VALUES (:manga_id, :name, :created, :modified)";
        $params = [
            'manga_id' => $manga_id,
            'name' => $chapter_name,
            'created' => date("Y-m-d H:i:s"),
            'modified' => date("Y-m-d H:i:s")
        ];
        try {
            $chapter_id = $this->db->executeReturnID($query, $params);

$total = count($attachments['name']);
            for ($i = 0; $i < $total; $i++) {
                $tmpFilePath = $attachments['tmp_name'][$i];
                $filename = $attachments['name'][$i];
                $this->addMangaChapterImage($manga_id, $chapter_id, $filename, $tmpFilePath);
            }
            return true;
        } catch (Exception $e) {
            $this->db->log($e);
        }
    }

function addMangaChapterImage($manga_id, $chapter_id, $filename, $tempname)
    {

$ext = pathinfo($filename, PATHINFO_EXTENSION);

//$filename = 'chap-' . uniqid() . uniqid() . '.' . $ext;

$image_root = '/home/mysenter/public_html/mangapiece.mys-enterprise.com';
        $folder_name = 'mangas/' . $manga_id . '/' . $chapter_id;
        $path = $image_root . '/' . $folder_name . '/' . $filename;
        $upload_dir = $image_root . '/' . $folder_name . '/';

if (!file_exists($upload_dir)) {
            mkdir($upload_dir, 0777, true);
        }

if (file_exists($upload_dir) && is_writable($upload_dir)) {
            if (move_uploaded_file($tempname, $path)) {
                $query = "INSERT INTO chapter_images (chapter_id, path, filename, created, modified)
                    VALUES (:chapter_id, :path, :filename, :created, :modified)";
                $params = [
                    'chapter_id' => $chapter_id,
                    'path' => 'https://mangapiece.com/' . $folder_name . '/' . $filename,
                    'filename' => $filename,
                    'created' => date("Y-m-d H:i:s"),
                    'modified' => date("Y-m-d H:i:s")
                ];
                try {
                    $result = $this->db->execute($query, $params);
                    return $result;
                } catch (Exception $e) {
                    $this->db->log($e);
                }
            }
        }
    }

function getMangaInfoByChapterId($id)
    {
        $query = "select m.* FROM chapters as c join mangas as m on (m.id = c.manga_id) where c.id = :id and m.active = 1";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getChapterInfo($id)
    {
        $query = "select * FROM chapters where id = :id";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getMangaChapterImages($id)
    {
        $this->updateMangaChapterView($id);
        $this->updateMangaChapterViewLog($id);
        $query = "select * FROM chapter_images where chapter_id = :id";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function updateMangaChapterView($id)
    {
        $query = "Update chapters set view=view+1 where id = :id";
        $params = ['id' => $id];
        return $this->db->execute($query, $params);
    }

function updateMangaChapterViewLog($id)
    {
        $chapter = $this->getChapterInfo($id)[0];
        $manga = $this->getMangaInfoByChapterId($id)[0];

$query = "INSERT INTO chapter_views (manga_id, chapter_id, ip, source, created, modified)
                    VALUES (:manga_id, :chapter_id, :ip, :source, :created, :modified)";
        $params = [
            'manga_id' => $manga['id'],
            'chapter_id' => $id,
            'ip' => $this->getClientIP(),
            'source' => 'mangaberri.com',
            'created' => date("Y-m-d H:i:s"),
            'modified' => date("Y-m-d H:i:s")
        ];
        try {
            return $this->db->execute($query, $params);
        } catch (Exception $e) {
            $this->db->log($e);
        }
    }

function deleteChapterImage($id)
    {
        $query = "DELETE FROM chapter_images where id = :id";
        $params = ['id' => $id];
        return $this->db->execute($query, $params);
    }

function deleteChapter($id)
    {
        $query = "DELETE FROM chapters where id = :id";
        $params = ['id' => $id];
        return $this->db->execute($query, $params);
    }

function deleteManga($id)
    {
        $query = "DELETE FROM mangas where id = :id";
        $params = ['id' => $id];
        return $this->db->execute($query, $params);
    }

function getGenres()
    {
        $query = "select * FROM genres where active = 1 order by name";
        return $this->db->select($query);
    }

function getActiveGenres()
    {
        $query = "select * from (
            SELECT g.name, (
                SELECT COUNT(m.id)
                FROM mangas AS m
                WHERE m.genres LIKE CONCAT('%', g.name, '%') and m.active = 1
            ) AS total
            FROM genres AS g) as k 
            where k.total > 0 order by name";
        return $this->db->select($query);
    }

function getGenreMangas($keyword)
    {
        $query = "select k.* from (select m.*, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id FROM mangas as m where genres like :keyword and m.active=1) as k where latest_chapter_id > 0";
        return $this->db->select($query, ['keyword' => '%' . $keyword . '%']);
    }

function getKeywordMangas($keyword)
    {
        $keyword = $this->sanitizeSearch($keyword);
        if (!empty($keyword)) {
            $this->addKeyword($keyword);
            $query = "select k.* from (select m.*, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id FROM mangas as m where m.title like :keyword) as k where latest_chapter_id > 0";
            return $this->db->select($query, ['keyword' => '%' . $keyword . '%']);
        }
        return [];
    }

function sanitizeSearch($input, $maxLength = 100)
    {
        // Force string & trim
        $search = trim((string)$input);

// Remove control chars (ASCII < 32, DEL)
        $search = preg_replace('/[\x00-\x1F\x7F]/u', '', $search);

// Strip HTML tags
        $search = strip_tags($search);

// Block / remove the special ad placeholder {search_term_string}
        if (stripos($search, '{search_term_string}') !== false) {
            $search = str_ireplace('{search_term_string}', '', $search);
        }

// Remove curly braces completely (blocks any { ... } tokens)
        $search = str_replace(['{', '}'], '', $search);

// Optionally: allow only a safe character set
        // Letters, numbers, spaces, and common punctuation
        $search = preg_replace('/[^a-zA-Z0-9\s\-\_\.\,\!\?\@\#\&\/]/u', '', $search);

// Collapse multiple spaces to single
        $search = preg_replace('/\s+/u', ' ', $search);

// Trim again after cleaning
        $search = trim($search);

// Limit length
        if ($maxLength > 0 && mb_strlen($search, 'UTF-8') > $maxLength) {
            $search = mb_substr($search, 0, $maxLength, 'UTF-8');
        }

return $search;
    }

function deleteGenre($id)
    {
        $query = "DELETE FROM genres where id = :id";
        $params = ['id' => $id];
        return $this->db->execute($query, $params);
    }

function getLatestUpdateChapter()
    {
        $query = "select max(c.id) as chapter_id,  m.* from chapters as c join mangas as m on (m.id = c.manga_id) where m.active = 1 group by m.id order by chapter_id desc limit 15";
        return $this->db->select($query);
    }

function getLatestChapter($id)
    {
        $query = "select * FROM chapters where manga_id = :id order by created desc limit 1;";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getRelatedMangas($id)
    {
        $target = $this->getManga($id)[0];
        $genres = explode(", ", $target['genres']);

$filter = '';
        $counter = 0;
        if (sizeof($genres) > 0) {
            $filter = "AND (";
            foreach ($genres as $g) {
                if ($counter == 0) {
                    $filter .= "genres like '%" . $g . "%'";
                } else {
                    $filter .= " OR genres like '%" . $g . "%'";
                }
                $counter += 1;
            }
            $filter .= ")";
        }

$query = "select m.*, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id FROM mangas as m where  m.id !=:id and m.active = 1 " . $filter . " ORDER BY RAND() limit 10";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getMangaComments($id)
    {
        $query = "select * FROM comments where manga_id = :id and parent_id is null order by created desc;";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

public function getAdminComments($parent_id)
    {
        $query = "select * FROM comments where parent_id = :parent_id";
        $params = ['parent_id' => $parent_id];
        return $this->db->select($query, $params);
    }

function containsWebLink($string)
    {
        // Regular expression to match URLs
        //$regex = '/\bhttps?:\/\/[^\s()<>]+(?:$[\w\d]+$|([^[:punct:]\s]|\/))/';
        $regex = '/\b(?:https?:\/\/)?(?:www\.)?[a-zA-Z0-9.-]+\.(?:com|org|net|edu|vip|gov|io|co|info|biz|uk|au|in|cn|[a-z]{2,})(?:\/[^\s]*)?\b/';

if (preg_match($regex, $string)) {
            return true; // The string contains a web link
        }

return false; // No web link found
    }

function addMangaComments($manga_id, $comment)
    {

$query = "INSERT INTO comments (manga_id, comment, ip, name, created, modified)
                    VALUES (:manga_id, :comment, :ip, :name, :created, :modified)";
        $params = [
            'manga_id' => $manga_id,
            'comment' => $comment,
            'ip' => $this->getClientIP(),
            'name' => uniqid(),
            'created' => date("Y-m-d H:i:s"),
            'modified' => date("Y-m-d H:i:s")
        ];
        try {
            return $this->db->select($query, $params);
        } catch (Exception $e) {
            $this->db->log($e);
        }
    }

function getMangaRatings($id)
    {
        $query = "select count(id) as total_rating, (sum(rating) / count(id)) as average_rating FROM ratings where manga_id = :id order by created desc;";
        $params = ['id' => $id];
        return $this->db->select($query, $params);
    }

function getAllTimeMangaViewRanking()
    {
        //$query = "SELECT sum(view) as total_view, m.* FROM chapters as c join mangas as m on (m.id = c.manga_id) where m.active = 1 group by manga_id ORDER BY sum(view) DESC";
        $query = "SELECT 
            stats.total_view,
            stats.latest_chapter_id,
            latest_chapter.name AS latest_chapter,
            m.*
            FROM (
            SELECT 
                c.manga_id,
                SUM(c.view) AS total_view,
                MAX(c.id) AS latest_chapter_id
            FROM chapters AS c
            GROUP BY c.manga_id
            ) AS stats
            JOIN chapters AS latest_chapter ON latest_chapter.id = stats.latest_chapter_id
            JOIN mangas AS m ON m.id = stats.manga_id
            WHERE m.active = 1
            ORDER BY stats.total_view DESC;";
        return $this->db->select($query);
    }

function getMangaGenreMostView($id)
    {
        $target = $this->getManga($id)[0];
        $genres = explode(", ", $target['genres']);

$filter = '';
        $counter = 0;
        if (sizeof($genres) > 0) {
            $filter = "AND (";
            foreach ($genres as $g) {
                if ($counter == 0) {
                    $filter .= "m.genres like '%" . $g . "%'";
                } else {
                    $filter .= " OR m.genres like '%" . $g . "%'";
                }
                $counter += 1;
            }
            $filter .= ")";
        }

$query = "SELECT sum(view) as total_view, m.* FROM chapters as c join mangas as m on (m.id = c.manga_id) " . $filter . " where m.active = 1 group by manga_id ORDER BY sum(view) DESC";
        return $this->db->select($query);
    }

function getMangaGenreMostViewByTitle($title)
    {
        $target = $this->getMangaByTitle($title)[0];

$filter = '';
        $genres = explode(", ", $target['genres']);
        $counter = 0;
        if (sizeof($genres) > 0) {
            $filter = "AND (";
            foreach ($genres as $g) {
                if ($counter == 0) {
                    $filter .= "m.genres like '%" . $g . "%'";
                } else {
                    $filter .= " OR m.genres like '%" . $g . "%'";
                }
                $counter += 1;
            }
            $filter .= ")";
        }

$query = "SELECT sum(view) as total_view, m.* FROM chapters as c join mangas as m on (m.id = c.manga_id AND m.active = 1) " . $filter . " group by manga_id ORDER BY sum(view) DESC";
        $this->log($query);
        return $this->db->select($query);
    }

function getGenreMostView($keyword)
    {
        $query = "SELECT sum(view) as total_view, m.* FROM chapters as c join mangas as m on (m.id = c.manga_id) where m.active= 1 and m.genres like '%" . $keyword . "%' group by manga_id ORDER BY sum(view) DESC";
        return $this->db->select($query);
    }

function getLatestWeekNewManga()
    {
        $query = "SELECT sum(view) as total_view, m.*, (select name from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter, (select id from chapters where manga_id = m.id order by id desc limit 1) as latest_chapter_id  FROM chapters as c join mangas as m on (m.id = c.manga_id) WHERE m.created >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH) and m.active = 1 group by manga_id ORDER BY m.created desc;";
        return $this->db->select($query);
    }

function getLatestWeekHotManga()
    {
        //$query = "SELECT count(cv.id) as total_view, m.* FROM chapter_views as cv join mangas as m on (m.id = cv.manga_id) WHERE m.active = 1 AND  DATE(cv.created) >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY AND DATE(cv.created) < CURDATE() - INTERVAL WEEKDAY(CURDATE()) - 7 DAY group by m.id order by total_view desc";
        $query = "
        SELECT 
            COUNT(cv.id) AS total_view,
            m.*,
            lc.id AS latest_chapter_id,
            lc.name AS latest_chapter
            FROM chapter_views AS cv
            JOIN mangas AS m ON m.id = cv.manga_id
            LEFT JOIN (
            SELECT c1.*
            FROM chapters c1
            INNER JOIN (
                SELECT manga_id, MAX(id) AS max_id
                FROM chapters
                GROUP BY manga_id
            ) c2 ON c1.manga_id = c2.manga_id AND c1.id = c2.max_id
            ) AS lc ON lc.manga_id = m.id
            WHERE m.active = 1
            AND DATE(cv.created) >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
            AND DATE(cv.created) < CURDATE() - INTERVAL WEEKDAY(CURDATE()) - 7 DAY
            GROUP BY m.id
            ORDER BY total_view DESC;";
        return $this->db->select($query);
    }

function getPollingResult()
    {
        $query = "select count(id) as total, selection FROM polls group by selection ";
        return $this->db->select($query);
    }

public function getDirectlinkUrl(string $placement): string
    {
        $query = "
      SELECT direct_url
      FROM ads_scripts
      WHERE placement = :p
        AND is_active = 1
        AND ad_type = 'direct'
        AND direct_url IS NOT NULL
        AND direct_url <> ''
      ORDER BY sort_order ASC, id DESC
      LIMIT 1
    ";
        $row = $this->db->select($query, ['p' => $placement]);
        if (!$row) return '';
        return (string)($row[0]['direct_url'] ?? '');
    }
}

${this.title}

Code Editor : dbhelper.php