ByJG\AnyDataset\Database\SQLHelper

Complexity

Total Complexity

30

Size/Duplication

Total Lines	157
Duplicated Lines	0 %

Coupling/Cohesion

Components	1
Dependencies	2

Test Coverage

Coverage

0%

Importance

Changes	9
Bugs	2	Features	0

7 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	4	1
C	generateSQL()	0	40	12
C	getValue()	0	35	11
A	getWhereClause()	0	7	2
A	setFieldDelimeters()	0	5	1
A	createSafeSQL()	0	8	2
A	getDbDataset()	0	4	1

<?php

namespace ByJG\AnyDataset\Database;

use ByJG\AnyDataset\Enum\SQLFieldType;
use ByJG\AnyDataset\Enum\SQLType;
use ByJG\AnyDataset\Repository\DBDataset;
use ByJG\AnyDataset\Repository\SingleRow;
use DateTime;
use Exception;

class SQLHelper
{

    /**
     * @var DBDataset
     */
    private $_db;
    protected $_fieldDeliLeft = " ";
    protected $_fieldDeliRight = " ";

    /**
     *
     * @param DBDataset $db
     */
    public function __construct(DBDataset $db)
    {
        $this->_db = $db;
    }

    /**
     * Generate and Execute UPDATE and INSERTS
     *
     * @param string $table
     * @param array $fields
     * @param $param
     * @param SQLType|int $type
     * @param string $filter
     * @param string $decimalpoint
     * @return string
     * @throws Exception
     */
    public function generateSQL($table, $fields, &$param, $type = SQLType::SQL_INSERT, $filter = "", $decimalpoint = ".")
    {
        if ($fields instanceof SingleRow) {
            return $this->generateSQL($table, $fields->toArray(), $param, $type, $filter, $decimalpoint);
        }

        if ((is_null($param)) || (!is_array($param))) {
            $param = array();
        }

        $sql = "";
        if ($type == SQLType::SQL_UPDATE) {
            foreach ($fields as $fieldname => $fieldvalue) {
                if ($sql != "") {
                    $sql .= ", ";
                }
                $sql .= " " . $this->_fieldDeliLeft . $fieldname . $this->_fieldDeliRight . " = " . $this->getValue($fieldname,
                        $fieldvalue, $param, $decimalpoint) . " ";
            }
            $sql = "update $table set $sql where $filter ";
        } elseif ($type == SQLType::SQL_INSERT) {
            $campos = "";
            $valores = "";
            foreach ($fields as $fieldname => $fieldvalue) {
                if ($campos != "") {
                    $campos .= ", ";
                    $valores .= ", ";
                }
                $campos .= $this->_fieldDeliLeft . $fieldname . $this->_fieldDeliRight;
                $valores .= $this->getValue($fieldname, $fieldvalue, $param, $decimalpoint);
            }
            $sql = "insert into $table ($campos) values ($valores)";
        } elseif ($type == SQLType::SQL_DELETE) {
            if ($filter == "") {
                throw new Exception("I can't generate delete statements without filter");
            }
            $sql = "delete from $table where $filter";
        }
        return $sql;
    }

    /**
     * Generic Function
     *
     * @param string $name
     * @param string $valores
     * @param array $param
     * @param string $decimalpoint
     * @return string
     */
    protected function getValue($name, $valores, &$param, $decimalpoint)
    {
        $paramName = "[[" . $name . "]]";
        if (!is_array($valores)) {
            $valores = array(SQLFieldType::TEXT, $valores);
        }

        if ($valores[0] == SQLFieldType::BOOLEAN) {
            $param[$name] = 'N';
            if ($valores[1] == "1") {
                $param[$name] = 'S';
            }
            return $paramName;
        } elseif (strlen($valores[1]) == 0) { // Zero is Empty!?!?!?!?

65% of this comment could be valid code. Did you maybe forget this after debugging?

            return "null";
        } elseif ($valores[0] == SQLFieldType::TEXT) {
            $param[$name] = trim($valores[1]);
            return $paramName;
        } elseif ($valores[0] == SQLFieldType::DATE) {
            $date = ($valores[1] instanceof DateTime ? $valores[1]->format(DBBaseFunctions::YMDH) : $valores[1]);
            $param[$name] = $date;
            if ($this->getDbDataset()->getConnectionManagement()->getDriver() == 'oci8') {
                return "TO_DATE($paramName, 'YYYY-MM-DD')";
            }
            return $paramName;

        } elseif ($valores[0] == SQLFieldType::NUMBER) {
            $search = ($decimalpoint == ".") ? "," : ".";
            $valores[1] = trim(str_replace($search, $decimalpoint, $valores[1]));
            $param[$name] = $valores[1];
            return $paramName;
        }

        return $valores[1];
    }

    /**
     * Used to create a FILTER in a SQL string.
     *
     * @param string $fieldName
     * @param string $relation
     * @param array() $value

The doc-type array() could not be parsed: Expected "|" or "end of type", but got "(" at position 5. (view supported doc-types)

     * @param &string $sql (Full SQL)

The doc-type &string could not be parsed: Unknown type name "&string" at position 0. (view supported doc-types)

     * @param &string $param

The doc-type &string could not be parsed: Unknown type name "&string" at position 0. (view supported doc-types)

     */
    public function getWhereClause($fieldName, $relation, $value, &$sql, &$param)
    {
        if (strlen($sql) > 4) {
            $sql .= ' and ';
        }
        $sql = " $fieldName " . $relation . " " . $this->getValue($fieldName, $value, $param, '.');
    }

    public function setFieldDelimeters($left, $right)
    {
        $this->_fieldDeliLeft = $left;
        $this->_fieldDeliRight = $right;
    }

    public static function createSafeSQL($sql, $list)
    {
        foreach ($list as $key => $value) {
            $value = str_replace(["'", ';'], ["", ''], $value);
            $sql = str_replace($key, $value, $sql);
        }
        return $sql;
    }

    /**
     * @return DBDataset
     */
    public function getDbDataset()
    {
        return $this->_db;
    }
}