private static void Mapping(ISQLiteStatement statement, Debt e)
{
e.id = (long)statement["id"];
e.amount = (double)statement["amount"];
e.deadline = DateTime.Parse((string)statement["deadline"]);
e.description = (string)statement["description"];
e.category_id = (long)statement["category_id"];
e.type_id = (long)statement["type_id"];
}
public static void Delete(Debt debt)
{
string sql = @"DELETE FROM debt WHERE id=@id";
DB db = DB.getDB(databaseFile);
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@id", debt.id);
db.NonQuery(sql, parms);
}
public static void Create(Debt debt)
{
string sql = @"INSERT INTO debt (amount, deadline, description, category_id, type_id)
VALUES (@amount, @deadline, @description, @category_id, @type_id)";
DB db = DB.getDB(databaseFile);
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@amount", debt.amount);
parms.Add("@deadline", debt.deadline.ToString("yyyy-MM-dd HH:mm:ss.fff"));
parms.Add("@description", debt.description);
parms.Add("@category_id", debt.category_id);
parms.Add("@type_id", debt.type_id);
db.NonQuery(sql, parms);
}
public static ObservableCollection <Debt> RetrieveAll()
{
ObservableCollection <Debt> res = new ObservableCollection <Debt>();
string sql = "SELECT * FROM debt";
DB db = DB.getDB(databaseFile);
ISQLiteStatement statement = db.Query(sql);
while (statement.Step() == SQLiteResult.ROW)
{
Debt e = new Debt();
Mapping(statement, e);
res.Add(e);
}
return(res);
}
public static void Update(Debt debt)
{
string sql = @"UPDATE debt
SET amount=@amount, deadline=@deadline, description=@description,
category_id=@category_id, type_id=@type_id
WHERE id=@id";
DB db = DB.getDB(databaseFile);
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@amount", debt.amount);
parms.Add("@deadline", debt.deadline.ToString("yyyy-MM-dd HH:mm:ss.fff"));
parms.Add("@description", debt.description);
parms.Add("@category_id", debt.category_id);
parms.Add("@type_id", debt.type_id);
parms.Add("@id", debt.id);
if (db.NonQuery(sql, parms))
{
debt.id = db.LastId();
}
}
public static ObservableCollection <Debt> RetrieveByDeadline(DateTime deadline)
{
ObservableCollection <Debt> res = new ObservableCollection <Debt>();
string sql = "SELECT * FROM debt WHERE deadline<=@deadline";
DB db = DB.getDB(databaseFile);
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@deadline", deadline.ToString("yyyy-MM-dd HH:mm:ss.fff"));
ISQLiteStatement statement = db.Query(sql, parms);
while (statement.Step() == SQLiteResult.ROW)
{
Debt e = new Debt();
Mapping(statement, e);
res.Add(e);
}
return(res);
}
public static ObservableCollection <Debt> RetrieveByType(long type_id)
{
ObservableCollection <Debt> res = new ObservableCollection <Debt>();
string sql = "SELECT * FROM debt WHERE type_id=@type_id ORDER BY deadline";
DB db = DB.getDB(databaseFile);
Dictionary <string, object> parms = new Dictionary <string, object>();
parms.Add("@type_id", type_id);
ISQLiteStatement statement = db.Query(sql, parms);
while (statement.Step() == SQLiteResult.ROW)
{
Debt e = new Debt();
Mapping(statement, e);
res.Add(e);
}
return(res);
}
