public string RetornaChamadasDia()
{
//Lista
var lista = new List <CallHoras>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["gtaConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT DATEPART(HOUR, data_ligacao) AS [HourofDay], COALESCE(COUNT(id_consulta), 0) AS [Total Calls] " +
" FROM [hspmCall].[dbo].[ativo_ligacao] " +
" WHERE day(data_ligacao) = 11 " +
" GROUP BY DATEPART(HOUR, data_ligacao) " +
" ORDER BY DATEPART(HOUR, data_ligacao)";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
//char[] ponto = { '.', ' ' };
while (dr1.Read())
{
CallHoras call = new CallHoras();
call.Horadodia = dr1.GetInt32(0);
call.TotalCall = dr1.GetInt32(1);
lista.Add(call);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
//O JavaScriptSerializer vai fazer o web service retornar JSON
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(lista));
}
public string ChamadasMes(string mesAno)
{
int mes = Convert.ToInt32(mesAno.Substring(0, 2));
int ano = Convert.ToInt32(mesAno.Substring(3, 4));
var horas = new List <string>();
var total = new List <int>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["gtaConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT DATEPART(DAY, data_ligacao) AS [Day], COALESCE(COUNT(id_consulta), 0) AS [Total Calls] " +
" FROM [hspmCall].[dbo].[ativo_ligacao] " +
" WHERE MONTH(data_ligacao) = " + mes + " and YEAR(data_ligacao) = " + ano +
" GROUP BY DATEPART(DAY, data_ligacao) " +
" ORDER BY DATEPART(DAY, data_ligacao)";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
while (dr1.Read())
{
CallHoras call = new CallHoras();
call.Horadodia = dr1.GetInt32(0);
call.TotalCall = dr1.GetInt32(1);
horas.Add(call.Horadodia.ToString());
total.Add(call.TotalCall);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
Chart _chart = new Chart();
_chart.labels = horas.ToArray();
_chart.datasets = new List <Datasets>();
List <Datasets> _dataSet = new List <Datasets>();
_dataSet.Add(new Datasets()
{
label = "Evolução de Ligações durante o Mês",
data = total.ToArray(),
backgroundColor = new string[] { "rgba(38, 185, 154, 0.31)" },
borderColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointBorderColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointBackgroundColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointHoverBackgroundColor = new string[] { "#fff" },
pointHoverBorderColor = new string[] { "rgba(220,220,220,1)" },
pointBorderWidth = "1",
borderWidth = "1"
});
_chart.datasets = _dataSet;
//O JavaScriptSerializer vai fazer o web service retornar JSON
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(_chart));
}
public string ChamadasEsteMes()
{
var horas = new List <string>();
var total = new List <int>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["gtaConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT DATEPART(DAY, data_ligacao) AS [Day], COALESCE(COUNT(id_consulta), 0) AS [Total Calls] " +
" FROM [hspmCall].[dbo].[ativo_ligacao] " +
" WHERE data_ligacao between (CONVERT(datetime, (DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) - 0, 103)) AND (CONVERT(datetime, GETDATE(), 103)) " +
" GROUP BY DATEPART(DAY, data_ligacao) " +
" ORDER BY DATEPART(DAY, data_ligacao)";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
while (dr1.Read())
{
CallHoras call = new CallHoras();
call.Horadodia = dr1.GetInt32(0);
call.TotalCall = dr1.GetInt32(1);
horas.Add(call.Horadodia.ToString());
total.Add(call.TotalCall);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
Chart _chart = new Chart();
_chart.labels = horas.ToArray();
_chart.datasets = new List <Datasets>();
List <Datasets> _dataSet = new List <Datasets>();
_dataSet.Add(new Datasets()
{
label = "Ativos Este Mês",
data = total.ToArray(),
backgroundColor = new string[] { "rgba(38, 185, 154, 0.31)" },
borderColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointBorderColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointBackgroundColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointHoverBackgroundColor = new string[] { "#fff" },
pointHoverBorderColor = new string[] { "rgba(220,220,220,1)" },
pointBorderWidth = "1",
borderWidth = "1"
});
_chart.datasets = _dataSet;
//O JavaScriptSerializer vai fazer o web service retornar JSON
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(_chart));
}