/**
* Faz a busca de um funcionario pela Razão Social e seu status.
*
* @param String nomeRazaoSocial
* @param boolean status
* @param boolean noComeco
* @return Funcionario
* @throws Exception
*/
public List<Funcionario> buscarPorNomeRazaoSocial(
String nomeRazaoSocial, boolean status, boolean noComeco) throws NoResultException {
String where = "";
if (nomeRazaoSocial != null) {
// Coloca o sinal de percente de acordo com o parametro de pesquisa do boolean
nomeRazaoSocial = (noComeco) ? nomeRazaoSocial + "%" : "%" + nomeRazaoSocial + "%";
where = " AND (pf.txNome LIKE ?1 OR pj.txRazaoSocial LIKE ?2)";
}
String sql =
" SELECT c FROM Funcionario c "
+ " JOIN c.pessoa p "
+ " LEFT JOIN p.pessoaFisica pf "
+ " LEFT JOIN p.pessoaJuridica pj "
+ " WHERE p.blStatus = ?3 "
+ where;
Query query = getSessao().createQuery(sql);
// Atualiza o cache desta consulta
atualizaCache(query);
if (nomeRazaoSocial != null) {
query.setParameter(1, nomeRazaoSocial).setParameter(2, nomeRazaoSocial);
}
return query.setParameter(3, status).getResultList();
}
@Override
public void removeEndpoints(CSARID csarId) {
this.init();
this.em.getTransaction().begin();
// FIXME if somebody deletes a csar before some endpoints where added,
// some exceptions occur
// get all rest endpoints for the given csarid
Query queryRestEndpoints =
this.em.createQuery("SELECT e FROM RESTEndpoint e where e.csarId = :csarId");
queryRestEndpoints.setParameter("csarId", csarId);
@SuppressWarnings("unchecked")
List<RESTEndpoint> restEndpoints = queryRestEndpoints.getResultList();
// get all wsdl endpoints for the given csarid
Query queryWsdlEndpoints =
this.em.createQuery("SELECT e FROM WSDLEndpoint e where e.csarId = :csarId");
queryWsdlEndpoints.setParameter("csarId", csarId);
@SuppressWarnings("unchecked")
List<WSDLEndpoint> wsdlEndpoints = queryWsdlEndpoints.getResultList();
// remove all found endpoints one by one
for (RESTEndpoint restEndpoint : restEndpoints) {
this.em.remove(restEndpoint);
}
for (WSDLEndpoint wsdlEndpoint : wsdlEndpoints) {
this.em.remove(wsdlEndpoint);
}
this.em.getTransaction().commit();
}
@Override
public Long getTotalContentDownloads(
Company company, Date eventDate, Campaign campaign, Device device) {
String query =
"SELECT COUNT(b.id) FROM BluetoothSend b WHERE b.sendStatus = ?1 AND b.eventDate BETWEEN ?2 AND ?3 AND b.company = ?4 ";
int paramCount = 4;
HashMap<Integer, Object> params = new HashMap<Integer, Object>();
if (campaign != null) {
paramCount++;
query += " AND b.campaign = ?" + paramCount + " ";
params.put(paramCount, campaign);
}
if (device != null) {
paramCount++;
query += " AND b.device = ?" + paramCount + " ";
params.put(paramCount, device);
}
Query q = em.createQuery(query);
q.setParameter(1, BluetoothSend.STATUS_SENT);
q.setParameter(2, eventDate);
q.setParameter(3, DateUtil.getEndOfDay(eventDate));
q.setParameter(4, company);
for (Map.Entry<Integer, Object> entry : params.entrySet()) {
Integer paramId = entry.getKey();
Object object = entry.getValue();
q.setParameter(paramId, object);
}
Long countResult = (Long) q.getSingleResult();
return countResult;
}
@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 List needPathologyReport(String days) {
List resultList = null;
Integer daysInteger = 0;
Date limitDate = null;
if (days != null && !days.equalsIgnoreCase("")) {
limitDate = getLimitDate(Integer.valueOf(days));
}
String hql =
"Select DISTINCT c from Client c "
+ "left join c.colorectalCycles colorectalCycles "
+ "left join colorectalCycles.rsdiFormColorectalCancers colorectalCancers "
+ "left join colorectalCancers.rsdiFormColorectalCancer colorectalCancer "
+ "where lower(colorectalCancer.performedBiopsyItem.code) like lower(:yes) "
+ "and colorectalCancer.performedDate >= :limitDate "
+ "and colorectalcancer.rsdiFormClientSnapshot.clientId is not null ";
/*+ "order by colorectalcancer.createDate DESC";*/
Query q = getEntityManager().createQuery(hql);
q.setParameter("surgery", "SURGERY");
if (limitDate != null) {
q.setParameter("limitDate", limitDate);
}
resultList = q.getResultList();
return resultList;
}
/**
* Y a t'il d'autres facture a exporter pour le même payeur ?
*
* @param moveLineList : une liste de ligne d'écriture de facture
* @param psl
* @return
*/
public boolean hasOtherInvoice(List<MoveLine> moveLineList, MoveLine moveLine) {
Partner partner = moveLine.getPartner();
Query q =
JPA.em()
.createQuery(
"select count(*) FROM MoveLine as self WHERE self IN ?1 AND self.partner = ?2 ");
q.setParameter(1, moveLineList);
q.setParameter(2, partner);
if ((long) q.getSingleResult() > 1) {
log.debug(
"Recherche d'une autre facture à prélever (autre que l'écriture {}) pour le tiers {} : OUI",
moveLine.getName(),
partner.getFullName());
return true;
}
log.debug(
"Recherche d'une autre facture à prélever (autre que l'écriture {}) pour le tiers {} : NON",
moveLine.getName(),
partner.getFullName());
return false;
}
/**
* Finds lab routings for the specified demographic and lab type
*
* @param demoNo Demographic to find labs for
* @param labType Type of the lab to get routings for
* @return Returns the routings found.
*/
@SuppressWarnings("unchecked")
public List<PatientLabRouting> findByDemographicAndLabType(Integer demoNo, String labType) {
Query query = createQuery("r", "r.demographicNo = :demoNo AND r.labType = :labType");
query.setParameter("demoNo", demoNo);
query.setParameter("labType", labType);
return query.getResultList();
}
@SuppressWarnings("unchecked")
public List<Task> getWeekTasksStat(String name, String momin, Date startWeek) {
Query query;
List<Task> list;
TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
query =
em.createQuery(
"select from "
+ Task.class.getName()
+ " t where t.mominId = :momin AND name =:name AND t.date >= :start AND t.date < :end order by date asc");
query.setParameter("name", name);
Task.moveToDay(startWeek, -1);
startWeek.setHours(0);
startWeek.setMinutes(0);
startWeek.setSeconds(0);
Date end = CalendarUtil.copyDate(startWeek);
CalendarUtil.addDaysToDate(end, 7);
query.setParameter("momin", momin);
query.setParameter("start", startWeek);
// end.setTime(end.getTime()-2000);
query.setParameter("end", end);
list = query.getResultList();
// if(group == null)
// getCache().put(keyWeek, list);
return list;
}
public List<Hsfo2Visit> getVisitRecordInDateRangeByDemographicNo(
String patientId, String startDate, String endDate) {
String sqlCommand =
"select x FROM Hsfo2Visit x where x.Patient_Id=?1 and x.VisitDate_Id>?2 and x.VisitDate_Id<?3 group by x.VisitDate_Id";
Query query = entityManager.createQuery(sqlCommand);
query.setParameter(1, patientId);
try {
Date start = new SimpleDateFormat("yyyy-MM-dd").parse(startDate);
Date end = new SimpleDateFormat("yyyy-MM-dd").parse(endDate);
query.setParameter(2, start);
query.setParameter(3, end);
@SuppressWarnings("unchecked")
List<Hsfo2Visit> results = query.getResultList();
return results;
} catch (ParseException e) {
logger.error("[Hsfo2VisitDao] - formatDate: ", e);
return null;
}
}
@SuppressWarnings("rawtypes")
public UserInfo loginUser(UserInfo user) {
String name = user.getName();
String passwd = user.getPasswd();
if (name != null && passwd != null) {
// manager.getTransaction().begin();
// Query query = manager.createNativeQuery(queryString); // ,
// "UserResult");
// UserInfo
// user2=new
// UserInfo();
// Object list=query.getSingleResult();
Query query =
manager.createQuery(
"select p from UserInfo p where p.name = :name and p.passwd = :passwd and p.verified =1");
query.setParameter("name", name);
query.setParameter("passwd", passwd);
// List<UserInfo> users=contentUserInfoTransfer(query.getResultList());
Iterator iterator = query.getResultList().iterator();
if (iterator.hasNext()) user = (UserInfo) iterator.next();
// user=users.size()==0?user:users.get(0);
// EntityManagerFactory factory =
// Persistence.createEntityManagerFactory("basfdb");
// // 获取EntityManager
// EntityManager em = factory.createEntityManager();
// // 开始事务处理
// em.getTransaction().begin();
// manager.getTransaction().commit();
if (user.getUser_id() == null) return null;
return user;
} else return null;
}
public void normalize(ArtResourceFrequency f) {
Query query = entityManager.createNamedQuery("artResource.taggingCount");
query.setParameter("resid", f.getResource().getId());
query.setParameter("lang", localeSelector.getLanguage());
double taggings = ((Long) query.getSingleResult()).doubleValue();
f.setCount(f.getCount() / taggings);
}
@SuppressWarnings("unchecked")
public List<LOSStorageLocation> getListByAreaType(Client client, LOSAreaType areaType) {
StringBuffer qstr = new StringBuffer();
qstr.append(
"SELECT sl FROM "
+ LOSStorageLocation.class.getSimpleName()
+ " sl "
+ ", "
+ LOSArea.class.getSimpleName()
+ " a "
+ "WHERE a.areaType = :areaType AND sl.area.id = a.id ");
if (!client.isSystemClient()) {
qstr.append("AND sl.client = :cl ");
}
qstr.append("ORDER BY sl.name");
Query query = manager.createQuery(qstr.toString());
query.setParameter("areaType", areaType);
if (!client.isSystemClient()) {
query.setParameter("cl", client);
}
return (List<LOSStorageLocation>) query.getResultList();
}
/**
* Returns a list of dates from trade open until expiration.
*
* @param expiration
* @return
*/
public static List<Date> getTradeDatesStarting(Date expiration, Date startDate) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPAOptionsTrader");
EntityManager em = emf.createEntityManager();
// Note: table name refers to the Entity Class and is case sensitive
// field names are property names in the Entity Class
Query query =
em.createQuery(
"select distinct(opt.trade_date) from "
+ TradeProperties.SYMBOL_FOR_QUERY
+ " opt "
+ "where "
+ "opt.trade_date >= :tradeDate "
+ "opt.expiration=:expiration "
// + "and opt.call_put = :call_put "
// + "and opt.delta > 0.04 "
// + "and opt.delta < 0.96 "
+ "order by opt.trade_date");
query.setParameter("tradeDate", startDate);
query.setParameter("expiration", expiration);
// query.setParameter("call_put", callPut);
List<Date> dates = query.getResultList();
em.close();
return dates;
}
private Query criarQueryEspecifica(Dao dao, Class entidade, List<ParametroConsulta> params) {
String nomeQuery = entidade.getSimpleName() + ".findBy";
for (ParametroConsulta t : params) {
nomeQuery +=
(String.valueOf(t.getCampo().charAt(0)).toUpperCase() + t.getCampo().substring(1));
}
Query query = dao.createNamedQuery(nomeQuery);
for (ParametroConsulta t : params) {
if (t.getTipo().equals("numero")) {
try {
query.setParameter(t.getCampo(), Long.valueOf(t.getValor()));
} catch (NumberFormatException exc) {
query.setParameter(t.getCampo(), 0L);
}
} else {
query.setParameter(t.getCampo(), t.getValor());
}
}
return query;
}
/**
* 描述：更近sc统计相关信息条数 <br>
* 创建时间：2013-5-11 下午11:51:02
*
* @author liyang
* @param lockMode
* @param cacheName
* @param sc
* @return
*/
public Object statBySearchCriterion(
final LockModeType lockMode, final ConstantsEnum.EHCACHE cacheName, SearchCriterion sc) {
if (sc == null) {
sc = new SearchCriterion();
}
Map<String, Object> map = sc.getFilterMap();
Query query = entityManager.createQuery("select count(*) " + this.getJPQL(sc));
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
map = sc.getFilterValue();
if (map != null && !map.isEmpty()) {
for (String str : map.keySet()) {
if (str.split("\\.").length > 1) {
query.setParameter(str.split("\\.")[1], map.get(str));
} else {
query.setParameter(str, map.get(str));
}
}
}
if (lockMode != null) {
query.setLockMode(lockMode);
}
if (cacheName != null) {
query.setHint("org.hibernate.cacheable", true);
query.setHint("org.hibernate.cacheRegion", cacheName.getStr());
}
return query.getSingleResult();
}
/**
* @param hospital
* @param name
* @return
*/
public Department findByHospitalAndName(Hospital hospital, String name) {
Query query = this.em.createNamedQuery(Department.QUERY_FIND_BY_HOSPTIAL_AND_NAME);
query.setParameter("hospital", hospital);
query.setParameter("name", name);
return getSingleResult(query);
}
@SuppressWarnings("unchecked")
public <T> List<T> findWithNamedQuery(String queryName, Map<String, Object> params) {
Set<Entry<String, Object>> rawParameters = params.entrySet();
Query query = entityManager.createNamedQuery(queryName);
// if has limit.
if (params.containsKey(QueryParameters.LIMIT_START)) {
int start = new Integer(params.get(QueryParameters.LIMIT_START).toString());
int n = (Integer) params.get(QueryParameters.LIMIT_N);
params.remove(QueryParameters.LIMIT_START);
params.remove(QueryParameters.LIMIT_N);
query.setFirstResult(start);
query.setMaxResults(n);
}
// if has order by.
if (params.containsKey(QueryParameters.ORDERBY)) {
String orderbyField = (String) params.get(QueryParameters.ORDERBY);
params.remove(QueryParameters.ORDERBY);
query.setParameter("orderby", String.format("%s", orderbyField));
if (params.containsKey(QueryParameters.ORDERBY_SORT)) {
params.remove(QueryParameters.ORDERBY_SORT);
}
}
// Add parameters
for (Entry<String, Object> entry : rawParameters) {
query.setParameter(entry.getKey(), entry.getValue());
}
return query.getResultList();
}
public List<ConceptoProveedor> getListaByBusqueda(
String codTipo, String txtBusqueda, boolean mostrarDebaja, int cantMax) {
try {
String sQuery =
"SELECT e FROM ConceptoProveedor e "
+ "WHERE ((e.codigo LIKE :codigo) OR (e.descripcion LIKE :descripcion)) "
+ (mostrarDebaja ? " " : " AND e.auditoria.debaja = 'N' ")
+ (codTipo == null ? " " : " AND e.tipo = :tipoConcepto ")
+ "ORDER BY e.codigo ";
Query q = em.createQuery(sQuery);
// System.err.println("sQuery: "+sQuery);
if (codTipo != null) {
q.setParameter("tipoConcepto", (codTipo.isEmpty() ? "" : codTipo));
}
q.setParameter("codigo", "%" + txtBusqueda.replace(" ", "%") + "%");
q.setParameter("descripcion", "%" + txtBusqueda.replace(" ", "%") + "%");
if (cantMax > 0) {
q.setMaxResults(cantMax);
}
return q.getResultList();
} catch (Exception e) {
e.printStackTrace();
log.log(Level.SEVERE, "getListaByBusqueda", e.getMessage());
return new ArrayList<ConceptoProveedor>();
}
}
@SuppressWarnings("unchecked")
@Override
public List<FiseTipDocRef> buscarFiseTipDocRef(String id, String descripcion)
throws SQLException {
String q = "SELECT d FROM " + FiseTipDocRef.class.getName() + " d WHERE 1=1 ";
if (FormatoUtil.isNotBlank(id)) {
q = q.concat(" AND d.idTipDocRef = :id ");
}
if (FormatoUtil.isNotBlank(descripcion)) {
q = q.concat(" AND d.descripcion LIKE :descripcion ");
}
q = q.concat(" ORDER BY d.descripcion ASC");
Query query = em.createQuery(q);
if (FormatoUtil.isNotBlank(id)) {
query.setParameter("id", id);
}
if (FormatoUtil.isNotBlank(descripcion)) {
String des = "%" + descripcion + "%";
query.setParameter("descripcion", des);
}
List<FiseTipDocRef> lista = query.getResultList();
if (lista == null) {
return Collections.EMPTY_LIST;
} else {
return lista;
}
}
/**
* Listado de Motivos por Status y Categorías
*
* @param status
* @param categoria
* @return
*/
public List<Motivo> findMotivo(int status, Categoria categoria) {
String query = "SELECT m from Motivo m WHERE m.status = :status and m.categoriaId = :categoria";
Query q = getEntityManager().createQuery(query);
q.setParameter("status", status);
q.setParameter("categoria", categoria);
return q.getResultList();
}
public Permissao pesquisaPermissao(int idModulo, int idRotina, int idEvento, int idCliente) {
try {
Query query =
getEntityManager()
.createQuery(
" SELECT P "
+ " FROM Permissao AS P "
+ " WHERE P.modulo.id = :modulo "
+ " AND P.rotina.id = :rotina "
+ " AND P.rotina.ativo = true "
+ " AND P.segEvento.id = :evento ");
// + " AND P.cliente.id = :cliente ");
query.setParameter("modulo", idModulo);
query.setParameter("rotina", idRotina);
query.setParameter("evento", idEvento);
// query.setParameter("cliente", idCliente);
List list = query.getResultList();
if (!list.isEmpty() && list.size() == 1) {
return (Permissao) query.getSingleResult();
}
} catch (Exception e) {
return null;
}
return null;
}
public static void index() {
Test test = Test.findById(1L);
Account account = Account.find("byEmail", Security.connected()).first();
TestResult testResult =
TestResult.find("test = ? and account = ? order by resultDate desc", test, account).first();
List<TestResultAttr> lastRes = TestResultAttr.find("byTestResult", testResult).fetch();
List<String[]> last = getTappingData(lastRes);
List<TestResultAttr> allRes =
TestResultAttr.find("testResult.test = ? and testResult.account = ?", test, account)
.fetch();
List<String[]> all = getTappingData(allRes);
@SuppressWarnings("JpaQlInspection")
Query query =
JPA.em()
.createQuery(
"select t.sortOrder - 1, avg(a.val) "
+ "from TestResult r, TestResultAttr a, TestAttr t "
+ "where a.testResult = r and a.testAttr = t and r.test = :test and r.account = :account group by t.sortOrder order by t.sortOrder");
query.setParameter("test", test);
query.setParameter("account", account);
List<String[]> avg = query.getResultList();
render(account, last, avg, all);
}
public List missingSurgeryReport(String days) {
List resultList = null;
Integer daysInteger = 0;
Date limitDate = null;
if (days != null && !days.equalsIgnoreCase("")) {
limitDate = getLimitDate(Integer.valueOf(days));
}
String hql =
"Select DISTINCT c from Client c "
+ "inner join c.colorectalCycles colorectalCycles "
+ "inner join colorectalCycles.rsdiFormPathologyReports pathologyReports "
+ "inner join pathologyReports.rsdiFormPathologyReport pathologyReport "
+ "where lower(pathologyReport.recommendedTestItem.code) like lower(:surgery) "
+ "and pathologyReport.reportCompletedDate <= :limitDate "
+ "and pathologyReport.rsdiFormClientSnapshot.clientId is not null ";
/*+ "order by colorectalcancer.createDate DESC";*/
Query q = getEntityManager().createQuery(hql);
q.setParameter("surgery", "SURGERY");
if (limitDate != null) {
q.setParameter("limitDate", limitDate);
}
resultList = q.getResultList();
return resultList;
}
public Page<Object[]> getCustomerService(int pageNo, int pageSize, Date minDate, Date maxDate) {
String hql =
"select count(cs.serviceType) from CustomerService cs "
+ " where cs.customer is not null And cs.serviceType is not null "
+ " and cs.createDate between ? and ?";
Query query = entityManager.createQuery(hql);
query.setParameter(1, minDate);
query.setParameter(2, maxDate);
long total = (long) query.getSingleResult();
hql =
"select cs.serviceType , count(cs.serviceType) from CustomerService cs "
+ "where cs.customer is not null and cs.serviceType is not null and cs.createDate between ? and ? "
+ " group by cs.serviceType";
query = entityManager.createQuery(hql);
query.setParameter(1, minDate);
query.setParameter(2, maxDate);
query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
List<Object[]> content = query.getResultList();
PageRequest pageable = new PageRequest(pageNo - 1, pageSize);
PageImpl<Object[]> page = new PageImpl<Object[]>(content, pageable, total);
return page;
}
@Override
/** {@Inheritdoc} */
public List<RESTEndpoint> getRestEndpoints(URI anyURI, CSARID csarId) {
this.init();
ArrayList<RESTEndpoint> results = new ArrayList<RESTEndpoint>();
/**
* Create Query to retrieve RESTEndpoints identified by a URI and thorID
*
* @see RESTEndpoint#getEndpointForPath
*/
Query getRestEndpointsQuery = this.em.createNamedQuery(RESTEndpoint.getEndpointForPath);
// Set Parameters
getRestEndpointsQuery.setParameter("path", anyURI.getPath());
getRestEndpointsQuery.setParameter("csarId", csarId);
// Get Query-Results and add them to the result list
@SuppressWarnings("unchecked")
// Result can only be a RESTEndpoint
List<RESTEndpoint> queryResults = getRestEndpointsQuery.getResultList();
for (RESTEndpoint endpoint : queryResults) {
results.add(endpoint);
}
return results;
}
private List<ServiceValueDetail> getServiceValueDetailList(
String where, Long userphoneCod, Long serviceCod, Integer cant) {
EntityManager em = null;
try {
String sql =
" SELECT o "
+ " FROM ServiceValueDetail o "
+ " WHERE o.enabledChr = true "
+ " AND o.serviceValue.enabledChr = true "
+ " AND o.serviceValue.userphone.userphoneCod = :userphoneCod "
+ " AND o.serviceValue.service.serviceCod = :serviceCod "
+ " AND trunc(o.recorddateDat) <= :date "
+ where
+ " ORDER BY o.servicevaluedetailCod DESC ";
em = getFacadeContainer().getEntityManager();
Query query = em.createQuery(sql);
query.setParameter("userphoneCod", userphoneCod);
query.setParameter("serviceCod", serviceCod);
query.setParameter("date", new Date());
query.setMaxResults(cant);
List<ServiceValueDetail> svdList = query.getResultList();
return svdList;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
if (em != null && getFacadeContainer().isEntityManagerTransactional()) {
em.close();
}
}
}
@Override
public Long getUniqueDevicesSupportingBluetooth(
Company company, Date eventDate, Campaign campaign, Device device) {
String query =
"SELECT COUNT(DISTINCT b.macAddress) FROM BluetoothSend b WHERE b.eventDate BETWEEN ?1 AND ?2 AND b.company = ?3 AND b.sendStatus in (1, 2, 3, 4, 5, 7) ";
int paramCount = 3;
HashMap<Integer, Object> params = new HashMap<Integer, Object>();
if (campaign != null) {
paramCount++;
query += " AND b.campaign = ?" + paramCount + " ";
params.put(paramCount, campaign);
}
if (device != null) {
paramCount++;
query += " AND b.device = ?" + paramCount + " ";
params.put(paramCount, device);
}
Query q = em.createQuery(query);
q.setParameter(1, eventDate);
q.setParameter(2, DateUtil.getEndOfDay(eventDate));
q.setParameter(3, company);
for (Map.Entry<Integer, Object> entry : params.entrySet()) {
Integer paramId = entry.getKey();
Object object = entry.getValue();
q.setParameter(paramId, object);
}
Long countResult = (Long) q.getSingleResult();
return countResult;
}
public List<Usuario> getUsuarioByBusqueda(
String txtBusqueda, boolean mostrarDeBaja, int cantMax) {
try {
String sQuery =
"SELECT e FROM Usuario e "
+ " WHERE (e.usuario LIKE :usuario OR e.nombre LIKE :nombre OR e.email LIKE :email) "
+ " ORDER BY e.usuario";
Query q = em.createQuery(sQuery);
q.setParameter("usuario", "%" + txtBusqueda.replaceAll(" ", "%") + "%");
q.setParameter("nombre", "%" + txtBusqueda.replaceAll(" ", "%") + "%");
q.setParameter("email", "%" + txtBusqueda.replaceAll(" ", "%") + "%");
if (cantMax > 0) {
q.setMaxResults(cantMax);
}
return q.getResultList();
} catch (Exception e) {
log.log(Level.SEVERE, "getUsuarioByBusqueda", e.getCause());
return new ArrayList<Usuario>();
}
}
@Override
public List<BluetoothFileSendSummary> fetchBluetoothFileSendSummaries(
Date eventDate, Company company, Campaign campaign, Device device) {
String query =
"SELECT b FROM BluetoothFileSendSummary b WHERE b.eventDate BETWEEN ?1 AND ?2 AND b.company = ?3 ";
int paramCount = 3;
HashMap<Integer, Object> params = new HashMap<Integer, Object>();
if (campaign != null) {
paramCount++;
query += " AND b.campaign = ?" + paramCount + " ";
params.put(paramCount, campaign);
}
if (device != null) {
paramCount++;
query += " AND b.device = ?" + paramCount + " ";
params.put(paramCount, device);
}
Query q = em.createQuery(query);
q.setParameter(1, eventDate);
q.setParameter(2, DateUtil.getEndOfDay(eventDate));
q.setParameter(3, company);
for (Map.Entry<Integer, Object> entry : params.entrySet()) {
Integer paramId = entry.getKey();
Object object = entry.getValue();
q.setParameter(paramId, object);
}
List<BluetoothFileSendSummary> results = (List<BluetoothFileSendSummary>) q.getResultList();
return results;
}
/**
* Deletes a tag.
*
* @param tagId Tag ID
* @param userId User ID
*/
public void delete(String tagId, String userId) {
EntityManager em = ThreadLocalContext.get().getEntityManager();
// Get the tag
Query q = em.createQuery("select t from Tag t where t.id = :id and t.deleteDate is null");
q.setParameter("id", tagId);
Tag tagDb = (Tag) q.getSingleResult();
// Delete the tag
Date dateNow = new Date();
tagDb.setDeleteDate(dateNow);
// Delete linked data
q =
em.createQuery(
"update DocumentTag dt set dt.deleteDate = :dateNow where dt.tagId = :tagId and dt.deleteDate is not null");
q.setParameter("dateNow", dateNow);
q.setParameter("tagId", tagId);
q.executeUpdate();
q =
em.createQuery(
"update Acl a set a.deleteDate = :dateNow where a.sourceId = :tagId and a.deleteDate is null");
q.setParameter("tagId", tagId);
q.setParameter("dateNow", dateNow);
q.executeUpdate();
// Create audit log
AuditLogUtil.create(tagDb, AuditLogType.DELETE, userId);
}