private void ShowDataCommandExecuted(object obj)
{
var searchRequest = new SearchRequestModel
{
Name = PurchaseName,
TypeId = ProductType.Id,
DateStart = SearchFromDate,
DateEnd = SearchToDate,
SearchByName = !string.IsNullOrEmpty(PurchaseName),
SearchByType = ProductType.Id != -1,
SearchByDate = true,
SearchByCost = false,
};
Task.Factory.StartNew(async () =>
{
var chartData = (await StoreService.LoadPurchaseList(searchRequest))
.GroupBy(x =>
{
return CutTimeTo(x.Date, SelectedInterval);
})
.Select(g => GetChartElement(g)).ToList();
ChartSeries = new List<IEnumerable<SeriesDateBasedElement>> { chartData };
});
}
public async Task<List<PurchaseModel>> LoadPurchaseList(SearchRequestModel filter)
{
var list = new List<PurchaseModel>();
try
{
logger.Debug("DatabaseService.LoadPurchaseList: Loading purchase items list");
using (var db = dbManager.GetConnection())
using (var command = db.Connection.CreateCommand())
{
string queue = "SELECT pu.Id AS PurchaseId, pu.Timestamp, pu.TotalCost, pu.ItemCost, pu.ItemsNumber," +
"p.Id AS ProductId, p.Name AS ProductName, p.TypeId, p.SubTypeId, p.IsMonthly, "+
"s.Id AS StoreId, s.Name AS StoreName, "+
"c.Text AS Comment, c.Rate "+
"FROM PURCHASE pu "+
"LEFT JOIN PRODUCT p ON pu.ProductId=p.Id "+
"LEFT JOIN STORE s ON pu.StoreId=s.Id "+
"LEFT JOIN COMMENT c ON pu.Id=c.PurchaseId "+
"WHERE";
if (filter.SearchById)
{
queue = string.Format("{0} PurchaseId={1} ", queue, filter.PurchaseId);
}
if (filter.SearchByName)
{
queue = string.Format("{0} ProductName LIKE '%{1}%' ", queue, StringUtilities.EscapeStringForDatabase(filter.Name.Trim(' ')));
}
if (filter.SearchByType)
{
queue = string.Format("{0} TypeId={1} ", queue, filter.TypeId);
}
if (filter.SearchByDate)
{
queue = string.Format("{0} Timestamp BETWEEN {1} AND {2} ", queue, filter.DateStart.Ticks, filter.DateEnd.Ticks);
}
if (filter.SearchByCost)
{
queue = string.Format("{0} TotalCost BETWEEN {1} AND {2} ", queue, filter.CostStart, filter.CostEnd);
}
if (filter.SearchByCommentId)
{
queue = string.Format("{0} CommentID={1} ", queue, filter.CommentId);
}
if (filter.SearchByStoreId)
{
queue = string.Format("{0} StoreID={1} ", queue, filter.StoreId);
}
if (filter.SearchByMonthly)
{
var isMonthlyString = string.Format(" (IsMonthly='{0}' ", filter.IsMonthly);
if (filter.IsMonthly)
{
isMonthlyString += ")";
}
else
{
isMonthlyString += "OR IsMonthly is NULL) ";
}
queue = string.Format("{0} {1} ", queue, isMonthlyString);
}
command.CommandText = queue
.TrimEnd(" WHERE")
.TrimEnd(' ')
.Replace(" ", " AND ");
logger.Debug("DatabaseService.LoadPurchaseList: queue: {0}", command.CommandText);
var dataReader = await command.ExecuteReaderAsync().ConfigureAwait(false);
while (dataReader.Read())
{
list.Add(new PurchaseModel
{
Id = dataReader.GetInt64(dataReader.GetOrdinal("PurchaseId")),
ProductName = dataReader.GetString(dataReader.GetOrdinal("ProductName")),
ProductId = dataReader.GetInt64(dataReader.GetOrdinal("ProductId")),
Timestamp = dataReader.GetInt64(dataReader.GetOrdinal("Timestamp")),
TotalCost = dataReader.GetDouble(dataReader.GetOrdinal("TotalCost")),
ItemCost = dataReader.GetDouble(dataReader.GetOrdinal("ItemCost")),
ItemsNumber = dataReader.GetDouble(dataReader.GetOrdinal("ItemsNumber")),
TypeId = dataReader.GetInt64(dataReader.GetOrdinal("TypeId")),
SubTypeId = (dataReader.GetValue(dataReader.GetOrdinal("SubTypeId")) as long?) ?? 0L,
Rate = (int)((dataReader.GetValue(dataReader.GetOrdinal("Rate")) as long?) ?? 0L),
Comment = (dataReader.GetValue(dataReader.GetOrdinal("Comment")) as string) ?? string.Empty,
IsMonthly = (dataReader.GetValue(dataReader.GetOrdinal("IsMonthly")) as bool?) ?? false,
StoreId = (dataReader.GetValue(dataReader.GetOrdinal("StoreId")) as long?) ?? 0L,
StoreName = (dataReader.GetValue(dataReader.GetOrdinal("StoreName")) as string) ?? string.Empty
});
}
logger.Debug("DatabaseService.LoadPurchaseList: data fetched succesfully");
logger.Debug("Found {0} items", list.Count);
}
}
catch (Exception ex)
{
logger.Error("Exception during execution method \"LoadPurchaseList\": {0}", ex.Message);
}
return list;
}
public async Task<List<Purchase>> LoadPurchaseList(SearchRequestModel filter)
{
var convertedList = new List<Purchase>();
var modelsList = await DBService.LoadPurchaseList(filter).ConfigureAwait(false);
foreach (var model in modelsList)
{
convertedList.Add(await ModelToPurchase(model));
}
return convertedList;
}
public async Task<List<Purchase>> LoadPurchaseList(SearchRequestModel.Requests enumFilter)
{
var list = new List<Purchase>();
switch (enumFilter)
{
case SearchRequestModel.Requests.Empty:
logger.Debug("Selected load purchase with no filter");
var modelsList = await DBService.LoadCompletePurchaseList().ConfigureAwait(false);
foreach (var model in modelsList)
{
list.Add(await ModelToPurchase(model));
}
break;
}
logger.Debug("StorageService: purchase list loaded");
return list;
}
private void SearchCommandExecute(object obj)
{
var searchRequest = new SearchRequestModel
{
Name = purchaseName,
TypeId = ProductType != null ?ProductType.Id : -1,
CostStart = costStart,
CostEnd = costEnd,
DateStart = SearchFromDate,
DateEnd = SearchToDate,
IsMonthly = IsMonthly,
SearchByName = SearchByName,
SearchByType = SearchByType,
SearchByDate = SearchByDate,
SearchByCost = SearchByCost,
SearchByMonthly = SearchByMonthly
};
Task.Factory.StartNew(async () =>
{
List<Purchase> results = await StoreService.LoadPurchaseList(searchRequest).ConfigureAwait(false);
results = results.OrderBy(p => p.Date).ToList();
TotalCount = results.Sum(p => p.ItemsNumber).ToString();
TotalCost = results.Sum(p => p.TotalCost).ToString();
BackupSearchList(results);
SearchResultList = new BindingList<Purchase>(results);
Status.Post("Пошук завершено, знайдено {0} записів", searchResultList.Count);
});
}
public void DatabaseStateTest()
{
StoreService = StorageService.GetInstance();
var purchase = new Purchase
{
Date = DateTime.Now,
Id = 0,
ItemCost = 10.4,
ItemsNumber = 135.2,
TotalCost = 1406.08,
IsMonthly = true,
Name = "Cote d'Azure apple",
PurchaseComment = "Воно чудо'ве",
PurchaseRate = 10,
StoreComment = "Цікави'й магазин",
StoreName = "Нову'с магазин",
StoreRate = 5,
Type = new ProductType { Name = "Їжа", Id = 0 },
SubType = new ProductSubType { Name = "Фрукт", Id = 0 }
};
var purchase2 = new Purchase
{
Date = DateTime.Now,
Id = 0,
ItemCost = 17.5,
ItemsNumber = 544,
TotalCost = 9520,
IsMonthly = false,
Name = "Ozone Mantra M6",
PurchaseComment = "Це палапрал",
PurchaseRate = 8,
StoreComment = "Завжди тут беру",
StoreName = "Від Перевалова",
StoreRate = 7,
Type = new ProductType { Name = "Спорядження", Id = 0 },
SubType = new ProductSubType { Name = "Льотне спорядження", Id = 0 }
};
var purchase3 = new Purchase
{
Date = DateTime.Now,
Id = 0,
ItemCost = 234,
ItemsNumber = 53,
TotalCost = 12402,
IsMonthly = true,
Name = "Помаранч",
PurchaseComment = "Нічотак",
PurchaseRate = 9,
StoreComment = "Зайшов після роботи",
StoreName = "Фора",
StoreRate = 6,
Type = new ProductType { Name = "Їжа", Id = 0 },
SubType = new ProductSubType { Name = "Фрукт", Id = 0 }
};
var purchase4 = new Purchase
{
Date = DateTime.Now,
Id = 0,
ItemCost = 1,
ItemsNumber = 2,
TotalCost = 2,
IsMonthly = false,
Name = "Груша",
Type = new ProductType { Name = "Їжа", Id = 0 }
};
//Cleanup
Assert.IsTrue(StoreService.DeletePurchase(purchase.Name).Result, "Cleanup failed");
Assert.IsTrue(StoreService.DeletePurchase(purchase2.Name).Result, "Cleanup failed");
Assert.IsTrue(StoreService.DeletePurchase(purchase3.Name).Result, "Cleanup failed");
Assert.IsTrue(StoreService.DeletePurchase(purchase4.Name).Result, "Cleanup failed");
//Save test
purchase = StoreService.AddPurchase(purchase).Result;
Assert.IsTrue(purchase != null, "Purchase saving error");
purchase2 = StoreService.AddPurchase(purchase2).Result;
Assert.IsTrue(purchase != null, "Purchase saving error");
purchase3 = StoreService.AddPurchase(purchase3).Result;
Assert.IsTrue(purchase != null, "Purchase saving error");
purchase4 = StoreService.AddPurchase(purchase4).Result;
Assert.IsTrue(purchase != null, "Purchase saving error");
//read test
var searchRequest = new SearchRequestModel
{
Name = purchase.Name,
SearchByName = true
};
var searchRequest2 = new SearchRequestModel
{
Name = purchase2.Name,
SearchByName = true
};
var searchRequest3 = new SearchRequestModel
{
Name = purchase3.Name,
SearchByName = true
};
var searchRequest4 = new SearchRequestModel
{
Name = purchase4.Name,
SearchByName = true
};
var foundList = StoreService.LoadPurchaseList(searchRequest).Result;
Assert.IsTrue(foundList.Count == 1, "Purchase storage error: records duplicating found");
var controlPurchase = foundList.FirstOrDefault();
Assert.IsTrue(controlPurchase.DeepEquals(purchase), "Purchase storage error: not consistent");
var foundList2 = StoreService.LoadPurchaseList(searchRequest2).Result;
Assert.IsTrue(foundList2.Count == 1, "Purchase storage error: records duplicating found");
var controlPurchase2 = foundList2.FirstOrDefault();
Assert.IsTrue(controlPurchase2.DeepEquals(purchase2), "Purchase storage error: not consistent");
var foundList3 = StoreService.LoadPurchaseList(searchRequest3).Result;
Assert.IsTrue(foundList3.Count == 1, "Purchase storage error: records duplicating found");
var controlPurchase3 = foundList3.FirstOrDefault();
Assert.IsTrue(controlPurchase3.DeepEquals(purchase3), "Purchase storage error: not consistent");
var foundList4 = StoreService.LoadPurchaseList(searchRequest4).Result;
Assert.IsTrue(foundList4.Count == 1, "Purchase storage error: records duplicating found");
var controlPurchase4 = foundList4.FirstOrDefault();
Assert.IsTrue(controlPurchase4.DeepEquals(purchase4), "Purchase storage error: not consistent");
//update test
purchase.IsMonthly = false;
purchase.Name = "Звичайне яблуко";
purchase = StoreService.AddPurchase(purchase).Result;
Assert.IsTrue(purchase != null, "Purchase saving error");
//repeated read test
var controlPurchase_repeat = StoreService.LoadPurchase(controlPurchase.Id).Result;
Assert.IsTrue(controlPurchase_repeat.DeepEquals(purchase), "Purchase storage error: not consistent");
//delete test
Assert.IsTrue(StoreService.DeletePurchase(purchase).Result, "Purchase deletion error");
var deletedPurchase = StoreService.LoadPurchase(purchase.Id).Result;
Assert.IsTrue (deletedPurchase == null, "Purchase deletion error: not deleted");
}
public async Task<List<ProductModel>> LoadProductList(SearchRequestModel filter)
{
var list = new List<ProductModel>();
try
{
logger.Debug("DatabaseService.LoadProductList: Loading product list");
using (var db = dbManager.GetConnection())
using (var command = db.Connection.CreateCommand())
{
string queue = "SELECT p.Id as ProductId, p.Name as ProductName, p.IsMonthly, t.Id as TypeId, t.Name as TypeName, st.Id as SubTypeId, st.TypeId as StTypeId, st.Name as SubTypeName"+
"FROM PRODUCT p"+
"JOIN PRODUCTTYPE t ON p.TypeId = t.Id" +
"LEFT JOIN PRODUCTSUBTYPE st on p.SubTypeId = st.Id" +
"WHERE";
if (filter.SearchByProductId)
{
queue = string.Format("{0} Id = {1} ", queue, filter.ProductId);
}
if (filter.SearchByName)
{
queue = string.Format("{0} Name LIKE '%{1}%' ", queue, StringUtilities.EscapeStringForDatabase(filter.Name.Trim(' ')));
}
if (filter.SearchByType)
{
queue = string.Format("{0} TypeId = {1} ", queue, filter.TypeId);
}
if (filter.SearchBySubType)
{
queue = string.Format("{0} SubTypeId = {1} ", queue, filter.SubTypeId);
}
if (filter.SearchByMonthly)
{
queue = string.Format("{0} IsMonthly = {1} ", queue, filter.IsMonthly);
}
command.CommandText = queue
.TrimEnd(" WHERE")
.TrimEnd(' ')
.Replace(" ", " AND ");
var dataReader = await command.ExecuteReaderAsync().ConfigureAwait(false);
while (dataReader.Read())
{
list.Add(new ProductModel
{
Id = dataReader.GetInt64(dataReader.GetOrdinal("ProductId")),
Name = dataReader.GetString(dataReader.GetOrdinal("ProductName")),
Type = new ProductTypeModel
{
TypeId = dataReader.GetInt64(dataReader.GetOrdinal("TypeId")),
Name = dataReader.GetString(dataReader.GetOrdinal("TypeName"))
},
SubType = new ProductSubTypeModel
{
Id = (dataReader.GetValue(dataReader.GetOrdinal("SubTypeId")) as long?) ?? 0L,
TypeId = (dataReader.GetValue(dataReader.GetOrdinal("StTypeId")) as long?) ?? 0L,
Name = (dataReader.GetValue(dataReader.GetOrdinal("SubTypeName")) as string) ?? string.Empty
},
IsMonthly = dataReader.GetBoolean(dataReader.GetOrdinal("IsMonthly"))
});
}
logger.Debug("DatabaseService.LoadProductList: data fetched succesfully");
}
}
catch (Exception ex)
{
logger.Error("Exception during execution method \"LoadProductList\": {0}", ex.Message);
}
return list;
}