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