private bool GetActivityNames() { try { IWorkbook workbook = spreadsheetControl1.Document; WorksheetCollection worksheets = workbook.Worksheets; for (int i = 0; i < worksheets.Count - 1; i++) { var worksheetx = workbook.Worksheets[i]; string[] dateFull = worksheetx.Name.Split(' '); string monthName = dateFull[0].Trim(); string yearName = dateFull[1].Trim(); int startNamesRow = 0; int endNamesRow = 0; int startActivitiesRow = 0; int endActivititiesRow = 0; int maxColumn = 0; Dictionary <int, int> dateDictionary = new Dictionary <int, int>(); workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[i]; int x = 0; bool breakLoop = true; while (breakLoop) { var cellTemp = worksheetx[x, 0]; DateTime date; if (cellTemp.Value.ToString().Trim().ToLower().Contains("name") || DateTime.TryParse(cellTemp.Value.ToString().Trim(), out date)) { startNamesRow = x; x++; while (breakLoop) { cellTemp = worksheetx[x, 0]; if (cellTemp.Value.IsEmpty) { endNamesRow = x; x++; while (breakLoop) { cellTemp = worksheetx[x, 0]; if (cellTemp.Value.IsEmpty) { x++; } else { startActivitiesRow = x; x++; while (breakLoop) { cellTemp = worksheetx[x, 0]; if (cellTemp.Value.IsEmpty) { endActivititiesRow = x; breakLoop = false; } x++; } } } } x++; } } x++; } breakLoop = true; int z = 0; while (breakLoop) { var cellTemp = worksheetx[startNamesRow, z]; if (cellTemp.Value.ToString().Trim().ToLower().Contains("total") || cellTemp.Value.IsEmpty) { maxColumn = z; breakLoop = false; } z++; } for (int b = 1; b < maxColumn; b++) { var cellTemp = worksheetx[startNamesRow, b]; int date; string resultString = Regex.Match(cellTemp.Value.ToString(), @"\d+").Value; if (int.TryParse(resultString, out date)) { dateDictionary.Add(b, date); } } if (startNamesRow > 0) { for (int y = 0; y < 26; y++) { for (int w = 0; w < startNamesRow; w++) { var cellValue = worksheetx[w, y]; AddActivity(cellValue); } } } else { for (int j = startActivitiesRow; j < endActivititiesRow; j++) { var cellValue = worksheetx[j, 0]; AddActivity(cellValue); } } var cellPrevious = worksheetx[0, 0]; for (int startNames = startNamesRow + 1; startNames < endNamesRow; startNames++) { string[] nameValue = worksheetx[startNames, 0].Value.ToString().Trim().Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries); string firstName = ""; string lastName = ""; var cellValue = worksheetx[startNames, 0]; spreadsheetControl1.ActiveWorksheet.ScrollTo(worksheetx[startNames - 1, 0]); var backColor = cellValue.FillColor; cellPrevious.Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin); cellPrevious.FillColor = backColor; cellValue.Borders.SetAllBorders(Color.LightBlue, BorderLineStyle.DashDot); cellValue.Fill.BackgroundColor = Color.Red; cellPrevious = cellValue; firstName = nameValue[0].Trim(); if (nameValue.Count() > 1) { lastName = nameValue[1].Trim(); } using (var context = new LorikeetAppEntities()) { var allMemberNames = context.Members.ToList(); if (allMemberNames.Any()) { if (lastName.Equals("")) { var check = (from m in context.Members where m.FirstName == firstName select m).ToList(); if (check.Count() > 1) { DialogResult dr = new DialogResult(); var getMembers = new FormChooseMember(check, firstName + " " + lastName); dr = getMembers.ShowDialog(); if (dr == DialogResult.OK) { cellValue.SetValueFromText(getMembers.memberName); } } else if (check.Count() == 0) { List <string> diceNames = new List <string>(); foreach (var m in allMemberNames) { double dice = firstName.FuzzyMatch(m.FirstName); if (dice >= 0.8) { diceNames.Add(m.FirstName + " " + m.Surname); } } if (diceNames.Count != 1) { DialogResult dr = new DialogResult(); var getMembers = new FormChooseMember(diceNames, firstName + " " + lastName); dr = getMembers.ShowDialog(); if (dr == DialogResult.OK) { cellValue.SetValueFromText(getMembers.memberName); } } } } else { var checkFullName = (from member in allMemberNames where member.FirstName == firstName && (member.Surname.StartsWith(lastName) || member.Surname == lastName) select member).ToList(); if (checkFullName.Count() > 1) { DialogResult dr = new DialogResult(); var getMembers = new FormChooseMember(checkFullName, firstName + " " + lastName); dr = getMembers.ShowDialog(); if (dr == DialogResult.OK) { cellValue.SetValueFromText(getMembers.memberName); } } if (checkFullName.Count() == 0) { List <string> diceNames = new List <string>(); foreach (var m in allMemberNames) { double dice = (firstName + " " + lastName).FuzzyMatch((m.FirstName + " " + m.Surname)); if (dice >= 0.8) { diceNames.Add(m.FirstName + " " + m.Surname); } } if (diceNames.Count > 1 || diceNames.Count == 0) { DialogResult dr = new DialogResult(); var getMembers = new FormChooseMember(diceNames, firstName + " " + lastName); dr = getMembers.ShowDialog(); if (dr == DialogResult.OK) { cellValue.SetValueFromText(getMembers.memberName); } } } } } else { MessageBox.Show("There are no members in the member DataBase"); return(false); } } spreadsheetControl1.SaveDocument(); } for (int startNames = startNamesRow + 1; startNames < endNamesRow; startNames++) { var namecellName = worksheetx[startNames, 0]; for (int c = 1; c < maxColumn; c++) { var cellValue = worksheetx[startNames, c]; if (!cellValue.Value.IsEmpty) { var backColor = cellValue.FillColor; cellPrevious.Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin); cellPrevious.FillColor = backColor; cellValue.Borders.SetAllBorders(Color.LightBlue, BorderLineStyle.DashDot); cellValue.Fill.BackgroundColor = Color.Red; spreadsheetControl1.ActiveWorksheet.ScrollTo(worksheetx[startNames - 1, 0]); cellPrevious = cellValue; using (var context = new LorikeetAppEntities()) { char[] splitters = { ' ', ',' }; string removeSpace = cellValue.Value.ToString().Replace(", ", ","); string[] shortcutSplit = removeSpace.Split(splitters); for (int d = 0; d < shortcutSplit.Count(); d++) { var shortcut = shortcutSplit[d].Trim(); var checkIfActivityExists = (from a in context.Labels where a.Shortcut == shortcut select a).DefaultIfEmpty().First(); var activityLabel = ""; if (checkIfActivityExists == null) { activityLabel = addActivity(shortcut, ref cellValue); } else { activityLabel = checkIfActivityExists.DisplayName; } if (activityLabel != null) { DateTime tempDate; string[] dateFormat = { "dd/MMMM/yyyy", "d/MMMM/yyyy" }; int dayName = dateDictionary[c]; if (DateTime.TryParseExact(dayName + "/" + monthName + "/" + yearName, dateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out tempDate)) { if (!checkIfAppointmentExists(activityLabel, tempDate)) { var getLabelID = (from a in context.Labels where a.DisplayName == activityLabel select a).FirstOrDefault(); if (getLabelID != null) { var appToAdd = new Appointment(); appToAdd.AllDay = false; appToAdd.Description = ""; appToAdd.EndDate = tempDate; appToAdd.Label = getLabelID.LabelID; appToAdd.Location = ""; appToAdd.ReminderInfo = ""; appToAdd.StartDate = tempDate; appToAdd.Status = 0; appToAdd.Subject = getLabelID.DisplayName; appToAdd.TimeZoneId = ""; appToAdd.Type = 0; context.Appointments.Add(appToAdd); context.SaveChanges(); } } int memberID = MiscStuff.GetMemberID(namecellName.Value.ToString()); int appointmentID = (from a in context.Appointments where DbFunctions.TruncateTime(a.StartDate) == DbFunctions.TruncateTime(tempDate) && a.Subject == activityLabel select a.UniqueID).FirstOrDefault(); if (appointmentID != -1 && memberID != -1) { var checkIfAppointmentMembersExists = (from a in context.AppointmentMembers where a.AppointmentsID == appointmentID && a.MemberID == memberID select a).FirstOrDefault(); if (checkIfAppointmentMembersExists == null) { var apptMemberToAdd = new AppointmentMember(); apptMemberToAdd.AppointmentsID = appointmentID; apptMemberToAdd.MemberID = memberID; context.AppointmentMembers.Add(apptMemberToAdd); context.SaveChanges(); } } var checkIfNoteExists = (from n in context.Notes where n.Date == tempDate && n.MemberID == memberID && n.Notes == "Member attended " + activityLabel + " group" select n).FirstOrDefault(); if (checkIfNoteExists == null) { var noteToAdd = new Note(); noteToAdd.Date = tempDate; noteToAdd.Editable = false; noteToAdd.MemberID = memberID; noteToAdd.Notes = "Member attended " + activityLabel + " group"; noteToAdd.StaffID = 1; context.Notes.Add(noteToAdd); context.SaveChanges(); } } } } } } } } spreadsheetControl1.SaveDocument(); } } catch (Exception ex) { MessageBox.Show(MiscStuff.GetAllMessages(ex)); return(false); } return(true); }
private void bbiSaveAndClose_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { int apptID = GetAppointmentID(); if (apptID != -1) { using (var context = new LorikeetAppEntities()) { try { var appointmentName = (from an in context.Appointments where an.UniqueID == apptID select an).First(); var appointmentsToRemove = (from atr in context.AppointmentMembers where atr.AppointmentsID == apptID select atr).ToList(); var notesToRemove = (from ntr in context.Notes join a in context.Members on ntr.MemberID equals a.MemberID where DbFunctions.TruncateTime(ntr.Date) == DbFunctions.TruncateTime(apt.Start) && ntr.Notes == "Member attended " + apt.Subject + " group" && ntr.MemberID == a.MemberID select ntr).ToList(); if (appointmentsToRemove.Count > 0) { foreach (var atr in appointmentsToRemove) { context.AppointmentMembers.Remove(atr); context.SaveChanges(); } foreach (var ntr in notesToRemove) { context.Notes.Remove(ntr); context.SaveChanges(); } } foreach (var m in membersToAdd) { var memberAppointmentTemp = new AppointmentMember(); memberAppointmentTemp.AppointmentsID = apptID; memberAppointmentTemp.MemberID = m.MemberID; context.AppointmentMembers.Add(memberAppointmentTemp); context.SaveChanges(); var notesTemp = new Note(); notesTemp.Date = appointmentName.StartDate.Value; notesTemp.MemberID = m.MemberID; notesTemp.StaffID = 0; notesTemp.Notes = "Member attended " + appointmentName.Subject + " group"; context.Notes.Add(notesTemp); context.SaveChanges(); } var activity = (from an in context.AppointmentsNumbers join l in context.Labels on an.LabelID equals l.LabelID where DbFunctions.TruncateTime(an.Date) == DbFunctions.TruncateTime(apt.Start) && l.DisplayName == apt.Subject select an).DefaultIfEmpty().First(); var labelID = (from l in context.Labels where l.DisplayName == apt.Subject select l).DefaultIfEmpty().First(); if (activity != null) { activity.Number = membersToAdd.Count(); } else { if (labelID != null) { var activityNumberToAdd = new AppointmentsNumber(); activityNumberToAdd.Date = this.apt.Start; activityNumberToAdd.LabelID = labelID.LabelID; activityNumberToAdd.Number = membersToAdd.Count(); context.AppointmentsNumbers.Add(activityNumberToAdd); } } context.SaveChanges(); Logging.AddLogEntry(staffID, Logging.ErrorCodes.Broadcast, Logging.RefreshCodes.Schedule, "Changed Appointments in Schedule", false); } catch (Exception ex) { Logging.AddLogEntry(staffID, Logging.ErrorCodes.Error, Logging.RefreshCodes.None, "Tried Changing Appointments in Schedule - Error - " + ex.Message, false); MessageBox.Show(MiscStuff.GetAllMessages(ex)); } } } this.Close(); }