public Staff GetStaff(int id)
{
objConn = objDB.EstablishConnection();
Staff staffData = new Staff();
string strSQL = "SELECT *, CONCAT(s.StaffFirstname,' ', s.StaffLastname) AS NameStaff FROM staffs s INNER JOIN StaffRole sr ON sr.StaffRoleID = s.StaffRoleID INNER JOIN Provinces p ON p.ProvinceID = s.ProvinceID INNER JOIN Gender g ON g.GenderID = s.GenderID WHERE p.LangID = 1 AND s.Deleted = 0 AND StaffID = " + id + " ORDER BY StaffID;";
DataTable dt = objDB.List(strSQL, objConn);
objConn.Close();
staffData.StaffID = Convert.ToInt32(dt.Rows[0]["StaffID"].ToString());
staffData.StaffRoleName = dt.Rows[0]["StaffRoleName"].ToString();
staffData.StaffPassword = dt.Rows[0]["StaffPassword"].ToString();
staffData.StaffRoleID = Convert.ToInt32(dt.Rows[0]["StaffRoleID"].ToString());
staffData.StaffFirstname = dt.Rows[0]["StaffFirstname"].ToString();
staffData.StaffLastname = dt.Rows[0]["StaffLastname"].ToString();
staffData.StaffName = dt.Rows[0]["NameStaff"].ToString();
staffData.StaffCode = dt.Rows[0]["StaffCode"].ToString();
staffData.Gender = dt.Rows[0]["GenderName"].ToString();
staffData.GenderID = Convert.ToInt32(dt.Rows[0]["GenderID"].ToString());
staffData.Address1 = dt.Rows[0]["StaffAddress1"].ToString();
staffData.Address2 = dt.Rows[0]["StaffAddress2"].ToString();
staffData.City = dt.Rows[0]["StaffCity"].ToString();
staffData.Province = dt.Rows[0]["ProvinceName"].ToString();
staffData.ProvinceID = Convert.ToInt32(dt.Rows[0]["ProvinceID"].ToString());
staffData.Zipcode = dt.Rows[0]["StaffZipcode"].ToString();
staffData.Telephone = dt.Rows[0]["StaffTel"].ToString();
staffData.Mobile = dt.Rows[0]["StaffMobile"].ToString();
staffData.Picture = dt.Rows[0]["StaffPictureName"].ToString();
staffData.Email = dt.Rows[0]["StaffEmail"].ToString();
return staffData;
}
public Staff PostStaffImageAll(string imageName, int staffid)
{
objConn = objDB.EstablishConnection();
Staff staffdelete = new Staff();
string strSQL = "UPDATE staffs SET StaffPictureName = '" + imageName + "'";
//strSQL += "WHERE StaffID = '" + StaffID + "';";
strSQL += "WHERE StaffID = '" + staffid + "';";
objDB.sqlExecute(strSQL, objConn);
objConn.Close();
return staffdelete;
}
public IEnumerable<Staff> PostStaffDeleteAll(Staff item)
{
objConn = objDB.EstablishConnection();
List<Staff> staffdelete = new List<Staff>();
string strSQL = "UPDATE staffs SET Deleted = '" + item.Deleted + "'";
strSQL += "WHERE StaffID = '" + item.StaffID + "';";
objDB.sqlExecute(strSQL, objConn);
objConn.Close();
return staffdelete;
}
public IEnumerable<Staff> PostStaffEditAll(Staff item)
{
objConn = objDB.EstablishConnection();
List<Staff> staffedit = new List<Staff>();
string strSQL = "UPDATE staffs SET StaffRoleID = '" + item.StaffRoleID + "', StaffCode = '" + item.StaffCode + "', GenderID = '" + item.GenderID + "', StaffFirstname = '" + item.StaffFirstname + "', StaffLastname = '" + item.StaffLastname + "', StaffAddress1 = '" + item.Address1 + "', StaffAddress2 = '" + item.Address2 + "', StaffCity = '" + item.City.ToString() + "', StaffZipcode = '" + item.Zipcode + "', StaffTel = '" + item.Telephone + "', StaffMobile = '" + item.Mobile + "', StaffEmail = '" + item.Email + "', ProvinceID = '" + item.ProvinceID + "'";
strSQL += "WHERE StaffID = '" + item.StaffID + "';";
objDB.sqlExecute(strSQL, objConn);
objConn.Close();
return staffedit;
}
public Staff PostLoginAll(Staff item)
{
objConn = objDB.EstablishConnection();
Staff login = new Staff();
string strSQL = "SELECT *, CONCAT(s.StaffFirstname,' ', s.StaffLastname) AS NameStaff FROM staffs s INNER JOIN StaffRole sr ON sr.StaffRoleID = s.StaffRoleID INNER JOIN Provinces p ON p.ProvinceID = s.ProvinceID INNER JOIN Gender g ON g.GenderID = s.GenderID WHERE p.LangID = 1 AND s.Deleted = 0 AND StaffCode = '" + item.StaffCode + "' AND StaffPassword = '******' ORDER BY StaffCode; ";
DataTable dt = objDB.List(strSQL, objConn);
objConn.Close();
if (dt.Rows.Count > 0)
{
login.StaffID = Convert.ToInt32(dt.Rows[0]["StaffID"].ToString());
login.StaffRoleName = dt.Rows[0]["StaffRoleName"].ToString();
login.StaffPassword = dt.Rows[0]["StaffPassword"].ToString();
login.StaffRoleID = Convert.ToInt32(dt.Rows[0]["StaffRoleID"].ToString());
login.StaffFirstname = dt.Rows[0]["StaffFirstname"].ToString();
login.StaffLastname = dt.Rows[0]["StaffLastname"].ToString();
login.StaffName = dt.Rows[0]["NameStaff"].ToString();
login.StaffCode = dt.Rows[0]["StaffCode"].ToString();
login.Gender = dt.Rows[0]["GenderName"].ToString();
login.GenderID = Convert.ToInt32(dt.Rows[0]["GenderID"].ToString());
login.Address1 = dt.Rows[0]["StaffAddress1"].ToString();
login.Address2 = dt.Rows[0]["StaffAddress2"].ToString();
login.City = dt.Rows[0]["StaffCity"].ToString();
login.Province = dt.Rows[0]["ProvinceName"].ToString();
login.ProvinceID = Convert.ToInt32(dt.Rows[0]["ProvinceID"].ToString());
login.Zipcode = dt.Rows[0]["StaffZipcode"].ToString();
login.Telephone = dt.Rows[0]["StaffTel"].ToString();
login.Mobile = dt.Rows[0]["StaffMobile"].ToString();
login.Picture = dt.Rows[0]["StaffPictureName"].ToString();
login.Email = dt.Rows[0]["StaffEmail"].ToString();
login.status = "true";
}
else {
login.status = "false";
}
return login;
}
public IEnumerable<Staff> PostStaffAll(Staff item)
{
objConn = objDB.EstablishConnection();
List<Staff> staff = new List<Staff>();
int rowid;
string strSQL1 = "SELECT MAX(StaffID) AS rowid FROM staffs ;";
DataTable dt = objDB.List(strSQL1, objConn);
rowid = Convert.ToInt32(dt.Rows[0]["rowid"].ToString());
int maxid = rowid + 1;
string strSQL2 = "INSERT INTO staffs(StaffID, StaffCode, StaffPassword, StaffRoleID, GenderID, StaffFirstname, StaffLastname, StaffAddress1, StaffAddress2, StaffCity, StaffZipcode, StaffTel, StaffMobile, StaffPictureName, StaffEmail, ProvinceID) ";
//strSQL2 += "VALUES ('" + maxid + "','" + item.StaffCode + "','" + item.StaffPassword + "','" + item.StaffRoleID + "','" + item.GenderID + "','" + item.StaffFirstname + "','" + item.StaffLastname + "','" + item.Address1 + "','" + item.Address2 + "','" + item.City + "','" + item.Zipcode + "','" + item.Telephone + "','" + item.Mobile + "','" + item.Email + "','" + item.ProvinceID + "')";
strSQL2 += "VALUES ('" + maxid + "','" + item.StaffCode + "','" + item.StaffPassword + "','" + item.StaffRoleID + "','" + item.GenderID + "','" + item.StaffFirstname + "','" + item.StaffLastname + "','" + item.Address1 + "','" + item.Address2 + "','" + item.City + "','" + item.Zipcode + "','" + item.Telephone + "','" + item.Mobile + "','" + item.Picture + "','" + item.Email + "','" + item.ProvinceID + "')";
objDB.sqlExecute(strSQL2, objConn);
objConn.Close();
return staff.ToArray();
}
public IEnumerable<Staff> GetStaffAll()
{
objConn = objDB.EstablishConnection();
List<Staff> staff = new List<Staff>();
string strSQL = "SELECT *, CONCAT(s.StaffFirstname,' ', s.StaffLastname) AS NameStaff FROM staffs s INNER JOIN StaffRole sr ON sr.StaffRoleID = s.StaffRoleID INNER JOIN Provinces p ON p.ProvinceID = s.ProvinceID INNER JOIN Gender g ON g.GenderID = s.GenderID WHERE p.LangID = 1 AND s.Deleted = 0 ORDER BY StaffCode;";
DataTable dt = objDB.List(strSQL, objConn);
objConn.Close();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Staff staffData = new Staff();
staffData.StaffID = Convert.ToInt32(dt.Rows[i]["StaffID"].ToString());
staffData.StaffRoleName = dt.Rows[i]["StaffRoleName"].ToString();
staffData.StaffPassword = dt.Rows[i]["StaffPassword"].ToString();
staffData.StaffRoleID = Convert.ToInt32(dt.Rows[i]["StaffRoleID"].ToString());
staffData.StaffFirstname = dt.Rows[i]["StaffFirstname"].ToString();
staffData.StaffLastname = dt.Rows[i]["StaffLastname"].ToString();
staffData.StaffName = dt.Rows[i]["NameStaff"].ToString();
staffData.StaffCode = dt.Rows[i]["StaffCode"].ToString();
staffData.Gender = dt.Rows[i]["GenderName"].ToString();
staffData.GenderID = Convert.ToInt32(dt.Rows[i]["GenderID"].ToString());
staffData.Address1 = dt.Rows[i]["StaffAddress1"].ToString();
staffData.Address2 = dt.Rows[i]["StaffAddress2"].ToString();
staffData.City = dt.Rows[i]["StaffCity"].ToString();
staffData.Province = dt.Rows[i]["ProvinceName"].ToString();
staffData.ProvinceID = Convert.ToInt32(dt.Rows[i]["ProvinceID"].ToString());
staffData.Zipcode = dt.Rows[i]["StaffZipcode"].ToString();
staffData.Telephone = dt.Rows[i]["StaffTel"].ToString();
staffData.Mobile = dt.Rows[i]["StaffMobile"].ToString();
staffData.Picture = dt.Rows[i]["StaffPictureName"].ToString();
staffData.Email = dt.Rows[i]["StaffEmail"].ToString();
staff.Add(staffData);
}
}
return staff.ToArray();
}
public IEnumerable<Staff> PostEditPasswordStaffAll(Staff item)
{
objConn = objDB.EstablishConnection();
List<Staff> editpassword = new List<Staff>();
string strSQL = "UPDATE staffs SET StaffPassword = '******'";
strSQL += "WHERE StaffID = '" + item.StaffID + "';";
objDB.sqlExecute(strSQL, objConn);
objConn.Close();
return editpassword;
}
public IEnumerable<Staff> Post(Staff item)
{
return repository.PostStaffAll(item);
}
public Staff PostStaffIndex(Staff item)
{
return repository.PostStaffIndexAll(item);
}
public IEnumerable<Staff> PostStaffEdit(Staff item)
{
Console.WriteLine(item);
return repository.PostStaffEditAll(item);
}
public Staff PostLogin(Staff item)
{
return repository.PostLoginAll(item);
}
public IEnumerable<Staff> PostEditPasswordStaff(Staff item)
{
return repository.PostEditPasswordStaffAll(item);
}