328 lines
11 KiB
PHP
328 lines
11 KiB
PHP
<?php
|
||
|
||
namespace Blog\Model;
|
||
|
||
use Blog\Model\Database;
|
||
use PDOException;
|
||
use Random\RandomException;
|
||
|
||
class UserModel extends Database
|
||
{
|
||
/**
|
||
* @throws RandomException
|
||
*/
|
||
public function createUser($values){
|
||
|
||
$salt = bin2hex(random_bytes(16));
|
||
|
||
$hash = hash('sha256', $values["password"] . $salt);
|
||
|
||
$guid = $this->createUUID();
|
||
|
||
$pdo = $this->linkDB();
|
||
|
||
$sql = "INSERT INTO user (`id`, `name`,`vorname`,`email`,`passwort`,`salt`,`role`)
|
||
VALUES (:guid, :name, :firstname, :email, :password, :salt, :role)";
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([
|
||
":guid" => $guid,
|
||
":name" => $values["lastname"],
|
||
":firstname" => $values["name"],
|
||
":email" => $values["email"],
|
||
":password" => $hash,
|
||
":salt" => $salt,
|
||
":role" => $values["role"]
|
||
]);
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Schreiben der Daten.", $e);
|
||
die;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
public function getUserByEmail($email){
|
||
$pdo = $this->linkDB();
|
||
$sql = "SELECT * FROM user WHERE email = :email";
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([":email" => $email]);
|
||
return $sth->fetch();
|
||
}
|
||
|
||
public function getUserById($id){
|
||
$pdo = $this->linkDB();
|
||
$sql = "SELECT * FROM user WHERE id = :id";
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([":id" => $id]);
|
||
return $sth->fetch();
|
||
}
|
||
|
||
public function deleteUser($id){
|
||
$pdo = $this->linkDB();
|
||
$sql = "DELETE FROM user WHERE id = :id";
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->bindParam(":id", $id);
|
||
$sth->execute();
|
||
}
|
||
|
||
public function updateUserData($id, $values){
|
||
$pdo = $this->linkDB();
|
||
$fields = [];
|
||
$params = [':id' => $id];
|
||
|
||
if(!empty($values["password"])){
|
||
$salt = bin2hex(random_bytes(16));
|
||
$hash = hash('sha256', $values["password"] . $salt);
|
||
$fields["password"] = "´passwort´ = :password";
|
||
$fields["salt"] = "´salt´ = :salt";
|
||
$params[":password"] = $hash;
|
||
$params[":salt"] = $salt;
|
||
}
|
||
|
||
foreach (['name','vorname','email'] as $col) {
|
||
if (isset($values[$col])) {
|
||
$fields[] = "`{$col}` = :{$col}";
|
||
$params[":{$col}"] = $values[$col];
|
||
}
|
||
}
|
||
|
||
$sql = "UPDATE user
|
||
SET " . implode(", ", $fields) . "
|
||
where id = :id";
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute($params);
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Aktualisieren der Daten.", $e);
|
||
die;
|
||
}
|
||
|
||
}
|
||
|
||
private function writeNewAddress($values) {
|
||
$guid = $this->createUUID();
|
||
|
||
$sql = "INSERT INTO ort (`id`, `stadt`, `strasse`, `plz`) VALUES (
|
||
:guid, :stadt, :strasse, :plz);";
|
||
|
||
$pdo = $this->linkDB();
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute(array(":guid" => $guid,
|
||
":stadt" => $values["stadt"],
|
||
":strasse" => $values["strasse"],
|
||
":plz" => $values["plz"],
|
||
));
|
||
return $guid;
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Schreiben der Daten.", $e);
|
||
die;
|
||
}
|
||
}
|
||
|
||
public function writeNewCourse($values, $kursleiterId)
|
||
{
|
||
// Bleibt übrig wenn Adresse erstellt wird aber Kurs nicht weil Error
|
||
$addressId = $this->writeNewAddress($values);
|
||
|
||
$guid = $this->createUUID();
|
||
|
||
$sql = "INSERT INTO kurs (`id`, `name`, `preis`, `dauer`, `rabatt`, `kategorie`, `beschreibung`, `kurseleiter`, `ort_id`) VALUES (
|
||
:guid, :name, :preis, :dauer, :rabatt, :kategorie, :beschreibung, :kurseleiter, :ort_id);";
|
||
|
||
$pdo = $this->linkDB();
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute(array(":guid" => $guid,
|
||
":name" => $values["name"],
|
||
":preis" => $values["preis"],
|
||
":dauer" => $values["dauer"],
|
||
":rabatt" => $values["rabatt"],
|
||
":kategorie" => $values["kategorie"],
|
||
":beschreibung" => $values["beschreibung"],
|
||
":kurseleiter" => $kursleiterId,
|
||
"ort_id" => $addressId
|
||
));
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Schreiben der Daten.", $e);
|
||
die;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
public function getMyCourses() {
|
||
$personId = $_SESSION["user_id"];
|
||
$isKursleiter = $_SESSION["user_role"] === "leiter";
|
||
|
||
$pdo = $this->linkDB();
|
||
|
||
if ($isKursleiter) {
|
||
$sql = "SELECT k.id, k.name, k.preis, k.dauer, k.rabatt, k.kategorie, k.beschreibung, k.ort_id,
|
||
o.stadt, o.strasse, o.plz, b.note, b.kommentar
|
||
FROM kurs AS k
|
||
JOIN ort AS o ON o.id = k.ort_id
|
||
LEFT JOIN bewertungen AS b ON b.kurs_id = k.id
|
||
WHERE k.kursleiter = :personId
|
||
ORDER BY k.name";
|
||
} else {
|
||
$sql = "SELECT k.id, k.name, k.preis, k.dauer, k.rabatt, k.kategorie, k.beschreibung, k.ort_id,
|
||
o.stadt, o.strasse, o.plz, b.note, b.kommentar
|
||
FROM kurs_user AS ku
|
||
JOIN kurs AS k ON k.id = ku.kurs_id
|
||
JOIN ort AS o ON o.id = k.ort_id
|
||
LEFT JOIN bewertungen AS b ON b.kurs_id = k.id
|
||
WHERE ku.user_id = :personId
|
||
ORDER BY k.name";
|
||
}
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([':personId' => $personId]);
|
||
return $sth->fetchAll(\PDO::FETCH_ASSOC);
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Lesen der Daten.", $e);
|
||
die;
|
||
}
|
||
}
|
||
public function getAllCourses() {
|
||
$pdo = $this->linkDB();
|
||
$userId = $_SESSION['user_id'] ?? null;
|
||
|
||
if ($userId === null) {
|
||
$sql = "SELECT
|
||
k.id, k.name, k.preis, k.dauer, k.rabatt, k.kategorie, k.beschreibung, k.ort_id,
|
||
o.stadt, o.strasse, o.plz, b.note, b.kommentar,
|
||
0 AS isTeilnehmer
|
||
FROM kurs AS k
|
||
JOIN ort AS o ON o.id = k.ort_id
|
||
LEFT JOIN bewertungen AS b ON b.kurs_id = k.id";
|
||
$params = [];
|
||
} else {
|
||
$sql = "SELECT
|
||
k.id, k.name, k.preis, k.dauer, k.rabatt, k.kategorie, k.beschreibung, k.ort_id,
|
||
o.stadt, o.strasse, o.plz, b.note, b.kommentar,
|
||
CASE WHEN ku.user_id IS NULL THEN 0 ELSE 1 END AS isTeilnehmer
|
||
FROM kurs AS k
|
||
JOIN ort AS o ON o.id = k.ort_id
|
||
LEFT JOIN bewertungen AS b ON b.kurs_id = k.id
|
||
LEFT JOIN kurs_user AS ku ON ku.kurs_id = k.id AND ku.user_id = :userId";
|
||
$params = ['userId' => $userId];
|
||
}
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute($params);
|
||
return $sth->fetchAll(\PDO::FETCH_ASSOC);
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Lesen der Daten.", $e);
|
||
die;
|
||
}
|
||
}
|
||
|
||
public function updateCourse($course) {
|
||
$pdo = $this->linkDB();
|
||
|
||
try {
|
||
if (isset($course['ort_id'])) {
|
||
$this->updateAddress($course);
|
||
$addressId = $course['ort_id'];
|
||
} else {
|
||
$addressId = $this->writeNewAddress($course);
|
||
}
|
||
|
||
$sql = "UPDATE kurs SET
|
||
`name` = :name,
|
||
`preis` = :preis,
|
||
`dauer` = :dauer,
|
||
`rabatt` = :rabatt,
|
||
`kategorie` = :kategorie,
|
||
`beschreibung` = :beschreibung,
|
||
`ort_id` = :ort_id
|
||
WHERE `id` = :id";
|
||
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([
|
||
':id' => $course['id'],
|
||
':name' => $course['name'],
|
||
':preis' => $course['preis'],
|
||
':dauer' => $course['dauer'],
|
||
':rabatt' => $course['rabatt'],
|
||
':kategorie' => $course['kategorie'],
|
||
':beschreibung' => $course['beschreibung'],
|
||
':ort_id' => $addressId
|
||
]);
|
||
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Aktualisieren des Kurses.", $e);
|
||
die;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
public function updateAddress($data) {
|
||
$pdo = $this->linkDB();
|
||
|
||
if (!isset($data['ort_id'])) {
|
||
throw new \Exception("Keine Adress-ID vorhanden zum Aktualisieren.");
|
||
}
|
||
|
||
$sql = "UPDATE ort SET
|
||
`strasse` = :strasse,
|
||
`stadt` = :stadt,
|
||
`plz` = :plz
|
||
WHERE `id` = :id";
|
||
|
||
try {
|
||
$sth = $pdo->prepare($sql);
|
||
$sth->execute([
|
||
':id' => $data['ort_id'],
|
||
':strasse' => $data['strasse'],
|
||
':stadt' => $data['stadt'],
|
||
':plz' => $data['plz']
|
||
]);
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Aktualisieren der Adresse.", $e);
|
||
die;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
public function enroll($courseId, $userId) {
|
||
$pdo = $this->linkDB();
|
||
|
||
try {
|
||
$checkSql = "SELECT COUNT(*) FROM kurs_user WHERE user_id = :user_id AND kurs_id = :kurs_id";
|
||
$checkStmt = $pdo->prepare($checkSql);
|
||
$checkStmt->execute([
|
||
':user_id' => $userId,
|
||
':kurs_id' => $courseId
|
||
]);
|
||
|
||
if ($checkStmt->fetchColumn() > 0) {
|
||
return false;
|
||
}
|
||
|
||
$insertSql = "INSERT INTO kurs_user (user_id, kurs_id) VALUES (:user_id, :kurs_id)";
|
||
$insertStmt = $pdo->prepare($insertSql);
|
||
$insertStmt->execute([
|
||
':user_id' => $userId,
|
||
':kurs_id' => $courseId
|
||
]);
|
||
|
||
} catch (PDOException $e) {
|
||
new \Blog\Library\ErrorMsg("Fehler beim Einschreiben in den Kurs.", $e);
|
||
die;
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
} |