public function searchUser($params, $user_id) { $query = UserDate::find(); $query->select('user_info.*, year, entitlement, entitlement - (SELECT SUM(hours_off) AS hours FROM `application` WHERE user_id = ' . $user_id . ' AND manager_ok = 1 AND hrm_ok = 1 AND reason = 13 AND YEAR(from_date) = user_date.`year`) AS balance'); $query->joinWith(['user']); $query->where(['user_date.user_id' => $user_id]); $dataProvider = new ActiveDataProvider(['query' => $query]); $this->load($params); if (!$this->validate()) { // uncomment the following line if you do not want to any records when validation fails // $query->where('0=1'); return $dataProvider; } $query->andFilterWhere(['id' => $this->id, 'user_id' => $this->user_id, 'year' => $this->year, 'entitlement' => $this->entitlement, 'balance' => $this->balance]); return $dataProvider; }
public function actionListDateOff() { $idStaff = Yii::$app->request->post('idOfStaff'); $query = UserDate::find(); $query = UserDate::find(); $query->select('user_info.*,id, year, entitlement, entitlement - (SELECT SUM(hours_off) AS hours FROM `application` WHERE manager_ok = 1 AND hrm_ok = 1 AND application.user_id = user_info.user_id AND reason = 13 AND YEAR(from_date) = user_date.`year`) AS balance'); $query->joinWith(['user']); $query->where(['user_date.user_id' => $idStaff]); $dataProvider = new ActiveDataProvider(['query' => $query]); $user = UserInfo::findOne(['user_id' => $idStaff]); $dataProvider = new ActiveDataProvider(['query' => $query]); return $this->renderAjax('list-date-off', ['dataProvider' => $dataProvider, 'user' => $user]); }