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());
	}
}