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();
}
