public static void deleteJobAlert(int jobAlertId, uint candidateid)
{
string sql;
sql = "delete from jobalert_industry where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", jobAlertId));
sql = "delete from jobalert_location where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", jobAlertId));
sql = "delete from jobalert_workType where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", jobAlertId));
sql = "delete from job_alert where Job_alertID=?jobAlertId;";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", jobAlertId));
sql = "delete from jobalert_isoc08 where job_alertId=?jobAlertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobAlertId", jobAlertId));
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(jobAlertId);
historyInfo.ParentRecordId = candidateid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Unsubscribed", NewValue = "Candidate " + candidateid + " was unsubscribed from Job Alerts by " + GPSession.UserName + "." });
history.insertHistory(historyInfo);
}
public static void addIPAddress(uint consultantid, string ipAddress)
{
string sql = "insert into consultants_ipaddress (consultantid, ipaddress) values (?consultantid, ?ipaddress)";
uint ipaddressid = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("consultantid", consultantid), new MySqlParameter("ipaddress", ipAddress)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.consultants_emails;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = ipaddressid;
info.ParentRecordId = consultantid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Add", NewValue = "ipaddress:" + ipAddress });
history.insertHistory(info);
}
public static void addConsultant(uint clientId, uint consultantid)
{
string sql = "insert into client_consultants (clientid,consultantid) values (?clientid,?consultantid);select last_insert_id()";
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("consultantid", consultantid), new MySqlParameter("clientid", clientId)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_consultants;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ParentRecordId = clientId;
info.ModifiedDate = DateTime.UtcNow;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "clientid:" + clientId + " ,consultantid:" + consultantid });
history.insertHistory(info);
}
public static void addAddress(uint candidateid, uint addresstypeid, uint addressid)
{
string sql = "insert into candidates_addresses (candidateid, addressid, addresstypeid) values (?candidateid, ?addressid, ?addresstypeid)";
DAO.ExecuteNonQuery(sql, new MySqlParameter("candidateid", candidateid), new MySqlParameter("addressid", addressid), new MySqlParameter("addresstypeid", addresstypeid));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.candidates_addresses;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = addressid;
info.ParentRecordId = candidateid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "addressid:" + addressid + " ,candidateid:" + candidateid + ", addresstypeId:" + addresstypeid });
history.insertHistory(info);
}
public static void addEmail(int representativeid, string email, int emailtypeid, bool defaultemail)
{
uint emailid = EmailDataProvider.insertEmail(email);
string sql = "insert into representatives_emails (representativeid, emailid, emailtypeid, defaultemail) values (?representativeid, ?emailid, ?emailtypeid, ?defaultemail)";
DAO.ExecuteNonQuery(sql, new MySqlParameter("representativeid", representativeid), new MySqlParameter("emailid", emailid), new MySqlParameter("emailtypeid", emailtypeid), new MySqlParameter("defaultemail", defaultemail));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.representatives_emails;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = emailid;
info.ParentRecordId = Convert.ToUInt32(representativeid);
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "emailid:" + emailid + " ,email:" + email + " ,representativeid:" + representativeid + " ,emailtypeid:" + emailtypeid + " ,defaultemail:" + defaultemail });
history.insertHistory(info);
}
public static void addEmail(uint clientid, string email, uint emailtypeid, bool defaultemail)
{
uint emailid = EmailDataProvider.insertEmail(email);
string sql = "insert into client_emails (clientid, emailid, emailtypeid, defaultemail) values (?clientid, ?emailid, ?emailtypeid, ?defaultemail);select last_insert_id()";
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("emailid", emailid), new MySqlParameter("emailtypeid", emailtypeid), new MySqlParameter("defaultemail", defaultemail)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_emails;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "emailid:" + emailid + " ,email:" + email + " ,clientid:" + clientid + " ,emailtypeid:" + emailtypeid + " ,defaultemail:" + defaultemail });
history.insertHistory(info);
}
public static void addPhone(uint clientid, string phonenumber, uint phonenumbertypeid, string countrycode)
{
uint phonenumberid = PhoneNumberDataProvider.insertPhoneNumber(phonenumber, countrycode);
string sql = "insert into client_phonenumbers (clientid, phonenumberid, phonenumbertypeid) values (?clientid, ?phonenumberid, ?phonenumbertypeid);select last_insert_id()";
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("phonenumberid", phonenumberid), new MySqlParameter("phonenumbertypeid", phonenumbertypeid), new MySqlParameter("countrycode", countrycode)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_phonenumbers;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "phonenumberid:" + phonenumberid + " ,number:" + phonenumber + " ,clientid:" + clientid + " ,phonenumbertypeid:" + phonenumbertypeid });
history.insertHistory(info);
}
public static void insertJobalertInvite(int candidateid, string email)
{
uint id = 0;
string sql = "insert into jobalert_invite ( candidateid,email,senddate) values (?candidateid,?email,?sendDate);select last_insert_id();";
id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("candidateid", candidateid), new MySqlParameter("email", email), new MySqlParameter("sendDate", DateTime.UtcNow)));
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.ModuleId = (int)HistoryInfo.Module.jobalert_invite;
info.RecordId = id;
info.ModifiedDate = DateTime.UtcNow;
info.ParentRecordId = Convert.ToUInt32(candidateid);
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Job alert invite", NewValue = "Job Alerts invitation initiated by " + GPSession.UserName + ", sent to " + email + "." });
HistoryDataProvider history = new HistoryDataProvider();
history.insertHistory(info);
}
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;
}
public static void updateUser(uint userid, bool active, int utcoffset, string timezoneid)
{
MySqlDataReader reader = getUser(userid);
string sql = "update users set active = ?active, utcoffset = ?utcoffset, timezoneid = ?timezoneid where userid = ?userid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("userid", userid), new MySqlParameter("active", active), new MySqlParameter("utcoffset", utcoffset), new MySqlParameter("timezoneid", timezoneid));
if (reader.HasRows)
{
reader.Read();
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.User;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = userid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (DAO.getBool(reader, "active") != active)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "active", OldValue = DAO.getBool(reader, "active").ToString(), NewValue = active.ToString() });
}
if (DAO.getInt(reader, "utcoffset") != utcoffset)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "utcoffset", OldValue = DAO.getInt(reader, "utcoffset").ToString(), NewValue = utcoffset.ToString() });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
}
public static void updateCVFormatStatus(int cvformatid, int status, int oldstatus)
{
string sql = "update cvformat_Status set status=?status,userid=?userid,modified=?modified where cvformatid=?cvformatid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("cvformatid", cvformatid), new MySqlParameter("status", status), new MySqlParameter("userid", GPSession.UserId), new MySqlParameter("modified", DateTime.UtcNow));
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.CVFormat;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(cvformatid);
historyInfo.Details = new List<HistoryDetailInfo>();
string oldStatus = string.Empty;
string newstatus = string.Empty;
if (status == 1)
newstatus = "Request";
else if (status == 2)
newstatus = "Completed";
else if (status == 3)
newstatus = "Cancelled";
else if (status == 4)
newstatus = "Waiting";
if (oldstatus == 1)
oldStatus = "Request";
else if (oldstatus == 2)
oldStatus = "Completed";
else if (oldstatus == 3)
oldStatus = "Cancelled";
else if (oldstatus == 4)
oldStatus = "Waiting";
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "Status", OldValue = oldStatus, NewValue = newstatus });
HistoryDataProvider history = new HistoryDataProvider();
history.insertHistory(historyInfo);
}
public static void updateCandidateJobs(int candidateid, int jobid, int statusid, string notes, uint cjId)
{
DataTable dt = getCandidateJobsById(cjId);
string sql = "update candidates_jobs set statusiconid=?statusid,notes=?notes where candidateid=?candidateid and jobid=?jobid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("candidateid", candidateid), new MySqlParameter("jobid", jobid), new MySqlParameter("statusid", statusid), new MySqlParameter("notes", notes));
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.candidate_jobs;
info.TypeId = (int)HistoryInfo.ActionType.Edit;
info.RecordId = cjId;
info.ModifiedDate = DateTime.UtcNow;
info.Details = new List<HistoryDetailInfo>();
if (dt.Rows[0]["notes"].ToString() != notes)
{
info.Details.Add(new HistoryDetailInfo { ColumnName = "notes", NewValue = notes, OldValue = dt.Rows[0]["notes"].ToString() });
}
if (dt.Rows[0]["statusiconid"].ToString() != statusid.ToString())
{
info.Details.Add(new HistoryDetailInfo { ColumnName = "statusiconid", NewValue = statusid.ToString(), OldValue = dt.Rows[0]["statusiconid"].ToString() });
}
if (info.Details.Count > 0)
{
HistoryDataProvider history = new HistoryDataProvider();
history.insertHistory(info);
}
}
public static void updateJobApprove(int jobId)
{
string sql = "update jobdetail JD inner join jobdetail_edit JE on JD.Jobdetailid= JE.jobdetailid " +
" set JD.locationID = JE.locationID, JD.typeID = JE.typeID, JD.salaryMin = JE.salaryMin, JD.salaryMax = JE.salaryMax, JD.ReferenceNo = JE.ReferenceNo, JD.title = JE.title, JD.subHeading = JE.subHeading,JD.version=JE.version, JD.internalchange = JE.internalchange," +
" JD.searchTitle = JE.searchTitle, JD.bullet1 = JE.bullet1, JD.bullet2 = JE.bullet2, JD.bullet3 = JE.bullet3, JD.summary = JE.summary, JD.jobContent = JE.jobContent, JD.websiteURL = JE.websiteURL, " +
" JD.adFooter = JE.adFooter, JD.isResidency = JE.isResidency, JD.status = JE.status, JD.isApprove = JE.isApprove, JD.clientId = JE.clientId, JD.jobIndustryId = JE.jobIndustryId, JD.jobIndustrySubId = JE.jobIndustrySubId, " +
" JD.modifiedDate = JE.modifiedDate, JD.modifiedBy = JE.modifiedBy, JD.hotJob = JE.hotJob, JD.salaryCurrency = JE.salaryCurrency, JD.salaryFrequency = JE.salaryFrequency, JD.published=JE.published, JD.isco08id=JE.isco08id, JD.isicrev4id=JE.isicrev4id Where JE.active=1 and JE.jobdetailid=?jobId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobId", jobId));
int jobeditid = 0;
if (JobDetailEditDataProvider.isExist(jobId, ref jobeditid))
{
JobEssentialCriteriaDataProvider.deleteEssentialCriteria(jobId);
sql = "insert into essentialcriteria (JobId, Description,AnswerLength,sortorder) (select JobID,Description,AnswerLength,sortorder from essentialcriteria_edit where jobeditid=?jobeditid) ";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobeditid", jobeditid));
JobDesirableCriteriaDataProvider.deleteDesirableCriteria(jobId);
sql = "insert into desirablecriteria (JobId, Description,AnswerLength,sortorder) (select JobID,Description,AnswerLength,sortorder from desirablecriteria_edit where jobeditid=?jobeditid) ";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobeditid", jobeditid));
JobConsultantDataProvider.deleteJobConsultant(jobId);
sql = "insert into job_consultants (jobID, consultantID) (select jobID, consultantID from job_consultants_edit where jobeditid=?jobeditid) ";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobeditid", jobeditid));
JobLocationDataProvider.deleteJobLocation(jobId);
sql = "insert into jobs_locations (jobdetailid,locationid,locationtype) (select jobdetailid,locationid,locationtype from jobs_locations_edit where jobeditid=?jobeditid) ";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobeditid", jobeditid));
}
sql = "update jobdetail set isApprove=1,status=6,ismodified=0 where JobDetailID=?jobId and status in(1,2,5,6)";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobId", jobId));
sql = "update jobdetail set isApprove=1,ismodified=0 where JobDetailID=?jobId and status in(3,4)";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobId", jobId));
// JobDetailEditDataProvider.deleteJobDetail(jobId);
JobDetailEditDataProvider.updateActive(jobId, jobeditid);
string referenceNo = getReferenceNo(jobId);
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Job;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(jobId);
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "approve", NewValue = referenceNo });
history.insertHistory(historyInfo);
}
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 void updateRepresentative(uint clientid, uint representativeid, DateTime enddate)
{
string sql = "select * from client_representatives where clientid=?clientid and representativeid=?representativeid";
MySqlDataReader reader = DAO.ExecuteReader(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("representativeid", representativeid));
sql = "update client_representatives set " +
"enddate = ?enddate " +
"where clientid = ?clientid " +
"and representativeid = ?representativeid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("representativeid", representativeid), new MySqlParameter("enddate", enddate));
if (reader.HasRows)
{
reader.Read();
HistoryInfo historyInfo = new HistoryInfo();
HistoryDataProvider history = new HistoryDataProvider();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.client_representatives;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(DAO.getUInt(reader, "id"));
historyInfo.ParentRecordId = clientid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (reader["enddate"].ToString() != enddate.ToString())
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "enddate:", OldValue = reader["enddate"].ToString(), NewValue = enddate.ToString() });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
reader.Close();
reader.Dispose();
}
public static void updateClient(uint clientid, string clientname, string status, List<string[]> emails, List<string[]> numbers)
{
string sql;
HistoryInfo historyInfo = new HistoryInfo();
HistoryDataProvider history = new HistoryDataProvider();
DataSet ds = new DataSet();
MySqlDataReader reader = getClient(clientid);
string[] tbl = new string[4];
ds.Load(reader, LoadOption.OverwriteChanges, tbl);
reader.Close();
reader.Dispose();
//client
sql = @"update client set
ClientName = ?client,
status = ?status
where ClientID = ?clientid;";
DAO.ExecuteNonQuery(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("client", clientname), new MySqlParameter("status", status));
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Client;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = clientid;
historyInfo.ParentRecordId = clientid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (ds.Tables[0].Rows[0]["clientname"].ToString() != clientname)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "clientname:", OldValue = ds.Tables[0].Rows[0]["clientname"].ToString(), NewValue = clientname });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
foreach (string[] email in emails)
{
uint emailid = Convert.ToUInt32(email[0]);
string address = email[1];
uint emailtypeid = Convert.ToUInt32(email[2]);
bool defaultemail = Convert.ToBoolean(email[3]);
updateEmail(clientid, emailid, address, emailtypeid, defaultemail);
DataRow[] drSeq = ds.Tables[1].Select("emailid=" + emailid);
if (drSeq.Length > 0)
{
historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.client_emails;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = emailid;
historyInfo.ParentRecordId = clientid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (drSeq[0]["email"].ToString() != address)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "email", OldValue = drSeq[0]["email"].ToString(), NewValue = address });
}
if (drSeq[0]["emailtypeid"].ToString() != emailtypeid.ToString())
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "emailtypeid", OldValue = drSeq[0]["emailtypeid"].ToString(), NewValue = emailtypeid.ToString() });
}
if (Convert.ToBoolean(drSeq[0]["defaultemail"]) != defaultemail)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "defaultemail", OldValue = drSeq[0]["defaultemail"].ToString(), NewValue = defaultemail.ToString() });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
}
//numbers
foreach (string[] number in numbers)
{
string countrycode = number[0];
uint phonenumberid = Convert.ToUInt32(number[1]);
string phonenumber = number[2];
uint phonenumbertypeid = Convert.ToUInt32(number[3]);
updatePhoneNumber(clientid, phonenumberid, phonenumber, phonenumbertypeid, countrycode);
DataRow[] drSeq = ds.Tables[2].Select("phonenumberid=" + phonenumberid);
if (drSeq.Length > 0)
{
historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.client_phonenumbers;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = phonenumberid;
historyInfo.ParentRecordId = clientid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (drSeq[0]["number"].ToString() != phonenumber)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "number", OldValue = drSeq[0]["number"].ToString(), NewValue = phonenumber });
}
if (drSeq[0]["phonenumbertypeid"].ToString() != phonenumbertypeid.ToString())
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "phonenumbertypeid", OldValue = drSeq[0]["phonenumbertypeid"].ToString(), NewValue = phonenumbertypeid.ToString() });
}
if (drSeq[0]["countrycode"].ToString() != countrycode)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "countrycode", OldValue = drSeq[0]["countrycode"].ToString(), NewValue = countrycode });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
}
}
public static void removePhoneNumber(uint clientid, uint phonenumberid)
{
string number = PhoneNumberDataProvider.getPhoneNumber(phonenumberid);
string sql = "delete from client_phonenumbers where clientid = ?clientid and phonenumberid = ?phonenumberid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("phonenumberid", phonenumberid));
PhoneNumberDataProvider.removePhoneNumber(phonenumberid);
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_phonenumbers;
info.TypeId = (int)HistoryInfo.ActionType.Delete;
info.RecordId = phonenumberid;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Delete", NewValue = number });
history.insertHistory(info);
}
public static void updateJobDetail(JobDetailInfo info)
{
MySqlDataReader reader = getJobById(Convert.ToUInt32(info.JobId), info.JobEditId);
string sql = "update jobdetail_edit set locationID=?locationID,typeID=?typeID,salaryMin=?salaryMin,salaryMax=?salaryMax,ReferenceNo=?ReferenceNo,title=?title,subHeading=?subHeading,searchTitle=?searchTitle,bullet1=?bullet1" +
",bullet2=?bullet2,bullet3=?bullet3,summary=?summary,jobContent=?jobContent,websiteURL=?websiteURL,adFooter=?adFooter,isResidency=?isResidency,status=?status,clientId=?clientId,jobIndustryId=?jobIndustryId,jobIndustrySubId=?jobIndustrySubId," +
" modifiedDate=?modifiedDate,modifiedBy=?modifiedBy,hotJob=?hotJob,salaryCurrency=?salaryCurrency,salaryFrequency=?salaryFrequency,published=?published,internalchange=?internalchange, isco08id=?isco08id,isicrev4id=?isicrev4id where JobDetailID=?jobId and active=1";
MySqlParameter[] param ={
new MySqlParameter("locationID",info.LocationId),
new MySqlParameter("typeID", info.TypeId),
new MySqlParameter("salaryMin", info.SalaryMin),
new MySqlParameter("salaryMax",info.SalaryMax),
new MySqlParameter("ReferenceNo",info.ReferenceNo),
new MySqlParameter("title",info.Title),
new MySqlParameter("subHeading",info.SubHeading),
new MySqlParameter("searchTitle",info.SearchTitle),
new MySqlParameter("bullet1",info.Bullet1),
new MySqlParameter("bullet2",info.Bullet2),
new MySqlParameter("bullet3",info.Bullet3),
new MySqlParameter("summary",info.Summary),
new MySqlParameter("jobContent",info.JobContent),
new MySqlParameter("websiteURL",info.WebsiteURL),
new MySqlParameter("adFooter",info.AdFooter),
new MySqlParameter("isResidency",info.IsResidency),
new MySqlParameter("status",info.Status),
new MySqlParameter("clientId",info.ClientId),
new MySqlParameter("jobIndustryId",info.JobIndustryId),
new MySqlParameter("jobIndustrySubId",info.JobIndustrySubId),
new MySqlParameter("modifiedDate",info.ModifiedDate),
new MySqlParameter("modifiedBy",info.ModifiedBy),
new MySqlParameter("hotJob",info.HotJob),
new MySqlParameter("salaryCurrency",info.SalaryCurrency),
new MySqlParameter("salaryFrequency",info.SalaryFrequency),
new MySqlParameter("published",info.Published),
new MySqlParameter("jobId",info.JobId),
new MySqlParameter("internalchange",info.InternalChange),
new MySqlParameter("isco08id",info.ISCO08Id),
new MySqlParameter("isicrev4id",info.ISICRev4Id)
};
DAO.ExecuteScalar(sql, param);
sql = "update jobdetail set ismodified=1 where JobDetailID=?jobId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("jobId", info.JobId));
deleteEssentialCriteria(info.JobId);
deleteDesirableCriteria(info.JobId);
deleteJobConsultant(info.JobId);
deleteJobLocation(info.JobId);
uint jobId = Convert.ToUInt32(info.JobId);
uint jobeditid = Convert.ToUInt32(info.JobEditId);
foreach (EssentialCriteriaInfo essInfo in info.EssentialCriteriaList)
{
insertEssentialCriteria(jobId, jobeditid, essInfo.Description, essInfo.AnswerLength, essInfo.SortOrder);
}
foreach (DesirableCriteriaInfo desiInfo in info.DesirableCriteriaList)
{
insertDesirableCriteria(jobId, jobeditid, desiInfo.Description, desiInfo.AnswerLength, desiInfo.SortOrder);
}
foreach (JobConsultantInfo consult in info.ConsultantList)
{
insertJobConsultant(jobId, jobeditid, consult.ConsultantId);
}
foreach (JobLocation location in info.LocationList)
{
insertJobLocation(jobId, location.LocationId, location.Locationtype, jobeditid);
}
JobDetailDataProvider.updateIsmodified(info.JobId);
#region History
if (reader.HasRows)
{
reader.Read();
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Job;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(info.JobId);
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (DAO.getInt(reader, "locationid") != info.LocationId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "locationid", OldValue = DAO.getInt(reader, "locationid").ToString(), NewValue = info.LocationId.ToString() });
}
if (DAO.getInt(reader, "typeid") != info.TypeId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "typeid", OldValue = DAO.getInt(reader, "typeid").ToString(), NewValue = info.TypeId.ToString() });
}
if (DAO.getString(reader, "salaryMax").ToString() != info.SalaryMax)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryMax", OldValue = DAO.getString(reader, "salaryMax"), NewValue = info.SalaryMax });
}
if (DAO.getString(reader, "salaryMin").ToString() != info.SalaryMin)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryMin", OldValue = DAO.getString(reader, "salaryMin"), NewValue = info.SalaryMin });
}
if (DAO.getString(reader, "ReferenceNo").ToString() != info.ReferenceNo)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "ReferenceNo", OldValue = DAO.getString(reader, "ReferenceNo"), NewValue = info.ReferenceNo });
}
if (DAO.getString(reader, "title").ToString() != info.Title)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "title", OldValue = DAO.getString(reader, "title"), NewValue = info.Title });
}
//if ((string.IsNullOrEmpty(DAO.getString(reader, "subHeading")) ? "" : DAO.getString(reader, "subHeading").ToString()) != info.SubHeading)
//{
// historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "subHeading", OldValue = DAO.getString(reader, "subHeading"), NewValue = info.SubHeading });
//}
//if (DAO.getString(reader, "searchTitle").ToString() != info.SubHeading)
//{
// historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "searchTitle", OldValue = DAO.getString(reader, "searchTitle"), NewValue = info.SearchTitle });
//}
if (DAO.getString(reader, "bullet1").ToString() != info.Bullet1)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet1", OldValue = DAO.getString(reader, "bullet1"), NewValue = info.Bullet1 });
}
if (DAO.getString(reader, "bullet2").ToString() != info.Bullet2)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet2", OldValue = DAO.getString(reader, "bullet2"), NewValue = info.Bullet2 });
}
if (DAO.getString(reader, "bullet3").ToString() != info.Bullet3)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet3", OldValue = DAO.getString(reader, "bullet3"), NewValue = info.Bullet3 });
}
if (DAO.getString(reader, "summary").ToString() != info.Summary)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "summary", OldValue = DAO.getString(reader, "summary"), NewValue = info.Summary });
}
if (DAO.getString(reader, "jobContent").ToString() != info.JobContent)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobContent", OldValue = DAO.getString(reader, "jobContent"), NewValue = info.JobContent });
}
if ((string.IsNullOrEmpty(DAO.getString(reader, "websiteURL")) ? string.Empty : DAO.getString(reader, "websiteURL").ToString()) != info.WebsiteURL)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "websiteURL", OldValue = DAO.getString(reader, "websiteURL"), NewValue = info.WebsiteURL });
}
if (DAO.getString(reader, "adFooter").ToString() != info.AdFooter)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "adFooter", OldValue = DAO.getString(reader, "adFooter"), NewValue = info.AdFooter });
}
if (DAO.getInt(reader, "status") != info.Status)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "status", OldValue = DAO.getInt(reader, "status").ToString(), NewValue = info.Status.ToString() });
}
if (DAO.getInt(reader, "clientId") != info.ClientId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "clientId", OldValue = DAO.getInt(reader, "clientId").ToString(), NewValue = info.ClientId.ToString() });
}
if (DAO.getInt(reader, "jobIndustryId") != info.JobIndustryId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobIndustryId", OldValue = DAO.getInt(reader, "jobIndustryId").ToString(), NewValue = info.JobIndustryId.ToString() });
}
if (DAO.getInt(reader, "jobIndustrySubId") != info.JobIndustrySubId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobIndustrySubId", OldValue = DAO.getInt(reader, "jobIndustrySubId").ToString(), NewValue = info.JobIndustrySubId.ToString() });
}
if (DAO.getBool(reader, "hotJob") != info.HotJob)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "hotJob", OldValue = DAO.getBool(reader, "hotJob").ToString(), NewValue = info.HotJob.ToString() });
}
if (DAO.getString(reader, "salaryCurrency").ToString() != info.SalaryCurrency)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryCurrency", OldValue = DAO.getString(reader, "salaryCurrency"), NewValue = info.SalaryCurrency });
}
if (DAO.getInt(reader, "salaryFrequency") != info.SalaryFrequency)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryFrequency", OldValue = DAO.getInt(reader, "salaryFrequency").ToString(), NewValue = info.SalaryFrequency.ToString() });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
reader.Close();
reader.Dispose();
#endregion
}
public static void removeEmail(uint clientid, uint emailid)
{
string email = EmailDataProvider.getEmail(emailid);
string sql = "delete from client_emails where clientid = ?clientid and emailid = ?emailid";
DAO.ExecuteNonQuery(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("emailid", emailid));
EmailDataProvider.removeEmail(emailid);
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_emails;
info.TypeId = (int)HistoryInfo.ActionType.Delete;
info.RecordId = emailid;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Delete", NewValue = email });
history.insertHistory(info);
}
public static void removeConsultant(uint clientid, uint consultantid, string clientname, string consultantname)
{
string sql = "select client_consultantid from client_consultants where clientid=?clientid and consultantid=?consultantid";
sql = "delete from client_consultants where clientid=?clientid and consultantid=?consultantid";
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("consultantid", consultantid), new MySqlParameter("clientid", clientid)));
DAO.ExecuteNonQuery(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("consultantid", consultantid));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_consultants;
info.TypeId = (int)HistoryInfo.ActionType.Delete;
info.RecordId = id;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.UtcNow;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Delete", NewValue = consultantname+" "+consultantid.ToString()+" was disassociated with "+clientname+" ("+clientid.ToString()+")", OldValue = string.Empty });
history.insertHistory(info);
}
public static uint insertClient(string clientname)
{
string sql = "insert into client(ClientName) values (?clientname); select last_insert_id()";
uint clientid = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("clientname", clientname)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.Client;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = clientid;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "ClientName:" + clientname });
history.insertHistory(info);
return clientid;
}
public static void addWorkHistory(uint candidateid, DateTime? from, string from_display, DateTime? to, string to_display, string employer, uint? locationid, int? locationtype, uint? industryid, uint? positionid, decimal? salary, string salarycurrency, int? employerid)
{
if (!string.IsNullOrEmpty(employer))
{
if (employerid == 0)
{
employerid = CommonDataProvider.existEmployer(employer, 0);
if (employerid == 0)
{
employerid = CommonDataProvider.addEmployer(employer);
}
// MySqlDataReader drEmp = CommonDataProvider.SearchEmployer(employer);
// if (drEmp.HasRows)
// {
// drEmp.Read();
// employerid = Convert.ToInt32(DAO.getString(drEmp, "employerid"));
// }
// else
// employerid = CommonDataProvider.addEmployer(employer);
// drEmp.Close();
// drEmp.Dispose();
}
}
string sql = "insert into candidates_workhistories (candidateid, `from`, from_display, `to`, to_display, employer, locationid, industryid, positionid, salary, salarycurrency,locationtype,employerid) " +
" values (?candidateid, ?from, ?from_display, ?to, ?to_display, ?employer, ?locationid, ?industryid, ?positionid, ?salary, ?salarycurrency,?locationtype,?employerid);select last_insert_id()";
MySqlParameter sqlSalary = new MySqlParameter("salary", MySqlDbType.Decimal);
if (salary == null)
{
sqlSalary.Value = DBNull.Value;
}
else
{
sqlSalary.Value = salary;
}
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("candidateid", candidateid), new MySqlParameter("from", from), new MySqlParameter("from_display", from_display), new MySqlParameter("to", to),
new MySqlParameter("to_display", to_display), new MySqlParameter("employer", employer), new MySqlParameter("locationid", locationid), new MySqlParameter("industryid", industryid), new MySqlParameter("positionid", positionid),
sqlSalary, new MySqlParameter("salarycurrency", salarycurrency), new MySqlParameter("locationtype", locationtype), new MySqlParameter("employerid", employerid)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.candidates_workhistories;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ParentRecordId = candidateid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo
{
ColumnName = "All",
NewValue = "candidateid:" + candidateid + " ,from:" + from_display.Replace(",", "-") + " ,to:" + (to != null ? to_display.Replace(",", "-") : "Present") + " ,employer:" + employer + " ,locationid:" + locationid + " " + locationtype
+ " ,industryid:" + industryid + " ,positionid:" + positionid
});
history.insertHistory(info);
}
public static void updateAlertConfirm(int alertId,string candidateid, string emailaddress)
{
string sql = "update job_alert set confirmed=1 where job_alertId=?alertId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("alertId", alertId));
string message = "An unconfirmed Job Alerts request by Candidate " + candidateid + " was approved internally by " + GPSession.UserName + ", with Job Alerts being sent to " + emailaddress + ".";
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.ModuleId = (int)HistoryInfo.Module.Jobalert;
info.RecordId = Convert.ToUInt32(alertId);
info.ModifiedDate = DateTime.UtcNow;
info.ParentRecordId = Convert.ToUInt32(alertId);
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "Job alert Confirm", NewValue = message });
HistoryDataProvider history = new HistoryDataProvider();
history.insertHistory(info);
}
public static uint insertJobDetailEdit(JobDetailInfo info, bool isnew)
{
MySqlDataReader reader = JobDetailDataProvider.getJobById(Convert.ToUInt32(info.JobId));
string sql = "insert into jobdetail_edit (jobdetailId,locationID, typeID,salaryMin,salaryMax,ReferenceNo,title,subHeading,searchTitle,bullet1,bullet2,bullet3,summary,jobContent,websiteURL,adFooter,isResidency,status,isApprove,clientId," +
"jobIndustryId,jobIndustrySubId, createdDate,createdBy,modifieddate,modifiedby,hotJob,salaryCurrency,salaryFrequency,published,internalchange,active,version,versionstatus,versiondate,versionuser,isco08id,isicrev4id)" +
"values (?jobdetailid,?locationID, ?typeID,?salaryMin,?salaryMax,?ReferenceNo,?title,?subHeading,?searchTitle,?bullet1,?bullet2,?bullet3,?summary,?jobContent,?websiteURL,?adFooter,?isResidency,?status,?isApprove,?clientId," +
"?jobIndustryId,?jobIndustrySubId,?createdDate,?createdBy,?modifieddate,?modifiedby,?hotJob,?salaryCurrency,?salaryFrequency,?published,?internalchange,?active,?version,?versionstatus,?vdate,?vuser,?isco08id,?isicrev4id); select last_insert_id()";
if (isnew)
{
info.Status = 1;
info.Published = true;
}
MySqlParameter[] param ={
new MySqlParameter("jobdetailid",info.JobId),
new MySqlParameter("locationID",info.LocationId),
new MySqlParameter("typeID", info.TypeId),
new MySqlParameter("salaryMin", info.SalaryMin),
new MySqlParameter("salaryMax",info.SalaryMax),
new MySqlParameter("ReferenceNo",info.ReferenceNo),
new MySqlParameter("title",info.Title),
new MySqlParameter("subHeading",info.SubHeading),
new MySqlParameter("searchTitle",info.SearchTitle),
new MySqlParameter("bullet1",info.Bullet1),
new MySqlParameter("bullet2",info.Bullet2),
new MySqlParameter("bullet3",info.Bullet3),
new MySqlParameter("summary",info.Summary),
new MySqlParameter("jobContent",info.JobContent),
new MySqlParameter("websiteURL",info.WebsiteURL),
new MySqlParameter("adFooter",info.AdFooter),
new MySqlParameter("isResidency",info.IsResidency),
new MySqlParameter("status", info.Status),
new MySqlParameter("isApprove",info.IsApprove),
new MySqlParameter("clientId",info.ClientId),
new MySqlParameter("jobIndustryId",info.JobIndustryId),
new MySqlParameter("jobIndustrySubId",info.JobIndustrySubId),
new MySqlParameter("createdDate",info.CreatedDate),
new MySqlParameter("createdBy",info.CreatedBy),
info.IsApprove?
new MySqlParameter("modifieddate", info.ModifiedDate):new MySqlParameter("modifieddate", DBNull.Value),
isnew? new MySqlParameter("modifiedBy",DBNull.Value): new MySqlParameter("modifiedBy",info.ModifiedBy),
new MySqlParameter("hotJob",info.HotJob),
new MySqlParameter("salaryCurrency",info.SalaryCurrency),
new MySqlParameter("salaryFrequency",info.SalaryFrequency),
new MySqlParameter("published",info.Published),
new MySqlParameter("internalchange",info.InternalChange),
new MySqlParameter("active",1),
new MySqlParameter("version",info.Version),
new MySqlParameter("versionstatus",1),
new MySqlParameter("vdate",DateTime.UtcNow),
new MySqlParameter("vuser",GPSession.UserId),
new MySqlParameter("isco08id",info.ISCO08Id),
new MySqlParameter("isicrev4id",info.ISICRev4Id)
};
uint jobeditId = Convert.ToUInt32(DAO.ExecuteScalar(sql, param));
uint jobId = Convert.ToUInt32(info.JobId);
uint essid;
foreach (EssentialCriteriaInfo essInfo in info.EssentialCriteriaList)
{
essid = insertEssentialCriteria(jobId, jobeditId, essInfo.Description, essInfo.AnswerLength, essInfo.SortOrder);
}
uint dessid;
foreach (DesirableCriteriaInfo desiInfo in info.DesirableCriteriaList)
{
dessid = insertDesirableCriteria(jobId, jobeditId, desiInfo.Description, desiInfo.AnswerLength, desiInfo.SortOrder);
}
foreach (JobConsultantInfo consult in info.ConsultantList)
{
insertJobConsultant(jobId, jobeditId, consult.ConsultantId);
}
foreach (JobLocation location in info.LocationList)
{
insertJobLocation(jobId, location.LocationId, location.Locationtype, jobeditId);
}
if (!isnew)
JobDetailDataProvider.updateIsmodified(info.JobId);
#region History
if (reader.HasRows)
{
reader.Read();
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Job;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(info.JobId);
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
if (DAO.getInt(reader, "locationid") != info.LocationId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "locationid", OldValue = DAO.getInt(reader, "locationid").ToString(), NewValue = info.LocationId.ToString() });
}
if (DAO.getInt(reader, "typeid") != info.TypeId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "typeid", OldValue = DAO.getInt(reader, "typeid").ToString(), NewValue = info.TypeId.ToString() });
}
if (DAO.getString(reader, "salaryMax").ToString() != info.SalaryMax)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryMax", OldValue = DAO.getString(reader, "salaryMax"), NewValue = info.SalaryMax });
}
if (DAO.getString(reader, "salaryMin").ToString() != info.SalaryMin)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryMin", OldValue = DAO.getString(reader, "salaryMin"), NewValue = info.SalaryMin });
}
if (DAO.getString(reader, "ReferenceNo").ToString() != info.ReferenceNo)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "ReferenceNo", OldValue = DAO.getString(reader, "ReferenceNo"), NewValue = info.ReferenceNo });
}
if (DAO.getString(reader, "title").ToString() != info.Title)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "title", OldValue = DAO.getString(reader, "title"), NewValue = info.Title });
}
//if (DAO.getString(reader, "subHeading").ToString() != info.SubHeading)
//{
// historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "subHeading", OldValue = DAO.getString(reader, "subHeading"), NewValue = info.SubHeading });
//}
//if (DAO.getString(reader, "searchTitle").ToString() != info.SubHeading)
//{
// historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "searchTitle", OldValue = DAO.getString(reader, "searchTitle"), NewValue = info.SearchTitle });
//}
if (DAO.getString(reader, "bullet1").ToString() != info.Bullet1)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet1", OldValue = DAO.getString(reader, "bullet1"), NewValue = info.Bullet1 });
}
if (DAO.getString(reader, "bullet2").ToString() != info.Bullet2)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet2", OldValue = DAO.getString(reader, "bullet2"), NewValue = info.Bullet2 });
}
if (DAO.getString(reader, "bullet3").ToString() != info.Bullet3)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "bullet3", OldValue = DAO.getString(reader, "bullet3"), NewValue = info.Bullet3 });
}
if (DAO.getString(reader, "summary").ToString() != info.Summary)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "summary", OldValue = DAO.getString(reader, "summary"), NewValue = info.Summary });
}
if (DAO.getString(reader, "jobContent").ToString() != info.JobContent)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobContent", OldValue = DAO.getString(reader, "jobContent"), NewValue = info.JobContent });
}
if ((string.IsNullOrEmpty(DAO.getString(reader, "websiteURL")) ? string.Empty : DAO.getString(reader, "websiteURL").ToString()) != info.WebsiteURL)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "websiteURL", OldValue = DAO.getString(reader, "websiteURL"), NewValue = info.WebsiteURL });
}
if (DAO.getString(reader, "adFooter") != info.AdFooter)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "adFooter", OldValue = DAO.getString(reader, "adFooter"), NewValue = info.AdFooter });
}
if (DAO.getInt(reader, "status") != info.Status)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "status", OldValue = DAO.getInt(reader, "status").ToString(), NewValue = info.Status.ToString() });
}
if (DAO.getInt(reader, "clientId") != info.ClientId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "clientId", OldValue = DAO.getInt(reader, "clientId").ToString(), NewValue = info.ClientId.ToString() });
}
if (DAO.getInt(reader, "jobIndustryId") != info.JobIndustryId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobIndustryId", OldValue = DAO.getInt(reader, "jobIndustryId").ToString(), NewValue = info.JobIndustryId.ToString() });
}
if (DAO.getInt(reader, "jobIndustrySubId") != info.JobIndustrySubId)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "jobIndustrySubId", OldValue = DAO.getInt(reader, "jobIndustrySubId").ToString(), NewValue = info.JobIndustrySubId.ToString() });
}
if (DAO.getBool(reader, "hotJob") != info.HotJob)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "hotJob", OldValue = DAO.getBool(reader, "hotJob").ToString(), NewValue = info.HotJob.ToString() });
}
if ((string.IsNullOrEmpty(DAO.getString(reader, "salaryCurrency")) ? string.Empty : DAO.getString(reader, "salaryCurrency").ToString()) != info.SalaryCurrency)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryCurrency", OldValue = DAO.getString(reader, "salaryCurrency"), NewValue = info.SalaryCurrency });
}
if (DAO.getInt(reader, "salaryFrequency") != info.SalaryFrequency)
{
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "salaryFrequency", OldValue = DAO.getInt(reader, "salaryFrequency").ToString(), NewValue = info.SalaryFrequency.ToString() });
}
if (historyInfo.Details.Count > 0)
{
history.insertHistory(historyInfo);
}
}
reader.Close();
reader.Dispose();
#endregion
return jobeditId;
}
public static uint insertJobDetail(JobDetailInfo info)
{
string sql = "insert into jobdetail (locationID, typeID,salaryMin,salaryMax,ReferenceNo,title,subHeading,searchTitle,bullet1,bullet2,bullet3,summary,jobContent,websiteURL,adFooter,isResidency,status,isApprove,clientId,jobIndustryId,jobIndustrySubId, createdDate,createdBy,hotJob,salaryCurrency,salaryFrequency,published,ismodified,isco08id,isicrev4id)";
sql += "values (?locationID, ?typeID,?salaryMin,?salaryMax,?ReferenceNo,?title,?subHeading,?searchTitle,?bullet1,?bullet2,?bullet3,?summary,?jobContent,?websiteURL,?adFooter,?isResidency,?status,?isApprove,?clientId,?jobIndustryId,?jobIndustrySubId,?createdDate,?createdBy,?hotJob,?salaryCurrency,?salaryFrequency,1,1,?isco08id,?isicrev4id); select last_insert_id()";
MySqlParameter[] param ={
new MySqlParameter("locationID",info.LocationId),
new MySqlParameter("typeID", info.TypeId),
new MySqlParameter("salaryMin", info.SalaryMin),
new MySqlParameter("salaryMax",info.SalaryMax),
new MySqlParameter("ReferenceNo",info.ReferenceNo),
new MySqlParameter("title",info.Title),
new MySqlParameter("subHeading",info.SubHeading),
new MySqlParameter("searchTitle",info.SearchTitle),
new MySqlParameter("bullet1",info.Bullet1),
new MySqlParameter("bullet2",info.Bullet2),
new MySqlParameter("bullet3",info.Bullet3),
new MySqlParameter("summary",info.Summary),
new MySqlParameter("jobContent",info.JobContent),
new MySqlParameter("websiteURL",info.WebsiteURL),
new MySqlParameter("adFooter",info.AdFooter),
new MySqlParameter("isResidency",info.IsResidency),
new MySqlParameter("status",info.Status),
new MySqlParameter("isApprove",info.IsApprove),
new MySqlParameter("clientId",info.ClientId),
new MySqlParameter("jobIndustryId",info.JobIndustryId),
new MySqlParameter("jobIndustrySubId",info.JobIndustrySubId),
new MySqlParameter("createdDate",info.CreatedDate),
new MySqlParameter("createdBy",info.CreatedBy),
new MySqlParameter("hotJob",info.HotJob),
new MySqlParameter("salaryCurrency",info.SalaryCurrency),
new MySqlParameter("salaryFrequency",info.SalaryFrequency),
new MySqlParameter("isco08id",info.ISCO08Id),
new MySqlParameter("isicrev4id",info.ISICRev4Id)
};
uint jobId = Convert.ToUInt32(DAO.ExecuteScalar(sql, param));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Job;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Add;
historyInfo.RecordId = jobId;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "title:" + info.Title + " ,referenceno:" + info.ReferenceNo });
history.insertHistory(historyInfo);
uint essid;
foreach (EssentialCriteriaInfo essInfo in info.EssentialCriteriaList)
{
essid = JobEssentialCriteriaDataProvider.insertEssentialCriteria(jobId, essInfo.Description, essInfo.AnswerLength, essInfo.SortOrder);
historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Essentialcriteria;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Add;
historyInfo.RecordId = essid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "Description:" + essInfo.Description + " ,AnswerLength:" + essInfo.AnswerLength });
history.insertHistory(historyInfo);
}
uint dessid;
foreach (DesirableCriteriaInfo desiInfo in info.DesirableCriteriaList)
{
dessid = JobDesirableCriteriaDataProvider.insertDesirableCriteria(jobId, desiInfo.Description, desiInfo.AnswerLength, desiInfo.SortOrder);
historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.Desirablecriteria;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Add;
historyInfo.RecordId = dessid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "Description:" + desiInfo.Description + " ,AnswerLength:" + desiInfo.AnswerLength });
history.insertHistory(historyInfo);
}
foreach (JobConsultantInfo consult in info.ConsultantList)
{
JobConsultantDataProvider.insertJobConsultant(jobId, consult.ConsultantId);
}
foreach (JobLocation location in info.LocationList)
{
JobLocationDataProvider.insertJobLocation(jobId, location.LocationId, location.Locationtype);
}
return jobId;
}
public static void addRepresentative(uint clientid, uint representativeid, DateTime startdate)
{
string sql = "insert into client_representatives (clientid, representativeid, startdate) values (?clientid, ?representativeid, ?startdate);select last_insert_id()";
uint id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("clientid", clientid), new MySqlParameter("representativeid", representativeid), new MySqlParameter("startdate", startdate)));
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.client_representatives;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ParentRecordId = clientid;
info.ModifiedDate = DateTime.Now;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "representativeid:" + representativeid + " ,clientid:" + clientid + " ,startdate:" + startdate });
history.insertHistory(info);
}
public static void insertCandidateJobs(int candidateid, int jobid, int category, int? searchid)
{
uint id;
string sql = "insert into candidates_jobs (candidateid,jobid,category,createddate,searchid,userid) values (?candidateid,?jobid,?category,?createddate,?searchid,?userid);select last_insert_id()";
id = Convert.ToUInt32(DAO.ExecuteScalar(sql, new MySqlParameter("candidateid", candidateid), new MySqlParameter("jobid", jobid), new MySqlParameter("category", category), new MySqlParameter("createddate", DateTime.UtcNow),
new MySqlParameter("searchid", searchid), new MySqlParameter("userid", GPSession.UserId)));
HistoryInfo info = new HistoryInfo();
info.UserId = GPSession.UserId;
info.ModuleId = (int)HistoryInfo.Module.candidate_jobs;
info.TypeId = (int)HistoryInfo.ActionType.Add;
info.RecordId = id;
info.ModifiedDate = DateTime.UtcNow;
info.Details = new List<HistoryDetailInfo>();
info.Details.Add(new HistoryDetailInfo { ColumnName = "All", NewValue = "candidateId:" + candidateid.ToString() + ", jobId:" + jobid.ToString() });
HistoryDataProvider history = new HistoryDataProvider();
history.insertHistory(info);
}
public static void updatePassword(uint userid, string password)
{
string sql = "select username from users where userid = ?userid";
string username = Convert.ToString(DAO.ExecuteScalar(sql, new MySqlParameter("userid", userid)));
updateUsernamePassword(userid, username, password);
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.User;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = userid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = "password", NewValue = "password changed" });
history.insertHistory(historyInfo);
}
public static void updateChangeRequestStaus(int filechangeId, int status, uint candidateid, int fileid, string newvalue, int changetype, int? replacefileid = null)
{
string sql = "update file_change set status=?status,modified=?modified,modifieduser=?userid,replacefileid=?replacefileid where file_changeid=?filechangeId";
DAO.ExecuteNonQuery(sql, new MySqlParameter("status", status), new MySqlParameter("filechangeId", filechangeId), new MySqlParameter("modified", DateTime.UtcNow), new MySqlParameter("userid", GPSession.UserId),
new MySqlParameter("replacefileid", replacefileid));
string columnname = string.Empty;
if (status == 2)//approve
{
if (changetype == 1)
{
columnname = "filename";
updateFilename(fileid, newvalue);
}
else if (changetype == 2)
{
columnname = "filetype";
updateFiletype(fileid, Convert.ToInt32(newvalue));
}
else
{
columnname = "delete";
updateFileDelete(fileid);
}
}
HistoryDataProvider history = new HistoryDataProvider();
HistoryInfo historyInfo = new HistoryInfo();
historyInfo.UserId = GPSession.UserId;
historyInfo.ModuleId = (int)HistoryInfo.Module.file_change;
historyInfo.TypeId = (int)HistoryInfo.ActionType.Edit;
historyInfo.RecordId = Convert.ToUInt32(filechangeId);
historyInfo.ParentRecordId = candidateid;
historyInfo.ModifiedDate = DateTime.Now;
historyInfo.Details = new List<HistoryDetailInfo>();
historyInfo.Details.Add(new HistoryDetailInfo { ColumnName = columnname, NewValue = status.ToString(), OldValue = "1" });
history.insertHistory(historyInfo);
}
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);
}
}