.-.-.-.-.-

<?php
class Stats
{
    private $con;

// Database Connection
    public function __construct(DBConnection $database)
    {
        $this->con = $database;
        return $this->con;
    }

public function getVisitors()
    {
        $query = "SELECT count(distinct(`ipaddress`)) FROM vote";
        $getpoliticians = $this->con->dbc->prepare($query);
        $getpoliticians->execute();
        $result = $getpoliticians->fetchColumn();

return $result;
    }

public function getTotalVotes()
    {
        $query = "SELECT count(*) FROM vote";
        $getpoliticians = $this->con->dbc->prepare($query);
        $getpoliticians->execute();
        $result = $getpoliticians->fetchColumn();

return $result;
    }

public function getVoteTotals()
    {
        $query = "SELECT count(*) FROM vote v where v.vote > 0 UNION SELECT count(*) FROM vote v where v.vote < 0";
        $getpoliticians = $this->con->dbc->prepare($query);
        $getpoliticians->execute();
        $result = $getpoliticians->fetchAll();

if (count($result) > 0) {
            $upvotes = $result[0];
            $downvotes = $result[1];
            return array($upvotes,$downvotes);
        } else {
            echo "Record not found";
        }
    }

public function top10UpVotes()
    {
        $query = "SELECT v.politicianid, (count(v.vote) + p.seedup) as votes, p.name, p.picture, p.seedup FROM vote v ".
        "JOIN politician p ON v.politicianid = p.aid ".
        "WHERE v.vote > 0 ".
        "GROUP BY v.politicianid ".
        "ORDER BY votes DESC ".
        "LIMIT 10";
        $getpoliticians = $this->con->dbc->prepare($query);
        $getpoliticians->execute();
        $result = $getpoliticians->fetchAll();

if (count($result) > 0) {
            return $result;
        } else {
            echo "Record not found";
        }
    }

public function top10DownVotes()
    {
        $query = "SELECT v.politicianid, (count(v.vote) + p.seeddown) as votes, p.name, p.picture, p.seeddown FROM vote v ".
        "JOIN politician p ON v.politicianid = p.aid ".
        "WHERE v.vote < 0 ".
        "GROUP BY v.politicianid ".
        "ORDER BY votes DESC ".
        "LIMIT 10";
        $getpoliticians = $this->con->dbc->prepare($query);
        $getpoliticians->execute();
        $result = $getpoliticians->fetchAll();

if (count($result) > 0) {
            return $result;
        } else {
            echo "Record not found";
        }
    }

public function getPopularity($id)
    {
        $query = "SELECT date(created) as date, count(`vote`) as upvote, 0 as downvote FROM vote WHERE vote > 0 AND created > '2021-03-08' AND politicianid = :id GROUP by date(created)  UNION SELECT date(created) as date, 0 as upvote, count(`vote`) as downvote FROM vote WHERE vote < 0 AND created > '2021-03-08' and politicianid = :id GROUP by date(created) ORDER BY date ";
        $getvotes = $this->con->dbc->prepare($query);
        $getvotes->bindParam(":id",$id);
        $getvotes->execute();
        $result = $getvotes->fetchAll();
        $popularity = 0;

foreach($result as $item)
        {
            if (isset($byDate[$item['date']])) {
                if ($item['upvote'] > 0) {
                    $popularity += $item['upvote'];
                    $byDate[$item['date']]['popularity'] = $popularity;
                }
                if ($item['downvote'] > 0) {
                    $popularity -= $item['downvote'];
                    $byDate[$item['date']]['popularity'] = $popularity;
                }
            }
            else {
                $popularity += $item['upvote'] - $item['downvote'];
                $byDate[$item['date']]['popularity'] = $popularity;
            }
        }

return $byDate;
    }

public function getPopularityEx($id)
    {
        $query = "SELECT date(created) as date, count(`vote`) as upvote, 0 as downvote FROM vote WHERE vote > 0 AND created > '2021-03-08' AND politicianid = :id GROUP by date(created)  UNION SELECT date(created) as date, 0 as upvote, count(`vote`) as downvote FROM vote WHERE vote < 0 AND created > '2021-03-08' and politicianid = :id GROUP by date(created) ORDER BY date ";
        $getvotes = $this->con->dbc->prepare($query);
        $getvotes->bindParam(":id",$id);
        $getvotes->execute();
        $result = $getvotes->fetchAll();
        $popularity = 0;
        $upvotes = 0;
        $downvotes = 0;

foreach($result as $item)
        {
            if (isset($byDate[$item['date']])) {
                if ($item['upvote'] > 0) {
                    $popularity += $item['upvote'];
                    $upvotes += $item['upvote'];
                    $byDate[$item['date']]['popularity'] = $popularity;
                    $byDate[$item['date']]['upvotes'] = $upvotes;
                }
                if ($item['downvote'] > 0) {
                    $popularity -= $item['downvote'];
                    $downvotes -= $item['downvote'];
                    $byDate[$item['date']]['popularity'] = $popularity;
                    $byDate[$item['date']]['downvotes'] = $downvotes;
                }
            }
            else {
                $popularity += $item['upvote'] - $item['downvote'];
                $upvotes += $item['upvote'];
                $downvotes -= $item['downvote'];
                $byDate[$item['date']]['popularity'] = $popularity;
                $byDate[$item['date']]['upvotes'] = $upvotes;
                $byDate[$item['date']]['downvotes'] = $downvotes;
            }
        }

return $byDate;
    }

public function getVotes($id)
    {
        $query = "SELECT date(created) as date, count(`vote`) as upvote, 0 as downvote FROM vote WHERE vote > 0 AND created > '2021-03-08' AND politicianid = :id GROUP by date(created)  UNION SELECT date(created) as date, 0 as upvote, count(`vote`) as downvote FROM vote WHERE vote < 0 AND created > '2021-03-08' and politicianid = :id GROUP by date(created) ORDER BY date ";
        $getvotes = $this->con->dbc->prepare($query);
        $getvotes->bindParam(":id",$id);
        $getvotes->execute();
        $result = $getvotes->fetchAll();
        $popularity = 0;
        $upvotes = 0;
        $downvotes = 0;

return $byDate;
    }

}

/home/wirbesti/unwaehlbar.ch/stats.php