|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace ByJG\AnyDataset\Database; |
|
4
|
|
|
|
|
5
|
|
|
use ByJG\AnyDataset\Database\Expressions\DbBaseFunctions; |
|
6
|
|
|
use ByJG\AnyDataset\Enum\SQLFieldType; |
|
7
|
|
|
use ByJG\AnyDataset\Enum\SQLType; |
|
8
|
|
|
use ByJG\AnyDataset\Repository\DBDataset; |
|
9
|
|
|
use ByJG\AnyDataset\Repository\SingleRow; |
|
10
|
|
|
use DateTime; |
|
11
|
|
|
use Exception; |
|
12
|
|
|
|
|
13
|
|
|
class SqlHelper |
|
14
|
|
|
{ |
|
15
|
|
|
|
|
16
|
|
|
/** |
|
17
|
|
|
* @var DBDataset |
|
18
|
|
|
*/ |
|
19
|
|
|
private $dataset; |
|
20
|
|
|
protected $fieldDeliLeft = " "; |
|
21
|
|
|
protected $fieldDeliRight = " "; |
|
22
|
|
|
|
|
23
|
|
|
/** |
|
24
|
|
|
* @param DBDataset $dataset |
|
25
|
|
|
*/ |
|
26
|
|
|
public function __construct(DBDataset $dataset) |
|
27
|
|
|
{ |
|
28
|
|
|
$this->dataset = $dataset; |
|
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( |
|
44
|
|
|
$table, |
|
45
|
|
|
$fields, |
|
46
|
|
|
&$param, |
|
47
|
|
|
$type = SQLType::SQL_INSERT, |
|
48
|
|
|
$filter = "", |
|
49
|
|
|
$decimalpoint = "." |
|
50
|
|
|
) { |
|
51
|
|
|
if ($fields instanceof SingleRow) { |
|
52
|
|
|
return $this->generateSQL($table, $fields->toArray(), $param, $type, $filter, $decimalpoint); |
|
53
|
|
|
} |
|
54
|
|
|
|
|
55
|
|
|
if ((is_null($param)) || (!is_array($param))) { |
|
56
|
|
|
$param = array(); |
|
57
|
|
|
} |
|
58
|
|
|
|
|
59
|
|
|
$sql = ""; |
|
60
|
|
|
if ($type == SQLType::SQL_UPDATE) { |
|
61
|
|
|
foreach ($fields as $fieldname => $fieldvalue) { |
|
62
|
|
|
if ($sql != "") { |
|
63
|
|
|
$sql .= ", "; |
|
64
|
|
|
} |
|
65
|
|
|
$sql .= " " |
|
66
|
|
|
. $this->fieldDeliLeft |
|
67
|
|
|
. $fieldname |
|
68
|
|
|
. $this->fieldDeliRight |
|
69
|
|
|
. " = " |
|
70
|
|
|
. $this->getValue($fieldname, $fieldvalue, $param, $decimalpoint) |
|
71
|
|
|
. " "; |
|
72
|
|
|
} |
|
73
|
|
|
$sql = "update $table set $sql where $filter "; |
|
74
|
|
|
} elseif ($type == SQLType::SQL_INSERT) { |
|
75
|
|
|
$campos = ""; |
|
76
|
|
|
$valores = ""; |
|
77
|
|
|
foreach ($fields as $fieldname => $fieldvalue) { |
|
78
|
|
|
if ($campos != "") { |
|
79
|
|
|
$campos .= ", "; |
|
80
|
|
|
$valores .= ", "; |
|
81
|
|
|
} |
|
82
|
|
|
$campos .= $this->fieldDeliLeft . $fieldname . $this->fieldDeliRight; |
|
83
|
|
|
$valores .= $this->getValue($fieldname, $fieldvalue, $param, $decimalpoint); |
|
84
|
|
|
} |
|
85
|
|
|
$sql = "insert into $table ($campos) values ($valores)"; |
|
86
|
|
|
} elseif ($type == SQLType::SQL_DELETE) { |
|
87
|
|
|
if ($filter == "") { |
|
88
|
|
|
throw new Exception("I can't generate delete statements without filter"); |
|
89
|
|
|
} |
|
90
|
|
|
$sql = "delete from $table where $filter"; |
|
91
|
|
|
} |
|
92
|
|
|
return $sql; |
|
93
|
|
|
} |
|
94
|
|
|
|
|
95
|
|
|
/** |
|
96
|
|
|
* Generic Function |
|
97
|
|
|
* |
|
98
|
|
|
* @param string $name |
|
99
|
|
|
* @param string $valores |
|
100
|
|
|
* @param array $param |
|
101
|
|
|
* @param string $decimalpoint |
|
102
|
|
|
* @return string |
|
103
|
|
|
*/ |
|
104
|
|
|
protected function getValue($name, $valores, &$param, $decimalpoint) |
|
105
|
|
|
{ |
|
106
|
|
|
$paramName = "[[" . $name . "]]"; |
|
107
|
|
|
if (!is_array($valores)) { |
|
108
|
|
|
$valores = array(SQLFieldType::TEXT, $valores); |
|
109
|
|
|
} |
|
110
|
|
|
|
|
111
|
|
|
if ($valores[0] == SQLFieldType::BOOLEAN) { |
|
112
|
|
|
$param[$name] = 'N'; |
|
113
|
|
|
if ($valores[1] == "1") { |
|
114
|
|
|
$param[$name] = 'S'; |
|
115
|
|
|
} |
|
116
|
|
|
return $paramName; |
|
117
|
|
|
} elseif (strlen($valores[1]) == 0) { // Zero is Empty!?!?!?!? |
|
|
|
|
|
|
118
|
|
|
return "null"; |
|
119
|
|
|
} elseif ($valores[0] == SQLFieldType::TEXT) { |
|
120
|
|
|
$param[$name] = trim($valores[1]); |
|
121
|
|
|
return $paramName; |
|
122
|
|
|
} elseif ($valores[0] == SQLFieldType::DATE) { |
|
123
|
|
|
$date = ($valores[1] instanceof DateTime ? $valores[1]->format(DbBaseFunctions::YMDH) : $valores[1]); |
|
124
|
|
|
$param[$name] = $date; |
|
125
|
|
|
if ($this->getDbDataset()->getConnectionManagement()->getDriver() == 'oci8') { |
|
126
|
|
|
return "TO_DATE($paramName, 'YYYY-MM-DD')"; |
|
127
|
|
|
} |
|
128
|
|
|
return $paramName; |
|
129
|
|
|
|
|
130
|
|
|
} elseif ($valores[0] == SQLFieldType::NUMBER) { |
|
131
|
|
|
$search = ($decimalpoint == ".") ? "," : "."; |
|
132
|
|
|
$valores[1] = trim(str_replace($search, $decimalpoint, $valores[1])); |
|
133
|
|
|
$param[$name] = $valores[1]; |
|
134
|
|
|
return $paramName; |
|
135
|
|
|
} |
|
136
|
|
|
|
|
137
|
|
|
return $valores[1]; |
|
138
|
|
|
} |
|
139
|
|
|
|
|
140
|
|
|
/** |
|
141
|
|
|
* Used to create a FILTER in a SQL string. |
|
142
|
|
|
* |
|
143
|
|
|
* @param string $fieldName |
|
144
|
|
|
* @param string $relation |
|
145
|
|
|
* @param array() $value |
|
|
|
|
|
|
146
|
|
|
* @param &string $sql (Full SQL) |
|
|
|
|
|
|
147
|
|
|
* @param &string $param |
|
|
|
|
|
|
148
|
|
|
*/ |
|
149
|
|
|
public function getWhereClause($fieldName, $relation, $value, &$sql, &$param) |
|
150
|
|
|
{ |
|
151
|
|
|
if (strlen($sql) > 4) { |
|
152
|
|
|
$sql .= ' and '; |
|
153
|
|
|
} |
|
154
|
|
|
$sql = " $fieldName " . $relation . " " . $this->getValue($fieldName, $value, $param, '.'); |
|
155
|
|
|
} |
|
156
|
|
|
|
|
157
|
|
|
public function setFieldDelimeters($left, $right) |
|
158
|
|
|
{ |
|
159
|
|
|
$this->fieldDeliLeft = $left; |
|
160
|
|
|
$this->fieldDeliRight = $right; |
|
161
|
|
|
} |
|
162
|
|
|
|
|
163
|
|
|
public static function createSafeSQL($sql, $list) |
|
164
|
|
|
{ |
|
165
|
|
|
return str_replace(array_keys($list), array_values($list), $sql); |
|
166
|
|
|
} |
|
167
|
|
|
|
|
168
|
|
|
/** |
|
169
|
|
|
* @return DBDataset |
|
170
|
|
|
*/ |
|
171
|
|
|
public function getDbDataset() |
|
172
|
|
|
{ |
|
173
|
|
|
return $this->dataset; |
|
174
|
|
|
} |
|
175
|
|
|
} |
|
176
|
|
|
|
byjg /
anydataset
Loading history...
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.