public function actionIndex() { Yii::$app->response->format = Response::FORMAT_JSON; $params = Yii::$app->getRequest(); $filter = array(); $sort = ""; $page = isset($params->page) ? $params->page : 1; $limit = isset($params->limit) ? $params->limit : 10; $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params->filter)) { $filter = (array) json_decode($params->filter); } if (isset($params->sort)) { $sort = $params->sort; if (isset($params->order)) { if ($params->order == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('restaurants')->orderBy($sort)->select("restaurant_id, name, street, house_nr, flat_nr, zip_code, city"); $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
/** * Lists all Airport models. * @return mixed */ public function actionIndex() { $params = $_REQUEST; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); if (isset($params['airport_code'])) { $query->offset($offset)->limit($limit)->from('airport')->andFilterWhere(['like', 'airport_code', $params['airport_code']])->orderBy($sort)->select("id,airport_code,airport_name,country,city"); } else { $query->offset($offset)->limit($limit)->from('airport')->orderBy($sort)->select("id,airport_code,airport_name,country,city"); } /* if(!empty($filter)){ $query->offset($offset) ->limit($limit) ->from('airport') // ->andFilterWhere(['like', 'id', $filter['id']]) ->andFilterWhere(['like', 'airport_code', $filter['airport_code']]) // ->andFilterWhere(['like', 'airport_name', $filter['airport_name']]) ->orderBy($sort) ->select("id,airport_code,airport_name,country,city"); }else{ */ /* }*/ $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
/** * Lists all City models. * @return mixed */ public function actionIndex() { $params = $_REQUEST; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } /* echo "<pre>"; print_r($params); echo "</pre>"; echo "<pre>"; print_r($filter); echo "</pre>"; exit;*/ if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('user')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'name', $filter['name']])->andFilterWhere(['like', 'age', $filter['age']])->orderBy($sort)->select("id,name,age,createdAt,updatedAt"); if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); } $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
/** * Lists all ExploreRecommend models. * @return mixed */ public function actionIndex() { $query = new Query(); $query->select('id, summary, created_at, title, username, user_id')->from('{{%explore_recommend}}')->where(['category' => 'post'])->orderBy('id DESC'); $pages = new \yii\data\Pagination(['totalCount' => $query->count()]); $posts = $query->offset($pages->offset)->limit($pages->limit)->all(); $forums = new Query(); $forums = $forums->select('f.forum_name, f.forum_url, f.forum_desc, f.forum_icon, e.id')->from('{{%forum}} as f')->join('LEFT JOIN', '{{%explore_recommend}} as e', 'e.table_id=f.id')->where(['e.category' => 'forum'])->orderBy('e.id DESC')->all(); $albums = new Query(); $albums = $albums->select('id, table_id')->from('{{%explore_recommend}}')->where(['category' => 'album'])->orderBy('id DESC')->all(); return $this->render('index', ['posts' => $posts, 'pages' => $pages, 'forums' => $forums, 'albums' => $albums]); }
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; }
public function actionIndex() { $params = $_REQUEST; //TODO Calling unknown method: yii\web\Request::getParams() why? //$params = \Yii::$app->request->getParams(); var_dump($params); exit; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('user')->orderBy($sort)->select("*"); /*if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); }*/ $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
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; }
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; }
public static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false) { $query = new Query(); $query->select('*')->from($tableName); if (!empty($conditions)) { foreach ($conditions as $name => $value) { if (is_array($value)) { $query->andWhere($value); } else { $query->andWhere([$name => $value]); } } } $total = 0; if ($isGetTotal) { $total = $query->count('*'); } $query->offset($offset)->limit($size)->orderBy($orderby); $list = $query->all(); return ['list' => $list, 'total' => $total]; }
/** * @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; }
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; }
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; }
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 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 actionIndex() { $role = UserRoleDetector::getUserRole(); Yii::$app->response->format = Response::FORMAT_JSON; if (isset($role)) { echo json_encode(array('status' => 0, 'error_code' => Codes::$UNAUTHORIZED, 'errors' => StatusCodeMessage::$UNAUTHORIZED), JSON_PRETTY_PRINT); } else { $params = Yii::$app->getRequest(); $filter = array(); $sort = ""; $page = isset($params->page) ? $params->page : 1; $limit = isset($params->limit) ? $params->limit : 10; $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params->filter)) { $filter = (array) json_decode($params->filter); } if (isset($params->sort)) { $sort = $params->sort; if (isset($params->order)) { if ($params->order == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('users')->orderBy($sort)->select("user_id, login, email, name, surname, street, house_nr, flat_nr, zipcode, city"); $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); } }
/** * @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; }