site-proximo/classes/dao.php

<?php


class Dao
{
    private $conn;

    public function __construct()
    {
        $username = 'proximo';
        $password = $this->read_password();
        $dsn = 'mysql:dbname=proximo;host=127.0.0.1';
        try {
            $this->conn = new PDO($dsn, $username, $password, [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8']);
        } catch (PDOException $e) {
            echo "error";
            echo $e;
        }
    }

    private function read_password()
    {
        $real_path = __DIR__ . DIRECTORY_SEPARATOR . ".htpassdb";
        $file = fopen($real_path, "r") or die("Unable to open DB password file!");
        $password = fgets($file);
        fclose($file);
        return trim($password);
    }

    public function get_articles()
    {
        $sql = 'SELECT * FROM articles';
        $cursor = $this->conn->prepare($sql);
        $cursor->execute();
        return $cursor->fetchAll(PDO::FETCH_ASSOC);
    }

    public function get_article_of_code($code)
    {
        $sql = 'SELECT * FROM articles WHERE code=?';
        $cursor = $this->conn->prepare($sql);
        $cursor->execute([$code]);
        return $cursor->fetchAll(PDO::FETCH_ASSOC)[0];
    }

    public function get_categories_of_article($articleid)
    {
        $sql = 'SELECT category_id FROM article_categories WHERE article_id=?';
        $cursor = $this->conn->prepare($sql);
        $cursor->execute([$articleid]);
        $result = $cursor->fetchAll(PDO::FETCH_ASSOC);
        $final = [];
        foreach ($result as $row) {
            array_push($final, $row["category_id"]);
        }
        return $final;
    }

    public function get_article_categories()
    {
        $sql = 'SELECT * FROM article_categories';
        $cursor = $this->conn->prepare($sql);
        $cursor->execute();
        return $cursor->fetchAll(PDO::FETCH_ASSOC);
    }

    public function remove_article_categories_of_article($articleId)
    {
        $sql = 'DELETE FROM article_categories WHERE article_id=?';
        $cursor = $this->conn->prepare($sql);
        return $cursor->execute([$articleId]);
    }

    public function remove_article_categories_of_category($categoryId)
    {
        $sql = 'DELETE FROM article_categories WHERE category_id=?';
        $cursor = $this->conn->prepare($sql);
        return $cursor->execute([$categoryId]);
    }

    public function save_article_categories($articleId, $categories)
    {
        foreach ($categories as $category) {
            $sql = 'INSERT INTO article_categories (article_id, category_id) VALUES (?, ?)';
            $cursor = $this->conn->prepare($sql);
            $data = [$articleId, $category];
            $cursor->execute($data);
        }
        return 1;
    }

    public function get_categories()
    {
        $sql = 'SELECT * FROM categories';
        $cursor = $this->conn->prepare($sql);
        $cursor->execute();
        return $cursor->fetchAll(PDO::FETCH_ASSOC);
    }

    public function create_category($category)
    {
        $sql = 'INSERT INTO categories (name, icon) VALUES (?, ?)';
        $cursor = $this->conn->prepare($sql);
        $data = [$category["name"], $category["icon"]];
        $cursor->execute($data);
        return $this->conn->lastInsertId();
    }

    public function update_category($category)
    {
        $sql = 'UPDATE categories SET name=?, icon=? WHERE id=?';
        $cursor = $this->conn->prepare($sql);
        $data = [$category["name"], $category["icon"], $category["id"]];
        $cursor->execute($data);
        return $category["id"];
    }

    public function remove_category($id)
    {
        $sql = 'DELETE FROM categories WHERE id=?';
        $cursor = $this->conn->prepare($sql);
        $data = [$id];
        $result = $cursor->execute($data);
        if ($result) {
            $this->remove_article_categories_of_category($id);
            return $cursor->rowCount();
        } else
            return 0;
    }

    public function create_article($article)
    {
        $sql = 'INSERT INTO articles (name, description, price, code) VALUES (?, ?, ?, ?)';
        $cursor = $this->conn->prepare($sql);
        $data = [$article["name"], $article["description"], $article["price"], $article["code"]];
        $cursor->execute($data);
        return $this->conn->lastInsertId();
    }

    public function update_article($article)
    {
        $sql = 'UPDATE articles SET name=?, description=?, price=?, code=? WHERE id=?';
        $cursor = $this->conn->prepare($sql);
        $data = [$article["name"], $article["description"], $article["price"], $article["code"], $article["id"]];
        $cursor->execute($data);
        return $article["id"];
    }

    public function remove_article($id)
    {
        $sql = 'DELETE FROM articles WHERE id=?';
        $cursor = $this->conn->prepare($sql);
        $data = [$id];
        $result = $cursor->execute($data);
        if ($result) {
            $this->remove_article_categories_of_article($id);
            return $cursor->rowCount();
        } else
            return 0;
    }

    public function update_article_stock($articleid, $diff)
    {
        $sql = 'UPDATE articles SET quantity=quantity+? WHERE id=?';
        $cursor = $this->conn->prepare($sql);
        $data = [$diff, $articleid];
        return $cursor->execute($data);
    }
}