package com.hxzkoa.services;
|
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import javax.persistence.EntityManager;
|
import javax.persistence.PersistenceContext;
|
import javax.persistence.Query;
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.RequestMethod;
|
|
import com.hxzkoa.json.tb_collector;
|
import java.sql.Time;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
|
import javax.persistence.EntityManager;
|
import javax.persistence.PersistenceContext;
|
import javax.persistence.Query;
|
|
import org.springframework.stereotype.Service;
|
|
import com.hxzkoa.json.User;
|
import com.hxzkoa.json.tb_achor;
|
import com.hxzkoa.json.tb_anchor_nearby;
|
import com.hxzkoa.json.tb_collector;
|
import com.hxzkoa.json.tb_department;
|
import com.hxzkoa.json.tb_fence;
|
import com.hxzkoa.json.tb_gas;
|
import com.hxzkoa.json.tb_gas_history;
|
import com.hxzkoa.json.tb_gps;
|
import com.hxzkoa.json.tb_history_power;
|
import com.hxzkoa.json.tb_historyinwarning;
|
import com.hxzkoa.json.tb_icon;
|
import com.hxzkoa.json.tb_map;
|
import com.hxzkoa.json.tb_person;
|
import com.hxzkoa.json.tb_realinwarning;
|
import com.hxzkoa.json.tb_realocation;
|
import com.hxzkoa.json.tb_realpositoin;
|
import com.hxzkoa.json.tb_shipin;
|
import com.hxzkoa.json.tb_system;
|
import com.hxzkoa.json.tb_tag;
|
import com.hxzkoa.json.tb_tagpower;
|
import com.hxzkoa.json.tb_tongbuanchor;
|
import com.hxzkoa.json.tb_track;
|
import com.hxzkoa.json.tb_warning;
|
import com.hxzkoa.json.tb_xunjianbaobiao;
|
import com.hxzkoa.json.tb_xunjianset;
|
|
@Service
|
public class ZhwService {
|
|
@PersistenceContext
|
private EntityManager em;
|
@Autowired
|
private CaozuoService cs;
|
|
public List<tb_map> getMap(String floor_number) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,mapname,x_Truelength,y_Truewidth,x0_length,y0_width,x_Pixel,y_Pixel FROM tb_map WHERE floor="+floor_number;
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_map> tb_mapList = new ArrayList<tb_map>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_map map = new tb_map();
|
Object[] obj = (Object[]) resultList.get(i);
|
map.setFloor((String) obj[0]);
|
map.setMapname((String) obj[1]);
|
map.setX_Truelength((String) obj[2]);
|
map.setY_Truewidth((String) obj[3]);
|
map.setX0_length((String) obj[4]);
|
map.setY0_width((String) obj[5]);
|
map.setX_Pixel((String) obj[6]);
|
map.setY_Pixel((String) obj[7]);
|
tb_mapList.add(map);
|
}
|
}
|
return tb_mapList;
|
}
|
|
public List<tb_map> getMap_all() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,mapname,x_Truelength,y_Truewidth,x0_length,y0_width,x_Pixel,y_Pixel FROM tb_map";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_map> tb_mapList = new ArrayList<tb_map>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_map map = new tb_map();
|
Object[] obj = (Object[]) resultList.get(i);
|
map.setFloor((String) obj[0]);
|
map.setMapname((String) obj[1]);
|
map.setX_Truelength((String) obj[2]);
|
map.setY_Truewidth((String) obj[3]);
|
map.setX0_length((String) obj[4]);
|
map.setY0_width((String) obj[5]);
|
map.setX_Pixel((String) obj[6]);
|
map.setY_Pixel((String) obj[7]);
|
tb_mapList.add(map);
|
}
|
}
|
return tb_mapList;
|
}
|
|
public List<tb_map> getFloor() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,mapname FROM tb_map";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_map> tb_mapList = new ArrayList<tb_map>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_map map = new tb_map();
|
Object[] obj = (Object[]) resultList.get(i);
|
map.setFloor((String) obj[0]);
|
map.setMapname((String) obj[1]);
|
tb_mapList.add(map);
|
}
|
}
|
return tb_mapList;
|
}
|
|
public List<tb_map> getFloorByMapname(String mapname) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,mapname FROM tb_map WHERE mapname='"+mapname+"'";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_map> tb_mapList = new ArrayList<tb_map>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_map map = new tb_map();
|
Object[] obj = (Object[]) resultList.get(i);
|
map.setFloor((String) obj[0]);
|
map.setMapname((String) obj[1]);
|
tb_mapList.add(map);
|
}
|
}
|
return tb_mapList;
|
}
|
|
public List<tb_achor> getAnchor() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT anchorid,anchorip FROM tb_achor";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_achor> tb_anchorList = new ArrayList<tb_achor>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_achor anchor = new tb_achor();
|
Object[] obj = (Object[]) resultList.get(i);
|
anchor.setAnchorid((String) obj[0]);
|
anchor.setAnchorip((String) obj[1]);
|
tb_anchorList.add(anchor);
|
}
|
}
|
return tb_anchorList;
|
}
|
|
public List<tb_fence> getFence(String floor_number) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,type,bumen,name,zuobiao,shape,start,stop,addtime,color FROM tb_fence WHERE floor="+floor_number;
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_fence> tb_fenceList = new ArrayList<tb_fence>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_fence fence = new tb_fence();
|
Object[] obj = (Object[]) resultList.get(i);
|
fence.setFloor((String) obj[0]);
|
fence.setType((String) obj[1]);
|
fence.setBumen((String) obj[2]);
|
fence.setName((String) obj[3]);
|
fence.setZuobiao((String) obj[4]);
|
fence.setShape((String) obj[5]);
|
fence.setStart(obj[6] == null ? "" : obj[6].toString());
|
fence.setStop(obj[7] == null ? "" : obj[7].toString());
|
fence.setAddtime((String) obj[8]);
|
fence.setColor((String) obj[9]);
|
tb_fenceList.add(fence);
|
}
|
}
|
return tb_fenceList;
|
}
|
|
public List<tb_fence> getFence_all() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT floor,type,bumen,name,zuobiao,shape,start,stop,addtime,color FROM tb_fence";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_fence> tb_fenceList = new ArrayList<tb_fence>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_fence fence = new tb_fence();
|
Object[] obj = (Object[]) resultList.get(i);
|
fence.setFloor((String) obj[0]);
|
fence.setType((String) obj[1]);
|
fence.setBumen((String) obj[2]);
|
fence.setName((String) obj[3]);
|
fence.setZuobiao((String) obj[4]);
|
fence.setShape((String) obj[5]);
|
fence.setStart(obj[6] == null ? "" : obj[6].toString());
|
fence.setStop(obj[7] == null ? "" : obj[7].toString());
|
fence.setAddtime((String) obj[8]);
|
fence.setColor((String) obj[9]);
|
tb_fenceList.add(fence);
|
}
|
}
|
return tb_fenceList;
|
}
|
|
@Transactional
|
public int drawFence_add(tb_fence fence) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_fence(id,floor,type,bumen,name,zuobiao,shape,start,stop,addtime,color) VALUES(:id,:floor,:type,:bumen,:name,:zuobiao,:shape,:start,:stop,now(),:color)";
|
cs.tb_caozuo("tb_fence", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("id", fence.getId());
|
query.setParameter("floor", Integer.parseInt(fence.getFloor()));
|
query.setParameter("type", fence.getType());
|
query.setParameter("bumen", fence.getBumen());
|
query.setParameter("name", fence.getName());
|
query.setParameter("zuobiao", fence.getZuobiao());
|
query.setParameter("shape", fence.getShape());
|
query.setParameter("start", fence.getStart());
|
query.setParameter("stop", fence.getStop());
|
query.setParameter("color", fence.getColor());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int xunjianSet_add(String name) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_xunjianset(quyu,startime,stoptime,xunshu,tagid,needstoptime,cishu,addtime) VALUES(:name,'23:59:00','无','无','所有标签','1','1',now())";
|
cs.tb_caozuo("tb_xunjianset", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("name", name);
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int fenceInout_add(String name,String zuobiaos,String shape,String floor) {
|
String sql = null;
|
Query query = null;
|
|
String bianshu = "";
|
StringBuffer canin=new StringBuffer();
|
StringBuffer canout=new StringBuffer();
|
if (shape == "矩形") {
|
bianshu = "4";
|
canin=new StringBuffer("1,1,1,1");
|
canout=new StringBuffer("1,1,1,1");
|
} else {
|
String[] zb=zuobiaos.split("\\;");
|
bianshu=String.valueOf(zb.length);
|
for(int i=0;i<zb.length-1;i++) {
|
canin.append("1,");
|
canout.append("1,");
|
if(i==(zb.length-2)) {
|
canin.append("1");
|
canout.append("1");
|
}
|
}
|
}
|
sql = "INSERT INTO tb_fence_inout(name, zuobiao, bianshu, canin, canout, floor, addtime) VALUES(:name,:zuobiao,:bianshu,:canin,:canout,:floor,now())";
|
cs.tb_caozuo("tb_fence_inout", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("name", name);
|
query.setParameter("zuobiao", zuobiaos);
|
query.setParameter("bianshu", bianshu);
|
query.setParameter("canin", canin.toString());
|
query.setParameter("canout", canout.toString());
|
query.setParameter("floor", floor);
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
|
|
public List<tb_track> getTagtrack(String tag_id,String begin_time,String end_time,String floor) throws ParseException {
|
String sql = null;
|
Query query = null;
|
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
Date now=dateFormat.parse(begin_time);
|
System.out.print(now);
|
SimpleDateFormat dateFormat2=new SimpleDateFormat("yyyyMMdd");
|
String now2 = dateFormat2.format(now);
|
System.out.print(now2);
|
// System.out.print(begin_time);
|
// System.out.print(end_time);
|
// System.out.print(tag_id);
|
// System.out.print(floor);
|
|
sql = "SELECT tagid,x,y,z,layer,time FROM tb_track_"+now2+" WHERE layer= '"+floor+"' AND tagid= '"+tag_id+"' AND time>= '"+begin_time+"' AND time<= '"+end_time+"' ORDER BY time";
|
// sql = "SELECT tagid,x,y,z,layer,time FROM tb_track WHERE layer= '"+floor+"' AND tagid= '"+tag_id+"' AND time>= '"+begin_time+"' AND time<= '"+end_time+"'";
|
|
System.out.print(sql);
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_track> tb_trackList = new ArrayList<tb_track>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_track map = new tb_track();
|
Object[] obj = (Object[]) resultList.get(i);
|
map.setTagid((String) obj[0]);
|
map.setX((String) obj[1]);
|
map.setY((String) obj[2]);
|
map.setZ((String) obj[3]);
|
map.setLayer((String) obj[4]);
|
map.setTime((String) obj[5]);
|
tb_trackList.add(map);
|
}
|
}
|
return tb_trackList;
|
}
|
|
public List<tb_achor> getAnchorip(String anchorid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT anchorid,anchorip FROM tb_achor WHERE anchorid="+anchorid;
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_achor> tb_anchorList = new ArrayList<tb_achor>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_achor anchor = new tb_achor();
|
Object[] obj = (Object[]) resultList.get(i);
|
anchor.setAnchorid((String) obj[0]);
|
anchor.setAnchorip((String) obj[1]);
|
tb_anchorList.add(anchor);
|
}
|
}
|
return tb_anchorList;
|
}
|
|
public List<tb_achor> getAnchorInfo(String floor) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.* FROM tb_achor AS a,( SELECT anchorid, max( GreateTime ) time FROM tb_achor GROUP BY anchorid ) AS b WHERE a.GreateTime = b.time AND a.anchorid = b.anchorid AND a.layer='"+floor+"'";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_achor> tb_anchorList = new ArrayList<tb_achor>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_achor anchor = new tb_achor();
|
Object[] obj = (Object[]) resultList.get(i);
|
anchor.setAnchorid((String) obj[1]);
|
anchor.setPOSX((String) obj[2]);
|
anchor.setPOSY((String) obj[3]);
|
anchor.setPOSZ((String) obj[4]);
|
anchor.setLayer((String) obj[5]);
|
anchor.setAnchormode((String) obj[8]);
|
anchor.setAnchorip((String) obj[9]);
|
anchor.setGreateTime((String) obj[13]);
|
tb_anchorList.add(anchor);
|
}
|
}
|
return tb_anchorList;
|
}
|
|
public List<tb_achor> getAnchorInfo_all() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.* FROM tb_achor AS a,( SELECT anchorid, max( GreateTime ) time FROM tb_achor GROUP BY anchorid ) AS b WHERE a.GreateTime = b.time AND a.anchorid = b.anchorid";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_achor> tb_anchorList = new ArrayList<tb_achor>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_achor anchor = new tb_achor();
|
Object[] obj = (Object[]) resultList.get(i);
|
anchor.setAnchorid((String) obj[1]);
|
anchor.setPOSX((String) obj[2]);
|
anchor.setPOSY((String) obj[3]);
|
anchor.setPOSZ((String) obj[4]);
|
anchor.setLayer((String) obj[5]);
|
anchor.setAnchormode((String) obj[8]);
|
anchor.setAnchorip((String) obj[9]);
|
anchor.setGreateTime((String) obj[13]);
|
tb_anchorList.add(anchor);
|
}
|
}
|
return tb_anchorList;
|
}
|
|
public List<tb_achor> getALlAnchor() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.* FROM tb_achor AS a,( SELECT anchorid, max( GreateTime ) time FROM tb_achor GROUP BY anchorid ) AS b WHERE a.GreateTime = b.time AND a.anchorid = b.anchorid";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_achor> tb_anchorList = new ArrayList<tb_achor>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_achor anchor = new tb_achor();
|
Object[] obj = (Object[]) resultList.get(i);
|
anchor.setAnchorid((String) obj[1]);
|
anchor.setPOSX((String) obj[2]);
|
anchor.setPOSY((String) obj[3]);
|
anchor.setPOSZ((String) obj[4]);
|
anchor.setLayer((String) obj[5]);
|
anchor.setAnchorip((String) obj[9]);
|
anchor.setGreateTime((String) obj[13]);
|
tb_anchorList.add(anchor);
|
}
|
}
|
return tb_anchorList;
|
}
|
|
public List<tb_tag> getRealTagState(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT * FROM tb_tag WHERE tag_id='"+tagid+"' ORDER BY addtime DESC LIMIT 1";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_realtagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_tag tag = new tb_tag();
|
Object[] obj = (Object[]) resultList.get(i);
|
tag.setTag_id(obj[1] == null ? "" : obj[1].toString());
|
tag.setState(obj[2] == null ? "" : obj[2].toString());
|
tag.setPower(obj[3] == null ? "" : obj[3].toString());
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
tag.setAddtime(obj[5] == null ? "" : obj[5].toString());
|
tb_realtagList.add(tag);
|
}
|
}
|
return tb_realtagList;
|
}
|
|
public List<tb_person> getRealpersonState(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT * FROM tb_person WHERE p_tagid='"+tagid+"' ORDER BY p_addtiem DESC LIMIT 1";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_person> tb_realpersonList = new ArrayList<tb_person>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_person person = new tb_person();
|
Object[] obj = (Object[]) resultList.get(i);
|
person.setP_online(obj[16] == null ? "" : obj[16].toString());
|
person.setP_power(obj[17] == null ? "" : obj[17].toString());
|
tb_realpersonList.add(person);
|
}
|
}
|
return tb_realpersonList;
|
}
|
|
public List<tb_person> getpersonLocation(String floor) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.* FROM tb_person AS a,( SELECT p_tagid, max( p_addtiem ) time FROM tb_person GROUP BY p_tagid ) AS b WHERE a.p_addtiem = b.time AND a.p_tagid = b.p_tagid AND a.p_floor='"+floor+"'";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_person> tb_realocationList = new ArrayList<tb_person>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_person realocation = new tb_person();
|
Object[] obj = (Object[]) resultList.get(i);
|
realocation.setP_name((String) obj[1]);
|
realocation.setP_tagid((String) obj[2]);
|
realocation.setP_x((String) obj[12]);
|
realocation.setP_y((String) obj[13]);
|
realocation.setP_floor((String) obj[14]);
|
realocation.setP_sos((String) obj[15]);
|
realocation.setP_online((String) obj[16]);
|
realocation.setP_power((String) obj[17]);
|
realocation.setP_kaoqing((String) obj[18]);
|
realocation.setP_fence((String) obj[19]);
|
realocation.setP_fencename((String) obj[20]);
|
realocation.setP_kaoqqingname((String) obj[21]);
|
realocation.setP_image((String) obj[22]);
|
realocation.setP_addtiem((String) obj[23]);
|
realocation.setP_sousuo((String) obj[25]);
|
realocation.setP_shipin((String) obj[26]);
|
tb_realocationList.add(realocation);
|
}
|
}
|
return tb_realocationList;
|
}
|
|
public List<tb_person> getpersonLocation_all() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.* FROM tb_person AS a,( SELECT p_tagid, max( p_addtiem ) time FROM tb_person GROUP BY p_tagid ) AS b WHERE a.p_addtiem = b.time AND a.p_tagid = b.p_tagid";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_person> tb_realocationList = new ArrayList<tb_person>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_person realocation = new tb_person();
|
Object[] obj = (Object[]) resultList.get(i);
|
realocation.setP_name((String) obj[1]);
|
realocation.setP_tagid((String) obj[2]);
|
realocation.setP_x((String) obj[12]);
|
realocation.setP_y((String) obj[13]);
|
realocation.setP_floor((String) obj[14]);
|
realocation.setP_sos((String) obj[15]);
|
realocation.setP_online((String) obj[16]);
|
realocation.setP_power((String) obj[17]);
|
realocation.setP_kaoqing((String) obj[18]);
|
realocation.setP_fence((String) obj[19]);
|
realocation.setP_fencename((String) obj[20]);
|
realocation.setP_kaoqqingname((String) obj[21]);
|
realocation.setP_image((String) obj[22]);
|
realocation.setP_addtiem((String) obj[23]);
|
tb_realocationList.add(realocation);
|
}
|
}
|
return tb_realocationList;
|
}
|
|
|
public List<tb_track> getRealPosTrack(String[] taglist,String start_time, String floor) {
|
String sql = null;
|
Query query = null;
|
Date date=new Date();
|
SimpleDateFormat dateFormat=new SimpleDateFormat("YYYYMMdd");
|
String now=dateFormat.format(date);
|
|
sql = "SELECT * FROM tb_track_"+now+" WHERE time >='" +start_time+"' AND layer='"+floor+ " ' AND ( ";
|
// sql = "SELECT * FROM tb_track WHERE time >='" +start_time+"' AND layer='"+floor+ "' AND (";
|
|
for (int i=0;i<taglist.length;i++) {
|
sql += "tagid='"+taglist[i]+"'";
|
if (i!=taglist.length-1) {
|
sql += " OR ";
|
} else {
|
sql += " ) ";
|
}
|
};
|
sql += " ORDER BY time";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_track> tb_realTrackList = new ArrayList<tb_track>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_track realtrack = new tb_track();
|
Object[] obj = (Object[]) resultList.get(i);
|
realtrack.setTagid((String) obj[1]);
|
realtrack.setX((String) obj[2]);
|
realtrack.setY((String) obj[3]);
|
realtrack.setZ((String) obj[4]);
|
realtrack.setLayer((String) obj[5]);
|
realtrack.setTime((String) obj[6]);
|
tb_realTrackList.add(realtrack);
|
}
|
}
|
return tb_realTrackList;
|
}
|
|
public List<tb_track> getRealPosTrack_all(String[] taglist, String start_time) {
|
String sql = null;
|
Query query = null;
|
Date date=new Date();
|
SimpleDateFormat dateFormat=new SimpleDateFormat("YYYYMMdd");
|
String now=dateFormat.format(date);
|
|
// sql = "SELECT * FROM tb_track_"+now+" WHERE tagid='"+tagid+"' AND time >='" +start_time+"' ORDER BY time";
|
// sql = "SELECT * FROM tb_track WHERE tagid='"+tagid+"' AND time >='" +start_time+"' ORDER BY time";
|
sql = "SELECT * FROM tb_track_"+now+" WHERE time >='" +start_time+"' AND ( ";
|
|
for (int i=0;i<taglist.length;i++) {
|
sql += "tagid='"+taglist[i]+"'";
|
if (i!=taglist.length-1) {
|
sql += " OR ";
|
} else {
|
sql += " ) ";
|
}
|
};
|
sql += " ORDER BY time";
|
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_track> tb_realTrackList = new ArrayList<tb_track>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_track realtrack = new tb_track();
|
Object[] obj = (Object[]) resultList.get(i);
|
realtrack.setTagid((String) obj[1]);
|
realtrack.setX((String) obj[2]);
|
realtrack.setY((String) obj[3]);
|
realtrack.setZ((String) obj[4]);
|
realtrack.setLayer((String) obj[5]);
|
realtrack.setTime((String) obj[6]);
|
tb_realTrackList.add(realtrack);
|
}
|
}
|
return tb_realTrackList;
|
}
|
|
|
public List<tb_gas> getGas() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT gas_type, nong_du, x,y FROM tb_gas";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_gas> tb_gasList = new ArrayList<tb_gas>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_gas gas = new tb_gas();
|
Object[] obj = (Object[]) resultList.get(i);
|
gas.setGas_type((String) obj[0]);
|
gas.setNong_du((String) obj[1]);
|
gas.setX((String) obj[2]);
|
gas.setY((String) obj[3]);
|
tb_gasList.add(gas);
|
}
|
}
|
return tb_gasList;
|
}
|
|
public List<tb_gps> getGPS() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT a.*,c.p_name FROM tb_gps AS a,( SELECT tagid, max(addtime) time FROM tb_gps GROUP BY tagid ) AS b, tb_person as c WHERE a.addtime = b.time AND a.tagid = b.tagid AND c.p_tagid=a.tagid";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_gps> tb_gpsList = new ArrayList<tb_gps>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_gps gps = new tb_gps();
|
Object[] obj = (Object[]) resultList.get(i);
|
gps.setGps_utc_time((String) obj[1]);
|
gps.setGps_weidu((String) obj[2]);
|
gps.setGps_NS((String) obj[3]);
|
gps.setGsp_jingdu((String) obj[4]);
|
gps.setGps_EW((String) obj[5]);
|
gps.setGps_state((String) obj[6]);
|
gps.setGps_num((String) obj[7]);
|
gps.setGps_hdop((String) obj[8]);
|
gps.setGps_haiba_gao((String) obj[9]);
|
gps.setGps_tuoqiu((String) obj[10]);
|
gps.setGps_chafen_time((String) obj[11]);
|
gps.setGps_chafen_id((String) obj[12]);
|
gps.setGps_jiaoyan((String) obj[13]);
|
gps.setTagid((String) obj[14]);
|
gps.setGps_kahao((String) obj[15]);
|
gps.setGps_power((String) obj[16]);
|
gps.setGps_sos((String) obj[17]);
|
gps.setAddtime((String) obj[18]);
|
gps.setName((String) obj[19]);
|
tb_gpsList.add(gps);
|
}
|
}
|
return tb_gpsList;
|
}
|
|
public List<tb_gps> getGPStrack(String tag_id,String begin_time,String end_time) throws ParseException {
|
String sql = null;
|
Query query = null;
|
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
Date now=dateFormat.parse(begin_time);
|
SimpleDateFormat dateFormat2=new SimpleDateFormat("yyyyMMdd");
|
String now2 = dateFormat2.format(now);
|
sql = "SELECT a.*,b.p_name FROM tb_gps_track_"+now2+" AS a, tb_person AS b WHERE a.tagid= '"+tag_id+"' AND a.addtime>= '"+begin_time+"' AND a.addtime<= '"+end_time+"' AND a.tagid=b.p_tagid ORDER BY addtime";
|
System.out.print(sql);
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_gps> tb_trackList = new ArrayList<tb_gps>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_gps gps = new tb_gps();
|
Object[] obj = (Object[]) resultList.get(i);
|
gps.setGps_utc_time((String) obj[1]);
|
gps.setGps_weidu((String) obj[2]);
|
gps.setGps_NS((String) obj[3]);
|
gps.setGsp_jingdu((String) obj[4]);
|
gps.setGps_EW((String) obj[5]);
|
gps.setGps_state((String) obj[6]);
|
gps.setGps_num((String) obj[7]);
|
gps.setGps_hdop((String) obj[8]);
|
gps.setGps_haiba_gao((String) obj[9]);
|
gps.setGps_tuoqiu((String) obj[10]);
|
gps.setGps_chafen_time((String) obj[11]);
|
gps.setGps_chafen_id((String) obj[12]);
|
gps.setGps_jiaoyan((String) obj[13]);
|
gps.setTagid((String) obj[14]);
|
gps.setGps_kahao((String) obj[15]);
|
gps.setGps_power((String) obj[16]);
|
gps.setGps_sos((String) obj[17]);
|
gps.setAddtime((String) obj[18]);
|
gps.setName((String) obj[19]);
|
tb_trackList.add(gps);
|
}
|
}
|
return tb_trackList;
|
}
|
|
@Transactional
|
public int gpsRecord_add(tb_gps gps) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_gps(`gps_utc_time`, `gps_weidu`, `gps_NS`, `gsp_jingdu`, `gps_EW`, "
|
+ "`gps_state`, `gps_num`, `gps_hdop`, `gps_haiba_gao`, `gps_tuoqiu`, `gps_chafen_time`, `gps_chafen_id`, "
|
+ "`gps_jiaoyan`, `tagid`, `gps_power`, `addtime`) VALUES(:gps_utc_time, :gps_weidu, :gps_NS,:gsp_jingdu, :gps_EW, "
|
+ ":gps_state, :gps_num, :gps_hdop, :gps_haiba_gao, :gps_tuoqiu, :gps_chafen_time, "
|
+ ":gps_chafen_id, :gps_jiaoyan, :tagid, :power, now(3))";
|
cs.tb_caozuo("tb_gps", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("gps_utc_time", gps.getGps_utc_time());
|
query.setParameter("gps_weidu", gps.getGps_weidu());
|
query.setParameter("gps_NS", gps.getGps_NS());
|
query.setParameter("gsp_jingdu", gps.getGsp_jingdu());
|
query.setParameter("gps_EW", gps.getGps_EW());
|
query.setParameter("gps_state", gps.getGps_state());
|
query.setParameter("gps_num", gps.getGps_num());
|
query.setParameter("gps_hdop", gps.getGps_hdop());
|
query.setParameter("gps_haiba_gao", gps.getGps_haiba_gao());
|
query.setParameter("gps_tuoqiu", gps.getGps_tuoqiu());
|
query.setParameter("gps_chafen_time", gps.getGps_chafen_time());
|
query.setParameter("gps_chafen_id", gps.getGps_chafen_id());
|
query.setParameter("gps_jiaoyan", gps.getGps_jiaoyan());
|
query.setParameter("tagid", gps.getTagid());
|
query.setParameter("power", gps.getGps_power());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
public List<tb_gps> searchGps(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT * FROM tb_gps WHERE tagid = :tagid";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", input);
|
List resultList = query.getResultList();
|
List<tb_gps> tb_gpsList = new ArrayList<tb_gps>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_gps gps = new tb_gps();
|
Object[] obj = (Object[]) resultList.get(i);
|
gps.setGps_utc_time((String) obj[1]);
|
gps.setGps_weidu((String) obj[2]);
|
gps.setGps_NS((String) obj[3]);
|
gps.setGsp_jingdu((String) obj[4]);
|
gps.setGps_EW((String) obj[5]);
|
gps.setGps_state((String) obj[6]);
|
gps.setGps_num((String) obj[7]);
|
gps.setGps_hdop((String) obj[8]);
|
gps.setGps_haiba_gao((String) obj[9]);
|
gps.setGps_tuoqiu((String) obj[10]);
|
gps.setGps_chafen_time((String) obj[11]);
|
gps.setGps_chafen_id((String) obj[12]);
|
gps.setGps_jiaoyan((String) obj[13]);
|
gps.setTagid((String) obj[14]);
|
gps.setGps_kahao((String) obj[15]);
|
gps.setGps_power((String) obj[16]);
|
gps.setGps_sos((String) obj[17]);
|
gps.setAddtime((String) obj[18]);
|
tb_gpsList.add(gps);
|
}
|
}
|
return tb_gpsList;
|
}
|
|
@Transactional
|
public int gpsRecord_modify(tb_gps gps) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_gps SET gps_utc_time=:gps_utc_time,gps_weidu=:gps_weidu,gps_NS=:gps_NS,gsp_jingdu=:gsp_jingdu"
|
+ ",gps_EW=:gps_EW,gps_state=:gps_state,gps_num=:gps_num,gps_hdop=:gps_hdop,gps_haiba_gao=:gps_haiba_gao,"
|
+ "gps_tuoqiu=:gps_tuoqiu,gps_chafen_time=:gps_chafen_time ,gps_chafen_id=:gps_chafen_id,gps_jiaoyan=:gps_jiaoyan,gps_power=:power, addtime=now(3) "
|
+ "WHERE tagid=:tagid";
|
cs.tb_caozuo("tb_gps", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("gps_utc_time", gps.getGps_utc_time());
|
query.setParameter("gps_weidu", gps.getGps_weidu());
|
query.setParameter("gps_NS", gps.getGps_NS());
|
query.setParameter("gsp_jingdu", gps.getGsp_jingdu());
|
query.setParameter("gps_EW", gps.getGps_EW());
|
query.setParameter("gps_state", gps.getGps_state());
|
query.setParameter("gps_num", gps.getGps_num());
|
query.setParameter("gps_hdop", gps.getGps_hdop());
|
query.setParameter("gps_haiba_gao", gps.getGps_haiba_gao());
|
query.setParameter("gps_tuoqiu", gps.getGps_tuoqiu());
|
query.setParameter("gps_chafen_time", gps.getGps_chafen_time());
|
query.setParameter("gps_chafen_id", gps.getGps_chafen_id());
|
query.setParameter("gps_jiaoyan", gps.getGps_jiaoyan());
|
query.setParameter("tagid", gps.getTagid());
|
query.setParameter("power", gps.getGps_power());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int gpsTrack_add(tb_gps gps) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_gps_track(`gps_utc_time`, `gps_weidu`, `gps_NS`, `gsp_jingdu`, `gps_EW`, "
|
+ "`gps_state`, `gps_num`, `gps_hdop`, `gps_haiba_gao`, `gps_tuoqiu`, `gps_chafen_time`, `gps_chafen_id`, "
|
+ "`gps_jiaoyan`, `tagid`, `gps_power`, `addtime`) VALUES(:gps_utc_time, :gps_weidu, :gps_NS,:gsp_jingdu, :gps_EW, "
|
+ ":gps_state, :gps_num, :gps_hdop, :gps_haiba_gao, :gps_tuoqiu, :gps_chafen_time, "
|
+ ":gps_chafen_id, :gps_jiaoyan, :tagid, :power, now())";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("gps_utc_time", gps.getGps_utc_time());
|
query.setParameter("gps_weidu", gps.getGps_weidu());
|
query.setParameter("gps_NS", gps.getGps_NS());
|
query.setParameter("gsp_jingdu", gps.getGsp_jingdu());
|
query.setParameter("gps_EW", gps.getGps_EW());
|
query.setParameter("gps_state", gps.getGps_state());
|
query.setParameter("gps_num", gps.getGps_num());
|
query.setParameter("gps_hdop", gps.getGps_hdop());
|
query.setParameter("gps_haiba_gao", gps.getGps_haiba_gao());
|
query.setParameter("gps_tuoqiu", gps.getGps_tuoqiu());
|
query.setParameter("gps_chafen_time", gps.getGps_chafen_time());
|
query.setParameter("gps_chafen_id", gps.getGps_chafen_id());
|
query.setParameter("gps_jiaoyan", gps.getGps_jiaoyan());
|
query.setParameter("tagid", gps.getTagid());
|
query.setParameter("power", gps.getGps_power());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int fencegao(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_person SET p_fence=0 WHERE p_tagid=:tagid";
|
cs.tb_caozuo("tb_person", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", tagid);
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int sosgao(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_person SET p_sos=0 WHERE p_tagid=:tagid";
|
cs.tb_caozuo("tb_person", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", tagid);
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int shipin(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_person SET p_shipin=0 WHERE p_tagid=:tagid";
|
cs.tb_caozuo("tb_person", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", tagid);
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
public String getstatus(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT status from tb_tag WHERE tag_id=:tagid";
|
cs.tb_caozuo("tb_gps", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", tagid);
|
List resultList = query.getResultList();
|
String status = resultList.get(0).toString();
|
return status;
|
}
|
|
public List<tb_person> getfencename(String tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT p_fencename from tb_person WHERE p_tagid="+tagid;
|
query = this.em.createNativeQuery(sql);
|
// System.out.print(sql);
|
List resultList = query.getResultList();
|
// System.out.print(resultList.size());
|
List<tb_person> tb_person = new ArrayList<tb_person>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_person person = new tb_person();
|
// Object[] obj = (Object[]) resultList.get(i);
|
person.setP_fencename(resultList.get(i) == null ? "" : resultList.get(i).toString());
|
// System.out.print(person.getP_fencename());
|
tb_person.add(person);
|
}
|
}
|
return tb_person;
|
}
|
|
public List<tb_shipin> getshebeiid(String fencename) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT shebeiid,tongdaoid from tb_shipin WHERE fencename=:fencename";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("fencename", fencename);
|
List resultList = query.getResultList();
|
List<tb_shipin> tb_shipinList = new ArrayList<tb_shipin>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_shipin shipin = new tb_shipin();
|
Object[] obj = (Object[]) resultList.get(i);
|
shipin.setShebeiid((String) obj[0]);
|
shipin.setTongdaoid((String) obj[1]);
|
tb_shipinList.add(shipin);
|
}
|
}
|
return tb_shipinList;
|
}
|
|
}
|
