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;
        }