public ActionResult RealizarConsultaGenHtml(string vistasql, int id)
{
if (vistasql != "")
{
appreportes _appreportes = new appreportes();
System.Web.Script.Serialization.JavaScriptSerializer jSearializer = new System.Web.Script.Serialization.JavaScriptSerializer();
string sqlQuery = string.Empty;
CSN_BPGlobal _CSN_BPGlobal = new CSN_BPGlobal();
sqlQuery = "SELECT * FROM " + vistasql + " WHERE id='" + id + "'";
SqlDataReader _reader = _CSN_BPGlobal.GetCommandReader(sqlQuery, System.Data.CommandType.Text);
DataTable table = new DataTable();
//convierto un datareader aun datatable
table.Load(_reader);
//List<DataRow> list = table.AsEnumerable().ToList();
//return Json(jSearializer.Serialize(list), JsonRequestBehavior.AllowGet);
//List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
//Dictionary<string, object> row = null;
//foreach (DataRow dr in table.Rows)
//{
// row = new Dictionary<string, object>();
// foreach (DataColumn col in table.Columns)
// {
// row.Add(col.ColumnName, dr[col]);
// }
// rows.Add(row);
//}
_appreportes.QueryString = sqlQuery;
List<string> valuesQuery = new List<string>();
MyViewModel modelQuery = new MyViewModel();
modelQuery.Rows = new List<RowViewModel>();
modelQuery.Columns = new List<ColumnViewModel>();
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnViewModel col = new ColumnViewModel();
col.Name = table.Columns[i].ColumnName;
modelQuery.Columns.Add(col);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
List<CellValueViewModel> listValues = new List<CellValueViewModel>();
for (int j = 0; j < table.Columns.Count; j++)
{
CellValueViewModel cel = new CellValueViewModel();
cel.Value = row[j].ToString();
listValues.Add(cel);
}
RowViewModel _RowViewModel = new RowViewModel();
_RowViewModel.Values = listValues;
modelQuery.Rows.Add(_RowViewModel);
}
_appreportes.QueryDataRows = modelQuery;
return PartialView("_DinamicQuery", _appreportes);
//return PartialView("_DinamicQuery", _RequestServiceClient.GetRequestDinamic(listParameters, dicRequest, srRequestName.Replace(" ", "").ToString()));
//return Json(jSearializer.Serialize(rows), JsonRequestBehavior.AllowGet);
}
else
{
return null;
}
}
public ActionResult EjecutaReporteSinParametros(int id)
{
appreportes _appreportes = new appreportesRepository().GetappreportesById(id);
_appreportes.NombreOpcionReporte = _appreportes.NombreOpcionReporte;
try
{
string select = string.Empty;
string where = string.Empty;
string order = string.Empty;
string gropuBy = string.Empty;
bool isGrouBy = false;
int iCountFields = 1;
if (_appreportes.appReport_Fields.Count == 0)
select = "*";
foreach (appReport_Fields item in _appreportes.appReport_Fields)
{
if (iCountFields < _appreportes.appReport_Fields.Count)
{
if ((item.Funcion != null ? item.Funcion.Trim() : item.Funcion) == "Agrupar por")
{
select += item.Campo.Trim() + ",";
}
else
{
select += (item.Funcion == null ? item.Campo.Trim() + "," : item.Funcion.Trim() + "(" + item.Campo.Trim() + ") as " + item.Funcion + "De" + item.Campo + ",");
}
}
else
{
if ((item.Funcion != null ? item.Funcion.Trim() : item.Funcion) == "Agrupar por")
{
select += item.Campo.Trim();
}
else
{
select += (item.Funcion == null ? item.Campo.Trim() : item.Funcion.Trim() + "(" + item.Campo.Trim() + ")as " + item.Funcion + "De@" + item.Campo);
}
}
if (isGrouBy == false)
isGrouBy = (item.Funcion != null ? true : false);
iCountFields++;
}
iCountFields = 1;
List<appReport_Fields> listFieldWhitoutFunction = _appreportes.appReport_Fields.Where(e => e.Funcion == null || e.Funcion.Trim() == "Agrupar por").ToList();
iCountFields = 1;
foreach (appReport_Order item in _appreportes.appReport_Order)
{
if (iCountFields < _appreportes.appReport_Order.Count)
order = order + item.Campo.Trim() + " " + item.Tipo_orden.Trim() + ",";
else
order = order + item.Campo.Trim() + " " + item.Tipo_orden.Trim();
if (select != "*")
{
appReport_Fields _appReport_Fields = _appreportes.appReport_Fields.SingleOrDefault(e => e.Campo == item.Campo);
if (_appReport_Fields == null)
{
select = select + " ," + item.Campo;
}
}
iCountFields++;
}
CSN_BPGlobal _CSN_BPGlobal = new CSN_BPGlobal();
iCountFields = 0;
//Valida si la vista tiene campo id_centro
string srCentros = string.Empty;
int iCoutnPos = 0;
if (_appreportes.Id_centro == null)
{
appReport_Fields _appReport_where = _appreportes.appReport_Fields.SingleOrDefault(e => e.Campo == "Id_centro");
string joined = string.Empty;
//Carga centros por role de usuario
List<Centros> listcentros = new CentrosRepository().GetCentrosByUser(this.User.Identity.Name).ToList();
string[] arrIdCentros = new string[listcentros.Count()];
foreach (Centros item in listcentros)
{
srCentros += item.Id_centro;
arrIdCentros[iCoutnPos] = item.Id_centro.ToString();
iCoutnPos++;
}
joined = String.Join(", ", arrIdCentros);
where = "Id_centro in(" + joined + ")";
}
else
where = "Id_centro= " + _appreportes.Id_centro;
//Construye group by
List<appReport_Fields> listFieldsGroupBy = null;
if (isGrouBy)
{
listFieldsGroupBy = _appreportes.appReport_Fields.Where(e => e.Funcion == null || e.Funcion == "Agrupar por").ToList();
string[] arrFieldsGroupBy = new string[listFieldsGroupBy.Count()];
iCoutnPos = 0;
foreach (appReport_Fields item in listFieldsGroupBy)
{
arrFieldsGroupBy[iCoutnPos] = item.Campo.ToString();
iCoutnPos++;
}
joined = String.Join(", ", arrFieldsGroupBy);
gropuBy = joined;
}
string sqlQuery = string.Empty;
if (isGrouBy == false)
{
if (select != string.Empty && where != string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} order by {3}", select, _appreportes.Nombre_vista, where, order);
if (select != string.Empty && where == string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} ", select, _appreportes.Nombre_vista);
if (select != string.Empty && where != string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2}", select, _appreportes.Nombre_vista, where);
if (select != string.Empty && where == string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} order by {2}", select, _appreportes.Nombre_vista, order);
}
else
{
if (select != string.Empty && where != string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} group by {4} order by {3}", select, _appreportes.Nombre_vista, where, order, gropuBy);
if (select != string.Empty && where == string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} group by {2} ", select, _appreportes.Nombre_vista, gropuBy);
if (select != string.Empty && where != string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} group by {3}", select, _appreportes.Nombre_vista, where, gropuBy);
if (select != string.Empty && where == string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} group by {3} order by {2}", select, _appreportes.Nombre_vista, order, gropuBy);
}
SqlDataReader _reader = _CSN_BPGlobal.GetCommandReader(sqlQuery, System.Data.CommandType.Text);
DataTable table = new DataTable();
//convierto un datareader aun datatable
table.Load(_reader);
_appreportes.QueryString = sqlQuery;
List<string> valuesQuery = new List<string>();
MyViewModel modelQuery = new MyViewModel();
modelQuery.Rows = new List<RowViewModel>();
modelQuery.Columns = new List<ColumnViewModel>();
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnViewModel col = new ColumnViewModel();
col.Name = table.Columns[i].ColumnName;
col.ColIndex = i;
string[] campoFuncion = table.Columns[i].ColumnName.Split(new char[] { '@' });
appReport_Fields _appReport_Fields = null;
if (campoFuncion.Length == 1)
_appReport_Fields = new appreportesRepository().GetappreportesById(_appreportes.Id_app_reporte).
appReport_Fields.FirstOrDefault(e => e.Campo == table.Columns[i].ColumnName);
else
_appReport_Fields = new appreportesRepository().GetappreportesById(_appreportes.Id_app_reporte).
appReport_Fields.FirstOrDefault(e => e.Campo == campoFuncion[1]);
if (_appReport_Fields != null)
if (_appReport_Fields.VerTotal != null)
col.VerTotal = (int)_appReport_Fields.VerTotal;
else
col.VerTotal = 0;
else
col.VerTotal = 0;
modelQuery.Columns.Add(col);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
List<CellValueViewModel> listValues = new List<CellValueViewModel>();
for (int j = 0; j < table.Columns.Count; j++)
{
CellValueViewModel cel = new CellValueViewModel();
cel.Value = row[j].ToString();
cel.ColumnIndex = j;
listValues.Add(cel);
}
RowViewModel _RowViewModel = new RowViewModel();
_RowViewModel.Values = listValues;
modelQuery.Rows.Add(_RowViewModel);
}
_appreportes.QueryDataRows = modelQuery;
return View("_ReportView", _appreportes);
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
return RedirectToAction("Edit", "appreportes", new { id = _appreportes.Id_app_reporte });
}
}
public JsonResult GetFieldsByView(string id)
{
try
{
string select = "SELECT TOP(1) * FROM " + id;
CSN_BPGlobal _CSN_BPGlobal = new CSN_BPGlobal();
SqlDataReader _reader = _CSN_BPGlobal.GetCommandReader(select, System.Data.CommandType.Text);
List<string> valuesQuery = new List<string>();
MyViewModel modelQuery = new MyViewModel();
modelQuery.Rows = new List<RowViewModel>();
DataTable table = new DataTable();
//convierto un datareader aun datatable
table.Load(_reader);
modelQuery.Columns = new List<ColumnViewModel>();
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnViewModel col = new ColumnViewModel();
col.Name = table.Columns[i].ColumnName;
modelQuery.Columns.Add(col);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
List<CellValueViewModel> listValues = new List<CellValueViewModel>();
for (int j = 0; j < table.Columns.Count; j++)
{
CellValueViewModel cel = new CellValueViewModel();
cel.Value = row[j].ToString();
listValues.Add(cel);
}
RowViewModel _RowViewModel = new RowViewModel();
_RowViewModel.Values = listValues;
modelQuery.Rows.Add(_RowViewModel);
}
var modelData = modelQuery.Columns.OrderBy(e => e.Name).Select(m => new SelectListItem()
{
Text = m.Name,
Value = m.Name
});
return Json(modelData, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
return Json(JsonResponseFactory.ErrorResponse(ex.Message), JsonRequestBehavior.AllowGet);
}
}
public ActionResult EjecutaReporte(appreportes model, FormCollection parameters)
{
appreportes _appreportes = new appreportesRepository().GetappreportesById(model.Id_app_reporte);
string sqlQuery = string.Empty;
try
{
string select = string.Empty;
string where = string.Empty;
string order = string.Empty;
string gropuBy = string.Empty;
bool isGrouBy = false;
int iCountFields = 1;
if (_appreportes.appReport_Fields.Count == 0)
select = "*";
foreach (appReport_Fields item in _appreportes.appReport_Fields)
{
if (iCountFields < _appreportes.appReport_Fields.Count)
{
if ((item.Funcion != null ? item.Funcion.Trim() : item.Funcion) == "Agrupar por")
{
select += item.Campo.Trim() + ",";
}
else
{
select += (item.Funcion == null ? item.Campo.Trim() + "," : item.Funcion.Trim() + "(" + item.Campo.Trim() + ") as '" + item.Funcion + "De" + item.Campo + "',");
}
}
else
{
if ((item.Funcion != null ? item.Funcion.Trim() : item.Funcion) == "Agrupar por")
{
select += item.Campo.Trim();
}
else
{
select += (item.Funcion == null ? item.Campo.Trim() : item.Funcion.Trim() + "(" + item.Campo.Trim() + ")as '" + item.Funcion + "De@" + item.Campo + "'");
}
}
if (isGrouBy == false)
isGrouBy = (item.Funcion != null ? true : false);
iCountFields++;
}
iCountFields = 1;
List<appReport_Fields> listFieldWhitoutFunction = _appreportes.appReport_Fields.Where(e => e.Funcion == null || e.Funcion.Trim() == "Agrupar por").ToList();
iCountFields = 1;
foreach (appReport_Order item in _appreportes.appReport_Order)
{
if (iCountFields < _appreportes.appReport_Order.Count)
order = order + item.Campo.Trim() + " " + item.Tipo_orden.Trim() + ",";
else
order = order + item.Campo.Trim() + " " + item.Tipo_orden.Trim();
if (select != "*")
{
appReport_Fields _appReport_Fields = _appreportes.appReport_Fields.SingleOrDefault(e => e.Campo == item.Campo);
if (_appReport_Fields == null)
{
select = select + " ," + item.Campo;
}
}
iCountFields++;
}
CSN_BPGlobal _CSN_BPGlobal = new CSN_BPGlobal();
iCountFields = 0;
foreach (appReport_where item in _appreportes.appReport_where)
{
item.Ultimo_valor = parameters[item.Campo].Trim().Replace(",", "");
string lastChar = parameters[item.Campo].Trim().Substring(parameters[item.Campo].Trim().Length - 1, 1);
if (lastChar == ",")
parameters[item.Campo] = parameters[item.Campo].Trim().Substring(0, parameters[item.Campo].Trim().Length - 1);
new appReport_whereRepository().EditWhere(item);
if (item.TipoParametro != "D")
{
if (item.Condicion.Trim() != "IN")
{
if (parameters[item.Campo].Trim().Replace(",", "") == "Nulos")
{
if (iCountFields == 0)
where += "ISNULL(" + item.Campo.Trim() + ",'')=''";
else
where += item.Operacion.Trim() + " " + "ISNULL(" + item.Campo.Trim() + ",'')=''";
}
else
if (parameters[item.Campo].Trim().Replace(",", "") == "Todos")
{
if (iCountFields == 0)
where += "ISNULL(" + item.Campo.Trim() + ",'')<>''";
else
where += item.Operacion.Trim() + " " + "ISNULL(" + item.Campo.Trim() + ",'')<>''";
}
else
if (iCountFields == 0)
where += item.Campo.Trim() + " " + item.Condicion.Trim() + " '" + parameters[item.Campo].Trim() + "'";
else
where += item.Operacion.Trim() + " " + item.Campo.Trim() + " " + item.Condicion.Trim() + " '" + parameters[item.Campo].Trim() + "'";
}
else
{
if (iCountFields == 0)
where += item.Campo.Trim() + " " + item.Condicion.Trim() + " (" + parameters["campos_" + item.Campo].Trim() + ")";
else
where += item.Operacion.Trim() + " " + item.Campo.Trim() + " " + item.Condicion.Trim() + " (" + parameters["campos_" + item.Campo].Trim() + ")";
}
}
else
{
string formatoFecha = System.Configuration.ConfigurationManager.AppSettings["FormatoFecha"];
StringBuilder strbuilder = new StringBuilder();
strbuilder.AppendFormat(formatoFecha, Convert.ToDateTime(parameters[item.Campo]));
string nameString = strbuilder.ToString();
StringBuilder strbuilderFecha2 = new StringBuilder();
strbuilderFecha2.AppendFormat(formatoFecha, Convert.ToDateTime(parameters[item.Campo + "2"]));
string nameStringFecha2 = strbuilderFecha2.ToString();
if (iCountFields == 0)
{
if (item.Condicion.Trim() == "Between")
{
where += item.Campo.Trim() + " " + item.Condicion.Trim() + "'" + nameString.Trim() + "'" + " and " + "'" + nameStringFecha2.Trim() + "'";
}
else
where += item.Campo.Trim() + " " + item.Condicion.Trim() + " " + " '" + nameString.Trim() + "'";
}
else
if (item.Condicion.Trim() == "Between")
{
where += item.Operacion.Trim() + " " + item.Campo.Trim() + " " + item.Condicion.Trim() + "'" + nameString.Trim() + "'" + " and " + "'" + nameStringFecha2.Trim() + "'";
}
else
where += item.Operacion.Trim() + " " + item.Campo.Trim() + " " + item.Condicion.Trim() + " '" + nameString.Trim() + "'";
}
iCountFields++;
}
//Valida si la vista tiene campo id_centro
string srCentros = string.Empty;
int iCoutnPos = 0;
appReport_Fields _appReport_where = _appreportes.appReport_Fields.SingleOrDefault(e => e.Campo == "Id_centro");
string joined = string.Empty;
if (_appreportes.Id_centro == null)
{
if (_appReport_where != null)
{
if (_appreportes.appReport_where.Count > 0)
{
//Carga centros por role de usuario
List<Centros> listcentros = new CentrosRepository().GetCentrosByUser(this.User.Identity.Name).ToList();
string[] arrIdCentros = new string[listcentros.Count()];
foreach (Centros item in listcentros)
{
srCentros += item.Id_centro;
arrIdCentros[iCoutnPos] = item.Id_centro.ToString();
iCoutnPos++;
}
joined = String.Join(", ", arrIdCentros);
where += "AND Id_centro in(" + joined + ")";
}
else
where = "Id_centro in(" + joined + ")";
}
}
else
{
if (where != "")
where += " AND Id_centro = " + _appreportes.Id_centro;
else
where += "Id_centro = " + _appreportes.Id_centro;
}
//Construye group by
List<appReport_Fields> listFieldsGroupBy = null;
if (isGrouBy)
{
listFieldsGroupBy = _appreportes.appReport_Fields.Where(e => e.Funcion == null || e.Funcion == "Agrupar por" || e.Funcion == "dbo.date_format").ToList();
string[] arrFieldsGroupBy = new string[listFieldsGroupBy.Count()];
iCoutnPos = 0;
foreach (appReport_Fields item in listFieldsGroupBy)
{
arrFieldsGroupBy[iCoutnPos] = item.Campo.ToString();
iCoutnPos++;
}
joined = String.Join(", ", arrFieldsGroupBy);
gropuBy = joined;
}
if (isGrouBy == false)
{
if (select != string.Empty && where != string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} order by {3}", select, _appreportes.Nombre_vista, where, order);
if (select != string.Empty && where == string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} ", select, _appreportes.Nombre_vista);
if (select != string.Empty && where != string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2}", select, _appreportes.Nombre_vista, where);
if (select != string.Empty && where == string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} order by {2}", select, _appreportes.Nombre_vista, order);
}
else
{
if (select != string.Empty && where != string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} group by {4} order by {3}", select, _appreportes.Nombre_vista, where, order, gropuBy);
if (select != string.Empty && where == string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} group by {2} ", select, _appreportes.Nombre_vista, gropuBy);
if (select != string.Empty && where != string.Empty && order == string.Empty)
sqlQuery = string.Format("select {0} from {1} where {2} group by {3}", select, _appreportes.Nombre_vista, where, gropuBy);
if (select != string.Empty && where == string.Empty && order != string.Empty)
sqlQuery = string.Format("select {0} from {1} group by {3} order by {2}", select, _appreportes.Nombre_vista, order, gropuBy);
}
SqlDataReader _reader = _CSN_BPGlobal.GetCommandReader(sqlQuery, System.Data.CommandType.Text);
DataTable table = new DataTable();
//convierto un datareader aun datatable
table.Load(_reader);
_appreportes.QueryString = sqlQuery;
List<string> valuesQuery = new List<string>();
MyViewModel modelQuery = new MyViewModel();
modelQuery.Rows = new List<RowViewModel>();
modelQuery.ArrayString = new List<string[]>();
modelQuery.Columns = new List<ColumnViewModel>();
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnViewModel col = new ColumnViewModel();
col.Name = table.Columns[i].ColumnName;
col.ColIndex = i;
string[] campoFuncion = table.Columns[i].ColumnName.Split(new char[] { '@' });
appReport_Fields _appReport_Fields = null;
if (campoFuncion.Length == 1)
_appReport_Fields = new appreportesRepository().GetappreportesById(model.Id_app_reporte).
appReport_Fields.FirstOrDefault(e => e.Campo == table.Columns[i].ColumnName);
else
_appReport_Fields = new appreportesRepository().GetappreportesById(model.Id_app_reporte).
appReport_Fields.FirstOrDefault(e => e.Campo == campoFuncion[1]);
if (_appReport_Fields != null)
if (_appReport_Fields.VerTotal != null)
col.VerTotal = (int)_appReport_Fields.VerTotal;
else
col.VerTotal = 0;
else
col.VerTotal = 0;
modelQuery.Columns.Add(col);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
List<CellValueViewModel> listValues = new List<CellValueViewModel>();
string[] stringArray = new string[table.Columns.Count];
for (int j = 0; j < table.Columns.Count; j++)
{
CellValueViewModel cel = new CellValueViewModel();
cel.Value = row[j].ToString();
cel.ColumnIndex = j;
listValues.Add(cel);
stringArray[j] = row[j].ToString();
}
RowViewModel _RowViewModel = new RowViewModel();
_RowViewModel.Values = listValues;
modelQuery.Rows.Add(_RowViewModel);
modelQuery.ArrayString.Add(stringArray);
}
_appreportes.QueryDataRows = modelQuery;
return PartialView("_ReportView", _appreportes);
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message + sqlQuery;
return PartialView("_ReportView", _appreportes);
}
}