//
public JsonResult SequelPost(string s)
{
string sret = string.Empty;
sret = "ok";
try
{
SQLController sqlcontroller = new SQLController();
DataTable data = sqlcontroller.ExecutaSQL(s);
List <string> lista_colunas = new List <string>();
List <ListaTable> lista_rows = new List <ListaTable>();
//
if (data != null && data.Columns != null && data.Columns.Count > 0)
{
foreach (var coluna in data.Columns)
{
lista_colunas.Add(coluna.ToString());
}
}
//
if (data != null && data.Rows != null && data.Rows.Count > 0)
{
foreach (DataRow row in data.Rows)
{
ListaTable item_lista = new ListaTable();
//
foreach (var item_array in row.ItemArray)
{
item_lista.row.Add(item_array.ToString());
}
//
lista_rows.Add(item_lista);
}
}
//
Session["sequel_colunas"] = lista_colunas;
Session["sequel_dados"] = lista_rows;
}
catch (Exception exc)
{
sret = exc.Message;
}
//
return(Json(new { data = sret, results = 1, success = true }, JsonRequestBehavior.AllowGet));
}
public JsonResult RelatorioTemperaturaGetDados(int idempresa, string lista_ids, string periodo, string horaini, string horafim)
{
List <Object> lista = new List <Object>();
DateTime data_filtro_ini = new DateTime();
DateTime data_filtro_fim = new DateTime();
string[] lista_ids_maquinas = lista_ids.Split(',');
//
string maq = string.Empty;
string sreult = string.Empty;
string sFileName = string.Empty;
Empresa empresaLista = null;
//
List <EmpresaRelatorioModel> lista_empresa_model = new List <EmpresaRelatorioModel>();
List <ColetorAlerta> lista_coletoralerta_model = new List <ColetorAlerta>();
List <ColetorAlertaLog> lista_coletor_alerta_log = new List <ColetorAlertaLog>();
//
//List<ColetorAlertaModel> lista_coletor_alerta_item_model = new List<ColetorAlertaModel>();
List <ColetorAlertaLogModel> lista_coletor_alerta_log_model = new List <ColetorAlertaLogModel>();
List <MaquinaItensReportModel> lista_maquina_itens_report_model = new List <MaquinaItensReportModel>();
//
empresaLista = db.Empresa.Where(x => x.Id == idempresa).FirstOrDefault();
//
if (!string.IsNullOrEmpty(periodo))
{
string[] datas_filro = periodo.Split('-');
string[] data_um = datas_filro[0].ToString().Split('/');
string[] data_dois = datas_filro[1].ToString().Split('/');
//
if (!string.IsNullOrEmpty(horaini) && horaini.Contains(":"))
{
data_filtro_ini = new DateTime(Convert.ToInt32(data_um[2]),
Convert.ToInt32(data_um[0]),
Convert.ToInt32(data_um[1]),
Convert.ToInt32(horaini.Split(':')[0]),
Convert.ToInt32(horaini.Split(':')[1]), 1);
}
else
{
data_filtro_ini = new DateTime(Convert.ToInt32(data_um[2]), Convert.ToInt32(data_um[0]), Convert.ToInt32(data_um[1]), 0, 0, 1);
}
//
if (!string.IsNullOrEmpty(horaini) && horafim.Contains(":"))
{
data_filtro_fim = new DateTime(Convert.ToInt32(data_dois[2]),
Convert.ToInt32(data_dois[0]),
Convert.ToInt32(data_dois[1]),
Convert.ToInt32(horafim.Split(':')[0]),
Convert.ToInt32(horafim.Split(':')[1]), 59);
}
else
{
data_filtro_fim = new DateTime(Convert.ToInt32(data_dois[2]), Convert.ToInt32(data_dois[0]), Convert.ToInt32(data_dois[1]), 23, 59, 59);
}
}
//
try
{
for (int i = 0; i < lista_ids_maquinas.Length; i++)
{
int idmaquina = Convert.ToInt32(lista_ids_maquinas[i]);
Coletor coletor = db.Coletor.Where(x => x.Maquina != null && x.Maquina.ID == idmaquina).FirstOrDefault();
//
if (coletor != null)
{
MaquinaItensReportModel mirm = new MaquinaItensReportModel();
//
mirm.DescricaoColetor = coletor.Descricao;
mirm.DescricaoMaquina = coletor.Maquina.Descricao;
if (string.IsNullOrEmpty(maq))
{
maq = coletor.Maquina.Descricao;
}
else
{
maq = maq + " - " + coletor.Maquina.Descricao;
}
//
lista_maquina_itens_report_model.Add(mirm);
//
lista_coletoralerta_model = db.ColetorAlerta.Where(x => x.Id_Coletor == coletor.Id).ToList();
//
string teste = @"
SELECT Cast(datahora AS DATE) AS 'Data',
DATENAME(weekday, Cast(datahora AS DATE)) AS 'DiaSemana',
Datepart(month, datahora) AS Mes,
Datepart(day, datahora) AS Dia,
Temperatura
FROM ColetorTemperaturaHistorico
WHERE Id_Coletor = 47
AND DataHora >= '2019-12-15 14:40:01.000'
AND DataHora <= '2019-12-31 14:40:01.000'
AND Temperatura IS NOT NULL
ORDER BY Datepart(month, datahora),
Datepart(day, datahora)
";
string sql_temp = @"SELECT Cast(datahora AS DATE) AS 'Data',
DATENAME(weekday, Cast(datahora AS DATE)) AS 'DiaSemana',
Datepart(month, datahora) AS Mes,
Datepart(day, datahora) AS Dia,
ROUND((Avg(Cast(temperatura AS FLOAT)) / 10 ), 2) AS 'TemperaturaMedia'
FROM ColetorTemperaturaHistorico
WHERE Id_Coletor = " + coletor.Id + @"
AND DataHora >= '" + data_filtro_ini.ToString("yyyy/MM/dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + @"'
AND DataHora <= '" + data_filtro_fim.ToString("yyyy/MM/dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + @"'
AND Temperatura IS NOT NULL
GROUP BY Cast(datahora AS DATE),
Datepart(day, datahora),
Datepart(month, datahora)
ORDER BY Datepart(month, datahora),
Datepart(day, datahora)";
//
SQLController sqlcontroller = new SQLController();
DataTable data_temp = sqlcontroller.ExecutaSQL(teste);
//
if (data_temp != null && data_temp.Rows.Count > 0)
{
double[] valoresY = new double[data_temp.Rows.Count];
string[] valoresX = new string[data_temp.Rows.Count];
//
for (int k = 0; k < data_temp.Rows.Count; k++)
{
valoresX[k] = Convert.ToString(data_temp.Rows[k]["Dia"]) + "/" + Convert.ToString(data_temp.Rows[k]["Mes"]);
valoresY[k] = Convert.ToDouble(data_temp.Rows[k]["Temperatura"]);
}
//
List <Object> lista_itens = new List <Object>();
lista_itens.Add(valoresX);
lista_itens.Add(valoresY);
lista_itens.Add(coletor.Maquina.Descricao);
//
lista.Add(lista_itens);
}
}
}
}
catch (Exception exc)
{
sFileName = "Erro: " + exc.Message;
sreult = "nok";
}
//
return(Json(new
{
data = "OK",
results = 1,
success = true,
errors = 0,
dados = lista,
maquinas = maq,
periodo = data_filtro_ini.ToShortDateString() + " " + data_filtro_ini.ToShortTimeString() + " - " + data_filtro_fim.ToShortDateString() + " " + data_filtro_fim.ToShortTimeString()
}, JsonRequestBehavior.AllowGet));
}
