//*******************************************************************************
//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
