backend.inc (29177B)
1 <?php 2 require_once("ressources/relations.inc"); 3 require_once("ressources/db.inc"); 4 5 6 7 /* Les prototypes des fonctions : 8 *===============================> 9 * getGidFromPgid($user, $pgid); 10 * checkLogin($user, $passwd); 11 * randomCenterNode(); 12 * randomCloudNode(); 13 * cgBuildResultSets($cloudSize, $centerEid, $r1, $r2); 14 * cgChooseRelations(); 15 * cgBuildCloud($centerEid, $cloudSize, $sources, $sumWeights); 16 * insertCreatedGame($centerEid, $cloud, $r1, $r2, $totalDifficulty, $userName); 17 * randomGameCore(); 18 * randomGame(); 19 * formatWord($word); 20 * game2array($user, $pgid); 21 * createGame($nbParties, $mode); 22 * createGameCore($cloudSize); 23 * computeScore($probas, $difficulty, $answer, $userReputation); 24 * computeUserReputation($score); 25 * normalizeProbas($row); 26 * setGame($user, $pgid, $gid, $answers); 27 * getGameRelations(); 28 * setGameGetScore($user, $pgid, $gid, $answers); 29 * getNodeEid($node); 30 * wordExist($node); 31 * setUserInfo($user, $key, $value); 32 * userPrefsDefaults(); 33 * userPrefs($user); 34 * setUserPref($user, $key, $value); 35 * getJAimePgid($user, $pgid); 36 * setJAimePgid($user, $pgid, $value); 37 * addGameCreationBonus($user, $nb); 38 * function deleteOneGameCreation($user); 39 * getNbGameCreationRemained($user); 40 */ 41 42 43 /* Les définitions 44 ====================*/ 45 46 function longStrVal($str) { 47 return preg_match('/^-?[0-9]+$/', ''.$str) ? ''.$str : '0'; 48 } 49 50 /** Vérifie la validité du couple nom d'utilisateur / mot de passe. 51 * @param user : Le nom d'utilisateur. 52 * @param passwd : Le mot de passe. 53 * @return boolean : true si OK sinon false. 54 */ 55 function checkLogin($user, $passwd) { 56 if(isset($_SESSION['userId'])) 57 return true; 58 connect($user, $passwd); 59 } 60 61 function connect($user, $passwd) { 62 $db = getDB(); 63 $hashPasswd = md5($passwd); 64 $loginIsOk = ($hashPasswd == $db->querySingle("SELECT hash_passwd FROM user WHERE login='".$user."';")); 65 return $loginIsOk; 66 } 67 68 /** Selectionne aléatoirement l'eid d'un mot central. 69 * @return eid : Identifiant d'un mot central, NULL en cas d'erreur. 70 */ 71 function randomCenterNode() 72 { 73 $db = getDB(); 74 return $db->querySingle("select eid from random_center_node where rowid = (abs(random()) % (select max(rowid) from random_center_node))+1;"); 75 } 76 77 /** Selectionne aléatoirement un noeud d'un nuage. 78 * @return eid : L'identifiant du noeud. 79 */ 80 function randomCloudNode() 81 { 82 $db = getDB(); 83 return $db->querySingle("select eid from random_cloud_node where rowid = (abs(random()) % (select max(rowid) from random_cloud_node))+1;"); 84 } 85 86 /** 87 * @param cloudSize : Taille du nuage. 88 * @param centerEid : Identifiant du mot central. 89 * @param r1 Type de la relation 1. 90 * @param r2 Type de la relation 2. 91 */ 92 function cgBuildResultSets($cloudSize, $centerEid, $r1, $r2) 93 { 94 $db = getDB(); 95 // 'w' => weight (poids), 'd' => difficulté, 's' => select 96 // Le select doit ranvoyer trois colonnes : 97 // eid => l'eid du mot à mettre dans le nuage, 98 // r1 => la probabilité pour que le mot soit dans r1, entre -1 et 1 (négatif = ne devrait pas y être, positif = devrait y être à coup sûr, 0 = on sait pas). 99 $typer1r2 = "type in ($r1, $r2)"; 100 $banned_types = "4, 12, 36, 18, 29, 45, 46, 47, 48, 1000, 1001"; 101 102 $sources = array( 103 // Voisins 1 saut du bon type (= relations déjà existantes) 104 array('w'=>40, 'd'=>1, 's'=>"select end as eid, type = $r1 as r1, type = $r2 as r2, 0 as r0, 0 as trash from relation where start = $centerEid and $typer1r2 order by random();"), 105 // Voisins 1 saut via r_associated (0), donc qu'on voudrait spécifier si possible. 106 array('w'=>40, 'd'=>2, 's'=>"select end as eid, 0.25 as r1, 0.25 as r2, 0.5 as r0, 0 as trash from relation where start = $centerEid and type = 0 order by random();"), 107 // Voisins 1 saut via les autres relations 108 array('w'=>20, 'd'=>3.1, 's'=>"select end as eid, 0.1 as r1, 0.1 as r2, 0.8 as r0, 0 as trash from relation where start = $centerEid and type not in (0, $r1, $r2, $banned_types) order by random();"), 109 // Voisins 2 sauts, avec un mix de R1 et R2 pour les liens. Par ex [ A -R1-> B -R2-> C ] ou bien [ A -R2-> B -R2-> C ] 110 // Version optimisée de : "select end as eid from relation where $typer1r2 and start in oneHopWithType order by random();" 111 array('w'=>30, 'd'=>3.2, 's'=>"select B.end as eid, ((A.type = $r1) + (B.type = $r1)) / 3. as r1, ((A.type = $r2) + (B.type = $r2)) / 3. as r2, 1/6. as r0, 1/6. as trash from relation as A, relation as B where A.start = $centerEid and A.$typer1r2 and B.start = A.end and B.$typer1r2 order by random();"), 112 // Voisins 1 saut r1/r2 + 1 saut synonyme 113 // Version optimisée de : "select end as eid from relation where start in oneHopWithType and type = 5 order by random()"; 114 array('w'=>20, 'd'=>5, 's'=>"select B.end as eid, (A.type = $r1) * 0.75 as r1, (A.type = $r2) * 0.75 as r2, 0.25 as r0, 0 as trash from relation as A, relation as B where A.start = $centerEid and A.$typer1r2 and B.start = A.end and B.type = 5 order by random();"), 115 // Version optimisée de : "select end as eid from relation where start in (select end from relation where start = $centerEid and type = 5) and $typer1r2 order by random();" 116 array('w'=>20, 'd'=>6, 's'=>"select B.end as eid, (B.type = $r1) * 0.75 as r1, (B.type = $r2) * 0.75 as r2, 0.25 as r0, 0 as trash from relation as A, relation as B where A.start = $centerEid and A.type = 5 and B.start = A.end and B.$typer1r2 order by random();"), 117 // Voisins 2 sauts (tous) 118 // Version optimisée de : "select end as eid, 0.1 as r1, 0.1 as r2, 0.3 as r0, 0.5 as trash from relation where start in (select end from relation where start = $centerEid and type not in ($banned_types)) and type not in ($banned_types) order by random();" 119 array('w'=>10, 'd'=>8, 's'=>"select x as eid, 0.1 as r1, 0.1 as r2, 0.3 as r0, 0.5 as trash from (select x from (select X.eid + Y.dumb as x from (select B.end as eid from relation as A, relation as B where A.type not in ($banned_types) and A.start = $centerEid and B.type not in ($banned_types) and B.start = A.end limit ".($cloudSize*4).") as X, (select 0 as dumb) as Y)) order by random();"), 120 // Centre pointe vers X, M pointe vers X aussi, on prend M. 121 // Version optimisée de : "select start as eid from relation where end in (select end from relation where start = $centerEid) and type not in ($banned_types) order by random();" 122 // Ce n'est toujours pas ça… : "select eid from (select B.start as eid from relation as A, relation as B where A.type not in ($banned_types) and A.start = $centerEid and B.type not in ($banned_types) and B.end = A.end limit 1) order by random();" 123 // Tordu, mais ça marche \o/ . En fait il faut empêcher l'optimiseur de ramener le random avant le limit (et l'optimiseur est malin… :) 124 array('w'=>10, 'd'=>8, 's'=>"select x as eid, 0.1 as r1, 0.1 as r2, 0.2 as r0, 0.6 as trash from (select x from (select X.eid + Y.dumb as x from (select B.start as eid from relation as A, relation as B where A.type not in ($banned_types) and A.start = $centerEid and B.type not in ($banned_types) and B.end = A.end limit ".($cloudSize*4).") as X, (select 0 as dumb) as Y)) order by random();"), 125 'rand' => array('w'=>5, 'd'=>10, 's'=>false) // random. Le r1 et r2 de random sont juste en-dessous 126 ); 127 128 $sumWeights = 0; 129 130 foreach ($sources as $k => $x) 131 { 132 $sumWeights += $x['w']; 133 $sources[$k]['rsPos'] = 0; 134 $sources[$k]['rsSize'] = 0; 135 136 if ($x['s'] !== false) 137 { 138 $sources[$k]['resultSet'] = array(); 139 $res = $db->query($x['s']); 140 $i = 0; 141 142 while ($i < $cloudSize && $sources[$k]['resultSet'][] = $res->fetchArray()) 143 { 144 $i++; 145 $sources[$k]['rsSize']++; 146 } 147 } 148 else 149 { 150 $sources[$k]['resultSet'] = array(); 151 152 for ($i = 0; $i < $cloudSize; $i++) 153 { 154 $sources[$k]['resultSet'][] = array('eid'=>randomCloudNode(), 'r1'=>0, 'r2'=>0, 'r0'=>0, 'trash'=>1); 155 $sources[$k]['rsSize']++; 156 } 157 } 158 } 159 160 return array($sources, $sumWeights); 161 } 162 163 164 /** Sélectionne aléatoirement deux relations. 165 * @return array : Tableau avec la relation 1 et la relation 2. 166 */ 167 function cgChooseRelations() 168 { 169 global $stringRelations; 170 $relations = array_values(array_diff(array_keys($stringRelations), array(-1, 0))); 171 $r1 = rand(0,count($relations)-1); 172 $r2 = rand(0,count($relations)-2); 173 174 if ($r2 >= $r1) 175 $r2++; 176 177 $r1 = $relations[$r1]; 178 $r2 = $relations[$r2]; 179 180 return array($r1, $r2); 181 } 182 183 /** Génération d'un nuage pour un mot central. 184 * @param cloudSize : Taille du nuage. 185 * @param sources Les sources. 186 * @param sumWeights La somme des poids. 187 * @return array : Tableau avec comme premier élément le nuage et comme second élément le total de difficulté. 188 */ 189 function cgBuildCloud($centerEid, $cloudSize, $sources, $sumWeights) 190 { 191 $db = getDB(); 192 // On boucle tant qu'il n'y a pas eu au moins 2 sources épuisées 193 $cloud = array(); 194 $nbFailed = 0; 195 $i = 0; 196 $totalDifficulty = 0; 197 198 while ($i < $cloudSize && $nbFailed < 10*$cloudSize) 199 { 200 // On choisit une source aléatoire en tennant compte des poids. 201 $rands = rand(1,$sumWeights); 202 $sumw = 0; 203 if (!isset($sources['rand'])) { 204 break; 205 } 206 $src = $sources['rand']; 207 $srck = 'rand'; 208 209 // Sélection d'une source aléatoire 210 foreach ($sources as $k => $x) 211 { 212 $sumw += $x['w']; 213 214 if ($rands < $sumw) 215 { 216 $src = $x; 217 $srck = $k; 218 break; 219 } 220 } 221 222 // Vérification si on est à la fin du ResultSet de cette source. 223 if ($src['rsPos'] >= $src['rsSize']) 224 { 225 $nbFailed++; 226 227 $sumWeights -= $src['w']; 228 unset($sources[$srck]); 229 230 continue; 231 } 232 233 // On récupère un résultat de cette source. 234 $res = $src['resultSet'][$src['rsPos']]; 235 $sources[$srck]['rsPos']++; 236 237 // On vérifie si le mot n'a pas déjà été sélectionné. 238 $rejected = false; 239 // Ne pas mettre le mot central dans le nuage. 240 if ($res['eid'] == $centerEid) { continue; } 241 $nodeName = $db->querySingle("select name from node where eid=".$res['eid'].";"); 242 if (substr($nodeName, 0, 2) == "::") { continue; } 243 foreach ($cloud as $c) { 244 if ($c['eid'] == $res['eid']) { 245 $nbFailed++; 246 $rejected = true; 247 break; 248 } 249 } 250 if ($rejected) { continue; } 251 252 // position dans le nuage, difficulté, eid, probaR1, probaR2 253 $totalDifficulty += $src['d']; 254 $cloud[$i] = array('pos'=>$i++, 'd'=>$src['d'], 'eid'=>$res['eid'], 'probaR1'=>$res['r1'], 'probaR2'=>$res['r2'], 'probaR0'=>$res['r0'], 'probaTrash'=>$res['trash']); 255 } 256 257 $res = $sources['rand']['resultSet'][0]; 258 259 while ($i < $cloudSize) 260 { 261 $totalDifficulty += $sources['rand']['d']; 262 $cloud[$i] = array('pos'=>$i++, 'd'=>$sources['rand']['d'], 'eid'=>randomCloudNode(), 'probaR1'=>$res['r1'], 'probaR2'=>$res['r2'], 'probaR0'=>$res['r0'], 'probaTrash'=>$res['trash']); 263 } 264 265 return array($cloud, $totalDifficulty); 266 } 267 268 269 function getSequencePlayedGame() { 270 $db = getDB(); 271 $db->exec("INSERT INTO played_game_sequence(id) values(null);"); 272 return -intval($db->lastInsertRowID()); 273 } 274 275 function decodeAndInsertGame($user,$game) { 276 $badWords = Array(); 277 $centerEid = getNodeEid($game['center']); 278 $r1 = $game['relations'][0]; 279 $r2 = $game['relations'][1]; 280 281 if($centerEid === null) 282 $badWords[] = $game['center']; 283 284 foreach($game['cloud'] as $key => $w) { 285 if ($w['name'] == "") continue; 286 $cloudEid = getNodeEid($w['name']); 287 $cloud[] = Array("eid" => $cloudEid, 288 "pos" => $key, 289 "d" => 5, 290 "probaR1" => $w['relations'][0] ? "1" : "0", 291 "probaR2" => $w['relations'][1] ? "1" : "0", 292 "probaR0" => $w['relations'][2] ? "1" : "0", 293 "probaTrash" => $w['relations'][3] ? "1" : "0"); 294 295 if($cloudEid === null) 296 $badWords[] = $w['name']; 297 } 298 299 if(count($badWords) > 0) { 300 echo JSON_encode($badWords); 301 } else if (count($cloud) < 5) { 302 echo JSON_encode(false); 303 } else { 304 insertCreatedGame($centerEid,$cloud,$r1,$r2,10,$user); 305 deleteOneGameCreation($user); 306 echo JSON_encode(true); 307 } 308 } 309 310 function insertCreatedGame($centerEid, $cloud, $r1, $r2, $totalDifficulty, $userName) 311 { 312 $db = getDB(); 313 $sqlUserName = SQLite3::escapeString($userName); 314 // Insère dans la base une partie avec le mot central $centerEid, le nuage $cloud et les relations $r1 et $r2 315 $db->exec("begin transaction;"); 316 $db->exec("INSERT INTO game(gid, eid_central_word, relation_1, relation_2, difficulty, author, nb_like, nb_dislike) 317 VALUES (null, $centerEid, $r1, $r2, $totalDifficulty, '".$sqlUserName."', 0, 0);"); 318 $gid = $db->lastInsertRowID(); 319 320 $t = time(); 321 $pgid1 = getSequencePlayedGame(); 322 $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp, like)" 323 ." VALUES ($pgid1, $gid, '".$sqlUserName."', $t, 0);"); 324 $pgid2 = getSequencePlayedGame(); 325 $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp, like)" 326 ." VALUES ($pgid2, $gid, '".$sqlUserName."', $t, 0);"); 327 $pgid0 = getSequencePlayedGame(); 328 $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp, like)" 329 ." VALUES ($pgid0, $gid, '".$sqlUserName."', $t, 0);"); 330 $pgidT = getSequencePlayedGame(); 331 $db->exec("INSERT INTO played_game(pgid, gid, login, timestamp, like)" 332 ." VALUES ($pgidT, $gid, '".$sqlUserName."', $t, 0);"); 333 334 // TODO : R0 et Trash + corrections 335 foreach ($cloud as $c) 336 { 337 $totalWeight = $c['probaR1'] + $c['probaR2'] + $c['probaR0'] + $c['probaTrash']; 338 $db->exec("INSERT INTO game_cloud(gid, num, difficulty, eid_word, totalWeight, probaR1, probaR2, probaR0, probaTrash) 339 VALUES ($gid, ".$c['pos'].", ".$c['d'].", ".$c['eid'].", $totalWeight, ".$c['probaR1'].", ".$c['probaR2'].", ".$c['probaR0'].", ".$c['probaTrash'].");"); 340 341 $db->exec("INSERT INTO played_game_cloud(pgid, login, gid, type, num, relation, weight, score) 342 VALUES ($pgid1, '".$sqlUserName."', $gid, 0, ".$c['pos'].", $r1, ".$c['probaR1'].", 0);"); 343 344 $db->exec("INSERT INTO played_game_cloud(pgid, login, gid, type, num, relation, weight, score) 345 VALUES ($pgid2, '".$sqlUserName."', $gid, 0, ".$c['pos'].", $r2, ".$c['probaR2'].", 0);"); 346 347 $db->exec("INSERT INTO played_game_cloud(pgid, login, gid, type, num, relation, weight, score) 348 VALUES ($pgid0, '".$sqlUserName."', $gid, 0, ".$c['pos'].", 0, ".$c['probaR0'].", 0);"); 349 350 $db->exec("INSERT INTO played_game_cloud(pgid, login, gid, type, num, relation, weight, score) 351 VALUES ($pgidT, '".$sqlUserName."', $gid, 0, ".$c['pos'].", -1, ".$c['probaTrash'].", 0);"); 352 } 353 354 $db->exec("commit;"); 355 } 356 357 /** Retourne un identifiant de partie aléatoire de la liste de parties jouables 358 * @return gid : Identifiant de partie. 359 */ 360 function randomGameCore() { 361 $db = getDB(); 362 363 $q = $db->query('SELECT gid,(nb_like - nb_dislike + 5 * (author != "bot") + 5) AS coef FROM game'); 364 365 $game = array(); 366 $sum = 0; 367 while($g = $q->fetchArray()) { 368 if($g['coef'] > 0) { 369 $game[] = $g; 370 $sum += $g['coef']; 371 } 372 } 373 374 $randomValue = rand(0,$sum-1); 375 $count = 0; 376 foreach($game as $g) { 377 $count += $g['coef']; 378 379 if($count >= $randomValue) 380 return $g['gid']; 381 } 382 383 return null; 384 385 // Ancienne requêtes de sélection. 386 //return $db->querySingle("select gid from game where gid = (abs(random()) % (select max(gid) from game))+1 or gid = (select max(gid) from game where gid > 0) order by gid limit 1;"); 387 } 388 389 /** Sélection aléatoire d'une partie de la base de données parmis les parties à jouer. 390 * @return gid : Identifiant de la partie selectionnée. 391 */ 392 function randomGame() 393 { 394 $gid = randomGameCore(); 395 396 if ($gid === null) { 397 // TODO : séparer ces créations de parties dans une fonction qui détecte le mode toussa. 398 for ($i = 0; $i < 100; $i++) 399 createGameCore(rand(5,15)); 400 401 $gid = randomGameCore(); 402 403 if ($gid === null) 404 throw new Exception("Erreur lors de la récupération de la partie. Vérifiez qu'il y a au moins une partie.", 6); 405 } 406 return $gid; 407 } 408 409 /** Formatage des mots lorsqu'il y a des généralisations/spécifications par le symbole ">". 410 * @param word : Le mot que l'on veut reformater. 411 * @return word : le mot formaté. 412 */ 413 function formatWord($word) { 414 $db = getDB(); 415 $res = ""; 416 $stack = array(); 417 418 while (($pos = strpos($word, ">")) !== false) { 419 $res .= substr($word,0,$pos) . " ("; 420 $eid = intval(substr($word,$pos+1)); 421 if ($eid == 0) { throw new Exception("Erreur lors du suivi des pointeurs de spécialisation du mot $word.", 7); } 422 if (in_array($eid, $stack)) { throw new Exception("Boucle rencontrée lors du suivi des pointeurs de spécialisation du mot $word.", 8); } 423 if (count($stack) > 10) { throw new Exception("Trop de niveaux de récursions lors du suivi des pointeurs de spécialisation du mot $word.", 9); } 424 $stack[] = $eid; 425 $word = $db->querySingle("select name from node where eid = $eid"); 426 } 427 428 $res .= $word; 429 430 for ($depth = count($stack); $depth > 0; $depth--) 431 $res .= ')'; 432 433 return $res; 434 } 435 436 function makePlayedGameRow($user, $gid, $pgid) { 437 // TODO : planter si la requête suivante échoue pour quelque raison que ce soit. 438 getDB()->exec("INSERT INTO played_game(pgid, gid, login, timestamp, like) VALUES (".longStrVal($pgid).", ".intval($gid).", '".SQLite3::escapeString($user)."', -1, 0);"); 439 } 440 441 function getGidFromPgid($user, $pgid) { 442 return getDB()->querySingle("SELECT gid FROM played_game WHERE pgid = ".longStrVal($pgid)." and login = '".SQLite3::escapeString($user)."';"); 443 } 444 445 function makePgid($user, $gid = null, $pgid = null) { 446 if ($gid === null && $pgid === null) { 447 $gid = randomGame(); 448 $pgid = getSequencePlayedGame(); 449 makePlayedGameRow($user, $gid, $pgid); 450 } elseif ($gid === null) { 451 // On essaie de voir si ce pgid est déjà connu. 452 $gid = getGidFromPgid($user, $pgid); 453 if ($gid === null) { 454 $gid = randomGame(); 455 makePlayedGameRow($user, $gid, $pgid); 456 } 457 } elseif ($pgid === null) { 458 $pgid = getSequencePlayedGame(); 459 makePlayedGameRow($user, $gid, $pgid); 460 } else { 461 if ($gid != getGidFromPgid($user, $pgid)) throw new Exception("Cette partie n'est pas associée à votre nom d'utilisateur.", 4); 462 } 463 return array($gid, $pgid); 464 } 465 466 /** Formate une partie en JSON en l'imprimant. 467 * @param user : l'utilisateur. 468 * @param gameId : L'identifiant d'une partie. 469 */ 470 function game2array($user, $pgid = null) 471 { 472 global $stringRelations; 473 $db = getDB(); 474 475 list($gid, $pgid) = makePgid($user, null, $pgid); 476 477 // TODO Yoann : faire des tests d'erreur pour ces select ? 478 $game = $db->query("select author, gid, (select name from node where eid = eid_central_word) as name_central_word, eid_central_word, relation_1, relation_2 from game where gid = ".$gid.";"); 479 $game = $game->fetchArray(); 480 481 $ret = array( 482 "author" => $game['author'], 483 "gid" => $gid, 484 "pgid" => $pgid, 485 "relations" => array( 486 array("id" => $game['relation_1'], "name" => ''.$stringRelations[$game['relation_1']]), 487 array("id" => $game['relation_2'], "name" => ''.$stringRelations[$game['relation_2']]), 488 array("id" => 0, "name" => ''.$stringRelations[0]), 489 array("id" => -1, "name" => ''.$stringRelations[-1]) 490 ), 491 "center" => array("id" => $game['eid_central_word'], "name" => formatWord($game['name_central_word'])), 492 "cloud" => array() 493 ); 494 495 $res = $db->query("select eid_word,(select name from node where eid=eid_word) as name_word from game_cloud where gid = ".$gid.";"); 496 while ($x = $res->fetchArray()) 497 { 498 $ret['cloud'][] = array("id" => $x['eid_word'], "name" => ''.formatWord($x['name_word'])); 499 } 500 return $ret; 501 } 502 503 /** Création d'un lot de parties suivant un mode donnée. 504 * @param nbParties : le nombre de parties à créer. 505 * @param mode : Le mode de jeu pour les parties à créer. 506 */ 507 function createGame($nbParties, $mode) 508 { 509 for ($i = 0; $i < $nbParties; $i++) 510 createGameCore(rand(5,15)); 511 } 512 513 /** Génère une partie (mode normal pour l'instant) pour une certaine taille de nuage. 514 * @param cloudSize : Taille du nuage. 515 * 516 * Est appelée par randomGame(), donc il faudra adapter quand on aura plusieurs modes, par exemple en ayant une fonction intermédiaire qui puisse être appelée par createGame et randomGame. 517 */ 518 function createGameCore($cloudSize) 519 { 520 // select random node 521 $centerEid = randomCenterNode(); 522 523 $r1 = cgChooseRelations(); $r2 = $r1[1]; $r1 = $r1[0]; 524 $sources = cgBuildResultSets($cloudSize, $centerEid, $r1, $r2); $sumWeights = $sources[1]; $sources = $sources[0]; 525 $cloud = cgBuildCloud($centerEid, $cloudSize, $sources, $sumWeights); $totalDifficulty = $cloud[1]; $cloud = $cloud[0]; 526 insertCreatedGame($centerEid, $cloud, $r1, $r2, $totalDifficulty, 'bot'); 527 } 528 529 function computeScore($probas, $difficulty, $answer, $userReputation) { 530 // Calcul du score. Score = proba[réponse de l'utilisateur]*coeff1 - proba[autres reponses]*coeff2 + bonus 531 // score = - proba[autres reponses]*coeff2 532 // On aura donc -5 <= score <= 0 533 $score = -5 * (($probas[0] + $probas[1] + $probas[2] + $probas[3]) - $probas[$answer]); 534 535 // score = proba[réponse de l'utilisateur]*coeff1 - proba[autres reponses]*coeff2 536 // On aura donc -5 <= score <= 10 537 $score += 10 * $probas[$answer]; 538 539 // On est indulgent si la réponse est 3 (poubelle) : 540 if ($answer == 3 && $score < 0) { 541 $score = $score / 2; 542 } 543 544 // Adapter le score en fonction de la réputation de l'utilisateur (quand il est jeune, augmenter le score pour le motiver). 545 // On aura donc -5 <= score <= 15 546 if ($score > 3) { 547 $score += max(0, min(5, 5 - $userReputation)); 548 } 549 550 return round($score); 551 } 552 553 /** Calcul de la réputation de l'utilisateur selon son score. 554 * @param score : Le score du joueur. 555 */ 556 function computeUserReputation($score) { 557 return max(round(log(max($score/10,1))*100)/100, 0); 558 } 559 560 /** Formatage des probalitées dans un tableau. 561 * @param row : le vecteur de probabilités. 562 * @return array : Le vecteur de probabilités normalisé. 563 */ 564 function normalizeProbas($row) { 565 return array($row['probaR1']/$row['totalWeight'], $row['probaR2']/$row['totalWeight'], $row['probaR0']/$row['totalWeight'], $row['probaTrash']/$row['totalWeight']); 566 } 567 568 /** Insertion des données d'une partie joué par un utilisateur. 569 * @param user : L'identifiant de l'utilisateur ayant joué à la partie. 570 * @param pgid : L'identifiant de la partie jouée. 571 * @param gid : L'identifiant du jeu auquel la partie appartient. 572 * @return score : Le score réalisé par le joueur. 573 */ 574 function setGame($user, $pgid, $gid, $answers) 575 { 576 $db = getDB(); 577 578 if ('ok' !== $db->querySingle("SELECT 'ok' FROM played_game WHERE pgid = ".longStrVal($pgid)." and gid = ".intval($gid)." and login = '".SQLite3::escapeString($user)."' and timestamp = -1;")) { 579 return getGameScores($user, $pgid, $gid); 580 } 581 582 $userReputation = computeUserReputation($db->querySingle("SELECT score FROM user WHERE login = '".SQLite3::escapeString($user)."';")); 583 584 $db->exec("begin transaction;"); 585 $db->exec("update played_game set timestamp = ".time()." where pgid = ".longStrVal($pgid)." and login = '".SQLite3::escapeString($user)."';"); 586 587 $r0 = 0; 588 $trash = -1; 589 $r1 = $db->querySingle("SELECT relation_1, relation_2 FROM game WHERE gid = ".intval($gid).";", true); 590 $r2 = $r1['relation_2']; 591 $r1 = $r1['relation_1']; 592 $res = $db->query("SELECT num, difficulty, totalWeight, probaR1, probaR2, probaR0, probaTrash FROM game_cloud WHERE gid = ".intval($gid).";"); 593 $gameScore = 0; 594 $scores = array(); 595 596 while ($row = $res->fetchArray()) 597 { 598 $num = intval($row['num']); 599 if (!isset($answers[$num])) { 600 throw new Exception("Cette requête \"Set partie\" ne donne pas de réponse (une relation) pour le mot numéro ".($num+1)." de la partie.", 5); 601 } 602 $relanswer = intval($answers[$num]); 603 604 switch ($relanswer) 605 { 606 case $r1: $answer = 0; $probaRx = "probaR1"; break; 607 case $r2: $answer = 1; $probaRx = "probaR2"; break; 608 case $r0: $answer = 2; $probaRx = "probaR0"; break; 609 case $trash: $answer = 3; $probaRx = "probaTrash"; break; 610 default: throw new Exception("Réponse ($relanswer) invalide pour le mot $num. Les réponses possibles sont : $r1, $r2, $r0, $trash", 5); 611 } 612 613 $wordScore = computeScore(normalizeProbas($row), $row['difficulty'], $answer, $userReputation); 614 $gameScore += $wordScore; 615 $scores[$num] = $wordScore; 616 617 $db->exec("insert into played_game_cloud(pgid, login, gid, type, num, relation, weight, score)" 618 ." values(".longStrVal($pgid).", '".SQLite3::escapeString($user)."', ".intval($gid).", 1, $num, $r1, ".$userReputation.", ".$wordScore.");"); 619 $db->exec("update game_cloud set $probaRx = $probaRx + ".max(min($userReputation/2,5),0)." where gid = ".intval($gid)." and num = $num;"); 620 $db->exec("update game_cloud set totalWeight = totalWeight + ".max(min($userReputation/2,5),0)." where gid = ".intval($gid)." and num = $num;"); 621 } 622 $db->exec("update user set score = score + ".$gameScore." where login = '$user';"); 623 624 $db->exec("commit;"); 625 626 return array( 627 'scoreTotal' => $gameScore, 628 'alreadyPlayed' => false, 629 'scores' => $scores 630 ); 631 } 632 633 function getGameScores($user, $pgid, $gid) { 634 $db = getDB(); 635 $timestamp = $db->querySingle("SELECT timestamp FROM played_game WHERE pgid = ".longStrVal($pgid)." and $gid = ".intval($gid)." and login = '".SQLite3::escapeString($user)."';"); 636 if ($timestamp == -1) { 637 throw new Exception("Cette partie n'a jamais été jouée.", 4); // TODO : code d'erreur en doublon avec celui ci-dessous. 638 } elseif ($timestamp == null) { 639 throw new Exception("Cette partie n'est pas associée à votre nom d'utilisateur.", 4); 640 } 641 642 $gameScore = 0; 643 $scores = array(); 644 $res = $db->query("SELECT num, score from played_game_cloud where pgid = ".longStrVal($pgid)." and gid = ".intval($gid)." and login = '".SQLite3::escapeString($user)."';"); 645 while ($row = $res->fetchArray()) 646 { 647 $gameScore += $row['score']; 648 $scores[$row['num']] = $row['score']; 649 } 650 return array( 651 'scoreTotal' => $gameScore, 652 'alreadyPlayed' => true, 653 'scores' => $scores 654 ); 655 } 656 657 /** Fourni l'ensembles des relations pouvant apparaître dans le jeu. 658 * @return JSON type et nom des relations. 659 */ 660 function getGameRelations() { 661 $json = "{"; 662 global $stringRelations; 663 664 foreach($stringRelations as $id=>$description) 665 if($id == -1) 666 $json .= '"'.$id.'":"'.$description.'"'; 667 else 668 $json .= ',"'.$id.'":"'.$description.'"'; 669 670 $json .= '}'; 671 672 return $json; 673 } 674 675 function setGameGetScore($user, $pgid, $answers) { 676 $scores = setGame($user, $pgid, getGidFromPgid($user, $pgid), $answers); 677 $g = game2array($user, $pgid); 678 $s = array(); 679 foreach ($g['cloud'] as $k => $v) { 680 $s[] = array('name' => $v['name'], 'score' => $scores['scores'][$k]); 681 } 682 $scores['scores'] = $s; 683 $scores['author'] = $g['author']; 684 // On renvoie une nouvelle partie pour garder le client toujours bien alimenté. 685 $scores['newGame'] = game2array($user); 686 $scores['minScore'] = -5; 687 $scores['maxScore'] = 10; 688 echo JSON_encode($scores); 689 } 690 691 692 /** retourne l'eid d'un mot. 693 * @param node : le mot dont on veut obtenir l'eid. 694 */ 695 function getNodeEid($node) { 696 $db = getDB(); 697 698 return $db->querySingle("SELECT eid FROM node WHERE name='".SQLite3::escapeString($node)."';"); 699 } 700 701 /* Permet de déterminer si le mot existe dans la base de données ou non. 702 * @param node : Le mot dont on veut connaître l'existance. 703 * @return char : "0" ou "1". 704 */ 705 function wordExist($node) { 706 $db = getDB(); 707 708 return getNodeEid($node) === null ? "0" : "1"; 709 } 710 711 function getUserInfo($user, $key) { 712 return getdb()->querySingle("SELECT value FROM user_info WHERE user = '".sqlite3::escapestring($user)."' AND key = '".SQLite3::escapeString($key)."';"); 713 } 714 715 function setUserInfo($user, $key, $value) { 716 getDB()->exec("INSERT OR REPLACE INTO user_info(user, key, value) values('".SQLite3::escapeString($user) 717 ."', '".SQLite3::escapeString($key) 718 ."', '".SQLite3::escapeString($value)."');"); 719 } 720 721 function userPrefsDefaults() { 722 return Array( 723 "theme" => "green" 724 ); 725 } 726 727 function userPrefs($user) { 728 $res = userPrefsDefaults(); 729 foreach ($res as $k => &$v) { 730 $x = getUserInfo($user, $k); 731 if ($x !== null) $v = $x; 732 } 733 $res['connected'] = true; 734 echo JSON_encode($res); 735 } 736 737 function setUserPref($user, $key, $value) { 738 if (array_key_exists($key, userPrefsDefaults())) { 739 setUserInfo($user, $key, $value); 740 } 741 } 742 743 function getJAimePgid($user, $pgid) { 744 getDB()->querySingle("SELECT like FROM played_game WHERE login = '".SQLite3::escapeString($user)."' and pgid = ".longStrVal($pgid).";"); 745 } 746 747 function setJAimePgid($user, $pgid, $value) { 748 $value = intval($value) / abs(intval($value) || 1); // Calcule le signe de $value : -1 ou 0 ou 1 749 $original = getJAimePgid($user, $pgid); 750 getDB()->querySingle("UPDATE played_game SET like = ".$value." WHERE login = '".SQLite3::escapeString($user)."' and pgid = ".longStrVal($pgid).";"); 751 $gid = getGidFromPgid($user, longStrVal($pgid)); 752 getDB()->querySingle("UPDATE game SET nb_like = nb_like".($original == 1 ? " - 1" : "").($value == 1 ? " + 1" : "")." WHERE gid = ".intval($gid).";"); 753 getDB()->querySingle("UPDATE game SET nb_dislike = nb_dislike".($original == -1 ? " - 1" : "").($value == -1 ? " + 1" : "")." WHERE gid = ".intval($gid).";"); 754 } 755 756 function addGameCreationBonus($user, $nb) { 757 if($nb > 0) { 758 getDB()->exec('UPDATE user SET cgCount = cgCount + '.$ng.' WHERE login=\''.$user.'\''); 759 return true; 760 } 761 762 return false; 763 } 764 765 function deleteOneGameCreation($user) { 766 if(getNbGameCreationRemained($user) > 0) 767 getDB()->exec('UPDATE user SET cgCount=cgCount-1 WHERE login=\''.$user.'\''); 768 } 769 770 function getNbGameCreationRemained($user) { 771 return getDB()->querySingle('SELECT cgCount FROM user WHERE login=\''.$user.'\''); 772 } 773 774 ?>