public ActionResult Index(ReqCatalogo reqCatalogo)
        {
            Catalogo catalogo = new Catalogo();
            SqlDataReader reader = null;
            SqlConnection myConnection = new SqlConnection();
            try
            {
                myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["BaseComercial"].ConnectionString;
                myConnection.Open();
                List<Categoria> categorias= new List<Categoria>();
                List<Articulo> articulos = new List<Articulo>();
                List<Modificador> modificadores = new List<Modificador>();
                try//EL SIGUEINTE BLOQUE ES PARA EXTRAER LAS CATEGORIAS
                {
                    SqlCommand command = new SqlCommand("SELECT * FROM AGRUPACION_CAT WHERE AGRU_PADRE = 0;", myConnection);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Categoria categoria = new Categoria();

                        List<Modificador> listaModificadores = new List<Modificador>();
                        categoria.agru_des = reader["agru_des"].ToString();
                        categoria.agru_desc = reader["agru_desc"].ToString();
                        categoria.agru_id = Convert.ToInt32(reader["agru_id"].ToString());
                        categoria.agru_padre = Convert.ToInt32(reader["agru_padre"].ToString());
                        categoria.agru_tipo = Convert.ToInt32(reader["agru_tipo"].ToString());
                        if(categoria.agru_tipo == 1){
                            categoria.hasSubCat = true;
                            //SqlCommand commandFindSub = new SqlCommand("SELECT * FROM AGRUPACION_CAT WHERE AGRU_TIPO = 1 AND AGRU_PADRE = "+categoria.agru_padre, myConnection);
                            SqlCommand commandFindSub = new SqlCommand("SELECT * FROM AGRUPACION_CAT WHERE AGRU_PADRE = " + categoria.agru_id, myConnection);
                            SqlDataReader readerSub = commandFindSub.ExecuteReader();
                            List<SubCategoria> listasubCateg = new List<SubCategoria>();
                            while(readerSub.Read()){
                                SubCategoria subCat = new SubCategoria();
                                subCat.agru_des = readerSub["agru_des"].ToString();
                                subCat.agru_desc = readerSub["agru_desc"].ToString();
                                subCat.agru_id = Convert.ToInt32(readerSub["agru_id"].ToString());
                                subCat.agru_padre = Convert.ToInt32(readerSub["agru_padre"].ToString());
                                subCat.agru_tipo = Convert.ToInt32(readerSub["agru_tipo"].ToString());
                                SqlCommand commandFindSubArt = new SqlCommand("SELECT A.AGRU_ID, A.ART_DES, A.ART_DESC, A.ART_EAN, A.TIPP_ID, B.ART_PRECIO FROM ARTICULO A, ARTICULO_PRECIO B WHERE  A.AGRU_ID = "+subCat.agru_id +" AND B.ART_EAN = A.ART_EAN AND B.SUCC_ID ="+reqCatalogo.succ_id, myConnection);
                                SqlDataReader readerSubArt = commandFindSubArt.ExecuteReader();
                                List<Articulo> listArticulos = new List<Articulo>();
                                while(readerSubArt.Read()){
                                    Articulo articulo = new Articulo();
                                    articulo.agru_id = Convert.ToInt32(readerSubArt["agru_id"].ToString());
                                    articulo.art_des = readerSubArt["art_des"].ToString();
                                    articulo.art_desc = readerSubArt["art_desc"].ToString();
                                    articulo.art_ean = Convert.ToDecimal(readerSubArt["art_ean"].ToString());
                                    articulo.art_precio = Convert.ToDecimal(readerSubArt["art_precio"].ToString());
                                    articulo.tipp_id = Convert.ToInt32(readerSubArt["tipp_id"].ToString());
                                    listArticulos.Add(articulo);
                                }//fin while busqueda Articulos por Casa SubCategoria
                                subCat.articulos = listArticulos;
                                SqlCommand commandFindModificadores = new SqlCommand("SELECT * FROM AGRUPACION_MODIF WHERE AGRU_ID = "+subCat.agru_id, myConnection);
                                SqlDataReader readerModif = commandFindModificadores.ExecuteReader();
                                while(readerModif.Read()){
                                    Modificador modificador = new Modificador();
                                    modificador.agru_consec = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                    modificador.agru_des = readerModif["agru_consec"].ToString();
                                    modificador.agru_desc = readerModif["agru_consec"].ToString();
                                    modificador.agru_id = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                    listaModificadores.Add(modificador);
                                }
                                subCat.modificadores = listaModificadores;
                                listasubCateg.Add(subCat);
                            }//Fin Busqueda SubCategorias
                            categoria.subCat = listasubCateg;
                        }else if(categoria.agru_tipo == 2){
                            categoria.hasSubCat = false;
                            SqlCommand commandFindArt = new SqlCommand("SELECT A.AGRU_ID, A.ART_DES, A.ART_DESC, A.ART_EAN, A.TIPP_ID, B.ART_PRECIO FROM ARTICULO A, ARTICULO_PRECIO B WHERE  A.AGRU_ID = " + categoria.agru_id + " AND B.ART_EAN = A.ART_EAN AND B.SUCC_ID =" + reqCatalogo.succ_id, myConnection);
                            SqlDataReader readerArt = commandFindArt.ExecuteReader();
                            List<Articulo> listArticulos = new List<Articulo>();
                            while (readerArt.Read())
                            {
                                Articulo articulo = new Articulo();
                                articulo.agru_id = Convert.ToInt32(readerArt["agru_id"].ToString());
                                articulo.art_des = readerArt["art_des"].ToString();
                                articulo.art_desc = readerArt["art_desc"].ToString();
                                articulo.art_ean = Convert.ToDecimal(readerArt["art_ean"].ToString());
                                articulo.art_precio = Convert.ToDecimal(readerArt["art_precio"].ToString());
                                articulo.tipp_id = Convert.ToInt32(readerArt["tipp_id"].ToString());
                                listArticulos.Add(articulo);
                            }//fin while busqueda Articulos por Casa SubCategoria
                            categoria.articulos = listArticulos;
                            SqlCommand commandFindModificadores = new SqlCommand("SELECT * FROM AGRUPACION_MODIF WHERE AGRU_ID = "+categoria.agru_id , myConnection);
                            SqlDataReader readerModif = commandFindModificadores.ExecuteReader();
                            while (readerModif.Read())
                            {
                                Modificador modificador = new Modificador();
                                modificador.agru_consec = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                modificador.agru_des = readerModif["agru_consec"].ToString();
                                modificador.agru_desc = readerModif["agru_consec"].ToString();
                                modificador.agru_id = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                listaModificadores.Add(modificador);
                            }//FIN while musca modificador
                            categoria.modificadores = listaModificadores;
                        }
                        categorias.Add(categoria);
                    }
                    catalogo.catalogo = categorias;
                }
                catch (SqlException exCat)
                {
                    catalogo.success = false;
                    catalogo.message = "ERROR 122" + exCat.Message;
                    return Json(catalogo);
                }
                /*
                //EL SIGUIENTE BLOQUE BUSCA LOS ARTICULOS Y SUS PRECIOS DE ACUERDO A LA SUCURSAL CORRESPONDIENTE
                try {
                    SqlCommand command = new SqlCommand("SELECT *   ", myConnection);// ? <- Query con join
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Articulo articulo = new Articulo();
                        articulo.agru_id = Convert.ToInt32(reader["agru_id"].ToString());
                        articulo.art_des = reader["art_des"].ToString();
                        articulo.art_desc = reader["art_desc"].ToString();
                        articulo.art_ean = Convert.ToDecimal(reader["art_ean"].ToString());
                        articulo.art_precio = Convert.ToDecimal(reader["art_precio"].ToString());
                        articulos.Add(articulo); //A la lista agrega el articulo recuperado durante este bucle del while
                    }//Fin while DataReader buscando Articulo
                    catalogo.articulos = articulos;//Agrega al objeto pre-json el list de los articulos recuperados
                }catch(SqlException exArt){
                    catalogo.success = false;
                    catalogo.message = "ERROR " + exArt.Message;
                    return Json(catalogo);
                }//Fin try-catch buscando Artículo
                //EL SIGUIENTE BLOQUE RECUPERA LOS MODIFICADORES REGISTRADOS EN LA BD
                try {
                    SqlCommand command = new SqlCommand("SELECT *  FROM AGRUPACION_MODIF", myConnection);// ? <- Query con join
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Modificador modificador = new Modificador();
                        modificador.agru_consec = Convert.ToInt32(reader["art_des"].ToString());
                        modificador.agru_des = reader["art_des"].ToString();
                        modificador.agru_desc = reader["art_des"].ToString();
                        modificador.agru_id = Convert.ToInt32(reader["art_des"].ToString());
                        modificadores.Add(modificador); //A la lista agrega el modificador recuperado durante este bucle del while
                    }//fin while DataReader buscando Modificadores
                    catalogo.modificadores = modificadores;//Agregar al objeto pre-json el Listo de Modificadores
                }catch (SqlException exModif) {
                    catalogo.success = false;
                    catalogo.message = "ERROR " + exModif.Message;
                    return Json(catalogo);
                }//fin try-catch buscando Modificadores
                 */
            }catch(Exception e){
                catalogo.success = false;//En caso de caer en exception el estado del boleano se envia en falso y en message el detalle del error
                catalogo.message = "ERROR 168 " + e.Message;
                return Json(catalogo);
            }finally{
                myConnection.Close();//Cerrando dentro del finally la conexión realizada a la base de datos
            }//fin Bloque completo de Try
            catalogo.success = true;//Completando el objeto json con estado true y mensaje OK
            catalogo.message = "OK";
            return Json(catalogo);
        }
        public ActionResult getCatalogo(ReqCatalogo reqCatalogo)
        {
            log.Info("Solicitando el Catalogo");
            Catalogo catalogo = new Catalogo();
            SqlDataReader reader = null;
            SqlConnection myConnection = new SqlConnection();
            try
            {
                myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["BaseComercial"].ConnectionString;
                myConnection.Open();
                List<Categoria> categorias = new List<Categoria>();
                List<Articulo> articulos = new List<Articulo>();
                List<Modificador> modificadores = new List<Modificador>();
                try//EL SIGUEINTE BLOQUE ES PARA EXTRAER LAS CATEGORIAS
                {
                    //SqlCommand command = new SqlCommand("SELECT A.* FROM AGRUPACION_CAT A, RESTAURANT_AGRUP B WHERE B.REST_ID = " + reqCatalogo.rest_id + " AND A.AGRU_PADRE = 0 AND A.AGRU_ID=B.AGRU_ID", myConnection);
                    SqlCommand command = new SqlCommand("SELECT A.* FROM AGRUPACION_CAT A WHERE A.AGRU_PADRE = 0", myConnection);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Categoria categoria = new Categoria();
                        List<Modificador> listaModificadores = new List<Modificador>();
                        categoria.agru_des = reader["agru_des"].ToString();
                        categoria.agru_desc = reader["agru_desc"].ToString();
                        categoria.agru_id = Convert.ToInt32(reader["agru_id"].ToString());
                        categoria.agru_padre = Convert.ToInt32(reader["agru_padre"].ToString());
                        categoria.agru_tipo = Convert.ToInt32(reader["agru_tipo"].ToString());
                        if (categoria.agru_tipo == 1)
                        {
                            categoria.hasSubCat = true;
                            //SqlCommand commandFindSub = new SqlCommand("SELECT * FROM AGRUPACION_CAT WHERE AGRU_TIPO = 1 AND AGRU_PADRE = "+categoria.agru_padre, myConnection);
                            //SqlCommand commandFindSub = new SqlCommand("SELECT * FROM AGRUPACION_CAT WHERE AGRU_PADRE = " + categoria.agru_id, myConnection);
                            //SqlCommand commandFindSub = new SqlCommand("SELECT A.* FROM AGRUPACION_CAT A, RESTAURANT_AGRUP B WHERE B.REST_ID = " + reqCatalogo.rest_id + " AND A.AGRU_PADRE = "+ categoria.agru_id+" AND A.AGRU_ID=B.AGRU_ID" , myConnection);
                            SqlCommand commandFindSub = new SqlCommand("SELECT A.* FROM AGRUPACION_CAT A WHERE A.AGRU_PADRE = " + categoria.agru_id , myConnection);

                            SqlDataReader readerSub = commandFindSub.ExecuteReader();
                            List<SubCategoria> listasubCateg = new List<SubCategoria>();
                            while (readerSub.Read())
                            {
                                SubCategoria subCat = new SubCategoria();
                                subCat.agru_des = readerSub["agru_des"].ToString();
                                subCat.agru_desc = readerSub["agru_desc"].ToString();
                                subCat.agru_id = Convert.ToInt32(readerSub["agru_id"].ToString());
                                subCat.agru_padre = Convert.ToInt32(readerSub["agru_padre"].ToString());
                                subCat.agru_tipo = Convert.ToInt32(readerSub["agru_tipo"].ToString());
                                SqlCommand commandFindSubArt = new SqlCommand("SELECT A.AGRU_ID, A.ART_DES, A.ART_DESC, A.ART_EAN, A.TIPP_ID, B.ART_PRECIO FROM ARTICULO A, ARTICULO_PRECIO B WHERE  A.AGRU_ID = " + subCat.agru_id + " AND B.ART_EAN = A.ART_EAN AND B.SUCC_ID =" + reqCatalogo.succ_id, myConnection);
                                SqlDataReader readerSubArt = commandFindSubArt.ExecuteReader();
                                List<Articulo> listArticulos = new List<Articulo>();
                                while (readerSubArt.Read())
                                {
                                    Articulo articulo = new Articulo();
                                    articulo.agru_id = Convert.ToInt32(readerSubArt["agru_id"].ToString());
                                    articulo.art_des = readerSubArt["art_des"].ToString();
                                    articulo.art_desc = readerSubArt["art_desc"].ToString();
                                    articulo.art_ean = Convert.ToDecimal(readerSubArt["art_ean"].ToString());
                                    articulo.art_precio = Convert.ToDecimal(readerSubArt["art_precio"].ToString());
                                    articulo.tipp_id = Convert.ToInt32(readerSubArt["tipp_id"].ToString());
                                    listArticulos.Add(articulo);
                                }//fin while busqueda Articulos por Casa SubCategoria
                                subCat.articulos = listArticulos;
                                SqlCommand commandFindModificadores = new SqlCommand("SELECT * FROM AGRUPACION_MODIF WHERE AGRU_ID = " + subCat.agru_id, myConnection);
                                SqlDataReader readerModif = commandFindModificadores.ExecuteReader();
                                while (readerModif.Read())
                                {
                                    Modificador modificador = new Modificador();
                                    modificador.agru_consec = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                    modificador.agru_des = readerModif["agru_des"].ToString();
                                    modificador.agru_desc = readerModif["agru_desc"].ToString();
                                    modificador.agru_id = Convert.ToInt32(readerModif["agru_id"].ToString());
                                    listaModificadores.Add(modificador);
                                }
                                subCat.modificadores = listaModificadores;
                                //categoria.modificadores = listaModificadores;
                                listasubCateg.Add(subCat);
                            }//Fin Busqueda SubCategorias
                            categoria.subCat = listasubCateg;
                        }
                        else if (categoria.agru_tipo == 2)
                        {
                            categoria.hasSubCat = false;
                            SqlCommand commandFindArt = new SqlCommand("SELECT A.AGRU_ID, A.ART_DES, A.ART_DESC, A.ART_EAN, A.TIPP_ID, B.ART_PRECIO FROM ARTICULO A, ARTICULO_PRECIO B WHERE  A.AGRU_ID = " + categoria.agru_id + " AND B.ART_EAN = A.ART_EAN AND B.SUCC_ID =" + reqCatalogo.succ_id, myConnection);
                            SqlDataReader readerArt = commandFindArt.ExecuteReader();
                            List<Articulo> listArticulos = new List<Articulo>();
                            while (readerArt.Read())
                            {
                                Articulo articulo = new Articulo();
                                articulo.agru_id = Convert.ToInt32(readerArt["agru_id"].ToString());
                                articulo.art_des = readerArt["art_des"].ToString();
                                articulo.art_desc = readerArt["art_desc"].ToString();
                                articulo.art_ean = Convert.ToDecimal(readerArt["art_ean"].ToString());
                                articulo.art_precio = Convert.ToDecimal(readerArt["art_precio"].ToString());
                                articulo.tipp_id = Convert.ToInt32(readerArt["tipp_id"].ToString());
                                listArticulos.Add(articulo);
                            }//fin while busqueda Articulos por Casa SubCategoria
                            categoria.articulos = listArticulos;
                            SqlCommand commandFindModificadores = new SqlCommand("SELECT * FROM AGRUPACION_MODIF WHERE AGRU_ID = " + categoria.agru_id, myConnection);
                            SqlDataReader readerModif = commandFindModificadores.ExecuteReader();
                            while (readerModif.Read())
                            {
                                Modificador modificador = new Modificador();
                                modificador.agru_consec = Convert.ToInt32(readerModif["agru_consec"].ToString());
                                modificador.agru_des = readerModif["agru_des"].ToString();
                                modificador.agru_desc = readerModif["agru_desc"].ToString();
                                modificador.agru_id = Convert.ToInt32(readerModif["agru_id"].ToString());
                                listaModificadores.Add(modificador);
                            }//FIN while musca modificador
                            categoria.modificadores = listaModificadores;
                        }//end else
                        categorias.Add(categoria);
                    }//end while
                    catalogo.catalogo = categorias;
                }
                catch (SqlException exCat)
                {
                    log.Error("Error Solicitando el catalogo en SQLException" + exCat.Message);
                    catalogo.success = false;
                    catalogo.message = "ERROR 122" + exCat.Message;
                    return Json(catalogo);
                }
            }
            catch (Exception e)
            {
                log.Error("Error Solicitando el catalogo en " + e.Message);
                catalogo.success = false;//En caso de caer en exception el estado del boleano se envia en falso y en message el detalle del error
                catalogo.message = "ERROR 168 " + e.Message;
                return Json(catalogo);
            }
            finally
            {
                myConnection.Close();//Cerrando dentro del finally la conexión realizada a la base de datos
            }//fin Bloque completo de Try
            catalogo.success = true;//Completando el objeto json con estado true y mensaje OK
            catalogo.message = "OK";
            return Json(catalogo);
        }