private void BuildCandidateLocationChart()
{
radChartCandidateLocation.ChartTitle.TextBlock.Text = ResourceManager.GetString("hypCandidateLocation");
radChartCandidateLocation.Chart.Series.Clear();
radChartCandidateLocation.PlotArea.Appearance.Dimensions.Margins.Left = 80;
radChartCandidateLocation.PlotArea.Appearance.Dimensions.Margins.Right = 100;
radChartCandidateLocation.PlotArea.Appearance.Dimensions.Margins.Top = 60;
radChartCandidateLocation.PlotArea.Appearance.Dimensions.Margins.Bottom = 200;
radChartCandidateLocation.PlotArea.YAxis.AxisLabel.TextBlock.Text = ResourceManager.GetString("lblStatisticsNbrOfCandidates");
radChartCandidateLocation.PlotArea.YAxis.AxisLabel.TextBlock.Appearance.TextProperties.Font = new Font("Arial", 12, FontStyle.Bold);
radChartCandidateLocation.PlotArea.YAxis.AxisLabel.Visible = true;
radChartCandidateLocation.PlotArea.YAxis.VisibleValues = ChartAxisVisibleValues.Positive;
radChartCandidateLocation.PlotArea.XAxis.AxisLabel.TextBlock.Text = ResourceManager.GetString("lblStatisticsLocation");
radChartCandidateLocation.PlotArea.XAxis.AxisLabel.TextBlock.Appearance.TextProperties.Font = new Font("Arial", 12, FontStyle.Bold);
radChartCandidateLocation.PlotArea.XAxis.AxisLabel.Visible = true;
radChartCandidateLocation.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Font = new Font("Arial", 10);
radChartCandidateLocation.PlotArea.XAxis.AutoScale = false;
radChartCandidateLocation.PlotArea.XAxis.LayoutMode = ChartAxisLayoutMode.Between;
radChartCandidateLocation.PlotArea.XAxis.Appearance.LabelAppearance.RotationAngle = 90;
radChartCandidateLocation.PlotArea.XAxis.Appearance.LabelAppearance.Position.AlignedPosition = AlignedPositions.Top;
radChartCandidateLocation.PlotArea.XAxis.Appearance.LabelAppearance.Position.Auto = false;
radChartCandidateLocation.PlotArea.XAxis.Appearance.LabelAppearance.Position.X = 0;
radChartCandidateLocation.PlotArea.XAxis.Appearance.LabelAppearance.Position.Y = 0;
ChartSeries locationSeries = new ChartSeries("Location", ChartSeriesType.Bar);
locationSeries.Appearance.FillStyle.MainColor = Color.LightSteelBlue;
locationSeries.Appearance.FillStyle.FillType = FillType.Solid;
locationSeries.Appearance.TextAppearance.Visible = true;
radChartCandidateLocation.AddChartSeries(locationSeries);
IList<ParamLocations> locationList = new ParamLocationsRepository().GetAllLocations();
foreach (ParamLocations location in locationList)
{
radChartCandidateLocation.PlotArea.XAxis.AddItem(location.Location.Trim());
int number = NeosDAO.GetNumberOfCandiateByLocation(location.Location.Trim(),
location.LocationUk.Trim(), location.LocationNL.Trim());
locationSeries.AddItem(number);
}
}
private static string BuildAdvancedQuery(CandidateSearchCriteria criteria, out List<SqlParameter> paramList)
{
string sql = @"select distinct Cand.CandidatID, Cand.Unit, Cand.Nom, Cand.Prenom, Cand.Adresse, Cand.CVMail,
Cand.CodePostal, Cand.inactif, Cand.DateModif
from tblCandidat as Cand
left outer join tblCandidatFonction as Func on Cand.candidatID=Func.CandidatID
left outer join tblCandidatAttentes as Att on Cand.CandidatID=Att.CandidatID
left outer join tblCandidatConaiss as Con on Cand.candidatID=Con.CandidatID
left outer join tblCandidatformation as Form on Cand.candidatID=Form.CandidatID
left outer join tblCandidatEval as CanEval on Cand.candidatID=CanEval.candidatID
";
StringBuilder whereCond = new StringBuilder();
whereCond.Append(" where Cand.CandidatID is not null ");
paramList = new List<SqlParameter>();
if (!string.IsNullOrEmpty(criteria.LastName))
{
whereCond.Append(" and Cand.Nom like @lastName ");
paramList.Add(new SqlParameter("@lastName", "%" + criteria.LastName + "%"));
}
if (!string.IsNullOrEmpty(criteria.FirstName))
{
whereCond.Append(" and Cand.Prenom like @firstName ");
paramList.Add(new SqlParameter("@firstName", "%" + criteria.FirstName + "%"));
}
if (criteria.AgeFrom.HasValue)
{
whereCond.Append(" and Cand.DateNaissance >= @DateNaissanceFrom ");
paramList.Add(new SqlParameter("@DateNaissanceFrom", criteria.AgeFrom.Value));
}
if (criteria.AgeTo.HasValue)
{
whereCond.Append(" and Cand.DateNaissance <= @DateNaissanceTo ");
paramList.Add(new SqlParameter("@DateNaissanceTo", criteria.AgeTo.Value));
}
if (criteria.Active == "Yes")
whereCond.Append(" and Cand.inactif = 0 ");
else if (criteria.Active == "No")
whereCond.Append(" and Cand.inactif = 1 ");
if (!string.IsNullOrEmpty(criteria.Interviewer))
{
whereCond.Append(" and Cand.Interviewer = @Interviewer ");
paramList.Add(new SqlParameter("@Interviewer", criteria.Interviewer));
}
if (criteria.DateInterviewerFrom.HasValue)
{
whereCond.Append(" and Cand.DateInterview >= @DateInterviewFrom ");
paramList.Add(new SqlParameter("@DateInterviewFrom", criteria.DateInterviewerFrom.Value));
}
if (criteria.DateInterviewerTo.HasValue)
{
whereCond.Append(" and Cand.DateInterview <= @DateInterviewTo ");
paramList.Add(new SqlParameter("@DateInterviewTo", criteria.DateInterviewerTo.Value));
}
//location
string[] locations = criteria.Locations.ToArray();
ParamLocationsRepository locRepo = new ParamLocationsRepository();
if (locations != null && locations.Length > 0)
{
ParamLocations location0 = locRepo.FindOne(new ParamLocations(locations[0]));
whereCond.Append(" and ((Att.region like @location0 or Att.region like @locationUk0 or Att.region like @locationNl0)");
paramList.Add(new SqlParameter("@location0", "%" + location0.Location + "%"));
paramList.Add(new SqlParameter("@locationUk0", "%" + location0.LocationUk + "%"));
paramList.Add(new SqlParameter("@locationNl0", "%" + location0.LocationNL + "%"));
for (int i = 1; i < locations.Length; i++)
{
ParamLocations locationi = locRepo.FindOne(new ParamLocations(locations[i]));
whereCond.AppendFormat(" or (Att.region like @location{0} or Att.region like @locationUk{0} or Att.region like @locationNl{0})", i.ToString());
paramList.Add(new SqlParameter(string.Format("@location{0}", i), "%" + locationi.Location + "%"));
paramList.Add(new SqlParameter(string.Format("@locationUk{0}", i), "%" + locationi.LocationUk + "%"));
paramList.Add(new SqlParameter(string.Format("@locationNl{0}", i), "%" + locationi.LocationNL + "%"));
}
whereCond.Append(") ");
}
//study
string[] studies = criteria.StudyAndLevelIDs;
if (studies != null && studies.Length > 0)
{
string[] composeID = studies[0].Split(';');
whereCond.Append(" and ((Form.FormationID = @FormationID0 and Form.NiveauEtudeID = @NiveauEtudeID0) ");
paramList.Add(new SqlParameter("@FormationID0", int.Parse(composeID[0])));
paramList.Add(new SqlParameter("@NiveauEtudeID0", int.Parse(composeID[1])));
for (int i = 1; i < studies.Length; i++)
{
string[] composeID2 = studies[i].Split(';');
if (criteria.StudyHaveOne)
whereCond.AppendFormat(" or (Form.FormationID = @FormationID{0} and Form.NiveauEtudeID = @NiveauEtudeID{0}) ", i.ToString());
else
{
whereCond.AppendFormat(@" and ((select count(*) from tblCandidatFormation t1
where t1.FormationID = @FormationID{0} and t1.NiveauEtudeID = @NiveauEtudeID{0}
and Cand.candidatID = t1.candidatID) = 1)", i.ToString());
//whereCond.AppendFormat(" and (Form.FormationID = @FormationID{0} and Form.NiveauEtudeID = @NiveauEtudeID{0}) ", i.ToString());
}
paramList.Add(new SqlParameter(string.Format("@FormationID{0}", i), int.Parse(composeID2[0])));
paramList.Add(new SqlParameter(string.Format("@NiveauEtudeID{0}", i), int.Parse(composeID2[1])));
}
whereCond.Append(") ");
}
//knowledge
if (criteria.FrenchLevel > 0)
{
whereCond.Append(" and CanEval.Francais >= @Francais ");
paramList.Add(new SqlParameter("@Francais", criteria.FrenchLevel));
}
if (criteria.DutchLevel > 0)
{
whereCond.Append(" and CanEval.Neerlandais >= @Neerlandais ");
paramList.Add(new SqlParameter("@Neerlandais", criteria.DutchLevel));
}
if (criteria.EnglishLevel > 0)
{
whereCond.Append(" and CanEval.Anglais >= @Anglais ");
paramList.Add(new SqlParameter("@Anglais", criteria.EnglishLevel));
}
if (criteria.GermanLevel > 0)
{
whereCond.Append(" and CanEval.Allemand >= @Allemand ");
paramList.Add(new SqlParameter("@Allemand", criteria.GermanLevel));
}
if (criteria.SpanishLevel > 0)
{
whereCond.Append(" and CanEval.Espagnol >= @Espagnol ");
paramList.Add(new SqlParameter("@Espagnol", criteria.SpanishLevel));
}
if (criteria.ItalianLevel > 0)
{
whereCond.Append(" and CanEval.Italien >= @Italien ");
paramList.Add(new SqlParameter("@Italien", criteria.ItalianLevel));
}
if (!string.IsNullOrEmpty(criteria.OtherLang) && criteria.OtherLevel > 0)
{
whereCond.Append(" and CanEval.AutreLangue = @AutreLangue ");
whereCond.Append(" and CanEval.ScoreAutreLangue >= @ScoreAutreLangue ");
paramList.Add(new SqlParameter("@AutreLangue", criteria.OtherLang));
paramList.Add(new SqlParameter("@ScoreAutreLangue", criteria.OtherLevel));
}
int[] knowledgeIDs = criteria.KnowledgeIDs;
if (knowledgeIDs != null && knowledgeIDs.Length > 0)
{
whereCond.Append(" and (Con.ConnaissanceID = @ConnaissanceID0 ");
paramList.Add(new SqlParameter("@ConnaissanceID0", knowledgeIDs[0]));
for (int i = 1; i < knowledgeIDs.Length; i++)
{
if (criteria.KnowledgeHaveOne)
whereCond.AppendFormat(" or Con.ConnaissanceID = @ConnaissanceID{0} ", i.ToString());
else
{
whereCond.AppendFormat(@" and ((select count(*) from tblCandidatConaiss t1
where t1.ConnaissanceID = @ConnaissanceID{0}
and Cand.candidatID = t1.candidatID) = 1)", i.ToString());
//whereCond.AppendFormat(" and Con.ConnaissanceID = @ConnaissanceID{0} ", i.ToString());
}
paramList.Add(new SqlParameter(string.Format("@ConnaissanceID{0}", i), knowledgeIDs[i]));
}
whereCond.Append(") ");
}
//Function
int[] functionIDs = criteria.FunctionIDs;
if (functionIDs != null && functionIDs.Length > 0)
{
whereCond.Append(" and (Func.FonctionID = @FonctionID0 ");
paramList.Add(new SqlParameter("@FonctionID0", functionIDs[0]));
for (int i = 1; i < functionIDs.Length; i++)
{
if (criteria.FunctionHaveOne)
whereCond.AppendFormat(" or Func.FonctionID = @FonctionID{0} ", i.ToString());
else
{
whereCond.AppendFormat(@" and ((select count(*) from tblCandidatFonction t1
where t1.FonctionID = @FonctionID{0}
and Cand.candidatID = t1.candidatID) = 1)", i.ToString());
//whereCond.AppendFormat(" and Func.FonctionID = @FonctionID{0} ", i.ToString());
}
paramList.Add(new SqlParameter(string.Format("@FonctionID{0}", i), functionIDs[i]));
}
whereCond.Append(") ");
}
return sql + whereCond.ToString();
}
private void LoadGeneralTabData(Candidate candidate)
{
ParamPaysRepository paramPaysRepo = new ParamPaysRepository();
ddlCountry.DataValueField = "PaysID";
ddlCountry.DataTextField = "Pays";
ddlCountry.DataSource = paramPaysRepo.FindAll();
ddlCountry.DataBind();
ddlGenre.Items.Add(new RadComboBoxItem("M", "M"));
ddlGenre.Items.Add(new RadComboBoxItem("F", "F"));
ddlStatus.Items.Add(new RadComboBoxItem("", ""));
ddlStatus.Items.Add(new RadComboBoxItem("actif", "False"));
ddlStatus.Items.Add(new RadComboBoxItem("inactif", "True"));
ParamNationaliteRepository nationalityRepo = new ParamNationaliteRepository();
ddlNationality.DataValueField = "NationaliteID";
ddlNationality.DataTextField = "NationaliteID";
ParamNationalite emptyNation = new ParamNationalite();
emptyNation.NationaliteID = string.Empty;
emptyNation.Label = string.Empty;
IList<ParamNationalite> nationList = nationalityRepo.FindAll();
nationList.Insert(0, emptyNation);
ddlNationality.DataSource = nationList;
ddlNationality.DataBind();
ddlCanArea.DataValueField = "Location";
ddlCanArea.DataTextField = "Location";
ParamLocationsRepository locationRepo = new ParamLocationsRepository();
ddlCanArea.DataSource = locationRepo.GetAllLocations();
ddlCanArea.DataBind();
ddlSalaryWish.Items.Add(new RadComboBoxItem("1500-1999"));
ddlSalaryWish.Items.Add(new RadComboBoxItem("2000-2999"));
ddlSalaryWish.Items.Add(new RadComboBoxItem("3000-3999"));
ddlSalaryWish.Items.Add(new RadComboBoxItem("4000-4999"));
ddlSalaryWish.Items.Add(new RadComboBoxItem(">5000"));
ddlCompanyWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("notImportantCompaniesText")));
ddlCompanyWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("smallAndMediumCompaniesText")));
ddlCompanyWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("bigCompaniesText")));
ddlCompanyWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("groupCompaniesText")));
ddlCompanyWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("multinationalCompaniesText")));
ddlContractTypeWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("notImportantContractText")));
ddlContractTypeWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("permanentContractText")));
ddlContractTypeWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("fixedPeriodeContractText")));
ddlContractTypeWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("projectMissionContractText")));
ddlContractTypeWish.Items.Add(new RadComboBoxItem(ResourceManager.GetString("freelanceContractText")));
if (candidate != null)
{
txtAddress.Text = !string.IsNullOrEmpty(candidate.Address) ? candidate.Address : "";
txtZip.Text = !string.IsNullOrEmpty(candidate.ZipCode) ? candidate.ZipCode : "";
txtCity.Text = !string.IsNullOrEmpty(candidate.City) ? candidate.City : "";
datDateOfBirth.SelectedDate = candidate.BirthDate;
if (candidate.BirthDate.HasValue)
{
DateTime today = DateTime.Today;
DateTime birthDay = candidate.BirthDate.Value;
int age = today.Year - birthDay.Year;
if ((today.Month < birthDay.Month) || (today.Month == birthDay.Month) && (today.Day < birthDay.Day))
age = age - 1;
txtAge.Text = age.ToString();
}
ddlCountry.SelectedValue = candidate.CountryCode;
if (candidate.Gender != null)
ddlGenre.SelectedValue = candidate.Gender.ToUpper();
ddlNationality.SelectedValue = candidate.Nationlity;
datCreationDate.SelectedDate = candidate.CreationDate;
ddlStatus.SelectedValue = candidate.Inactive.ToString();
txtInactivityReason.Text = candidate.ReasonDesactivation;
txtRemarkGeneral.Text = candidate.Remark;
//Tab General : Candidate Wishes :
CandidateExpectationRepository candidateExpectRepo = new CandidateExpectationRepository();
CandidateExpectation expect = candidateExpectRepo.GetCandidateExpectation(candidate.CandidateId);
if (expect != null)
{
SessionManager.CandidateExpectation = expect;
//txtArea.Text = expect.Region;
if (!string.IsNullOrEmpty(expect.Region))
{
string[] areaArray = expect.Region.Split(';');
for (int i = 0; i < areaArray.Length; i++)
{
listCanArea.Items.Add(new ListItem(areaArray[i].Trim(), areaArray[i].Trim()));
}
}
hiddenCanAreaList.Value = expect.Region;
ddlCompanyWish.Text = expect.Company;
txtFunction.Text = expect.Function;
ddlSalaryWish.Text = expect.SalaryLevel;
ddlContractTypeWish.Text = expect.TypeofContract;
txtMotivation.Text = expect.Motivation;
}
}
}
protected void OnLocationDeleteClicked(object sender, EventArgs e)
{
LinkButton lnkItem = (LinkButton)sender;
string location = lnkItem.CommandArgument;
ParamLocations deleteItem = new ParamLocations(location);
ParamLocationsRepository repo = new ParamLocationsRepository();
repo.Delete(deleteItem);
BindGridData();
gridLocations.DataBind();
}
private void BindGridData()
{
ParamLocationsRepository repo = new ParamLocationsRepository();
gridLocations.DataSource = repo.GetAllLocations();
}
protected void OnBtnSaveClicked(object sender, EventArgs e)
{
ParamLocationsRepository repo = new ParamLocationsRepository();
ParamLocations saveItem = new ParamLocations();
saveItem.Location = txtLocation.Text.Trim();
if (!string.IsNullOrEmpty(txtHierarchy.Text))
saveItem.Hierarchie = Int32.Parse(txtHierarchy.Text);
else
saveItem.Hierarchie = null;
saveItem.LocationUk = txtLocationUk.Text.Trim();
saveItem.LocationNL = txtLocationNl.Text.Trim();
saveItem.CodeLocation = txtCode.Text.Trim();
string location = Request.QueryString["Location"];
if (string.IsNullOrEmpty(location))
{
//Insert new record
ParamLocations oldItem = repo.GetLocation(txtLocation.Text.Trim());
ParamLocations oldItem1 = repo.GetLocationByCode(txtCode.Text.Trim());
if (oldItem == null && oldItem1 == null)
repo.InserNewLocation(saveItem);
else
{
string message = ResourceManager.GetString("itemAlreadyExist");
string script1 = "<script type=\"text/javascript\">";
script1 += " alert(\"" + message + "\");";
script1 += " </script>";
if (!ClientScript.IsClientScriptBlockRegistered("redirectUser"))
ClientScript.RegisterStartupScript(this.GetType(), "redirectUser", script1);
}
}
else
{
location = location.Trim();
string message = string.Empty;
ParamLocations oldLocation = repo.GetLocation(location);
if (saveItem.Location.Trim() == location && saveItem.CodeLocation.Trim() == oldLocation.CodeLocation.Trim())
{
//Update the record.
repo.Update(saveItem);
}
else if(saveItem.Location.Trim() != location)
{
if (oldLocation.NumberIDUsed > 0)
message = ResourceManager.GetString("messageLocationBeingUsed");
}
else
{
if (oldLocation.NumberCodeUsed > 0)
message = ResourceManager.GetString("messageLocationCodeBeingUsed");
}
if (string.IsNullOrEmpty(message))
{
if (saveItem.Location.Trim() == location)
repo.Update(saveItem);
else
{
repo.Delete(new ParamLocations(location));
repo.InserNewLocation(saveItem);
}
}
else
{
string scriptMes = "<script type=\"text/javascript\">";
scriptMes += " alert(\"" + message + "\")";
scriptMes += " </script>";
if (!ClientScript.IsClientScriptBlockRegistered("redirectUser"))
ClientScript.RegisterStartupScript(this.GetType(), "redirectUser", scriptMes);
return;
}
}
string script = "<script type=\"text/javascript\">";
script += " OnBtnSaveClientClicked();";
script += " </script>";
if (!ClientScript.IsClientScriptBlockRegistered("redirectUser"))
ClientScript.RegisterStartupScript(this.GetType(), "redirectUser", script);
}
protected void Page_Load(object sender, EventArgs e)
{
if (SessionManager.CurrentUser == null)
{
Common.RedirectToLoginPage(this);
return;
}
else if (!IsPostBack)
{
FillLabelLanguage();
if (!string.IsNullOrEmpty(Request.QueryString["Location"]))
{
string locationID = Request.QueryString["Location"];
ParamLocations location = new ParamLocationsRepository().GetLocation(locationID);
txtLocation.Text = location.Location;
if(location.Hierarchie.HasValue)
txtHierarchy.Text = location.Hierarchie.Value.ToString();
txtLocationUk.Text = location.LocationUk;
txtLocationNl.Text = location.LocationNL;
txtCode.Text = location.CodeLocation;
}
}
}
