コード例 #1
0
ファイル: BaseDao.java プロジェクト: artyama/jeesite
 /**
  * 设置查询结果类型
  *
  * @param query
  * @param resultClass
  */
 private void setResultTransformer(SQLQuery query, Class<?> resultClass) {
   if (resultClass != null) {
     if (resultClass == Map.class) {
       query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
     } else if (resultClass == List.class) {
       query.setResultTransformer(Transformers.TO_LIST);
     } else {
       query.addEntity(resultClass);
     }
   }
 }
コード例 #2
0
  @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();
  }
コード例 #3
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);
    }
  }
コード例 #4
0
ファイル: UserDAO.java プロジェクト: app-ad/Baatna-server
  public void setUserDayRating() {

    Session session = null;
    double rating = 0;
    int userId;
    double avg1;
    double avg2;

    try {
      session = DBUtil.getSessionFactory().openSession();
      Transaction transaction = session.beginTransaction();

      // String sql = "SELECT er.Reviewed, (Select avg(Rating) as
      // AvgRating from UserRating ur where ur.Reviewed = er.Reviewed)
      // temp from UserRating er";
      String sql =
          "SELECT er.USERID,(SELECT avg(U2RATEDU1) AS AvgRating from USERWISH uw where uw.USERID=er.USERID and uw.Wish_Status=er.Wish_Status and uw.U2RATEDU1<>:b) avg1, (SELECT avg(U1RATEDU2) AS AvgRatingTwo from USERWISH aw WHERE aw.USER_TWO_ID=er.USERID and aw.Wish_Status=er.Wish_Status and aw.U1RATEDU2<>:a) avg2 from USERWISH er WHERE er.WISH_STATUS= :status";
      SQLQuery query = session.createSQLQuery(sql);
      query.setParameter("a", 0);
      query.setParameter("b", 0);
      query.setParameter("status", CommonLib.STATUS_FULLFILLED);
      query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

      List results = query.list();
      for (Object object : results) {
        Map row = (Map) object;
        userId = (int) row.get("USERID");
        if (row.get("avg1") != null) avg1 = (double) row.get("avg1");
        else avg1 = 0;
        if (row.get("avg2") != null) avg2 = (double) row.get("avg2");
        else avg2 = 0;
        if (avg1 != 0 && avg2 != 0) rating = (avg1 + avg2) / 2;
        else rating = Math.max(avg1, avg2);
        if (rating != 0) {
          String sql2 = "UPDATE USER SET RATING= :rating WHERE USERID= :userId";
          SQLQuery query2 = session.createSQLQuery(sql2);
          query2.addEntity(User.class);
          query2.setParameter("rating", rating);
          query2.setParameter("userId", userId);

          query2.executeUpdate();
        }
      }
      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();
    }
  }
コード例 #5
0
  public List<DmFolderNode> getFirstLevelFolderNodeList(String accountId) {
    List<DmFolderNode> folderNodeList = null;

    String sqlSub =
        "SELECT"
            + " count(*)"
            + " from dm_folder fChild"
            //            + " left join dm_folder_permission fpChild"
            //            + " on fChild.df_id = fpChild.df_id"
            + " where fChild.df_parent_id = f.df_id"
        //            + " and fpChild.da_id = :accountSharedId"
        //            + " and fpChild.da_id <> :accountOwnerId"
        ;

    //        String sqlSub2 = "SELECT"
    //            + " fpPrnt2.df_id"
    //            + " FROM dm_folder_permission fpPrnt2"
    //            + " WHERE fpPrnt2.da_id = :accountSharedId"
    //            + " and fpPrnt2.da_id <> :accountOwnerId"
    //            ;

    String sql =
        "SELECT"
            + " f.df_id as \"id\", f.df_name as \"name\", ("
            + sqlSub
            + ") as \"numChildren\""
            + ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
            + " FROM dm_folder_permission fp"
            + " LEFT JOIN dm_folder f"
            + " on fp.df_id = f.df_id and fp.da_id = f.da_id_owner"
            //            + " LEFT JOIN dm_folder fPrnt"
            //            + " on f.df_parent_id = fPrnt.df_id"
            + " WHERE f.da_id_owner = :accountId"
            //            + " AND fp.da_id <> :accountOwnerId"
            //            + " AND f.da_id_owner = :accountId"
            + " AND f.df_parent_id is null"
            //            + " AND ((fPrnt.df_id IS NULL) OR (fPrnt.df_id NOT IN (" + sqlSub2 + ")))"
            + " ORDER BY f.df_name asc";

    SQLQuery query = getSession().createSQLQuery(sql);

    query.setString("accountId", accountId);

    query.addScalar("id", Hibernate.STRING);
    query.addScalar("name", Hibernate.STRING);
    query.addScalar("numChildren", Hibernate.INTEGER);
    query.addScalar("ownerAccountId", Hibernate.STRING);
    query.addScalar("permissionType", Hibernate.INTEGER);

    query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));

    folderNodeList = query.list();

    return folderNodeList;
  }
コード例 #6
0
ファイル: GenericDao.java プロジェクト: jekay100/J2EENote
 public <N extends Object> List<N> listBySql(
     String sql, Object[] args, Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
   sql = initSort(sql);
   SQLQuery sq = getSession().createSQLQuery(sql);
   setAliasParameter(sq, alias);
   setParameter(sq, args);
   if (hasEntity) {
     sq.addEntity(clz);
   } else sq.setResultTransformer(Transformers.aliasToBean(clz));
   return sq.list();
 }
コード例 #7
0
  public List<DmFolderNode> getChildFolderNodeList(String folderIdParent) {
    DmFolder parentFolder = null;
    if (folderIdParent != null) {
      parentFolder = findById(folderIdParent);
    }

    List<DmFolderNode> folderNodeList = null;

    if (parentFolder != null) {
      String sqlSub =
          "SELECT"
              + " count(*)"
              + " from dm_folder fChild"
              //                + " left join dm_folder_permission fChildPerm"
              //                + " on fChild.df_id = fChildPerm.df_id"
              + " where fChild.df_parent_id = f.df_id"
          //                + " and fChildPerm.da_id = :accountSharedId"
          //                + " and fChildPerm.da_id <> :accountOwnerId"
          ;

      String sql =
          "SELECT"
              + " f.df_id as \"id\", f.df_name as \"name\", ("
              + sqlSub
              + ") as \"numChildren\""
              + ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
              + " FROM dm_folder_permission fp"
              + " LEFT JOIN dm_folder f"
              + " on fp.df_id = f.df_id and fp.da_id = f.da_id_owner"
              + " WHERE f.df_parent_id = :folderIdParent"
              + " ORDER BY f.df_name asc";

      SQLQuery query = getSession().createSQLQuery(sql);

      query.setString("folderIdParent", folderIdParent);
      //            query.setString("accountOwnerId", parentFolder.getOwner().getId());

      query.addScalar("id", Hibernate.STRING);
      query.addScalar("name", Hibernate.STRING);
      query.addScalar("numChildren", Hibernate.INTEGER);
      query.addScalar("ownerAccountId", Hibernate.STRING);
      query.addScalar("permissionType", Hibernate.INTEGER);

      query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));

      folderNodeList = query.list();
    }
    return folderNodeList;
  }
コード例 #8
0
ファイル: GenericDao.java プロジェクト: jekay100/J2EENote
 public <N extends Object> Pager<N> findBySql(
     String sql, Object[] args, Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
   sql = initSort(sql);
   String cq = getCountHql(sql, false);
   SQLQuery sq = getSession().createSQLQuery(sql);
   SQLQuery cquery = getSession().createSQLQuery(cq);
   setAliasParameter(sq, alias);
   setAliasParameter(cquery, alias);
   setParameter(sq, args);
   setParameter(cquery, args);
   Pager<N> pages = new Pager<N>();
   setPagers(sq, pages);
   if (hasEntity) {
     sq.addEntity(clz);
   } else {
     sq.setResultTransformer(Transformers.aliasToBean(clz));
   }
   List<N> datas = sq.list();
   pages.setRows(datas);
   long total = ((BigInteger) cquery.uniqueResult()).longValue();
   pages.setTotal(total);
   return pages;
 }
コード例 #9
0
  /**
   * @ Description - Returns query that retrieves zone/ward/block/propertywise Arrear, Current
   * Demand and Collection Details Final Query Form : select boundary,arrear,current from (select
   * boundary,arrear,0 as collection group by boundary union select boundary,0 as arrear, collection
   * group by boundary) group by boundary
   *
   * @return
   */
  public SQLQuery prepareQuery() {
    StringBuffer queryStr = new StringBuffer("");
    StringBuffer unionQueryStr = new StringBuffer("");
    String arrear_innerCommonQry0 = "",
        arrear_innerCommonQry1 = "",
        current_innerCommonQry0 = "",
        current_innerCommonQry1 = "";
    String finalCommonQry = "",
        finalSelectQry = "",
        finalGrpQry = "",
        finalWhereQry = "",
        finalFrmQry = "";
    String innerSelectQry0 = "",
        innerSelectQry1 = "",
        arrearGroupBy = "",
        whereQry = "",
        collGroupBy = "",
        orderBy = "";
    Long param = null;

    if (boundaryId != -1 && boundaryId != null) {
      param = boundaryId;
    }
    // To retreive Arrear Demand and Collection Details
    arrear_innerCommonQry0 =
        "idc.* from egpt_mv_inst_dem_coll idc, egpt_mv_propertyinfo pi,  eg_installment_master im "
            + "where idc.id_basic_property=pi.basicpropertyid and im.id=idc.id_installment "
            + "and im.start_date not between (select STARTINGDATE from financialyear where now() between STARTINGDATE and ENDINGDATE) "
            + "and  (select ENDINGDATE from financialyear where now() between STARTINGDATE and ENDINGDATE)";

    arrear_innerCommonQry1 =
        "sum(GeneralTax) as arrearGT, sum(LibCessTax) as arrearLC, sum(EduCessTax) as arrearEC,"
            + "sum(UnauthPenaltyTax) as arrearUPT,sum(PenaltyFinesTax) as arrearPFT,sum(SewTax) as arrearST,"
            + "sum(VacantLandTax) as arrearVLT,sum(PubSerChrgTax) as arrearPSCT,sum(GeneralTaxColl) as arrearGTColl, "
            + "sum(LibCessTaxColl) as arrearLCColl, sum(EduCessTaxColl) as arrearECColl,sum(UnauthPenaltyTaxColl) as arrearUPTColl,"
            + "sum(PenaltyFinesTaxColl) as arrearPFTColl,sum(SewTaxColl) as arrearSTColl,"
            + "sum(VacantLandTaxColl) as arrearVLTColl,sum(PubSerChrgTaxColl) as arrearPSCTColl,"
            + "0 as curGT, 0 as curLC, 0 as curEC,0 as curUPT,0 as curPFT,0 as curST,"
            + "0 as curVLT,0 as curPSCT,0 as curGTColl,0 as curLCColl,0 as curECColl,0 as curUPTColl,"
            + "0 as curPFTColl,0 as curSTColl, 0 as curVLTColl,0 as curPSCTColl from (";

    // To retreive Current Demand and Collection Details
    current_innerCommonQry0 =
        "idc.* from egpt_mv_inst_dem_coll idc, egpt_mv_propertyinfo pi,  eg_installment_master im "
            + "where idc.id_basic_property=pi.basicpropertyid and im.id=idc.id_installment "
            + "and im.start_date between (select STARTINGDATE from financialyear where now() between STARTINGDATE and ENDINGDATE) "
            + "and  (select ENDINGDATE from financialyear where now() between STARTINGDATE and ENDINGDATE)";

    current_innerCommonQry1 =
        "0 as arrearGT, 0 as arrearLC, 0 as arrearEC,0 as arrearUPT,0 as arrearPFT,0 as arrearST,"
            + "0 as arrearVLT,0 as arrearPSCT,0 as arrearGTColl,0 as arrearLCColl,0 as arrearECColl,0 as arrearUPTColl,"
            + "0 as arrearPFTColl,0 as arrearSTColl, 0 as arrearVLTColl,0 as arrearPSCTColl,"
            + "sum(GeneralTax) as curGT, sum(LibCessTax) as curLC, sum(EduCessTax) as curEC,"
            + "sum(UnauthPenaltyTax) as curUPT,sum(PenaltyFinesTax) as curPFT,sum(SewTax) as curST,"
            + "sum(VacantLandTax) as curVLT,sum(PubSerChrgTax) as curPSCT,sum(GeneralTaxColl) as curGTColl, "
            + "sum(LibCessTaxColl) as curLCColl, sum(EduCessTaxColl) as curECColl,sum(UnauthPenaltyTaxColl) as curUPTColl,"
            + "sum(PenaltyFinesTaxColl) as curPFTColl,sum(SewTaxColl) as curSTColl,"
            + "sum(VacantLandTaxColl) as curVLTColl,sum(PubSerChrgTaxColl) as curPSCTColl from (";

    // Final query that retreives both Arrear and Current details from the other two inner queries
    finalCommonQry =
        "cast(sum(arrearGT) AS numeric) as \"dmnd_arrearPT\", cast(sum(arrearLC)  AS numeric) as \"dmnd_arrearLC\", cast(sum(arrearEC) AS numeric) as \"dmnd_arrearEC\","
            + "cast(sum(arrearUPT) AS numeric) as \"dmnd_arrearUPT\",cast(sum(arrearPFT) AS numeric) as \"dmnd_arrearPFT\",cast(sum(arrearST) AS numeric) as \"dmnd_arrearST\","
            + "cast(sum(arrearVLT) AS numeric) as \"dmnd_arrearVLT\",cast(sum(arrearPSCT) AS numeric) as \"dmnd_arrearPSCT\",cast(SUM(arrearGTColl) AS numeric)  AS \"clctn_arrearPT\", "
            + "cast(sum(arrearLCColl) AS numeric) as \"clctn_arrearLC\", cast(sum(arrearECColl) AS numeric) as \"clctn_arrearEC\",cast(sum(arrearUPTColl) AS numeric) as \"clctn_arrearUPT\","
            + "cast(sum(arrearPFTColl) AS numeric) as \"clctn_arrearPFT\",cast(sum(arrearSTColl) AS numeric) as \"clctn_arrearST\","
            + "cast(sum(arrearVLTColl) AS numeric) as \"clctn_arrearVLT\",cast(sum(arrearPSCTColl) AS numeric) as \"clctn_arrearPSCT\","
            + "cast(sum(curGT) AS numeric) as \"dmnd_currentPT\", cast(sum(curLC) AS numeric) as \"dmnd_currentLC\", cast(sum(curEC) AS numeric) as \"dmnd_currentEC\","
            + "cast(sum(curUPT) AS numeric) as \"dmnd_currentUPT\",cast(sum(curUPT) AS numeric) as \"dmnd_currentPFT\",cast(sum(curST) AS numeric) as \"dmnd_currentST\","
            + "cast(sum(curVLT) AS numeric) as \"dmnd_currentVLT\",CAST(sum(curPSCT) AS numeric) as \"dmnd_currentPSCT\",CAST(sum(curGTColl) AS numeric) as \"clctn_currentPT\", "
            + "cast(sum(curLCColl) AS numeric) as \"clctn_currentLC\", cast(sum(curECColl) AS numeric) as \"clctn_currentEC\",cast(sum(curUPTColl) AS numeric) as \"clctn_currentUPT\","
            + "cast(sum(curPFTColl) AS numeric) as \"clctn_currentPFT\",cast(sum(curSTColl) AS numeric) as \"clctn_currentST\","
            + "cast(sum(curVLTColl) AS numeric) as \"clctn_currentVLT\",cast(sum(curPSCTColl) AS numeric) as \"clctn_currentPSCT\" from (";

    // Conditions to Retrieve data based on selected boundary types
    if (!mode.equalsIgnoreCase(PROPERTY)) {
      finalSelectQry =
          "select cast(id as integer) as \"boundaryId\",boundary.name as \"boundaryName\", ";
      finalGrpQry = " group by boundary.id,boundary.name order by boundary.name";
      finalFrmQry = " )as dcbinfo,eg_boundary boundary ";
    }
    if (mode.equalsIgnoreCase(ZONEWISE)) {
      innerSelectQry0 = "select distinct pi.zoneid as zone,";
      innerSelectQry1 = "select zone as zone,";
      arrearGroupBy = ") as arrear  group by zone ";
      collGroupBy = ") as collection  group by zone ";
      if (param != 0) whereQry = " and pi.zoneid = " + param;
      finalWhereQry = " where dcbinfo.zone=boundary.id ";
    } else if (mode.equalsIgnoreCase(WARDWISE)) {
      innerSelectQry0 = "select distinct pi.wardid as ward,";
      innerSelectQry1 = "select ward as ward,";
      arrearGroupBy = ") as arrear group by ward ";
      collGroupBy = ") as collection  group by ward ";
      whereQry = " and pi.zoneid = " + param;
      finalWhereQry = " where dcbinfo.ward=boundary.id ";
    } else if (mode.equalsIgnoreCase(BLOCKWISE)) {
      innerSelectQry0 = "select distinct pi.blockid as block,";
      innerSelectQry1 = "select block as block,";
      arrearGroupBy = ") as arrear group by block ";
      collGroupBy = ") as collection  group by block ";
      whereQry = " and pi.wardid = " + param;
      finalWhereQry = " where dcbinfo.block=boundary.id ";
    } else if (mode.equalsIgnoreCase(PROPERTY)) {
      innerSelectQry0 = "select distinct pi.upicno as upicno,";
      innerSelectQry1 = "select upicno as upicno,";
      arrearGroupBy = ") as arrear group by upicno ";
      collGroupBy = ") as collection  group by upicno ";
      whereQry = " and pi.blockid = " + param;
      finalSelectQry = "select COALESCE(upicno,null,'',upicno) as \"assessmentNo\", ";
      finalFrmQry = " )as dcbinfo ";
      finalWhereQry = "";
      finalGrpQry = " group by dcbinfo.upicno order by dcbinfo.upicno ";
    }
    //  Arrear Demand query union Current Demand query
    unionQueryStr
        .append(innerSelectQry1)
        .append(arrear_innerCommonQry1)
        .append(innerSelectQry0)
        .append(arrear_innerCommonQry0)
        .append(whereQry)
        .append(arrearGroupBy)
        .append(" union ")
        .append(innerSelectQry1)
        .append(current_innerCommonQry1)
        .append(innerSelectQry0)
        .append(current_innerCommonQry0)
        .append(whereQry)
        .append(collGroupBy);
    // Final Query : Retrieves arrear and current for the selected boundary.
    queryStr
        .append(finalSelectQry)
        .append(finalCommonQry)
        .append(unionQueryStr)
        .append(finalFrmQry)
        .append(finalWhereQry)
        .append(finalGrpQry);

    SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString());
    query.setResultTransformer(new AliasToBeanResultTransformer(DCBReportResult.class));
    return query;
  }
コード例 #10
0
ファイル: BlogDAO.java プロジェクト: subaochen/seam
  private List<BlogEntryCount> countBlogEntries(
      WikiDirectory startDir,
      WikiDocument ignoreDoc,
      final boolean projectYear,
      final boolean projectMonth,
      final boolean projectDay,
      Integer limitYear,
      Integer limitMonth,
      Integer limitDay,
      String tag) {

    // Sanity input check
    if (projectDay && (!projectMonth || !projectYear))
      throw new IllegalArgumentException("Can't project on day without months or year");
    if (projectMonth && !projectYear)
      throw new IllegalArgumentException("Can't project on month without year");

    StringBuilder queryString = new StringBuilder();

    queryString.append("select count(doc.NODE_ID) as NUM_OF_ENTRIES").append(" ");
    if (projectYear) queryString.append(", ").append("year(doc2.CREATED_ON) as YEAR");
    if (projectMonth) queryString.append(", ").append("month(doc2.CREATED_ON) as MONTH");
    if (projectDay) queryString.append(", ").append("day(doc2.CREATED_ON) as DAY");
    queryString.append(" ");

    queryString.append(getblogEntryFromClause(tag));
    queryString.append(getBlogEntryWhereClause(ignoreDoc, limitYear, limitMonth, limitDay, tag));

    if (projectYear || projectMonth || projectDay) queryString.append("group by").append(" ");
    if (projectYear) queryString.append("year(doc2.CREATED_ON)");
    if (projectMonth) queryString.append(", month(doc2.CREATED_ON)");
    if (projectDay) queryString.append(", day(doc2.CREATED_ON)");

    if (projectYear || projectMonth || projectDay) queryString.append("order by").append(" ");
    if (projectYear) queryString.append("YEAR desc");
    if (projectMonth) queryString.append(", MONTH desc");
    if (projectDay) queryString.append(", DAY desc");

    SQLQuery query = getSession().createSQLQuery(queryString.toString());

    bindBlogEntryWhereClause(query, startDir, ignoreDoc, limitYear, limitMonth, limitDay, tag);

    query.setComment("Finding blogEntry counts");
    query.addScalar("NUM_OF_ENTRIES", Hibernate.LONG);
    if (projectYear) query.addScalar("YEAR", Hibernate.INTEGER);
    if (projectMonth) query.addScalar("MONTH", Hibernate.INTEGER);
    if (projectDay) query.addScalar("DAY", Hibernate.INTEGER);

    query.setResultTransformer(
        new ResultTransformer() {
          public Object transformTuple(Object[] result, String[] aliases) {
            BlogEntryCount beCount = new BlogEntryCount();
            beCount.setNumOfEntries((Long) result[0]);
            if (projectYear) beCount.setYear((Integer) result[1]);
            if (projectMonth) beCount.setMonth((Integer) result[2]);
            if (projectDay) beCount.setDay((Integer) result[3]);
            return beCount;
          }

          public List transformList(List list) {
            return list;
          }
        });

    return (List<BlogEntryCount>) query.list();
  }
コード例 #11
0
ファイル: BlogDAO.java プロジェクト: subaochen/seam
  public List<BlogEntry> findBlogEntriesInDirectory(
      WikiDirectory startDir,
      WikiDocument ignoreDoc,
      Pager pager,
      Integer year,
      Integer month,
      Integer day,
      String tag,
      boolean countComments) {

    final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();

    StringBuilder queryString = new StringBuilder();
    queryString.append("select").append(" ");
    for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
      queryString.append(getWikiDocumentSQLColumnNames()[i]);
      if (i != getWikiDocumentSQLColumnNames().length - 1) queryString.append(", ");
    }
    queryString.append(", '0' as COMMENT_COUNT").append(" ");
    queryString.append(getblogEntryFromClause(tag));
    queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));

    queryString.append(" ");
    queryString.append("order by doc2.CREATED_ON desc");

    SQLQuery query = getSession().createSQLQuery(queryString.toString());

    bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);

    query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
    query.addEntity(WikiDocument.class);
    query.addScalar("COMMENT_COUNT", Hibernate.LONG);
    query.setFirstResult(pager.getQueryFirstResult());
    query.setMaxResults(pager.getQueryMaxResults());

    query.setResultTransformer(
        new ResultTransformer() {
          public Object transformTuple(Object[] result, String[] aliases) {
            BlogEntry be = new BlogEntry();
            be.setEntryDocument((WikiDocument) result[0]);
            blogEntryMap.put(
                be.getEntryDocument().getId(),
                be); // Put in map so we can attach comment count later
            return be;
          }

          public List transformList(List list) {
            return list;
          }
        });

    List<BlogEntry> result = (List<BlogEntry>) query.list();

    if (countComments && result.size() > 0) {
      // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some
      // DBs...
      StringBuilder commentQueryString = new StringBuilder();
      commentQueryString
          .append("select doc.NODE_ID as DOC_ID, count(c1.NODE_ID) as COMMENT_COUNT")
          .append(" ");
      commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
      commentQueryString
          .append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID")
          .append(" ");
      commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
      commentQueryString.append("group by doc.NODE_ID");

      SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
      commentQuery.setComment("Finding comment count for blog entries");
      commentQuery.addScalar("DOC_ID");
      commentQuery.addScalar("COMMENT_COUNT");
      commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());

      commentQuery.setResultTransformer(
          new ResultTransformer() {
            public Object transformTuple(Object[] result, String[] aliases) {
              BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue());
              be.setCommentCount(((BigInteger) result[1]).longValue());
              return null;
            }

            public List transformList(List list) {
              return list;
            }
          });
      commentQuery.list();
    }

    return result;
  }
コード例 #12
0
  protected void cummulativeLoassMakt() {
    db_connections dbconnection = new db_connections();

    SessionFactory SFact = new Configuration().configure().buildSessionFactory();
    Session session = SFact.openSession();
    org.hibernate.Transaction tx = session.beginTransaction();

    for (int k = 2004; k <= 2014; k++) {

      String all_drawdown =
          "SELECT x.PERMNO AS permno,x.YRMO AS yrmo,x.CAPM_resid AS drawdownValue,y.CAPM_resid_date AS drawdownDate,x.value1 AS marketCapitalization,y.returnValue FROM ( SELECT A.PERMNO, A.YRMO, A.CAPM_resid, B.value1 FROM ( SELECT * FROM capm_drawdowns_results WHERE YRMO LIKE '"
              + k
              + "%' AND HORIZON = 1) AS A INNER JOIN ( SELECT permno, yrmo, value1 FROM caaf_marketcapitalization WHERE yrmo LIKE '"
              + k
              + "%') AS B ON A.PERMNO = B.permno ) AS x INNER JOIN (SELECT K.PERMNO_date,K.YRMO_date,K.CAPM_resid_date,L.value1 AS returnValue FROM (SELECT PERMNO_date,YRMO_date,CAPM_resid_date FROM capm_drawdowns_date WHERE YRMO_date LIKE '"
              + k
              + "%' AND HORIZON = 1 ) AS K INNER JOIN (SELECT permno,yrmo,value1 FROM caaf_returns WHERE yrmo LIKE '"
              + k
              + "%') AS L ON K.PERMNO_date=L.permno AND K.YRMO_date=L.yrmo) AS y ON y.PERMNO_date = x.PERMNO AND y.YRMO_date = x.yrmo ORDER BY y.CAPM_resid_date";

      SQLQuery q = session.createSQLQuery(all_drawdown);
      q.setResultTransformer(Transformers.aliasToBean(Drawdown.class));

      @SuppressWarnings("unchecked")
      List<Drawdown> results = q.list();
      int count = 0;
      int emptyCount = 0;
      System.out.println("Results.size is :" + results.size());
      String multiArry[][] = new String[results.size()][2];

      // multiple drawdownValue and marketCapitalization
      for (Iterator<Drawdown> iterator = results.iterator(); iterator.hasNext(); ) {

        Drawdown data = (Drawdown) iterator.next();

        if (!data.getDrawdownDate().isEmpty()) {

          multiArry[count][0] = data.getDrawdownDate();
          BigDecimal marketcapitalization = new BigDecimal(data.getMarketCapitalization());
          BigDecimal lossmarket = data.getDrawdownValue().multiply(marketcapitalization);
          multiArry[count][1] = String.valueOf(lossmarket);
          count = count + 1;
        } else {
          emptyCount = emptyCount + 1;
        }
      }

      String secondSortArray[][] = new String[365][2];
      SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
      int countforSecondArray = 0;
      BigDecimal AdditionofValue = new BigDecimal(0);
      BigDecimal zero = BigDecimal.ZERO;

      for (int i = 0; i < multiArry.length - emptyCount - 1; i++) {

        if (multiArry[i][0].equals(multiArry[i + 1][0])) {
          AdditionofValue = ((new BigDecimal(multiArry[i][1])).add(AdditionofValue));
          secondSortArray[countforSecondArray][0] = multiArry[i][0];
          secondSortArray[countforSecondArray][1] = AdditionofValue.toString();
        } else {
          countforSecondArray = countforSecondArray + 1;
          AdditionofValue = zero;
        }
      }

      // ----------------------------index query output---------------------------------------//
      String index =
          "SELECT B.date_withyear AS Index_dates,A.value1 AS Index_values FROM ( SELECT  permno, value1,yrmo FROM caaf_drawdowns WHERE  permno=0 AND yrmo LIKE '"
              + k
              + "%') AS  A  JOIN (SELECT  permno_end,date_withyear,yrmo_end FROM  caaf_drawdownend WHERE permno_end=0 AND yrmo_end LIKE '"
              + k
              + "%') AS  B ON A.permno=B.permno_end AND A.yrmo=B.yrmo_end";
      String arrayYear[] = new String[12];

      try {
        ResultSet set = dbconnection.selectData(index);
        int yearCount = 0;
        while (set.next()) {
          arrayYear[yearCount] = set.getString("Index_dates");
          yearCount = yearCount + 1;
        }

      } catch (SQLException e) {
        e.printStackTrace();
      }

      BigDecimal cummilativeValue = new BigDecimal(0);
      String CumalativeArray[][] = new String[countforSecondArray + 1][2];

      for (int i = 0; i < arrayYear.length; i++) {

        for (int j = 0; j < countforSecondArray + 1; j++) {
          Date d1;
          Date d2;
          Date d3;
          Date d4;
          Date d6;
          try {

            if (i == 0) {
              d1 = (Date) format.parse(arrayYear[i]);
              d2 = (Date) format.parse(arrayYear[i + 1]);
              d3 = (Date) format.parse(secondSortArray[j][0]);
              if (d3.before(d1)) {
                cummilativeValue = new BigDecimal(secondSortArray[j][1]).add(cummilativeValue);
                CumalativeArray[j][0] = secondSortArray[j][0];
                CumalativeArray[j][1] = cummilativeValue.toString();
              } else if (d3.equals(d1) || d3.after(d1) && d3.before(d2)) {
                cummilativeValue = new BigDecimal(secondSortArray[j][1]).add(cummilativeValue);
                CumalativeArray[j][0] = secondSortArray[j][0];
                CumalativeArray[j][1] = cummilativeValue.toString();

              } else {
                cummilativeValue = zero;
              }
            } else if (0 < i && i <= 10) {

              d1 = (Date) format.parse(arrayYear[i]);
              d2 = (Date) format.parse(arrayYear[i + 1]);
              d3 = (Date) format.parse(secondSortArray[j][0]);
              // pwr.println(d3);
              if (d3.equals(d1) || d3.after(d1) && d3.before(d2)) {

                cummilativeValue = new BigDecimal(secondSortArray[j][1]).add(cummilativeValue);
                CumalativeArray[j][0] = secondSortArray[j][0];
                CumalativeArray[j][1] = cummilativeValue.toString();
              } else {
                cummilativeValue = zero;
              }
            } else if (i == 11) {
              d4 = (Date) format.parse(arrayYear[i]);
              d6 = (Date) format.parse(secondSortArray[j][0]);

              if (d6.equals(d4) || d6.after(d4)) {

                cummilativeValue = new BigDecimal(secondSortArray[j][1]).add(cummilativeValue);
                CumalativeArray[j][0] = secondSortArray[j][0];
                CumalativeArray[j][1] = cummilativeValue.toString();
              }
            }

          } catch (ParseException e) {

            e.printStackTrace();
          }
        }
      }

      for (int i = 0; i < CumalativeArray.length; i++) {
        Sys_CLM_CumulativeLMC cummulativeObject = new Sys_CLM_CumulativeLMC();
        cummulativeObject.setDate(CumalativeArray[i][0]);
        cummulativeObject.setValue(new BigDecimal(CumalativeArray[i][1]));
        session.save(cummulativeObject);
      }
    }

    tx.commit();
    session.close();
  }
コード例 #13
0
  public List<DmFolderNode> getSharedChildFolderNodeList(
      String accountSharedId, String folderIdParent) {
    //        DmAccount permittedAccount = null;
    //        if (accountSharedId != null) {
    //            permittedAccount = new DmAccount();
    //            permittedAccount.setId(accountSharedId);
    //        }
    //
    DmFolder parentFolder = null;
    if (folderIdParent != null) {
      parentFolder = findById(folderIdParent);
      //            parentFolder = new DmFolder();
      //            parentFolder.setId(folderIdParent);
    }

    List<DmFolderNode> folderNodeList = null;

    //        DetachedCriteria subquery = DetachedCriteria.forClass(DmFolder.class);
    //        subquery.setProjection(Projections.count("id"));
    //        subquery.add(Restrictions.eq("account", permittedAccount));
    //        disjunction.add(Subqueries.propertyNotIn("folderParent.parent", subquery));

    //        Conjunction conjunction = Restrictions.conjunction();
    //        conjunction.add(Restrictions.ge("permissionType",
    // ApplicationConstants.FOLDER_PERMISSION_WRITER));
    //        conjunction.add(Restrictions.eq("account", permittedAccount));
    //        crit.add(conjunction);

    //        DetachedCriteria subquery = DetachedCriteria.forClass(DmFolderPermission.class);
    //        subquery.setProjection(Projections.property("folder"));
    //        subquery.add(conjunction);

    //        Disjunction disjunction = Restrictions.disjunction();
    //        disjunction.add(Restrictions.isNull("folderChildren.permissionList"));
    //        disjunction.add(Subqueries.propertyIn("folderInfo.childList", subquery));

    //        disjunction.add(Restrictions.eq("childPermission.account", permittedAccount));
    //        disjunction.add(Subqueries.propertyIn("folderChildren", subquery));

    //        Criteria crit = getSession().createCriteria(DmFolderPermission.class, "permission");
    //        crit.createAlias("folder", "folderInfo");
    //
    //        crit.add(Restrictions.eq("account", permittedAccount));
    //        crit.add(Restrictions.ge("permission.permissionType",
    // ApplicationConstants.FOLDER_PERMISSION_WRITER));

    //        crit.createAlias("folderInfo.childList", "folderChildren",
    // CriteriaSpecification.LEFT_JOIN);
    //        crit.createCriteria("folderInfo.childList", "folderChildren",
    // CriteriaSpecification.LEFT_JOIN);

    //        crit.createAlias("folderChildren.permissionList", "childPermission",
    // CriteriaSpecification.LEFT_JOIN);
    //        crit.add(disjunction);

    // select fp.df_name, count(fc.df_id)
    // from dm_folder fp
    // left join dm_folder fc
    // on fp.df_id = fc.df_parent_id
    // -- where
    // -- fp.df_id = '517243bd-3ea5-489a-9b87-feeabd8c4f4c'
    // group by fp.df_name

    //        crit.setProjection(
    //            Projections.projectionList()
    //                   .add( Projections.property("folderInfo.id"), "id" )
    //                   .add( Projections.property("folderInfo.name"), "name" )
    //                   .add( Projections.count("folderChildren.id"), "numChildren" )
    ////                   .add( Projections.property("folderChildren.id"), "numChildren" )
    //                   .add( Projections.property("folderInfo.owner.id"), "ownerAccountId" )
    //                   .add( Projections.property("permissionType"), "permissionType" )
    //                   .add( Projections.groupProperty("id") )
    //        );

    //        crit.add(Restrictions.eq("folderInfo.parent", parentFolder));

    //        crit.addOrder(Order.asc("folderInfo.name"));

    //        crit.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));

    //        folderNodeList = crit.list();

    if (parentFolder != null) {
      String sqlSub =
          "SELECT"
              + " count(*)"
              + " from dm_folder fChild"
              + " left join dm_folder_permission fChildPerm"
              + " on fChild.df_id = fChildPerm.df_id"
              + " where fChild.df_parent_id = f.df_id"
              + " and fChildPerm.da_id = :accountSharedId"
              + " and fChildPerm.da_id <> :accountOwnerId"
          //            + " and fChildPerm.dfp_permission_type >= " +
          // ApplicationConstants.FOLDER_PERMISSION_WRITER
          ;

      String sql =
          "SELECT"
              + " f.df_id as \"id\", f.df_name as \"name\", ("
              + sqlSub
              + ") as \"numChildren\""
              + ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
              + " FROM dm_folder_permission fp"
              + " LEFT JOIN dm_folder f"
              + " on fp.df_id = f.df_id"
              + " WHERE fp.da_id = :accountSharedId"
              + " AND f.df_parent_id = :folderIdParent"
              + " ORDER BY f.df_name asc";

      SQLQuery query = getSession().createSQLQuery(sql);

      query.setString("accountSharedId", accountSharedId);
      query.setString("folderIdParent", folderIdParent);
      query.setString("accountOwnerId", parentFolder.getOwner().getId());

      query.addScalar("id", Hibernate.STRING);
      query.addScalar("name", Hibernate.STRING);
      query.addScalar("numChildren", Hibernate.INTEGER);
      query.addScalar("ownerAccountId", Hibernate.STRING);
      query.addScalar("permissionType", Hibernate.INTEGER);

      //                   .add( Projections.property("folderInfo.id"), "id" )
      //                   .add( Projections.property("folderInfo.name"), "name" )
      //                   .add( Projections.count("folderChildren.id"), "numChildren" )
      ////                   .add( Projections.property("folderChildren.id"), "numChildren" )
      //                   .add( Projections.property("folderInfo.owner.id"), "ownerAccountId" )
      //                   .add( Projections.property("permissionType"), "permissionType" )

      query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));

      folderNodeList = query.list();
    }
    return folderNodeList;
  }
コード例 #14
0
  public List<DmFolderNode> getSharedFirstLevelFolderNodeList(
      String accountSharedId, String accountOwnerId) {
    List<DmFolderNode> folderNodeList = null;

    String sqlSub =
        "SELECT"
            + " count(*)"
            + " from dm_folder fChild"
            + " left join dm_folder_permission fpChild"
            + " on fChild.df_id = fpChild.df_id"
            + " where fChild.df_parent_id = f.df_id"
            + " and fpChild.da_id = :accountSharedId"
            + " and fpChild.da_id <> :accountOwnerId"
        //            + " and fpChild.dfp_permission_type >= " +
        // ApplicationConstants.FOLDER_PERMISSION_MANAGER
        ;

    //        String sqlSub2 = "''";
    String sqlSub2 =
        "SELECT"
            + " fpPrnt2.df_id"
            + " FROM dm_folder_permission fpPrnt2"
            + " WHERE fpPrnt2.da_id = :accountSharedId"
            + " and fpPrnt2.da_id <> :accountOwnerId"
        //            + " and fpPrnt2.dfp_permission_type >= " +
        // ApplicationConstants.FOLDER_PERMISSION_WRITER
        ;

    String sql =
        "SELECT"
            + " f.df_id as \"id\", f.df_name as \"name\", ("
            + sqlSub
            + ") as \"numChildren\""
            + ", f.da_id_owner as \"ownerAccountId\", fp.dfp_permission_type as \"permissionType\""
            + " FROM dm_folder_permission fp"
            + " LEFT JOIN dm_folder f"
            + " on fp.df_id = f.df_id"
            + " LEFT JOIN dm_folder fPrnt"
            + " on f.df_parent_id = fPrnt.df_id"
            //            + " LEFT JOIN dm_folder_permission fpPrnt"
            //            + " on fPrnt.df_id = fpPrnt.df_id"
            + " WHERE fp.da_id = :accountSharedId"
            //            + " AND fp.dfp_permission_type >= " +
            // ApplicationConstants.FOLDER_PERMISSION_WRITER
            + " AND fp.da_id <> :accountOwnerId"
            + " AND f.da_id_owner = :accountOwnerId"
            //            + " AND ((fPrnt.df_id IS NULL) OR (fPrnt.df_id NOT IN (" + sqlSub2 + ")))"
            + " AND ((fPrnt.df_id IS NULL) OR (fPrnt.df_id NOT IN ("
            + sqlSub2
            + ")))"
            + " ORDER BY f.df_name asc";

    SQLQuery query = getSession().createSQLQuery(sql);

    query.setString("accountSharedId", accountSharedId);
    query.setString("accountOwnerId", accountOwnerId);

    query.addScalar("id", Hibernate.STRING);
    query.addScalar("name", Hibernate.STRING);
    query.addScalar("numChildren", Hibernate.INTEGER);
    query.addScalar("ownerAccountId", Hibernate.STRING);
    query.addScalar("permissionType", Hibernate.INTEGER);

    //                   .add( Projections.property("folderInfo.id"), "id" )
    //                   .add( Projections.property("folderInfo.name"), "name" )
    //                   .add( Projections.count("folderChildren.id"), "numChildren" )
    ////                   .add( Projections.property("folderChildren.id"), "numChildren" )
    //                   .add( Projections.property("folderInfo.owner.id"), "ownerAccountId" )
    //                   .add( Projections.property("permissionType"), "permissionType" )

    query.setResultTransformer(Transformers.aliasToBean(DmFolderNode.class));

    folderNodeList = query.list();

    return folderNodeList;
  }