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 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 AddExceptionalCircumstance(this DataPackage datapackage, Guid id, Guid learnerid, string forename, string surname, Guid exceptionId, string description, string startdate, string enddate, string code)
{
var ExceptionalCircumstanceTypeid = Queries.GetExceptionalCircumstanceTypeID(code);
var startsessionid = Queries.GetStartSessionid();
var endsessionid = Queries.GetEndSessionid();
var schoolattendancecodeid = Queries.GetSchoolAttendanceCode();
datapackage.AddData("exceptionalcircumstance", new
{
ID = exceptionId,
School = CoreQueries.GetSchoolId(),
TenantID = tenantId,
Description = description,
StartDate = startdate,
EndDate = enddate,
ExceptionalCircumstanceType = ExceptionalCircumstanceTypeid,
StartSession = startsessionid,
EndSession = endsessionid,
SchoolAttendanceCode = schoolattendancecodeid,
});
datapackage.AddData("learnerexceptionalcircumstance", new
{
ID = id = Guid.NewGuid(),
TenantID = tenantId,
ExceptionalCircumstance = exceptionId,
Learner = learnerid,
});
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 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 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 PrimaryClass GetFirstPrimaryClass(int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
var schoolId = CoreQueries.GetSchoolId();
var sqlEffectiveDate = DateTime.Now.ToString(SqlDateFormat);
string sql = String.Format(
"SELECT TOP 1 pc.ID, pc.ShortName, pc.FullName" +
" FROM dbo.PrimaryClass pc inner join PrimaryClassSetMembership pcm" +
" on pc.id = pcm.PrimaryClass" +
" and pcm.StartDate < '" + sqlEffectiveDate + "' " +
" and (pcm.EndDate >= '" + sqlEffectiveDate + "' " +
" or pcm.EndDate is null)" +
" and pc.school = '" + schoolId + "'" +
" and pc.TenantID = '{0}'", tenantId);
var primaryClass = DataAccessHelpers.GetEntities <PrimaryClass>(sql).FirstOrDefault();
if (primaryClass == null)
{
throw new NullReferenceException("Could not retrieve YearGroup/YearGroupMembership");
}
sql = String.Format("SELECT SchoolNCYear FROM dbo.YearGroup where ID ='{0}'", primaryClass.ID);
primaryClass.SchoolNCYear = DataAccessHelpers.GetValue <Guid>(sql);
return(primaryClass);
}
/// <exception cref="NullReferenceException">Condition.</exception>
public static YearGroup GetFirstYearGroup(int?tenantId = null)
{
tenantId = tenantId ?? SeSugar.Environment.Settings.TenantId;
var schoolId = CoreQueries.GetSchoolId();
var sqlEffectiveDate = DateTime.Now.ToString(SqlDateFormat);
string sql = String.Format(
"SELECT TOP 1 yg.ID, yg.ShortName, yg.FullName" +
" FROM dbo.YearGroup yg inner join YearGroupSetMembership ygm" +
" on yg.ID = ygm.YearGroup" +
" and ygm.StartDate < '" + sqlEffectiveDate + "' " +
" and (ygm.EndDate >= '" + sqlEffectiveDate + "' " +
" or ygm.EndDate is null)" +
" inner join SchoolNCYear sncy" +
" on yg.SchoolNCYear = sncy.ID" +
" inner join NCYearNaturalAgeRange ncy" +
" on sncy.NCYear = ncy.NCYear" +
" and yg.School = '" + schoolId + "'" +
" and yg.TenantID = '{0}'", tenantId);
var yearGroup = DataAccessHelpers.GetEntities <YearGroup>(sql).FirstOrDefault();
if (yearGroup == null)
{
throw new NullReferenceException("Could not retrieve YearGroup/YearGroupMembership");
}
sql = String.Format("SELECT SchoolNCYear FROM dbo.YearGroup where ID ='{0}'", yearGroup.ID);
yearGroup.SchoolNCYear = DataAccessHelpers.GetValue <Guid>(sql);
return(yearGroup);
}
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 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 object GenerateAgency(out Guid id, string AgencyName)
{
return(new
{
ID = id = Guid.NewGuid(),
AgencyName = AgencyName,
ResourceProvider = CoreQueries.GetSchoolId(),
TenantID = tenantId
});
}
public static object GenerateMedicalPractice(out Guid id, string MedicalPracticeName)
{
return(new
{
ID = id = Guid.NewGuid(),
Name = MedicalPracticeName,
ResourceProvider = CoreQueries.GetSchoolId(),
TenantID = tenantId
});
}
public static DataPackage AddPupilDateOfLeaving(this DataPackage datapackage, Guid learnerEnrolmentId, Guid learnerId, string leavingdate)
{
datapackage.AddData("LearnerEnrolment", new
{
ID = learnerEnrolmentId,
Learner = learnerId,
School = CoreQueries.GetSchoolId(),
TenantID = tenantId,
DOL = leavingdate,
});
return(datapackage);
}
public static PrimaryClass GetPrimaryClass(string primaryclass, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
var schoolId = CoreQueries.GetSchoolId();
string sql = String.Format(
"SELECT TOP 1 pc.ID, pc.ShortName, pc.FullName" +
" FROM dbo.PrimaryClass pc where FullName = '" + primaryclass + "'" +
" and pc.School = '" + schoolId + "'" +
" and pc.TenantID = '{0}'", tenantId);
var primaryClass = DataAccessHelpers.GetEntities <PrimaryClass>(sql).FirstOrDefault();
return(primaryClass);
}
public static YearGroup GetYearGroup(string yeargroup, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
var schoolId = CoreQueries.GetSchoolId();
string sql = String.Format(
"SELECT TOP 1 yg.ID, yg.ShortName, yg.FullName" +
" FROM dbo.YearGroup yg where FullName = '" + yeargroup + "'" +
" and yg.School = '" + schoolId + "'" +
" and yg.TenantID = '{0}'", tenantId);
var yearGroup = DataAccessHelpers.GetEntities <YearGroup>(sql).FirstOrDefault();
return(yearGroup);
}
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 GenerateClass(this DataPackage datapackage, Guid classid, string classname, string shortname)
{
datapackage.AddData("PrimaryClass", new
{
ID = classid = Guid.NewGuid(),
FullName = classname,
ShortName = shortname,
Active = "1",
School = CoreQueries.GetSchoolId(),
TenantID = SeSugar.Environment.Settings.TenantId,
});
return(datapackage);
}
public static Guid GetLearnerId(string legalSurname, string legalforename, string dob)
{
var schoolId = CoreQueries.GetSchoolId();
string sql = String.Format(
"SELECT TOP 1 l.ID " +
"FROM dbo.Learner L " +
"WHERE L.School = '" + schoolId + "' " +
"AND L.LegalSurname = '" + legalSurname + "' " +
"AND L.LegalForename = '" + legalforename + "' " +
"AND L.DateOfBirth = '" + dob + "' ");
return(DataAccessHelpers.GetValue <Guid>(sql));
}
public static DataPackage AddClasses(this DataPackage dataPackage, Guid classId, string shortName, string fullName, int displayOrder, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.PrimaryClass, new
{
ID = classId,
TenantID = tenantId,
ShortName = shortName,
FullName = fullName,
School = CoreQueries.GetSchoolId(),
DisplayOrder = displayOrder
});
return(dataPackage);
}
public static PrimaryClass GetFirstPrimaryClass(int?tenantId = null)
{
var schoolId = CoreQueries.GetSchoolId();
tenantId = tenantId ?? Environment.Settings.TenantId;
string sql = String.Format(
"SELECT TOP 1 ID, FullName" +
" FROM dbo.[PrimaryClass] " +
" WHERE school = '" + schoolId + "'" +
" and TenantID = '{0}'" +
" and Active = 1", tenantId);
var primaryClass = DataAccessHelpers.GetEntities <PrimaryClass>(sql).FirstOrDefault();
return(primaryClass);
}
public static DataPackage AddSchoolNCYearLookup(this DataPackage dataPackage, Guid schoolNCYearId, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
string sql = string.Format("SELECT TOP 1 ID FROM dbo.NCYear where TenantID ='{0}'", tenantId);
Guid nCYearID = DataAccessHelpers.GetValue <Guid>(sql);
dataPackage.AddData(Constants.Tables.SchoolNCYear, new
{
ID = schoolNCYearId,
TenantID = tenantId,
School = CoreQueries.GetSchoolId(),
NCYear = nCYearID
});
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 DataPackage GenerateEarlyYearProvision(this DataPackage datapackage, Guid provisionId, string provisionname, string provisionshortname, string startdate, string starttime, string endtime)
{
datapackage.AddData("EarlyYearsSessionType", new
{
ID = provisionId,
School = CoreQueries.GetSchoolId(),
TenantID = tenantId,
ProvisionName = provisionname,
ShortName = provisionshortname,
StartDate = startdate,
//EndDate = enddate,
StartTime = starttime,
EndTime = endtime,
});
return(datapackage);
}
public void Delete_Criteria_without_policy()
{
//Login
//Insert a Criteria into database
Guid criteriaId = Guid.NewGuid();
string criteriaName = CoreQueries.GetColumnUniqueString("AdmissionsCriteria", "Name", 10, tenantID);
string criteriaDescription = CoreQueries.GetColumnUniqueString("AdmissionsCriteria", "Description", 10, tenantID);
using (new DataSetup(GetCriteriaRecord(criteriaId, criteriaName, criteriaDescription)))
{
//Act
Navigate_To_Criteria();
CriteriaDetails criteriadetails = new CriteriaDetails();
criteriadetails.SearchByName(criteriaName);
criteriadetails.ClickDeleteButton();
}
}
public void Search_Criteria_ByName()
{
//Insert a Criteria into database
Guid criteriaId = Guid.NewGuid();
string criteriaName = CoreQueries.GetColumnUniqueString("AdmissionsCriteria", "Name", 10, tenantID);
string criteriaDescription = CoreQueries.GetColumnUniqueString("AdmissionsCriteria", "Description", 10, tenantID);
using (new DataSetup(GetCriteriaRecord(criteriaId, criteriaName, criteriaDescription)))
{
//Act
//Login
Navigate_To_Criteria();
CriteriaDetails criteriadetails = new CriteriaDetails();
criteriadetails.SearchByName(criteriaName);
}
//Purge created data
PurgeLinkedData.DeletAdmissionCriteria(criteriaId);
}
public void Delete_Policy_WIthout_Criteria()
{
//Login
PolicyDetails policydetails = new PolicyDetails();
//Insert a policy into database
Guid policyId = Guid.NewGuid();
string policyName = CoreQueries.GetColumnUniqueString("AdmissionsPolicy", "Name", 10, tenantID);
string policyDescription = CoreQueries.GetColumnUniqueString("AdmissionsPolicy", "Description", 10, tenantID);
using (new DataSetup(GetPolicyRecord(policyId, policyName, policyDescription)))
{
//Act
Navigate_To_Policy();
policydetails.SearchByName(policyName);
policydetails.ClickDeleteButton();
}
}
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 void Search_Policy_ByName()
{
//Login
PolicyDetails policydetails = new PolicyDetails();
//Insert a policy into database
Guid policyId = Guid.NewGuid();
string policyName = CoreQueries.GetColumnUniqueString("AdmissionsPolicy", "Name", 10, tenantID);
string policyDescription = CoreQueries.GetColumnUniqueString("AdmissionsPolicy", "Description", 10, tenantID);
using (new DataSetup(GetPolicyRecord(policyId, policyName, policyDescription)))
{
//Act
Navigate_To_Policy();
policydetails.ClickAddPolicyButton();
policydetails.SearchByName(policyName);
}
//Purge created data
PurgeLinkedData.DeletAdmissionPolicy(policyId);
}
