public ActionResult DetalhadoView(int id, int idIndicador, string data1, string data2)
{
var views = new ViewsNome();
var dataAtual = DateTime.Now;
ViewBag.data1 = String.IsNullOrEmpty(data1) ? new DateTime(dataAtual.Year, dataAtual.Month, 1).ToString() : data1;
ViewBag.data2 = String.IsNullOrEmpty(data2) ? new DateTime(dataAtual.Year, dataAtual.Month, 1).AddMonths(1).AddDays(-1).ToString() : data2;
string query = "SELECT Tipo,object_id Id,upper(NAME) Nome FROM ";
query += "(SELECT 'view' Tipo,object_id,name FROM SYS.views ";
query += "union ";
query += "SELECT 'procedure' Tipo,object_id,name FROM SYS.procedures) as p ";
query += "WHERE object_id = '" + id + "' ";
views = db.Database.SqlQuery <ViewsNome>(query).First();
views.Indicador = db.T_Indicadores.Find(idIndicador);
if (views.Tipo == "view")
{
views.Campos = db.Database.SqlQuery <ViewsCampos>("SELECT name Nome FROM sys.columns where object_id = " + id.ToString()).ToList();
}
else
{
views.Campos = Util.QueryAnaliser.GetCamposProcedure(views.Nome);
}
views.Valores = new string[0, 0];
db.Database.CommandTimeout = 999;
if ((data1 != "" && data1 != null) && (data2 != "" && data2 != null))
{
views.Valores = QueryAnaliser.GetValores(views.Tipo, views.Nome, data1, data2);
}
views.Nome = views.Nome.Replace("VW_SGI_" + idIndicador.ToString() + "_", "").Replace("_", " ");
//select list itens
return(View(views));
}
public ActionResult ExportarExcel(int id, int idIndicador, string data1, string data2)
{
var views = new ViewsNome();
var dataAtual = DateTime.Now;
ViewBag.data1 = String.IsNullOrEmpty(data1) ? new DateTime(dataAtual.Year, dataAtual.Month, 1).ToString() : data1;
ViewBag.data2 = String.IsNullOrEmpty(data2) ? new DateTime(dataAtual.Year, dataAtual.Month, 1).AddMonths(1).AddDays(-1).ToString() : data2;
string query = "SELECT Tipo,object_id Id,upper(NAME) Nome FROM ";
query += "(SELECT 'view' Tipo,object_id,name FROM SYS.views ";
query += "union ";
query += "SELECT 'procedure' Tipo,object_id,name FROM SYS.procedures) as p ";
query += "WHERE object_id = '" + id + "' ";
views = db.Database.SqlQuery <ViewsNome>(query).First();
views.Indicador = db.T_Indicadores.Find(idIndicador);
if (views.Tipo == "view")
{
views.Campos = db.Database.SqlQuery <ViewsCampos>("SELECT name Nome FROM sys.columns where object_id = " + id.ToString()).ToList();
}
else
{
views.Campos = Util.QueryAnaliser.GetCamposProcedure(views.Nome);
}
views.Valores = new string[0, 0];
if ((data1 != "" && data1 != null) && (data2 != "" && data2 != null))
{
views.Valores = QueryAnaliser.GetValores(views.Tipo, views.Nome, data1, data2);
}
views.Nome = views.Nome.Replace("VW_SGI_" + idIndicador.ToString() + "_", "").Replace("_", " ");
var products = new System.Data.DataTable(views.Nome);
//Adiciona colunas
for (int i = 0; i < views.Campos.Count; i++)
{
products.Columns.Add(views.Campos[i].Nome, typeof(string));
}
//Preenche valores
string[] valores;
for (int ln = 0; ln < views.Valores.GetLength(0); ln++)
{
valores = new string[views.Campos.Count];
for (int col = 0; col < views.Campos.Count; col++)
{
valores[col] = views.Valores[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=" + views.Nome + 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());
}