public AdminModel.Usuario ExibirUsuarios(String SQLQuery)
{
AdminModel.Usuario pModel = new AdminModel.Usuario();
using (var conn = new MySqlConnection(PATH))
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(SQLQuery, conn);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow row in dt.Rows)
{
pModel.idUsuario = row["ID_USUARIO"].ToString();
pModel.Login = row["LOGIN"].ToString();
pModel.NomeUsuario = row["NOME_USUARIO"].ToString();
pModel.EmailUsuario = row["EMAIL_USUARIO"].ToString();
pModel.Grupo = row["GRUPO_NOME"].ToString();
pModel.Permissao = row["NOME_PERMISSAO"].ToString();
pModel.StatusUsuario = row["STATUS_USUARIO"].ToString();
pModel.Empresa = row["EMPRESA_NOME"].ToString();
}
}
return(pModel);
}
public void IncluirUsuario(AdminModel.Usuario pModel)
{
Repository db = new Repository();
pModel.Senha = RandomString(6);
String SQL = @"INSERT INTO USUARIOS (ID_USUARIO,
LOGIN,
SENHA,
NOME_USUARIO,
STATUS_USUARIO,
ID_GRUPO,
ID_EMPRESA,
ID_PERMISSAO,
EMAIL_USUARIO)
VALUES (( SELECT MAXID FROM (SELECT MAX(ID_USUARIO) + 1 AS MAXID FROM USUARIOS) AS T1),'" + //ID_USUARIO
pModel.Login + "','" + //LOGIN
pModel.Senha + "','" + //SENNHA
pModel.NomeUsuario + "','" + //NOME_USUARIO
pModel.StatusUsuario + "'," + //STATUS_USUARIO
"(SELECT ID FROM (SELECT ID_GRUPO AS ID FROM GRUPO_USUARIO WHERE GRUPO_NOME = '" + pModel.Grupo + "') AS TEMP)" + "," + //ID_GRUPO
"(SELECT ID FROM(SELECT ID_EMPRESA AS ID FROM EMPRESAS WHERE EMPRESA_NOME = '" + pModel.Empresa + "') AS TEMP1)" + "," + //ID_EMPRESA
"(SELECT ID FROM(SELECT ID_PERMISSAO AS ID FROM PERMISSOES_USUARIOS WHERE NOME_PERMISSAO = '" + pModel.Permissao + "') AS TEMP2)" + ",'" + //ID_PERMISSAO
pModel.EmailUsuario + "')"; //EMAIL_USUARIO
db.Inserir(SQL);
#region Envio de Email dados de Login
string remetenteEmail = "*****@*****.**";
MailMessage mail = new MailMessage();
mail.To.Add(pModel.EmailUsuario);
mail.From = new MailAddress(remetenteEmail, "SSM", System.Text.Encoding.UTF8);
mail.Subject = "SSM - Seu Novo Usuário";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = @"Olá! Seu novo usuário ao System Service Management foi criado. Seguem abaixo os dados de Login. <br />
Seu Login é: <b>" + pModel.Login + "</b> <br />" +
"Sua senha é: <b>" + pModel.Senha + "</b> <br />" +
"http://ssmenterprise.gear.host/";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true;
SmtpClient client = new SmtpClient();
client.Credentials = new System.Net.NetworkCredential(remetenteEmail, "est@ciotcc2");
client.Port = 587;
client.Host = "smtp.gmail.com";
client.EnableSsl = true;
try
{
client.Send(mail);
}
catch (Exception ex)
{
Console.WriteLine("Ocorreu um erro com envio do e-mail" + ex);
//throw new Exception("Ocorreu um erro com envio do e-mail" + ex);
}
#endregion
}
public AdminModel.Usuario ExibirUsuario(string nome)
{
Repository db = new Repository();
AdminModel.Usuario pModel = new AdminModel.Usuario();
string Sql = @"SELECT US.ID_USUARIO, US.LOGIN, US.NOME_USUARIO, US.EMAIL_USUARIO, EM.EMPRESA_NOME, GP.GRUPO_NOME, PU.NOME_PERMISSAO, US.STATUS_USUARIO FROM USUARIOS US
JOIN GRUPO_USUARIO GP ON US.ID_GRUPO = GP.ID_GRUPO
LEFT JOIN EMPRESAS EM ON EM.ID_EMPRESA = US.ID_EMPRESA
LEFT JOIN PERMISSOES_USUARIOS PU ON PU.ID_PERMISSAO = US.ID_PERMISSAO WHERE US.NOME_USUARIO ='" + nome + "'";
pModel = db.ExibirUsuarios(Sql);
return(pModel);
}
public void EditarUsuario(AdminModel.Usuario pModel)
{
Repository db = new Repository();
String SQL = @"UPDATE USUARIOS SET " +
"LOGIN = '******'," +
"NOME_USUARIO = '" + pModel.NomeUsuario + "'," +
"STATUS_USUARIO = '" + pModel.StatusUsuario + "'," +
"LOGIN = '******'," +
"ID_GRUPO = (SELECT ID FROM (SELECT ID_GRUPO AS ID FROM GRUPO_USUARIO WHERE GRUPO_NOME = '" + pModel.Grupo + "') AS TEMP)" + "," +
"ID_EMPRESA = (SELECT ID FROM (SELECT ID_EMPRESA AS ID FROM EMPRESAS WHERE EMPRESA_NOME = '" + pModel.Empresa + "') AS TEMP1)" + "," +
"ID_PERMISSAO = (SELECT ID FROM (SELECT ID_PERMISSAO AS ID FROM PERMISSOES_USUARIOS WHERE NOME_PERMISSAO = '" + pModel.Permissao + "') AS TEMP2)" + "," +
"EMAIL_USUARIO = '" + pModel.EmailUsuario + "' " +
" WHERE ID_USUARIO = " + pModel.idUsuario;
db.Update(SQL);
}