public static void viewAd(String id) {
Ad ad = Ad.findById(Long.parseLong(id));
List<Category> cats = Category.find("categorytype_id=?1 order by id", "1").fetch();
EntityManager entityManager = play.db.jpa.JPA.em();
List<BigInteger> bCounts =
entityManager
.createNativeQuery(
"select count(*) as maxCount from Ad as a group by category_id order by maxCount")
.getResultList();
int min = bCounts.get(0).intValue();
int max = bCounts.get(bCounts.size() - 1).intValue();
bCounts =
entityManager
.createNativeQuery(
"select count(*) as maxCount from Ad as a group by category_id order by category_id ")
.getResultList();
List<String> fonts = new ArrayList<String>();
for (int i = 0; i < bCounts.size(); i++) {
BigInteger count = bCounts.get(i);
int x = Ads.getFontSize(count.intValue(), min, max);
fonts.add(String.valueOf(x));
}
render(ad, fonts, min, max, cats);
}
@Override
public Long countbfjlBeanByCondition(bfjlBean bfjlBean) {
String strHql =
"select count(t.bfjl_id) from T_BFJL t ,t_khgl t1 ,t_bfjl t2 ,t_employee t3 where t.khgl_id =t1.khgl_id and t.bfjl_id =t2.bfjl_id and t.employee_id =t3.employee_id";
Query query = entityManager.createNativeQuery(strHql);
return Long.parseLong(query.getSingleResult().toString());
}
@SuppressWarnings("unchecked")
public List<ReportTask> findTODOTasks() {
String sql =
"select * from report_tasks where status in ('%s', '%s') and ( gen_count IS NULL or gen_count <= %d )";
sql = String.format(sql, Status.planned, Status.inprogress, MAX_GENERATION_COUNT);
Query query = manager.createNativeQuery(sql, ReportTask.class);
query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
List<?> result = query.getResultList();
List<ReportTask> tasks = (List<ReportTask>) result;
StringBuffer idSbs = new StringBuffer();
// fill project identifier
if (!tasks.isEmpty()) {
List<Long> pId = new ArrayList<Long>();
for (ReportTask task : tasks) {
pId.add(task.getProjectId());
}
Map<Long, String> projects = new HashMap<Long, String>();
sql = "select id, name from projects where id in ( %s )";
query = manager.createNativeQuery(String.format(sql, StringUtils.join(pId, ',')));
query.setHint(QueryHints.HINT_CACHE_MODE, CacheMode.IGNORE);
result = query.getResultList();
for (Object o : result) {
Object[] objs = (Object[]) o;
projects.put(((Number) objs[0]).longValue(), objs[1].toString());
}
for (ReportTask t : tasks) {
t.setProjectName(projects.get(t.getProjectId()));
idSbs.append(t.getId().toString()).append(",");
}
}
logger.info("Find " + tasks.size() + " tasks! :: " + idSbs);
return tasks;
}
@PostConstruct
public void startup() throws Exception {
Query q = entityManager.createNativeQuery("DELETE from ADDRESS");
q.executeUpdate();
q = entityManager.createNativeQuery("DELETE from PERSON");
q.executeUpdate();
entityManager.flush();
p = new Person();
p.setFirstName("Shane");
p.setLastName("Bryzak");
p.setDateOfBirth(df.parse("1901-01-01"));
p.setAddresses(new ArrayList<Address>());
a = new Address();
a.setPerson(p);
a.setStreetNo(100);
a.setStreetName("Main");
a.setSuburb("Pleasantville");
a.setPostCode("32123");
a.setCountry("Australia");
p.getAddresses().add(a);
a = new Address();
a.setPerson(p);
a.setStreetNo(57);
a.setStreetName("1st Avenue");
a.setSuburb("Pittsville");
a.setPostCode("32411");
a.setCountry("Australia");
p.getAddresses().add(a);
entityManager.persist(p);
entityManager.flush();
p = new Person();
p.setFirstName("Jozef");
p.setLastName("Hartinger");
p.setDateOfBirth(df.parse("1901-01-01"));
p.setAddresses(new ArrayList<Address>());
a = new Address();
a.setPerson(p);
a.setStreetNo(99);
a.setStreetName("Purkynova");
a.setSuburb("Kralovo pole");
a.setPostCode("60200");
a.setCountry("Czech republic");
p.getAddresses().add(a);
entityManager.persist(p);
entityManager.flush();
}
@Override
public List<Request> findEntitiesWithAvailableStatus(boolean all, int maxResults, int firstResult)
throws Exception {
EntityManager entityManager = getEntityManager();
RequestDAO rdao = new RequestDAO();
Long resultIdLong;
Request cr;
Query nquery =
entityManager.createNativeQuery(nativeQuerySequenceFindRequestWithAvailableProducts);
try {
if (!all) {
nquery.setMaxResults(maxResults);
nquery.setFirstResult(firstResult);
}
List resultListId = nquery.getResultList();
List<Request> resultList = new ArrayList<Request>();
for (Object elem : resultListId) {
resultIdLong = Long.parseLong(elem.toString());
cr = rdao.findById(resultIdLong);
resultList.add(cr);
}
for (Object elem : resultList) {}
return resultList;
} catch (NullPointerException e) {
LogBean.getLogger().debug(FINDMSG + " " + java.util.Calendar.getInstance().getTime());
return null;
}
}
/** Creates a query. */
@Override
public Query createNativeQuery(String sql, String map) {
EntityManager em = getCurrent();
if (em != null) {
return em.createNativeQuery(sql, map);
}
em = createEntityManager();
try {
return em.createNativeQuery(sql, map);
} finally {
freeEntityManager(em);
}
}
@Override
public List<Dish> genDishByBudgetAndCate(double budget, int cate) {
if (budget > 0 && cate != 0) {
EntityManager em = entityManager;
try {
Query q =
em.createNativeQuery(
"Select * From dish Where price <= ? And id_category = ?", Dish.class);
q.setParameter(1, budget);
q.setParameter(2, cate);
q.setHint(QueryHints.MAINTAIN_CACHE, HintValues.FALSE);
List<Dish> tmpLstDish = q.getResultList();
if (tmpLstDish.size() > 10 && tmpLstDish != null) {
List<Dish> lstDish = new ArrayList<Dish>();
Collections.shuffle(tmpLstDish);
for (int i = 0; i < 10; i++) {
lstDish.add(tmpLstDish.get(i));
}
return lstDish;
} else {
return tmpLstDish;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
public Object getSpecialPrice(String nombre) {
Query q =
em.createNativeQuery(
"SELECT PRECIO FROM GENERIC.PUBLIC.PRECIOESPECIAL p WHERE p.NOMBRE=?1");
q.setParameter(1, nombre);
return q.getSingleResult();
}
@Override
public void execute(Application application) {
Query query =
em.createNativeQuery("{call PROC_BORROW_OLD(?)}")
.setParameter(1, application.getApplicationNo());
query.executeUpdate();
}
public void deleteUserCard(User user, long cardId) {
entityManager
.createNativeQuery("DELETE FROM Card_User WHERE user = :user AND card = :cardId")
.setParameter("user", user.getId())
.setParameter("cardId", cardId)
.executeUpdate();
}
public List<Object[]> acumuladoPuntossucursal(List<Evaluacion> eval, Dependencia dep) {
List<Object[]> res = null;
String cadena = eval.get(0).getId().toString();
for (int i = 1; i < eval.size(); i++) {
cadena += "," + eval.get(i).getId().toString();
}
StringBuffer s =
new StringBuffer("select sum(puntos/c.PESO),v.ID_EVALUACION")
.append(" from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) ")
.append(
" join dependencia d on(d.ID=e.ID_DEPENDENCIA) join evaluacion c on (v.ID_EVALUACION=c.ID) join estado_empleado s on(e.ID=s.ID_EMPLEADO) ")
.append(" where id_evaluacion in(")
.append(cadena)
.append(") and s.activo=1 and d.id=")
.append(dep.getId().toString())
.append(" group by v.ID_EVALUACION order by v.ID_EVALUACION");
try {
res = em.createNativeQuery(s.toString()).getResultList();
} catch (Exception e) {
return null;
}
return res;
}
@Override
public int insertCorte(CorteCaja cc) {
System.out.println("+++++++++++++EJB++++++++++");
System.out.println(cc.toString());
Query query =
em.createNativeQuery(
"INSERT INTO Corte_Caja(ID_CORTE_CAJA_PK,ID_CAJA_FK,FECHA,CANT_CHEQUES_ANT,"
+ " MONTO_CHEQUES_ANT,SALDO_ANTERIOR,CANT_CHEQUES_NUEVOS,MONTO_CHEQUES_NUEVOS,NUEVO_SALDO,"
+ " COMENTARIOS,ID_USER_FK,ID_STATUS_FK,MONTO_CUENTA_ANT,MONTO_CUENTA_NUEVO) VALUES(?,?,sysdate,?,?,?,?,?,?,?,?,?,?,?)");
query.setParameter(1, cc.getIdCorteCajaPk());
query.setParameter(2, cc.getIdCajaFk());
query.setParameter(3, cc.getCantChequesAnt());
query.setParameter(4, cc.getMontoChequesAnt());
query.setParameter(5, cc.getSaldoAnterior());
query.setParameter(6, cc.getCantChequesNuevos());
query.setParameter(7, cc.getMontoChequesNuevos());
query.setParameter(8, cc.getSaldoNuevo());
query.setParameter(9, cc.getComentarios());
query.setParameter(10, cc.getIdUserFk());
query.setParameter(11, cc.getIdStatusFk());
query.setParameter(12, cc.getMontoCuentaAnterior());
query.setParameter(13, cc.getMontoCuentaNuevo());
return query.executeUpdate();
}
private Collection<GroupData> fetchGroupDataForUser(
final User user, final boolean restrictToSuscribed) {
final String ifConnectedColumns =
"max(ifnull(USER_ID=:userId, false))>0, sum(ifnull(USER_ID=:userId AND LAST_VISIT<PUBLIC_MESSAGES.`DATE`, false))";
final Query query =
entityManager.createNativeQuery(
"select GROUPS.ID, name, count(DISTINCT GROUP_USER.USER_ID), "
+ (user != null ? ifConnectedColumns : " 0,0")
+ " from GROUPS left join GROUP_USER on GROUP_ID=ID "
+ " left join PUBLIC_MESSAGES on PUBLIC_MESSAGES.GROUP_ID=GROUP_USER.GROUP_ID "
+ (restrictToSuscribed ? " where GROUP_USER.USER_ID=:userId" : "")
+ " group by GROUPS.ID order by CREATION_DATE");
if (user != null) query.setParameter("userId", user.getId());
final List<Object[]> list = query.getResultList();
final Collection<GroupData> result = new ArrayList<GroupData>(list.size());
for (final Object[] o : list)
result.add(
new GroupData(
((Number) o[0]).longValue(),
UserStringImpl.valueOf(String.valueOf(o[1])),
((Number) o[2]).longValue(),
((Number) o[3]).intValue() != 0,
((Number) o[4]).intValue()));
return result;
}
/**
* getManagedPagedDataByActivity
*
* @param firstRow
* @param totalRows
* @param order
* @return list of dataverses ordered by activity
*/
public List getManagedPagedDataByLastUpdated(int firstRow, int totalRows, String order) {
List<VDC> list = new ArrayList();
try {
String queryString =
"SELECT vdc.id, vdc.name, vdc.alias, vdc.affiliation, vdc.releasedate, "
+ "vdc.dtype, vdc.createddate, vdc.dvndescription, username, "
+ "CASE WHEN count(owner_id) is null THEN 0 ELSE count(owner_id) END AS owned_studies, "
+ "CASE WHEN max(lastupdatetime) is null THEN vdc.releasedate ELSE max(lastupdatetime) END as lastupdated, "
+ "vdc.restricted "
+ "FROM vdcuser, vdc "
+ "LEFT OUTER JOIN study on vdc.id = study.owner_id "
+ "LEFT OUTER JOIN studyfileactivity on study.id = studyfileactivity.study_id "
+ "WHERE vdc.creator_id = vdcuser.id "
+ "GROUP BY vdc.id, vdc.name, vdc.alias, vdc.affiliation, vdc.releasedate, vdc.dtype, vdc.createddate, vdc.dvndescription, username, vdc.restricted "
+ "ORDER BY "
+ "CASE WHEN max(lastupdatetime) is null THEN vdc.releasedate ELSE max(lastupdatetime) END "
+ order
+ " LIMIT "
+ totalRows
+ " OFFSET "
+ firstRow;
Query query = em.createNativeQuery(queryString);
list = (List<VDC>) query.getResultList();
} catch (Exception e) {
// do something here with the exception
list = new ArrayList();
} finally {
return list;
}
}
public List<Object[]> acumuladoPuntosEmpleadoPeso(List<Evaluacion> eval, Empleado emp) {
List<Object[]> res = null;
String cadena = eval.get(0).getId().toString();
for (int i = 1; i < eval.size(); i++) {
cadena += "," + eval.get(i).getId().toString();
}
StringBuffer s =
new StringBuffer(
"select (puntos/c.peso),v.ID_EVALUACION from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) join evaluacion c on (v.ID_EVALUACION=c.ID) ")
.append(" where id_evaluacion in(")
.append(cadena)
.append(") and e.id=")
.append(emp.getId().toString())
.append(" order by v.ID_EVALUACION");
try {
res = em.createNativeQuery(s.toString()).getResultList();
} catch (Exception e) {
return null;
}
return res;
}
public Map<Object, Object> getBannerListAndTotalCount(
int start, ListFilterCriteria filterCriteria) {
log.debug("BannerDao.getBannerListAndTotalCount() ");
EntityManager entityManager = getEntityManagerForActiveEntities();
Query bannerCountQuery =
entityManager.createNativeQuery(
"select count(*) from banner b where b."
+ filterCriteria.getColumn()
+ " like '%"
+ filterCriteria.getValueToSearch()
+ "%'");
Query bannerListQuery =
entityManager.createQuery(
"select b from Banner b where b."
+ filterCriteria.getColumn()
+ " like '%"
+ filterCriteria.getValueToSearch()
+ "%' order by "
+ filterCriteria.getSortBy()
+ " "
+ filterCriteria.getSortOrder());
bannerListQuery.setFirstResult(start);
bannerListQuery.setMaxResults(filterCriteria.getItemsPerPage());
List<Banner> bannersList = bannerListQuery.getResultList();
Map<Object, Object> bannerDetails = new HashMap<Object, Object>();
bannerDetails.put("list", bannersList);
bannerDetails.put("totalCount", ((BigInteger) bannerCountQuery.getSingleResult()).intValue());
return bannerDetails;
}
public List<MrTblPrepAndSendHistory> mrTblPrepAndSendHistory(
String jobName, int organizeId, String processId) {
String sqlString =
" SELECT rownum as id, entity.*\n"
+ " FROM MR_TBL_PREP_AND_SEND_HISTORY entity\n"
+ " WHERE entity.ORGANIZE_ID = "
+ organizeId
+ "\n"
+ " AND entity.PROCESS_ID = '"
+ processId
+ "'\n"
+ " AND entity.JOB_NAME = '"
+ jobName
+ "'\n"
+ " AND entity.FIRM_BUCKET =\n"
+ " (SELECT MAX(FIRM_BUCKET)\n"
+ " FROM MR_TBL_PREP_AND_SEND_HISTORY\n"
+ " WHERE ORGANIZE_ID = 1\n"
+ " AND PROCESS_ID = 'IP'\n"
+ " AND JOB_NAME = '"
+ jobName
+ "')";
Query query = em.createNativeQuery(sqlString, MrTblPrepAndSendHistory.class);
return query.getResultList();
}
public List<String> mr1008PDataDesName(int organizeId, String processId) {
String sqlString =
"SELECT rownum as id, JOB_GROUP_DESC as jobGroupDesc\n"
+ "FROM\n"
+ " (SELECT *\n"
+ " FROM MR_TBL_DATA_TO_OTHER_CONFIG\n"
+ " WHERE ORGANIZE_ID = 1\n"
+ " AND PROCESS_ID = 'IP'\n"
+ " ORDER BY JOB_SEQ ASC\n"
+ " ) entity\n"
+ "INNER JOIN\n"
+ " (SELECT JOB_GROUP_NAME ,\n"
+ " JOB_GROUP_SEQ ,\n"
+ " JOB_GROUP_DESC\n"
+ " FROM MR_TBL_DATA_TO_OTHER_JOB_GROUP\n"
+ " WHERE ORGANIZE_ID = "
+ organizeId
+ "\n"
+ " AND PROCESS_ID = '"
+ processId
+ "'\n"
+ " ORDER BY JOB_GROUP_SEQ ASC\n"
+ " ) dplu\n"
+ "ON entity.JOB_GROUP_NAME = dplu.JOB_GROUP_NAME\n"
+ "GROUP BY JOB_GROUP_DESC, rownum\n"
+ "ORDER BY JOB_GROUP_DESC ASC";
Query query = em.createNativeQuery(sqlString, String.class);
return query.getResultList();
}
// 员工管理
@Override
public List<Object[]> ygglfindygglByCondition(Employee Employee, Integer start, Integer limit) {
String strHql =
"select t.employee_id,t.yggl_djrq ,t.employee_no,t.yggl_rybhbj,t.employee_name,t.yggl_ywxm,t1.organization_id,t1.organization_no,t1.organization_name, t2.xbsd_id,t2.xbsd_bh,t2.xbsd_mc ,t.yggl_sfzh,t3.jzzt_id,t3.jzzt_bh,t3.jzzt_mc,t.yggl_csrq,t4.hyzk_id,t4.hyzk_bh,t4.hyzk_mc,t5.xxsd_id,t5.xxsd_bh, t5.xxsd_mc ,t6.jgsd_id,t6.jgsd_bh,t6.jgsd_mc ,t7.gjsd_id,t7.gjsd_bh,t7.gjsd_mc ,t8.mzsd_id,t8.mzsd_bh,t8.mzsd_mc, t9.sdzw_id,t9.sdzw_bh,t9.sdzw_mc, t10.xsl_id,t10.xsl_bh,t10.xsl_mc,t.yggl_zzmm,t.yggl_rzrq,t.yggl_zzrq,t.yggl_syqc,t.yggl_lzrq,t11.jszc_id,t11.jszc_bh,t11.jszc_mc,t12.sdgw_id,t12.sdgw_bh, t12.sdgw_mc,t.yggl_dzyj,t.yggl_hjdh,t.yggl_hjyb,t.yggl_hjdz,t.yggl_jtdh,t.yggl_jtyb,t.yggl_jtzz,t.yggl_xdh,t.yggl_xyb,t.yggl_xzz,t.yggl_jjlxr, t.yggl_jjlxyb,t.yggl_jjlxdh,t.yggl_jjlxrdz,t13.xlsd_id,t13.xlsd_bh,t13.xlsd_mc ,t.yggl_xw,t.yggl_byyx,t.yggl_sxzy,t.yggl_wysp,t.yggl_tjrq,t.yggl_dqrq, t.yggl_rjrq,t.yggl_hzhm,t.yggl_htqs,t.yggl_htjs,t.yggl_htqc,t.yggl_gzfa,t.yggl_jbxz,t.yggl_fxfs,t.yggl_yhkh,t.yggl_sdsksfs,t.yggl_bz "
+ "from T_EMPLOYEE t ,t_organization t1 ,t_xbsd t2 ,t_jzzt t3 ,t_hyzk t4 , t_xxsd t5 ,t_jgsd t6 ,t_gjsd t7 ,t_mzsd t8 ,t_sdzw t9, t_xsl t10 ,t_jszc t11 ,t_sdgw t12 ,t_xlsd t13 where t.organization_id=t1.organization_id and t.xbsd_id =t2.xbsd_id and t.xbsd_id=t2.xbsd_id and t.jzzt_id =t3.jzzt_id and t.hyzk_id=t4.hyzk_id and t.xxsd_id=t5.xxsd_id and t.jgsd_id=t6.jgsd_id and t.gjsd_id=t7.gjsd_id and t.mzsd_id=t8.mzsd_id and t.sdzw_id=t9.sdzw_id and t.xsl_id=t10.xsl_id and t.jszc_id=t11.jszc_id and t.sdgw_id=t12.sdgw_id and t.xlsd_id =t13.xlsd_id";
// 人员姓名
if (null != Employee.getEmployeeName() && !"".equals(Employee.getEmployeeName().trim())) {
strHql += " and t.employee_name LIKE('%" + Employee.getEmployeeName().trim() + "%')";
}
// 所属部门
if (null != Employee.getYgglbz() && !"".equals(Employee.getYgglbz())) {
strHql += " and t1.organization_name LIKE('%" + Employee.getYgglbz() + "%')";
}
if (SearchUntil.search_strHql != null) {
strHql += SearchUntil.search_strHql;
SearchUntil.search_strHql = "";
}
strHql += " ORDER BY t.employee_id desc";
Query query = entityManager.createNativeQuery(strHql);
if (null != start && null != limit) {
if (0 == start) start = 1;
query.setFirstResult((start.intValue() - 1) * limit);
query.setMaxResults(limit.intValue());
}
List<Object[]> lstO = query.getResultList();
return lstO;
}
/** Test of an SQL query using a result set mapping giving two entities (Login + LoginAccount). */
public void testSQLResult() {
if (vendorID == null) {
return;
}
try {
EntityManager em = getEM();
EntityTransaction tx = em.getTransaction();
try {
tx.begin();
LoginAccount acct = new LoginAccount(1, "Fred", "Flintstone");
Login login = new Login("flintstone", "pwd");
acct.setLogin(login);
em.persist(login);
em.persist(acct);
tx.commit();
} finally {
if (tx.isActive()) {
tx.rollback();
}
em.close();
}
em = getEM();
tx = em.getTransaction();
try {
tx.begin();
// Check the results
List result =
em.createNativeQuery(
"SELECT P.ID, P.FIRSTNAME, P.LASTNAME, P.LOGIN_ID, L.ID, L.USERNAME, L.PASSWORD "
+ "FROM JPA_AN_LOGINACCOUNT P, JPA_AN_LOGIN L",
"AN_LOGIN_PLUS_ACCOUNT")
.getResultList();
assertEquals(1, result.size());
Iterator iter = result.iterator();
while (iter.hasNext()) {
// Should be a String (type of "ID" column)
Object[] obj = (Object[]) iter.next();
assertEquals("Fred", ((LoginAccount) obj[0]).getFirstName());
assertEquals("flintstone", ((Login) obj[1]).getUserName());
assertEquals("Fred", ((LoginAccount) obj[0]).getFirstName());
assertTrue(((LoginAccount) obj[0]).getLogin() == ((Login) obj[1]));
}
tx.rollback();
} finally {
if (tx.isActive()) {
tx.rollback();
}
em.close();
}
} finally {
clean(LoginAccount.class);
clean(Login.class);
}
}
@Override
@Transactional(readOnly = true)
public Page<UserGroupCheck> findAllByPage(Pageable pageable, Long groupId, long total) {
// TODO use setParameter
String sql = USER_GROUP_CHECK_QUERY;
String orderBy = "";
String sort = "";
Iterator<Order> i = pageable.getSort().iterator();
while (i.hasNext()) {
Order order = i.next();
orderBy = order.getProperty();
sort = order.getDirection().name();
}
sql = sql.replace(ORDER_BY, orderBy);
sql = sql.replace(SORT, sort);
Query query = entityManager.createNativeQuery(sql);
query.setParameter(GROUP_ID, groupId);
query.setParameter(LIMIT, pageable.getPageSize());
query.setParameter(OFFSET, pageable.getOffset());
List<UserGroupCheck> result = JpaUtil.getResultList(query, UserGroupCheck.class);
Page<UserGroupCheck> page = new PageImpl<>(result, pageable, total);
return page;
}
@Override
public List<Productos> productosLike(String par, String id) {
/* String jpql = "SELECT EX.productos FROM Existencias EX "
+ " WHERE EX.bodegas.idBodega = '59' AND EX.productos.idProducto LIKE :par"
+ " OR EX.productos.nombre LIKE :par";*/
String jpql =
"SELECT PR FROM Productos PR where PR.idProducto LIKE :par OR PR.nombre LIKE :par";
Query query = em.createQuery(jpql);
String jpql1 =
"select DISTINCT pro.IdProducto, pro.Nombre from bodegaVirtualMM vb inner join Productos pro on vb.IdProducto = pro.IdProducto and vb.IdVendedor = ? where pro.IdProducto = ? or pro.Nombre like ? ";
// String jpql1 = "select DISTINCT pro.IdProducto, pro.Nombre from bodegaVirtualMM vb inner
// join Productos pro on vb.IdProducto = pro.IdProducto and vb.IdVendedor = ? order by
// pro.Nombre";
Query query2 = em.createNativeQuery(jpql1, Productos.class);
query2.setParameter(1, id);
query2.setParameter(2, par);
query2.setParameter(3, "%" + par + "%");
// query2.setParameter(1, "%"+ par +"%");
// query2.setParameter("par", "%"+ par +"%");
productosLikeList = query2.getResultList();
return productosLikeList;
/* String jpql = "select us.UID, us.Nombre as 'NombreUsuario' , pm.Nombre as 'NombrePerfil', pm.Descripcion as 'DescripcionPerfil' ,\n" +
" am.Nombre as NombreApp, am.Descripcion as 'DescripcionMovil' , am.Logo from Usuarios us inner join PerfilesMoviles pm \n" +
"on us.IdPerfilMovil = pm.IdPerfilMovil inner join AppPorPerfiles ap on us.IdPerfilMovil = ap.IdPerfilMovil\n" +
"inner join AppMoviles am on ap.IdAppMovil = am.IdAppMovil\n" +
"where us.UID = :par" ;*/
}
public void deleteMessageFor(final Long id, final User user) {
final Query query =
entityManager.createNativeQuery(
"update MESSAGES SET deleted_by=:user_id where ID=:id and deleted_by IS NULL and receiver_id <> sender_id and (receiver_id=:user_id or sender_id=:user_id)");
query.setParameter("user_id", user.getId());
query.setParameter("id", id);
final int updated = query.executeUpdate();
if (updated == 0) {
final Query query2 =
entityManager.createNativeQuery(
"delete from MESSAGES where ID=:id and (receiver_id=:user_id or sender_id=:user_id)");
query2.setParameter("id", id);
query2.setParameter("user_id", user.getId());
query2.executeUpdate();
}
}
public List<Object[]> cantidadEmpleados(List<Evaluacion> eval, Dependencia dep) {
List<Object[]> res = null;
String cadena = eval.get(0).getId().toString();
for (int i = 1; i < eval.size(); i++) {
cadena += "," + eval.get(i).getId().toString();
}
StringBuffer s =
new StringBuffer("select count(v.ID_EMPLEADO),v.ID_EVALUACION")
.append(" from EVA.EVALUACION_EMPLEADO v join empleado e on(v.ID_EMPLEADO=e.ID) ")
.append(
" join dependencia d on(d.ID=e.ID_DEPENDENCIA) join evaluacion c on (v.ID_EVALUACION=c.ID)")
.append(" where id_evaluacion in(")
.append(cadena)
.append(") and (d.id=")
.append(dep.getId().toString())
.append(" or d.ID in(select id from dependencia where ID_DEPENDENCIA_PADRE=")
.append(dep.getId().toString())
.append(")) group by v.ID_EVALUACION order by v.ID_EVALUACION");
try {
res = em.createNativeQuery(s.toString()).getResultList();
} catch (Exception e) {
return null;
}
return res;
}
private void separatePrivateMessagesFromWorkout(final Long id) {
final Query query =
entityManager.createNativeQuery(
"UPDATE MESSAGES SET WORKOUT_ID=NULL WHERE WORKOUT_ID=:id AND RECEIVER_ID IS NOT NULL");
query.setParameter("id", id);
query.executeUpdate();
}
@SuppressWarnings("unchecked")
@GET
@Path("/neighborhood")
@Produces("application/json;charset=UTF-8")
public Map<String, List<String>> registerNeighborhoodsToNewCity(
@QueryParam("state") String state, @QueryParam("cityName") String cityName) {
StringBuilder query = new StringBuilder();
query.append("(SELECT CAST(endereco.bairro AS char) AS b ");
query.append("FROM ");
query.append("CEP.");
query.append(state.toLowerCase());
query.append(" endereco ");
query.append("WHERE endereco.cidade = :cityName) ");
query.append("UNION ");
query.append("(SELECT CAST(bairro.nome AS char) AS b ");
query.append("FROM CEP2.bairros bairro ");
query.append("JOIN CEP2.cidades cidade ON cidade.id = bairro.cidade ");
query.append("WHERE cidade.nome = :cityName) ");
query.append("ORDER BY b ASC ");
List<String> neighborhoodsList =
em.createNativeQuery(query.toString()).setParameter("cityName", cityName).getResultList();
Map<String, List<String>> neighborhoods = new HashMap<String, List<String>>();
neighborhoods.put("neighborhoods", neighborhoodsList);
return neighborhoods;
}
private void setH2LockTimeout() {
EntityManager manager = entityManagerFactory.createEntityManager();
manager.getTransaction().begin();
manager.createNativeQuery("SET DEFAULT_LOCK_TIMEOUT " + locktimeout).executeUpdate();
manager.getTransaction().commit();
manager.close();
}
@Override
public List getSome() {
// TODO Auto-generated method stub
return em.createNativeQuery("findLastFiveTransaction", "BalanceWithCategoryType")
.getResultList();
}
/**
* 查询类别包含数量
*
* @param parentId
* @param error
* @return
*/
public static List<v_news_types> queryTypeAndCount(long parentId, ErrorInfo error) {
error.clear();
List<v_news_types> types = new ArrayList<v_news_types>();
StringBuffer sql = new StringBuffer("");
sql.append(SQLTempletes.SELECT);
sql.append(SQLTempletes.V_NEWS_TYPES);
sql.append(" and parent_id = ? and status = true order by _order");
try {
// types = v_news_types.find("parent_id = ? and status = true order by _order",
// parentId).fetch();
EntityManager em = JPA.em();
Query query = em.createNativeQuery(sql.toString(), v_news_types.class);
query.setParameter(1, parentId);
types = query.getResultList();
} catch (Exception e) {
e.printStackTrace();
error.code = -1;
error.msg = "查询类别失败";
return null;
}
error.code = 0;
return types;
}
public void popularity(ShopRoomTarriffCorporateRateView item) {
ShopRoomTarriffCorporateRateView shoproomtarriffcorporaterateview = null;
if (item != null) {
shoproomtarriffcorporaterateview = findById(item.getId(), false);
}
if (shoproomtarriffcorporaterateview != null) {
if (SObject.class.isAssignableFrom(ShopRoomTarriffCorporateRateView.class)) {
if (shoproomtarriffcorporaterateview.getPopularity() == null) {
shoproomtarriffcorporaterateview.setPopularity(-1);
}
shoproomtarriffcorporaterateview.setPopularity(
shoproomtarriffcorporaterateview.getPopularity() + 1);
try {
em.createNativeQuery(
String.format(updatePopularityClause, tableName),
ShopRoomTarriffCorporateRateView.class)
.setParameter("popularity", shoproomtarriffcorporaterateview.getPopularity())
.setParameter("id", item.getId())
.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}