public EmpresasType selectByAssociacao(AssociacaoType associacao)
{
EmpresasType types = new EmpresasType();
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT * FROM empresa " +
"WHERE id_associacao = @id";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", associacao.IdAssociacao);
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
types.Add(execReader(dr));
}
return(types);
}
public double selectSaldoAnterior(AssociacaoType assoc, string ano, string mes)
{
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT IFNULL(SUM(valor), 0) AS saldo FROM financeiro " +
"WHERE id_associacao = @id " +
"AND financeiro.`data` < @data";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", assoc.IdAssociacao);
cmd.Parameters.AddWithValue("@data", ano + "-" + mes + "-01");
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
double saldo = 0;
if (dr.Read())
{
saldo = dr.GetDouble("saldo");
}
return(saldo);
}
public FinanceirosType selectByAssociacao(AssociacaoType assoc, string ano, string mes)
{
FinanceirosType types = new FinanceirosType();
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT * FROM financeiro " +
"WHERE id_associacao = @id " +
"AND YEAR(financeiro.`data`) = @ano " +
"AND MONTH(financeiro.`data`) = @mes " +
"ORDER BY `data`, `id_financeiro`";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", assoc.IdAssociacao);
cmd.Parameters.AddWithValue("@ano", ano);
cmd.Parameters.AddWithValue("@mes", mes);
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
types.Add(execReader(dr));
}
return(types);
}
public double selectSaldoAnterior(AssociacaoType assoc, string ano, string mes)
{
return(DAL.selectSaldoAnterior(assoc, ano, mes));
}
public FinanceirosType selectByAssociacao(AssociacaoType assoc, string ano, string mes)
{
return(DAL.selectByAssociacao(assoc, ano, mes));
}
public double selectSaldoAnterior(AssociacaoType assoc, string ano, string mes)
{
return DAL.selectSaldoAnterior(assoc, ano, mes);
}
public FinanceirosType selectByAssociacao(AssociacaoType assoc, string ano, string mes)
{
return DAL.selectByAssociacao(assoc, ano, mes);
}
public FinanceirosType selectByAssociacao(AssociacaoType assoc, string ano, string mes)
{
FinanceirosType types = new FinanceirosType();
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT * FROM financeiro " +
"WHERE id_associacao = @id " +
"AND YEAR(financeiro.`data`) = @ano " +
"AND MONTH(financeiro.`data`) = @mes " +
"ORDER BY `data`, `id_financeiro`";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", assoc.IdAssociacao);
cmd.Parameters.AddWithValue("@ano", ano);
cmd.Parameters.AddWithValue("@mes", mes);
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
types.Add(execReader(dr));
}
return types;
}
public double selectSaldoAnterior(AssociacaoType assoc, string ano, string mes)
{
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT IFNULL(SUM(valor), 0) AS saldo FROM financeiro " +
"WHERE id_associacao = @id "+
"AND financeiro.`data` < @data";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", assoc.IdAssociacao);
cmd.Parameters.AddWithValue("@data", ano + "-" + mes + "-01");
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
double saldo = 0;
if (dr.Read())
{
saldo = dr.GetDouble("saldo");
}
return saldo;
}
public EmpresasType selectByAssociacao(AssociacaoType associacao)
{
EmpresasType types = new EmpresasType();
MySqlConnection con = new MySqlConnection(Dados.StringConexao);
string SQL = "SELECT * FROM empresa " +
"WHERE id_associacao = @id";
MySqlCommand cmd = new MySqlCommand(SQL, con);
cmd.Parameters.AddWithValue("@id", associacao.IdAssociacao);
con.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
types.Add(execReader(dr));
}
return types;
}
