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