程式碼：用車申請-列表查詢

ZHOU_VIP發表於2017-02-23

/*我的用車申請列表查詢介面*/
@RequestMapping(value = "/apply/myList", method = RequestMethod.POST, produces = "application/json")
@ResponseBody
public BaseResp applyList(@RequestBody VehUseApplyInfoListReq bizReq, HttpServletRequest request){
    //時間格式為yyyy-MM-dd HH:mm ,查詢時需要填充到最大秒
    if(bizReq.getPlanEndTime() != null)//用車計劃結束時間
        bizReq.setPlanEndTime(DateUtils.setSeconds(bizReq.getPlanEndTime(),59));
    return new AbstractRequestExecutor("查詢我的用車申請列表介面", "myVehUseApplyList"){
        protected ListResp queryList(BaseEntity bizReq, HttpServletRequest request) throws Exception{
            return vehUseApplyService.queryApplyInfoList((VehUseApplyInfoListReq) bizReq);
        }
    }.executeQueryList(bizReq, request, true);
}



PagerListResp queryApplyInfoList(VehUseApplyInfoListReq req) throws Exception;



@Override
public PagerListResp queryApplyInfoList(VehUseApplyInfoListReq req) throws Exception{
    //申請人編號
    req.setCreateUserId(Common.getLoginAppUserId());
    
    long totalCount = vehUseApplyInfoDao.queryApplyInfoTotalCount(req);
    PagerListResp resp = new PagerListResp(totalCount, req.getStartRow(), req.getMaxSize());
    List<VehUseApplyInfoListResp> useApplyInfos = Collections.emptyList();
    if(totalCount > 0){
        useApplyInfos = vehUseApplyInfoDao.queryApplyInfoList(req);
        DictionaryUtil.translate(useApplyInfos);
    }
    resp.setList(useApplyInfos);
    return resp;
}



long queryApplyInfoTotalCount(VehUseApplyInfoListReq req) throws Exception;

List<VehUseApplyInfoListResp> queryApplyInfoList(VehUseApplyInfoListReq req) throws Exception;



@Override
public long queryApplyInfoTotalCount(VehUseApplyInfoListReq req) throws Exception {
    HibernateParams hParams = new HibernateParams();
    hParams.addSqlStrBuffer("SELECT COUNT(*) ");
    generateCondition(req, hParams);
    return findCountBySql(hParams.getSqlStr(), hParams.getParamObj());
}


@Override
public List<VehUseApplyInfoListResp> queryApplyInfoList(VehUseApplyInfoListReq req) throws Exception {
	HibernateParams hParams = new HibernateParams();
	hParams.addSqlStrBuffer("SELECT apply_id,veh_user_id,veh_user_name,dest_addr as dest_address,'SDL_RUNTIME.view.myCarApplyManage.myCarApplySP.MainLeaderSpWin' flowNodeKey ");
	hParams.addSqlStrBuffer(",veh_use_org_dept_name,apply_status,decode(apply_status,5,0,9,1,11,1) as processStatus");
	hParams.addSqlStrBuffer(",veh_use_type,host_veh_type,veh_use_range,veh_use_plan_num,dispatch_status,dispatch_lock_status");
	hParams.addSqlStrBuffer(",begin_use_time,end_use_time,veh_use_reason as applyReason");
	hParams.addSqlStrBuffer(",flow_inst_id,flow_execution_Id,flow_key");
	hParams.addSqlStrBuffer(",create_time as applyTime");
	hParams.addSqlStrBuffer(",create_user_id as applyUserId");
	hParams.addSqlStrBuffer(",create_user_name as applyUser");
	hParams.addSqlStrBuffer(",create_org_dept_name as applyUserOrgDeptName");
	generateCondition(req, hParams);
	hParams.addSqlStrBuffer(" ORDER BY create_time desc");
	return findBySql(hParams, req.getStartRow().intValue(), req.getMaxSize().intValue(), VehUseApplyInfoListResp.class);
}


private void generateCondition(VehUseApplyInfoListReq req,HibernateParams hParams) throws Exception{
    OrgDeptSearchFilter helper = new OrgDeptSearchFilter();
    hParams.addSqlStrBuffer(" FROM VD_VEH_USE_APPLY_INFO t ");
    StringBuilder condition = new StringBuilder();
    
    if(Util.isNotEmpty(req.getCreateUserId())) 
        addEqualCondition(condition, hParams, "create_user_id", req.getCreateUserId());
    //只返回正常用車申請，不包括緊急排程
    addEqualCondition(condition, hParams, "apply_list_type", Constant.VehUseApplyType.NORMAL);
    if(!ArrayUtils.isEmpty(req.getApplyStatus())){//申請狀態
        if (req.getApplyStatus().length == 1){
            condition.append(" and apply_Status=? ");
            hParams.addParamObj(req.getApplyStatus()[0]);
        }else{
            condition.append(" and apply_Status in (").append(whereIn(req.getApplyStatus(), hParams)).append(") ");
        }
    }
    
    addEqualCondition(condition, hParams, "dispatch_status", req.getDispatchStatus());
    addEqualCondition(condition, hParams, "veh_Use_Type", req.getVehUseType());
    addEqualCondition(condition, hParams, "host_veh_type", req.getHostVehType());
    addEqualCondition(condition, hParams, "veh_Use_Range", req.getVehUseRange());
    
    if(isNotEmpty(req.getApplyId()))//申請單編號
        addCondition(condition, hParams, "apply_id like ? ", likeValue(req.getApplyId().trim()));
    if(isNotEmpty(req.getVehUserName()))//用車人姓名
        addCondition(condition, hParams, "veh_User_Name like ? ", likeValue(req.getVehUserName().trim()));

    if(req.getPlanBeginTime() != null && req.getPlanEndTime() == null) {
        addCondition(condition,hParams,"begin_use_time < ? ", req.getPlanBeginTime());
        addCondition(condition,hParams,"end_use_time > ? ", req.getPlanBeginTime());
    }else if(req.getPlanBeginTime() == null && req.getPlanEndTime() != null) {
        addCondition(condition,hParams,"begin_use_time < ? ", req.getPlanEndTime());
        addCondition(condition,hParams,"end_use_time > ? ", req.getPlanEndTime());
    }else if(req.getPlanBeginTime() != null && req.getPlanEndTime() != null) {//首尾時間有交集或包含關係
        condition.append(" and (begin_use_time <= ? and end_use_time>=?)");
        hParams.addParamObj(req.getPlanEndTime());
        hParams.addParamObj(req.getPlanBeginTime());
    }
    
    if(req.getApplyBeginTime()!=null) {
        addCondition(condition,hParams,"create_time >= ? ", req.getApplyBeginTime());
    }
    
    if(req.getApplyEndTime()!=null) {
        addCondition(condition,hParams,"create_time <= ? ", req.getApplyEndTime());
    }
    
    if(req.getVehUseOrgId() != null && req.getVehUseDeptId() != null) {
        condition.append(" and(veh_use_org_id,veh_use_dept_id) in(");
        condition.append(helper.getMyViewSubDeptSql(req.getVehUseOrgId(),req.getVehUseDeptId(),hParams));
        condition.append(")");
    }
    
    if(Util.isNotEmpty(req.getCreateOrgId()) && Util.isNotEmpty(req.getCreateDeptId())) {
        condition.append(" and(create_org_id,create_dept_id) in(");
        condition.append(helper.getMyViewSubDeptSql(req.getCreateOrgId(),req.getCreateDeptId(),hParams));
        condition.append(")");
    }
    
    if(Util.isNotEmpty(req.getQueryCreateTimeBegin())) addCondition(condition, hParams, "create_time >= ?", req.getQueryCreateTimeBegin());
    if(Util.isNotEmpty(req.getQueryCreateTimeEnd())) addCondition(condition, hParams, "create_time <= ?", req.getQueryCreateTimeEnd());
    
    hParams.addSqlStrBuffer(condition.toString().replaceFirst(" and ", " where "));
}



SELECT COUNT(*)
  FROM VD_VEH_USE_APPLY_INFO t
 where create_user_id = ?
   and apply_list_type = ?

SELECT apply_id,
       veh_user_id,
       veh_user_name,
       dest_addr as dest_address,
       'SDL_RUNTIME.view.myCarApplyManage.myCarApplySP.MainLeaderSpWin' flowNodeKey,
       veh_use_org_dept_name,
       apply_status,
       decode(apply_status, 5, 0, 9, 1, 11, 1) as processStatus,
       veh_use_type,
       host_veh_type,
       veh_use_range,
       veh_use_plan_num,
       dispatch_status,
       dispatch_lock_status,
       begin_use_time,
       end_use_time,
       veh_use_reason as applyReason,
       flow_inst_id,
       flow_execution_Id,
       flow_key,
       create_time as applyTime,
       create_user_id as applyUserId,
       create_user_name as applyUser,
       create_org_dept_name as applyUserOrgDeptName
  FROM VD_VEH_USE_APPLY_INFO t
 where create_user_id = 1
   and apply_list_type = 1
 ORDER BY create_time desc

相關文章