Creates a DB command that can be used to execute this query.
|
public createCommand ( |
||
| $db | the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used. | |
| Résultat | the created DB command instance. | |
/**
* Perform the DB Query
*
* @return array
*/
public function perform()
{
// Create the command
$command = $this->query->createCommand();
// Execute the command and return
return $command->queryAll();
}
public function actionProspek()
{
$regional = Regional::find()->all();
$query = new Query();
$data = array();
foreach ($regional as $rows) {
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD3 IS NOT NULL')->all();
$handphone = $query->createCommand()->queryScalar();
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD6 IS NOT NULL')->all();
$email = $query->createCommand()->queryScalar();
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->join('LEFT OUTER JOIN', 'TABLE2', 'TABLE2.FIELD1 = TABLE3.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE2.FIELD7 IS NOT NULL')->all();
$phone = $query->createCommand()->queryScalar();
array_push($data, array('regional' => $rows->FIELD1, 'handphone' => $handphone, 'email' => $email, 'phone' => $phone));
}
return $this->render('prospek', ['data' => $data, 'regional' => $regional]);
}
/**
* @return \yii\web\Response
*/
public function actionLoadTranslations()
{
$sourceMessageTable = Adm::getInstance()->manager->createSourceMessageQuery('tableName');
$messageTable = Adm::getInstance()->manager->createMessageQuery('tableName');
/* @var $i18n \pavlinter\translation\I18N */
$i18n = Yii::$app->i18n;
$languages = $i18n->getLanguages();
$query = new Query();
$query->from($sourceMessageTable)->select(['id']);
/* @var $reader \yii\db\DataReader */
$reader = $query->createCommand()->query();
$count = 0;
while ($row = $reader->read()) {
$id = $row['id'];
foreach ($languages as $language_id => $language) {
$query = new Query();
$exists = $query->from($messageTable)->where(['id' => $id, 'language_id' => $language_id])->exists();
if (!$exists) {
Yii::$app->db->createCommand()->insert($messageTable, ['id' => $id, 'language_id' => $language_id, 'translation' => ''])->execute();
$count++;
}
}
}
Yii::$app->getSession()->setFlash('success', Adm::t('source-message', 'Loaded {count} translations.', ['count' => $count]));
return $this->redirect(['index']);
}
public function uploadImage($route, $gallery_types_id, $gallery_groups_id)
{
$type = (new Query())->select('*')->from($this->tableTypes)->where(['id' => $gallery_types_id])->createCommand()->queryOne();
$route = preg_replace('/\\/$/', '', $route);
$destination = preg_replace('/\\/$/', '', $type['destination']);
if (!is_dir($route . $destination)) {
if (!mkdir($route . $destination)) {
return ['success' => false, 'message' => 'Failed to add directory'];
}
}
$imageFile = UploadedFile::getInstanceByName('image');
$image = (new Query())->select('*')->from($this->tableImages)->where(['gallery_groups_id' => $gallery_groups_id])->andWhere(['name' => $imageFile->baseName . '.' . $imageFile->extension])->createCommand()->queryOne();
if ($image) {
return ['success' => false, 'message' => 'File downloaded previously in this group'];
}
$src_file = $route . $destination . '/' . $imageFile->baseName . '.' . $imageFile->extension;
$imageFile->saveAs($src_file, true);
$size = $this->getSize($src_file, $type);
$image_small = $this->renderFilename($route . $destination, $imageFile->extension);
$image_large = $this->renderFilename($route . $destination, $imageFile->extension);
Image::$driver = [Image::DRIVER_GD2];
Image::thumbnail($src_file, $size['small_width'], $size['small_height'])->save($route . $destination . '/' . $image_small . '.' . $imageFile->extension, ['quality' => $type['quality']]);
Image::thumbnail($src_file, $size['large_width'], $size['large_height'])->save($route . $destination . '/' . $image_large . '.' . $imageFile->extension, ['quality' => $type['quality']]);
unlink($src_file);
$query = new Query();
$query->createCommand()->insert($this->tableImages, ['gallery_groups_id' => $gallery_groups_id, 'small' => $destination . '/' . $image_small . '.' . $imageFile->extension, 'large' => $destination . '/' . $image_large . '.' . $imageFile->extension, 'name' => $imageFile->baseName . '.' . $imageFile->extension, 'seq' => $this->getLastSequence($gallery_groups_id) + 1])->execute();
return ['success' => true, 'gallery_images_id' => Yii::$app->db->getLastInsertID(), 'gallery_groups_id' => $gallery_groups_id, 'small' => $destination . '/' . $image_small . '.' . $imageFile->extension, 'large' => $destination . '/' . $image_large . '.' . $imageFile->extension];
}
/**
* Обновляет запись в таблицу
*
*/
public function update()
{
$query = new Query();
$command = $query->createCommand();
$command->update(self::TABLE, ['email' => $this->email, 'name_first' => $this->name_first, 'name_last' => $this->name_last, 'rating' => $this->rating], ['id' => $this->id])->execute();
return true;
}
/**
* @return html roles for roles column in admin index
*/
public function getRolesHTML()
{
$query = new Query();
$query->select('item_name')->from('{{%auth_assignment}}')->where('user_id=' . $this->id);
$command = $query->createCommand();
$roles = $command->queryAll();
return $roles;
}
/**
* 按 categoryid 查询该分类下的所有疾病
* @param category $categoryId 分类ID
*/
public function getDiseaseByCategoryId($categoryId)
{
$query = new Query();
$query->select(['cdr.id as id', 'cdr.diseaseid', 'cdr.class_level11', 'cdr.class_level2', 'd.name'])->from('9939_category_disease_rel as cdr')->join('LEFT_JOIN', '9939_disease as d', 'd.id = cdr.diseaseid');
$command = $query->createCommand(static::getDb());
$res = $command->queryAll();
return $res;
}
/**
* Creates database view.
* @param string $name View name.
* @param Query $query Query that is used to create view.
* @param bool $replace Whether to replace existing view with the same name.
* @throws \yii\db\Exception
* @see dropView
*/
public function createView($name, Query $query, $replace = true)
{
echo " > create table {$name} ...";
$time = microtime(true);
$sql = 'CREATE' . ($replace ? ' OR REPLACE' : '') . ' VIEW ' . $this->db->quoteTableName($name) . ' AS ' . $query->createCommand()->getRawSql();
$this->db->createCommand($sql)->execute();
echo ' done (time: ' . sprintf('%.3f', microtime(true) - $time) . "s)\n";
}
public static function getMeal()
{
$query = new Query();
$query->select(['meal.*', 'meal_image.image_new_url', 'meal_image.image_org_url'])->from('meal')->join('LEFT JOIN', 'meal_image', 'meal_image.meal_id = meal.id')->where(['deal_status' => 1]);
$command = $query->createCommand();
$data = $command->queryAll();
return $data;
}
/**
* Обновляет запись в таблицу
* @return boolean результат операции
*/
public function update()
{
$query = new Query();
$command = $query->createCommand();
$fields = ['password' => md5($this->password)];
$command->update(self::TABLE, $fields, ['id' => $this->id])->execute();
return true;
}
protected function getProperties()
{
$result = [];
$query = new Query();
$query->select(Property::tableName() . '.key, ' . Property::tableName() . '.name')->from(Property::tableName());
$query->innerJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id');
$query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->object->id]);
$command = $query->createCommand();
return ArrayHelper::map($command->queryAll(), 'key', 'name');
}
public function getData()
{
$query = new Query();
$query->select(Property::tableName() . '.id, ' . Property::tableName() . '.name')->from(Property::tableName());
$query->leftJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id');
$query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->objectId]);
$command = $query->createCommand();
$this->data = ArrayHelper::map($command->queryAll(), 'id', 'name');
return parent::getData();
}
/**
* Returns list of users' full names
* @param int $limit records count
* @return array list of users' names
*/
public static function getUserList($limit = null)
{
$query = new Query();
$query->select(['id', 'text' => "CONCAT(`u`.`first_name`,' ', `u`.`last_name`)"])->from(['u' => 'User']);
if (isset($limit)) {
$query->limit($limit);
}
$query->each();
$command = $query->createCommand();
return $command->queryAll();
}
/**
* @param Cart $cart
*
* @return array|mixed
*/
public function load(Cart $cart)
{
$items = [];
$identifier = $this->getIdentifier(Yii::$app->session->getId());
$query = new Query();
$query->select($this->dataField)->from($this->table)->where([$this->idField => $identifier]);
if ($data = $query->createCommand($this->_db)->queryScalar()) {
$items = unserialize($data);
}
return $items;
}
/**
* Обновляет запись в таблице
*/
public function update()
{
$query = new Query();
$command = $query->createCommand();
$command->update(self::TABLE, ['email' => $this->email, 'name_first' => $this->name_first, 'name_last' => $this->name_last], ['id' => $this->id])->execute();
$command->delete(self::TABLE_LINK, ['user_id' => $this->id])->execute();
foreach ($this->roles as $roleId) {
$command->insert(self::TABLE_LINK, ['user_id' => $this->id, 'role_id' => $roleId])->execute();
}
return true;
}
/**
* Displays a single Event model.
* @param integer $id
* @return mixed
*/
public function actionView($id)
{
$query = new Query();
$query->select(['*'])->distinct()->from('checkin')->where('event_id=' . $id)->join('JOIN', 'message', 'message.sender_id =checkin.user_id')->join('JOIN', 'user', 'user.id = checkin.user_id')->all();
$command = $query->createCommand();
$data = $command->queryAll();
$searchModel2 = new CheckinSearch();
$dataProvider2 = $searchModel2->searchUsers(\Yii::$app->user->identity->id);
//die();
return $this->render('view', ['model' => $this->findModel($id), 'messages' => $data, 'dataProvider2' => $dataProvider2]);
}
public function actionMahasiswalist($q = null)
{
$query = new Query();
$query->select('nama_mahasiswa, nim')->from('aitk_r_mahasiswa')->where('nama_mahasiswa LIKE "%' . $q . '%"')->orderBy('nama_mahasiswa');
$command = $query->createCommand();
$data = $command->queryAll();
$out = [];
foreach ($data as $d) {
$out[] = ['value' => $d['nama_mahasiswa'] . '(' . $d['nim'] . ')'];
}
echo Json::encode($out);
}
public function actionContactList($q = null)
{
$out = [];
if (!is_null($q)) {
$query = new Query();
$query->select('ID as id, Name As name')->from('pbk')->where('Name LIKE "%' . $q . '%"');
$command = $query->createCommand();
$data = $command->queryAll();
$out = array_values($data);
}
echo Json::encode($out);
}
public function actionGetNikkesList($q = null)
{
$query = new Query();
$query->select('nikkes')->from('peserta')->where('nikkes LIKE "%' . $q . '%"')->orderby('nikkes');
$command = $query->createCommand();
$data = $command->queryAll();
foreach ($data as $d) {
# code...
$out[] = ['value' => $d['nikkes']];
}
echo Json::encode($out);
}
public function updateStatus($stationId, $decimal)
{
$binary = Convert::dec2Bin($decimal, 10);
// get equipments
$query = new Query();
$query->select('b1.*, b2.sort')->from('equipment_status b1')->leftJoin('equipment b2', 'b1.equipment_id = b2.id')->where(['station_id' => $stationId]);
$equipments = $query->createCommand()->queryAll();
if (!empty($equipments)) {
foreach ($equipments as $e) {
Yii::$app->db->createCommand()->update('equipment_status', ['status' => $binary[$e['sort']]], ['station_id' => $stationId, 'equipment_id' => $e['equipment_id']])->execute();
}
}
}
/**
* Displays a single MealItem model.
* @param string $id
* @return mixed
*/
public function actionView($id)
{
$query = new Query();
$query->select(['meal_item.*', 'meal_item_image.image_new_url'])->from('meal_item')->join('LEFT JOIN', 'meal_item_image', 'meal_item_image.itemId = meal_item.id')->where(['meal_item.id' => $id]);
$command = $query->createCommand();
$data = $command->queryAll();
// echo '<pre>';
// print_r($data);die;
/* return $this->render('view', [
'model' => $this->findModel($id),
'productImages' => $productImages,
]);*/
return $this->render('view', ['model' => $data[0]]);
}
public function actionGetLastDate($nikkes)
{
$query = new Query();
$query->select('*')->from('monitoring_kacamata')->where('nikkes LIKE "' . $nikkes . '"')->orderby('tgl_ambil DESC')->limit(3);
$command = $query->createCommand();
$data = $command->queryAll();
//$out = "";
foreach ($data as $d) {
# code...
$out[] = ['nikkes' => $d['nikkes'], 'tgl_ambil' => $d['tgl_ambil'], 'hak' => $d['hak_kacamata_id']];
}
echo Json::encode(!empty($out) ? $out : null);
//echo Json::encode($out);
}
public function actionSearch($q = null)
{
if (!$q) {
return $this->render('/site/error', ['name' => 'Bad request', 'message' => 'q is not defined']);
}
\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$out = ['results' => ['id' => '', 'text' => '']];
$query = new Query();
$query->select('id, name AS text')->from('student')->where(['like', 'name', $q])->limit(20);
$command = $query->createCommand();
$data = $command->queryAll();
$out['results'] = array_values($data);
return $out;
}
public function actionGetmessages($mr_id)
{
$model = new RoomsConversations();
/*$model=new RoomsConversations();
$model = RoomsConversations::find()->where(['mr_id'=>$mr_id])->all();
return $model;
*/
$query = new Query();
$jointype = "LEFT OUTER JOIN";
$query->select('personal_information.pi_id,personal_information.pi_name,
user.id,message_rooms.mr_id,conversations.cr_id,conversations.message,conversations.timestamp,message_rooms.user_id_2')->from('rooms_conversations')->join($jointype, 'message_rooms', 'message_rooms.mr_id=rooms_conversations.mr_id')->join($jointype, 'conversations', 'rooms_conversations.cr_id=conversations.cr_id')->join($jointype, 'user', 'user.id=conversations.user_id')->join($jointype, 'personal_information', 'user.pi_id=personal_information.pi_id')->where(['rooms_conversations.mr_id' => $mr_id]);
$command = $query->createCommand();
$data = $command->queryAll();
return $data;
}
/**
* Action Ajax for searc customer list in select2 widget
* @param [type] $search [description]
* @param [type] $id [description]
* @return [json] JSON['customer_id'=>'name']
*/
public function actionSearchCustomer($search = null, $id = null)
{
$out = ['more' => false];
$q = new Query();
if (!is_null($search)) {
$q->select('p.id, p.name as text')->from(ResPartner::tableName() . ' as p')->where('LOWER(p.name) LIKE :search')->andWhere('employee is false and customer is true')->addParams([':search' => '%' . strtolower($search) . '%']);
$users = $q->createCommand()->queryAll();
$out['results'] = array_values($users);
} elseif ($id > 0) {
$out['results'] = ['id' => $id, 'text' => ResPartner::find()->where(['id' => $id])->one()->name];
} else {
$out['results'] = ['id' => 0, 'text' => 'No matching records found'];
}
echo Json::encode($out);
}
/**
* @param int $limit
*
* @param null $version
*
* @return array
*/
protected function getMigrationHistory($limit, $version = null)
{
if ($this->db->schema->getTableSchema($this->migrationTable, true) === null) {
$this->createMigrationHistoryTable();
}
$query = new Query();
$query->select(['version', 'apply_time'])->from($this->migrationTable)->orderBy('version DESC')->limit($limit);
if ($version) {
$query->where(['app_version' => $version]);
}
$rows = $query->createCommand($this->db)->queryAll();
$history = ArrayHelper::map($rows, 'version', 'apply_time');
unset($history[self::BASE_MIGRATION]);
return $history;
}
public function run($search = null, $id = null)
{
$out = ['more' => false];
if (!is_null($search)) {
$query = new Query();
$query->select('[[id]], [[username]] AS [[text]]')->from('{{%user}}')->filterWhere(['like', '[[username]]', $search])->limit(20);
$command = $query->createCommand();
$data = $command->queryAll();
$out['results'] = array_values($data);
} elseif ($id > 0) {
$out['results'] = ['id' => $id, 'text' => User::findOne($id)->username];
} else {
$out['results'] = ['id' => 0, 'text' => Yii::t('app', 'No matching records found')];
}
return Json::encode($out);
}
public function actionPartnerlist($search = null, $id = null)
{
$out = ['more' => false];
if (!is_null($search)) {
$query = new Query();
$query->select('id, name AS text')->from('res_partner')->where(['like', 'name', $search])->andWhere(['customer' => true])->andWhere(['is_company' => true])->limit(20);
$command = $query->createCommand();
$data = $command->queryAll();
$out['results'] = array_values($data);
} elseif ($id > 0) {
$out['results'] = ['id' => $id, 'text' => ResPartner::find($id)->name];
} else {
$out['results'] = ['id' => 0, 'text' => 'No matching records found'];
}
echo Json::encode($out);
}
/**
* Lists all Test1 models.
* @return mixed
*/
public function actionIndex()
{
$params = $_REQUEST;
$filter = array();
$sort = "";
$page = 1;
$limit = 10;
if (isset($params['page'])) {
$page = $params['page'];
}
if (isset($params['limit'])) {
$limit = $params['limit'];
}
$offset = $limit * ($page - 1);
/* Filter elements */
if (isset($params['filter'])) {
$filter = (array) json_decode($params['filter']);
}
if (isset($params['datefilter'])) {
$datefilter = (array) json_decode($params['datefilter']);
}
if (isset($params['sort'])) {
$sort = $params['sort'];
if (isset($params['order'])) {
if ($params['order'] == "false") {
$sort .= " desc";
} else {
$sort .= " asc";
}
}
}
$query = new Query();
$query->offset($offset)->limit($limit)->from('test1')->orderBy($sort);
$query->andFilterWhere(['id' => $filter['id'], 'createdAt' => $filter['createdAt'], 'updatedAt' => $filter['updatedAt']]);
$query->andFilterWhere(['like', 'name', $filter['name']]);
if ($datefilter['from']) {
$query->andWhere("createdAt >= '" . $datefilter['from'] . "' ");
}
if ($datefilter['to']) {
$query->andWhere("createdAt <= '" . $datefilter['to'] . "'");
}
$command = $query->createCommand();
$models = $command->queryAll();
$totalItems = $query->count();
$this->setHeader(200);
echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT);
}
public function __set($name, $value)
{
if (is_array($value) && count($value) > 0 && !$value[0] instanceof Object || !is_array($value) && !$value instanceof Object) {
$getter = 'get' . $name;
/** @var ActiveQuery $activeQuery */
$activeQuery = $this->owner->{$getter}();
/* @var $modelClass ActiveRecord */
$modelClass = $activeQuery->modelClass;
$value = $modelClass::findAll($value);
if (!empty($activeQuery->via)) {
list($junctionTable, $primaryModelColumn, $relatedModelColumn) = $this->parseQuery($activeQuery);
$query = new Query(['select' => [$relatedModelColumn], 'from' => [$junctionTable], 'where' => [$primaryModelColumn => $this->owner->primaryKey]]);
$this->oldRelations[$name] = $query->createCommand()->queryColumn();
}
}
$this->owner->populateRelation($name, $value);
}
