//*******************************************************************************
        //NOMBRE_FUNCION:  Consultar_Tarifas_Giro
        //DESCRIPCION: Metodo que Consulta las cuentas congeladas con estatus de cobranza
        //PARAMETROS : 1.- Cls_Rpt_Cor_Cc_Reportes_Varios_Neogcio Datos, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 11/Abril/2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static DataTable Consultar_Tarifas_Giro(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            DataTable Dt_Consulta = new DataTable();
            String    Str_My_Sql  = "";

            try
            {
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                Str_My_Sql = "select *";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  from **********************************************************************************************************************************
                Str_My_Sql += " from Cat_Cor_Giros";

                Dt_Consulta = SqlHelper.ExecuteDataset(Cls_Constantes.Str_Conexion, CommandType.Text, Str_My_Sql).Tables[0];
            }
            catch (Exception Ex)
            {
                throw new Exception("Error: " + Ex.Message);
            }

            return(Dt_Consulta);
        }// fin de consulta
        }// fin del metodo

        //*******************************************************************************
        //NOMBRE_FUNCION:  Consultar_Tabla_Historicos_Montos_Pagos
        //DESCRIPCION: Metodo que los pagos realizados a la facturacion
        //PARAMETROS : 1.- Cls_Rpt_Cor_Cc_Reportes_Varios_Neogcio Datos, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 11/Abril/2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static DataTable Consultar_Tabla_Historicos_Volumenes(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            DataTable Dt_Consulta = new DataTable();

            String Str_My_Sql = "";

            try
            {
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                Str_My_Sql  = "select ";
                Str_My_Sql += " * ";


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  from **********************************************************************************************************************************
                Str_My_Sql += " from Ope_Cor_Plan_Volumenes";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  where **********************************************************************************************************************************
                Str_My_Sql += " where";
                Str_My_Sql += " Año = " + Datos.P_Anio;

                if (!String.IsNullOrEmpty(Datos.P_Giro_Id))
                {
                    Str_My_Sql += " and giro_Id = '" + Datos.P_Giro_Id + "'";
                }

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  **********************************************************************************************************************************

                Dt_Consulta = SqlHelper.ExecuteDataset(Cls_Constantes.Str_Conexion, CommandType.Text, Str_My_Sql).Tables[0];
            }
            catch (Exception Ex)
            {
                throw new Exception("Error: " + Ex.Message);
            }

            return(Dt_Consulta);
        }// fin de consulta
        }// fin de consulta

        //*******************************************************************************
        //NOMBRE_FUNCION:  Consultar_Volumenes
        //DESCRIPCION: Metodo que Consulta las cuentas congeladas con estatus de cobranza
        //PARAMETROS : 1.- Cls_Rpt_Cor_Cc_Reportes_Varios_Neogcio Datos, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 11/Abril/2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static DataTable Consultar_Volumenes_Pipa(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            DataTable Dt_Consulta = new DataTable();
            String    Str_My_Sql  = "";

            try
            {
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                Str_My_Sql  = "select ";
                Str_My_Sql += " isnull(isnull(sum(dd.Cantidad), 0) * isnull(cc.m3_pipa, 0) , 0) as M3";
                Str_My_Sql += ", MONTH(d.Fecha_Creo) as Mes";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  from **********************************************************************************************************************************
                Str_My_Sql += " from Cat_Cor_Conceptos_Cobros cc";
                Str_My_Sql += " join Ope_Cor_Diversos_Detalles dd on cc.Concepto_ID = dd.Concepto_ID";
                Str_My_Sql += " join Ope_Cor_Diversos d on d.No_Diverso = dd.No_Diverso";


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  where **********************************************************************************************************************************
                Str_My_Sql += " where";
                Str_My_Sql += " cc.Nombre like ('%pipa%') and d.estatus  ='PAGADO'";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  group by **********************************************************************************************************************************
                Str_My_Sql += " GROUP BY	";
                Str_My_Sql += "m3_pipa";
                Str_My_Sql += ", d.Fecha_Creo";

                Dt_Consulta = SqlHelper.ExecuteDataset(Cls_Constantes.Str_Conexion, CommandType.Text, Str_My_Sql).Tables[0];
            }
            catch (Exception Ex)
            {
                throw new Exception("Error: " + Ex.Message);
            }

            return(Dt_Consulta);
        }// fin de consulta
示例#4
0
        //*******************************************************************************
        //NOMBRE DE LA FUNCIÓN:Consultar_Informacion
        //DESCRIPCIÓN: Metodo que permite llenar el Grid con la informacion de la consulta
        //PARAMETROS:
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 07/Abril/2016
        //MODIFICO:
        //FECHA_MODIFICO:
        //CAUSA_MODIFICACIÓN:
        //*******************************************************************************
        public void Consultar_Informacion()
        {
            Cls_Rpt_Plan_Volumenes_Negocio Rs_Consulta = new Cls_Rpt_Plan_Volumenes_Negocio();
            DataTable Dt_Consulta = new DataTable();
            DataTable Dt_Tarifas  = new DataTable();
            DataTable Dt_Reporte  = new DataTable();
            DataTable Dt_Auxiliar = new DataTable();
            DataTable Dt_Resumen  = new DataTable();
            DataRow   Dr_Nuevo_Elemento;
            String    Str_Nombre_Mes = "";
            Dictionary <Int32, String> Dic_Meses;
            Double    Db_Total      = 0;
            Decimal   Dc_Total      = 0;
            DataTable Dt_Existencia = new DataTable();

            try
            {
                Dic_Meses          = Cls_Metodos_Generales.Crear_Diccionario_Meses();
                Rs_Consulta.P_Anio = DateTime.Now.Year;
                Rs_Consulta.P_Mes  = DateTime.Now.Month;
                Dt_Tarifas         = Rs_Consulta.Consultar_Tarifas_Giro();
                Dt_Reporte         = Crear_Tabla_Reporte();



                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  Se ingresan los encabezados para las tomas
                foreach (DataRow Registro in Dt_Tarifas.Rows)
                {
                    Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                    Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString();
                    Dr_Nuevo_Elemento["Id"]        = Registro["giro_id"].ToString();
                    Dr_Nuevo_Elemento["Accion"]    = 1;
                    Dr_Nuevo_Elemento["Concepto"]  = "Volumen facturado medido a " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")";


                    Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);
                }

                //   se consultan las tomas que se realizaron en el año de consulta
                Rs_Consulta.P_Estimado = "NO";
                Dt_Consulta            = Rs_Consulta.Consultar_Volumenes();


                foreach (DataRow Registro in Dt_Reporte.Rows)
                {
                    Registro.BeginEdit();

                    Db_Total = 0;

                    for (int Cont_For = 1; Cont_For <= 12; Cont_For++)
                    {
                        Str_Nombre_Mes = "";
                        if (Dic_Meses.ContainsKey(Cont_For) == true)
                        {
                            Str_Nombre_Mes = Dic_Meses[Cont_For];
                        }//    fin de la validacion del diccionario


                        Dc_Total = (from ord in Dt_Consulta.AsEnumerable()
                                    where ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() &&
                                    ord.Field <Int32>("bimestre") == Cont_For &&
                                    ord.Field <Int32>("anio") == Rs_Consulta.P_Anio
                                    select ord.Field <Decimal>("Consumo")
                                    ).Sum();

                        Registro[Str_Nombre_Mes] = Convert.ToDouble(Dc_Total);
                        Db_Total = Db_Total + Convert.ToDouble(Dc_Total);
                    }

                    Registro["Total"] = Db_Total;
                    Db_Total          = 0;

                    Registro.EndEdit();
                    Registro.AcceptChanges();
                }


                //  se borraran las tarifas id, ya que comenzara con el tercer proceso
                foreach (DataRow Registro_Reporte in Dt_Reporte.Rows)
                {
                    Registro_Reporte.BeginEdit();
                    Registro_Reporte["Tarifa_Id"] = "";
                    Registro_Reporte.EndEdit();
                    Registro_Reporte.AcceptChanges();
                }


                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  Se ingresan los encabezados para las tomas
                foreach (DataRow Registro in Dt_Tarifas.Rows)
                {
                    Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                    Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString();
                    Dr_Nuevo_Elemento["Id"]        = Registro["giro_id"].ToString();
                    Dr_Nuevo_Elemento["Accion"]    = 2;
                    Dr_Nuevo_Elemento["Concepto"]  = "Volumen facturado estimado " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")";


                    Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);
                }

                //   se consultan las tomas que se realizaron en el año de consulta
                Rs_Consulta.P_Estimado = "SI";
                Dt_Consulta            = Rs_Consulta.Consultar_Volumenes();


                foreach (DataRow Registro in Dt_Reporte.Rows)
                {
                    if (!String.IsNullOrEmpty(Registro["tarifa_Id"].ToString()))
                    {
                        Registro.BeginEdit();

                        Db_Total = 0;

                        for (int Cont_For = 1; Cont_For <= 12; Cont_For++)
                        {
                            Str_Nombre_Mes = "";
                            if (Dic_Meses.ContainsKey(Cont_For) == true)
                            {
                                Str_Nombre_Mes = Dic_Meses[Cont_For];
                            }//    fin de la validacion del diccionario


                            Dc_Total = (from ord in Dt_Consulta.AsEnumerable()
                                        where ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() &&
                                        ord.Field <Int32>("bimestre") == Cont_For &&
                                        ord.Field <Int32>("anio") == Rs_Consulta.P_Anio
                                        select ord.Field <Decimal>("Consumo")
                                        ).Sum();

                            Registro[Str_Nombre_Mes] = Convert.ToDouble(Dc_Total);
                            Db_Total = Db_Total + Convert.ToDouble(Dc_Total);
                        }

                        Registro["Total"] = Db_Total;
                        Db_Total          = 0;

                        Registro.EndEdit();
                        Registro.AcceptChanges();
                    }
                }

                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  Volumen de agua distribuido en pipas********************************************************************************************************************
                Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                Dr_Nuevo_Elemento["tarifa_Id"] = "X";
                Dr_Nuevo_Elemento["Id"]        = "PIPA";
                Dr_Nuevo_Elemento["Concepto"]  = "Volumen de agua distribuido en pipas";
                Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);

                Dt_Consulta.Clear();
                //Dt_Consulta = Rs_Consulta.Consultar_Volumenes_Pipa();


                foreach (DataRow Registro in Dt_Reporte.Rows)
                {
                    if (Registro["tarifa_id"] == "X")
                    {
                        Registro.BeginEdit();
                        Db_Total = 0;

                        Registro[Str_Nombre_Mes] = Convert.ToDouble(Dc_Total);
                        Db_Total = Db_Total + Convert.ToDouble(Dc_Total);


                        //  se recorreran los meses del año que se busca
                        for (int Cont_For = 1; Cont_For <= 12; Cont_For++)
                        {
                            Str_Nombre_Mes = "";
                            Dc_Total       = 0;

                            if (Dic_Meses.ContainsKey(Cont_For) == true)
                            {
                                Str_Nombre_Mes = Dic_Meses[Cont_For];
                            }//    fin de la validacion del diccionario


                            Dc_Total = (from ord in Dt_Consulta.AsEnumerable()
                                        where ord.Field <Int32>("mes") == Cont_For
                                        select ord.Field <Decimal>("M3")
                                        ).Sum();
                            Db_Total = Db_Total + Convert.ToDouble(Dc_Total);
                            Registro[Str_Nombre_Mes] = Dc_Total;
                        }

                        Registro["Total"] = Db_Total;
                        Registro.EndEdit();
                        Registro.AcceptChanges();
                    }
                }

                //  se borraran las tarifas id, ya que comenzara con el tercer proceso
                foreach (DataRow Registro_Reporte in Dt_Reporte.Rows)
                {
                    Registro_Reporte.BeginEdit();
                    Registro_Reporte["Tarifa_Id"] = "";
                    Registro_Reporte.EndEdit();
                    Registro_Reporte.AcceptChanges();
                }

                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  Volumen de agua distribuido en bebedero 1********************************************************************************************************************
                Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                Dr_Nuevo_Elemento["tarifa_Id"] = "X";
                Dr_Nuevo_Elemento["Id"]        = "BEB1";
                Dr_Nuevo_Elemento["Concepto"]  = "Volumen de agua distribuido en bebedero 1";
                Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);

                Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                Dr_Nuevo_Elemento["tarifa_Id"] = "X";
                Dr_Nuevo_Elemento["Id"]        = "BEB2";
                Dr_Nuevo_Elemento["Concepto"]  = "Volumen de agua distribuido en bebedero 2";
                Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);


                Dr_Nuevo_Elemento = Dt_Reporte.NewRow();
                Dr_Nuevo_Elemento["tarifa_Id"] = "X";
                Dr_Nuevo_Elemento["Id"]        = "BEB3";
                Dr_Nuevo_Elemento["Concepto"]  = "Volumen de agua distribuido en bebedero 3";
                Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento);


                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  llenan los espacios vacion con cero 0********************************************************************************************************************
                foreach (DataRow Registro_Reporte in Dt_Reporte.Rows)
                {
                    Registro_Reporte.BeginEdit();
                    Registro_Reporte["Tarifa_Id"] = "";

                    for (int Cont_For = 1; Cont_For <= 12; Cont_For++)
                    {
                        Str_Nombre_Mes = "";
                        if (Dic_Meses.ContainsKey(Cont_For) == true)
                        {
                            Str_Nombre_Mes = Dic_Meses[Cont_For];
                        }//    fin de la validacion del diccionario

                        if (String.IsNullOrEmpty(Registro_Reporte[Str_Nombre_Mes].ToString()))
                        {
                            Registro_Reporte[Str_Nombre_Mes] = 0;
                        }
                    }

                    if (String.IsNullOrEmpty(Registro_Reporte["Total"].ToString()))
                    {
                        Registro_Reporte["Total"] = 0;
                    }
                    Registro_Reporte.EndEdit();
                    Registro_Reporte.AcceptChanges();
                }


                //********************************************************************************************************************
                //********************************************************************************************************************
                //********************************************************************************************************************
                //  se ingresara la informacion

                //  se realizara la insercion de la informacion
                foreach (DataRow Registro in Dt_Reporte.Rows)
                {
                    Dt_Existencia.Clear();

                    Str_Nombre_Mes = "";
                    Str_Nombre_Mes = Dic_Meses[DateTime.Now.Month];
                    Rs_Consulta.P_Str_Nombre_Mes = Str_Nombre_Mes;
                    Rs_Consulta.P_Giro_Id        = Registro["id"].ToString();
                    Rs_Consulta.P_Anio           = DateTime.Now.Year;
                    Rs_Consulta.P_Dr_Registro    = Registro;
                    Rs_Consulta.P_Str_Usuario    = "Servicio";

                    if (!String.IsNullOrEmpty(Registro["Accion"].ToString()))
                    {
                        Rs_Consulta.P_Str_Accion = Registro["Accion"].ToString();
                    }
                    else
                    {
                        Rs_Consulta.P_Str_Accion = "";
                    }


                    Dt_Existencia = Rs_Consulta.Consultar_Si_Existe_Registro_Volumen();

                    //  validacion de la consulta
                    if (Dt_Existencia != null && Dt_Existencia.Rows.Count > 0)
                    {
                        //  actualizacion
                        Rs_Consulta.P_Id = Dt_Existencia.Rows[0]["ID"].ToString();
                        Rs_Consulta.Actualizar_Registro_Volumenes();
                    }// fin del if
                    else
                    {
                        //  insercion
                        Rs_Consulta.Insertar_Registro_Volumenes();
                    } // fin el else
                }     // fin foreach
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Eror:   " + Ex.Message, "Mensaje", MessageBoxButtons.OK);
            }
        }// fin
        }// fin de consulta

        //*******************************************************************************
        //NOMBRE_FUNCION:  Consultar_Volumenes
        //DESCRIPCION: Metodo que Consulta las cuentas congeladas con estatus de cobranza
        //PARAMETROS : 1.- Cls_Rpt_Cor_Cc_Reportes_Varios_Neogcio Datos, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 11/Abril/2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static DataTable Consultar_Volumenes(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            DataTable Dt_Consulta = new DataTable();
            String    Str_My_Sql  = "";

            try
            {
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************

                Str_My_Sql  = "select ";
                Str_My_Sql += "GIRO_ID";
                Str_My_Sql += ", Nombre_Giro";
                Str_My_Sql += ", sum(Consumo) as Consumo";
                Str_My_Sql += ", Bimestre";
                Str_My_Sql += ", Anio";
                Str_My_Sql += ", CUOTA_ID";

                Str_My_Sql += " from( ";


                Str_My_Sql += "select ";
                Str_My_Sql += " g.GIRO_ID";
                Str_My_Sql += ", g.Nombre_Giro ";
                Str_My_Sql += ", isnull(sum(f.Consumo), 0) as Consumo";
                Str_My_Sql += ", MONTH(f.Fecha_Emision) as Bimestre";
                Str_My_Sql += ", year(f.Fecha_Emision) as Anio";
                Str_My_Sql += ", tp.CUOTA_ID";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  from **********************************************************************************************************************************
                Str_My_Sql += " from Ope_Cor_Facturacion_Recibos f";
                Str_My_Sql += " left outer join Cat_Cor_Predios p on p.Predio_ID = f.Predio_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Tarifas t ON p.Tarifa_ID = t.Tarifa_ID";
                Str_My_Sql += " left outer JOIN CAT_COR_TIPOS_CUOTAS tp ON tp.CUOTA_ID = t.Cuota_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Giros_Actividades ga ON ga.Actividad_Giro_ID = p.Giro_Actividad_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Giros g ON g.GIRO_ID = ga.Giro_ID";


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  where **********************************************************************************************************************************
                Str_My_Sql += " where";
                Str_My_Sql += " year(f.fecha_emision)  in (" + Datos.P_Anio + ")";
                Str_My_Sql += " and month(f.fecha_emision)  in (" + Datos.P_Mes + ")";
                Str_My_Sql += " and f.Estimado = 'NO' ";
                Str_My_Sql += " and f.Comentarios NOT LIKE ('%CALC%') ";

                if (Datos.P_Estimado == "NO")
                {
                    Str_My_Sql += " and tp.CUOTA_ID = '00002'";
                }
                else
                {
                    Str_My_Sql += " and tp.CUOTA_ID = '00001'";
                }


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  GROUP BY **********************************************************************************************************************************
                Str_My_Sql += " GROUP by";
                Str_My_Sql += " g.GIRO_ID";
                Str_My_Sql += ", g.Nombre_Giro";
                Str_My_Sql += ", tp.CUOTA_ID ";
                Str_My_Sql += ", f.Fecha_Emision";


                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                Str_My_Sql += " union all ";
                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

                Str_My_Sql += " select ";
                Str_My_Sql += " g.GIRO_ID";
                Str_My_Sql += ", g.Nombre_Giro ";
                //Str_My_Sql += ", isnull(sum(f.Consumo), 0) as Consumo";
                Str_My_Sql += ",(  " +
                              " SELECT TOP 1 Consumo " +
                              " FROM Ope_Cor_Facturacion_Recibos_Historicos " +
                              " WHERE No_Factura_Recibo = f.No_Factura_Recibo " +
                              " ORDER BY Facturacion_Historico_ID ASC " +
                              " ) AS Consumo";


                Str_My_Sql += ", MONTH(f.Fecha_Emision) as Bimestre";
                Str_My_Sql += ", year(f.Fecha_Emision) as Anio";
                Str_My_Sql += ", tp.CUOTA_ID";

                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  from **********************************************************************************************************************************
                Str_My_Sql += " from Ope_Cor_Facturacion_Recibos f";
                Str_My_Sql += " left outer join Cat_Cor_Predios p on p.Predio_ID = f.Predio_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Tarifas t ON p.Tarifa_ID = t.Tarifa_ID";
                Str_My_Sql += " left outer JOIN CAT_COR_TIPOS_CUOTAS tp ON tp.CUOTA_ID = t.Cuota_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Giros_Actividades ga ON ga.Actividad_Giro_ID = p.Giro_Actividad_ID";
                Str_My_Sql += " left outer JOIN Cat_Cor_Giros g ON g.GIRO_ID = ga.Giro_ID";


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  where **********************************************************************************************************************************
                Str_My_Sql += " where";
                Str_My_Sql += " year(f.fecha_emision)  in (" + Datos.P_Anio + ")";
                Str_My_Sql += " and month(f.fecha_emision)  in (" + Datos.P_Mes + ")";
                Str_My_Sql += " and f.Estimado = 'SI' ";
                Str_My_Sql += " and f.Comentarios NOT LIKE ('%CALC%') ";

                if (Datos.P_Estimado == "NO")
                {
                    Str_My_Sql += " and tp.CUOTA_ID = '00002'";
                }
                else
                {
                    Str_My_Sql += " and tp.CUOTA_ID = '00001'";
                }


                //  ****************************************************************************************************************************************
                //  ****************************************************************************************************************************************
                //  GROUP BY **********************************************************************************************************************************
                Str_My_Sql += " GROUP by";
                Str_My_Sql += " g.GIRO_ID";
                Str_My_Sql += ", g.Nombre_Giro";
                Str_My_Sql += ", tp.CUOTA_ID ";
                Str_My_Sql += ", f.Fecha_Emision";
                Str_My_Sql += ", f.No_Factura_Recibo ";

                Str_My_Sql += ")as x ";

                ////  ****************************************************************************************************************************************
                ////  ****************************************************************************************************************************************
                ////  GROUP BY **********************************************************************************************************************************
                Str_My_Sql += "GROUP BY GIRO_ID";
                Str_My_Sql += ",Nombre_Giro";
                Str_My_Sql += ",CUOTA_ID";
                Str_My_Sql += ",Bimestre";
                Str_My_Sql += ", Anio";
                Str_My_Sql += ", CUOTA_ID";



                ////  ****************************************************************************************************************************************
                ////  ****************************************************************************************************************************************
                ////  ORDER BY **********************************************************************************************************************************
                //Str_My_Sql += " ORDER BY";
                //Str_My_Sql += "  year(f.Fecha_Emision) ";
                //Str_My_Sql += ", g.GIRO_ID";
                //Str_My_Sql += ", month(f.Fecha_Emision)";

                Dt_Consulta = SqlHelper.ExecuteDataset(Cls_Constantes.Str_Conexion, CommandType.Text, Str_My_Sql).Tables[0];
            }
            catch (Exception Ex)
            {
                throw new Exception("Error: " + Ex.Message);
            }

            return(Dt_Consulta);
        }// fin de consulta
        }// fin del metodo

        //*******************************************************************************
        //NOMBRE_FUNCION:  Actualizar_Registro_Volumenes
        //DESCRIPCION: Metodo que ingresa la informacion de los montos de la facturacion
        //PARAMETROS : 1.- Cls_Rpt_Plan_Montos_Negocio Clase_Negocios, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 25-Octubre-2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static void Actualizar_Registro_Volumenes(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            //Declaración de las variables
            SqlTransaction Obj_Transaccion = null;
            SqlConnection  Obj_Conexion    = new SqlConnection(Cls_Constantes.Str_Conexion);
            SqlCommand     Obj_Comando     = new SqlCommand();
            String         Mi_SQL          = "";

            try
            {
                Obj_Conexion.Open();
                Obj_Transaccion         = Obj_Conexion.BeginTransaction();
                Obj_Comando.Transaction = Obj_Transaccion;
                Obj_Comando.Connection  = Obj_Conexion;


                #region montos_facturacion


                Mi_SQL  = "update  Ope_Cor_Plan_Volumenes set ";
                Mi_SQL += "  " + Datos.P_Str_Nombre_Mes + " = " + Datos.P_Dr_Registro[Datos.P_Str_Nombre_Mes].ToString();
                Mi_SQL += ", fecha_modifico = getdate()";
                Mi_SQL += ", usuario_modifico = '" + Datos.P_Str_Usuario + "'";
                Mi_SQL += " where id = '" + Datos.P_Id + "'";

                if (!String.IsNullOrEmpty(Datos.P_Str_Accion))
                {
                    Mi_SQL += " and accion = " + Datos.P_Str_Accion;
                }

                Obj_Comando.CommandText = Mi_SQL;
                Obj_Comando.ExecuteNonQuery();

                #endregion Fin montos_facturacion

                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //ejecucion de la transaccion    ***********************************************************************************
                Obj_Transaccion.Commit();
            }
            catch (SqlException Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            catch (DBConcurrencyException Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            catch (Exception Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            finally
            {
                Obj_Conexion.Close();
            }
        }// fin del metodo
        }// fin de consulta

        //*******************************************************************************
        //NOMBRE_FUNCION:  Modifica_Notificacion
        //DESCRIPCION: Metodo que ingresa la informacion de los montos de la facturacion
        //PARAMETROS : 1.- Cls_Rpt_Plan_Montos_Negocio Clase_Negocios, objeto de la clase de negocios
        //CREO       : Hugo Enrique Ramírez Aguilera
        //FECHA_CREO : 25-Octubre-2016
        //MODIFICO   :
        //FECHA_MODIFICO:
        //CAUSA_MODIFICO:
        //*******************************************************************************
        public static void Insertar_Registro_Volumenes(Cls_Rpt_Plan_Volumenes_Negocio Datos)
        {
            //Declaración de las variables
            SqlTransaction Obj_Transaccion = null;
            SqlConnection  Obj_Conexion    = new SqlConnection(Cls_Constantes.Str_Conexion);
            SqlCommand     Obj_Comando     = new SqlCommand();
            String         Mi_SQL          = "";

            try
            {
                Obj_Conexion.Open();
                Obj_Transaccion         = Obj_Conexion.BeginTransaction();
                Obj_Comando.Transaction = Obj_Transaccion;
                Obj_Comando.Connection  = Obj_Conexion;


                #region montos_facturacion


                Mi_SQL  = "INSERT INTO  Ope_Cor_Plan_Volumenes (";
                Mi_SQL += "  Giro_Id";                          //  1
                Mi_SQL += ", Concepto";                         //  2
                Mi_SQL += ", Año";                              //  3
                Mi_SQL += ", " + Datos.P_Str_Nombre_Mes;        //  4
                Mi_SQL += ", Fecha_Creo";                       //  5
                Mi_SQL += ", Usuario_Creo";                     //  6

                if (!String.IsNullOrEmpty(Datos.P_Str_Accion))
                {
                    Mi_SQL += ", accion ";                      //  7
                }

                Mi_SQL += ")";
                //***************************************************************************
                Mi_SQL += " values ";
                //***************************************************************************
                Mi_SQL += "(";
                Mi_SQL += "  '" + Datos.P_Giro_Id + "'";                                            //  1
                Mi_SQL += ", '" + Datos.P_Dr_Registro["Concepto"].ToString() + "'";                 //  2
                Mi_SQL += ",  " + Datos.P_Anio + "";                                                //  3
                Mi_SQL += ",  " + Datos.P_Dr_Registro[Datos.P_Str_Nombre_Mes].ToString() + "";      //  4
                Mi_SQL += ",  getdate()";                                                           //  5
                Mi_SQL += ", '" + Datos.P_Str_Usuario + "'";                                        //  6


                if (!String.IsNullOrEmpty(Datos.P_Str_Accion))
                {
                    Mi_SQL += ", " + Datos.P_Str_Accion;                                   //  7
                }


                Mi_SQL += ")";

                Obj_Comando.CommandText = Mi_SQL;
                Obj_Comando.ExecuteNonQuery();

                #endregion Fin montos_facturacion

                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //***********************************************************************************************************************
                //ejecucion de la transaccion    ***********************************************************************************
                Obj_Transaccion.Commit();
            }
            catch (SqlException Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            catch (DBConcurrencyException Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            catch (Exception Ex)
            {
                Obj_Transaccion.Rollback();
                throw new Exception("Error: " + Ex.Message);
            }
            finally
            {
                Obj_Conexion.Close();
            }
        }// fin del metodo