public IEnumerable <ExpensesView> GetAllExpensesPaged(int rowsPerPage, int pageNo)
{
return(_connection.Query <ExpensesView>(@"select result.*,app_user.username
from (select expenses.id,(select getdivideamongnames(expenses.divideamong))as divideamongnames,expenses.paidby,expenses.comment,expenses.divideamong,
expenses.amount,expenses.expensedate::timestamp::date,category.id as categoryid,category.name as categoryname
from expenses left join category on expenses.category = category.id)as result
left join app_user on result.paidby = app_user.id order by expensedate DESC OFFSET ((@pageNo -1) * @rowsPerPage) ROWS FETCH NEXT 5 ROWS ONLY", new { pageNo = pageNo, rowsPerPage = rowsPerPage }));
}
public dynamic GetCategoryWiseExpenseAmount()
{
return(_connection.Query <dynamic>(@"SELECT name, SUM(amount) AS amount FROM expenses left join category on expenses.category = category.id GROUP BY name"));
}