public static string Create(string name, string short_desc, string long_desc, Guid parent)
{
try {
JobBoardDataContext db = new JobBoardDataContext();
if (parent == null || parent == Guid.Empty) {
int parent_count = db.Categories.Where(x => x.parent == null || x.parent == Guid.Empty).Count();
if (parent_count >= 4) {
throw new Exception("There are already (4) parent categories in the system. You may not exceed (4) parent categories. Please add the category a sub-category");
}
}
Category cat = new Category {
id = Guid.NewGuid(),
name = name,
short_desc = short_desc,
long_desc = long_desc,
date_added = DateTime.Now,
parent = parent
};
db.Categories.InsertOnSubmit(cat);
db.SubmitChanges();
return "";
} catch (Exception e) {
return e.Message;
}
}
/// <summary>
/// Retrieve a specific Contact record from the database
/// </summary>
/// <param name="id">Identification of the contact record</param>
/// <returns>DisposableContact</returns>
public static DisplayableContact Get(Guid id)
{
try {
JobBoardDataContext db = new JobBoardDataContext();
DisplayableContact contact = (from c in db.Contacts
join s in db.States on c.state_id equals s.id into DefaultStates
from ds in DefaultStates.DefaultIfEmpty()
where c.id.Equals(id)
select new DisplayableContact {
id = c.id,
name = c.name,
street = c.street,
city = c.city,
state_id = c.state_id,
state = ds.state1,
abbr = ds.abbr,
phone = c.phone,
fax = c.fax,
email = c.email,
username = c.username,
password = c.password,
listing_count = db.Jobs.Where(x => x.contact == c.id).Count(),
level = c.level
}).FirstOrDefault<DisplayableContact>();
return contact;
} catch (Exception) {
return new DisplayableContact();
}
}
/// <summary>
///
/// </summary>
/// <param name="contact_id"></param>
/// <returns></returns>
private bool checkAuth(string contact_id)
{
try {
Guid id = new Guid(contact_id);
if (id.ToString().Length > 0) { // Make sure the id is a valid contact id
JobBoardDataContext db = new JobBoardDataContext();
Contact contact = db.Contacts.Where(x => x.id == id && x.level != "DISABLED").FirstOrDefault<Contact>();
if (contact == null) { throw new Exception(); }
ViewBag.user = contact;
Session["contact_id"] = id;
HttpCookie contactID = new HttpCookie("contact_id");
contactID.Value = id.ToString();
contactID.Expires = DateTime.Now.AddDays(30);
Response.Cookies.Add(contactID);
return true;
} else {
return false;
}
} catch (Exception) {
return false;
}
}
public static List<Education> GetEducation()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
return db.Educations.ToList<Education>();
} catch (Exception) {
return new List<Education>();
}
}
public static Guid Login(string username, string password)
{
if (username.Length == 0 || password.Length == 0) { throw new Exception(); }
JobBoardDataContext db = new JobBoardDataContext();
Guid contact = db.Contacts.Where(x => x.username == username && x.password == password && x.level != "DISABLED").Select(x => x.id).FirstOrDefault<Guid>();
return contact;
}
public static List<TieredCategories> GetMenu()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
List<TieredCategories> cats = new List<TieredCategories>();
// Get the parent categories
List<Category> parents = db.Categories.Where(x => x.parent.Equals(Guid.Empty)).ToList<Category>();
foreach (Category parent in parents) {
// Get the subs of this category
List<Category> subs = (from c in db.Categories
join jc in db.JobCategories on c.id equals jc.cat
join j in db.Jobs on jc.job equals j.id
where c.parent.Equals(parent.id) && j.jobState.Equals(JobState.PUBLISHED.ToString())
select c).ToList<Category>();
// Check to see if there are any jobs tied to this category
//int job_count = db.JobCategories.Where(x => x.cat == parent.id).Count();
int job_count = (from jc in db.JobCategories
join j in db.Jobs on jc.job equals j.id
where jc.cat.Equals(parent.id) && j.jobState.Equals(JobState.PUBLISHED.ToString())
select jc.job).Count();
if (subs.Count > 0 || job_count > 0) {
TieredCategories cat = new TieredCategories {
id = parent.id,
name = parent.name,
short_desc = parent.short_desc,
long_desc = parent.long_desc,
date_added = parent.date_added,
parent = parent.parent,
subs = subs,
jobCount = job_count
};
cats.Add(cat);
}
}
/*List<TieredCategories> cats = (from c in db.Categories
from parent_jobs in c.JobCategories.ToList()
join c2 in db.Categories on c.id equals c2.parent into SubCats
from sub_jobs in SubCats.ToList()
where (c.parent.Equals(Guid.Empty) || sub_jobs.JobCategories.Count > 0) && (parent_jobs.Jobs.Count > 0 || sub_jobs.JobCategories.Count > 0)
select new TieredCategories {
id = c.id,
name = c.name,
short_desc = c.short_desc,
long_desc = c.long_desc,
date_added = c.date_added,
parent = c.parent,
subs = SubCats.ToList<Category>()
}).ToList<TieredCategories>();*/
return cats;
} catch (Exception) {
return new List<TieredCategories>();
}
}
public static List<Experience> GetExperience()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
return db.Experiences.ToList<Experience>();
} catch (Exception) {
return new List<Experience>();
}
}
public static Category Get(Guid id)
{
try {
JobBoardDataContext db = new JobBoardDataContext();
return db.Categories.Where(x => x.id == id).FirstOrDefault<Category>();
} catch (Exception) {
return new Category();
}
}
public static List<Shift> GetAll()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
return db.Shifts.ToList<Shift>();
} catch (Exception) {
return new List<Shift>();
}
}
public ActionResult DeleteFile(Guid id = new Guid(), Guid file_id = new Guid())
{
JobBoardDataContext db = new JobBoardDataContext();
Application app = db.Applications.Where(x => x.id.Equals(id)).FirstOrDefault<Application>();
ApplicationFile file = app.ApplicationFiles.Where(x => x.id.Equals(file_id)).FirstOrDefault<ApplicationFile>();
db.ApplicationFiles.DeleteOnSubmit(file);
db.SubmitChanges();
return RedirectToAction("Resume", new { id = id });
}
public static Shift Get(Guid id)
{
try {
if (id == null || id == Guid.Empty) { throw new Exception("Invalid reference."); }
// Get the Shift record
JobBoardDataContext db = new JobBoardDataContext();
return db.Shifts.Where(x => x.id == id).FirstOrDefault<Shift>();
} catch (Exception) {
return new Shift();
}
}
public static List<Job> GetJobs(Guid id)
{
try {
if (id == null || id == Guid.Empty) { throw new Exception("Invalid reference."); }
// Get the jobs for the given experience level
JobBoardDataContext db = new JobBoardDataContext();
return db.Jobs.Where(x => x.education == id).ToList<Job>();
} catch (Exception) {
return new List<Job>();
}
}
public static List<Job> GetJobs(Guid id)
{
try {
if (id == null || id == Guid.Empty) { throw new Exception("Invalid reference."); }
// Get the jobs for the given experience level
JobBoardDataContext db = new JobBoardDataContext();
List<Job> jobs = (from j in db.Jobs
join js in db.JobShifts on j.id equals js.job
where js.shift.Equals(id)
select j).ToList<Job>();
return jobs;
} catch (Exception) {
return new List<Job>();
}
}
public static List<DisplayableLocation> GetLocations()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
List<DisplayableLocation> locs = (from l in db.Locations
join s in db.States on l.state_id equals s.id
select new DisplayableLocation {
id = l.id,
city = l.city,
state_id = l.state_id,
state = s.state1,
abbr = s.abbr
}).ToList<DisplayableLocation>();
return locs;
} catch (Exception) {
return new List<DisplayableLocation>();
}
}
public static string AddJobShift(Guid job_id, Guid shift_id)
{
JobBoardDataContext db = new JobBoardDataContext();
// Lets make sure we don't already have an association for this category
int exists = db.JobShifts.Where(x => x.shift == shift_id && x.job == job_id).Count();
if (exists > 0) { return "You may not add this shift more than once."; }
JobShift js = new JobShift {
id = Guid.NewGuid(),
job = job_id,
shift = shift_id
};
db.JobShifts.InsertOnSubmit(js);
db.SubmitChanges();
JavaScriptSerializer jss = new JavaScriptSerializer();
return jss.Serialize(js);
}
public static void Delete(Guid id)
{
if (id == null || id == Guid.Empty) {
throw new Exception("Invalid reference.");
}
// Retrieve the record to delete
JobBoardDataContext db = new JobBoardDataContext();
Experience exp = db.Experiences.Where(x => x.id == id).FirstOrDefault<Experience>();
// Get the jobs listings for this expeience level and set their experience to blank
List<Job> jobs = db.Jobs.Where(x => x.experience == id).ToList<Job>();
foreach (Job j in jobs) {
j.experience = Guid.Empty;
}
// Save changes
db.Experiences.DeleteOnSubmit(exp);
db.SubmitChanges();
}
public static void Delete(Guid id)
{
if (id == null || id == Guid.Empty) {
throw new Exception("Invalid reference.");
}
// Retrieve the record to delete
JobBoardDataContext db = new JobBoardDataContext();
Location loc = db.Locations.Where(x => x.id == id).FirstOrDefault<Location>();
// Get the jobs listings for this location and set their location to blank
List<Job> jobs = db.Jobs.Where(x => x.location == id).ToList<Job>();
foreach (Job j in jobs) {
j.location = Guid.Empty;
}
// Save changes
db.Locations.DeleteOnSubmit(loc);
db.SubmitChanges();
}
public static void Create(string education)
{
if (education.Length == 0) { throw new Exception("Invalid experience."); }
JobBoardDataContext db = new JobBoardDataContext();
// Make sure we don't already have a education entry for this
int existing = db.Educations.Where(x => x.edu_level == education).Count();
if (existing > 0) { throw new Exception("Existing entry."); }
// Create new education level
Education ed = new Education {
id = Guid.NewGuid(),
edu_level = education,
date_added = DateTime.Now
};
// Save education
db.Educations.InsertOnSubmit(ed);
db.SubmitChanges();
}
/// <summary>
/// Gets all the Category records with references to their parent category
/// </summary>
/// <returns>List of CategoryWithParent</returns>
public static List<CategoryWithParent> GetAll()
{
try {
JobBoardDataContext db = new JobBoardDataContext();
List<CategoryWithParent> cats = (from c in db.Categories
join c2 in db.Categories on c.parent equals c2.id into DefaultCats
from c3 in DefaultCats.DefaultIfEmpty()
select new CategoryWithParent {
id = c.id,
name = c.name,
short_desc = c.short_desc,
long_desc = c.long_desc,
date_added = c.date_added,
parent = c.parent,
parent_name = c3.name
}).ToList<CategoryWithParent>();
return cats;
} catch (Exception) {
return new List<CategoryWithParent>();
}
}
public static void Create(string shift)
{
if (shift.Length == 0) { throw new Exception("Invalid shift."); }
JobBoardDataContext db = new JobBoardDataContext();
// Make sure we don't already have a education entry for this
int existing = db.Shifts.Where(x => x.shift1 == shift).Count();
if (existing > 0) { throw new Exception("Existing entry."); }
// Create new education level
Shift sh = new Shift {
id = Guid.NewGuid(),
shift1 = shift,
date_added = DateTime.Now
};
// Save education
db.Shifts.InsertOnSubmit(sh);
db.SubmitChanges();
}
public static void Create(string experience)
{
if (experience.Length == 0) { throw new Exception("Invalid experience."); }
JobBoardDataContext db = new JobBoardDataContext();
// Make sure we don't already have a experience entry for this
int existing = db.Experiences.Where(x => x.experience1 == experience).Count();
if (existing > 0) { throw new Exception("Existing entry."); }
// Create new experience level
Experience exp = new Experience {
id = Guid.NewGuid(),
experience1 = experience,
date_added = DateTime.Now
};
// Save experience
db.Experiences.InsertOnSubmit(exp);
db.SubmitChanges();
}
public static void Delete(Guid id)
{
if (id == null || id == Guid.Empty) {
throw new Exception("Invalid reference.");
}
// Retrieve the record to delete
JobBoardDataContext db = new JobBoardDataContext();
Shift shift = db.Shifts.Where(x => x.id == id).FirstOrDefault<Shift>();
// Get the jobs listings for this education level and set their education to blank
List<JobShift> jobs = (from js in db.JobShifts
where js.shift.Equals(shift.id)
select js).ToList<JobShift>();
db.JobShifts.DeleteAllOnSubmit(jobs);
db.SubmitChanges();
// Save changes
db.Shifts.DeleteOnSubmit(shift);
db.SubmitChanges();
}
/// <summary>
/// Creates a new Contact record
/// </summary>
/// <param name="name"></param>
/// <param name="street"></param>
/// <param name="city"></param>
/// <param name="state_id"></param>
/// <param name="phone"></param>
/// <param name="fax"></param>
/// <param name="email"></param>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public static string Create(string name, string street, string city, Guid state_id, string phone, string fax, string email, string username, string password, string level)
{
JobBoardDataContext db = new JobBoardDataContext();
if (password.Length == 0) { username = ""; }
Contact con = new Contact {
id = Guid.NewGuid(),
name = name,
street = street,
city = city,
state_id = state_id,
phone = phone,
fax = fax,
email = email,
username = username,
password = password,
level = level.Trim().ToUpper()
};
db.Contacts.InsertOnSubmit(con);
db.SubmitChanges();
return "Contact saved.";
}
public static void Create(string city, Guid state_id)
{
if (city.Length == 0) { throw new Exception("Invalid city."); }
if (state_id == null || state_id == Guid.Empty) { throw new Exception("Invalid state."); }
JobBoardDataContext db = new JobBoardDataContext();
// Make sure we don't already have a location entry for this
int existing = db.Locations.Where(x => x.city == city && x.state_id == state_id).Count();
if (existing > 0) { throw new Exception("Existing entry."); }
// Create new location
Location loc = new Location {
id = Guid.NewGuid(),
city = city,
state_id = state_id
};
// Save location
db.Locations.InsertOnSubmit(loc);
db.SubmitChanges();
}
/// <summary>
/// Display job categories
/// </summary>
/// <returns></returns>
public ActionResult Categories()
{
JobBoardDataContext db = new JobBoardDataContext();
Contact user = ViewBag.user;
// Get all the jobs
List<CategoryWithParent> categories = CategoryModel.GetAll();
foreach (CategoryWithParent category in categories) {
// instead of extending category again, we'll just reuse the job count field for application count
if(user.isAdmin()) {
category.jobCount = (from c in db.Categories
join jc in db.JobCategories on c.id equals jc.cat
join j in db.Jobs on jc.job equals j.id
join a in db.Applications on j.id equals a.job_id
where c.id.Equals(category.id) && j.jobState.Equals(JobState.PUBLISHED.ToString())
&& a.status.Equals(ApplicationStatus.ACTIVE.ToString()) && !a.dateSubmitted.Equals(null)
&& j.jobState.Equals(JobState.PUBLISHED.ToString())
select a).Count();
} else {
JobContact contact = new JobContact {
contact = user.id
};
category.jobCount = (from c in db.Categories
join jc in db.JobCategories on c.id equals jc.cat
join j in db.Jobs on jc.job equals j.id
join a in db.Applications on j.id equals a.job_id
where c.id.Equals(category.id) && j.jobState.Equals(JobState.PUBLISHED.ToString())
&& a.status.Equals(ApplicationStatus.ACTIVE.ToString()) && !a.dateSubmitted.Equals(null)
&& j.Notifications.Contains(contact, new JobContactEqualityComparer())
&& j.jobState.Equals(JobState.PUBLISHED.ToString())
select a).Count();
}
}
ViewBag.categories = categories;
return View();
}
public static string AddJobCategory(Guid job_id, Guid cat_id)
{
JobBoardDataContext db = new JobBoardDataContext();
// Lets make sure we don't already have an association for this category
int exists = db.JobCategories.Where(x => x.cat == cat_id && x.job == job_id).Count();
if (exists > 0) { return "You may not add this category more than once."; }
JobCategory jc = new JobCategory {
job = job_id,
cat = cat_id
};
db.JobCategories.InsertOnSubmit(jc);
db.SubmitChanges();
JobCategory json = new JobCategory {
id = jc.id,
job = jc.job,
cat = jc.cat
};
JavaScriptSerializer js = new JavaScriptSerializer();
return js.Serialize(json);
}
public static string Delete(Guid id, string contact_id)
{
if (id == null) { throw new Exception(); }
JobBoardDataContext db = new JobBoardDataContext();
Guid con_id = new Guid(contact_id);
// Get the job listings that are tied to the contact we're removing
List<Job> jobs = db.Jobs.Where(x => x.contact == id).ToList<Job>();
List<JobContact> jobcontacts = db.JobContacts.Where(x => x.contact.Equals(id)).ToList<JobContact>();
db.JobContacts.DeleteAllOnSubmit(jobcontacts);
// Loop through the jobs and reassign them to the logged in users ID
foreach (Job j in jobs) {
j.contact = con_id;
}
Contact con = db.Contacts.Where(x => x.id == id).FirstOrDefault<Contact>();
db.Contacts.DeleteOnSubmit(con);
db.SubmitChanges();
return "";
}
public static string Delete(Guid id)
{
try {
if (id == null || id == Guid.Empty) { throw new Exception("Invalid category"); }
JobBoardDataContext db = new JobBoardDataContext();
// Get the category record
Category cat = db.Categories.Where(x => x.id == id).FirstOrDefault<Category>();
// We need to remove any job listing references to this category
List<JobCategory> jobs = db.JobCategories.Where(x => x.cat == id).ToList<JobCategory>();
foreach (JobCategory jc in jobs) {
db.JobCategories.DeleteOnSubmit(jc);
}
db.Categories.DeleteOnSubmit(cat);
db.SubmitChanges();
return "";
} catch (Exception e) {
return e.Message;
}
}
public static Guid Create(string title, string short_desc, string salary_type, string status, string long_desc, Guid experience, Guid education, Guid location, Guid contact, int isDriving, List<Guid> categories, List<Guid> shifts)
{
JobBoardDataContext db = new JobBoardDataContext();
// Create the new job record
Job new_job = new Job {
id = Guid.NewGuid(),
title = title,
short_desc = short_desc,
long_desc = long_desc.Replace("\n","<br />"),
experience = experience,
education = education,
location = location,
contact = contact,
date_added = DateTime.Now,
isDriving = isDriving,
salary_type = salary_type,
status = status,
jobState = JobState.CREATED.ToString()
};
// Save the job record
db.Jobs.InsertOnSubmit(new_job);
db.SubmitChanges();
// Create the job categories
foreach (Guid cat_id in categories) {
JobCategory new_jobcat = new JobCategory {
job = new_job.id,
cat = cat_id
};
db.JobCategories.InsertOnSubmit(new_jobcat);
}
// Create the job categories
foreach (Guid shift_id in shifts) {
JobShift new_jobshift = new JobShift {
id = Guid.NewGuid(),
job = new_job.id,
shift = shift_id
};
db.JobShifts.InsertOnSubmit(new_jobshift);
}
db.SubmitChanges();
return new_job.id;
}
public static void Update(Guid id, string title, string short_desc, string salary_type, string status, string long_desc, Guid experience, Guid education, Guid location, Guid contact, int isDriving, string jobState)
{
if (id == null || id == Guid.Empty) { throw new Exception("Invalid reference"); }
JobBoardDataContext db = new JobBoardDataContext();
Job job = db.Jobs.Where(x => x.id == id).FirstOrDefault<Job>();
job.title = title;
job.short_desc = short_desc;
job.long_desc = long_desc.Replace("\n", "<br />");
job.experience = experience;
job.education = education;
job.location = location;
job.contact = contact;
job.isDriving = isDriving;
job.salary_type = salary_type;
job.status = status;
job.jobState = jobState.Trim().ToUpper();
db.SubmitChanges();
}