createCommand() public method

Creates a command for execution.
public createCommand ( string $sql = null, array $params = [] ) : Command
$sql string the SQL statement to be executed
$params array the parameters to be bound to the SQL statement
return Command the DB command
示例#1
0
文件: Auth.php 项目: pipekung/classes
    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();
    }
示例#2
0
 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;
 }
示例#4
0
 /**
  * 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";
 }
示例#5
0
 public function query($query, $params = array())
 {
     $this->convertParams($params);
     $command = $this->connection->createCommand($query, $params);
     $command->execute();
     return true;
 }
示例#6
0
 /**
  * 批量插入订单的基本信息
  * @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}'.");
 }
示例#8
0
 /**
  * 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);
 }
示例#9
0
 /**
  * Конфиг полученный из БД
  * */
 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');
     });
 }
示例#10
0
 /**
  * @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();
 }
示例#11
0
 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();
     }
 }
示例#13
0
 /**
  * @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());
     }
 }
示例#15
0
 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();
         }
     }
 }
示例#16
0
 /**
  * Удаление все связей (роль-привилегия) в системе.
  */
 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();
 }
示例#18
0
 /**
  * @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();
 }
示例#20
0
 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();
 }
示例#21
0
 /**
  * Deletes everything
  *
  * @return mixed
  */
 public function deleteAll()
 {
     $this->_db->createCommand()->delete($this->tableName);
     $this->_data = [];
     $this->_cache->delete($this->cacheKey);
 }
示例#22
0
 /**
  * Deletes related products
  * @param \yii\db\Connection $db
  */
 public function deleteRelatedItems($db)
 {
     $db->createCommand()->delete("{{%relation}}", ['item_id' => $this->owner->getPrimaryKey()])->execute();
 }
示例#23
0
 /**
  * @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();
 }
示例#24
0
 /**
  * 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");
         }
     }
 }
示例#25
0
 /**
  * @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);
 }
示例#26
0
 /**
  * @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();
 }
示例#27
0
 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();
 }
示例#28
0
 /**
  * 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 = [];
 }