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 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_collector;
import com.hxzkoa.json.tb_department;
import com.hxzkoa.json.tb_icon;
import com.hxzkoa.json.tb_kaoqing;
import com.hxzkoa.json.tb_person;
import com.hxzkoa.json.tb_realkaoqing;
import com.hxzkoa.json.tb_tag;
import com.hxzkoa.util.Config;
import com.hxzkoa.util.ModifyConfig;

@Service
public class BasicInfoService {
	@PersistenceContext
	private EntityManager em;
	@Autowired
	private CaozuoService cs;

	public List<tb_icon> getIconManagement(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT Serial_number,icon_name,icon_adress,add_time,beizhu FROM tb_icon LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		List resultList = query.getResultList();
		List<tb_icon> tb_iconList = new ArrayList<tb_icon>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_icon icon = new tb_icon();
				Object[] obj = (Object[]) resultList.get(i);
				icon.setSerial_number((int) obj[0]);
				icon.setIcon_name(obj[1] == null ? "" : obj[1].toString());
				icon.setIcon_adress(obj[2] == null ? "" : obj[2].toString());
				icon.setAdd_time(obj[3] == null ? "" : obj[3].toString());
				icon.setBeizhu(obj[4] == null ? "" : obj[4].toString());
				tb_iconList.add(icon);
			}
		}
		return tb_iconList;
	}

	public int getIconManagementCount() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_icon";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@Transactional
	public int iconManagement_add(tb_icon icon) {
		String sql = null;
		Query query = null;
		sql = "INSERT INTO tb_icon( icon_name,icon_adress,add_time,beizhu) VALUES(:icon_name,:icon_adress,now(),:beizhu)";
		cs.tb_caozuo("tb_icon", 1);
		query = this.em.createNativeQuery(sql);
		query.setParameter("icon_name", icon.getIcon_name());
		query.setParameter("icon_adress", icon.getIcon_adress());
		query.setParameter("beizhu", icon.getBeizhu());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	@Transactional
	public int iconManagement_check(String iconName) {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_icon WHERE icon_Name= :iconName";
		query = this.em.createNativeQuery(sql);
		query.setParameter("iconName", iconName);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@Transactional
	public void iconManagement_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_icon WHERE Serial_number = :id";
			cs.tb_caozuo("tb_icon", 2);
			query = this.em.createNativeQuery(sql);
			query.setParameter("id", id);
			query.executeUpdate();
		}
	}

	public List<tb_department> getDepartmentManagement() {
		String sql = null;
		Query query = null;
		sql = "SELECT id,departmentName,iconadress,addtime FROM tb_department";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setId((int) obj[0]);
				department.setDepartmentName(obj[1] == null ? "" : obj[1].toString());
				department.setIconadress(obj[2] == null ? "" : obj[2].toString());
				department.setAddtime(obj[3] == null ? "" : obj[3].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}

	public List<tb_department> getDepartmentManagement(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT id,departmentName,iconadress,addtime FROM tb_department LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setId((int) obj[0]);
				department.setDepartmentName(obj[1] == null ? "" : obj[1].toString());
				department.setIconadress(obj[2] == null ? "" : obj[2].toString());
				department.setAddtime(obj[3] == null ? "" : obj[3].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}
	
	public List<tb_department> getDepartmentManagement2() {
		String sql = null;
		Query query = null;
		sql = "SELECT id,departmentName,iconadress,addtime FROM tb_department";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setId((int) obj[0]);
				department.setDepartmentName(obj[1] == null ? "" : obj[1].toString());
				department.setIconadress(obj[2] == null ? "" : obj[2].toString());
				department.setAddtime(obj[3] == null ? "" : obj[3].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}

	public List<tb_icon> getIconList() {
		String sql = null;
		Query query = null;
		sql = "SELECT icon_name,icon_adress FROM tb_icon";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_icon> tb_iconList = new ArrayList<tb_icon>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_icon icon = new tb_icon();
				Object[] obj = (Object[]) resultList.get(i);
				icon.setIcon_name(obj[0] == null ? "" : obj[0].toString());
				icon.setIcon_adress(obj[1] == null ? "" : obj[1].toString());
				tb_iconList.add(icon);
			}
		}
		return tb_iconList;
	}

	public List<tb_department> getAllDepartmentManagement() {
		String sql = null;
		Query query = null;
		sql = "SELECT id,departmentName,iconadress,addtime FROM tb_department";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setId((int) obj[0]);
				department.setDepartmentName(obj[1] == null ? "" : obj[1].toString());
				department.setIconadress(obj[2] == null ? "" : obj[2].toString());
				department.setAddtime(obj[3] == null ? "" : obj[3].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}

	public int getDepartmentManagementCount() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_department";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	public List<tb_department> searchDepartmentManagement(String input) {
		String departmentName = input;
		String sql = null;
		Query query = null;
		sql = "SELECT id,departmentName,iconadress,addtime FROM tb_department WHERE departmentName = :departmentName";
		query = this.em.createNativeQuery(sql);
		query.setParameter("departmentName", departmentName);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setId((int) obj[0]);
				department.setDepartmentName(obj[1] == null ? "" : obj[1].toString());
				department.setIconadress(obj[2] == null ? "" : obj[2].toString());
				department.setAddtime(obj[3] == null ? "" : obj[3].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}

	@Transactional
	public void departmentManagement_delete(String[] checkVal) {
		String sql = null;
		Query query = null;
		for (int i = 0; i < checkVal.length; i++) {
			String departmentName = checkVal[i];
			sql = "DELETE FROM tb_department WHERE departmentName = :departmentName";
			cs.tb_caozuo("tb_department", 2);
			query = this.em.createNativeQuery(sql);
			query.setParameter("departmentName", departmentName);
			query.executeUpdate();
		}
	}

	@Transactional
	public void departmentManagement_deleteAll() {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_department";
		cs.tb_caozuo("tb_department", 2);
		query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}

	@Transactional
	public int departmentManagement_add(tb_department department) {
		String sql = null;
		Query query = null;
		sql = "INSERT INTO tb_department(departmentName,iconadress,addtime) VALUES(:departmentName,:iconadress,now())";
		cs.tb_caozuo("tb_department", 1);
		query = this.em.createNativeQuery(sql);
		query.setParameter("departmentName", department.getDepartmentName());
		query.setParameter("iconadress", department.getIconadress());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	@Transactional
	public int departmentManagement_check(String name) {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_department WHERE departmentName= :departmentName";
		query = this.em.createNativeQuery(sql);
		query.setParameter("departmentName", name);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@Transactional
	public void departmentManagement_modify(tb_department department) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_department SET departmentName=:departmentName,iconadress=:iconadress,addtime = now() WHERE id=:id";
		cs.tb_caozuo("tb_department", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("id", department.getId());
		query.setParameter("departmentName", department.getDepartmentName());
		query.setParameter("iconadress", department.getIconadress());
		query.executeUpdate();
	}

	public List<tb_person> getPersonManagement() {
		String sql = null;
		Query query = null;
		sql = "SELECT id,p_name,p_tagid,p_sex,p_minzu,p_phone,p_department,p_ban,p_zu,p_idcardnum,p_adress,p_canin,p_x,p_y,p_floor,p_sos,p_online,p_power,p_kaoqing,p_fence,p_fencename,p_kaoqqingname,p_image,p_addtiem FROM tb_person";
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_sex(obj[3] == null ? "" : obj[3].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_phone(obj[5] == null ? "" : obj[5].toString());
				person.setP_x(obj[12] == null ? "" : obj[12].toString());
				person.setP_y(obj[13] == null ? "" : obj[13].toString());
				person.setP_floor(obj[14] == null ? "" : obj[14].toString());
				person.setP_online(obj[16] == null ? "" : obj[16].toString());
				person.setP_power(obj[17] == null ? "" : obj[17].toString());
				person.setP_image(obj[22] == null ? "" : obj[22].toString());
				person.setP_addtiem(obj[23] == null ? "" : obj[23].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement2() {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '车载带屏' OR p.p_tagid = t.tag_id AND t.type = '车载无屏'";
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement3() {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '物资标签'";
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement4() {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department,p.p_sex,p.p_phone FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签'";
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_sex(obj[7] == null ? "" : obj[7].toString());
				person.setP_phone(obj[8] == null ? "" : obj[8].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagementTemplate(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT p_name,p_tagid,p_sex,p_phone,p_department FROM tb_person LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setP_name(obj[0] == null ? "" : obj[0].toString());
				person.setP_tagid(obj[1] == null ? "" : obj[1].toString());
				person.setP_sex(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[4] == null ? "" : obj[4].toString());
				person.setP_phone(obj[3] == null ? "" : obj[3].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagementTemplate2(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT p.p_name,p.p_tagid,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '车载带屏' OR p.p_tagid = t.tag_id AND t.type = '车载无屏' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setP_name(obj[0] == null ? "" : obj[0].toString());
				person.setP_tagid(obj[1] == null ? "" : obj[1].toString());
				person.setP_department(obj[2] == null ? "" : obj[2].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagementTemplate3(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT p.p_name,p.p_tagid,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '物资标签' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setP_name(obj[0] == null ? "" : obj[0].toString());
				person.setP_tagid(obj[1] == null ? "" : obj[1].toString());
				person.setP_department(obj[2] == null ? "" : obj[2].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagementTemplate4(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT p.p_name,p.p_tagid,p.p_sex,p.p_phone,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setP_name(obj[0] == null ? "" : obj[0].toString());
				person.setP_tagid(obj[1] == null ? "" : obj[1].toString());
				person.setP_sex(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[4] == null ? "" : obj[4].toString());
				person.setP_phone(obj[3] == null ? "" : obj[3].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}

	public List<tb_person> getPersonManagement(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = perPage;
		String sql = null;
		Query query = null;
		sql = "SELECT id,p_name,p_tagid,p_sex,p_minzu,p_phone,p_department,p_ban,p_zu,p_idcardnum,p_adress,p_canin,p_x,p_y,p_floor,p_sos,p_online,p_power,p_kaoqing,p_fence,p_fencename,p_kaoqqingname,p_image,p_addtiem FROM tb_person LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_sex(obj[3] == null ? "" : obj[3].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_phone(obj[5] == null ? "" : obj[5].toString());
				person.setP_x(obj[12] == null ? "" : obj[12].toString());
				person.setP_y(obj[13] == null ? "" : obj[13].toString());
				person.setP_floor(obj[14] == null ? "" : obj[14].toString());
				person.setP_online(obj[16] == null ? "" : obj[16].toString());
				person.setP_power(obj[17] == null ? "" : obj[17].toString());
				person.setP_image(obj[22] == null ? "" : obj[22].toString());
				person.setP_addtiem(obj[23] == null ? "" : obj[23].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement2(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = 50;
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '车载带屏' OR p.p_tagid = t.tag_id AND t.type = '车载无屏' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement3(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = 50;
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '物资标签' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> getPersonManagement4(int page) {
		int perPage = Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage"));
		int start = (page - 1) * perPage;
		int end = 50;
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department,p.p_sex,p.p_phone FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签' LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_sex(obj[7] == null ? "" : obj[7].toString());
				person.setP_phone(obj[8] == null ? "" : obj[8].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}

	public int getPersonManagementCount() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_person";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}
	
	public int getPersonManagementCount2() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(*) FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '车载带屏' OR p.p_tagid = t.tag_id AND t.type = '车载无屏'";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}
	
	public int getPersonManagementCount3() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(*) FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '物资标签'";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}
	
	public int getPersonManagementCount4() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(*) FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签'";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	public List<tb_person> searchPersonManagement2(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '车载带屏' AND p.p_tagid = :p_tagid OR p.p_tagid = t.tag_id AND t.type = '车载带屏' AND p.p_name = :p_tagid OR p.p_tagid = t.tag_id AND t.type = '车载无屏' AND p.p_tagid = :p_tagid OR p.p_tagid = t.tag_id AND t.type = '车载无屏' AND p.p_name = :p_tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", input);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> searchPersonManagement3(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type = '物资标签' AND p.p_tagid = :p_tagid OR p.p_tagid = t.tag_id AND t.type = '物资标签' AND p.p_name = :p_tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", input);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> searchPersonManagement4(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT p.id,p.p_name,p.p_tagid,p.p_power,p.p_image,p.p_addtiem,p.p_department,p.p_sex,p.p_phone FROM tb_person p,tb_tag t WHERE p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签' AND p.p_tagid = :p_tagid OR p.p_tagid = t.tag_id AND t.type <> '车载带屏' AND t.type <> '车载无屏' AND t.type <> '物资标签' AND p.p_name = :p_tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", input);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_power(obj[3] == null ? "" : obj[3].toString());
				person.setP_image(obj[4] == null ? "" : obj[4].toString());
				person.setP_addtiem(obj[5] == null ? "" : obj[5].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_sex(obj[7] == null ? "" : obj[7].toString());
				person.setP_phone(obj[8] == null ? "" : obj[8].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_person> searchPersonManagement(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT id,p_name,p_tagid,p_sex,p_minzu,p_phone,p_department,p_ban,p_zu,p_idcardnum,p_adress,p_canin,p_x,p_y,p_floor,p_sos,p_online,p_power,p_kaoqing,p_fence,p_fencename,p_kaoqqingname,p_image,p_addtiem FROM tb_person WHERE p_tagid = :p_tagid OR p_name = :p_tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", input);
		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++) {
				tb_person person = new tb_person();
				Object[] obj = (Object[]) resultList.get(i);
				person.setId((int) obj[0]);
				person.setP_name(obj[1] == null ? "" : obj[1].toString());
				person.setP_tagid(obj[2] == null ? "" : obj[2].toString());
				person.setP_sex(obj[3] == null ? "" : obj[3].toString());
				person.setP_department(obj[6] == null ? "" : obj[6].toString());
				person.setP_phone(obj[5] == null ? "" : obj[5].toString());
				person.setP_x(obj[12] == null ? "" : obj[12].toString());
				person.setP_y(obj[13] == null ? "" : obj[13].toString());
				person.setP_floor(obj[14] == null ? "" : obj[14].toString());
				person.setP_power(obj[17] == null ? "" : obj[17].toString());
				person.setP_image(obj[22] == null ? "" : obj[22].toString());
				person.setP_addtiem(obj[23] == null ? "" : obj[23].toString());
				tb_personList.add(person);
			}
		}
		return tb_personList;
	}
	
	public List<tb_tag> searchPersonManagementa(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT * 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_personList = new ArrayList<tb_tag>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_tag tb_tag = new tb_tag();
				Object[] obj = (Object[]) resultList.get(i);
				tb_tag.setId((int) obj[0]);
				tb_tag.setState(obj[2] == null ? "" : obj[2].toString());
				tb_tag.setPower(obj[3] == null ? "" : obj[3].toString());
				tb_personList.add(tb_tag);
			}
		}
		return tb_personList;
	}

	@Transactional
	public int personManagement_delete(String checkVal) {
		String sql = null;
		Query query = null;
		String str = "未绑定";
		sql = "UPDATE tb_person SET p_name=:str WHERE p_tagid = :tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("tagid", checkVal);
		query.setParameter("str", str);
		query.executeUpdate();
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

//	@Transactional
//	public int personManagement_deleteAll() {
//		String sql = null;
//		Query query = null;
//		String str = "未绑定";
//		sql = "UPDATE tb_person SET p_name=:str";
//		cs.tb_caozuo("tb_person", 3);
//		query = this.em.createNativeQuery(sql);
//		query.setParameter("str", str);
//		query.executeUpdate();
//		int executeUpdate = query.executeUpdate();
//		return executeUpdate;
//	}
	
	@Transactional
	public int personManagement_deleteAll() {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_person";
		query = this.em.createNativeQuery(sql);
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	public List<tb_department> getDepartmentList() {
		String sql = null;
		Query query = null;
		sql = "SELECT departmentName,iconadress FROM tb_department";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_department> tb_departmentList = new ArrayList<tb_department>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_department department = new tb_department();
				Object[] obj = (Object[]) resultList.get(i);
				department.setDepartmentName(obj[0] == null ? "" : obj[0].toString());
				department.setIconadress(obj[1] == null ? "" : obj[1].toString());
				tb_departmentList.add(department);
			}
		}
		return tb_departmentList;
	}

	@Transactional
	public int personManagement_modify(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_image = (SELECT iconadress FROM tb_department WHERE departmentName = :p_department LIMIT 1),p_power=(SELECT power FROM tb_tag WHERE tag_id = :p_tagid LIMIT 1),p_department = :p_department,p_name = :p_name,p_sex = :p_sex,p_phone = :p_phone,p_addtiem = now() WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_department", person.getP_department());
		query.setParameter("p_name", person.getP_name());
		query.setParameter("p_sex", person.getP_sex());
		query.setParameter("p_phone", person.getP_phone());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	@Transactional
	public int personManagement_modify2(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_image = (SELECT iconadress FROM tb_department WHERE departmentName = :p_department LIMIT 1),p_power=(SELECT power FROM tb_tag WHERE tag_id = :p_tagid LIMIT 1),p_department = :p_department,p_name = :p_name,p_addtiem = now() WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_department", person.getP_department());
		query.setParameter("p_name", person.getP_name());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	@Transactional
	public int personManagement_add(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "INSERT INTO tb_person(p_tagid,p_image,p_power,p_department,p_name,p_sex,p_phone,p_addtiem,p_minzu,p_x,p_y,p_floor,p_sos,p_online,p_kaoqing,p_fence,baoliu4,baoliu12) VALUES(:p_tagid,(SELECT iconadress FROM tb_department WHERE departmentName = :p_department LIMIT 1),(SELECT power FROM tb_tag WHERE tag_id = :p_tagid LIMIT 1),:p_department,:p_name,:p_sex,:p_phone,now(),:p_minzu,:p_x,:p_y,:p_floor,:p_sos,:p_online,:p_kaoqing,:p_fence,:baoliu4,:baoliu12)";
		cs.tb_caozuo("tb_person", 1);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_department", person.getP_department());
		query.setParameter("p_name", person.getP_name());
		query.setParameter("p_sex", person.getP_sex());
		query.setParameter("p_phone", person.getP_phone());
		query.setParameter("p_minzu", person.getP_minzu());
		query.setParameter("p_x", person.getP_x());
		query.setParameter("p_y", person.getP_y());
		query.setParameter("p_floor", person.getP_floor());
		query.setParameter("p_sos", person.getP_sos());
		query.setParameter("p_online", person.getP_online());
		query.setParameter("p_kaoqing", person.getP_kaoqing());
		query.setParameter("p_fence", person.getP_fence());
		query.setParameter("baoliu4", person.getBaoliu4());
		query.setParameter("baoliu12", person.getBaoliu12());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	@Transactional
	public int renyuandaoru(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_name=:p_name,p_sex=:p_sex,p_department=:p_department,p_phone=:p_phone WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_department", person.getP_department());
		query.setParameter("p_name", person.getP_name());
		query.setParameter("p_sex", person.getP_sex());
		query.setParameter("p_phone", person.getP_phone());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	@Transactional
	public int person_modify_kaoqin(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_kaoqing = :p_kaoqin WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_kaoqin", person.getP_kaoqing());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	@Transactional
	public int person_modify_power(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_power = :p_power WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_power", person.getP_power());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	@Transactional
	public int person_modify_sos(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_sos = :p_sos WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_sos", person.getP_sos());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}

	@Transactional
	public int person_modify_warning(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_sos = :p_sos WHERE p_tagid= :p_tagid";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_sos", person.getP_sos());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	@Transactional
	public int person_modify_XYF(tb_person person) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_person SET p_x = :p_x,p_y = :p_y,p_floor = :p_floor,p_addtiem = now() WHERE p_tagid= :p_tagid ";
		cs.tb_caozuo("tb_person", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", person.getP_tagid());
		query.setParameter("p_x", person.getP_x());
		query.setParameter("p_y", person.getP_y());
		query.setParameter("p_floor", person.getP_floor());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
}