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