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);
}
protected string popEducation(string education)
{
dataAccess database = new dataAccess();
DataTable eduTbl = database.getTable("SELECT * FROM jobsdb_Education");
int arraySize = eduTbl.Rows.Count + 1;
ListItem[] item = new ListItem[arraySize];
ddlEducation.Items.Clear();
ddlEducation.Enabled = true;
//Populate ddlRegion with Regions from jobsdb_Region specific to the Region
item[0] = new ListItem();
item[0].Text = "Select Education Level";
item[0].Value = "";
ddlEducation.Items.Add(item[0]);
for (int i = 0; i < eduTbl.Rows.Count; i++)
{
item[i + 1] = new ListItem();
item[i + 1].Text = eduTbl.Rows[i]["EducationLevel"].ToString();
item[i + 1].Value = eduTbl.Rows[i]["ID"].ToString();
ddlEducation.Items.Add(item[i + 1]);
}
return(education);
}
protected string popExperience(string experience)
{
dataAccess database = new dataAccess();
DataTable expTbl = database.getTable("SELECT * FROM jobsdb_Experience");
int arraySize = expTbl.Rows.Count + 1;
ListItem[] item = new ListItem[arraySize];
ddlWorkExperience.Items.Clear();
ddlWorkExperience.Enabled = true;
//Populate ddlRegion with Regions from jobsdb_Region specific to the Region
item[0] = new ListItem();
item[0].Text = "Select Experience";
item[0].Value = "";
ddlWorkExperience.Items.Add(item[0]);
for (int i = 0; i < expTbl.Rows.Count; i++)
{
item[i + 1] = new ListItem();
item[i + 1].Text = expTbl.Rows[i]["Years"].ToString() + " years";
item[i + 1].Value = expTbl.Rows[i]["Years"].ToString();
ddlWorkExperience.Items.Add(item[i + 1]);
}
return(experience);
}
protected string popCountry(string countryID, string country)
{
dataAccess database = new dataAccess();
DataTable countryTbl = database.getTable("SELECT * FROM jobsdb_Country ORDER BY Country");
int arraySize = countryTbl.Rows.Count + 1;
ListItem[] item = new ListItem[arraySize];
ddlCountry.Items.Clear();
ddlCountry.Enabled = true;
//Populate ddlRegion with Regions from jobsdb_Region specific to the Region
item[0] = new ListItem();
item[0].Text = "Select Country";
item[0].Value = "";
ddlCountry.Items.Add(item[0]);
for (int i = 0; i < countryTbl.Rows.Count; i++)
{
item[i + 1] = new ListItem();
item[i + 1].Text = countryTbl.Rows[i]["Country"].ToString();
item[i + 1].Value = countryTbl.Rows[i]["ID"].ToString();
ddlCountry.Items.Add(item[i + 1]);
}
if (country != "Zambia")
{
ddlLocation.Enabled = false;
ddlRegion.Enabled = false;
}
return(countryID);
}
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);
}
protected string popCategory(string advertiserCategory)
{
dataAccess database = new dataAccess();
DataTable CatTbl = database.getTable("SELECT * FROM jobsdb_JobCategory ORDER BY Category");
int arraySize = CatTbl.Rows.Count + 1;
ListItem[] item = new ListItem[arraySize];
ddlCategory.Items.Clear();
ddlCategory.Enabled = true;
//Populate ddlRegion with Regions from jobsdb_Region specific to the Region
item[0] = new ListItem();
item[0].Text = "Select Category";
item[0].Value = "";
ddlCategory.Items.Add(item[0]);
for (int i = 0; i < CatTbl.Rows.Count; i++)
{
item[i + 1] = new ListItem();
item[i + 1].Text = CatTbl.Rows[i]["Category"].ToString();
item[i + 1].Value = CatTbl.Rows[i]["ID"].ToString();
ddlCategory.Items.Add(item[i + 1]);
}
return(advertiserCategory);
}
protected string popNationality(string nationality)
{
dataAccess database = new dataAccess();
DataTable natTbl = database.getTable("SELECT * FROM jobsdb_Nationality");
int arraySize = natTbl.Rows.Count + 1;
ListItem[] item = new ListItem[arraySize];
ddlNationality.Items.Clear();
ddlNationality.Enabled = true;
//Populate ddlRegion with Regions from jobsdb_Region specific to the Region
item[0] = new ListItem();
item[0].Text = "Select Nationality";
item[0].Value = "";
ddlNationality.Items.Add(item[0]);
for (int i = 0; i < natTbl.Rows.Count; i++)
{
item[i + 1] = new ListItem();
item[i + 1].Text = natTbl.Rows[i]["Nationality"].ToString();
item[i + 1].Value = natTbl.Rows[i]["ID"].ToString();
ddlNationality.Items.Add(item[i + 1]);
}
return(nationality);
}
protected void Page_Load(object sender, EventArgs e)
{
FillTable("", "");
LoadBtn();
GridView1.PageSize = Convert.ToInt32(ddlRecordsPerPage.SelectedValue);
administrator order = new administrator();
DataTable orderTbl = order.GetOrders();
lblOrderCount.Text = orderTbl.Rows.Count.ToString();
orderNumTbl = database.getTable("SELECT OrderNum FROM jobsdb_UnitOrder");
}
protected void btnCreate_Click(object sender, EventArgs e)
{
dataAccess database = new dataAccess();
Format format = new Format();
DataTable dt = database.getTable("SELECT Email from aspnet_Membership WHERE Email = '" + txtEmail.Text.ToLower() + "'");
if (dt.Rows.Count == 0) // Check for existing email in database. If not zero email exists. Display invalid email
{
bool flag = false;
string fname = format.capitalizeStart(txtFName.Text.ToString());
string lname = format.capitalizeStart(txtLName.Text.ToString());
string contactNum = txtContactNum.Text.ToString();
string passportID = txtPassportID.Text.ToString();
string email = txtEmail.Text.ToString();
string address = txtPhysicalAddress.Text.ToString();
string password = txtPassword.Text.ToString();
string insertString = "INSERT INTO jobsdb_Administrator (FirstName, LastName, ContactNumber,Email, PhysicalAddress, PassportID, Active) VALUES ('" + fname + "','" + lname + "','" + contactNum + "','" + email + "','" + address + "','" + passportID + "','True')";
try
{
flag = database.insertData(insertString);
if (flag)
{
//Creste user with Email and Password given
Membership.CreateUser(email, password, email);
//Assign user admin role
Roles.AddUserToRole(email, ConfigurationSettings.AppSettings["AdminRoleName"]);
}
}
catch (Exception)
{
}
if (flag)
{
Response.Redirect("RegistrationSuccessful.aspx");
}
else
{
Response.Redirect("RegistrationUnsuccessful.aspx");
}
}
else
{
lblMessage.Text = "Email already exists. Please use another";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
public string GetRegion(string region)
{
string regionName = "";
if (region != "" && region != null)
{
dataAccess database = new dataAccess();
DataTable regTbl = database.getTable("SELECT Region FROM jobsdb_Region WHERE ID='" + region + "'");
regionName = regTbl.Rows[0]["Region"].ToString();
}
else
{
regionName = "Any Region";
}
return(regionName);
}
public void displayAdvertiserLogo()
{
try
{
dataAccess database = new dataAccess();
string queryString = Request.QueryString["imageID"].ToString();
System.Data.DataTable logos = database.getTable("SELECT Logo FROM jobsdb_Employer Where ID='" + queryString + "'");
Byte[] buffer = (Byte[])logos.Rows[0]["logo"];
Response.Clear();
Response.ContentType = "Image/jpeg";
Response.BinaryWrite(buffer);
}
catch (NullReferenceException)
{
}
}
public string GetEducation(string education)
{
string educationName = "";
if (education != "" && education != null)
{
dataAccess database = new dataAccess();
DataTable conTbl = database.getTable("SELECT EducationLevel FROM jobsdb_Education WHERE ID='" + education + "'");
educationName = conTbl.Rows[0]["EducationLevel"].ToString();
}
else
{
educationName = "Any Education";
}
return(educationName);
}
public string GetCountry(string country)
{
string countryName = "";
if (country != "" && country != null)
{
dataAccess database = new dataAccess();
DataTable conTbl = database.getTable("SELECT Country FROM jobsdb_Country WHERE ID='" + country + "'");
countryName = conTbl.Rows[0]["Country"].ToString();
}
else
{
countryName = "Any Country";
}
return(countryName);
}
public string GetLocation(string location)
{
string locationName = "";
if (location != "" && location != null)
{
dataAccess database = new dataAccess();
DataTable locTbl = database.getTable("SELECT Location FROM jobsdb_Location WHERE ID='" + location + "'");
locationName = locTbl.Rows[0]["Location"].ToString();
}
else
{
locationName = "Any Location";
}
return(locationName);
}
public string GetCategory(string category)
{
string categoryName = "";
if (category != "" && category != null)
{
dataAccess database = new dataAccess();
DataTable catTbl = database.getTable("SELECT Category FROM jobsdb_JobCategory WHERE ID='" + category + "'");
categoryName = catTbl.Rows[0]["Category"].ToString();
}
else
{
categoryName = "Any Category";
}
return(categoryName);
}
protected void Page_Load(object sender, EventArgs e)
{
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 = "UploadLogo.aspx?imageID=" + ID;
displayAdvertiserLogo();
}
}
protected void ddlRegion_SelectedIndexChanged(object sender, EventArgs e)
{
// Changes ddlLocation acording to region
lblCountryBind.Visible = false;
lblRegionBind.Visible = false;
lblLocationBind.Visible = false;
ddlCountry.Visible = true;
ddlRegion.Visible = true;
ddlLocation.Visible = true;
if (ddlRegion.SelectedValue.ToString() != "" && ddlCountry.SelectedItem.ToString() == "Zambia")
{
//Get table of location for specific region selected in ddlRegion
dataAccess Location = new dataAccess();
DataTable LocTbl = Location.getTable("SELECT * FROM jobsdb_Location WHERE Region = " + ddlRegion.SelectedValue + "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 = "Select 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]);
}
}
else
{
ddlLocation.Enabled = false;
ddlRegion.Enabled = true;
ddlLocation.SelectedValue = "";
rfvLocation.Enabled = false;
}
}
protected void btnPost_Click(object sender, EventArgs e)
{
SqlConnection connString = new SqlConnection(ConfigurationManager.ConnectionStrings["ZamJobsConnString"].ConnectionString);
string title = txtJobTitle.Text;
string overview = txtJobOverview.Text;
string companyEmail = User.Identity.Name;
dataAccess advertiser = new dataAccess();
advertiser post = new advertiser();
string totalUnits = post.GetUnitCountByEmail(User.Identity.Name);
DataTable IdTbl = advertiser.getTable("SELECT ID FROM jobsdb_Employer WHERE email = '" + companyEmail + "'");
Int16 companyID = Convert.ToInt16(IdTbl.Rows[0]["ID"]);
Int16 country = Convert.ToInt16(ddlCountry.SelectedValue);
Int16 region = 0;
if (ddlRegion.SelectedValue != "")
{
region = Convert.ToInt16(ddlRegion.SelectedValue);
}
Int16 location = 0;
if (ddlLocation.SelectedValue != "")
{
location = Convert.ToInt16(ddlLocation.SelectedValue);
}
Int16 type = Convert.ToInt16(ddlJobType.SelectedValue);
Int16 category = Convert.ToInt16(ddlCategory.SelectedValue);
Int16 jobType = Convert.ToInt16(ddlJobType.SelectedValue);
Int16 experience = Convert.ToInt16(ddlWorkExperience.SelectedValue);
string exerienceComment = txtWorkExperience.Text;
Int16 education = Convert.ToInt16(ddlEducation.SelectedValue);
string educationComment = txtEducation.Text;
string personalAttributes = txtPesonalAttributes.Text;
string competencies = txtCompetencies.Text;
string postDate = string.Format("{0:yyyy/MM/dd}", DateTime.Today);
int unitAmount = 0;
string expiryDate = string.Format("{0:yyyy/MM/dd}", DateTime.Now.AddDays(Convert.ToDouble(ddlAdvertLength.SelectedValue.ToString())));
if (ddlAdvertLength.SelectedValue.ToString() == "15")
{
unitAmount = 1;
}
else if (ddlAdvertLength.SelectedValue.ToString() == "30")
{
unitAmount = 2;
}
// create query string to insert data in database
string insertString = @"
insert into jobsdb_JobPost
(Title,Overview,Company,Country,Region,Location,Category,Type,
WorkExperience,WorkExperienceComment,EducationLevel,EducationLevelComment,PersonalAttributes,Competencies,PostDate,ExpireDate)
values ('" + title + "','" + overview + "','" + companyID + "','" + country + "','" + region + "','" + location +
"','" + category + "','" + type + "','" + experience + "','" + exerienceComment + "','" + education +
"','" + educationComment + "','" + personalAttributes + "','" + competencies + "','" + postDate + "','" + expiryDate + "'); SELECT SCOPE_IDENTITY()";
try
{
bool flag = post.DepleteUnitsByEmail(unitAmount, User.Identity.Name);
//Redirect user to success page
if (flag)
{
//SqlCommand cmd = new SqlCommand(insertString, connString);
Int64 id = advertiser.insertData_ReturnID(insertString);
//connString.Open();
// Call ExecuteNonQuery to send command
//cmd.ExecuteNonQuery();
//connString.Close();
Response.Redirect("PostSuccessfull.aspx?PostID=" + id + "&type=n&amt=" + unitAmount.ToString() + "&units=" + totalUnits);
}
else
{
}
}
catch (SqlException)
{
Response.Redirect("PostUnsuccessfull.aspx");
}
}
public DataTable getList(string category, string region, string location, string country, string education, string experience, string filter)
{
string queryFilter = "";
string categoryFilter = CategorySearch(category);
string regionFilter = RegionSearch(region);
string locationFilter = LocationSearch(location);
string countryFilter = CountrySearch(country);
string educationFilter = EducationSearch(education);
string experienceFilter = ExperienceSearch(experience, filter);
int filters = 6;
string[] filterArray = new string[filters];
filterArray[0] = categoryFilter;
filterArray[1] = regionFilter;
filterArray[2] = locationFilter;
filterArray[3] = countryFilter;
filterArray[4] = educationFilter;
filterArray[5] = experienceFilter;
bool concatenateflag = false;
if (categoryFilter == "" && regionFilter == "" && locationFilter == "" && countryFilter == "" && educationFilter == "" && experienceFilter == "")
{
queryFilter = "";
concatenateflag = false;
}
else
{
queryFilter = " AND ";
concatenateflag = true;
}
if (concatenateflag)
{
for (int i = 0; i < filters; i++)
{
if (filterArray[i] != "")
{
if (queryFilter != " AND ")
{
queryFilter = queryFilter + " AND " + filterArray[i];
}
else
{
queryFilter = queryFilter + filterArray[i];
}
}
}
}
dataAccess JobListing = new dataAccess();
string queryString = @"SELECT ID, FirstName, LastName,
(SELECT Country
FROM jobsdb_Country
WHERE (ID = jobsdb_JobSeeker.Country)) AS Country,
(SELECT Region
FROM jobsdb_Region
WHERE (ID = jobsdb_JobSeeker.Region)) AS Region,
(SELECT Location
FROM jobsdb_Location
WHERE (ID = jobsdb_JobSeeker.Location)) AS Location,
(SELECT Nationality
FROM jobsdb_Nationality
WHERE (ID = jobsdb_JobSeeker.Nationality)) AS Nationality,
(SELECT Category
FROM jobsdb_JobCategory
WHERE (ID = jobsdb_JobSeeker.JobCategory)) AS Category,Experience,
(SELECT EducationLevel
FROM jobsdb_Education
WHERE (ID = jobsdb_JobSeeker.Education)) AS Education
FROM jobsdb_JobSeeker WHERE Status ='1' AND Active = 'Y' " + queryFilter;
DataTable list = JobListing.getTable(queryString);
return(list);
}
protected void loadData()
{
btnCancel.Visible = false;
btnCancel.Enabled = false;
btnUpdate.Visible = false;
btnUpdate.Enabled = false;
btnEdit.Visible = true;
btnEdit.Enabled = true;
btnDelete.Visible = true;
btnDelete.Enabled = true;
Format formatObj = new Format();
string UserID = User.Identity.Name;
dataAccess details = new dataAccess();
DataTable advitisor = details.getTable(@"SELECT ID, CompanyName, ContactName, Email,
(SELECT Country
FROM jobsdb_Country
WHERE (ID = jobsdb_Employer.Country)) AS Country,
(SELECT Region
FROM jobsdb_Region
WHERE (ID = jobsdb_Employer.Region)) AS Region, Region as RegionID,
(SELECT Location
FROM jobsdb_Location
WHERE (ID = jobsdb_Employer.Location)) AS Location, Location AS LocationID, ContactNumber, AltContactNumber, PostalAddress, WebsiteURL,
(SELECT Category
FROM jobsdb_JobCategory
WHERE (ID = jobsdb_Employer.Category)) AS Category, Category AS CategoryID, Description
FROM jobsdb_Employer WHERE Email = '" + UserID + "'");
lblIDBind.Text = formatObj.GetAdvertiserIdFormat(advitisor.Rows[0]["ID"].ToString());
lblIDBind.Visible = true;
//txtCompanyID.Text = advitisor.Rows[0]["ID"].ToString();
lblEmailBind.Text = advitisor.Rows[0]["Email"].ToString();
lblEmailBind.Visible = true;
//txtEmail.Text = advitisor.Rows[0]["Email"].ToString();
//txtCompanyName.Text = advitisor.Rows[0]["CompanyName"].ToString() ;
lblCompanyNameBind.Text = advitisor.Rows[0]["CompanyName"].ToString();
lblCompanyNameBind.Visible = true;
txtCompanyName.Visible = false;
//txtContactName.Text = advitisor.Rows[0]["ContactName"].ToString();
lblContactNameBind.Text = advitisor.Rows[0]["ContactName"].ToString();
lblContactNameBind.Visible = true;
txtContactName.Visible = false;
//ddlCountry.SelectedValue = advitisor.Rows[0]["Country"].ToString();
lblCountryBind.Text = advitisor.Rows[0]["Country"].ToString();
lblCountryBind.Visible = true;
ddlCountry.Visible = false;
//ddlRegion.SelectedValue = advitisor.Rows[0]["RegionID"].ToString();
lblRegionBind.Text = advitisor.Rows[0]["Region"].ToString();
lblRegionBind.Visible = true;
ddlRegion.Visible = false;
lblRegionHidden.Visible = false;
lblRegionHidden.Text = advitisor.Rows[0]["RegionID"].ToString();
//ddlLocation.SelectedItem.Text = advitisor.Rows[0]["Location"].ToString();
lblLocationBind.Text = advitisor.Rows[0]["Location"].ToString();
lblLocationBind.Visible = true;
ddlLocation.Visible = false;
lblLocationHidden.Text = advitisor.Rows[0]["LocationID"].ToString();
lblLocationHidden.Visible = false;
//txtContactNumber.Text = advitisor.Rows[0]["ContactNumber"].ToString();
lblContactNumberBind.Text = advitisor.Rows[0]["ContactNumber"].ToString();
lblContactNumberBind.Visible = true;
txtContactNumber.Visible = false;
//txtContactNumber2.Text = advitisor.Rows[0]["AltContactNumber"].ToString();
lblAltContactNumberBind.Text = advitisor.Rows[0]["AltContactNumber"].ToString();
lblAltContactNumberBind.Visible = true;
txtContactNumber2.Visible = false;
//txtPostalAddress.Text = advitisor.Rows[0]["PostalAddress"].ToString();
lblPostalAddressBind.Text = advitisor.Rows[0]["PostalAddress"].ToString();
lblPostalAddressBind.Visible = true;
txtPostalAddress.Visible = false;
//txtWebsiteURL.Text = advitisor.Rows[0]["WebsiteURL"].ToString();
lblWebsiteURLBind.Text = advitisor.Rows[0]["WebsiteURL"].ToString();
lblWebsiteURLBind.Visible = true;
txtWebsiteURL.Visible = false;
//ddlCategory.SelectedItem.Text = advitisor.Rows[0]["Category"].ToString();
lblCategoryBind.Text = advitisor.Rows[0]["Category"].ToString();
lblCategoryBind.Visible = true;
ddlCategory.Visible = false;
lblCategoryHidden.Text = advitisor.Rows[0]["CategoryID"].ToString();
lblCategoryHidden.Visible = false;
//txtCompanyDesc.Text = advitisor.Rows[0]["Description"].ToString();
lblOverviewBind.Text = advitisor.Rows[0]["Description"].ToString();
lblOverviewBind.Visible = true;
txtCompanyDesc.Visible = false;
}
