//catch and record any database issues
public void DumpDBException(DbEntityValidationException eve)
{
try
{
foreach (var ve in eve.EntityValidationErrors)
{
using (DataScienceContext context = new DataScienceContext())
{
Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
ve.Entry.Entity.GetType().Name, ve.Entry.State);
foreach (var eee in ve.ValidationErrors)
{
Debug.WriteLine("Entity of type: " + ve.Entry.Entity.GetType().Name + " has the following validation errors: " + ve.Entry.State, "Property: " + eee.PropertyName + ", Error: " + eee.ErrorMessage);
Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
eee.PropertyName, eee.ErrorMessage);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
Debug.WriteLine(ex.StackTrace);
}
}
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);
}
}
