/// <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); }