How to find the records of previous months in Yii2


(Jamess) #1

Hi,

I am using Yii2 basic. I have a module called groupsavingdetails where each month the user records the Group Saving. I have a form where user selects the Group, there is input box for entering year and dropdown to select month.

For eg: For Group 1 the date for formation is 2017-08-08. Here user should enter year greater than 2017 and select month greater than August. For Group 1 the year is 2017 and month is August, the user enters all other details. The controller checks whether there exists group saving records which are less than entered year and selected month. If not exists then four fields will be set to 0, otherwise previous months data will be considered for calculation.

How should I get the previous months record.

I have a below SQL which returns the previous months data but when the year is changed i.e 2018 and the first month is January then how should I retrieve 2017 December data.

$group = Yii::$app->db->createCommand('SELECT *
 FROM groupsavingdetails
 where groupsavingdetails.GroupId=:id
	 and groupsavingdetails.Year<=:year
 and groupsavingdetails.Month=:month-1'
	 )->bindValues([':id'=>$model->GroupId,':year'=>$model->Year,':month'=>$model->Month])
	 
 ->queryAll();

In the form I have the below code:

<?= $form->field($model, 'Month',['enableAjaxValidation' => true])->dropDownList(['01'=>'January', '02' => 'February','03'=>'March','04'=>'April','05'=>'May','06'=>'June','07'=>'July','08'=>'August','09'=>'September','10'=>'October','11'=>'November','12'=>'December'], ['prompt'=>'Select Month'])?>

(Bizley) #2

Just prepare $year and $month variables before the query and use them in it.


(Jamess) #3

Yes, I have passed the year and month values. But when the year is 2018 and month is January how can I get the records of year 2017 and month December.?


(Bizley) #4

I’m confused by what might be confusing with it but here you go…

$year = $model->Year;
$month = $model->Month;
$previousMonth = $month - 1;
if ($previousMonth === 0) {
    $previousMonth = 12;
    $year--;
}
$group = Yii::$app->db->createCommand('SELECT *
    FROM groupsavingdetails
    where groupsavingdetails.GroupId=:id
    and groupsavingdetails.Year<=:year
    and groupsavingdetails.Month=:month')
->bindValues([
    ':id' => $model->GroupId,
    ':year' => $year,
    ':month' => $previousMonth,
])->queryAll();

(Jamess) #5

Thanks. It is working fine