public static IList<Candidate> AdvancedSearchCandidatesByPage(CandidateSearchCriteria criteria,
int pageSize, int pageNumber, string sortOrder, string sortOrderInvert)
{
IList<Candidate> result = new List<Candidate>();
List<SqlParameter> paramList;
string sql = BuildAdvancedQuery(criteria, out paramList);
sql = "select {0} * from (" + sql + ") as T0 ";
sql += " order by " + sortOrder;
sql = string.Format(sql, "top " + pageSize * pageNumber + " ");
sql = "(select top " + pageSize + " * from \n (" + sql + " ) As T1 \n"
+ " Order by " + sortOrderInvert;
sql = "select * from \n " + sql + " ) As T2 \n"
+ " Order by " + sortOrder;
//sql = string.Format(sql, lastName);
SqlDataReader reader = null;
try
{
reader = SqlHelper.ExecuteReader(_connectionString, CommandType.Text, sql, paramList.ToArray());
result = GetCandidateFromReader(reader);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return result;
}
public static int CountAdvancedSearchCandidates(CandidateSearchCriteria criteria)
{
List<SqlParameter> paramList;
string sql = BuildAdvancedQuery(criteria, out paramList);
sql = "select count(*) from (" + sql + ") as T0 ";
return (int) SqlHelper.ExecuteScalar(_connectionString, CommandType.Text, sql, paramList.ToArray());
}
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();
}
protected void Page_Load(object sender, EventArgs e)
{
if (SessionManager.CurrentUser == null)
{
Common.RedirectToLoginPage(this);
return;
}
else if (!IsPostBack)
{
FillLabelLanguage();
//set properties for the labels of each column
RadChartGeneralStatistics.PlotArea.Appearance.Dimensions.Margins.Left = 150;
RadChartGeneralStatistics.PlotArea.Appearance.Dimensions.Margins.Right = 50;
RadChartGeneralStatistics.PlotArea.XAxis.Appearance.TextAppearance.MaxLength = 1000;
RadChartGeneralStatistics.PlotArea.XAxis.Appearance.TextAppearance.AutoTextWrap = AutoTextWrap.Auto;
RadChartGeneralStatistics.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Font = new Font("Arial", 10);
RadChartGeneralStatistics.PlotArea.XAxis.AutoScale = false;
RadChartGeneralStatistics.PlotArea.XAxis.LayoutMode = ChartAxisLayoutMode.Between;
//set the value for the columns
ChartSeries generalSeries = new ChartSeries();// ("General", ChartSeriesType.a);
generalSeries.Appearance.FillStyle.MainColor = Color.LightGreen;
generalSeries.Appearance.FillStyle.FillType = FillType.Solid;
generalSeries.Appearance.TextAppearance.Visible = true;
RadChartGeneralStatistics.Chart.Series.Clear();
RadChartGeneralStatistics.AddChartSeries(generalSeries);
List<int> generalStatisticList = new List<int>();
CandidateSearchCriteria criteria = new CandidateSearchCriteria();
//Number of active candidates.
criteria.Active = "Yes";
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblNbrActiveCandidateText"));
generalSeries.AddItem(NeosDAO.CountAdvancedSearchCandidates(criteria));
//Total number of candidates.
criteria.Active = null;
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblTotalCandidateText"));
generalSeries.AddItem(NeosDAO.CountAdvancedSearchCandidates(criteria));
//Number of active candidates related to the currently logged Néos user (based on [tblCandidat].[Interviewer])
criteria.Active = "Yes";
criteria.Interviewer = SessionManager.CurrentUser.UserID.Trim();
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblNbrActCanOfCurUserText"));
generalSeries.AddItem(NeosDAO.CountAdvancedSearchCandidates(criteria));
//Total number of candidates related to the currently logged Néos user
criteria.Active = null;
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblTotalCanOfCurUserText"));
generalSeries.AddItem(NeosDAO.CountAdvancedSearchCandidates(criteria));
CompanyRepository comRepo = new CompanyRepository();
//Number of companies.
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblNbrCompanyText"));
generalSeries.AddItem(comRepo.CountAllCompanies());
//Number of Active Customers
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblNbrActiveCustomerText"));
generalSeries.AddItem(comRepo.CountCustomerCompanies());
//Number of Prospects
RadChartGeneralStatistics.PlotArea.XAxis.AddItem(ResourceManager.GetString("lblNbrProspectsText"));
generalSeries.AddItem(comRepo.CountPropectCompanies());
}
}
