PHP Connection::createCommand方法代码示例

    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();
         }
     }
 }