/// <summary>
/// This function returns the result for auto completes
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public List<AutoComplete> GetAutoComplete(AutoCompleteFilter filters)
{
var result = new List<AutoComplete>();
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filters.Count),
new SqlParameter("@Keyword", filters.Prefix ),
new SqlParameter("@ClientId", filters.ClientId ),
new SqlParameter("@SelectedIds",string.IsNullOrEmpty( filters.SelectedIds)? "": filters.SelectedIds.Trim().TrimEnd(Convert.ToChar(",")) ),
new SqlParameter("@RetrieveType", filters.Type),
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("AutoComplete", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
result = (from DataRow dr in ds.Tables[0].Rows
select new AutoComplete
{
id = (int)dr["Id"],
name = (dr["Name"] ?? "").ToString(),
}).ToList();
}
return result;
}
/// <summary>
/// This function returns the job applcations for the given filters
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<JobApplication> GetJobApplications(JobApplicationFilter filters)
{
var jobApplications = new List<JobApplication>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@JobId", filters.JobId),
new SqlParameter("@ApplicationStatusId", filters.ApplicationStatusId),
new SqlParameter("@CandidateId", filters.CandidateId),
new SqlParameter("@JobApplicationIdsIn", filters.JobApplicationIdsIn),
new SqlParameter("@SortBy", filters.SortBy),
new SqlParameter("@NeedCandidateDetails", filters.NeedCandidateDetails),
new SqlParameter("@NeedApplicationStatus", filters.NeedApplicationStatus),
new SqlParameter("@NeedJobTitle", filters.NeedJobTitle)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveJobApplications", parameters);
// iterate and return job applications
if (ds != null && ds.Tables.Count > 0)
{
jobApplications = (from DataRow dr in ds.Tables[0].Rows
select ExtractJobApplication(filters, dr)).ToList();
}
return jobApplications;
}
/// <summary>
/// This function retuns emails and smses for given filters
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<ContactEmailSms> GetContactEmailsAndSmses(HelperContactEmailSmsFilter filters)
{
var emailAndSmses = new List<ContactEmailSms>();
// setup the parameters
SqlParameter[] parameters =
{
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@UserId", filters.UserId),
new SqlParameter("@RetrieveType", filters.RetrieveType),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@CandidateId", filters.ContactId),
new SqlParameter("@MostRecent", filters.MostRecent)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveContactSmsAndEmails", parameters);
// iterate and return sms and emails
if (ds != null && ds.Tables.Count > 0)
{
emailAndSmses = (from DataRow dr in ds.Tables[0].Rows
select ExtractEmailSms(dr)).ToList();
}
return emailAndSmses;
}
/// <summary>
/// This function retuns a job board list fr the given filters
/// </summary>
/// <param name="jBoardFilter"></param>
/// <returns></returns>
public IEnumerable<JobBoard> GetJobBoards(JobBoardFilter jBoardFilter)
{
var jobBoards = new List<JobBoard>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@IsActive", jBoardFilter.IsActive),
new SqlParameter("@IsTopJobBoards", jBoardFilter.IsTopJobBoards),
new SqlParameter("@Keyword", jBoardFilter.keyword),
new SqlParameter("@SectorIds", string.IsNullOrEmpty( jBoardFilter.SectorIds) ? "" : jBoardFilter.SectorIds.Trim().TrimEnd(',')),
new SqlParameter("@JobId", jBoardFilter.JobId)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveJobBoards", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
jobBoards = (from DataRow dr in ds.Tables[0].Rows
select new JobBoard
{
JobBoardId = (int)dr["JobBoardId"],
Title = (dr["Title"] ?? "").ToString(),
CreditsPerJob = (int)dr["CreditsPerJob"],
IsTopJobBoard = (bool)dr["IsTopJobBoard"],
TotalCredits = (int)dr["TotalCredits"],
PricePerJob = (double)dr["PricePerJob"],
JobBoardUrl = (dr["JobBoardUrl"] ?? "").ToString(),
IsSelected = (int)dr["IsSelected"] > 0,
}).ToList();
}
//return job board list
return jobBoards;
}
/// <summary>
/// This function gets the clients and contacts for the given filters
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<ClientAndContact> GetClientsAndContacts(ClientContactFilter filters)
{
var parser = new FullTextQueryParser(true);
filters.Keyword = parser.ParseQuery(filters.Keyword);
var clientsAndContacts = new List<ClientAndContact>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@ConsultantId", filters.ConsultantId),
new SqlParameter("@UserId", filters.UserId),
new SqlParameter("@ClientId", filters.ClientId ),
new SqlParameter("@ContactStatuses", filters.ContactStatusIds),
new SqlParameter("@ClientStatuses", filters.ClientStatusIds),
new SqlParameter("@Client", filters.Client),
new SqlParameter("@Forename", filters.Forename),
new SqlParameter("@Surname", filters.Surname),
new SqlParameter("@Email", filters.Email),
new SqlParameter("@JobTitle", filters.JobTitle),
new SqlParameter("@Employer", filters.Employer),
new SqlParameter("@Sectors", filters.Sectors),
new SqlParameter("@LocationId", filters.Locations),
new SqlParameter("@Qualifications", filters.Qualifications),
new SqlParameter("@Skills", filters.Skills),
new SqlParameter("@MinSalary", filters.MinSalary),
new SqlParameter("@MaxSalary", filters.MaxSalary),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@SortBy", filters.SortBy),
new SqlParameter("@Deleted" ,filters.Deleted),
new SqlParameter("@LastUpdatedDateFrom" ,filters.LastUpdatedDateFrom),
new SqlParameter("@LastUpdatedDateTo", filters.LastUpdatedDateTo),
new SqlParameter("@VacancyType" ,filters.VacancyType),
new SqlParameter("@GeneralCandidatesFolderId" ,filters.GeneralCandidatesFolderId),
new SqlParameter("@Hours" ,filters.Hours),
new SqlParameter("@GroupId" ,filters.GroupId),
new SqlParameter("@Miles" ,filters.LocationRadius),
new SqlParameter("@RetrieveType",filters.RetrieveType)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveClientContacts", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
clientsAndContacts = (from DataRow dr in ds.Tables[0].Rows
select ExtractClientContact(dr)).ToList();
}
return clientsAndContacts;
}
/// <summary>
/// This function gets the candidates for the given filters
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<CandidateBasic> GetCandidatesBasic(CandidateFilter filters)
{
// cv keyword parser
var parser = new FullTextQueryParser(true);
filters.Keyword = parser.ParseQuery(filters.Keyword);
var candidates = new List<CandidateBasic>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@ClientId", filters.ClientId ),
new SqlParameter("@Forename", filters.Forename),
new SqlParameter("@Surname", filters.Surname),
new SqlParameter("@Email", filters.Email),
new SqlParameter("@JobTitle", filters.JobTitle),
new SqlParameter("@Employer", filters.Employer),
new SqlParameter("@Sectors", filters.Sectors),
new SqlParameter("@LocationId", filters.Locations),
new SqlParameter("@MinSalary", filters.MinSalary),
new SqlParameter("@MaxSalary", filters.MaxSalary),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@SortBy", filters.SortBy),
new SqlParameter("@Deleted" ,filters.Deleted),
new SqlParameter("@LastUpdatedDateFrom" ,filters.LastUpdatedDateFrom),
new SqlParameter("@LastUpdatedDateTo", filters.LastUpdatedDateTo),
new SqlParameter("@VacancyType" ,filters.VacancyType),
new SqlParameter("@Hours" ,filters.Hours),
new SqlParameter("@GroupId" ,filters.GroupId),
new SqlParameter("@Miles" ,filters.LocationRadius)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveCandidates", parameters);
// iterate and return candidate
if (ds != null && ds.Tables.Count > 0)
{
candidates = (from DataRow dr in ds.Tables[0].Rows
select ExtractCandidate(dr)).ToList();
}
return candidates;
}
/// <summary>
/// This function returns clients and contacts for top search
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<ClientAndContact> GetTopSearchResults(ClientContactFilter filters)
{
var clientsAndContacts = new List<ClientAndContact>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", 25),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@Keyword", filters.Keyword)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveTopSearchResults", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
clientsAndContacts = (from DataRow dr in ds.Tables[0].Rows
select ExtractClientContactTopSearch(dr)).ToList();
}
return clientsAndContacts;
}
/// <summary>
/// This function get the selected contact/client objects for popup
/// </summary>
/// <param name="selectedClientIds"></param>
/// <param name="filters"></param>
/// <param name="selectedContactIds"></param>
/// <returns></returns>
public IEnumerable<ContactClient> GetSelectedClientContactsForPoup(string selectedContactIds, string selectedClientIds, ClientContactFilter filters)
{
// init the response
var result = new List<ContactClient>();
// setup the parameters
SqlParameter[] parameters;
if (string.IsNullOrEmpty(selectedContactIds) && string.IsNullOrEmpty(selectedClientIds))
{
var parser = new FullTextQueryParser(true);
filters.Keyword = parser.ParseQuery(filters.Keyword);
parameters = new[] {
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@ConsultantId", filters.ConsultantId),
new SqlParameter("@UserId", filters.UserId),
new SqlParameter("@ClientId", filters.ClientId ),
new SqlParameter("@ContactStatuses", filters.ContactStatusIds),
new SqlParameter("@ClientStatuses", filters.ClientStatusIds),
new SqlParameter("@Client", filters.Client),
new SqlParameter("@Forename", filters.Forename),
new SqlParameter("@Surname", filters.Surname),
new SqlParameter("@Email", filters.Email),
new SqlParameter("@JobTitle", filters.JobTitle),
new SqlParameter("@Employer", filters.Employer),
new SqlParameter("@Sectors", filters.Sectors),
new SqlParameter("@LocationId", filters.Locations),
new SqlParameter("@Qualifications", filters.Qualifications),
new SqlParameter("@Skills", filters.Skills),
new SqlParameter("@MinSalary", filters.MinSalary),
new SqlParameter("@MaxSalary", filters.MaxSalary),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@SortBy", filters.SortBy),
new SqlParameter("@Deleted" ,filters.Deleted),
new SqlParameter("@LastUpdatedDateFrom" ,filters.LastUpdatedDateFrom),
new SqlParameter("@LastUpdatedDateTo", filters.LastUpdatedDateTo),
new SqlParameter("@VacancyType" ,filters.VacancyType),
new SqlParameter("@Hours" ,filters.Hours),
new SqlParameter("@GroupId" ,filters.GroupId),
new SqlParameter("@Miles" ,filters.LocationRadius),
new SqlParameter("@RetrieveType",filters.RetrieveType)
};
}
else
{
//assume that you dont use the filters, if you send the client ids or contacts ids
parameters = new[] {
new SqlParameter("@RetrieveType",filters.RetrieveType),
new SqlParameter("@CandidateIdsIn", string.IsNullOrEmpty(selectedContactIds) ? "" : selectedContactIds ),
new SqlParameter("@ClientIdsIn", string.IsNullOrEmpty(selectedClientIds) ? "" : selectedClientIds )
};
}
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveClientContactsForPopup", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
var obj = ExtractClientOrContact(dr);
var contact = obj as Candidate;
if (contact != null)
result.Add(new ContactClient
{
ClientOrContact = contact,
IsContact = true
});
else
{
//client found
var client = obj as Client;
if (client != null)
result.Add(new ContactClient
{
ClientOrContact = client,
IsContact = false
});
}
}
}
return result;
}
/// <summary>
/// This function add Client Contacts to group
/// </summary>
/// <param name="gr"></param>
/// <returns></returns>
public int AddClientContactsToGroup(ClientContactAddToGroup gr)
{
var context = new dbDataContext();
// check for the group
if (gr.Group.GroupId == 0 && !string.IsNullOrEmpty(gr.Group.Name.Trim()))
{
//create new group
var group = new tbl_Group
{
UserId = gr.Group.UserId,
CreatedDate = DateTime.Now,
IsPublic = gr.Group.IsPublic,
Name = gr.Group.Name.Trim()
};
context.tbl_Groups.InsertOnSubmit(group);
context.SubmitChanges();
gr.Group.GroupId = group.GroupId;
}
else if (gr.Group.GroupId == 0)
return -1;
//add client and contacts
if (!string.IsNullOrEmpty(gr.SelectedIds))
{
//If selected Ids were passed
foreach (var id in gr.SelectedIds.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries))
{
var contactId = int.Parse(id);
var contactCount =
context.tbl_CandidateGroups.Count(t => t.GroupId == gr.Group.GroupId && t.CandidateId == contactId);
if (contactCount == 0)
{
var clientContactGroup = new tbl_CandidateGroup
{
CandidateId = contactId,
GroupId = gr.Group.GroupId
};
context.tbl_CandidateGroups.InsertOnSubmit(clientContactGroup);
context.SubmitChanges();
}
}
}
else
{
//use stored procedure to insert contacts/clients
var filters = gr.Filter;
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@GroupToAdd", gr.Group.GroupId),
new SqlParameter("@ConsultantId", filters.ConsultantId),
new SqlParameter("@UserId", filters.UserId),
new SqlParameter("@ClientId", filters.ClientId ),
new SqlParameter("@ContactStatuses", filters.ContactStatusIds),
new SqlParameter("@ClientStatuses", filters.ClientStatusIds),
new SqlParameter("@Client", filters.Client),
new SqlParameter("@Forename", filters.Forename),
new SqlParameter("@Surname", filters.Surname),
new SqlParameter("@Email", filters.Email),
new SqlParameter("@JobTitle", filters.JobTitle),
new SqlParameter("@Employer", filters.Employer),
new SqlParameter("@Sectors", filters.Sectors),
new SqlParameter("@Locations", filters.Locations),
new SqlParameter("@Qualifications", filters.Qualifications),
new SqlParameter("@Skills", filters.Skills),
new SqlParameter("@MinSalary", filters.MinSalary),
new SqlParameter("@MaxSalary", filters.MaxSalary),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@SortBy", filters.SortBy),
new SqlParameter("@Deleted" ,filters.Deleted),
new SqlParameter("@LastUpdatedDateFrom" ,filters.LastUpdatedDateFrom),
new SqlParameter("@LastUpdatedDateTo", filters.LastUpdatedDateTo),
new SqlParameter("@VacancyType" ,filters.VacancyType),
new SqlParameter("@Hours" ,filters.Hours),
new SqlParameter("@GroupId" ,filters.GroupId),
new SqlParameter("@LocationRadius" ,filters.LocationRadius),
new SqlParameter("@RetrieveType",filters.RetrieveType)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
//execute th stored procedure
conn.GetDataSetFromSP("AddClientsContactsToGroupsByFilter", parameters);
}
return gr.Group.GroupId;
}
/// <summary>
/// This function returns the filterd histories
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<History> GetHistories(HistoryFilter filters)
{
var histories = new List<History>();
// setup the parameters
SqlParameter[] parameters =
{
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@ClientUserId", filters.UserId),
new SqlParameter("@CandidateId", filters.ContactId),
new SqlParameter("@ClientId", filters.ClientId),
new SqlParameter("@MostRecent", filters.MostRecent),
new SqlParameter("@TypeIds", string.IsNullOrEmpty(filters.TypeIds) ? "" : filters.TypeIds.Trim().TrimEnd(Convert.ToChar(",")))
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveHistories", parameters);
// iterate and return histories
if (ds != null && ds.Tables.Count > 0)
{
histories = (from DataRow dr in ds.Tables[0].Rows
select ExtractHistoryItem(dr, filters.ClientId > 0 || filters.ContactId > 0)).ToList();
}
return histories;
}
/// <summary>
/// This function returns the events
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public IEnumerable<Event> GetEvents(EventFilter filter)
{
// public string MainType { get; set; }
//public string Keyword { get; set; }
//public bool? Completed { get; set; }
var events = new List<Event>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filter.RecordsPerPage ),
new SqlParameter("@PageNo", filter.CurrentPage),
new SqlParameter("@DateFrom", string.IsNullOrEmpty(filter.DateFrom) ? "" : new Utils().GetValidDate(filter.DateFrom) ),
new SqlParameter("@DateTo ", string.IsNullOrEmpty(filter.DateTo) ? "" : new Utils().GetValidDate(filter.DateTo) ),
new SqlParameter("@UserIds", string.IsNullOrEmpty(filter.UserIds) ? "" : filter.UserIds.Trim().TrimEnd(Convert.ToChar(","))),
new SqlParameter("@AllDay", filter.AllDay ),
new SqlParameter("@SortBy", filter.SortBy),
new SqlParameter("@ClientId", filter.ClientId),
new SqlParameter("@VacancyId", filter.VacancyId) ,
new SqlParameter("@CreatedBy", filter.CreatedBy),
new SqlParameter("@Type", filter.Type) ,
new SqlParameter("@MainType", filter.MainType) ,
new SqlParameter("@Keyword", filter.Keyword) ,
new SqlParameter("@Completed", filter.Completed)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveEvents", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
events = (from DataRow dr in ds.Tables[0].Rows
select new Event
{
EventId = (int)dr["EventId"],
MainType = (dr["MainType"] ?? "").ToString(),
Title = (dr["Title"] ?? "").ToString(),
Location = (dr["Location"] ?? "").ToString(),
Description = (dr["Description"] ?? "").ToString(),
StartDate = new Utils().GetUkDate((DateTime)dr["StartDate"]),
EndDate = new Utils().GetUkDate((DateTime)dr["EndDate"]),
StartTime = ((DateTime)dr["StartDate"]).ToString("HH:mm"),
EndTime = ((DateTime)dr["EndDate"]).ToString("HH:mm"),
IsDayEvent = (bool)dr["AllDayEvent"],
UserId = (int)dr["UserId"],
Username = (dr["Username"] ?? "").ToString(),
CreatedBy = (int)(dr["CreatedBy"]),
CreatedDate = (DateTime)(dr["CreatedDate"]),
RecurrenceId = (int)(dr["RecurrenceId"]),
Completed = (bool)dr["Completed"],
Locked = ((bool)dr["Locked"]),
EventType = new EventType { EventTypeId = (int)dr["EventTypeId"], EventTypeText = (dr["EventType"] ?? "").ToString() },
Vacancy = (int)dr["VacancyId"] > 0 ? (new EventTag { id = (int)dr["VacancyId"], name = (dr["JobTitle"] ?? "").ToString() }) : null,
Client = (int)dr["ClientId"] > 0 ? (new EventTag { id = (int)dr["ClientId"], name = (dr["Client"] ?? "").ToString() }) : null,
ClientContact = (int)dr["ClientContactId"] > 0 ? (new EventTag { id = (int)dr["ClientContactId"], name = (dr["ClientContactName"] ?? "").ToString() }) : null,
RecordCount = (int)dr["RECORDCOUNT"]
}).ToList();
}
return events;
}
/// <summary>
/// This function gets the calendar items
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public IEnumerable<CalendarEvent> GetCalendarEvents(CalendarFilter filter)
{
var events = new List<CalendarEvent>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@DateFrom", string.IsNullOrEmpty(filter.DateFrom) ? "" : new Utils().GetValidDate(filter.DateFrom) ),
new SqlParameter("@DateTo ", string.IsNullOrEmpty(filter.DateTo) ? "" : new Utils().GetValidDate(filter.DateTo) ),
new SqlParameter("@UserIds", string.IsNullOrEmpty(filter.UserIds) ? "" : filter.UserIds.Trim().TrimEnd(Convert.ToChar(","))),
new SqlParameter("@EventIds", string.IsNullOrEmpty(filter.EventIds) ? "" : filter.EventIds.Trim().TrimEnd(Convert.ToChar(","))),
new SqlParameter("@AllDay", filter.AllDay ),
new SqlParameter("@ClientId", filter.ClientId),
new SqlParameter("@VacancyId", filter.VacancyId)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveCalendarEvents", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
events = (from DataRow dr in ds.Tables[0].Rows
select new CalendarEvent
{
EndDate = ((DateTime)dr["EndDate"]).ToString("s"),
StartDate = ((DateTime)dr["StartDate"]).ToString("s"),
Title = (dr["Title"] ?? "").ToString(),
Location = (dr["Location"] ?? "").ToString(),
EventId = (int)dr["EventId"],
RecurrenceId = (int)(dr["RecurrenceId"]),
IsDayEvent = (bool)dr["AllDayEvent"],
UserId = (int)dr["UserId"],
Locked = ((bool)dr["Locked"]),
CalendarColor = (dr["CalendarColor"] ?? "").ToString()
}).ToList();
}
if (string.IsNullOrEmpty(filter.EventIds))
{
var userIds = string.IsNullOrEmpty(filter.UserIds) ? "" : filter.UserIds.Trim().TrimEnd(Convert.ToChar(","));
//get calendar events
var calEvents = GetOutlookEvents(userIds, Convert.ToDateTime(filter.DateFrom), Convert.ToDateTime(filter.DateTo));
events.AddRange(calEvents);
//get interviews
var interviews = new Interviews().GetCalendarInterviews(userIds, Convert.ToDateTime(filter.DateFrom), Convert.ToDateTime(filter.DateTo));
events.AddRange(interviews);
}
return events;
}
/// <summary>
/// Get calendar interview for the users
/// </summary>
/// <param name="userIds"></param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
public List<CalendarEvent> GetCalendarInterviews(string userIds, DateTime startDateTime, DateTime endDateTime)
{
var events = new List<CalendarEvent>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@PageNo", 1),
new SqlParameter("@Start", startDateTime.ToString("s")),
new SqlParameter("@End", endDateTime.ToString("s")),
new SqlParameter("@UserIds",userIds != null ? userIds.Trim().TrimEnd(Convert.ToChar(",")) :"")
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveCalendarInterviews", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
events = (from DataRow dr in ds.Tables[0].Rows
select new CalendarEvent
{
IsOutlook = false,
EndDate = GetDisplayDate((DateTime)dr["EndDate"]),
StartDate = GetDisplayDate((DateTime)dr["InterviewDate"]),
Title = (dr["Title"] ?? "").ToString(),
Location = (dr["Location"] ?? "").ToString(),
EventId = 0,
RecurrenceId = 0,
Locked = true,
IsDayEvent = false,
UserId = (int)dr["InterviewerId"],
InterviewTypeId = (int)dr["InterviewType"],
JobId = (int)dr["VacancyId"],
CalendarColor = (dr["CalendarColor"] ?? "").ToString(),
}).ToList();
}
return events;
}
/// <summary>
/// This function returns the jobs for the given criteria
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<Job> GetJobs(JobFilter filters)
{
var jobs = new List<Job>();
// setup the parameters
SqlParameter[] parameters =
{
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@ClientId", filters.ClientId),
new SqlParameter("@ManagerId", filters.ManagerId),
new SqlParameter("@JobTitle", filters.JobTitle),
new SqlParameter("@Sectors", filters.Sectors),
new SqlParameter("@Locations", filters.Locations),
new SqlParameter("@MinSalary", filters.MinSalary),
new SqlParameter("@MaxSalary", filters.MaxSalary),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@JobIdsIn", filters.JobIdsIn),
new SqlParameter("@JobUsers", filters.JobUsers),
new SqlParameter("@Published", filters.Published),
new SqlParameter("@Archived", filters.Archived),
new SqlParameter("@Deleted", filters.Deleted),
new SqlParameter("@SortBy", filters.SortBy)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveJobs", parameters);
// iterate and return jobs
if (ds != null && ds.Tables.Count > 0)
{
jobs = (from DataRow dr in ds.Tables[0].Rows
select new Job
{
RecordCount = (int)dr["RECORDCOUNT"],
JobId = (int)dr["JobId"],
ClientId = (int)dr["ClientId"],
ClientName = (dr["Client"] ?? "").ToString(),
JobTitle = (dr["JobTitle"] ?? "").ToString(),
Ref = (dr["Ref"] ?? "").ToString(),
JobLocation = (dr["JobLocation"] ?? "").ToString(),
Published = (bool)dr["Published"],
Archived = (bool)dr["Archived"],
Deleted = (bool)dr["Deleted"],
ExpiryDate = dr["ExpiryDate"] is DBNull ? null : (DateTime?)dr["ExpiryDate"],
CreatedDate = (DateTime)dr["CreatedDate"],
LastUpdatedDate = (DateTime?)dr["LastUpdatedDate"],
RefId = (dr["RefId"] ?? "").ToString(),
StartDate = dr["StartDate"] is DBNull ? null : (DateTime?)dr["StartDate"],
ApplicationCount = (int)dr["ApplicationCount"]
}).ToList();
}
return jobs;
}
/// <summary>
/// This function returns the clients for give filters. Uses paging and a stored procedure
/// </summary>
/// <param name="filters"></param>
/// <returns></returns>
public IEnumerable<Client> GetClients(ClientFilter filters)
{
var clients = new List<Client>();
// setup the parameters
SqlParameter[] parameters = {
new SqlParameter("@RecordsPerPage", filters.RecordsPerPage),
new SqlParameter("@PageNo", filters.CurrentPage),
new SqlParameter("@Client", filters.Client),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@Keyword", filters.Keyword),
new SqlParameter("@ClientStatusId", filters.ClientStatusId),
new SqlParameter("@ConsultantId", filters.ConsultantId),
new SqlParameter("@UserId", filters.UserId),
new SqlParameter("@ClientIdsIn", filters.ClientIdsIn),
new SqlParameter("@SortBy", filters.SortBy)
};
// setup the connection
var conn = new Sql_DataAccess(_connString);
var ds = conn.GetDataSetFromSP("RetrieveClients", parameters);
// iterate and return clients
if (ds != null && ds.Tables.Count > 0)
{
clients = (from DataRow dr in ds.Tables[0].Rows
select new Client
{
TotalCount = (int)dr["RECORDCOUNT"],
ClientId = (int)dr["ClientId"],
ClientName = (dr["Client"] ?? "").ToString(),
ConsultantId = (int)dr["ConsultantId"],
UserId = (int)dr["UserId"],
ParentClientId = (int)dr["ParentClientId"],
ClientStatus = new ClientStatus { ClientStatusId = (int)dr["ClientStatusId"], ClientStatusText = (dr["ClientStatus"] ?? "").ToString() },
Email = (dr["Email"] ?? "").ToString(),
Telephone = (dr["Telephone"] ?? "").ToString(),
Fax = (dr["Fax"] ?? "").ToString(),
Website = (dr["Website"] ?? "").ToString(),
Deleted = (bool)dr["Deleted"],
CreatedDate = (DateTime)dr["CreatedDate"],
LastUpdatedDate = (DateTime)dr["LastUpdatedDate"],
RefId = (dr["RefId"] ?? "").ToString()
}).ToList();
}
return clients;
}
