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();
}
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 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();
}
/**
* Returns list of users' full names
* @param int $limit records count
* @return array list of users' names
*/
public static function getUserList($limit = null)
{
$query = new Query();
$query->select(['id', 'text' => "CONCAT(`u`.`first_name`,' ', `u`.`last_name`)"])->from(['u' => 'User']);
if (isset($limit)) {
$query->limit($limit);
}
$query->each();
$command = $query->createCommand();
return $command->queryAll();
}
public function getTopPsychologists($amount = null)
{
$query = new Query();
$query->select('profile.*')->from('profile')->join('join', 'psychologist_top', 'psychologist_top.psychologist_id = profile.user_id')->orderBy('profile.id');
if ($amount) {
$query->limit($amount);
return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]);
//return $query->all();
}
return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]);
}
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
/**
* Получает записи с базы данных
* @param string $where
* @param string $column
*/
public function GetItems($columns = [], $where = [], $limit = 0)
{
$query = new Query();
$query->select($columns);
$query->from($this->table);
if ($where) {
$query->where($where);
}
if ($limit) {
$query->limit($limit);
}
$result = $query->all();
return $result;
}
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
if (isset($filters['D05_DEN_NO']) && $filters['D05_DEN_NO']) {
$query->andwhere('D05_DEN_NO=:den_no', [':den_no' => $filters['D05_DEN_NO']]);
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
/**
* @return array
* @throws yii\web\NotFoundHttpException
*/
public function run()
{
if (!isset($_GET[$this->query_variable])) {
throw new yii\web\NotFoundHttpException();
}
$modelName = $this->modelName;
Yii::$app->response->format = yii\web\Response::FORMAT_JSON;
$query = new yii\db\Query();
$query->select($this->json_attributes)->from($modelName::tableName());
$search_query = $_GET[$this->query_variable];
foreach ($this->search_attributes as $attribute) {
$query->orWhere(['like', $attribute, $search_query]);
}
$suggest = $query->limit($this->limit)->all();
return $suggest;
}
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
if (count($filters)) {
foreach ($filters as $field => $val) {
if ($field != 'offset' && $field != 'limit') {
$query->andwhere($field . ' = ' . $val);
}
}
}
//$query->where('status=:status', [':status' => $status]);
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
/**
* @param array $filters
* @param string $select
* @return Query
*/
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
if (isset($filters['M08_HAN_CD']) && $filters['M08_HAN_CD']) {
$query->where('M08_HAN_CD=:han_cd', [':han_cd' => $filters['M08_HAN_CD']]);
}
if (isset($filters['M08_SS_CD']) && $filters['M08_SS_CD']) {
$query->where('M08_SS_CD=:ss_cd', [':ss_cd' => $filters['M08_SS_CD']]);
}
if (isset($filters['M08_JYUG_CD']) && $filters['M08_JYUG_CD']) {
$query->where('M08_JYUG_CD=:jy_cd', [':jy_cd' => $filters['M08_JYUG_CD']]);
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
/**
* 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]);
}
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
//$query->where('status=:status', [':status' => $status]);
if (isset($filters['D01_KAIIN_CD']) && $filters['D01_KAIIN_CD']) {
$query->andwhere('D01_KAIIN_CD = ' . $filters['D01_KAIIN_CD']);
}
if (isset($filters['D01_CUST_NO']) && $filters['D01_CUST_NO']) {
$query->andwhere('D01_CUST_NO = ' . $filters['D01_CUST_NO']);
}
if (isset($filters['D01_KAKE_CARD_NO']) && $filters['D01_KAKE_CARD_NO']) {
$query->where(['D01_KAKE_CARD_NO' => $filters['D01_KAKE_CARD_NO']]);
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
public static function getList($page = 1, $rows = 10, $condition = '', $conditionParams = [])
{
$return = [];
$query = new Query();
$query->select('COUNT(1)');
$query->from(self::tableName() . ' t0');
$query->where($condition, $conditionParams);
$result['total'] = $query->scalar();
$query->select(['t0.id', 't0.username', 't0.email', 't1.firstname', 't1.lastname']);
$query->join('LEFT JOIN', 'user_profile t1', 't0.id = t1.user_id');
$query->offset($page * $rows - $rows);
$query->limit($rows);
$result['rows'] = $query->All();
foreach ($result['rows'] as $k => $v) {
$myRoles = Yii::$app->authManager->getRolesByUser($v['id']);
if (count($myRoles)) {
foreach ($myRoles as $kk => $vv) {
$result['rows'][$k]['role'] = $kk;
break;
}
}
}
return $result;
}
/**
* @param array $filters
* @param string $select
* @return Query
*/
private function getWhere($filters = [], $select = '*')
{
$query = new Query();
$query->select($select)->from(static::tableName());
if (isset($filters['M05_COM_CD_IN']) && $filters['M05_COM_CD_IN'] != '') {
$query->andwhere(['IN', 'M05_COM_CD', $filters['M05_COM_CD_IN']]);
}
if (isset($filters['M05_COM_CD']) && $filters['M05_COM_CD'] != '') {
$query->andwhere(['like', 'M05_COM_CD', $filters['M05_COM_CD']]);
}
if (isset($filters['M05_COM_NAMEN']) && $filters['M05_COM_NAMEN'] != '') {
$query->andwhere(['like', 'M05_COM_NAMEN', $filters['M05_COM_NAMEN']]);
}
if (isset($filters['M05_NST_CD']) && $filters['M05_NST_CD'] != '') {
$query->andwhere(['like', 'M05_NST_CD', $filters['M05_NST_CD']]);
}
if (isset($filters['in']) && count($filters['in'])) {
$query->andwhere(['IN', 'M05_KIND_DM_NO', $filters['in']]);
}
if (isset($filters['not_in']) && count($filters['not_in'])) {
$query->andwhere('M05_KIND_DM_NO NOT IN (' . implode(',', $filters['not_in']) . ') OR M05_KIND_DM_NO IS NULL');
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
return $query;
}
public function testLimitOffset()
{
$query = new Query();
$query->limit(10)->offset(5);
$this->assertEquals(10, $query->limit);
$this->assertEquals(5, $query->offset);
}
private function getWhereSearch($filters)
{
$query = new Query();
$query->select(["SDP_TD03_DENPYO.*, TO_CHAR(D03_UPD_DATE, 'YYYY/mm/DD') as CHAR_D03_UPD_DATE,\n SDP_TD01_CUSTOMER.D01_CUST_NAMEN, SDP_TD01_CUSTOMER.D01_CUST_NAMEK, SDP_TD01_CUSTOMER.D01_NOTE"])->from(static::tableName())->leftJoin('SDP_TD01_CUSTOMER', 'SDP_TD03_DENPYO.D03_CUST_NO = SDP_TD01_CUSTOMER.D01_CUST_NO')->orderBy('D03_DEN_NO');
if (isset($filters['status']) && $filters['status'] != '') {
if ($filters['status'] == 1) {
$query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]);
$query->andWhere(['>', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]);
}
if ($filters['status'] == 2) {
$query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 1]);
}
if ($filters['status'] == 0) {
$query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]);
$query->andWhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]);
}
}
if (isset($filters['car']) && $filters['car']) {
$query->andwhere(['like', 'SDP_TD03_DENPYO.D03_CAR_NO', $filters['car']]);
}
if (isset($filters['job']) && $filters['job'] != '') {
$denpyo_sagyo = new Sdptd04denpyosagyo();
$data = $denpyo_sagyo->getData(['D04_SAGYO_NO' => $filters['job']]);
$arr_den_no = [-1];
foreach ($data as $k => $v) {
$arr_den_no[] = $v['D04_DEN_NO'];
}
$query->andwhere(['in', 'SDP_TD03_DENPYO.D03_DEN_NO', $arr_den_no]);
}
if (isset($filters['start_time']) && $filters['start_time']) {
$query->andwhere(['>=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['start_time']]);
}
if (isset($filters['end_time']) && $filters['end_time']) {
$query->andwhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['end_time']]);
}
if (isset($filters['detail_no'])) {
$query->andwhere('SDP_TD03_DENPYO.D03_DEN_NO=:den_no', [':den_no' => $filters['detail_no']]);
}
if (isset($filters['m50_ss_cd'])) {
$query->andwhere('SDP_TD03_DENPYO.D03_SS_CD =:m50_ss_cd', [':m50_ss_cd' => $filters['m50_ss_cd']]);
}
if (isset($filters['offset']) && $filters['offset']) {
$query->offset($filters['offset']);
}
if (isset($filters['limit']) && $filters['limit']) {
$query->limit($filters['limit']);
}
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;
}
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 function ur_l()
{
$db = new Query();
if ($_POST['search']['value'] != "") {
$db->where(['like', 'name', $_POST['search']['value']]);
$db->orWhere(['like', 'address', $_POST['search']['value']]);
$db->orWhere(['like', 'inn', $_POST['search']['value']]);
$db->orWhere(['like', 'wabc', $_POST['search']['value']]);
}
if (is_numeric($_POST['region'])) {
$db->InnerJoin('region_ur_l', 'id_ur = ur_l.id');
$db->andWhere(['=', 'id_reg', $_POST['region']]);
}
if ($_POST['pharmopeka']) {
$db->andWhere(['=', 'ur_l.farmopeka', '1']);
}
if ($_POST['order'][0]['column']) {
$column_order = $_POST['order'][0]['column'];
$column = $_POST['columns'][$column_order]['data'];
$db->orderBy($_POST['columns'][$column_order]['data'] . " " . $_POST['order'][0]['dir']);
}
if (Yii::$app->user->identity->status == 2) {
//Регионалы
$db->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
}
if (Yii::$app->user->identity->status == 3) {
//Провизоры
$db->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
}
$db->from('ur_l');
$db->select(['ur_l.name', 'ur_l.id', 'ur_l.inn', 'ur_l.address', 'ur_l.wabc']);
$db->limit(10);
$db->offset(intval(Yii::$app->request->post('start')));
$data = $db->all();
$count = $db->count();
for ($i = 0; $i < count($data); $i++) {
$data[$i]['name'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][name] . "</a>";
$data[$i]['id'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][id] . "</a>";
}
$array = array('draw' => intval(Yii::$app->request->post('draw')), 'recordsTotal' => $count, 'recordsFiltered' => $count, 'data' => $data);
return $array;
}
