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