// 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)); }