设备支持多箱号查询，mybatis<if><foreach>标签

 

/**
 * 查询设备信息列表
 * 
 * @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>