public function inscription(\Slim\Slim $app)
{
$allPostVars = $app->request->post();
$username = $allPostVars['username'];
$mail = $allPostVars['mail'];
$mdp = $allPostVars['mdp'];
try {
$db = getDB();
$verif = $db->prepare("SELECT username \n\t\t\t\tFROM users\n\t\t\t\tWHERE username = :username");
$verif->bindParam(':username', $username, PDO::PARAM_INT);
$verif->execute();
$usernamed = $verif->fetch(PDO::FETCH_OBJ);
$verif->closeCursor();
if ($usernamed) {
$answer = "Ce nom d'utilisateur est déjà pris, merci de retenter avec un nouveau.";
} else {
$sth = $db->prepare("INSERT INTO users \n\t\t\t\t(username, mail, mdp)\n\t\t\t\tVALUES (:username, :mail, :mdp)");
$sth->bindParam(':username', $username, PDO::PARAM_INT);
$sth->bindParam(':mail', $mail, PDO::PARAM_INT);
$sth->bindParam(':mdp', $mdp, PDO::PARAM_INT);
$sth->execute();
$answer = array("status" => "success", "code" => 1);
}
$app->response->setStatus(200);
$app->response()->headers->set('Content-Type', 'application/json');
echo json_encode($answer);
$db = null;
} catch (PDOException $e) {
$app->response()->setStatus(404);
echo '{"error":{"text":' . $e->getMessage() . '}}';
}
}
function makeArticleEasy($article)
{
log_info($article);
$conn = getDB();
$words = explode(" ", $article);
$outp = "";
$array = array();
foreach ($words as $word) {
$word = str_replace('"', "", $word);
$word = str_replace("'", "", $word);
$word = str_replace(" ", "", $word);
$word = str_replace(".", "", $word);
$word = str_replace(",", "", $word);
$word = str_replace(":", "", $word);
$word = str_replace("\\", "", $word);
if (empty($word) || strlen($word) < 3) {
continue;
}
$meaning = '';
$gender = '';
$partOfSpeech = '';
//check if the meaning exist in DB
$result = mysqli_query($conn, "SELECT * FROM words WHERE word = '" . $word . "'");
if (mysqli_num_rows($result) == 0) {
// row not found, get from online dicts
$link = "http://de-en.dict.cc/?s=" . utf8_encode($word);
$meaning = getMeaningFromOnlinDict($link);
if (!empty($meaning)) {
insertWord($conn, $word, $meaning, null, null, 0);
} else {
continue;
}
} else {
//take db value
$row = mysqli_fetch_row($result);
//var_dump($row);
$meaning = $row[2];
$partOfSpeech = $row[3];
$gender = $row[4];
}
if (empty($meaning)) {
continue;
}
//now return as json object
// if ($outp != "") {$outp .= ",";}
$a = new Article();
$a->word = $word;
$a->meaning = $meaning;
array_push($array, $a);
// $outp .= '{"word":"' . $word . '",';
//$outp .= '"meaning":"' . $meaning . '",';
// $outp .= '"meaning":"' . $meaning. '"}';
// $outp .= '"partOfSpeech":"' . $partOfSpeech . '",';
// $outp .= '"gender":"' . $gender . '"}';
}
//forloop
//$outp ='{"words":['.$outp.']}';
echo json_encode($array);
$conn->close();
}
public function testDataType()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE copy_from_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
$this->markTestSkipped("Create table for copy_from_test failed");
return;
}
$data = [];
for ($i = 0; $i < 100; $i++) {
if ($i != 50) {
$data[] = "text_{$i}\t{$i}";
} else {
$data[] = "text_{$i}\t\\N";
}
}
if (!file_put_contents(__TESTS_TEMP_DIR__ . '/copy_from_test.csv', implode("\n", $data))) {
$this->markTestSkipped("Unable to save the file needed for copy from test");
return;
}
$this->assertTrue(getDB()->copyFromFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_from_test.csv', null, null, ['field1', 'field2']));
$r = getDB()->query("SELECT count(field1) AS f1, count(field2) AS f2 FROM copy_from_test")->fetch(PDO::FETCH_ASSOC);
$this->assertEquals($r['f1'], 100);
$this->assertEquals($r['f2'], 99);
}
public function __set($name, $value)
{
if (getUser()->data->id != $this->data->owner && getUser()->data->access_level != 0) {
return;
}
switch ($name) {
case 'name':
case 'mime':
getDB()->query('setFile', array('id' => $this->fid, 'field' => $name, 'value' => $value));
$this->data->{$name} = $value;
break;
case 'list':
getDB()->query('setFile', array('id' => $this->fid, 'field' => $name, 'value' => $value));
$this->data = getDB()->query('refreshData', array('pid' => $this->pid));
$this->data = $this->data->dataObj;
break;
case 'file':
if (!is_file(SYS_TMP . $file)) {
$this->throwError('$value isn`t a file', $value);
}
getRAR()->execute('moveFile', array('source' => SYS_TMP . $value, 'destination' => SYS_SHARE_PROJECTS . $this->fid));
break;
default:
return;
}
}
function createUser()
{
$sql = "INSERT INTO users(nombre, email, telefono, foto_perfil, rol, password, poblacion, pais) VALUES(:nombre, :email, :telefono, :foto_perfil, :rol, :password, :poblacion, :pais)";
$request = \Slim\Slim::getInstance()->request();
$user = $request->params();
$x1 = $user["nombre"];
$x2 = $user["email"];
$x3 = $user["telefono"];
$x4 = $user["foto_perfil"];
$x5 = $user["rol"];
$x6 = $user["pass"];
$x7 = $user["poblacion"];
$x8 = $user["pais"];
/*try {*/
$dbh = getDB();
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':nombre', $x1);
$stmt->bindParam(':email', $x2);
$stmt->bindParam(':telefono', $x3);
$stmt->bindParam(':foto_perfil', $x4);
$stmt->bindParam(':rol', $x5);
$stmt->bindParam(':password', $x6);
$stmt->bindParam(':poblacion', $x7);
$stmt->bindParam(':pais', $x8);
$stmt->execute();
/*$dbh=null;*/
/*echo json_encode($user);
} catch (PDOException $e) {
echo "Error";
}*/
}
function login()
{
$sql = "SELECT user_id, username, name, password FROM users WHERE username = ?";
try {
$body = getBody();
$db = getDB();
$stmt = $db->prepare($sql);
$stmt->execute(array($body->username));
$users = $stmt->fetchAll(PDO::FETCH_OBJ);
$db = null;
if (sizeof($users) > 0) {
$user = reset($users);
$salt = substr($user->password, 0, 10);
$password = substr($user->password, 10);
if ($password == hash('sha256', $salt . $body->password)) {
$_SESSION["user"]["isLoggedIn"] = true;
$_SESSION["user"]["id"] = $user->user_id;
$_SESSION["user"]["username"] = $user->username;
$_SESSION["user"]["name"] = $user->name;
echo '{"success": true, "user": '******'}';
} else {
echo '{"success": false, "message": "Wrong password."}';
}
} else {
echo '{"success": false, "message": "Username not found."}';
}
} catch (PDOException $e) {
//error_log($e->getMessage(), 3, '/var/tmp/phperror.log'); //Write error log
echo '{"error":{"message":' . $e->getMessage() . '}}';
}
}
/**
* @param string $requiredPgSqlVersion
* @return bool
*/
function skipTest($requiredPgSqlVersion = '0.0')
{
if (is_null(getDB())) {
return true;
}
return defined('__PGSQL_VERSION__') && __PGSQL_VERSION__ < $requiredPgSqlVersion;
}
public function testQuery()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE test_query(int_field INTEGER NOT NULL, ts_field TIMESTAMP)") === false) {
$this->markTestSkipped("Create table for testing query method of PDO");
}
$sampleData = [];
for ($i = 0; $i < 3; $i++) {
$sampleData[] = [':int' => $i, ':datetime' => (new \DateTime("2015-09-01 00:00:00"))->add(new DateInterval("P{$i}D"))];
}
$s = getDB()->prepare("INSERT INTO test_query(int_field, ts_field) VALUES(:int, :datetime)");
for ($i = 0; $i < 3; $i++) {
$s->bindParam(":int", $sampleData[$i][':int']);
$s->bindParam(":datetime", $sampleData[$i][':datetime'], PDO::PARAM_DATETIME);
$this->assertEquals(1, $s->execute());
}
$s = getDB()->query("SELECT * FROM test_query");
$s->setColumnTypes(['ts_field' => PDO::PARAM_DATETIME]);
$idx = 0;
foreach ($s as $rIdx => $r) {
$this->assertEquals($sampleData[$idx][':int'], $r['int_field']);
$this->assertEquals($sampleData[$idx][':datetime'], $r['ts_field']);
$idx++;
}
$this->assertEquals(3, $idx);
}
function existingEmail($emailToCheck, $keyToCheck, $dbSheetToCheck)
{
$db = getDB();
//get list of sites to compare with site entered
$stmt = $db->prepare("SELECT * FROM {$dbSheetToCheck}");
$contentsOfDB = array();
$newArrayToCheck = array();
if ($stmt->execute() && $stmt->rowCount() > 0) {
$contentsOfDB = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// //print entire array with keys for testing purposes
// $keys = array_keys($contentsOfDB);
// for($i = 0; $i < count($contentsOfDB); $i++)
// {
// echo $keys[$i] . "{<br>";
// foreach($contentsOfDB[$keys[$i]] as $key => $value)
// {
// echo $key . " : " . $value . "<br>";
// }
// echo "}<br>";
// }
for ($i = 0; $i < count($contentsOfDB); $i++) {
$newArrayToCheck[$i] = $contentsOfDB[$i][$keyToCheck];
}
if (in_array($emailToCheck, $newArrayToCheck) == false) {
return false;
} else {
return true;
}
}
/** Schliesst Registrierungsprozess ab.
*
* @param integer $id Registrierungs-ID
* @param string $hash Registrierungshash
* @param string $username Gewünschter Benutzername
* @param string $password Passwort
* @return mixed true bei Erfolg oder String mit Fehlermeldung, false falls
* Datenbankabfrage fehlschlug
* @author Cédric Neukom
*/
public function completeRegistration($id, $hash, $username, $password)
{
// Falls login durchgeführt werden kann abbrechen
if (!$this->permitted) {
return $this->throwError('You\'re already logged in. You can\'t register a new account.');
}
$db = getDB();
// Registrierung validieren
$id = intval($id);
$reg = $db->query('getRegistrationByID', array('id' => $id));
if (!$reg || !$reg->dataLength) {
return $this->throwError('Your registration could not be found.');
}
if ($reg->dataObj->password !== $hash) {
return $this->throwError('Your registration could not be found.');
}
// Benutzername prüfen
if (strlen($username) < SYS_USERNAME_MINLENGTH || strlen($username) > SYS_USERNAME_MAXLENGTH) {
return $this->throwError('The length of your username must be between ' . SYS_USERNAME_MINLENGTH . ' and ' . SYS_USERNAME_MAXLENGTH . ' characters.');
}
$names = $db->query('getUser', array('user' => $username));
if (!$names || $names->dataLength) {
return $this->throwError('The username you chose is already taken.');
}
// Passwort prüfen
if (strlen($password) < SYS_PASSWORD_MINLENGTH || strlen($password) > SYS_PASSWORD_MAXLENGTH) {
return $this->throwError('The length of you password must be between ' . SYS_PASSWORD_MINLENGTH . ' and ' . SYS_PASSWORD_MAXLENGTH . ' characters.');
}
// Registrierung abschliessen
return $db->query('registerUser', array('pwd' => self::hashPwd($password), 'name' => $username, 'id' => $id));
}
function getArticulosByCodgru($arCodgru)
{
global $app;
// $app = \Slim\Slim::getInstance();
try {
$db = getDB();
$sth = $db->prepare("SELECT * FROM articulos WHERE ArCodgru=:arCodgru ORDER BY ArCodgru");
$sth->bindParam(':arCodgru', $arCodgru, PDO::PARAM_STR);
$result = $sth->execute();
//$articulos = 1;
$page = $app->request->get('page', 1);
$pageSize = $app->request->get('size', 1000);
if ($result) {
$app->response->setStatus(200);
$app->response()->headers->set('Content-Type', 'application/json');
$inicio = $page * $pageSize - $pageSize;
$tope = $page * $pageSize;
$i = 0;
while (($articulo = $sth->fetch(PDO::FETCH_OBJ)) && $i < $tope) {
if ($i >= $inicio) {
echo json_encode($articulo);
}
$i++;
}
$db = null;
} else {
throw new PDOException('No records found.');
}
} catch (PDOException $e) {
$app->response()->setStatus(404);
echo '{"error":{"text":' . $e->getMessage() . '}}';
}
}
public function testDataType()
{
if (skipTest('9.3')) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE json(field JSON NOT NULL)") === false) {
$this->markTestSkipped("Create table with json data type failed");
}
$s = getDB()->prepare("INSERT INTO json VALUES(:json)");
$this->assertInstanceOf('\\PgBabylon\\PDOStatement', $s, "Asserting pdo::prepare returns a pgbabylon statement");
$p = ["key_1" => "val_1", "key_2" => 2];
$s->bindParam(":json", $p, PDO::PARAM_JSON);
$r = $s->execute();
$this->assertTrue($r, "Testing json insert using PHP array");
$s = getDB()->prepare("SELECT field AS json_col FROM json");
$s->bindColumn("json_col", $val, PDO::PARAM_JSON);
$r = $s->execute();
$this->assertTrue($r, "Testing json select");
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json");
$this->assertSame($p, $val, "Asserting fetch sets the previously bound variable as json");
$s = getDB()->prepare("SELECT field AS json_col FROM json", PDO::AUTO_COLUMN_BINDING);
$r = $s->execute();
$this->assertTrue($r, "Testing json select with auto column binding");
$r = $s->fetch(PDO::FETCH_ASSOC);
$this->assertSame(['json_col' => $p], $r, "Asserting fetch return an array after deserializing the pgsql json with auto column binding");
$p = ["key_2" => "val_2", "key_3" => 3];
$s = getDB()->prepare("INSERT INTO json VALUES(:json)");
$r = $s->execute([':json' => PgBabylon\DataTypes\JSON($p)]);
$this->assertTrue($r, "Testing json insert using PHP array directly in execute");
}
public function testDataType()
{
if (skipTest()) {
$this->markTestSkipped();
return;
}
if (getDB()->exec("CREATE TABLE copy_to_test(field1 TEXT NOT NULL, field2 INTEGER, field3 DATE)") === false) {
$this->markTestSkipped("Create table for copy_to_test failed");
return;
}
$data = [];
$s = getDB()->prepare("INSERT INTO copy_to_test(field1,field2) VALUES (:field1, :field2)");
for ($i = 0; $i < 100; $i++) {
if ($i != 50) {
$data[] = "text_{$i}\t{$i}";
$s->execute([":field1" => "text_{$i}", ":field2" => $i]);
} else {
$data[] = "text_{$i}\t\\N";
$s->execute([":field1" => "text_{$i}", ":field2" => null]);
}
}
$this->assertTrue(getDB()->copyToFile("copy_from_test", __TESTS_TEMP_DIR__ . '/copy_to_test.csv', null, null, ['field1', 'field2']));
if (!($f = file_get_contents(__TESTS_TEMP_DIR__ . '/copy_to_test.csv'))) {
$this->markTestSkipped("Unable to load the file needed for copy to test");
return;
}
$this->assertSame(implode("\n", $data) . "\n", $f);
}
function deleteArticle($articleId)
{
$conn = getDB();
$query = "DELETE FROM Articles WHERE articleId = " . $articleId . " limit 1";
$result = mysqli_query($conn, $query);
$conn->close();
}
function getExpenseReviewForm($CategoryParentType, $CategoryParentOrder)
{
$ResultsToReturn = getDB($CategoryParentType, $CategoryParentOrder);
?>
<hr><h4 class="panel-title col-sm-5"><?php
echo substr($ResultsToReturn[0]["categoryParentName"], 7);
?>
</h4>
<div class="col-sm-1">Client</div>
<div class="col-sm-1">Spouse</div>
<div class="col-sm-1">Total</div>
<div class="col-sm-1">Actual</div>
<div class="col-sm-1">Difference</div>
<div class="panel-body">
<?php
$subtotal = 0;
foreach ($ResultsToReturn as $row) {
$total = $row["budgetSelfAmount"] + $row["budgetSpouseAmount"];
$subtotal += $total;
$GLOBALS['totalExpense'] += $total;
if ($total > 0.0) {
?>
<div class = "row">
<div class="col-sm-5"><span><?php
echo $row["categoryName"];
?>
</span></div>
<div class = "col-sm-1"><span>$ <?php
echo number_format($row["budgetSelfAmount"]);
?>
</span></div>
<div class = "col-sm-1"><span>$ <?php
echo number_format($row["budgetSpouseAmount"]);
?>
</span></div>
<div class = "col-sm-1"><span>$ <?php
echo number_format($total);
?>
</span></div>
<div class="col-sm-1">_________</div>
<div class="col-sm-1">_________</div>
</div>
<?php
}
}
?>
<div class="row">
<div class="col-sm-7"><h5><u>Total</u></h5></div>
<div class="col-sm-1"><u>$ <?php
echo number_format($subtotal);
?>
</u></div>
<div class="col-sm-2"><u><?php
echo number_format($subtotal / $GLOBALS['grossIncome'] * 100);
?>
 % gross income</u></div>
</div>
</div>
<?php
}
function logError($err)
{
print $err;
$sth = getDB()->prepare("INSERT INTO upload_tracking (upload_id,file_id,error,error_message) VALUES (?,?,'t',?)");
$sth->execute(array($_POST["UPLOAD_IDENTIFIER"], 6, $err));
exit;
}
function insertarUser()
{
//echo "Insertar usuario";
$request = \Slim\Slim::getInstance()->request();
$user = $request->params();
/*var_dump($user);
die;*/
$v1 = $user["nickname"];
$v2 = $user["email"];
$v3 = $user["nombre"];
$v4 = $user["apellidos"];
$v5 = $user["contra"];
$v6 = $user["telefono"];
$v7 = $user["ciudad"];
$sql = "INSERT INTO usuarios (nickname, email, nombre, apellidos, contra, telefono, ciudad) VALUES (?, ?, ?, ?, ?, ?, ?)";
$dbh = getDB();
$stmt = $dbh->prepare($sql);
$stmt->bindParam(1, $v1);
$stmt->bindParam(2, $v2);
$stmt->bindParam(3, $v3);
$stmt->bindParam(4, $v4);
$stmt->bindParam(5, $v5);
$stmt->bindParam(6, $v6);
$stmt->bindParam(7, $v7);
$stmt->execute();
}
/**
* dbLastInsertID
*
* The dbLastInsertID helper function calls MySQL's LAST_INSERT_ID() function
* to obtain the Primary Key of the last inserted row. This is how you can
* discover the ID of the row you just inserted elsewhere.
*/
function dbLastInsertID()
{
$db = getDB();
$stmt = $db->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt->fetch();
$lastId = $lastId[0];
return $lastId;
}
function getinfo($option)
{
$db = getDB();
$result = $db->query("SELECT option_value FROM settings WHERE option_name='{$option}'") or die(mysqli_error());
while ($row = $result->fetch_row()) {
return $row[0];
}
$result->close();
}
function updateID($email, $id)
{
$row = colCont('googleAccount', $email, "users");
$DATA = getDB("users", $row);
if (!$DATA['identifier']) {
writeDB($row, 'identifier', $id, "users");
}
return 1;
}
function sortDatabase($column, $order)
{
$db = getDB();
$stmt = $db->prepare("SELECT * FROM corps ORDER BY {$column} {$order}");
$results = array();
if ($stmt->execute() && $stmt->rowCount() > 0) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return $results;
}
function getAllCategories()
{
$db = getDB();
$stmt = $db->prepare("SELECT * FROM categories");
$results = array();
if ($stmt->execute() && $stmt->rowCount() > 0) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return $results;
}
function createProduct($category_id, $product, $price, $image)
{
$db = getDB();
$stmt = $db->prepare("INSERT INTO products SET category_id = :category_id, product = :product, price = :price, image = :image ");
$binds = array(":category_id" => $category_id, ":product" => $product, ":price" => $price, ":image" => $image);
if ($stmt->execute($binds) && $stmt->rowCount() > 0) {
return true;
}
return false;
}
function isValidUser($email, $pass)
{
$db = getDB();
$stmt = $db->prepare("SELECT * FROM users WHERE email = :email and password = :password");
$pass = sha1($pass);
$binds = array(":email" => $email, ":password" => $pass);
if ($stmt->execute($binds) && $stmt->rowCount() > 0) {
return true;
}
return false;
}
function storeVideo($url, $embeded, $headline, $year)
{
$db = getDB();
$sql = "INSERT INTO video (url,embeded,headline,year,datum,userid) VALUES ('" . mysql_real_escape_string($url) . "','" . $embeded . "','" . mysql_real_escape_string($headline) . "'," . $year . ",now()," . getCurrentUserId() . ")";
$result = mysql_query($sql);
if (mysql_affected_rows() != 1 || $errorno != 0) {
echo "<center class=\"error\">Eintrag fehlgeschlagen</center>";
} else {
echo "<center class=\"successful\">Eintrag erfolgreich</center>";
}
}
function findAllMediaItemsByMediaEventId($mediaeventId)
{
$db = getDB();
$sql = "SELECT * from mediaitem where mediaeventId = " . trim(mysql_real_escape_string($mediaeventId));
$result = mysql_query($sql, $db);
$ret = array();
while ($myrow = mysql_fetch_assoc($result)) {
$mediaItem = fillMediaItemObject($myrow);
array_push($ret, $mediaItem);
}
return $ret;
}
function getStandings()
{
$sql = "SELECT * FROM Standings ORDER BY points desc";
try {
$db = getDB();
$stmt = $db->query($sql);
$users = $stmt->fetchAll(PDO::FETCH_OBJ);
return '{"games": ' . json_encode($users) . '}';
} catch (PDOException $e) {
echo '{"error":{"text1":' . $e->getMessage() . '}}';
}
}
function getProductByID()
{
$db = getDB();
$stmt = $db->prepare("SELECT * FROM products JOIN categories ON categories.category_id = products.category_id WHERE product_id = :product_id ");
$product_id = filter_input(INPUT_GET, 'id');
$binds = array(":product_id" => $product_id);
$results = array();
if ($stmt->execute($binds) && $stmt->rowCount() > 0) {
$results = $stmt->fetchALL(PDO::FETCH_ASSOC);
}
return $results;
}
function getTeam()
{
$sql = "SELECT DISTINCT team1 FROM oldboysdata";
try {
$db = getDB();
$stmt = $db->query($sql);
$users = $stmt->fetchAll(PDO::FETCH_OBJ);
echo '{"games": ' . json_encode($users) . '}';
} catch (PDOException $e) {
echo '{"error":{"text1":' . $e->getMessage() . '}}';
}
}
function getLogin($name)
{
$sql = "SELECT * FROM Player WHERE player_name = '{$name}' ";
try {
$db = getDB();
$stmt = $db->query($sql);
$users = $stmt->fetchAll(PDO::FETCH_OBJ);
echo '{"games": ' . json_encode($users) . '}';
} catch (PDOException $e) {
echo '{"error":{"text1":' . $e->getMessage() . '}}';
}
}
