public int delete()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("DELETE FROM Income WHERE income_id = @id");
handler.addParameter("@id", this.id.ToString());
return handler.ExecuteNonQuery();
}
public static int delete(int id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("DELETE FROM [Position] WHERE position_id = @id");
handler.addParameter("@id", id.ToString());
return handler.ExecuteNonQuery();
}
public static int create(string department_name)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO [Department] (department_name) VALUES (@name)");
handler.addParameter("@name", department_name);
return handler.ExecuteNonQuery();
}
public static int create(string position_name, int department_id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO [Position] (position_name,position_department) VALUES (@name,@id)");
handler.addParameter("@name", position_name);
handler.addParameter("@id", department_id.ToString());
return handler.ExecuteNonQuery();
}
public static int create(int employee_id, string type, string per, float amount)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO Income (employee, income_type, income_amount, income_per) VALUES (@employee_id,@type,@amount,@per)");
handler.addParameter("@employee_id", employee_id.ToString());
handler.addParameter("@type", type);
handler.addParameter("@amount", amount.ToString());
handler.addParameter("@per", per);
return handler.ExecuteNonQuery();
}
public static int create(int employee_id, string name, string path, int size, string note)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO [Document] (employee, document_name, document_path, document_size, document_note) VALUES (@employee_id,@name,@path,@size,@note)");
handler.addParameter("@employee_id",employee_id.ToString());
handler.addParameter("@name",name);
handler.addParameter("@path",path);
handler.addParameter("@size",size.ToString());
handler.addParameter("@note",note);
return handler.ExecuteNonQuery();
}
public int getCountOfEmployees()
{
clartItems();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT count(employee_id) as Expr1 FROM Employee");
handler.queryExecute();
while (handler.reader.Read())
{
int count = int.Parse(handler.reader["Expr1"].ToString());
return count;
}
return 0;
}
/// <summary>
/// This method will return an object of class user if the user found. Otherwise it will return null
/// </summary>
/// <param name="username">The username of the user</param>
/// <param name="password">The password of the user</param>
/// <returns></returns>
public static HRUser authenticate(string username, string password)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM [User] WHERE user_name = @name AND user_password = @password");
handler.addParameter("@name", username);
handler.addParameter("@password", password);
handler.queryExecute();
while (handler.reader.Read()) {
HRUser u = new HRUser(int.Parse(handler.reader["user_id"].ToString()));
return u;
}
return null;
}
public ArrayList getALL()
{
clartItems();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT employee_id AS Expr1, employee_lastName FROM Employee WHERE (employee_working_status = 1) ORDER BY employee_lastName");
handler.queryExecute();
while (handler.reader.Read()) {
int employee_id = int.Parse(handler.reader["Expr1"].ToString());
Employee e = new Employee();
e.findById(employee_id);
items.Add(e);
}
return items;
}
public ArrayList getApproved()
{
clartItems();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT employee_id FROM Employee WHERE (employee_approved = @status) ORDER BY employee_lastName");
handler.addParameter("@status", "TRUE");
handler.queryExecute();
while (handler.reader.Read())
{
int employee_id = int.Parse(handler.reader["employee_id"].ToString());
Employee e = new Employee();
e.findById(employee_id);
items.Add(e);
}
return items;
}
public static float getALLMontly()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT SUM(income_amount) as total FROM Income WHERE income_per = @m");
handler.addParameter("@m", "Monthly");
handler.queryExecute();
try
{
while (handler.reader.Read())
{
return float.Parse(handler.reader["total"].ToString());
}
}
catch (Exception ex) {
return 0;
}
return 0;
}
public static ArrayList getALL()
{
ArrayList departments = new ArrayList();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM [Department] ORDER BY department_name");
handler.queryExecute();
while (handler.reader.Read()) {
string department_name = handler.reader["department_name"].ToString();
int department_id = int.Parse(handler.reader["department_id"].ToString());
Department d = new Department(department_id,department_name);
departments.Add(d);
}
return departments;
}
public int getPositionsCount()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT COUNT(position_id) AS countpositions FROM Position WHERE (position_department = @id)");
handler.addParameter("@id", this.id.ToString());
handler.queryExecute();
int count = 0;
while (handler.reader.Read())
{
count = int.Parse(handler.reader["countpositions"].ToString());
}
return count;
}
public bool updatePassword(string oldPassword, string newPassword)
{
if (this.password == oldPassword)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE [User] SET user_password = @pass WHERE user_id = @id");
handler.addParameter("@pass", newPassword);
handler.addParameter("@id", id.ToString());
return (handler.ExecuteNonQuery() == 1);
}
else {
return false;
}
}
/// <summary>
/// Getting the user information from the database
/// </summary>
private void init()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM [User] WHERE user_id = @id");
handler.addParameter("@id", id.ToString());
handler.queryExecute();
while (handler.reader.Read()) {
username = handler.reader["user_name"].ToString();
password = handler.reader["user_password"].ToString();
fullName = handler.reader["user_full_name"].ToString();
email = handler.reader["user_email"].ToString();
role = int.Parse(handler.reader["user_role"].ToString());
}
}
/// <summary>
/// This will return an arraylist that contains the name and id and the department for each
/// </summary>
/// <returns></returns>
public static ArrayList getALL()
{
ArrayList list = new ArrayList();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Position ORDER BY position_department");
handler.queryExecute();
while (handler.reader.Read()) {
int id = int.Parse(handler.reader["position_id"].ToString());
string name = handler.reader["position_name"].ToString();
int department_id = int.Parse(handler.reader["position_department"].ToString());
Position p = new Position(id,name,new Department(department_id));
list.Add(p);
}
return list;
}
/// <summary>
/// This method should create any new user. Return the number of rows affected.. Should be 1
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <param name="full_name"></param>
/// <param name="email"></param>
/// <returns></returns>
public static int create(string username, string password, string full_name, string email)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO [User](user_name, user_password, user_email, user_full_name) VALUES (@name,@pass,@email,@fullname)");
handler.addParameter("@name", username);
handler.addParameter("@pass", password);
handler.addParameter("@fullname", full_name);
handler.addParameter("@email", email);
return handler.ExecuteNonQuery();
}
public static int create(int employee_id, DateTime startDate, DateTime endDate, int paidDays, string type)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO Time_off (employee, time_off_end_date, time_off_start_date, time_off_paid_days, time_off_type, time_off_approved) VALUES (@employee_id,@endDate,@startDate,@paidDays,@type, 0)");
handler.addParameter("employee_id", employee_id.ToString());
handler.addParameter("endDate", endDate.Date.ToString());
handler.addParameter("startDate", startDate.Date.ToString());
handler.addParameter("paidDays", paidDays.ToString());
handler.addParameter("type", type);
return handler.ExecuteNonQuery();
}
private void init()
{
if (this.id != 0) {
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Position WHERE position_id = @id");
handler.addParameter("@id", this.id.ToString());
handler.queryExecute();
while (handler.reader.Read()) {
this.name = handler.reader["position_name"].ToString();
int dep_id = int.Parse(handler.reader["position_department"].ToString());
Department d = new Department(dep_id);
this.department = d;
}
}
}
public int getCountOfEmployees()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT COUNT(employee_id) AS countpositions FROM Employee WHERE (employee_position = @id)");
handler.addParameter("@id", this.id.ToString());
handler.queryExecute();
int count = 0;
while (handler.reader.Read())
{
count = int.Parse(handler.reader["countpositions"].ToString());
}
return count;
}
public static int update(int positionID, string newName, int newDepartmentID)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE [Position] SET position_name = @name, position_department = @department WHERE (position_id = @id)");
handler.addParameter("@id", positionID.ToString());
handler.addParameter("@name", newName);
handler.addParameter("@department", newDepartmentID.ToString());
return handler.ExecuteNonQuery();
}
/// <summary>
/// This will return an arraylist that contains the name and id and the department for each
/// </summary>
/// <returns></returns>
public static ArrayList getByDepartmentID(int department_id)
{
ArrayList list = new ArrayList();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Position WHERE position_department = @id");
handler.addParameter("@id", department_id.ToString());
handler.queryExecute();
while (handler.reader.Read())
{
int id = int.Parse(handler.reader["position_id"].ToString());
string name = handler.reader["position_name"].ToString();
Position p = new Position(id, name, new Department(department_id));
list.Add(p);
}
return list;
}
/// <summary>
/// Saving the information to the database (Except for the password)
/// </summary>
/// <returns></returns>
public bool save()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE [User] SET user_name = @u, user_email = @e, user_full_name = @f WHERE user_id = @id");
handler.addParameter("@u", username);
handler.addParameter("@e", email);
handler.addParameter("@f", fullName);
handler.addParameter("@id", id.ToString());
return (handler.ExecuteNonQuery() == 1);
}
/**
* Init
* */
public void init(int employee_id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Income WHERE employee = @employee_id");
handler.addParameter("@employee_id", employee_id.ToString());
handler.queryExecute();
while (handler.reader.Read())
{
int id = int.Parse(handler.reader["income_id"].ToString());
string type = handler.reader["income_type"].ToString();
string per = handler.reader["income_per"].ToString();
float amount = float.Parse(handler.reader["income_amount"].ToString());
Income income = new Income(id, type, per, amount);
items.Add(income);
}
}
private void init()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM [Department] WHERE department_id = @id");
handler.addParameter("@id",this.id.ToString());
handler.queryExecute();
while (handler.reader.Read()) {
this.name = handler.reader["department_name"].ToString();
}
}
public static int updateName(int department_id, string newName)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE [Department] SET department_name = @name WHERE department_id = @id");
handler.addParameter("@id", department_id.ToString());
handler.addParameter("@name", newName);
return handler.ExecuteNonQuery();
}
/*
* Record Creator
*
* @return int number of rows affected -- Should be 1 if everyting is ok
*/
public static int create(int employee_id, int number, DateTime issueDate, DateTime expireDate, string type, string note)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("INSERT INTO Record (employee, record_number, record_issue_date, record_expire_date, record_type, record_note, record_approved) VALUES (@employee,@number,@issue,@expire,@type,@note, 0)");
//(@employee,@number,@issue,@expire,@type,@note, 0)
handler.addParameter("@employee", employee_id.ToString());
handler.addParameter("@number", number.ToString());
handler.addParameter("@issue", issueDate.Date.ToString());
handler.addParameter("@expire", expireDate.Date.ToString());
handler.addParameter("@type", type);
handler.addParameter("@note", note);
return handler.ExecuteNonQuery();
}
/**
* Init
* This method will take care of getting all the Documents for an employee
*
* @pram int id
* @return none
* */
public void init(int employee_id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Document WHERE employee = @employee_id");
handler.addParameter("@employee_id", employee_id.ToString());
handler.queryExecute();
while (handler.reader.Read())
{
int id = int.Parse(handler.reader["document_id"].ToString());
string name = handler.reader["document_name"].ToString();
string path = handler.reader["document_path"].ToString();
string note = handler.reader["document_note"].ToString();
int size = int.Parse(handler.reader["document_size"].ToString());
Document d = new Document(id, name, path, note, size);
items.Add(d);
}
}
/**
* Init
* This method will take care of getting all the Time Offs for an employee
*
* @pram int id
* @return none
* */
public void init(int employee_id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Time_off WHERE employee = @employee_id");
handler.addParameter("@employee_id", employee_id.ToString());
handler.queryExecute();
while (handler.reader.Read())
{
int id = int.Parse(handler.reader["time_off_id"].ToString());
DateTime startDate = DateTime.Parse(handler.reader["time_off_start_date"].ToString());
DateTime endDate = DateTime.Parse(handler.reader["time_off_end_date"].ToString());
string type = handler.reader["time_off_type"].ToString();
int paidDays = int.Parse(handler.reader["time_off_paid_days"].ToString());
bool approved = (handler.reader["time_off_approved"].ToString() == "1");
TimeOff t = new TimeOff(id,startDate,endDate,paidDays,type,approved);
items.Add(t);
}
}
/**
* Init
* This method will take care of getting all the records for an employee
*
* @pram int id
* @return none
* */
public void init(int employee_id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("SELECT * FROM Record WHERE employee = @employee_id");
handler.addParameter("@employee_id", employee_id.ToString());
handler.queryExecute();
while (handler.reader.Read()) {
int id = int.Parse(handler.reader["record_id"].ToString());
int number = int.Parse(handler.reader["record_number"].ToString());
DateTime issueDate = DateTime.Parse(handler.reader["record_issue_date"].ToString());
DateTime expireDate = DateTime.Parse(handler.reader["record_expire_date"].ToString());
string type = handler.reader["record_type"].ToString();
string note = handler.reader["record_note"].ToString();
bool approved = (handler.reader["record_approved"].ToString() == "1");
Record r = new Record(id,number,issueDate,expireDate,type,note,approved);
items.Add(r);
}
}