private void LoadStaff()
{
StaffList.Clear();
var sqlData = new StaffQuery().SelectAllNotRetirement();
if (sqlData.HasRows)
{
while (sqlData.Read())
{
StaffDTO dto = new StaffDTO
{
STAFF_IDNUMBER = sqlData["idnumber"].ToString(),
STAFF_DEPT = sqlData["dept"].ToString(),
STAFF_RANK = sqlData["rank"].ToString(),
STAFF_NAME = sqlData["name"].ToString(),
STAFF_ADDRESS = sqlData["address"].ToString(),
STAFF_TEL = sqlData["tel"].ToString(),
STAFF_JOIN_DAY = sqlData["join_day"].ToString(),
STAFF_RETIREMENT_DAY = sqlData["retirement_day"].ToString(),
STAFF_STATE = sqlData["state"].ToString()
};
StaffList.Add(new StaffModel(dto));
}
sqlData.Close();
SharedPreference.Instance.DBM.SqlConn.Close();
}
else
{
sqlData.Close();
SharedPreference.Instance.DBM.SqlConn.Close();
}
}
public void Update(StaffDTO dto)
{
SharedPreference.Instance.DBM.SqlConn.Open();
string query = $"UPDATE tbl_staff SET dept = '{dto.STAFF_DEPT}', rank = '{dto.STAFF_RANK}', name = '{dto.STAFF_NAME}', address = '{dto.STAFF_ADDRESS}', tel = '{dto.STAFF_TEL}', join_day = '{dto.STAFF_JOIN_DAY}', retirement_day = '{dto.STAFF_RETIREMENT_DAY}', state = '{dto.STAFF_STATE}' WHERE idnumber = '{dto.STAFF_IDNUMBER}';";
SharedPreference.Instance.DBM.SqlComm.CommandText = query;
SharedPreference.Instance.DBM.SqlComm.ExecuteNonQuery();
SharedPreference.Instance.DBM.SqlConn.Close();
}
public void Insert(StaffDTO dto)
{
SharedPreference.Instance.DBM.SqlConn.Open();
string query = $"INSERT INTO tbl_staff (idnumber, dept, rank, name, address, tel, join_day, state) VALUES ('{dto.STAFF_IDNUMBER}', '{dto.STAFF_DEPT}', '{dto.STAFF_RANK}', '{dto.STAFF_NAME}', '{dto.STAFF_ADDRESS}', '{dto.STAFF_TEL}', '{dto.STAFF_JOIN_DAY}', '{dto.STAFF_STATE}');";
SharedPreference.Instance.DBM.SqlComm.CommandText = query;
SharedPreference.Instance.DBM.SqlComm.ExecuteNonQuery();
SharedPreference.Instance.DBM.SqlConn.Close();
}
private void LoadStaff()
{
SharedPreference.Instance.StaffList.Clear();
if (SharedPreference.Instance.SelectedDept != null)
{
SqlDataReader sqlData = null;
if (!SharedPreference.Instance.IsManagement)
{
sqlData = new StaffQuery().SelectWithDeptUser(SharedPreference.Instance.SelectedDept.DEPT_CODE);
}
else
{
sqlData = new StaffQuery().SelectWithDept(SharedPreference.Instance.SelectedDept.DEPT_CODE);
}
while (sqlData.Read())
{
var dto = new StaffDTO()
{
STAFF_IDNUMBER = sqlData["idnumber"].ToString(),
STAFF_DEPT = sqlData["dept"].ToString(),
STAFF_RANK = sqlData["rank"].ToString(),
STAFF_NAME = sqlData["name"].ToString(),
STAFF_ADDRESS = sqlData["address"].ToString(),
STAFF_TEL = sqlData["tel"].ToString(),
STAFF_JOIN_DAY = sqlData["join_day"].ToString(),
STAFF_RETIREMENT_DAY = sqlData["retirement_day"].ToString(),
STAFF_STATE = sqlData["state"].ToString()
};
SharedPreference.Instance.StaffList.Add(new StaffModel(dto));
}
var list = SharedPreference.Instance.StaffList.OrderBy(p => p.STAFF_IDNUMBER);
SharedPreference.Instance.StaffList = new ObservableCollection <StaffModel>(list);
sqlData.Close();
SharedPreference.Instance.DBM.SqlConn.Close();
}
}
public StaffModel(StaffDTO dto)
{
this.Dto = dto;
}