private static void SavePatientFromFile(Excel.Worksheet worksheet) { int index = 0; using (dsaEntities model = new dsaEntities()) { foreach (Excel.Range row in worksheet.UsedRange.Rows) { if (index == 0) { index++; continue; } string lastName = (worksheet.Cells[row.Row, 1].Value2 ?? "").ToString(); string firstName = (worksheet.Cells[row.Row, 2].Value2 ?? "").ToString(); var patient = model.Patients.FirstOrDefault(item => item.name == firstName && item.surname == lastName); if (patient == null) { patient = new Patient() { name = firstName, surname = lastName }; model.Patients.AddObject(patient); } var birthValue = worksheet.Cells[row.Row, 3].Value2; if (birthValue != null) { patient.BirthDate = DateTime.FromOADate(birthValue); } if (worksheet.Cells[row.Row, 4].Value2 != null) { patient.City = (worksheet.Cells[row.Row, 4].Value2).ToString(); } patient.Street = worksheet.Cells[row.Row, 5].Value2 != null ? (worksheet.Cells[row.Row, 5].Value2).ToString() : ""; patient.StreetNumber = (worksheet.Cells[row.Row, 6].Value2) != null ? (worksheet.Cells[row.Row, 6].Value2).ToString() : ""; patient.Ocupation = (worksheet.Cells[row.Row, 7].Value2) != null ? (worksheet.Cells[row.Row, 7].Value2).ToString() : ""; patient.Phone = (worksheet.Cells[row.Row, 8].Value2) != null ? (worksheet.Cells[row.Row, 8].Value2).ToString() : ""; patient.Email = (worksheet.Cells[row.Row, 9].Value2) != null ? (worksheet.Cells[row.Row, 9].Value2).ToString() : ""; } model.SaveChanges(); } }
private static void SaveInterventionFromXls(Excel.Worksheet worksheet) { int index = 0; using (dsaEntities model = new dsaEntities()) { DateTime biggerDate = LocalCache.Instance.ProgramInfo.LastInterventionAddedTime; foreach (Excel.Range row in worksheet.UsedRange.Rows) { var intervention = new Intervention(); if (index == 0) { index++; continue; } intervention.DateHourDetail = new DateHourDetail(); if (worksheet.Cells[row.Row, 1].Value2 == null) { break; } DateTime interventionDate; var dateValue = (worksheet.Cells[row.Row, 1].Value2).ToString(); int pointApps = 0; foreach (char c in dateValue) { if (c == '.') { pointApps++; } } interventionDate = pointApps > 1 ? DateTime.ParseExact(dateValue.Replace(".", "/"), "d/M/yyyy", CultureInfo.InvariantCulture) : DateTime.FromOADate(Convert.ToDouble(dateValue)); DateTime interventionStartHour = DateTime.FromOADate(worksheet.Cells[row.Row, 6].Value2); if (LocalCache.Instance.ProgramInfo.LastInterventionAddedTime < interventionStartHour) { var inteventionYear = model.Years.FirstOrDefault(item => item.YearNb == interventionDate.Year); if (inteventionYear == null) { inteventionYear = new Year() { YearNb = interventionDate.Year }; model.Years.AddObject(inteventionYear); model.SaveChanges(); } intervention.DateHourDetail.Date = interventionDate; if (interventionStartHour > biggerDate) { biggerDate = interventionStartHour; } string allPatientName = (worksheet.Cells[row.Row, 2].Value2).ToString(); int spaceOcc = allPatientName.IndexOf(" "); var firstName = spaceOcc > 0 ? allPatientName.Substring(spaceOcc + 1, allPatientName.Length - spaceOcc - 1) : allPatientName; var lastName = spaceOcc > 0 ? allPatientName.Substring(0, spaceOcc) : ""; var patientToAddTo = model.Patients.FirstOrDefault(item => item.name == firstName && item.surname == lastName); if (patientToAddTo == null) { patientToAddTo = new Patient() { name = firstName, surname = lastName }; model.Patients.AddObject(patientToAddTo); } intervention.Patient = patientToAddTo; if ((worksheet.Cells[row.Row, 3].Value2) != null) { string location = (worksheet.Cells[row.Row, 3].Value2).ToString(); intervention.Location = model.Locations.FirstOrDefault(item => item.Name == location); } if ((worksheet.Cells[row.Row, 4].Value2) != null) { string work = (worksheet.Cells[row.Row, 4].Value2).ToString(); intervention.Work = model.Works.FirstOrDefault(item => item.Name == work); } if ((worksheet.Cells[row.Row, 5].Value2) != null) { string area = (worksheet.Cells[row.Row, 5].Value2).ToString(); var areaToMapTo = model.Areas.FirstOrDefault(item => item.Name == area); intervention.Area = areaToMapTo; } intervention.DateHourDetail.StartHour = interventionStartHour; TimeSpan timespan = TimeSpan.FromMinutes(Convert.ToInt64(worksheet.Cells[row.Row, 7].Value2)); intervention.DateHourDetail.Duration = timespan.Ticks; intervention.DateHourDetail.EndingHour = DateTime.FromOADate(worksheet.Cells[row.Row, 8].Value2); intervention.Month = model.Months.FirstOrDefault(item => item.MOnthNumber == interventionDate.Month); intervention.Year = inteventionYear; intervention.Revenue = Convert.ToDouble(worksheet.Cells[row.Row, 9].Value2); intervention.Percent = Convert.ToDouble((intervention.Revenue) * (intervention.Work != null && intervention.Work.Percent != 0 ? intervention.Work.Percent / 100 : 1)); model.Interventions.AddObject(intervention); } } LocalCache.Instance.ProgramInfo.LastInterventionAddedTime = biggerDate; var progInfo = model.ProgramInfoes.FirstOrDefault(); progInfo.LastAddedInterventionDate = biggerDate; model.SaveChanges(); } }