public int InsertJobAlert(JobAlertInfo info)
{
int candidateId = info.CadidateId;
int alertId = info.JobAlertId;
int emailId = JobAlertDataAccess.getCandidateEmailId(candidateId, info.Email);
alertId = JobAlertDataAccess.insertJobAlert(candidateId, info.FrequencyId, info.PhoneCode, info.PhoneNo, info.Email, info.CreatedDate, emailId, info.Confirmed);
int historyid = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, 1);
JobAlertDataAccess.insertHistoryDetails(historyid, "New Subscription", string.Empty, "New Job Alerts subscription requested by Candidate " + candidateId + " whilst uploading a CV or applying for a job. The need to confirm the Job Alerts request has been bypassed. Job Alerts will be sent to \"" + info.Email + "\".");
foreach (JobAlertIndustry industry in info.IndustryList)
{
JobAlertDataAccess.inserJobAlertIndustry(alertId, candidateId, industry.ISICRev4Id);
}
if (!string.IsNullOrEmpty(info.IndustryNameList))
{
JobAlertDataAccess.insertHistoryDetails(historyid, "Industry", string.Empty, info.IndustryNameList);
}
foreach (JobAlertLocation location in info.LocationList)
{
JobAlertDataAccess.insertJobAlertLocation(alertId, candidateId, location.LocationId, location.LocationType);
}
if (!string.IsNullOrEmpty(info.LocationNameList))
{
JobAlertDataAccess.insertHistoryDetails(historyid, "Location", string.Empty, info.LocationNameList);
}
foreach (JobAlertWorkType type in info.WorkTypeList)
{
JobAlertDataAccess.insertJobAlertWorkType(alertId, candidateId, type.WorkTypeId);
}
foreach (JobAlertOccupation occupation in info.OccupationList)
{
JobAlertDataAccess.insertJobAlertOccupation(alertId, candidateId, occupation.ISCO08Id);
}
if (!string.IsNullOrEmpty(info.OccupationNameList))
{
JobAlertDataAccess.insertHistoryDetails(historyid, "Occupation", string.Empty, info.OccupationNameList);
}
return alertId;
}
public int InsertJobAlert(JobAlertInfo info, ref string candidateguid)
{
int candidateId = info.CadidateId;
int alertId = info.JobAlertId;
int emailId = 0;
int historyType;
//candidateId = JobAlertDataAccess.existCandidate(info.Email);
//if (candidateId == 0)
//{
// candidateId = JobAlertDataAccess.insertCandidate(1, "Unknown", info.FirstName, info.SurName, "U", info.Email, info.PhoneNo, info.PhoneCode);
//}
//else
// alertId = JobAlertDataAccess.existJobAlert(candidateId);
DataSet ds = new DataSet();
if (alertId > 0)
{
//JobAlertDataAccess.deleteJobAlert(alertId);
historyType = 2;
ds = JobAlertDataAccess.getJobAlert(alertId, candidateguid);
JobAlertDataAccess.updateJobAlert(alertId, info.FrequencyId, info.PhoneCode, info.PhoneNo, info.Email);
JobAlertDataAccess.deleteJobAlertRelatedRecord(alertId);
}
else
{
JobApply apply = new JobApply();
DataTable dtCandidates = apply.GetCandidatesByEmail(info.Email.Trim());
if (dtCandidates.Rows.Count == 1)
{
if (dtCandidates.Rows[0]["first"].ToString().Trim().ToLower() == info.FirstName.Trim().ToLower())
{
candidateguid = dtCandidates.Rows[0]["candidateguid"].ToString();
candidateId = Convert.ToInt32(dtCandidates.Rows[0]["candidateid"].ToString());
emailId = Convert.ToInt32(dtCandidates.Rows[0]["emailid"].ToString());
}
else
{
candidateId = JobAlertDataAccess.insertCandidate(1, "Unknown", info.FirstName, info.SurName, "U", info.Email, info.PhoneNo, info.PhoneCode, ref candidateguid, ref emailId, info.MiddleName);
apply.CandidateCombine(dtCandidates, candidateId);
}
}
else if (dtCandidates.Rows.Count > 1)
{
candidateId = JobAlertDataAccess.insertCandidate(1, "Unknown", info.FirstName, info.SurName, "U", info.Email, info.PhoneNo, info.PhoneCode, ref candidateguid, ref emailId, info.MiddleName);
apply.CandidateCombine(dtCandidates, candidateId);
}
else
{
candidateId = JobAlertDataAccess.insertCandidate(1, "Unknown", info.FirstName, info.SurName, "U", info.Email, info.PhoneNo, info.PhoneCode, ref candidateguid, ref emailId, info.MiddleName);
}
// candidateId = JobAlertDataAccess.existCandidate(info.Email, ref candidateguid, ref emailId);
//if (candidateId == 0)
//{
// candidateId = JobAlertDataAccess.insertCandidate(1, "Unknown", info.FirstName, info.SurName, "U", info.Email, info.PhoneNo, info.PhoneCode, ref candidateguid, ref emailId);
//}
alertId = JobAlertDataAccess.existJobAlert(candidateId);
if (alertId == 0)
{
historyType = 1;
alertId = JobAlertDataAccess.insertJobAlert(candidateId, info.FrequencyId, info.PhoneCode, info.PhoneNo, info.Email, info.CreatedDate, emailId, info.Confirmed);
}
else
{
historyType = 2;
ds = JobAlertDataAccess.getJobAlert(alertId, candidateguid);
JobAlertDataAccess.updateJobAlert(alertId, info.FrequencyId, info.PhoneCode, info.PhoneNo, info.Email);
JobAlertDataAccess.deleteJobAlertRelatedRecord(alertId);
}
}
int historyId = 0;
if (historyType == 1)
{
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "New Subscription", string.Empty, "A Job Alerts subscription was requested for " + candidateId + ". An email requesting confirmation was sent to " + info.Email);
}
else
{
if (ds.Tables[0].Rows[0]["email"].ToString() != info.Email)
{
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Email", ds.Tables[0].Rows[0]["email"].ToString(), info.Email);
}
if (ds.Tables[0].Rows[0]["frequencyId"].ToString() != info.FrequencyId.ToString())
{
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Frequency", ds.Tables[0].Rows[0]["frequencyId"].ToString(), info.FrequencyId.ToString());
}
DataTable dtIndustry = ds.Tables[3];
if (dtIndustry.Rows.Count != info.IndustryList.Count)
{
string industrylist = string.Empty;
foreach (DataRow dr in dtIndustry.Rows)
{
industrylist = dr["code"].ToString() + " " + dr["description"].ToString() + "<br/> " + industrylist;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Industry", industrylist, info.IndustryNameList);
}
else
{
foreach (JobAlertIndustry industry in info.IndustryList)
{
DataRow[] rowSel = dtIndustry.Select("isicrev4id=" + industry.ISICRev4Id);
if (rowSel.Count() == 0)
{
string industrylist = string.Empty;
foreach (DataRow dr in dtIndustry.Rows)
{
industrylist = dr["code"].ToString() + " " + dr["description"].ToString() + "<br/> " + industrylist;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Industry", industrylist, info.IndustryNameList);
break;
}
}
}
DataTable dtOccpation = ds.Tables[4];
if (dtOccpation.Rows.Count != info.OccupationList.Count)
{
string occupationlist = string.Empty;
foreach (DataRow dr in dtOccpation.Rows)
{
occupationlist = dr["groupcode"].ToString() + " " + dr["title"].ToString() + "<br/> " + occupationlist;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Occupation", occupationlist, info.OccupationNameList);
}
else
{
foreach (JobAlertOccupation occupation in info.OccupationList)
{
DataRow[] rowSel = dtOccpation.Select("isco08id=" + occupation.ISCO08Id);
if (rowSel.Count() == 0)
{
string occupationlist = string.Empty;
foreach (DataRow dr in dtOccpation.Rows)
{
occupationlist = dr["groupcode"].ToString() + " " + dr["title"].ToString() + "<br/> " + occupationlist;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Occupation", occupationlist, info.OccupationNameList);
break;
}
}
}
DataTable dtLocation = ds.Tables[1];
if (dtLocation.Rows.Count != info.LocationList.Count)
{
string locationList = string.Empty;
foreach (DataRow dr in dtLocation.Rows)
{
locationList = dr["location"].ToString() + "<br/> " + locationList;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Location", locationList, info.LocationNameList);
}
else
{
foreach (JobAlertLocation location in info.LocationList)
{
DataRow[] rowSel = dtLocation.Select("lid='" + location.LocationId + ":" + location.LocationType + "'");
if (rowSel.Count() == 0)
{
string locationList = string.Empty;
foreach (DataRow dr in dtLocation.Rows)
{
locationList = dr["location"].ToString() + "<br/> " + locationList;
}
if (historyId == 0)
historyId = JobAlertDataAccess.insertJobalertHistory(candidateId, alertId, historyType);
JobAlertDataAccess.insertHistoryDetails(historyId, "Location", locationList, info.LocationNameList);
break;
}
}
}
}
foreach (JobAlertIndustry industry in info.IndustryList)
{
JobAlertDataAccess.inserJobAlertIndustry(alertId, candidateId, industry.ISICRev4Id);
}
foreach (JobAlertLocation location in info.LocationList)
{
JobAlertDataAccess.insertJobAlertLocation(alertId, candidateId, location.LocationId, location.LocationType);
}
foreach (JobAlertWorkType type in info.WorkTypeList)
{
JobAlertDataAccess.insertJobAlertWorkType(alertId, candidateId, type.WorkTypeId);
}
foreach (JobAlertOccupation occupation in info.OccupationList)
{
JobAlertDataAccess.insertJobAlertOccupation(alertId, candidateId, occupation.ISCO08Id);
}
return alertId;
}
public ActionResult JobAlert(JobAlertModel model)
{
if (ModelState.IsValid)
{
YG_Business.JobAlertInfo info = new YG_Business.JobAlertInfo();
info.JobAlertId = model.JobAlertId;
info.CadidateId = model.CandidateId;
info.FirstName = model.Name;
info.SurName = model.SurName;
info.Email = model.Email;
info.PhoneCode = model.PhoneCode;
info.PhoneNo = model.ContactNumber;
info.CreatedDate = DateTime.Now;
info.FrequencyId = model.MailFrequency;
info.WorkTypeList = new List<YG_Business.JobAlertWorkType>();
info.LocationList = new List<YG_Business.JobAlertLocation>();
info.IndustryList = new List<YG_Business.JobAlertIndustry>();
YG_Business.JobAlertIndustry industryInfo;
foreach (int indsurty in model.IndustrySelect)
{
industryInfo = new YG_Business.JobAlertIndustry();
industryInfo.SubIndustryId = indsurty;
info.IndustryList.Add(industryInfo);
}
YG_Business.JobAlertLocation locationInfo;
foreach (int location in model.LocationSelect)
{
locationInfo = new YG_Business.JobAlertLocation();
locationInfo.LocationId = location;
info.LocationList.Add(locationInfo);
}
YG_Business.JobAlertWorkType workTypeInfo;
foreach (int workType in model.WorkTypeSelect)
{
workTypeInfo = new YG_Business.JobAlertWorkType();
workTypeInfo.WorkTypeId = workType;
info.WorkTypeList.Add(workTypeInfo);
}
string candidateguid = model.CandidateGUID;
MailMessage message = new MailMessage();
string address = ConfigurationManager.AppSettings.Get("emailAddress");
string displayName = ConfigurationManager.AppSettings.Get("emailName");
string body;
JobAlert alert = new JobAlert();
int alertId = 0;
bool alertExist = false;
if (model.JobAlertId == 0)
{
alertId = alert.getJobAlertId(info.Email, ref candidateguid);
}
if (alertId > 0)
{
body = System.IO.File.ReadAllText(Server.MapPath("~/Template") + "/alertExist.htm");
message = new MailMessage
{
From = new MailAddress(address, displayName),
Subject = "Please edit your YOU Global Job Alert subscription",
Body = string.Format(body, model.Name, "<a href='" + ConfigurationManager.AppSettings["baseURL"].ToString() + "Jobs/JobAlertEdit.shtml" + "?alertId=" + alertId + "&CID=" + candidateguid + "' > click here</a>", "<img src='" + ConfigurationManager.AppSettings["logoURL"].ToString() + "' />"),
IsBodyHtml = true
};
message.To.Add(model.Email);
alertExist = true;
}
else
{
alertId = alert.InsertJobAlert(info, ref candidateguid);
body = System.IO.File.ReadAllText(Server.MapPath("~/Template") + "/alertemail.html");
body = string.Format(body, model.Name, "<a href='" + ConfigurationManager.AppSettings["subscribeConfirm"].ToString() + "?aD=" + alertId + "&CID=" + candidateguid + "' > click here</a>", "<img src='" + ConfigurationManager.AppSettings["logoURL"].ToString() + "' />");
if (model.JobAlertId == 0)
{
message = new MailMessage
{
From = new MailAddress(address, displayName),
Subject = "Please activate your YOU Global Job Alert subscription",
Body = body,
IsBodyHtml = true
};
message.To.Add(model.Email);
}
else
{
DataSet ds = alert.getJobAlert(alertId, candidateguid);
if (ds.Tables.Count > 0)
{
body = System.IO.File.ReadAllText(Server.MapPath("~/Template") + "/alertConfirmedmail.htm");
string name = ds.Tables[0].Rows[0]["first"].ToString();
string sector = "<table>";
string location = "<table>";
string worktype = "<table>";
foreach (DataRow drSector in ds.Tables[3].Rows)
{
sector += "<tr><td>" + drSector["subclassification"].ToString() + "</td></tr>";
}
sector += "</table>";
foreach (DataRow drLocation in ds.Tables[1].Rows)
{
location += "<tr><td>" + drLocation["name"].ToString() + "</td></tr>";
}
location += "</table>";
foreach (DataRow drWorkType in ds.Tables[2].Rows)
{
worktype += "<tr><td>" + drWorkType["type"].ToString() + "</td></tr>";
}
worktype += "</table>";
string frequney = ds.Tables[0].Rows[0]["frequency"].ToString();
body = string.Format(body, name, sector, location, worktype, frequney, ConfigurationManager.AppSettings["baseURL"].ToString() + "Jobs/JobAlertEdit.shtml" + "?alertId=" + alertId + "&CID=" + candidateguid, ConfigurationManager.AppSettings["logoURL"].ToString());
}
message = new MailMessage
{
From = new MailAddress(address, displayName),
Subject = "Your YOU Global Job Alert subscription is confirmed",
Body = body,
IsBodyHtml = true
};
message.To.Add(model.Email);
}
}
SmtpClient sc = new SmtpClient();
sc.Host = ConfigurationManager.AppSettings["smtpHost"].ToString();
string smtpUser = ConfigurationManager.AppSettings["smtpUserName"].ToString();
string smtpPwd = ConfigurationManager.AppSettings["smtpPassword"].ToString();
sc.Credentials = new System.Net.NetworkCredential(smtpUser, smtpPwd);
sc.Send(message);
if (alertExist)
return base.View("JobAlertExist");
else
{
if (model.JobAlertId == 0)
{
return base.View("JobAlertSuccess", alertId);
}
else
{
model.JobAlertId = alertId;
model.CandidateGUID = candidateguid;
return base.View("JobAlertEditSuccess", model);
}
}
}
return base.View("JobAlertSuccess");
}