urfat-orm/src/ORM/DBInstance.php

<?php

namespace ORM;

class DBInstance
{

    private static function addPrefix($sql, $instance = 'default')
    {
        $con = Connections::getConnection($instance);

        //Where is preg_replace when we need it D`:
        $sql = str_replace("\n", "", $sql);
        while (strpos($sql, "  ") > -1) {
            $sql = str_replace("  ", " ", $sql);
        }
        $sql = str_replace("{", $con->prefix, $sql);
        $sql = str_replace("}", "", $sql);

        return $sql;
    }

    /**
     * Execute
     *
     * This method will execute a SQL on the database
     *
     * @ctag DBInstance::execute($sql);
     * @ctag DBInstance::execute($sql, []);
     *
     * @param String $sql The SQL to be executed
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param String $instance Select the connection to execute
     */
    public static function execute($sql, $data = null, $instance = 'default')
    {
        $sql = self::addPrefix($sql, $instance);
        if ($data) {
            self::queryPrepare($sql, $data, $instance);
        } else {
            $con = Connections::getConnection($instance);
            $con->exec($sql);
        }
    }

    /*
     *
     * @ctag DBInstance::lastInsert();
     */

    public static function lastInsert($instance = 'default')
    {
        $con = Connections::getConnection($instance);
        return $con->lastInsertId();
    }

    /**
     * Query
     *
     * This method will execute a SQL and return a pointer to the resultset
     *
     * @ctag DBInstance::query($sql);
     * @ctag DBInstance::query($sql, []);
     *
     * @param String $sql The SQL to be executed
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param String $instance Select the connection to execute
     *
     * @return pointer A pointer for a foreach loop
     */
    public static function query($sql, $data = null, $instance = 'default')
    {
        $sql = self::addPrefix($sql, $instance);

        if ($data) {
            return self::queryPrepare($sql, $data, $instance);
        } else {
            $con = Connections::getConnection($instance);
            return $con->query($sql, \PDO::FETCH_OBJ);
        }
    }

    /**
     * queryOne
     *
     * @ctag DBInstance::queryOne($sql);
     * @ctag DBInstance::queryOne($sql, []);
     *
     * This method will execute a SQL and return the first featched register
     *
     * @param String $sql The SQL to be executed
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param String $instance Select the connection to execute
     *
     * @return stdClass A object with the record info
     */
    public static function queryOne($sql, $data = null, $instance = 'default')
    {

        $sql = self::addPrefix($sql, $instance);

        if ($data) {
            return self::queryPrepare($sql, $data, $instance);
        } else {
            $con = Connections::getConnection($instance);
            return $con->query($sql, \PDO::FETCH_OBJ)->fetch();
        }
    }

    /**
     * getRecord
     *
     * This method will execute get a register from a table
     *
     * @ctag DBInstance::getRecord($table);
     * @ctag DBInstance::getRecord($table, []);
     * @ctag DBInstance::getRecord($table, [], []);
     *
     * @param String $table The table to be searched
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param Array $select Info to be put on the SELECT part of the query
     * @param String $instance Select the connection to execute
     *
     * @return stdClass A object with the record info
     */
    public static function getRecord($table, $data = array(), $select = array('*'), $instance = 'default')
    {
        $select = implode(', ', $select);

        $conditions = "";
        $params = array();

        foreach ($data as $key => $cond) {
            if (is_array($cond)) {
                if (is_array($cond[1])) {
                    $inner_conditions = "$key $cond[0] (";
                    foreach ($cond[1] as $in_key => $c) {
                        $inner_conditions .= ":" . $key . "_" . $in_key . ", ";
                        $params[$key . "_" . $in_key] = $c;
                    }
                    $conditions .= rtrim($inner_conditions, ', ') . ") AND ";
                } else {
                    $conditions .= "$key $cond[0] :$key AND ";
                    $params[$key] = $cond[1];
                }
            } else {
                $conditions .= "$key = :$key AND ";
                $params[$key] = $cond;
            }
        }

        $conditions = rtrim($conditions, 'AND ');
        if ($conditions == "") {
            $conditions = "1 = 1";
        }

        $sql = "SELECT $select FROM {" . $table . "} WHERE $conditions LIMIT 1";

        return self::queryOne($sql, $params, $instance);
    }

    /**
     * getRecordSql
     *
     * This method will get a record from the database using SQL
     *
     * @ctag DBInstance::getRecordSql($sql);
     * @ctag DBInstance::getRecordSql($sql, []);
     *
     * @param String $sql The SQL to be executed
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param String $instance Select the connection to execute
     *
     * @return stdClass A object with the record info
     */
    public static function getRecordSql($sql, $data = null, $instance = 'default')
    {
        $sql = self::addPrefix($sql, $instance);

        if ($data) {
            $pointer = self::queryPrepare($sql, $data, $instance);
        } else {
            $con = Connections::getConnection($instance);
            return $con->query($sql, \PDO::FETCH_OBJ)->fetch();
        }
    }

    /**
     * getRecords
     *
     * This method will get records from the database
     *
     * @ctag DBInstance::getRecords($table);
     * @ctag DBInstance::getRecords($table, []);
     *
     * @param String $table The table to be searched
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param Array $select Info to be put on the SELECT part of the query
     * @param String $instance Select the connection to execute
     *
     * @return stdClass A object with the record info
     */
    public static function getRecords($table, $data = null, $select = array('*'), $limits = array(), $instance = 'default')
    {
        $pointer = self::getRecordsPointer($table, $data, $select, $limits, $instance);

        $elements = array();
        foreach ($pointer as $point) {
            $key = array_keys((array) $point)[0];
            $elements[((array) $point)[$key]] = $point;
        }
        return $elements;
    }

    /**
     * getRecordsPointer
     *
     * This method will get records from the database
     *
     * @ctag DBInstance::getRecordsPointer($table);
     * @ctag DBInstance::getRecordsPointer($table, []);
     *
     * @param String $table The table to be searched
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param Array $select Info to be put on the SELECT part of the query
     * @param String $instance Select the connection to execute
     *
     * @return stdClass A object with the record info
     */
    public static function getRecordsPointer($table, $data = array(), $select = array('*'), $limits = array(), $instance = 'default')
    {
        $select = implode(', ', $select);

        $conditions = "";
        $limits_sql = "";
        $params = array();

        foreach ($data as $key => $cond) {
            if (is_array($cond)) {
                if (is_array($cond[1])) {
                    $inner_conditions = "$key $cond[0] (";
                    foreach ($cond[1] as $in_key => $c) {
                        $inner_conditions .= ":" . $key . "_" . $in_key . ", ";
                        $params[$key . "_" . $in_key] = $c;
                    }
                    $conditions .= rtrim($inner_conditions, ', ') . ") AND ";
                } else {
                    $conditions .= "$key $cond[0] :$key AND ";
                    $params[$key] = $cond[1];
                }
            } else {
                $conditions .= "$key = :$key AND ";
                $params[$key] = $cond;
            }
        }

        foreach ($limits as $key => $value) {
            $limits_sql .= "$key $value ";
        }

        $conditions = rtrim($conditions, 'AND ');
        if ($conditions == "") {
            $conditions = "1 = 1";
            $params = false;
        }

        $sql = "SELECT $select FROM {" . $table . "} WHERE $conditions $limits_sql";

        return self::query($sql, $params, $instance);
    }

    /**
     * getRecordsSql
     *
     * This method will get records from the database
     *
     * @ctag DBInstance::getRecordsSql($sql);
     * @ctag DBInstance::getRecordsSql($sql, []);
     *
     * @param String $sql The SQL to be executed
     * @param Array $data Pass the info to be replaced on the query_prepare
     * @param String $instance Select the connection to execute
     *
     * @return Array Array with objects representing the database data
     */
    public static function getRecordsSql($sql, $data = null, $instance = 'default')
    {
        $sql = self::addPrefix($sql, $instance);

        $pointer = array();
        if ($data) {
            $pointer = self::queryPrepare($sql, $data, $instance);
        } else {
            $con = Connections::getConnection($instance);
            $pointer = $con->query($sql, \PDO::FETCH_OBJ);
        }
        $elements = array();
        foreach ($pointer as $point) {
            $key = array_keys((array) $point)[0];
            $elements[((array) $point)[$key]] = $point;
        }
        return $elements;
    }

    public static function insertRecord($table, $data = array(), $connection = 'default')
    {

        if (is_object($data)) {
            $data = (array)$data;
        }

        $columns = array_keys($data);

        $first_argument = '';
        $second_argument = '';
        $insert_data = array();

        foreach ($columns as $column) {
            $first_argument .= $column . ', ';

            $insert_data[$column] = $data[$column];
            $second_argument .= ":$column, ";
        }

        $first_argument = rtrim($first_argument, ', ');
        $second_argument = rtrim($second_argument, ', ');

        $sql = "INSERT INTO {" . $table . "} ($first_argument) VALUES ($second_argument)";

        DBInstance::execute($sql, $insert_data, $connection);
    }

    public static function queryPrepare($sql, $data = array(), $instance = 'default')
    {
        $sql = self::addPrefix($sql, $instance);
        $con = Connections::getConnection($instance);
        $statement = $con->prepare($sql);
        $statement->execute($data);
        return $statement->fetchAll(\PDO::FETCH_OBJ);
    }
}