Ejemplo n.º 1
0
  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;
  }
Ejemplo n.º 2
0
  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;
  }
Ejemplo n.º 7
0
 /**
  * 根据用户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();
 }
Ejemplo n.º 8
0
  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;
  }
Ejemplo n.º 9
0
 /**
  * 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;
 }
Ejemplo n.º 10
0
  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();
  }
Ejemplo n.º 12
0
 @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();
 }
Ejemplo n.º 13
0
 /**
  * ��������:
  *
  * @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;
 }
Ejemplo n.º 14
0
  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;
  }
Ejemplo n.º 16
0
 @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;
 }
Ejemplo n.º 17
0
 public List nativeSQLQuery(final String queryString, Class clazz) {
   SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
   if (clazz != null) {
     sqlQuery = sqlQuery.addEntity(clazz);
   }
   return sqlQuery.list();
 }
Ejemplo n.º 18
0
  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);
  }
Ejemplo n.º 19
0
	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;
	}
Ejemplo n.º 20
0
  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>();
  }
Ejemplo n.º 21
0
  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;
  }
Ejemplo n.º 22
0
  /**
   * 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;
  }
Ejemplo n.º 23
0
  @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);
    }
  }
Ejemplo n.º 24
0
  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;
  }
Ejemplo n.º 26
0
 @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;
 }
Ejemplo n.º 27
0
 @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();
 }
Ejemplo n.º 28
0
  /** 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();
 }
Ejemplo n.º 30
0
 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;
 }