public Resultquery PainelGestorOEEChart()
{
var queryResult = new Resultquery();
string[,] dados = new string[0, 0];
int cont = 0;
var cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PlayConect"].ConnectionString);
var cmd = new SqlCommand();
cmd.CommandText = " SELECT * FROM V_PAINEL_GESTOR_OEE WHERE PER_ID = 'M' AND LEFT(MED_DATAMEDICAO,6) = FORMAT( GETDATE(), 'yyyyMM','en-US') ";
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
cnn.Open();
try
{
cmd.CommandTimeout = 99999;
cmd.Connection = cnn;
DataSet ds = new DataSet();
DataAdapter data = new SqlDataAdapter(cmd);
data.Fill(ds);
queryResult.Id = 0;
queryResult.Titulo = "";
queryResult.Descricao = "";
queryResult.Tipo = "SQL";
queryResult.Qtdlinhas = ds.Tables[0].Columns.Count;
queryResult.Dados = new string[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
queryResult.Colunas = new List <Coluna>();
queryResult.Linhas = new List <LineData>();
//public List<Colunas> Colunas { get; set; }
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Coluna Col = new Coluna()
{
Nome = ds.Tables[0].Columns[i].ColumnName,
Tipo = ds.Tables[0].Columns[i].DataType.ToString()
};
queryResult.Colunas.Add(Col);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
LineData line = new LineData();
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
queryResult.Dados[cont, i] = row[i].ToString();
line.linha.Add(row[i].ToString());
}
queryResult.Linhas.Add(line);
cont++;
}
}
catch (Exception erro)
{
throw new Exception("Erro ao listar tipo embalagem: " + erro.Message);
}
finally
{
cnn.Close();
}
return(queryResult);
}
public ActionResult ListaConsultas(String sPesquisa)
{
var queryResult = new Resultquery();
string[,] dados = new string[0, 0];
int cont = 0;
var cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PlayConect"].ConnectionString);
var cmd = new SqlCommand();
cmd.CommandText = "SELECT CON_ID ID,CON_TITULO TITULO,CON_DESCRICAO DESCRICAO,CON_GRUPO GRUPO FROM T_CONSULTAS ";
if (!string.IsNullOrEmpty(sPesquisa))
{
cmd.CommandText = " SELECT CON_ID ID,CON_TITULO TITULO,CON_DESCRICAO DESCRICAO,CON_GRUPO GRUPO FROM T_CONSULTAS WHERE CON_DESCRICAO LIKE @PESQUISA OR CON_GRUPO LIKE @PESQUISA OR CON_ID LIKE @PESQUISA ";
cmd.Parameters.Add("@PESQUISA", SqlDbType.NVarChar).Value = "%" + sPesquisa + "%";
}
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
cnn.Open();
try
{
cmd.CommandTimeout = 99999;
cmd.Connection = cnn;
DataSet ds = new DataSet();
DataAdapter data = new SqlDataAdapter(cmd);
data.Fill(ds);
queryResult.Id = 0;
queryResult.Titulo = "";
queryResult.Descricao = "";
queryResult.Tipo = "SQL";
queryResult.Qtdlinhas = ds.Tables[0].Columns.Count;
queryResult.Dados = new string[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
queryResult.Colunas = new List <Coluna>();
//public List<Colunas> Colunas { get; set; }
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Coluna Col = new Coluna()
{
Nome = ds.Tables[0].Columns[i].ColumnName,
Tipo = ds.Tables[0].Columns[i].DataType.ToString()
};
queryResult.Colunas.Add(Col);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
queryResult.Dados[cont, i] = row[i].ToString();
}
cont++;
}
}
catch (Exception erro)
{
throw new Exception("Erro ao listar tipo embalagem: " + erro.Message);
}
finally
{
cnn.Close();
}
return(View(queryResult));
}
public ActionResult ExecutaConsultaGrafico(int indId, int dimensao, string periodo, string subDim, string dataIni, string dataFim, Boolean excel = true)
{
var queryResult = new Resultquery();
List <Parametro> aiparam = new List <Parametro>();
string sql = "";
string cipartemp = "";
bool bicopia = false;
int cont = 0;
bool lijatem = false;
int nipos = 0;
string cichar = "";
int niini = 0;
var conexao = "PlayConect";
excel = true;
var ind = db.Database.SqlQuery <IndicadorDimencao>("select * from T_INDICADORES_DIMENCOES WHERE IND_ID = " + indId + " AND DIM_ID = " + dimensao).ToList();
foreach (var item in ind)
{
sql = item.DIM_SQL.ToUpper();
}
sql = sql.Replace("SELECT", "");
int ifrom = 0;
int iwhere = 0;
for (int i = 0; i < sql.Length - 5; i++)
{
if (sql.Substring(i, 4).ToUpper() == "FROM")
{
ifrom = i;
iwhere = sql.Length + 1;
}
if (sql.Substring(i, 5).ToUpper() == "WHERE")
{
iwhere = i;
}
}
string campos = sql.Substring(0, ifrom - 1);
string tabela = sql.Substring(ifrom + 5, iwhere - 5 - ifrom);
string[] sql_split = campos.Split(',');
sql = "SELECT * FROM " + tabela;
string where = "";
//indId, dimensao, subDimensao, dataIni, dataFim, periodo
if (subDim != "")
{
where += " WHERE " + sql_split[0] + "'" + subDim + "'";
}
if (periodo.Trim() == "D")
{
if (where.Length > 0)
{
where += " AND ";
}
else
{
where += " WHERE ";
}
where += sql_split[2] + " BETWEEN '" + dataIni.Substring(6, 4) + dataIni.Substring(3, 2) + dataIni.Substring(0, 2) + "' AND '" + dataFim.Substring(6, 4) + dataFim.Substring(3, 2) + dataFim.Substring(0, 2) + "'";
}
// montagem da query por parametro
sql += where;
var cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[conexao].ConnectionString);
var cmd = new SqlCommand();
try
{
cmd.Connection = cnn;
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
cnn.Open();
cmd.CommandText = "USE " + cnn.Database;
cmd.ExecuteNonQuery();
cmd.CommandText = sql;
cmd.CommandTimeout = 99999;
DataSet ds = new DataSet();
DataAdapter data = new SqlDataAdapter(cmd);
data.Fill(ds);
queryResult.Id = 0;
queryResult.Titulo = "";
queryResult.Descricao = "";
queryResult.Tipo = "SQL";
queryResult.Qtdlinhas = ds.Tables[0].Columns.Count;
queryResult.Dados = new string[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
queryResult.Colunas = new List <Coluna>();
//public List<Colunas> Colunas { get; set; }
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Coluna Col = new Coluna()
{
Nome = ds.Tables[0].Columns[i].ColumnName,
Tipo = ds.Tables[0].Columns[i].DataType.ToString()
};
queryResult.Colunas.Add(Col);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
queryResult.Dados[cont, i] = row[i].ToString();
}
cont++;
}
}
catch (Exception erro)
{
throw new Exception("Erro ao listar tipo embalagem: " + erro.Message);
}
finally
{
cnn.Close();
}
queryResult.Parametros = aiparam;
if (excel)
{
var products = new System.Data.DataTable(queryResult.Titulo);
//Adiciona colunas
for (int i = 0; i < queryResult.Colunas.Count; i++)
{
products.Columns.Add(queryResult.Colunas[i].Nome, typeof(string));
}
//Preenche valores
string[] valores;
for (int ln = 0; ln < queryResult.Dados.GetLength(0); ln++)
{
valores = new string[queryResult.Colunas.Count];
for (int col = 0; col < queryResult.Colunas.Count; col++)
{
valores[col] = queryResult.Dados[ln, col];
}
products.Rows.Add(valores);
}
var grid = new GridView();
grid.DataSource = products;
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + queryResult.Titulo + DateTime.Now.Date.ToString("ddMMyyyy") + ".xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
return(View(queryResult));
}
public ActionResult ExecutaConsulta(int id, FormCollection paramforms)
{
var queryResult = new Resultquery();
List <Parametro> aiparam = new List <Parametro>();
string sql = "";
string cipartemp = "";
bool bicopia = false;
int cont = 0;
bool lijatem = false;
int nipos = 0;
string cichar = "";
int niini = 0;
bool excel = false;
string conexao = "";
var cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PlayConect"].ConnectionString);
var cmd = new SqlCommand();
cmd.CommandText = "SELECT CON_ID,CON_DESCRICAO,CON_GRUPO,CON_COMAND,CON_CONEXAO,CON_TITULO,CON_TIPO FROM T_CONSULTAS WHERE CON_ID = @ID ";
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
cmd.Connection = cnn;
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
cnn.Open();
try
{
// Montando parametros PARA FORMANURIO
if (paramforms.Count == 0)
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
queryResult.Id = (int)reader["CON_ID"];
queryResult.Titulo = reader["CON_TITULO"].ToString();
queryResult.Descricao = reader["CON_DESCRICAO"].ToString();
queryResult.Tipo = reader["CON_TIPO"].ToString();
queryResult.Qtdlinhas = 0;
//queryResult.Dados = new string[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
//queryResult.Colunas = new List<Coluna>();
sql = reader["CON_COMAND"].ToString();
conexao = reader["CON_CONEXAO"].ToString().Trim();
}
for (int i = 0; i < sql.Length; i++)
{
if (sql.Substring(i, 1) == "@")
{
cipartemp = "";
bicopia = true;
nipos = i + 1;
cichar = sql.Substring(nipos, 1);
niini = i;
while (cichar != "@")
{
nipos++;
cichar = sql.Substring(nipos, 1);
}
i = nipos + 1;
cipartemp = sql.Substring(niini, nipos - (niini) + 1);
lijatem = false;
foreach (Parametro p in aiparam)
{
if (p.Idfull == cipartemp)
{
lijatem = true;
}
}
if (!lijatem)
{
var aiparamtemp = cipartemp.Split('#');
// exemplo @Titulo# Tipo campo(c,d,dtos,i) # PARAMETRO_ID # valor defalt # Tabela de pesquisa#@
Parametro Pal = new Parametro()
{
Idfull = cipartemp,
Idform = aiparamtemp[2].Replace("@", ""),
Descricao = aiparamtemp[0].Replace("@", ""),
Tipo = aiparamtemp[1].Replace("@", ""),
Conteudo = aiparamtemp[3].Replace("@", "")
};
aiparam.Add(Pal);
}
}
}
}
else
{
/*foreach (KeyValuePair<int, String> item in paramforms)
* {
* //item.key
* //item.value
* }
* foreach (var key in paramforms.AllKeys)
* {
* var value = paramforms[key];
* }
*
*/
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
queryResult.Id = (int)reader["CON_ID"];
queryResult.Titulo = reader["CON_TITULO"].ToString();
queryResult.Descricao = reader["CON_DESCRICAO"].ToString();
queryResult.Tipo = reader["CON_TIPO"].ToString();
queryResult.Qtdlinhas = 0;
sql = reader["CON_COMAND"].ToString();
conexao = reader["CON_CONEXAO"].ToString().Trim();
}
string parfull = "";
string conteudo = "";
foreach (var key in paramforms.Keys)
{
if (key.ToString() == "EXCEL")
{
excel = true;
}
var value = paramforms[key.ToString()];
if (key.ToString().Substring(0, 4) == "REP_")
{
parfull = "FULL_" + key.ToString().Substring(4, key.ToString().Length - 4);
parfull = paramforms[parfull];
conteudo = paramforms[key.ToString()];
sql = sql.Replace(parfull, conteudo);
// exemplo @Pesquisar#C#PARAMETRO_ID#@
var aiparamtemp = parfull.Split('#');
Parametro Pal = new Parametro()
{
Idfull = parfull,
Idform = aiparamtemp[2].Replace("@", ""),
Descricao = aiparamtemp[0].Replace("@", ""),
Tipo = aiparamtemp[1].Replace("@", ""),
Conteudo = conteudo
};
aiparam.Add(Pal);
}
}
reader.Close();
var cnn2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[conexao].ConnectionString);
var cmd2 = new SqlCommand();
cmd2.Connection = cnn;
if (cnn2.State != ConnectionState.Closed)
{
cnn2.Close();
}
cnn2.Open();
cmd2.CommandText = "USE " + cnn2.Database;
cmd2.ExecuteNonQuery();
cmd2.CommandText = sql;
cmd2.CommandTimeout = 99999;
DataSet ds = new DataSet();
DataAdapter data = new SqlDataAdapter(cmd2);
data.Fill(ds);
queryResult.Id = 0;
queryResult.Titulo = "";
queryResult.Descricao = "";
queryResult.Tipo = "SQL";
queryResult.Qtdlinhas = ds.Tables[0].Columns.Count;
queryResult.Dados = new string[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
queryResult.Colunas = new List <Coluna>();
//public List<Colunas> Colunas { get; set; }
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
Coluna Col = new Coluna()
{
Nome = ds.Tables[0].Columns[i].ColumnName,
Tipo = ds.Tables[0].Columns[i].DataType.ToString()
};
queryResult.Colunas.Add(Col);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
queryResult.Dados[cont, i] = row[i].ToString();
}
cont++;
}
}
}
catch (Exception erro)
{
throw new Exception("Erro ao listar tipo embalagem: " + erro.Message);
}
finally
{
cnn.Close();
}
queryResult.Parametros = aiparam;
if (excel)
{
var products = new System.Data.DataTable(queryResult.Titulo);
//Adiciona colunas
for (int i = 0; i < queryResult.Colunas.Count; i++)
{
products.Columns.Add(queryResult.Colunas[i].Nome, typeof(string));
}
//Preenche valores
string[] valores;
for (int ln = 0; ln < queryResult.Dados.GetLength(0); ln++)
{
valores = new string[queryResult.Colunas.Count];
for (int col = 0; col < queryResult.Colunas.Count; col++)
{
valores[col] = queryResult.Dados[ln, col];
}
products.Rows.Add(valores);
}
var grid = new GridView();
grid.DataSource = products;
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + queryResult.Titulo + DateTime.Now.Date.ToString("ddMMyyyy") + ".xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
return(View(queryResult));
}
