public static function by_type()
{
$output = "";
array_push(static::$db_fields, 'total', 'itemsCount', 'amountCHF');
$table = static::$table_name;
$sql = "SELECT \n expense_type_id,\n COUNT(id) AS itemsCount,\n SUM(amount) AS amount,\n SUM(amount * rate) AS amountCHF\nFROM\n {$table}\nGROUP BY expense_type_id;";
$table_class = Table::full_table_class();
$output .= "<table class='{$table_class} '>";
$output .= "<tr>\n <th class='text-center'>Expense Type" . "</th> \n <th class='text-center'>Items" . "</th>\n <th class='text-center'>Total Type" . "</th>\n <th class='text-center'>Total CHF" . "</th>\n </tr>";
$results = static::find_by_sql($sql);
if ($results) {
foreach ($results as $result) {
$myType = MyHouseExpenseType::find_by_id($result->expense_type_id);
$type = $myType->expense_type;
// $myperson=MyExpensePerson::find_by_id($result->person_id);
// $person=$myperson->person_name;
$output .= "<tr>";
// $output.="<td class='text-center'>{$person}</td>";
// $output.="<td class='text-center'>{$result->person_name}</td>";
// $output.="<td class='text-center'>{$result->person_id}</td>";
$output .= "<td class='text-center'>{$type}</td>";
$output .= "<td class='text-center'>{$result->itemsCount}</td>";
$output .= "<td class='text-right'>" . number_format($result->amount, 2) . "</td>";
$output .= "<td class='text-right'>" . number_format($result->amountCHF, 2) . "</td>";
$output .= "</tr>";
unset($type);
unset($myType);
}
}
unset($results);
$sum = number_format(static::sum_field_where($field = "amount * rate"), 2);
$output .= "<tr>";
$output .= "<td class='text-center'><strong>Total</strong></td>";
$output .= str_repeat("<td></td>", 2);
$output .= "<td class='text-right'><strong>" . $sum . "</strong></td>";
$output .= "</tr>";
$output .= "</table>";
return $output;
}
