/// <summary>
/// Select All Departments
/// </summary>
/// <returns></returns>
public DataTable GetAllDepartments()
{
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT d.DepartmentId,d.DepartmentName,d.Description, s.StaffName, t.DepartmentName from[Staff] s , [Department] d ,[Department] t where d.ParentDepartmentId=t.DepartmentId and d.DepartmentManagerId = s.StaffId";
DataTable dt = _dataAccess.ExecuteSql(sql);
return(dt);
}
}
/// <summary>
/// Select All records
/// </summary>
/// <returns></returns>
public List <LeaveDetails> GetAllLeaveDetails()
{
List <LeaveDetails> leaveDetailsList = new List <LeaveDetails>();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [LeaveDetails] ";
DataTable dt = _dataAccess.ExecuteSql(sql);
foreach (DataRow dr in dt.Rows)
{
LeaveDetails leaveDetails = new LeaveDetails()
{
LeaveDetailsId = Convert.ToInt32(dr[LeaveDetailsId]),
StaffId = Convert.ToInt32(dr[StaffId]),
LeaveTypeId = Convert.ToInt32(dr[LeaveTypeId]),
StartDate = Convert.ToDateTime(dr[StartDate]),
EndDate = Convert.ToDateTime(dr[EndDate]),
Duration = Convert.ToSingle(dr[Duration]),
Remark = dr[Remark].ToString()
};
leaveDetailsList.Add(leaveDetails);
}
}
return(leaveDetailsList);
}
/// <summary>
/// Select All records
/// </summary>
/// <returns></returns>
public List <Status> GetAllStatus()
{
List <Status> statusList = new List <Status>();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [Status] ";
DataTable dt = _dataAccess.ExecuteSql(sql);
foreach (DataRow dr in dt.Rows)
{
Status status = new Status()
{
StatusId = Convert.ToInt32(dr[StatusId]),
StatusName = dr[StatusName].ToString(),
};
statusList.Add(status);
}
}
return(statusList);
}
/// <summary>
/// Select All records
/// </summary>
/// <returns></returns>
public List <LeaveType> GetAllLeaveTypes()
{
List <LeaveType> leaveTypeList = new List <LeaveType>();
using (_dataAccess.Connection = new OleDbConnection(connectionString))
{
_dataAccess.Open();
string sql;
sql = "SELECT * FROM [LeaveType]";
DataTable dt = _dataAccess.ExecuteSql(sql);
foreach (DataRow dr in dt.Rows)
{
LeaveType leaveType = new LeaveType()
{
LeaveTypeId = Convert.ToInt32(dr[LeaveTypeId]),
LeaveTypeName = dr[LeaveTypeName].ToString(),
};
leaveTypeList.Add(leaveType);
}
}
return(leaveTypeList);
}
/// <summary>
/// Select All records
/// </summary>
/// <returns></returns>
public List <SystemConstant> GetAllSystemConstants()
{
List <SystemConstant> systemConstantList = new List <SystemConstant>();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [SystemConstant] ";
DataTable dt = _dataAccess.ExecuteSql(sql);
foreach (DataRow dr in dt.Rows)
{
SystemConstant SystemConstant = new SystemConstant()
{
SystemConstantId = Convert.ToInt32(dr[SystemConstantId]),
ConstantName = dr[ConstantName].ToString(),
ConstantValue = dr[ConstantValue].ToString(),
Description = dr[Description].ToString(),
};
systemConstantList.Add(SystemConstant);
}
}
return(systemConstantList);
}
/// <summary>
/// Select All records
/// </summary>
/// <returns></returns>
public List <User> GetAllUsers()
{
List <User> userList = new List <User>();
using (_dataAccess.Connection = new OleDbConnection(ConnectionString))
{
_dataAccess.Open();
string sql = "SELECT * FROM [User] ";
DataTable dt = _dataAccess.ExecuteSql(sql);
int a = dt.Columns.Count;
foreach (DataRow dr in dt.Rows)
{
User user = new User()
{
UserId = Convert.ToInt32(dr[userId]),
UserName = dr[userName].ToString(),
Password = dr[password].ToString()
};
userList.Add(user);
}
_dataAccess.Close();
}
return(userList);
}