public function beforeInsert()
{
$last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one();
if ($last === null) {
$this->owner->{$this->orderAttribute} = 1;
} else {
$this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1;
}
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$this->buildSearchQuery();
$this->applyContextFilters();
$dataProvider = new ActiveDataProvider(['query' => $this->query, 'pagination' => ['pageSize' => $this->pageSize]]);
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
$this->applySearchFilters();
$this->query->orderBy('date_retrieved DESC');
return $dataProvider;
}
public function actionIndex($date = null)
{
// $apteki=LogReestr::find()->where(['resstr' => 'apteki'])->all();
if (!$date) {
$date = date('Y-m');
}
$db = new Query();
$db->from(LogReestr::tableName());
$db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.action', 'log_reestr.name', 'log_reestr.ur_l_id', 'log_reestr.id_resstr', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
$db->where(['=', 'resstr', 'apteki']);
$db->leftJoin('users', "users.id = log_reestr.user");
$db->orderBy('log_reestr.created_at DESC');
$date_search = $date . '%';
$db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
$apteki = $db->all();
// $apteki_count = $db->count();
$db = new Query();
$db->from(LogReestr::tableName());
$db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.name', 'log_reestr.action', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']);
$db->where(['=', 'resstr', 'ur_l']);
$db->leftJoin('users', "users.id = log_reestr.user");
$db->andWhere(['like', 'log_reestr.created_at', $date_search, false]);
$db->orderBy('log_reestr.created_at DESC');
$ur_l = $db->all();
// $ur_l_count = $db->count();
$statm = \Yii::$app->db->createCommand("SELECT users.username , COUNT(*) as count FROM log_reestr INNER JOIN users ON users.id=log_reestr.user\n where log_reestr.created_at like '" . $date . "%'\n GROUP BY USER order by count DESC");
$stat = $statm->queryAll();
$statAllm = \Yii::$app->db->createCommand("SELECT COUNT(*) as count FROM log_reestr\n where log_reestr.created_at like '" . $date . "%' ");
$statAll = $statAllm->queryOne();
return $this->render('index', ['apteki' => $apteki, 'ur_l' => $ur_l, 'date' => $date, 'stat' => $stat, 'statAll' => $statAll]);
}
public function actionIndex($user = null, $date = null, $trimestr = null, $scroll = null)
{
if (\Yii::$app->user->identity->status == 3) {
$user = \Yii::$app->user->identity->id;
}
//$model = Preparats::find()->where(['region_id' => $region])->orderBy('name')->all();
if ($user) {
//$apteki = Apteki::find()->where(['pi_id' => $user, 'farmopeka' => '1'])->orderBy('address')->all();
$query = new Query();
$query->select(['ur_l.name', 'apteki.address', 'apteki.id']);
$query->from('apteki');
$query->LeftJoin('ur_l', 'ur_l.id = apteki.ur_l_id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'apteki.pi_id', $user]);
//$query->orderBy('ur_l.name,apteki.address');
$query->orderBy(['ur_l.name' => SORT_ASC, 'apteki.address' => SORT_ASC]);
$apteki = $query->all();
}
if (!$date) {
$date = date('Y-m-01');
}
// if (!trimestr) $trimestr = Visitors::getTrimestr($date);
if ($user) {
$table = Visitors::getTable($user, $apteki, $date, $trimestr, $scroll);
}
// print \Yii::$app->params['infoEmail'];
//Количество привязвнных ир.лиц и розничных точек
$count['ur'] = Ur::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count();
$count['apteki'] = Apteki::find()->where(['pi_id' => $user])->andWhere(['farmopeka' => 1])->count();
return $this->render('index', ['user' => $user, 'table' => $table, 'date' => $date, 'trimestr' => $trimestr, 'count' => $count, 'scroll' => $scroll]);
}
public function retrieve($className, $primaryKey)
{
$tableName = call_user_func([$className, "tableName"]);
$current = $className::find()->where(['id' => $primaryKey])->asArray()->one();
$query = new Query();
$query->select(['field_name', 'old_value', 'event', 'action_uuid', 'created_at']);
$query->from($this->tableName);
$query->where(['field_id' => $primaryKey, 'table_name' => $tableName]);
$query->orderBy(['created_at' => SORT_ASC]);
$changes = [];
foreach ($query->all() as $element) {
$uuid = $element['action_uuid'];
if (!isset($changes[$uuid])) {
$changes[$uuid] = $current;
}
$changes[$uuid][$element['field_name']] = $element['old_value'];
$current = $changes[$uuid];
}
$models = array_map(function ($element) use($className) {
$model = $className::instantiate($element);
$className::populateRecord($model, $element);
return $model;
}, $changes);
return new ArrayDataProvider(['allModels' => array_values($models)]);
}
public static function getRegion()
{
$db = new yii\db\Query();
$db->select('id', 'name');
$db->from('region');
$db->orderBy('name');
return $db->all();
}
public function beforeInsert()
{
if (!is_null($this->where)) {
if (!is_array($this->where)) {
$this->where = array($this->where);
}
foreach ($this->where as $where) {
$this->query->andWhere([$where => $this->owner->{$where}]);
}
}
$last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one();
if ($last === null) {
$this->owner->{$this->orderAttribute} = 1;
} else {
$this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1;
}
}
public static function getMostActive()
{
$query = new Query();
$query->select('profile.*,(SELECT COUNT(*) FROM discussion_post_reply WHERE profile.user_id = discussion_post_reply.user_id) as repliesCount');
$query->from('profile');
$query->orderBy('repliesCount DESC');
$query->limit('4');
return $query->all();
}
public static function getPopularVideos()
{
$query = new Query();
$query->select('videos.*,(SELECT COUNT(*) FROM videos_comments WHERE videos.id = videos_comments.video_id) as commentsCount');
$query->from('videos');
$query->orderBy('commentsCount DESC');
$query->limit('4');
return $query->all();
}
public static function getPopularPosts()
{
$query = new Query();
$query->select('article.*,(SELECT COUNT(*) FROM article_comments WHERE article.id = article_comments.article_id) as commentsCount');
$query->from('article');
$query->orderBy('commentsCount DESC');
$query->limit('4');
return $query->all();
}
/**
* @param Query $query
* @param array $params
* @return Query
*/
protected function additionalFilter($query, $params)
{
if (!isset($params['sort'])) {
$query->orderBy(['date' => SORT_DESC, 'created_at' => SORT_DESC, 'id' => SORT_DESC]);
} else {
switch ($params['sort']) {
case 'total':
$query->orderBy(['type_id' => SORT_DESC, 'total' => SORT_ASC]);
break;
case '-total':
$query->orderBy(['type_id' => SORT_ASC, 'total' => SORT_DESC]);
break;
}
}
$period = Yii::$app->request->get('period');
if (isset($period)) {
switch ($period) {
case 'today':
$today = Yii::$app->getFormatter()->asDate('now', "php:Y-m-d");
$query->andFilterWhere(['like', 'date', $today]);
break;
case 'yesterday':
$yesterday = date('Y-m-d', strtotime(date('Y-m-d') . " - 1 day"));
$query->andFilterWhere(['like', 'date', $yesterday]);
break;
case 'current_month':
$query = $this->setDefaultPeriod($query);
break;
case 'all':
break;
default:
$query = $this->setDefaultPeriod($query);
break;
}
} else {
$query = $this->setDefaultPeriod($query);
}
if (isset($params['category'])) {
$query->leftJoin(Transaction2Category::tableName(), 'transaction_id = id');
$query->andWhere(['transaction2category.category_id' => $params['category']]);
}
return $query;
}
public function getHistory($chatId, $limit = 10)
{
$query = new Query();
$query->select(['user_id', 'username', 'message', 'timestamp', 'avatar_16', 'avatar_32'])->from(self::tableName())->where(['chat_id' => $chatId]);
$query->orderBy(['timestamp' => SORT_DESC]);
if ($limit) {
$query->limit($limit);
}
return $query->all();
}
public function search($params)
{
$query = new \yii\db\Query();
$dataProvider = new ActiveDataProvider(['query' => $query->from(IisAccessLog::tableName()), 'pagination' => ['pageSize' => 50]]);
$query->orderBy('Id desc');
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
if ($this->date_reg) {
$this->start_date = $this->date_reg;
$this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date)));
}
$query->andFilterWhere(['Ip1' => $this->Ip1]);
$query->andFilterWhere(['>=', 'date_reg', $this->start_date]);
$query->andFilterWhere(['<', 'date_reg', $this->end_date]);
$query->orderBy('Id desc');
return $dataProvider;
}
public function search($params)
{
$query = new \yii\db\Query();
$dataProvider = new ActiveDataProvider(['query' => $query->from(AccessLogIismost::tableName()), 'pagination' => ['pageSize' => 50]]);
$query->orderBy('AccessIPNum desc');
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
if ($this->Date_time) {
$this->start_date = $this->Date_time;
$this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date)));
}
$query->andFilterWhere(['Website' => $this->Website]);
$query->andFilterWhere(['server' => $this->server]);
$query->andFilterWhere(['>=', 'Date_time', $this->start_date]);
$query->andFilterWhere(['<', 'Date_time', $this->end_date]);
$query->orderBy('AccessIPNum desc');
return $dataProvider;
}
public static function getSingleCategoryWithPosts($categoryId)
{
$query = new Query();
$query->select('discussion_posts.*,discussion_categories.*,discussion_posts.id as dpId');
$query->from('discussion_categories');
$dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 8]]);
$query->andFilterWhere(['discussion_categories.id' => $categoryId]);
$query->join('left join', 'discussion_posts', 'discussion_posts.discussion_category_id=discussion_categories.id');
$query->orderBy('discussion_posts.id DESC');
return $dataProvider;
}
public function actionEmptyregional()
{
$db = new Query();
$db->from('ur_l');
$db->select(['ur_l.name', 'ur_l.id', 'GROUP_CONCAT(region.name) as rname']);
$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
//$db->andWhere(['like', 'log_reestr.created_at', $date]);
$db->GroupBy('ur_l.id');
$db->orderBy('rname', 'ur_l.name');
$data = $db->all();
return $this->render('empty', ['data' => $data]);
}
public function search($params)
{
$query = new \yii\db\Query();
//判断参数中的时间，从而选择正确的数据库
$queryTable = AccessLogss::tableName();
if (isset($params['AccessLogssSearch']['date_reg'])) {
$baseDay = $params['AccessLogssSearch']['date_reg'];
//判断当前表是不是在这七天内
$queryDaystring = strtotime($baseDay);
$querydaytimestring = date("Y-m-d", $queryDaystring);
$querydayint = strtotime($querydaytimestring);
//今天的标记daytime
$todayint = strtotime(date('Y-m-d', time()));
if ($querydayint < $todayint && $querydayint + 8 * 24 * 60 * 60 >= $todayint && $querydayint >= strtotime('2016-8-11')) {
//判断是否在对应的天内
//得到要查询的表的数据
$queryTable = $queryTable . "_" . date("Ymd", $querydayint);
}
}
$dataProvider = new ActiveDataProvider(['query' => $query->from($queryTable), 'db' => self::getDb(), 'pagination' => ['pageSize' => 50]]);
$query->orderBy('Id desc');
$this->load($params);
if (!$this->validate()) {
return $dataProvider;
}
if ($this->date_reg) {
$this->start_date = $this->date_reg;
$this->end_date = date('Y-m-d 00:00:00', strtotime('+1 day', strtotime($this->start_date)));
}
$query->andFilterWhere(['Ip1' => $this->Ip1]);
$query->andFilterWhere(['visitwebsite' => $this->visitwebsite]);
$query->andFilterWhere(['>=', 'request_time', $this->request_time]);
$query->andFilterWhere(['>=', 'date_reg', $this->start_date]);
$query->andFilterWhere(['<', 'date_reg', $this->end_date]);
$query->orderBy('date_reg desc');
return $dataProvider;
}
/**
* Lists all UrQuestions models.
* @return mixed
*/
public function actionIndex()
{
//топ аптек
$db = new Query();
$db->from('ur_questions');
$db->select(['COUNT(*) AS count', 'ur_l_id', 'ur_l.name']);
$db->leftJoin('ur_l', "ur_l.id=ur_questions.`ur_l_id`");
$db->groupBy('ur_l_id');
$db->orderBy('count DESC');
$db->limit(5);
$top = $db->all();
$query = (new \yii\db\Query())->from('ur_questions');
$query->select('username,ur_questions.id,ur_questions.created_at,question,ur_l.name,date_ansver');
$query->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id');
$query->leftJoin('users', 'ur_questions.user_id = users.id');
if (\Yii::$app->user->identity->status != 1) {
// $query->where(['ur_questions.user_id'=>\Yii::$app->user->identity->id]);
}
$query->orderBy(['ur_questions.id' => SORT_DESC]);
$dataProvider = new ActiveDataProvider(['key' => 'id', 'query' => $query->from('ur_questions')]);
return $this->render('index', ['dataProvider' => $dataProvider, 'top' => $top]);
}
public function ListAptekiAnsver()
{
$query = new Query();
$query->select(['GROUP_CONCAT(DISTINCT(region.name)) as rname', 'COUNT(preparats_ansver.id) AS count', 'ur_l.name AS uname', 'ur_l.id']);
$query->from('ur_l');
$query->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
$query->InnerJoin('region', 'region_ur_l.id_reg = region.id');
$query->leftJoin('preparats_ansver', 'ur_l.id = preparats_ansver.id_apteka');
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
}
$query->andFilterWhere(['=', 'farmopeka', '1']);
$query->groupBy('ur_l.id');
$query->orderBy('rname', 'ur_l.name');
// $query->InnerJoin('apteki', 'preparats_ansver.id_o=preparats_ansver.id');
return $query->all();
}
public function search_tip_id($params)
{
$query = new Query();
$query->from('t_tovar t');
$options = [];
$joptions = isset($params['options']) ? $params['options'] : [];
// var_dump($joptions);die;
if (is_array($joptions)) {
foreach ($joptions as $key => $value) {
if (!empty($key)) {
$options[$key] = $value;
}
}
} else {
$options = json_decode($joptions);
}
$n = 1;
// var_dump($joptions);die;
foreach ($options as $id => $value) {
$name = 't' . $n;
$fname = $id;
// $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=' . $id)
$query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=:' . $name, [':' . $name => $id])->addSelect([$fname => $name . '.value_char']);
if (!empty($value)) {
$p = ':value_' . $n;
$param = [$p => $value];
// var_dump($param,$name . '.value_char='.$p);die;
$query->andWhere($name . '.value_char=' . $p, $param);
// $n=$n+1;
}
$n = $n + 1;
}
$query->leftJoin('t_price p', 'p.id_tovar=t.id and p.id_store=:store_id', [':store_id' => $params['store_id']])->addSelect('p.price,p.id_store,p.count,t.id,t.name,t.tip_id,t.category_id');
// $query->limit($params['page_size'])->offset(($params['page'] - 1) * $params['page_size']);
$query->andWhere($params['where']);
$query->orderBy($params['orderby']);
// var_dump($query);die;
$dataProvider = new ActiveDataProvider(['query' => $query]);
// $dataProvider = new ArrayDataProvider(['allModels' => $query->all()]);
$dataProvider->pagination->page = $params['page'] - 1;
$dataProvider->pagination->pageSize = $params['page_size'];
// var_dump($dataProvider->models);die;
//var_dump($dataProvider);die;
return $dataProvider;
}
public function testOrder()
{
$query = new Query();
$query->orderBy('team');
$this->assertEquals(['team' => SORT_ASC], $query->orderBy);
$query->addOrderBy('company');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC], $query->orderBy);
$query->addOrderBy('age');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_ASC], $query->orderBy);
$query->addOrderBy(['age' => SORT_DESC]);
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_DESC], $query->orderBy);
$query->addOrderBy('age ASC, company DESC');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_DESC, 'age' => SORT_ASC], $query->orderBy);
}
public static function find($module, $params = [], $page = 30)
{
$relation = ['country' => ['translations'], 'city' => ['country', 'country.translations', 'airports', 'airports.translations', 'translations'], 'direction' => ['iata', 'from', 'from.translations', 'from.country', 'from.country.translations', 'to', 'to.translations', 'to.country', 'to.country.translations'], 'airport' => ['translations', 'country', 'country.translations', 'city', 'city.translations'], 'airline' => []];
if (!empty($params['relation'])) {
$relation = array_merge($relation, $params['relation']);
unset($params['relation']);
}
$model = 'common\\models\\Received' . ucfirst($module);
if (!class_exists($model)) {
throw new ErrorException('no module > ' . $module);
}
$query = new Query();
$query->select(["{$module}.id"])->from([$module => "received_{$module}"]);
if ($module == 'direction') {
$query->addSelect(["{$module}.popularity"]);
$query->orderBy(['direction.popularity' => SORT_DESC]);
$query->groupBy(['direction.city_to_code', 'direction.city_from_code']);
}
foreach ($params as $key => $value) {
$postfix = '';
if ($key == 'from' || $key == 'to') {
$postfix = "_{$key}";
$key = 'city';
}
if (is_array($value) && $value) {
$query->leftJoin([$key . $postfix => "received_{$key}"], "{$module}.{$key}{$postfix}_code = {$key}{$postfix}.code");
foreach ($value as $_key => $_value) {
if (is_array($_value) && $_value) {
$query->leftJoin([$_key . $postfix => "received_{$_key}"], "{$key}{$postfix}.{$_key}_code = {$_key}{$postfix}.code");
foreach ($_value as $__key => $__value) {
$query->andFilterWhere(["{$_key}{$postfix}.{$__key}" => $__value]);
}
} else {
$query->andFilterWhere(["{$key}{$postfix}.{$_key}" => $_value]);
}
}
} else {
$query->andFilterWhere(["{$module}.{$key}{$postfix}" => $value]);
}
}
$total = $query->count();
$id = $query->column();
if ($page > 1) {
if (!empty($_GET['page'])) {
$_page = $_GET['page'];
} else {
$_page = 0;
}
$id = array_slice($id, $_page > 1 ? ($_page - 1) * $page : 0, $page);
// VarDumper::dump($id);
}
/** @var $model \yii\db\ActiveRecord */
$query = $model::find()->where(['id' => $id])->asArray();
if ($module == 'direction') {
$query->orderBy(['received_direction.popularity' => SORT_DESC]);
}
$query->with($relation[$module]);
if ($page == 1) {
return $query->all();
}
$pagination = new Pagination(['totalCount' => $total, 'pageSize' => $page, 'pageSizeParam' => false]);
if ($page == 1) {
$page = 0;
}
$query->offset(0)->limit($page);
return [$query->all(), $pagination];
}
public function setLangBegin()
{
if (!$this->langBegin) {
$key = static::className() . 'langBegin';
$this->langBegin = $this->cache->get($key);
if ($this->langBegin === false) {
$query = new Query();
$query->select(['c' => $this->codeField])->from($this->table);
if ($this->condition) {
$query->where($this->condition);
}
if ($this->orderBy) {
$query->orderBy($this->orderBy);
}
$this->langBegin = ArrayHelper::getColumn($query->all($this->getDb()), 'c');
}
if ($this->enableCaching) {
if ($this->durationCaching !== null) {
$this->cache->set($key, $this->langBegin, $this->durationCaching);
} else {
$query = new Query();
$sql = $query->select('COUNT(*),MAX(' . $this->updatedAtField . ')')->from($this->table)->createCommand($this->getDb())->getRawSql();
$this->cache->set($key, $this->langBegin, $this->durationCaching, new \yii\caching\DbDependency(['sql' => $sql]));
}
}
}
}
/**
* 获取等待运行的一个条件,锁定,运行
*/
public function getPlanGetOrderDownload()
{
$query = new Query();
$query->select('*');
$query->from('t_run_ebay_order_download');
$query->where('1=1');
$query->andWhere(['status' => 0]);
$query->orderBy(['id' => SORT_ASC]);
$this->planDownload = $query->one();
if (empty($this->planDownload)) {
throw new \Exception('没有符合条件的下载任务');
}
}
/**
* Пулечение всех юо.лиц в регионе плательщиков взносов
* @param null $regional_id
* @param $region_id
* @return array
*/
public static function getUr_l_in_region($regional_id, $region_id)
{
$regional_id = (int) $regional_id;
$region_id = (int) $region_id;
$db = new Query();
$db->from(Ur::tableName());
$db->select(['ur_l.name', 'ur_l.id']);
$db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id");
$db->Where(['=', 'region_ur_l.id_reg', $region_id]);
$db->andWhere(['=', 'ur_l.plat', '1']);
$db->andwhere(['=', 'ur_l.regional_id', $regional_id]);
$db->orderBy('ur_l.name ASC');
$return = $db->all();
return $return;
}
public function getPOLineRelatedQuery($params = [], $modelline = [], $groupBy = null)
{
$query = new Query();
if ($params['partner_id']) {
$partner_id = $params['partner_id'];
} else {
$partner_id = '0';
}
if ($modelline) {
if ($modelline['product_id']) {
$product = $modelline['product_id'];
} else {
$product = '0';
}
} else {
$product = $params['product_id'];
}
if ($params['state']) {
$state = $params['state'];
} else {
$state = '0';
}
if ($params['date_order']) {
$dattefrom = $params['date_order'];
$dateto = $params['duedate'];
} else {
$dattefrom = '0';
$dateto = '0';
}
// if($params['pricelist']){
// if(is_array($params['pricelist'])){
// $pricelist=implode(",", $params['pricelist']);
// }else{
// $pricelist=$params['pricelist'];
// }
// }else{
// $pricelist='0';
// }
if ($groupBy) {
if (is_array($params['pricelist'])) {
$pricelist = implode(",", $params['pricelist']);
} else {
$pricelist = $params['pricelist'];
}
}
if ($groupBy) {
if ($groupBy == 'partner') {
$query->select(['CONCAT("pol"."partner_id", \'/\',"pid"."id",\'/\',\'' . $product . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id,
rp.name as partner,
SUM(pol.price_unit*pol.product_qty) as total,
pid.name as pricelist']);
}
} else {
$query->select('
pol.id as id,
pol.partner_id as partner_id,
po.date_order as date_order,
po.name as no_po,
pol.name as pol_desc,
rp.name as partner,
pol.product_id as product_id,
pp.name_template as product,
pol.price_unit as price_unit,
pol.state as state,
pol.product_qty as product_qty,
pu.name as uom,
pid.name as pricelist,
(pol.product_qty*pol.price_unit) as total,
');
}
$query->from('purchase_order_line as pol')->join('LEFT JOIN', 'purchase_order as po', 'po.id=pol.order_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=po.pricelist_id')->join('LEFT JOIN', 'product_product as pp', 'pp.id=pol.product_id')->join('LEFT JOIN', 'product_uom as pu', 'pu.id=pol.product_uom')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=pol.partner_id');
if ($groupBy) {
if ($groupBy == 'partner') {
$query->groupBy(['pol.partner_id', 'rp.name', 'pid.id']);
$query->orderBy('rp.name ASC');
}
}
if (isset($params['partner_id']) && $params['partner_id']) {
if ($params['partner_id'] != '0') {
$query->andWhere(['pol.partner_id' => explode(',', $params['partner_id'])]);
}
}
if (isset($modelline['name']) && $modelline['name']) {
$query->andWhere(['ilike', 'pol.name', $modelline['name']]);
// die();
}
if (isset($modelline['product_id']) && $modelline['product_id']) {
if ($modelline['product_id'] != '0') {
$query->andWhere(['pol.product_id' => explode(',', $modelline['product_id'])]);
}
}
if (isset($params['state']) && $params['state']) {
if ($params['state'] == "purchased") {
$cekstate = 'confirmed, approved, done';
if ($params['state'] != '0') {
$query->andWhere(['pol.state' => explode(',', $cekstate)]);
}
} else {
if ($params['state'] != '0') {
$to_state = [];
$exps = explode(',', urldecode($params['state']));
foreach ($exps as $exp) {
if ($exp == 'purchased') {
$to_state[] = 'confirmed';
$to_state[] = 'approved';
$to_state[] = 'done';
} else {
$to_state[] = $exp;
}
}
$query->andWhere(['pol.state' => $to_state]);
}
}
} else {
$query->andWhere(['in', 'pol.state', ['confirmed', 'approved', 'done']]);
}
if (isset($params['date_order']) && $params['date_order']) {
if ($params['date_order'] != '0') {
$query->andWhere(['>=', 'po.date_order', $params['date_order']]);
$query->andWhere(['<=', 'po.date_order', $params['duedate']]);
}
}
if (isset($params['pricelist'])) {
$query->andWhere(['po.pricelist_id' => $params['pricelist']]);
}
if (!$groupBy) {
$query->addOrderBy(['po.date_order' => SORT_DESC]);
}
return $query;
}
public static function getWarning($orderBy, $limit, $conditions = [])
{
$query = new Query();
$query->select('w.*, s.name AS station_name, a.name AS area_name, c.name AS center_name')->from('warning w')->leftJoin('station s', 's.id = w.station_id')->innerJoin('area a', 's.area_id = a.id')->innerJoin('center c', 'c.id = s.center_id')->where([]);
if (!empty($conditions)) {
$oneCondition = 0;
foreach ($conditions as $con) {
$query->andWhere($con);
}
}
$query->orderBy($orderBy);
if ($limit > 0) {
$query->limit($limit);
}
$warnings = $query->all();
if (!empty($warnings)) {
foreach ($warnings as $k => $w) {
$warnings[$k]['pics'] = self::findPicture($w['id']);
}
}
return $warnings;
}
FROM
apteki
INNER JOIN users
ON users.`id` = apteki.pi_id
LEFT JOIN visitors
ON visitors.apteki_id = apteki.id
WHERE apteki.id = 4581 AND visitors.date LIKE '2016%'
*/
//тут новый запрос
$query = new Query();
$query->select(['apteki.address']);
$query->from('apteki');
$query->LeftJoin('users', 'users.id = apteki.pi_id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'users.id', 1]);
$query->orderBy('apteki.address');
?>
<br><br>
<?php
echo Html::SubmitButton("Сохранить", ['class' => 'btn btn-success']);
?>
<div style="float:right">
<?php
echo Html::a('Удалить', ['/visitors/delete/', 'id' => $model['id']], ['class' => 'btn btn-danger', 'data' => ['confirm' => 'Удалить?']]);
public static function buildContentQuery($tableName, $other = [], $where = null)
{
$query = new Query();
if (isset($other['fields'])) {
$query->select($other['fields']);
}
if (empty($tableName)) {
// todo
$tableName = 'model_news';
}
$query->from($tableName);
if ($where !== null) {
$query->andWhere($where);
}
if (isset($other['where'])) {
$query->andWhere($other['where']);
}
if (isset($other['att1']) && is_integer($other['att1'])) {
$query->andWhere(['att1' => $other['att1']]);
}
if (isset($other['att2']) && is_integer($other['att2'])) {
$query->andWhere(['att2' => $other['att2']]);
}
if (isset($other['att3']) && is_integer($other['att3'])) {
$query->andWhere(['att3' => $other['att3']]);
}
if (isset($other['flag'])) {
$flagValue = CommonUtility::getFlagValue($other['flag']);
if ($flagValue > 0) {
$query->andWhere('flag&' . $flagValue . '>0');
}
}
if (isset($other['is_pic']) && $other['is_pic']) {
$query->andWhere(['is_pic' => 1]);
}
if (isset($other['order'])) {
$query->orderBy($other['order']);
} else {
$query->orderBy('publish_time desc');
}
if (isset($other['offset'])) {
$query->offset(intval($other['offset']));
} else {
$query->offset(0);
}
if (isset($other['limit'])) {
$query->limit(intval($other['limit']));
} else {
$query->limit(10);
}
return $query;
}
public static function getTable($users, $apteki, $date, $trimestr, $scroll = null)
{
$countMon = "";
//$nowdate = date('Y') . '-' . self::getFirstMonthTrimestr($trimestr) . '-01';
$now = new \DateTime($date);
$clone = $now;
// $table= $now->format( 'd-m-Y' ). "\n". $clone->format( 'd-m-Y' );
$table = "";
$table .= '<table class="table tableVisitors">
<thead>
<tr>
<th>Розничная точка</th>';
$clone->modify('-1 month');
for ($i = 0; $i < 3; $i++) {
$mon = $clone->format('n');
$table .= "<th align='center'>" . \Yii::$app->params['ruMonth'][$mon] . "</th>";
$dateSearch = $clone->format('Y-m');
//$visitors[$i] = self::find()->andFilterWhere(['LIKE', 'date', $dateSearch])->andFilterWhere(['=', 'user_id', $users])->all();
$query = new Query();
$query->select(['visitors.apteki_id', 'visitors.date_type', 'visitors.date']);
$query->from('apteki');
$query->InnerJoin('users', 'users.id = apteki.pi_id');
$query->LeftJoin('visitors', 'visitors.apteki_id = apteki.id');
$query->andFilterWhere(['=', 'apteki.farmopeka', '1']);
$query->andFilterWhere(['=', 'users.id', $users]);
$query->andFilterWhere(['LIKE', 'visitors.date', $dateSearch]);
$query->orderBy('apteki.address');
$visitors[$i] = $query->all();
$mydate[$i] = $clone->format('Y-m');
$myMon[$i] = $clone->format('n');
$clone->modify('+1 month');
}
$table .= '</thead><tbody>';
foreach ($apteki as $apt) {
$style = "";
if ($scroll == $apt['id']) {
$style = "bgcolor='#fdf579'";
}
$table .= '<tr class="trr" id="' . $apt['id'] . '" ' . $style . '>
<td><a name="apt' . $apt['id'] . '" href="/apteki/update?id=' . $apt['id'] . '"><b>' . $apt['name'] . '</b> ' . $apt['address'] . '</a></td>';
for ($n = 0; $n < count($visitors); $n++) {
$m = null;
foreach ($visitors[$n] as $vis) {
if ($apt['id'] == $vis['apteki_id']) {
$monTemp = $myMon[$n];
if (!isset($countMon[$monTemp][1])) {
$countMon[$monTemp][1] = 0;
}
if (!isset($countMon[$monTemp][2])) {
$countMon[$monTemp][2] = 0;
}
if ($vis['date_type'] == 1) {
$alert = "warning";
$countMon[$monTemp]['1'] = $countMon[$monTemp]['1'] + 1;
}
if (date('dmY', strtotime($vis['date'])) == date('dmY')) {
//Подсетка текущей даты
$table .= '<script>
$("#' . $apt['id'] . '").css( "background", "#ddd" );
</script>';
}
if ($vis['date_type'] == 2) {
$alert = "success";
$countMon[$monTemp]['2'] = $countMon[$monTemp]['2'] + 1;
}
$table .= '<td align="center" class="' . $alert . '"><a href="/visitors/update/?date=' . $mydate[$n] . '-' . date('d', strtotime($vis['date'])) . '&apt=' . $apt['id'] . '"><span class="badge">' . date('d', strtotime($vis['date'])) . '</span></a></div>';
$m = 1;
}
}
if (!$m) {
$table .= '<td align="center" class="addnewdate">';
$table .= '<a href="/visitors/update/?date=' . $mydate[$n] . '&apt=' . $apt[id] . '"><span class="glyphicon glyphicon-flag"></span></td></a></td>';
}
}
$table .= '</tr>';
}
$table .= '</tbody>
</table><br>';
$table2 = "<br><table>";
if ($countMon) {
foreach ($countMon as $countMon2 => $val) {
$table2 .= "<tr><td><span class=\"label label-info\">" . \Yii::$app->params['ruMonth'][$countMon2] . "</span></td>";
$table2 .= "<td><span class=\"label label-warning\">" . $val[1] . "</span></td>";
$table2 .= "<td><span class=\"label label-success\">" . $val[2] . "</span></td>";
$table2 .= "</tr>";
}
}
$table2 .= "</table>";
return $table2 . $table;
}
