Date与String相互转化 （含倒推时间段）

package com.ds.hfcp.dao.impl;


import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;


import com.ds.hfcp.dao.ITrivialAnalysisDao;
import com.ds.hfcp.exception.PoliceSituationException;
import com.ds.hfcp.vo.HandleReasonVO;
import com.ds.hfcp.vo.HandleTrendVO;
import com.ds.hfcp.vo.HandleUnitVO;


/**
 * 杂项分析
 * @author OP
 *
 */
@Repository
public class TrivialAnalysisDaoImpl implements ITrivialAnalysisDao {

@PersistenceContext
private EntityManager em;


private final Logger logger = LoggerFactory.getLogger(getClass());



/**
* 杂项--单位--前一段顺推时间段
*/
public List<HandleUnitVO> findHandleUnitPublish(HandleUnitVO handleUnitVO) throws PoliceSituationException {
logger.error("=== TrivialAnalysisDaoImpl === findHandleUnitPublish === begin");
List<HandleUnitVO> list = new ArrayList<HandleUnitVO>();


try {

String orgTwo = handleUnitVO.getOrgTwo();
String startTime = handleUnitVO.getStartTime();
String endTime = handleUnitVO.getEndTime();
// String statisticsWay = handleUnitVO.getStatisticsWay();

Date dateStart = new SimpleDateFormat("yyyy-MM-dd").parse(startTime);
Date dateEnd = new SimpleDateFormat("yyyy-MM-dd").parse(endTime);
//相隔的天數   /(24*60*60*1000)
long day=(dateEnd.getTime()-dateStart.getTime());
// 往前顺推的开始时间
String earlyStartDate = new SimpleDateFormat("yyyy-MM-dd").format(dateStart.getTime()-day);

String sql = null;

if ("全部".equals(orgTwo)) {

// sql = "  select DISTINCT tt.jc,count(1) "
//         + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
//         + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2  "
//         + " and tt.sjdw=4  "
//         + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
//         + " group by tt.jc  ";

// sql = "  SELECT   temp.jc,sum(temp.bh) "
// + " FROM (select DISTINCT  t.sbsj,tt.bh,TT.SJDW,tt.jc,count(1)   "
// + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
// + " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and  TT.SJDW=4  "
// + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')   "
// + " group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc ) temp  GROUP BY temp.jc  ";


sql = " select DISTINCT tt.jc,sum(tt.bh) "
        + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
        + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue "
        + " and tt.sjdw=4  "
        + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
        + " group by tt.jc ";



}else {

// sql = "  select DISTINCT tt.jc,count(1) "
//         + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
//         + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue  and ttt.dicvalue<>2  "
//         + " and tt.sjdw=4  "
//         + " and tt.jc='"+orgTwo+"' "
//         + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
//         + " group by tt.jc  ";

// sql = "  SELECT   temp.jc,sum(temp.bh) "
// + " FROM (select DISTINCT  t.sbsj,tt.bh,TT.SJDW,tt.jc,count(1)   "
// + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
// + " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and  TT.SJDW=4  "
// + " and tt.jc='"+orgTwo+"' "
// + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')   "
// + " group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc ) temp  GROUP BY temp.jc  ";


sql = " select DISTINCT tt.jc,sum(tt.bh) "
        + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
        + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue "
        + " and tt.sjdw=4  "
        + " and tt.jc='"+orgTwo+"' "
        + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
        + " group by tt.jc ";




}


Query query = em.createNativeQuery(sql);

@SuppressWarnings("unchecked")
List<Object> objectList = query.getResultList();


if (null != objectList && 0 < objectList.size()) {
for (int i = 0; i < objectList.size(); i++) {
Object[] obj = (Object[]) objectList.get(i);

HandleUnitVO vo = new HandleUnitVO();

if (null != obj[0]) { // 单位简称
vo.setJc(obj[0].toString().trim());
} else {
vo.setJc("");
}
if (null != obj[1]) { // 案件总数
vo.setCaseNum((BigDecimal) obj[1]);
} else {
vo.setCaseNum(new BigDecimal(0));
}

list.add(vo);

}
} 

} catch (Exception e) {
throw new PoliceSituationException("方法执行错误：findHandleUnitPublish方法执行失败");
}
logger.error("=== TrivialAnalysisDaoImpl === findHandleUnitPublish === end");
return list;
}





/**
* 杂项--单位
*/
public List<HandleUnitVO> findHandleUnit(HandleUnitVO handleUnitVO) throws PoliceSituationException {
logger.error("=== TrivialAnalysisDaoImpl === findHandleUnit === begin");
List<HandleUnitVO> list = new ArrayList<HandleUnitVO>();


try {

String orgTwo = handleUnitVO.getOrgTwo();
String startTime = handleUnitVO.getStartTime();
String endTime = handleUnitVO.getEndTime();

String sql = null;

if ("全部".equals(orgTwo)) {

// sql = "  select DISTINCT tt.jc,count(1) "
//         + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
//         + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2  "
//         + " and tt.sjdw=4  "
//         + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
//         + " group by tt.jc  ";

// sql = "  SELECT   temp.jc,sum(temp.bh) "
// + " FROM (select DISTINCT  t.sbsj,tt.bh,TT.SJDW,tt.jc,count(1)   "
// + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
// + " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and  TT.SJDW=4  "
// + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')   "
// + " group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc ) temp  GROUP BY temp.jc  ";


sql = " select DISTINCT tt.jc,sum(tt.bh) "
        + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
        + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue "
        + " and tt.sjdw=4  "
        + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
        + " group by tt.jc ";


}else {


// sql = "  select DISTINCT tt.jc,count(1) "
//         + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
//         + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2  "
//         + " and tt.sjdw=4  "
//         + " and tt.jc='"+orgTwo+"' "
//         + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
//         + " group by tt.jc  ";

// sql = "  SELECT   temp.jc,sum(temp.bh) "
// + " FROM (select DISTINCT  t.sbsj,tt.bh,TT.SJDW,tt.jc,count(1)   "
// + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
// + " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and  TT.SJDW=4  "
// + " and tt.jc='"+orgTwo+"' "
// + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')   "
// + " group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc ) temp  GROUP BY temp.jc  ";

sql = " select DISTINCT tt.jc,sum(tt.bh) "
        + " from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
        + " where ttt.ref_typeid=301 and  t.xxbb_type=0 and t.info_type=ttt.dicvalue "
        + " and tt.sjdw=4  "
        + " and tt.jc='"+orgTwo+"' "
        + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
        + " group by tt.jc ";



}


Query query = em.createNativeQuery(sql);

@SuppressWarnings("unchecked")
List<Object> objectList = query.getResultList();


if (null != objectList && 0 < objectList.size()) {
for (int i = 0; i < objectList.size(); i++) {
Object[] obj = (Object[]) objectList.get(i);

HandleUnitVO vo = new HandleUnitVO();

if (null != obj[0]) { // 单位简称
vo.setJc(obj[0].toString().trim());
} else {
vo.setJc("");
}
if (null != obj[1]) { // 案件总数
vo.setCaseNum((BigDecimal) obj[1]);
} else {
vo.setCaseNum(new BigDecimal(0));
}

list.add(vo);

}
} 

} catch (Exception e) {
throw new PoliceSituationException("方法执行错误：findHandleUnit方法执行失败");
}
logger.error("=== TrivialAnalysisDaoImpl === findHandleUnit === end");
return list;
}


/**
* 杂项--趋势--发布时段
*/
@Override
public List<HandleTrendVO> findHandleTrendPublish(HandleTrendVO handleTrendVO) throws PoliceSituationException {
logger.error("=== TrivialAnalysisDaoImpl === findHandleTrendPublish === begin");
List<HandleTrendVO> list = new ArrayList<HandleTrendVO>();


try {

String orgTwo = handleTrendVO.getOrgTwo();
String startTime = handleTrendVO.getStartTime();
String endTime = handleTrendVO.getEndTime();
String statisticsWay = handleTrendVO.getStatisticsWay();

Date dateStart = new SimpleDateFormat("yyyy-MM-dd").parse(startTime);
Date dateEnd = new SimpleDateFormat("yyyy-MM-dd").parse(endTime);
//相隔的天數   /(24*60*60*1000)
long day=(dateEnd.getTime() - dateStart.getTime());
// 往前顺推的开始时间
String earlyStartDate = new SimpleDateFormat("yyyy-MM-dd").format(dateStart.getTime()-day);


String sql = null;

if ("按日分析".equals(statisticsWay)) {
if ("全部".equals(orgTwo)) {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM-dd')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4  and "
        + "  t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}else {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM-dd')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4    and "
+ "  tt.jc='"+orgTwo+"' "
        + " and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}

} else {
//按月分析
if ("全部".equals(orgTwo)) {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4   and "
        + "  t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}else {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select to_char(t.sbsj,'yyyy-MM')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4   and "
+ "  tt.jc='"+orgTwo+"' "
        + "  and t.SBSJ BETWEEN to_date('"+earlyStartDate+"','yyyy-MM-dd') and to_date('"+startTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}


}

Query query = em.createNativeQuery(sql);


@SuppressWarnings("unchecked")
List<Object> objectList = query.getResultList();


if (null != objectList && 0 < objectList.size()) {
for (int i = 0; i < objectList.size(); i++) {
Object[] obj = (Object[]) objectList.get(i);
HandleTrendVO vo = new HandleTrendVO();


if (null != obj[0]) { // 上报时间
vo.setSbsj(obj[0].toString().trim());
} else {
vo.setSbsj("");
}
if (null != obj[1]) { // 案件总数
vo.setCaseNum((BigDecimal) obj[1]);
} else {
vo.setCaseNum(new BigDecimal(0));
}

list.add(vo);
}
} 
} catch (Exception e) {
throw new PoliceSituationException("方法执行错误：findHandleTrendPublish方法执行失败");
}
logger.error("=== TrivialAnalysisDaoImpl === findHandleTrendPublish === end");
return list;
}

/**
* 杂项--趋势--比较时段
*/
@Override
public List<HandleTrendVO> findHandleTrendCompare(HandleTrendVO handleTrendVO) throws PoliceSituationException {
logger.error("=== TrivialAnalysisDaoImpl === findHandleTrendCompare === begin");
List<HandleTrendVO> list = new ArrayList<HandleTrendVO>();


try {

String orgTwo = handleTrendVO.getOrgTwo();
String startTime = handleTrendVO.getStartTime();
String endTime = handleTrendVO.getEndTime();
String statisticsWay = handleTrendVO.getStatisticsWay();

String sql = null;

if ("按日分析".equals(statisticsWay)) {
if ("全部".equals(orgTwo)) {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM-dd')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4  and "
        + "  t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}else {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM-dd')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4   and "
+ "  tt.jc='"+orgTwo+"' "
        + " and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}

} else {
//按月分析
if ("全部".equals(orgTwo)) {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select  to_char(t.sbsj,'yyyy-MM')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4   and "
        + "  t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}else {
sql = " SELECT temp.sbsj,sum(temp.bh) "
+ " FROM "
+ " ( select to_char(t.sbsj,'yyyy-MM')sbsj,tt.bh,TT.SJDW,tt.jc,count(1) "
+ "  from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt  "
+ " where tt.bh=t.sbdwbh(+) and ttt.ref_typeid=301 and t.info_type=ttt.dicvalue and ttt.dicvalue<>2 and  t.xxbb_type=0  and tt.sjdw=4   and "
+ "  tt.jc='"+orgTwo+"' "
        + "  and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by t.sbsj,tt.bh,TT.SJDW,tt.dm,tt.jc  ORDER BY t.sbsj ASC )temp GROUP BY temp.sbsj ";
}


}

Query query = em.createNativeQuery(sql);


@SuppressWarnings("unchecked")
List<Object> objectList = query.getResultList();


if (null != objectList && 0 < objectList.size()) {
for (int i = 0; i < objectList.size(); i++) {
Object[] obj = (Object[]) objectList.get(i);
HandleTrendVO vo = new HandleTrendVO();


if (null != obj[0]) { // 上报时间
vo.setSbsj(obj[0].toString().trim());
} else {
vo.setSbsj("");
}
if (null != obj[1]) { // 案件总数
vo.setCaseNum((BigDecimal) obj[1]);
} else {
vo.setCaseNum(new BigDecimal(0));
}

list.add(vo);
}
} 
} catch (Exception e) {
throw new PoliceSituationException("方法执行错误：findHandleTrendCompare方法执行失败");
}
logger.error("=== TrivialAnalysisDaoImpl === findHandleTrendCompare === end");
return list;
}




/**
* 杂项--原因
*/
@Override
public List<HandleReasonVO> findHandleReason(HandleReasonVO handleReasonVO) throws PoliceSituationException {
logger.error("=== TrivialAnalysisDaoImpl === findHandleReason === begin");
List<HandleReasonVO> list = new ArrayList<HandleReasonVO>();


try {

String orgTwo = handleReasonVO.getOrgTwo();
String startTime = handleReasonVO.getStartTime();
String endTime = handleReasonVO.getEndTime();

String sql = null;

if ("全部".equals(orgTwo)) {
// sql = " select DISTINCT ttt.dicname ,count(1) from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt,T_XXBB_INFO_EXT a where tt.bh=t.sbdwbh(+)  and ttt.ref_typeid=303  and t.info_type=2   and  t.xxbb_type=0  and t.xxbh=a.ref_xxbh and tt.sjdw=4  and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  group by ttt.dicname ";

sql = "  select  DISTINCT ttt.dicname ,sum(TT.bh) "
+ "  from  T_XXBB t, T_BD_DW tt,T_DICTIONARY ttt, T_XXBB_INFO_EXT a  where tt.bh=t.sbdwbh(+)  and ttt.ref_typeid=303  and t.info_type=2  and  t.xxbb_type=0  and t.xxbh=a.ref_xxbh  and tt.sjdw=4  "
+ "  and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by ttt.dicname  ";



}else {

// sql = " select DISTINCT ttt.dicname ,count(1) from  T_XXBB t,T_BD_DW tt, T_DICTIONARY ttt,T_XXBB_INFO_EXT a where tt.bh=t.sbdwbh(+)  and ttt.ref_typeid=303  and t.info_type=2   and  t.xxbb_type=0  and t.xxbh=a.ref_xxbh and tt.sjdw=4 "
// + " and tt.jc='"+orgTwo+"'   "
// + "and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  group by ttt.dicname ";

sql = "  select  DISTINCT ttt.dicname ,sum(TT.bh) "
+ "  from  T_XXBB t, T_BD_DW tt,T_DICTIONARY ttt, T_XXBB_INFO_EXT a  where tt.bh=t.sbdwbh(+)  and ttt.ref_typeid=303  and t.info_type=2  and  t.xxbb_type=0  and t.xxbh=a.ref_xxbh  and tt.sjdw=4  "
+ " and tt.jc='"+orgTwo+"'   "
+ "  and t.SBSJ BETWEEN to_date('"+startTime+"','yyyy-MM-dd') and to_date('"+endTime+"','yyyy-MM-dd')  "
+ "  group by ttt.dicname  ";
}

Query query = em.createNativeQuery(sql);



@SuppressWarnings("unchecked")
List<Object> objectList = query.getResultList();


if (null != objectList && 0 < objectList.size()) {
for (int i = 0; i < objectList.size(); i++) {
Object[] obj = (Object[]) objectList.get(i);
HandleReasonVO vo = new HandleReasonVO();



if (null != obj[0]) { // 事件原因
vo.setReason(obj[0].toString().trim());
} else {
vo.setReason("");
}
if (null != obj[1]) { // 案件总数
vo.setCaseNum((BigDecimal) obj[1]);
} else {
vo.setCaseNum(new BigDecimal(0));
}

list.add(vo);
}
} 
} catch (Exception e) {
throw new PoliceSituationException("方法执行错误：findHandleReason方法执行失败");
}
logger.error("=== TrivialAnalysisDaoImpl === findHandleReason === end");
return list;
}


}