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 SetClubData(Club club, DataRow row, SqLiteDbContext db)
{
club.MondayOpening = row[8].ToString();
club.MondayClosing = row[9].ToString();
club.ThusdayClosing = row[10].ToString();
club.ThusdayOpening = row[11].ToString();
club.WednesdayOpening = row[12].ToString();
club.WednesdayClosing = row[13].ToString();
club.ThursdayOpening = row[14].ToString();
club.ThursdayClosing = row[15].ToString();
club.FridayOpening = row[16].ToString();
club.FridayClosing = row[17].ToString();
club.SaturdayOpening = row[18].ToString();
club.SaturdayClosing = row[19].ToString();
club.SundayOpening = row[20].ToString();
club.SundayClosing = row[21].ToString();
string music = row[22].ToString();
var musicKind = db.MusicKinds.FirstOrDefault(x => x.KindOfMusic.Equals(music));
if (musicKind != null)
{
var musicKindId = musicKind.Id;
club.MusicKindId = musicKindId;
}
else
club.MusicKind = new MusicKind {KindOfMusic = music};
string age = row[23].ToString();
var ageKind = db.AgeKinds.FirstOrDefault(x => x.KindOfAge.Equals(age));
if (ageKind != null)
{
var kindId = ageKind.Id;
club.AgeKindId = kindId;
}
else
club.AgeKind = new AgeKind {KindOfAge = age};
string price = row[24].ToString();
var enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceMondayId = enterPriceId;
}
else
club.EnterPriceMonday = new EnterPriceDay {Price = price};
price = row[25].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceThusdayId = enterPriceId;
}
else
club.EnterPriceThusday = new EnterPriceDay { Price = price };
price = row[26].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceWednesdayId = enterPriceId;
}
else
club.EnterPriceWednesday = new EnterPriceDay { Price = price };
price = row[27].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceThursdayId = enterPriceId;
}
else
club.EnterPriceThursday = new EnterPriceDay { Price = price }; ;
price = row[28].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceFridayId = enterPriceId;
}
else
club.EnterPriceFriday = new EnterPriceDay { Price = price };
price = row[29].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceSaturdayId = enterPriceId;
}
else
club.EnterPriceSaturday = new EnterPriceDay { Price = price };
price = row[30].ToString();
enterPrice = db.EnterPriceDays.FirstOrDefault(x => x.Price.Equals(price));
if (enterPrice != null)
{
var enterPriceId = enterPrice.Id;
club.EnterPriceSundayId = enterPriceId;
}
else
club.EnterPriceSunday = new EnterPriceDay { Price = price };
club.CoatroomPrice = IntValue(row[31].ToString());
club.AverageAge = DoubleValue(row[32].ToString());
club.AvailableCoupons = row[33].ToString().ToLower().Equals("yes");
club.Juice = DoubleValue(row[34].ToString());
club.BeerG = DoubleValue(row[35].ToString());
club.BeerBootle = DoubleValue(row[36].ToString());
club.VS = DoubleValue(row[37].ToString());
club.VB = DoubleValue(row[38].ToString());
club.VBJ = DoubleValue(row[39].ToString());
club.Drink1 = DoubleValue(row[40].ToString());
club.Drink2 = DoubleValue(row[41].ToString());
club.Drink3 = DoubleValue(row[42].ToString());
}
