示例#1
0
        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);
        }
示例#2
0
        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);
        }
示例#3
0
        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);
        }
示例#4
0
        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);
        }
示例#5
0
        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);
        }
示例#6
0
        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);
        }
示例#7
0
        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);
        }
示例#8
0
        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);
        }
示例#9
0
        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);
        }
示例#10
0
        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);
        }
示例#11
0
        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);
        }
示例#12
0
        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);
        }
示例#13
0
        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);
        }
示例#14
0
        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);
        }