Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.demo.impl; import com.demo.dao.TablaCuentasGestionDao; import com.demo.model.TablaCuentasGestion; import com.demo.util.HibernateUtil; import com.demo.util.LogSistema; import java.io.Serializable; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.hibernate.HibernateException; import org.hibernate.Session; import org.hibernate.Transaction; /** * * @author brionvega */ public class TablaCuentasGestionImpl implements Serializable, TablaCuentasGestionDao { @Override public List<TablaCuentasGestion> getDatos() { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); List<TablaCuentasGestion> lista = session.createSQLQuery( "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv\n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor limit 5000;") .addEntity(TablaCuentasGestion.class).list(); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog(this.getClass().getName() + " Method: getDatos, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } @Override public List<TablaCuentasGestion> getDatosPorCampGest(int idCampagne, int idGestor) { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); String consulta; if (idGestor != -1) { consulta = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv and a.Cat_Gestores_Cat_Gestor_clv = " + idGestor + " \n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Campagne_Camp_Clv = " + idCampagne + " and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor limit 5000;"; } else { consulta = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv \n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Campagne_Camp_Clv = " + idCampagne + " and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor limit 5000;"; } List<TablaCuentasGestion> lista = session.createSQLQuery(consulta).addEntity(TablaCuentasGestion.class) .list(); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog(this.getClass().getName() + " Method: getDatos, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } @Override public List<TablaCuentasGestion> getDatosPorCampGestMarc(int idCampagne, int idGestor, int marcaje) { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); String consulta; if (idGestor != -1) { consulta = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp join marcaje_credito marc \n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII \n" + "and a.marcaje_credito_idmarcaje_credito = marc.idmarcaje_credito \n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv and a.Cat_Gestores_Cat_Gestor_clv = " + idGestor + " \n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Campagne_Camp_Clv = " + idCampagne + " and a.marcaje_credito_idmarcaje_credito = " + marcaje + " and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor limit 5000;"; } else { consulta = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp join marcaje_credito marc \n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.marcaje_credito_idmarcaje_credito = marc.idmarcaje_credito \n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv \n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Campagne_Camp_Clv = " + idCampagne + " and a.marcaje_credito_idmarcaje_credito = " + marcaje + " and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor limit 5000;"; } System.out.println("TablaCuentaGestionImpl.java getDatosCampGestMarc: Cnsulta = " + consulta); List<TablaCuentasGestion> lista = session.createSQLQuery(consulta).addEntity(TablaCuentasGestion.class) .list(); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog( this.getClass().getName() + " Method: getDatosPorCampGestMarc, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } @Override public List<TablaCuentasGestion> getDatosPorPlan(int idPlan) { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); String consulta = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv\n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 and a.Plan_De_Trabajo_Plan_Clv = " + idPlan + " group by a.CreditosII order by a.Estado_Gestion, c.Deudor limit 5000;"; List<TablaCuentasGestion> lista = session.createSQLQuery(consulta).addEntity(TablaCuentasGestion.class) .list(); System.out.println("CONSULTA getDatosPorPlan" + consulta); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog( this.getClass().getName() + " Method: getDatosPorPlan, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } @Override public List<TablaCuentasGestion> getDatosPorNombre(String nombre) { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); String sql = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , ' ' as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and c.Deudor like concat('%','" + nombre + "','%')\n" + "and c.Folio=a.Datos_primarios_Folio\n" + "and a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII) \n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv\n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv order by c.Deudor;"; LogSistema.guardarlog("sql " + sql); System.out.println("CONSULTA PARA CONSTRUIR OBJETO" + sql); List<TablaCuentasGestion> lista = session.createSQLQuery(sql).addEntity(TablaCuentasGestion.class) .list(); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog( this.getClass().getName() + " Method: getDatosPorNombre, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } @Override public List<TablaCuentasGestion> getDatosPorFiltroAuditoria(String estado, String municipio, int minMeses, int maxMeses, Date fechainicioUlt, Date fechaFinUlt, float minMens, float maxMens, float minSdoVen, float maxSdoVen, float minSdoCap, float maxSdoCap, String productoElegido) { try { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction t = session.beginTransaction(); String sql; sql = "select a.CreditosII as Folio, a.Campagne_Camp_Clv as Campagne, c.Deudor as Nombre, a.ImpMens as Mensualidad, b.MesVenLineas as MesesVencidos, b.SdoVen as SaldoVencido, b.SdoVen as Adeudo, d.Gestion_Fecha as FechaUltGestion, d.Nota_Importante as UltimaNota, 4 as TipoCredito, (select max(co.idConvenio) from convenio co where co.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII ) as idConvenio , gestor.Cat_Gestor_clv , \" \" as Estatus, a.Estado_Gestion as EstadoGestion, c.Folio as FolioDeudor , if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 7, 'nivelC',(if(DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) < 7 and DATEDIFF(CURDATE(),((select max(gest.Gestion_Fecha ) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII))) > 3,'nivelB','nivelA'))) as Color, camp.Camp_Desc as CampagneDesc\n" + "from credito_sf_lt_nt_ct a join actu_sf_lt_nt_ct b join datos_primarios c join direcciones dir join gestiones d join cat_gestores gestor join campagne camp\n" + "where b.Consecutivo = (select max(act.Consecutivo) from actu_sf_lt_nt_ct act where act.Credito_SF_LT_NT_CT_CreditosII= a.CreditosII)\n" + "and b.MesVenLineas>=" + minMeses + " and b.MesVenLineas<=" + maxMeses + " "; if (productoElegido.compareTo("Todos") != 0) { if (productoElegido.compareTo("SOFOM COMERCIAL") == 0) { sql += " and (a.Producto = 'A_LINEA aITO TELMEX PF 50' or a.Producto = 'A_LINEA aITO TELMEX PM 50' or a.Producto = 'A_EX LINEA aITO TELMEX PF 50' or a.Producto = 'A_EXLINEA aITO TELMEX PM 50') "; } else if (productoElegido.compareTo("SOFOM PERSONAL") == 0) { sql += " and (a.Producto = 'A_LINEA aITO TELMEX PERSONAL 38' or a.Producto = 'EX LINEA aITO TELMEX PERSONAL 38') "; } else if (productoElegido.compareTo("CT EXPRESS") == 0) { sql += " and (a.Producto = 'CT EXPRESS PF' or a.Producto = 'CT EXPRESS PM' or a.Producto = 'aITOS EXPRESS PF' or a.Producto = 'aITOS EXPRESS PM' or a.Producto = 'EXPRESS ABIERTO PF' or a.Producto = 'EXPRESS ABIERTO PM') "; } else if (productoElegido.compareTo("LINEA TELMEX") == 0) { sql += " and (a.Producto = 'LINEA aITO TELMEX PF' or a.Producto = 'LINEA aITO TELMEX PM' or a.Producto = 'EX LINEA aITO TELMEX PF' or a.Producto = 'EXLINEA aITO TELMEX PM') "; } else if (productoElegido.compareTo("TELNOR LT") == 0) { sql += " and (a.Producto = 'LINEA aITO TELNOR PF' or a.Producto = 'LINEA aITO TELNOR PM' or a.Producto = 'EX LINEA aITO TELNOR PF' or a.Producto = 'EXLINEA aITO TELNOR PM') "; } else if (productoElegido.compareTo("TELNOR SOFOM") == 0) { sql += " and (a.Producto = 'A_LINEA aITO TELNOR PF 50' or a.Producto = 'A_LINEA aITO TELNOR PM 50' or a.Producto = 'A_EX LINEA aITO TELNOR PF 50' or a.Producto = 'A_EXLINEA aITO TELNOR PM 50') "; } else if (productoElegido.compareTo("CT CASTIGO") == 0) { sql += " and (a.Producto = 'CT EXPRESS PF CASTIGO' or a.Producto = 'CT EXPRESS PM CASTIGO') "; } else if (productoElegido.compareTo("QUEBRANTO COMERCIAL") == 0) { sql += " and (a.Producto = 'LINEA aITO TELMEX PF 50 QUEBRANTO' or a.Producto = 'LINEA aITO TELMEX PM 50 QUEBRANTO' or a.Producto = 'EX LINEA aITO TELMEX PF 50 QUEBRANTO' or a.Producto = 'EXLINEA aITO TELMEX PM 50 QUEBRANTO' or a.Producto = 'LINEA aITO TELMEX PF QUEBRANTO' or a.Producto = 'LINEA aITO TELMEX PM QUEBRANTO' or a.Producto = 'EX LINEA aITO TELMEX PF QUEBRANTO' or a.Producto = 'EXLINEA aITO TELMEX PM QUEBRANTO') "; } else if (productoElegido.compareTo("TELNOR PERSONAL") == 0) { sql += " and (a.Producto = 'A_LINEA aITO TELNOR PERSONAL 38' or a.Producto = 'EX LINEA aITO TELNOR PERSONAL 38') "; } else { sql += " and a.Producto = '" + productoElegido + "' "; } } SimpleDateFormat formatoDeFecha = new SimpleDateFormat("yyyy-MM-dd"); sql += "and a.CreditosII = b.Credito_SF_LT_NT_CT_CreditosII\n" + "and ((a.`FechUltPag` >'" + formatoDeFecha.format(fechainicioUlt) + "' and a.`FechUltPag`<'" + formatoDeFecha.format(fechaFinUlt) + "') or a.`FechUltPag` is null)\n" + "and a.ImpMens>=" + minMens + " and a.ImpMens<=" + maxMens + "\n" + "and b.SdoVen>=" + minSdoVen + " and b.SdoVen<=" + maxSdoVen + "\n" + "and b.SdoTotal>=" + minSdoCap + " and b.SdoTotal<=" + maxSdoCap + "\n" + "and a.Datos_primarios_Folio = c.Folio\n" + "and a.Datos_primarios_Folio=dir.Datos_primarios_Folio and "; if (estado != null && estado.compareTo("") != 0) { if (estado.compareToIgnoreCase("mexico") != 0) { if (estado.compareToIgnoreCase("BAJA CALIFORNIA") != 0) { sql += " dir.`EdoDeu`='" + estado + "' and "; } else { sql += "( dir.`EdoDeu`='baja california' or dir.`EdoDeu`='baja california norte' or dir.`EdoDeu`='baja california nte' ) and "; } } else { sql += "( dir.`EdoDeu`='mexico' or dir.`EdoDeu`='edo. mex' or dir.`EdoDeu`='estado de mexico' ) and "; } } if (municipio != null && municipio.compareTo("") != 0) { sql += "dir.Entdeu='" + municipio + "' and "; } sql += " a.CreditosII = d.Credito_SF_LT_NT_CT_CreditosII\n" + "and d.Gestion_Consecutivo = (select max(gest.Gestion_Consecutivo) from Gestiones gest where gest.Credito_SF_LT_NT_CT_CreditosII = a.CreditosII)\n" + "and a.Cat_Gestores_Cat_Gestor_clv = gestor.Cat_Gestor_clv\n" + "and a.Campagne_Camp_Clv = camp.Camp_Clv and a.Cat_Subestatus_Cuenta_Subestatus_Clv = 5 order by c.Deudor;"; System.out.println("sql " + sql); List<TablaCuentasGestion> lista = session.createSQLQuery(sql).addEntity(TablaCuentasGestion.class) .list(); t.commit(); if (session.isOpen()) { session.close(); } return lista; } catch (HibernateException he) { LogSistema.guardarlog(this.getClass().getName() + " Method: getDatosPorFiltroAuditoria, Exception: " + he.getMessage()); List<TablaCuentasGestion> lista = new ArrayList<>(); return lista; } } }