public function actionProducts() { $posted_data = Yii::$app->request->post(); $data = ['transactions' => []]; if (Yii::$app->request->getQueryParam('page')) { $posted_data = Yii::$app->session['posted_data']; } if (!empty($posted_data)) { Yii::$app->session['posted_data'] = $posted_data; $date_range = $posted_data['date_range']; $player_select = $posted_data['player_select']; $category_select = $posted_data['category_select']; $warehouse_select = $posted_data['warehouse_select']; $products_select = $posted_data['products_select']; $query = TransactionItems::find(); $query->joinWith(['transaction', 'product', 'transaction.userAccount']); if ($player_select > 0) { $query->andWhere([Transactions::tableName() . '.user_id' => $player_select]); } if ($date_range != '') { $date_range = explode(' to ', $date_range); $start_date = $date_range[0]; $end_date = $date_range[1]; $query->andWhere(['BETWEEN', 'transaction_date', Yii::$app->formatter->asDate($start_date, 'php:Y-m-d') . ' 00:00:00', Yii::$app->formatter->asDate($end_date, 'php:Y-m-d') . ' 23:59:59']); } if ($category_select > 0) { $query->andWhere([Products::tableName() . '.category_id' => $category_select]); } if ($warehouse_select > 0) { $query->andWhere([Products::tableName() . '.warehouse_id' => $warehouse_select]); } if ($products_select > 0) { $query->andWhere(['product_id' => $products_select]); } $pagination = new Pagination(['totalCount' => $query->count(), 'pageSize' => 10]); $total_quantity = $query->sum(TransactionItems::tableName() . '.quantity'); $total_buy_price = $query->sum(TransactionItems::tableName() . '.buy_price'); $total_sell_price = $query->sum(TransactionItems::tableName() . '.sell_price'); $total_amount = $query->sum(TransactionItems::tableName() . '.quantity * ' . TransactionItems::tableName() . '.sell_price'); // echo $query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql; $transactions = $query->offset($pagination->offset)->limit($pagination->limit)->all(); $data = ['transactions' => $transactions, 'g_total_quantity' => $total_quantity, 'g_total_buy_price' => $total_buy_price, 'g_total_sell_price' => $total_sell_price, 'g_total_amount' => $total_amount, 'pagination' => $pagination, 'posted_data' => $posted_data]; } return $this->render('products', $data); }