package com.hxzkoa.services;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.hxzkoa.json.tb_cojilu;
import com.hxzkoa.json.tb_heart_record;
import com.hxzkoa.json.tb_kaoqing;
import com.hxzkoa.json.tb_lixianrecord;
import com.hxzkoa.json.tb_person;
import com.hxzkoa.json.tb_realkaoqing;
import com.hxzkoa.json.tb_shishi;
import com.hxzkoa.json.tb_track;
import com.hxzkoa.json.vo_kaoqin_p;
import com.hxzkoa.util.Config;
import com.hxzkoa.util.ModifyConfig;
import com.hxzkoa.util.RequestUtils;

import net.sf.json.JSONObject;

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

	public List<vo_kaoqin_p> getRealAttendance(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 id,area,name,bumen,tagid,intime,beizhu FROM
		// tb_realkaoqing LIMIT :start,:end";
		sql = "SELECT rk.id, rk.area, rk.name, rk.bumen, rk.tagid, rk.intime, rk.beizhu, t.power FROM tb_realkaoqing rk, tb_tag t WHERE rk.tagid = t.tag_id LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		List resultList = query.getResultList();
		List<vo_kaoqin_p> tb_realkaoqingList = new ArrayList<vo_kaoqin_p>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				vo_kaoqin_p realkaoqing = new vo_kaoqin_p();
				realkaoqing.setId((Integer) obj[0]);
				realkaoqing.setArea(obj[1] == null ? "" : obj[1].toString());
				realkaoqing.setName(obj[2] == null ? "" : obj[2].toString());
				realkaoqing.setBumen(obj[3] == null ? "" : obj[3].toString());
				realkaoqing.setTagid(obj[4] == null ? "" : obj[4].toString());
				realkaoqing.setIntime(obj[5] == null ? "" : obj[5].toString());
				realkaoqing.setBeizhu(obj[6] == null ? "" : obj[6].toString());
				realkaoqing.setPower(obj[7] == null ? "" : obj[7].toString());
				tb_realkaoqingList.add(realkaoqing);
			}
		}
		return tb_realkaoqingList;
	}

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

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

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

	public List<vo_kaoqin_p> searchRealAttendance(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT rk.id, rk.area, rk.name, rk.bumen, rk.tagid, rk.intime, rk.beizhu, t.power FROM tb_realkaoqing rk, tb_tag t WHERE rk.tagid = t.tag_id AND rk.tagid = :tagid OR rk.tagid = t.tag_id AND rk.name = :tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("tagid", input);
		List resultList = query.getResultList();
		List<vo_kaoqin_p> tb_realkaoqingList = new ArrayList<vo_kaoqin_p>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				vo_kaoqin_p realkaoqing = new vo_kaoqin_p();
				realkaoqing.setId((Integer) obj[0]);
				realkaoqing.setArea(obj[1] == null ? "" : obj[1].toString());
				realkaoqing.setName(obj[2] == null ? "" : obj[2].toString());
				realkaoqing.setBumen(obj[3] == null ? "" : obj[3].toString());
				realkaoqing.setTagid(obj[4] == null ? "" : obj[4].toString());
				realkaoqing.setIntime(obj[5] == null ? "" : obj[5].toString());
				realkaoqing.setBeizhu(obj[6] == null ? "" : obj[6].toString());
				realkaoqing.setPower(obj[7] == null ? "" : obj[7].toString());
				tb_realkaoqingList.add(realkaoqing);
			}
		}
		return tb_realkaoqingList;
	}

	public Map<String, Integer> getRealAttendanceType() {
		String sql = null;
		Query query = null;
		sql = "SELECT bumen,num FROM tb_gongzhong";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		Map<String, Integer> reMap = new HashMap<String, Integer>();
		int realAttendanceCount = getRealAttendanceCount();
//		if (resultList.size() > 3) {
//			for (int i = 0; i < 3; i++) {
//				Object[] obj = (Object[]) resultList.get(i);
//				reMap.put(obj[0].toString(), Integer.parseInt(obj[1].toString()));
//				realAttendanceCount = realAttendanceCount - Integer.parseInt(obj[1].toString());
//			}
//			reMap.put("其他", realAttendanceCount);
//		} else {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				reMap.put(obj[0].toString(), Integer.parseInt(obj[1].toString()));
			}
//		}
		return reMap;
	}

	public String getPeople() {
		String sql = null;
		Query query = null;
		String people = null;
		sql = "select area,count('办公室') as count from tb_realkaoqing group by area";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		if (resultList.size() > 0) {
			Object[] obj = (Object[]) resultList.get(0);
			//System.out.print(obj);
			people = obj[1].toString();
		}
		return people;
	}

	public List<vo_kaoqin_p> getHistoricalAttendance() {
		String sql = null;
		Query query = null;
		sql = "SELECT k.id,k.area,k.name,k.bumen,k.tagid,k.intime,k.outtime,k.alltime,k.beizhu,t.power FROM tb_kaoqing k,tb_tag t WHERE k.tagid = t.tag_id";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<vo_kaoqin_p> tb_kaoqingList = new ArrayList<vo_kaoqin_p>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				vo_kaoqin_p kaoqing = new vo_kaoqin_p();
				kaoqing.setId((Integer) obj[0]);
				kaoqing.setArea(obj[1] == null ? "" : obj[1].toString());
				kaoqing.setName(obj[2] == null ? "" : obj[2].toString());
				kaoqing.setBumen(obj[3] == null ? "" : obj[3].toString());
				kaoqing.setTagid(obj[4] == null ? "" : obj[4].toString());
				kaoqing.setIntime(obj[5] == null ? "" : obj[5].toString());
				kaoqing.setOuttime(obj[6] == null ? "" : obj[6].toString());
				kaoqing.setAlltime(obj[7] == null ? "" : obj[7].toString());
				kaoqing.setBeizhu(obj[8] == null ? "" : obj[8].toString());
				kaoqing.setPower(obj[9] == null ? "" : obj[9].toString());
				tb_kaoqingList.add(kaoqing);
			}
		}
		return tb_kaoqingList;
	}

	public List<vo_kaoqin_p> getHistoricalAttendance(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 k.id,k.area,k.name,k.bumen,k.tagid,k.intime,k.outtime,k.alltime,k.beizhu,t.power FROM tb_kaoqing k,tb_tag t LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", start);
		query.setParameter("end", end);
		List resultList = query.getResultList();
		List<vo_kaoqin_p> tb_kaoqingList = new ArrayList<vo_kaoqin_p>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				vo_kaoqin_p kaoqing = new vo_kaoqin_p();
				kaoqing.setId((Integer) obj[0]);
				kaoqing.setArea(obj[1] == null ? "" : obj[1].toString());
				kaoqing.setName(obj[2] == null ? "" : obj[2].toString());
				kaoqing.setBumen(obj[3] == null ? "" : obj[3].toString());
				kaoqing.setTagid(obj[4] == null ? "" : obj[4].toString());
				kaoqing.setIntime(obj[5] == null ? "" : obj[5].toString());
				kaoqing.setOuttime(obj[6] == null ? "" : obj[6].toString());
				kaoqing.setAlltime(obj[7] == null ? "" : obj[7].toString());
				kaoqing.setBeizhu(obj[8] == null ? "" : obj[8].toString());
				kaoqing.setPower(obj[9] == null ? "" : obj[9].toString());
				tb_kaoqingList.add(kaoqing);
			}
		}
		return tb_kaoqingList;
	}

	public List<vo_kaoqin_p> searchHistoricalAttendance(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT k.id,k.area,k.name,k.bumen,k.tagid,k.intime,k.outtime,k.alltime,k.beizhu,t.power FROM tb_kaoqing k,tb_tag t WHERE k.tagid = t.tag_id AND k.tagid = :tagid OR k.tagid = t.tag_id AND k.name = :tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("tagid", input);
		List resultList = query.getResultList();
		List<vo_kaoqin_p> tb_kaoqingList = new ArrayList<vo_kaoqin_p>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				Object[] obj = (Object[]) resultList.get(i);
				vo_kaoqin_p kaoqing = new vo_kaoqin_p();
				kaoqing.setId((Integer) obj[0]);
				kaoqing.setArea(obj[1] == null ? "" : obj[1].toString());
				kaoqing.setName(obj[2] == null ? "" : obj[2].toString());
				kaoqing.setBumen(obj[3] == null ? "" : obj[3].toString());
				kaoqing.setTagid(obj[4] == null ? "" : obj[4].toString());
				kaoqing.setIntime(obj[5] == null ? "" : obj[5].toString());
				kaoqing.setOuttime(obj[6] == null ? "" : obj[6].toString());
				kaoqing.setAlltime(obj[7] == null ? "" : obj[7].toString());
				kaoqing.setBeizhu(obj[8] == null ? "" : obj[8].toString());
				kaoqing.setPower(obj[9] == null ? "" : obj[9].toString());
				tb_kaoqingList.add(kaoqing);
			}
		}
		return tb_kaoqingList;
	}

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

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

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

	@Transactional
	public int realAttendance_add(tb_realkaoqing realkaoqing) {
		String sql = null;
		Query query = null;
		sql = "INSERT INTO tb_tag(area,name,bumen,tagid,intime,beizhu) VALUES(:area,:name,:bumen,:tagid,now(),:beizhu)";
		cs.tb_caozuo("tb_tag", 1);
		query = this.em.createNativeQuery(sql);
		query.setParameter("area", realkaoqing.getArea());
		query.setParameter("name", realkaoqing.getName());
		query.setParameter("bumen", realkaoqing.getBumen());
		query.setParameter("tagid", realkaoqing.getTagid());
		query.setParameter("beizhu", realkaoqing.getBeizhu());
		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 WHERE tagid= :p_tagid";
//		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());
//		int executeUpdate = query.executeUpdate();
//		return executeUpdate;
//	}
	
	public Map<String, Integer> getRealtongjiType() {
		String sql = null;
		Query query = null;
		sql = "SELECT type_name,num FROM tb_tongji";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		Map<String, Integer> reMap = new HashMap<String, Integer>();
		for (int i = 0; i < resultList.size(); i++) {
			Object[] obj = (Object[]) resultList.get(i);
			reMap.put(obj[0].toString(), Integer.parseInt(obj[1].toString()));
		}
		return reMap;
	}
	
	public List<tb_shishi> getRealshishiType() {
		String sql = null;
		Query query = null;
		sql = "SELECT type,num FROM tb_shishi";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_shishi> shishilist = new ArrayList<tb_shishi>();
		for (int i = 0; i < resultList.size(); i++) {
			tb_shishi shi = new tb_shishi();
			Object[] obj = (Object[]) resultList.get(i);
			shi.setType(obj[0] == null ? "" : obj[0].toString());
			shi.setNum(obj[1] == null ? "" : obj[1].toString());
			shishilist.add(shi);
		}
		return shishilist;
	}
	
	public List<Object> gettongjishuju() {
		String sql = null;
		Query query = null;
		sql = "SELECT type,num FROM tb_tongji";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<String> rMap = new ArrayList<String>();
		List<String> reMap = new ArrayList<String>();
		for (int i = 0; i < resultList.size(); i++) {
			Object[] obj = (Object[]) resultList.get(i);
			rMap.add(obj[0].toString());
			reMap.add(obj[1].toString());
		}
		List<Object> zong = new ArrayList<Object>();
		zong.add(rMap);
		zong.add(reMap);
		return zong;
	}
	
	public List<Object> getgongzhongnum(){
		String sql = null;
		Query query = null;
		sql = "SELECT bumen,num FROM tb_gongzhong";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<String> gongzhongk = new ArrayList<String>();
		List<Integer> gongzhong = new ArrayList<Integer>();
		for (int i = resultList.size()-1; i >= 0; i--) {
			Object[] obj = (Object[]) resultList.get(i);
			gongzhongk.add(obj[0].toString());
			gongzhong.add(Integer.parseInt(obj[1].toString()));
		}
		List<Object> zong = new ArrayList<Object>();
		zong.add(gongzhongk);
		zong.add(gongzhong);
		return zong;
	}
	
	public List<Integer> getgaojingnum(){
		String sql = null;
		Query query = null;
		String str = "未处理";
		sql = "SELECT type,count(1) c FROM tb_warning WHERE status = :str GROUP BY type";
		query = this.em.createNativeQuery(sql);
		query.setParameter("str", str);
		List resultList = query.getResultList();
		List<Integer> gaojing = new ArrayList<Integer>();
		for (int i = resultList.size()-1; i >= 0; i--) {
			Object[] obj = (Object[])resultList.get(i);
			gaojing.add(Integer.parseInt(obj[1].toString()));
		}
		//System.out.print(gaojing);
		return gaojing;
	}
	
	public List<tb_heart_record> findxinlv(int page){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,heart,okheart,state,time from tb_heart_record ORDER BY time DESC 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_heart_record> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_heart_record per = new tb_heart_record();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setHeart(obj[3] == null?null:obj[3].toString());
				per.setOkheart(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setTime(obj[6] == null?null:obj[6].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public List<tb_heart_record> findxinlv2(){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,heart,okheart,state,time from tb_heart_record ORDER BY time DESC";
		query = this.em.createNativeQuery(sql);
		List resultlist = query.getResultList();
		List<tb_heart_record> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_heart_record per = new tb_heart_record();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setHeart(obj[3] == null?null:obj[3].toString());
				per.setOkheart(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setTime(obj[6] == null?null:obj[6].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public List<tb_heart_record> findxinlv3(String input){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,heart,okheart,state,time from tb_heart_record WHERE tagid = :input OR name = :input ORDER BY time DESC";
		query = this.em.createNativeQuery(sql);
		query.setParameter("input", input);
		List resultlist = query.getResultList();
		List<tb_heart_record> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_heart_record per = new tb_heart_record();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setHeart(obj[3] == null?null:obj[3].toString());
				per.setOkheart(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setTime(obj[6] == null?null:obj[6].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public int getxinlvManagementCount() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_heart_record";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}
	
	@Transactional
	public void xinlv_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_heart_record WHERE id = :id";
			query = this.em.createNativeQuery(sql);
			query.setParameter("id", id);
			query.executeUpdate();
		}
	}
	
	@Transactional
	public void xinlv_deletea() {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_heart_record";
		query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}
	
	public List<tb_cojilu> findco(int page){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,nongdu,oknongdu,state,xypos,time from tb_cojilu ORDER BY time DESC LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", (page - 1) * Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage")));
		query.setParameter("end", Integer.parseInt(ModifyConfig.readData(Config.getPageConfig(), "perPage")));
		List resultlist = query.getResultList();
		List<tb_cojilu> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_cojilu per = new tb_cojilu();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setNongdu(obj[3] == null?null:obj[3].toString());
				per.setOknongdu(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setXypos(obj[6] == null?null:obj[6].toString());
				per.setTime(obj[7] == null?null:obj[7].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public List<tb_cojilu> findco2(){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,nongdu,oknongdu,state,xypos,time from tb_cojilu ORDER BY time DESC";
		query = this.em.createNativeQuery(sql);
		List resultlist = query.getResultList();
		List<tb_cojilu> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_cojilu per = new tb_cojilu();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setNongdu(obj[3] == null?null:obj[3].toString());
				per.setOknongdu(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setXypos(obj[6] == null?null:obj[6].toString());
				per.setTime(obj[7] == null?null:obj[7].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public List<tb_cojilu> findco3(String input){
		String sql = null;
		Query query = null;
		sql = "select id,tagid,name,nongdu,oknongdu,state,xypos,time from tb_cojilu WHERE tagid = :input OR name = :input ORDER BY time DESC";
		query = this.em.createNativeQuery(sql);
		query.setParameter("input", input);
		List resultlist = query.getResultList();
		List<tb_cojilu> permissions = new ArrayList<>();
		if (resultlist.size() > 0) {
			for (int i=0; i<resultlist.size(); i++) {
				Object[] obj = (Object[])resultlist.get(i);
				tb_cojilu per = new tb_cojilu();
				per.setId((int)obj[0]);
				per.setTagid(obj[1] == null?null:obj[1].toString());
				per.setName(obj[2] == null?null:obj[2].toString());
				per.setNongdu(obj[3] == null?null:obj[3].toString());
				per.setOknongdu(obj[4] == null?null:obj[4].toString());
				per.setState(obj[5] == null?null:obj[5].toString());
				per.setXypos(obj[6] == null?null:obj[6].toString());
				per.setTime(obj[7] == null?null:obj[7].toString());
				permissions.add(per);
			}
		}
		return permissions;
	}
	
	public int getcoManagementCount() {
		String sql = null;
		Query query = null;
		sql = "SELECT count(1) FROM tb_cojilu";
		query = this.em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}
	
	@Transactional
	public void co_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_cojilu WHERE id = :id";
			query = this.em.createNativeQuery(sql);
			query.setParameter("id", id);
			query.executeUpdate();
		}
	}
	
	@Transactional
	public void co_deletea() {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_cojilu";
		query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}
}