public function actionReport2()
{
$persons = Personal::find()->all();
$d = date('Y');
$data = [];
foreach ($persons as $p) {
$sql = "SELECT p.firstname,p.lastname,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=01 AND YEAR(m.date_start)='" . $d . "') AS m1,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=02 AND YEAR(m.date_start)='" . $d . "') AS m2,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=03 AND YEAR(m.date_start)='" . $d . "') AS m3,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=04 AND YEAR(m.date_start)='" . $d . "') AS m4,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=05 AND YEAR(m.date_start)='" . $d . "') AS m5,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=06 AND YEAR(m.date_start)='" . $d . "') AS m6,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=07 AND YEAR(m.date_start)='" . $d . "') AS m7,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=08 AND YEAR(m.date_start)='" . $d . "') AS m8,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=09 AND YEAR(m.date_start)='" . $d . "') AS m9,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=10 AND YEAR(m.date_start)='" . $d . "') AS m10,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=11 AND YEAR(m.date_start)='" . $d . "') AS m11,\n (SELECT COUNT(m.id) FROM mission m WHERE m.personal_user_id='" . $p->user_id . "' AND MONTH(m.date_start)=12 AND YEAR(m.date_start)='" . $d . "') AS m12\n FROM mission m\n LEFT JOIN personal p ON p.user_id = m.personal_user_id\n WHERE m.personal_user_id='" . $p->user_id . "'\n ";
$command = Yii::$app->db->createCommand($sql);
$rs = $command->queryOne();
$data[] = ['name' => $p['firstname'] . ' ' . $p['lastname'], 'data' => [intval($rs['m1']), intval($rs['m2']), intval($rs['m3']), intval($rs['m4']), intval($rs['m5']), intval($rs['m6']), intval($rs['m7']), intval($rs['m8']), intval($rs['m9']), intval($rs['m10']), intval($rs['m11']), intval($rs['m12'])]];
}
// end foreach personal
return $this->render('report2', ['data' => $data]);
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Personal::find();
$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;
}
$query->andFilterWhere(['user_id' => $this->user_id, 'department_id' => $this->department_id]);
$query->andFilterWhere(['like', 'firstname', $this->firstname])->andFilterWhere(['like', 'lastname', $this->lastname])->andFilterWhere(['like', 'address', $this->address])->andFilterWhere(['like', 'tel', $this->tel])->andFilterWhere(['like', 'color', $this->color]);
return $dataProvider;
}
use yii\helpers\ArrayHelper;
use backend\modules\personal\models\Personal;
use dosamigos\datetimepicker\DateTimePicker;
/* @var $this yii\web\View */
/* @var $model backend\modules\mission\models\Mission */
/* @var $form yii\widgets\ActiveForm */
?>
<div class="mission-form">
<?php
$form = ActiveForm::begin();
?>
<?php
echo $form->field($model, 'personal_user_id')->dropDownList(ArrayHelper::map(Personal::find()->all(), 'user_id', function ($model) {
return '[ ' . $model->department->department . ' ] ' . ' ' . $model->firstname . ' ' . $model->lastname;
}));
?>
<?php
echo $form->field($model, 'title')->textInput(['maxlength' => true]);
?>
<?php
echo $form->field($model, 'description')->textarea(['rows' => 6]);
?>
<?php
echo $form->field($model, 'date_start')->widget(DateTimePicker::className(), ['language' => 'th', 'template' => '{addon}{input}', 'clientOptions' => ['format' => 'yyyy-mm-dd HH:ii:00', 'startDate' => date("Y-m-d H:i:s")]]);
?>
public function getPersonal()
{
return $this->hasOne(Personal::className(), ['user_id' => 'personal_user_id']);
}
use backend\modules\personal\models\Personal;
?>
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>ลำดับ</th>
<th>ชื่อ</th>
<th>นามสกุล</th>
</tr>
</thead>
<tbody>
<?php
$i = 1;
foreach (Personal::find()->all() as $p) {
?>
<tr>
<td><?php
echo $i++;
?>
</td>
<td><?php
echo $p->firstname;
?>
</td>
<td><?php
echo $p->lastname;
?>
</td>
</tr>
?>
<div class="mission-index">
<h1><?php
echo Html::encode($this->title);
?>
</h1>
<?php
// echo $this->render('_search', ['model' => $searchModel]);
?>
<p>
<?php
echo Html::a('เพิ่มภารกิจ', ['create'], ['class' => 'btn btn-success']);
?>
</p>
<?php
echo GridView::widget(['dataProvider' => $dataProvider, 'filterModel' => $searchModel, 'columns' => [['class' => 'yii\\grid\\SerialColumn'], ['attribute' => 'personal_user_id', 'value' => function ($model) {
return $model->personal->firstname . ' ' . $model->personal->lastname;
}, 'filter' => Html::activeDropDownList($searchModel, 'personal_user_id', ArrayHelper::map(Personal::find()->all(), 'user_id', function ($model) {
return $model->firstname . ' ' . $model->lastname;
}), ['class' => 'form-control'])], ['format' => 'html', 'attribute' => 'title', 'value' => function ($model) {
return Html::a($model->title, ['mission/view', 'id' => $model->id]);
}], 'date_start', 'date_end', ['attribute' => 'user_id', 'value' => function ($user) {
return $user->personalUser->firstname . ' ' . $user->personalUser->lastname;
}], ['class' => 'yii\\grid\\ActionColumn']]]);
?>
</div>
/**
* Finds the Personal model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Personal the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Personal::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
echo Yii::$app->user->identity->username;
?>
</span>
</a>
<ul class="dropdown-menu">
<!-- User image -->
<li class="user-header">
<img src="<?php
echo $directoryAsset;
?>
/img/user2-160x160.jpg" class="img-circle"
alt="User Image"/>
<p>
<?php
echo Personal::findOne(Yii::$app->user->identity->id)->firstname . ' ' . Personal::findOne(Yii::$app->user->identity->id)->lastname;
?>
<small>สร้างเมื่อ <?php
echo date('Y-m-d', User::findOne(Yii::$app->user->identity->id)->created_at);
?>
</small>
</p>
</li>
<!-- Menu Footer-->
<li class="user-footer">
<div class="pull-left">
<?php
$id = Yii::$app->user->identity->id;
$route = Url::to(['/personal/personal/view', 'id' => $id]);
?>
<a href="<?php
