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