//******************************************************************************* //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_Montos_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: Actualizar_Registro_Facturacion //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_Pago(Cls_Rpt_Plan_Montos_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_Montos_Pagos 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 + "'"; 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: Consultar_Pagos_A_Facturacion_Planeacion //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_Si_Existe_Registro_Pago(Cls_Rpt_Plan_Montos_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_Montos_Pagos"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // where ********************************************************************************************************************************** Str_My_Sql += " where"; Str_My_Sql += " Año = " + Datos.P_Anio; Str_My_Sql += " and giro_Id = '" + Datos.P_Giro_Id + "'"; Str_My_Sql += " and servicio = " + Datos.P_Int_Servicio + ""; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // ********************************************************************************************************************************** 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_Facturacion_Planeacion //DESCRIPCION: Metodo que consulta los importes que se han facturado a lo largo de un año //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_Facturacion_Planeacion(Cls_Rpt_Plan_Montos_Negocio Datos) { DataTable Dt_Consulta = new DataTable(); String Str_My_Sql = ""; try { // **************************************************************************************************************************************** // **************************************************************************************************************************************** // **************************************************************************************************************************************** Str_My_Sql = "select "; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", cc.Concepto_Id"; Str_My_Sql += ", cc.Nombre"; Str_My_Sql += ", sum(fd.Total) As Total_Facturado"; Str_My_Sql += ", MONTH(f.Fecha_Emision) as Bimestre"; Str_My_Sql += ", YEAR(f.Fecha_Emision) as Anio"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // from ********************************************************************************************************************************** Str_My_Sql += " from Ope_Cor_Facturacion_Recibos f"; Str_My_Sql += " join Ope_Cor_Facturacion_Recibos_Detalles fd on fd.No_Factura_Recibo = f.No_Factura_Recibo"; Str_My_Sql += " join Cat_Cor_Conceptos_Cobros cc on cc.Concepto_ID = fd.Concepto_ID"; Str_My_Sql += " JOIN Cat_Cor_Predios p on p.Predio_ID = f.Predio_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros_Actividades ga ON ga.Actividad_Giro_ID = p.Giro_Actividad_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros g ON g.GIRO_ID = ga.Giro_ID"; Str_My_Sql += " JOIN Cat_Cor_Tarifas t ON t.Tarifa_ID = p.Tarifa_ID"; Str_My_Sql += " JOIN CAT_COR_TIPOS_CUOTAS cu ON cu.CUOTA_ID = t.Cuota_ID"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // where ********************************************************************************************************************************** Str_My_Sql += " where"; Str_My_Sql += " YEAR(f.Fecha_Emision) = " + Datos.P_Anio; Str_My_Sql += " and MONTH(f.Fecha_Emision) = " + Datos.P_Mes; Str_My_Sql += " AND (cu.CLAVE = 'CF' OR cu.CLAVE = 'SM' )"; Str_My_Sql += " and(cc.Concepto_ID = (select p.CONCEPTO_AGUA from Cat_Cor_Parametros p) " + " OR cc.Concepto_ID = (select p.Concepto_Agua_Comercial from Cat_Cor_Parametros p)" + " OR cc.Concepto_ID = (select p.CONCEPTO_DRENAJE from Cat_Cor_Parametros p) " + " OR cc.Concepto_ID = (select p.CONCEPTO_SANAMIENTO from Cat_Cor_Parametros p))"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // GROUP BY ********************************************************************************************************************************** Str_My_Sql += " GROUP BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", cc.Concepto_ID "; Str_My_Sql += ", cc.Nombre"; Str_My_Sql += ", f.Fecha_Emision"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // ORDER BY ********************************************************************************************************************************** Str_My_Sql += " ORDER BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", 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: 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_Pago(Cls_Rpt_Plan_Montos_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_Montos_Pagos ("; 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 Mi_SQL += ", Servicio"; // 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 Mi_SQL += ", '" + Datos.P_Int_Servicio + "'"; // 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
}// fin de consulta //******************************************************************************* //NOMBRE_FUNCION: Consultar_Pagos_A_Facturacion_Planeacion //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_Pagos_A_Facturacion_Planeacion(Cls_Rpt_Plan_Montos_Negocio Datos) { DataTable Dt_Consulta = new DataTable(); String Str_My_Sql = ""; try { // **************************************************************************************************************************************** // **************************************************************************************************************************************** // **************************************************************************************************************************************** Str_My_Sql = "select "; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", fd.Concepto_Id"; Str_My_Sql += ", cc.Nombre"; Str_My_Sql += ", sum(mc.importe) as Pagado"; Str_My_Sql += ", month(f.Fecha_Emision) as Mes"; Str_My_Sql += ", year(f.Fecha_Emision) as Año"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // from ********************************************************************************************************************************** Str_My_Sql += " from "; Str_My_Sql += " Ope_Cor_Facturacion_Recibos f"; Str_My_Sql += " JOIN Ope_Cor_Facturacion_Recibos_Detalles fd ON f.No_Factura_Recibo = fd.No_Factura_Recibo "; Str_My_Sql += " JOIN Ope_Cor_Caj_Movimientos_Cobros mc ON mc.No_Movimiento_Facturacion = fd.No_Movimiento "; Str_My_Sql += " join Ope_Cor_Caj_Recibos_Cobros rc on rc.NO_RECIBO = mc.NO_RECIBO "; Str_My_Sql += " JOIN Cat_Cor_Predios p on p.Predio_ID = f.Predio_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros_Actividades ga ON ga.Actividad_Giro_ID = p.Giro_Actividad_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros g ON g.GIRO_ID = ga.Giro_ID"; Str_My_Sql += " join Cat_Cor_Conceptos_Cobros cc on cc.Concepto_ID = fd.CONCEPTO_ID"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // where ********************************************************************************************************************************** Str_My_Sql += " where"; Str_My_Sql += " fd.Estatus in ('PAGADO') "; Str_My_Sql += " and year(rc.FECHA_CREO) = " + Datos.P_Anio; Str_My_Sql += " and month(rc.FECHA_CREO) =" + Datos.P_Mes; Str_My_Sql += " and(cc.Concepto_ID = (select p.CONCEPTO_AGUA from Cat_Cor_Parametros p) " + " OR cc.Concepto_ID = (select p.Concepto_Agua_Comercial from Cat_Cor_Parametros p)" + " OR cc.Concepto_ID = (select p.CONCEPTO_DRENAJE from Cat_Cor_Parametros p) " + " OR cc.Concepto_ID = (select p.CONCEPTO_SANAMIENTO from Cat_Cor_Parametros p))"; Str_My_Sql += "and year(f.Fecha_Emision) = year(rc.FECHA_CREO) " + " and MONTH(f.Fecha_Emision) = MONTH(rc.FECHA_CREO)"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // GROUP BY ********************************************************************************************************************************** Str_My_Sql += " GROUP BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", fd.Concepto_ID "; Str_My_Sql += ", cc.Nombre"; Str_My_Sql += ", rc.FECHA_CREO"; Str_My_Sql += ", f.Fecha_Emision"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // ORDER BY ********************************************************************************************************************************** Str_My_Sql += " ORDER BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", rc.FECHA_CREO"; Str_My_Sql += ", fd.Concepto_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
//******************************************************************************* //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_Cat_Cor_Parametros_Negocio Rs_Parametros = new Cls_Cat_Cor_Parametros_Negocio(); Cls_Rpt_Plan_Montos_Negocio Rs_Consulta = new Cls_Rpt_Plan_Montos_Negocio(); DataTable Dt_Consulta_Facturacion_Estimado_No = new DataTable(); DataTable Dt_Consulta_Facturacion_Estimado_Si = new DataTable(); DataTable Dt_Consulta_Pagos = new DataTable(); DataTable Dt_Tarifas = new DataTable(); DataTable Dt_Reporte = new DataTable(); DataTable Dt_Reporte_Pagos = new DataTable(); DataTable Dt_Auxiliar = new DataTable(); DataTable Dt_Resumen = new DataTable(); DataTable Dt_Parametros = new DataTable(); DataRow Dr_Nuevo_Elemento; Int32 Int_Mes = 0; String Str_Nombre_Mes = ""; Dictionary <Int32, String> Dic_Meses; int Cont_Encabezao = 0; Double Db_Total = 0; Double Db_Total_Concepto = 0; String Str_Concepto_Agua_Id = ""; String Str_Concepto_Agua_Comercial_Id = ""; String Str_Concepto_Drenaje_Id = ""; String Str_Concepto_Saneamiento_Id = ""; Decimal Dc_Total_Facturado_Estimado_No = 0; Decimal Dc_Total_Facturado_Estimado_Si = 0; Decimal Dc_Total_Pagado = 0; DataTable Dt_Existencia = new DataTable(); try { // se consultan los parametros Dt_Parametros = Rs_Parametros.Consulta_Parametros(); // se obtienen los id de los parametros foreach (DataRow Registro in Dt_Parametros.Rows) { Str_Concepto_Agua_Id = Registro["CONCEPTO_AGUA"].ToString(); Str_Concepto_Agua_Comercial_Id = Registro["Concepto_Agua_Comercial"].ToString(); Str_Concepto_Drenaje_Id = Registro["CONCEPTO_DRENAJE"].ToString(); Str_Concepto_Saneamiento_Id = Registro["CONCEPTO_SANAMIENTO"].ToString(); } // se consultan los valeres a reportar 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(); Dt_Reporte_Pagos = Crear_Tabla_Reporte(); Dt_Consulta_Facturacion_Estimado_No = Rs_Consulta.Consultar_Facturacion_Planeacion(); Dt_Consulta_Facturacion_Estimado_Si = Rs_Consulta.Consultar_Facturacion_Planeacion_Estimado_Si(); Dt_Consulta_Pagos = Rs_Consulta.Consultar_Pagos_A_Facturacion_Planeacion(); //******************************************************************************************************************** //******************************************************************************************************************** //******************************************************************************************************************** // Se ingresan los encabezados para la facturacion 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["Accion"] = "0"; Dr_Nuevo_Elemento["Concepto"] = "Monto facturado por el servicio de agua de las tomas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento); //******************************************************************************** //******************************************************************************** //******************************************************************************** Dr_Nuevo_Elemento = Dt_Reporte.NewRow(); Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString(); Dr_Nuevo_Elemento["Accion"] = "1"; Dr_Nuevo_Elemento["Concepto"] = "Monto facturado por el servicio de drenaje de las descargas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento); //******************************************************************************** //******************************************************************************** //******************************************************************************** Dr_Nuevo_Elemento = Dt_Reporte.NewRow(); Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString(); Dr_Nuevo_Elemento["Accion"] = "2"; Dr_Nuevo_Elemento["Concepto"] = "Monto facturado por el servicio de tratemiento de las descargas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte.Rows.Add(Dr_Nuevo_Elemento); } int cont_x = 0; //******************************************************************************************************************** //******************************************************************************************************************** //******************************************************************************************************************** // Facturacion******************************************************************************************************************** foreach (DataRow Registro in Dt_Reporte.Rows) { Registro.BeginEdit(); Db_Total = 0; for (int Cont_For = 1; Cont_For <= 12; Cont_For++) { //Dt_Auxiliar = Dt_Consulta_Facturacion.Copy(); //Dt_Auxiliar.DefaultView.RowFilter = "giro_id = '" + Registro["tarifa_id"].ToString() + "' and bimestre = '" + Cont_For.ToString() + "'"; //Dt_Auxiliar = Dt_Auxiliar.DefaultView.ToTable(); Str_Nombre_Mes = ""; if (Dic_Meses.ContainsKey(Cont_For) == true) { Str_Nombre_Mes = Dic_Meses[Cont_For]; }// fin de la validacion del diccionario Db_Total_Concepto = 0; //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> if (Registro["Accion"].ToString() == "0")// Agua ó Agua_comercial { //foreach (DataColumn Columna in Dt_Consulta_Facturacion_Estimado_Si.Columns) //{ // Type Tipo_Dato = Columna.DataType; //} // 1 ******************************************************************************** Dc_Total_Facturado_Estimado_No = (from ord in Dt_Consulta_Facturacion_Estimado_No.AsEnumerable() where (ord.Field <String>("Concepto_id") == Str_Concepto_Agua_Id || ord.Field <String>("Concepto_id") == Str_Concepto_Agua_Comercial_Id) && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For select ord.Field <Decimal>("Total_Facturado")) .Sum(); cont_x++; //if (cont_x == 28) //{ // String X = ""; //} // 1 ******************************************************************************** Dc_Total_Facturado_Estimado_Si = (from ord in Dt_Consulta_Facturacion_Estimado_Si.AsEnumerable() where (ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For) select ord.Field <Decimal>("Agua")) .Sum(); } //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> else if (Registro["Accion"].ToString() == "1")// DRENAJE { // 2 ******************************************************************************** Dc_Total_Facturado_Estimado_No = (from ord in Dt_Consulta_Facturacion_Estimado_No.AsEnumerable() where ord.Field <String>("Concepto_id") == Str_Concepto_Drenaje_Id && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For select ord.Field <Decimal>("Total_Facturado")) .Sum(); // 2 ******************************************************************************** Dc_Total_Facturado_Estimado_Si = (from ord in Dt_Consulta_Facturacion_Estimado_Si.AsEnumerable() where (ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For) select ord.Field <Decimal>("drenaje")) .Sum(); } //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> else if (Registro["Accion"].ToString() == "2")// SANEAMIENTO { // 3 ******************************************************************************** Dc_Total_Facturado_Estimado_No = (from ord in Dt_Consulta_Facturacion_Estimado_No.AsEnumerable() where ord.Field <String>("Concepto_id") == Str_Concepto_Saneamiento_Id && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For select ord.Field <Decimal>("Total_Facturado")) .Sum(); // 3 ******************************************************************************** Dc_Total_Facturado_Estimado_Si = (from ord in Dt_Consulta_Facturacion_Estimado_Si.AsEnumerable() where (ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("bimestre") == Cont_For) select ord.Field <Decimal>("saneamiento")) .Sum(); } //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> // se agrega el concepto al mes correspondiente Registro[Str_Nombre_Mes] = Dc_Total_Facturado_Estimado_No + Dc_Total_Facturado_Estimado_Si; Db_Total = Db_Total + Convert.ToDouble(Dc_Total_Facturado_Estimado_No) + Convert.ToDouble(Dc_Total_Facturado_Estimado_Si); } Registro["Total"] = Db_Total; Db_Total = 0; Registro.EndEdit(); Registro.AcceptChanges(); } //******************************************************************************************************************** //******************************************************************************************************************** //******************************************************************************************************************** // Se ingresan los encabezados para los pagos foreach (DataRow Registro in Dt_Tarifas.Rows) { Dr_Nuevo_Elemento = Dt_Reporte_Pagos.NewRow(); Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString(); Dr_Nuevo_Elemento["Accion"] = "0"; Dr_Nuevo_Elemento["Concepto"] = "Monto ingresados por el servicio de agua de las tomas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte_Pagos.Rows.Add(Dr_Nuevo_Elemento); //******************************************************************************** //******************************************************************************** //******************************************************************************** Dr_Nuevo_Elemento = Dt_Reporte_Pagos.NewRow(); Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString(); Dr_Nuevo_Elemento["Accion"] = "1"; Dr_Nuevo_Elemento["Concepto"] = "Monto ingresados por el servicio de drenaje de las descargas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte_Pagos.Rows.Add(Dr_Nuevo_Elemento); //******************************************************************************** //******************************************************************************** //******************************************************************************** Dr_Nuevo_Elemento = Dt_Reporte_Pagos.NewRow(); Dr_Nuevo_Elemento["tarifa_Id"] = Registro["giro_id"].ToString(); Dr_Nuevo_Elemento["Accion"] = "2"; Dr_Nuevo_Elemento["Concepto"] = "Monto ingresados por el servicio de tratemiento de las descargas " + Registro["Nombre_Giro"].ToString() + " (" + Registro["Clave"].ToString() + ")"; Dt_Reporte_Pagos.Rows.Add(Dr_Nuevo_Elemento); } //******************************************************************************************************************** //******************************************************************************************************************** //******************************************************************************************************************** // pagos******************************************************************************************************************** foreach (DataRow Registro in Dt_Reporte_Pagos.Rows) { Registro.BeginEdit(); Db_Total = 0; for (int Cont_For = 1; Cont_For <= 12; Cont_For++) { //if (Cont_For == 5) //{ // string x = ""; //} Dt_Auxiliar = Dt_Consulta_Pagos.Copy(); Dt_Auxiliar.DefaultView.RowFilter = "giro_id = '" + Registro["tarifa_id"].ToString() + "' and mes = '" + Cont_For.ToString() + "'"; Dt_Auxiliar = Dt_Auxiliar.DefaultView.ToTable(); 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_Pagado = 0; if (Registro["Accion"].ToString() == "0")// Agua ó Agua_comercial { //foreach (DataColumn Columna in Dt_Consulta_Pagos.Columns) //{ // Type Tipo_Dato = Columna.DataType; //} // 1 ******************************************************************************** Dc_Total_Pagado = (from ord in Dt_Consulta_Pagos.AsEnumerable() where (ord.Field <String>("Concepto_id") == Str_Concepto_Agua_Id || ord.Field <String>("Concepto_id") == Str_Concepto_Agua_Comercial_Id) && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("Mes") == Cont_For select ord.Field <Decimal>("Pagado")) .Sum(); } else if (Registro["Accion"].ToString() == "1")// DRENAJE { // 2 ******************************************************************************** Dc_Total_Pagado = (from ord in Dt_Consulta_Pagos.AsEnumerable() where ord.Field <String>("Concepto_id") == Str_Concepto_Drenaje_Id && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("Mes") == Cont_For select ord.Field <Decimal>("Pagado")) .Sum(); } else if (Registro["Accion"].ToString() == "2")// SANEAMIENTO { // 3 ******************************************************************************** Dc_Total_Pagado = (from ord in Dt_Consulta_Pagos.AsEnumerable() where ord.Field <String>("Concepto_id") == Str_Concepto_Saneamiento_Id && ord.Field <String>("giro_id") == Registro["tarifa_id"].ToString() && ord.Field <Int32>("Mes") == Cont_For select ord.Field <Decimal>("Pagado")) .Sum(); } // se agrega el concepto al mes correspondiente Registro[Str_Nombre_Mes] = Dc_Total_Pagado; Db_Total = Db_Total + Convert.ToDouble(Dc_Total_Pagado); } Registro["Total"] = Db_Total; Db_Total = 0; Registro.EndEdit(); Registro.AcceptChanges(); } Rs_Consulta.P_Str_Usuario = "Servicio"; // 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_Int_Servicio = Convert.ToInt32(Registro["Accion"].ToString()); Rs_Consulta.P_Giro_Id = Registro["tarifa_id"].ToString(); Rs_Consulta.P_Anio = DateTime.Now.Year; Rs_Consulta.P_Mes_Insercion = DateTime.Now.Month; Rs_Consulta.P_Dr_Registro = Registro; Dt_Existencia = Rs_Consulta.Consultar_Si_Existe_Registro_Facturacion(); // 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_Facturacion(); }// fin del if else { // insercion Rs_Consulta.Insertar_Registro_Facturacion(); } // fin el else } // fin foreach // se realizara la insercion de la informacion foreach (DataRow Registro in Dt_Reporte_Pagos.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_Int_Servicio = Convert.ToInt32(Registro["Accion"].ToString()); Rs_Consulta.P_Giro_Id = Registro["tarifa_id"].ToString(); Rs_Consulta.P_Anio = Convert.ToInt32(DateTime.Now.Year); Rs_Consulta.P_Mes_Insercion = DateTime.Now.Month; Rs_Consulta.P_Dr_Registro = Registro; Dt_Existencia = Rs_Consulta.Consultar_Si_Existe_Registro_Pago(); // 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_Pago(); }// fin del if else { // insercion Rs_Consulta.Insertar_Registro_Pago(); } // fin el else } // fin foreach MessageBox.Show("Proceso exitoso", "Aviso"); } catch (Exception Ex) { //Mostrar_Informacion(1, "Error: (Consultar_Notificaciones)" + Ex.ToString()); MessageBox.Show("Error: (Consultar_Notificaciones)" + Ex.ToString(), "Aviso"); } }
}// fin de consulta //******************************************************************************* //NOMBRE_FUNCION: Consultar_Facturacion_Planeacion //DESCRIPCION: Metodo que consulta los importes que se han facturado a lo largo de un año //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_Facturacion_Planeacion_Estimado_Si(Cls_Rpt_Plan_Montos_Negocio Datos) { DataTable Dt_Consulta = new DataTable(); String Str_My_Sql = ""; try { // **************************************************************************************************************************************** // **************************************************************************************************************************************** // **************************************************************************************************************************************** Str_My_Sql = "select "; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", F.No_Factura_Recibo"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", MONTH(f.Fecha_Emision) as Bimestre"; Str_My_Sql += ", YEAR(f.Fecha_Emision) as Anio"; //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Str_My_Sql += ",(" + " SELECT sum(fdh.importe)" + " FROM Ope_Cor_Facturacion_Recibos_Detalles_Historicos fdh" + " WHERE Facturacion_Historico_ID IN (" + " SELECT TOP 1 Facturacion_Historico_ID" + " FROM Ope_Cor_Facturacion_Recibos_Historicos" + " WHERE No_Factura_Recibo = f.No_Factura_Recibo" + " ORDER BY Facturacion_Historico_ID ASC" + " )" + " AND (" + " fdh.Concepto_ID = (" + " SELECT p.CONCEPTO_AGUA" + " FROM Cat_Cor_Parametros p" + " )" + " OR fdh.Concepto_ID = (" + " SELECT p.Concepto_Agua_Comercial" + " FROM Cat_Cor_Parametros p" + " )" + " )" + " ) AS Agua"; //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Str_My_Sql += ",(" + " SELECT sum(fdh.importe)" + " FROM Ope_Cor_Facturacion_Recibos_Detalles_Historicos fdh" + " WHERE Facturacion_Historico_ID IN (" + " SELECT TOP 1 Facturacion_Historico_ID" + " FROM Ope_Cor_Facturacion_Recibos_Historicos" + " WHERE No_Factura_Recibo = f.No_Factura_Recibo" + " ORDER BY Facturacion_Historico_ID ASC" + " )" + " AND (" + " fdh.Concepto_ID = (" + " SELECT p.CONCEPTO_DRENAJE" + " FROM Cat_Cor_Parametros p" + " )" + " )" + " ) AS drenaje"; //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Str_My_Sql += ",(" + " SELECT sum(fdh.importe)" + " FROM Ope_Cor_Facturacion_Recibos_Detalles_Historicos fdh" + " WHERE Facturacion_Historico_ID IN (" + " SELECT TOP 1 Facturacion_Historico_ID" + " FROM Ope_Cor_Facturacion_Recibos_Historicos" + " WHERE No_Factura_Recibo = f.No_Factura_Recibo" + " ORDER BY Facturacion_Historico_ID ASC" + " )" + " AND (" + " fdh.Concepto_ID = (" + " SELECT p.CONCEPTO_SANAMIENTO" + " FROM Cat_Cor_Parametros p" + " )" + " )" + " ) AS saneamiento"; //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> // **************************************************************************************************************************************** // **************************************************************************************************************************************** // from ********************************************************************************************************************************** Str_My_Sql += " from Ope_Cor_Facturacion_Recibos f"; Str_My_Sql += " join Ope_Cor_Facturacion_Recibos_Detalles fd on fd.No_Factura_Recibo = f.No_Factura_Recibo"; Str_My_Sql += " join Cat_Cor_Conceptos_Cobros cc on cc.Concepto_ID = fd.Concepto_ID"; Str_My_Sql += " JOIN Cat_Cor_Predios p on p.Predio_ID = f.Predio_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros_Actividades ga ON ga.Actividad_Giro_ID = p.Giro_Actividad_ID"; Str_My_Sql += " JOIN Cat_Cor_Giros g ON g.GIRO_ID = ga.Giro_ID"; Str_My_Sql += " JOIN Cat_Cor_Tarifas t ON t.Tarifa_ID = p.Tarifa_ID"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // where ********************************************************************************************************************************** Str_My_Sql += " where"; Str_My_Sql += " YEAR(f.Fecha_Emision) = " + Datos.P_Anio; Str_My_Sql += " and MONTH(f.Fecha_Emision) = " + Datos.P_Mes; Str_My_Sql += " AND f.Estimado = 'SI' "; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // GROUP BY ********************************************************************************************************************************** Str_My_Sql += " GROUP BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", F.No_Factura_Recibo"; Str_My_Sql += ", g.Nombre_Giro"; Str_My_Sql += ", f.Fecha_Emision"; // **************************************************************************************************************************************** // **************************************************************************************************************************************** // ORDER BY ********************************************************************************************************************************** Str_My_Sql += " ORDER BY"; Str_My_Sql += " g.GIRO_ID"; Str_My_Sql += ", 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