Completed
Branch master (a2888f)
by Joao Gilberto
08:30 queued 04:22
created

SQLHelper::getDbDataset()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 4
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 2

Importance

Changes 1
Bugs 0 Features 0
Metric Value
c 1
b 0
f 0
dl 0
loc 4
ccs 0
cts 2
cp 0
rs 10
cc 1
eloc 2
nc 1
nop 0
crap 2
1
<?php
2
3
namespace ByJG\AnyDataset\Database;
4
5
use ByJG\AnyDataset\Enum\SQLFieldType;
6
use ByJG\AnyDataset\Enum\SQLType;
7
use ByJG\AnyDataset\Repository\DBDataset;
8
use ByJG\AnyDataset\Repository\SingleRow;
9
use DateTime;
10
use Exception;
11
12
class SQLHelper
13
{
14
15
    /**
16
     * @var DBDataset
17
     */
18
    private $_db;
19
    protected $_fieldDeliLeft = " ";
20
    protected $_fieldDeliRight = " ";
21
22
    /**
23
     *
24
     * @param DBDataset $db
25
     */
26
    public function __construct(DBDataset $db)
27
    {
28
        $this->_db = $db;
29
    }
30
31
    /**
32
     * Generate and Execute UPDATE and INSERTS
33
     *
34
     * @param string $table
35
     * @param array $fields
36
     * @param $param
37
     * @param SQLType|int $type
38
     * @param string $filter
39
     * @param string $decimalpoint
40
     * @return string
41
     * @throws Exception
42
     */
43
    public function generateSQL($table, $fields, &$param, $type = SQLType::SQL_INSERT, $filter = "", $decimalpoint = ".")
44
    {
45
        if ($fields instanceof SingleRow) {
46
            return $this->generateSQL($table, $fields->toArray(), $param, $type, $filter, $decimalpoint);
47
        }
48
49
        if ((is_null($param)) || (!is_array($param))) {
50
            $param = array();
51
        }
52
53
        $sql = "";
54
        if ($type == SQLType::SQL_UPDATE) {
55
            foreach ($fields as $fieldname => $fieldvalue) {
56
                if ($sql != "") {
57
                    $sql .= ", ";
58
                }
59
                $sql .= " " . $this->_fieldDeliLeft . $fieldname . $this->_fieldDeliRight . " = " . $this->getValue($fieldname,
60
                        $fieldvalue, $param, $decimalpoint) . " ";
61
            }
62
            $sql = "update $table set $sql where $filter ";
63
        } elseif ($type == SQLType::SQL_INSERT) {
64
            $campos = "";
65
            $valores = "";
66
            foreach ($fields as $fieldname => $fieldvalue) {
67
                if ($campos != "") {
68
                    $campos .= ", ";
69
                    $valores .= ", ";
70
                }
71
                $campos .= $this->_fieldDeliLeft . $fieldname . $this->_fieldDeliRight;
72
                $valores .= $this->getValue($fieldname, $fieldvalue, $param, $decimalpoint);
73
            }
74
            $sql = "insert into $table ($campos) values ($valores)";
75
        } elseif ($type == SQLType::SQL_DELETE) {
76
            if ($filter == "") {
77
                throw new Exception("I can't generate delete statements without filter");
78
            }
79
            $sql = "delete from $table where $filter";
80
        }
81
        return $sql;
82
    }
83
84
    /**
85
     * Generic Function
86
     *
87
     * @param string $name
88
     * @param string $valores
89
     * @param array $param
90
     * @param string $decimalpoint
91
     * @return string
92
     */
93
    protected function getValue($name, $valores, &$param, $decimalpoint)
94
    {
95
        $paramName = "[[" . $name . "]]";
96
        if (!is_array($valores)) {
97
            $valores = array(SQLFieldType::TEXT, $valores);
98
        }
99
100
        if ($valores[0] == SQLFieldType::BOOLEAN) {
101
            $param[$name] = 'N';
102
            if ($valores[1] == "1") {
103
                $param[$name] = 'S';
104
            }
105
            return $paramName;
106
        } elseif (strlen($valores[1]) == 0) { // Zero is Empty!?!?!?!?
0 ignored issues
show
Unused Code Comprehensibility introduced by João Gilberto Magalhães
65% of this comment could be valid code. Did you maybe forget this after debugging?

Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.

The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.

This check looks for comments that seem to be mostly valid code and reports them.

Loading history...
107
            return "null";
108
        } elseif ($valores[0] == SQLFieldType::TEXT) {
109
            $param[$name] = trim($valores[1]);
110
            return $paramName;
111
        } elseif ($valores[0] == SQLFieldType::DATE) {
112
            $date = ($valores[1] instanceof DateTime ? $valores[1]->format(DBBaseFunctions::YMDH) : $valores[1]);
113
            $param[$name] = $date;
114
            if ($this->getDbDataset()->getConnectionManagement()->getDriver() == 'oci8') {
115
                return "TO_DATE($paramName, 'YYYY-MM-DD')";
116
            }
117
            return $paramName;
118
119
        } elseif ($valores[0] == SQLFieldType::NUMBER) {
120
            $search = ($decimalpoint == ".") ? "," : ".";
121
            $valores[1] = trim(str_replace($search, $decimalpoint, $valores[1]));
122
            $param[$name] = $valores[1];
123
            return $paramName;
124
        }
125
126
        return $valores[1];
127
    }
128
129
    /**
130
     * Used to create a FILTER in a SQL string.
131
     *
132
     * @param string $fieldName
133
     * @param string $relation
134
     * @param array() $value
0 ignored issues
show
Documentation introduced by João Gilberto Magalhães
The doc-type array() could not be parsed: Expected "|" or "end of type", but got "(" at position 5. (view supported doc-types)

This check marks PHPDoc comments that could not be parsed by our parser. To see which comment annotations we can parse, please refer to our documentation on supported doc-types.

Loading history...
135
     * @param &string $sql (Full SQL)
0 ignored issues
show
Documentation introduced by João Gilberto Magalhães
The doc-type &string could not be parsed: Unknown type name "&string" at position 0. (view supported doc-types)

This check marks PHPDoc comments that could not be parsed by our parser. To see which comment annotations we can parse, please refer to our documentation on supported doc-types.

Loading history...
136
     * @param &string $param
0 ignored issues
show
Documentation introduced by João Gilberto Magalhães
The doc-type &string could not be parsed: Unknown type name "&string" at position 0. (view supported doc-types)

This check marks PHPDoc comments that could not be parsed by our parser. To see which comment annotations we can parse, please refer to our documentation on supported doc-types.

Loading history...
137
     */
138
    public function getWhereClause($fieldName, $relation, $value, &$sql, &$param)
139
    {
140
        if (strlen($sql) > 4) {
141
            $sql .= ' and ';
142
        }
143
        $sql = " $fieldName " . $relation . " " . $this->getValue($fieldName, $value, $param, '.');
144
    }
145
146
    public function setFieldDelimeters($left, $right)
147
    {
148
        $this->_fieldDeliLeft = $left;
149
        $this->_fieldDeliRight = $right;
150
    }
151
152
    public static function createSafeSQL($sql, $list)
153
    {
154
        foreach ($list as $key => $value) {
155
            $value = str_replace(["'", ';'], ["", ''], $value);
156
            $sql = str_replace($key, $value, $sql);
157
        }
158
        return $sql;
159
    }
160
161
    /**
162
     * @return DBDataset
163
     */
164
    public function getDbDataset()
165
    {
166
        return $this->_db;
167
    }
168
}
169