Bitrix-D7 22.6
 
Загрузка...
Поиск...
Не найдено
bulkoperation.php
1<?php
2
4
6
7
9{
11 private static $tableFields;
12
24 public static function bulkAdd(array $rows, $primary = null)
25 {
26 if (empty($rows))
27 {
28 return;
29 }
30 $tableName = static::getTableName();
31 $connection = Main\Application::getConnection();
32 $sqlHelper = $connection->getSqlHelper();
33
34 if (empty(static::$tableFields))
35 {
36 static::$tableFields = $connection->getTableFields($tableName);
37 }
38
39 $columns0 = \array_keys($rows[0]);
40 $columns = [];
41 foreach ($columns0 as $c)
42 {
43 $columns[$c] = $sqlHelper->quote(\mb_strtoupper($c));
44 }
45
46 $sqlValues = [];
47 foreach ($rows as $data)
48 {
49 foreach ($data as $columnName => $value)
50 {
51 $data[$columnName] = $sqlHelper->convertToDb($value, static::$tableFields[$columnName]);
52 }
53 $sqlValues[] = '('.\implode(', ', $data).')';
54 }
55 unset($data);
56
57 $sql = "INSERT INTO {$tableName} (".\implode(', ', $columns).") VALUES ".\implode(', ', $sqlValues);
58
59 $checkPrimary = false;
60 if (!empty($primary))
61 {
62 if (!\is_array($primary))
63 {
64 $primary = array($primary);
65 }
66 if (\count(\array_intersect($primary, $columns0)) > 0)
67 {
68 $checkPrimary = true;
69 }
70 }
71 if ($checkPrimary)
72 {
73 $sqlUpdate = array();
74 foreach (\array_diff($columns0, $primary) as $columnName)
75 {
76 $sqlUpdate[] = "{$columns[$columnName]} = VALUES({$columns[$columnName]})";
77 }
78 $sql .= " ON DUPLICATE KEY UPDATE ".\implode(', ', $sqlUpdate);
79 }
80
81 $connection->queryExecute($sql);
82 }
83
93 public static function bulkUpdate(array $fields, array $filter = [])
94 {
95 if (empty($fields))
96 {
97 return;
98 }
99 $tableName = static::getTableName();
100 $connection = Main\Application::getConnection();
101 $helper = $connection->getSqlHelper();
102
103 $update = $helper->prepareUpdate($tableName, $fields);
104 $valuesSql = $update[0];
105
106 if (!empty($valuesSql))
107 {
108 if (!empty($filter))
109 {
110 $hasSubQuery = false;
111 foreach ($filter as $field => $value)
112 {
113 if (\mb_strpos($field, '.') !== false)
114 {
115 $hasSubQuery = true;
116 break;
117 }
118 }
119 if ($hasSubQuery)
120 {
121 $whereSql = (static::query())
122 ->setSelect(['ID' => 'ID'])
123 ->setFilter($filter)
124 ->getQuery();
125
126 $querySql = "UPDATE {$tableName} target INNER JOIN ({$whereSql}) source ON target.ID = source.ID SET {$valuesSql} ";
127 }
128 else
129 {
130 $whereSql = Main\ORM\Query\Query::buildFilterSql(static::getEntity(), $filter);
131 $querySql = "UPDATE {$tableName} SET {$valuesSql} WHERE {$whereSql}";
132 }
133 }
134 else
135 {
136 $querySql = "UPDATE {$tableName} SET {$valuesSql}";
137 }
138
139 $connection->queryExecute($querySql);
140 }
141 }
142
151 public static function bulkDelete(array $filter = [])
152 {
153 $tableName = static::getTableName();
154 $connection = Main\Application::getConnection();
155
156 if (!empty($filter))
157 {
158 $hasSubQuery = false;
159 foreach ($filter as $field => $value)
160 {
161 if (\mb_strpos($field, '.') !== false)
162 {
163 $hasSubQuery = true;
164 break;
165 }
166 }
167 if ($hasSubQuery)
168 {
169 $whereSql = (static::query())
170 ->setSelect(['ID' => 'ID'])
171 ->setFilter($filter)
172 ->getQuery();
173
174 $querySql = "DELETE target FROM {$tableName} target INNER JOIN ({$whereSql}) source ON target.ID = source.ID";
175 }
176 else
177 {
178 $whereSql = Main\ORM\Query\Query::buildFilterSql(static::getEntity(), $filter);
179 $querySql = "DELETE FROM {$tableName} WHERE {$whereSql}";
180 }
181 }
182 else
183 {
184 $querySql = "TRUNCATE TABLE {$tableName}";
185 }
186
187 $connection->queryExecute($querySql);
188 }
189
190
199 private static function prepareWhere(array $filterFields, array $filterAlias = array())
200 {
201 $sqlHelper = Main\Application::getConnection()->getSqlHelper();
202
203 $where = array();
204 $logic = 'AND';
205 foreach ($filterFields as $key => $val)
206 {
207 if ($key === 'LOGIC')
208 {
209 $logic = $val;
210 continue;
211 }
212 $operator = '=';
213 if (!\is_numeric($key))
214 {
215 if (\preg_match("/^([=<>!@%]+)([^=<>!@%]+)$/", $key, $parts))
216 {
217 [, $operator, $key] = $parts;
218 }
219 if (\is_array($val) && !isset($val['LOGIC']))
220 {
221 if ($operator === '=')
222 {
223 $operator = '@';
224 }
225 elseif ($operator === '!')
226 {
227 $operator = '!@';
228 }
229 }
230 if (isset($filterAlias[$key]))
231 {
232 $key = $filterAlias[$key];
233 }
234 }
235 switch ($operator)
236 {
237 case '!':
238 $where[] = "$key != '". $sqlHelper->forSql($val). "'";
239 break;
240
241 case '%':
242 $where[] = "$key LIKE '%". $sqlHelper->forSql($val). "%'";
243 break;
244
245 case '!%':
246 $where[] = "$key NOT LIKE '%". $sqlHelper->forSql($val). "%'";
247 break;
248
249 case '@':
250 {
251 if (\is_array($val) && \count($val) > 0)
252 {
253 $val = \array_map(array($sqlHelper, 'forSql'), $val);
254 $where[] = "$key IN('".\implode("', '", $val)."')";
255 }
256 elseif (\is_string($val) && $val <> '')
257 {
258 $where[] = "$key IN(".$val.')';
259 }
260 break;
261 }
262
263 case '!@':
264 {
265 if (\is_array($val) && \count($val) > 0)
266 {
267 $val = \array_map(array($sqlHelper, 'forSql'), $val);
268 $where[] = "$key NOT IN('".\implode("', '", $val)."')";
269 }
270 elseif (\is_string($val) && $val <> '')
271 {
272 $where[] = "$key NOT IN(".$val.')';
273 }
274 break;
275 }
276
277 default:
278 {
279 if (\is_array($val))
280 {
281 $subLogic = 'AND';
282 if (isset($val['LOGIC']) && $val['LOGIC'] === 'OR')
283 {
284 $subLogic = 'OR';
285 unset($val['LOGIC']);
286 }
287
288 $condition = array();
289 foreach ($val as $k => $v)
290 {
291 $subOperator = '=';
292 if (\preg_match("/^([=<>!@%]+)([^=<>!@%]+)$/", $k, $parts))
293 {
294 [, $subOperator, $k] = $parts;
295 }
296 if (isset($filterAlias[$k]))
297 {
298 $k = $filterAlias[$k];
299 }
300 switch ($subOperator)
301 {
302 case '!':
303 $condition[] = "$k != '".$sqlHelper->forSql($v)."'";
304 break;
305 case '%':
306 $condition[] = "$k LIKE '%".$sqlHelper->forSql($v)."%'";
307 break;
308 case '!%':
309 $condition[] = "$k NOT LIKE '%".$sqlHelper->forSql($v)."%'";
310 break;
311 default:
312 $condition[] = "$k $subOperator '".$sqlHelper->forSql($v)."'";
313 }
314 }
315 $where[] = '('.\implode(" $subLogic ", $condition).')';
316 }
317 else
318 {
319 $where[] = "$key $operator '".$sqlHelper->forSql($val)."'";
320 }
321 break;
322 }
323 }
324 }
325
326 $whereSql = '';
327 if (\count($where))
328 {
329 $whereSql = ' AND '. \implode(" $logic ", $where);
330 }
331
332 return $whereSql;
333 }
334}