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