Yii2-使用SqlDataProvider和GridView并启用Gridview筛选器进行搜索 [英] Yii2-Use SqlDataProvider with GridView and enable Gridview Filter for search
本文介绍了Yii2-使用SqlDataProvider和GridView并启用Gridview筛选器进行搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在研究 yii2 。我有一个自定义视图，在其中放置了 DetailView 和 GridView 的视图。下面是我的控制器代码
I am working on yii2. I have a custom view in which I have placed to views DetailView and GridView. Below is my controller code
public function actionViewcreated($id)// passed the id of my model which is created in the previous step
{
$model=$this->findModel($id); // this will find my model/record based on the id
$sub_div = $model->sub_div;
$meter_type = $model->meter_type;
$query = "SELECT DISTINCT m.`id` AS meter_id, ins.`meter_msn` AS Meter_Serial_Number, ins.`meter_type` AS Meter_Type, sd.`sub_div_code` AS Sub_Division_Code,sd.`name` AS Sub_Division_Name
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` = map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = sd.`sub_div_code`
WHERE ins.`meter_type` = '$meter_type'
AND sd.`sub_div_code` = '$sub_div'
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM
`meter_acceptance_details` md WHERE md.`flag` IN (1))";
$session = Yii::$app->session;
$session->set('my_sql', $query);
$count = Yii::$app->db->createCommand("SELECT COUNT(DISTINCT m.`id`)
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` = map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = sd.`sub_div_code`
WHERE ins.`meter_type` = '$meter_type'
AND sd.`sub_div_code` = '$sub_div'
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM `meter_acceptance_details` md WHERE md.`flag` IN (1))")->queryScalar();
$session = Yii::$app->session;
$session->set('total', $count);
if($count <= 0)
{
$this->findModel($id)->delete();
\Yii::$app->getSession()->setFlash('errors', '
<div class="alert alert-error alert-dismissable">
<button aria-hidden="true" data-dismiss="alert" class="close" type="button">×</button>
<strong>There are no meters installed against the selected Sub Division!!!! </strong>Acceptance is not Created</div>');
return $this->redirect(['index', 'id' => $model->id]);
}
else
{
$dataProvider = new SqlDataProvider([
'sql' => $query,
'totalCount' => $count,
'pagination' => [
'pageSize' => 40,
],
]);
return $this->render('viewcreated', [
'dataProvider' => $dataProvider,
'model' => $model,
'id' => $model->id
/*'searchModel' => $searchModel*/
]);
}
}
查看
<?php Pjax::begin(); ?>
<?= DetailView::widget([
'model' => $model,
'attributes' => [
[
'label'=>'Serial #',
'value' => function($d)
{
return $d->id;
}
],
[
'label' => 'Meter Type',
'value' => function ($d) {
if(is_object($d))
return $d->meter_type;
return ' - ';
},
],
'sub_div',
[
'label' => 'Sub Division Name',
'value' => function ($d) {
if(is_object($d))
return $d->subDiv->name;
return '-';
},
],
[
'label' => 'Prepared By',
'value' => function ($d) {
if(is_object($d))
return $d->prepared->name;
},
],
'prepared_at',
'status',
],
]) ?>
<br>
<?= GridView::widget([
'dataProvider' => $dataProvider,
/*'filterModel' => $searchModel,*/
'id'=>'gv',
'columns' => [
['class' => 'yii\grid\SerialColumn'],
['class' => 'yii\grid\CheckboxColumn', 'checkboxOptions' => function($d) {
return ['value' => $d['meter_id']];
}],
'Meter_Serial_Number',
'Meter_Type',
'Sub_Division_Code',
'Sub_Division_Name',
],
]); ?>
<?php Pjax::end(); ?>
JS
$("#chk input:checkbox").prop("checked", true);
输出
在上面的网格视图中，在仪表序列号中添加搜索列，在子部门名称中添加下拉列表。
In the above grid view I want to add search column for Meter Serial Number and dropdown for Sub Division Name.
更新1
安装模型
class Installations extends \yii\db\ActiveRecord
{
/**
* @inheritdoc
*/
public static function tableName()
{
return 'installations';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['created_at', 'updated_at','imsi_no','old_meter_power','old_meter_reactive_power','new_meter_power','new_meter_reactive_power','ct_ratio','ct_ratio_quantity','cable_length','cable_type','atb_installed'], 'safe'],
[['created_by', 'updated_by', 'status'], 'integer'],
[['ref_no', 'meter_msn', 'billing_msn', 'customer_id','ct_ratio_quantity','cable_length','meter_type'], 'string', 'max' => 50],
[['tarrif', 's_load', 'ct_ratio','cable_type'], 'string', 'max' => 100],
[['latitude', 'longitude'], 'string', 'max' => 11],
[['site_issues', 'istallation_status', 'comm_status'], 'string', 'max' => 200],
[['consumer_name', 'consumer_address'], 'string', 'max' => 255],
[['so_person_name'], 'string', 'max' => 40],
[['so_phone_number', 'so_person_designation','atb_installed'], 'string', 'max' => 20],
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'id' => 'ID',
'ref_no' => 'Ref No',
'meter_msn' => 'Meter MSN',
'billing_msn' => 'Billing MSN',
'tarrif' => 'Tarrif',
'created_at' => 'Created At',
'updated_at' => 'Updated At',
'created_by' => 'Created By',
'updated_by' => 'Updated By',
'status' => 'Status',
'latitude' => 'Latitude',
'longitude' => 'Longitude',
'site_issues' => 'Site Issues',
'istallation_status' => 'Installation Status',
'comm_status' => 'Comm Status',
'customer_id' => 'Customer ID',
'consumer_name' => 'Consumer Name',
'consumer_address' => 'Consumer Address',
's_load' => 'Sanctioned Load',
'so_person_name' => 'Person Name',
'so_phone_number' => 'Phone Number',
'so_person_designation' => 'Person Designation',
'imsi_no' => 'IMSI #',
'old_meter_power' => 'Old Meter Power (kWh)',
'old_meter_reactive_power' => 'Old Meter Reactive Power (kVArh)',
'new_meter_power' => 'New Meter Power (kWh)',
'new_meter_reactive_power'=>' New Meter Reactive Power (kVArh)',
'ct_ratio'=>'CT Ratio',
'ct_ratio_quantity'=>'CT Ratio Quantity',
'cable_length'=>'Cable Length',
'cable_type'=>'Cable Type',
'atb_installed'=>'ATB Installed',
'meter_type'=>'Meter Type',
];
}
public static function getCommStatus()
{
return [
//'' => 'Please Select',
'Verified' => 'Verified',
'Failed' => 'Failed',
'System Verified' => 'System Verified'
//'Return Communication Failed SIM' => 'Return Communication Failed SIM'
];
}
public static function getInstallStatus()
{
return [
//'' => 'Please Select',
'Installed' => 'Installed',
'Not Installed' => 'Not Installed',
'System Installed'=>'System Installed'
//'Return Communication Failed SIM' => 'Return Communication Failed SIM'
];
}
/**
* @return \yii\db\ActiveQuery
*/
public function getInstallationImages()
{
return $this->hasMany(InstallationImages::className(), ['installation_id' => 'id']);
}
public function getImages(){
return InstallationImages::find()->where(['installation_id' => $this->id])->all();
}
public function getSiteImages(){
return InstallationImagesSite::find()->where(['installation_id' => $this->id])->all();
}
public function getUser(){
return User::find()->where(['id'=>$this->created_by]);
}
public function getTeamsuser()
{
return $this->hasMany(User::className(), ['id' => 'created_by']);
}
}
安装搜索模型
class InstallationsSearch extends Installations
{
/**
* @inheritdoc
*/
public function rules()
{
return [
[['id', 'created_by', 'updated_by', 'status'], 'integer'],
[['ref_no','meter_type','install_type', 'meter_msn', 'billing_msn',
'tarrif', 'created_at','created_by', 'updated_at', 'latitude',
'longitude', 'site_issues', 'istallation_status', 'comm_status',
'customer_id', 'consumer_name', 'consumer_address', 's_load',
'so_person_name', 'so_phone_number', 'so_person_designation','imsi_no',
'meter_id' , 'Meter_Serial_Number' ,'Meter_Type','Sub_Division_Code','Sub_Division_Name'], 'safe'],
];
}
/**
* @inheritdoc
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Installations::find();
// add conditions that should always apply here
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
// grid filtering conditions
$query->andFilterWhere([
'id' => $this->id,
'created_at' => $this->created_at,
'updated_at' => $this->updated_at,
'created_by' => $this->created_by,
'updated_by' => $this->updated_by,
'meter_type' => $this->meter_type,
'install_type'=>$this->install_type,
'istallation_status'=>$this->istallation_status,
'status' => $this->status,
]);
$query->andFilterWhere(['like', 'ref_no', $this->ref_no])
->andFilterWhere(['like', 'meter_msn', $this->meter_msn])
->andFilterWhere(['like', 'billing_msn', $this->billing_msn])
->andFilterWhere(['like', 'tarrif', $this->tarrif])
->andFilterWhere(['like', 'latitude', $this->latitude])
->andFilterWhere(['like', 'longitude', $this->longitude])
->andFilterWhere(['like', 'site_issues', $this->site_issues])
->andFilterWhere(['like', 'istallation_status', $this->istallation_status])
->andFilterWhere(['like', 'comm_status', $this->comm_status])
->andFilterWhere(['like', 'customer_id', $this->customer_id])
->andFilterWhere(['like', 'consumer_name', $this->consumer_name])
->andFilterWhere(['like', 'consumer_address', $this->consumer_address])
->andFilterWhere(['like', 's_load', $this->s_load])
->andFilterWhere(['like', 'so_person_name', $this->so_person_name])
->andFilterWhere(['like', 'so_phone_number', $this->so_phone_number])
->andFilterWhere(['like','meter_type',$this->meter_type])
->andFilterWhere(['like','created_by',$this->created_by])
//->andFilterWhere(['like', 'imsi_no', $this->imsi_no])
->andFilterWhere(['like', 'so_person_designation', $this->so_person_designation]);
if (!Yii::$app->user->isGuest && in_array(Yii::$app->user->identity->user_role, [3,4])) {
$query->joinWith('teamsuser', true);
$query->andFilterWhere(['=', 'user.group_id', Yii::$app->user->identity->group_id]);
}
$query->orderBy(['id' => SORT_DESC]);
return $dataProvider;
}
}
如何在网格视图中添加这两个选项？
How can I add these two options in the grid view?
任何帮助将不胜感激。
Any help would be highly appreciated.
更新2
根据给出的答案，我已经更新了代码
As per answer given I have updated my code
InstallationSearch
class InstallationsSearch extends Installations
{
public $meter_id;
public $Meter_Serial_Number;
public $Meter_Type;
public $Sub_Division_Code;
public $Sub_Division_Name;
/**
* @inheritdoc
*/
public function rules()
{
return [
[['id', 'created_by', 'updated_by', 'status'], 'integer'],
[['ref_no','meter_type','install_type', 'meter_msn', 'billing_msn', 'tarrif', 'created_at','created_by', 'updated_at', 'latitude', 'longitude', 'site_issues', 'istallation_status', 'comm_status', 'customer_id', 'consumer_name', 'consumer_address', 's_load', 'so_person_name', 'so_phone_number', 'so_person_designation','imsi_no','meter_id' , 'Meter_Serial_Number' ,'Meter_Type','Sub_Division_Code','Sub_Division_Name'], 'safe'],
];
}
/**
* @inheritdoc
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Installations::find();
// add conditions that should always apply here
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
// grid filtering conditions
$query->andFilterWhere([
'id' => $this->id,
'created_at' => $this->created_at,
'updated_at' => $this->updated_at,
'created_by' => $this->created_by,
'updated_by' => $this->updated_by,
'meter_type' => $this->meter_type,
'install_type'=>$this->install_type,
'istallation_status'=>$this->istallation_status,
'status' => $this->status,
]);
$query->andFilterWhere(['like', 'ref_no', $this->ref_no])
->andFilterWhere(['like', 'meter_msn', $this->meter_msn])
->andFilterWhere(['like', 'billing_msn', $this->billing_msn])
->andFilterWhere(['like', 'tarrif', $this->tarrif])
->andFilterWhere(['like', 'latitude', $this->latitude])
->andFilterWhere(['like', 'longitude', $this->longitude])
->andFilterWhere(['like', 'site_issues', $this->site_issues])
->andFilterWhere(['like', 'istallation_status', $this->istallation_status])
->andFilterWhere(['like', 'comm_status', $this->comm_status])
->andFilterWhere(['like', 'customer_id', $this->customer_id])
->andFilterWhere(['like', 'consumer_name', $this->consumer_name])
->andFilterWhere(['like', 'consumer_address', $this->consumer_address])
->andFilterWhere(['like', 's_load', $this->s_load])
->andFilterWhere(['like', 'so_person_name', $this->so_person_name])
->andFilterWhere(['like', 'so_phone_number', $this->so_phone_number])
->andFilterWhere(['like','meter_type',$this->meter_type])
->andFilterWhere(['like','created_by',$this->created_by])
//->andFilterWhere(['like', 'imsi_no', $this->imsi_no])
->andFilterWhere(['like', 'so_person_designation', $this->so_person_designation]);
if (!Yii::$app->user->isGuest && in_array(Yii::$app->user->identity->user_role, [3,4])) {
$query->joinWith('teamsuser', true);
$query->andFilterWhere(['=', 'user.group_id', Yii::$app->user->identity->group_id]);
}
$query->orderBy(['id' => SORT_DESC]);
return $dataProvider;
}
/**
* @param $params
* @return SqlDataProvider
* @throws \yii\db\Exception
*/
public function searchInstallations($params )
{
$query = /** @lang text */
"SELECT DISTINCT
m.`id` AS meter_id,
ins.`meter_msn` AS Meter_Serial_Number,
ins.`meter_type` AS Meter_Type,
sd.`sub_div_code` AS Sub_Division_Code,
sd.`name` AS Sub_Division_Name
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` =
map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` =
sd.`sub_div_code`
WHERE ins.`meter_type` = :meter_type
AND sd.`sub_div_code` = :sub_div
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM
`meter_acceptance_details` md WHERE md.`flag` IN (1))";
$queryParams = [
':meter_type' => $params['meter_type'] ,
':sub_div' => $params['sub_div']
];
$queryCount = /** @lang text */
"SELECT COUNT(DISTINCT m.`id`)
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` = map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = sd.`sub_div_code`
WHERE ins.`meter_type` = :meter_type
AND sd.`sub_div_code` = :sub_div
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM
`meter_acceptance_details` md WHERE md.`flag` IN (1))";
$dataProvider = new SqlDataProvider ( [
'sql' => $query ,
'pagination' => [
'pageSize' => 60 ,
] ,
] );
if ( !($this->load ( $params ) && $this->validate ()) ) {
return $dataProvider;
}
if($this->Meter_Serial_Number!==''){
$dataProvider->sql .= ' AND ins.meter_msn like :meter_serial_number';
$queryParams[':meter_serial_number'] = $this->Meter_Serial_Number . '%';
$queryCount .= ' AND ins.meter_msn like :meter_serial_number';
}
if($this->Sub_Division_Code!==''){
$dataProvider->sql .= ' AND sd.sub_div_code like :sub_div_code';
$queryParams[':sub_div_code'] = $this->Sub_Division_Code . '%';
$queryCount .= ' AND sd.sub_div_code like :sub_div_code';
}
$count = Yii::$app->db->createCommand ( $queryCount , $queryParams )->queryScalar ();
$dataProvider->totalCount = $count;
$dataProvider->params = $queryParams;
//add to session
$session = Yii::$app->session;
$session->set ( 'my_sql' , Yii::$app->db->createCommand ( $query , $queryParams )->rawSql );
$session->set ( 'total' , $count );
//return data provider
return $dataProvider;
}
}
控制代码
public function actionViewcreated( $id ) {// passed the id of my model which is created in the previous step
$model = $this->findModel ( $id ); // this will find my model/record based on the id
$sub_div = $model->sub_div;
$meter_type = $model->meter_type;
$queryParams = Yii::$app->request->queryParams;
$queryParams['sub_div'] = $sub_div;
$queryParams['meter_type'] = $meter_type;
$searchModel = new InstallationsSearch();
$dataProvider = $searchModel->searchInstallations( $queryParams );
if ( Yii::$app->session->get ( 'total' ) <= 0 ) {
$this->findModel ( $id )->delete ();
\Yii::$app->getSession ()->setFlash ( 'errors' , '
<div class="alert alert-error alert-dismissable">
<button aria-hidden="true" data-dismiss="alert" class="close"
type="button">×</button>
<strong>There are no meters installed against the selected Sub Division!!!! </strong>Acceptance is not Created</div>' );
return $this->redirect ( [ 'index' , 'id' => $model->id ] );
}
return $this->render ( 'viewcreated' , [
'dataProvider' => $dataProvider ,
'model' => $model ,
'id' => $model->id ,
'searchModel' => $searchModel
] );
}
查看
<?= GridView::widget([
'dataProvider' => $dataProvider,
//'ajaxUpdate' => true,
//'filterModel' => $searchModel,
'id'=>'gv',
'columns' => [
['class' => 'yii\grid\SerialColumn'],
['class' => 'yii\grid\CheckboxColumn', 'checkboxOptions' => function($d) {
return ['value' => $d['meter_id']];
}],
[
'attribute'=>'Meter_Serial_Number',
'filter'=> \yii\helpers\Html::activeTextInput($searchModel, 'Meter_Serial_Number',['class'=>'form-control']),
],
'Meter_Type',
'Sub_Division_Code',
[
'attribute' => 'Sub_Division_Name',
'filter'=> \yii\helpers\Html::activeTextInput($searchModel,'Sub_Division_Name', ['class'=>'form-control']),
],
],
]); ?>
更新3
更新代码后，我在仪表序列和子部门
但是当我尝试搜索序列号时，页面出现了刷新并再次看到完整视图
But when I try to search a serial number then the page got refreshed and again I see the full view
通过 echo $ dataProvider-> sql; 我得到
选择区域m。 id AS meter_id，ins。 meter_msn AS Meter_Serial_Number， meter_type AS Meter_Type，sd。 sub_div_code AS Sub_Division_Code，sd。 name AS Sub_Division_Name来自安装 ins内联接电表 m in ins。 meter_msn = m。 meter_msn INNER JOIN meter_acceptance_header map in ins 。 meter_type =地图。 meter_type 内部联接调查 sur ON实例。 ref_no = sur。 ref_no INNER JOIN survey_hesco_subdivision sd on sur。 = sd c code> sub_div_code =：sub_div和地图。 id 不输入（选择不显示md。 meter_id FROM meter_acceptance_details md WHERE md。 flag IN（1））和ins.meter_msn，如：meter_serial_number AND sd .sub_div_code像：sub_div_code
SELECT DISTINCT m.idAS meter_id, ins.meter_msnAS Meter_Serial_Number, ins.meter_typeAS Meter_Type, sd.sub_div_codeAS Sub_Division_Code, sd.nameAS Sub_Division_Name FROMinstallationsins INNER JOINmetersm ON ins.meter_msn= m.meter_msnINNER JOINmeter_acceptance_headermap ON ins.meter_type= map.meter_typeINNER JOINsurveysur ON ins.ref_no= sur.ref_noINNER JOINsurvey_hesco_subdivisionsd ON sur.sub_division= sd.sub_div_codeWHERE ins.meter_type= :meter_type AND sd.sub_div_code= :sub_div AND map.idNOT IN (SELECT DISTINCT md.meter_idFROMmeter_acceptance_detailsmd WHERE md.flagIN (1)) AND ins.meter_msn like :meter_serial_number AND sd.sub_div_code like :sub_div_code
无论我是否搜索任何序列号，上述查询都保持不变。
The above query remains the same whether I have searched for any serial number or not.
注意：我已经知道如何通过在搜索模型的计算列中添加过滤器字段来添加搜索选项。但是由于我有一个自定义视图，所以我很困惑该怎么办。
Note: I already know how to add a search option by adding a filter field in a calculated column in my search model. But as I have a custom view so I am confused that how can I do it.
推荐答案
我还不太习惯尽管有了GridView，如果有人建议的话，可能会有比这更好的解决方案，但这可以按照您想要的方式工作。
I have'nt used it quite though with the GridView and there could be better solutions than this one if anyone could suggest but this can work the way you want.
您应该更新您的 InstallationSearch 模型可将过滤器与 GridView 一起使用。如果未使用默认的 InstallationSearch 模型的 search（）函数，然后您就可以更新现有的搜索方法或创建单独的方法。我假设您没有在其他任何地方使用它，我将添加一个搜索功能，该功能将使用 SqlDataProvider 来显示和过滤 GridView 。
You should update your InstallationSearch model to use the filter with the GridView.If you are not using the default InstallationSearch model's search() function anywhere then you can update the existing search method or else create a separate method. I assume you are not using it anywhere else and I will add a search function that will use the SqlDataProvider for displaying and filtering the GridView.
要实现搜索，您需要确定要为列使用的别名数量，必须将所有别名声明为搜索模型的公共属性
To implement the search you need to identify how many aliases are you using for the columns you have to declare all of them as public attributes of your search model
public $meter_id;
public $Meter_Serial_Number;
public $Meter_Type;
public $Sub_Division_Code;
public $Sub_Division_Name;
然后将它们添加到安全规则中
then add them to the safe rule
public function rules() {
return [
[ [ 'meter_id' , 'Meter_Serial_Number' ,'Meter_Type','Sub_Division_Code','Sub_Division_Name'] , 'safe' ] ,
];
}
现在添加 search（）函数
public function searchInstallations($params)
{
$query = "SELECT DISTINCT
m.`id` AS meter_id,
ins.`meter_msn` AS Meter_Serial_Number,
ins.`meter_type` AS Meter_Type,
sd.`sub_div_code` AS Sub_Division_Code,
sd.`name` AS Sub_Division_Name
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` = map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = sd.`sub_div_code`
WHERE ins.`meter_type` = :meter_type
AND sd.`sub_div_code` = :sub_div
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM
`meter_acceptance_details` md WHERE md.`flag` IN (1))";
$queryParams = [
':meter_type' => $params['meter_type'],
':sub_div' => $params['sub_div']
];
$queryCount = "SELECT COUNT(DISTINCT m.`id`)
FROM `installations` ins
INNER JOIN `meters` m ON ins.`meter_msn` = m.`meter_msn`
INNER JOIN `meter_acceptance_header` map ON ins.`meter_type` = map.`meter_type`
INNER JOIN `survey` sur ON ins.`ref_no` = sur.`ref_no`
INNER JOIN `survey_hesco_subdivision` sd ON sur.`sub_division` = sd.`sub_div_code`
WHERE ins.`meter_type` = :meter_type
AND sd.`sub_div_code` = :sub_div
AND map.`id` NOT IN (SELECT DISTINCT md.`meter_id` FROM `meter_acceptance_details` md WHERE md.`flag` IN (1))";
$dataProvider = new SqlDataProvider(
[
'sql' => $query,
'pagination' => [
'pageSize' => 40
]
]
);
if (!($this->load($params) && $this->validate())) {
$dataProvider->params = $queryParams;
$count = Yii::$app->db->createCommand($queryCount, $queryParams)->queryScalar();
$dataProvider->totalCount = $count;
return $dataProvider;
}
if ($this->Meter_Serial_Number !== '') {
$dataProvider->sql .= ' AND ins.meter_msn like :meter_serial_number';
$queryParams[':meter_serial_number'] = $this->Meter_Serial_Number . '%';
$queryCount .= ' AND ins.meter_msn like :meter_serial_number';
}
if ($this->Sub_Division_Code !== '') {
$dataProvider->sql .= ' AND sd.sub_div_code like :sub_div_code';
$queryParams[':sub_div_code'] = $this->Sub_Division_Code . '%';
$queryCount .= ' AND sd.sub_div_code like :sub_div_code';
}
$count = Yii::$app->db->createCommand($queryCount, $queryParams)->queryScalar();
$dataProvider->totalCount = $count;
$dataProvider->params = $queryParams;
//add to session
$session = Yii::$app->session;
$session->set('my_sql', Yii::$app->db->createCommand($query, $queryParams)->rawSql);
$session->set('total', $count);
//return data provider
return $dataProvider;
}
Update your actionViewcreated to the following
Update your actionViewcreated to the following
public function actionViewcreated($id)
{
// passed the id of my model which is created in the previous step
$model = $this->findModel($id); // this will find my model/record based on the id
$sub_div = $model->sub_div;
$meter_type = $model->meter_type;
$queryParams = Yii::$app->request->queryParams;
$queryParams['sub_div'] = $sub_div;
$queryParams['meter_type'] = $meter_type;
$searchModel = new InstallationSearch();
$dataProvider = $searchModel->searchInstallation($queryParams);
if (Yii::$app->session->get('total') <= 0) {
$this->findModel($id)->delete();
\Yii::$app->getSession()->setFlash(
'errors',
'<div class="alert alert-error alert-dismissable"><button aria-hidden="true" data-dismiss="alert" class="close" type="button">×</button>
<strong>There are no meters installed against the selected Sub Division!!!! </strong>Acceptance is not Created</div>'
);
return $this->redirect(['index', 'id' => $model->id]);
}
return $this->render(
'viewcreated', [
'dataProvider' => $dataProvider,
'model' => $model,
'id' => $model->id,
'searchModel' => $searchModel
]
);
}
and enable the filter for the grid view like below
and enable the filter for the grid view like below
[
'attribute'=>'Meter_Serial_Number',
'filter'=> \yii\helpers\Html::activeTextInput($searchModel, 'Meter_Serial_Number',['class'=>'form-control']),
],
[
'attribute' => 'Sub_Division_Name',
'filter'=> \yii\helpers\Html::activeTextInput($searchModel,'Sub_Division_Name', ['class'=>'form-control']),
],
EDIT
For making your dropdown work either change the function toArrayList() to the following if it is not used anywhere else or create a separate function as you need to have the sub_div_name as the value to be searched in the table so change the sub_div_code to the field which has the sub_div_name i assume it is the name field in the SurveyHescoSubdivision .
EDIT
For making your dropdown work either change the function toArrayList() to the following if it is not used anywhere else or create a separate function as you need to have the sub_div_name as the value to be searched in the table so change the sub_div_code to the field which has the sub_div_name i assume it is the name field in the SurveyHescoSubdivision .
/**
* @return mixed
*/
public static function toArrayList()
{
return ArrayHelper::map(
self::find()->all(), 'name', function ($model, $defaultValue) {
return $model['sub_div_code'] . ' - ' . $model['name'];
}
);
}
这篇关于Yii2-使用SqlDataProvider和GridView并启用Gridview筛选器进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
