Beispiel #1
0
 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();
 }
Beispiel #2
0
 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();
 }
Beispiel #3
0
 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();
 }
Beispiel #4
0
 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();
 }
Beispiel #5
0
 /**
  * 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();
 }
Beispiel #6
0
 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]);
 }
Beispiel #7
0
 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;
 }
Beispiel #8
0
 /**
  * Получает записи с базы данных
  * @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;
 }
Beispiel #9
0
 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;
 }
Beispiel #11
0
 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;
 }
Beispiel #12
0
 /**
  * @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;
 }
Beispiel #13
0
 /**
  * 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]);
 }
Beispiel #14
0
 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;
 }
Beispiel #15
0
 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;
 }
Beispiel #16
0
 /**
  * @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);
 }
Beispiel #18
0
 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;
 }
Beispiel #19
0
 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;
 }
Beispiel #20
0
 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;
 }
Beispiel #21
0
 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;
 }