GAT專案新需求：保險管理增加查詢統計-傳日期引數問題

ZHOU_VIP發表於2017-04-20

需求：不分頁，預設合計今年的簽單保費和車船費


1.CarSecureController

2.查詢條件


3.介面


4.介面的實現


5.Dao介面


6.Dao實現


查詢條件：generateConditionTotal


private void generateConditionTotal(CarSecureListReqFrist req, HibernateParams hParams) throws Exception{

    StringBuilder sb = new StringBuilder();
    
    Integer querySysId = 0;
    Integer queryDeptId = 0;

    if(Util.isEmpty(req.getAppSysId()) || Util.isEmpty(req.getAppDeptId())){
        //未傳值 查詢當前登入人的機構部門資訊
        UserSysDeptRes userSysDeptRes = Common.getLoginInfo().getUserSysDept();
        querySysId = userSysDeptRes.getAppSysId();
        queryDeptId = userSysDeptRes.getAppDeptId();
    }else{
        //傳值 查詢輸入機構部門資訊
        querySysId = req.getAppSysId();
        queryDeptId = req.getAppDeptId();
    }
    // 包含下級
    OrgDeptSearchFilter helper = new OrgDeptSearchFilter();
    sb.append(" and((po.appsysid,po.appdeptid) in(");
    sb.append(helper.getSubDeptSql(querySysId, queryDeptId, hParams));
    sb.append("))");
    
    //車牌號碼
    if(!Util.isEmpty(req.getCarHostNO())){
        sb.append(" and t.HOST_NO like ? ");
        hParams.addParamObj(likeValue(req.getCarHostNO()));
    }
    
    //查詢範圍
    if(Util.isEmpty(req.getQueryQbBeginTime()) && Util.isEmpty(req.getQueryZbEndTime()) &&  Util.isEmpty(req.getQueryZbBeginTime()) && Util.isEmpty(req.getQueryZbEndTime())){
        //什麼都不填，預設查詢當年的
        sb.append(" and t.SECURE_START_TIME >= (select trunc(sysdate,'yyyy') FROM DUAL) ");
        sb.append(" and t.SECURE_START_TIME <= (select add_months(trunc(sysdate,'yyyy'),12)-1 from dual) ");
    }
    if(!Util.isEmpty(req.getQueryQbBeginTime())){
        sb.append(" and t.SECURE_START_TIME >= ? ");
        hParams.addParamObj(req.getQueryQbBeginTime());
    }
    
    if(!Util.isEmpty(req.getQueryQbEndTime())){
        sb.append(" and t.SECURE_START_TIME <= ? ");
        hParams.addParamObj(req.getQueryQbEndTime());
    }
    
    if(!Util.isEmpty(req.getQueryZbBeginTime())){
        sb.append(" and t.SECURE_END_TIME >= ? ");
        hParams.addParamObj(req.getQueryZbBeginTime());
    }
    
    if(!Util.isEmpty(req.getQueryZbEndTime())){
        sb.append(" and t.SECURE_END_TIME <= ? ");
        hParams.addParamObj(req.getQueryZbEndTime());
    }
    
    hParams.addSqlStrBuffer(sb.toString());
}
測試OK：


SQL語句：



select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.HOST_VIN as hostVin,
       p.ENGINE_ID as engineId,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.SECURE_TAX as secureTax,
       t.SECURE_REMARK as secureRemark
  from CAR_SECURE t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid
   and ((po.appsysid, po.appdeptid) in
       (select appsysid, appdeptid from table(get_subdept(-1, 2000, 0))))
   and t.HOST_NO like '%人a1002%'
   and t.SECURE_START_TIME >= to_date('2017-1-1','yyyy-mm-dd')
   and t.SECURE_START_TIME <= to_date('2017-2-25','yyyy-mm-dd')
   and t.SECURE_END_TIME >= to_date('2017-3-20','yyyy-mm-dd')
   and t.SECURE_END_TIME <= to_date('2017-4-25','yyyy-mm-dd')
 order by t.UPDATE_TIME desc
注意：

使用註解把接收到的日期String型別"queryQbBeginTime":"2017-1-1"轉成Date型別




所以sql語句要用to_date轉：


select t.RECORD_ID as recordId,
       t.HOST_ID as hostId,
       t.HOST_NO as carHostNO,
       t.SECURE_PAY as securePay,
       p.HOST_VIN as hostVin,
       p.ENGINE_ID as engineId,
       po.APPSYSID as appSysId,
       po.APPDEPTID as appDeptId,
       t.SECURE_NO as secureNo,
       to_char(t.SECURE_START_TIME, 'yyyy-MM-dd') as secureStartTime,
       to_char(t.SECURE_END_TIME, 'yyyy-MM-dd') as secureEndTime,
       t.SECURE_HANDLE as secureHandle,
       t.SECURE_TAX as secureTax,
       t.SECURE_REMARK as secureRemark
  from CAR_SECURE t, POSITION_HOST_INFO_EXTEND p, POSITION_HOST_INFO po
 where t.host_id = p.hostid
   and t.host_id = po.hostid
   and ((po.appsysid, po.appdeptid) in
       (select appsysid, appdeptid from table(get_subdept(-1, 2000, 0))))
   and t.HOST_NO like '%人a1002%'
   and t.SECURE_START_TIME >= to_date('2017-1-1','yyyy-mm-dd')
   and t.SECURE_START_TIME <= to_date('2017-2-25','yyyy-mm-dd')
   and t.SECURE_END_TIME >= to_date('2017-3-20','yyyy-mm-dd')
   and t.SECURE_END_TIME <= to_date('2017-4-25','yyyy-mm-dd')
 order by t.UPDATE_TIME desc
-------------------------------------------------------------------------------------------------------------------
如果用String接收：


則：


-------------------------------------------------------------------------------------------------------------------

如果用String接收：


則：


-------------------------------------------------------------------------------------------------------------------

看到其他模組還有這種寫法的：





相關文章