public static void CreateSpecialismAssessment(int specialismId) { var tlAssessmentSeriesId = Constants.TlAssessmentSeriesId; var createSpecialismAssessment = "Insert into TqSpecialismAssessment values('" + specialismId + "','" + tlAssessmentSeriesId + "',GETDATE(),Null,1,0,GETDATE(),'System',GETDATE(),'System')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(createSpecialismAssessment, ConnectionString); }
public static void InsertAddress() { var providerId = "Select Id from TlProvider where Name='Barnsley College'"; var getProviderId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(providerId, ConnectionString); int result = Convert.ToInt32(getProviderId[0][0]); var insertAddress = "Insert Into TlProviderAddress values ('" + result + "','Department', 'BARNSLEY COLLEGE','CHURCH STREET',NULL,'BARNSLEY','S70 2AX',1,GETDATE(),'System',Null,Null)"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(insertAddress, ConnectionString); }
public static void CreateQualificationAcheivedForLrsMathsNAEnglishA(int profileId) { string CreateQualificationAcheived = "Insert into QualificationAchieved values ('" + profileId + "',38,9,0,GETDATE(),'SYSTEM',GETDATE(),'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(CreateQualificationAcheived, ConnectionString); string CreateQualificationAcheived1 = "Insert into QualificationAchieved values ('" + profileId + "',69,8,1,GETDATE(),'SYSTEM',GETDATE(),'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(CreateQualificationAcheived1, ConnectionString); }
public static int CreateRegistrationProfile(string uln) { string CreateRegistrationProfile = "Insert into TqRegistrationProfile values(" + uln + ", 'Db FirstName','Db LastName','2001-01-01',Null,Null,Null,Null,Null,GETDATE(),'System', GETDATE(),'System')"; string 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()); }
public static void CreateQualificationAcheivedForLrsSEND(int profileId) { var createQualificationAcheived = "Insert into QualificationAchieved values ('" + profileId + "',517,8,1,GETDATE(),'SYSTEM',GETDATE(),'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(createQualificationAcheived, ConnectionString); var createQualificationAcheived1 = "Insert into QualificationAchieved values ('" + profileId + "',596,8,1,GETDATE(),'SYSTEM',GETDATE(),'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(createQualificationAcheived1, ConnectionString); }
public static int CreatePathwayAssessment(int pathwayId) { string CreatePathwayAssessment = "Insert into TqPathwayAssessment values('" + pathwayId + "',1,GETDATE(),NULL,1,0,GETDATE(),'System',Null,Null )"; string 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()); }
public static int CreateRegSpecialismForLrs(int pathwayId) { var tlSpecialismId = Constants.TlSpecialismId; string CreateRegSpecialism = "Insert into TqRegistrationSpecialism values('" + pathwayId + "','" + tlSpecialismId + "',GETDATE(),NULL,1,0,GETDATE(),'System',Null,Null )"; string GetSpecialismId = "select top 1 id from TqRegistrationSpecialism where TqRegistrationPathwayId = '" + pathwayId + "'"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(CreateRegSpecialism, ConnectionString); var specialismId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(GetSpecialismId, ConnectionString); return((int)specialismId.FirstOrDefault().FirstOrDefault()); }
public static int CreateRegistrationPathway(int profileId) { var tqProviderId = Constants.TqProviderId; string CreateRegPathway = "Insert into TqRegistrationPathway values('" + profileId + "', '" + tqProviderId + "','2020', GETDATE(),NULL,1,1,GETDATE(),'System',NULL,NULL)"; string 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 SetTLevelstoReviewedQueriedUnconfirmed() { var setThreeTLevelsToReviewed = "update TqAwardingOrganisation set reviewstatus = 2, modifiedon = GETDATE(), ModifiedBy = 'Test User' where TlPathwayId in (select ao.TlPathwayId from TqAwardingOrganisation AO, TlPathway p where AO.TlPathwayId = p.id and ao.TlAwardingOrganisatonId = 1 and p.TlevelTitle in ('T Level in Agriculture, Environmental and Animal Care','T Level in Business and Administration','T Level in Digital Business Services'))"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(setThreeTLevelsToReviewed, ConnectionString); var setThreeTLevelsToQueried = "update TqAwardingOrganisation set reviewstatus = 3, modifiedon = GETDATE(), ModifiedBy = 'Test User' where TlPathwayId in (select ao.TlPathwayId from TqAwardingOrganisation AO, TlPathway p where AO.TlPathwayId = p.id and ao.TlAwardingOrganisatonId = 1 and p.TlevelTitle in ('T Level in Digital Support Services','T Level in Education and Childcare','T Level in Engineering and Manufacturing'))"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(setThreeTLevelsToQueried, ConnectionString); var setFourTLevelsToUnconfirmed = "update TqAwardingOrganisation set reviewstatus = 1, modifiedon = GETDATE(), ModifiedBy = 'Test User' where TlPathwayId in (select ao.TlPathwayId from TqAwardingOrganisation AO, TlPathway p where AO.TlPathwayId = p.id and ao.TlAwardingOrganisatonId = 1 and p.TlevelTitle in ('T Level in Health','T Level in Healthcare Science','T Level in Legal, Finance and Accounting','T Level in Science'))"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(setFourTLevelsToUnconfirmed, ConnectionString); }
public static int CreateRegistrationProfileForLrsCombination(string uln, string IsLearnerVerified, string IsEmAchieved, string SendLearner, string IsRcFeed) { var createRegistrationProfile = "Insert into TqRegistrationProfile values(" + uln + ", 'Db FirstName','Db LastName','2001-01-01',Null," + IsLearnerVerified + "," + IsEmAchieved + "," + SendLearner + "," + IsRcFeed + ",GETDATE(),'System', GETDATE(),'System')"; Console.WriteLine(createRegistrationProfile); string 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()); }
public static void UpdateAcademicYearToPreviousYear() { var getAcademicYear = "Select Name from dbo.AcademicYear where getdate() >= StartDate and getdate() <= EndDate"; var academicYear = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getAcademicYear, ConnectionString); var result = Convert.ToString(academicYear[0][0]); var previousAcademicYear = result.Substring(0, 4); int x = Int32.Parse(previousAcademicYear); x = x - 1; var updateRegistrationYear = "Update TqRegistrationPathway set AcademicYear = " + x + " where tqregistrationprofileid in (select id from TqRegistrationProfile where UniqueLearnerNumber like '99%')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(updateRegistrationYear, ConnectionString); }
public static void DeletePrintTableRecords(string uln) { var recordCountSql = "select count(*) from PrintCertificate where uln =" + uln; var recordCount = SqlDatabaseConncetionHelper.ReadDataFromDataBase(recordCountSql, ConnectionString); int recordCount1 = Convert.ToInt32(recordCount[0][0]); if (recordCount1 == 0) { Console.WriteLine("Do Nothing"); } else { var retrievePrintBatchItemId = "select top 1 printbatchitemid from PrintCertificate where uln =" + uln; var printBatchItemId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrievePrintBatchItemId, ConnectionString); var retrieveBatchId = "Select top 1 batchID from PrintBatchItem where id in (select printbatchitemid from PrintCertificate where uln =" + uln + ")"; var batchId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrieveBatchId, ConnectionString); var deletePrintCertificateRecord = "delete from PrintCertificate where ULN =" + uln; var deletePrintBatchItemRecord = "delete from PrintBatchItem where id =" + printBatchItemId[0][0]; var deleteBatchRecord = "delete from batch where id =" + batchId[0][0]; SqlDatabaseConncetionHelper.ExecuteSqlCommand(deletePrintCertificateRecord, ConnectionString); SqlDatabaseConncetionHelper.ExecuteSqlCommand(deletePrintBatchItemRecord, ConnectionString); SqlDatabaseConncetionHelper.ExecuteSqlCommand(deleteBatchRecord, ConnectionString); } }
public void DbUpdate() { _ = SqlDatabaseConncetionHelper.ExecuteSqlCommand(UpdateDBReview, ConnectionString); }
protected static void SetAllTLevelsToQueried() { var setAllTLevelsToNotReviewed = "Update TqAwardingOrganisation set ReviewStatus=3 where TlAwardingOrganisatonId=1"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(setAllTLevelsToNotReviewed, ConnectionString); }
protected static void UpdateAutumn2021AssessmentSeriesWithFutureDate() { var createQualificationAcheived = "update AssessmentSeries set appealenddate = '2021-09-24 00:00:00.000' where id = 1"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(createQualificationAcheived, ConnectionString); }
protected static void SetOneTLevelsToNotReviewed() { var setTwoTLevelsToNotReviewed = "update TqAwardingOrganisation set reviewstatus = 1 where TlPathwayId in (select ao.TlPathwayId from TqAwardingOrganisation AO, TlPathway p where AO.TlPathwayId = p.id and ao.TlAwardingOrganisatonId = 1 and p.TlevelTitle in ('T Level in Agriculture, Environmental and Animal Care'))"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(setTwoTLevelsToNotReviewed, ConnectionString); }
public static void CreateIndustryPlacement(int pathwayId, int status) { string CreateIpData = "Insert into IndustryPlacement values ('" + pathwayId + "','" + status + "',GETDATE(),'SYSTEM',GETDATE(),'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(CreateIpData, ConnectionString); }
public static void CreatePathwayResult(int pathwayAssessmentId) { string CreatePathwayResult = "Insert into TqPathwayResult values ('" + pathwayAssessmentId + "',2,GETDATE(),NULL,1,0,GETDATE(),'SYSTEM',NULL,'SYSTEM')"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(CreatePathwayResult, ConnectionString); }
public static void UpdateRegistrationProfileRecord(string SQL) { SqlDatabaseConncetionHelper.ExecuteSqlCommand(SQL, ConnectionString); }
public static void UpdateAssessmentSeriesToPastOne(string Uln) { var updateAssessmentSeries = "update TqPathwayAssessment set AssessmentSeriesId = 1 where Id in (select max(Id) from TqPathwayAssessment where TqRegistrationPathwayId in (select Id from TqRegistrationPathway where tqregistrationprofileid in (select id from TqRegistrationProfile where UniqueLearnerNumber = " + Uln + ")))"; SqlDatabaseConncetionHelper.ExecuteSqlCommand(updateAssessmentSeries, ConnectionString); }