public function actionOverview()
{
$model = new Dashboard();
$thisyear = date('Y');
$thismonth = date('m');
$lastmonth = date('m', strtotime('-1 months', strtotime(date('Y-m-d'))));
$user = Yii::$app->user->identity->id;
//get current month revenue
$command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 1 AND MONTH(date) = {$thismonth} AND YEAR(date) = {$thisyear}");
$currentmonth_revenue = $command->queryScalar();
//get current month expense
$command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 2 AND MONTH(date) = {$thismonth} AND YEAR(date) = {$thisyear}");
$currentmonth_expense = $command->queryScalar();
//get previous month revenue
$lastmonth_revenue_command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 1 AND MONTH(date) = {$lastmonth} AND YEAR(date) = {$thisyear}");
$lastmonth_revenue = $lastmonth_revenue_command->queryScalar();
//get all revenue exclude previous month
$all_revenue_command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 1 AND MONTH(date) < {$lastmonth}");
$all_revenue = $all_revenue_command->queryScalar();
//get previous month expense
$lastmonth_expense_command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 2 AND MONTH(date) = {$lastmonth} AND YEAR(date) = {$thisyear}");
$previousmonth_expense = $lastmonth_expense_command->queryScalar();
//get all expense exclude previous month
$all_expense_command = Yii::$app->db->createCommand("SELECT sum(value) FROM cashbook WHERE user_id = {$user} AND type_id = 2 AND MONTH(date) < {$lastmonth}");
$all_expense = $all_expense_command->queryScalar();
//calculate balance exclude previous month
$balance = $all_revenue + $all_expense;
//calculate previous month revenue include balance
$previousmonth_revenue = $balance + $lastmonth_revenue;
$category_cmd = Yii::$app->db->createCommand("SELECT desc_category AS cat, category.hexcolor_category as color, SUM(value) as value FROM cashbook\n INNER JOIN category\n ON cashbook.category_id = category.id_category\n WHERE category.user_id = {$user} AND type_id = 2 AND MONTH(date) = {$thismonth} AND YEAR(date) = {$thisyear}\n GROUP BY category.id_category\n ORDER BY value ASC LIMIT 10\n ");
$category = $category_cmd->queryAll();
$cat = array();
$color = array();
$value = array();
for ($i = 0; $i < sizeof($category); $i++) {
$cat[] = $category[$i]["cat"];
$color[] = $category[$i]["color"] != '' ? $category[$i]["color"] : '#2C3E50';
$value[] = abs((int) $category[$i]["value"]);
//turn value into positive number for chart gen
}
$segment_cmd = Yii::$app->db->createCommand("SELECT x.`year`, x.`month`, y.desc_category as seg, y.hexcolor_category as colorseg, sum( x.value) as total FROM (\n SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value, \n year(c.`date`) as `year` , month(c.`date`) AS `month`\n FROM category\n INNER JOIN cashbook AS c ON category.id_category = c.category_id ) AS x \n INNER JOIN category AS y ON x.parent_id = y.id_category\n INNER JOIN user AS u ON y.user_id = u.id\n WHERE u.id = {$user}\n GROUP BY y.desc_category, x.`year`, x.`month`\n having x.`year` = year(now()) and x.`month` = month(now()) \n ");
$segment = $segment_cmd->queryAll();
$seg = array();
$colorseg = array();
$total = array();
for ($i = 0; $i < sizeof($segment); $i++) {
$seg[] = $segment[$i]["seg"];
$colorseg[] = $segment[$i]["colorseg"] != '' ? $segment[$i]["colorseg"] : '#2C3E50';
$total[] = abs((int) $segment[$i]["total"]);
}
return $this->render('overview', ['model' => $model, 'currentmonth_revenue' => $currentmonth_revenue, 'currentmonth_expense' => $currentmonth_expense, 'previousmonth_revenue' => $previousmonth_revenue, 'previousmonth_expense' => $previousmonth_expense, 'cat' => $cat, 'color' => $color, 'value' => $value, 'seg' => $seg, 'total' => $total, 'colorseg' => $colorseg]);
}
