protected string popRegion(string region) { dataAccess database = new dataAccess(); DataTable RegTbl = null; if (region != "" || region != string.Empty) { RegTbl = database.getTable("SELECT * FROM jobsdb_Region ORDER BY Region"); } else { RegTbl = database.getTable("SELECT * FROM jobsdb_Region"); region = ""; } int arraySize = RegTbl.Rows.Count + 1; ListItem[] item = new ListItem[arraySize]; ddlRegion.Items.Clear(); ddlRegion.Enabled = true; //Populate ddlRegion with Regions from jobsdb_Region specific to the Region item[0] = new ListItem(); item[0].Text = "Any Region"; item[0].Value = ""; ddlRegion.Items.Add(item[0]); for (int i = 0; i < RegTbl.Rows.Count; i++) { item[i + 1] = new ListItem(); item[i + 1].Text = RegTbl.Rows[i]["Region"].ToString(); item[i + 1].Value = RegTbl.Rows[i]["ID"].ToString(); ddlRegion.Items.Add(item[i + 1]); } return(region); }
public DataTable getList(string category, string region, string location, string title, string company) { string queryFilter = ""; string categoryFilter = CategorySearch(category); string regionFilter = RegionSearch(region); string locationFilter = LocationSearch(location); string titleFilter = JobTitleSearch(title); string companyFilter = CompanySearch(company); int filters = 5; string[] filterArray = new string[filters]; filterArray[0] = categoryFilter; filterArray[1] = regionFilter; filterArray[2] = locationFilter; filterArray[3] = titleFilter; filterArray[4] = companyFilter; bool concatenateflag = false; if (categoryFilter == "" && regionFilter == "" && locationFilter == "" && titleFilter == "" && companyFilter == "") { queryFilter = ""; concatenateflag = false; } else { queryFilter = " WHERE "; concatenateflag = true; } if (concatenateflag) { for (int i = 0; i < filters; i++) { if (filterArray[i] != "") { if (queryFilter != " WHERE ") { queryFilter = queryFilter + " AND " + filterArray[i]; } else { queryFilter = queryFilter + filterArray[i]; } } } } dataAccess JobListing = new dataAccess(); string queryString = "SELECT ID, Title, Overview," + "(SELECT CompanyName FROM jobsdb_Employer WHERE (jobsdb_JobPost.Company = ID)) AS Company," + "(SELECT Country FROM jobsdb_Country WHERE (ID = jobsdb_JobPost.Country)) AS Country," + "(SELECT Region FROM jobsdb_Region WHERE (ID = jobsdb_JobPost.Region)) AS Region, Region AS RegionID," + "(SELECT Location FROM jobsdb_Location WHERE (ID = jobsdb_JobPost.Location)) AS Location, Location AS LocationID ," + "(SELECT Category FROM jobsdb_JobCategory WHERE (ID = jobsdb_JobPost.Category)) AS Category, Category AS CategoryID," + "(SELECT JobType FROM jobsdb_JobType WHERE (ID = jobsdb_JobPost.Type)) AS Type," + "PostDate, ExpireDate FROM jobsdb_JobPost" + queryFilter; DataTable list = JobListing.getTable(queryString); return(list); }
protected string popLocation(string location, string regionID) { dataAccess Location = new dataAccess(); DataTable LocTbl = Location.getTable("SELECT * FROM jobsdb_Location WHERE Region = '" + regionID + "' ORDER BY Location"); int arraySize = LocTbl.Rows.Count + 1; ListItem[] item = new ListItem[arraySize]; ddlLocation.Items.Clear(); ddlLocation.Enabled = true; ddlRegion.Enabled = true; //rfvLocation.Enabled = true; //Populate ddlLocation with locations from jobsdb_Location specific to the Region item[0] = new ListItem(); item[0].Text = "Any Location"; item[0].Value = ""; ddlLocation.Items.Add(item[0]); for (int i = 0; i < LocTbl.Rows.Count; i++) { item[i + 1] = new ListItem(); item[i + 1].Text = LocTbl.Rows[i]["Location"].ToString(); item[i + 1].Value = LocTbl.Rows[i]["ID"].ToString(); ddlLocation.Items.Add(item[i + 1]); } return(location); }
public DataTable GetJobseekerEmail(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT ID, FirstName, LastName, Email, (SELECT Country FROM jobsdb_Country WHERE (ID = jobsdb_JobSeeker.Country)) AS Country, Country AS CountryID, (SELECT Region FROM jobsdb_Region WHERE (ID = jobsdb_JobSeeker.Region)) AS Region, Region AS RegionID, (SELECT Location FROM jobsdb_Location WHERE (ID = jobsdb_JobSeeker.Location)) AS Location, Location AS LocationID, (SELECT Nationality FROM jobsdb_Nationality WHERE (ID = jobsdb_JobSeeker.Nationality)) AS Nationality, Nationality AS NationalityID, ID_Passport, ContactNumber, (SELECT Category FROM jobsdb_JobCategory WHERE (ID = jobsdb_JobSeeker.JobCategory)) AS Category, JobCategory AS CategoryID , Experience, (SELECT EducationLevel FROM jobsdb_Education WHERE (ID = jobsdb_JobSeeker.Education)) AS Education, Education AS EducationID,(SELECT Status FROM jobsdb_AccountStatus WHERE (jobsdb_JobSeeker.Status = ID))AS Status, Status AS StatusID FROM jobsdb_JobSeeker WHERE Email ='" + Email + "'"; DataTable jobseekerTbl = database.getTable(queryString); return(jobseekerTbl); }
protected void Page_Load(object sender, EventArgs e) { HttpCookie objck = Request.Cookies["zamjobs_authcookie"]; if (objck == null) // Double checks to ensure user is logged in. If not user redirected to default.aspx { Response.Redirect("~/Default.aspx"); } else { lblUserID.Text = User.Identity.Name; } /*image logo = new image(); * logo.displayAdvertiserLogo(User.Identity.Name);*/ advertiser advertiser = new advertiser(); string ID = advertiser.getCompanyID(User.Identity.Name); dataAccess database = new dataAccess(); System.Data.DataTable logos = database.getTable("SELECT Logo FROM jobsdb_Employer Where ID='" + ID + "'"); if (logos.Rows[0]["Logo"].ToString() == "") { imgLogo.ImageUrl = "~/Images/no-logo.gif"; } else { imgLogo.ImageUrl = "Profile.aspx?imageID=" + ID; displayAdvertiserLogo(); } }
public string GetAdminIDByEmail(string email) { dataAccess database = new dataAccess(); DataTable adminIDTbl = database.getTable("SELECT ID FROM jobsdb_Administrator WHERE Email='" + email + "'"); return(adminIDTbl.Rows[0]["ID"].ToString()); }
public int GetAdminCount() { dataAccess database = new dataAccess(); DataTable count = database.getTable("SELECT COUNT(*) FROM jobsdb_Administrator"); return(Convert.ToInt32(count.Rows[0][0].ToString())); }
public string GetAdminEmail(string adminID) { dataAccess database = new dataAccess(); DataTable emailTbl = database.getTable("SELECT Email FROM jobsdb_Administrator WHERE ID ='" + adminID + "'"); return(emailTbl.Rows[0]["Email"].ToString()); }
public bool DeleteAdmin(string adminID) { bool flag = false; bool deleteAdmin = false; bool deleteMembership = false; dataAccess database = new dataAccess(); string email = GetAdminEmail(adminID); try { deleteAdmin = database.DeleteData("DELETE FROM jobsdb_Administrator WHERE ID='" + adminID + "'"); Roles.RemoveUserFromRole(email, "admin"); deleteMembership = Membership.DeleteUser(email, true); if (deleteAdmin && deleteMembership) { flag = true; } } catch (Exception) { } return(flag); }
public bool PurchaseJobseeker(string jobseekerID, string email) { string id = getCompanyID(email); dataAccess database = new dataAccess(); return(database.insertData("INSERT INTO jobsdb_PurchaseJobseeker (Company,Jobseeker) VALUES (" + id + "," + jobseekerID + ")")); }
public DateTime GetExpireDateByJobPostID(string jobPostID) { dataAccess database = new dataAccess(); DataTable date = database.getTable("SELECT ExpireDate From jobsdb_JobPost WHERE ID='" + jobPostID + "'"); return(Convert.ToDateTime(string.Format("{0:dd/MM/YYYY}", date.Rows[0][0].ToString()))); }
public string getCompanyID(string Email) { dataAccess database = new dataAccess(); string queryString = "SELECT ID FROM jobsdb_Employer WHERE Email ='" + Email + "'"; DataTable companyTable = database.getTable(queryString); return(companyTable.Rows[0]["ID"].ToString()); }
public DataTable getOrderNum(string companyID) { dataAccess database = new dataAccess(); string queryString = "SELECT OrderNum FROM jobsdb_UnitOrder WHERE Employer='" + companyID + "'"; DataTable orderNums = database.getTable(queryString); return(orderNums); }
public DataTable GetAdminByID(string adminID) { dataAccess database = new dataAccess(); string selectQuery = @"SELECT * FROM jobsdb_Administrator WHERE ID='" + adminID + "'"; DataTable admins = database.getTable(selectQuery); return(admins); }
public DataTable getJobPostIDByCompanyEmail(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT ID FROM jobsdb_JobPost WHERE (Company ='" + getCompanyID(Email) + "')"; DataTable jobPost = database.getTable(queryString); return(jobPost); }
public string GetUnitCountByEmail(string email) { dataAccess database = new dataAccess(); string queryString = "SELECT Units FROM jobsdb_Employer WHERE Email='" + email + "'"; DataTable unitTbl = database.getTable(queryString); return(unitTbl.Rows[0][0].ToString()); }
public DataTable getJobSeekerDetails(string jobSeekerID) { dataAccess database = new dataAccess(); string queryString = @"SELECT * FROM jobsdb_Jobseeker WHERE (ID = '" + jobSeekerID + "')"; DataTable jobSeekerDetails = database.getTable(queryString); return(jobSeekerDetails); }
public string GetJobseekerName(string jobseekerID) { dataAccess database = new dataAccess(); Format format = new Format(); DataTable jobseeker = database.getTable("SELECT FirstName, LastName FROM jobsdb_JobSeeker WHERE ID='" + jobseekerID + "'"); string name = format.GetJobseekerIdFormat(jobseekerID) + "_" + jobseeker.Rows[0]["FirstName"].ToString() + "_" + jobseeker.Rows[0]["LastName"].ToString(); return(name); }
public bool UpdateOrderActivate(string orderNum) { string updateString = "UPDATE jobsdb_UnitOrder SET Active = 'Y' WHERE OrderNum='" + orderNum + "'"; bool flag = false; dataAccess database = new dataAccess(); flag = database.updateData(updateString); return(flag); }
public string GetUnitAmount() { string UnitAmount = ""; dataAccess database = new dataAccess(); string selectQuery = "SELECT * FROM jobsdb_StoredValues WHERE Description = 'Unit Amount' "; DataTable UnitAmountTbl = database.getTable(selectQuery); UnitAmount = UnitAmountTbl.Rows[0]["Value"].ToString(); return(UnitAmount); }
public bool ActivateAdmin(string adminID) { bool flag = false; dataAccess database = new dataAccess(); string email = GetAdminEmail(adminID); flag = database.updateData("UPDATE jobsdb_Administrator SET Active = 'True' WHERE ID='" + adminID + "'"); database.updateData("UPDATE aspnet_Membership SET IsLockedOut = 'False' WHERE Email='" + email + "'"); return(flag); }
public int getApplicationsCount(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT JobPostID, JobseekerID FROM jobsdb_Application, jobsdb_JobPost WHERE jobsdb_Application.JobPostID = jobsdb_JobPost.ID AND jobsdb_JobPost.Company ='" + getCompanyID(Email) + "'"; DataTable application = database.getTable(queryString); return(application.Rows.Count); }
public DataTable getJobSeeker(string jobPostID) { dataAccess database = new dataAccess(); string queryString = @"SELECT jobsdb_Application.JobseekerID, jobsdb_JobSeeker.FirstName, jobsdb_JobSeeker.LastName, jobsdb_JobSeeker.Email, jobsdb_JobSeeker.ContactNumber FROM jobsdb_Application INNER JOIN jobsdb_JobSeeker ON jobsdb_Application.JobseekerID = jobsdb_JobSeeker.ID WHERE (jobsdb_Application.JobPostID = '" + jobPostID + "')"; DataTable jobSeeker = database.getTable(queryString); return(jobSeeker); }
public DataTable GetOrdersByOrderNum(string orderNum) { dataAccess database = new dataAccess(); string selectQuery = @"SELECT OrderNum, Employer AS CompanyID, (SELECT CompanyName FROM jobsdb_Employer WHERE (ID = jobsdb_UnitOrder.Employer)) AS CompanyName, UnitAmount, UnitQty, Date, Paid, Credited, Active FROM jobsdb_UnitOrder WHERE OrderNum='" + orderNum + "'"; DataTable orders = database.getTable(selectQuery); return(orders); }
public DataTable getApplications(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT jobsdb_Application.JobPostID, jobsdb_JobPost.Title, jobsdb_JobPost.PostDate, jobsdb_JobPost.ExpireDate, jobsdb_Application.JobseekerID, jobsdb_JobSeeker.FirstName, jobsdb_JobSeeker.LastName, jobsdb_JobSeeker.Email, jobsdb_JobSeeker.ContactNumber FROM jobsdb_JobSeeker INNER JOIN jobsdb_Application ON jobsdb_JobSeeker.ID = jobsdb_Application.JobseekerID INNER JOIN jobsdb_JobPost ON jobsdb_Application.JobPostID = jobsdb_JobPost.ID WHERE jobsdb_JobPost.Company ='" + getCompanyID(Email) + "' ORDER BY jobsdb_Application.JobPostID"; DataTable application = database.getTable(queryString); return(application); }
public bool CheckJobseekerStatus(string jobseekerID) // return true if jobseeker is active and visible { dataAccess database = new dataAccess(); bool flag = false; DataTable count = database.getTable("SELECT COUNT(*) FROM jobsdb_JobSeeker WHERE Active='Y' AND Status='1' AND ID='" + jobseekerID + "'"); if (count.Rows[0][0].ToString() == "1") { flag = true; } return(flag); }
public DataTable getJobPost(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT jobsdb_Application.JobPostID,jobsdb_JobPost.Title, jobsdb_JobPost.PostDate, jobsdb_JobPost.ExpireDate FROM jobsdb_Application INNER JOIN jobsdb_JobPost ON jobsdb_Application.JobPostID = jobsdb_JobPost.ID WHERE (jobsdb_JobPost.Company = '" + getCompanyID(Email) + @"') GROUP BY jobsdb_Application.JobPostID, jobsdb_JobPost.Title, jobsdb_JobPost.PostDate, jobsdb_JobPost.ExpireDate"; DataTable jobPost = database.getTable(queryString); return(jobPost); }
///////////////////////////////////////////////////////////// /// ZamJobs Search Functions /// /// Created by Jarred Jardine /// /// /// /// The following function allows the jobsdb to be /// /// searched by specific criteria including job title, /// /// job category, region, location, /// /// /// /// It works by creaing a dynamic sql select query that /// /// changes apon input. /// /// /// ///////////////////////////////////////////////////////////// public DataTable getList(string category, string region, string location, string title, string country) { string queryFilter = ""; string categoryFilter = CategorySearch(category); string regionFilter = RegionSearch(region); string locationFilter = LocationSearch(location); string titleFilter = JobTitleSearch(title); string countryFilter = CountrySearch(country); int filters = 5; string[] filterArray = new string[filters]; filterArray[0] = categoryFilter; filterArray[1] = regionFilter; filterArray[2] = locationFilter; filterArray[3] = titleFilter; filterArray[4] = countryFilter; bool concatenateflag = false; if (categoryFilter == "" && regionFilter == "" && locationFilter == "" && titleFilter == "" && countryFilter == "") { queryFilter = ""; concatenateflag = false; } else { queryFilter = " WHERE "; concatenateflag = true; } if (concatenateflag) { for (int i = 0; i < filters; i++) { if (filterArray[i] != "") { if (queryFilter != " WHERE ") { queryFilter = queryFilter + " AND " + filterArray[i]; } else { queryFilter = queryFilter + filterArray[i]; } } } } dataAccess JobListing = new dataAccess(); string queryString = "SELECT * FROM jobsdb_ActiveJobPosts_View" + queryFilter; DataTable list = JobListing.getTable(queryString); return(list); }
public bool CheckExistingPurchaseJobseeker(string jobseekerID, string email) // true if user exists { string id = getCompanyID(email); dataAccess database = new dataAccess(); bool flag = false; DataTable count = database.getTable("SELECT COUNT(*) FROM jobsdb_PurchaseJobseeker WHERE Company='" + id + "' AND Jobseeker='" + jobseekerID + "'"); if (count.Rows[0][0].ToString() == "1") { flag = true; } return(flag); }
public DataTable GetApplications(string Email) { dataAccess database = new dataAccess(); string queryString = @"SELECT jobsdb_JobPost.ID AS JobPostID, jobsdb_JobPost.Title, jobsdb_Employer.ID AS CompanyID, jobsdb_Employer.CompanyName, jobsdb_JobPost.PostDate, jobsdb_JobPost.ExpireDate FROM jobsdb_JobSeeker INNER JOIN jobsdb_Application ON jobsdb_JobSeeker.ID = jobsdb_Application.JobseekerID INNER JOIN jobsdb_JobPost ON jobsdb_Application.JobPostID = jobsdb_JobPost.ID INNER JOIN jobsdb_Employer ON jobsdb_JobPost.Company = jobsdb_Employer.ID WHERE (jobsdb_JobSeeker.Email ='" + Email + "')"; DataTable application = database.getTable(queryString); return(application); }