package com.hxzkoa.services;
|
|
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.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import com.hxzkoa.json.tb_achor;
|
import com.hxzkoa.json.tb_realocation;
|
import com.hxzkoa.json.tb_tag;
|
import com.hxzkoa.json.tb_track;
|
import com.hxzkoa.util.Config;
|
import com.hxzkoa.util.ModifyConfig;
|
|
@Service
|
public class LocationService {
|
@PersistenceContext
|
private EntityManager em;
|
@Autowired
|
private CaozuoService cs;
|
|
public List<tb_realocation> getRealTimeLocation(String date) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_track_" +date+ "";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_realocation realocation = new tb_realocation();
|
Object[] obj = (Object[]) resultList.get(i);
|
realocation.setId((int) obj[0]);
|
realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
|
realocation.setX(obj[2] == null ? "" : obj[2].toString());
|
realocation.setY(obj[3] == null ? "" : obj[3].toString());
|
realocation.setZ(obj[4] == null ? "" : obj[4].toString());
|
realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
|
realocation.setTime(obj[6] == null ? "" : obj[6].toString());
|
tb_realocationList.add(realocation);
|
}
|
}
|
return tb_realocationList;
|
}
|
|
public List<tb_realocation> getRealTimeLocation(int page,int perPage) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_realocation LIMIT :start,:end";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("start", (page - 1) * perPage);
|
query.setParameter("end", perPage);
|
List resultList = query.getResultList();
|
List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_realocation realocation = new tb_realocation();
|
Object[] obj = (Object[]) resultList.get(i);
|
realocation.setId((int) obj[0]);
|
realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
|
realocation.setX(obj[2] == null ? "" : obj[2].toString());
|
realocation.setY(obj[3] == null ? "" : obj[3].toString());
|
realocation.setZ(obj[4] == null ? "" : obj[4].toString());
|
realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
|
realocation.setTime(obj[6] == null ? "" : obj[6].toString());
|
tb_realocationList.add(realocation);
|
}
|
}
|
return tb_realocationList;
|
}
|
|
public int getRealTimeLocationCount(String date) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_track_" +date+ "";
|
query = this.em.createNativeQuery(sql);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public List<tb_realocation> searchRealTimeLocation(String input,String date) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT t.id,t.tagid,t.x,t.y,t.z,t.layer,t.time,t.baoliu1,t.baoliu2,t.baoliu3,t.baoliu4 FROM tb_track_" +date+ " t,tb_person p WHERE t.tagid = p.p_tagid AND p.p_name = :p_tagid OR t.tagid = p.p_tagid AND t.tagid = :p_tagid";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_tagid", input);
|
List resultList = query.getResultList();
|
List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_realocation realocation = new tb_realocation();
|
Object[] obj = (Object[]) resultList.get(i);
|
realocation.setId((int) obj[0]);
|
realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
|
realocation.setX(obj[2] == null ? "" : obj[2].toString());
|
realocation.setY(obj[3] == null ? "" : obj[3].toString());
|
realocation.setZ(obj[4] == null ? "" : obj[4].toString());
|
realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
|
realocation.setTime(obj[6] == null ? "" : obj[6].toString());
|
realocation.setFangkeid(obj[7] == null ? "" : obj[7].toString());
|
realocation.setJingdu(obj[8] == null ? "" : obj[8].toString());
|
realocation.setWeidu(obj[9] == null ? "" : obj[9].toString());
|
realocation.setGaocheng(obj[10] == null ? "" : obj[10].toString());
|
tb_realocationList.add(realocation);
|
}
|
}
|
return tb_realocationList;
|
}
|
|
public int searchtablenameLocation(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT COUNT(`TABLE_NAME`) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'hxzkuwb' AND `TABLE_NAME` = :tablename";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tablename", input);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
@Transactional
|
public void realTimeLocation_delete(String[] checkVal,String date) {
|
String sql = null;
|
Query query = null;
|
for (int i = 0; i < checkVal.length; i++) {
|
int id = Integer.parseInt(checkVal[i]);
|
sql = "DELETE FROM tb_track_" +date+ " WHERE id = :id";
|
cs.tb_caozuo("tb_realocation", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("id", id);
|
query.executeUpdate();
|
}
|
}
|
|
@Transactional
|
public int realTimeLocation_deletea(String[] checkVal,String date) {
|
String sql = null;
|
Query query = null;
|
int aaa = checkVal.length;
|
for (int i = 0; i < checkVal.length; i++) {
|
int id = Integer.parseInt(checkVal[i]);
|
sql = "DELETE FROM tb_track_" +date+ " WHERE id = :id";
|
cs.tb_caozuo("tb_realocation", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("id", id);
|
query.executeUpdate();
|
}
|
return aaa;
|
}
|
|
@Transactional
|
public void realTimeLocation_deleteAll(String date) {
|
String sql = null;
|
Query query = null;
|
sql = "DELETE FROM tb_track_" +date+ "";
|
query = this.em.createNativeQuery(sql);
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int realTimeLocation_deleteAlla(String date) {
|
String sql = null;
|
Query query = null;
|
sql = "DELETE FROM tb_track_" +date+ "";
|
query = this.em.createNativeQuery(sql);
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public int track_add(String date,tb_track track) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_track_"+date+"(tagid,x,y,z,layer,time) VALUES(:tag_id,:x,:y,:z,:layer,now())";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", track.getTagid());
|
query.setParameter("x", track.getX());
|
query.setParameter("y", track.getY());
|
query.setParameter("z", track.getZ());
|
query.setParameter("layer", track.getLayer());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
public List<tb_track> getTrack(String date,int page,int perPage) {
|
// 日期格式 例20210101
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_track_"+ date +" LIMIT :start,:end";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("start", (page - 1) * perPage);
|
query.setParameter("end", perPage);
|
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 track = new tb_track();
|
Object[] obj = (Object[]) resultList.get(i);
|
track.setId((int) obj[0]);
|
track.setTagid(obj[1] == null ? "" : obj[1].toString());
|
track.setX(obj[2] == null ? "" : obj[2].toString());
|
track.setY(obj[3] == null ? "" : obj[3].toString());
|
track.setZ(obj[4] == null ? "" : obj[4].toString());
|
track.setLayer(obj[5] == null ? "" : obj[5].toString());
|
track.setTime(obj[6] == null ? "" : obj[6].toString());
|
tb_trackList.add(track);
|
}
|
}
|
return tb_trackList;
|
}
|
|
public List<tb_track> getTracka(String date) {
|
// 日期格式 例20210101
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,x,y,z,layer,time,baoliu1,baoliu2,baoliu3,baoliu4 FROM tb_track_"+ date +"";
|
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 track = new tb_track();
|
Object[] obj = (Object[]) resultList.get(i);
|
track.setId((int) obj[0]);
|
track.setTagid(obj[1] == null ? "" : obj[1].toString());
|
track.setX(obj[2] == null ? "" : obj[2].toString());
|
track.setY(obj[3] == null ? "" : obj[3].toString());
|
track.setZ(obj[4] == null ? "" : obj[4].toString());
|
track.setLayer(obj[5] == null ? "" : obj[5].toString());
|
track.setTime(obj[6] == null ? "" : obj[6].toString());
|
track.setFangkeid(obj[7] == null ? "" : obj[7].toString());
|
track.setJingdu(obj[8] == null ? "" : obj[8].toString());
|
track.setWeidu(obj[9] == null ? "" : obj[9].toString());
|
track.setGaocheng(obj[10] == null ? "" : obj[10].toString());
|
tb_trackList.add(track);
|
}
|
}
|
return tb_trackList;
|
}
|
|
public List<String> getTrackDateList() {
|
String sql = null;
|
Query query = null;
|
sql = "select right(table_name,8) from information_schema.tables where table_schema='hxzkuwb' and table_name like 'tb_track_%'";
|
query = this.em.createNativeQuery(sql);
|
List<String> resultList = query.getResultList();
|
return resultList;
|
}
|
|
@Transactional
|
public void tag_time(tb_tag tag) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET addtime=:addtime WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("addtime", tag.getAddtime());
|
query.setParameter("tag_id", tag.getTag_id());
|
query.executeUpdate();
|
}
|
|
public List<String> panduanweiyi(String name){
|
String sql = null;
|
Query query = null;
|
sql = "select p_tagid from tb_person where p_name = :name";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("name", name);
|
List resultlist = query.getResultList();
|
List<String> strs = new ArrayList<>();
|
for (int i=0; i<resultlist.size(); i++) {
|
strs.add(resultlist.get(i).toString());
|
}
|
return strs;
|
}
|
}
|
