/// <summary>
/// Returns List of Company Representatives based on Company Name
/// </summary>
/// <param name="companyName"></param>
/// <returns></returns>
public List <CompanyRepresentative> GetRepresentativeByCompanyID(int companyID)
{
CompanyRepresentative companyRepObj = null;
List <CompanyRepresentative> list = new List <CompanyRepresentative>();
string query = "select * from company_rep cr where cr.company_id = @cid";
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.Parameters.AddWithValue("@cid", companyID);
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
companyRepObj = new CompanyRepresentative();
companyRepObj.first_name = dr["first_name"].ToString();
companyRepObj.last_name = dr["last_name"].ToString();
companyRepObj.email = dr["email"].ToString();
companyRepObj.phone = Convert.ToInt32(dr["phone"]);
if (Convert.ToInt32(dr["isAlumni"]).Equals(1))
{
companyRepObj.isAlumni = true;
}
else
{
companyRepObj.isAlumni = false;
}
list.Add(companyRepObj);
}
return(list);
}
}
public List <Job> getJobs(string id)
{
Job jobObj = null;
List <Job> list = new List <Job>();
string query = "select j.* from Job j join company c on j.company_id = c.id where c.id = " + id;
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
MySqlCommand cmd = new MySqlCommand(query, cn);
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
jobObj = new Job();
jobObj.seeking_degree = dr["seeking_degree"].ToString();
jobObj.type = dr["type"].ToString();
jobObj.title = dr["title"].ToString();
jobObj.visa_sponsorship = dr["visa_sponsorhip"].ToString();
list.Add(jobObj);
}
return(list);
}
}
public List <Company> GetCompanies()
{
List <Company> list = new List <Company>();
Company company;
Booth booth;
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
var query = "select * from company c join booth b on c.id = b.company_id";
MySqlCommand cmd = new MySqlCommand(query, cn);
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
company = new Company();
booth = new Booth();
company.companyId = dr["id"].ToString();
company.companyName = dr["name"].ToString();
company.sector = dr["sector"].ToString();
company.headquarters = dr["headquarters"].ToString();
booth.boothID = dr["id"].ToString();
booth.boothNumber = dr["booth_number"].ToString();
booth.date = dr["day"].ToString();
booth.queueLength = dr["queue_length"].ToString();
company.boothList.Add(booth);
list.Add(company);
}
}
return(list);
}
public List <Int32> getReports()
{
List <Int32> list = new List <Int32>();
Int32 m = new Int32();
Int32 n = new Int32();
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
MySqlCommand cmd = new MySqlCommand("reportGeneration", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new MySqlParameter("companyId", 1));
cmd.Parameters["companyId"].Direction = ParameterDirection.Input;
cmd.Parameters.Add(new MySqlParameter("m", m));
cmd.Parameters["m"].Direction = ParameterDirection.Output;
cmd.Parameters.Add(new MySqlParameter("n", n));
cmd.Parameters["n"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
m = (Int32)cmd.Parameters["m"].Value;
n = (Int32)cmd.Parameters["n"].Value;
cmd.Connection.Close();
}
list.Add(m);
list.Add(n);
return(list);
}
/// <summary>
/// Checks if user with given password exists in the database
/// </summary>
/// <param name="_username">User name</param>
/// <param name="_password">User password</param>
/// <returns>True if user exist and password is correct</returns>
public static string IsValid(string _username, string _password)
{
DbConnSingleton db = DbConnSingleton.getDbInstance();
using (var conn = db.GetDBConnection())
{
string _sql = @"SELECT role FROM credentials " +
@"WHERE username = @u AND password = @p";
var cmd = new MySqlCommand(_sql, conn);
cmd.Parameters
.Add(new MySqlParameter("@u", MySqlDbType.String))
.Value = _username;
cmd.Parameters
.Add(new MySqlParameter("@p", MySqlDbType.String))
.Value = Helpers.LoginEncryption.Encode(_password);
string reader = cmd.ExecuteScalar().ToString();
if (!reader.Equals(null))
{
cmd.Dispose();
return(reader.ToString());
}
else
{
cmd.Dispose();
return(null);
}
}
}
public List <Job> GetJobs(string companyRepName)
{
List <Job> list = new List <Job>();
Job job;
UserDetailsInterface udInterface = new Helper();
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
// Need to change below query so that company_id becomes dynamic
var query = "select * from job where company_id = @cid";
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.Parameters.AddWithValue("@cid", udInterface.GetIDByUserName(companyRepName, "company_rep"));
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
job = new Job();
job.seeking_degree = dr["seeking_degree"].ToString();
job.type = dr["type"].ToString();
job.title = dr["title"].ToString();
job.visa_sponsorship = dr["visa_sponsorhip"].ToString();
list.Add(job);
}
}
return(list);
}
public void addJob(Job j)
{
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
var query = "insert into job(company_id,seeking_degree,type,title,visa_sponsorhip) values(" + j.company_id + ",'" + j.seeking_degree + "','" + j.type + "','" + j.title + "'," + j.visa_sponsorship + ")";
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.ExecuteNonQuery();
}
}
public StudentCompanyInformation AddNotes(string id, int sid, string additionalNotes = null)
{
StudentCompanyInformation info = new StudentCompanyInformation();
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
var query = "insert into student_company_info(student_id,company_id,interest,notes) values(@sid,@id, 1,@notes)";
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.Parameters.AddWithValue("@notes", additionalNotes.ToString());
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@sid", sid);
cmd.ExecuteNonQuery();
}
info = getStudentCompanyDetails(id, sid);
return(info);
}
public StudentCompanyInformation getStudentCompanyDetails(string companyid = null, int studentID = 1, string id = null)
{
Job job;
StudentCompanyInformation information = new StudentCompanyInformation();
var company_id = "";
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
string query = null;
if (companyid != null)
{
query = "select sc.notes,sc.interest, c.name from company c join student_company_info sc on c.id = sc.company_id join job j on c.id = j.company_id where student_id = @sid and c.id = " + companyid;
company_id = companyid;
}
else
{
query = "select sc.notes,sc.interest, c.name from company c join student_company_info sc on c.id = sc.company_id join job j on c.id = j.company_id where student_id = @sid and c.id = " + id;
company_id = id;
}
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.Parameters.AddWithValue("@sid", studentID);
MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
// information = new StudentCompanyInformation();
// job = new Job();
information.notes = dr["notes"].ToString();
information.isInterested = dr["interest"].ToString();
//job.seeking_degree = dr["seeking_degree"].ToString();
//job.type = dr["type"].ToString();
//job.visa_sponsorship = dr["visa_sponsorhip"].ToString();
//job.title = dr["title"].ToString();
information.companyName = dr["name"].ToString();
// information.jobs.Add(job);
}
}
information.jobs = getJobs(company_id); // jobs posted by company (if any)
return(information);
}
public StudentCompanyInformation editBoothQueue(string id, int studentID, bool inQueue)
{
StudentCompanyInformation info = new StudentCompanyInformation();
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
if (inQueue)
{
MySqlCommand cmd = new MySqlCommand("EditBoothQueue", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@companyid", id);
// cmd.Parameters.AddWithValue("@queueval", inQueue);
cmd.Parameters.AddWithValue("@studentID", studentID);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
info = getStudentCompanyDetails(id, studentID);
return(info);
}
/// <summary>
/// Registers the user into the system
/// </summary>
/// <param name="_username"></param>
/// <param name="_password"></param>
/// <param name="_email"></param>
/// <param name="_role"></param>
/// <param name="_uin"></param>
/// <param name="_firstname"></param>
/// <param name="_lastname"></param>
/// <param name="_major"></param>
/// <param name="_degree"></param>
/// <param name="_address"></param>
/// <param name="_phone"></param>
/// <returns></returns>
public static bool RegisterUser(string _username, string _password, string _email, string _role,
double _uin, string _firstname, string _lastname, string _major,
string _degree, string _address, double _phone)
{
DbConnSingleton db = DbConnSingleton.getDbInstance();
using (var conn = db.GetDBConnection())
{
string _sql = "spRegisterUser";
var cmd = new MySqlCommand(_sql, conn);
cmd.Parameters.AddWithValue("username", _username);
cmd.Parameters.AddWithValue("password", Helpers.LoginEncryption.Encode(_password));
cmd.Parameters.AddWithValue("emailid", _email);
cmd.Parameters.AddWithValue("role", _role);
cmd.Parameters.AddWithValue("uid", _uin);
cmd.Parameters.AddWithValue("fname", _firstname);
cmd.Parameters.AddWithValue("lname", _lastname);
cmd.Parameters.AddWithValue("major", _major);
cmd.Parameters.AddWithValue("degree", _degree);
cmd.Parameters.AddWithValue("address", _address);
cmd.Parameters.AddWithValue("phone", _phone);
cmd.CommandType = CommandType.StoredProcedure;
int reader = cmd.ExecuteNonQuery();
if (reader != -1)
{
cmd.Dispose();
return(true);
}
else
{
cmd.Dispose();
return(false);
}
}
}
public StudentCompanyInformation UpdateNotes(string id, int sid, string notes = null, string isInterested = null, bool inQueue = false)
{
// StudentCompanyDetails compdetails = new StudentCompanyDetails();
StudentCompanyInformation info = new StudentCompanyInformation();
using (var cn = DbConnSingleton.getDbInstance().GetDBConnection())
{
//var query = "insert into student_company_info(student_id,company_id,interest,notes) values(1,?companyID, ?isInterested, ?notes)";
var query = "Update student_company_info set notes = @notes where company_id = @id and student_id = @sid";
MySqlCommand cmd = new MySqlCommand(query, cn);
cmd.Parameters.AddWithValue("@notes", notes);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@sid", sid);
// cmd.Parameters.AddWithValue("?companyID", HttpUtility.ParseQueryString("id").ToString());
// cmd.Parameters.AddWithValue("?isInterested", 1);
// cmd.Parameters.AddWithValue("?notes", notes);
cmd.ExecuteNonQuery();
}
info = getStudentCompanyDetails(id, sid);
return(info);
}