public appreportes Edit(appreportes model)
{
entities.appreportes.Attach(model);
entities.ObjectStateManager.ChangeObjectState(model, EntityState.Modified);
this.Commit();
return model;
}
public ActionResult ActualizarListas(appreportes model, FormCollection par)
{
try
{
GuardaCambiosReporte(model);
TempData["DisplayMessage"] = String.Format("Los cambios se realizaron con éxito");
appreportes _appreportes = new appreportesRepository().GetappreportesById(model.Id_app_reporte);
//string opcion = par["Opcion"].ToString();
if (!returnToIndex)
if (_appreportes.appReport_where.Count > 0)
return RedirectToAction("CargaParametrosReporte", "appreportes", new { id = model.Id_app_reporte, parameters = par });
else
return RedirectToAction("EjecutaReporteSinParametros", "appreportes", new { id = model.Id_app_reporte });
else
{
returnToIndex = false;
appopciones _appopciones = new appopcionesRepository().GetappopcionesByName(PublicNombreOpcion);
ViewData["IdOpcion"] = _appopciones.Id_opcion;
ViewData["NombreOpcion"] = PublicTituloOpcion;
ViewData["srNombreVista"] = _appreportes.Nombre_vista;
List<appreportes> _Listappreportes = appreportesService.GetReportByViewData(_appreportes.Nombre_vista);
//return View("Index", _Listappreportes);
if (par["iOption"] == "1")
{
return RedirectToAction("Edit", "appreportes", new { id = _appreportes.Id_app_reporte, TituloOpcion = PublicTituloOpcion });
}
else
{
return View("Index", _Listappreportes);
}
}
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
return View();
}
}
public appreportes GetappreportesById(string srNameControles)
{
//appopciones opciones = entities.appopciones.Single(e => e.Nombre == srNameControles);
appopciones opciones = (from opcion in entities.appopciones
where opcion.Nombre == srNameControles
select opcion).FirstOrDefault();
appreportes miTipoAccion = new appreportes();
if (opciones != null)
{
miTipoAccion = (from c in entities.appreportes
where c.Id_opcion == opciones.Id_opcion
select c).FirstOrDefault();
if (miTipoAccion == null)
return null;
}
return miTipoAccion;
}
public appreportes Create(appreportes model)
{
entities.appreportes.AddObject(model);
this.Commit();
return model;
}
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 JsonResult GetCetrosByEmpresa(int id, appreportes model)
{
try
{
List<Centros> listCentros = new CentrosRepository().GetAllCentrosByEmpresa(this.User.Identity.Name, id).ToList();
var modelData = listCentros.Select(m => new SelectListItem()
{
Text = m.Nombre,
Value = m.Id_centro.ToString()
});
return Json(modelData, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
TempData["ErrorMessage"] = ex.Message;
return Json(JsonResponseFactory.ErrorResponse(ex.Message), JsonRequestBehavior.AllowGet);
}
}
public ActionResult Edit(appreportes model)
{
try
{
appreportesService.Edit(model);
return RedirectToAction("Index");
}
catch
{
return View(model);
}
}
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);
}
}
public ActionResult Create(string srViewDataName, int idOpcion, string TituloOpcion)
{
appreportes _appreportes = new appreportes();
_appreportes.Nombre_vista = srViewDataName;
_appreportes.selectedFiedls = "";
_appreportes.selectedFiedlsOrder = "";
_appreportes.selectedFiedlsWhere = "";
_appreportes.NombreOpcionReporte = TituloOpcion;
_appreportes.Id_opcion = idOpcion;
return View(_appreportes);
}
private void GuardaCambiosReporte(appreportes model)
{
appreportesRepository _appreportesRepository = new appreportesRepository();
if (model.Id_centro == 0)
model.Id_centro = null;
if (model.Id_app_reporte == 0)
_appreportesRepository.Create(model);
else
{
_appreportesRepository.Edit(model);
EditCurrentQuery(model);
}
string[] split = (model.selectedFiedls == null ? new string[0] : model.selectedFiedls.Split(new Char[] { ',' }));
string[] splitWhere = (model.selectedFiedlsWhere == null ? new string[0] : model.selectedFiedlsWhere.Split(new Char[] { ',' }));
string[] splitOrder = (model.selectedFiedlsOrder == null ? new string[0] : model.selectedFiedlsOrder.Split(new Char[] { ',' }));
///Guarda los campos de la instrucci[on select
for (int i = 0; i < split.Length; i++)
{
int start = split[i].IndexOf("(");
int end = split[i].IndexOf(")");
string result = string.Empty;
string srfuncion = string.Empty;
if (start != -1 && end != -1)
{
result = split[i].Substring(start + 1, end - start - 1);
srfuncion = split[i].Substring(0, start);
}
appReport_Fields _appReport_Fields = new appReport_Fields();
_appReport_Fields.Id_app_reporte = model.Id_app_reporte;
if (result != string.Empty)
{
string[] split2 = split[i].Trim().Split(new Char[] { '|' });
if (split2.Length == 1)
{
_appReport_Fields.Campo = result.Trim();
_appReport_Fields.Funcion = srfuncion.Trim();
}
else
{
_appReport_Fields.Campo = result.Trim();
_appReport_Fields.Funcion = srfuncion.Trim();
_appReport_Fields.VerTotal = 1;
}
}
else
{
string[] split2 = split[i].Trim().Split(new Char[] { '|' });
if (split2.Length == 1)
_appReport_Fields.Campo = split[i].Trim();
else
{
_appReport_Fields.Campo = split2[0].Trim();
_appReport_Fields.VerTotal = 1;
}
}
_appReport_Fields.Orden = i + 1;
model.appReport_Fields.Add(_appReport_Fields);
}
List<appReport_Fields> campoIdCentro = model.appReport_Fields.Where(e => e.Campo == "Id_centro").ToList();
//if (campoIdCentro.Count() == 0)
//{
// if (model.tieneCentro == true)
// {
// model.appReport_Fields.Add(
// new appReport_Fields
// {
// Campo = "Id_centro"
// });
// }
//}
///Guarda los campos condicionales
for (int i = 0; i < splitWhere.Length; i++)
{
appReport_where _appReport_where = new appReport_where();
_appReport_where.Id_app_reporte = model.Id_app_reporte;
string[] splitWhereField = splitWhere[i].Split(new Char[] { '.' });
_appReport_where.Campo = splitWhereField[1].Trim();
_appReport_where.Condicion = splitWhereField[2].Trim();
_appReport_where.TipoParametro = splitWhereField[3].Trim();
_appReport_where.Operacion = splitWhereField[0].Trim();
if (splitWhereField.Length == 5)
_appReport_where.Ultimo_valor = splitWhereField[4].Trim();
model.appReport_where.Add(_appReport_where);
}
for (int i = 0; i < splitOrder.Length; i++)
{
appReport_Order _appReport_Order = new appReport_Order();
_appReport_Order.Id_app_reporte = model.Id_app_reporte;
string[] splitOrderField = splitOrder[i].Trim().Split(new Char[] { ' ' });
_appReport_Order.Campo = splitOrderField[0].Trim();
if (splitOrderField.Length > 1)
_appReport_Order.Tipo_orden = splitOrderField[1].Trim();
else
_appReport_Order.Tipo_orden = "ASC";
_appReport_Order.Orden = i + 1;
model.appReport_Order.Add(_appReport_Order);
appReport_Fields _appReport_Fields2 = model.appReport_Fields.SingleOrDefault(e => e.Campo == _appReport_Order.Campo);
if (_appReport_Fields2 == null)
{
appReport_Fields _appReport_Fields = new appReport_Fields();
_appReport_Fields.Campo = splitOrderField[0].Trim();
int countFields = 0;
if (model.appReport_Fields != null)
countFields = model.appReport_Fields.Count() + 1;
else
countFields = 1;
_appReport_Fields.Orden = countFields;
model.appReport_Fields.Add(_appReport_Fields);
}
}
_appreportesRepository.Edit(model);
_appreportesRepository.Commit();
_appReport_whereRepository.Commit();
_appReport_OrderRepository.Commit();
_appReport_FieldsRepository.Commit();
}
private void EditCurrentQuery(appreportes model)
{
string[] split = (model.selectedFiedls == null ? new string[0] : model.selectedFiedls.Split(new Char[] { ',' }));
string[] splitWhere = (model.selectedFiedlsWhere == null ? new string[0] : model.selectedFiedlsWhere.Split(new Char[] { ',' }));
string[] splitOrder = (model.selectedFiedlsOrder == null ? new string[0] : model.selectedFiedlsOrder.Split(new Char[] { ',' }));
List<appReport_Fields> listField = model.appReport_Fields.ToList();
List<appReport_where> listWhere = model.appReport_where.ToList();
List<appReport_Order> listOrder = model.appReport_Order.ToList();
foreach (appReport_Fields item in listField)
{
appReport_Fields _appReport_Fields = new appReport_Fields();
_appReport_Fields = model.appReport_Fields.SingleOrDefault(e => e.Campo == item.Campo);
if (_appReport_Fields != null)
model.appReport_Fields.Remove(_appReport_Fields);
_appReport_FieldsRepository.Delete(_appReport_Fields.Id_report_field);
}
foreach (appReport_where item in listWhere)
{
appReport_where _appReport_where = new appReport_where();
_appReport_where = model.appReport_where.SingleOrDefault(e => e.Campo == item.Campo);
if (_appReport_where != null)
model.appReport_where.Remove(_appReport_where);
_appReport_whereRepository.Delete(_appReport_where.Id_report_where);
}
foreach (appReport_Order item in listOrder)
{
appReport_Order _appReport_Order = new appReport_Order();
_appReport_Order = model.appReport_Order.SingleOrDefault(e => e.Campo == item.Campo);
if (_appReport_Order != null)
model.appReport_Order.Remove(_appReport_Order);
_appReport_OrderRepository.Delete(_appReport_Order.Id_report_order);
}
}
/// <summary>
/// Deprecated Method for adding a new object to the appreportes EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead.
/// </summary>
public void AddToappreportes(appreportes appreportes)
{
base.AddObject("appreportes", appreportes);
}
/// <summary>
/// Create a new appreportes object.
/// </summary>
/// <param name="id_app_reporte">Initial value of the Id_app_reporte property.</param>
public static appreportes Createappreportes(global::System.Int32 id_app_reporte)
{
appreportes appreportes = new appreportes();
appreportes.Id_app_reporte = id_app_reporte;
return appreportes;
}
