public static List <Interview> GetInterviews(int id)
{
SqlConnection conn = DB.DbReadOnlyConnect();
conn.Open();
string queryString = "SELECT * FROM CRM_CandidateInterviews WHERE candidateId = @id ORDER BY interviewDate DESC";
SqlCommand getInterviews = new SqlCommand(queryString, conn);
getInterviews.Parameters.AddWithValue("id", id);
SqlDataReader interviewReader = getInterviews.ExecuteReader();
List <Interview> m_Interviews = new List <Interview>();
while (interviewReader.Read())
{
Interview m_Interview = new Interview();
m_Interview.Id = interviewReader.GetInt32(0);
m_Interview.InterviewDate = interviewReader.GetDateTime(1);
m_Interview.JobId = interviewReader.GetInt32(2);
m_Interview.CandidateId = interviewReader.GetInt32(3);
m_Interview.InterviewType = interviewReader.GetString(4);
JobOrder m_Job = DBJobOrder.RetrieveOne(m_Interview.JobId);
m_Interview.JobName = m_Job.PositionTitle;
m_Interviews.Add(m_Interview);
}
conn.Close();
return(m_Interviews);
}
public static Offer RetrieveOne(int id)
{
SqlConnection conn = DB.DbReadOnlyConnect();
conn.Open();
string queryString = "SELECT * FROM CRM_Offers WHERE id = @id";
SqlCommand getOffer = new SqlCommand(queryString, conn);
getOffer.Parameters.AddWithValue("id", id);
SqlDataReader offerReader = getOffer.ExecuteReader();
Offer m_Offer = new Offer();
if (offerReader.Read())
{
m_Offer.Id = offerReader.GetInt32(0);
m_Offer.OfferDate = offerReader.GetDateTime(1);
m_Offer.JobId = offerReader.GetInt32(2);
m_Offer.OfferText = offerReader.GetString(3);
m_Offer.Accepted = offerReader.GetByte(4);
m_Offer.StartDate = offerReader.GetDateTime(5);
m_Offer.CandidateId = offerReader.GetInt32(6);
Candidate m_Cand = DBCandidate.RetrieveOne(m_Offer.CandidateId);
m_Offer.CandidateName = m_Cand.LastName + ", " + m_Cand.FirstName;
JobOrder m_Job = DBJobOrder.RetrieveOne(m_Offer.JobId);
m_Offer.JobName = m_Job.PositionTitle;
}
conn.Close();
return(m_Offer);
}
public static List <Resume> GetResumesSent(int id)
{
SqlConnection conn = DB.DbReadOnlyConnect();
conn.Open();
string queryString = "SELECT * FROM CRM_ResumesSent WHERE candidateId = @id ORDER BY sentDate DESC";
SqlCommand getResumesSent = new SqlCommand(queryString, conn);
getResumesSent.Parameters.AddWithValue("id", id);
SqlDataReader resumeReader = getResumesSent.ExecuteReader();
List <Resume> m_Resumes = new List <Resume>();
while (resumeReader.Read())
{
Resume m_Resume = new Resume();
m_Resume.Id = resumeReader.GetInt32(0);
m_Resume.CandidateId = resumeReader.GetInt32(1);
m_Resume.JobId = resumeReader.GetInt32(2);
m_Resume.DateSent = resumeReader.GetDateTime(3);
JobOrder m_Job = DBJobOrder.RetrieveOne(m_Resume.JobId);
m_Resume.JobName = m_Job.PositionTitle;
m_Resumes.Add(m_Resume);
}
conn.Close();
return(m_Resumes);
}
public static Interview RetrieveOne(int id)
{
SqlConnection conn = DB.DbReadOnlyConnect();
conn.Open();
string queryString = "SELECT * FROM CRM_CandidateInterviews WHERE id = @id";
SqlCommand getInt = new SqlCommand(queryString, conn);
getInt.Parameters.AddWithValue("id", id);
SqlDataReader intReader = getInt.ExecuteReader();
Interview m_Interview = new Interview();
if (intReader.Read())
{
m_Interview.Id = intReader.GetInt32(0);
m_Interview.InterviewDate = intReader.GetDateTime(1);
m_Interview.JobId = intReader.GetInt32(2);
m_Interview.CandidateId = intReader.GetInt32(3);
m_Interview.InterviewType = intReader.GetString(4);
JobOrder m_JobOrder = DBJobOrder.RetrieveOne(m_Interview.JobId);
m_Interview.JobName = m_JobOrder.PositionTitle;
Candidate m_Candidate = DBCandidate.RetrieveOne(m_Interview.CandidateId);
m_Interview.CandidateName = m_Candidate.LastName + ", " + m_Candidate.FirstName;
}
conn.Close();
return(m_Interview);
}