public List <Relatorio> GetRelatorioTempoAtendimentoDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
Chm.idChamado
,Srv.nome
,Usr.nome
,case when datediff(SECOND, dataInicioAtendimento, dataFimAtendimento) < 0 then 0 else datediff(SECOND, dataInicioAtendimento, dataFimAtendimento) end from
Atendimento Atd inner join
Chamado Chm on (Atd.idChamado = Chm.idChamado) inner join
Usuario Usr on (Atd.idTecnico = Usr.idUsuario) inner join
Servico Srv on (Srv.idServico = Chm.idServico) where
Chm.idStatusChamado = 3 and Atd.idAtendimento = (select max(idAtendimento) from Atendimento where idChamado = Chm.idChamado) and Chm.idEmpresa = @Empresa
order by 4;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Chamado = Dr.GetInt32(0);
Obj.Servico = Dr.GetString(1);
Obj.Tecnico = Dr.GetString(2);
Obj.TempoAtendimento = TimeSpan.FromSeconds(Convert.ToDouble(Dr[3]));
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetRelatorioRecusaAtendimentoDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
Chm.idChamado
,Usr.nome
,Rec.dataRegistro from
RecusaAtendimento Rec inner join
Usuario Usr on (Rec.idTecnico = Usr.idUsuario) inner join
Chamado Chm on (Rec.idChamado = Chm.idChamado) where
Rec.idEmpresa = @Empresa;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Chamado = Dr.GetInt32(0);
Obj.Tecnico = Dr.GetString(1);
Obj.DataOcorrido = Dr.GetDateTime(2);
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetRelatorioSLADAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
Chm.idChamado, Srv.nome as Servico, datediff(SECOND,dataAbertura, dataFechamento) as Tempo_Conclusao_Chamado
,CONVERT(CHAR(5), DATEADD(MINUTE, 60*sla, 0), 108) as SLA_Servico from
Chamado Chm inner join
Servico Srv on (Chm.idServico = Srv.idServico) where
idStatusChamado = 3 and Chm.idEmpresa = @Empresa
order by 3;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Chamado = Dr.GetInt32(0);
Obj.Servico = Dr.GetString(1);
Obj.TempoAtendimento = TimeSpan.FromSeconds(Convert.ToDouble(Dr[2]));
Obj.SLA = TimeSpan.Parse(Dr[3].ToString());
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetAtendimentoPorRegiaoDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
top 10 Rg.Regiao
, COUNT(*) as Quantidade from
Atendimento Atd inner join
RegiaoDeAtendimento Rg on (Atd.idRegiaoAtendimento = Rg.idRegiaoAtendimento) where
Atd.idEmpresa = @Empresa
group by Rg.regiao
order by 2;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Regiao = Dr.GetString(0);
Obj.Media = Dr.GetInt32(1);
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetQtdPorServicoDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
(TpSrv.tipo +': '+Srv.nome) as Servico
,count(*) as Qtd from
Chamado Chm inner join
Servico Srv on (Chm.idServico = Srv.idServico) inner join
TipoServico TpSrv on (Srv.idTipo = TpSrv.idTipoServ) where
Chm.idEmpresa = @Empresa
group by Srv.nome,TpSrv.tipo;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Servico = Dr.GetString(0);
Obj.Media = Dr.GetInt32(1);
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetTopSolucoesDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select top 5 (Usr.nome)
,sum(Avs.likeSolucao) as Soluções_Uteis from
Atendimento At inner join
Usuario Usr on (At.idTecnico = Usr.idUsuario) inner join
AvaliacaoSolucao Avs on (At.idAtendimento = Avs.idAtendimento) where
Usr.idEmpresa = @Empresa
group by Usr.nome
order by 2 desc;" , Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Usuario = Dr.GetString(0);
Obj.Media = Dr.GetInt32(1);
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Roles.GetRolesForUser(Membership.GetUser().ToString())[0].Equals("Administrador") || Roles.GetRolesForUser(Membership.GetUser().ToString())[0].Equals("Gestor"))
{
ListaGeo = new ManterGeo(ObjGeo).InformaGeo();
foreach (var Lista in new ManterRelatorio(ObjRelatorio).GetQtdChamados())
{
ObjRelatorio = Lista;
}
ObjRelatorioDonut.RelatMes = true;
foreach (var Lista in new ManterRelatorio(ObjRelatorioDonut).GetQtdChamados())
{
ObjRelatorioDonut = Lista;
}
ObjRelatorioChart.RelatAno = true;
ListAux = new List <Relatorio>();
ListAux = new ManterRelatorio(ObjRelatorioChart).GetQtdChamados();
Count = 0;
foreach (var Lista in ListAux)
{
Relatorio Obj = FactoryRelatorio.GetNew();
if (Lista.Status.Equals(1))
{
Obj.Mes = Lista.Mes;
Obj.QtdChamadosAber = Lista.QtdChamadosAber;
}
if (!ListAux.Count.Equals(Count + 1))
{
if (ListAux[Count + 1].Mes.Equals(Obj.Mes) && ListAux[Count + 1].Status.Equals(3))
{
Obj.QtdChamadosConc = ListAux[Count + 1].QtdChamadosConc;
}
}
ListRelat.Add(Obj);
Count += 1;
}
ListTopAtend = new ManterRelatorio(ObjRelatorio).GetTopTecnicos();
ListTopSoluc = new ManterRelatorio(ObjRelatorio).GetTopSolucoes();
ListServico = new ManterRelatorio(ObjRelatorio).GetQtdPorServico();
ListRegiao = new ManterRelatorio(ObjRelatorio).GetAtendimentoPorRegiao();
ListTopTempo = new ManterRelatorio(ObjRelatorio).GetRelatorioTempoAtendimento();
ListTopTempo.RemoveRange(5, ListTopTempo.Count - 5); //Verificar quando a lista tem menos de 5 da erro
ObjRelatorioTempoMedio = new ManterRelatorio(ObjRelatorioTempoMedio).GetTempoMedioAtendimento();
}
}
catch (Exception Ex)
{
LogException.InsereLogBd(Ex);
//MsgLabel.Text = LogException.CodigoErroBd();
}
}
public List <Relatorio> GetRelatorioChamadosDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
Cmd = new SqlCommand(@"
select
Chm.idChamado
,Usr.nome as Cliente
,UsrTec.nome as Tecnico
,Chm.assunto
,Srv.nome as Servico
,RgAtd.regiao
,PrChm.descricao as Prioridade
,StChm.status
,Chm.dataAbertura
,Chm.dataFechamento
,case when Atd.dataInicioAtendimento is NULL then '2000-01-01 00:00:00.000' else Atd.dataInicioAtendimento end
,case when Atd.dataFimAtendimento is NULL then '2000-01-01 00:00:00.000' else Atd.dataFimAtendimento end
,case when Atd.tempoAtendimento is NULL then '0.00' else Atd.tempoAtendimento end
,case when Chm.infoCancelamento is NULL then '' else Chm.infoCancelamento end
,case when Chm.ContReabertura is NULL then '0' else Chm.ContReabertura end
,case when Chm.ContPendencia is NULL then '0' else Chm.ContPendencia end
from Chamado Chm inner join
Atendimento Atd on (Chm.idChamado = Atd.idChamado) inner join
Usuario Usr on (Usr.idUsuario = Atd.idCliente) inner join
Usuario UsrTec on (UsrTec.idUsuario = Atd.idTecnico) inner join
StatusChamado StChm on (Chm.idStatusChamado = StChm.idStatusChamado) inner join
Servico Srv on (Chm.idServico = Srv.idServico) inner join
PrioridadeChamado PrChm on (Chm.idPrioridade = PrChm.idPrioridade) inner join
RegiaoDeAtendimento RgAtd on (RgAtd.idRegiaoAtendimento = Atd.idRegiaoAtendimento) where
Chm.idEmpresa = @Empresa " + ObjRelatorio.FiltroRelatorio + @"
order by Chm.idChamado;", Con);
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
Relatorio Obj = FactoryRelatorio.GetNew();
Obj.Chamado = Dr.GetInt32(0);
Obj.Cliente = Dr.GetString(1);
Obj.Tecnico = Dr.GetString(2);
Obj.Assunto = Dr.GetString(3);
Obj.Servico = Dr.GetString(4);
Obj.Regiao = Dr.GetString(5);
Obj.Prioridade = Dr.GetString(6);
Obj.StatusChamado = Dr.GetString(7);
Obj.DataAbertura = Dr.GetDateTime(8);
Obj.DataFechamento = Dr.GetDateTime(9);
Obj.DataInicioAtendimento = Dr.GetDateTime(10);
Obj.DataFimAtendimento = Dr.GetDateTime(11);
Obj.TempoAtendimento = TimeSpan.FromMinutes(Convert.ToDouble(Dr[12].ToString()));
Obj.InformacaoCancelamento = Dr.GetString(13);
Obj.Reaberturas = Dr.GetInt32(14);
Obj.Pendencias = Dr.GetInt32(15);
ListRelat.Add(Obj);
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
public List <Relatorio> GetQtdChamadosDAO()
{
try
{
SqlDataReader Dr = null;
using (SqlConnection Con = new Conexao().ConexaoDB())
{
if (ObjRelatorio.RelatMes)
{
Cmd = new SqlCommand(@"
select count(*), idStatusChamado
from chamado
where idStatusChamado = 1 and idEmpresa = @Empresa and dataAbertura >= @Mes
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 2 and idEmpresa = @Empresa and dataAbertura >= @Mes
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 3 and idEmpresa = @Empresa and dataAbertura >= @Mes
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 4 and idEmpresa = @Empresa and dataAbertura >= @Mes
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 5 and idEmpresa = @Empresa and dataAbertura >= @Mes
group by idStatusChamado;" , Con);
Cmd.Parameters.AddWithValue("@Mes", new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1));
}
else if (ObjRelatorio.RelatAno)
{
Cmd = new SqlCommand(@"
select count(*) AS QTD, MONTH(dataAbertura) AS MES, 1 as Status
from chamado
where dataAbertura >= @Ano and idEmpresa = @Empresa
group by MONTH(dataAbertura)
union all
select count(*) AS QTD, MONTH(dataFechamento) AS MES, 3 as Status
from chamado
where dataFechamento >= @Ano and idEmpresa = @Empresa
group by MONTH(dataFechamento)
order by MES, Status;" , Con);
Cmd.Parameters.AddWithValue("@Ano", new DateTime(DateTime.Now.Year, 1, 1));
}
else
{
Cmd = new SqlCommand(@"
select count(*), idStatusChamado
from chamado
where idStatusChamado = 1 and idEmpresa = @Empresa
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 2 and idEmpresa = @Empresa
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 3 and idEmpresa = @Empresa
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 4 and idEmpresa = @Empresa
group by idStatusChamado
union all
select count(*), idStatusChamado
from chamado
where idStatusChamado = 5 and idEmpresa = @Empresa
group by idStatusChamado;" , Con);
}
Cmd.Parameters.AddWithValue("@Empresa", InfoGlobal.GlobalIdEmpresa);
Dr = Cmd.ExecuteReader();
while (Dr.Read())
{
if (ObjRelatorio.RelatAno)
{
Relatorio Obj = FactoryRelatorio.GetNew();
if (Dr.GetInt32(2).Equals(1))
{
Obj.QtdChamadosAber = Dr.GetInt32(0);
Obj.Mes = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Dr.GetInt32(1));
Obj.Status = Dr.GetInt32(2);
ListRelat.Add(Obj);
}
else
{
Obj.QtdChamadosConc = Dr.GetInt32(0);
Obj.Mes = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Dr.GetInt32(1));
Obj.Status = Dr.GetInt32(2);
ListRelat.Add(Obj);
}
}
else
{
switch (Dr.GetInt32(1))
{
case 1:
ObjRelatorio.QtdChamadosAber = Dr.GetInt32(0);
ListRelat.Add(ObjRelatorio);
break;
case 2:
ObjRelatorio.QtdChamadosEmAnd = Dr.GetInt32(0);
ListRelat.Add(ObjRelatorio);
break;
case 3:
ObjRelatorio.QtdChamadosConc = Dr.GetInt32(0);
ListRelat.Add(ObjRelatorio);
break;
case 4:
ObjRelatorio.QtdChamadosRetra = Dr.GetInt32(0);
ListRelat.Add(ObjRelatorio);
break;
case 5:
ObjRelatorio.QtdChamadosCanc = Dr.GetInt32(0);
ListRelat.Add(ObjRelatorio);
break;
default:
break;
}
}
}
return(ListRelat);
}
}
catch (SqlException)
{
throw;
}
}
