public string QuantidadeBeProcedencia(string mesAno)
{
int mes = Convert.ToInt32(mesAno.Substring(0, 2));
int ano = Convert.ToInt32(mesAno.Substring(3, 4));
var dados = new List <BeStatus>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["psConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT " +
" CASE " +
" WHEN procedencia != 'Espontânea' THEN 'Resgate' " +
" ELSE procedencia " +
" END AS Procedencia , " +
" COUNT(*) as qtd, " +
" CAST(COUNT(procedencia) * 100.0 /(SELECT COUNT(*) AS Expr1 FROM [hspmPs].[dbo].[ficha] WHERE (MONTH(dt_hr_be) = " + mes + ") AND (YEAR(dt_hr_be) = " + ano + ") AND status_ficha != 8 AND status_ficha != 4 ) AS decimal(5, 2)) AS porcentagem " +
" FROM [hspmPs].[dbo].[ficha] " +
" WHERE status_ficha != 8 AND status_ficha != 4 " +
" AND MONTH(dt_hr_be) = " + mes + " and YEAR(dt_hr_be) = " + ano +
" group by CASE " +
" WHEN procedencia != 'Espontânea' THEN 'Resgate' " +
" ELSE procedencia " +
" END ";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
//char[] ponto = { '.', ' ' };
while (dr1.Read())
{
BeStatus call = new BeStatus();
call.descricao = dr1.GetString(0);
call.quantidade = dr1.GetInt32(1);
call.porcentagem = Convert.ToString(dr1.GetDecimal(2)) + "%";
dados.Add(call);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(dados));
}
public string QuantidadeBeStatus(string mesAno)
{
int mes = Convert.ToInt32(mesAno.Substring(0, 2));
int ano = Convert.ToInt32(mesAno.Substring(3, 4));
var dados = new List <BeStatus>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["psConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT COUNT(s.descricao_status) AS qtd_status, s.descricao_status, " +
" cast((count(f.status_ficha)*100.0)/(select COUNT(*) FROM hspmPs.dbo.ficha f INNER JOIN hspmPs.dbo.status_ficha s ON f.status_ficha = s.cod_status " +
" WHERE MONTH(f.dt_hr_be) =" + mes + " and YEAR(f.dt_hr_be) = " + ano + ")as decimal(5,2)) as porcentagem " +
" FROM hspmPs.dbo.ficha f INNER JOIN hspmPs.dbo.status_ficha s ON f.status_ficha = s.cod_status " +
" WHERE MONTH(f.dt_hr_be) = " + mes + " and YEAR(f.dt_hr_be) = " + ano + " " +
" GROUP BY s.descricao_status " +
" ORDER BY qtd_status DESC";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
//char[] ponto = { '.', ' ' };
while (dr1.Read())
{
BeStatus call = new BeStatus();
call.quantidade = dr1.GetInt32(0);
call.descricao = dr1.GetString(1);
call.porcentagem = Convert.ToString(dr1.GetDecimal(2)) + "%";
dados.Add(call);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
return(js.Serialize(dados));
}
public string QuantidadeBeStatusGrafico(string mesAno)
{
int mes = Convert.ToInt32(mesAno.Substring(0, 2));
int ano = Convert.ToInt32(mesAno.Substring(3, 4));
var dados = new List <BeStatus>();
var quantidade1 = new List <int>();
var descricao1 = new List <string>();
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["psConnectionString"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT count(f.status_ficha) as qtd_status, s.descricao_status " +
" FROM [hspmPs].[dbo].[ficha] f, [hspmPs].[dbo].[status_ficha] s " +
" WHERE s.cod_status = f.status_ficha " +
" AND MONTH(f.dt_hr_be) = " + mes + " and YEAR(f.dt_hr_be) = " + ano +
" GROUP BY s.descricao_status";
try
{
cnn.Open();
SqlDataReader dr1 = cmm.ExecuteReader();
//char[] ponto = { '.', ' ' };
while (dr1.Read())
{
BeStatus be = new BeStatus();
be.quantidade = dr1.GetInt32(0);
be.descricao = dr1.GetString(1) + " - " + dr1.GetInt32(0);
quantidade1.Add(be.quantidade);
descricao1.Add(be.descricao.ToString());
//dados.Add(call);
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
Chart _chart = new Chart();
_chart.labels = descricao1.ToArray();
_chart.datasets = new List <Datasets>();
List <Datasets> _dataSet = new List <Datasets>();
_dataSet.Add(new Datasets()
{
label = "Total do Mês",
data = quantidade1.ToArray(),
backgroundColor = new string[] { "rgba(38, 185, 154, 0.31)",
"rgba(0, 255, 255)",
"rgba(0, 128, 255)",
"rgba(0, 0, 255)",
"rgba(0,100,0)",
"rgba(0,255,0)",
"rgba(143,188,143)",
"rgba(102,205,170)",
"rgba(0,128,128)",
"rgba(0,0,0)",
"rgba(224,255,255)",
"rgba(106,90,205)",
"rgba(128,0,128)" },
borderColor = new string[] { "rgba(38, 185, 154, 0.7)",
"rgba(0, 255, 255)",
"rgba(0, 128, 255)",
"rgba(0, 0, 255)",
"rgba(0,100,0)",
"rgba(0,255,0)",
"rgba(143,188,143)",
"rgba(102,205,170)",
"rgba(0,128,128)",
"rgba(0,0,0)",
"rgba(224,255,255)",
"rgba(106,90,205)",
"rgba(128,0,128)" },
pointHoverBackgroundColor = new string[] { "#fff" },
pointHoverBorderColor = new string[] { "rgba(220,220,220,1)" },
pointBorderColor = new string[] { "rgba(38, 185, 154, 0.7)" },
pointBackgroundColor = new string[] { "rgba(38, 185, 154, 0.7)" }
});
_chart.datasets = _dataSet;
//O JavaScriptSerializer vai fazer o web service retornar JSON
JavaScriptSerializer js = new JavaScriptSerializer();
//return js.Serialize(dados);
return(js.Serialize(_chart));
}
