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