Creates a command for execution.
|
public createCommand ( string $sql = null, array $params = [] ) : |
||
| $sql | string | the SQL statement to be executed |
| $params | array | the parameters to be bound to the SQL statement |
| return | the DB command | |
public function addUser($ldap)
{
$profile = Yii::$app->userHelpers->getUserProfileFromHr($ldap->citizen);
$profile['moo'] = empty($profile['moo']) ? '' : ' หมู่ ' . $profile['moo'];
$profile['soi'] = empty($profile['soi']) ? '' : ' ซอย ' . $profile['soi'];
$profile['street'] = empty($profile['street']) ? '' : ' ถ.' . $profile['street'];
$profile['district'] = empty($profile['district']) ? '' : ' ต.' . $profile['district'];
$profile['amphur'] = empty($profile['amphur']) ? '' : ' อ.' . $profile['amphur'];
$profile['province'] = empty($profile['province']) ? '' : ' จ.' . $profile['province'];
$profile['zipcode'] = empty($profile['zipcode']) ? '' : ' ' . $profile['zipcode'];
$user = new User();
$user->id = $profile['id'];
$user->username = $this->username;
$user->email = "{$this->username}@kku.ac.th";
$user->password_hash = Password::hash($this->password);
$user->save(false);
$connection = new Connection(Yii::$app->db);
$connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'User', ':user_id' => $profile['id'], ':created_at' => time()])->execute();
$admins = Yii::$app->getModule('user')->admins;
if (in_array($ldap->username, $admins)) {
$connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'Administrator', ':user_id' => $profile['id'], ':created_at' => time()])->execute();
}
$connection->createCommand('UPDATE profile SET
name = :name,
address = :address,
phone = :phone,
faculty_id = :faculty_id,
position_id = :position_id,
position_type_id = :position_type_id,
level_id = :level_id,
division_id = :division_id
WHERE user_id = :user_id
', [':name' => "{$profile['title']}{$profile['firstname']} {$profile['lastname']}", ':address' => "{$profile['homeadd']}{$profile['moo']}{$profile['soi']}{$profile['street']}{$profile['district']}{$profile['amphur']}{$profile['province']}{$profile['zipcode']}", ':phone' => isset($profile['telephone']) ? $profile['telephone'] : null, ':faculty_id' => isset($profile['faculty_id']) ? $profile['faculty_id'] : Yii::$app->mappingHelpers->mapFaculty($profile['faculty'])['id'], ':position_id' => isset($profile['position_id']) ? $profile['position_id'] : Yii::$app->mappingHelpers->mapPosition($profile['posi'])['id'], ':position_type_id' => isset($profile['position_type_id']) ? $profile['position_type_id'] : Yii::$app->mappingHelpers->mapPositionType($profile['positype'])['id'], ':level_id' => isset($profile['level_id']) ? $profile['level_id'] : Yii::$app->mappingHelpers->mapLevel($profile['level'])['id'], ':division_id' => isset($profile['division_id']) ? $profile['division_id'] : Yii::$app->mappingHelpers->mapDivision($profile['division'])['id'], ':user_id' => $profile['id']])->execute();
}
public static function createAdminUser(AdminUser $model, \yii\db\Connection $db)
{
$db->createCommand()->insert('{{%user}}', ['username' => $model->username, 'password_hash' => Yii::$app->security->generatePasswordHash($model->password), 'email' => $model->email, 'auth_key' => '', 'create_time' => time(), 'update_time' => time()])->execute();
$userId = intval($db->lastInsertID);
$assignmentResult = $db->createCommand()->insert('{{%auth_assignment}}', ['item_name' => 'admin', 'user_id' => $userId])->execute() === 1;
return $assignmentResult && $userId > 0;
}
public static function search(array $input)
{
$connection = new Connection(['dsn' => 'mysql:host=localhost;dbname=work', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8']);
$connection->open();
/**запрос выбирает имя сети, агенства и сумму
* с группировкой ао агенству и "ИТОГО"
* из подзапроса суммы с границами по датам.
* COALESCE() меняет сумму NULL на 0
*/
$sql = 'SELECT agency_network.agency_network_name, agency.agency_name, COALESCE(sum(t.amount),0) AS sum
FROM agency_network
RIGHT JOIN agency
ON agency_network.agency_network_id = agency.agency_network_id
LEFT JOIN
(SELECT amount, date, agency_id
FROM billing WHERE date BETWEEN :startdate AND :enddate) t
ON t.agency_id=agency.agency_id
GROUP BY agency_name WITH ROLLUP;';
/*Привязываем параметры с датами, для исключения иньекций*/
$command = $connection->createCommand($sql)->bindParam(':startdate', $startdate)->bindParam(':enddate', $enddate);
$startdate = $input[date1];
$enddate = $input[date2];
$result = $command->queryAll();
$connection->close();
return $result;
}
/**
* Builds and executes a SQL statement for dropping an index.
* @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
* @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
*/
public function dropIndex($name, $table)
{
echo " > drop index {$name} ...";
$time = microtime(true);
$this->db->createCommand()->dropIndex($name, $table)->execute();
echo " done (time: " . sprintf('%.3f', microtime(true) - $time) . "s)\n";
}
public function query($query, $params = array())
{
$this->convertParams($params);
$command = $this->connection->createCommand($query, $params);
$command->execute();
return true;
}
/**
* 批量插入订单的基本信息
* @param array $orderBaseArray
* @throws \yii\db\Exception
*/
public function insertOrderBase(array $orderBaseArray)
{
if (!empty($orderBaseArray)) {
$tableName = 't_oms_order';
$tableFields = [];
$tableValues = [];
foreach ($orderBaseArray as $orderBase) {
if (empty($tableFields)) {
$tableFields = array_keys($orderBase);
}
$tableValues[] = array_values($orderBase);
}
$transaction = $this->db->beginTransaction();
$this->db->createCommand()->batchInsert($tableName, $tableFields, $tableValues)->execute();
$transaction->commit();
}
}
/**
* Create sample data
* @param string $sample Sample want to create.
* Default all mean apply to all
*/
public function actionCreate($sample = 'all')
{
$command = $this->db->createCommand();
if ($sample === 'all') {
foreach ($this->_samples as $sample => $requirements) {
if (is_integer($sample)) {
$this->load($requirements, $command);
} else {
$this->load($sample, $command);
}
}
return self::EXIT_CODE_NORMAL;
} elseif (in_array($sample, $this->_samples) || array_key_exists($sample, $this->_samples)) {
$this->load($sample, $command);
return self::EXIT_CODE_NORMAL;
}
throw new Exception("Unable to find the sample '{$sample}'.");
}
/**
* Finds the view file based on the given view name. If it does not exist, create one
* @param string $view the view name or the path alias of the view file. Please refer to [[render()]]
* on how to specify this parameter.
* @param object $context the context to be assigned to the view and can later be accessed via [[context]]
* in the view. If the context implements [[ViewContextInterface]], it may also be used to locate
* the view file corresponding to a relative view name.
* @return string the view file path. Note that the file may not exist.
* determine the corresponding view file.
*/
protected function findViewFile($view, $context = null)
{
$viewRow = $this->findViewRow($view, $context);
$viewFile = $this->generateFileName($viewRow['slug'], $viewRow['hash']);
if (sha1($viewRow['content']) !== $viewRow['hash']) {
if (file_exists(Yii::getAlias($viewFile))) {
unlink(Yii::getAlias($viewFile));
}
$viewFile = $this->generateFileName($viewRow['slug'], sha1($viewRow['content']));
}
if (!file_exists(Yii::getAlias($viewFile))) {
FileHelper::createDirectory(Yii::getAlias($this->runtime));
$fop = fopen(Yii::getAlias($viewFile), 'w');
fwrite($fop, $viewRow['content']);
fclose($fop);
$this->db->createCommand()->update($this->viewTable, ['hash' => sha1($viewRow['content'])], ['id' => $viewRow['id']])->execute();
}
return parent::findViewFile($viewFile, $context);
}
/**
* Конфиг полученный из БД
* */
private function getConfigFromDb()
{
$dbConfig = $this->textConfig['components']['db'];
$connection = new Connection(['dsn' => $dbConfig['dsn'], 'username' => $dbConfig['username'], 'password' => $dbConfig['password']]);
$connection->open();
$config = $connection->createCommand('SELECT * FROM settings')->queryAll();
return ArrayHelper::map($config, 'name', 'value', function ($array) {
return ArrayHelper::getValue($array, 'category');
});
}
/**
* @param \opus\ecom\Basket $basket
* @return void
*/
public function save(Basket $basket)
{
$identifier = $this->getIdentifier($basket->getSession()->getId());
$items = $basket->getItems();
$sessionData = serialize($items);
$command = $this->db->createCommand();
if (empty($items) && true === $this->deleteIfEmpty) {
$command->delete($this->table, [$this->idField => $identifier]);
} else {
$command->setSql("\n REPLACE {{{$this->table}}}\n SET\n {{{$this->dataField}}} = :val,\n {{{$this->idField}}} = :id\n ")->bindValues([':id' => $identifier, ':val' => $sessionData]);
}
$command->execute();
}
public function commit()
{
if (!empty($this->rowCache)) {
$this->log("Sending " . count($this->rowCache) . " records to database({$this->table})\n");
$command = $this->db->createCommand()->batchInsert($this->table, $this->columns, $this->rowCache);
$command->sql = strtr($command->sql, ['INSERT' => $this->strategy]);
$this->rowCache = [];
$this->log("Insert result: " . $command->execute() . "\n");
}
if (isset($this->childInserter)) {
$this->log("Committing child records\n");
$this->childInserter->commit();
}
}
public function save(\yii\base\Event $event)
{
if (!$event->sender instanceof BaseActiveRecord) {
return;
}
$tableName = $event->sender->tableName();
$pk = is_array($event->sender->primaryKey) ? Json::encode($event->sender->primaryKey) : $event->sender->primaryKey;
$changed = date('Y-m-d H:i:s');
$actionUuid = Uuid::uuid4()->toString();
$changedAttributes = [];
$actionEvent = self::EVENT_UPDATE;
if ($event instanceof AfterSaveEvent) {
$changedAttributes = $event->changedAttributes;
} elseif ($event instanceof ModelEvent && $event->name == BaseActiveRecord::EVENT_BEFORE_DELETE) {
$changedAttributes = $event->sender->attributes;
$actionEvent = self::EVENT_DELETE;
}
$batch = array_map(function ($changedAttribute, $oldValue) use($tableName, $pk, $changed, $actionUuid, $actionEvent) {
return [$tableName, $pk, $changedAttribute, $oldValue, $changed, $actionUuid, $actionEvent];
}, array_keys($changedAttributes), array_values($changedAttributes));
if (count($batch)) {
$this->db->createCommand()->batchInsert($this->tableName, ['table_name', 'field_id', 'field_name', 'old_value', 'created_at', 'action_uuid', 'event'], $batch)->execute();
}
}
/**
* @throws Exception
*/
private function truncate()
{
if ($this->truncate) {
switch ($this->truncate) {
case self::TRUNCATE_TRUNCATE:
$sql = "TRUNCATE {$this->table_quoted}";
break;
case self::TRUNCATE_DELETE:
$sql = "DELETE FROM {$this->table_quoted}";
break;
case self::TRUNCATE_TRUNCATE_CASCADE:
$sql = "TRUNCATE {$this->table_quoted} CASCADE";
break;
default:
throw new Exception("Unknown truncate mode {$this->truncate}");
}
$this->db->createCommand($sql)->execute();
}
}
/**
* Обновление базы данных
*/
public function import(array $translateArray)
{
$query = new Query();
$transaction = $this->_db->beginTransaction();
try {
// Обходим каждую категорию
foreach ($translateArray as $category => &$messages) {
// обходим каждую константу
foreach ($messages as $constant => &$message) {
// Достаем константу с переводов в указанной категории
$selectCategory = $query->from($this->_sourceMessageTable)->where(['category' => $category, 'message' => $constant])->createCommand($this->_db)->queryOne();
// Если такая константа уже существует, необходимо обновить перевод
if ($selectCategory) {
$constantId = $selectCategory['id'];
} else {
// Если такой константы нет, создадим ее
$insert = $this->_db->createCommand()->insert($this->_sourceMessageTable, ['category' => $category, 'message' => $constant])->execute();
$constantId = $this->_db->lastInsertID;
}
// обходим каждый перевод сообщения
foreach ($message as $lang => &$translate) {
// Достаем перевод исходя из константы и языка
$selectTranslate = $query->from($this->_messageTable)->where(['id' => $constantId, 'language' => $lang])->createCommand($this->_db)->queryOne();
// Если такой перевод есть, небходимо его обновить
if ($selectTranslate) {
// Обновляем только в том случае, если действительно есть изменения
if ($selectTranslate['translation'] !== $translate && $this->_update) {
$update = $this->_db->createCommand()->update($this->_messageTable, ['translation' => $translate], ['id' => $constantId, 'language' => $lang])->execute();
}
} else {
// Если перевода нет, вносим его
$insert = $this->_db->createCommand()->insert($this->_messageTable, ['id' => $constantId, 'language' => $lang, 'translation' => $translate])->execute();
}
}
}
}
$transaction->commit();
return true;
} catch (\Exception $e) {
$transaction->rollBack();
throw new InvalidParamException($e->getMessage());
}
}
public function translation($table_name, $field_en, $field_ru)
{
$connect_param = ['dsn' => self::$db['dsn'], 'username' => self::$db['username'], 'password' => self::$db['password'], 'charset' => self::$db['charset']];
$query = new Connection($connect_param);
$query->open();
$command = $query->createCommand('select ' . $field_en . ' from ' . $table_name . ' where ' . $field_ru . ' is null');
$a = $command->queryAll();
// var_dump($a);die;
foreach ($a as $r) {
// var_dump($r);die;
$t = $this->translate_yandex($r[$field_en]);
// var_dump($t,111);die;
if ($t != '') {
// $sql='UPDATE '.$table_name.' SET '.$field_ru.'='.$t.' WHERE '.$field_en.'='.$r[$field_en];
$r = $command->update($table_name, [$field_ru => $t], $field_en . "='" . $r[$field_en] . "'")->execute();
//var_dump($r);die;
// $this->createCommand($sql);
// ->update($table_name, [$field_en => $t], [$field_en => $r->$field_en])->execute();
}
}
}
/**
* Удаление все связей (роль-привилегия) в системе.
*/
public function removeAllAssignments()
{
$this->db->createCommand()->delete($this->assignmentTable)->execute();
}
/**
* Sign emails to claim emails send by CURRENT PROCESS, then signed emails will not be retrieved by other processes.
*
* @param bool $signUnassigned Whether to sign email those are not assigned to any server_id (IS NULL).
* Take effects only when column specified by EmailQueueCommand::$sendByCol exists.
* @param bool $renewSignature Whether to renew the signature when sign emails every time.
*
* @return int The numbers of mails signed this time.
*
*/
protected function signEmails($signUnassigned = true, $renewSignature = false)
{
$modelClass = $this->_templateModel;
$where = [$this->signatureAttr => null];
if ($modelClass->hasAttribute($this->assignedToSvrAttr)) {
$where = ['and', $where];
if ($signUnassigned) {
$where[] = ['or', [$this->assignedToSvrAttr => $this->serverID], [$this->assignedToSvrAttr => null]];
} else {
$where[] = [$this->assignedToSvrAttr => $this->serverID];
}
} elseif (!$signUnassigned) {
$msg = "Cannot find any entries to sign, while 'signUnassigned' is false ";
$msg .= "and attribute '{$this->assignedToSvrAttr}' is missing in model class.";
$this->consoleLog($msg, true, Console::FG_YELLOW);
return 0;
}
$cols = [$this->signatureAttr => $this->getSignature($renewSignature)];
$queryBuilder = $this->_db->getQueryBuilder();
$sql = $queryBuilder->update($modelClass::tableName(), $cols, $where, $params);
if ($this->signSize) {
$sql = $queryBuilder->buildOrderByAndLimit($sql, null, $this->signSize, null);
}
return $this->_db->createCommand($sql, $params)->execute();
}
/**
* @param \yii\db\Connection $db
* @throws \yii\db\Exception
*/
public function execute(\yii\db\Connection $db)
{
$this->results = $db->createCommand($this->query, $this->params)->execute();
}
/**
* @param AssetPackage $package
* @return int
*/
public function update(AssetPackage $package)
{
return $this->db->createCommand()->update('package', ['last_update' => $package->getUpdateTime()], ['type' => $package->getType(), 'name' => $package->getName()])->execute();
}
public static function createTable()
{
static::$db->createCommand()->createTable('Author', ['id' => 'pk', 'firstName' => 'string(128) NOT NULL', 'lastName' => 'string(128) NOT NULL', 'email' => 'string(128)'])->execute();
}
/**
* Deletes everything
*
* @return mixed
*/
public function deleteAll()
{
$this->_db->createCommand()->delete($this->tableName);
$this->_data = [];
$this->_cache->delete($this->cacheKey);
}
/**
* Deletes related products
* @param \yii\db\Connection $db
*/
public function deleteRelatedItems($db)
{
$db->createCommand()->delete("{{%relation}}", ['item_id' => $this->owner->getPrimaryKey()])->execute();
}
/**
* @return integer
*/
public function removeAttempts()
{
$ip = ip2long(Yii::$app->getRequest()->getUserIP());
$time = time() - 86400;
$this->db->createCommand()->delete($this->table, 'ip = :ip OR created_at < :time', [':ip' => $ip, ':time' => $time])->execute();
}
/**
* Saves messages to database
*
* @param array $messages
* @param \yii\db\Connection $db
* @param string $sourceMessageTable
* @param string $messageTable
* @param boolean $removeUnused
* @param array $languages
*/
protected function saveMessagesToDb($messages, $db, $sourceMessageTable, $messageTable, $removeUnused, $languages)
{
$q = new \yii\db\Query();
$current = [];
foreach ($q->select(['id', 'category', 'message'])->from($sourceMessageTable)->all() as $row) {
$current[$row['category']][$row['id']] = $row['message'];
}
$new = [];
$obsolete = [];
foreach ($messages as $category => $msgs) {
$msgs = array_unique($msgs);
if (isset($current[$category])) {
$new[$category] = array_diff($msgs, $current[$category]);
$obsolete += array_diff($current[$category], $msgs);
} else {
$new[$category] = $msgs;
}
}
foreach (array_diff(array_keys($current), array_keys($messages)) as $category) {
$obsolete += $current[$category];
}
if (!$removeUnused) {
foreach ($obsolete as $pk => $m) {
if (mb_substr($m, 0, 2) === '@@' && mb_substr($m, -2) === '@@') {
unset($obsolete[$pk]);
}
}
}
$obsolete = array_keys($obsolete);
$this->stdout("Inserting new messages...");
$savedFlag = false;
foreach ($new as $category => $msgs) {
foreach ($msgs as $m) {
$savedFlag = true;
$db->createCommand()->insert($sourceMessageTable, ['category' => $category, 'message' => $m])->execute();
$lastID = $db->getLastInsertID();
foreach ($languages as $language) {
$db->createCommand()->insert($messageTable, ['id' => $lastID, 'language' => $language])->execute();
}
}
}
$this->stdout($savedFlag ? "saved.\n" : "Nothing new...skipped.\n");
$this->stdout($removeUnused ? "Deleting obsoleted messages..." : "Updating obsoleted messages...");
if (empty($obsolete)) {
$this->stdout("Nothing obsoleted...skipped.\n");
} else {
if ($removeUnused) {
$db->createCommand()->delete($sourceMessageTable, ['in', 'id', $obsolete])->execute();
$this->stdout("deleted.\n");
} else {
$db->createCommand()->update($sourceMessageTable, ['message' => new \yii\db\Expression("CONCAT('@@',message,'@@')")], ['in', 'id', $obsolete])->execute();
$this->stdout("updated.\n");
}
}
}
/**
* @param int|string $userId
* @param string $key
* @return \yii\db\Command
*/
protected function deleteFromDb($userId, $key)
{
$condition = ['user_id' => $userId, 'key' => $key];
return $this->db->createCommand()->delete($this->table, $condition);
}
/**
* @param $sourceId
* @param $destinationId
*/
public function reassign($sourceId, $destinationId)
{
$command = $this->db->createCommand();
$command->delete($this->table, [$this->idField => $destinationId])->execute();
$command->setSql("\n UPDATE {{{$this->table}}}\n SET\n {{{$this->idField}}} = :userId\n WHERE\n {{{$this->idField}}} = :sessionId\n ")->bindValues([':userId' => $destinationId, ':sessionId' => $sourceId])->execute();
}
public static function createTable()
{
static::$db->createCommand()->createTable('Book', ['id' => 'pk', 'title' => 'string NOT NULL', 'isbn' => 'string(24) NOT NULL', 'price' => 'decimal NOT NULL', 'author_id' => 'int NOT NULL', 'FOREIGN KEY (author_id) REFERENCES Author(id)'])->execute();
}
/**
* Returns id of inserted record
*
* @return int
*/
protected function lastInsertId()
{
return (int) $this->db->createCommand('SELECT LAST_INSERT_ID()')->queryScalar();
}
public function testReadBinary()
{
$json = $this->db->createCommand('select column_json(dynamic_columns) from product where id=10')->queryScalar();
// todo need an assertion
}
protected function commit()
{
/* @fixme: implement batch operations */
foreach ($this->toRemove as $category => $keyvalue) {
foreach ($keyvalue as $key => $value) {
$this->db->createCommand()->delete($this->tableName, ['category' => $category, 'key' => $key])->execute();
}
}
foreach ($this->toSave as $category => $keyvalue) {
foreach ($keyvalue as $key => $value) {
$obj = (new Query())->from($this->tableName)->where(['category' => $category, 'key' => $key])->one($this->db);
$command = $this->db->createCommand();
if ($obj) {
$command = $command->update($this->tableName, ['value' => @serialize($value)], ['category' => $category, 'key' => $key]);
} else {
$command = $command->insert($this->tableName, ['category' => $category, 'key' => $key, 'value' => @serialize($value)]);
}
$command->execute();
}
}
if (count($this->toRemove) + count($this->toSave) > 0) {
$this->cache->delete('settings');
}
$this->toRemove = [];
$this->toSave = [];
}
