SqlDataProvider provides data in terms of arrays, each representing a row of query result. Like other data providers, SqlDataProvider also supports sorting and pagination. It does so by modifying the given [[sql]] statement with "ORDER BY" and "LIMIT" clauses. You may configure the [[sort]] and [[pagination]] properties to customize sorting and pagination behaviors. SqlDataProvider may be used in the following way: php $count = Yii::$app->db->createCommand(' SELECT COUNT(*) FROM user WHERE status=:status ', [':status' => 1])->queryScalar(); $dataProvider = new SqlDataProvider([ 'sql' => 'SELECT * FROM user WHERE status=:status', 'params' => [':status' => 1], 'totalCount' => $count, 'sort' => [ 'attributes' => [ 'age', 'name' => [ 'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC], 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC], 'default' => SORT_DESC, 'label' => 'Name', ], ], ], 'pagination' => [ 'pageSize' => 20, ], ]); get the user records in the current page $models = $dataProvider->getModels(); Note: if you want to use the pagination feature, you must configure the [[totalCount]] property to be the total number of rows (without pagination). And if you want to use the sorting feature, you must configure the [[sort]] property so that the provider knows which columns can be sorted. For more details and usage information on SqlDataProvider, see the guide article on data providers.
Since: 2.0
Author: Qiang Xue (qiang.xue@gmail.com)
Inheritance: extends BaseDataProvider
SqlDataProvider Class Documentation
Exemple #1
0
 public function dataRekamDiagnosa()
 {
     $user = Yii::$app->user->id;
     $count = Yii::$app->db->createCommand('SELECT COUNT(DISTINCT(diagnosa_ke))  FROM  diagnosa_user WHERE user_id=:user_id GROUP BY  diagnosa_ke', [':user_id' => $user])->execute();
     $dataProvider = new SqlDataProvider(['sql' => 'SELECT *  FROM  diagnosa_user WHERE user_id=:user_id GROUP BY  diagnosa_ke', 'params' => [':user_id' => $user], 'totalCount' => $count, 'sort' => ['attributes' => ['diagnosa_ke']], 'pagination' => ['pageSize' => 20]]);
     // get the user records in the current page
     $models = $dataProvider->getModels();
     return $dataProvider;
 }
Exemple #2
0
 public function dataRekamDiagnosa($ke)
 {
     $user = Yii::$app->user->id;
     // $count = Yii::$app->db->createCommand('SELECT count(id)  FROM  diagnosa_user_detil', [':user_id' => $user])->execute();
     $count = Yii::$app->db->createCommand('SELECT count(id)  FROM  diagnosa_user_detil')->execute();
     $sql = "SELECT \n                dud.`diagnosa_user_id`,\n                du.`user_id`,\n                du.`diagnosa_ke`,\n                du.`diagnosa_date`,\n                dud.`gejala_id`,\n                pg.`nama_gejala`\n                FROM diagnosa_user_detil dud \n                JOIN penyakit_gejala pg ON dud.`gejala_id`=pg.`id`\n                JOIN diagnosa_user du ON du.`id`=dud.`diagnosa_user_id`\n                where du.`user_id`=:user_id and du.`diagnosa_ke`=:ke\n                #ORDER BY du.`id`,dud.`gejala_id`";
     $dataProvider = new SqlDataProvider(['sql' => $sql, 'params' => [':user_id' => $user, ':ke' => $ke], 'totalCount' => $count, 'sort' => ['attributes' => ['diagnosa_ke', 'nama_gejala']], 'pagination' => ['pageSize' => 5]]);
     // get the user records in the current page
     $models = $dataProvider->getModels();
     return $dataProvider;
 }
Exemple #3
0
 public function actionProposalExport()
 {
     // return 'Export excel';
     $dataProvider = new SqlDataProvider(['sql' => "SELECT * FROM proposal"]);
     $filename = "Proposal-Export-" . Date('YmdGis') . '-Mahasiswa.xls';
     $model = $dataProvider->getModels();
     header('Content-type:application/vnd-ms-excel');
     header('Content-Disposition: attachement;filename=' . $filename);
     echo "<h1>Proposal Mahasiswa</h1>";
     echo "<table border=1>\n                 <tr>\n                    <td>No</td>\n                    <td>Nim</td>\n                    <td>Nama Mahasiswa</td>\n                    <td>Judul Proposal</td>\n                    <td>Pembimbing</td>\n                </tr>";
     $no = 1;
     foreach ($model as $key => $value) {
         echo "\n                 <tr>\n                    <td>" . $no . "</td>\n                    <td>" . $value['nim'] . "</td>\n                    <td>" . $value['nama_mahasiswa'] . "</td>\n                    <td>" . $value['judul'] . "</td>\n                    <td>" . $value['nama_dosen'] . "</td>\n                </tr>";
         $no++;
     }
     echo "</table>";
 }
Exemple #4
0
 public static function getAllActivityByUserId($userId = '', $isPage = true)
 {
     $count = Yii::$app->db->createCommand('SELECT (au.id) as count FROM  ' . self::tableName() . ' au LEFT JOIN ' . Activity::tableName() . ' a ON au.activity_id = a.id WHERE au.user_id=:user_id', [':user_id' => $userId])->queryScalar();
     $dataProvider = new SqlDataProvider(['sql' => 'SELECT au.id as id,au.activity_id,a.name,a.category,a.create_time,a.address,a.lesson,a.score,au.status,a.begin_time,a.recruit_count FROM  ' . self::tableName() . ' au LEFT JOIN ' . Activity::tableName() . ' a ON au.activity_id = a.id WHERE au.user_id=:user_id', 'params' => [':user_id' => $userId], 'totalCount' => $count, 'pagination' => ['pageSize' => $isPage ? '5' : '9999']]);
     $models = $dataProvider->getModels();
     return $models;
 }
Exemple #5
0
 /**
  * Show advanced options for a request model.
  * For ajax request will return json object
  * and for non-ajax request if deletion is successful, the browser will be redirected to the 'index' page.
  * @param string $id
  * @return mixed
  * @throws NotFoundHttpException
  */
 public function actionAdvanced($id)
 {
     $request = Yii::$app->request;
     if (!Yii::$app->user->isGuest) {
         $specificRequest = $this->findModel($id);
         if ($request->isAjax) {
             /*
              *   Process for ajax request
              */
             Yii::$app->response->format = Response::FORMAT_JSON;
             return ['forceClose' => true, 'forceReload' => '#crud-datatable-pjax'];
         } else {
             /*
              *   Process for non-ajax request
              */
             $availableUsersQuery = new Query();
             $availableUsersQuery->select('*')->from('users')->leftJoin('users_request', '`users`.`id` = `users_request`.`user_id`
                 and `users_request`.`request_id` = ' . $id)->where(['users_request.user_id' => null]);
             $command = $availableUsersQuery->createCommand();
             $availableUsers = $command->queryAll();
             $users = ArrayHelper::map($availableUsers, 'id', 'first_name');
             $dataProvider = new SqlDataProvider(['sql' => 'SELECT * FROM users_request WHERE request_id=:id', 'params' => [':id' => $id]]);
             $models = $dataProvider->getModels();
             return $this->render('advanced', ['request' => $specificRequest, 'users' => $users, 'responsible' => $models]);
         }
     } else {
         throw new NotFoundHttpException('The requested page does not exist.');
     }
 }
Exemple #6
0
 public function actionVolunteer($id)
 {
     $count = Yii::$app->db->createCommand("SELECT COUNT(EV.id) FROM edge_volunteer EV INNER JOIN volunteers V ON V.id = EV.reference_id WHERE 1=1 AND course_id=:course_id", [":course_id" => $id])->queryScalar();
     $dataProvider = new SqlDataProvider(['sql' => 'SELECT EV.id,EV.course_id,V.first_name,V.last_name FROM edge_volunteer EV INNER JOIN volunteers V ON V.id = EV.reference_id WHERE 1=1 AND course_id=:course_id', 'params' => [':course_id' => $id], 'totalCount' => $count, 'sort' => ['attributes' => ['name' => ['asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC], 'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC], 'default' => SORT_DESC, 'label' => 'Name'], 'first_name', 'last_name']], 'pagination' => ['pageSize' => 20]]);
     $models = $dataProvider->getModels();
     $model = EdgeCourse::findOne($id);
     return $this->render('volunteer', ["model" => $dataProvider, "modelData" => $model]);
 }
Exemple #7
0
 public function actionSearch($type, $reg, $w, $p, $n, $m)
 {
     $conn = \Yii::$app->db;
     $regionalsN = $conn->createCommand('SELECT UCS from NEW_SME where UCS is not null group by UCS order by UCS ASC')->query();
     $productsN = $conn->createCommand("SELECT PROD_TELKOM FROM NEW_SME WHERE PROD_TELKOM IS NOT NULL OR PROD_TELKOM!='' GROUP BY PROD_TELKOM ORDER BY PROD_TELKOM ASC")->query();
     $nonproductsN = $conn->createCommand("SELECT NON_PRODUK_TELKOM FROM NEW_SME WHERE NON_PRODUK_TELKOM IS NOT NULL OR NON_PRODUK_TELKOM!='' GROUP BY NON_PRODUK_TELKOM ORDER BY NON_PRODUK_TELKOM ASC")->query();
     $regionalsE = $conn->createCommand('SELECT REGIONAL from DATA_PROSPEK where REGIONAL is not null group by REGIONAL order by REGIONAL ASC')->query();
     $productsE = $conn->createCommand("SELECT P_TYPE FROM DATA_PROSPEK WHERE P_TYPE IS NOT NULL OR P_TYPE!='' GROUP BY P_TYPE ORDER BY P_TYPE ASC")->query();
     $networksE = $conn->createCommand("SELECT TYPE_JARINGAN FROM DATA_PROSPEK WHERE TYPE_JARINGAN IS NOT NULL OR TYPE_JARINGAN!='' GROUP BY TYPE_JARINGAN ORDER BY TYPE_JARINGAN ASC")->query();
     if ($type == 'new') {
         if ($m == 'twitter') {
             $sql = "(TWITTER_ACCOUNT IS NOT NULL OR TWITTER_ACCOUNT!='')";
         } else {
             $sql = "(FACEBOOK_ACCOUNT IS NOT NULL OR FACEBOOK_ACCOUNT!='')";
         }
         if (!empty($reg)) {
             $sql .= " AND UCS='{$reg}'";
         }
         if (!empty($w)) {
             $sql .= " AND WITEL='{$w}'";
         }
         if (!empty($p)) {
             $sql .= " AND PROD_TELKOM='{$p}'";
         }
         if (!empty($n)) {
             $sql .= " AND NON_PRODUK_TELKOM='{$n}'";
         }
         $count = $conn->createCommand("SELECT COUNT(*) FROM NEW_SME WHERE {$sql}")->queryScalar();
         $query = "SELECT * FROM NEW_SME WHERE {$sql}";
         $sort = ['attributes' => ['NAMA_PERUSAHAAN' => ['asc' => ['NAMA_PERUSAHAAN' => 'SORT_ASC'], 'desc' => ['NAMA_PERUSAHAAN' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Nama Perusahaan'], 'ALAMAT' => ['asc' => ['ALAMAT' => 'SORT_ASC'], 'desc' => ['ALAMAT' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Alamat'], 'KOTA' => ['asc' => ['KOTA' => 'SORT_ASC'], 'desc' => ['KOTA' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Kota']]];
     } elseif ($type == 'exist') {
         if ($m == 'twitter') {
             $sql = "(TWITTER_ACCOUNT IS NOT NULL OR TWITTER_ACCOUNT!='')";
         } else {
             $sql = "(FACEBOOK_ACCOUNT IS NOT NULL OR FACEBOOK_ACCOUNT!='')";
         }
         if (!empty($reg)) {
             $sql = "REGIONAL='{$reg}'";
         }
         if (!empty($w)) {
             $sql .= " AND WITEL='{$w}'";
         }
         if (!empty($p)) {
             $sql .= " AND P_TYPE='{$p}'";
         }
         if (!empty($n)) {
             $sql .= " AND TYPE_JARINGAN='{$n}'";
         }
         $count = $conn->createCommand("SELECT COUNT(*) FROM DATA_PROSPEK WHERE {$sql}")->queryScalar();
         $query = "SELECT * FROM DATA_PROSPEK WHERE {$sql}";
         $sort = ['attributes' => ['NIPNAS_GROUP' => ['asc' => ['NIPNAS_GROUP' => 'SORT_ASC'], 'desc' => ['NIPNAS_GROUP' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'NIPNAS Group'], 'CLIENT_NAME' => ['asc' => ['CLIENT_NAME' => 'SORT_ASC'], 'desc' => ['CLIENT_NAME' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Nama Client'], 'CITY' => ['asc' => ['CITY' => 'SORT_ASC'], 'desc' => ['CITY' => 'SORT_DESC'], 'default' => SORT_DESC, 'label' => 'Kota']]];
     } else {
         throw new NotFoundHttpException('The requested page does not exist.');
     }
     $dataProvider = new SqlDataProvider(['sql' => $query, 'totalCount' => (int) $count, 'sort' => $sort, 'pagination' => ['pageSize' => 20]]);
     $models = [];
     foreach ($dataProvider->getModels() as $mm) {
         $models[] = [$mm['ID'], $mm['TWITTER_ACCOUNT']];
     }
     Yii::$app->session->set('models', $models);
     if ($type == 'new') {
         $p = $_GET['p'] !== '' ? $_GET['p'] : 'All Produk';
         $n = $_GET['n'] !== '' ? $_GET['n'] : 'All Non-Produk';
     } elseif ($type == 'exist') {
         $p = $_GET['p'] !== '' ? $_GET['p'] : 'All Prospek';
         $n = $_GET['n'] !== '' ? $_GET['n'] : 'All Tipe Jaringan';
     }
     return $this->render('search', ['type' => $type, 'reg' => $_GET['reg'] !== '' ? $_GET['reg'] : 'All Regional', 'w' => $_GET['w'] !== '' ? $_GET['w'] : 'All Witel', 'p' => $p, 'n' => $n, 'm' => $_GET['m'], 'dataProvider' => $dataProvider, 'models' => $models, 'regionalsN' => $regionalsN, 'productsN' => $productsN, 'nonproductsN' => $nonproductsN, 'regionalsE' => $regionalsE, 'productsE' => $productsE, 'networksE' => $networksE]);
 }
Exemple #8
0
 public static function getAllTrainByUserId($userId = '', $isPage = true)
 {
     $count = Yii::$app->db->createCommand('SELECT (tu.id) as count FROM  ' . self::tableName() . ' tu LEFT JOIN ' . Train::tableName() . ' t ON tu.train_id = t.id WHERE tu.user_id=:user_id ', [':user_id' => $userId])->queryScalar();
     $dataProvider = new SqlDataProvider(['sql' => 'SELECT tu.id as id,tu.train_id,t.name,t.category,t.create_time,t.address,t.recruit_count,tu.status,t.begin_time,tu.user_id as train_user_id,t.period_num,t.train_land_id FROM  ' . TrainUsers::tableName() . ' tu LEFT JOIN ' . Train::tableName() . ' t ON tu.train_id = t.id WHERE tu.user_id=:user_id  ORDER BY id desc', 'params' => [':user_id' => $userId], 'totalCount' => $count, 'pagination' => ['pageSize' => $isPage ? '5' : '9999']]);
     $models = $dataProvider->getModels();
     return $models;
 }
Exemple #9
0
 /**
  * @param boolean $model Whether to return models (true, default) or arrays
  * @return ArrayDataProvider
  */
 private function generateProvider($model = true)
 {
     if ($model) {
         return new ArrayDataProvider(['allModels' => [new ModelRow(['x' => 0, 'y1' => 2, 'y2' => 10, 'y3' => -5]), new ModelRow(['x' => 1, 'y1' => 4, 'y2' => 6, 'y3' => -6]), new ModelRow(['x' => 2, 'y1' => 6, 'y2' => 2, 'y3' => -7])]]);
     } else {
         $mockProvider = $this->getMockBuilder(SqlDataProvider::className())->getMock();
         $mockProvider->method("getModels")->willReturn([['x' => 0, 'y1' => 2, 'y2' => 10, 'y3' => -5], ['x' => 1, 'y1' => 4, 'y2' => 6, 'y3' => -6], ['x' => 2, 'y1' => 6, 'y2' => 2, 'y3' => -7]]);
         return $mockProvider;
     }
 }
Exemple #10
0
 /**
  * Получение id специалистов, у который есть заявки на приём на заданный день $date_
  * @param date $date_ дата приёма
  * @return mixed
  */
 public function actionGetDayReportMain($date_)
 {
     $sql = 'SELECT `rd`.`specialist_id`, `oc`.`name` AS `oname`, `dr`.`name` AS `dname`, `rd`.`start_time`
         FROM `records` AS `rd`, `occupations` AS `oc`, `specialists` AS `sp`, `doctors` AS `dr`
         WHERE DATE(`rd`.`start_time`) = "' . $date_ . '"
         AND `rd`.`reserved` = "1"
         AND `rd`.`visited` = "0"
         AND `rd`.`specialist_id` = `sp`.`id` AND `sp`.`occupation_id` = `oc`.`id`
         AND `sp`.`doctor_id` = `dr`.`id`
         GROUP BY `specialist_id`
         ORDER BY `dname`';
     $provider = new SqlDataProvider(['sql' => $sql]);
     $models = $provider->getModels();
     return Json::encode($models);
 }