List of usage examples for org.hibernate.type DoubleType DoubleType
public DoubleType()
From source file:com.court.controller.GuarantorsFxmlController.java
private List<Member> getAvailableGuarantors() { Session session = HibernateUtil.getSessionFactory().openSession(); SQLQuery query = session//ww w. ja va2 s. c o m .createSQLQuery("SELECT\n" + " m.member_id AS memberId,\n" + " m.full_name AS fullName,\n" + " wb.branch_name AS workingOffice,\n" + " pb.branch_name AS payingOffice,\n" + " ml.member_loan_code AS loanCode,\n" + " ml.granted_date AS grantedDate,\n" + " ml.loan_amount AS loanAmount,\n" + " ml.loan_interest AS loanInterest,\n" + " ml.interest_per AS interestPer,\n" + " ml.interest_method AS interMethod,\n" + " ml.loan_duration AS loanDuration,\n" + " ml.duration_per AS lDurationPer,\n" + " ml.is_complete AS loanComplete\n" + "FROM\n" + " court_loan.branch wb\n" + "INNER JOIN\n" + " court_loan.member m\n" + "ON\n" + " (\n" + " wb.id = m.branch_id)\n" + "INNER JOIN\n" + " court_loan.branch pb\n" + "ON\n" + " (\n" + " m.pay_office_id = pb.id)\n" + "LEFT OUTER JOIN\n" + " court_loan.member_loan ml\n" + "ON\n" + " (\n" + " m.id = ml.member_id) ;") .addScalar("memberId", new StringType()).addScalar("fullName", new StringType()) .addScalar("workingOffice", new StringType()).addScalar("payingOffice", new StringType()) .addScalar("loanCode", new StringType()).addScalar("grantedDate", new DateType()) .addScalar("loanAmount", new DoubleType()).addScalar("loanInterest", new DoubleType()) .addScalar("interestPer", new StringType()).addScalar("interMethod", new StringType()) .addScalar("loanDuration", new IntegerType()).addScalar("lDurationPer", new StringType()) .addScalar("loanComplete", new BooleanType()); List<Object[]> rows = query.list(); List<Member> list = new ArrayList<>(); for (Object[] row : rows) { Member m = new Member(); m.setMemberId(row[0].toString()); m.setFullName(row[1].toString()); m.setBranch(new Branch(row[2].toString())); m.setPayOffice(new Branch(row[3].toString())); list.add(m); } session.close(); return list; }
From source file:com.sisrni.dao.BecaDao.java
public List<PojoBeca> getBecas(Integer idBecaSearch) { String query = "SELECT bec.ID_BECA idBeca,\n" + " bec.ANIO_GESTION anioGestion,\n" + " prb.NOMBRE_PROGRAMA as programaBeca,\n" + " per.NOMBRE_PERSONA nombreBecario,\n" + " per.APELLIDO_PERSONA apellidoBecario,\n" + " per.EMAIL_PERSONA correoBecario,\n" + " fac.NOMBRE_FACULTAD facultad,\n" + "pai.NOMBRE_PAIS paisDestino, org.NOMBRE_ORGANISMO universidadDestino, bec.MONTO_TOTAL montoBeca,IF(bec.OTORGADA = 1, 'SI','NO') as otorgada\n" + "FROM BECA bec\n" + "INNER JOIN PROGRAMA_BECA prb\n" + "ON bec.ID_PROGRAMA_BECA = prb.ID_PROGRAMA\n" + "INNER JOIN PERSONA_BECA peb\n" + "ON bec.ID_BECA = peb.ID_BECA\n" + "INNER JOIN PERSONA per\n" + "ON peb.ID_PERSONA = per.ID_PERSONA\n" + " INNER JOIN CARRERA ca\n" + " ON per.ID_CARRERA = ca.ID_CARRERA\n" + " INNER JOIN FACULTAD fac\n" + " ON ca.ID_FACULTAD = fac.ID_FACULTAD\n" + "INNER JOIN ORGANISMO org\n" + " ON bec.ID_UNIVERSIDAD = org.ID_ORGANISMO\n" + "INNER JOIN PAIS pai\n" + "ON bec.ID_PAIS_DESTINO = pai.ID_PAIS\n" + "WHERE peb.ID_TIPO_PERSONA=6"; if (idBecaSearch > 0) { query = query + " AND bec.ID_BECA=" + idBecaSearch; }/*from www .ja va 2 s .co m*/ query += " ORDER BY bec.ID_BECA DESC"; try { Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("idBeca", new IntegerType()).addScalar("anioGestion", new IntegerType()) .addScalar("programaBeca", new StringType()).addScalar("nombreBecario", new StringType()) .addScalar("apellidoBecario", new StringType()).addScalar("correoBecario", new StringType()) .addScalar("facultad", new StringType()).addScalar("paisDestino", new StringType()) .addScalar("universidadDestino", new StringType()).addScalar("montoBeca", new DoubleType()) .addScalar("otorgada", new StringType()) .setResultTransformer(Transformers.aliasToBean(PojoBeca.class)); return q.list(); } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:com.sisrni.dao.BecaDao.java
public List<BecasGestionadasPojo> getDataBecasGestionadasReportes(Integer desde, Integer hasta) { String query = "SELECT b.ANIO_GESTION anio, count(*) gestionadas,\n" + " sum(if(b.OTORGADA=1,1,0)) becasOtorgadas,\n" + " sum(if(b.OTORGADA=0,1,0)) becasDenegadas,\n" + " sum(if(OTORGADA=1,b.MONTO_TOTAL,0.00)) montoOtorgadas\n" + "FROM BECA b WHERE b.ANIO_GESTION BETWEEN " + desde + " AND " + hasta + " \n" + "GROUP BY b.ANIO_GESTION ORDER BY b.ANIO_GESTION asc"; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query).addScalar("anio", new IntegerType()) .addScalar("gestionadas", new IntegerType()).addScalar("becasOtorgadas", new IntegerType()) .addScalar("montoOtorgadas", new DoubleType()).addScalar("becasDenegadas", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(BecasGestionadasPojo.class)); return q.list(); }
From source file:com.sisrni.dao.BecaDao.java
public List<BecasGestionadasPojo> getDataBecasGestionadasGroupPaisDestino(Integer desde, Integer hasta) { String query = "select p.NOMBRE_PAIS nombrePais,count(b.ID_BECA) gestionadas,\n" + "(SELECT COUNT(i.ID_BECA) FROM beca i WHERE i.OTORGADA=1 and i.ID_PAIS_DESTINO=b.ID_PAIS_DESTINO) becasOtorgadas,\n" + "(SELECT SUM(a.MONTO_TOTAL) FROM beca a WHERE a.OTORGADA=1 and a.ID_PAIS_DESTINO=b.ID_PAIS_DESTINO) montoOtorgadas,\n" + "(SELECT COUNT(c.ID_BECA) FROM beca c WHERE c.OTORGADA=0 and c.ID_PAIS_DESTINO=b.ID_PAIS_DESTINO) becasDenegadas,\n" + "(SELECT SUM(r.MONTO_TOTAL) FROM beca r WHERE r.OTORGADA=0 and r.ID_PAIS_DESTINO=b.ID_PAIS_DESTINO) montoDenegadas\n" + "from BECA b INNER JOIN PAIS p ON b.ID_PAIS_DESTINO=p.ID_PAIS\n" + "WHERE b.ANIO_GESTION BETWEEN " + desde + " and " + hasta + "\n" + "GROUP BY b.ID_PAIS_DESTINO ORDER BY b.ID_PAIS_DESTINO asc"; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("nombrePais", new StringType()).addScalar("gestionadas", new IntegerType()) .addScalar("becasOtorgadas", new IntegerType()).addScalar("montoOtorgadas", new DoubleType()) .addScalar("becasDenegadas", new IntegerType()).addScalar("montoDenegadas", new DoubleType()) .setResultTransformer(Transformers.aliasToBean(BecasGestionadasPojo.class)); return q.list(); }
From source file:com.sisrni.dao.BecaDao.java
public List<BecasGestionadasPojo> getDataBecasGestionadasGroupFacultad(Integer desde, Integer hasta) { String query = "SELECT f.NOMBRE_FACULTAD nombreFacultad, count(*) gestionadas,\n" + "SUM(if(b.OTORGADA=0,1,0)) becasDenegadas,\n" + " SUM(if(b.OTORGADA=1,1,0)) becasOtorgadas,\n" + " SUM(if(b.OTORGADA=1,b.MONTO_TOTAL,0)) montoOtorgadas\n" + "from BECA b\n" + "INNER JOIN PERSONA_BECA pb ON b.ID_BECA = pb.ID_BECA\n" + "INNER JOIN PERSONA p ON pb.ID_PERSONA = p.ID_PERSONA\n" + "INNER JOIN CARRERA c ON p.ID_CARRERA = c.ID_CARRERA\n" + "INNER JOIN FACULTAD f ON c.ID_FACULTAD = f.ID_FACULTAD\n" + "WHERE b.ANIO_GESTION BETWEEN " + desde + " AND " + hasta + "\n" + "GROUP BY f.ID_FACULTAD ORDER BY f.NOMBRE_FACULTAD ASC"; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("nombreFacultad", new StringType()).addScalar("gestionadas", new IntegerType()) .addScalar("becasOtorgadas", new IntegerType()).addScalar("montoOtorgadas", new DoubleType()) .addScalar("becasDenegadas", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(BecasGestionadasPojo.class)); return q.list(); }
From source file:com.sisrni.dao.BecaDao.java
public List<RptDetalleBecasPojo> getDetalleBecas(Integer desde, Integer hasta) { String query = "SELECT p.NOMBRE_PERSONA nombre, p.APELLIDO_PERSONA apellido, f.NOMBRE_FACULTAD facultad,pro.NOMBRE_PROGRAMA programaBeca,b.ANIO_GESTION anio, x.NOMBRE_PAIS paisDestino,\n" + "org.NOMBRE_ORGANISMO universidadDestino,b.MONTO_TOTAL montoBeca\n" + "from BECA b INNER JOIN PERSONA_BECA pb ON b.ID_BECA = pb.ID_BECA\n" + "INNER JOIN PERSONA p ON pb.ID_PERSONA = p.ID_PERSONA\n" + "INNER JOIN CARRERA c ON p.ID_CARRERA = c.ID_CARRERA\n" + "INNER JOIN FACULTAD f ON c.ID_FACULTAD = f.ID_FACULTAD\n" + "INNER JOIN PROGRAMA_BECA pro ON b.ID_PROGRAMA_BECA = pro.ID_PROGRAMA\n" + "INNER JOIN ORGANISMO org ON b.ID_UNIVERSIDAD = org.ID_ORGANISMO\n" + "INNER JOIN PAIS x ON org.ID_PAIS= x.ID_PAIS\n" + "WHERE pb.ID_TIPO_PERSONA = 6 AND b.OTORGADA=1\n" + "AND b.ANIO_GESTION BETWEEN " + desde + " AND " + hasta + "\n" + "ORDER BY p.NOMBRE_PERSONA,p.APELLIDO_PERSONA, b.ANIO_GESTION desc"; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("nombre", new StringType()).addScalar("apellido", new StringType()) .addScalar("facultad", new StringType()).addScalar("programaBeca", new StringType()) .addScalar("anio", new IntegerType()).addScalar("paisDestino", new StringType()) .addScalar("universidadDestino", new StringType()).addScalar("montoBeca", new DoubleType()) .setResultTransformer(Transformers.aliasToBean(RptDetalleBecasPojo.class)); return q.list(); }
From source file:com.sisrni.dao.BecaDao.java
public List<BecasGestionadasPojo> getDataBecasGestionadasGroupOrganismos(Integer desde, Integer hasta) { String query = "SELECT o.NOMBRE_ORGANISMO organismo, count(*) gestionadas,\n" + " SUM(if(b.OTORGADA=1,1,0)) becasOtorgadas,\n" + " SUM(if(b.OTORGADA=0,1,0)) becasDenegadas,\n" + " SUM(if(b.OTORGADA=1,b.MONTO_TOTAL,0)) montoOtorgadas\n" + "FROM BECA b INNER JOIN ORGANISMO o\n" + "ON b.ID_ORGANISMO_COOPERANTE = o.ID_ORGANISMO\n" + "WHERE\n" + " b.ANIO_GESTION BETWEEN " + desde + " AND " + hasta + "\n" + " GROUP BY b.ID_ORGANISMO_COOPERANTE"; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("organismo", new StringType()).addScalar("gestionadas", new IntegerType()) .addScalar("becasOtorgadas", new IntegerType()).addScalar("montoOtorgadas", new DoubleType()) .addScalar("becasDenegadas", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(BecasGestionadasPojo.class)); return q.list(); }
From source file:com.sisrni.dao.BecaDao.java
public List<PojoMapaInteractivoBecas> getBecastListToCharts(List<String> paisSelected, List<String> tipoBecaSelected, String desde, String hasta) { String wherePais = ""; String whereTipoBeca = ""; String groupBy = " GROUP BY b.ID_PAIS_DESTINO"; String limite = ""; List<String> paisesFinales = new ArrayList<String>(); if (paisSelected.size() > 0) { wherePais = wherePais + " AND pa.ID_PAIS IN (" + String.join(",", paisSelected) + ")"; } else {//from w ww .j a v a2 s .com limite += " LIMIT 5"; } if (tipoBecaSelected.size() > 0) { whereTipoBeca += " AND b.ID_TIPO_BECA IN (" + String.join(",", tipoBecaSelected) + ")"; } String query = "SELECT pa.ID_PAIS idPais,\n" + " pa.CODIGO_PAIS codigoPais,\n" + " pa.NOMBRE_PAIS nombrePais,\n" + " COUNT(b.ID_BECA) cantidadBecas,\n" + " SUM(b.MONTO_TOTAL) montoCooperacion\n" + "FROM BECA b INNER JOIN PAIS pa ON b.ID_PAIS_DESTINO= pa.ID_PAIS\n" + "WHERE b.OTORGADA=1 AND b.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + wherePais + whereTipoBeca + groupBy;// try { Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("idPais", new IntegerType()).addScalar("codigoPais", new StringType()) .addScalar("nombrePais", new StringType()).addScalar("montoCooperacion", new DoubleType()) .addScalar("cantidadBecas", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(PojoMapaInteractivoBecas.class)); List<PojoMapaInteractivoBecas> listPojos = q.list(); if (listPojos.size() > 0) { for (PojoMapaInteractivoBecas pj : listPojos) { paisesFinales.add(pj.getIdPais() + ""); } String qt = "SELECT tb.ID_TIPO_BECA idTipoBeca,\n" + " tb.NOMBRE_TIPO_BECA nombreTipoBeca,\n" + " count(b.ID_BECA) cantidad\n" + "from BECA b INNER JOIN TIPO_BECA tb\n" + "ON b.ID_TIPO_BECA = tb.ID_TIPO_BECA\n" + "WHERE b.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + " AND b.ID_PAIS_DESTINO IN (" + String.join(",", paisesFinales) + ")\n" + "AND b.ID_TIPO_BECA IN (" + String.join(",", tipoBecaSelected) + ")\n" + " AND b.OTORGADA=1\n" + "GROUP BY b.ID_TIPO_BECA"; Query rtp = getSessionFactory().getCurrentSession().createSQLQuery(qt) .addScalar("idTipoBeca", new IntegerType()).addScalar("nombreTipoBeca", new StringType()) .addScalar("cantidad", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(PojoBecasByTipo.class)); List<PojoBecasByTipo> listTipos = rtp.list(); for (PojoMapaInteractivoBecas pj : listPojos) { String qp = "SELECT * FROM BECA pr \n" + " WHERE pr.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + "AND pr.OTORGADA=1 AND pr.ID_PAIS_DESTINO=" + pj.getIdPais(); //String qp = "from Proyect pr Where pr.idPaisCooperante='" + pj.getCodigoPais() + "' and pr.idTipoProyecto in (" + String.join(",", tipoProyectoSelected) + ") and pr.anioGestion between " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta); Query r = getSessionFactory().getCurrentSession().createSQLQuery(qp).addEntity(Beca.class); pj.setBecastList(r.list()); pj.setSeries(listTipos); } } return listPojos; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:com.sisrni.dao.MovilidadDao.java
public List<PojoMapaMovilidad> getBecastListToCharts(Integer tipoMovilidad, List<String> paisSelected, List<String> categoriaSelected, String desde, String hasta) { String campoUnion = ""; if (tipoMovilidad == 1) { campoUnion = campoUnion + "m.ID_PAIS_ORIGEN"; } else {//from w w w.j a v a 2s . c o m campoUnion = campoUnion + "m.ID_PAIS_DESTINO"; } String query = "SELECT\n" + " p.ID_PAIS idPais,\n" + " p.CODIGO_PAIS codigoPais," + " p.NOMBRE_PAIS nombrePais," + " count(*) cantidadMovilidades," + " SUM(m.VIATICOS+m.PAGO_DE_CURSO+m.VOLETO_AEREO) montoMovilidades " + " FROM MOVILIDAD m INNER JOIN PAIS p ON " + campoUnion + " = p.ID_PAIS" + " WHERE m.ID_TIPO_MOVILIDAD = " + tipoMovilidad + " and m.ID_ETAPA_MOVILIDAD=3 " + " and YEAR(m.FECHA_INICIO) BETWEEN " + Integer.parseInt(desde) + " and " + Integer.parseInt(hasta) + " and " + campoUnion + " IN " + "(" + String.join(",", paisSelected) + ")" + " and m.ID_CATEGORIA IN " + "(" + String.join(",", categoriaSelected) + ")" + "GROUP BY " + campoUnion; Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("idPais", new IntegerType()).addScalar("codigoPais", new StringType()) .addScalar("nombrePais", new StringType()).addScalar("cantidadMovilidades", new IntegerType()) .addScalar("montoMovilidades", new DoubleType()) .setResultTransformer(Transformers.aliasToBean(PojoMapaMovilidad.class)); return q.list(); }
From source file:com.sisrni.dao.ProyectoDao.java
public List<PojoMapaInteractivo> getProjectListToCharts(List<String> paisSelected, List<String> tipoProyectoSelected, String desde, String hasta) { String wherePais = ""; String whereTipoProyecto = ""; String groupBy = " GROUP BY pr.ID_PAIS_COOPERANTE"; String limite = ""; List<String> paisesFinales = new ArrayList<String>(); if (paisSelected.size() > 0) { wherePais = wherePais + " AND pa.ID_PAIS IN (" + String.join(",", paisSelected) + ")"; } else {//from w w w . j a v a2 s. co m limite += " LIMIT 5"; } if (tipoProyectoSelected.size() > 0) { whereTipoProyecto += " AND pr.ID_TIPO_PROYECTO IN (" + String.join(",", tipoProyectoSelected) + ")"; } String query = "SELECT pa.ID_PAIS as idPais,pa.CODIGO_PAIS as codigoPais, pa.NOMBRE_PAIS as nombrePais, SUM(pr.MONTO_PROYECTO) as montoCooperacion,COUNT(pr.ID_PROYECTO) as cantidadProyectos\n" + " FROM PROYECTO pr INNER JOIN PAIS pa ON pr.ID_PAIS_COOPERANTE = pa.ID_PAIS\n" + " WHERE pr.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + wherePais + whereTipoProyecto + groupBy + limite; try { Query q = getSessionFactory().getCurrentSession().createSQLQuery(query) .addScalar("idPais", new IntegerType()).addScalar("codigoPais", new StringType()) .addScalar("nombrePais", new StringType()).addScalar("montoCooperacion", new DoubleType()) .addScalar("cantidadProyectos", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(PojoMapaInteractivo.class)); List<PojoMapaInteractivo> listPojos = q.list(); if (listPojos.size() > 0) { for (PojoMapaInteractivo pj : listPojos) { paisesFinales.add(pj.idPais.toString().trim()); } String qt = "SELECT tp.ID_TIPO_PROYECTO as idTipoProyecto,tp.NOMBRE_TIPO_PROYECTO as nombreTipoProyecto,count(pr.ID_PROYECTO) as cantidadProyectos FROM PROYECTO pr INNER JOIN TIPO_PROYECTO tp ON pr.ID_TIPO_PROYECTO = tp.ID_TIPO_PROYECTO\n" + " WHERE pr.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + "AND pr.ID_PAIS_COOPERANTE IN(" + String.join(",", paisesFinales) + ")" + whereTipoProyecto + " GROUP BY tp.ID_TIPO_PROYECTO"; Query rtp = getSessionFactory().getCurrentSession().createSQLQuery(qt) .addScalar("idTipoProyecto", new IntegerType()) .addScalar("nombreTipoProyecto", new StringType()) .addScalar("cantidadProyectos", new IntegerType()) .setResultTransformer(Transformers.aliasToBean(PojoProyectosByTipo.class)); List<PojoProyectosByTipo> listTipos = rtp.list(); for (PojoMapaInteractivo pj : listPojos) { String qp = "SELECT * FROM PROYECTO pr \n" + " WHERE pr.ANIO_GESTION BETWEEN " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta) + "\n" + "AND pr.ID_PAIS_COOPERANTE=" + pj.getIdPais() + whereTipoProyecto; //String qp = "from Proyect pr Where pr.idPaisCooperante='" + pj.getCodigoPais() + "' and pr.idTipoProyecto in (" + String.join(",", tipoProyectoSelected) + ") and pr.anioGestion between " + Integer.parseInt(desde) + " AND " + Integer.parseInt(hasta); Query r = getSessionFactory().getCurrentSession().createSQLQuery(qp).addEntity(Proyecto.class); pj.setProjectList(r.list()); pj.setSeries(listTipos); } } return listPojos; } catch (Exception e) { e.printStackTrace(); } return null; }