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); }