11 private static $tableFields;
24 public static function bulkAdd(array $rows, $primary =
null)
30 $tableName = static::getTableName();
31 $connection = Main\Application::getConnection();
32 $sqlHelper = $connection->getSqlHelper();
34 if (empty(static::$tableFields))
36 static::$tableFields = $connection->getTableFields($tableName);
39 $columns0 = \array_keys($rows[0]);
41 foreach ($columns0 as $c)
43 $columns[$c] = $sqlHelper->quote(\mb_strtoupper($c));
47 foreach ($rows as $data)
49 foreach ($data as $columnName => $value)
51 $data[$columnName] = $sqlHelper->convertToDb($value, static::$tableFields[$columnName]);
53 $sqlValues[] =
'('.\implode(
', ', $data).
')';
57 $sql =
"INSERT INTO {$tableName} (".\implode(
', ', $columns).
") VALUES ".\implode(
', ', $sqlValues);
59 $checkPrimary =
false;
62 if (!\is_array($primary))
64 $primary = array($primary);
66 if (\count(\array_intersect($primary, $columns0)) > 0)
74 foreach (\array_diff($columns0, $primary) as $columnName)
76 $sqlUpdate[] =
"{$columns[$columnName]} = VALUES({$columns[$columnName]})";
78 $sql .=
" ON DUPLICATE KEY UPDATE ".\implode(
', ', $sqlUpdate);
81 $connection->queryExecute($sql);
93 public static function bulkUpdate(array $fields, array $filter = [])
99 $tableName = static::getTableName();
100 $connection = Main\Application::getConnection();
101 $helper = $connection->getSqlHelper();
103 $update = $helper->prepareUpdate($tableName, $fields);
104 $valuesSql = $update[0];
106 if (!empty($valuesSql))
110 $hasSubQuery =
false;
111 foreach ($filter as $field => $value)
113 if (\mb_strpos($field,
'.') !==
false)
121 $whereSql = (static::query())
122 ->setSelect([
'ID' =>
'ID'])
126 $querySql =
"UPDATE {$tableName} target INNER JOIN ({$whereSql}) source ON target.ID = source.ID SET {$valuesSql} ";
130 $whereSql = Main\ORM\Query\Query::buildFilterSql(static::getEntity(), $filter);
131 $querySql =
"UPDATE {$tableName} SET {$valuesSql} WHERE {$whereSql}";
136 $querySql =
"UPDATE {$tableName} SET {$valuesSql}";
139 $connection->queryExecute($querySql);
151 public static function bulkDelete(array $filter = [])
153 $tableName = static::getTableName();
154 $connection = Main\Application::getConnection();
158 $hasSubQuery =
false;
159 foreach ($filter as $field => $value)
161 if (\mb_strpos($field,
'.') !==
false)
169 $whereSql = (static::query())
170 ->setSelect([
'ID' =>
'ID'])
174 $querySql =
"DELETE target FROM {$tableName} target INNER JOIN ({$whereSql}) source ON target.ID = source.ID";
178 $whereSql = Main\ORM\Query\Query::buildFilterSql(static::getEntity(), $filter);
179 $querySql =
"DELETE FROM {$tableName} WHERE {$whereSql}";
184 $querySql =
"TRUNCATE TABLE {$tableName}";
187 $connection->queryExecute($querySql);
199 private static function prepareWhere(array $filterFields, array $filterAlias = array())
201 $sqlHelper = Main\Application::getConnection()->getSqlHelper();
205 foreach ($filterFields as $key => $val)
207 if ($key ===
'LOGIC')
213 if (!\is_numeric($key))
215 if (\preg_match(
"/^([=<>!@%]+)([^=<>!@%]+)$/", $key, $parts))
217 [, $operator, $key] = $parts;
219 if (\is_array($val) && !isset($val[
'LOGIC']))
221 if ($operator ===
'=')
225 elseif ($operator ===
'!')
230 if (isset($filterAlias[$key]))
232 $key = $filterAlias[$key];
238 $where[] =
"$key != '". $sqlHelper->forSql($val).
"'";
242 $where[] =
"$key LIKE '%". $sqlHelper->forSql($val).
"%'";
246 $where[] =
"$key NOT LIKE '%". $sqlHelper->forSql($val).
"%'";
251 if (\is_array($val) && \count($val) > 0)
253 $val = \array_map(array($sqlHelper,
'forSql'), $val);
254 $where[] =
"$key IN('".\implode(
"', '", $val).
"')";
256 elseif (\is_string($val) && $val <>
'')
258 $where[] =
"$key IN(".$val.
')';
265 if (\is_array($val) && \count($val) > 0)
267 $val = \array_map(array($sqlHelper,
'forSql'), $val);
268 $where[] =
"$key NOT IN('".\implode(
"', '", $val).
"')";
270 elseif (\is_string($val) && $val <>
'')
272 $where[] =
"$key NOT IN(".$val.
')';
282 if (isset($val[
'LOGIC']) && $val[
'LOGIC'] ===
'OR')
285 unset($val[
'LOGIC']);
288 $condition = array();
289 foreach ($val as $k => $v)
292 if (\preg_match(
"/^([=<>!@%]+)([^=<>!@%]+)$/", $k, $parts))
294 [, $subOperator, $k] = $parts;
296 if (isset($filterAlias[$k]))
298 $k = $filterAlias[$k];
300 switch ($subOperator)
303 $condition[] =
"$k != '".$sqlHelper->forSql($v).
"'";
306 $condition[] =
"$k LIKE '%".$sqlHelper->forSql($v).
"%'";
309 $condition[] =
"$k NOT LIKE '%".$sqlHelper->forSql($v).
"%'";
312 $condition[] =
"$k $subOperator '".$sqlHelper->forSql($v).
"'";
315 $where[] =
'('.\implode(
" $subLogic ", $condition).
')';
319 $where[] =
"$key $operator '".$sqlHelper->forSql($val).
"'";
329 $whereSql =
' AND '. \implode(
" $logic ", $where);