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 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);
}
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);
}
/// <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 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 GenerateMedicalPractice(out Guid id, string MedicalPracticeName)
{
return(new
{
ID = id = Guid.NewGuid(),
Name = MedicalPracticeName,
ResourceProvider = CoreQueries.GetSchoolId(),
TenantID = tenantId
});
}
public static object GenerateAgency(out Guid id, string AgencyName)
{
return(new
{
ID = id = Guid.NewGuid(),
AgencyName = AgencyName,
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 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 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 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 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 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 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 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 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 AddYearGroupLookup(this DataPackage dataPackage, Guid yearGroupId, Guid schoolNCYearId, string shortName, string fullName, int displayOrder, int?tenantId = null)
{
tenantId = tenantId ?? Environment.Settings.TenantId;
dataPackage.AddData(Constants.Tables.YearGroup, new
{
ID = yearGroupId,
TenantID = tenantId,
ShortName = shortName,
FullName = fullName,
School = CoreQueries.GetSchoolId(),
SchoolNCYear = schoolNCYearId
})
.Add(Constants.Tables.YearGroupSetMembership, new
{
ID = Guid.NewGuid(),
YearGroup = yearGroupId,
TenantID = tenantId,
StartDate = new DateTime(2015, 01, 01)
});
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 PrimaryClass GetFirstPrimaryClass(int?tenantId = null, DateTime?dateOfAdmission = null)
{
if (dateOfAdmission == null)
{
dateOfAdmission = DateTime.Today;
}
var schoolId = CoreQueries.GetSchoolId();
tenantId = tenantId ?? Environment.Settings.TenantId;
string sql = String.Format(
@"SELECT TOP 1 pc.ID, pc.FullName
FROM dbo.[PrimaryClass] pc INNER JOIN dbo.[YearGroupPrimaryClassAssociation] pca
ON pc.ID = pca.PrimaryClass
WHERE pc.School = '" + schoolId + "'" +
" and pc.TenantID = '{0}'" +
" and pc.Active = 1" +
"AND pca.StartDate <= '{1}' and (pca.EndDate IS NULL OR pca.EndDate>='{1}')", tenantId, dateOfAdmission);
var primaryClass = DataAccessHelpers.GetEntities <PrimaryClass>(sql).FirstOrDefault();
return(primaryClass);
}
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);
}
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);
}
