public static void ImportFixedData(string path)
{
//var excelFile = new ExcelQueryFactory(path) { TrimSpaces = TrimSpacesType.Both, ReadOnly = true };
//foreach (var row in excelFile.GetWorksheetNames()
// .Select(worksheet => (from row in excelFile.Worksheet(worksheet) select row)).SelectMany(rows => rows))
{
using (var ds = FillDataSet(path))
{
for (var i = 1; i < ds.Tables[0].Rows.Count; i++)
{
using (var db = new SqLiteDbContext())
{
var local = ds.Tables[0].Rows[i][1].ToString();
switch (local)
{
case "Klub":
var club = new Club();
SetCommonData(club, ds.Tables[0].Rows[i], db);
SetClubData(club, ds.Tables[0].Rows[i], db);
db.Clubs.AddOrUpdate(x => x.Code, club);
break;
case "Pub":
var pub = new Pub();
SetCommonData(pub, ds.Tables[0].Rows[i], db);
SetPubData(pub, ds.Tables[0].Rows[i], db);
db.Pubs.AddOrUpdate(x => x.Code, pub);
break;
case "Restaurant":
var restaurant = new Restaurant();
SetCommonData(restaurant, ds.Tables[0].Rows[i], db);
SetRestaurantData(restaurant, ds.Tables[0].Rows[i], db);
db.Restaurants.AddOrUpdate(x => x.Code, restaurant);
break;
case "Kawiarnie":
var café = new Cafe();
SetCommonData(café, ds.Tables[0].Rows[i], db);
SetCaféData(café, ds.Tables[0].Rows[i], db);
db.Cafes.AddOrUpdate(x => x.Code, café);
break;
case "Hotel":
var hotel = new Hotel();
SetCommonData(hotel, ds.Tables[0].Rows[i], db);
SetHotelData(hotel, ds.Tables[0].Rows[i], db);
db.Hotels.AddOrUpdate(x => x.Code, hotel);
break;
}
db.SaveChanges();
}
}
}
}
}
private static void SetCaféData(Cafe cafe, DataRow row, SqLiteDbContext db)
{
cafe.MondayOpening = row[8].ToString();
cafe.MondayClosing = row[9].ToString();
cafe.ThusdayClosing = row[10].ToString();
cafe.ThusdayOpening = row[11].ToString();
cafe.WednesdayOpening = row[12].ToString();
cafe.WednesdayClosing = row[13].ToString();
cafe.ThursdayOpening = row[14].ToString();
cafe.ThursdayClosing = row[15].ToString();
cafe.FridayOpening = row[16].ToString();
cafe.FridayClosing = row[17].ToString();
cafe.SaturdayOpening = row[18].ToString();
cafe.SaturdayClosing = row[19].ToString();
cafe.SundayOpening = row[20].ToString();
cafe.SundayClosing = row[21].ToString();
cafe.AvailableCoupons = row[33].ToString().ToLower().Equals("yes");
string c = row[49].ToString();
var cafeType = db.CafeTypes.FirstOrDefault(x => x.Name.Equals(c));
if (cafeType != null)
{
var cafeTypeId = cafeType.Id;
cafe.CafeTypeId = cafeTypeId;
}
else
cafe.CafeType = new CafeType {Name = c};
cafe.Tea = DoubleValue(row[50].ToString());
cafe.BlackCoffee = row[51].ToString().Equals("none") ? null : DoubleValue(row[51].ToString());
cafe.WhiteCoffee = row[52].ToString().Equals("none") ? null : DoubleValue(row[52].ToString());
cafe.Espresso = row[53].ToString().Equals("none") ? null : DoubleValue(row[53].ToString());
cafe.Capuccino = row[54].ToString().Equals("none") ? null : DoubleValue(row[54].ToString());
cafe.Frappucino = row[55].ToString().Equals("none") ? null : DoubleValue(row[55].ToString());
cafe.DessertsLow = row[56].ToString().Equals("none") ? null : DoubleValue(row[56].ToString());
cafe.DessertsHigh = row[57].ToString().Equals("none") ? null : DoubleValue(row[57].ToString());
cafe.HotDishes = row[58].ToString().ToLower().Equals("yes");
}
