private static string ToEnrollingSchool(this registration row)
{
return(string.Format("<div>{0}</div><div>{1}</div>", row.schoolid.HasValue?row.school.name:"", row.schoolyearid.HasValue?row.school_year.name:""));
}
var(registration, callback) = x;
private static string ToEnrolGridActions(this registration row)
{
return(string.Format(
"<a class='jqedit' href='/enrolment/edit/{0}'>edit</a><a class='jqdelete' href='#'>del</a>",
row.id));
}
private ActionResult ImportStudent(bool?commit)
{
var fs = System.IO.File.OpenRead(AppDomain.CurrentDomain.BaseDirectory + "/Content/media/student.xls");
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.GetSheet("student");
var count = 1; // skips header
try
{
while (true)
{
var row = sheet.GetRow(count++);
if (row == null)
{
break;
}
var name = GetCellValueAsString(row.GetCell(1, MissingCellPolicy.RETURN_NULL_AND_BLANK));
if (string.IsNullOrEmpty(name))
{
continue;
}
var leavingDate = GetCellValueAsString(row.GetCell(2, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var leavingState = GetCellValueAsString(row.GetCell(3, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var currentYear = GetCellValueAsString(row.GetCell(4, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var dob = GetCellValueAsString(row.GetCell(5, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var nricpassport = GetCellValueAsString(row.GetCell(6, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var admissiondate = GetCellValueAsString(row.GetCell(7, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var schoolclass = GetCellValueAsString(row.GetCell(11, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var rank = GetCellValueAsString(row.GetCell(12, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var sex = GetCellValueAsString(row.GetCell(13, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var race = GetCellValueAsString(row.GetCell(14, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var nationality = GetCellValueAsString(row.GetCell(15, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var religion = GetCellValueAsString(row.GetCell(16, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var phone_house = GetCellValueAsString(row.GetCell(17, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var father_name = GetCellValueAsString(row.GetCell(18, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var father_nricpassport = GetCellValueAsString(row.GetCell(19, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var father_phone_cell = GetCellValueAsString(row.GetCell(20, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var father_phone_office = GetCellValueAsString(row.GetCell(21, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var father_occupation = GetCellValueAsString(row.GetCell(22, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var mother_name = GetCellValueAsString(row.GetCell(23, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var mother_nricpassport = GetCellValueAsString(row.GetCell(24, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var mother_phone_cell = GetCellValueAsString(row.GetCell(25, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var mother_phone_office = GetCellValueAsString(row.GetCell(26, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var mother_occupation = GetCellValueAsString(row.GetCell(27, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var address1 = GetCellValueAsString(row.GetCell(28, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var address2 = GetCellValueAsString(row.GetCell(29, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var postcode = GetCellValueAsString(row.GetCell(30, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var guardian_name = GetCellValueAsString(row.GetCell(31, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var guardian_nricpassport = GetCellValueAsString(row.GetCell(32, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var guardian_phone_hand = GetCellValueAsString(row.GetCell(33, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var guardian_phone_home = GetCellValueAsString(row.GetCell(34, MissingCellPolicy.RETURN_NULL_AND_BLANK));
var student = new user();
student.designation = "";
student.usergroup = (int)UserGroup.STUDENT;
student.name = name;
if (leavingState == "0")
{
student.settings = (int)UserSettings.INACTIVE;
}
else
{
student.settings = (int)UserSettings.NONE;
}
if (!string.IsNullOrEmpty(sex))
{
if (sex == "M")
{
student.gender = Gender.MALE.ToString();
}
else if (sex == "F")
{
student.gender = Gender.FEMALE.ToString();
}
else
{
return(Content("Unrecognised gender row " + count));
}
}
if (!string.IsNullOrEmpty(race))
{
if (race == "C")
{
student.race = "Chinese";
}
else
{
student.race = race;
}
}
if (!string.IsNullOrEmpty(nationality))
{
student.citizenship = nationality;
}
if (!string.IsNullOrEmpty(religion))
{
student.religion = religion;
}
student.dob = ParseDate(dob);
if (!string.IsNullOrEmpty(nricpassport))
{
switch (GetIDType(nricpassport))
{
case IDTYPE.PASSPORT:
student.passportno = nricpassport;
break;
case IDTYPE.NEWIC:
student.nric_new = nricpassport;
break;
case IDTYPE.BIRTHCERT:
student.birthcertno = nricpassport;
break;
}
}
var registration = new registration();
registration.admissionDate = ParseDate(admissiondate);
registration.leftDate = ParseDate(leavingDate);
student.registrations.Add(registration);
if (!string.IsNullOrEmpty(schoolclass))
{
var school_class = repository.GetSchoolClasses().SingleOrDefault(x => x.name == schoolclass);
if (school_class == null)
{
return(Content("unrecognised school: row " + count));
}
var student_class_allocated = new classes_students_allocated();
student_class_allocated.classid = school_class.id;
if (string.IsNullOrEmpty(currentYear))
{
student_class_allocated.year = 2011;
}
else
{
student_class_allocated.year = int.Parse(currentYear);
}
student.classes_students_allocateds.Add(student_class_allocated);
}
repository.AddUser(student);
var address = string.Concat(address1, Environment.NewLine, address2, Environment.NewLine, postcode);
user father = null;
if (!string.IsNullOrEmpty(father_name))
{
father = new user();
bool foundDuplicate = false;
if (!string.IsNullOrEmpty(father_nricpassport))
{
user found = null;
switch (GetIDType(father_nricpassport))
{
case IDTYPE.PASSPORT:
father.passportno = father_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.passportno == father_nricpassport);
if (found != null)
{
foundDuplicate = true;
father = found;
}
break;
case IDTYPE.NEWIC:
father.nric_new = father_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.nric_new == father_nricpassport);
if (found != null)
{
foundDuplicate = true;
father = found;
}
break;
case IDTYPE.BIRTHCERT:
father.birthcertno = father_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.birthcertno == father_nricpassport);
if (found != null)
{
foundDuplicate = true;
father = found;
}
break;
}
}
if (!foundDuplicate)
{
father.designation = "";
father.usergroup = (int)UserGroup.GUARDIAN;
father.user_parents = new user_parent();
father.settings = (int)UserSettings.NONE;
father.gender = Gender.MALE.ToString();
father.name = father_name;
father.address = address;
if (!string.IsNullOrEmpty(phone_house))
{
father.phone_home = phone_house;
}
if (!string.IsNullOrEmpty(father_phone_cell))
{
father.phone_cell = father_phone_cell;
}
if (!string.IsNullOrEmpty(father_phone_office))
{
father.user_parents.phone_office = father_phone_office;
}
if (!string.IsNullOrEmpty(father_occupation))
{
father.user_parents.occupation = father_occupation;
}
repository.AddUser(father);
}
}
user mother = null;
if (!string.IsNullOrEmpty(mother_name))
{
mother = new user();
bool foundDuplicate = false;
if (!string.IsNullOrEmpty(mother_nricpassport))
{
user found = null;
switch (GetIDType(mother_nricpassport))
{
case IDTYPE.PASSPORT:
mother.passportno = mother_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.passportno == mother_nricpassport);
if (found != null)
{
foundDuplicate = true;
mother = found;
}
break;
case IDTYPE.NEWIC:
mother.nric_new = mother_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.nric_new == mother_nricpassport);
if (found != null)
{
foundDuplicate = true;
mother = found;
}
break;
case IDTYPE.BIRTHCERT:
mother.birthcertno = mother_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.birthcertno == mother_nricpassport);
if (found != null)
{
foundDuplicate = true;
mother = found;
}
break;
}
}
if (!foundDuplicate)
{
mother.designation = "";
mother.usergroup = (int)UserGroup.GUARDIAN;
mother.user_parents = new user_parent();
mother.settings = (int)UserSettings.NONE;
mother.gender = Gender.FEMALE.ToString();
mother.name = mother_name;
mother.address = address;
if (!string.IsNullOrEmpty(phone_house))
{
mother.phone_home = phone_house;
}
if (!string.IsNullOrEmpty(mother_phone_cell))
{
mother.phone_cell = mother_phone_cell;
}
if (!string.IsNullOrEmpty(mother_phone_office))
{
mother.user_parents.phone_office = mother_phone_office;
}
if (!string.IsNullOrEmpty(mother_occupation))
{
mother.user_parents.occupation = mother_occupation;
}
repository.AddUser(mother);
}
}
user guardian = null;
if (!string.IsNullOrEmpty(guardian_name))
{
guardian = new user();
bool foundDuplicate = false;
if (!string.IsNullOrEmpty(guardian_nricpassport))
{
user found = null;
switch (GetIDType(guardian_nricpassport))
{
case IDTYPE.PASSPORT:
guardian.passportno = guardian_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.passportno == guardian_nricpassport);
if (found != null)
{
foundDuplicate = true;
guardian = found;
}
break;
case IDTYPE.NEWIC:
guardian.nric_new = guardian_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.nric_new == guardian_nricpassport);
if (found != null)
{
foundDuplicate = true;
guardian = found;
}
break;
case IDTYPE.BIRTHCERT:
guardian.birthcertno = guardian_nricpassport;
found =
repository.GetUsers().SingleOrDefault(x => x.birthcertno == guardian_nricpassport);
if (found != null)
{
foundDuplicate = true;
guardian = found;
}
break;
}
}
if (!foundDuplicate)
{
guardian.designation = "";
guardian.usergroup = (int)UserGroup.GUARDIAN;
guardian.user_parents = new user_parent();
guardian.name = guardian_name;
guardian.settings = (int)UserSettings.NONE;
guardian.address = address;
if (!string.IsNullOrEmpty(guardian_phone_home))
{
guardian.phone_home = guardian_phone_home;
}
if (!string.IsNullOrEmpty(guardian_phone_hand))
{
guardian.phone_cell = guardian_phone_hand;
}
repository.AddUser(guardian);
}
}
if (commit.HasValue && commit.Value)
{
// save new users
repository.Save();
// add relationship
if (father != null)
{
var f = new students_guardian();
f.parentid = father.id;
f.type = (byte)GuardianType.FATHER;
student.students_guardians.Add(f);
}
if (mother != null)
{
var m = new students_guardian();
m.parentid = mother.id;
m.type = (byte)GuardianType.MOTHER;
student.students_guardians.Add(m);
}
if (guardian != null)
{
var g = new students_guardian();
g.parentid = guardian.id;
g.type = (byte)GuardianType.GUARDIAN;
student.students_guardians.Add(g);
}
}
}
}
catch (Exception ex)
{
return(Content(count + ":" + ex.Message));
}
if (commit.HasValue && commit.Value)
{
repository.Save();
return(Content("commited rows " + count));
}
return(Content("done rows " + count));
}
partial void Deleteregistration(registration instance);
partial void Updateregistration(registration instance);
partial void Insertregistration(registration instance);
