/**
* Lists all Journal models.
* @return mixed
*/
public function actionIndex()
{
$searchModel = new JournalSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
$query = $dataProvider->query;
if (!isset($req["sort"])) {
$query->orderBy(Transaction::tableName() . ".time desc");
}
return $this->render('index', ['searchModel' => $searchModel, 'dataProvider' => $dataProvider]);
}
public function getSaldo()
{
return $this->db->createCommand("SELECT \n\t\t\t\t\tsum(case when j.type = 0 then amount else j.amount*(-1) end) as saldo \n\t\t\t\t\tFROM " . Journal::tableName() . " as j\n\t\t\t\t\tLEFT JOIN " . $this->tableName() . " as a on j.account_id = a.id\n\t\t\t\t\tLEFT JOIN " . Transaction::tableName() . " as t on j.transaction_id = t.id\n\t\t\t\t\tWHERE (a.id_left >= :lid AND a.id_right <= :rid) AND j.isdel = 0 AND t.isdel = 0")->bindValues(["lid" => $this->id_left, "rid" => $this->id_right])->queryScalar();
}
public static function find()
{
return parent::find()->where([Transaction::tableName() . '.isdel' => 0]);
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = $this->find();
$query->joinWith(['transaction', 'account']);
$dataProvider = new ActiveDataProvider(['query' => $query]);
$dataProvider->sort->attributes['time'] = ['asc' => [Transaction::tableName() . '.time' => SORT_ASC], 'desc' => [Transaction::tableName() . '.time' => SORT_DESC]];
$dataProvider->sort->attributes['title'] = ['asc' => [Transaction::tableName() . '.title' => SORT_ASC], 'desc' => [Transaction::tableName() . '.title' => SORT_DESC]];
$dataProvider->sort->attributes['subject'] = ['asc' => [Transaction::tableName() . '.subject' => SORT_ASC], 'desc' => [Transaction::tableName() . '.subject' => SORT_DESC]];
$dataProvider->sort->attributes['tags'] = ['asc' => [Transaction::tableName() . '.tags' => SORT_ASC], 'desc' => [Transaction::tableName() . '.tags' => SORT_DESC]];
$dataProvider->sort->attributes['transactionRemarks'] = ['asc' => [Transaction::tableName() . '.remarks' => SORT_ASC], 'desc' => [Transaction::tableName() . '.remarks' => SORT_DESC]];
$dataProvider->sort->attributes['accountName'] = ['asc' => [AccountCode::tableName() . '.code' => SORT_ASC], 'desc' => [AccountCode::tableName() . '.code' => SORT_DESC]];
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id, 'account_id' => $this->account_id, 'transaction_id' => $this->transaction_id, 'type' => $this->type, 'isdel' => $this->isdel]);
$params = self::queryNumber([['quantity'], ['amount']]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$params = self::queryTime([['time', Transaction::tableName()]]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$params = self::queryString([['remarks', Journal::tableName()], ['title', Transaction::tableName()], ['subject', Transaction::tableName()], ['tags', Transaction::tableName()]]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$query->andFilterWhere(['like', 'lower(' . Transaction::tableName() . '.remarks)', strtolower($this->transactionRemarks)])->andFilterWhere(['like', "lower(concat(" . AccountCode::tableName() . ".code,' - '," . AccountCode::tableName() . ".name))", strtolower($this->accountName)]);
return $dataProvider;
}
