public function actionIndex() { $fmShortDatePhp = DateTimeUtils::getDateFormat(DateTimeUtils::FM_KEY_PHP, null); $fmShortDateJui = DateTimeUtils::getDateFormat(DateTimeUtils::FM_KEY_JUI, null); JarPayment::$_PHP_FM_SHORTDATE = $fmShortDatePhp; $arrAccount = ModelUtils::getArrData(JarAccount::find()->select(['account_id', 'account_name'])->where(['delete_flag' => MasterValueUtils::MV_FIN_FLG_DELETE_FALSE])->orderBy('account_type, status, order_num'), 'account_id', 'account_name'); $arrEntryLog = MasterValueUtils::getArrData('jar_payment_status'); $searchModel = new JarPayment(); // submit data $postData = Yii::$app->request->post(); // populate model attributes with user inputs $searchModel->load($postData); // init value $today = DateTimeUtils::getNow(); if (Yii::$app->request->getIsGet()) { $tdInfo = getdate($today->getTimestamp()); $searchModel->entry_date_to = $today->format($fmShortDatePhp); $searchModel->entry_date_from = DateTimeUtils::parse($tdInfo[DateTimeUtils::FN_KEY_GETDATE_YEAR] - 1 . '0101', DateTimeUtils::FM_DEV_DATE, $fmShortDatePhp); } $searchModel->scenario = MasterValueUtils::SCENARIO_LIST; // sum current month $beginMonth = DateTimeUtils::parse($today->format(DateTimeUtils::FM_DEV_YM) . '01', DateTimeUtils::FM_DEV_DATE); $endMonth = DateTimeUtils::addDateTime($beginMonth, 'P1M'); DateTimeUtils::subDateTime($endMonth, 'P1D', null, false); $sumCurrentMonthQuery = (new Query())->select(['SUM(IF(account_source > 0, entry_value, 0)) AS debit', 'SUM(IF(account_target > 0, entry_value, 0)) AS credit']); $sumCurrentMonthQuery->from('jar_payment')->where(['delete_flag' => MasterValueUtils::MV_FIN_FLG_DELETE_FALSE]); $sumCurrentMonthQuery->andWhere(['OR', ['=', 'account_source', MasterValueUtils::MV_JAR_ACCOUNT_NONE], ['=', 'account_target', MasterValueUtils::MV_JAR_ACCOUNT_NONE]]); $sumCurrentMonthQuery->andWhere(['>=', 'entry_date', $beginMonth->format(DateTimeUtils::FM_DB_DATE)]); $sumCurrentMonthQuery->andWhere(['<=', 'entry_date', $endMonth->format(DateTimeUtils::FM_DB_DATE)]); $sumCurrentMonthData = $sumCurrentMonthQuery->createCommand()->queryOne(); // sum Debit Amount & Credit Amount $sumEntryValue = false; // query for dataprovider $dataQuery = null; if ($searchModel->validate()) { $dataQuery = JarPayment::find()->where(['delete_flag' => MasterValueUtils::MV_FIN_FLG_DELETE_FALSE]); $sumEntryQuery = (new Query())->select(['SUM(IF(account_source > 0, entry_value, 0)) AS debit', 'SUM(IF(account_target > 0, entry_value, 0)) AS credit']); $sumEntryQuery->from('jar_payment')->where(['delete_flag' => MasterValueUtils::MV_FIN_FLG_DELETE_FALSE]); $sumEntryQuery->andWhere(['OR', ['=', 'account_source', MasterValueUtils::MV_JAR_ACCOUNT_NONE], ['=', 'account_target', MasterValueUtils::MV_JAR_ACCOUNT_NONE]]); if (!empty($searchModel->entry_date_from)) { $dataQuery->andWhere(['>=', 'entry_date', $searchModel->entry_date_from]); $sumEntryQuery->andWhere(['>=', 'entry_date', $searchModel->entry_date_from]); } if (!empty($searchModel->entry_date_to)) { $dataQuery->andWhere(['<=', 'entry_date', $searchModel->entry_date_to]); $sumEntryQuery->andWhere(['<=', 'entry_date', $searchModel->entry_date_to]); } if (is_array($searchModel->entry_status)) { $dataQuery->andWhere(['entry_status' => $searchModel->entry_status]); $sumEntryQuery->andWhere(['entry_status' => $searchModel->entry_status]); } if (!empty($searchModel->description)) { $dataQuery->andWhere(['like', 'description', $searchModel->description]); $sumEntryQuery->andWhere(['like', 'description', $searchModel->description]); } if ($searchModel->account_source > 0) { $dataQuery->andWhere(['=', 'account_source', $searchModel->account_source]); } if ($searchModel->account_target > 0) { $dataQuery->andWhere(['=', 'account_target', $searchModel->account_target]); } $dataQuery->orderBy('entry_date DESC, id DESC'); $sumEntryValue = $sumEntryQuery->createCommand()->queryOne(); } else { $dataQuery = JarPayment::find()->where(['id' => -1]); } // render GUI $renderData = ['searchModel' => $searchModel, 'fmShortDatePhp' => $fmShortDatePhp, 'fmShortDateJui' => $fmShortDateJui, 'arrEntryLog' => $arrEntryLog, 'arrAccount' => $arrAccount, 'dataQuery' => $dataQuery, 'sumEntryValue' => $sumEntryValue, 'sumCurrentMonthData' => $sumCurrentMonthData]; return $this->render('index', $renderData); }