public static void updateJobAlert(JobAlertInfo info)
{
MySqlDataReader drAlert = CandidateDataProvider.getJobAlert(Convert.ToUInt32(info.CandidateId));
DataSet ds = new DataSet();
ds.Load(drAlert, LoadOption.PreserveChanges, new string[5]);
drAlert.Close();
drAlert.Dispose();
string sql = "update job_alert set frequencyId=?frequencyId,phoneCode=?phoneCode,phoneNumber=?phoneNumber,emailid=?emailid where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId), new MySqlParameter("frequencyId", info.FrequencyId), new MySqlParameter("phoneCode", info.PhoneCode), new MySqlParameter("phoneNumber", info.PhoneNo),
new MySqlParameter("emailid", info.EmailId));
sql = "delete from jobalert_industry where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId));
sql = "delete from jobalert_location where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId));
sql = "delete from jobalert_workType where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId));
sql = "delete from jobalert_isoc08 where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId));
foreach (JobAlertIndustry industry in info.IndustryList)
{
sql = "insert into jobalert_industry (job_alertId,candidateID,ISICRev4Id) values (?jobAlertId,?candidateId,?ISICRev4Id);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId), new MySqlParameter("candidateId", industry.CandidateId), new MySqlParameter("ISICRev4Id", industry.ISICRev4Id));
}
foreach (JobAlertLocation location in info.LocationList)
{
sql = "insert into jobalert_location (job_alertId,candidateID,locationID,locationtype) values (?jobAlertId,?candidateId,?locationID,?locationtype);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId), new MySqlParameter("candidateId", location.CandidateId), new MySqlParameter("locationID", location.LocationId), new MySqlParameter("locationtype", location.LocationType));
}
foreach (JobAlertWorkType type in info.WorkTypeList)
{
sql = "insert into jobalert_workType (job_alertId,candidateID,job_typeId) values (?jobAlertId,?candidateId,?typeId);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId), new MySqlParameter("candidateId", type.CandidateId), new MySqlParameter("typeID", type.WorkTypeId));
}
foreach (JobAlertOccupation occupation in info.OccupationList)
{
sql = "insert into jobalert_isoc08 (job_alertId,candidateID,isco08id) values (?jobAlertId,?candidateId,?iscoid);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", info.JobAlertId), new MySqlParameter("candidateId", occupation.CandidateId), new MySqlParameter("iscoid", occupation.ISCO08Id));
}
CandidateDataProvider.updateAlertEmail(Convert.ToUInt32(info.CandidateId), info.EmailId, info.CandidateFullName);
#region History
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Jobalert;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Delete;
historyInfo.RecordId = Convert.ToUInt32(info.JobAlertId);
historyInfo.ParentRecordId = Convert.ToUInt32(info.CandidateId);
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (ds.Tables[0].Rows[0]["email"].ToString() != info.Email)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Email", OldValue = ds.Tables[0].Rows[0]["email"].ToString(), NewValue = info.Email });
}
if (ds.Tables[0].Rows[0]["frequencyId"].ToString() != info.FrequencyId.ToString())
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Frequency", OldValue = ds.Tables[0].Rows[0]["frequencyId"].ToString(), NewValue = 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() + ", " + industrylist;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Industry", OldValue = industrylist, NewValue = 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;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Industry", OldValue = industrylist, NewValue = 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;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Occupation", OldValue = occupationlist, NewValue = 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;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Occupation", OldValue = occupationlist, NewValue = 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().Split(',')[0] + "<br/> " + locationList;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Location", OldValue = locationList, NewValue = 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().Split(',')[0] + "<br/> " + locationList;
}
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Location", OldValue = locationList, NewValue = info.LocationNameList });
break;
}
}
}
if (historyInfo.Details.Count > 0)
history.insertHistory(historyInfo);
#endregion
}
public static int insertJobAlert(JobAlertInfo info)
{
int alertId = 0;
string sql = "insert into job_alert (candidateId,frequencyId,phoneCode,phoneNumber,emailid,createdDate,importednewcandidate,importedexistingcandidate,confirmed) values " +
" (?candateId,?frequencyID,?phoneCode,?phoneNumber,?emailid,?createdDate,?importnew,?importexist,?confirmed); select last_insert_id() ";
alertId = Convert.ToInt32(DAO.ExecuteScalar(sql, new MySqlParameter("candateId", info.CandidateId), new MySqlParameter("frequencyID", info.FrequencyId), new MySqlParameter("phoneCode", info.PhoneCode),
new MySqlParameter("phoneNumber", info.PhoneNo), new MySqlParameter("emailid", info.EmailId), new MySqlParameter("createdDate", info.CreatedDate), new MySqlParameter("importnew", info.ImportedNewCandidate),
new MySqlParameter("importexist", info.ImportedExistingCandidate), new MySqlParameter("confirmed", info.Confirmed)));
foreach (JobAlertIndustry industry in info.IndustryList)
{
sql = "insert into jobalert_industry (job_alertId,candidateID,isicrev4id) values (?jobAlertId,?candidateId,?isicrev4id);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", alertId), new MySqlParameter("candidateId", info.CandidateId), new MySqlParameter("isicrev4id", industry.ISICRev4Id));
}
foreach (JobAlertLocation location in info.LocationList)
{
sql = "insert into jobalert_location (job_alertId,candidateID,locationID,locationtype) values (?jobAlertId,?candidateId,?locationID,?locationtype);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", alertId), new MySqlParameter("candidateId", info.CandidateId), new MySqlParameter("locationID", location.LocationId), new MySqlParameter("locationtype", location.LocationType));
}
foreach (JobAlertWorkType type in info.WorkTypeList)
{
sql = "insert into jobalert_workType (job_alertId,candidateID,job_typeId) values (?jobAlertId,?candidateId,?typeId);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", alertId), new MySqlParameter("candidateId", info.CandidateId), new MySqlParameter("typeID", type.WorkTypeId));
}
foreach (JobAlertOccupation occupation in info.OccupationList)
{
sql = "insert into jobalert_isoc08 (job_alertId,candidateID,isco08id) values (?jobAlertId,?candidateId,?iscoid);";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", alertId), new MySqlParameter("candidateId", info.CandidateId), new MySqlParameter("iscoid", occupation.ISCO08Id));
}
CandidateDataProvider.updateAlertEmail(Convert.ToUInt32(info.CandidateId), info.EmailId, info.CandidateFullName);
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Jobalert;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Delete;
historyInfo.RecordId = Convert.ToUInt32(alertId);
historyInfo.ParentRecordId = Convert.ToUInt32(info.CandidateId);
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "New Subscription", NewValue = "New subscription created for Candidate (" + info.CandidateId + ") to email " + info.Email });
history.insertHistory(historyInfo);
return alertId;
}