<?php

/**
 * Class MySQL
 */
class MySQL{

    /**
     * @var PDO Conexión a la base de datos
     */

    private $pdo;

    /**
     * @var string Host de la BD
     */

    private $dbhost;

    /**
     * @var string User de la BD
     */

    private $dbuser;

    /**
     * @var string Pass de la bd
     */

    private $dbpass;

    /**
     * @var string Nombre de la BD
     */

    private $dbname;

    /**
     * @var string Nombre de la tabla
     */

    private $table;

    /**
     * @var string Campo a ordenar
     */

    private $orderby;

    /**
     * @var int Límite de registros
     */

    private $limit;

    /**
     * @var string Objeto a castear los resultados
     */
    private $object;

    /**
     * Inicia la conexión
     *
     * @param string $dbhost Host
     * @param string $dbuser Usuario
     * @param string $dbpass Contraseña
     * @param string $dbname BD
     */
    public function __construct($dbhost, $dbuser, $dbpass, $dbname){
        $this->dbhost = $dbhost;
        $this->dbuser = $dbuser;
        $this->dbpass = $dbpass;
        $this->dbname = $dbname;
        $this->object = NULL;

        $pdo = new PDO("mysql:host=" . $this->dbhost . ";dbname=" . $this->dbname . ";charset=utf8", $this->dbuser, $this->dbpass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo = $pdo;
    }

    /**
     * Devuelve el nombre de la table
     *
     * @return mixed
     */
    public function getTable(){
        return $this->table;
    }

    /**
     * Asigna el nombre de la tabla
     *
     * @param string $table Nombre de la tabla
     */
    public function setTable($table){
        $this->table = $table;
    }

    /**
     * Asigna el campo a ordenar
     *
     * @param string $orderby Campo a ordenar
     */
    public function setOrderBy($orderby){
        $this->orderby = $orderby;
    }

    /**
     * Asigna el límite de registros
     *
     * @param int $limit Límite de registros
     */
    public function setLimit($limit){
        $this->limit = $limit;
    }

    /**
     * Asigna el objeto a castear los resultados
     *
     * @param string $class Objeto a castear
     */
    public function setObject($class){
        $this->object = $class;
    }

    /**
     * Devuelve el objeto a castear
     *
     * @return string Objeto a castear
     */
    public function getObject(){
        return $this->object;
    }

    /**
     * @return PDO Obtiene la conexión
     */
    public function getPDO(){
        return $this->pdo;
    }

    /**
     * Crea el SQL de la condición a partir del array
     *
     * $a["field"] => Campo
     * $a["value"] => Valor
     * $a["logic"] => Operador lógico (AND, OR, ...)
     * $a["op"] => Operador de comparación (=, <>, <, >, ...)
     *
     * FORMATO: $a["logic"] $a["field"] $a["op"] $a["value"]
     *
     * @param array $conditions Condiciones
     * @return bool|string SQL de la condición
     */
    private function condition($conditions){
        if (is_array($conditions)) {
            $i = 0;
            $conditionString = "";
            foreach ($conditions as $condition) {
                $k = $i++;

                switch ($k) {
                    case 0:
                        $conditionString .= $condition["field"] . " " . $condition["op"] . " :" . $condition["field"];
                        break;

                    default:
                        $conditionString .= " " . $condition["logic"] . " " . $condition["field"] . " " . $condition["op"] . " :" . $condition["field"];
                        break;
                }
            }
            return $conditionString;
        } else {
            return false;
        }
    }

    /**
     * Otra información como el límite o el campo a ordenar
     *
     * @return string
     */
    private function others(){
        $others = "";
        if (isset($this->orderby)) {
            $others .= " ORDER BY " . $this->orderby;
        }
        if (isset($this->limit)) {
            $others .= " LIMIT " . $this->limit;
        }
        return $others;
    }

    /**
     * Devuelve los resultados con las condiciones $data si procede
     *
     * @param array $data Condiciones
     * @return array|bool
     */
    public function select($data = NULL){

        $condition = ($data != NULL) ? " WHERE " . $this->condition($data) : "";
        $condition .= $this->others();

        $query = $this->pdo->prepare("SELECT * FROM " . $this->table . $condition);

        if ($data != NULL) foreach ($data as $element) $query->bindValue(":" . $element["field"], $element["value"]);

        if ($this->object != NULL) $query->setFetchMode(PDO::FETCH_CLASS, $this->object);
        else $query->setFetchMode(PDO::FETCH_ASSOC);
        try{
            $query->execute();
            $array = array();

                while ($object = $query->fetch()) {
                    $array[] = $object;
                }
                $return = $array;

        }catch(PDOException $e){
            $return = false;
        }

        return $return;
    }

    /**
     * Devuelve EL resultado con las condiciones $data si procede
     *
     * @param array $data Condiciones
     * @return bool|mixed
     */
    public function single($data = NULL){

        $condition = ($data != NULL) ? " WHERE " . $this->condition($data) : "";
        $condition .= $this->others();

        $query = $this->pdo->prepare("SELECT * FROM " . $this->table . $condition . " LIMIT 1");

        if ($this->object != NULL) $query->setFetchMode(PDO::FETCH_CLASS, $this->object);
        else $query->setFetchMode(PDO::FETCH_ASSOC);

        if ($data != NULL) foreach ($data as $element) $query->bindValue(":" . $element["field"], $element["value"]);

        try{
            $query->execute();
            $return = $query->fetch();
        }catch(PDOException $e){
            $return = false;
        }

        return $return;

    }

    /**
     * Inserta un registro en la tabla
     *
     * @param array $data Campos del registro
     * @return bool
     */
    public function insert($data){
        $success = true;
        if ($data != NULL) {

            $append = "";
            $prepend = "";
            $args = (count($data) - 1);
            $i = 0;

            foreach ($data as $field => $value) {
                $k = $i++;
                switch ($k) {
                    case 0:
                        $append .= "(" . $field;
                        $prepend .= "(:" . $field . "";
                        break;

                    case $args:
                        $append .= "," . $field . ")";
                        $prepend .= ",:" . $field . ")";
                        break;

                    default:
                        $append .= "," . $field;
                        $prepend .= ",:" . $field . "";
                        break;
                }
            }

            $sql = "INSERT INTO " . $this->table . " " . $append . " VALUES " . $prepend;
            $query = $this->pdo->prepare($sql);

            foreach ($data as $field => $value) $query->bindValue(":" . $field, $value);

            try{
                $query->execute();
            }catch(PDOException $e){
                $success = false;
                print $e->getMessage();
            }
        } else {
            $success = false;
        }
        return $success;
    }

    /**
     * Elimina un registro de la tabla
     *
     * @param array $data Condiciones
     * @return bool
     */
    public function delete($data){
        $success = true;
        if ($data != NULL) {
            $sql = "DELETE FROM " . $this->table . " WHERE " . $this->condition($data);
            $query = $this->pdo->prepare($sql);
            if($data != NULL) foreach($data as $element) $query->bindValue(":".$element["field"],$element["value"]);
            try{
                $query->execute();
            }catch(PDOException $e){
                $success = false;
            }
        } else {
            $success = false;
        }

        return $success;
    }

    /**
     * Actualiza un registro de la tabla
     *
     * @param array $data Datos a actualizar
     * @param array $cond Condiciones
     * @return bool
     */
    public function update($data, $cond){
        $success = true;
        if ($data != NULL and $cond != NULL) {
            if ($data != NULL) {
                $append = '';
                $i = 0;
                foreach ($data as $field => $value) {
                    $k = $i++;
                    switch ($k) {
                        case 0:
                            $append .= $field . " = :data_" . $field;
                            break;

                        default:
                            $append .= " , ". $field . " = :data_" . $field;
                            break;
                    }
                }
            }
            if ($cond != NULL) $condition = $this->condition($cond);

            $sql = "UPDATE " . $this->table . " SET " . $append . " WHERE " . $condition;
            $query = $this->pdo->prepare($sql);

            if($data != NULL) foreach($data as $field => $value) $query->bindValue(":data_".$field,$value);
            if($cond != NULL) foreach($cond as $element) $query->bindValue(":".$element["field"],$element["value"]);

            try{
                $query->execute();
            }catch(PDOException $e){
                $success = false;
            }

        } else {
            $success = false;
        }
        return $success;
    }


    /**
     * Hace una consulta a modo manual
     *
     * @param string $query SQL de la consulta
     * @param array $data Datos
     * @return array|bool
     */
    public function manualSelect($query,$data){

        $query = $this->pdo->prepare($query);

        if ($data != NULL) foreach ($data as $field=>$value) $query->bindValue(":" . $field, $value);

        if ($this->object != NULL) $query->setFetchMode(PDO::FETCH_CLASS, $this->object);
        else $query->setFetchMode(PDO::FETCH_ASSOC);


        try{
            $query->execute();
            $array = array();

            while ($object = $query->fetch()) {
                $array[] = $object;
            }
            $return = $array;
        }catch(PDOException $e){
            $return = false;
        }

        return $return;
    }

    /**
     * Ejecuta una query SQL de forma manual
     *
     * @param string $query SQL del query
     * @param array $data Datos del query
     * @return bool
     */
    public function manualQuery($query,$data){

        $query = $this->pdo->prepare($query);

        if ($data != NULL) foreach ($data as $field=>$value) $query->bindValue(":" . $field, $value);
        $return = true;

        try{
            $query->execute();
        }catch(PDOException $e){
            $return = false;
        }

        return $return;
    }

    /**
     * Comprueba si existe un registro dada una condicion
     *
     * @param array $data Condiciones
     * @return bool
     */
    public function check($data){
        $return = true;
        if ($data != NULL) {
            $condition = $this->condition($data);
            $sql = "SELECT COUNT(*) AS numRows FROM " . $this->table . " WHERE " . $condition;
            $query = $this->pdo->prepare($sql);
            foreach($data as $element) $query->bindValue(":".$element["field"],$element["value"]);
            $query->setFetchMode(PDO::FETCH_ASSOC);
            try{
                $query->execute();
                $data = $query->fetch();
                $return = ((int)$data["numRows"] > 0);
            }catch(PDOException $e){
                $return = false;
            }
        } else {
            $return = false;
        }
        return $return;
    }

    /**
     * Devuelve el numero de registros dada una condicion
     * @param array $data Condiciones
     * @return bool|int
     */
    public function rows($data = NULL){
        if ($data != NULL) {
            $condition = $this->condition($data);
            $sql = "SELECT COUNT(*) AS numRows FROM " . $this->table . " WHERE " . $condition;
            $query = $this->pdo->prepare($sql);
            foreach($data as $element) $query->bindValue(":".$element["field"],$element["value"]);
            $query->setFetchMode(PDO::FETCH_ASSOC);
            try{
                $query->execute();
                $data = $query->fetch();
                $return = (int) $data["numRows"];
            }catch(PDOException $e){
                $return = false;
            }
        } else {
            $return = 0;
        }
        return $return;
    }
}