List of usage examples for org.hibernate Session createSQLQuery
@Override
NativeQuery createSQLQuery(String queryString);
From source file:com.demo.impl.prevalidacionImpl.java
@Override public List<Prevalidacion> obtenerRemesa() { List<Prevalidacion> lista = new ArrayList(); try {//www. j av a 2 s .c om Session session = HibernateUtil.getSessionFactory().openSession(); Transaction tx = session.beginTransaction(); lista = session.createSQLQuery("select * from prevalidacion;").addEntity(Prevalidacion.class).list(); tx.commit(); } catch (NullPointerException e) { LogSistema .guardarlog(this.getClass().getName() + " Method: obtenerRemesa, Exception: " + e.getMessage()); } catch (HibernateException j) { LogSistema .guardarlog(this.getClass().getName() + " Method: obtenerRemesa, Exception: " + j.getMessage()); System.out.println("PrevalidacionImpl : " + j.getMessage()); } return lista; }
From source file:com.demo.impl.PromesadePagoImpl.java
@Override public PromesaPago obtenerUltimoPago(TablaCuentasGestion credito) { PromesaPago prom = new PromesaPago(); Session sess = HibernateUtil.getSessionFactory().openSession(); Transaction tx = sess.beginTransaction(); try {/*from w ww. j a v a 2s .c om*/ switch (credito.getTipoCredito()) { case 1: prom = (PromesaPago) sess.createSQLQuery( "select a.* from Promesa_Pago a join Convenio b where a.Convenio_idConvenio = b.idConvenio and b.Credito_Auto_CreditoAut = '" + credito.getFolio() + "' order by Promesa_Fecha limit 1;") .addEntity(PromesaPago.class).uniqueResult(); break; case 2: prom = (PromesaPago) sess.createSQLQuery( "select a.* from Promesa_Pago a join Convenio b where a.Convenio_idConvenio = b.idConvenio and b.Credito_Fianzas_NumContratoFZ = '" + credito.getFolio() + "' order by Promesa_Fecha limit 1;") .addEntity(PromesaPago.class).uniqueResult(); break; case 3: prom = (PromesaPago) sess.createSQLQuery( "select a.* from Promesa_Pago a join Convenio b where a.Convenio_idConvenio = b.idConvenio and b.Credito_Hipotecario_CreditoHip = '" + credito.getFolio() + "' order by Promesa_Fecha limit 1;") .addEntity(PromesaPago.class).uniqueResult(); break; case 4: String consulta = "select a.* from Promesa_Pago a join Convenio b where a.Convenio_idConvenio = b.idConvenio and b.Credito_SF_LT_NT_CT_CreditosII = '" + credito.getFolio() + "' and a.Promesa_Status='Pendiente' order by Promesa_Fecha limit 1;"; prom = (PromesaPago) sess.createSQLQuery(consulta).addEntity(PromesaPago.class).uniqueResult(); System.out.println(consulta); //LogSistema.guardarlog(consulta); break; } tx.commit(); return prom; } catch (HibernateException he) { tx.rollback(); LogSistema.guardarlog( this.getClass().getName() + " Method: obtenerUltimoPago, Exception: " + he.getMessage()); return prom; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@Override public List<TablaCuentasGestion> getDatos() { try {/*w w w . j a va 2 s .co m*/ 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; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@Override public List<TablaCuentasGestion> getDatosPorCampGest(int idCampagne, int idGestor) { try {// ww w . j a v a 2 s .c o m 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; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@Override public List<TablaCuentasGestion> getDatosPorCampGestMarc(int idCampagne, int idGestor, int marcaje) { try {//from w w w . j a v a 2s . c o m 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; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@Override public List<TablaCuentasGestion> getDatosPorPlan(int idPlan) { try {/*from ww w . j av a2 s .c om*/ 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; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@Override public List<TablaCuentasGestion> getDatosPorNombre(String nombre) { try {/*w ww. j a v a 2 s . c o m*/ 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; } }
From source file:com.demo.impl.TablaCuentasGestionImpl.java
@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 {// ww w.j av a2 s .c om 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; } }
From source file:com.demo.impl.TareasImpl.java
@Override public List<Tarea> getTareas(int gestor) { Session session; Transaction t;//from w w w .ja v a 2 s. com List<Tarea> retorno = new ArrayList<>(); try { session = HibernateUtil.getSessionFactory().openSession(); t = session.beginTransaction(); List<Tarea> lista = session.createSQLQuery("select t.* from Tarea t, Credito_SF_LT_NT_CT cl where\n" + "t.Credito_SF_LT_NT_CT_CreditosII=cl.CreditosII and cl.Cat_Gestores_Cat_Gestor_clv=" + gestor + " and\n" + "t.Activa=true;").addEntity(Tarea.class).list(); t.commit(); retorno.addAll(lista); session = HibernateUtil.getSessionFactory().openSession(); t = session.beginTransaction(); lista = session.createSQLQuery("select t.* from Tarea t, Credito_Auto ca where\n" + "t.Credito_Auto_CreditoAut=ca.CreditoAut and ca.Cat_Gestores_Cat_Gestor_clv=" + gestor + " and\n" + "t.Activa=true;").addEntity(Tarea.class).list(); t.commit(); retorno.addAll(lista); session = HibernateUtil.getSessionFactory().openSession(); t = session.beginTransaction(); lista = session.createSQLQuery("select t.* from Tarea t, Credito_Fianzas cf where\n" + "t.Credito_Fianzas_NumContratoFZ=cf.NumContratoFZ and cf.Cat_Gestores_Cat_Gestor_clv=" + gestor + " and\n" + "t.Activa=true;").addEntity(Tarea.class).list(); t.commit(); retorno.addAll(lista); session = HibernateUtil.getSessionFactory().openSession(); t = session.beginTransaction(); lista = session.createSQLQuery("select t.* from Tarea t, Credito_Hipotecario ch where\n" + "t.Credito_Hipotecario_CreditoHip=ch.CreditoHip and ch.Cat_Gestores_Cat_Gestor_clv=" + gestor + " and\n" + "t.Activa=true;").addEntity(Tarea.class).list(); t.commit(); retorno.addAll(lista); if (session.isOpen()) { session.close(); } return retorno; } catch (HibernateException he) { LogSistema.guardarlog(this.getClass().getName() + " Method: getTareas, Exception: " + he.getMessage()); List<Tarea> lista = new ArrayList<>(); return lista; } }
From source file:com.demo.impl.TareasImpl.java
@Override public List<Tarea> getTareas(String credito, int tipoCredito) { Session session; Transaction t;/*from w w w . j av a2 s .c o m*/ List<Tarea> retorno = new ArrayList<>(); try { session = HibernateUtil.getSessionFactory().openSession(); t = session.beginTransaction(); switch (tipoCredito) { case 1: break; case 2: break; case 3: break; case 4: retorno = session.createSQLQuery("select t.* from Tarea t where\n" + "t.Credito_SF_LT_NT_CT_CreditosII=" + credito + " and\n" + "t.Activa=true;") .addEntity(Tarea.class).list(); t.commit(); break; } return retorno; } catch (HibernateException he) { LogSistema.guardarlog(this.getClass().getName() + " Method: getTareas, Exception: " + he.getMessage()); List<Tarea> lista = new ArrayList<>(); return lista; } }