public CompanyStatistics GetTotalIncomeByYear(int year) { CompanyStatistics companyStatistics = new CompanyStatistics(); try { var dataTable = GetDataByView($"SELECT dbo.funcGetTotalInvoicePriceByYear('{year}')"); if (dataTable.Rows.Count > 0) { string test = dataTable.Rows[0][0].ToString(); if (test == "") { companyStatistics.TotalIncomeByYear = 0; } else { companyStatistics.TotalIncomeByYear = Convert.ToDecimal(dataTable.Rows[0][0].ToString()); } } } catch (SqlException sqlEx) { throw new InvoiceException( $"Neem contact op met de beheerder onder sqldatabase exceptionCode:{sqlEx.Number}"); } catch (Exception ex) { throw new InvoiceException($"Neem contact op met de beheerder onder exceptionCode:{ex.HResult}"); } return(companyStatistics); }
public static void IncrementRegistredProducts(this ApplicationDbContext context, int companyId) { var currentDate = GetCurrentDate(); var currentStatistics = context.CompanyStatistics.Find(currentDate, companyId); if (currentStatistics == null) { var companyStatistics = new CompanyStatistics() { Date = currentDate, CompanyId = companyId, RegistredProducts = 1 }; currentStatistics = context.CompanyStatistics.Add(companyStatistics); } else { currentStatistics.RegistredProducts += 1; } }
protected override void Seed(ApplicationDbContext context) { if (System.Diagnostics.Debugger.IsAttached == false) { // System.Diagnostics.Debugger.Launch(); } // This method will be called after migrating to the latest version. context.Configuration.LazyLoadingEnabled = true; // Generation configuration int companyCount = 3; int uniqueAddressStreetCount = 2; int uniqueAddressCityCount = 5; int userCount = 5; int uniqueUserNameCount = 5; int uniqueUserSurnameCount = 10; int productCount = 20; int employeeCount = 5; int productRatingCount = 10; int productCommentCount = 10; int companyReportCount = 10; int watchedProductCount = 20; int existingProductCount = 40; //int qrCodeCount = 10; int companyStatisticsCountPerCompany = 10; int scanCount = 20; Random random = new Random(); // delete everything context.CompanyStatistics.RemoveRange(context.CompanyStatistics); context.SaveChanges(); context.QRs.RemoveRange(context.QRs); context.SaveChanges(); context.ProductComments.RemoveRange(context.ProductComments); context.SaveChanges(); context.ExistingProducts.RemoveRange(context.ExistingProducts); context.SaveChanges(); context.ProductRatings.RemoveRange(context.ProductRatings); context.SaveChanges(); context.Employees.RemoveRange(context.Employees); context.SaveChanges(); context.WatchedProducts.RemoveRange(context.WatchedProducts); context.SaveChanges(); ((DbSet <ApplicationUser>)context.Users).RemoveRange(context.Users); context.SaveChanges(); context.ProductPictures.RemoveRange(context.ProductPictures); context.SaveChanges(); context.Companies.RemoveRange(context.Companies); context.SaveChanges(); context.Products.RemoveRange(context.Products); context.SaveChanges(); ((DbSet <IdentityRole>)context.Roles).RemoveRange(context.Roles); context.SaveChanges(); context.Statistics.RemoveRange(context.Statistics); context.SaveChanges(); context.Scans.RemoveRange(context.Scans); context.SaveChanges(); // UserRoles context.Roles.Add(new IdentityRole("User")); context.Roles.Add(new IdentityRole("Employee")); context.Roles.Add(new IdentityRole("Administrator")); context.SaveChanges(); // Company for (int i = 0; i < companyCount; ++i) { List <int> nipDigits = new List <int>(new int[10]); List <int> nipWeights = new List <int>(new int[9] { 6, 5, 7, 2, 3, 4, 5, 6, 7 }); int nipLastDigit = 0; for (int j = 0; j < nipWeights.Count; j++) { nipDigits[j] = random.Next(10); nipLastDigit += nipDigits[j] * nipWeights[j]; } nipLastDigit = nipLastDigit % 11; nipDigits[9] = nipLastDigit; Company company = new Company { Name = "companyName" + i, Kind = ((random.Next(2) == 0) ? "Production" : "Services"), AddressStreet = "addressStreet" + random.Next(uniqueAddressStreetCount), AddressZipCode = "CT-" + random.Next(99999), AddressCity = "addressCity" + random.Next(uniqueAddressCityCount), Email = "companyName" + i + "@mail.com", NIP = String.Join("", nipDigits), REGON = random.Next(999999999).ToString(), JoinDate = new DateTime(random.Next(2015, 2017), random.Next(12) + 1, random.Next(25) + 1) }; context.Companies.AddOrUpdate(c => c.Name, company); } context.SaveChanges(); // User var store = new UserStore <ApplicationUser>(context); var manager = new ApplicationUserManager(store); manager.PasswordValidator = new PasswordValidator { RequiredLength = 1, RequireNonLetterOrDigit = false, RequireDigit = false, RequireLowercase = false, RequireUppercase = false, }; for (int i = 0; i < userCount; i++) { List <int> phoneNumber = new List <int>(new int[10]); for (int j = 0; j < phoneNumber.Count; j++) { phoneNumber[j] = random.Next(10); } ApplicationUser user = new ApplicationUser { Name = "name" + i, Surname = "surname" + random.Next(uniqueUserSurnameCount), Email = "user" + i + "@email.com", UserName = "******" + i + "@email.com", EmailConfirmed = true, PasswordHash = ("user" + i).GetHashCode().ToString(), SecurityStamp = "?", PhoneNumber = String.Join("", phoneNumber.ToArray()), TwoFactorEnabled = false, LockoutEnabled = false, AccessFailedCount = 0 }; manager.CreateAsync(user, user.Name).Wait(); if (i == 0) { manager.AddToRoleAsync(user.Id, "Administrator").Wait(); } } // Employee List <Employee> employees = new List <Employee>(employeeCount); for (int i = 0; i < employeeCount; i++) { Employee employee = new Employee { UserId = context.Users.ToList()[random.Next(context.Users.Count())].Id, JoinDate = new DateTime(random.Next(2015, 2018), random.Next(12) + 1, random.Next(25) + 1), CompanyId = context.Companies.ToList()[random.Next(context.Companies.Count())].Id }; employees.Add(employee); manager.AddToRoleAsync(employee.UserId, "Employee").Wait(); } var distinceEmployees = employees.Distinct(new EmployeeEqualityComparer()).ToArray(); context.Employees.AddOrUpdate(e => e.UserId, distinceEmployees); context.SaveChanges(); // Product var products = new List <Product>(productCount); for (int i = 0; i < productCount; i++) { Product product = new Product { Name = "product" + i, Barcode = random.Next(9999999).ToString() + random.Next(999999), Description = "description" + i, CreationDate = new DateTime(random.Next(2015, 2017), random.Next(12) + 1, random.Next(25) + 1),//new DateTime(random.Next(2018, 2019), random.Next(1, 13), random.Next(25) + 1), CountryOfOrigin = "country" + random.Next(10), SuggestedPrice = (decimal)random.Next(201), CompanyId = context.Companies.ToList()[random.Next(context.Companies.Count())].Id }; products.Add(product); } context.Products.AddRange(products); context.SaveChanges(); // Product picture string[] filePaths; try { string appDataPath = AppDomain.CurrentDomain.BaseDirectory + "/App_Data"; var picturesPath = Path.Combine(appDataPath, "products"); filePaths = Directory.GetFiles(picturesPath); } catch (Exception e) { string appDataPath = AppDomain.CurrentDomain.BaseDirectory + "/.../App_Data"; var picturesPath = Path.Combine(appDataPath, "products"); filePaths = Directory.GetFiles(picturesPath); } var pictures = new List <byte[]>(); foreach (var path in filePaths) { var bytes = File.ReadAllBytes(path); pictures.Add(bytes); } var productPictures = new List <ProductPicture>(productCount); for (int i = 0; i < productCount; ++i) { var productPicture = new ProductPicture() { ProductId = products[i].Id, Content = pictures[random.Next(pictures.Count)] }; productPictures.Add(productPicture); } context.ProductPictures.AddRange(productPictures); context.SaveChanges(); // ProductRating List <ProductRating> productRatings = new List <ProductRating>(productRatingCount); for (int i = 0; i < productRatingCount; i++) { int[] rateArray = new[] { 1, 2, 3, 4, 5 }; ProductRating productRating = new ProductRating { Rating = random.Next(1, rateArray.Length), UserId = context.Users.ToList()[random.Next(context.Users.Count())].Id, ProductId = context.Products.ToList()[random.Next(context.Products.Count())].Id, Added = new DateTime(2018, 12, random.Next(28) + 1) }; if (!productRatings.Any(pr => pr.UserId.Equals(productRating.UserId) && pr.ProductId.Equals(productRating.ProductId))) { productRatings.Add(productRating); } } context.ProductRatings.AddOrUpdate(pr => pr.UserId, productRatings.ToArray()); context.SaveChanges(); // WatchedProduct var watchedProductList = new List <WatchedProduct>(watchedProductCount); for (int i = 0; i < watchedProductCount; i++) { WatchedProduct watchedProduct = new WatchedProduct { ApplicationUserId = context.Users.ToList()[random.Next(context.Users.Count())].Id, ProductId = context.Products.ToList()[random.Next(context.Products.Count())].Id }; if (watchedProductList.Any(wp => wp.ApplicationUserId == watchedProduct.ApplicationUserId && wp.ProductId == watchedProduct.ProductId) == false) { watchedProductList.Add(watchedProduct); } } context.WatchedProducts.AddRange(watchedProductList); context.SaveChanges(); // ProductComment for (int i = 0; i < productCommentCount; i++) { var productComment = new ProductComment { ApplicationUserId = context.Users.ToList()[random.Next(context.Users.Count())].Id, ProductId = context.Products.ToList()[random.Next(context.Products.Count())].Id, Comment = "Comment" + i, Date = new DateTime(random.Next(2016, 2018), random.Next(12) + 1, random.Next(25) + 1) }; context.ProductComments.AddOrUpdate(productComment); } context.SaveChanges(); // CompanyReport for (int i = 0; i < companyReportCount; i++) { CompanyReport companyReport = new CompanyReport { Content = "content" + i, CompanyId = context.Companies.ToList()[random.Next(context.Companies.Count())].Id }; context.CompanyReports.AddOrUpdate(cr => cr.Content, companyReport); } context.SaveChanges(); // ExistingProduct for (int i = 0; i < existingProductCount; i++) { ExistingProduct existingProduct = new ExistingProduct { ExpirationDate = new DateTime(random.Next(2018, 2030), random.Next(12) + 1, random.Next(25) + 1), CreationDate = new DateTime(random.Next(2018, 2019), random.Next(1, 13), random.Next(25) + 1), ProductId = context.Products.ToList()[random.Next(context.Products.Count())].Id }; existingProduct.GenerateSecret(); context.ExistingProducts.AddOrUpdate(existingProduct); } context.SaveChanges(); // QR /*List<QR> qrs = new List<QR>(qrCodeCount); * for (int i = 0; i < qrCodeCount; i++) * { * QR qr = new QR * { * ExistingProductId = context.ExistingProducts.ToList()[random.Next(context.ExistingProducts.Count())].Id, * Version = 1, * Content = new byte[10] * }; * qrs.Add(qr); * //context.QRs.AddOrUpdate(q => q.ExistingProductId, qr); * } * var distinctQrs = qrs.Distinct(new QREqualityComparer()).ToArray(); * context.QRs.AddOrUpdate(q => q.ExistingProductId, distinctQrs); * context.SaveChanges();*/ // CompanyStatistics var companyStatistics = new List <CompanyStatistics>(companyCount * companyStatisticsCountPerCompany); foreach (var company in context.Companies) { for (int j = 0; j < companyStatisticsCountPerCompany; j++) { var statistics = new CompanyStatistics() { CompanyId = company.Id, Date = DateTime.Now.AddDays(-j).Date, RegistredProducts = random.Next(10), }; companyStatistics.Add(statistics); } } context.CompanyStatistics.AddRange(companyStatistics); context.SaveChanges(); // Statistics var currentMonth = DateTime.Now.Month; var currentYear = DateTime.Now.Year; var currentMonthBeginning = new DateTime(currentYear, currentMonth, 1); context.Statistics.Add(new Statistics { LastMonthCompanyCount = context.Companies.Where(c => c.JoinDate < currentMonthBeginning).ToList().Count(), LastMonthProductCount = context.Products.Where(p => p.CreationDate < currentMonthBeginning).ToList().Count() }); context.SaveChanges(); // Scans var scans = new List <Scan>(scanCount); for (int i = 0; i < scanCount; ++i) { var scan = new Scan() { UserId = context.Users.ToList()[random.Next(context.Users.Count())].Id, ExistingProductId = context.ExistingProducts.ToList()[random.Next(context.ExistingProducts.Count())].Id, Date = DateTime.Now.AddDays(-i).Date, }; if (scans.Any(s => s.UserId == scan.UserId && s.ExistingProductId == scan.ExistingProductId)) { continue; } scans.Add(scan); } context.Scans.AddRange(scans); context.SaveChanges(); }