private void subButton_Click(object sender, EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
int test = 0;
int r = 0;
try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = false;
oWB = oXL.Workbooks.Open("ImportData.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oRng = oSheet.UsedRange;
rows = oRng.Rows.Count;
for (r = 2; r <= rows; r++)
{
try
{
//studentID = oRng.Cells[r, 1] + "";
studentID = int.Parse(oRng.Cells[r, 1].Value.ToString());
}
catch
{
oWB.Close(false);
//MessageBox.Show("error");
MessageBox.Show("There is an error in the Student ID. Please make sure there is a number.");
this.Close();
return;
}
test = 1;
students = Model.getStudentIDs();
bool isAlreadyStudent = false;
foreach (string a in students)
{
if (a.TrimStart('0').Equals(studentID.ToString()))
{
isAlreadyStudent = true;
}
}
if (isAlreadyStudent)
{
;
}
else
{
try
{
firstName = oSheet.Cells[r, 2].Value.ToString();
}
catch
{
MessageBox.Show("Please provide a first name");
return;
}
try
{
lastName = oSheet.Cells[r, 3].Value.ToString();
}
catch
{
MessageBox.Show("Please provide a last name");
return;
}
}
try
{
gender = oSheet.Cells[r, 4].Value.ToString();
} catch
{
gender = " ";
}
try
{
race = oSheet.Cells[r, 5].Value.ToString();
} catch
{
race = " ";
}
try
{
gpa = float.Parse((string)oSheet.Cells[r, 6].Value.ToString());
}
catch
{
try
{
gpa = float.Parse((string)oSheet.Cells[r, 7].Value.ToString());
gpa = Model.convertGPA(gpa);
}
catch
{
gpa = 0;
}
}
try
{
string gradeDate = oRng.Cells[r, 8].Value.ToString();
string[] gradeDate2 = gradeDate.Split(' ');
gradeDate = gradeDate2[0];
gradeDate2 = gradeDate.Split('/');
string month = gradeDate2[0];
string day = gradeDate2[1];
string year = gradeDate2[2];
// double d = double.Parse((string) oRng.Cells[r, 8].Value.ToString());
// MessageBox.Show(d.ToString() + "grademod");
// DateTime conv = DateTime.FromOADate(d);
//String year = conv.Year.ToString();
//String month = conv.Month.ToString();
//String day = conv.Day.ToString();
gradeMod = year + '-' + month + '-' + day;
}
catch
{
DateTime date = DateTime.Now;
String year = date.Year.ToString();
String month = date.Month.ToString();
String day = date.Day.ToString();
gradeMod = year + '-' + month + '-' + day;
}
try
{
daysAbsent = int.Parse((string)oSheet.Cells[r, 9].Value.ToString());
} catch
{
daysAbsent = 0;
}
try
{
numRefs = int.Parse((string)oSheet.Cells[r, 10].Value.ToString());
} catch
{
numRefs = 0;
}
try
{
school = oSheet.Cells[r, 11].Value.ToString();
} catch
{
school = " ";
}
String y = "";
String m = "";
String da = "";
try
{
string gradeDate = oRng.Cells[r, 12].Value.ToString();
string[] gradeDate2 = gradeDate.Split(' ');
gradeDate = gradeDate2[0];
gradeDate2 = gradeDate.Split('/');
m = gradeDate2[0];
da = gradeDate2[1];
y = gradeDate2[2];
regDate = y + '-' + m + '-' + da;
}
catch
{
DateTime date = DateTime.Now;
String year = date.Year.ToString();
String month = date.Month.ToString();
String day = date.Day.ToString();
gradeMod = year + '-' + month + '-' + day;
}
try
{
grade = int.Parse((string)oSheet.Cells[r, 13].Value.ToString());
} catch
{
grade = 0;
}
students = Model.getStudentIDs();
isAlreadyStudent = false;
foreach (string a in students)
{
if (a.TrimStart('0').Equals(studentID.ToString()))
{
isAlreadyStudent = true;
}
}
if (isAlreadyStudent)
{
try
{
Model.UpdateStudentTable(studentID.ToString(), grade.ToString(), gradeMod, regDate, gender, race, daysAbsent.ToString());
Model.InsertCumGPA(studentID, gpa, gradeMod);
DateTime date = DateTime.Now;
String year = date.Year.ToString();
String month = date.Month.ToString();
String day = date.Day.ToString();
string curDate = year + '-' + month + '-' + day;
Model.InsertEmptyReferrals(studentID, curDate, numRefs);
}
catch
{
MessageBox.Show("SQL update didn't work");
oWB.Close(false);
}
}
else
{
try
{
Model.InsertStudent(firstName, lastName, studentID, gpa, school, grade, numRefs,
daysAbsent, gender, race, "yes", regDate, gradeMod);
DateTime date = DateTime.Now;
String year = date.Year.ToString();
String month = date.Month.ToString();
String day = date.Day.ToString();
string curDate = year + '-' + month + '-' + day;
List <String> schools = Model.getSchools();
if (!schools.Contains(school))
{
Model.InsertSchool(school);
}
Model.InsertAttends(curDate, studentID, school);
Model.InsertCumGPA(studentID, gpa, gradeMod);
Model.InsertEmptyReferrals(studentID, curDate, numRefs);
}
catch
{
MessageBox.Show("sql insert didn't work");
oWB.Close(false);
}
}
}
}
catch (Exception ex)
{
string errorMessage;
errorMessage = "Error: ";
errorMessage = string.Concat(errorMessage, ex.Message);
errorMessage = string.Concat(errorMessage, " Line: ");
errorMessage = string.Concat(errorMessage, ex.Source);
errorMessage = string.Concat(errorMessage, ex.StackTrace);
MessageBox.Show(errorMessage);
//oWB.Close(false);
}
this.Close();
}
private void AddStudentSubmit_Click_1(object sender, EventArgs e)
{
String firstName;
String lastName;
if (addFirstName.Text != "" && addLastName.Text != "")
{
firstName = addFirstName.Text;
lastName = addLastName.Text;
}
else
{
MessageBox.Show("Please enter a name for the student!");
return;
}
int studentID;
if (addStudentID.Text != "")
{
try
{
studentID = int.Parse(addStudentID.Text);
} catch
{
MessageBox.Show("Something went wrong in reading the ID of the student." +
"\nPlease make sure that there is a number in the ID field.");
return;
}
}
else
{
MessageBox.Show("Please enter an ID for the student!");
return;
}
if (Model.StudentExists(studentID.ToString()))
{
MessageBox.Show("A student with this ID already exists in the database!");
return;
}
Double gpa;
if (AddGPA.Text != "")
{
try
{
gpa = Double.Parse(AddGPA.Text);
} catch
{
MessageBox.Show("Something went wrong in reading the GPA of the student." +
"\nPlease make sure that there is a number in the GPA field.");
return;
}
}
else
{
MessageBox.Show("Please enter a GPA on a 4.0 scale!");
return;
}
String school = addSchool.Text;
int grade;
if (AddGrade.Text != "")
{
try
{
grade = int.Parse(AddGrade.Text);
}
catch
{
MessageBox.Show("Something went wrong in reading the grade level of the student." +
"\nPlease make sure that there is a number in the grade level.");
return;
}
}
else
{
MessageBox.Show("There is nothing in the Grade Level blank!");
return;
}
int numRefs;
if (AddBehavior.Text != "")
{
numRefs = int.Parse(AddBehavior.Text);
}
else
{
numRefs = 0;
}
int daysMissed;
if (AddAttend.Text != "")
{
daysMissed = int.Parse(AddAttend.Text);
}
else
{
daysMissed = 0;
}
String gender = AddGender.Text;
String race = raceBox.Text;
String curStudent = AddCurrent.Text;
DateTime date = DateTime.Now;
String year = date.Year.ToString();
String month = date.Month.ToString();
String day = date.Day.ToString();
String curDay = year + '-' + month + '-' + day;
Model.InsertStudent(firstName, lastName, studentID, gpa, school, grade, numRefs, daysMissed, gender, race, curStudent, curDay, curDay);
Model.InsertAttends(curDay, studentID, school);
Model.InsertCumGPA(studentID, (float)gpa, curDay);
Model.InsertEmptyReferrals(studentID, curDay, numRefs);
this.Close();
}