public static DataPackage AddPupilContactRelationship(this DataPackage dataPackage,
Guid pupilContactRelationshipId,
Guid learnerId,
Guid pupilContactId,
string relationshipType = "PAR",
int priority = 1,
bool hasParentalResponsibility = false,
bool receivesCorrespondance = false,
bool hasSchoolReport = false,
bool hasCourtOrder = false,
int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.LearnerContactRelationship, new
{
ID = pupilContactRelationshipId,
Priority = priority,
Learner = learnerId,
LearnerContact = pupilContactId,
LearnerContactRelationshipType = CoreQueries.GetLookupItem(Constants.Tables.LearnerContactRelationshipType, relationshipType),
HasParentalResponsibility = hasParentalResponsibility,
ReceivesCorrespondance = receivesCorrespondance,
HasCourtOrder = hasCourtOrder,
ReceivesSchoolReport = hasSchoolReport,
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage GetStaffRecord(this DataPackage dataPackage, Guid staffId, string forename, string surname)
{
//return this.BuildDataPackage()
dataPackage.AddData("Staff", new
{
Id = staffId,
LegalForename = forename,
LegalSurname = surname,
LegalMiddleNames = "Middle",
PreferredForename = forename,
PreferredSurname = surname,
DateOfBirth = new DateTime(2000, 1, 1),
Gender = CoreQueries.GetLookupItem("Gender", description: "Female"),
PolicyACLID = CoreQueries.GetPolicyAclId("Staff"),
School = CoreQueries.GetSchoolId(),
TenantID = tenantId
})
.AddData("StaffServiceRecord", new
{
Id = Guid.NewGuid(),
DOA = DateTime.Today.AddDays(-1),
ContinuousServiceStartDate = DateTime.Today.AddDays(-1),
LocalAuthorityStartDate = DateTime.Today.AddDays(-1),
Staff = staffId,
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddPupilContact(this DataPackage dataPackage,
Guid pupilContactId,
string surname,
string forename,
string salutation = null,
string addressee = null,
string jobTitle = null,
string titleCode = "Mr",
string genderCode = "1",
string occupation = "ACC",
int?tenantId = null
)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.LearnerContact, new
{
ID = pupilContactId,
Surname = surname,
Forename = forename,
School = CoreQueries.GetSchoolId(),
Gender = CoreQueries.GetLookupItem(Constants.Tables.Gender, code: genderCode),
Title = CoreQueries.GetLookupItem(Constants.Tables.Title, code: titleCode),
Salutation = salutation,
JobTitle = jobTitle,
Addressee = addressee,
Occupation = CoreQueries.GetLookupItem(Constants.Tables.Occupation, code: occupation),
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddBasicLearnerContactAddress(this DataPackage dataPackage, Guid learnerId, Guid learnerContactId, Guid addressId, Guid learnerContactAddressId, Guid learnerAddressId, DateTime startDate, int?tenantId = null, string postCode = null, string addressType = "H")
{
tenantId = tenantId ?? SeSugar.Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.Address, new
{
ID = addressId,
TenantID = tenantId,
})
.AddData(Constants.Tables.LearnerAddress, new
{
ID = learnerAddressId,
StartDate = startDate,
Learner = learnerId,
Address = addressId,
AddressType = CoreQueries.GetLookupItem(Constants.Tables.AddressType, code: addressType),
TenantID = tenantId
})
.AddData(Constants.Tables.LearnerContactAddress, new
{
ID = learnerContactAddressId,
StartDate = startDate,
LearnerContact = learnerContactId,
Address = addressId,
AddressType = CoreQueries.GetLookupItem(Constants.Tables.AddressType, code: addressType),
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddBasicApplicant(this DataPackage dataPackage,
Guid applicationId,
Guid learnerId,
Guid learnerEnrolmentId,
Guid admissionGroupId,
DateTime dateOfApplication,
string applicationStatus = "Applied",
string proposedEnrolStatus = "C",
int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
Guid applicationStatusId;
Guid applicationStatusLogId = Guid.NewGuid();
dataPackage.AddData(Constants.Tables.Application, new
{
ID = applicationId,
Learner = learnerId,
LearnerEnrolment = learnerEnrolmentId,
CurrentApplicationStatus = applicationStatusId = CoreQueries.GetLookupItem(Constants.Tables.ApplicationStatus, code: applicationStatus),
ProposedEnrolmentStatus = CoreQueries.GetLookupItem(Constants.Tables.EnrolmentStatus, code: proposedEnrolStatus),
DateOfApplication = dateOfApplication,
AdmissionGroup = admissionGroupId,
TenantID = tenantId
})
.AddData(Constants.Tables.ApplicationStatusLog, new
{
ID = applicationStatusLogId,
Application = applicationId,
TenantID = tenantId,
ApplicationStatus = applicationStatusId
});
return(dataPackage);
}
public static DataPackage AddSuspensionWithMeeting(this DataPackage dataPackage, Guid learnerId, Guid suspensionId, DateTime startDate, DateTime endDate, int days, Guid exclusionType, Guid exclusionReason, string tenantId = null)
{
tenantId = tenantId ?? TestDefaults.Default.TenantId.ToString();
dataPackage.AddData("LearnerExclusion", new
{
Id = suspensionId,
Learner = learnerId,
StartTime = "09:00",
EndTime = "16:00",
StartDate = startDate,
EndDate = endDate,
ExclusionType = exclusionType,
ExclusionReason = exclusionReason,
NumberOfSchoolDays = days,
SessionsMissed = days,
TenantID = tenantId
})
.AddData("ExclusionMeeting", new
{
Id = Guid.NewGuid(),
LearnerExclusion = suspensionId,
StartDate = startDate,
EndDate = endDate,
StartTime = "09:00",
EndTime = "16:00",
ExclusionMeetingType = CoreQueries.GetLookupItem("ExclusionMeetingType", description: "Discipline Committee"),
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddBasicStaff(this DataPackage dataPackage, Guid staffId, string forename, string surname)
{
return(dataPackage
.AddData("Staff", new
{
Id = staffId,
LegalForename = forename,
LegalSurname = surname,
LegalMiddleNames = "Middle",
PreferredForename = forename,
PreferredSurname = surname,
DateOfBirth = new DateTime(2000, 1, 1),
Gender = CoreQueries.GetLookupItem("Gender", description: "Male"),
PolicyACLID = CoreQueries.GetPolicyAclId("Staff"),
School = CoreQueries.GetSchoolId(),
TenantID = SeSugar.Environment.Settings.TenantId,
Title = CoreQueries.GetLookupItem("Title", description: "Mr"),
})
.AddData("StaffServiceRecord", new
{
Id = Guid.NewGuid(),
DOA = DateTime.Today.AddDays(-1),
ContinuousServiceStartDate = DateTime.Today.AddDays(-1),
LocalAuthorityStartDate = DateTime.Today.AddDays(-1),
Staff = staffId,
TenantID = SeSugar.Environment.Settings.TenantId
}));
}
public static DataPackage AddLeaver(this DataPackage dataPackage, Guid learnerId, string surname,
string forename, DateTime dateOfBirth, DateTime dateOfAdmission, DateTime dateOfLeaving, string genderCode = "1",
string enrolStatus = "C", string reasonForLeaving = "OT", string tenantId = null)
{
Guid learnerEnrolmentId;
var yearGroup = Queries.GetFirstYearGroup();
tenantId = tenantId ?? TestDefaults.Default.TenantId.ToString();
dataPackage.AddData("Learner", new
{
Id = learnerId,
School = CoreQueries.GetSchoolId(),
Gender = CoreQueries.GetLookupItem("Gender", code: genderCode),
LegalForename = forename,
LegalSurname = surname,
DateOfBirth = dateOfBirth,
PolicyAclId = CoreQueries.GetPolicyAclId("Learner"),
TenantID = tenantId
})
.AddData("LearnerEnrolment", new
{
Id = learnerEnrolmentId = Guid.NewGuid(),
School = CoreQueries.GetSchoolId(),
Learner = learnerId,
DOA = dateOfAdmission,
DOL = dateOfLeaving,
ReasonForLeaving = CoreQueries.GetLookupItem("ReasonForLeaving", code: reasonForLeaving),
TenantID = tenantId
})
.AddData("LearnerEnrolmentStatus", new
{
Id = Guid.NewGuid(),
LearnerEnrolment = learnerEnrolmentId,
StartDate = dateOfAdmission,
EndDate = dateOfLeaving,
EnrolmentStatus = CoreQueries.GetLookupItem("EnrolmentStatus", code: enrolStatus),
TenantID = tenantId
})
.AddData("LearnerYearGroupMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
YearGroup = yearGroup.ID,
StartDate = dateOfAdmission,
EndDate = dateOfLeaving,
TenantID = tenantId
})
.AddData("LearnerNCYearMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
SchoolNCYear = yearGroup.SchoolNCYear,
StartDate = dateOfAdmission,
EndDate = dateOfLeaving,
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddSENSStagetoLearner(this DataPackage dataPackage, Guid learnerId, string senStage = "1", int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData("LearnerSENStatus", new
{
Id = Guid.NewGuid(),
StartDate = new DateTime(2015, 05, 05),
Learner = learnerId,
SENStatus = CoreQueries.GetLookupItem("SENStatus", code: senStage),
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddStandardPupilLogNote(this DataPackage dataPackage, Guid learnerId, string title, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData("PupilLogNoteStandard", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
PupilLogNoteCategory = CoreQueries.GetLookupItem("PupilLogNoteCategory", code: "GENNA"),
Title = title,
NoteText = Utilities.GenerateRandomString(20, "Selenium"),
Pinned = false,
CreatedOn = new DateTime(2015, 01, 10),
TenantID = tenantId
});
return(dataPackage);
}
public static object GenerateStaff(out Guid id, string surname, Guid?employeeId = null, string forename = null)
{
return(new
{
ID = id = Guid.NewGuid(),
LegalForename = forename,
LegalSurname = surname,
LegalMiddleNames = "Middle Names",
PreferredForename = forename,
PreferredSurname = surname,
DateOfBirth = new DateTime(2000, 1, 1),
Gender = CoreQueries.GetLookupItem("Gender", description: "Male"),
PolicyACLID = CoreQueries.GetPolicyAclId("Staff"),
Employee = employeeId,
School = CoreQueries.GetSchoolId(),
TenantID = tenantId
});
}
public static DataPackage AddStaff(this DataPackage dataPackage, Guid staffId, Guid employeeId, string surname, string forename, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.Staff, new
{
ID = staffId,
LegalForename = forename,
LegalSurname = surname,
PreferredForename = forename,
PreferredSurname = surname,
DateOfBirth = new DateTime(2000, 1, 1),
Gender = CoreQueries.GetLookupItem("Gender", description: "Male"),
PolicyACLID = CoreQueries.GetPolicyAclId("Staff"),
Employee = employeeId,
School = CoreQueries.GetSchoolId(),
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddSchoolIntake(
this DataPackage dataPackage,
Guid schoolIntakeId,
string admissionYear,
string admissionTerm,
YearGroup yearGroup,
int numberOfPlannedAdmission,
string admissionGroupName,
DateTime dateOfAdmission,
int capacity,
Guid?admissionGroupId = null,
int?tenantId = null,
string schoolInTakeName = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData("SchoolIntake", new
{
Id = schoolIntakeId,
School = CoreQueries.GetSchoolId(),
Name = schoolInTakeName ?? admissionYear + " - " + admissionTerm + " " + yearGroup.FullName,
YearOfAdmission = Queries.GetAdmissionYear(admissionYear).ID,
AdmissionTerm = CoreQueries.GetLookupItem("AdmissionTerm", code: admissionTerm),
YearGroup = yearGroup.ID,
PlannedAdmissionNumber = numberOfPlannedAdmission,
IsActive = true,
TenantID = tenantId
})
.AddData("AdmissionGroup", new
{
Id = admissionGroupId ?? Guid.NewGuid(),
Name = admissionGroupName,
SchoolIntake = schoolIntakeId,
DateOfAdmission = dateOfAdmission,
Capacity = capacity,
IsActive = true,
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddBasicLearner(this DataPackage dataPackage, Guid learnerId, string surname, string forename, DateTime dateOfBirth, DateTime dateOfAdmission, string genderCode = "1", string enrolStatus = "C", Guid?uniqueLearnerEnrolmentId = null, int?tenantId = null, string salutation = null, string addressee = null, Guid?yearGroupId = null, Guid?schoolNCYearId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
string sql = String.Format(
"UPDATE School SET LastAdmissionNumber='B05000' " +
"WHERE TenantID='" + tenantId + "' " +
"AND IsRegistered=1 " +
"AND (LastAdmissionNumber IS NULL " +
"OR CAST(SUBSTRING(LastAdmissionNumber,2,10) AS INT) < 5000)");
DataAccessHelpers.Execute(sql);
Guid learnerEnrolmentId;
var yearGroup = Queries.GetFirstYearGroup();
dataPackage.AddData("Learner", new
{
Id = learnerId,
School = CoreQueries.GetSchoolId(),
Gender = CoreQueries.GetLookupItem("Gender", code: genderCode),
LegalForename = forename,
LegalSurname = surname,
DateOfBirth = dateOfBirth,
TenantID = tenantId,
ParentalSalutation = salutation,
ParentalAddressee = addressee,
PolicyAclId = CoreQueries.GetPolicyAclId("Learner")
});
dataPackage.AddData("LearnerEnrolment", new
{
Id = learnerEnrolmentId = uniqueLearnerEnrolmentId ?? Guid.NewGuid(),
School = CoreQueries.GetSchoolId(),
Learner = learnerId,
DOA = dateOfAdmission,
TenantID = tenantId
});
dataPackage.AddData("LearnerEnrolmentStatus", new
{
Id = Guid.NewGuid(),
LearnerEnrolment = learnerEnrolmentId,
StartDate = dateOfAdmission,
EnrolmentStatus = CoreQueries.GetLookupItem("EnrolmentStatus", code: enrolStatus),
TenantID = tenantId
});
dataPackage.AddData("LearnerYearGroupMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
YearGroup = yearGroupId ?? yearGroup.ID,
StartDate = dateOfAdmission,
TenantID = tenantId
});
dataPackage.AddData("LearnerNCYearMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
SchoolNCYear = schoolNCYearId ?? yearGroup.SchoolNCYear,
StartDate = dateOfAdmission,
TenantID = tenantId
});
return(dataPackage);
}
public static DataPackage AddBasicLearner(
this DataPackage dataPackage,
Guid learnerId,
string surname,
string forename,
DateTime dateOfBirth,
DateTime dateOfAdmission,
string genderCode = "1",
string enrolStatus = "C",
Guid?uniqueLearnerEnrolmentId = null,
int?tenantId = null,
string salutation = null,
string addressee = null,
Guid?yearGroupId = null,
Guid?schoolNCYearId = null
)
{
tenantId = tenantId ?? SeSugar.Environment.Settings.TenantId;
Guid learnerEnrolmentId;
var yearGroup = Queries.GetFirstYearGroup();
dataPackage
.AddData("Learner", new
{
Id = learnerId,
School = CoreQueries.GetSchoolId(),
Gender = CoreQueries.GetLookupItem("Gender", code: genderCode),
LegalForename = forename,
LegalSurname = surname,
DateOfBirth = dateOfBirth,
TenantID = tenantId,
ParentalSalutation = salutation,
ParentalAddressee = addressee,
PolicyAclId = CoreQueries.GetPolicyAclId("Learner")
})
.AddData("LearnerEnrolment", new
{
Id = learnerEnrolmentId = uniqueLearnerEnrolmentId ?? Guid.NewGuid(),
School = CoreQueries.GetSchoolId(),
Learner = learnerId,
DOA = dateOfAdmission,
TenantID = tenantId
})
.AddData("LearnerEnrolmentStatus", new
{
Id = Guid.NewGuid(),
LearnerEnrolment = learnerEnrolmentId,
StartDate = dateOfAdmission,
EnrolmentStatus = CoreQueries.GetLookupItem("EnrolmentStatus", code: enrolStatus),
TenantID = tenantId
})
.AddData("LearnerYearGroupMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
YearGroup = yearGroupId ?? yearGroup.ID,
StartDate = dateOfAdmission,
TenantID = tenantId
})
.AddData("LearnerNCYearMembership", new
{
Id = Guid.NewGuid(),
Learner = learnerId,
SchoolNCYear = schoolNCYearId ?? yearGroup.SchoolNCYear,
StartDate = dateOfAdmission,
TenantID = tenantId
});
return(dataPackage);
}