public static bool atualizar(Usuario u, Contato c) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "update contato set telefones = @telefones, " + "endereco = @endereco, aniversario = @aniversario, outros = @outros " + "where login = @login and nome = @nome"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@nome", c.Nome); cmd.Parameters.AddWithValue("@telefones", c.Telefones); cmd.Parameters.AddWithValue("@endereco", c.Endereco); cmd.Parameters.AddWithValue("@aniversario", c.Aniversario); cmd.Parameters.AddWithValue("@outros", c.Outros); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static bool cadastrar(Usuario u, Contato c) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into contato(login, nome, telefones, endereco, aniversario, outros) " + "values (@login, @nome, @telefones, @endereco, @aniversario, @outros)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@nome", c.Nome); cmd.Parameters.AddWithValue("@telefones", c.Telefones); cmd.Parameters.AddWithValue("@endereco", c.Endereco); cmd.Parameters.AddWithValue("@aniversario", c.Aniversario); cmd.Parameters.AddWithValue("@outros", c.Outros); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static PaginaDiario procurarPagina(Usuario u, DateTime dt) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from pg_diario " + "where login = @login and data = @data"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@data", dt); SqlCeDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { PaginaDiario p = new PaginaDiario(reader); return p; } else { return null; } } catch (Exception err) { throw err; } }
public static TipoFluxo procurarTipoFluxo(Usuario u, String desc) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from tipo_fluxo " + "where tfc_login = @login and tfc_descricao = @desc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@desc", desc); SqlCeDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { TipoFluxo t = new TipoFluxo(reader); return t; } else { return null; } } catch (Exception err) { throw err; } }
public static bool verificarLoginExistente(Usuario usuario) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from usuario u where u.login = @login"; cmd.Parameters.AddWithValue("@login", usuario.Login); Boolean loginExistente = Convert.ToBoolean((int)cmd.ExecuteScalar()); if (loginExistente) { throw new Exception("Login já cadastrado. Escolha outro."); } else { return false; } } catch (Exception err) { throw err; } }
public static void cadastrar(Usuario u, FluxoCaixa f) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into fluxo_caixa(flc_login, flc_tipo, " + "flc_data, flc_descricao, flc_valor) " + "values (@login, @tipo, @data, @desc, @valor)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@tipo", f.Tipo.Id); cmd.Parameters.AddWithValue("@data", f.Data); cmd.Parameters.AddWithValue("@desc", f.Descricao); cmd.Parameters.AddWithValue("@valor", f.Valor); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static List<Lembrete> lista(Usuario u) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from lembrete where login = @login"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); List<Lembrete> lista = new List<Lembrete>(); while (reader.Read()) { Lembrete lemb = new Lembrete(reader); lista.Add(lemb); } return lista; } catch (Exception err) { throw err; } }
public static bool verificarLoginValido(Usuario u) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from usuario u " + "where u.login = @login and u.senha = @senha"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@senha", u.Senha); int res = (int) cmd.ExecuteScalar(); if (res > 0) { return true; } else { throw new Exception("Login/senha incorretos."); } } catch (Exception err) { throw err; } }
public static List<TipoFluxo> lista(Usuario u) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from tipo_fluxo " + "where tfc_login is null OR tfc_login = @login"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); List<TipoFluxo> lista = new List<TipoFluxo>(); while (reader.Read()) { TipoFluxo c = new TipoFluxo(reader); lista.Add(c); } return lista; } catch (Exception err) { throw err; } }
public static Configuracao carregar(Usuario u) { Configuracao c = new Configuracao(); String nAniversariantes = RegistroWindows.recuperarValor( u.Login + "_" + "maxAniversariantes"); if (nAniversariantes == null) c.MaxAniversariantes = AniversariosBusiness.MAX_ANIVERSARIANTES; else c.MaxAniversariantes = Convert.ToInt32(nAniversariantes); return c; }
public static bool logarUsuario(String login, String senha) { try { Usuario u = new Usuario(); u.Login = login; u.Senha = senha; UsuarioDAO.verificarLoginValido(u); SessaoBusiness.UsuarioLogado = u; return true; } catch (Exception err) { throw err; } }
public static void excluir(Usuario u, Compromisso c) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "delete compromisso where cpm_id = @id"; cmd.Prepare(); cmd.Parameters.AddWithValue("@id", c.Id); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static bool excluir(Usuario u, Lembrete lemb) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "delete lembrete where id = @id"; cmd.Prepare(); cmd.Parameters.AddWithValue("@id", lemb.Id); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static void atualizarPagina(Usuario u, PaginaDiario p) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "update pg_diario set texto = @texto " + "where login = @login and data = @data"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@data", p.Data); cmd.Parameters.AddWithValue("@texto", p.Texto); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static void cadastrarPagina(Usuario u, PaginaDiario p) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into pg_diario(login, data, texto) " + "values (@login, @data, @texto)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@data", p.Data); cmd.Parameters.AddWithValue("@texto", p.Texto); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static bool cadastrar(Usuario u, Lembrete lemb) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into lembrete(login, descricao) " + "values (@login, @descricao)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@descricao", lemb.Descricao); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static void cadastrar(Usuario u, Compromisso c) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into compromisso (cpm_login, cpm_inicio, cpm_fim, cpm_descricao) " + "values (@login, @inicio, @fim, @descricao)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@inicio", c.Inicio); cmd.Parameters.AddWithValue("@fim", c.Fim); cmd.Parameters.AddWithValue("@descricao", c.Descricao); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static bool cadastrar(Usuario usuario) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into usuario(login, senha) values (@login, @senha)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", usuario.Login); cmd.Parameters.AddWithValue("@senha", usuario.Senha); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static List<Contato> aniversariantesMes(Usuario u, int max) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); List<Contato> lista = new List<Contato>(); cmd.CommandText = "select * from contato " + "where login = @login and " + "datepart(day, aniversario) >= @hojeDia and " + "datepart(month, aniversario) = @hojeMes " + "order by aniversario asc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@hojeDia", DateTime.Today.Day); cmd.Parameters.AddWithValue("@hojeMes", DateTime.Today.Month); SqlCeDataReader reader = cmd.ExecuteReader(); int n = 0; // contador while (reader.Read() && n < max) { Contato c = new Contato(reader); lista.Add(c); n++; } return lista; } catch (Exception err) { throw err; } }
public static bool cadastrarUsuario(String login, String senha, String confSenha) { try { if (login == null || login.Length == 0) { throw new Exception("Digite um login."); } if (senha != confSenha) { throw new Exception("Senha e confirmação de senha não conferem."); } // instancia usuario Usuario usuario = new Usuario(); usuario.Login = login; usuario.Senha = senha; // verifica se login é existente UsuarioDAO.verificarLoginExistente(usuario); // cadastra usuario UsuarioDAO.cadastrar(usuario); // comita transação Conexao.commit(); return true; } catch (Exception err) { Conexao.rollback(); throw err; } }
public static bool cadastrar(Usuario u, TipoFluxo t) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "insert into tipo_fluxo(tfc_login, tfc_descricao, tfc_fluxo) " + "values (@login, @desc, @fluxo)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@desc", t.Descricao); cmd.Parameters.AddWithValue("@fluxo", t.Fluxo); cmd.ExecuteNonQuery(); return true; } catch (Exception err) { throw err; } }
public static void alterar(Usuario u, Compromisso c) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.Transaction = Conexao.Transacao; cmd.CommandText = "update compromisso set cpm_inicio = @inicio, " + "cpm_fim = @fim, cpm_descricao = @descricao " + "where cpm_id = @id"; cmd.Prepare(); cmd.Parameters.AddWithValue("@id", c.Id); cmd.Parameters.AddWithValue("@inicio", c.Inicio); cmd.Parameters.AddWithValue("@fim", c.Fim); cmd.Parameters.AddWithValue("@descricao", c.Descricao); cmd.ExecuteNonQuery(); } catch (Exception err) { throw err; } }
public static List<Contato> lista(Usuario u, String filtro) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from contato where login = @login"; if (filtro != null) cmd.CommandText += " and (nome like @filtro or telefones like @filtro)"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); if (filtro != null) cmd.Parameters.AddWithValue("@filtro", "%" + filtro + "%"); SqlCeDataReader reader = cmd.ExecuteReader(); List<Contato> lista = new List<Contato>(); while (reader.Read()) { Contato c = new Contato(reader); lista.Add(c); } return lista; } catch (Exception err) { throw err; } }
public static List<Contato> proximosAniversariantes(Usuario u, int max) { try { List<Contato> niverMes = aniversariantesMes(u, max); List<Contato> niverProximosMeses = aniversariantesProximosMesesAno(u, max); List<Contato> niverProximoAno = aniversariantesProximoAno(u, max); Dictionary<String, Contato> aniversariantes = new Dictionary<String, Contato>(max); // evita duplicidade de aniversariantes foreach (Contato c in niverMes) { aniversariantes.Add(c.Nome, c); } foreach (Contato c in niverProximosMeses) { if (aniversariantes.Count < max && !aniversariantes.ContainsKey(c.Nome)) aniversariantes.Add(c.Nome, c); } foreach (Contato c in niverProximoAno) { if (aniversariantes.Count < max && !aniversariantes.ContainsKey(c.Nome)) aniversariantes.Add(c.Nome, c); } return aniversariantes.Values.ToList<Contato>(); } catch (Exception err) { throw err; } }
public static List<DateTime> listaDatas(Usuario u) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from compromisso " + "where cpm_login = @login"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); HashSet<DateTime> listaDatas = new HashSet<DateTime>(); while (reader.Read()) { Compromisso c = new Compromisso(reader); DateTime data = c.Inicio.Date; do { listaDatas.Add(data); data = data.AddDays(1); } while (data <= c.Fim); } return listaDatas.ToList(); } catch (Exception err) { throw err; } }
public static List<Compromisso> lista(Usuario u, DateTime data) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from compromisso " + "where cpm_login = @login and ((datepart(day, cpm_inicio) = @dataDia " + "and datepart(month, cpm_inicio) = @dataMes " + "and datepart(year, cpm_inicio) = @dataAno) or " + "(datepart(day, cpm_fim) = @dataDia " + "and datepart(month, cpm_fim) = @dataMes " + "and datepart(year, cpm_fim) = @dataAno) or " + "(cpm_inicio < @data and cpm_fim > @data)) " + "order by cpm_inicio asc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); cmd.Parameters.AddWithValue("@data", data); cmd.Parameters.AddWithValue("@dataDia", data.Day); cmd.Parameters.AddWithValue("@dataMes", data.Month); cmd.Parameters.AddWithValue("@dataAno", data.Year); SqlCeDataReader reader = cmd.ExecuteReader(); List<Compromisso> lista = new List<Compromisso>(); while (reader.Read()) { Compromisso c = new Compromisso(reader); lista.Add(c); } return lista; } catch (Exception err) { throw err; } }
public static List<Compromisso> lista(Usuario u) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from compromisso " + "where cpm_login = @login " + "order by cpm_inicio asc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); List<Compromisso> lista = new List<Compromisso>(); while (reader.Read()) { Compromisso c = new Compromisso(reader); lista.Add(c); } return lista; } catch (Exception err) { throw err; } }
public static void salvarPagina(Usuario u, PaginaDiario p) { try { PaginaDiario p2 = procurarPagina(u, p.Data); if (p2 != null) { atualizarPagina(u, p); } else { cadastrarPagina(u, p); } } catch (Exception err) { throw err; } }
public static List<FluxoCaixa> lista(Usuario u, FiltroControleFinanceiro filtro) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from fluxo_caixa " + "join tipo_fluxo on flc_tipo = tfc_id " + "where flc_login = @login"; if (filtro != null) { if (filtro.Descricao != null) { cmd.CommandText += " and flc_descricao like @desc "; cmd.Parameters.AddWithValue("@desc", "%" + filtro.Descricao + "%"); } if (filtro.PeriodoIni != null) { cmd.CommandText += " and flc_data >= @periodoIni "; cmd.Parameters.AddWithValue("@periodoIni", filtro.PeriodoIni); } if (filtro.PeriodoFim != null) { cmd.CommandText += " and flc_data < @periodoFim "; cmd.Parameters.AddWithValue("@periodoFim", filtro.PeriodoFim.AddDays(1)); } if (filtro.Tipos != null && filtro.Tipos.Count > 0) { String ids = ""; foreach (TipoFluxo t in filtro.Tipos) ids += t.Id.ToString() + ","; ids = ids.Remove(ids.Length - 1); cmd.CommandText += " and flc_tipo in (" + ids + ") "; } } cmd.CommandText += " order by flc_data desc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); List<FluxoCaixa> lista = new List<FluxoCaixa>(); while (reader.Read()) { FluxoCaixa f = new FluxoCaixa(reader); lista.Add(f); } return lista; } catch (Exception err) { throw err; } }
private static List<Contato> aniversariantesProximoAno(Usuario u, int max) { try { SqlCeConnection conn = Conexao.ConexaoSql; SqlCeCommand cmd = conn.CreateCommand(); List<Contato> lista = new List<Contato>(); cmd.CommandText = "select * from contato " + "where login = @login " + "order by datepart(month, aniversario) asc, datepart(day, aniversario) asc"; cmd.Prepare(); cmd.Parameters.AddWithValue("@login", u.Login); SqlCeDataReader reader = cmd.ExecuteReader(); int n = 0; // contador while (reader.Read() && n < max) { Contato c = new Contato(reader); lista.Add(c); n++; } return lista; } catch (Exception err) { throw err; } }