public function search($params)
{
$query = Member::find()->indexBy('id');
$query->poll_id($this->poll_id);
$query->distinct();
// also possible is to group by member.id to get only the unique member entries.
//$query->addGroupBy($this->tableName().'.id');
// $query->with('codes.vote'); // old join
//$query->joinWith('codes', false, 'LEFT JOIN');
//$query->joinWith('codes.vote', true, 'LEFT JOIN');
// $query->joinWith(['relationname' => function ($query) {
// $query->from(['alias' => 'tablename']);
// }]);
// $query->joinWith(['codes' => function ($query) {
// $query->from(['codes' => 'code']);
// }]);
$query->joinWith(['codes.vote']);
$dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['name' => SORT_ASC]], 'pagination' => ['pageSize' => 20]]);
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
$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]);
$query->andFilterWhere(['like', 'name', $this->name])->andFilterWhere(['like', 'group', $this->group]);
//if code CODE_STATUS_INVALID_UNUSED is searched also search for CODE_STATUS_INVALID_USED
if ($this->getAttribute('codes.code_status') == Code::CODE_STATUS_INVALID_UNUSED) {
$query->andWhere([Code::tableName() . '.code_status' => [Code::CODE_STATUS_INVALID_UNUSED, Code::CODE_STATUS_INVALID_USED]]);
} else {
$query->andFilterWhere(['=', Code::tableName() . '.code_status', $this->getAttribute('codes.code_status')]);
}
return $dataProvider;
}
public function search()
{
$count = Yii::$app->db->createCommand("SELECT\n\t\t\t\tCOUNT(DISTINCT (z_p_id))\n\t\t\t\tfrom " . Code::tableName() . "\n\t\t\t\twhere LOWER(z_b_id) = :bank\n\t\t\t\t", [':bank' => strtolower($this->z_b_id)])->queryScalar();
$dataProvider = new SqlDataProvider(['sql' => "select\n\t\t\t\tbezeichnung,\n\t\t\t\tcount(z_b_id) as tic_count,\n\t\t\t\tSUM(if(used = 1, 1, 0)) AS bused,\n\t\t\t\tSUM(if(status > 49 and used=0, 1, 0)) AS bused50,\n\t\t\t\tSUM(if(status > 49, 1, 0)) AS busedtot\n\t\t\t\tfrom " . Code::tableName() . ", " . Group::tableName() . "\n\t\t\t\twhere LOWER(z_b_id) = :bank and z_p_id = p_id\n\t\t\t\tgroup by z_p_id", "params" => [':bank' => strtolower($this->z_b_id)], 'totalCount' => $count]);
return $dataProvider;
}
