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 bool checkUserAccessToJobseeker(string email, string jobseekerID) // checks to see whether advertiser has purchased jobseeker or jobseeker applied for job. return bool { bool flag = false; string companyID = getCompanyID(email); dataAccess database = new dataAccess(); DataTable purchasedTbl = database.getTable("SELECT COUNT(*) FROM jobsdb_PurchaseJobseeker WHERE Company='" + companyID + "' AND Jobseeker='" + jobseekerID + "'"); DataTable appliedTbl = database.getTable("SELECT COUNT(*) FROM jobsdb_JobPost INNER JOIN jobsdb_Application ON jobsdb_JobPost.ID = jobsdb_Application.JobPostID WHERE (jobsdb_JobPost.Company='" + companyID + "') AND (jobsdb_Application.JobseekerID='" + jobseekerID + "')"); if (purchasedTbl.Rows[0][0].ToString() == "1" || appliedTbl.Rows[0][0].ToString() == "1") { flag = true; } return(flag); }
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 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); }
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 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 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 int GetAdminCount() { dataAccess database = new dataAccess(); DataTable count = database.getTable("SELECT COUNT(*) FROM jobsdb_Administrator"); return(Convert.ToInt32(count.Rows[0][0].ToString())); }
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 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 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 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 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 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); }
protected void btnApply_Click(object sender, EventArgs e) { SqlConnection connString = new SqlConnection(ConfigurationManager.ConnectionStrings["ZamJobsConnString"].ConnectionString); dataAccess applicant = new dataAccess(); DataTable idTable = applicant.getTable("SELECT ID FROM jobsdb_Jobseeker WHERE Email = '" + User.Identity.Name + "'"); string id = idTable.Rows[0]["ID"].ToString(); string jobPost = Request.QueryString["PostID"]; int userId = Convert.ToInt32(id); int postID = Convert.ToInt32(jobPost); DataTable application = applicant.getTable("SELECT JobseekerID, JobPostID FROM jobsdb_Application WHERE JobseekerID = " + userId + " AND JobPostID = " + postID); if (application.Rows.Count == 0) { string insertString = "INSERT INTO jobsdb_Application (JobseekerID, JobPostID, Date) VALUES ('" + userId + "','" + postID + "','" + DateTime.Now.ToShortDateString() + "')"; try { SqlCommand cmd = new SqlCommand(insertString, connString); connString.Open(); // Call ExecuteNonQuery to send command cmd.ExecuteNonQuery(); connString.Close(); lblMessage.Text = "Application successful. Thankyou for applying."; lblMessage.ForeColor = System.Drawing.Color.Green; lblMessage.Visible = true; } catch (SqlException) { lblMessage.Text = "An Error has occured with your application. Please retry. If problem persists please contact us."; lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Visible = true; } } else { lblMessage.Text = "You have Already Applied for this job."; lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Visible = true; } }
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 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); }
///////////////////////////////////////////////////////////// /// 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 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); }
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 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 bool CreditAdvertiser(string orderNum) { bool flag = false; dataAccess database = new dataAccess(); string selectAdvertiser = "SELECT Employer, UnitQty FROM jobsdb_UnitOrder WHERE OrderNum='" + orderNum + "'"; DataTable advertiser = database.getTable(selectAdvertiser); string advertiserID = advertiser.Rows[0]["Employer"].ToString(); string selectCompanyName = "SELECT CompanyName,Units FROM jobsdb_Employer WHERE ID='" + advertiserID + "'"; DataTable companyRecord = database.getTable(selectCompanyName); SetAdvertiserName(companyRecord.Rows[0]["CompanyName"].ToString()); string unitQty = advertiser.Rows[0]["UnitQty"].ToString(); SetUnitQty(unitQty); Int32 unitValue = Convert.ToInt32(companyRecord.Rows[0]["Units"].ToString()) + Convert.ToInt32(unitQty); string updateString = "UPDATE jobsdb_Employer SET Units='" + unitValue + "' WHERE ID ='" + advertiserID + "'"; flag = database.updateData(updateString); 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); }
public bool DepleteUnitsByEmail(int amount, string email) { dataAccess database = new dataAccess(); bool flag = false; DataTable advertiserUnitTbl = database.getTable("SELECT Units FROM jobsdb_Employer WHERE Email='" + email + "'"); Int32 units = Convert.ToInt32(advertiserUnitTbl.Rows[0][0].ToString()); if (amount <= units) { units = units - amount; flag = database.updateData("Update jobsdb_Employer SET Units='" + units + "' WHERE Email='" + email + "'"); } return(flag); }