public function actionIndex() { $query = new \yii\db\Query(); $tasks = $query->select('*')->from('task')->all(); $projects = $query->select('*')->from('project p')->leftjoin('project_user as pu', 'p.id=pu.project_id')->where(['user_id' => 2])->limit(20)->all(); $data = ['user' => 'Hugo.huang', 'js' => JS, 'css' => CSS . ',css/home/index.css', 'nav' => 'task', 'tasks' => $tasks, 'projects' => $projects]; return $this->renderPartial('index.html', $data); }
public function search($params) { $query = new \yii\db\Query(); if (!$this->start_date) { $this->start_date = date("Y-m-d 00:00:00"); $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } $dataProvider = new ActiveDataProvider(['db' => self::getDb(), 'query' => $query->from(SqlTraceTop50::tableName())]); $query->select(['sqltext', 'sum(amount) as amount', 'queryusemaxtime', 'databasetype', 'sqlquerytime', 'ip']); $query->groupBy('querymd5'); $query->orderBy('queryusemaxtime desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } $query->andFilterWhere(['>=', 'queryusemaxtime', $this->queryusemaxtime]); if ($this->databasetype && $this->databasetype != 'all') { $query->andFilterWhere(['databasetype' => $this->databasetype]); } $query->andFilterWhere(['>=', 'queryusemaxtime', $this->queryusemaxtime]); $query->andFilterWhere(['>=', 'sqlquerytime', $this->start_date]); $query->andFilterWhere(['<', 'sqlquerytime', $this->end_date]); $query->groupBy('querymd5'); $query->orderBy('queryusemaxtime desc'); return $dataProvider; }
public function up() { $this->insert('{{property_group}}', ['object_id' => 3, 'name' => Yii::t('app', 'Special actions'), 'is_internal' => 1, 'hidden_group_title' => 1]); $propertyGroupId = $this->db->lastInsertID; $this->addColumn('{{%product_property}}', 'todays_deals', Schema::TYPE_INTEGER); $this->addColumn('{{%product_property}}', 'sale', Schema::TYPE_INTEGER); $this->insert('{{%property}}', ['property_group_id' => $propertyGroupId, 'name' => Yii::t('app', 'Show in Today\'s deals'), 'key' => 'todays_deals', 'value_type' => 'NUMBER', 'property_handler_id' => 3, 'is_column_type_stored' => 1, 'handler_additional_params' => '{}']); $todaysDealsPropertyId = $this->db->lastInsertID; $this->insert('{{%property}}', ['property_group_id' => $propertyGroupId, 'name' => Yii::t('app', 'Show in Sale'), 'key' => 'sale', 'value_type' => 'NUMBER', 'property_handler_id' => 3, 'is_column_type_stored' => 1, 'handler_additional_params' => '{}']); $salePropertyId = $this->db->lastInsertID; $q = new \yii\db\Query(); $ids = $q->select('id')->from('{{%product}}')->where(['like', 'name', 'Lenovo'])->column(); srand(); foreach ($ids as $id) { $this->insert('{{%object_property_group}}', ['object_id' => 3, 'object_model_id' => $id, 'property_group_id' => $propertyGroupId]); $this->insert('{{%product_property}}', ['object_model_id' => $id, 'todays_deals' => rand(0, 1), 'sale' => rand(0, 1)]); } $this->insert('{{%filter_sets}}', ['category_id' => 3, 'sort_order' => 4, 'property_id' => $salePropertyId]); $this->insert('{{%filter_sets}}', ['category_id' => 3, 'sort_order' => 3, 'property_id' => $todaysDealsPropertyId]); $this->insert('{{%prefiltered_pages}}', ['slug' => 'sale', 'active' => '1', 'last_category_id' => '3', 'params' => '{"' . $salePropertyId . '":"1"}', 'title' => 'Total sale in our awesome shop!', 'announce' => NULL, 'content' => '<p>This is an example content for our prefiltered page</p>', 'h1' => 'Total sale - up to 20%', 'meta_description' => 'Harry up! Total sale in our awesome shop - up to 20%!', 'breadcrumbs_label' => 'Total sale', 'view_id' => '0']); $this->insert('{{%prefiltered_pages}}', ['slug' => 'todays-deals', 'active' => '1', 'last_category_id' => '3', 'params' => '{"' . $todaysDealsPropertyId . '":"1"}', 'title' => 'Today\'s deals!', 'announce' => NULL, 'content' => '<p>Here\'s some hand-picked products for the Deal of the day!</p>', 'h1' => 'Deal of the day - up to 30%!', 'meta_description' => 'Deal of the day in our shop!', 'breadcrumbs_label' => 'Deal of the day', 'view_id' => '0']); $this->insert('{{navigation}}', ['parent_id' => 1, 'name' => Yii::t('app', 'Today\'s deals'), 'url' => '/todays-deals', 'sort_order' => -1]); $this->insert('{{navigation}}', ['parent_id' => 1, 'name' => Yii::t('app', 'Sale'), 'url' => '/sale', 'sort_order' => 3]); $this->update('{{%page}}', ['slug_compiled' => ':mainpage:', 'content' => 'You can edit content of main page in backend/page section'], ['id' => 1]); $this->insert('{{%view}}', ['name' => 'Main page', 'view' => '@app/extensions/demo/views/main-page.php']); $this->insert('{{%view_object}}', ['view_id' => $this->db->lastInsertID, 'object_id' => 1, 'object_model_id' => 1]); }
public function actionEmpinforeport() { $employee_data = $selected_list = array(); if (!empty(Yii::$app->request->post('e_info'))) { $selected_list = Yii::$app->request->post('e_info'); $employee_d = new \yii\db\Query(); $employee_d->select('*')->from('emp_master emp')->join('join', 'emp_info e_info', 'e_info.emp_info_id = emp.emp_master_emp_info_id')->join('join', 'emp_department emp_dep', 'emp_dep.emp_department_id = emp.emp_master_department_id ')->join('join', 'emp_designation emp_des', 'emp_des.emp_designation_id = emp.emp_master_designation_id ')->join('join', 'emp_category emp_cat', 'emp_cat.emp_category_id = emp. emp_master_category_id')->leftJoin('emp_address eadd', 'eadd.emp_address_id = emp.emp_master_emp_address_id')->where(['emp.is_status' => 0])->andFilterWhere(['emp.emp_master_department_id' => Yii::$app->request->post('department')])->andFilterWhere(['emp.emp_master_designation_id' => Yii::$app->request->post('designation')])->andFilterWhere(['emp.emp_master_emp_address_id' => Yii::$app->request->post('city')])->andFilterWhere(['emp.emp_master_category_id' => Yii::$app->request->post('category')])->andFilterWhere(['e_info.emp_gender' => Yii::$app->request->post('gender')]); $command = $employee_d->createCommand(); $employee_data = $command->queryAll(); Yii::$app->session->set('data["empData"]', $employee_data); Yii::$app->session->set('data["selection"]', $selected_list); if (empty($employee_data)) { \Yii::$app->getSession()->setFlash('emperror', "<i class='fa fa-exclamation-triangle'></i> <b>" . Yii::t('report', 'No Record Found For This Criteria.') . "</b>"); return $this->redirect(['empinforeport']); } return $this->render('emp_info_report', ['employee_data' => $employee_data, 'selected_list' => $selected_list]); } else { if (Yii::$app->request->get('exportExcel')) { $file = $this->renderPartial('emp_info_report_excel', array('employee_data' => Yii::$app->session->get('data["empData"]'), 'selected_list' => Yii::$app->session->get('data["selection"]'))); $fileName = "Employee_info_report" . date('YmdHis') . '.xls'; $options = ['mimeType' => 'application/vnd.ms-excel']; return Yii::$app->excel->exportExcel($file, $fileName, $options); } else { if (Yii::$app->request->get('exportPDF')) { $html = $this->renderPartial('emp_info_report_pdf', array('employee_data' => Yii::$app->session->get('data["empData"]'), 'selected_list' => Yii::$app->session->get('data["selection"]'))); ob_clean(); return Yii::$app->pdf->exportData('Employee Info Report', 'Employee_info_report', $html); } } } return $this->render('emp_report_view', ['selected_list' => $selected_list]); }
public function search($params, $personal = false) { /* User identifier */ $userIdentifier = \Yii::$app->user->getId() ? \Yii::$app->user->getId() : 0; /* Base query */ $query = new \yii\db\Query(); /* Selector */ $query->select(['"projects".*', 'COUNT("boxes"."id") as boxCount']); /* Table */ $query->from('projects'); /* Joins */ $query->join('LEFT JOIN', 'boxes', '"projects"."id" = "boxes"."project_id" AND "boxes"."deleted" = FALSE'); $query->join('LEFT JOIN', 'project_counters', '"projects"."id" = "project_counters"."project_id" AND "project_counters"."user_id" = :user_id', ['user_id' => $userIdentifier]); /* Conditions */ $query->where(['"projects"."deleted"' => false]); if (!$personal) { if (!is_object(Yii::$app->user->getIdentity()) || is_object(Yii::$app->user->getIdentity()) && !Yii::$app->user->getIdentity()->hasRole(['validator', 'administrator'])) { $query->andWhere(['or', ['"projects"."owner_id"' => $userIdentifier], ['"projects"."main_observer_id"' => $userIdentifier], ['or', ['is', '"projects"."embargo"', NULL], ['<=', '"projects"."embargo"', 'NOW()']], ['is not', '"project_counters"."user_id"', NULL]]); } } else { $query->andWhere(['or', ['"projects"."owner_id"' => $userIdentifier], ['"projects"."main_observer_id"' => $userIdentifier], ['is not', '"project_counters"."user_id"', NULL]]); } /* Group */ $query->groupBy('"projects"."id"'); $dataProvider = new ActiveDataProvider(['query' => $query]); $this->load($params); return $dataProvider; }
public function actionIndex3() { $query = new \yii\db\Query(); $rawdata = $query->select('*')->from('person')->where(['id' => [1, 2, 3]])->createCommand()->queryAll(); $provider = new \yii\data\ArrayDataProvider(['allModels' => $rawdata, 'sort' => ['attributes' => array_keys($rawdata[0])], 'pagination' => ['pageSize' => 10]]); return $this->render('index3', ['dataProvider' => $provider]); }
/** * */ public static function changeMailConfig() { $key = static::className() . '-econfig'; $row = Yii::$app->cache->get($key); if ($row === false) { $row = static::find()->asArray()->one(); $query = new \yii\db\Query(); $sql = $query->select('MAX(updated_at)')->from(static::tableName())->createCommand()->getRawSql(); Yii::$app->cache->set($key, $row, 86400, new \yii\caching\DbDependency(['sql' => $sql])); } Yii::$app->params['adminEmail'] = $row['from_email']; Yii::$app->params['adminName'] = $row['from_name']; if ($row['from_name'] !== '') { Yii::$app->params['adminEmailName'] = [$row['from_email'] => $row['from_name']]; } else { Yii::$app->params['adminEmailName'] = $row['from_email']; } /* @var \yii\swiftmailer\Mailer $mailer */ $mailer = Yii::$app->mailer; if ($row['enable_smtp']) { $transport = ['class' => 'Swift_SmtpTransport', 'host' => $row['host'], 'username' => $row['username'], 'password' => $row['password'], 'port' => $row['port'], 'encryption' => $row['encryption']]; $mailer->setTransport($transport); $mailer->getSwiftMailer(); //rewrite Instance } }
public function search($params) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTraceSqlNumber::tableName())]); $query->orderBy('Amount desc'); $this->load($params); if (!$this->validate()) { return $dataProvider; } if ($this->update_time) { $this->start_date = $this->update_time; $this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date))); } if (!empty($this->databasetype)) { $query = new \yii\db\Query(); $dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTraceSqlNumber::tableName())]); } $query->andFilterWhere(['databasetype' => $this->databasetype]); $query->select("sqltext,databasetype,update_time,sum(Amount) as sAmount"); $query->andFilterWhere(['>=', 'update_time', $this->start_date]); $query->andFilterWhere(['<', 'update_time', $this->end_date]); $query->groupBy('sqltext_md5'); $query->orderBy('sAmount desc'); return $dataProvider; }
public function getPosts() { $query = new \yii\db\Query(); $query->select('posts.*, user_name, category_name')->from('posts')->leftJoin('users', ' posts.post_author = users.id')->innerJoin('posts_category', ' posts.post_category = posts_category.id'); $command = $query->createCommand(); return $posts_category = $command->queryAll(); }
public function getProduct($product_id) { $query = new \yii\db\Query(); $query->select(['*', 'product_description.name AS name', 'product.image', 'manufacturer.name AS manufacturer', '(SELECT price FROM product_discount pd2 WHERE pd2.product_id = product.product_id AND pd2.customer_group_id = "' . (int) \Yii::$app->params['config_customer_group_id'] . '" AND pd2.quantity = "1" AND ((pd2.date_start = "0000-00-00" OR pd2.date_start < NOW()) AND (pd2.date_end = "0000-00-00" OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount', '(SELECT price FROM product_special ps WHERE ps.product_id = product.product_id AND ps.customer_group_id = "' . (int) \Yii::$app->params['config_customer_group_id'] . '" AND ((ps.date_start = "0000-00-00" OR ps.date_start < NOW()) AND (ps.date_end = "0000-00-00" OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special', '(SELECT points FROM product_reward pr WHERE pr.product_id = product.product_id AND pr.customer_group_id ="' . (int) \Yii::$app->params['config_customer_group_id'] . '") AS reward', '(SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = product.stock_status_id AND ss.language_id ="' . (int) \Yii::$app->params['config_language_id'] . '") AS stock_status', '(SELECT wcd.unit FROM weight_class_description wcd WHERE product.weight_class_id = wcd.weight_class_id AND wcd.language_id = "' . (int) \Yii::$app->params['config_language_id'] . '") AS weight_class', '(SELECT lcd.unit FROM length_class_description lcd WHERE product.length_class_id = lcd.length_class_id AND lcd.language_id = "' . (int) \Yii::$app->params['config_language_id'] . '") AS length_class', '(SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = product.product_id AND r1.status = "1" GROUP BY r1.product_id) AS rating', '(SELECT COUNT(*) AS total FROM review r2 WHERE r2.product_id = product.product_id AND r2.status = "1" GROUP BY r2.product_id) AS reviews', 'product.sort_order'])->from('product')->join('LEFT JOIN', 'product_description', 'product.product_id = product_description.product_id')->join('LEFT JOIN', 'product_to_store', 'product.product_id = product_to_store.product_id')->join('LEFT JOIN', 'manufacturer', 'product.manufacturer_id = manufacturer.manufacturer_id'); $query->where('product.product_id = :product_id ' . 'AND product_description.language_id = :language_id ' . 'AND product.status = :status ' . 'AND product.date_available <= NOW() ' . 'AND product_to_store.store_id = :store_id ', [':product_id' => (int) $product_id, ':language_id' => (int) \Yii::$app->params['config_language_id'], ':status' => 1, ':store_id' => (int) \Yii::$app->params['store_id']]); return $query->one(); }
/** * Displays a single ReportClassified model. * @param integer $id * @return mixed */ public function actionView($id) { $queryReport = new \yii\db\Query(); $queryReport->select(['report_classified.id', 'report_classified.message', 'report_classified.email_reporter', 'report_classified.create_at', 'report_classified.update_at', 'report_classified.checked', 'report_classified.type', 'report_classified.classified_id', 'subject_report.subject'])->from('report_classified')->join('JOIN', 'subject_report', 'subject_report.id = report_classified.subject_id')->where(['report_classified.id' => $id])->all(); $commandReport = $queryReport->createCommand(); $dataReport = $commandReport->queryAll(); return $this->render('view', ['dataReport' => $dataReport]); }
public function actionCustomers() { $query = new \yii\db\Query(); $query->select('*')->from('customers')->limit(10); $query->createCommand(); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => false]); return $this->renderPartial('latest-customers', ['dataProvider' => $dataProvider]); }
public function actionSortPosts($sort_id) { $query = new \yii\db\Query(); $query->select('posts.*, user_name, category_name')->from('posts')->leftJoin('users', ' posts.post_author = users.user_id')->innerJoin('posts_category', ' posts.post_category = posts_category.category_id')->WHERE("posts_category.category_id=" . $sort_id); $command = $query->createCommand(); $sort_posts = $command->queryAll(); return $this->render('index', ['posts' => $sort_posts, 'posts_category' => $this->getPostsCategory()]); }
public function leerTodos() { $query = new \yii\db\Query(); //$fecha_actual = date('Y-m-d'); $query->select('*')->from('usuarios'); $rows = $query->all(Usuarios::getDb()); return $rows; }
function actionQuery() { $query = new \yii\db\Query(); $sql = $query->select(['count(id)'])->from('user')->where(['last_name' => 'Smith'])->limit(10)->createCommand()->rawSql; $query = new \yii\db\Query(); $sqlb = $query->select('(' . $sql . ')-(' . $sql . ')')->createCommand()->rawSql; var_dump($sqlb); }
/** * Displays a single Classified model. * @param integer $id * @return mixed */ public function actionView($id) { // $modelReport = new \common\models\ClassifiedReport(); $queryView = new \yii\db\Query(); $queryView->select(['classified.id', 'classified.title', 'classified.description', 'main_category.main_category', 'category.category', 'country.country', 'region.region', 'city.city', 'classified.price', 'classified.create_at', 'classified.update_at', 'classified.user_id', 'classified.is_status', 'classified.type'])->from('classified')->join('JOIN', 'main_category', 'main_category.id = classified.main_category_id')->join('JOIN', 'category', 'category.id = classified.category_id')->join('JOIN', 'country', 'country.id = classified.country_id')->join('JOIN', 'region', 'region.id = classified.region_id')->join('JOIN', 'city', 'city.id = classified.city_id')->where(['classified.id' => $id])->all(); $commandView = $queryView->createCommand(); $dataView = $commandView->queryAll(); return $this->render('view', ['dataView' => $dataView]); }
public function search() { $query = new \yii\db\Query(); $maskExp = new \yii\db\Expression("'****************************************************************************************'"); $query->select(['id' => 'id', 'label' => 'label', 'tokenMask' => $maskExp]); $query->from = ['authentication_token']; $query->where(['user_id' => $this->user_id]); return new ActiveDataProvider(['query' => $query]); }
public function actionDetail() { $id = $_GET['id']; $query = new \yii\db\Query(); $news = $query->select("*")->from("pms_region")->where(['id' => $id])->one(); if (isset($_GET['lang']) && $_GET['lang'] == 'en') { return $this->render("detail_en", ['news' => $news]); } return $this->render("detail", ['news' => $news]); }
public function actionUpdate() { $id = $_GET['id']; $query = new \yii\db\Query(); $user = $query->select("id,role,username")->from("pms_user")->where(['id' => $id])->one(); if (isset($_GET['lang']) && $_GET['lang'] == 'en') { return $this->render('update_en', ['user' => $user]); } return $this->render('update', ['user' => $user]); }
public function checkGroup($attribute, $params) { foreach ($this->{$attribute} as $gid) { $query = new \yii\db\Query(); $query->select('id')->from(GroupSales::tableName())->where('id = :gid')->addParams([':gid' => $gid]); if (!$query->exists()) { $this->addError($attribute, 'Sales Man did you search is not exist for group id ' . $gid); } } }
public function getCategories($parent_id = 0) { $query = new \yii\db\Query(); $query->select('*')->from('category'); $query->join('LEFT JOIN', 'category_description', 'category_description.category_id = category.category_id AND category_description.language_id = ' . (int) \Yii::$app->params['config_language_id']); $query->join('LEFT JOIN', 'category_to_store', 'category_to_store.category_id = category.category_id AND category_to_store.store_id = ' . (int) \Yii::$app->params['store_id']); $query->where(['=', 'category.parent_id', $parent_id]); $query->orderBy(['category.sort_order' => SORT_ASC]); return $query->all(); }
public function actionContact($id) { $modelCover = new Profile(); $modelCover = $this->findModel($id); //query model $query = new \yii\db\Query(); $query->select(['profile.name', 'profile.image_profile', 'profile.public_email', 'profile.phone_number', 'profile.gender', 'profile.facebook_url', 'profile.twitter_url', 'profile.instagram_url', 'city.city', 'region.region', 'country.country', 'user.created_at', 'user.id'])->from('profile')->join('JOIN', 'user', 'user.id = profile.user_id')->join('JOIN', 'city', 'city.id = profile.city_id')->join('JOIN', 'region', 'region.id = profile.region_id')->join('JOIN', 'country', 'country.id = profile.country_id')->where(['user_id' => $id])->all(); $command = $query->createCommand(); $data = $command->queryAll(); return $this->render('contact', ['modelCover' => $modelCover, 'data' => $data]); }
public function getAllBanner($banner_id, $limit) { $query = new \yii\db\Query(); $query->select('*')->from('banner_image'); $query->join('LEFT JOIN', 'banner_image_description', 'banner_image_description.banner_image_id = banner_image.banner_image_id'); $query->where(['=', 'banner_image.banner_id', $banner_id]); $query->orderBy(['banner_image.sort_order' => SORT_ASC]); $query->limit($limit); $query->offset(0); return $query->all(); }
/** * Scanning database table * @param array $tables */ private function _scanningTable($tables) { $query = new \yii\db\Query(); $data = $query->select($tables['columns'])->from($tables['table'])->createCommand(Yii::$app->{$tables}['connection'])->queryAll(); foreach ($data as $columns) { $columns = array_map('trim', $columns); foreach ($columns as $column) { $this->_scanner->addLanguageItem(Scanner::CATEGORY_DATABASE, $column); } } }
private function _getUserId($user_name) { $query = new \yii\db\Query(); // compose the query $data = $query->select('id')->from('user u')->where('u.user_name=:user_name')->addParams(['user_name' => $user_name])->all(); if (count($data) == 1) { return $data[0]['id']; } else { return false; } }
public function up() { $query = new \yii\db\Query(); $query->select('notification.id')->from('notification'); $query->andWhere(['or', ['class' => 'humhub\\modules\\space\\notifications\\Invite'], ['class' => 'humhub\\modules\\space\\notifications\\InviteAccepted'], ['class' => 'humhub\\modules\\space\\notifications\\InviteDeclined'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequest'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequestAccepted'], ['class' => 'humhub\\modules\\space\\notifications\\ApprovalRequestDeclined']]); $query->leftJoin('user', 'notification.originator_user_id=user.id'); $query->andWhere('user.id IS NULL'); foreach ($query->all() as $notification) { $this->delete('notification', ['id' => $notification['id']]); } }
/** * @return GalleryImage[] */ public function getImages($desc = false) { if ($this->_images === null) { $query = new \yii\db\Query(); $imagesData = $query->select(['id', 'name', 'description', 'rank', 'liked'])->from($this->tableName)->where(['type' => $this->type, 'ownerId' => $this->getGalleryId()])->orderBy(['rank' => $desc ? SORT_DESC : SORT_ASC])->all(); $this->_images = []; foreach ($imagesData as $imageData) { $this->_images[] = new GalleryImage($this, $imageData); } } return $this->_images; }
public function getModule($module_id) { $query = new \yii\db\Query(); $query->select('*')->from('module'); $query->where(['=', 'module.module_id', $module_id]); $data = $query->one(); if ($data) { return json_decode($data['setting'], true); } else { return array(); } }
public static function getInfo($id) { $db = new yii\db\Query(); $db->from('ur_questions'); $db->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id'); $db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); $db->InnerJoin('region', 'region_ur_l.id_reg = region.id'); $db->groupBy('ur_l.id'); $db->select(['ur_l.name as uname', 'GROUP_CONCAT(DISTINCT(region.name)) as rname', 'ur_l.contact_mail', 'ur_l.contact_phone', 'ur_l.contact_face', 'question', 'qfiles', 'ansver', 'ur_questions.created_at', 'ur_l.id', 'ur_questions.id as qid']); $db->where(['=', 'ur_questions.id', $id]); //$db->leftJoin('region', 'ur_l.region_id = region.id'); return $db->One(); }
public function sum($type, $date_from, $date_to) { //$date_from = "01-01-$yaer 00:00:00"; //$date_to = "31-12-$yaer 23:59:59"; $query = new \yii\db\Query(); $query->select("sum(`leadsum`) AS lead,{{%transactions}}.type,{{%transactions}}.valuedate")->from('{{%transactions}}'); $query->join = [['LEFT JOIN', '{{%accounts}}', '{{%accounts}}.id=account_id']]; $query->groupBy = "{{%accounts}}.type"; $query->where("{{%accounts}}.type = :type"); $query->andWhere("valuedate>=:date_from"); $query->andWhere("valuedate<=:date_to"); $query->params([':date_from' => $date_from, ':date_to' => $date_to, ':type' => $type]); $command = $query->createCommand(); return $command->queryScalar(); }