I have a table called groupsavingdetails which saves the monthly saving for each month of groups. The table contains fields as GroupSavingDetailsId, GroupId, Year,Month, OpeningBalance, TotalSaving, LoanRecovery, LoanInterest, Fine, BankInterest, BankLoan, Expenses, LoanGiven, TotalValusOfLoanGiven, LoanRepaidUptilNow, TotalValueOfLoanOutstanding, CashInHand,CashInBank and ClosingBalance.
The ClosingBalance of 1st month will be the OpeningBalance for 2nd month and so on.
If I have records for month August, September, October and November and for year 2017. Now
I have a situation where updating a record for September involves updating all the records greater than September in the table based on the changes made to September record. The changes made to September should get updated to October record. October to November and so on.
Meaning if I change values in September record, then the closing balance saved for September should be the opening balance for October record through query and October closing balance will also change. Then this October opening balance and closing balance should be used for November and so on. So how the query will be?
```
public function actionUpdate($id)
{
$model = $this->findModel($id);
if (Yii::$app->request->isAjax && $model->load(Yii::$app->request->post())) {
Yii::$app->response->format = Response::FORMAT_JSON;
return ActiveForm::validate($model);
}
if ($model->load(Yii::$app->request->post())) {
$count = Yii::$app->db->createCommand('SELECT count(*)
FROM groupsavingdetails
where groupsavingdetails.GroupSavingDetailsId>:id and groupsavingdetails.GroupId=:Id and Month>:month
')
->bindValues([
':id' => $model->GroupSavingDetailsId,':Id'=>$model->GroupId,':month'=>$model->Month
])
->queryAll();
if($count==0)
{
}
if($count==1)
{
}
if($count>1)
{
if($model->LoanGiven!=0)
{
$model->TotalValueofLoanGiven+=$model->LoanGiven;
}
if($model->LoanRecovery!=0)
{
$model->LoanRepaidUptilNow+=$model->LoanRecovery;
}
$model->TotalValueOfLoanOutstanding=$model->TotalValueofLoanGiven-$model->LoanRepaidUptilNow;
$model->ClosingBalance=($model->OpeningBalance+$model->TotalSaving+$model->LoanRecovery+$model->LoanInterest+$model->Fine+$model->BankInterest+$model->BankLoan-$model->Expenses-$model->LoanGiven);
$groups = Yii::$app->db->createCommand('SELECT *
FROM groupsavingdetails
where groupsavingdetails.GroupSavingDetailsId>:id and groupsavingdetails.GroupId=:Id and Month>:month
')
->bindValues([
':id' => $model->GroupSavingDetailsId,':Id'=>$model->GroupId,':month'=>$model->Month,
])
->queryAll();
$result =array();
foreach($groups as $key=> $result)
{
}
//$model->save();
//return $this->redirect(['view', 'id' => $model->GroupSavingDetailsId]);
}
//return $this->redirect(['view', 'id' => $model->GroupSavingDetailsId]);
}
else
{
return $this->render('update', [
'model' => $model,
]);
}
}