634 lines
17 KiB
PHP
634 lines
17 KiB
PHP
<?php
|
|
|
|
namespace App\Database;
|
|
|
|
use PDO;
|
|
use PDOException;
|
|
use RuntimeException;
|
|
|
|
/**
|
|
* Class Database
|
|
*
|
|
* Gère la connexion PDO à la base de données MySQL.
|
|
*/
|
|
class Database
|
|
{
|
|
private PDO $connection;
|
|
|
|
public const DB_HOST = '127.0.0.1';
|
|
public const DB_NAME = 'bolsa';
|
|
public const DB_USER = 'root';
|
|
public const DB_PASS = 'sysadm-1963';
|
|
public const DB_CHARSET = 'utf8mb4';
|
|
|
|
/**
|
|
* Database constructor.
|
|
*
|
|
* Initialise la connexion PDO.
|
|
*
|
|
* @throws RuntimeException
|
|
*/
|
|
public function __construct()
|
|
{
|
|
$dsn = sprintf(
|
|
'mysql:host=%s;dbname=%s;charset=%s',
|
|
self::DB_HOST,
|
|
self::DB_NAME,
|
|
self::DB_CHARSET
|
|
);
|
|
|
|
$options = [
|
|
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
|
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
|
PDO::ATTR_EMULATE_PREPARES => false,
|
|
];
|
|
|
|
try {
|
|
$this->connection = new PDO($dsn, self::DB_USER, self::DB_PASS, $options);
|
|
} catch (PDOException $exception) {
|
|
throw new RuntimeException('Connexion à la base de données impossible : ' . $exception->getMessage());
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Retourne l'objet PDO.
|
|
*
|
|
* @return PDO
|
|
*/
|
|
public function getConnection(): PDO
|
|
{
|
|
return $this->connection;
|
|
}
|
|
|
|
/**
|
|
* Ferme la connexion PDO.
|
|
*
|
|
* @return void
|
|
*/
|
|
public function disconnect(): void
|
|
{
|
|
$this->connection = null;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Class CompteJFGManager
|
|
*
|
|
* Opérations CRUD sur la table `compteJFG`.
|
|
*/
|
|
class CompteJFGManager
|
|
{
|
|
private PDO $connection;
|
|
|
|
public function __construct(Database $database)
|
|
{
|
|
$this->connection = $database->getConnection();
|
|
}
|
|
|
|
public function listCompteJFG(): array
|
|
{
|
|
$statement = $this->connection->query(
|
|
'SELECT id, date_operation, debit, credit, libelle, updated_at
|
|
FROM compteJFG
|
|
ORDER BY date_operation DESC, id DESC'
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
public function createCompteJFG(array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'INSERT INTO compteJFG (date_operation, debit, credit, libelle, created_at, updated_at)
|
|
VALUES (:date_operation, :debit, :credit, :libelle, NOW(), NOW())'
|
|
);
|
|
|
|
$statement->bindValue(':date_operation', $data['date_operation'], PDO::PARAM_STR);
|
|
$statement->bindValue(':debit', $data['debit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':credit', $data['credit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':libelle', $data['libelle'], PDO::PARAM_STR);
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
public function updateCompteJFG(int $id, array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'UPDATE compteJFG
|
|
SET date_operation = :date_operation,
|
|
debit = :debit,
|
|
credit = :credit,
|
|
libelle = :libelle,
|
|
updated_at = NOW()
|
|
WHERE id = :id'
|
|
);
|
|
|
|
$statement->bindValue(':date_operation', $data['date_operation'], PDO::PARAM_STR);
|
|
$statement->bindValue(':debit', $data['debit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':credit', $data['credit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':libelle', $data['libelle'], PDO::PARAM_STR);
|
|
$statement->bindValue(':id', $id, PDO::PARAM_INT);
|
|
|
|
return $statement->execute();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Class LoginManager
|
|
*
|
|
* Fournit les opérations CRUD pour la table `login`.
|
|
*/
|
|
class LoginManager
|
|
{
|
|
private PDO $connection;
|
|
|
|
public function __construct(Database $database)
|
|
{
|
|
$this->connection = $database->getConnection();
|
|
}
|
|
|
|
/**
|
|
* Crée un nouvel utilisateur dans la table login.
|
|
*
|
|
* @param array $data
|
|
* @return int L'ID généré de l'utilisateur.
|
|
*/
|
|
public function create(array $data): int
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'INSERT INTO login (login, nom, prenom, mail, pass, actif, creer_le, modifie_le)
|
|
VALUES (:login, :nom, :prenom, :mail, :pass, :actif, NOW(), NOW())'
|
|
);
|
|
|
|
$hashedPassword = password_hash($data['pass'], PASSWORD_DEFAULT);
|
|
$actif = array_key_exists('actif', $data) ? (int) $data['actif'] : 0;
|
|
|
|
$statement->bindValue(':login', $data['login'], PDO::PARAM_STR);
|
|
$statement->bindValue(':nom', $data['nom'], PDO::PARAM_STR);
|
|
$statement->bindValue(':prenom', $data['prenom'], PDO::PARAM_STR);
|
|
$statement->bindValue(':mail', $data['mail'], PDO::PARAM_STR);
|
|
$statement->bindValue(':pass', $hashedPassword, PDO::PARAM_STR);
|
|
$statement->bindValue(':actif', $actif, PDO::PARAM_INT);
|
|
|
|
$statement->execute();
|
|
|
|
return (int) $this->connection->lastInsertId();
|
|
}
|
|
|
|
/**
|
|
* Lit un utilisateur par son ID.
|
|
*
|
|
* @param int $id
|
|
* @return array|null
|
|
*/
|
|
public function read(int $id): ?array
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT id, login, nom, prenom, mail, creer_le, modifie_le
|
|
FROM login
|
|
WHERE id = :id'
|
|
);
|
|
|
|
$statement->bindValue(':id', $id, PDO::PARAM_INT);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return $result === false ? null : $result;
|
|
}
|
|
|
|
/**
|
|
* Met à jour un utilisateur.
|
|
*
|
|
* @param int $id
|
|
* @param array $data
|
|
* @return bool
|
|
*/
|
|
public function update(int $id, array $data): bool
|
|
{
|
|
$fields = [];
|
|
$params = [':id' => $id];
|
|
|
|
if (isset($data['login'])) {
|
|
$fields[] = 'login = :login';
|
|
$params[':login'] = $data['login'];
|
|
}
|
|
|
|
if (isset($data['nom'])) {
|
|
$fields[] = 'nom = :nom';
|
|
$params[':nom'] = $data['nom'];
|
|
}
|
|
|
|
if (isset($data['prenom'])) {
|
|
$fields[] = 'prenom = :prenom';
|
|
$params[':prenom'] = $data['prenom'];
|
|
}
|
|
|
|
if (isset($data['mail'])) {
|
|
$fields[] = 'mail = :mail';
|
|
$params[':mail'] = $data['mail'];
|
|
}
|
|
|
|
if (isset($data['pass'])) {
|
|
$fields[] = 'pass = :pass';
|
|
$params[':pass'] = password_hash($data['pass'], PASSWORD_DEFAULT);
|
|
}
|
|
|
|
if (empty($fields)) {
|
|
return false;
|
|
}
|
|
|
|
$fields[] = 'modifie_le = NOW()';
|
|
|
|
$statement = $this->connection->prepare(
|
|
sprintf('UPDATE login SET %s WHERE id = :id', implode(', ', $fields))
|
|
);
|
|
|
|
foreach ($params as $param => $value) {
|
|
$statement->bindValue($param, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
|
|
}
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
/**
|
|
* Supprime un utilisateur par son ID.
|
|
*
|
|
* @param int $id
|
|
* @return bool
|
|
*/
|
|
public function delete(int $id): bool
|
|
{
|
|
$statement = $this->connection->prepare('DELETE FROM login WHERE id = :id');
|
|
$statement->bindValue(':id', $id, PDO::PARAM_INT);
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
/**
|
|
* Retourne tous les utilisateurs.
|
|
*
|
|
* @return array
|
|
*/
|
|
public function list(): array
|
|
{
|
|
$statement = $this->connection->query(
|
|
'SELECT id, login, nom, prenom, mail, creer_le, modifie_le FROM login ORDER BY id ASC'
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* Retourne un utilisateur par son login.
|
|
*
|
|
* @param string $login
|
|
* @return array|null
|
|
*/
|
|
public function getByLogin(string $login): ?array
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT id, login, nom, prenom, mail, pass, actif, creer_le, modifie_le
|
|
FROM login
|
|
WHERE login = :login'
|
|
);
|
|
|
|
$statement->bindValue(':login', $login, PDO::PARAM_STR);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return $result === false ? null : $result;
|
|
}
|
|
|
|
/**
|
|
* Vérifie si le mot de passe correspond au login.
|
|
*
|
|
* @param string $login
|
|
* @param string $password
|
|
* @return bool
|
|
*/
|
|
public function verifyPasswordByLogin(string $login, string $password): bool
|
|
{
|
|
$user = $this->getByLogin($login);
|
|
|
|
if ($user === null) {
|
|
return false;
|
|
}
|
|
|
|
return ((int) ($user['actif'] ?? 0) === 1) && password_verify($password, $user['pass']);
|
|
}
|
|
|
|
/**
|
|
* Vérifie si un login ou un email existe déjà.
|
|
*
|
|
* @param string $login
|
|
* @param string $email
|
|
* @return bool
|
|
*/
|
|
public function existsByLoginOrEmail(string $login, string $email): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT COUNT(*) AS total
|
|
FROM login
|
|
WHERE login = :login OR mail = :mail'
|
|
);
|
|
|
|
$statement->bindValue(':login', $login, PDO::PARAM_STR);
|
|
$statement->bindValue(':mail', $email, PDO::PARAM_STR);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return isset($result['total']) && (int) $result['total'] > 0;
|
|
}
|
|
|
|
/**
|
|
* Vérifie si une adresse e-mail existe.
|
|
*
|
|
* @param string $email
|
|
* @return bool
|
|
*/
|
|
public function existsByEmail(string $email): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT COUNT(*) AS total FROM login WHERE mail = :mail'
|
|
);
|
|
|
|
$statement->bindValue(':mail', $email, PDO::PARAM_STR);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return isset($result['total']) && (int) $result['total'] > 0;
|
|
}
|
|
|
|
/**
|
|
* Définit un token de réinitialisation pour un utilisateur identifié par son e-mail.
|
|
*
|
|
* @param string $email
|
|
* @param string $token
|
|
* @return bool
|
|
*/
|
|
public function setResetTokenByEmail(string $email, string $token): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'UPDATE login SET reset_token = :token, reset_token_created_at = NOW(), modifie_le = NOW() WHERE mail = :mail'
|
|
);
|
|
|
|
$statement->bindValue(':token', $token, PDO::PARAM_STR);
|
|
$statement->bindValue(':mail', $email, PDO::PARAM_STR);
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
/**
|
|
* Retourne un utilisateur identifié par son token de réinitialisation.
|
|
*
|
|
* @param string $token
|
|
* @return array|null
|
|
*/
|
|
public function getByResetToken(string $token): ?array
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT id, login, nom, prenom, mail, pass, actif, reset_token, reset_token_created_at, creer_le, modifie_le
|
|
FROM login
|
|
WHERE reset_token = :token
|
|
AND reset_token_created_at >= NOW() - INTERVAL 30 MINUTE'
|
|
);
|
|
|
|
$statement->bindValue(':token', $token, PDO::PARAM_STR);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return $result === false ? null : $result;
|
|
}
|
|
|
|
/**
|
|
* Met à jour le mot de passe d'un utilisateur et supprime le token de réinitialisation.
|
|
*
|
|
* @param int $id
|
|
* @param string $password
|
|
* @return bool
|
|
*/
|
|
public function updatePasswordById(int $id, string $password): bool
|
|
{
|
|
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
|
|
|
|
$statement = $this->connection->prepare(
|
|
'UPDATE login SET pass = :pass, reset_token = NULL, reset_token_created_at = NULL, modifie_le = NOW() WHERE id = :id'
|
|
);
|
|
|
|
$statement->bindValue(':pass', $hashedPassword, PDO::PARAM_STR);
|
|
$statement->bindValue(':id', $id, PDO::PARAM_INT);
|
|
|
|
return $statement->execute();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Class ActionsManager
|
|
*
|
|
* Opérations CRUD sur la table `actions`.
|
|
*/
|
|
class ActionsManager
|
|
{
|
|
private PDO $connection;
|
|
|
|
public function __construct(Database $database)
|
|
{
|
|
$this->connection = $database->getConnection();
|
|
}
|
|
|
|
/**
|
|
* Retourne toutes les actions.
|
|
*
|
|
* @return array
|
|
*/
|
|
public function listActions(): array
|
|
{
|
|
$statement = $this->connection->query(
|
|
'SELECT isin, ticker, yahoo_code, company_name, updated_at
|
|
FROM actions
|
|
ORDER BY updated_at DESC'
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* Exporte toutes les actions vers un fichier CSV.
|
|
*
|
|
* @param string $filePath
|
|
* @return bool
|
|
*/
|
|
public function exportActionsToCsv(string $filePath): bool
|
|
{
|
|
$actions = $this->listActions();
|
|
|
|
$directory = dirname($filePath);
|
|
if (!is_dir($directory) && !mkdir($directory, 0755, true) && !is_dir($directory)) {
|
|
return false;
|
|
}
|
|
|
|
$handle = fopen($filePath, 'w');
|
|
if ($handle === false) {
|
|
return false;
|
|
}
|
|
|
|
$header = ['isin', 'ticker', 'yahoo_code', 'company_name', 'updated_at'];
|
|
if (fputcsv($handle, $header) === false) {
|
|
fclose($handle);
|
|
return false;
|
|
}
|
|
|
|
foreach ($actions as $action) {
|
|
if (fputcsv($handle, [
|
|
$action['isin'],
|
|
$action['ticker'],
|
|
$action['yahoo_code'],
|
|
$action['company_name'],
|
|
$action['updated_at'],
|
|
]) === false) {
|
|
fclose($handle);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
fclose($handle);
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Retourne une action par son ISIN.
|
|
*
|
|
* @param string $isin
|
|
* @return array|null
|
|
*/
|
|
public function getActionByIsin(string $isin): ?array
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'SELECT isin, ticker, yahoo_code, company_name, updated_at
|
|
FROM actions
|
|
WHERE isin = :isin'
|
|
);
|
|
|
|
$statement->bindValue(':isin', $isin, PDO::PARAM_STR);
|
|
$statement->execute();
|
|
|
|
$result = $statement->fetch();
|
|
|
|
return $result === false ? null : $result;
|
|
}
|
|
|
|
/**
|
|
* Crée une nouvelle action.
|
|
*
|
|
* @param array $data
|
|
* @return bool
|
|
*/
|
|
public function createAction(array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'INSERT INTO actions (isin, ticker, yahoo_code, company_name, updated_at)
|
|
VALUES (:isin, :ticker, :yahoo_code, :company_name, NOW())'
|
|
);
|
|
|
|
$statement->bindValue(':isin', $data['isin'], PDO::PARAM_STR);
|
|
$statement->bindValue(':ticker', $data['ticker'], PDO::PARAM_STR);
|
|
$statement->bindValue(':yahoo_code', $data['yahoo_code'], PDO::PARAM_STR);
|
|
$statement->bindValue(':company_name', $data['company_name'], PDO::PARAM_STR);
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
/**
|
|
* Met à jour une action.
|
|
*
|
|
* @param string $isin
|
|
* @param array $data
|
|
* @return bool
|
|
*/
|
|
public function updateAction(string $isin, array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'UPDATE actions
|
|
SET ticker = :ticker,
|
|
yahoo_code = :yahoo_code,
|
|
company_name = :company_name,
|
|
updated_at = NOW()
|
|
WHERE isin = :isin'
|
|
);
|
|
|
|
$statement->bindValue(':ticker', $data['ticker'], PDO::PARAM_STR);
|
|
$statement->bindValue(':yahoo_code', $data['yahoo_code'], PDO::PARAM_STR);
|
|
$statement->bindValue(':company_name', $data['company_name'], PDO::PARAM_STR);
|
|
$statement->bindValue(':isin', $isin, PDO::PARAM_STR);
|
|
|
|
return $statement->execute();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Class CompteGFManager
|
|
*
|
|
* Opérations CRUD sur la table `compteGF`.
|
|
*/
|
|
class CompteGFManager
|
|
{
|
|
private PDO $connection;
|
|
|
|
public function __construct(Database $database)
|
|
{
|
|
$this->connection = $database->getConnection();
|
|
}
|
|
|
|
public function listCompteGF(): array
|
|
{
|
|
$statement = $this->connection->query(
|
|
'SELECT id, date_operation, debit, credit, libelle, updated_at
|
|
FROM compteGF
|
|
ORDER BY date_operation DESC, id DESC'
|
|
);
|
|
|
|
return $statement->fetchAll();
|
|
}
|
|
|
|
public function createCompteGF(array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'INSERT INTO compteGF (date_operation, debit, credit, libelle, created_at, updated_at)
|
|
VALUES (:date_operation, :debit, :credit, :libelle, NOW(), NOW())'
|
|
);
|
|
|
|
$statement->bindValue(':date_operation', $data['date_operation'], PDO::PARAM_STR);
|
|
$statement->bindValue(':debit', $data['debit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':credit', $data['credit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':libelle', $data['libelle'], PDO::PARAM_STR);
|
|
|
|
return $statement->execute();
|
|
}
|
|
|
|
public function updateCompteGF(int $id, array $data): bool
|
|
{
|
|
$statement = $this->connection->prepare(
|
|
'UPDATE compteGF
|
|
SET date_operation = :date_operation,
|
|
debit = :debit,
|
|
credit = :credit,
|
|
libelle = :libelle,
|
|
updated_at = NOW()
|
|
WHERE id = :id'
|
|
);
|
|
|
|
$statement->bindValue(':date_operation', $data['date_operation'], PDO::PARAM_STR);
|
|
$statement->bindValue(':debit', $data['debit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':credit', $data['credit'], PDO::PARAM_STR);
|
|
$statement->bindValue(':libelle', $data['libelle'], PDO::PARAM_STR);
|
|
$statement->bindValue(':id', $id, PDO::PARAM_INT);
|
|
|
|
return $statement->execute();
|
|
}
|
|
}
|