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.
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;
}
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;
}
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>";
}
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;
}
/**
* 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.');
}
}
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]);
}
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]);
}
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;
}
/**
* @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;
}
}
/**
* Получение 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);
}