Hi, I am using yii2 basic.
I have groupsavingdetails CRUD.
When I try to save the data it gives me foll error.
# Database Exception – [yii\db\Exception](http://www.yiiframework.com/doc-2.0/yii-db-exception.html)
## SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
The SQL being executed was: SELECT count(*)
FROM groupsavingdetails
where groupsavingdetails.GroupId=29
and groupsavingdetails.Year<=2017
and groupsavingdetails.Month<=
My model is as follows:
<?php
namespace app\models;
use Yii;
use app\models\Groupdetails;
/**
* This is the model class for table "groupsavingdetails".
*
* @property integer $GroupSavingDetailsId
* @property integer $EmpId
* @property integer $GroupId
* @property string $Year
* @property string $Month
* @property double $OpeningBalance
* @property double $TotalSaving
* @property double $LoanRecovery
* @property double $LoanInterest
* @property double $Fine
* @property double $BankInterest
* @property double $Expenses
* @property double $LoanGiven
* @property double $BankLoan
* @property double $TotalValueofLoanGiven
* @property double $LoanRepaidUptilNow
* @property double $TotalValueOfLoanOutstanding
* @property double $CashInHand
* @property double $CashInBank
* @property double $ClosingBalance
*
* @property Employee $emp
* @property Groupdetails $group
*/
class Groupsavingdetails extends \yii\db\ActiveRecord
{
/**
* @inheritdoc
*/
public static function tableName()
{
return 'groupsavingdetails';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['EmpId', 'GroupId', 'Year', 'Month', 'OpeningBalance', 'TotalSaving', 'LoanRecovery', 'LoanInterest', 'Fine', 'BankInterest', 'Expenses', 'LoanGiven', 'BankLoan', 'TotalValueofLoanGiven', 'LoanRepaidUptilNow', 'TotalValueOfLoanOutstanding', 'CashInHand', 'CashInBank', 'ClosingBalance'], 'required'],
[['EmpId', 'GroupId'], 'integer'],
[['Year'],'validateyear'],
[['Year'],'integer'],
[[ 'Month'], 'string'],
[['Month'],'validatemonth'],
[['OpeningBalance', 'TotalSaving', 'LoanRecovery', 'LoanInterest', 'Fine', 'BankInterest', 'Expenses', 'LoanGiven', 'BankLoan', 'TotalValueofLoanGiven', 'LoanRepaidUptilNow', 'TotalValueOfLoanOutstanding', 'CashInHand', 'CashInBank', 'ClosingBalance'], 'number'],
[['EmpId'], 'exist', 'skipOnError' => true, 'targetClass' => Employee::className(), 'targetAttribute' => ['EmpId' => 'EmpId']],
[['GroupId'], 'exist', 'skipOnError' => false, 'targetClass' => Groupdetails::className(), 'targetAttribute' => ['GroupId' => 'GroupId']],
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'GroupSavingDetailsId' => 'Group Saving Details ID',
'EmpId' => 'Emp ID',
'GroupId' => 'Group',
'Year' => 'Year',
'Month' => 'Month',
'OpeningBalance' => 'Opening Balance',
'TotalSaving' => 'Total Saving',
'LoanRecovery' => 'Loan Recovery',
'LoanInterest' => 'Loan Interest',
'Fine' => 'Fine',
'BankInterest' => 'Bank Interest',
'Expenses' => 'Expenses',
'LoanGiven' => 'Loan Given',
'BankLoan' => 'Bank Loan',
'TotalValueofLoanGiven' => 'Total Valueof Loan Given',
'LoanRepaidUptilNow' => 'Loan Repaid Uptil Now',
'TotalValueOfLoanOutstanding' => 'Total Value Of Loan Outstanding',
'CashInHand' => 'Cash In Hand',
'CashInBank' => 'Cash In Bank',
'ClosingBalance' => 'Closing Balance',
];
}
/**
* @return \yii\db\ActiveQuery
*/
public function getEmp()
{
return $this->hasOne(Employee::className(), ['EmpId' => 'EmpId']);
}
public function validateyear($attribute,$params,$validator)
{
$group = Groupdetails::find()->where(['GroupId' => $this->GroupId])->all();
$year;
if ($group !=null) {
foreach($group as $groups)
{
$year= $groups['FormationDate'];
}
$year1= explode('-', $year);
if($this->$attribute<$year1[0])
$this->addError($attribute,'Year should be greater than or = to '.$year1[0]);
}
else
{
$this->addError($attribute,'Such group not there');
}
}
public function validatemonth($attribute, $params, $validator)
{
$group = Groupdetails::find()->where(['GroupId' => $this->GroupId])->all();
$year;
if ($group !=null) {
foreach($group as $groups)
{
$year= $groups['FormationDate'];
}
$year1= explode('-', $year);
if($this->Year==$year1[0]&&$this->$attribute<$year1[1])
$this->addError($attribute,'Month should be greater than or = to '.date("F",strtotime($year)));
}
else
{
$this->addError($attribute,'Such group not there');
}
}
/**
* @return \yii\db\ActiveQuery
*/
public function getGroup()
{
return $this->hasOne(Groupdetails::className(), ['GroupId' => 'GroupId']);
}
}
My controller actionCreate() is
public function actionCreate()
{
$model = new Groupsavingdetails();
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='.$model->GroupId.'
and groupsavingdetails.Year<='.$model->Year.'
and groupsavingdetails.Month<='.$model->Month
)
->queryScalar();
if($count==0)
{
$model->OpeningBalance=0;
$model->TotalValueofLoanGiven=0;
$model->LoanRepaidUptilNow= 0;
$model->TotalValueOfLoanOutstanding = 0;
$model->ClosingBalance=($model->OpeningBalance+$model->TotalSaving+$model->LoanRecovery+$model->LoanInterest+$model->Fine+$model->BankInterest-$model->Expenses-$model->LoanGiven-$model->BankLoan);
$model->save();
}
return $this->redirect(['view', 'id' => $model->GroupSavingDetailsId]);
} else {
return $this->render('create', [
'model' => $model,
]);
}
}
How should I resolve this?