ControleurScript.php 13.4 KB
Newer Older
SASSU Thomas's avatar
SASSU Thomas committed
1
2
3
4
<?php


namespace gp\controleurs;
SASSU Thomas's avatar
SASSU Thomas committed
5
use Illuminate\Database\Capsule\Manager as DB;
SASSU Thomas's avatar
SASSU Thomas committed
6
7
use gp\modeles\Game_rating;
use gp\modeles\Genre;
SASSU Thomas's avatar
SASSU Thomas committed
8
9
10
use gp\modeles\Platform;
use gp\modeles\Game;
use gp\modeles\Company;
SASSU Thomas's avatar
SASSU Thomas committed
11
use gp\modeles\Character;
SASSU Thomas's avatar
SASSU Thomas committed
12
use gp\modeles\Rating_board;
SASSU Thomas's avatar
SASSU Thomas committed
13
14
15

class ControleurScript
{
SASSU Thomas's avatar
SASSU Thomas committed
16
17
    public function td1()
    {
SASSU Thomas's avatar
SASSU Thomas committed
18
        echo "script TD1";
SASSU Thomas's avatar
SASSU Thomas committed
19
        $tab = null;
SASSU Thomas's avatar
SASSU Thomas committed
20
21
22
23
24
        $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);
SASSU Thomas's avatar
SASSU Thomas committed
25
26
27
        $s = "<p>--------------------------------------------------------<br>" .
            "Jeu contenant dans leur nom 'Mario'<br>" .
            "--------------------------------------------------------</p>";
SASSU Thomas's avatar
SASSU Thomas committed
28
        echo $s;
SASSU Thomas's avatar
SASSU Thomas committed
29
30
        foreach ($tab[0] as $game) {
            echo("<p>{$game->name}</p>");
SASSU Thomas's avatar
SASSU Thomas committed
31
        }
SASSU Thomas's avatar
SASSU Thomas committed
32
33
        $s = "<p>--------------------------------------------------------<br>" .
            "Compagnies installées au 'Japon'<br>" .
SASSU Thomas's avatar
SASSU Thomas committed
34
35
            "--------------------------------------------------------</p>";
        echo $s;
SASSU Thomas's avatar
SASSU Thomas committed
36
37
        foreach ($tab[1] as $company) {
            echo("<p>{$company->name}</p>");
SASSU Thomas's avatar
SASSU Thomas committed
38
        }
SASSU Thomas's avatar
SASSU Thomas committed
39
40
        $s = "<p>--------------------------------------------------------<br>" .
            "Plateformes dont la base installée est >= 10 000 000<br>" .
SASSU Thomas's avatar
SASSU Thomas committed
41
42
            "--------------------------------------------------------</p>";
        echo $s;
SASSU Thomas's avatar
SASSU Thomas committed
43
44
        foreach ($tab[2] as $plat) {
            echo("<p>{$plat->name}</p>");
SASSU Thomas's avatar
SASSU Thomas committed
45
        }
SASSU Thomas's avatar
SASSU Thomas committed
46
47
        $s = "<p>--------------------------------------------------------<br>" .
            "liste de 442 jeux à partir du 21173ème<br>" .
SASSU Thomas's avatar
SASSU Thomas committed
48
49
            "--------------------------------------------------------</p>";
        echo $s;
SASSU Thomas's avatar
SASSU Thomas committed
50
51
        foreach ($tab[3] as $game) {
            echo("<p>{$game->name}</p>");
SASSU Thomas's avatar
SASSU Thomas committed
52
        }
SASSU Thomas's avatar
SASSU Thomas committed
53
54
        $s = "<p>--------------------------------------------------------<br>" .
            "liste des jeux en paginant (taille des pages : 500)<br>" .
SASSU Thomas's avatar
SASSU Thomas committed
55
56
57
            "--------------------------------------------------------</p>";
        echo $s;
        $res = "";
SASSU Thomas's avatar
SASSU Thomas committed
58
        foreach ($data as $t) {
SASSU Thomas's avatar
SASSU Thomas committed
59
60
            $res = $res . "<p>$t->name</p>";
        }
SASSU Thomas's avatar
SASSU Thomas committed
61
        $res = "<div class=\"container\">
SASSU Thomas's avatar
SASSU Thomas committed
62
63
                    $res
                </div>";
SASSU Thomas's avatar
SASSU Thomas committed
64
        echo $res;
SASSU Thomas's avatar
SASSU Thomas committed
65
66
    }

SASSU Thomas's avatar
SASSU Thomas committed
67
68
    private function gameByName($name)
    {
SASSU Thomas's avatar
SASSU Thomas committed
69
70
71
        return Game::where("name", "like", "%{$name}%");
    }

SASSU Thomas's avatar
SASSU Thomas committed
72
73
    private function companyByCountry($country)
    {
SASSU Thomas's avatar
SASSU Thomas committed
74
75
76
        return Company::where("location_country", "like", "{$country}");
    }

SASSU Thomas's avatar
SASSU Thomas committed
77
78
    private function platformByBase($base)
    {
SASSU Thomas's avatar
SASSU Thomas committed
79
80
        return Platform::where("install_base", ">=", "{$base}");
    }
SASSU Thomas's avatar
SASSU Thomas committed
81
82
83
84
85

    /**
     *  TD_2
     */

PERCIN Cahit's avatar
PERCIN Cahit committed
86

SASSU Thomas's avatar
SASSU Thomas committed
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
    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>");
PERCIN Cahit's avatar
PERCIN Cahit committed
193
        $list = Game_rating::select('name')->where('name', 'like', '%3+%')->get();
SASSU Thomas's avatar
SASSU Thomas committed
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
        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();
SASSU Thomas's avatar
SASSU Thomas committed
223
    }
PERCIN Cahit's avatar
PERCIN Cahit committed
224

SASSU Thomas's avatar
SASSU Thomas committed
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
/*-------------------------------------------------------------------
 *                              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(){
SASSU Thomas's avatar
SASSU Thomas committed
251
        return Game::where('name', 'like','%Mario%')->get();
SASSU Thomas's avatar
SASSU Thomas committed
252
253
254
255
256
257
258
259
    }

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

    public function td3_q3b(){
        $res = null;
SASSU Thomas's avatar
SASSU Thomas committed
260
261
262
263
264
265
        $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'))
SASSU Thomas's avatar
SASSU Thomas committed
266
267
                $res[] = $character;
        }
SASSU Thomas's avatar
SASSU Thomas committed
268
        $res = array_unique((array)$res);
SASSU Thomas's avatar
SASSU Thomas committed
269
270
271
272
        return $res;
    }

    public function td3_q4b(){
SASSU Thomas's avatar
SASSU Thomas committed
273
274
275
276
277
278
279
280
        $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;
SASSU Thomas's avatar
SASSU Thomas committed
281
282
283
    }

    public function td3_q5b(){
SASSU Thomas's avatar
SASSU Thomas committed
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
        $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;
SASSU Thomas's avatar
SASSU Thomas committed
308
    }
SASSU Thomas's avatar
merge    
SASSU Thomas committed
309

310
311
312
313
314
315
316
317
    public function td3_index($val){
        $time_start = microtime(true);
        Game::where('name', 'like', $val.'%')->get();
        $time_end = microtime(true);
        $time = $time_end - $time_start;
        return $time;
    }

PERCIN Cahit's avatar
PERCIN Cahit committed
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
    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);
PERCIN Cahit's avatar
PERCIN Cahit committed
336
337
338
339
        $q= Game::where('name', 'like', "Mario%")->get();
        foreach ($q as $game) {
            $game->characters()->get();
        }
PERCIN Cahit's avatar
PERCIN Cahit committed
340
341
342
343
344
345
346
347
348
349
350
351
        $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;
    }
SASSU Thomas's avatar
SASSU Thomas committed
352
}