public List getRoomList(String build) throws Exception {
List list = null;
if (!"".equals(build)) {
list =
roomDAO.find(
"select distinct new map(a.roomId as roomId,a.roomName as roomname) from TRoom a where a.build='"
+ build
+ "'");
} else {
list = roomDAO.find("select distinct new map(a.roomName as roomname) from TRoom a ");
}
return list;
}
public List getBuildList(String schoolarea) throws Exception {
List list = null;
if (!"".equals(schoolarea)) {
list =
roomDAO.find(
"select distinct new map(a.build as build) from TRoom a where a.schoolArea.areaCode='"
+ schoolarea
+ "'");
} else {
list = roomDAO.find("select distinct new map(a.build as build) from TRoom a");
}
return list;
}
public List getUnbindDevList() throws Exception {
List list =
roomDAO.find(
"from TDevice a where a.deviceid not in (select distinct b.id.deviceId from TRoomdevice b) "); // and
// a.devtypecode='9000'");
return list;
}
public List getMyClassList(String accountId) throws Exception {
List list = new ArrayList();
if (!"".equals(accountId)) {
list =
roomDAO.find(
"select distinct d from Toperlimit tol,Department d where tol.id.deptId=d.id and tol.id.operId='"
+ accountId
+ "' order by d.id");
}
return list;
}
public List getBindDevList(String roomid) throws Exception {
// TODO Auto-generated method stub
List list = new ArrayList();
if (!"".equals(roomid)) {
list =
roomDAO.find(
"select a from TDevice a,TRoomdevice b where b.id.roomId='"
+ roomid
+ "' and a.deviceid=b.id.deviceId");
}
return list;
}
public List getClassDevList(String schoolarea, String build, String roomname) throws Exception {
// TODO Auto-generated method stub
StringBuffer sql = new StringBuffer();
sql.append(
"select new org.king.check.domain.ClassDevAllInfo(a.roomId,a.schoolArea.areaCode,a.schoolArea.areaName,a.build,a.roomName,'') from TRoom a where 1=1 ");
if (null != schoolarea && !"".equals(schoolarea)) {
sql.append("and a.schoolArea.areaCode='").append(schoolarea).append("' ");
}
if (null != build && !"".equals(build)) {
sql.append("and a.build='").append(build).append("' ");
}
if (null != roomname && !"".equals(roomname)) {
sql.append("and a.roomName='").append(roomname).append("' ");
}
List entitys = roomDAO.find(sql.toString());
for (int i = 0; i < entitys.size(); i++) {
ClassDevAllInfo classDevAllInfo = (ClassDevAllInfo) entitys.get(i);
if (!"".equals(classDevAllInfo.getRoomid()) && classDevAllInfo.getRoomid() != null) {
List list_devs =
roomDAO.find(
"select b.devicename from TRoomdevice a,TDevice b "
+ "where a.id.roomId='"
+ classDevAllInfo.getRoomid()
+ "' and a.id.deviceId=b.deviceid");
String devicename = "";
if (list_devs.size() > 0) {
for (int j = 0; j < list_devs.size(); j++) {
devicename += list_devs.get(j).toString() + ";";
}
}
if (devicename.lastIndexOf(",") != -1) {
devicename = devicename.substring(0, devicename.lastIndexOf(","));
}
classDevAllInfo.setDevname(devicename);
}
}
return entitys;
}
public List findRoomDevice(TRoomdevice roomdevice) throws Exception {
StringBuffer query = new StringBuffer(" from TRoomdevice rd where 1=1 ");
if (StringUtils.isNotEmpty(roomdevice.getId().getRoomId())) {
query.append(" and rd.id.roomId ='").append(roomdevice.getId().getRoomId()).append("'");
}
if (roomdevice.getId().getDeviceId() != null
&& !"".equals(roomdevice.getId().getDeviceId().toString())) {
query.append(" and rd.id.deviceId =").append(roomdevice.getId().getDeviceId());
}
List result = roomDAO.find(query.toString());
return result;
}
// 查找某些个教室
public List findRoom(TRoom room) throws Exception {
StringBuffer query = new StringBuffer(" from TRoom r where 1=1 ");
if (StringUtils.isNotEmpty(room.getSchoolArea().getAreaCode())) {
query = query.append(" and r.schoolArea ='").append(room.getSchoolArea()).append("'");
}
if (StringUtils.isNotEmpty(room.getBuild())) {
query = query.append(" and r.build like '%").append(room.getBuild()).append("%'");
}
if (StringUtils.isNotEmpty(room.getRoomName())) {
query = query.append(" and r.roomName like '%").append(room.getRoomName()).append("%'");
}
return roomDAO.find(query.toString());
}
public List getLectureList(String accountId) throws Exception {
List list = new ArrayList();
if (!"".equals(accountId)) {
list =
roomDAO.find(
" select distinct tc.course from TCourseplan tc,TCourseplanclass tcp,Toperlimit tol,Department d"
+ " where tc.id=tcp.id.planid and tcp.id.classno=tol.id.deptId "
+ " and tol.id.deptId=d.id and tol.id.operId='"
+ accountId
+ "' and tc.useDate='"
+ DateUtil.getNow("yyyyMMdd")
+ "' order by tc.course.coursename");
}
return list;
}
public TRoom findOneRoom(String areaName, String build, String roomName) throws Exception {
StringBuffer query = new StringBuffer(" from TRoom r where 1=1 ");
if (StringUtils.isNotEmpty(areaName)) {
query = query.append(" and r.schoolArea.areaName ='").append(areaName).append("'");
}
if (StringUtils.isNotEmpty(build)) {
query = query.append(" and r.build = '").append(build).append("'");
}
if (StringUtils.isNotEmpty(roomName)) {
query = query.append(" and r.roomName = '").append(roomName).append("'");
}
List list = roomDAO.find(query.toString());
if (list != null && !list.isEmpty()) {
TRoom oneroom = (TRoom) list.get(0);
return oneroom;
}
return null;
}
public String saveRoomImp(HSSFSheet sheet) throws Exception {
String result = "";
try {
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount > 0) {
int savenum = 0;
for (int i = 1; i < rowCount; i++) {
HSSFRow row = sheet.getRow(i);
try {
String schoolArea = ""; // 校区
String build = ""; // 楼宇
String roomName = ""; // 教室
schoolArea = row.getCell((short) 0).getStringCellValue().trim(); // 校区
build = row.getCell((short) 1).getStringCellValue().trim(); // 楼宇
roomName = row.getCell((short) 2).getStringCellValue().trim(); // 教室
if (findOneRoom(schoolArea, build, roomName) != null) {
result = result + " 第" + i + "行教室已存在，未导入。<br>";
continue;
} else {
TArea area = areaService.findAreaByName(schoolArea);
TRoom room = new TRoom();
room.setSchoolArea(area);
room.setBuild(build);
room.setRoomName(roomName);
// room.setStatus(status);
roomDAO.save(room);
}
savenum++;
} catch (Exception e) {
result = result + " 第" + i + "行，数据导入失败。<br>";
}
}
result = result + " 成功导入" + savenum + "行。<br>";
} else {
result = result + " 没有找到有效的记录。<br>";
}
} catch (Exception e) {
result = result + "导入出现异常中断。<br>";
}
return result;
}
public void deleteRoom(TRoom Troom) throws Exception {
Troom.setStatus(0);
roomDAO.update(Troom);
}
public void updateRoom(TRoom Troom) throws Exception {
roomDAO.update(Troom);
}
public TRoom getRoom(String id) throws Exception {
return roomDAO.get(id);
}
public void saveRoom(TRoom Troom) throws Exception {
roomDAO.save(Troom);
}
public List getAreaList() throws Exception {
List list = roomDAO.find("from TArea a where a.areaLevel=2");
return list;
}
public void deleteRoomDevice(TRoomdevice roomdevice) throws Exception {
roomDAO.delete(roomdevice);
}
public void saveRoomDevice(TRoomdevice roomdevice) throws Exception {
roomDAO.save(roomdevice);
}
public List getAreaListMap() throws Exception {
List list =
roomDAO.find(
"select new Map(a.areaCode as areaCode ,a.areaName as areaName ) from TArea a where a.areaLevel=2");
return list;
}