public IEnumerable <MonthCategoryCost> GetTotalCostPerCategoryAndMonth(int year, int month, int UserId)
{
List <MonthCategoryCost> list = new List <MonthCategoryCost>();
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "SELECT Expense.ExpenseTypeId, " +
"ExpenseType.ExpenseTypeName, " +
"SUM(Cost) as TotalCost " +
"from Expense INNER JOIN ExpenseType " +
"ON Expense.ExpenseTypeId = ExpenseType.ExpenseTypeId " +
"WHERE UserId = @UserId AND strftime('%Y', Expense.Date) = @Year AND strftime('%m', Expense.Date) = @Month " +
"GROUP BY Expense.ExpenseTypeId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@UserId", UserId));
cmd.Parameters.Add(new SQLiteParameter("@Year", year.ToString()));
cmd.Parameters.Add(new SQLiteParameter("@Month", month.ToString("00")));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MonthCategoryCost item = new MonthCategoryCost();
item.ExpenseTypeId = Int32.Parse(reader["ExpenseTypeId"].ToString());
item.ExpenseTypeName = reader["ExpenseTypeName"].ToString();
item.TotalCost = Double.Parse(reader["TotalCost"].ToString());
list.Add(item);
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata zapisa o trošku za zadani mjesec i kategoriju [Expense->GetTotalCostPerCategoryAndMonth]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(list);
}
public IEnumerable <IExpenseModel> GetAllByUserIdAndExpenseType(int UserId, IExpenseTypeModel expenseTypeModel)
{
List <ExpenseModel> list = new List <ExpenseModel>();
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "SELECT * FROM Expense WHERE Expense.UserId = @UserId AND Expense.ExpenseTypeId = @ExpenseTypeId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseTypeModel.ExpenseTypeId));
cmd.Parameters.Add(new SQLiteParameter("@UserId", UserId));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ExpenseModel expenseModel = new ExpenseModel();
expenseModel.ExpenseId = Int32.Parse(reader["ExpenseId"].ToString());
expenseModel.ExpenseTypeId = Int32.Parse(reader["ExpenseTypeId"].ToString());
expenseModel.Date = reader["Date"].ToString();
expenseModel.Cost = Double.Parse(reader["Cost"].ToString());
list.Add(expenseModel);
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata zapisa o trošku za zadanu kategoriju troška [Expense->getAllByExpenseType]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(list);
}
public void Create(IUserModel userModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "INSERT INTO User (FirstName, LastName, CarModel) VALUES (@FirstName, @LastName, @CarModel)";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
try
{
RecordExistsCheck(cmd, userModel);
}
catch (DataAccessException ex)
{
ex.DataAccessResult.CustomMessage = "Korisnik već postoji u bazi podataka";
ex.DataAccessResult.ExceptionMessage = string.Copy(ex.Message);
ex.DataAccessResult.StackTrace = string.Copy(ex.StackTrace);
throw ex;
}
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.AddWithValue("@FirstName", userModel.FirstName);
cmd.Parameters.AddWithValue("@LastName", userModel.LastName);
cmd.Parameters.AddWithValue("@CarModel", userModel.CarModel);
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod kreiranja novog korisnika [User->Create]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public void Update(IExpenseTypeModel expenseTypeModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "UPDATE ExpenseType SET ExpenseTypeName = @ExpenseTypeName WHERE ExpenseType.ExpenseTypeId = @ExpenseTypeId;";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
try
{
RecordExistsCheck(cmd, expenseTypeModel);
}
catch (DataAccessException ex)
{
ex.DataAccessResult.CustomMessage = "Kategorija troška već postoji u bazi podataka";
ex.DataAccessResult.ExceptionMessage = string.Copy(ex.Message);
ex.DataAccessResult.StackTrace = string.Copy(ex.StackTrace);
throw ex;
}
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseTypeModel.ExpenseTypeId));
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeName", expenseTypeModel.ExpenseTypeName));
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod ažuriranja zapisa kategorije troška [ExpenseType->Update]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public IExpenseTypeModel GetById(int Id)
{
DataAccessResult dataAccessResult = new DataAccessResult();
ExpenseTypeModel expenseModel = new ExpenseTypeModel();
string sql = "SELECT * FROM ExpenseType WHERE ExpenseType.ExpenseTypeId = @ExpenseTypeId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", Id));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
expenseModel.ExpenseTypeId = Int32.Parse(reader["ExpenseTypeId"].ToString());
expenseModel.ExpenseTypeName = reader["ExpenseTypeName"].ToString();
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata zapisa o trošku [ExpenseType->GetById]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(expenseModel);
}
public List <int> GetDistinctYears(int UserId)
{
List <int> list = new List <int>();
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "SELECT DISTINCT(strftime('%Y', Date)) as Year from Expense WHERE UserId = @UserId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@UserId", UserId));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(Int32.Parse(reader["Year"].ToString()));
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata liste godina potrošnje [Expense->GetDistinctYears]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(list);
}
public IEnumerable <IExpenseTypeModel> GetAll()
{
DataAccessResult dataAccessResult = new DataAccessResult();
List <ExpenseTypeModel> list = new List <ExpenseTypeModel>();
string sql = "SELECT * FROM ExpenseType";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ExpenseTypeModel expenseTypeModel = new ExpenseTypeModel();
expenseTypeModel.ExpenseTypeId = Int32.Parse(reader["ExpenseTypeId"].ToString());
expenseTypeModel.ExpenseTypeName = reader["ExpenseTypeName"].ToString();
list.Add(expenseTypeModel);
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata svih zapisa kategorije troška [ExpenseType->GetAll]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(list);
}
public void Update(IExpenseModel expenseModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "UPDATE Expense SET ExpenseTypeId = @ExpenseTypeId, Date = @Date, Cost = @Cost WHERE Expense.ExpenseId = @ExpenseId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseId", expenseModel.ExpenseId));
cmd.Parameters.Add(new SQLiteParameter("@ExpenseTypeId", expenseModel.ExpenseTypeId));
cmd.Parameters.Add(new SQLiteParameter("@Date", expenseModel.Date));
cmd.Parameters.Add(new SQLiteParameter("@Cost", expenseModel.Cost));
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Unable to update expense [Expense->Update]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public void Create(IExpenseModel expenseModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "INSERT INTO Expense (ExpenseTypeId, Date, Cost, UserId) VALUES (@ExpenseTypeId, @Date, @Cost, @UserId)";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.AddWithValue("@ExpenseTypeId", expenseModel.ExpenseTypeId);
cmd.Parameters.AddWithValue("@Date", DateTime.Parse(expenseModel.Date));
cmd.Parameters.AddWithValue("@Cost", expenseModel.Cost);
cmd.Parameters.AddWithValue("@UserId", expenseModel.UserId);
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod kreiranja zapisa troška [Expense->Create]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public void Delete(IExpenseModel expenseModel)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "DELETE FROM Expense WHERE Expense.ExpenseId = @ExpenseId";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@ExpenseId", expenseModel.ExpenseId));
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod brisanja zapisa troška [Expense->Delete]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return;
}
public IUserModel GetByFirstAndLastName(string firstName, string lastName)
{
DataAccessResult dataAccessResult = new DataAccessResult();
string sql = "SELECT * FROM User u WHERE u.FirstName = @FirstName AND u.LastName = @LastName";
using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
{
try
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
{
cmd.CommandText = sql;
cmd.Prepare();
cmd.Parameters.Add(new SQLiteParameter("@FirstName", firstName));
cmd.Parameters.Add(new SQLiteParameter("@LastName", lastName));
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
UserModel userModel = new UserModel();
userModel.UserId = Int32.Parse(reader["UserId"].ToString());
userModel.FirstName = reader["FirstName"].ToString();
userModel.LastName = reader["LastName"].ToString();
userModel.CarModel = reader["CarModel"].ToString();
return(userModel);
}
}
else
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: "",
customMessage: "Korisnik sa zadanim imenom i prezimenom nije pronađen u bazi podataka",
helpLink: "",
errorCode: 0,
stackTrace: "");
throw new DataAccessException(dataAccessResult);
}
}
}
}
catch (SQLiteException e)
{
dataAccessResult.setValues(
status: "Error",
operationSucceeded: false,
exceptionMessage: e.Message,
customMessage: "Greška kod dohvata korisnika po imenu i prezimenu [User->GetByFirstAndLastName]",
helpLink: e.HelpLink,
errorCode: e.ErrorCode,
stackTrace: e.StackTrace);
throw new DataAccessException(e.Message, e.InnerException, dataAccessResult);
}
}
return(null);
}
