public void deleteUser(WkTUser user) {
// 首先删除用户角色关系
Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
session.beginTransaction();
String deleteUserRole = "delete WkTUserole as u_r where u_r.id.kuId=?";
Query dUserRole = session.createQuery(deleteUserRole);
dUserRole.setLong(0, user.getKuId());
dUserRole.executeUpdate();
// 还需要删除用户发布信息、消息等
String deleteNR = "delete XyNUrd as xnurd where xnurd.id.kuId=?";
Query updateNRQ = session.createQuery(deleteNR);
updateNRQ.setLong(0, user.getKuId());
updateNRQ.executeUpdate();
String deleteM = "delete XyMReceiver as xmr where xmr.id.kuId=?";
Query updateM = session.createQuery(deleteM);
updateM.setLong(0, user.getKuId());
updateM.executeUpdate();
String deleteN = "delete XyMessage as xm where xm.kuId=?";
Query updateN = session.createQuery(deleteN);
updateN.setLong(0, user.getKuId());
updateN.executeUpdate();
session.getTransaction().commit();
// 删除用户
getHibernateTemplate().delete(user);
}
public int updateUserCoin(Long userId, Long coin) {
Query query =
getSession().createQuery("update UserEntity set COINS = :coin where ID = :userId");
query.setLong("coin", coin);
query.setLong("userId", userId);
return query.executeUpdate();
}
private static boolean atualizarCarencia(Segurado segurado, Long idTitular) throws Exception {
String sqlConsignacao =
"select min(c.dataDoCredito) from ConsignacaoSegurado c where c.titular.idSegurado = :idTitular "
+ "and c.statusConsignacao = 'P' and c.dataDoCredito >= :dataAdesao";
String sqlCobranca =
"select min(c.dataPagamento) from Cobranca c where c.titular.idSegurado = :idTitular "
+ "and descricao = 'Pago(a)' and c.dataPagamento >= :dataAdesao";
Query queryConsignacao = HibernateUtil.currentSession().createQuery(sqlConsignacao);
Query queryCobranca = HibernateUtil.currentSession().createQuery(sqlCobranca);
queryConsignacao.setLong("idTitular", idTitular);
queryConsignacao.setDate("dataAdesao", segurado.getDataAdesao());
Date inicioCarencia = (Date) queryConsignacao.uniqueResult();
if (inicioCarencia == null) {
queryCobranca.setLong("idTitular", idTitular);
queryCobranca.setDate("dataAdesao", segurado.getDataAdesao());
inicioCarencia = (Date) queryCobranca.uniqueResult();
}
if (inicioCarencia != null) {
System.out.println(segurado.getNumeroDoCartao() + " - " + inicioCarencia);
segurado.setInicioDaCarencia(inicioCarencia);
ImplDAO.save(segurado);
return true;
}
return false;
}
@Override
@SuppressWarnings("unchecked")
public List<PopulationDto> getListePopulations(Long uidPersonneMorale) {
final StringBuffer requete =
new StringBuffer("select population, count(distinct uidAssure) from Contrat ")
.append("where uidSouscripteur = :uidPersonneMorale ")
.append("and uidAssure is not null ")
.append("and statut.id = :idStatutEnCours ")
.append("and nature.id = :idNatureSante ")
.append("and isVisible = true ")
.append("group by population");
final Query query = createQuery(requete.toString());
query.setLong("uidPersonneMorale", uidPersonneMorale);
query.setLong("idStatutEnCours", adherentMappingService.getIdStatutContratEnCours());
query.setLong("idNatureSante", adherentMappingService.getIdNatureContratSante());
final List<Object[]> listResult = query.list();
final List<PopulationDto> listePopulations = new ArrayList<PopulationDto>();
if (listResult != null && listResult.size() > 0) {
for (Object[] result : listResult) {
final String libellePopulation = (String) result[0];
final Integer effectif = Integer.valueOf((String) result[1].toString());
final PopulationDto population = new PopulationDto();
population.setLibelle(libellePopulation);
population.setEffectif(effectif);
listePopulations.add(population);
}
}
return listePopulations;
}
/** @author alizadeh */
@Override
public QueryResult<AdjustmentEstimateDetailsDto> getCamp5YearEstimateAmmunitions(
int estimateId, int tadilPercent, int priceShowType, SearchOption searchOption) {
HashMap<String, Object> params = new HashMap<String, Object>();
String hql =
" select e.id as id,e.goodType.title as ammoTitle,e.quantity as quantity,e.fee as fi,"
+ " cast(((e.quantity*e.fee)/:priceShowType) as long) as rialCost ,"
+ " round( e.quantity-((e.quantity/100)*:tadilPercent)) as quantityAfterAdjustment,"
+ " cast((round((e.quantity-((e.quantity/100)*:tadilPercent))*e.fee)/:priceShowType) as long)as rialCostAfterAdjustment "
+ " from "
+ domainClass.getName()
+ " e where e.camp5YearEstimate.id =:estimateId ";
params.put("estimateId", estimateId);
params.put("tadilPercent", tadilPercent);
params.put("priceShowType", priceShowType);
List<AdjustmentEstimateDetailsDto> list =
getAll(hql, params, searchOption, AdjustmentEstimateDetailsDto.class);
int count = 0;
if (!list.isEmpty()) {
Session session = getSession();
String QueryCount =
"select count(*) from "
+ domainClass.getName()
+ " e where e.camp5YearEstimate.id =:estimateId ";
String QuerySum =
" select cast((sum(e.quantity*e.fee)/:priceShowType) as long )as sumOfRialCost,"
+ " (cast((sum(round((e.quantity-((e.quantity/100)*:tadilPercent))*e.fee))/:priceShowType)as long)) as sumOfRialCostAfterAdjustment "
+ " from "
+ domainClass.getName()
+ " e where e.camp5YearEstimate.id =:estimateId ";
StringBuffer jqlCount = new StringBuffer(QueryCount);
StringBuffer jqlSum = new StringBuffer(QuerySum);
Query countQuery = session.createQuery(jqlCount.toString());
Query querySum = session.createQuery(jqlSum.toString());
countQuery.setParameter("estimateId", estimateId);
querySum.setParameter("estimateId", estimateId);
querySum.setLong("tadilPercent", tadilPercent);
querySum.setLong("priceShowType", priceShowType);
Iterator iterator = querySum.iterate();
if (iterator.hasNext() && list.size() > 0) {
Object[] row = (Object[]) iterator.next();
list.get(0).setSumOfRialCost(Long.parseLong(row[0].toString()));
list.get(0).setSumOfRialCostAfterAdjustment(Long.parseLong(row[1].toString()));
}
Integer res = ((Long) countQuery.uniqueResult()).intValue();
if (res != null) count = res;
}
return new QueryResult<AdjustmentEstimateDetailsDto>(
searchOption.getPageNumber(), count, searchOption.getPageSize(), list);
}
@SuppressWarnings("unchecked")
@Override
public List<GarantieBeneficiaireDto> getListeBeneficiairesFromContrats(
List<Long> listeUidContrats, Long uidAssure, boolean filtrerContratEnCOurs) {
final StringBuffer requete =
new StringBuffer("select uidBeneficiaire, contrat.id, role.id, role.libelle ");
requete.append("from Garantie where ");
if (listeUidContrats != null && listeUidContrats.size() > 0) {
requete.append("contrat.id in (:listeUidContrats) and ");
}
if (filtrerContratEnCOurs) {
requete.append("statut.id = :idStatutGarantieEnCours and ");
}
requete.append("contrat.isVisible = true and isVisible = true and ");
requete.append("uidAssure = :uidAssure order by statut.ordre, role.ordre");
final Query query = createQuery(requete.toString());
if (listeUidContrats != null && listeUidContrats.size() > 0) {
query.setParameterList("listeUidContrats", listeUidContrats);
}
if (filtrerContratEnCOurs) {
query.setLong("idStatutGarantieEnCours", adherentMappingService.getIdStatutGarantieEnCours());
}
query.setLong("uidAssure", uidAssure);
final List<Object[]> listResult = query.list();
final List<GarantieBeneficiaireDto> listeBeneficiaires =
new ArrayList<GarantieBeneficiaireDto>();
final List<IdsBeneficiaireContrat> listeIdsBenefsContrats =
new ArrayList<IdsBeneficiaireContrat>();
if (listResult != null && listResult.size() > 0) {
for (Object[] result : listResult) {
final Long idBenef = (Long) result[0];
final Long idContrat = (Long) result[1];
final IdsBeneficiaireContrat idBenefContrat = new IdsBeneficiaireContrat();
idBenefContrat.setIdBenef(idBenef);
idBenefContrat.setIdContrat(idContrat);
if (!listeIdsBenefsContrats.contains(idBenefContrat)) {
final GarantieBeneficiaireDto benef = new GarantieBeneficiaireDto();
benef.setIdBenef(idBenef);
benef.setIdContrat(idContrat);
benef.setRole(new IdentifiantLibelleDto((Long) result[2], String.valueOf(result[3])));
listeBeneficiaires.add(benef);
listeIdsBenefsContrats.add(idBenefContrat);
}
}
}
return listeBeneficiaires;
}
public void getDistPrefsTableAsPdf(
OutputStream out,
HttpServletRequest request,
SessionContext context,
Long subjectAreaId,
String courseNbr,
Long examTypeId)
throws Exception {
Query q =
new DistributionPrefDAO()
.getSession()
.createQuery(
"select distinct dp from DistributionPref dp "
+ "inner join dp.distributionObjects do, Exam x inner join x.owners o "
+ "where "
+ (courseNbr == null || courseNbr.trim().length() == 0
? ""
: courseNbr.indexOf('*') >= 0
? "o.course.courseNbr like :courseNbr and "
: "o.course.courseNbr=:courseNbr and")
+ (subjectAreaId == null
? ""
: " o.course.subjectArea.uniqueId=:subjectAreaId and ")
+ "dp.distributionType.examPref = true and "
+ "do.prefGroup = x and x.session.uniqueId=:sessionId and x.examType.uniqueId=:examTypeId")
.setLong("sessionId", context.getUser().getCurrentAcademicSessionId())
.setLong("examTypeId", examTypeId);
if (subjectAreaId != null) q.setLong("subjectAreaId", subjectAreaId);
if (courseNbr != null && courseNbr.trim().length() != 0)
q.setString("courseNbr", courseNbr.trim().replaceAll("\\*", "%"));
List distPrefs = q.setCacheable(true).list();
toPdfTable(out, request, context, distPrefs, examTypeId);
}
public static Friend findByInviter(long id, User user) {
Session session = getSession();
Query query = session.getNamedQuery("Friend.getByInviter");
query.setLong("id", id);
query.setEntity("user", user);
return (Friend) query.uniqueResult();
}
/**
* Retrieve a Role object with specified id.
*
* @param id the id of the Role.
* @return an object representing the Role with specified id.
* @throws DataBackendException if there was an error accessing the data backend.
* @throws UnknownEntityException if the role does not exist.
*/
public Role getRoleById(Object id) throws DataBackendException, UnknownEntityException {
Role role = null;
if (id != null)
try {
Query query =
getPersistenceHelper()
.retrieveSession()
.createQuery("from " + getClassName() + " sr where sr.id=:id");
query.setLong("id", (Long) id);
List roles = query.list();
if (roles.size() == 0) {
throw new UnknownEntityException("Could not find role by id " + id);
}
role = (Role) roles.get(0);
} catch (HibernateException e) {
throw new DataBackendException("Error retriving role information", e);
}
return role;
}
private String getFolio(Almacen almacen) {
Query query =
currentSession()
.createQuery(
"select f from Folio f where f.nombre = :nombre and f.almacen.id = :almacenId");
query.setString("nombre", "FACTURA");
query.setLong("almacenId", almacen.getId());
query.setLockOptions(LockOptions.UPGRADE);
Folio folio = (Folio) query.uniqueResult();
if (folio == null) {
folio = new Folio("FACTURA");
folio.setAlmacen(almacen);
currentSession().save(folio);
return getFolio(almacen);
}
folio.setValor(folio.getValor() + 1);
java.text.NumberFormat nf = java.text.DecimalFormat.getInstance();
nf.setGroupingUsed(false);
nf.setMinimumIntegerDigits(9);
nf.setMaximumIntegerDigits(9);
nf.setMaximumFractionDigits(0);
StringBuilder sb = new StringBuilder();
sb.append("FA-");
sb.append(almacen.getEmpresa().getOrganizacion().getCodigo());
sb.append(almacen.getEmpresa().getCodigo());
sb.append(almacen.getCodigo());
sb.append(nf.format(folio.getValor()));
return sb.toString();
}
public static Query getShopVehicleBrandModelByShopId(Session session, Long shopId) {
StringBuffer hql = new StringBuffer();
hql.append(" from ShopVehicleBrandModel as p where p.shopId =:shopId");
Query query = session.createQuery(hql.toString());
query = query.setLong("shopId", shopId);
return query;
}
public Profile findProfileForEmployer(long id) {
String queryString = "from EmployerProfile e where e.employer.id= :id ";
Query query = getSession().createQuery(queryString);
query.setLong("id", id);
Profile profile = (Profile) query.uniqueResult();
return profile;
}
@Override
public Integer getNombreAdherentsContrat(String eidContrat) {
final StringBuffer requete =
new StringBuffer("select count(distinct uidAssure) from Garantie ")
.append("where contrat.identifiantExterieur like :idContrat ")
.append("and contrat.uidAssure is not null ")
.append("and contrat.statut.id = :idStatutContratEnCours ")
.append("and statut.id = :idStatutGarantieEnCours ")
.append("and isVisible = true");
final Query query = createQuery(requete.toString());
query.setString("idContrat", eidContrat.substring(0, NB_CARACTERE_ID_CONTRAT) + POURCENT);
query.setLong("idStatutContratEnCours", adherentMappingService.getIdStatutContratEnCours());
query.setLong("idStatutGarantieEnCours", adherentMappingService.getIdStatutGarantieEnCours());
final Integer nbAdherents = Integer.valueOf(query.uniqueResult().toString());
return nbAdherents;
}
@SuppressWarnings("unchecked")
@Override
public String getGestionnaireContratCollectif(Long uidPersonne) {
final StringBuffer requete =
new StringBuffer("select a.contrat_gestionnaire from data_contrat a ")
.append("join dim_contrat_nature b on a.contrat_nature_uid = b.contrat_nature_uid ")
.append(
"join data_personne_physique c on c.personne_uid = a.contrat_assure_uid and c.personne_segment_uid = 3 ")
.append("inner join (")
.append("select contrat_assure_uid,max(contrat_nature_eid || ")
.append(
"cast(contrat_date_adhesion as text)||cast(coalesce(contrat_date_resiliation,'2222/01/01') as text)) ")
.append("ID from data_contrat, dim_contrat_nature ")
.append(
"where data_contrat.contrat_nature_uid = dim_contrat_nature.contrat_nature_uid ")
.append("and contrat_segment_uid = 3 ")
.append("group by contrat_assure_uid) contrat_coll_unique_assure ")
.append("on a.contrat_assure_uid = contrat_coll_unique_assure.contrat_assure_uid ")
.append("and b.contrat_nature_eid ||cast(a.contrat_date_adhesion as text) ")
.append(
" || cast(coalesce(a.contrat_date_resiliation,'2222/01/01') as text) = contrat_coll_unique_assure.ID ")
.append("and a.contrat_segment_uid = 3 ")
.append("and a.contrat_assure_uid= :uidPersonne");
final Query crit = createSqlQuery(requete.toString());
crit.setLong("uidPersonne", uidPersonne);
final List<String> resultats = (ArrayList<String>) crit.list();
return resultats.size() >= 1 ? resultats.get(0) : null;
}
public static Query getProducts(Session session, Long shopId, int start, int rows) {
StringBuffer sb = new StringBuffer();
sb.append("select p from Product as p");
if (shopId != null) sb.append(" where p.shopId=:shopId");
Query query = session.createQuery(sb.toString());
if (shopId != null) query.setLong("shopId", shopId);
return query.setFirstResult(start).setMaxResults(rows);
}
public Employer loadEmployerAccInfo(long id) {
String queryString = "from Employer p where p.id= :id";
Query query = getSession().createQuery(queryString);
query.setLong("id", id);
Employer employer = (Employer) query.uniqueResult();
return employer;
}
/**
* 更新分类名
*
* @param typeId
* @param typeName
*/
public void updateTypeName(Integer typeId, String typeName) {
String hql =
"update SecondType st set st.typeName = :typeName st.lastModifyTime = :lastModifyTime where st.typeId = :typeId";
Query query = getSession().createQuery(hql);
query.setInteger("typeId", typeId);
query.setString("typeName", typeName);
query.setLong("lastModifyTime", System.currentTimeMillis());
query.executeUpdate();
}
@Override
public int delete(int id) {
String hql = "delete Authority a where a.aId = ?";
Session session = this.getSession();
Query q = session.createQuery(hql);
q.setLong(0, id);
int result = q.executeUpdate();
return result;
}
public static Query getProductDTOMapByProductLocalInfoIds(
Session session, Long shopId, Set<Long> productLocalInfoIds) {
StringBuffer sb =
new StringBuffer(
"select p,pli from Product p,ProductLocalInfo pli where p.shopId =:shopId and p.id = pli.productId and pli.id in(:productLocalInfoIds)");
Query query = session.createQuery(sb.toString());
query.setLong("shopId", shopId).setParameterList("productLocalInfoIds", productLocalInfoIds);
return query;
}
@Override
public VoiceVerify getTodayCountByTel(Long tel) {
try {
Query query =
sessionFactory
.getCurrentSession()
.createQuery(
"from VoiceVerify obj where obj.telephone=? and obj.updateTime>? order by obj.updateTime desc");
query.setLong(0, tel);
query.setLong(1, TimeUtils.getStartOfDay(System.currentTimeMillis()));
query.setFirstResult(0);
query.setMaxResults(1);
return (VoiceVerify) query.uniqueResult();
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
@SuppressWarnings("unchecked")
public Contrat getContratSantePersonneAVenir(Long idPersonne) {
final StringBuffer requete = new StringBuffer("select contrat_numero from data_contrat ");
requete.append("where data_contrat.contrat_assure_uid = :idPersonne ");
requete.append("and data_contrat.contrat_statut_uid = :idStatutFutur ");
requete.append("and data_contrat.contrat_nature_uid = :idNatureSante ");
// requete.append("and data_contrat.contrat_adhesion_onum <> 'groupe' ");
requete.append("and data_contrat.contrat_date_adhesion > now() ");
requete.append("and data_contrat.contrat_visible = true ");
requete.append("group by contrat_numero");
final Query crit = createSqlQuery(requete.toString());
crit.setLong("idPersonne", idPersonne);
crit.setLong("idStatutFutur", adherentMappingService.getIdStatutContratFutur());
crit.setLong("idNatureSante", adherentMappingService.getIdNatureContratSante());
final List<String> resultats = (ArrayList<String>) crit.list();
return resultats.size() == 1 ? ((Contrat) getContratByNumero(resultats.get(0))) : null;
}
@Override
@Transactional(readOnly = true)
public Doctor getDoctorById(Long doctorId) {
Session session = sessionFactory.getCurrentSession();
Query query = session.createQuery("from Doctor where id = :doctorId");
query.setLong("doctorId", doctorId);
Doctor doctor = (Doctor) query.uniqueResult();
return doctor;
}
public int updateVerificationCodeById(long id, String text) {
// TODO Auto-generated method stub
Session session = sessionFactory.getCurrentSession();
String hql =
"update VerificationCode verificationCode set verificationCode.text = :text where verificationCode.uniqueId = :uniqueId";
Query queryupdate = session.createQuery(hql);
queryupdate.setLong("uniqueId", new Long(id));
queryupdate.setString("text", text);
int ret = queryupdate.executeUpdate();
return ret;
}
public static Task getById(long taskId) throws HibernateException {
Task task = null;
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();
Query query = session.createQuery(HQL_GET_BY_TASK_ID);
query.setLong("taskId", taskId);
task = (Task) query.uniqueResult();
tx.commit();
return task;
}
@Override
public Boolean hasPersonneContrats(Long idPersonne) {
final StringBuffer requete =
new StringBuffer("select count(id) from Contrat ")
.append("where uidAssure = :idPersonne ")
.append("and isVisible = true");
final Query query = createQuery(requete.toString());
query.setLong("idPersonne", idPersonne);
final Integer nbContrats = Integer.valueOf(query.uniqueResult().toString());
return nbContrats > 0;
}
@SuppressWarnings("unchecked")
@Override
public List<GeoObject> listByLayerIdAndTagKey(Long layerId, String key) {
Query query =
getCurrentSession()
.createQuery(
"select o from GeoObject o join o.geoLayers l join o.tags t where l.id = :layerId and t.key = :tagKey");
query.setLong("layerId", layerId);
query.setString("tagKey", key);
return query.list();
}
public static Customer loadCustomerByCustomerId(String customerId) {
SessionFactory factory = new Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
try {
Query query = session.createQuery("FROM Customer where id = :customerId");
query.setLong("customerId", Long.parseLong(customerId));
return (Customer) query.uniqueResult();
} finally {
session.close();
}
}
public List getQPItems(final Long questionPoolId) {
final HibernateCallback<List<ItemData>> hcb =
session -> {
Query q =
session.createQuery(
"select ab from ItemData ab, QuestionPoolItem qpi where qpi.itemId=ab.itemIdString and qpi.questionPoolId = :id");
q.setLong("id", questionPoolId);
return q.list();
};
return getHibernateTemplate().execute(hcb);
}
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
Query query = session.createQuery("from Book where pub_id = :pubId");
query.setLong("pubId", 2);
List<Book> books = query.list();
for (Book book : books) {
System.out.println(
book.getId() + " | " + book.getName() + " | " + book.getPublisher().getName());
}
session.close();
System.exit(0);
}
@SuppressWarnings("unchecked")
@Override
public List<GeoObject> listByLayerIdPartial(Long layerId, Long start, Long count) {
Query query =
getCurrentSession()
.createQuery("select l.geoObjects from GeoLayer l where l.id = :layerId");
query.setLong("layerId", layerId);
if (count != null) query.setMaxResults(count.intValue());
query.setFirstResult(start.intValue());
return query.list();
}