function filter($userid = FALSE, $year = FALSE, $month = FALSE) { $this->view_data['userlist'] = User::find('all', array('conditions' => array('status = ?', 'active'))); $this->view_data['username'] = User::find_by_id($userid); $this->view_data['user_id'] = $userid; $this->view_data['year'] = $year; $this->view_data['month'] = $month; $search = ""; $stats_search = ""; if ($userid) { $search .= "user_id = {$userid} and "; $stats_search = " AND user_id = {$userid} "; } if ($month && $year) { $search .= "date >= '{$year}-{$month}-01' and date <= '{$year}-{$month}-31'"; } else { $search .= "date >= '{$year}-01-01' and date <= '{$year}-12-31'"; } //statistic $graph_month = $month != 0 ? $month : date('m'); if ($month == 0) { $lastday_in_month = strtotime($year . "-12-31"); $firstday_in_month = strtotime($year . "-01-01"); $this->view_data['days_in_this_month'] = 12; $this->view_data['expenses_this_month'] = Expense::count(array('conditions' => 'UNIX_TIMESTAMP(`date`) <= ' . $lastday_in_month . ' and UNIX_TIMESTAMP(`date`) >= ' . $firstday_in_month . $stats_search)); $this->view_data['expenses_owed_this_month'] = Expense::find_by_sql('select sum(value) AS "owed" from expenses where UNIX_TIMESTAMP(`date`) >= "' . $firstday_in_month . '" AND UNIX_TIMESTAMP(`date`) <= "' . $lastday_in_month . '"' . $stats_search); $this->view_data['expenses_due_this_month_graph'] = Expense::find_by_sql('select sum(value) AS "owed", MONTH(`date`) as `date` from expenses where UNIX_TIMESTAMP(`date`) >= "' . $firstday_in_month . '" AND UNIX_TIMESTAMP(`date`) <= "' . $lastday_in_month . '"' . $stats_search . ' Group By MONTH(`date`)'); } else { $days_in_this_month = days_in_month($graph_month, $year); $lastday_in_month = strtotime($year . "-" . $graph_month . "-" . $days_in_this_month); $firstday_in_month = strtotime($year . "-" . $graph_month . "-01"); $this->view_data['days_in_this_month'] = $days_in_this_month; $this->view_data['expenses_this_month'] = Expense::count(array('conditions' => 'UNIX_TIMESTAMP(`date`) <= ' . $lastday_in_month . ' and UNIX_TIMESTAMP(`date`) >= ' . $firstday_in_month . $stats_search)); $this->view_data['expenses_owed_this_month'] = Expense::find_by_sql('select sum(value) AS "owed" from expenses where UNIX_TIMESTAMP(`date`) >= "' . $firstday_in_month . '" AND UNIX_TIMESTAMP(`date`) <= "' . $lastday_in_month . '"' . $stats_search); $this->view_data['expenses_due_this_month_graph'] = Expense::find_by_sql('select sum(value) AS "owed", `date` from expenses where UNIX_TIMESTAMP(`date`) >= "' . $firstday_in_month . '" AND UNIX_TIMESTAMP(`date`) <= "' . $lastday_in_month . '"' . $stats_search . ' Group By `date`'); } $this->view_data['expenses'] = Expense::find('all', array('conditions' => array("{$search}"))); $this->content_view = 'expenses/all'; }