public List <DashBoard> RetornarDadosGraficoPie()
{
List <DashBoard> lista = new List <DashBoard>();
DashBoard item;
string Id_usuario_logado = HttpContextAccessor.HttpContext.Session.GetString("IdUsuarioLogado");
string sql = $"select sum(t.valor) as total, p.Descricao from transacao as t inner join plano_contas as p on t.Plano_Contas_Id = p.Id where t.Tipo = 'Despesa' and t.Usuario_Id = '{Id_usuario_logado}' group by p.Descricao";
DAL objDAL = new DAL();
DataTable dt = new DataTable();
dt = objDAL.RetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
item = new DashBoard();
item.Total = double.Parse(dt.Rows[i]["Total"].ToString());
item.PlanoConta = dt.Rows[i]["Descricao"].ToString();
lista.Add(item);
}
return(lista);
}
public List <DashBoard> RetornarDadosGraficoBar()
{
List <DashBoard> lista = new List <DashBoard>();
DashBoard item;
string Id_usuario_logado = HttpContextAccessor.HttpContext.Session.GetString("IdUsuarioLogado");
string sql = $"select saldo, Nome from conta where Usuario_id = '{Id_usuario_logado}';";
DAL objDAL = new DAL();
DataTable dt = new DataTable();
dt = objDAL.RetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
item = new DashBoard();
item.saldoConta = double.Parse(dt.Rows[i]["saldo"].ToString());
item.NomeConta = dt.Rows[i]["Nome"].ToString();
lista.Add(item);
}
return(lista);
}
public List <DashBoard> RetornarDadosGraficoPie(TransacaoModel formulario)
{
string id_usuario_logado = HttpContextAccessor.HttpContext.Session.GetString("IdUsuarioLogado");
List <DashBoard> lista = new List <DashBoard>();
DashBoard item;
string filtro = "";
string sql = $@"select p.Descricao, sum(t.valor) as total from transacao as t
inner join plano_contas as p
on t.Plano_Contas_Id = p.Id where t.tipo = '{formulario.Tipo}' and t.usuario_id = {id_usuario_logado}
{filtro}
group by p.Descricao";
if (formulario.Conta_Id > 0)
{
filtro += $"and t.Conta_Id = {formulario.Plano_Conta_Id} ";
}
if (formulario.Data != null && formulario.DataFinal != null)
{
filtro += $" and t.Data >='{DateTime.Parse(formulario.Data).ToString("yyyy/MM/dd")}' and t.Data <= '{DateTime.Parse(formulario.DataFinal).ToString("yyyy/MM/dd")}' ";
}
DAL objDAL = new DAL();
DataTable dt = new DataTable();
dt = objDAL.RetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
item = new DashBoard();
item.Total = double.Parse(dt.Rows[i]["total"].ToString());
item.PlanoConta = dt.Rows[i]["Descricao"].ToString();
lista.Add(item);
}
return(lista);
}