package com.hxzkoa.services;
|
|
import java.util.ArrayList;
|
import java.util.Collections;
|
import java.util.Comparator;
|
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_adress_and_port;
|
import com.hxzkoa.json.tb_history_power;
|
import com.hxzkoa.json.tb_permission;
|
import com.hxzkoa.json.tb_person;
|
import com.hxzkoa.json.tb_tag;
|
import com.hxzkoa.json.tb_tagpower;
|
import com.hxzkoa.json.vo_tp_t_p;
|
import com.hxzkoa.util.Config;
|
import com.hxzkoa.util.ModifyConfig;
|
|
@Service
|
public class LabelService {
|
@PersistenceContext
|
private EntityManager em;
|
@Autowired
|
private CaozuoService cs;
|
|
public List<tb_tag> getLabelManagement() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,status,gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu FROM tb_tag ORDER BY id DESC";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_tag> getLabelManagement(int page) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,status,gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM (SELECT id,tag_id,state,power,status,gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM tb_tag ORDER BY id DESC) s LIMIT :start,:end";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("start", (page - 1) * Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage")));
|
query.setParameter("end", 50);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_tag> getLabelManagementa() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,status,gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM (SELECT id,tag_id,state,power,status,gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM tb_tag ORDER BY id DESC) s";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_person> getLabelManagementa2() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,p_tagid,p_name,p_fencename,p_department,baoliu4,baoliu16 FROM (SELECT id,p_tagid,p_name,p_fencename,p_department,baoliu4,baoliu16 FROM tb_person ORDER BY id DESC) s";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_person> tb_personList = new ArrayList<tb_person>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_person person = new tb_person();
|
person.setId((int) obj[0]);
|
person.setP_tagid(obj[1] == null ? "" : obj[1].toString());
|
person.setP_name(obj[2] == null ? "" : obj[2].toString());
|
person.setP_fencename(obj[3] == null ? "" : obj[3].toString());
|
person.setP_department(obj[4] == null ? "" : obj[4].toString());
|
person.setBaoliu4(obj[5] == null ? "" : obj[5].toString());
|
person.setBaoliu12(obj[6] == null ? "" : obj[6].toString());
|
tb_personList.add(person);
|
}
|
}
|
return tb_personList;
|
}
|
|
public List<tb_tag> searchLabelManagement(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,'status',gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM tb_tag WHERE tag_id = :tag_id OR state = :tag_id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", input);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_tag> searchLabelManagementaa(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,'status',gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM tb_tag WHERE tag_id = :tag_id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", input);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public int getLabelManagementCount() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_tag";
|
query = this.em.createNativeQuery(sql);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public int getdepartmentCount(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_person WHERE p_department = :department";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("department", input);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public int getdepartmentCount2(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_person WHERE p_department = :department and p_online = 1";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("department", input);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public List<String> getLabelManagementTagid() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT DISTINCT tag_id FROM tb_tag";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<String> tb_tagList = new ArrayList<String>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
String tag_id = (String) resultList.get(i);
|
tb_tagList.add(tag_id);
|
}
|
}
|
return tb_tagList;
|
}
|
|
@Transactional
|
public int labelManagement_add(tb_tag tag) {
|
//默认值逻辑处理
|
tag.setStatus("1");
|
tag.setPower("100");
|
if(("").equals(tag.getState())){
|
tag.setState("未绑定");
|
}
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("1000");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_tag(status,tag_id,state,power,gaodu,sudu,pinglv,version,addtime) VALUES(:status,:tag_id,:state,:power,:gaodu,:sudu,:pinglv,:version,now())";
|
cs.tb_caozuo("tb_tag", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("status", tag.getStatus());
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("state", tag.getState());
|
query.setParameter("power", tag.getPower());
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.setParameter("version", tag.getVersion());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int anchorManagement_modifyaa(tb_achor achor) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_achor SET baoliu6=:baoliu6,baoliu7=:baoliu7 WHERE anchorid=:anchorid";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("anchorid", achor.getAnchorid());
|
query.setParameter("baoliu6", achor.getBaoliu6());
|
query.setParameter("baoliu7", achor.getBaoliu7());
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public int updatetagname(tb_tag tag) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET state=:state WHERE tag_id=:tag_id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("state", tag.getState());
|
query.setParameter("tag_id", tag.getTag_id());
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public int labelManagement_adda(tb_tag tag) {
|
//默认值逻辑处理
|
tag.setStatus("1");
|
tag.setPower("100");
|
if(("").equals(tag.getState())){
|
tag.setState("未绑定");
|
}
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("1000");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "INSERT INTO tb_tag(status,tag_id,state,power,gaodu,sudu,pinglv,version,addtime) VALUES(:status,:tag_id,:state,:power,:gaodu,:sudu,:pinglv,:version,now())";
|
cs.tb_caozuo("tb_tag", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("status", tag.getStatus());
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("state", tag.getState());
|
query.setParameter("power", tag.getPower());
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.setParameter("version", tag.getVersion());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public void labelManagement_modify(tb_tag tag) {
|
//默认值逻辑处理
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("无");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET state=:state,gaodu=:gaodu,sudu=:sudu,pinglv=:pinglv,type=:type WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("state", tag.getState());
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.setParameter("type", tag.getType());
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int labelManagement_modifytag(tb_tag tag) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET state=:state WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("state", tag.getState());
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public void labelManagement_modifyperson(tb_person person) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_person SET p_name=:p_name,baoliu1=:baoliu1,p_idcardnum=:p_idcardnum WHERE p_tagid=:p_tagid";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_tagid", person.getP_tagid());
|
query.setParameter("p_name", person.getP_name());
|
// query.setParameter("p_sex", person.getP_sex());
|
// query.setParameter("p_minzu", person.getP_minzu());
|
query.setParameter("baoliu1", person.getP_fangkeid());
|
query.setParameter("p_idcardnum", person.getP_idcardnum());
|
// query.setParameter("p_adress", person.getP_adress());
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int labelManagement_modifypersonaa(tb_person person) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT into tb_fangke(userid,name,idcardnum) value(:userid,:name,:idcardnum)";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("userid", person.getP_tagid());
|
query.setParameter("name", person.getP_name());
|
query.setParameter("idcardnum", person.getP_idcardnum());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int labelManagement_modifya(tb_tag tag) {
|
//默认值逻辑处理
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("无");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET state=:state,gaodu=:gaodu,sudu=:sudu,pinglv=:pinglv,type=:type WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("state", tag.getState());
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.setParameter("type", tag.getType());
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public void labelManagement_modifyAll(tb_tag tag) {
|
//默认值逻辑处理
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("无");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET gaodu=:gaodu,sudu=:sudu,pinglv=:pinglv";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int labelManagement_modifyAlla(tb_tag tag) {
|
//默认值逻辑处理
|
if(("").equals(tag.getGaodu())){
|
tag.setGaodu("150");
|
}
|
if(("").equals(tag.getSudu())){
|
tag.setSudu("无");
|
}
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET gaodu=:gaodu,sudu=:sudu,pinglv=:pinglv";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("gaodu", tag.getGaodu());
|
query.setParameter("sudu", tag.getSudu());
|
query.setParameter("pinglv", tag.getPinglv());
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public void labelManagement_delete(String[] checkVal) {
|
String sql = null;
|
Query query = null;
|
for (int i = 0; i < checkVal.length; i++) {
|
sql = "DELETE FROM tb_tag WHERE tag_id = :tag_id";
|
cs.tb_caozuo("tb_tag", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", checkVal[i]);
|
query.executeUpdate();
|
}
|
}
|
|
@Transactional
|
public int labelManagement_deletea(String[] checkVal) {
|
String sql = null;
|
Query query = null;
|
int aaa = checkVal.length;
|
for (int i = 0; i < checkVal.length; i++) {
|
sql = "DELETE FROM tb_tag WHERE tag_id = :tag_id";
|
cs.tb_caozuo("tb_tag", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", checkVal[i]);
|
query.executeUpdate();
|
}
|
return aaa;
|
}
|
|
@Transactional
|
public void labelManagement_deleteAll() {
|
String sql = null;
|
Query query = null;
|
sql = "DELETE FROM tb_tag";
|
cs.tb_caozuo("tb_tag", 2);
|
query = this.em.createNativeQuery(sql);
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int labelManagement_deleteAlla() {
|
String sql = null;
|
Query query = null;
|
sql = "DELETE FROM tb_tag";
|
cs.tb_caozuo("tb_tag", 2);
|
query = this.em.createNativeQuery(sql);
|
int aaa = query.executeUpdate();
|
return aaa;
|
}
|
|
@Transactional
|
public void labelManagement_state(String p_name, String tag_id) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET state=:p_name WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_name", p_name);
|
query.setParameter("tag_id", tag_id);
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public void labelManagement_more(tb_tag tag) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET sleep_satus=:sleep_satus,sleep_time=:sleep_time,gongfang=:gongfang,pinglv=:pinglv,imu=:imu,dong_status=:dong_status WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("sleep_satus", tag.getSleep_satus());
|
query.setParameter("sleep_time", tag.getSleep_time());
|
query.setParameter("gongfang", tag.getGongfang());
|
query.setParameter("pinglv", tag.getPinglv());
|
query.setParameter("imu", tag.getImu());
|
query.setParameter("dong_status", tag.getDong_status());
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public int labelDeliver_add(tb_person person) {
|
String sql = null;
|
Query query = null;
|
sql = "INSERT tb_person (p_name,p_tagid,p_sex,p_minzu,p_phone,p_department,p_idcardnum,p_adress,p_sos,p_online,p_power,p_addtiem) VALUES (:p_name,:p_tagid,:p_sex,:p_minzu,:p_phone,:p_department,:p_idcardnum,:p_adress,:p_sos,:p_online,:p_power,now())";
|
cs.tb_caozuo("tb_person", 1);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_name", person.getP_name());
|
query.setParameter("p_tagid", person.getP_tagid());
|
query.setParameter("p_sex", person.getP_sex());
|
query.setParameter("p_minzu", person.getP_minzu());
|
query.setParameter("p_phone", person.getP_phone());
|
query.setParameter("p_department", person.getP_department());
|
query.setParameter("p_idcardnum", person.getP_idcardnum());
|
query.setParameter("p_adress", person.getP_adress());
|
query.setParameter("p_sos", person.getP_sos());
|
query.setParameter("p_online", person.getP_online());
|
query.setParameter("p_power", person.getP_power());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int labelDeliver_modify(tb_person person) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_person SET p_name = :p_name,p_department = :p_department,p_sex = :p_sex,p_minzu = :p_minzu,p_phone = :p_phone,p_idcardnum = :p_idcardnum,p_adress = :p_adress,p_addtiem = now() WHERE p_tagid= :p_tagid";
|
cs.tb_caozuo("tb_person", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_name", person.getP_name());
|
query.setParameter("p_department", person.getP_department());
|
query.setParameter("p_sex", person.getP_sex());
|
query.setParameter("p_minzu", person.getP_minzu());
|
query.setParameter("p_phone", person.getP_phone());
|
query.setParameter("p_idcardnum", person.getP_idcardnum());
|
query.setParameter("p_adress", person.getP_adress());
|
query.setParameter("p_tagid", person.getP_tagid());
|
int executeUpdate = query.executeUpdate();
|
return executeUpdate;
|
}
|
|
@Transactional
|
public int labelDeliver_exist(String p_tagid) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_person WHERE p_tagid= :p_tagid";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("p_tagid", p_tagid);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public List<tb_adress_and_port> getAdress_and_port() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT name,adress,port,type,state,baoliu,baoliu2,baoliu3,addtime FROM tb_adress_and_port";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_adress_and_port> tb_adress_and_portList = new ArrayList<tb_adress_and_port>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_adress_and_port adress_and_port = new tb_adress_and_port();
|
adress_and_port.setName(obj[0] == null ? "" : obj[0].toString());
|
adress_and_port.setAdress(obj[1] == null ? "" : obj[1].toString());
|
adress_and_port.setPort(obj[2] == null ? "" : obj[2].toString());
|
adress_and_port.setType(obj[3] == null ? "" : obj[3].toString());
|
adress_and_port.setState(obj[4] == null ? "" : obj[4].toString());
|
adress_and_port.setBaoliu(obj[5] == null ? "" : obj[5].toString());
|
adress_and_port.setBaoliu2(obj[6] == null ? "" : obj[6].toString());
|
adress_and_port.setBaoliu3(obj[7] == null ? "" : obj[7].toString());
|
adress_and_port.setAddtime(obj[8] == null ? "" : obj[8].toString());
|
tb_adress_and_portList.add(adress_and_port);
|
}
|
}
|
return tb_adress_and_portList;
|
}
|
|
@Transactional
|
public void updateAdress_and_port(tb_adress_and_port port) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_adress_and_port SET baoliu=:baoliu,baoliu2=:baoliu2 WHERE name=:name";
|
cs.tb_caozuo("tb_adress_and_port", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("baoliu", port.getBaoliu());
|
query.setParameter("baoliu2", port.getBaoliu2());
|
query.setParameter("name", port.getName());
|
query.executeUpdate();
|
}
|
|
public List<vo_tp_t_p> getRealTimePower() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT tp.id,tp.tagid,t.power,tp.time,t.pinglv,p.p_name FROM tb_tagpower tp,tb_tag t,tb_person p WHERE tp.tagid = t.tag_id AND tp.tagid = p.p_tagid";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<vo_tp_t_p> vo_tp_t_pList = new ArrayList<vo_tp_t_p>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
vo_tp_t_p tp_t_p = new vo_tp_t_p();
|
Object[] obj = (Object[]) resultList.get(i);
|
tp_t_p.setTagid(obj[1] == null ? "" : obj[1].toString());
|
tp_t_p.setPower(obj[2] == null ? "" : obj[2].toString());
|
tp_t_p.setTime(obj[3] == null ? "" : obj[3].toString());
|
tp_t_p.setPinglv(obj[4] == null ? "" : obj[4].toString());
|
tp_t_p.setP_name(obj[5] == null ? "" : obj[5].toString());
|
vo_tp_t_pList.add(tp_t_p);
|
}
|
}
|
return vo_tp_t_pList;
|
}
|
|
public List<tb_tagpower> getRealTimePower(int page) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT * FROM (SELECT * FROM tb_tagpower ORDER BY time DESC) tp LIMIT :start,:end";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("start", (page - 1) * Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage")));
|
query.setParameter("end", 50);
|
List resultList = query.getResultList();
|
List<tb_tagpower> tagpowerList = new ArrayList<tb_tagpower>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_tagpower tagpower = new tb_tagpower();
|
Object[] obj = (Object[]) resultList.get(i);
|
tagpower.setTagid(obj[1] == null ? "" : obj[1].toString());
|
tagpower.setPower(obj[2] == null ? "" : obj[2].toString());
|
tagpower.setTime(obj[3] == null ? "" : obj[3].toString());
|
tagpower.setHz(obj[4] == null ? "" : obj[4].toString());
|
tagpower.setName(obj[5] == null ? "" : obj[5].toString());
|
tagpowerList.add(tagpower);
|
}
|
}
|
return tagpowerList;
|
}
|
|
public int getRealTimePowerCount() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_tagpower tp,tb_tag t,tb_person p WHERE tp.tagid = t.tag_id AND tp.tagid = p.p_tagid";
|
query = this.em.createNativeQuery(sql);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public List<vo_tp_t_p> searchRealTimePower(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT tp.id,tp.tagid,tp.power,tp.time,tp.hz,p.p_name FROM tb_tagpower tp,tb_tag t,tb_person p WHERE tp.tagid = t.tag_id AND tp.tagid = p.p_tagid AND tp.tagid = :tag_id OR tp.`name` = t.state AND tp.`name` = p.p_name AND tp.`name` = :tag_id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", input);
|
List resultList = query.getResultList();
|
List<vo_tp_t_p> vo_tp_t_pList = new ArrayList<vo_tp_t_p>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
vo_tp_t_p tp_t_p = new vo_tp_t_p();
|
Object[] obj = (Object[]) resultList.get(i);
|
tp_t_p.setTagid(obj[1] == null ? "" : obj[1].toString());
|
tp_t_p.setPower(obj[2] == null ? "" : obj[2].toString());
|
tp_t_p.setTime(obj[3] == null ? "" : obj[3].toString());
|
tp_t_p.setPinglv(obj[4] == null ? "" : obj[4].toString());
|
tp_t_p.setP_name(obj[5] == null ? "" : obj[5].toString());
|
vo_tp_t_pList.add(tp_t_p);
|
}
|
}
|
return vo_tp_t_pList;
|
}
|
|
public List<tb_history_power> getHistoricalPower() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,power,time FROM tb_history_power";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<tb_history_power> tb_history_powerList = new ArrayList<tb_history_power>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_history_power history_power = new tb_history_power();
|
Object[] obj = (Object[]) resultList.get(i);
|
history_power.setId((int) obj[0]);
|
history_power.setTagid(obj[1] == null ? "" : obj[1].toString());
|
history_power.setPower(obj[2] == null ? "" : obj[2].toString());
|
history_power.setTime(obj[3] == null ? "" : obj[3].toString());
|
tb_history_powerList.add(history_power);
|
}
|
}
|
return tb_history_powerList;
|
}
|
|
public List<tb_history_power> getHistoricalPower(int page) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,power,time FROM (SELECT * FROM tb_history_power ORDER BY time DESC) thp LIMIT :start,:end";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("start", (page - 1) * Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage")));
|
query.setParameter("end", 50);
|
List resultList = query.getResultList();
|
List<tb_history_power> tb_history_powerList = new ArrayList<tb_history_power>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_history_power history_power = new tb_history_power();
|
Object[] obj = (Object[]) resultList.get(i);
|
history_power.setId((int) obj[0]);
|
history_power.setTagid(obj[1] == null ? "" : obj[1].toString());
|
history_power.setPower(obj[2] == null ? "" : obj[2].toString());
|
history_power.setTime(obj[3] == null ? "" : obj[3].toString());
|
tb_history_powerList.add(history_power);
|
}
|
}
|
return tb_history_powerList;
|
}
|
|
public int getHistoricalPowerCount() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_history_power";
|
query = this.em.createNativeQuery(sql);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
|
public List<tb_history_power> searchHistoricalPower(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT h.id,h.tagid,h.power,h.time FROM tb_history_power h,tb_person p WHERE h.tagid = p.p_tagid AND h.tagid = :tag_id OR h.tagid = p.p_tagid AND p.p_name = :tag_id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", input);
|
List resultList = query.getResultList();
|
List<tb_history_power> tb_history_powerList = new ArrayList<tb_history_power>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_history_power history_power = new tb_history_power();
|
Object[] obj = (Object[]) resultList.get(i);
|
history_power.setId((int) obj[0]);
|
history_power.setTagid(obj[1] == null ? "" : obj[1].toString());
|
history_power.setPower(obj[2] == null ? "" : obj[2].toString());
|
history_power.setTime(obj[3] == null ? "" : obj[3].toString());
|
tb_history_powerList.add(history_power);
|
}
|
}
|
return tb_history_powerList;
|
}
|
|
public List<tb_history_power> searchnameHistoricalPower(String begin,String end,String tagida) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tagid,power,time FROM tb_history_power WHERE tagid = :tagida AND time>= :begin AND time<= :end ORDER BY power";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("begin", begin);
|
query.setParameter("end", end);
|
query.setParameter("tagida", tagida);
|
List resultList = query.getResultList();
|
List<tb_history_power> tb_history_powerList = new ArrayList<tb_history_power>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_history_power history_power = new tb_history_power();
|
Object[] obj = (Object[]) resultList.get(i);
|
history_power.setId((int) obj[0]);
|
history_power.setTagid(obj[1] == null ? "" : obj[1].toString());
|
history_power.setPower(obj[2] == null ? "" : obj[2].toString());
|
history_power.setTime(obj[3] == null ? "" : obj[3].toString());
|
tb_history_powerList.add(history_power);
|
}
|
}
|
return tb_history_powerList;
|
}
|
|
public List<tb_history_power> searchPowerAnalysis(String input) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT tagid,max(power),substring(time,6,5) date FROM tb_history_power WHERE tagid = :tag_id GROUP BY tagid,date";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", input);
|
List resultList = query.getResultList();
|
List<tb_history_power> tb_history_powerList = new ArrayList<tb_history_power>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
tb_history_power history_power = new tb_history_power();
|
Object[] obj = (Object[]) resultList.get(i);
|
history_power.setTagid(obj[0] == null ? "" : obj[0].toString());
|
history_power.setPower(obj[1] == null ? "" : obj[1].toString());
|
history_power.setTime(obj[2] == null ? "" : obj[2].toString());
|
tb_history_powerList.add(history_power);
|
}
|
}
|
return tb_history_powerList;
|
}
|
|
@Transactional
|
public void historicalPower_delete(String[] checkVal) {
|
String sql = null;
|
Query query = null;
|
for (int i = 0; i < checkVal.length; i++) {
|
int id = Integer.parseInt(checkVal[i]);
|
sql = "DELETE FROM tb_history_power WHERE id = :id";
|
cs.tb_caozuo("tb_history_power", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("id", id);
|
query.executeUpdate();
|
}
|
}
|
|
@Transactional
|
public void historicalPower_deleteAll() {
|
String sql = null;
|
Query query = null;
|
sql = "DELETE FROM tb_history_power";
|
cs.tb_caozuo("tb_history_power", 2);
|
query = this.em.createNativeQuery(sql);
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public void label_bw_power(tb_tag tag) {
|
String sql = null;
|
Query query = null;
|
sql = "UPDATE tb_tag SET power=:power WHERE tag_id=:tag_id";
|
cs.tb_caozuo("tb_tag", 3);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", tag.getTag_id());
|
query.setParameter("power", tag.getPower());
|
query.executeUpdate();
|
}
|
|
@Transactional
|
public void labelperson_delete(String[] checkVal) {
|
String sql = null;
|
Query query = null;
|
for (int i = 0; i < checkVal.length; i++) {
|
sql = "DELETE FROM tb_person WHERE p_tagid = :tag_id";
|
cs.tb_caozuo("tb_person", 2);
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tag_id", checkVal[i]);
|
query.executeUpdate();
|
}
|
}
|
|
public List<tb_tag> searchidLabelManagement(String id) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,'status',gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu FROM tb_tag WHERE id = :id";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("id", id);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public String findpname(String tagid){
|
String sql = null;
|
Query query = null;
|
sql = "select p_name from tb_person where p_tagid = :tagid";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("tagid", tagid);
|
List resultList = query.getResultList();
|
return resultList.get(0).toString();
|
}
|
|
public String findptagid(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();
|
return resultList.get(0).toString();
|
}
|
|
public List<String> findleixing(){
|
String sql = null;
|
Query query = null;
|
sql = "select type from tb_tag where type is not null group by type";
|
query = this.em.createNativeQuery(sql);
|
List resultlist = query.getResultList();
|
List<String> strs = new ArrayList<>();
|
for (int i=0; i<resultlist.size(); i++) {
|
// Object[] obj = (Object[])resultlist.get(i);
|
strs.add(resultlist.get(i).toString());
|
}
|
return strs;
|
}
|
|
public List<tb_tag> shailabel1(String leixing,String bumen) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT t.id,t.tag_id,t.state,t.power,t.status,t.gaodu,t.sudu,t.pinglv,t.sleep_satus,t.sleep_time,t.gongfang,t.dong_status,t.version,t.addtime,t.imu,t.type FROM tb_tag t,tb_person p WHERE p.p_tagid = t.tag_id and p.p_department = :bumen and t.type = :leixing";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("leixing", leixing);
|
query.setParameter("bumen", bumen);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_tag> shailabel2(String bumen) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT t.id,t.tag_id,t.state,t.power,t.status,t.gaodu,t.sudu,t.pinglv,t.sleep_satus,t.sleep_time,t.gongfang,t.dong_status,t.version,t.addtime,t.imu,t.type FROM tb_tag t,tb_person p WHERE p.p_tagid = t.tag_id and p.p_department = :bumen";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("bumen", bumen);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public List<tb_tag> shailabel3(String leixing) {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT id,tag_id,state,power,'status',gaodu,sudu,pinglv,sleep_satus,sleep_time,gongfang,dong_status,version,addtime,imu,type FROM tb_tag WHERE type = :leixing";
|
query = this.em.createNativeQuery(sql);
|
query.setParameter("leixing", leixing);
|
List resultList = query.getResultList();
|
List<tb_tag> tb_tagList = new ArrayList<tb_tag>();
|
if (resultList.size() > 0) {
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
tb_tag tag = new tb_tag();
|
tag.setId((int) obj[0]);
|
tag.setStatus(obj[4] == null ? "" : obj[4].toString());
|
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.setGaodu(obj[5] == null ? "" : obj[5].toString());
|
tag.setSudu(obj[6] == null ? "" : obj[6].toString());
|
tag.setPinglv(obj[7] == null ? "" : obj[7].toString());
|
tag.setVersion(obj[12] == null ? "" : obj[12].toString());
|
tag.setAddtime(obj[13] == null ? "" : obj[13].toString());
|
tag.setType(obj[15] == null ? "" : obj[15].toString());
|
tb_tagList.add(tag);
|
}
|
}
|
return tb_tagList;
|
}
|
|
public int getweibangding() {
|
String sql = null;
|
Query query = null;
|
sql = "SELECT count(1) FROM tb_person where p_name = '未绑定'";
|
query = this.em.createNativeQuery(sql);
|
return Integer.parseInt(query.getSingleResult().toString());
|
}
|
}
|
