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 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();
}
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();
}
/**
* create
*
* IS A static method that will return 0 if the creations has not compleated or the ID of the new Employee if the employee was created Successfully
*/
public static int create(string SSN, string FirstName, char middleInitial, string lastName, DateTime dob, char gender, string phone, Job job, Address address)
{
DatabaseHandler handler = new DatabaseHandler();
string insertSt = "INSERT INTO Employee (employee_SSN, employee_firstName, employee_middleInital, employee_lastName, employee_dob, employee_working_status, employee_contract, employee_hoursPerDay, employee_firstDay, employee_gender, employee_position, employee_approved, employee_created_at, employee_address1, employee_address2, employee_city, employee_state, employee_zip_code, employee_phone) VALUES (@SSN,@firstName,@middleInitial,@lastName,@DOB,@workingStatus,@contract,@hoursPerDay,@firstDay,@gender,@position,@approved,@created_at,@address1,@address2,@city,@state,@zipCode,@phone)";
handler.setSQL(insertSt);
//Adding the employee information
handler.addParameter("@SSN", SSN);
handler.addParameter("@firstName", FirstName);
handler.addParameter("@middleInitial", middleInitial.ToString());
handler.addParameter("@lastName", lastName);
handler.addParameter("@DOB", dob.Date.ToString());
handler.addParameter("@gender", gender.ToString());
handler.addParameter("@approved", "0");
string created = DateTime.Now.ToString();
handler.addParameter("@created_at", created);
handler.addParameter("@phone", phone.ToString());
bool workingStatus = job.WorkingStatus;
string WorkingstatusToInt;
if(workingStatus)
WorkingstatusToInt = "1";
else
WorkingstatusToInt = "0";
// JOB
handler.addParameter("@workingStatus", WorkingstatusToInt);
handler.addParameter("@contract", job.Contract);
handler.addParameter("@hoursPerDay", job.HoursPerDay.ToString());
handler.addParameter("@firstDay", job.FirstDayAtWork.Date.ToString());
handler.addParameter("@position", job.Position.ID.ToString());
// ADDRESS
handler.addParameter("@address1", address.Address1);
handler.addParameter("@address2", address.Address2);
handler.addParameter("@city", address.City);
handler.addParameter("@state", address.State);
handler.addParameter("@zipCode", address.ZipCode.ToString());
int rows = handler.ExecuteNonQuery();
return rows;
}
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();
}
/// <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 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>
/// 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);
}
/**
* This method should save all the data
* @return the number of rows affected (IT SHOULD BE 1 OR 0)
*/
public int save()
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE Employee SET employee_SSN = @SSN, employee_firstName = @firstName, employee_middleInital = @middleInitial, employee_lastName = @lastName, employee_dob = @dob, employee_gender = @gender, employee_working_status = @workingStatus, employee_contract = @contract, employee_hoursPerDay = @hoursPerDay, employee_firstDay = @firstDay, employee_position = @position, employee_approved = @approved, employee_address1 = @address1, employee_address2 = @address2, employee_city = @city, employee_state = @state, employee_zip_code = @zipCode, employee_phone = @phone WHERE employee_id = @id");
handler.addParameter("@id", this.id.ToString());
// EMPLOYEE INFO
handler.addParameter("@SSN", this.SSN);
handler.addParameter("@firstName", this.firstName);
handler.addParameter("@middleInitial", middleInitial.ToString());
handler.addParameter("@lastName", this.lastName);
handler.addParameter("@dob", dob.Date.ToString());
handler.addParameter("@gender", this.gender.ToString());
handler.addParameter("@phone", this.phone);
int approvedStatus = 1;
if (this.approved == false)
approvedStatus = 0;
handler.addParameter("@approved", approvedStatus.ToString());
// JOB INFO
int workingStatus = 1;
if (Job.WorkingStatus == false)
workingStatus = 0;
handler.addParameter("@workingStatus", workingStatus.ToString());
handler.addParameter("@contract", Job.Contract);
handler.addParameter("@hoursPerDay ", Job.HoursPerDay.ToString());
handler.addParameter("@firstDay", Job.FirstDayAtWork.Date.ToString());
handler.addParameter("@position", Job.Position.ID.ToString());
// ADDRESS INFO
handler.addParameter("@address1", Address.Address1);
handler.addParameter("@address2", Address.Address2);
handler.addParameter("@state", Address.State);
handler.addParameter("@city", Address.City);
handler.addParameter("@zipCode", Address.ZipCode.ToString());
return handler.ExecuteNonQuery();
}
public int delete()
{
records.deleteALL();
timeOff.deleteALL();
documents.deleteALL();
income.deleteALL();
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("DELETE FROM Employee WHERE employee_id = @id");
handler.addParameter("@id", this.id.ToString());
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();
}
public static int approve(int id)
{
DatabaseHandler handler = new DatabaseHandler();
handler.setSQL("UPDATE Employee SET employee_approved = 1 WHERE employee_id = @id");
handler.addParameter("@id", id.ToString());
return handler.ExecuteNonQuery();
}
