public void PopulateAverageService(String filePath, int year) { try { using (DataScienceContext context = new DataScienceContext()) { using (TextFieldParser parser = new TextFieldParser(filePath)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); int yearID = (from u in context.Years where u.Year == year select u.YearID).FirstOrDefault(); while (!parser.EndOfData) { //Process row int[] indices = new int[] { 0, 1 }; IEnumerable <String> columns = parser.ReadFields().Select((field, index) => new { field, index }).Where(fi => indices.Contains(fi.index)).Select(fi => fi.field); String agencyCode = columns.ElementAtOrDefault(0).Trim().Substring(0, 4); String serviceCount = columns.ElementAtOrDefault(1).Replace("%", "").Trim(); double percentService = 0; int agencyID = (from u in context.Agency where u.AgencyCode.Equals(agencyCode) && u.YearID == yearID select u.AgencyID).FirstOrDefault(); if (agencyID != 0) { var employment = (from u in context.Employment where u.AgencyID == agencyID && u.YearID == yearID select u).FirstOrDefault(); if (employment != null) { if (serviceCount.Equals("NA")) { percentService = 0; } else { percentService = Convert.ToDouble(serviceCount); } employment.AverageService = Convert.ToInt32(percentService); } } } } context.SaveChanges(); } } catch (DbEntityValidationException ex) { new DBErrorLogging().DumpDBException(ex); } catch (Exception ex) { Debug.WriteLine(ex); Debug.WriteLine(ex.StackTrace); } }
public void PopulateAttritionNumbers(string filePath, int year) { try { using (DataScienceContext context = new DataScienceContext()) { using (TextFieldParser parser = new TextFieldParser(filePath)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); int yearID = (from u in context.Years where u.Year == year select u.YearID).FirstOrDefault(); while (!parser.EndOfData) { //Process row int[] indices = new int[] { 0, 1, 2 }; IEnumerable <String> columns = parser.ReadFields().Select((field, index) => new { field, index }).Where(fi => indices.Contains(fi.index)).Select(fi => fi.field); String agencyCode = columns.ElementAtOrDefault(0).Trim().Substring(0, 4); String transfer = columns.ElementAtOrDefault(1).Trim(); String quit = columns.ElementAtOrDefault(2).Trim(); int transferCount = 0; int quitCount = 0; double totalLeaveCount = 0; double attritionRate = 0; if (!agencyCode.Equals("Coun") && !transfer.Equals("Transfer Out - Individual Transfer") && !quit.Equals("Quit")) { transferCount = Convert.ToInt32(transfer); quitCount = Convert.ToInt32(quit); totalLeaveCount = (transferCount += quitCount); } int agencyID = (from u in context.Agency where u.AgencyCode.Equals(agencyCode) && u.YearID == yearID select u.AgencyID).FirstOrDefault(); if (agencyID != 0) { var employment = (from u in context.Employment where u.AgencyID == agencyID && u.YearID == yearID select u).FirstOrDefault(); if (employment != null) { if (totalLeaveCount != 0) { double employeeCount = employment.EmployeeCount; attritionRate = (totalLeaveCount / employeeCount) * 100; employment.QuitCount = Convert.ToInt32(totalLeaveCount); employment.AttritionRate = Convert.ToInt32(attritionRate); } else { employment.QuitCount = 0; employment.AttritionRate = 0; } } } } } context.SaveChanges(); } } catch (DbEntityValidationException ex) { new DBErrorLogging().DumpDBException(ex); } catch (Exception ex) { Debug.WriteLine(ex); Debug.WriteLine(ex.StackTrace); } }
public void PopulateEmploymentNumbers(string filePath, int yearNumber) { try { using (DataScienceContext context = new DataScienceContext()) { using (TextFieldParser parser = new TextFieldParser(filePath)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); //add new year if there is one if (!context.Years.Any(x => x.Year == yearNumber)) { Years years = new Years() { Year = yearNumber }; context.Years.Add(years); context.SaveChanges(); } int yearID = (from u in context.Years where u.Year == yearNumber select u.YearID).FirstOrDefault(); while (!parser.EndOfData) { //Process row int[] indices = new int[] { 0, 1 }; IEnumerable <String> columns = parser.ReadFields().Select((field, index) => new { field, index }).Where(fi => indices.Contains(fi.index)).Select(fi => fi.field); String agencyName = columns.ElementAtOrDefault(0).Trim().Remove(0, 5); String agencyCode = columns.ElementAtOrDefault(0).Trim().Substring(0, 4); String employees = columns.ElementAtOrDefault(1).Trim(); int employeeCount = 0; if (!employees.Equals("NA") && !employees.Equals("Employment") && !employees.Equals("Total")) { employeeCount = Convert.ToInt32(employees); } //add agency code if not exists if (!String.IsNullOrWhiteSpace(agencyCode) && !String.IsNullOrWhiteSpace(agencyName) && employeeCount != 0 && !agencyCode.Equals("Agen") && !agencyName.Equals("UNSPECIFIED")) { //prevent duplicates var agencyCheck = (from u in context.Agency where u.AgencyName.Equals(agencyName) && u.AgencyCode.Equals(agencyCode) && u.YearID == yearID select u.AgencyID).FirstOrDefault(); if (agencyCheck == 0) { Agency agency = new Agency() { AgencyName = agencyName, AgencyCode = agencyCode, YearID = yearID, }; context.Agency.Add(agency); context.SaveChanges(); } } int agencyID = (from u in context.Agency where u.AgencyName.Equals(agencyName) && u.YearID == yearID select u.AgencyID).FirstOrDefault(); if (agencyID != 0) { //prevent duplicates var employmentCheck = (from u in context.Employment where u.AgencyID == agencyID && u.YearID == yearID && employeeCount != 0 select u.EmploymentID).FirstOrDefault(); if (employmentCheck == 0) { Employment employment = new Employment() { AgencyID = agencyID, EmployeeCount = employeeCount, QuitCount = null, AttritionRate = null, AverageSalary = null, Education = null, AverageService = null, Sex = null, YearID = yearID }; context.Employment.Add(employment); context.SaveChanges(); } else { var employment = (from u in context.Employment where u.AgencyID == agencyID && u.YearID == yearID select u).FirstOrDefault(); if (employment != null) { employment.EmployeeCount += employeeCount; } } } } } context.SaveChanges(); } } catch (DbEntityValidationException ex) { new DBErrorLogging().DumpDBException(ex); } catch (Exception ex) { Debug.WriteLine(ex); Debug.WriteLine(ex.StackTrace); } }
public void PopulateSurveyUtils(String filePath, int yearNumber) { try { using (DataScienceContext context = new DataScienceContext()) { using (TextFieldParser parser = new TextFieldParser(filePath)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); //add new year if there is one if (!context.Years.Any(x => x.Year == yearNumber)) { Years years = new Years() { Year = yearNumber }; context.Years.Add(years); context.SaveChanges(); } int yearID = (from u in context.Years where u.Year == yearNumber select u.YearID).FirstOrDefault(); int[] indices = new int[74]; //account for all columns for (int i = 1; i < 74; i++) { indices[i] = i; } while (!parser.EndOfData) { IEnumerable <String> columns = parser.ReadFields().Select((field, index) => new { field, index }).Where(fi => indices.Contains(fi.index)).Select(fi => fi.field); String agencyCode = columns.ElementAtOrDefault(1).Trim(); int agencyID = (from u in context.Agency where u.AgencyCode.Equals(agencyCode) select u.AgencyID).FirstOrDefault(); if (agencyID != 0) { //prevent duplicates if (!context.Survey.Any(x => x.AgencyID == agencyID && x.YearID == yearID)) { for (int i = 3; i < 74; i++) { int questionNumber = i - 2; //fill in Survey table with responses Survey survey = new Survey() { QuestionNumber = questionNumber, AgencyID = agencyID, YearID = yearID, ResponseValue = Convert.ToDouble(columns.ElementAtOrDefault(i).Trim()) }; context.Survey.Add(survey); } //save survey data per agency context.SaveChanges(); } } } } } } catch (DbEntityValidationException ex) { new DBErrorLogging().DumpDBException(ex); } catch (Exception ex) { Debug.WriteLine(ex); Debug.WriteLine(ex.StackTrace); } }