/**
* 统计知识点审核率
*/
@SuppressWarnings({ "unchecked", "rawtypes"})
public List<KnowLedgeStatistics> doStatisticKnowledge(){
//原始sql:SELECT tab2.topname ,tab2.tcount,tab1.kcount FROM ((SELECT tt.topParentName AS topname, COUNT(*) AS tcount FROM lc_knowledge_type AS tt WHERE child=0 GROUP BY tt.topParentName) AS tab2 LEFT JOIN ((SELECT t.topParentName AS topname, COUNT(*) AS kcount FROM lc_knowledge_manager AS m LEFT JOIN lc_knowledge_type AS t ON m.knowledgeType_id = t.id WHERE STATUS>1 GROUP BY t.topParentName) AS tab1 ) ON tab2.topname=tab1.topname) ORDER BY tab1.kcount DESC,tab2.tcount ASC
final String sql = "SELECT tab2.topname ,tab2.tcount,tab1.kcount "
+"FROM ("
+"(SELECT tt.topParentName AS topname, COUNT(*) AS tcount FROM lc_knowledge_type AS tt WHERE child=0 GROUP BY tt.topParentName) AS tab2"
+" LEFT JOIN (SELECT t.topParentName AS topname, COUNT(DISTINCT m.knowledgeType_id) AS kcount FROM lc_knowledge_manager AS m LEFT JOIN lc_knowledge_type AS t ON m.knowledgeType_id = t.id WHERE STATUS>2 AND t.child=0 GROUP BY t.topParentName) AS tab1 "
+" ON tab2.topname=tab1.topname) ";
List<Object[]> lizt = getHibernateTemplate().execute(new HibernateCallback()
{
@Override
public List<Object[]> doInHibernate(Session session)
throws HibernateException, SQLException {
SQLQuery q = session.createSQLQuery(sql);
q.setCacheable(false);
return q.addScalar("topname",StandardBasicTypes.STRING)
.addScalar("tcount",StandardBasicTypes.INTEGER)
.addScalar("kcount",StandardBasicTypes.INTEGER)
.list();
}
});
List<KnowLedgeStatistics> klist = new ArrayList<KnowLedgeStatistics>();
for(Object[] obj :lizt)
{
KnowLedgeStatistics ks = new KnowLedgeStatistics();
ks.setTopParentName(obj[0].toString());
ks.setGroupLeafNodeNum((int)obj[1]);
if(obj[2]!=null)
ks.setCheckedNum((int)obj[2]);
else
ks.setCheckedNum(0);
ks.setRate( String.format("%.2f%%", (((double)ks.getCheckedNum()) / ((double)ks.getGroupLeafNodeNum()) * 100)));
klist.add(ks);
}
return klist;
}