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 SetPubData(Pub pub, DataRow row, SqLiteDbContext db)
{
pub.MondayOpening = row[8].ToString();
pub.MondayClosing = row[9].ToString();
pub.ThusdayClosing = row[10].ToString();
pub.ThusdayOpening = row[11].ToString();
pub.WednesdayOpening = row[12].ToString();
pub.WednesdayClosing = row[13].ToString();
pub.ThursdayOpening = row[14].ToString();
pub.ThursdayClosing = row[15].ToString();
pub.FridayOpening = row[16].ToString();
pub.FridayClosing = row[17].ToString();
pub.SaturdayOpening = row[18].ToString();
pub.SaturdayClosing = row[19].ToString();
pub.SundayOpening = row[20].ToString();
pub.SundayClosing = row[21].ToString();
pub.AvailableCoupons = row[33].ToString().ToLower().Equals("yes");
pub.AverageAge = DoubleValue(row[32].ToString());
pub.Juice = DoubleValue(row[34].ToString());
pub.BeerG = DoubleValue(row[35].ToString());
pub.BeerBootle = DoubleValue(row[36].ToString());
pub.VS = DoubleValue(row[37].ToString());
pub.VB = DoubleValue(row[38].ToString());
pub.VBJ = DoubleValue(row[39].ToString());
pub.Drink1 = DoubleValue(row[40].ToString());
pub.Drink2 = DoubleValue(row[41].ToString());
pub.Drink3 = DoubleValue(row[42].ToString());
string price = row[43].ToString();
var barPrice = db.Prices.FirstOrDefault(x => x.Name.Equals(price));
if (barPrice != null)
{
var barPriceId = barPrice.Id;
pub.BarPriceId = barPriceId;
}
else
pub.Price = new Price { Name = price};
price = row[44].ToString();
barPrice = db.Prices.FirstOrDefault(x => x.Name.Equals(price));
if (barPrice != null)
{
var barMenuPriceId = barPrice.Id;
pub.BarMenuPriceId = barMenuPriceId;
}
else
pub.MenuPrice = new Price { Name = price };
}