private void ReadSheet(ExcelSalesReport report, string Name, OleDbConnection connection) { OleDbCommand excelCommand = new OleDbCommand("SELECT * FROM [" + Name + " Sales$]", connection); using (var oleDbDataAdapter = new OleDbDataAdapter(excelCommand)) { var dataSet = new DataSet(); oleDbDataAdapter.Fill(dataSet); using (var reader = dataSet.CreateDataReader()) { reader.Read(); if (string.IsNullOrEmpty(reader[0].ToString())) { LeftOffset = 1; } else { LeftOffset = 0; } report.DistributorName = reader[LeftOffset + 0].ToString(); reader.Read(); // this skips column names this.GetReportEntries(reader, Name, report.Records); } } }
public void SeedSalesTable_alt(ExcelSalesReport excelSalesReport) { var employeeIdList = this.employees.All().Select(e => e.Id).ToList(); var VehicleIdAndModel = this.vehicles.All().Select(v => new { v.Id, v.Model }).ToList(); var ShopIdAndName = this.shops.All().Select(sh => new { sh.Id, sh.Name }).ToList(); var shop = ShopIdAndName.Where(s => s.Name.ToLower() == excelSalesReport.DistributorName.ToLower()).FirstOrDefault(); if (shop != null) { string formattedDateTime = String.Format("{0:yyyy/M/d HH:mm:ss}", excelSalesReport.DateOfSale); using (var dealershipDbContext = new DealershipDbContext()) { var result = dealershipDbContext.Database.ExecuteSqlCommand($"DELETE FROM [Sales] WHERE ShopId={shop.Id} AND DateOfSale='{formattedDateTime}'"); } int counter = 0; foreach (var record in excelSalesReport.Records) { var vehicle = VehicleIdAndModel.Where(v => v.Model.ToLower().Contains(record.VehicleModel.ToLower())).FirstOrDefault(); if (employeeIdList.Exists(i => (i == record.EmployeeId)) && vehicle != null) { Sale s = new Sale() { ShopId = shop.Id, VehicleId = vehicle.Id, EmployeeId = record.EmployeeId, Quantity = record.Quantity, Price = record.UnitPrice, DateOfSale = excelSalesReport.DateOfSale }; this.sales.Add(s); counter++; //Console.Write("."); if (counter > maxRecordsPerLoad) { this.data.SaveChanges(); counter = 0; } } else //null { Console.Write($"No such EmployeeId: {record.EmployeeId} or/and VehicleModel: {record.VehicleModel}"); } } this.data.SaveChanges(); } else //null { Console.Write($"No such DistributorName{excelSalesReport.DistributorName}"); } }
public ExcelSalesReport ReadReport(string reportPath, string reportDate) { ExcelSalesReport report = new ExcelSalesReport(); report.DateOfSale = DateTime.Parse(reportDate); string LocalconnectionString = string.Format(this.ConnectionString, reportPath); using (OleDbConnection connection = new OleDbConnection(LocalconnectionString)) { connection.Open(); ReadSheet(report, "Vehicle", connection); } return(report); }
public void SeedSalesTable(ExcelSalesReport excelSalesReport) { string shopName = excelSalesReport.DistributorName; int recordsCounter = 0; int shopId = GetShopIdByName(shopName); foreach (var record in excelSalesReport.Records) { try { ValidateEmployeeId(record.EmployeeId); Sale s = new Sale() { ShopId = shopId, VehicleId = GetVehicleIdByModel(record.VehicleModel), EmployeeId = record.EmployeeId, Quantity = record.Quantity, Price = record.UnitPrice, DateOfSale = excelSalesReport.DateOfSale }; this.sales.Add(s); recordsCounter++; if (recordsCounter >= maxRecordsPerLoad) { this.data.SaveChanges(); recordsCounter = 0; } } catch (ArgumentException ex) { Console.WriteLine("No such product or shop id in database!/n" + ex.Message); } } this.data.SaveChanges(); }