裝置支援多箱號查詢，mybatis標籤

 

/**
 * 查詢裝置資訊列表
 * 
 * @param request
 * @param response
 * @return
 */
@RequestMapping("devList")
@ResponseBody
public List<OsmDev> orderList(HttpServletRequest request, HttpServletResponse response) throws Exception {
	//專案編號
	String projectid = request.getParameter("projectid");
	//箱號
	String boxnoStr = request.getParameter("boxno");
	List<String> boxnoList = new ArrayList<String>();
	if(boxnoStr !=null && !boxnoStr.equals("")){
		String[] boxnoArr = boxnoStr.split(",");
		for(String boxno : boxnoArr){
			boxnoList.add(boxno);
		}
	}
	//裝置編號
	String deveui = request.getParameter("deveui");
	deveui = (deveui == null) ? "" : deveui.trim();
	deveui = deveui.trim().toLowerCase();
	//發貨單編號
	String ordNo = request.getParameter("ordNo");
	
	//裝置型別
	String devtype = request.getParameter("devtype");
	//裝置狀態
	String devStatusStr = request.getParameter("devStatus");
	Integer devStatus=-1;
	if(devStatusStr != null && !devStatusStr.trim().equals("")){
		devStatus = Integer.parseInt(devStatusStr);
	}
	//登記開始時間
	String beginTime = request.getParameter("beginTime");  
	String beginTimeFenmiao ="";
	if(beginTime !=null && !beginTime.trim().equals("") ){
		beginTimeFenmiao = beginTime + " "+"00:00:00";
	}
	//登記結束時間
	String endTime = request.getParameter("endTime");  
	String endTimeFenmiao ="";
	if(endTime !=null && !endTime.trim().equals("") ){
		endTimeFenmiao = endTime + " "+"23:59:59";
	}
	logger.info("deveui = "+deveui+", ordNo="+ordNo+",devStatus="+devStatus+", beginTime="+beginTime+", finishdate="+endTime);		
	projectid = (projectid == null) ? "" : projectid.trim();
	ordNo = (ordNo == null) ? "" : ordNo.trim();
	devtype = (devtype == null) ? "" : devtype.trim();
	deveui = (deveui == null) ? "" : deveui.trim();
	beginTime = (beginTime == null) ? "" : beginTime.trim();
	endTime = (endTime == null) ? "" : endTime.trim();
	DevQueryPara para = genDevQryParam(projectid.trim(),deveui.trim(),ordNo.trim(), devtype.trim(),devStatus,beginTimeFenmiao, endTimeFenmiao,boxnoList);
	return devService.queryAllOsmDevByPara(para);
}

private DevQueryPara genDevQryParam(String projectid,String deveui,String ordNo,String devtype,Integer devStatus,String beginTimeFenmiao,String endTimeFenmiao,List<String> boxnoList)
{
	DevQueryPara para = new DevQueryPara();
	para.setProjectid(projectid);
	para.setDeveui(deveui);
	para.setOrdNo(ordNo);
	para.setDevType(devtype);
	para.setDevStatus(devStatus);
	para.setBeginDate(DateUtil.parseDatetime(beginTimeFenmiao));
	para.setEndDate(DateUtil.parseDatetime(endTimeFenmiao));
	para.setBoxnoList(boxnoList);
	return para;
}

// 查詢OsmDev資訊
public List<OsmDev> queryAllOsmDevByPara(DevQueryPara p) throws Exception;


@Override
public List<OsmDev> queryAllOsmDevByPara(DevQueryPara p) throws Exception {
	return this.devMapper.queryAllOsmDevByPara(p);
}

// 查詢OsmDev資訊
public List<OsmDev> queryAllOsmDevByPara(@Param("p") DevQueryPara p) throws Exception;

<select id="queryAllOsmDevByPara" resultType="com.zte.claa.infiboss.app.model.osm.OsmDev">
	 SELECT t.DEVEUI AS deveui, 
			t.DEVTYPE AS devType, 
			t.ORDNO AS ordNo, 
			t.CLAANO AS claaNo, 
			t.DEVSTATUS AS devStatus, 
			t.OPTIME AS opTime, 
			t.REMARK AS remark,
			t.PROJECTID AS projectid,
			t.ADDRESS AS address,
			t.GPSLAT AS gpslat,
			t.GPSLNG AS gpslng,
			t.GPSALT AS gpsalt,
			t.BOXNO AS boxno
	   FROM osmdb.t_osm_dev t 
	  WHERE 1 = 1 
	  <if test='p.projectid != null and p.projectid != "" '>
		 AND t.PROJECTID = #{p.projectid, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.devType != null and p.devType != "" '>
		 AND t.DEVTYPE = #{p.devType, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.deveui != null and p.deveui != "" '>
		 AND t.DEVEUI = #{p.deveui, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.ordNo != null and p.ordNo != "" '> 
		 AND t.DEVEUI IN (SELECT DISTINCT DEVEUI 
							FROM osmdb.t_osm_order_dev 
						   WHERE ORDNO = #{p.ordNo, jdbcType=VARCHAR}) 
	  </if>
	  <if test='p.devStatus != -1 and p.devStatus != 99 and p.devStatus != -99 '>
		 AND t.DEVSTATUS = #{p.devStatus, jdbcType=INTEGER} 
	  </if>
	  <if test='p.devStatus == 99'>
		 AND t.DEVSTATUS NOT IN (20,21) 
	  </if>
	  <!--  -99代表後臺重新建立發貨清單   -->
	  <if test='p.devStatus == -99'>
		 AND t.DEVSTATUS IN (11,13) 
	  </if>
	  <if test='p.beginDate != null'>
		 AND t.OPTIME &gt;= #{p.beginDate, jdbcType=DATE} 
	  </if>
	  <if test='p.endDate != null'>
		 AND t.OPTIME &lt;= #{p.endDate, jdbcType=DATE} 
	  </if>
	  <if test='p.boxnoList != null and p.boxnoList.size()>0'>
		 AND t.BOXNO in  
	  <foreach collection="p.boxnoList"  open="("  close=")" separator="," item="boxno">  
			 #{boxno, jdbcType=VARCHAR} 
	  </foreach>
	  </if>
	  ORDER BY t.DEVEUI 
	  LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER} 
</select>