public async Task <List <Item> > GetItems(string caseNumber, ItemOrderEnum itemOrder = ItemOrderEnum.TransactionDateAscending, string searchText = "")
{
//get data
var items = await ItemDataAccessOperation.GetItemsOfACase(itemOrder, caseNumber);
if (itemOrder == ItemOrderEnum.InOutAscending)
{
items = items.OrderBy((s) => s.IsIn).ToList();
}
else if (itemOrder == ItemOrderEnum.InOutDescending)
{
items = items.OrderByDescending((s) => s.IsIn).ToList();
}
//filter data
if (!string.IsNullOrWhiteSpace(searchText))
{
items = (from
item in items
where
item.Sender.Contains(searchText) ||
item.Reciever.Contains(searchText) ||
item.TransactionDate.Contains(searchText) ||
item.CaseNumber.Contains(searchText) ||
item.Amount.ToString().Contains(searchText)
select item).ToList();
}
return(items);
}
public static async Task <List <Item> > GetItemsOfACase(ItemOrderEnum itemOrder, string caseNumber)
{
using var dbConnection = new SQLiteConnection("Data Source=appdata.sqlite");
await dbConnection.OpenAsync();
using var sql = itemOrder switch
{
ItemOrderEnum.SenderAscending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY sender ASC", dbConnection),
ItemOrderEnum.RecieverAscending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY reciever ASC", dbConnection),
ItemOrderEnum.AmountAscending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY amount ASC", dbConnection),
ItemOrderEnum.TransactionDateAscending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY transactionDate ASC", dbConnection),
ItemOrderEnum.SenderDescending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY sender DESC", dbConnection),
ItemOrderEnum.RecieverDescending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY reciever DESC", dbConnection),
ItemOrderEnum.AmountDescending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY amount DESC", dbConnection),
ItemOrderEnum.TransactionDateDescending => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY transactionDate DESC", dbConnection),
_ => new SQLiteCommand(@"SELECT * FROM ITEMS WHERE caseNumber = @caseNumber ORDER BY transactionDate DESC", dbConnection)
};
sql.Parameters.AddWithValue("@caseNumber", caseNumber);
await sql.ExecuteNonQueryAsync();
var response = await sql.ExecuteReaderAsync();
var items = new List <Item>();
while (await response.ReadAsync())
{
items.Add(new Item()
{
Id = (long)response["id"],
CaseNumber = response["caseNumber"].ToString(),
Sender = response["sender"].ToString(),
Reciever = response["reciever"].ToString(),
Amount = (Int64)response["amount"],
TransactionDate = response["transactionDate"].ToString(),
IsIn = (bool)response["isIn"]
});
}
response.Close();
dbConnection.Close();
return(items);
}
}
