public List<Review> getReviewListbyProductId(int productId) {
SessionFactory sf = HibernateUtility.getSessionfactory();
Session session = sf.openSession();
session.beginTransaction();
String sql = "SELECT * FROM review where product_productId = :pId";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Review.class);
query.setParameter("pId", productId);
System.out.println(query.list().size());
List<Review> reviewlist = query.list();
session.getTransaction().commit();
return reviewlist;
}
public List<Map<String, Object>> getWardwisePerformanceTab(final String zoneName) {
final SQLQuery overAllQry = getQuery("revenue.ptis.wardwise.overall.performance");
overAllQry.setParameter("zoneName", zoneName);
final List<Object[]> overAllData = overAllQry.list();
final Map<String, Map<String, Object>> revenueDataHolder =
new HashMap<String, Map<String, Object>>();
for (final Object[] revenueObj : overAllData) {
final Map<String, Object> revnData = new HashMap<String, Object>();
revnData.put("ward", String.valueOf(revenueObj[0]));
final BigDecimal collectionPerc = (BigDecimal) revenueObj[2];
revnData.put("collectionPerc", collectionPerc != null ? collectionPerc.doubleValue() : "0");
revenueDataHolder.put(String.valueOf(revenueObj[0]), revnData);
}
final SQLQuery monthlyQry = getQuery("revenue.ptis.wardwise.monthly.performance");
monthlyQry.setParameter("zoneName", zoneName);
final List<Object[]> monthlyData = monthlyQry.list();
for (final Object[] revenueObj : monthlyData) {
final Map<String, Object> revnData = revenueDataHolder.get(String.valueOf(revenueObj[0]));
final BigDecimal amtTargeted = (BigDecimal) revenueObj[1];
revnData.put("amtTargeted", amtTargeted != null ? amtTargeted.doubleValue() : "0");
final BigDecimal amt_collectd = (BigDecimal) revenueObj[2];
revnData.put("amt_collectd", amt_collectd != null ? amt_collectd.doubleValue() : "0");
final BigDecimal percCollections = (BigDecimal) revenueObj[3];
revnData.put(
"percCollections",
percCollections != null
? percCollections.setScale(2, RoundingMode.CEILING).doubleValue()
: "0");
}
final List<Map<String, Object>> revenueAggrData =
new ArrayList<Map<String, Object>>(revenueDataHolder.values());
// SORT BY WARDWISE MONTHLY COLLECTION %
sortData(revenueAggrData, "percCollections");
// ASSIGN MONTHLY RANK BASED ON SORT ORDER
assignRank(revenueAggrData, "rank");
// SORT BY WARDWISE OVERALL COLLECTION %
sortData(revenueAggrData, "collectionPerc");
// ASSIGN OVERALL RANK BASED ON SORT ORDER
assignRank(revenueAggrData, "overallrank");
return revenueAggrData;
}
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;
}
public Integer getObsAnswerBetweenDates(
Integer patientId,
List<Integer> questions,
Integer answerId,
Date beforeDate,
Date afterDate,
Date targetDate) {
SQLQuery obsBeforeDate =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select obs_id from obs where person_id = :patientId and value_coded = :conceptId and concept_id in (:questions) and voided = 0 and obs_dateTime > :beforeDate and obs_dateTime < :afterDate ORDER BY abs(:targetDate - obs_dateTime)");
obsBeforeDate.setInteger("patientId", patientId);
obsBeforeDate.setInteger("conceptId", answerId);
obsBeforeDate.setDate("beforeDate", beforeDate);
obsBeforeDate.setDate("afterDate", afterDate);
obsBeforeDate.setDate("targetDate", targetDate);
obsBeforeDate.setParameterList("questions", questions);
List<Integer> obs = obsBeforeDate.list();
if (obs != null && obs.size() > 0) {
return obs.get(0);
}
return null;
}
public Integer getObsValueBetweenDates(
Integer patientId,
Integer conceptId,
Integer groupId,
Date beforeDate,
Date afterDate,
Date targetDate) {
SQLQuery obsBeforeDate =
sessionFactory
.getCurrentSession()
.createSQLQuery(
"select o.obs_id from obs o, obs og where o.person_id = :patientId and o.concept_id = :conceptId and o.voided = 0 and o.obs_dateTime > :beforeDate and o.obs_dateTime < :afterDate and o.obs_group_id = og.obs_id and og.voided = 0 and og.concept_id = :groupId ORDER BY abs(:targetDate - obs_dateTime)");
obsBeforeDate.setInteger("patientId", patientId);
obsBeforeDate.setInteger("conceptId", conceptId);
obsBeforeDate.setDate("beforeDate", beforeDate);
obsBeforeDate.setInteger("groupId", groupId);
obsBeforeDate.setDate("afterDate", afterDate);
obsBeforeDate.setDate("targetDate", targetDate);
List<Integer> obs = obsBeforeDate.list();
if (obs != null && obs.size() > 0) {
return obs.get(0);
}
return null;
}
/**
* 根据用户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 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;
}
/**
* 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;
}
private static void updateService(Session session) {
SQLQuery sqlQuery =
session.createSQLQuery("select ID," + "SERVICE_TYPE," + "PRICED_TYPE" + " from SERVICE");
List list = sqlQuery.list();
for (Object o : list) {
BigInteger ID = (BigInteger) ((Object[]) o)[0];
String SERVICE_TYPE = (String) ((Object[]) o)[1];
String PRICED_TYPE = (String) ((Object[]) o)[2];
sqlQuery =
session.createSQLQuery(
"INSERT INTO FURNITURE_CODE ("
+ "ID,"
+ "SERVICE_TYPE,"
+ "PRICED_TYPE,"
+ "MANUFACTURER_ID, "
+ "CODE,"
+ "NAME)"
+ " VALUES (?, ?, ?, ?, ?, ?)");
sqlQuery.setBigInteger(0, ID);
sqlQuery.setString(1, SERVICE_TYPE);
sqlQuery.setString(2, PRICED_TYPE);
sqlQuery.setBigInteger(3, new BigInteger("20"));
sqlQuery.setString(4, "0");
sqlQuery.setString(5, SERVICE_TYPE);
sqlQuery.executeUpdate();
}
}
@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();
}
@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();
}
/**
* ��������:
*
* @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;
}
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 List<DeviceList> getDeviceList(String location) {
SessionFactory sessionFactory = HibernateSessionFactory.getSessionFactory();
List<DeviceList> results = new ArrayList<DeviceList>();
Session session = sessionFactory.openSession();
if (session != null) {
SQLQuery query =
session.createSQLQuery(
"select d.*,da.*,du.* "
+ "from device_app.device d, device_app.device_availability da, device_app.device_user du "
+ "where d.id=da.device_id and "
+ "da.checked_out_user = du.email_id and "
+ "d.device_status='active' and "
+ "du.user_location=:location");
query.addEntity(Device.class);
query.addEntity(DeviceAvailability.class);
query.addEntity(DeviceUser.class);
query.setString("location", location);
List<Object[]> resultList = query.list();
if (resultList != null && resultList.size() > 0) {
System.out.println("results size is " + resultList.size());
for (Object[] array : resultList) {
DeviceList row = new DeviceList();
for (Object resultObject : array) {
System.out.println(resultObject.getClass());
if (resultObject instanceof Device) {
Device device = (Device) resultObject;
row.setBarCodeIdentifier(device.getBarCodeId());
row.setDeviceCapacity(device.getDeviceCapacity());
row.setDeviceName(device.getDeviceName());
row.setDeviceNickname(device.getDeviceNickName());
row.setDeviceOs(device.getDeviceOs());
row.setDeviceOsVersion(device.getDeviceOsVersion());
row.setDeviceSerialNumber(device.getDeviceSerialNumber());
row.setDeviceStatus(device.getDeviceStatus());
row.setDeviceType(device.getDeviceType());
row.setId(device.getId());
row.setOwnedBy(device.getDeviceOwner());
} else if (resultObject instanceof DeviceAvailability) {
DeviceAvailability availability = (DeviceAvailability) resultObject;
//
// row.setCheckedOutAt(DateUtils.truncate(availability.getModifiedDate(),Calendar.MINUTE).toString());
row.setCheckedOutAt(
DateFormatUtils.format(availability.getModifiedDate(), "yyyy-MM-dd HH:mm:ss"));
row.setCurrentlyCheckedOutById(availability.getDeviceUser().getEmailId());
row.setIsCheckedOut(availability.isIsCheckedOut());
row.setLocation(location);
} else {
DeviceUser user = (DeviceUser) resultObject;
}
}
results.add(row);
}
}
}
return results;
}
@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 List nativeSQLQuery(final String queryString, Class clazz) {
SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
if (clazz != null) {
sqlQuery = sqlQuery.addEntity(clazz);
}
return sqlQuery.list();
}
public void AverageRatingByProductName(String prodName) {
SessionFactory sf = HibernateUtility.getSessionfactory();
Session session = sf.openSession();
session.beginTransaction();
String sql1 = "SELECT * FROM product where productName = :productName";
SQLQuery query1 = session.createSQLQuery(sql1);
query1.setParameter("productName", prodName);
query1.addEntity(Product.class);
Product product = (Product) query1.uniqueResult();
String sql2 = "SELECT * FROM ratingdetails where productId = :productId";
SQLQuery query2 = session.createSQLQuery(sql2);
query2.setParameter("productId", product.getProductId());
query2.addEntity(RatingDetails.class);
List ratings = query2.list();
session.getTransaction().commit();
float count = ratings.size();
int sum = 0;
for (Object r : ratings) {
RatingDetails rate = (RatingDetails) r;
sum = sum + rate.getRating();
}
float averageRating = (sum / count);
System.out.println("pid " + product + " avg " + averageRating);
}
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 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 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;
}
/**
* 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;
}
@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);
}
}
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;
}
@Transactional
@Override
public String removeSyncDuplicates() {
String syncTable =
"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'openmrs' AND table_name in('sync_server_record', 'sync_record');";
String q =
"SELECT MIN(record_id) as record_id FROM sync_record s group by original_uuid having count(*)>1;";
String delete_sync_server_record =
" Delete from sync_server_record where record_id in (:recordIds);";
String delete_sync_record = " Delete from sync_record where record_id in (:recordIds);";
String result = "";
SQLQuery checkSyncInstallation = sessionFactory.getCurrentSession().createSQLQuery(syncTable);
List noOfSyncTables = checkSyncInstallation.list();
Integer res = ((BigInteger) noOfSyncTables.get(0)).intValue();
if (res == 0) {
return "Sync Module is not installed. Please install it if needed";
}
SQLQuery selectDuplicatesQuery = sessionFactory.getCurrentSession().createSQLQuery(q);
List<Integer> duplicateIds = selectDuplicatesQuery.list();
if (duplicateIds.isEmpty()) {
return "No duplicates were found.";
}
SQLQuery deleteDuplicateSyncServerRecordQuery =
sessionFactory.getCurrentSession().createSQLQuery(delete_sync_server_record);
SQLQuery deleteDuplicateSyncRecordQuery =
sessionFactory.getCurrentSession().createSQLQuery(delete_sync_record);
deleteDuplicateSyncServerRecordQuery.setParameter("recordIds", duplicateIds);
deleteDuplicateSyncRecordQuery.setParameter("recordIds", duplicateIds);
int syncServerRecordResult = deleteDuplicateSyncServerRecordQuery.executeUpdate();
result +=
(syncServerRecordResult >= 0)
? syncServerRecordResult + " duplicates in sync_server_record successfully removed\n"
: "Could not remove sync_server_record duplicates\n";
int syncRecordResult = deleteDuplicateSyncRecordQuery.executeUpdate();
result +=
(syncRecordResult >= 0)
? syncRecordResult + " duplicates in sync_record successfully removed\n"
: "Could not remove sync_record duplicates\n";
return result;
}
@Override
@SuppressWarnings("unchecked")
public List<CommentModel> findByAllWithSQLQuery() {
Session session = sessionFactory.getCurrentSession();
SQLQuery query = session.createSQLQuery("SELECT * FROM comment");
List<CommentModel> result = query.list();
return result;
}
@Override
public List<LanguageProficiency> findByMemberId(int memberId) {
SQLQuery sqlQuery =
getNamedSQLQuery("resume.query.updateResume.getLanguageProficiencyByMemberId");
sqlQuery.setParameter("memberId", memberId);
sqlQuery.addEntity(LanguageProficiency.class);
return (List<LanguageProficiency>) sqlQuery.list();
}
/** Get the user details based on the userId */
public Object[] getRating(int currentUserId, int userId) {
Object[] objects = new Object[2];
Session session = null;
try {
session = DBUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
String sql =
"SELECT count(*) FROM USERWISH WHERE ( USERID = :currentUserId and USER_TWO_ID =:userId) or (USERID = :currentUserId1 and USER_TWO_ID =:userId1)";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("currentUserId", currentUserId);
query.setParameter("userId", userId);
query.setParameter("currentUserId1", userId);
query.setParameter("userId1", currentUserId);
java.util.List results = (java.util.List) query.list();
// check if the current user can rate the userId.
if (results.get(0) instanceof BigInteger && ((BigInteger) results.get(0)).intValue() > 0) {
objects[0] = true;
// fetch the previous rating value
String userRatingSql =
"SELECT Rating FROM UserRating WHERE Reviewer =:reviewer and Reviewed = :reviewed";
SQLQuery userRatingQuery = session.createSQLQuery(userRatingSql);
userRatingQuery.addEntity(User.class);
userRatingQuery.setParameter("reviewer", currentUserId);
userRatingQuery.setParameter("reviewed", userId);
java.util.List userRatingResults = (java.util.List) query.list();
if (userRatingResults.get(0) instanceof BigInteger) {
objects[1] = ((BigInteger) results.get(0)).intValue();
}
}
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 objects;
}
public <X> List<X> findBySql(String sql, Object... values) {
SQLQuery sqlQuery = getSession().createSQLQuery(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
sqlQuery.setParameter(i, values[i]);
}
}
return sqlQuery.list();
}
public int countBySQL(String count_sql) {
SessionFactory sf = HibernateUtil.getSessionFactory();
Session session = sf.getCurrentSession();
session.beginTransaction();
SQLQuery q = session.createSQLQuery(count_sql);
int ret = ((BigInteger) q.list().get(0)).intValue();
session.getTransaction().commit();
return ret;
}