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