/**
*
*/
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
}
}
/**
* @inheritdoc
*/
public function behaviors()
{
$behaviors = ArrayHelper::merge(parent::behaviors(), ['authenticator' => ['class' => CompositeAuth::className(), 'authMethods' => [['class' => HttpBearerAuth::className()], ['class' => QueryParamAuth::className(), 'tokenParam' => 'accessToken']]], 'exceptionFilter' => ['class' => ErrorToExceptionFilter::className()], 'corsFilter' => ['class' => \backend\rest\filters\Cors::className(), 'cors' => ['Origin' => ['*'], 'Access-Control-Request-Method' => ['POST', 'PUT', 'OPTIONS', 'PATCH', 'DELETE'], 'Access-Control-Request-Headers' => ['X-Pagination-Total-Count', 'X-Pagination-Page-Count', 'X-Pagination-Current-Page', 'X-Pagination-Per-Page', 'Content-Length', 'Content-type', 'Link'], 'Access-Control-Allow-Credentials' => true, 'Access-Control-Max-Age' => 3600, 'Access-Control-Expose-Headers' => ['X-Pagination-Total-Count', 'X-Pagination-Page-Count', 'X-Pagination-Current-Page', 'X-Pagination-Per-Page', 'Content-Length', 'Content-type', 'Link'], 'Access-Control-Allow-Headers' => ['X-Pagination-Total-Count', 'X-Pagination-Page-Count', 'X-Pagination-Current-Page', 'X-Pagination-Per-Page', 'Content-Length', 'Content-type', 'Link']]]]);
if (isset(\Yii::$app->params['httpCacheActive']) and \Yii::$app->params['httpCacheActive']) {
$params = \Yii::$app->getRequest()->getQueryParams();
unset($params['accessToken']);
$behaviors['httpCache'] = ['class' => HttpCache::className(), 'params' => $params, 'lastModified' => function ($action, $params) {
$q = new \yii\db\Query();
$class = $this->modelClass;
if (in_array('updated_at', $class::getTableSchema()->getColumnNames())) {
return strtotime($q->from($class::tableName())->max('updated_at'));
}
if (in_array('modified', $class::getTableSchema()->getColumnNames())) {
return strtotime($q->from($class::tableName())->max('modified'));
}
return null;
}, 'etagSeed' => function (Action $action, $params) {
$iterator = new \RecursiveIteratorIterator(new \RecursiveArrayIterator($params));
$keys = array();
foreach ($iterator as $key => $value) {
// Build long key name based on parent keys
for ($i = $iterator->getDepth() - 1; $i >= 0; $i--) {
$key = $iterator->getSubIterator($i)->key() . '_' . $key;
if (!is_array($iterator->getSubIterator($i)->current())) {
$value = $iterator->getSubIterator($i)->current() . '_' . $value;
}
}
$keys[] = $key . '-' . $value;
}
$uniqueId = implode('-', $keys);
return $uniqueId;
}];
}
return $behaviors;
}
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 findRecursive($children_ids, $direct = 'up', $only_leaf = false)
{
$placeholders = !empty($children_ids) ? str_repeat('?,', count($children_ids) - 1) . '?' : false;
$first_param = $direct === 'up' ? 'id' : 'parent_id';
$second_param = $direct === 'up' ? 'parent_id' : 'id';
$sql = 'WITH RECURSIVE tree AS (
SELECT
*,
0 AS level
FROM rubric' . ($placeholders ? ' WHERE ' . $first_param . ' IN (' . $placeholders . ')' : '') . ' UNION
SELECT
parent.*,
child.level + 1 AS level
FROM rubric AS parent
JOIN tree AS child ON child.' . $second_param . ' = parent.' . $first_param . '
)
SELECT DISTINCT t.id, t.title, t.parent_id ' . ' FROM tree as t' . ($only_leaf ? ' LEFT OUTER JOIN tree AS r ON t.id = r.parent_id WHERE r.parent_id IS NULL' : '') . ' ORDER BY id ASC;';
$query = new \yii\db\Query();
$command = $query->createCommand()->setSql($sql);
if ($placeholders) {
foreach ($children_ids as $i => $children) {
$command->bindValue($i + 1, $children);
}
}
$rubric = $command->queryAll();
// var_export($sql); die();
return $rubric;
}
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();
}
public function actionRollBack($id)
{
$model = $this->findModel($id);
$table = (new $model->category())->tableName();
$attributes = $model['data']['attributes'];
$updated = 0;
$inserted = 0;
try {
$query = new \yii\db\Query();
$oldRow = $query->from($table)->andWhere(['id' => $attributes['id']])->one();
if ($oldRow) {
$updated = Yii::$app->db->createCommand()->update($table, $attributes, ['id' => $attributes['id']])->execute();
}
if (!$oldRow) {
$inserted = Yii::$app->db->createCommand()->insert($table, $attributes)->execute();
}
} catch (\yii\db\Exception $exc) {
Yii::$app->session->setFlash('alert', ['options' => ['class' => 'alert-error'], 'body' => Yii::t('backend', 'Can\'t roll back. ') . $exc->getMessage()]);
}
if (empty($exc)) {
Yii::$app->session->setFlash('alert', ['options' => ['class' => 'alert-success'], 'body' => Yii::t('backend', 'Updated: {u}. Inserted: {i}.', ['u' => $updated, 'i' => $inserted])]);
}
TimelineEvent::log($model->category, 'afterRollBack', ['attributes' => $attributes, 'uid' => Yii::$app->user->identity->id]);
$redirectUrlParams = ['index'];
if (Yii::$app->request->get('TimelineEventSearch')) {
$redirectUrlParams['TimelineEventSearch'] = Yii::$app->request->get('TimelineEventSearch');
}
$this->redirect($redirectUrlParams);
}
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 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 behaviors()
{
return ['access' => ['class' => AccessControl::className(), 'only' => ['index', 'view', 'create', 'update', 'delete', 'actions'], 'rules' => [['actions' => ['index', 'view', 'create', 'update', 'delete', 'actions'], 'allow' => true, 'roles' => ['@']]]], 'verbs' => ['class' => VerbFilter::className(), 'actions' => ['delete' => ['post']]], ['class' => 'yii\\filters\\PageCache', 'only' => ['index'], 'duration' => 60, 'variations' => [\Yii::$app->language], 'dependency' => ['class' => 'yii\\caching\\DbDependency', 'sql' => 'SELECT COUNT(*) FROM ' . Video::tableName()]], ['class' => 'yii\\filters\\HttpCache', 'only' => ['index'], 'lastModified' => function ($action, $params) {
$q = new \yii\db\Query();
$res = $q->from('video')->max('create_date');
return strtotime($res);
}]];
}
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()]);
}
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);
}
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;
}
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);
}
/**
* 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 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]);
}
/**
* 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 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 behaviors()
{
return [['class' => 'yii\\filters\\HttpCache', 'only' => ['index'], 'lastModified' => function ($action, $params) {
$q = new \yii\db\Query();
return $q->from('tbl_posts')->max('create_date');
}], ['class' => 'yii\\filters\\HttpCache', 'only' => ['details'], 'etagSeed' => function ($action, $params) {
$post = $this->findModel(\Yii::$app->request->get('id'));
return serialize([$post->title, $post->description]);
}], 'verbs' => ['class' => VerbFilter::className(), 'actions' => ['delete' => ['post']]]];
}
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 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;
}
/**
* 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);
}
}
}
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]);
}
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;
}
}
/**
* @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 afterSave($insert, $changedAttributes)
{
$reviews = \common\models\Reviews::find();
$transaction = new \yii\db\Query();
$id = $this->review_trainer_id;
$rating_count = $reviews->where(['review_trainer_id' => $id])->count();
$rating_count_positive = $reviews->where(['review_trainer_id' => $id])->andWhere('review_rating > 3')->count();
$amount_sum = $transaction->from('transaction_history')->where("`class_id` IN (SELECT `class_id` FROM `classes` WHERE `class_trainer_id` = " . abs((int) $id) . ")")->sum('amount');
$rating_count_positive = @round($rating_count_positive / $rating_count * 100);
$status = (int) $amount_sum > 1000 && $rating_count_positive >= 75 ? 'Gold' : 'Silver';
PaymentInformations::updateAll(['payment_user_status' => $status], ['payment_user_id' => [$id]]);
}
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 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;
}
