Hi, I am using Yii2 basic. I have a table called groupsavingdetails for storing monthly groups saving. I have a situation.
When I update September record, I enter 200 as loan given and click save. So TotalValueofLoanGiven is 200, LoanRepaidUptilNow is 0 and TotalValueOfLoanOutstanding is 200.
Now when I update October record, I enter 100 as Loan given.
Thus, TotalValueofLoanGiven is 300, LoanRepaidUptilNow is 0 and TotalValueOfLoanOutstanding is 300.
Now when I again update September record, and enter 100 as Loan Given then, TotalValueofLoanGiven is 100, LoanRepaidUptilNow is 0 and TotalValueOfLoanOutstanding is 100.
But the records greater than September(i,e. October, November, and Decembers) TotalValueofLoanGiven becomes 100, LoanRepaidUptilNow is 0 and TotalValueOfLoanOutstanding is 100.
It should be 200 . Following is the action update.
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.GroupId=:Id
and ( Year > :year or (Year>=:year and Month > :month) )
')
->bindValues([
':Id'=>$model->GroupId,':year'=>$model->Year,':month'=>$model->Month
])
->queryAll();
if($count>1)
{
$group1 = Groupsavingdetails::find()->where(['GroupSavingDetailsId'=>$model->GroupSavingDetailsId])->all();
$loangiven;$loanrecovery;
foreach($group1 as $group2)
{
$loangiven=$group2['LoanGiven'];
$loanrecovery=$group2['LoanRecovery'];
}
if($model->LoanGiven!=0)
{
if($model->LoanGiven!=$loangiven)
{
$query = Yii::$app->db->createCommand('select * from groupsavingdetails where GroupId=:id and Year>=:year and LoanGiven!=0 and GroupSavingDetailsId=:ID order by GroupSavingDetailsId ASC limit 1')->bindValues([':id' => $model->GroupId,':year'=>$model->Year,':ID'=>$model->GroupSavingDetailsId])->queryAll();
if($query!=null)
{
foreach($query as $querys)
{
$model->TotalValueofLoanGiven=0;
$model->TotalValueofLoanGiven+=$model->LoanGiven;
}
}
else
{
$model->TotalValueofLoanGiven+=$model->LoanGiven;
}
}
}
if($model->LoanRecovery!=0)
{
if($model->LoanRecovery!=$loanrecovery)
{
$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 = Groupsavingdetails::find()
->andWhere(['and',
["GroupId" => $model->GroupId],
['>=', "Year", $model->Year],
['>', "Month", $model->Month],
])
->orWhere(['and',
["GroupId" => $model->GroupId],
['>', "Year", $model->Year],
['>', "Month",0],
])->all();
foreach ($groups as $k => $group) {
if($k==0)
{
$group->OpeningBalance = $model->ClosingBalance;
$group->TotalValueofLoanGiven = $model->TotalValueofLoanGiven;
$group->LoanRepaidUptilNow = $model->LoanRepaidUptilNow;
$group->TotalValueOfLoanOutstanding = $model->TotalValueOfLoanOutstanding;
$group1 = Groupsavingdetails::find()->where(['GroupSavingDetailsId'=>$model->GroupSavingDetailsId])->all();
$loangiven;$loanrecovery;
foreach($group1 as $group2)
{
$loangiven=$group2['LoanGiven'];
$loanrecovery=$group2['LoanRecovery'];
}
if($group->LoanGiven!=0)
{
if($group->LoanGiven!=$loangiven)
{
$query = Yii::$app->db->createCommand('select * from groupsavingdetails where GroupId=:id and Year>=:year and LoanGiven!=0 and GroupSavingDetailsId=:ID order by GroupSavingDetailsId ASC limit 1')->bindValues([':id' => $model->GroupId,':year'=>$model->Year,':ID'=>$model->GroupSavingDetailsId])->queryAll();
if($query!=null)
{
foreach($query as $querys)
{
$group->TotalValueofLoanGiven=0;
$group->TotalValueofLoanGiven+=$group->LoanGiven;
}
}
else
{
$group->TotalValueofLoanGiven+=$group->LoanGiven;
}
}
}
if($group->LoanRecovery!=0)
{
if($group->LoanRecovery!=$model->LoanRecovery)
{
$group->LoanRepaidUptilNow+=$group->LoanRecovery;
}
}
$group->TotalValueOfLoanOutstanding=$group->TotalValueofLoanGiven-$group->LoanRepaidUptilNow;
$group->ClosingBalance=($group->OpeningBalance+$group->TotalSaving+$group->LoanRecovery+$group->LoanInterest+$group->Fine+$group->BankInterest+$group->BankLoan-$group->Expenses-$group->LoanGiven);
$group->save();
}
if($k!=0)
{
$group['OpeningBalance'] = $groups[$k-1]->ClosingBalance;
$group['TotalValueofLoanGiven'] = $groups[$k-1]->TotalValueofLoanGiven;
$group['LoanRepaidUptilNow'] = $groups[$k-1]->LoanRepaidUptilNow;
$group['TotalValueOfLoanOutstanding'] = $groups[$k-1]->TotalValueOfLoanOutstanding;
$group1 = Groupsavingdetails::find()->where(['GroupSavingDetailsId'=>$groups[$k-1]->GroupSavingDetailsId])->all();
$loangiven;$loanrecovery;
foreach($group1 as $group2)
{
$loangiven=$group2['LoanGiven'];
$loanrecovery=$group2['LoanRecovery'];
}
if($group->LoanGiven!=0)
{
if($group->LoanGiven!=$loangiven)
{
$query = Yii::$app->db->createCommand('select * from groupsavingdetails where GroupId=:id and Year>=:year and LoanGiven!=0 and GroupSavingDetailsId=:ID order by GroupSavingDetailsId ASC limit 1')->bindValues([':id' => $groups[$k-1]->GroupId,':year'=> $groups[$k-1]->Year,':ID'=> $groups[$k-1]->GroupSavingDetailsId])->queryAll();
if($query!=null)
{
foreach($query as $querys)
{
$group->TotalValueofLoanGiven=0;
$group->TotalValueofLoanGiven+=$group->LoanGiven;
}
}
else
{
$group->TotalValueofLoanGiven+=$group->LoanGiven;
}
}
}
if($group->LoanRecovery!=0)
{
if($group->LoanRecovery!=$groups[$k-1]->LoanRecovery)
{
$group->LoanRepaidUptilNow+=$group->LoanRecovery;
}
}
$group->TotalValueOfLoanOutstanding=$group->TotalValueofLoanGiven-$group->LoanRepaidUptilNow;
$group->ClosingBalance=($group->OpeningBalance+$group->TotalSaving+$group->LoanRecovery+$group->LoanInterest+$group->Fine+$group->BankInterest+$group->BankLoan-$group->Expenses-$group->LoanGiven);
$group->save();
}
}
$model->save();
return $this->redirect(['view', 'id' => $model->GroupSavingDetailsId]);
}
}
else
{
return $this->render('update', [
'model' => $model,
]);
}
}