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;
}
}