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