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