private void bgwInsert_DoWork(object sender, DoWorkEventArgs e) { Random random = new Random(); if ((int)e.Argument == 1) { using (var context = DbContextCreator.Create()) { List <Station> stations = context.Stations.ToList(); for (int i = 0; i < rows.Count; ++i) { if (bgwInsert.CancellationPending) { e.Cancel = true; return; } if (e.Cancel == false) { _pause.WaitOne(); Station station = stations.Find(x => x.Name == rows[i][1]); if (station == null) { // 역이 없을 경우 추가 station = new Station(); station.Name = rows[i][1]; context.Stations.Add(station); stations.Add(station); } string transferName = rows[i][2]; FootTraffic footTraffic = new FootTraffic() { Date = ParsingDateTime(rows[i][0]), Station = station, Transfer = context.Transfers.Where(x => x.Name == transferName).FirstOrDefault(), BeforeSix = int.Parse(rows[i][3]), SixToSeven = int.Parse(rows[i][4]), SevenToEight = int.Parse(rows[i][5]), EightToNine = int.Parse(rows[i][6]), NineToTen = int.Parse(rows[i][7]), TenToEleven = int.Parse(rows[i][8]), ElevenToTwelve = int.Parse(rows[i][9]), TwelveToThirteen = int.Parse(rows[i][10]), ThirteenToFourteen = int.Parse(rows[i][11]), FourteenToFifteen = int.Parse(rows[i][12]), FifteenToSixteen = int.Parse(rows[i][13]), SixteenToSeventeen = int.Parse(rows?[i][14]), SeventeenToEighteen = int.Parse(rows[i][15]), EighteenToNineteen = int.Parse(rows[i][16]), NineteenToTwenty = int.Parse(rows[i][17]), TwentyToTwentyOne = int.Parse(rows[i][18]), TwnetyOneToTwentyTwo = int.Parse(rows[i][19]), TwentyTwoToTwentyThree = int.Parse(rows[i][20]), TwentyThreeToTwentyFour = int.Parse(rows[i][21]), AfterTwentyFour = int.Parse(rows[i][22]) }; context.FootTraffics.Add(footTraffic); bgwInsert.ReportProgress((i + 1) * 100 / rows.Count, (i + 1).ToString()); } } context.SaveChanges(); } } else if ((int)e.Argument == 2) { using (var context = DbContextCreator.Create()) { int maxFirstId = (context.SatisfactionCategories.Max(x => x.SatisfactionCategoryId)) / 100; List <SatisfactionCategory> satisfactionCategories = context.SatisfactionCategories.ToList(); for (int i = 0; i < rows.Count; ++i) { _pause.WaitOne(); if (bgwInsert.CancellationPending) { e.Cancel = true; return; } if (e.Cancel == false) { // 카테고리 검사 SatisfactionCategory first = satisfactionCategories.Find(x => x.Item == rows[i][1]); if (first == null) { // 카테고리1이 없을 때 first = new SatisfactionCategory(); first.Item = rows[i][1]; first.SatisfactionCategoryId = maxFirstId * 100; maxFirstId += 1; context.SatisfactionCategories.Add(first); satisfactionCategories.Add(first); } int upperId = first.SatisfactionCategoryId; SatisfactionCategory second = satisfactionCategories.Find(x => x.Item == rows[i][2]); if (second == null || (second != null && second.UpperId != upperId)) { // 카테고리2가 없을 때, 또는 카테고리2가 있는데 카테고리1이 다를 때 second = new SatisfactionCategory(); second.Item = rows[i][2]; second.UpperId = upperId; SatisfactionCategory upper = satisfactionCategories .OrderByDescending(x => x.SatisfactionCategoryId).Where(x => x.UpperId == upperId).FirstOrDefault(); if (upper == null) { second.SatisfactionCategoryId = upperId + 1; } else { second.SatisfactionCategoryId = upper.SatisfactionCategoryId + 1; } second.SatisfactionCategory2 = first; context.SatisfactionCategories.Add(second); satisfactionCategories.Add(second); } // Satisfaction Satisfaction satisfaction = new Satisfaction() { Year = ParsingDateTime(rows[i][0]), SatisfactionCategory = second, Excellent = decimal.Parse(rows[i][3]), Good = decimal.Parse(rows[i][4]), Soso = decimal.Parse(rows[i][5]), Bad = decimal.Parse(rows[i][6]), Terrible = decimal.Parse(rows[i][7]) }; context.Satisfactions.Add(satisfaction); bgwInsert.ReportProgress((i + 1) * 100 / rows.Count, (i + 1).ToString()); } } context.SaveChanges(); } } else if ((int)e.Argument == 3) { // 날짜 전력사용량 전기요금 using (var context = DbContextCreator.Create()) { for (int i = 0; i < rows.Count; ++i) { _pause.WaitOne(); if (bgwInsert.CancellationPending) { e.Cancel = true; return; } if (e.Cancel == false) { Electricity electricity = new Electricity(); electricity.Month = ParsingDateTime(rows[i][0]); electricity.Usage = int.Parse(rows[i][1]); electricity.Bill = int.Parse(rows[i][2]); context.Electricities.Add(electricity); bgwInsert.ReportProgress((i + 1) * 100 / rows.Count, (i + 1).ToString()); } } context.SaveChanges(); } } else if ((int)e.Argument == 4) { // 날짜 역명 카드사 수입 //SubwayCard // 1.Wayne shipping, 2.DK Transport, 3.Gotham Credit using (var context = DbContextCreator.Create()) { List <Station> stations = context.Stations.ToList(); List <SubwayCard> subwayCards = context.SubwayCards.ToList(); for (int i = 0; i < rows.Count; i++) { _pause.WaitOne(); if (bgwInsert.CancellationPending) { e.Cancel = true; return; } if (e.Cancel == false) { Station station = stations.Find(x => x.Name == rows[i][1]); if (station == null) { // 역이 없을 경우 추가 station = new Station(); station.Name = rows[i][1]; context.Stations.Add(station); stations.Add(station); } SubwayCard subwayCard = subwayCards.Find(x => x.CompanyName == rows[i][2]); if (subwayCard == null) { // 카드사가 없을 경우 추가 subwayCard = new SubwayCard(); subwayCard.CompanyName = rows[i][2]; context.SubwayCards.Add(subwayCard); subwayCards.Add(subwayCard); } Revenue revenue = new Revenue() { Station = station, SubwayCard = subwayCard, Month = ParsingDateTime(rows[i][0]), Income = int.Parse(rows[i][3]) }; context.Revenues.Add(revenue); bgwInsert.ReportProgress((i + 1) * 100 / rows.Count, (i + 1).ToString()); } } context.SaveChanges(); } } }
private void bgwInsert_DoWork(object sender, DoWorkEventArgs e) { Random random = new Random(); if ((int)e.Argument == 1) { // Entity에 값 넣기 List <Station> stations = new List <Station>(); List <FootTraffic> footTraffics = new List <FootTraffic>(); List <SubwayCard> subwayCards = new List <SubwayCard>(); List <Revenue> revenues = new List <Revenue>(); List <Transfer> transfers = new List <Transfer>(); using (var context = DbContextCreator.Create()) { footTraffics = context.FootTraffics.ToList(); stations = context.Stations.ToList(); subwayCards = context.SubwayCards.ToList(); transfers = context.Transfers.ToList(); } for (int i = 0; i < rows.Count; ++i) { // Station Station station = new Station(); if (stations.Find(x => x.Name == rows[i][1]) == null) { // 역이 없을 경우 추가해주기 station.Name = rows[i][1]; stations.Add(station); } else { // 역이 있을 경우 연결 station = stations.Find(x => x.Name == rows[i][1]); } // FootTraffic FootTraffic footTraffic = new FootTraffic() { Station = station, TransferId = transfers.Find(x => x.Name == rows[i][2]).TransferId }; double dateNumber = 0; dateNumber = double.Parse(rows[i][0]); if (dateNumber > 60d) { dateNumber = dateNumber - 2; } else { dateNumber = dateNumber - 1; } DateTime dateTime = new DateTime(1900, 1, 1); dateTime = dateTime.AddDays(dateNumber); footTraffic.Date = dateTime; footTraffic.BeforeSix = int.Parse(rows[i][3]); footTraffic.SixToSeven = int.Parse(rows[i][4]); footTraffic.SevenToEight = int.Parse(rows[i][5]); footTraffic.EightToNine = int.Parse(rows[i][6]); footTraffic.NineToTen = int.Parse(rows[i][7]); footTraffic.TenToEleven = int.Parse(rows[i][8]); footTraffic.ElevenToTwelve = int.Parse(rows[i][9]); footTraffic.TwelveToThirteen = int.Parse(rows[i][10]); footTraffic.ThirteenToFourteen = int.Parse(rows[i][11]); footTraffic.FourteenToFifteen = int.Parse(rows[i][12]); footTraffic.FifteenToSixteen = int.Parse(rows[i][13]); footTraffic.SixteenToSeventeen = int.Parse(rows[i][14]); footTraffic.SeventeenToEighteen = int.Parse(rows[i][15]); footTraffic.EighteenToNineteen = int.Parse(rows[i][16]); footTraffic.NineteenToTwenty = int.Parse(rows[i][17]); footTraffic.TwentyToTwentyOne = int.Parse(rows[i][18]); footTraffic.TwnetyOneToTwentyTwo = int.Parse(rows[i][19]); footTraffic.TwentyTwoToTwentyThree = int.Parse(rows[i][20]); footTraffic.TwentyThreeToTwentyFour = int.Parse(rows[i][21]); footTraffic.AfterTwentyFour = int.Parse(rows[i][22]); footTraffics.Add(footTraffic); } //SubwayCard // 1.Wayne shipping, 2.DK Transport, 3.Gotham Credit List <SubwayCard> subwayCardList = new List <SubwayCard>(); subwayCardList.Add(new SubwayCard() { CompanyName = "Wayne shipping" }); subwayCardList.Add(new SubwayCard() { CompanyName = "DK Transport" }); subwayCardList.Add(new SubwayCard() { CompanyName = "Gotham Credit" }); subwayCards.AddRange(subwayCardList); for (int i = 1; i <= 12; i++) { for (int j = 0; j < stations.Count; j++) { List <FootTraffic> list = footTraffics.FindAll(x => x.Date.Month == i && x.StationId == (j + 1)); int sum = 0; foreach (var item in list) { sum = item.BeforeSix + item.SixToSeven + item.SevenToEight + item.EightToNine + item.NineToTen + item.TenToEleven + item.ElevenToTwelve + item.TwelveToThirteen + item.ThirteenToFourteen + item.FourteenToFifteen + item.FifteenToSixteen + item.SixteenToSeventeen + item.SeventeenToEighteen + item.EighteenToNineteen + item.NineteenToTwenty + item.TwentyToTwentyOne + item.TwnetyOneToTwentyTwo + item.TwentyTwoToTwentyThree + item.TwentyThreeToTwentyFour + item.AfterTwentyFour; } sum *= 1300; for (int k = 0; k < 3; k++) { Revenue revenue = new Revenue(); revenue.StationId = j + 1; revenue.Month = new DateTime(list.First().Date.Year - 1, i, 1); revenue.SubwayCardId = k + 1; if (k < 2) { int income = (int)(sum * (random.NextDouble() % 0.4)); revenue.Income = income; sum -= income; } else { revenue.Income = sum; sum = 0; } revenues.Add(revenue); } } } // Entity를 DB에 저장 using (var context = DbContextCreator.Create()) { for (int i = context.Stations.Count(); i < stations.Count; i++) { context.Stations.Add(stations[i]); } for (int i = context.SubwayCards.Count(); i < subwayCards.Count; i++) { context.SubwayCards.Add(subwayCards[i]); } context.FootTraffics.AddRange(footTraffics); context.Revenues.AddRange(revenues); context.SaveChanges(); } } else if ((int)e.Argument == 2) { // Entity에 값 넣기 List <Satisfaction> satisfactions = new List <Satisfaction>(); List <SatisfactionCategory> satisfactionCategories = new List <SatisfactionCategory>(); int maxFirstId; int beforeSatisfactionCategoryCount; using (var context = DbContextCreator.Create()) { satisfactionCategories = context.SatisfactionCategories.ToList(); if (satisfactionCategories.Count != 0) { maxFirstId = (context.SatisfactionCategories.Max(x => x.SatisfactionCategoryId)) / 100; } else { maxFirstId = 1; } beforeSatisfactionCategoryCount = satisfactionCategories.Count; } for (int i = 0; i < rows.Count; ++i) { // SatisfactionCategory SatisfactionCategory firstSatisfactionCategory = new SatisfactionCategory(); // 카테고리1이 없을 떄 만들어주기 if (satisfactionCategories.Find(x => x.Item == rows[i][0]) == null) { firstSatisfactionCategory.Item = rows[i][0]; firstSatisfactionCategory.SatisfactionCategoryId = maxFirstId * 100; maxFirstId += 1; satisfactionCategories.Add(firstSatisfactionCategory); } int upperId = satisfactionCategories.Find(x => x.Item == rows[i][0]).SatisfactionCategoryId; SatisfactionCategory secondSatisfactionCategory = new SatisfactionCategory(); // 카테고리2가 없을 때, 또는 카테고리2가 있는데 카테고리1이 다를 때 if (satisfactionCategories.Find(x => x.Item == rows[i][1]) == null || ((satisfactionCategories.Find(x => x.Item == rows[i][1]) != null) && satisfactionCategories.Find(x => x.Item == rows[i][1]).UpperId != upperId)) { secondSatisfactionCategory.Item = rows[i][1]; secondSatisfactionCategory.UpperId = upperId; if (satisfactionCategories.Find(x => x.UpperId == upperId) == null) { secondSatisfactionCategory.SatisfactionCategoryId = upperId + 1; } else { secondSatisfactionCategory.SatisfactionCategoryId = satisfactionCategories.Where(x => x.UpperId == upperId).Max(x => x.SatisfactionCategoryId) + 1; } secondSatisfactionCategory.SatisfactionCategory2 = satisfactionCategories.Find(x => x.SatisfactionCategoryId == secondSatisfactionCategory.UpperId); satisfactionCategories.Add(secondSatisfactionCategory); } else { secondSatisfactionCategory = satisfactionCategories.Find(x => x.Item == rows[i][1] && x.UpperId == upperId); } // Satisfaction Satisfaction satisfaction = new Satisfaction() { SatisfactionCategory = secondSatisfactionCategory }; satisfaction.Excellent = decimal.Parse(rows[i][2]); satisfaction.Good = decimal.Parse(rows[i][3]); satisfaction.Soso = decimal.Parse(rows[i][4]); satisfaction.Bad = decimal.Parse(rows[i][5]); satisfaction.Terrible = decimal.Parse(rows[i][6]); satisfactions.Add(satisfaction); } foreach (var s in satisfactionCategories) { if (s.SatisfactionCategoryId == 0) { bgwInsert.ReportProgress(0, "error"); } } // Entity를 DB에 저장 using (var context = DbContextCreator.Create()) { for (int i = beforeSatisfactionCategoryCount; i < satisfactionCategories.Count; ++i) { context.SatisfactionCategories.Add(satisfactionCategories[i]); } context.Satisfactions.AddRange(satisfactions); context.SaveChanges(); } } else if ((int)e.Argument == 3) { // Entity에 값 넣기 List <Electricity> electricities = new List <Electricity>(); for (int i = 0; i < rows.Count; ++i) { Electricity electricity = new Electricity(); double dateNumber = 0; dateNumber = double.Parse(rows[i][0]); if (dateNumber > 60d) { dateNumber = dateNumber - 2; } else { dateNumber = dateNumber - 1; } DateTime dateTime = new DateTime(1900, 1, 1); dateTime = dateTime.AddDays(dateNumber); electricity.Month = dateTime; electricity.Usage = int.Parse(rows[i][1]); electricity.Bill = int.Parse(rows[i][2]); electricities.Add(electricity); bgwInsert.ReportProgress(0, i.ToString()); } // Entity를 DB에 저장하기 using (var context = DbContextCreator.Create()) { //foreach (Electricity electricity in electricities) context.Electricities.AddRange(electricities); context.SaveChanges(); } } else if ((int)e.Argument == 4) { // Satisfaction List <Satisfaction> satisfactions = Dao.Satisfaction.GetAll(); List <Satisfaction> newSatisfactions = new List <Satisfaction>(); for (int i = 0; i < satisfactions.Count; i++) { List <int> randomNumbers = new List <int>(); int sum = 0; for (int j = 0; j < 5; j++) { randomNumbers.Add(random.Next(1500, 2000)); sum += randomNumbers[j]; } Satisfaction satisfaction = new Satisfaction() { SatisfactionCategoryId = satisfactions[i].SatisfactionCategoryId, Excellent = (decimal)((randomNumbers[0] * 100.0) / (double)sum), Good = (decimal)((randomNumbers[1] * 100.0) / (double)sum), Soso = (decimal)((randomNumbers[2] * 100.0) / (double)sum), Bad = (decimal)((randomNumbers[3] * 100.0) / (double)sum), Terrible = (decimal)((randomNumbers[4] * 100.0) / (double)sum) }; newSatisfactions.Add(satisfaction); } using (var context = DbContextCreator.Create()) { context.Satisfactions.AddRange(newSatisfactions); context.SaveChanges(); } } }