/**
* 设置查询结果类型
*
* @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);
}
}
}
@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();
}
@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 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();
}
}
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;
}
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();
}
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;
}
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;
}
/**
* @ 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;
}
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();
}
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;
}
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();
}
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;
}
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;
}
