[fmAAC][plugin] Get Power Gamers CRON

[GhostWD]

this so called 'plugin' require running on Linux
​

1. EXECUTE DB QUERY
{
    CREATE TABLE today_exp (
        id int NOT NULL AUTO INCREMENT,
        exp int NOT NULL,
        player_id int,
        PRIMARY KEY (id),
        FOREIGN KEY (player_id) REFERENCES players(id)
    );
}


2. SET CRON TASK
{
IN CONSOLE
    crontab -e
SELECT nano
AT BOTTOM OF FILE WRITE
    00 00 * * * php {DIR TO powergamers.php}
FOR EXAMPLE
    00 00 * * * php /var/www/html/aac/CRON/powergamers.php
}


3. EDIT aac/src/Twig/OtsExtension.php ADD THIS FUNCTION
{
    public function getPowerGamers()
    {
        $rsm = new ResultSetMapping;
        $rsm->addScalarResult('name', 'name');
        $rsm->addScalarResult('expDiff', 'expDiff');

        $powerGamers = $this->doctrine->getManager()
            ->createNativeQuery("SELECT name,id,(t1.experience - expBefore) as expDiff FROM players t1 INNER JOIN (SELECT player_id, exp as expBefore FROM today_exp) t2 ON t1.id = t2.player_id ORDER BY expDiff DESC", $rsm)
        ->getResult();

        return $powerGamers;
    }

AND ADD
    'getPowerGamers' => new Twig_Function('getPowerGamers', [$this, 'getPowerGamers']),
TO return OF getFunction()
}

CronHowto - Community Help Wiki

<?php
/*
CREATE TABLE today_exp (
    id int AUTO_INCREMENT,
    exp int NOT NULL,
    player_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (player_id) REFERENCES players(id)
);
*/

$cfg = [
    'database' => "DATABASE NAME",
    'user' => "root",
    'password' => "DATABASE PASSWORD",
    'host' => "127.0.0.1",
];

$dbh = new PDO("mysql:host={$cfg['host']};dbname={$cfg['database']}", $cfg['user'], $cfg['password']);


$stmt = $dbh->prepare('DELETE FROM today_exp');
$stmt->execute();

$stmt = $dbh->prepare('ALTER TABLE today_exp AUTO_INCREMENT = 1');
$stmt->execute();

$stmt = $dbh->prepare('SELECT experience, id FROM players;');
$stmt->execute();

$result = $stmt->fetchAll();

foreach ($result as $key => $value) {

    $stmt = $dbh->prepare("INSERT INTO today_exp VALUES (NULL, :exp, :playerid);");
    $stmt->bindValue(':playerid', $value['id'], PDO::PARAM_INT);
    $stmt->bindValue(':exp', $value['experience'], PDO::PARAM_INT);
    $stmt->execute();
    echo $value['id']."\t".$value['experience']."\n";

}

$dbh = null;
$stmt = null;
$result = null;