/// <summary>
/// Select a Staff list by Staff id
/// </summary>
/// <param name="Staff"></param>
/// <returns></returns>
public Staff GetStaffByStaffId(Staff s)
{
Staff staff = new Staff();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [Staff] WHERE StaffId=@StaffId";
QueryParameter p = new QueryParameter("StaffId", s.StaffId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
staff.StaffId = Convert.ToInt32(dr[StaffId]);
staff.EmployeeId = dr[EmployeeId].ToString();
staff.StaffName = dr[StaffName].ToString();
staff.ChineseName = dr[ChineseName].ToString();
staff.Title = dr[Title].ToString();
staff.OnboardDate = Convert.ToDateTime(dr[OnboardDate]);
staff.Email = dr[Email].ToString();
staff.PhoneNumber = dr[PhoneNumber].ToString();
staff.Gender = Convert.ToInt32(dr[Gender]);
staff.Married = Convert.ToBoolean(dr[Married]);
staff.StatusId = Convert.ToInt32(dr[StatusId]);
staff.Location = dr[Location].ToString();
staff.EmployeeCategory = dr[EmployeeCategory].ToString();
staff.DepartmentId = Convert.ToInt32(dr[DepartmentId]);
staff.ContractTerm = dr[ContractTerm].ToString();
staff.LastyearRemains = Convert.ToSingle(dr[LastyearRemains]);
staff.StartWork = Convert.ToDateTime(dr[StartWork]);
}
CommonFunction commonFunction = new CommonFunction();
staff = commonFunction.ChangeLastyearRemain(staff);
}
return(staff);
}
public DataTable AllStaffsLeavingDetailsSelect()
{
//OleDbDataAccess dataAccess = new OleDbDataAccess();
dataAccess.Open();
string sql = "SELECT * FROM [LeavingDetails]";
return(dataAccess.GetTable(sql));;
}
/// <summary>
/// Select a Status list by Status id
/// </summary>
/// <param name="Status"></param>
/// <returns></returns>
public Status GetStatusByStatusId(Status s)
{
Status status = new Status();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [Status] WHERE StatusId=@StatusId";
QueryParameter p = new QueryParameter("StatusId", s.StatusId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
status.StatusId = Convert.ToInt32(dr[StatusId]);
status.StatusName = dr[StatusName].ToString();
}
}
return(status);
}
/// <summary>
/// Select a user list by user id
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public LeaveType GetLeaveTypeByLeaveTypeId(LeaveType lT)
{
LeaveType leaveType = new LeaveType();
using (_dataAccess.Connection = new OleDbConnection(connectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [LeaveType] WHERE [LeaveTypeId]=@LeaveTypeId";
QueryParameter p = new QueryParameter("LeaveTypeId", lT.LeaveTypeId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
leaveType.LeaveTypeId = Convert.ToInt32(dr[LeaveTypeId]);
leaveType.LeaveTypeName = dr[LeaveTypeName].ToString();
}
}
return(leaveType);
}
/// <summary>
/// Select a user list by user id
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public User GetUserByUserId(User u)
{
User user = new User();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [User] WHERE UserId=@UserId";
QueryParameter p = new QueryParameter("UserId", u.UserId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
user.UserId = Convert.ToInt32(dr[userId]);
user.UserName = dr[userName].ToString();
user.Password = dr[password].ToString();
}
}
return(user);
}
/// <summary>
/// Select a SystemConstant list by SystemConstant id
/// </summary>
/// <param name="SystemConstant"></param>
/// <returns></returns>
public SystemConstant GetSystemConstantBySystemConstantId(SystemConstant s)
{
SystemConstant systemConstant = new SystemConstant();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [SystemConstant] WHERE SystemConstantId=@SystemConstantId";
QueryParameter p = new QueryParameter("SystemConstantId", s.SystemConstantId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
systemConstant.SystemConstantId = Convert.ToInt32(dr[SystemConstantId]);
systemConstant.ConstantName = dr[ConstantName].ToString();
systemConstant.ConstantValue = dr[ConstantValue].ToString();
systemConstant.Description = dr[Description].ToString();
}
}
return(systemConstant);
}
/// <summary>
/// Select a LeaveDetails list by LeaveDetails id
/// </summary>
/// <param name="LeaveDetails"></param>
/// <returns></returns>
public LeaveDetails GetLeaveDetailsByLeaveDetailsId(LeaveDetails ld)
{
LeaveDetails leaveDetails = new LeaveDetails();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [LeaveDetails] WHERE LeaveDetailsId=@LeaveDetailsId";
QueryParameter p = new QueryParameter("LeaveDetailsId", ld.LeaveDetailsId, DbType.Int32);
DataTable dt = _dataAccess.GetTable(sql, p);
foreach (DataRow dr in dt.Rows)
{
leaveDetails.LeaveDetailsId = Convert.ToInt32(dr[LeaveDetailsId]);
leaveDetails.StaffId = Convert.ToInt32(dr[StaffId]);
leaveDetails.LeaveTypeId = Convert.ToInt32(dr[LeaveTypeId]);
leaveDetails.StartDate = Convert.ToDateTime(dr[StartDate]);
leaveDetails.EndDate = Convert.ToDateTime(dr[EndDate]);
leaveDetails.Duration = Convert.ToSingle(dr[Duration]);
leaveDetails.Remark = dr[Remark].ToString();
}
}
return(leaveDetails);
}
/// <summary>
/// Get managerId by manager name
/// </summary>
/// <param name="staffname"></param>
/// <returns></returns>
public int GetManagerId(string staffname)
{
_dataAccess.Open();
string sql = "SELECT StaffId FROM Staff where StaffName=@staffname";
QueryParameter p = new QueryParameter("@staffname", staffname, DbType.String);
DataTable dt = _dataAccess.GetTable(sql, p);
return(Convert.ToInt32(dt.Rows[0][0].ToString()));
}