public static void updateCVFormat(CVFormatInfo info) { MySqlDataReader dr = getCVFormat(info.CVFormatId); DataSet dsCvFormat = new DataSet(); string[] tbl = new string[10]; dsCvFormat.EnforceConstraints = false; dsCvFormat.Load(dr, LoadOption.PreserveChanges, tbl); string sql = "UPDATE cvformat SET interface=?interface,interfaceDate= ?interfaceDate,interfacenote=?interfacenote,location=?location,visa=?visa,dob=?dob,nodob=?nodob,dobformat=?dobformat,releventExperience=?releventExperience,marital=?marital," + " lastsalarycurrency=?lastsalcurrency,lastsalary=?lastsalary,lastsalaryfrequency=?lastsalfreq,lastsalarynote=?lastsalnote,expectsalarycurrency=?expectsalcurrency,expectsalary=?expectsalary," + " expectsalaryfrequency=?expectsalfreq,expectsalarynote=?expectsalnote,availability=?availability,modified=?modified,expecttosalary=?expecttosalary WHERE cvformatid=?cvformatid; "; MySqlParameter[] param ={ new MySqlParameter("interface",info.Interfaced), new MySqlParameter("interfacedate",info.InterfacedDate), new MySqlParameter("interfacenote",info.InterfacedNote), new MySqlParameter("location",info.Location), new MySqlParameter("visa",info.Visa), new MySqlParameter("dob",info.DOB), new MySqlParameter("nodob",info.NODOB), new MySqlParameter("dobformat",info.DOBFormat), new MySqlParameter("releventExperience",info.ReleventExperience), //new MySqlParameter("qualification",info.Qualification), new MySqlParameter("marital",info.Marital), new MySqlParameter("lastsalcurrency",info.LastSalaryCurrency), new MySqlParameter("lastsalary",info.LastSalary), new MySqlParameter("lastsalfreq",info.LastSalaryFrequency), new MySqlParameter("lastsalnote",info.LastSalaryNote), new MySqlParameter("expectsalcurrency",info.ExpectSalaryCurrency), new MySqlParameter("expectsalary",info.ExpectSalary), new MySqlParameter("expectsalfreq",info.ExpectSalaryFrequency), new MySqlParameter("expectsalnote",info.ExpectSalaryNote), new MySqlParameter("availability",info.Availability), new MySqlParameter("modified",DateTime.UtcNow), new MySqlParameter("cvformatid",info.CVFormatId), new MySqlParameter("expecttosalary",info.ExpectToSalary) }; DAO.ExecuteNonQuery(sql, param); if (dr.HasRows) { string oldvalue = string.Empty; string newvalue = string.Empty; bool lastremunarationchanged = false; bool expectremunarationchanged = false; HistoryInfo historyInfo = new HistoryInfo(); historyInfo.UserId = GPSession.UserId; historyInfo.ModuleId = (int)HistoryInfo.Module.CVFormat; historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit; historyInfo.RecordId = Convert.ToUInt32(info.CVFormatId); historyInfo.Details = new List<HistoryDetailInfo>(); dr.Read(); if (DAO.getString(dr, "Interface") != info.Interfaced) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Interface", OldValue = DAO.getString(dr, "Interface"), NewValue = info.Interfaced }); } if (DAO.getString(dr, "interfacedate") != info.InterfacedDate.ToString()) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "interfacedate", OldValue = DAO.getString(dr, "interfacedate"), NewValue = info.InterfacedDate.ToString() }); } if (DAO.getString(dr, "interfacenote") != info.InterfacedNote) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "interfacenote", OldValue = DAO.getString(dr, "interfacenote"), NewValue = info.InterfacedNote }); } if (DAO.getString(dr, "location") != info.Location) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "location", OldValue = DAO.getString(dr, "location"), NewValue = info.Location }); } if (DAO.getString(dr, "visa") != info.Visa) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "visa", OldValue = DAO.getString(dr, "visa"), NewValue = info.Visa }); } if (DAO.getString(dr, "dob") != info.DOB.ToString()) { oldvalue = String.IsNullOrEmpty(DAO.getString(dr, "dob")) ? "Birth date not provided by Candidate" : DAO.getString(dr, "dob"); newvalue = String.IsNullOrEmpty(info.DOB.ToString()) ? "Birth date not provided by Candidate" : info.DOB.ToString(); historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "dob", OldValue = oldvalue, NewValue = newvalue }); } if (DAO.getString(dr, "releventExperience") != info.ReleventExperience) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "releventExperience", OldValue = DAO.getString(dr, "releventExperience"), NewValue = info.ReleventExperience }); } if (DAO.getString(dr, "marital") != info.Marital) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "marital", OldValue = DAO.getString(dr, "marital"), NewValue = info.Marital }); } lastremunarationchanged = false; oldvalue = DAO.getString(dr, "lastsalarycurrency") + " " + DAO.getString(dr, "lastsalary") + " " + DAO.getString(dr, "lastsalaryfrequency") + " " + DAO.getString(dr, "lastsalarynote"); newvalue = info.LastSalaryCurrency + " " + info.LastSalary + " " + info.LastSalaryFrequency.ToString() + " " + info.LastSalaryNote; if (DAO.getString(dr, "lastsalarycurrency") != info.LastSalaryCurrency) { if (!lastremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "last remunaration", OldValue = oldvalue, NewValue = newvalue }); lastremunarationchanged = true; } } if (DAO.getString(dr, "lastsalary") != info.LastSalary) { if (!lastremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "last remunaration", OldValue = oldvalue, NewValue = newvalue }); lastremunarationchanged = true; } } if (DAO.getString(dr, "lastsalaryfrequency") != info.LastSalaryFrequency.ToString()) { if (!lastremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "last remunaration", OldValue = oldvalue, NewValue = newvalue }); lastremunarationchanged = true; } } if (DAO.getString(dr, "lastsalarynote") != info.LastSalaryNote) { if (!lastremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "last remunaration", OldValue = oldvalue, NewValue = newvalue }); lastremunarationchanged = true; } } expectremunarationchanged = false; oldvalue = DAO.getString(dr, "expectsalarycurrency") + " " + DAO.getString(dr, "expectsalary") + " " + DAO.getString(dr, "expectsalaryfrequency") + " " + DAO.getString(dr, "expectsalarynote"); newvalue = info.ExpectSalaryCurrency + " " + info.ExpectSalary + " " + info.ExpectSalaryFrequency.ToString() + " " + info.ExpectSalaryNote; if (DAO.getString(dr, "expectsalarycurrency") != info.ExpectSalaryCurrency) { if (!expectremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "expect remunaration", OldValue = oldvalue, NewValue = newvalue }); expectremunarationchanged = true; } } if (DAO.getString(dr, "expectsalary") != info.ExpectSalary) { if (!expectremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "expect remunaration", OldValue = oldvalue, NewValue = newvalue }); expectremunarationchanged = true; } } if (DAO.getString(dr, "expectsalaryfrequency") != info.ExpectSalaryFrequency.ToString()) { if (!expectremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "expect remunaration", OldValue = oldvalue, NewValue = newvalue }); expectremunarationchanged = true; } } if (DAO.getString(dr, "expectsalarynote") != info.ExpectSalaryNote) { if (!expectremunarationchanged) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "expect remunaration", OldValue = oldvalue, NewValue = newvalue }); expectremunarationchanged = true; } } if (DAO.getString(dr, "availability") != info.Availability) { historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "availability", OldValue = DAO.getString(dr, "availability"), NewValue = info.Availability }); } if (historyInfo.Details.Count > 0) { HistoryDataProvider history = new HistoryDataProvider(); history.insertHistory(historyInfo); } } HistoryInfo historyInfoList = new HistoryInfo(); historyInfoList.UserId = GPSession.UserId; historyInfoList.ModuleId = (int)HistoryInfo.Module.CVFormat; historyInfoList.TypeId = (int)HistoryInfo.ActionType.Edit; historyInfoList.RecordId = Convert.ToUInt32(info.CVFormatId); historyInfoList.Details = new List<HistoryDetailInfo>(); CandidateDataProvider.updateCandidateBirthDate(info.CandidateId, Convert.ToDateTime(info.DOB), info.NODOB, info.DOBFormat); CandidateDataProvider.updateCandidateMaritalStatus(info.CandidateId, info.Marital); if (!string.IsNullOrEmpty(info.LastToSalary)) { CandidateDataProvider.addCandidateSalary(Convert.ToUInt32(info.CandidateId), 1, Convert.ToInt32(string.IsNullOrEmpty(info.LastSalary) ? "0" : info.LastSalary), Convert.ToInt32(info.LastToSalary), info.LastSalaryFrequency, info.LastSalaryCurrency, DateTime.UtcNow, info.CVFormatId, 3); } if (!string.IsNullOrEmpty(info.ExpectToSalary)) { CandidateDataProvider.addCandidateSalary(Convert.ToUInt32(info.CandidateId), 2, Convert.ToInt32(string.IsNullOrEmpty(info.ExpectSalary) ? "0" : info.ExpectSalary), Convert.ToInt32(info.ExpectToSalary), info.LastSalaryFrequency, info.LastSalaryCurrency, DateTime.UtcNow, info.CVFormatId, 3); } //For Getting New Nationality inserted string newdata = string.Empty; string olddata = string.Empty; deleteCVFormatRelated(info.CVFormatId); CandidateDataProvider.addNationfromCVFormat(info.CandidateId, info.NationalityList); foreach (int countryid in info.NationalityList) { DataRow[] rwCountry = dsCvFormat.Tables[1].Select("countryid = " + countryid.ToString()); if (rwCountry.Length == 0) { newdata = LocationDataProvider.getCountry(countryid); historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "nationality", OldValue = string.Empty, NewValue = newdata }); } insertNationality(countryid, info.CVFormatId); } CandidateDataProvider.removeLanguage(info.CandidateId); foreach (CVFormatLanguageInfo language in info.LanguageList) { DataRow[] rwLanguage = dsCvFormat.Tables[2].Select("languageid = " + language.LanguageId); if (rwLanguage.Length == 0) { newdata = language.Language; historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "languages", OldValue = string.Empty, NewValue = newdata + " added" }); } language.CVFormatId = info.CVFormatId; insertCVFormatLanguages(language); CandidateDataProvider.addLanguagefromCVFormat(info.CandidateId, language.LanguageId, language.Spoken, language.Written, language.Listening, language.Reading); } foreach (ChildStatusInfo child in info.ChildStatusList) { DataRow[] rwChildStatus = dsCvFormat.Tables[3].Select("relationshipsid = " + child.RelationshipId.ToString() + " and age = " + child.Age + " and gender = '" + child.Gender + "'"); if (rwChildStatus.Length == 0) { newdata = child.Relationship + " " + child.Gender + " " + child.Age; historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "relationships", OldValue = string.Empty, NewValue = newdata + " added" }); } child.CVFormatId = info.CVFormatId; insertChildStatus(child); } foreach (SummaryPointInfo summary in info.SummaryPointList) { DataRow[] rwSummaryPoint = dsCvFormat.Tables[4].Select("notes = '" + summary.Notes.ToString() + "'"); if (rwSummaryPoint.Length == 0) { newdata = summary.Notes; historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "summary point", OldValue = string.Empty, NewValue = newdata + " added" }); } summary.CVFormatId = info.CVFormatId; insertSummaryPoint(summary); } foreach (AdditionalInfo additional in info.AdditionalInfoList) { DataRow[] rwAddInfo = dsCvFormat.Tables[5].Select("notes = '" + additional.Notes + "'"); if (rwAddInfo.Length == 0) { newdata = additional.Notes; historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "additional instruction", OldValue = string.Empty, NewValue = newdata + " added" }); } additional.CVFormatId = info.CVFormatId; insertAdditionalInfo(additional); } foreach (QualificationInfo qualification in info.QualificationList) { DataRow[] rwQualification = dsCvFormat.Tables[7].Select("qualification = '" + qualification.Qualification.ToString() + "'"); if (rwQualification.Length == 0) { newdata = qualification.Qualification; historyInfoList.Details.Add(new HistoryDetailInfo { ColumnName = "qualification", OldValue = string.Empty, NewValue = newdata + " added" }); } qualification.CVFormatId = info.CVFormatId; insertQualification(qualification); } foreach (CriteriaNotMetInfo criteria in info.CriteriaNotMetList) { criteria.CVFormatId = info.CVFormatId; insertCriteriaNotMet(criteria); } if (historyInfoList.Details.Count > 0) { HistoryDataProvider history = new HistoryDataProvider(); history.insertHistory(historyInfoList); } }
public static int insertCVFormat(CVFormatInfo info) { string sql = "insert into cvformat (candidateid,jobid,interface,interfacedate,interfacenote,location,visa,dob,nodob,dobformat,releventExperience,marital,lastsalarycurrency,lastsalary,lastsalaryfrequency,lastsalarynote,expectsalarycurrency " + " ,expectsalary,expectsalaryfrequency,expectsalarynote,availability,userid,created,lasttosalary,expecttosalary) values (?candidateid,?jobid,?interface,?interfacedate,?interfacenote,?location,?visa,?dob,?nodob,?dobformat,?releventExperience,?marital,?lastsalcurrency,?lastsalary " + " ,?lastsalfreq,?lastsalnote,?expectsalcurrency,?expectsalary,?expectsalfreq,?expectsalnote,?availability,?userid,?created,?lasttosalary,?expecttosalary); select last_insert_id()"; MySqlParameter[] param ={ new MySqlParameter("candidateid",info.CandidateId), new MySqlParameter("jobid",info.JobId), new MySqlParameter("interface",info.Interfaced), new MySqlParameter("interfacedate",info.InterfacedDate), new MySqlParameter("interfacenote",info.InterfacedNote), new MySqlParameter("location",info.Location), new MySqlParameter("visa",info.Visa), new MySqlParameter("dob",info.DOB), new MySqlParameter("nodob",info.NODOB), new MySqlParameter("dobformat",info.DOBFormat), new MySqlParameter("releventExperience",info.ReleventExperience), //new MySqlParameter("qualification",info.Qualification), new MySqlParameter("marital",info.Marital), new MySqlParameter("lastsalcurrency",info.LastSalaryCurrency), new MySqlParameter("lastsalary",info.LastSalary), new MySqlParameter("lastsalfreq",info.LastSalaryFrequency), new MySqlParameter("lastsalnote",info.LastSalaryNote), new MySqlParameter("expectsalcurrency",info.ExpectSalaryCurrency), new MySqlParameter("expectsalary",info.ExpectSalary), new MySqlParameter("expectsalfreq",info.ExpectSalaryFrequency), new MySqlParameter("expectsalnote",info.ExpectSalaryNote), new MySqlParameter("availability",info.Availability), new MySqlParameter("userid",GPSession.UserId), new MySqlParameter("created",DateTime.UtcNow), new MySqlParameter("lasttosalary",info.LastToSalary), new MySqlParameter("expecttosalary",info.ExpectToSalary) }; int id = Convert.ToInt32(DAO.ExecuteScalar(sql, param)); CandidateDataProvider.updateCandidateBirthDate(info.CandidateId, Convert.ToDateTime(info.DOB), info.NODOB, info.DOBFormat); CandidateDataProvider.updateCandidateMaritalStatus(info.CandidateId, info.Marital); if (!string.IsNullOrEmpty(info.LastToSalary)) { CandidateDataProvider.addCandidateSalary(Convert.ToUInt32(info.CandidateId), 1, Convert.ToInt32(string.IsNullOrEmpty(info.LastSalary) ? "0" : info.LastSalary), Convert.ToInt32(info.LastToSalary), info.LastSalaryFrequency, info.LastSalaryCurrency, DateTime.UtcNow, id, 3); } if (!string.IsNullOrEmpty(info.ExpectToSalary)) { CandidateDataProvider.addCandidateSalary(Convert.ToUInt32(info.CandidateId), 2, Convert.ToInt32(string.IsNullOrEmpty(info.ExpectSalary) ? "0" : info.ExpectSalary), Convert.ToInt32(info.ExpectToSalary), info.LastSalaryFrequency, info.LastSalaryCurrency, DateTime.UtcNow, id, 3); } insertCVFormatStatus(id, 1); CandidateDataProvider.addNationfromCVFormat(info.CandidateId, info.NationalityList); foreach (int countryid in info.NationalityList) { insertNationality(countryid, id); } CandidateDataProvider.removeLanguage(info.CandidateId); foreach (CVFormatLanguageInfo language in info.LanguageList) { language.CVFormatId = id; insertCVFormatLanguages(language); CandidateDataProvider.addLanguagefromCVFormat(info.CandidateId, language.LanguageId, language.Spoken, language.Written, language.Listening, language.Reading); } foreach (ChildStatusInfo child in info.ChildStatusList) { child.CVFormatId = id; insertChildStatus(child); } foreach (SummaryPointInfo summary in info.SummaryPointList) { summary.CVFormatId = id; insertSummaryPoint(summary); } foreach (AdditionalInfo additional in info.AdditionalInfoList) { additional.CVFormatId = id; insertAdditionalInfo(additional); } foreach (QualificationInfo qualification in info.QualificationList) { qualification.CVFormatId = id; insertQualification(qualification); } foreach (CriteriaNotMetInfo criteria in info.CriteriaNotMetList) { criteria.CVFormatId = id; insertCriteriaNotMet(criteria); } return id; }