public static int ReturnRegistrationProfileID(string uln)
{
var getRegProfileId = "Select top 1 id from TqRegistrationProfile where UniqueLearnerNumber='" + uln + "'";
var profileId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getRegProfileId, ConnectionString);
return((int)profileId.FirstOrDefault().FirstOrDefault());
}
private List <object[]> GetEmployerDetails(String employerName)
{
String query = ("select companyname, PrimaryContact, phone, email from employer where CompanyName = '" + employerName + "'");
var queryResult = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
return(queryResult);
}
public static int ReturnRegistrationPathwayForLrs(int profileId)
{
var getRegPathwayId = "select top 1 id from TqRegistrationPathway where TqRegistrationProfileId = '" + profileId + "'";
var pathwayId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getRegPathwayId, ConnectionString);
return((int)pathwayId.FirstOrDefault().FirstOrDefault());
}
public static string ReturnLastActiveGrade(string uln)
{
string ReturnLastActiveGrade = "select top 1 Tll.Value from TqRegistrationProfile TQPR, TqRegistrationPathway TQPA, TqPathwayAssessment TQPAS, TqPathwayResult TQR, TlLookup Tll where TQPR.ID = TQPA.TqRegistrationProfileId and TQPA.id = TQPAS.TqRegistrationPathwayId and TQPAS.ID = TQR.TqPathwayAssessmentId and TQR.TlLookupId = Tll.id and TQPR.uniquelearnernumber = " + uln + " and TQPA.EndDate is not Null order by TQR.Id desc";
var Grade = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ReturnLastActiveGrade, ConnectionString);
return((string)Grade.FirstOrDefault().FirstOrDefault());
}
public int GetIsCompleted(String query)
{
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
String OpportunityItemId = null;
foreach (object[] OppItemId in queryResults)
{
OpportunityItemId = OppItemId[0].ToString();
}
int IsCompleted = 0;
for (int i = 0; i < 90000; i++)
{
if (IsCompleted == 0)
{
var query2 = ("select IsCompleted from OpportunityItem where Id = '" + OpportunityItemId + "'");
var queryResults2 = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query2, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
foreach (object[] Value in queryResults2)
{
IsCompleted = Convert.ToInt32(Value[0]);
}
}
else
{
break;
}
}
return(IsCompleted);
}
public void VerifyEmployersAnswers()
{
String query = ("Select oi.Postcode, oi.SearchRadius, oi.JobRole, oi.PlacementsKnown, oi.Placements, e.CompanyName from opportunity o, OpportunityItem OI, employer e where o.id = oi.OpportunityId and o.EmployerCrmId = e.CrmId and o.ID in (select max(id) from opportunity)");
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
foreach (object[] fieldNo in queryResults)
{
//Assign values to variables from the SQL query run
actualPostcode = fieldNo[0].ToString();
actualSearchRadius = (fieldNo[1].ToString() + " miles");
actualJobtitle = fieldNo[2].ToString();
actualPlacementsKnown = Convert.ToInt32(fieldNo[3]);
actualNoOfPlacements = fieldNo[4].ToString();
actualEmployername = fieldNo[5].ToString();
//actualSkillArea = fieldNo[6].ToString();
//OpportunityId = Convert.ToInt32(fieldNo[7]);
//ScenarioContext.Current["_provisionGapOpportunityID"] = OpportunityId;
Console.WriteLine("ActualNoOfPlacements: " + actualNoOfPlacements + "ExpectedNoOfPlacements: " + expectedNoOfPlacementsKnown);
Console.WriteLine("ActualPostcode: " + actualPostcode + "ExpectedPostcode: " + expectedPostcode);
Console.WriteLine("ActualSearchRadius: " + actualSearchRadius + "ExpectedSearchRadius: " + expectedSearchRadius);
Console.WriteLine("ActualJobTitle: " + actualJobtitle + "ExpectedJobTitle: " + expectedJobType);
Console.WriteLine("ActualSkillArea: " + actualSkillArea + "ExpectedSkillArea: " + expectedTypeOfPlacement);
Console.WriteLine("ActualEmployerName: " + actualEmployername + "ExpectedEmployerName: " + expectedEmployername);
Console.WriteLine("ActualPLACEMENTS: " + actualPlacementsKnown);
//Assert the variables above to the actual values displayed on the screen
//PageInteractionHelper.VerifyText(actualSkillArea, expectedTypeOfPlacement);
//PageInteractionHelper.VerifyText(actualPostcode, expectedPostcode);
//PageInteractionHelper.VerifyText(actualSearchRadius, expectedSearchRadius);
//PageInteractionHelper.VerifyText(actualJobtitle, expectedJobType);
//PageInteractionHelper.VerifyText(actualNoOfPlacements, actualNoOfPlacements);
}
}
public void VerifyReferralRecordsCreated()
{
string provider1 = (string)ScenarioContext.Current["_Provider1"];
//string provider2 = (string)ScenarioContext.Current["_Provider2"];
string[] providerArray;
providerArray = new string[] { provider1 };
foreach (String items in providerArray)
{
String query = ("select count(*) from referral where opportunityID = " + opportunityID + " and providervenueId in (select pv.id from ProviderVenue pv, provider p where pv.ProviderId = p.Id and p.name = '" + items + "')");
Console.WriteLine(query);
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
foreach (object[] fieldNo in queryResults)
{
//Assign values to variables from the SQL query run
String actualCount = fieldNo[0].ToString();
String expectedCount = "1";
//Assert the variables above to the actual values displayed on the screen
PageInteractionHelper.VerifyText(expectedCount, actualCount);
}
}
}
public void VerifyNewProviderCreated()
{
Thread.Sleep(10000);
String ProviderRecordCount = "Select Count(*) FROM Provider";
var result = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ProviderRecordCount, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
Assert.AreEqual(341, result[0][0]);
}
public static string GetSpecialismAssessmentSeries()
{
var getSpecialismAssessmentSeries = "Select Name from dbo.AssessmentSeries where getdate() >= StartDate and getdate() <= EndDate and componenttype = 2";
var specialismAssessmentSeries = SqlDatabaseConncetionHelper.ReadDataFromDataBase(getSpecialismAssessmentSeries, ConnectionString);
var result = Convert.ToString(specialismAssessmentSeries[0][0]);
return(result);
}
public static int ReturnLastActiveGradeCount(string uln)
{
string ReturnLastActiveGradeCount = "select count(Tll.Value) from TlLookup TLL, TqPathwayResult TQR where TQR.TlLookupId = Tll.id and TQR.tqpathwayassessmentid in (select id from TqPathwayAssessment where TqRegistrationPathwayId in (select top 1 TQPA.ID from TqRegistrationProfile TQPR, TqRegistrationPathway TQPA where TQPR.ID = TQPA.TqRegistrationProfileId and TQPR.uniquelearnernumber = " + uln + "and TQPA.EndDate is not Null order by TQPA.EndDate desc)) and IsOptedin > 0 group by Tll.Value";
var Records = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ReturnLastActiveGradeCount, ConnectionString);
int result = Convert.ToInt32(Records.Count);
return(result);
}
public static string GetAcademicYear()
{
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]);
return(result);
}
public static int RetrievePathwayAssessmentID(string uln)
{
string PathwayAssessmentID = " select pa.Id from TqPathwayAssessment pa join TqRegistrationPathway rw ON pa.TqRegistrationPathwayId = rw.Id join TqRegistrationProfile rp on rw.TqRegistrationProfileId = rp.Id where UniqueLearnerNumber = " + uln;
var PAID = SqlDatabaseConncetionHelper.ReadDataFromDataBase(PathwayAssessmentID, ConnectionString);
int result = Convert.ToInt32(PAID[0][0]);
return(result);
}
public static int ReturnIPStatus(string uln)
{
string ReturnIPStatus = "select status from IndustryPlacement where TqRegistrationPathwayId in ( Select max (id) from TqRegistrationPathway where TqRegistrationProfileId in (select id from TqRegistrationProfile where uniquelearnernumber =" + uln + "))";
var Records = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ReturnIPStatus, ConnectionString);
int result = Convert.ToInt32(Records[0][0]);
return(result);
}
public static int VerifyRegistrationDeleted(string uln)
{
string ReturnRegistrationRecords = "select count(TQPR.Id) from TqRegistrationProfile TQPR, TqRegistrationPathway TQPA where TQPR.ID = TQPA.TqRegistrationProfileId and TQPR.uniquelearnernumber = " + uln;
var Records = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ReturnRegistrationRecords, ConnectionString);
int result = Convert.ToInt32(Records[0][0]);
return(result);
}
public OpportunitiesBasketReferralPage VerifyLatestReferralRecordValues()
{
string EmployerName = (string)ScenarioContext.Current["_provisionGapEmployerName"];
string _employercontact = (string)ScenarioContext.Current["_EmployerContactName"];
string _employercontactemail = (string)ScenarioContext.Current["_EmployerContactEmail"];
string _employercontactphone = (string)ScenarioContext.Current["_EmployerContactNumber"];
string _opportunityType = "Referral";
string _searchPostcode = (string)ScenarioContext.Current["_SearchPostcode"];
string _placementsRequired = (string)ScenarioContext.Current["_provisionGapNumberofPlacements"];
string _placementsKnown = (string)ScenarioContext.Current["_provisionGapplacementsKnown"];
string query = ("select oi.id, o.EmployerContact, o.EmployerContactEmail, o.EmployerContactPhone, OI.OpportunityType, oi.CreatedBy, oi.JobRole, oi.Postcode, oi.PlacementsKnown, oi.Placements, e.CompanyName, oi.SearchRadius from Employer E, Opportunity O, OpportunityItem OI where o.EmployerCrmId = e.CrmId and o.Id = OI.OpportunityId and oi.id in (select max (oi.id) from Employer E, Opportunity O, OpportunityItem OI where o.EmployerCrmId = e.CrmId and o.Id = OI.OpportunityId and e.CompanyName = '" + EmployerName + "')");
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
foreach (object[] fieldNo in queryResults)
{
//Assign values to variables from the SQL query run
string id = fieldNo[0].ToString();
string employercontact = fieldNo[1].ToString();
string employercontactemail = fieldNo[2].ToString();
string employercontactphone = fieldNo[3].ToString();
string OpportunityType = fieldNo[4].ToString();
string jobRole = fieldNo[6].ToString();
string searchPostcode = fieldNo[7].ToString();
string placementsKnown = fieldNo[8].ToString();
string numberofPlacements = fieldNo[9].ToString();
//if (numberofPlacements == "1")
//{
// _placementsKnown = "False";
// _placementsRequired = "1";
//}
//else
//{
// _placementsKnown = "True";
//}
//Assert the variables above to the actual values displayed on the screen
Console.WriteLine("1");
PageInteractionHelper.VerifyText(employercontact.ToUpper(), _employercontact.ToUpper());
Console.WriteLine("2");
PageInteractionHelper.VerifyText(employercontactemail, _employercontactemail);
Console.WriteLine("3");
PageInteractionHelper.VerifyText(employercontactphone, _employercontactphone);
Console.WriteLine("4");
PageInteractionHelper.VerifyText(OpportunityType, _opportunityType);
Console.WriteLine("5");
PageInteractionHelper.VerifyText(searchPostcode, _searchPostcode);
Console.WriteLine("6");
PageInteractionHelper.VerifyText(placementsKnown, _placementsKnown);
Console.WriteLine("7");
PageInteractionHelper.VerifyText(numberofPlacements, _placementsRequired);
}
return(this);
}
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 string CreateRegistrationAppealUrl(string uln)
{
var profileId = SqlQueries.ReturnRegistrationProfileForLrs(uln);
var pathwayId = SqlQueries.ReturnRegistrationPathwayForLrs(profileId);
var retrieveTqPathwayAssessmentId = "Select top 1 Id from TqPathwayAssessment where TqRegistrationPathwayId =" + pathwayId;
var pathwayAssessmentId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrieveTqPathwayAssessmentId, ConnectionString);
var url = "https://test.manage-tlevel-results.tlevels.gov.uk/reviews-and-appeals-learner-status/" + profileId + "/" + pathwayAssessmentId[0][0];
return(url);
}
private List <Object[]> GetConfirmationSelected()
{
String query = ("Select ConfirmationSelected from opportunity where id = " + opportunityID);
Console.WriteLine(query);
Console.WriteLine("Opportunity ID: " + opportunityID);
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
return(queryResults);
}
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 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());
}
private List <Object[]> GetProvisionGapCount()
{
String query = ("select count(*) from ProvisionGap where opportunityID = " + opportunityID);
Console.WriteLine(query);
Console.WriteLine("Opportunity ID: " + opportunityID);
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
return(queryResults);
}
public static int RetrieveResultStatus(string uln)
{
var profileId = SqlQueries.ReturnRegistrationProfileForLrs(uln);
var pathwayId = SqlQueries.ReturnRegistrationPathwayForLrs(profileId);
var retrieveTqPathwayAssessmentId = "Select top 1 Id from TqPathwayAssessment where TqRegistrationPathwayId =" + pathwayId;
var pathwayAssessmentId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrieveTqPathwayAssessmentId, ConnectionString);
var tqPathwayResultStatusValue = "select top 1 prsstatus from TqPathwayResult where TqPathwayAssessmentId =" + pathwayAssessmentId[0][0] + " order by id desc";
var resultStatusValue = SqlDatabaseConncetionHelper.ReadDataFromDataBase(tqPathwayResultStatusValue, ConnectionString);
int resultStatusValue1 = Convert.ToInt32(resultStatusValue[0][0]);
return(resultStatusValue1);
}
public static string RetrieveResultCreatedOnData(string uln)
{
var profileId = SqlQueries.ReturnRegistrationProfileForLrs(uln);
var pathwayId = SqlQueries.ReturnRegistrationPathwayForLrs(profileId);
var retrieveTqPathwayAssessmentId = "Select top 1 Id from TqPathwayAssessment where TqRegistrationPathwayId =" + pathwayId;
var pathwayAssessmentId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrieveTqPathwayAssessmentId, ConnectionString);
var tqPathwayResultCreatedByValue = "select top 1 createdOn from TqPathwayResult where TqPathwayAssessmentId =" + pathwayAssessmentId[0][0] + " order by id desc";
var createdOnValue = SqlDatabaseConncetionHelper.ReadDataFromDataBase(tqPathwayResultCreatedByValue, ConnectionString);
var createdOnValue1 = Convert.ToString(createdOnValue[0][0]);
return(createdOnValue1);
}
public String GetQualificationId(String query)
{
var queryResults = SqlDatabaseConncetionHelper.ReadDataFromDataBase(query, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
String LardIdMappedToService = null;
foreach (object[] LarId in queryResults)
{
LardIdMappedToService = LarId[0].ToString();
}
return(LardIdMappedToService);
}
public void ClearProviderTable()
{
String DeleteProviderQuery = "Delete FROM Provider";
SqlDatabaseConncetionHelper.ExecuteDeleteSqlCommand(DeleteProviderQuery, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
//Confirm theProvider table is cleared down:
String ProviderRecordCount = "Select Count(*) FROM Provider";
var result = SqlDatabaseConncetionHelper.ReadDataFromDataBase(ProviderRecordCount, Configurator.GetConfiguratorInstance().GetMatchingServiceConnectionString());
Assert.AreEqual(result[0][0], 0);
}
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 string RetrieveLatestGrade(string uln)
{
var profileId = SqlQueries.ReturnRegistrationProfileForLrs(uln);
var pathwayId = SqlQueries.ReturnRegistrationPathwayForLrs(profileId);
var retrieveTqPathwayAssessmentId = "Select top 1 Id from TqPathwayAssessment where TqRegistrationPathwayId =" + pathwayId;
var pathwayAssessmentId = SqlDatabaseConncetionHelper.ReadDataFromDataBase(retrieveTqPathwayAssessmentId, ConnectionString);
var tqPathwayResultGradeLookup = "select top 1 TlLookupId from TqPathwayResult where TqPathwayAssessmentId =" + pathwayAssessmentId[0][0] + " order by id desc";
var gradeLookupValue = SqlDatabaseConncetionHelper.ReadDataFromDataBase(tqPathwayResultGradeLookup, ConnectionString);
var lookupGrade = "select value from TlLookup where ID = " + gradeLookupValue[0][0];
var grade = SqlDatabaseConncetionHelper.ReadDataFromDataBase(lookupGrade, ConnectionString);
var grade1 = Convert.ToString(grade[0][0]);
return(grade1);
}
