private static List <long> GetUlns(int ukprn)
{
var getUlnNo = "SELECT [tqProfile].[UniqueLearnerNumber] FROM [TqRegistrationPathway] AS [tqPathway] INNER JOIN [TqProvider] AS [tqProvider] ON [tqPathway].[TqProviderId] = [tqProvider].[Id] INNER JOIN [TqAwardingOrganisation] AS [tqAo] ON [tqProvider].[TqAwardingOrganisationId] = [tqAo].[Id] INNER JOIN [TlAwardingOrganisation] AS [tlAo] ON [tqAo].[TlAwardingOrganisatonId] = [tlAo].[Id] INNER JOIN [TqRegistrationProfile] AS [tqProfile] ON [tqPathway].[TqRegistrationProfileId] = [tqProfile].[Id] WHERE [tlAo].[UkPrn] = '" + ukprn + "'";
List <long> ulnId = SqlDatabaseConncetionHelper.GetUlnsFromDatabse(getUlnNo, ConnectionString);
return(ulnId);
}
private static IEnumerable <int> GetPathwayIds(string uln)
{
var getPathwayId = "select rs.TqRegistrationPathwayId from TqRegistrationSpecialism rs join TqRegistrationPathway rp on rp.Id = rs.TqRegistrationPathwayId join TqRegistrationProfile pr on pr.Id = rp.TqRegistrationProfileId where pr.UniqueLearnerNumber = '" + uln + "'";
var pathwayIds = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getPathwayId, ConnectionString);
return(pathwayIds.Select(x => Convert.ToInt32(x[0])).ToList());
}
public EnglishAndMathsStatus GetEMStatus(string uln)
{
string profileTableQuery = "select IsEnglishAndMathsAchieved from TqRegistrationProfile where UniqueLearnerNumber= " + uln;
var englishMathsStatus = SqlDatabaseConncetionHelper.ReadDataFromDataBase(profileTableQuery, ConnectionString);
return((EnglishAndMathsStatus)Convert.ToInt32(englishMathsStatus[0][0]));
}
public static int GetProfileID(string uln)
{
string getProfileId = "Select top 1 Id from TqRegistrationProfile where UniqueLearnerNumber = '" + uln + "'";
var profileId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getProfileId, ConnectionString);
int result = Convert.ToInt32(profileId[0][0]);
return(result);
}
public static int getPathwayId(string uln)
{
string getPathwayId = "select rs.TqRegistrationPathwayId from TqRegistrationSpecialism rs join TqRegistrationPathway rp on rp.Id = rs.TqRegistrationPathwayId join TqRegistrationProfile pr on pr.Id = rp.TqRegistrationProfileId where pr.UniqueLearnerNumber = '" + uln + "'";
var pathwayId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getPathwayId, ConnectionString);
int result = Convert.ToInt32(pathwayId[0][0]);
return(result);
}
public IndustryPlacementStatus GetIPStatus(string uln)
{
string profileIdQuery = "select rep.Id from TqRegistrationProfile rp join TqRegistrationPathway rep on rp.Id = rep.TqRegistrationProfileId where rp.UniqueLearnerNumber = " + uln;
var regPathwayId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(profileIdQuery, ConnectionString);
string IpQuery = "select status from IndustryPlacement where TqRegistrationPathwayId=" + Convert.ToInt32(regPathwayId[0][0]);;
var ipStatusId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(IpQuery, ConnectionString);
return((IndustryPlacementStatus)Convert.ToInt32(ipStatusId[0][0]));
}
private static int InsertRegistrationProfile(string uln)
{
var createRegistrationProfile = "Insert into TqRegistrationProfile values(" + uln + ", 'Db FirstName','Db LastName','2001-01-01',Null,Null,Null,Null,Null,GETDATE(),'System', GETDATE(),'System')";
var getRegProfileId = "Select top 1 id from TqRegistrationProfile where UniqueLearnerNumber='" + uln + "'";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createRegistrationProfile, ConnectionString);
var profileId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getRegProfileId, ConnectionString);
return((int)profileId.FirstOrDefault().FirstOrDefault());
}
private static int InsertRegistrationSpecialism2(int pathwayId)
{
var tlSpecialismId2 = Constants.VentilationSpecialismId;
var createRegSpecialism = "Insert into TqRegistrationSpecialism values('" + pathwayId + "','" + tlSpecialismId2 + "',GETDATE(),NULL,1,0,GETDATE(),'System',Null,Null )";
var getSpecialismId = "select top 1 id from TqRegistrationSpecialism where TqRegistrationPathwayId = '" + pathwayId + "'order by Id desc";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createRegSpecialism, ConnectionString);
var specialismId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getSpecialismId, ConnectionString);
return((int)specialismId.FirstOrDefault().FirstOrDefault());
}
private static int InsertSpecialismAssessment2(int specialismId)
{
var tlAssessmentSeriesId = Constants.TlAssessmentSeriesId;
var createSpecialismAssessment = "Insert into TqSpecialismAssessment values('" + specialismId + "','" + tlAssessmentSeriesId + "',GETDATE(),Null,1,0,GETDATE(),'System',GETDATE(),'System')";
var getSpecialismAssessmentId = "select top 1 id from TqSpecialismAssessment where TqRegistrationSpecialismId = '" + specialismId + "'order by Id desc";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createSpecialismAssessment, ConnectionString);
var specialismAssessmentId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getSpecialismAssessmentId, ConnectionString);
return((int)specialismAssessmentId.FirstOrDefault().FirstOrDefault());
}
public static void DeleteBulkUploadData()
{
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteIpData, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteEMData, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeletePathwayResults, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteAssessmentPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteAssessmentSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegistrationSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegistrationPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegistrationProfile, ConnectionString);
}
private static int InsertCoreAssessment(int pathwayId)
{
var pathwayAssessmentSeriesId = Constants.PathwayAssessmentSeriesId;
var createPathwayAssessment = "Insert into TqPathwayAssessment values('" + pathwayId + "','" + pathwayAssessmentSeriesId + "',GETDATE(),NULL,1,0,GETDATE(),'System',Null,Null )";
var getRegPathwayId = "select top 1 id from TqPathwayAssessment where TqRegistrationPathwayId = '" + pathwayId + "'";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createPathwayAssessment, ConnectionString);
var regPathwayId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getRegPathwayId, ConnectionString);
return((int)regPathwayId.FirstOrDefault().FirstOrDefault());
}
private static int InsertRegistrationPathway(int profileId)
{
var tqProviderId = Constants.CityAndGuildsProviderId;
var createRegPathway = "Insert into TqRegistrationPathway values('" + profileId + "', '" + tqProviderId + "','2020', GETDATE(),NULL,1,1,GETDATE(),'System',NULL,NULL)";
var getRegPathwayId = "select top 1 id from TqRegistrationPathway where TqRegistrationProfileId = '" + profileId + "'";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createRegPathway, ConnectionString);
var pathwayId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getRegPathwayId, ConnectionString);
return((int)pathwayId.FirstOrDefault().FirstOrDefault());
}
public static void UpdateToWithdrawn(string uln)
{
var updatePathway = "Update TqRegistrationPathway set status=4, EndDate=GETDATE(),ModifiedOn=GETDATE(),ModifiedBy='SYSTEM' from TqRegistrationPathway rp join TqRegistrationProfile pr on rp.TqRegistrationProfileId = pr.Id where UniqueLearnerNumber = " + uln + "";
var updateSpecialism = "Update TqRegistrationSpecialism set EndDate=GETDATE(),ModifiedOn=GETDATE(),ModifiedBy='SYSTEM' from TqRegistrationSpecialism rs join TqRegistrationPathway rp on rs.TqRegistrationPathwayId = rp.Id join TqRegistrationProfile pr on rp.TqRegistrationProfileId = pr.Id where UniqueLearnerNumber =" + uln + "";
var updateAssessment = "Update TqPathwayAssessment set EndDate=GETDATE(),ModifiedOn=GETDATE(),ModifiedBy='SYSTEM' from TqPathwayAssessment pa join TqRegistrationPathway rp on rp.Id = pa.TqRegistrationPathwayId join TqRegistrationProfile pf on pf.id = rp.TqRegistrationProfileId where UniqueLearnerNumber =" + uln + "";
var updateResult = "Update TqPathwayResult set EndDate=GETDATE(),ModifiedOn=GETDATE(),ModifiedBy='SYSTEM' from TqPathwayResult pr join TqPathwayAssessment pa on pr.TqPathwayAssessmentId = pa.Id join TqRegistrationPathway rp on rp.Id = pa.TqRegistrationPathwayId join TqRegistrationProfile pf on pf.id = rp.TqRegistrationProfileId where UniqueLearnerNumber =" + uln + "";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(updateResult, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(updateAssessment, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(updateSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(updatePathway, ConnectionString);
}
protected static void DeleteLrsDataFromTables(string uln)
{
var pathwayIds = GetPathwayIds(uln);
var deleteIpRecords = "Delete from IndustryPlacement where TqRegistrationPathwayId in (" + string.Join(",", pathwayIds) + ")";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteIpRecords, ConnectionString);
var profileId = GetProfileId(uln);
var deleteQualificationAcheived = "Delete from QualificationAchieved where TqRegistrationProfileId = '" + profileId + "'";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteQualificationAcheived, ConnectionString);
DeleteRegistrationFromTables(uln);
}
public void DeleteLrsRecordsFromTables(string uln)
{
var pathwayId = getPathwayId(uln);
string DeleteIpRecords = "Delete from IndustryPlacement where TqRegistrationPathwayId = '" + pathwayId + "'";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteIpRecords, ConnectionString);
var profileId = GetProfileID(uln);
string DeleteQualificationAcheived = "Delete from QualificationAchieved where TqRegistrationProfileId = '" + profileId + "'";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteQualificationAcheived, ConnectionString);
DeleteRegistrationFromTables(uln);
}
protected static void RegisterWithdrawnLearnerWithAnotherAo(string uln)
{
var profileId = SqlQueries.ReturnRegistrationProfileID(uln);
var createNewRegPathway = "Insert into TqRegistrationPathway values('" + profileId + "', '" + "15184" + "','2020', GETDATE(),NULL,1,1,GETDATE(),'System',NULL,NULL)";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createNewRegPathway, ConnectionString);
//var profileId = SqlQueries.CreateRegistrationProfile(uln);
//var pathwayId = SqlQueries.CreateRegistrationPathway(profileId);
//SqlQueries.CreateRegSpecialism(pathwayId);
//var pathwayAssessmentId = SqlQueries.CreatePathwayAssessment(pathwayId);
//SqlQueries.CreatePathwayResult(pathwayAssessmentId);
}
public void DeleteRegistrationFromTables(string uln)
{
string DeletePathwayResults = "Delete pr from TqPathwayResult pr join TqPathwayAssessment pa on pr.TqPathwayAssessmentId = pa.Id join TqRegistrationPathway rw ON pa.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId = rp.Id where UniqueLearnerNumber = '" + uln + "'";
string DeleteRegSpecialism = "Delete rs from TqRegistrationSpecialism rs join TqRegistrationPathway rw ON rs.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId =rp.Id where UniqueLearnerNumber = '" + uln + "'";
string DeleteRegPathway = "Delete from TqRegistrationPathway where TqRegistrationProfileId In (select Id from TqRegistrationProfile where UniqueLearnerNumber= '" + uln + "')";
string DeleteRegProfile = "Delete from TqRegistrationProfile where UniqueLearnerNumber = '" + uln + "'";
string DeleteAssPathway = "Delete pa from TqPathwayAssessment pa join TqRegistrationPathway rw ON pa.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId =rp.Id where UniqueLearnerNumber= '" + uln + "'";
string DeleteAssSpecialism = "Delete sa from TqSpecialismAssessment sa join TqRegistrationSpecialism rs ON sa.TqRegistrationSpecialismId = rs.Id join TqRegistrationPathway rp on rs.TqRegistrationPathwayId=rp.Id join TqRegistrationProfile tr on rp.TqRegistrationProfileId =tr.Id where UniqueLearnerNumber= '" + uln + "'";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeletePathwayResults, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteAssPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteAssSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteRegProfile, ConnectionString);
}
protected static void CreateRegWithAppealState(string uln)
{
var profileId = SqlQueries.CreateRegistrationProfile(uln);
var pathwayId = SqlQueries.CreateRegistrationPathway(profileId);
SqlQueries.CreateRegSpecialism(pathwayId);
var pathwayAssessmentId = SqlQueries.CreatePathwayAssessment(pathwayId);
SqlQueries.CreatePathwayResult(pathwayAssessmentId);
var updateResult = "update TqPathwayResult set EndDate = GETDATE() where TqPathwayAssessmentId='" + pathwayAssessmentId + "'";
SqlDatabaseConncetionHelper.UpdateSqlCommand(updateResult, ConnectionString);
var insertAppealRow = "Insert into TqPathwayResult values('" + pathwayAssessmentId + "',2,GETDATE(),Null,3,1,0,GETDATE(),'System',GETDATE(),'System')";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(insertAppealRow, ConnectionString);
}
private static void DeleteRegistrations(List <long> ulns)
{
var uln = string.Join(",", ulns);
var deletePathwayResults = "Delete pr from TqPathwayResult pr join TqPathwayAssessment pa on pr.TqPathwayAssessmentId = pa.Id join TqRegistrationPathway rw ON pa.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId = rp.Id where UniqueLearnerNumber in (" + uln + ")";
var deleteRegSpecialism = "Delete rs from TqRegistrationSpecialism rs join TqRegistrationPathway rw ON rs.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId =rp.Id where UniqueLearnerNumber in (" + uln + ")";
var deleteRegPathway = "Delete from TqRegistrationPathway where TqRegistrationProfileId In (select Id from TqRegistrationProfile where UniqueLearnerNumber in (" + uln + "))";
var deleteRegProfile = "Delete from TqRegistrationProfile where UniqueLearnerNumber in (" + uln + ")";
var deleteAssPathway = "Delete pa from TqPathwayAssessment pa join TqRegistrationPathway rw ON pa.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId =rp.Id where UniqueLearnerNumber in (" + uln + ")";
var deleteAssSpecialism = "Delete sa from TqSpecialismAssessment sa join TqRegistrationSpecialism rs ON sa.TqRegistrationSpecialismId = rs.Id join TqRegistrationPathway rp on rs.TqRegistrationPathwayId=rp.Id join TqRegistrationProfile tr on rp.TqRegistrationProfileId =tr.Id where UniqueLearnerNumber in (" + uln + ")";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deletePathwayResults, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteAssPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteAssSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteRegSpecialism, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteRegPathway, ConnectionString);
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(deleteRegProfile, ConnectionString);
}
private static void InsertAssessmentResult(int specialismAssessmentId, int grade)
{
var createPathwayResult = "Insert into TqSpecialismResult values ('" + specialismAssessmentId + "','" + grade + "',GETDATE(),NULL,Null,1,0,GETDATE(),'SYSTEM',NULL,'SYSTEM')";
SqlDatabaseConncetionHelper.ExecuteSqlCommand(createPathwayResult, ConnectionString);
}
public static void UpdateAcademicYearTo2020()
{
var updateAcademicYear = "Update TqRegistrationPathway set AcademicYear= 2020 where TqRegistrationProfileId In (select Id from TqRegistrationProfile where UniqueLearnerNumber like '99%')";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(updateAcademicYear, ConnectionString);
}
