/**
* @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 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 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 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 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 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 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 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 search($params)
{
$query = new \yii\db\Query();
if (empty($params) || empty($params['SqlLogSearch']['databasetype'])) {
$dataProvider = new ActiveDataProvider(['query' => $query->from("SqlAttack"), 'sort' => ['defaultOrder' => ['executedate' => SORT_DESC]]]);
} else {
//判断参数中的时间，从而选择正确的数据库
$queryTable = 'SqlAttack';
if (isset($params['SqlAttackSearch']['start_date'])) {
$baseDay = $params['SqlAttackSearch']['start_date'];
//判断当前表是不是在这3天内
$queryDaystring = strtotime($baseDay);
$querydaytimestring = date("Y-m-d", $queryDaystring);
$querydayint = strtotime($querydaytimestring);
//今天的标记daytime
$todayint = strtotime(date('Y-m-d', time()));
//if ($querydayint < $todayint && ($querydayint + 4 * 24 * 60 * 60) >= $todayint&&$querydayint>= strtotime('2016-8-9')) {
// //判断是否在对应的天内
// //得到要查询的表的数据
// $queryTable = "SqlAttack" . date("Ymd", $querydayint);
// }
}
$dataProvider = new ActiveDataProvider(['query' => $query->from($queryTable), 'sort' => ['defaultOrder' => ['executedate' => SORT_DESC]]]);
}
//$query->groupBy('querymd5');
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
if ($this->databasetype && $this->databasetype != 'all') {
$query->andFilterWhere(['databasetype' => $this->databasetype]);
}
$query->andFilterWhere(['like', 'sqltext', $this->sqltext]);
$query->andFilterWhere(['>=', 'sqlusedtime', $this->start_sqlusedtime]);
$query->andFilterWhere(['<=', 'sqlusedtime', $this->end_sqlusedtime]);
$query->andFilterWhere(['>=', 'executedate', $this->start_date]);
$query->andFilterWhere(['<=', 'executedate', $this->end_date]);
return $dataProvider;
}
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 search($params)
{
$query = new \yii\db\Query();
$dataProvider = new ActiveDataProvider(['query' => $query->from(SqlTracePersql50Search::tableName()), 'db' => self::getDb(), 'pagination' => ['pageSize' => 50]]);
$query->orderBy('queryusemaxtime desc');
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
if ($this->sqlquerytime) {
$this->start_date = $this->sqlquerytime;
$this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->sqlquerytime)));
}
$query->andFilterWhere(['databasetype' => $this->databasetype]);
$query->andFilterWhere(['sqlquerytime' => $this->start_date]);
return $dataProvider;
}
private function getStudentList()
{
$transaction = new \yii\db\Query();
return $transaction->from('payment_informations')->select('
payment_informations.payment_user_id,
payment_informations.payment_first_name,
payment_informations.payment_last_name,
payment_informations.payment_gender,
payment_informations.payment_image,
countries.country_short_name
')->leftJoin('users', '`users`.`user_id` = `payment_informations`.`payment_user_id`')->leftJoin('countries', '`countries`.`country_id` = `users`.`user_country`')->where("`payment_user_id` IN (\n SELECT `user_id` \n FROM `transaction_history` \n WHERE `class_id` = {$this->class_id} \n AND (`status` = 'succeeded' OR `status` = 'SUCCESS')\n AND (`transaction_dispute_status` = 'close' OR `transaction_dispute_status` = 'declined')\n AND `transation_id` NOT IN (\n SELECT `transation_id`\n FROM `transaction_history` \n WHERE `class_id` = {$this->class_id} AND `status` = 'REFUNDED'\n )\n )")->all();
}
public function actionUser()
{
echo 'Start export.' . PHP_EOL;
// Наше PDO подключение к БД
$db = Yii::$app->db;
// Размер одной части обрабатываемых данных
$part_size = 1000;
$fields = ['user.id' => 'ID', 'profile.name' => 'Name', 'profile.last_name' => 'Last Name', 'user.social_id' => 'Social ID', 'created_at' => 'Created at'];
// Файл, в который будем записывать результат (в корне сайта)
$fname = Yii::$app->basePath . '/export/user.csv';
$f = @fopen($fname, 'w');
// Записываем в начало файла заголовок для sitemap-файла
$csvHeader = '"' . implode('";"', $fields) . '"';
fwrite($f, $csvHeader . PHP_EOL);
$query = new \yii\db\Query();
// Команда, которая будет делать порционную выборку новостей
$query->select(implode(',', array_keys($fields)));
$query->from('user, profile');
$query->andWhere('user.id = profile.user_id');
//$query->join('LEFT JOIN', 'profile', 'user.id = profile.user_id');
// Определяем количество данных, которое нам нужно обработать
$all_count = (int) $db->createCommand("SELECT COUNT(id) FROM user")->queryScalar();
// Устанавливаем лимит, сколько новостей надо выбрать из таблицы
$query->limit($part_size);
// Перебираем все части данных
for ($i = 0; $i < ceil($all_count / $part_size); $i++) {
// Сюда будем складывать порции данных, для записи в файл, каждый
// элемент массива - это одна строка
$part = array();
// Вычисляем отступ от уже обработанных данных
$offset = $i * $part_size;
// Устанавливам отступ
$query->offset($offset);
// Находим очередную часть данных
$rows = $query->all();
// Перебираем найденные данные
foreach ($rows as $row) {
$row['created_at'] = date('m-d-Y', $row['created_at']);
// Открываем тег <url> - начало описания элемента в sitemap-файле
$part[] = '"' . implode('";"', $row) . '"';
}
// Убираем из памяти найденную часть данных
unset($rows);
// Добавляем в наш файл обработанную часть данных
if (count($part)) {
// Здесь мы объединяем все элементы массива $xml в строки
fwrite($f, implode(PHP_EOL, $part) . PHP_EOL);
}
unset($part);
}
// Заканчиваем работу с файлом
fclose($f);
echo 'Done.' . PHP_EOL;
}
