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