Sets the GROUP BY part of the query.
| public groupBy ( string | array | yii\db\Expression $columns ) | ||
| $columns | string | array | yii\db\Expression | the columns to be grouped by. Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression). Note that if your group-by is an expression containing commas, you should always use an array to represent the group-by information. Otherwise, the method will not be able to correctly determine the group-by columns. Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
/**
* 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 testGroup()
{
$query = new Query();
$query->groupBy('team');
$this->assertEquals(['team'], $query->groupBy);
$query->addGroupBy('company');
$this->assertEquals(['team', 'company'], $query->groupBy);
$query->addGroupBy('age');
$this->assertEquals(['team', 'company', 'age'], $query->groupBy);
}
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 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 actionTable()
{
if (\Yii::$app->user->identity->status == 1) {
$users = Users::find()->where(['status' => 2])->orderBy('username')->all();
} else {
$users = Users::find()->where(['id' => \Yii::$app->user->identity->id])->orderBy('username')->all();
}
foreach ($users as $user) {
$id = $user['id'];
$db = new Query();
$db->from(Ur::tableName());
$db->select(['COUNT(*) AS count', 'id_reg', 'region.name']);
$db->where(['=', 'ur_l.regional_id', $id]);
$db->andWhere(['=', 'ur_l.plat', '1']);
$db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id");
$db->innerJoin('region', "region.id = region_ur_l.id_reg");
$db->groupBy('region_ur_l.id_reg');
$db->orderBy('region.name ASC');
$ur_region_array[$id] = $db->all();
$db = new Query();
$db->from(Apteki::tableName());
$db->select(['COUNT(*) AS count', 'region.id', 'region.name']);
$db->where(['=', 'apteki.regional_id', $id]);
$db->innerJoin('region', "region.id = apteki.region_id");
$db->groupBy('region.id');
$db->orderBy('region.name ASC');
$apteki_region_array[$id] = $db->all();
}
return $this->render('table', compact(['ur_region_array', 'apteki_region_array', 'users']));
}
protected function getClusteringMarkers($zoom, $lat_max, $lat_min, $lng_min, $lng_max)
{
$number = 0;
$multiplyNumber = 1 / 500 * pow(2.4, $zoom);
$query = new Query();
$query->from('{{%adver}}')->select(["COUNT(*) AS [[adver_count]]", "AVG(latitude) AS [[lat]]", "AVG(longitude) AS [[lng]]", "FORMAT([[latitude]] * {$multiplyNumber} , {$number}) AS [[g_lt]]", "FORMAT([[longitude]] * {$multiplyNumber} , {$number}) AS [[g_ln]]"]);
$query->andWhere(['between', '[[latitude]]', $lat_min, $lat_max]);
$query->andWhere(['between', '[[longitude]]', $lng_min, $lng_max]);
$query->WHERE(['[[status]]' => self::STATUS_ACTIVE, 'lang' => ['*', Yii::$app->language]]);
// adjust the query by adding the filters
$query->andFilterWhere(['[[category_id]]' => $this->category_id]);
$query->andFilterWhere(['[[country_id]]' => $this->country_id]);
$query->andFilterWhere(['[[province_id]]' => $this->province_id]);
$query->andFilterWhere(['[[city_id]]' => $this->city_id]);
$query->andFilterWhere(['like', '[[title]]', $this->title]);
$query->andFilterWhere(['like', '[[address]]', $this->address]);
$query->groupBy(['[[g_lt]]', '[[g_ln]]']);
return $this->mergeBubbles($zoom, $query->all());
}
public function getSOLineRelatedQuery($params = [], $groupBy = null)
{
$query = new Query();
if ($params['productcategory']) {
if (is_array($params['productcategory'])) {
$category = implode(",", $params['productcategory']);
} else {
$category = $params['productcategory'];
}
} else {
$category = '0';
}
if ($params['partner']) {
$partner = $params['partner'];
} else {
$partner = '0';
}
if ($params['product']) {
$product = $params['product'];
} else {
$product = '0';
}
if ($params['state']) {
$state = $params['state'];
} else {
$state = '0';
}
if ($params['date_from']) {
$dattefrom = $params['date_from'];
$dateto = $params['date_to'];
} else {
$dattefrom = '0';
$dateto = '0';
}
if ($params['pricelist']) {
if (is_array($params['pricelist'])) {
$pricelist = implode(",", $params['pricelist']);
} else {
$pricelist = $params['pricelist'];
}
} else {
$pricelist = '0';
}
// jika group
if ($groupBy) {
$query->select(['CONCAT("pc"."id", \'/\', "pid"."id", \'/\', "pc"."name", \'/\', "pid"."name",\'/\',\'' . $category . '\',\'/\',\'' . $partner . '\',\'/\',\'' . $product . '\',\'/\',\'' . $pricelist . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id,
pc.name as category,
SUM(sol.price_unit*sol.product_uom_qty) as total,
pid.name as pricelist']);
} else {
$query->select('
sol.id as id,
so.partner_id as partner_id,
so.date_order as date_order,
so.name as no_po,
rp.name as partner,
sol.product_id as product_id,
pp.name as product,
sol.price_unit as price_unit,
sol.state as state,
pc.name as category,
pid.name as pricelist,
sol.product_uom_qty as qty,
sol.name as product_desc,
(sol.product_uom_qty*sol.price_unit) as total,
so.name as so_no
');
}
$query->from('sale_order_line as sol')->join('LEFT JOIN', 'sale_order as so', 'so.id=sol.order_id')->join('LEFT JOIN', 'product_template as pp', 'pp.id=sol.product_id')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=so.partner_id')->join('LEFT JOIN', 'product_category as pc', 'pc.id=pp.categ_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=so.pricelist_id');
if ($groupBy) {
$query->groupBy(['pc.id', 'pid.id']);
}
if (isset($params['partner']) && $params['partner']) {
if ($params['partner'] != '0') {
$query->andWhere(['so.partner_id' => explode(',', $params['partner'])]);
}
}
if (isset($params['productcategory']) && $params['productcategory']) {
if ($params['productcategory'] != '0') {
if (is_array($params['productcategory'])) {
$query->andWhere(['pp.categ_id' => explode(',', implode(",", $params['productcategory']))]);
} else {
$query->andWhere(['pp.categ_id' => explode(',', $params['productcategory'])]);
}
}
}
if (isset($params['product']) && $params['product']) {
if ($params['product'] != '0') {
$query->andWhere(['sol.product_id' => explode(',', $params['product'])]);
}
}
if (isset($params['pricelist']) && $params['pricelist']) {
if ($params['pricelist'] != '0') {
if (is_array($params['pricelist'])) {
$query->andWhere(['so.pricelist_id' => explode(',', implode(",", $params['pricelist']))]);
} else {
$query->andWhere(['so.pricelist_id' => explode(',', $params['pricelist'])]);
}
}
}
// if(isset($params['state']) && $params['state']){
// if($params['state']!='0')
// {
// $query->andWhere(['sol.state'=>explode(',',$params['state'])]);
// }
// }
if (isset($params['state']) && $params['state']) {
if ($params['state'] == "order") {
$cekstate = 'confirmed, approved, done';
if ($params['state'] != '0') {
$query->andWhere(['sol.state' => explode(',', $cekstate)]);
}
} else {
if ($params['state'] != '0') {
$query->andWhere(['sol.state' => explode(',', $params['state'])]);
}
}
} else {
$query->andWhere(['in', 'sol.state', ['confirmed', 'approved', 'done']]);
}
if (isset($params['date_from']) && $params['date_from']) {
if ($params['date_from'] != '0') {
$query->andWhere(['>=', 'so.date_order', $params['date_from']]);
$query->andWhere(['<=', 'so.date_order', $params['date_to']]);
}
}
$query->andWhere(['not', ['sol.product_id' => null]]);
if (!$groupBy) {
$query->addOrderBy(['so.date_order' => SORT_DESC]);
}
return $query;
}
private static function saveErrorLogMonth($str_time, $appname_list)
{
$format_cur_time = date("Y-m-d H:i:s", time());
$cur_time_year = date("Y");
$cur_time_month = date("m");
$str_time_year = date("Y", $str_time);
$str_time_month = date("m", $str_time);
$diff_month = ($cur_time_year - $str_time_year) * 12 + ($cur_time_month - $str_time_month);
$end_time = strtotime('+1 month', $str_time);
$format_str_time = date("Y-m-d H:i:s", $str_time);
$format_end_time = date("Y-m-d H:i:s", $end_time);
for ($i = 0; $i < $diff_month; $i++) {
//统计错误日志的数量
$error_query = new Query();
$error_query->select("count(id) as total,ApplicationId")->from("ErrorLog")->where(["in", "ApplicationId", $appname_list]);
$error_query->andWhere("AddDate>=:str_time", array(":str_time" => $format_str_time));
$error_query->andWhere("AddDate<:end_time", array(":end_time" => $format_end_time));
$error_query->groupBy("ApplicationId");
$error_count_list = $error_query->all();
$log_arr = [];
$month = strtotime($format_str_time);
foreach ($error_count_list as $key => $value) {
//保存数据在ErrorLog_month
$log_arr[] = [$value['ApplicationId'], $value['total'], date("Ym", $month), $format_cur_time];
}
if (!empty($log_arr)) {
$command = \Yii::$app->db->createCommand();
$command->batchInsert(ErrorLogMonth::tableName(), ['ApplicationId', 'Number', 'Month', 'Updatetime'], $log_arr);
$command->execute();
}
$format_str_time = $format_end_time;
$end_time = strtotime('+1 month', $end_time);
$format_end_time = date("Y-m-d H:i:s", $end_time);
}
}
