public ClienteEntity Obter(int codCli)
{
ClienteEntity cliente = new ClienteEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Cliente.codCli, Cliente.cnpjEmp, Cliente.cnpjCli, nomEmp, nomCli, codVen, Vendedor.cpfUsu AS cpfVen, nomUsu AS nomVen FROM Cliente LEFT JOIN (SELECT DISTINCT cnpjEmp, nomEmp FROM Empresa) AS Empresa1 ON Empresa1.cnpjEmp = Cliente.cnpjEmp LEFT JOIN (SELECT DISTINCT cnpjEmp AS cnpjCli, nomEmp AS nomCli FROM Empresa) AS Empresa2 ON Empresa2.cnpjCli = Cliente.cnpjCli LEFT JOIN Vendedor ON Vendedor.codCli = Cliente.codCli LEFT JOIN Usuario ON Usuario.cpfUsu = Vendedor.cpfUsu WHERE Cliente.codCli = @codCli ORDER BY nomCli", connection);
command.Parameters.AddWithValue("@codCli", codCli);
IDataReader reader = command.ExecuteReader();
reader.Read();
cliente.codCli = ExtraDAO.ObterValor <int>(reader, 0, 0);
cliente.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 1, null);
cliente.cnpjCli = ExtraDAO.ObterValor <string>(reader, 2, null);
cliente.nomEmp = ExtraDAO.ObterValor <string>(reader, 3, null);
cliente.nomCli = ExtraDAO.ObterValor <string>(reader, 4, null);
cliente.codVen = ExtraDAO.ObterValor <int>(reader, 5, 0);
cliente.cpfVen = ExtraDAO.ObterValor <string>(reader, 6, null);
cliente.nomVen = ExtraDAO.ObterValor <string>(reader, 7, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(cliente);
}
public LembreteEntity Obter(int codLem)
{
LembreteEntity lembrete = new LembreteEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codLem, Lembrete.codPar, datLem, horLem, diaLem, semLem, cpfUsu, Participante.codAti, datAti, horAti FROM Lembrete LEFT JOIN Participante ON Lembrete.codPar = Participante.codPar LEFT JOIN Atividade ON Participante.codAti = Atividade.codAti WHERE codPar = @codPar ORDER BY datLem", connection);
command.Parameters.AddWithValue("@codLem", codLem);
IDataReader reader = command.ExecuteReader();
reader.Read();
lembrete.codLem = ExtraDAO.ObterValor <int>(reader, 0, 0);
lembrete.codPar = ExtraDAO.ObterValor <int>(reader, 1, 0);
lembrete.datLem = ExtraDAO.ObterValor <DateTime>(reader, 2, new DateTime()).ToShortDateString();
lembrete.horLem = ExtraDAO.ObterValor <TimeSpan>(reader, 3, new TimeSpan()).ToString().Substring(0, 5);
lembrete.diaLem = ExtraDAO.ObterValor <int>(reader, 4, 0);
lembrete.semLem = ExtraDAO.ObterValor <bool>(reader, 5, false);
lembrete.cpfUsu = ExtraDAO.ObterValor <string>(reader, 6, null);
lembrete.codAti = ExtraDAO.ObterValor <int>(reader, 7, 0);
lembrete.datAti = ExtraDAO.ObterValor <DateTime>(reader, 8, new DateTime()).ToShortDateString();
lembrete.horAti = ExtraDAO.ObterValor <TimeSpan>(reader, 9, new TimeSpan()).ToString().Substring(0, 5);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(lembrete);
}
public AtividadeEntity Obter(int codAti)
{
AtividadeEntity atividade = new AtividadeEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codAti, Atividade.codCli, desAti, tipAti, datAti, horAti, nomEmp AS nomCli FROM Atividade LEFT JOIN Cliente ON Atividade.codCli = Cliente.codCli LEFT JOIN Empresa ON Cliente.cnpjCli = Empresa.cnpjEmp WHERE Atividade.codAti = @codAti ORDER BY datAti", connection);
command.Parameters.AddWithValue("@codAti", codAti);
IDataReader reader = command.ExecuteReader();
reader.Read();
atividade.codAti = ExtraDAO.ObterValor <int>(reader, 0, 0);
atividade.codCli = ExtraDAO.ObterValor <int>(reader, 1, 0);
atividade.desAti = ExtraDAO.ObterValor <string>(reader, 2, null);
atividade.tipAti = ExtraDAO.ObterValor <string>(reader, 3, null);
atividade.datAti = ExtraDAO.ObterValor <DateTime>(reader, 4, new DateTime()).ToShortDateString();
atividade.horAti = ExtraDAO.ObterValor <TimeSpan>(reader, 5, new TimeSpan()).ToString().Substring(0, 5);
atividade.nomCli = ExtraDAO.ObterValor <string>(reader, 6, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(atividade);
}
public ContratoEntity ObterAtivo(string cpfUsu)
{
ContratoEntity contrato = new ContratoEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codCtr, Contrato.cpfUsu, Empresa.cnpjEmp, Contrato.codCar, iniCtr, fimCtr, nomUsu, nomEmp, nomCar FROM Contrato JOIN Cargo ON Contrato.codCar = Cargo.codCar JOIN Usuario ON Contrato.cpfUsu = Usuario.cpfUsu LEFT JOIN Empresa ON Contrato.cnpjEmp = Empresa.cnpjEmp WHERE Contrato.cpfUsu = @cpfUsu AND fimCtr IS NULL ORDER BY iniCtr", connection);
command.Parameters.AddWithValue("@cpfUsu", cpfUsu);
IDataReader reader = command.ExecuteReader();
reader.Read();
contrato.codCtr = ExtraDAO.ObterValor <int>(reader, 0, 0);
contrato.cpfUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
contrato.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 2, null);
contrato.codCar = ExtraDAO.ObterValor <int>(reader, 3, 0);
contrato.iniCtr = ExtraDAO.ObterValor <DateTime>(reader, 4, new DateTime()).ToShortDateString();
contrato.fimCtr = ExtraDAO.ObterValor <DateTime>(reader, 5, new DateTime()).ToShortDateString();
contrato.nomUsu = ExtraDAO.ObterValor <string>(reader, 6, null);
contrato.nomEmp = ExtraDAO.ObterValor <string>(reader, 7, null);
contrato.nomCar = ExtraDAO.ObterValor <string>(reader, 8, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(contrato);
}
public ParticipanteEntity Obter(string cpfUsu, int codAti)
{
ParticipanteEntity participante = new ParticipanteEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codPar, Participante.cpfUsu, Participante.codAti, nomUsu, codCli, datAti, horAti FROM Participante LEFT JOIN Usuario ON Participante.cpfUsu = Usuario.cpfUsu LEFT JOIN Atividade ON Participante.codAti = Atividade.codAti WHERE Participante.cpfUsu = @cpfUsu AND Participante.codAti = @codAti ORDER BY nomUsu", connection);
command.Parameters.AddWithValue("@cpfUsu", cpfUsu);
command.Parameters.AddWithValue("@codAti", codAti);
IDataReader reader = command.ExecuteReader();
reader.Read();
participante.codPar = ExtraDAO.ObterValor <int>(reader, 0, 0);
participante.cpfUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
participante.codAti = ExtraDAO.ObterValor <int>(reader, 2, 0);
participante.nomUsu = ExtraDAO.ObterValor <string>(reader, 3, null);
participante.codCli = ExtraDAO.ObterValor <int>(reader, 4, 0);
participante.datAti = ExtraDAO.ObterValor <DateTime>(reader, 5, new DateTime()).ToShortDateString();
participante.horAti = ExtraDAO.ObterValor <TimeSpan>(reader, 6, new TimeSpan()).ToString().Substring(0, 5);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(participante);
}
public EmpresaEntity Obter(string cnpjEmp)
{
EmpresaEntity empresa = new EmpresaEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT cnpjEmp, nomEmp, endEmp, cidEmp, ufEmp, telEmp FROM Empresa WHERE cnpjEmp = @cnpjEmp ORDER BY nomEmp", connection);
command.Parameters.AddWithValue("@cnpjEmp", cnpjEmp);
IDataReader reader = command.ExecuteReader();
reader.Read();
empresa.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 0, null);
empresa.nomEmp = ExtraDAO.ObterValor <string>(reader, 1, null);
empresa.endEmp = ExtraDAO.ObterValor <string>(reader, 2, null);
empresa.cidEmp = ExtraDAO.ObterValor <string>(reader, 3, null);
empresa.ufEmp = ExtraDAO.ObterValor <string>(reader, 4, null);
empresa.telEmp = ExtraDAO.ObterValor <string>(reader, 5, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(empresa);
}
public List <EmpresaEntity> ObterTodos(string cnpjEmp)
{
List <EmpresaEntity> empresas = new List <EmpresaEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT cnpjEmp, nomEmp, endEmp, cidEmp, ufEmp, telEmp FROM Empresa WHERE cnpjEmp <> @cnpjEmp AND cnpjEmp NOT IN (SELECT DISTINCT cnpjCli FROM Cliente WHERE cnpjEmp = @cnpjEmp) ORDER BY nomEmp", connection);
command.Parameters.AddWithValue("@cnpjEmp", cnpjEmp);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
EmpresaEntity empresa = new EmpresaEntity();
empresa.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 0, null);
empresa.nomEmp = ExtraDAO.ObterValor <string>(reader, 1, null);
empresa.endEmp = ExtraDAO.ObterValor <string>(reader, 2, null);
empresa.cidEmp = ExtraDAO.ObterValor <string>(reader, 3, null);
empresa.ufEmp = ExtraDAO.ObterValor <string>(reader, 4, null);
empresa.telEmp = ExtraDAO.ObterValor <string>(reader, 5, null);
empresas.Add(empresa);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(empresas);
}
public List <GraduacaoEntity> ObterTodos()
{
List <GraduacaoEntity> graduacoes = new List <GraduacaoEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codGra, nomGra FROM Graduacao ORDER BY nomGra", connection);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GraduacaoEntity graduacao = new GraduacaoEntity();
graduacao.codGra = ExtraDAO.ObterValor <int>(reader, 0, 0);
graduacao.nomGra = ExtraDAO.ObterValor <string>(reader, 1, null);
graduacoes.Add(graduacao);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(graduacoes);
}
public List <VendedorEntity> ObterTodos(string cpfUsu)
{
List <VendedorEntity> vendedores = new List <VendedorEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT codVen, Vendedor.cpfUsu, Vendedor.codCli, nomUsu, cnpjEmp, cnpjCli FROM Vendedor LEFT JOIN Cliente ON Vendedor.codCli = Cliente.codCli LEFT JOIN Usuario ON Vendedor.cpfUsu = Usuario.cpfUsu WHERE Vendedor.cpfUsu = @cpfUsu ORDER BY nomUsu", connection);
command.Parameters.AddWithValue("@cpfUsu", cpfUsu);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
VendedorEntity vendedor = new VendedorEntity();
vendedor.codVen = ExtraDAO.ObterValor <int>(reader, 0, 0);
vendedor.cpfUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
vendedor.codCli = ExtraDAO.ObterValor <int>(reader, 2, 0);
vendedor.nomUsu = ExtraDAO.ObterValor <string>(reader, 3, null);
vendedor.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 4, null);
vendedor.cnpjCli = ExtraDAO.ObterValor <string>(reader, 5, null);
vendedores.Add(vendedor);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(vendedores);
}
public UsuarioEntity Validar(string emaUsu, string senUsu)
{
UsuarioEntity usuario = new UsuarioEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Usuario.cpfUsu, nomUsu, codCar, nomCar, cnpjEmp, nomEmp FROM Usuario LEFT JOIN (SELECT DISTINCT cpfUsu, Cargo.codCar, nomCar, Contrato.cnpjEmp, nomEmp FROM Contrato LEFT JOIN Cargo ON Contrato.codCar = Cargo.codCar LEFT JOIN Empresa ON Contrato.cnpjEmp = Empresa.cnpjEmp WHERE fimCtr IS NULL) AS Result ON Usuario.cpfUsu = Result.cpfUsu WHERE emaUsu = @emaUsu AND senUsu = @senUsu ORDER BY nomUsu", connection);
command.Parameters.AddWithValue("@emaUsu", emaUsu);
command.Parameters.AddWithValue("@senUsu", senUsu);
IDataReader reader = command.ExecuteReader();
reader.Read();
usuario.cpfUsu = ExtraDAO.ObterValor <string>(reader, 0, null);
usuario.nomUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
usuario.codCar = ExtraDAO.ObterValor <int>(reader, 2, 0);
usuario.nomCar = ExtraDAO.ObterValor <string>(reader, 3, null);
usuario.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 4, null);
usuario.nomEmp = ExtraDAO.ObterValor <string>(reader, 5, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(usuario);
}
public List <UsuarioEntity> ObterTodosFuncionarios(string cnpjEmp)
{
List <UsuarioEntity> usuarios = new List <UsuarioEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Usuario.cpfUsu, nomUsu, Cargo.codCar, nomCar, Contrato.cnpjEmp, nomEmp FROM Usuario LEFT JOIN Contrato ON Usuario.cpfUsu = Contrato.cpfUsu LEFT JOIN Cargo ON Contrato.codCar = Cargo.codCar LEFT JOIN Empresa ON Contrato.cnpjEmp = Empresa.cnpjEmp WHERE fimCtr is NULL AND Contrato.cnpjEmp = @cnpjEmp ORDER BY nomUsu", connection);
command.Parameters.AddWithValue("@cnpjEmp", cnpjEmp);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
UsuarioEntity usuario = new UsuarioEntity();
usuario.cpfUsu = ExtraDAO.ObterValor <string>(reader, 0, null);
usuario.nomUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
usuario.codCar = ExtraDAO.ObterValor <int>(reader, 2, 0);
usuario.nomCar = ExtraDAO.ObterValor <string>(reader, 3, null);
usuario.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 4, null);
usuario.nomEmp = ExtraDAO.ObterValor <string>(reader, 5, null);
usuarios.Add(usuario);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(usuarios);
}
public UsuarioEntity Obter(string cpfUsu)
{
UsuarioEntity usuario = new UsuarioEntity();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Usuario.cpfUsu, nomUsu, sexUsu, endUsu, cidUsu, ufUsu, telUsu, emaUsu, senUsu, Usuario.codGra, nomGra, Contrato.codCar, nomCar, Contrato.cnpjEmp, nomEmp FROM Usuario LEFT JOIN Graduacao ON Usuario.codGra = Graduacao.codGra LEFT JOIN Contrato ON Usuario.cpfUsu = Contrato.cpfUsu LEFT JOIN Cargo ON Contrato.codCar = Cargo.codCar LEFT JOIN Empresa ON Contrato.cnpjEmp = Empresa.cnpjEmp WHERE Usuario.cpfUsu = @cpfUsu ORDER BY nomUsu", connection);
command.Parameters.AddWithValue("@cpfUsu", cpfUsu);
IDataReader reader = command.ExecuteReader();
reader.Read();
usuario.cpfUsu = ExtraDAO.ObterValor <string>(reader, 0, null);
usuario.nomUsu = ExtraDAO.ObterValor <string>(reader, 1, null);
usuario.sexUsu = ExtraDAO.ObterValor <string>(reader, 2, null);
usuario.endUsu = ExtraDAO.ObterValor <string>(reader, 3, null);
usuario.cidUsu = ExtraDAO.ObterValor <string>(reader, 4, null);
usuario.ufUsu = ExtraDAO.ObterValor <string>(reader, 5, null);
usuario.telUsu = ExtraDAO.ObterValor <string>(reader, 6, null);
usuario.emaUsu = ExtraDAO.ObterValor <string>(reader, 7, null);
usuario.senUsu = ExtraDAO.ObterValor <string>(reader, 8, null);
usuario.codGra = ExtraDAO.ObterValor <int>(reader, 9, 0);
usuario.nomGra = ExtraDAO.ObterValor <string>(reader, 10, null);
usuario.codCar = ExtraDAO.ObterValor <int>(reader, 11, 0);
usuario.nomCar = ExtraDAO.ObterValor <string>(reader, 12, null);
usuario.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 13, null);
usuario.nomEmp = ExtraDAO.ObterValor <string>(reader, 14, null);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(usuario);
}
public List <ClienteEntity> Buscar(string cnpjEmp, string busca)
{
List <ClienteEntity> clientes = new List <ClienteEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Cliente.codCli, Cliente.cnpjEmp, Cliente.cnpjCli, nomEmp, nomCli, codVen, Vendedor.cpfUsu AS cpfVen, nomUsu AS nomVen FROM Cliente LEFT JOIN (SELECT DISTINCT cnpjEmp, nomEmp FROM Empresa) AS Empresa1 ON Empresa1.cnpjEmp = Cliente.cnpjEmp LEFT JOIN (SELECT DISTINCT cnpjEmp AS cnpjCli, nomEmp AS nomCli FROM Empresa) AS Empresa2 ON Empresa2.cnpjCli = Cliente.cnpjCli LEFT JOIN Vendedor ON Vendedor.codCli = Cliente.codCli LEFT JOIN Usuario ON Usuario.cpfUsu = Vendedor.cpfUsu WHERE Cliente.cnpjEmp = @cnpjEmp AND nomCli LIKE CONCAT('%', @busca, '%') ORDER BY nomCli", connection);
command.Parameters.AddWithValue("@cnpjEmp", cnpjEmp);
command.Parameters.AddWithValue("@busca", busca);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
ClienteEntity cliente = new ClienteEntity();
cliente.codCli = ExtraDAO.ObterValor <int>(reader, 0, 0);
cliente.cnpjEmp = ExtraDAO.ObterValor <string>(reader, 1, null);
cliente.cnpjCli = ExtraDAO.ObterValor <string>(reader, 2, null);
cliente.nomEmp = ExtraDAO.ObterValor <string>(reader, 3, null);
cliente.nomCli = ExtraDAO.ObterValor <string>(reader, 4, null);
cliente.codVen = ExtraDAO.ObterValor <int>(reader, 5, 0);
cliente.cpfVen = ExtraDAO.ObterValor <string>(reader, 6, null);
cliente.nomVen = ExtraDAO.ObterValor <string>(reader, 7, null);
clientes.Add(cliente);
}
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(clientes);
}
public List <AtividadeEntity> ObterTodos(string cpfUsu)
{
List <AtividadeEntity> atividades = new List <AtividadeEntity>();
try
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT DISTINCT Atividade.codAti, Atividade.codCli, desAti, tipAti, datAti, horAti, nomEmp AS nomCli FROM Atividade LEFT JOIN Cliente ON Atividade.codCli = Cliente.codCli LEFT JOIN Empresa ON Cliente.cnpjCli = Empresa.cnpjEmp LEFT JOIN Participante ON Atividade.codAti = Participante.codAti WHERE cpfUsu = @cpfUsu ORDER BY datAti", connection);
command.Parameters.AddWithValue("@cpfUsu", cpfUsu);
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AtividadeEntity atividade = new AtividadeEntity();
atividade.codAti = ExtraDAO.ObterValor <int>(reader, 0, 0);
atividade.codCli = ExtraDAO.ObterValor <int>(reader, 1, 0);
atividade.desAti = ExtraDAO.ObterValor <string>(reader, 2, null);
atividade.tipAti = ExtraDAO.ObterValor <string>(reader, 3, null);
atividade.datAti = ExtraDAO.ObterValor <DateTime>(reader, 4, new DateTime()).ToShortDateString();
atividade.horAti = ExtraDAO.ObterValor <TimeSpan>(reader, 5, new TimeSpan()).ToString().Substring(0, 5);
atividade.nomCli = ExtraDAO.ObterValor <string>(reader, 6, null);
atividades.Add(atividade);
}
}
catch
{ }
finally
{
if (connection != null)
{
connection.Close();
}
}
return(atividades);
}