ControleurScript.php

<?php


namespace gp\controleurs;
use Illuminate\Database\Capsule\Manager as DB;
use gp\modeles\Game_rating;
use gp\modeles\Genre;
use gp\modeles\Platform;
use gp\modeles\Game;
use gp\modeles\Company;
use gp\modeles\Character;
use gp\modeles\Rating_board;

class ControleurScript
{
    public function td1()
    {
        echo "script TD1";
        $tab = null;
        $tab[] = $this->gameByName('Mario')->get();
        $tab[] = $this->CompanyByCountry('Japan')->get();
        $tab[] = $this->platformByBase('10000000')->get();
        $tab[] = Game::skip(21173)->take(442)->get();
        $data = Game::simplePaginate(500);
        $s = "<p>--------------------------------------------------------<br>" .
            "Jeu contenant dans leur nom 'Mario'<br>" .
            "--------------------------------------------------------</p>";
        echo $s;
        foreach ($tab[0] as $game) {
            echo("<p>{$game->name}</p>");
        }
        $s = "<p>--------------------------------------------------------<br>" .
            "Compagnies installées au 'Japon'<br>" .
            "--------------------------------------------------------</p>";
        echo $s;
        foreach ($tab[1] as $company) {
            echo("<p>{$company->name}</p>");
        }
        $s = "<p>--------------------------------------------------------<br>" .
            "Plateformes dont la base installée est >= 10 000 000<br>" .
            "--------------------------------------------------------</p>";
        echo $s;
        foreach ($tab[2] as $plat) {
            echo("<p>{$plat->name}</p>");
        }
        $s = "<p>--------------------------------------------------------<br>" .
            "liste de 442 jeux à partir du 21173ème<br>" .
            "--------------------------------------------------------</p>";
        echo $s;
        foreach ($tab[3] as $game) {
            echo("<p>{$game->name}</p>");
        }
        $s = "<p>--------------------------------------------------------<br>" .
            "liste des jeux en paginant (taille des pages : 500)<br>" .
            "--------------------------------------------------------</p>";
        echo $s;
        $res = "";
        foreach ($data as $t) {
            $res = $res . "<p>$t->name</p>";
        }
        $res = "<div class=\"container\">
                    $res
                </div>";
        echo $res;
    }

    private function gameByName($name)
    {
        return Game::where("name", "like", "%{$name}%");
    }

    private function companyByCountry($country)
    {
        return Company::where("location_country", "like", "{$country}");
    }

    private function platformByBase($base)
    {
        return Platform::where("install_base", ">=", "{$base}");
    }

    /**
     *  TD_2
     */


    public function td2_q1()
    {
        $q = $this->nameDeckByGame(12342)->get();
        echo("<p>---------------------------------------------<br>" .
            "<p>(name, deck) des personnages du jeu 12342<br>" .
            "<p>---------------------------------------------<br>");
        foreach ($q as $item) {
            echo "<p>nom :$item->name , deck : $item->deck</p>";
        }
    }

    public function td2_q2()
    {
        $q = Game::where('name', 'like', "Mario%")->get();
        echo("<p>---------------------------------------------<br>" .
            "<p>les personnages des jeux dont le nom (du jeu) débute par 'Mario<br>" .
            "<p>---------------------------------------------<br>");
        foreach ($q as $game) {
            echo "<p>Characters from :$game->name</p>";
            $list = $game->characters()->get();
            foreach ($list as $char)
                echo "<p>nom : $char->name</p>";
        }
    }

    public function td2_q3()
    {
        $q = Company::where("name", "like", "%sony%")->get();
        echo("<p>---------------------------------------------<br>" .
            "<p>les jeux développés par une compagnie dont le nom contient 'Sony<br>" .
            "<p>---------------------------------------------<br>");
        foreach ($q as $company) {
            echo "<p>Games from :$company->name</p>";
            $list = $company->games()->get();
            foreach ($list as $game)
                echo "<p>nom : $game->name</p>";
        }
    }

    public function td2_q4()
    {
        $q = Game::where('name', 'like', "Mario%")->get();
        echo("<p>---------------------------------------------<br>" .
            "<p>le rating initial (indiquer le rating board) des jeux  dont le nom contient Mario<br>" .
            "<p>---------------------------------------------<br>");
        foreach ($q as $game) {
            $list = $game->ratings()->get();
            echo "<p>From $game->name : </p>";
            foreach ($list as $rate)
                echo "<p>id rating_board : $rate->rating_board_id, name : {$rate->rating_board()->first()->name}</p>";
        }
    }

    public function td2_q5()
    {
        $q = Game::where('name', 'like', "Mario%")->get();
        echo("<p>---------------------------------------------<br>" .
            "<p>les jeux dont le nom débute par Mario et ayant plus de 3 personnages<br>" .
            "<p>---------------------------------------------<br>");
        foreach ($q as $game) {
            $list = $game->characters()->get();
            if (count($list) > 3) echo "<p>$game->name</p>";
        }
    }

    public function td2_q6()
    {
        echo("<p>---------------------------------------------<br>" .
            "<p>les jeux dont le nom débute par Mario et dont le rating initial contient \"3+\"<br>" .
            "<p>---------------------------------------------<br>");
        $list = Game_rating::select('name')->where('name', 'like', "%3+%")->get();
        foreach ($list as $rate) {
            echo "<p>$rate->name</p>";
            $games = $rate->games()->where('name', 'like', "Mario%")->get();
            echo $games;
            foreach ($games as $game) echo "<p>$game->name</p>";
        }
    }

    public function td2_q8()
    {
        $tmpGames = null;
        $tmpBoard = null;
        echo("<p>---------------------------------------------<br>" .
            "<p>les jeux dont le nom débute par Mario, publiés par une compagnie dont le nom contient \"Inc.\" et dont le rating initial contient \"3+\"<br>" .
            "<p>---------------------------------------------<br>");
        $list = Game_rating::select('name')->where('name', 'like', "%3+%")->get();
        foreach ($list as $rate) {
            echo "<p>$rate->name</p>";
            $games = $rate->games()->where('name', 'like', "Mario%")->get();
            echo $games;
            foreach ($games as $game) if($game->developers()->where('name', 'like', '%Inc.%')->get != null) $tmpGames[] = $game;
        }
        $tmpBoard = Game_rating::where('name', 'like', 'CERO')->first();
        if($tmpBoard!=null) $tmpBoard = $tmpBoard->games()->get();
        $res = array_intersect((array)$tmpGames, (array)$tmpBoard);
        foreach ($res as $games){
            echo "<p> {$games->nom} </p>";
        }
    }

    public function td2_q7()
    {
        echo("<p>---------------------------------------------<br>" .
            "<p>les jeux dont le nom débute par Mario, publiés par une compagnie dont le nom contient \"Inc.\" et dont le rating initial contient \"3+\" t ayant reçu un avis de la part du  rating board nommé \"CERO\"<br>" .
            "<p>---------------------------------------------<br>");
        $list = Game_rating::select('name')->where('name', 'like', '%3+%')->get();
        foreach ($list as $rate) {
            echo "<p>$rate->name</p>";
            $games = $rate->games()->where('name', 'like', "Mario%")->get();
            echo $games;
            foreach ($games as $game) if($game->developers()->where('name', 'like', '%Inc.%')->get != null) echo "<p>$game->name</p>";
        }
    }

    public function td2_q9()
    {
        echo "q9";
        $games = Game::where('id', '=', 12)->orWhere('id', '=', 56)->orWhere('id', '=', 345)->get();
        if(Genre::where('name', 'like', '')->first() == null) {
            $genre = Genre::create('Hardcore', 'Hardcore is hard.','<p style="">Hardcore games where you die a lot to succed.</p>');
            foreach ($games as $game){
                if($game->genres()->where('id', '=', $genre->id)->first() == null) $game->genres()->attach([$genre->id]);
            }
        }
        foreach ($games as $game) {
            echo "<p>Id : $game->id : </p>";
            $genres = $game->genres()->get();
                foreach ($genres as $genre){
                    echo "<p> Cat : $genre->name</p>";
                }
            }
    }

    private function nameDeckByGame($id){
        return Game::where('id','=',$id)->first()->characters();
    }

/*-------------------------------------------------------------------
 *                              TD3 PART II
 * ------------------------------------------------------------------
 */
    public function setLog($bool){
        if($bool) DB::connection()->enableQueryLog();
        else DB::connection()->disableQueryLog();
    }

    public function getLog(){
        $res = '<center><h2>Log</h2></center>';
        $log = DB::connection()->getQueryLog();
        foreach ($log as $line){
            $res = $res . "<div> <p>----------------------------------------------------------------------------------------------------------</p>";
            $res = $res . "<p> - Requete sql : " . $line['query'] . " : </p>";
            $res = $res . "<div><p>&nbsp&nbsp | - Parametres : </p>";
           foreach ($line['bindings'] as $bind){
              $res = $res . "<p>&nbsp&nbsp&nbsp&nbsp  - $bind</p>";
            }
            $res = $res . "</div> <p>&nbsp&nbsp | - Temps d'execution : " . $line['time'] . "</p>";
            $res = $res . "<p>----------------------------------------------------------------------------------------------------------</p></div>";
        }
        return $res;
    }

    public function td3_q1b(){
        return Game::where('name', 'like','%Mario%')->get();
    }

    public function td3_q2b(){
        return Game::where('id', '=', 12342)->first()->characters()->get();
    }

    public function td3_q3b(){
        $res = null;
        $q = Game::where('name', 'like', "%Mario%")->get();
        foreach ($q as $game) {
            $list = $game->characters()->get();
            foreach ($list as $character)
                $first = $character->games()->orderBy('original_release_date', 'asc')->first();
                if($first != null && str_contains(strtolower($first->name),'mario'))
                $res[] = $character;
        }
        $res = array_unique((array)$res);
        return $res;
    }

    public function td3_q4b(){
        $q = Game::where('name', 'like', "Mario%")->get();
        foreach ($q as $game) {
            $characters = $game->characters()->get();
            foreach ($characters as $char)
                $res[] = $char;
        }
        $res = array_unique((array)$res);
        return $res;
    }

    public function td3_q5b(){
        $res = null;
        $comps = Company::where('name','like', '%Sony%')->get();
        foreach ($comps as $comp) {
            $games = $comp->games()->get();
            foreach ($games as $game) $res[] = $game;
        }
        $res = array_unique((array)$res);
        return $res;
    }

    public function td3_q6b(){
        $res = null;
        $results = DB::table('game')
            ->select(DB::raw('group_concat(character.name) as names'))
            ->join('game2character', 'game.id', '=', 'game2character.game_id')
            ->join('character', 'game2character.character_id', '=', 'character.id')
            ->whereRaw('`game`.`name` like \'%Mario%\'')
            ->groupBy('game.name')
            ->get();
        foreach ($results as $result) {
            $names = explode(',', $result->names);
            foreach($names as $name) $res[] = $name;
        }
        return $res;
    }

    public function allGame(){
        $time_start = microtime(true);
        Game::get();
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        return $time;
    }

    public function gameMario(){
        $time_start = microtime(true);
        Game::where('name', 'like', "%Mario%")->get();
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        return $time;
    }

    public function gameDebMario(){
        $time_start = microtime(true);
        $q= Game::where('name', 'like', "Mario%")->get();
        foreach ($q as $game) {
            $game->characters()->get();
        }
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        return $time;
    }

    public function gameDebMarioRate(){
        $time_start = microtime(true);
        Game_rating::select('name')->where('name', 'like', '%3+%')->get();
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        return $time;
    }
}