public MDUserLoginData LoginSystem(string username, string password)
{
MDUserLoginData oUser = new MDUserLoginData();
DataTable dTable = new DataTable();
SqlCommand sql = new SqlCommand();
sql.CommandText = @"SELECT username,password,firstname,lastname,empCode";
sql.CommandText += @",department,position,email,active,createby,createdate";
sql.CommandText += @",updateby,updatedate";
sql.CommandText += @" FROM userList";
sql.CommandText += @" WHERE username=@username AND password=@password";
sql.Parameters.Add(new SqlParameter("@username", username));
sql.Parameters.Add(new SqlParameter("@password", password));
dTable = oConn.Query(sql);
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
MDUserLoginData.CMDUserLoginData oMD = new MDUserLoginData.CMDUserLoginData();
oMD.Username = row["username"].ToString();
oMD.firstname = row["firstname"].ToString();
oMD.lastname = row["lastname"].ToString();
oMD.empCode = row["empCode"].ToString();
oMD.department = row["department"].ToString();
oMD.position = row["position"].ToString();
oMD.email = row["email"].ToString();
oMD.active = row["active"].ToString() == "1" ? "Active" : "Inactive";
oMD.createby = row["createby"].ToString();
oMD.createdate = row["createdate"].ToString();
oMD.updateby = row["updateby"].ToString();
oMD.updatedate = row["updatedate"].ToString();
// Insert Group
sql = new SqlCommand();
DataTable dTableGroup = new DataTable();
sql.CommandText = "SELECT uig_id,username,ug.ug_id,ug_name";
sql.CommandText += " FROM UserInGroup uig";
sql.CommandText += " LEFT JOIN UserGroupList ug ON uig.ug_id = ug.ug_id";
sql.CommandText += " WHERE username = @username";
sql.Parameters.Add(new SqlParameter("@username", username));
dTableGroup = oConn.Query(sql);
if (dTableGroup.Rows.Count > 0)
{
foreach (DataRow rowGroup in dTableGroup.Rows)
{
MDUserLoginData.CMDUserLoginData.CGroup oMDGroup = new MDUserLoginData.CMDUserLoginData.CGroup();
oMDGroup.GroupID = rowGroup["ug_id"] != DBNull.Value ? Convert.ToInt32(rowGroup["ug_id"]) : 0;
oMDGroup.GroupName = rowGroup["ug_name"].ToString();
oMD.ListOfGroup.Add(oMDGroup);
}
}
oUser.ListOfUser.Add(oMD);
}
}
return(oUser);
}
public MDUserLoginData GetUser(string inSearch)
{
inSearch = inSearch + "%";
MDUserLoginData oUser = new MDUserLoginData();
DataTable dTable = new DataTable();
SqlCommand sql = new SqlCommand();
sql.CommandText = @"SELECT username,password,firstname,lastname,empCode";
sql.CommandText += @",department,position,email,active,createby,createdate";
sql.CommandText += @",updateby,updatedate";
sql.CommandText += @" FROM userList";
sql.CommandText += @" WHERE ISNULL(username,'')+ISNULL(firstname,'')+ISNULL(lastname,'')";
sql.CommandText += @"+ISNULL(empCode,'') like @inSearch --AND username != 'admin'";
sql.Parameters.Add(new SqlParameter("@inSearch", inSearch));
dTable = oConn.Query(sql);
if (dTable.Rows.Count > 0)
{
foreach (DataRow row in dTable.Rows)
{
MDUserLoginData.CMDUserLoginData oMD = new MDUserLoginData.CMDUserLoginData();
oMD.Username = row["username"].ToString();
oMD.firstname = row["firstname"].ToString();
oMD.lastname = row["lastname"].ToString();
oMD.empCode = row["empCode"].ToString();
oMD.department = row["department"].ToString();
oMD.position = row["position"].ToString();
oMD.email = row["email"].ToString();
oMD.active = row["active"].ToString();
oMD.createby = row["createby"].ToString();
oMD.createdate = row["createdate"].ToString();
oMD.updateby = row["updateby"].ToString();
oMD.updatedate = row["updatedate"].ToString();
oUser.ListOfUser.Add(oMD);
}
}
return(oUser);
}
