/*
Gets the LoginID ignoring case.
*/
private Login findByIdOracleInsensitive(org.openiam.idm.srvc.auth.dto.LoginId id) {
log.debug("findByIdOracleInsensitive..");
String select =
" select /*+ INDEX(IDX_LOGIN_UPPER) */ "
+ " SERVICE_ID, LOGIN, MANAGED_SYS_ID, IDENTITY_TYPE, CANONICAL_NAME, USER_ID, PASSWORD, "
+ " PWD_EQUIVALENT_TOKEN, PWD_CHANGED, PWD_EXP, RESET_PWD, FIRST_TIME_LOGIN, IS_LOCKED, STATUS, "
+ " GRACE_PERIOD, CREATE_DATE, CREATED_BY, CURRENT_LOGIN_HOST, AUTH_FAIL_COUNT, LAST_AUTH_ATTEMPT, "
+ " LAST_LOGIN, IS_DEFAULT, PWD_CHANGE_COUNT, LAST_LOGIN_IP, PREV_LOGIN, PREV_LOGIN_IP, "
+ " PSWD_RESET_TOKEN, PSWD_RESET_TOKEN_EXP, LOGIN_ATTR_IN_TARGET_SYS "
+ " FROM LOGIN "
+ " WHERE SERVICE_ID = :serviceId AND UPPER(LOGIN) = :login AND MANAGED_SYS_ID = :managedSysId ";
log.debug("SQL to get Identity: " + select);
Session session = sessionFactory.getCurrentSession();
SQLQuery qry = session.createSQLQuery(select);
qry.addEntity(Login.class);
qry.setString("serviceId", id.getDomainId());
qry.setString("login", id.getLogin().toUpperCase());
qry.setString("managedSysId", id.getManagedSysId());
try {
return (Login) qry.uniqueResult();
} catch (Exception e) {
log.error(e.toString());
}
return null;
}
/** Removes the push notification Id */
public int nullifyPushId(int userId) {
Session session = null;
int pushId = 0;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql = "DELETE FROM SESSION WHERE PUSHID = :pushId && USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("pushId", pushId);
query.setParameter("user_id", userId);
int result = query.executeUpdate();
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
e.printStackTrace();
System.out.println("error");
return -1;
} finally {
if (session != null && session.isOpen()) session.close();
}
return pushId;
}
public ArrayList<com.application.baatna.bean.Session> getNearbyUsers(int userId) {
ArrayList<com.application.baatna.bean.Session> users = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
users = new ArrayList<com.application.baatna.bean.Session>();
String sql = "SELECT * FROM SESSION WHERE USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("user_id", userId);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
users.add((com.application.baatna.bean.Session) iterator.next());
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return users;
}
public Integer userIDInBd(User user, String id) {
Session session = HibernateUtil.openSession();
Transaction transaction = null;
Integer userIdDB = null;
try {
transaction = session.getTransaction();
transaction.begin();
SQLQuery query = session.createSQLQuery("SELECT id FROM user WHERE login=?;");
query.setParameter(0, user.getLogin());
userIdDB = (Integer) query.uniqueResult();
System.out.println("Печать id юзера из базы" + userIdDB);
transaction.commit(); // применяем транзакцию
} catch (Exception e) {
// отменяем транзакцию
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}
return userIdDB;
}
@SuppressWarnings("unchecked")
public static <E> List<E> find(
Session session, String sql, Class<E> clazz, PageInfo pageInfo, Object... params) {
List<E> list = new ArrayList<E>();
SQLQuery sqlQuery = session.createSQLQuery(sql);
setParameter(sqlQuery, params);
if (pageInfo != null) {
Integer first = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
// if(ValidateUtil.isEmpty(first)){
// first = 1;
// }
sqlQuery.setFirstResult(first);
@SuppressWarnings("unused")
Integer pageSize = pageInfo.getPageSize();
// if(Validator.isEmpty(pageSize)){
// pageSize = PageInfoUtil.getDefaultPagesize();
// }
sqlQuery.setMaxResults(pageInfo.getPageSize());
Integer total = getCount(session, sql, params);
pageInfo.setPageTotal(total);
}
sqlQuery = sqlQuery.addEntity("model", clazz);
list = sqlQuery.list();
return list;
}
public String sqlRun() {
logs = "数据管理器，v0.1,请对该应用地址保密！";
pagesize = 20;
currentPage = "1";
Session session = sessionFactory.openSession();
list = null;
try {
SQLQuery query = session.createSQLQuery(sql);
if (sql.trim().toUpperCase().indexOf("SELECT") == 0) {
list = query.list();
if (list.size() > 0) {
if (list.get(0) instanceof Object[]) {
unitonlyone = false;
} else {
unitonlyone = true;
}
}
} else {
query.executeUpdate();
}
logs = "SQL执行成功：" + sql + query.getQueryString();
} catch (Exception e) {
logs = sql + ":" + e.getMessage() + e.toString();
} finally {
session.close();
}
type = "2";
return SUCCESS;
}
public List nativeSQLQuery(final String queryString, Class clazz) {
SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
if (clazz != null) {
sqlQuery = sqlQuery.addEntity(clazz);
}
return sqlQuery.list();
}
/**
* ��������:
*
* @param username
* @param userpass
* @return
* @see com.demo.hello.model.login.dao.UserDao#queryUserByUserAndPass(java.lang.String,
* java.lang.String)
*/
public boolean queryUserByUserAndPass(String username, String userpass) {
// TODO Auto-generated method stub
boolean existtag = false;
Session session = getSession();
Transaction tx = session.beginTransaction();
try {
String sql =
"select * from com_t_users where username='******' and userpass = '******'";
SQLQuery query = session.createSQLQuery(sql);
List<Object[]> list = query.list();
tx.commit();
if (list.size() > 0) existtag = true;
} catch (HibernateException e) {
// TODO Auto-generated catch block
tx.rollback();
existtag = false;
// e.printStackTrace();
} finally {
session.close();
}
return existtag;
}
@Override
public List<Visiting> getVisitngsToBeCreatedForAnotherThreeMonths() throws Exception {
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MONTH, -3);
Date date = calendar.getTime();
System.out.println("BEFORE THREE MONTHS :" + date);
String queryString =
"SELECT visiting_id as visiting_id"
+ " FROM visiting"
+ " WHERE start_date IN (SELECT Max(start_date)"
+ " FROM visiting"
+ " WHERE visiting_status='ACTIVE'"
+ " GROUP BY avg_time_per_appointment, created_user, no_of_patients, hospital_doctor_id, day_of_week)";
SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(queryString);
sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List<Map> result = sqlQuery.list();
List<String> results = new ArrayList<String>();
for (Map map : result) {
results.add((String) map.get("visiting_id"));
}
Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Visiting.class);
criteria.add(
Restrictions.conjunction()
.add(Restrictions.in("visitingId", results))
.add(Restrictions.le("startDate", date)));
return criteria.list();
}
/**
* 根据用户ID查询该用户允许访问的所有菜单列表
*
* @param userId
* @return
*/
@SuppressWarnings("unchecked")
public List<Menu> getAllowedAccessMenu(Long userId) {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("select * from (");
// 获取Sec_Menu表中定义且未关联资源表Sec_Resource的所有菜单列表
sqlBuffer.append(" select m.id,m.name,m.parent_menu,m.description,m.orderby from sec_menu m ");
sqlBuffer.append(" where not exists (select re.id from sec_resource re where re.menu = m.id)");
sqlBuffer.append(" union all ");
// 获取Sec_Resource表中已关联且未设置权限的菜单列表
sqlBuffer.append(
" select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_resource re ");
sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id ");
sqlBuffer.append(
" where re.menu is not null and not exists (select ar.authority_id from sec_authority_resource ar where ar.resource_id = re.id)");
sqlBuffer.append(" union all ");
// 获取Sec_Resource表中已关联且设置权限，并根据当前登录账号拥有相应权限的菜单列表
sqlBuffer.append(
" select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_user u ");
sqlBuffer.append(" left outer join sec_role_user ru on u.id=ru.user_id ");
sqlBuffer.append(" left outer join sec_role r on ru.role_id=r.id ");
sqlBuffer.append(" left outer join sec_role_authority ra on r.id = ra.role_id ");
sqlBuffer.append(" left outer join sec_authority a on ra.authority_id = a.id ");
sqlBuffer.append(" left outer join sec_authority_resource ar on a.id = ar.authority_id ");
sqlBuffer.append(" left outer join sec_resource re on ar.resource_id = re.id ");
sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id ");
sqlBuffer.append(" where u.id=? and re.menu is not null ");
sqlBuffer.append(") tbl order by orderby");
SQLQuery query = menuDao.createSQLQuery(sqlBuffer.toString(), userId);
query.addEntity(Menu.class);
return query.list();
}
public Map<String, Object> coverageEfficiencyWard(final String zoneName) {
final SQLQuery overAllQry = getQuery("revenue.ptis.coverage.efficiency.ward");
overAllQry.setParameter("zoneName", zoneName);
final List<Object[]> overAllData = overAllQry.list();
final Map<String, Map<String, Object>> coverageDataHolder =
new HashMap<String, Map<String, Object>>();
for (final Object[] revenueObj : overAllData) {
final Map<String, Object> revnData = new HashMap<String, Object>();
revnData.put("name", String.valueOf(revenueObj[0]));
final BigDecimal noOfProps =
revenueObj[1] != null ? new BigDecimal(revenueObj[1].toString()) : BigDecimal.ZERO;
revnData.put("noOfProps", noOfProps != null ? noOfProps.doubleValue() : "0");
final BigDecimal noOfTaxProps =
revenueObj[2] != null ? new BigDecimal(revenueObj[2].toString()) : BigDecimal.ZERO;
revnData.put("noOfTaxProps", noOfTaxProps != null ? noOfTaxProps.doubleValue() : "0");
final BigDecimal coverageEfficiency =
revenueObj[3] != null ? new BigDecimal(revenueObj[3].toString()) : BigDecimal.ZERO;
revnData.put("y", coverageEfficiency != null ? coverageEfficiency.doubleValue() : "0");
coverageDataHolder.put(String.valueOf(revenueObj[0]), revnData);
}
final List<Map<String, Object>> revenueCovEfficiency =
new ArrayList<Map<String, Object>>(coverageDataHolder.values());
// SORT BY WARDWISE Coverage Efficiency %
sortData(revenueCovEfficiency, "y");
final Map<String, Object> revenueAggrData = new HashMap<String, Object>();
revenueAggrData.put("overallCoverage", revenueCovEfficiency);
return revenueAggrData;
}
public String addCriticalTransaction(int authID, String ManagerID) {
try {
Session session = Hibernate_Utility.getSessionFactory().openSession();
criticalTransactions cTrans = getCriticalTransaction(authID);
if (cTrans == null) {
return "Action Failed! Please try later.";
}
String insertInternalUserQuery =
"INSERT INTO critical_transactions_authorizations (`transaction_id`,`auth_given_by`,`auth_given_to`) VALUES ( "
+ cTrans.getTransactionId()
+ " , \'"
+ cTrans.getAuthGivento()
+ "\' , \'"
+ ManagerID
+ "\');";
SQLQuery query = session.createSQLQuery(insertInternalUserQuery);
int rowsAffected = query.executeUpdate();
if (rowsAffected > 0) {
String commitQuery = "COMMIT;";
query = session.createSQLQuery(commitQuery);
rowsAffected = query.executeUpdate();
return "successfully escalated!";
} else {
return "Cannot Escalate now! Try Later";
}
} catch (HibernateException e) {
return "Action Failed! Please try later.";
} catch (ApplicationException e) {
return "Action Failed! Please try later.";
}
}
public Date getDateOfWorkflowStateChange(
Integer patientId, Integer conceptId, Date startDate, Date endDate) {
Date sDate = new Date();
sDate.setTime(0);
Date eDate = Calendar.getInstance().getTime();
if (startDate != null) {
sDate = startDate;
}
if (endDate != null) {
eDate = endDate;
}
SQLQuery dateOfWorkFlowStateQuery =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select start_date from patient_state ps, patient_program pp, program_workflow_state pws where pp.patient_program_id = ps.patient_program_id and pws.program_workflow_state_id = ps.state and ps.voided = 0 and pws.concept_id = :conceptId and pp.patient_id = :patientId and ps.start_date >= :startDate and ps.start_date <= :endDate");
dateOfWorkFlowStateQuery.setInteger("patientId", patientId);
dateOfWorkFlowStateQuery.setInteger("conceptId", conceptId);
dateOfWorkFlowStateQuery.setDate("startDate", sDate);
dateOfWorkFlowStateQuery.setDate("endDate", eDate);
List<Date> dateOfWorkflow = (List<Date>) dateOfWorkFlowStateQuery.list();
// TODO: figure out what is the most logical date to return when multiples are found
if (dateOfWorkflow != null && dateOfWorkflow.size() > 0) {
return dateOfWorkflow.get(0);
}
return null;
}
public Date getDateOfProgramEnrolmentAscending(
Integer patientId, Integer programId, Date startDate, Date endDate) {
Date sDate = new Date();
sDate.setTime(0);
Date eDate = Calendar.getInstance().getTime();
if (startDate != null) {
sDate = startDate;
}
if (endDate != null) {
eDate = endDate;
}
SQLQuery dateOfProgramEnrolment =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select date_enrolled from patient_program where patient_id = :patientId and program_id = :programId and voided = 0 and date_enrolled >= :startDate and date_enrolled <= :endDate order by date_enrolled asc");
dateOfProgramEnrolment.setInteger("patientId", patientId);
dateOfProgramEnrolment.setInteger("programId", programId);
dateOfProgramEnrolment.setDate("startDate", sDate);
dateOfProgramEnrolment.setDate("endDate", eDate);
List<Date> dateOfEnrolment = (List<Date>) dateOfProgramEnrolment.list();
// TODO: figure out what is the most logical date to return when multiples are found
if (dateOfEnrolment != null && dateOfEnrolment.size() > 0) {
return dateOfEnrolment.get(0);
}
return null;
}
/**
* 拿到所有的根类别
*
* @return
*/
public List<Category> getTopCategories() {
String sql = "select * from znzn_category where grade=1";
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
List<Category> list = q.addEntity(Category.class).list();
return list;
}
public boolean addUserToBlockedList(int blockingUserId, int userId) {
Session session = null;
boolean retVal = false;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"INSERT INTO BLOCKING(BLOCKING_USERID,BLOCKED_USERID) VALUES(:blockingUserId,:userId)";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Blocking.class);
query.setParameter("userId", userId);
query.setParameter("blockingUserId", blockingUserId);
query.executeUpdate();
retVal = true;
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
e.printStackTrace();
} finally {
if (session != null && session.isOpen()) session.close();
}
return retVal;
}
/**
* 拿到某一个目录的下一级子类别
*
* @return
*/
public List<Category> getChilds(int id) {
String sql = "select * from znzn_category where pid=" + id;
Session session = sessionFactory.getCurrentSession();
SQLQuery q = session.createSQLQuery(sql);
List<Category> list = q.addEntity(Category.class).list();
return list;
}
public boolean isUserBlocked(int userId, int blockerId) {
Session session = null;
boolean retVal = false;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"SELECT count(*) FROM BLOCKING WHERE BLOCKING_USERID= :blockerId AND BLOCKED_USERID= :userId ";
SQLQuery query = session.createSQLQuery(sql);
// query.addEntity(com.application.baatna.bean.Blocking.class);
query.setParameter("userId", userId);
query.setParameter("blockerId", blockerId);
java.util.List results = (java.util.List) query.list();
// query.executeUpdate();
if (results.get(0) instanceof BigInteger) {
int count = ((BigInteger) results.get(0)).intValue();
if (count != 0) retVal = true;
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
e.printStackTrace();
} finally {
if (session != null && session.isOpen()) session.close();
}
return retVal;
}
public static List<Agreement> findFiltered(
String agreementId,
String billUcn,
String agreementLinkId,
String agreementTypeCode,
String note,
char neStatus) {
try {
String sqlQuery = "SELECT agreement.* FROM agreement WHERE `status` <> '" + neStatus + "' ";
if (agreementId != null && agreementId.length() > 0)
sqlQuery += " AND id like '%" + agreementId + "%' ";
if (billUcn != null && billUcn.length() > 0)
sqlQuery += " AND billUcn like '%" + billUcn + "%'";
if (agreementLinkId != null && agreementLinkId.length() > 0)
sqlQuery += " AND agreementLinkId like '%" + agreementLinkId + "%'";
if (agreementTypeCode != null && agreementTypeCode.length() > 0)
sqlQuery += " AND agreementTypeCode like '%" + agreementTypeCode + "%'";
if (note != null && note.length() > 0) sqlQuery += " AND note like '%" + note + "%'";
sqlQuery += " order by id";
SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery);
query.addEntity(getObjectReference(objectName));
@SuppressWarnings("unchecked")
List<Agreement> objects = query.list();
return objects;
} catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
return new ArrayList<Agreement>();
}
public com.application.baatna.bean.Session getSession(int userId) {
com.application.baatna.bean.Session loginSession = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
loginSession = new com.application.baatna.bean.Session();
// finding user info
System.out.println("Getting Record");
String sql = "SELECT * FROM SESSION WHERE USERID = :userid";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("userid", userId);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
loginSession = (com.application.baatna.bean.Session) iterator.next();
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return loginSession;
}
public long count(final String queryString, final Object... values) {
String tmp = queryString.toLowerCase().replaceAll("\\s+", " ");
if (!tmp.contains("group by") && !tmp.contains("distinct")) {
String countQueryString =
"select count(*) " + queryString.substring(queryString.indexOf("from "));
return (Long) queryUnique(countQueryString, values);
}
QueryTranslatorImpl queryTranslator =
new QueryTranslatorImpl(
null, queryString, Collections.EMPTY_MAP, (SessionFactoryImplementor) sessionFactory);
queryTranslator.compile(Collections.EMPTY_MAP, false);
final String countQueryString =
"select count(*) as result from (" + queryTranslator.getSQLString() + ")";
SQLQuery query = getSession().createSQLQuery(countQueryString);
if (cacheRegion.get() != null) {
// TODO
// SQLQuery遭遇UpdateTimestampsCache时，有问题。详见:http://raymondhekk.iteye.com/blog/250427
// 故这里暂停使用二级缓存
// query.setCacheRegion(cacheRegion.get());
// query.setCacheable(true);
cacheRegion.set(null);
}
for (int position = 0; position < values.length; position++) {
query.setParameter(position, values[position]);
}
query.addScalar("result", StandardBasicTypes.BIG_DECIMAL);
return ((Number) query.uniqueResult()).longValue();
}
/** Delete an accessToken for a particular user */
public boolean nullifyAccessToken(int userId, String accessToken) {
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql = "DELETE FROM SESSION WHERE ACCESS_TOKEN = :access_token && USERID = :user_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(com.application.baatna.bean.Session.class);
query.setParameter("access_token", accessToken);
query.setParameter("user_id", userId);
int result = query.executeUpdate();
transaction.commit();
session.close();
return true;
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return false;
}
// Метод проверки существования пользователя по логину в базе
public String userLoginInBd(User user) {
Session session = HibernateUtil.openSession();
Transaction transaction = null;
String userLoginDB = null;
try {
transaction = session.getTransaction();
transaction.begin();
SQLQuery query = session.createSQLQuery("SELECT login FROM user WHERE login=?;");
query.setParameter(0, user.getLogin());
userLoginDB = (String) query.uniqueResult(); // возвращает экземпляр или null
transaction.commit(); // применяем транзакцию
System.out.println("Пользователь с логином = " + userLoginDB + "уже существует");
} catch (Exception e) {
// отменяем транзакцию
transaction.rollback();
e.printStackTrace();
} finally {
session.close();
}
return userLoginDB;
}
/**
* Get user details based on the email\password combo. Used in case of login. TODO: Add check for
* access Token from session
*/
public User getUserDetails(String email, String passWord) {
User user = null;
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
user = new User();
String sql = "SELECT * FROM USER WHERE EMAIL = :email_id && PASSW = :password";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("email_id", email);
query.setParameter("password", passWord);
java.util.List results = (java.util.List) query.list();
for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) {
user = (User) iterator.next();
}
transaction.commit();
session.close();
} catch (HibernateException e) {
System.out.println(e.getMessage());
System.out.println("error");
} finally {
if (session != null && session.isOpen()) session.close();
}
return user;
}
/**
* Deletes all database entries based on the class where method is called.
*
* @return
*/
public synchronized boolean deleteAll() {
Session session = null;
Transaction transaction = null;
boolean errorResult = false;
try {
session = HibernateUtil.getCurrentSession();
transaction = session.beginTransaction();
SQLQuery query = session.createSQLQuery(DELETE_ALL + getTableName());
query.executeUpdate();
transaction.commit();
return true;
} catch (HibernateException exception) {
rollback(transaction);
BookingLogger.getDefault()
.severe(this, Messages.METHOD_DELETE_ALL, Messages.HIBERNATE_FAILED, exception);
return errorResult;
} catch (RuntimeException exception) {
rollback(transaction);
BookingLogger.getDefault()
.severe(this, Messages.METHOD_DELETE_ALL, Messages.GENERIC_FAILED, exception);
return errorResult;
} finally {
closeSession();
}
}
public Collection<KeyValueModel> getTradingPartnerEmailID() {
Collection<KeyValueModel> keyvalue = new ArrayList<KeyValueModel>();
Set<String> emailId = new HashSet<String>();
try {
StringBuilder query = new StringBuilder();
query.append(
"SELECT CONCAT_WS(',',GROUP_CONCAT(cs.`email1`) ,GROUP_CONCAT(cs.`email2`),GROUP_CONCAT(cc.`email`))");
query.append(" FROM customer_address cs LEFT JOIN customer_contact cc ");
query.append("ON cc.`customer_address_id` = cs.`id` ");
query.append("WHERE cs.trading_partner_id IN (SELECT tp.`id` ");
query.append("FROM trading_partner tp WHERE tp.`account_number` IN ");
query.append("(SELECT acc.`number` FROM user_account acc WHERE ");
query.append("acc.`user_id` = ").append(currentUser.getId()).append("))");
SQLQuery s = new BaseHibernateDAO<Domain>().createSQLQuery(query.toString());
Object result = s.uniqueResult();
emailId.addAll(Arrays.asList(result.toString().split(",")));
keyvalue.add(new KeyValueModel("", "-- Select --"));
for (String email : emailId) {
if (!email.isEmpty()) {
keyvalue.add(new KeyValueModel(email));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return keyvalue;
}
/**
* SQL 分页查询
*
* @param page
* @param sqlString
* @param resultClass
* @param parameter
* @return
*/
@SuppressWarnings("unchecked")
public <E> Page<E> findBySql(
Page<E> page, String sqlString, Parameter parameter, Class<?> resultClass) {
// get count
if (!page.isDisabled() && !page.isNotCount()) {
String countSqlString = "select count(*) " + removeSelect(removeOrders(sqlString));
// page.setCount(Long.valueOf(createSqlQuery(countSqlString,
// parameter).uniqueResult().toString()));
Query query = createSqlQuery(countSqlString, parameter);
List<Object> list = query.list();
if (list.size() > 0) {
page.setCount(Long.valueOf(list.get(0).toString()));
} else {
page.setCount(list.size());
}
if (page.getCount() < 1) {
return page;
}
}
// order by
String sql = sqlString;
if (StringUtils.isNotBlank(page.getOrderBy())) {
sql += " order by " + page.getOrderBy();
}
SQLQuery query = createSqlQuery(sql, parameter);
// set page
if (!page.isDisabled()) {
query.setFirstResult(page.getFirstResult());
query.setMaxResults(page.getMaxResults());
}
setResultTransformer(query, resultClass);
page.setList(query.list());
return page;
}
@Override
@SuppressWarnings({"rawtypes", "unchecked"})
public List<IpMessageVO> findBySQL(String sql, Map<String, ?> map) {
try {
SQLQuery query = getSession().createSQLQuery(sql);
// Set<String> keys = map.keySet();
query
.addScalar("userName", Hibernate.STRING)
.addScalar("userProperties", Hibernate.STRING)
.addScalar("userAddress", Hibernate.STRING)
.addScalar("contactName", Hibernate.STRING)
.addScalar("contactPhone", Hibernate.STRING)
.addScalar("contactDocumentNum", Hibernate.STRING)
.addScalar("installedAddress", Hibernate.STRING)
.addScalar("installedDate", Hibernate.TIMESTAMP)
.addScalar("startIp", Hibernate.STRING)
.addScalar("endIp", Hibernate.STRING);
query.setResultTransformer(Transformers.aliasToBean(IpMessageVO.class));
// for (String key : keys) {
// Object o = map.get(key);
// if (o instanceof Collection) {
// query.setParameterList(key, (Collection) map.get(key));
// } else if(o instanceof Page){
// query.setFirstResult((((Page) o).getPageNo()-1)*((Page) o).getPageSize());
// query.setMaxResults(((Page) o).getPageSize());
// }else {
// query.setParameter(key, map.get(key));
// }
// }
return query.list();
} catch (Exception e) {
throw new HsCloudException(BillConstant.ACCOUNT_DATABASE_ERROR, e.getMessage(), logger, e);
}
}
@SuppressWarnings("unchecked")
public List<Object[]> countTypeByLine() {
String sql =
"select (case when t3.name = '一号线' then '01号线' "
+ "when t3.name = '二号线' then '02号线' "
+ "when t3.name = '三号线' then '03号线' "
+ "when t3.name = '四号线' then '04号线' "
+ "when t3.name = '五号线' then '05号线' "
+ "when t3.name = '六号线' then '06号线' "
+ "when t3.name = '七号线' then '07号线' "
+ "when t3.name = '八号线' then '08号线' "
+ "when t3.name = '九号线' then '09号线' "
+ "when t3.name = '十号线' then '10号线' "
+ "when t3.name = '十一号线' then '11号线' "
+ "when t3.name = '十二号线' then '12号线' "
+ "when t3.name = '十三号线' then '13号线' "
+ "when t3.name = '十四号线' then '14号线' "
+ "when t3.name = '十五号线' then '15号线' "
+ "when t3.name = '十六号线' then '16号线' end) line,"
+ "t4.name type, sum(to_number(t.compact_price))/10000 sum_price ,count(t.type_1) count_sum "
+ "from t_asset_info t ,t_asset_code_info t3,t_asset_code_info t4 "
+ "where (REGEXP_LIKE(t.compact_price,'^-?[1-9]\\d*$') or REGEXP_LIKE(t.compact_price,'^-?([1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*|0?\\.0+|0)$')) "
+ "and t.removed = 0 and t.route_num = t3.id and t.type_1 = t4.id "
+ "group by t3.name,t4.name "
+ "order by line";
SQLQuery query = hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery(sql);
query
.addScalar("line", Hibernate.STRING)
.addScalar("type", Hibernate.STRING)
.addScalar("sum_price", Hibernate.DOUBLE)
.addScalar("count_sum", Hibernate.LONG);
return query.list();
}
@Override
public void deleteWithSQLQuery(int commentId) {
Session session = sessionFactory.getCurrentSession();
String sql = "DELETE FROM comment WHERE comment_id=:commentId";
SQLQuery query = session.createSQLQuery(sql);
query.setParameter("commentId", commentId);
query.executeUpdate();
}