package com.hxzkoa.services;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.hxzkoa.json.tb_achor;
import com.hxzkoa.json.tb_realocation;
import com.hxzkoa.json.tb_tag;
import com.hxzkoa.json.tb_track;
import com.hxzkoa.util.Config;
import com.hxzkoa.util.ModifyConfig;

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

	public List<tb_realocation> getRealTimeLocation(String date) {
		String sql = null;
		Query query = null;
		sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_track_" +date+ "";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_realocation realocation = new tb_realocation();
				Object[] obj = (Object[]) resultList.get(i);
				realocation.setId((int) obj[0]);
				realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
				realocation.setX(obj[2] == null ? "" : obj[2].toString());
				realocation.setY(obj[3] == null ? "" : obj[3].toString());
				realocation.setZ(obj[4] == null ? "" : obj[4].toString());
				realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
				realocation.setTime(obj[6] == null ? "" : obj[6].toString());
				tb_realocationList.add(realocation);
			}
		}
		return tb_realocationList;
	}

	public List<tb_realocation> getRealTimeLocation(int page,int perPage) {
		String sql = null;
		Query query = null;
		sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_realocation LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", (page - 1) * perPage);
		query.setParameter("end", perPage);
		List resultList = query.getResultList();
		List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_realocation realocation = new tb_realocation();
				Object[] obj = (Object[]) resultList.get(i);
				realocation.setId((int) obj[0]);
				realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
				realocation.setX(obj[2] == null ? "" : obj[2].toString());
				realocation.setY(obj[3] == null ? "" : obj[3].toString());
				realocation.setZ(obj[4] == null ? "" : obj[4].toString());
				realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
				realocation.setTime(obj[6] == null ? "" : obj[6].toString());
				tb_realocationList.add(realocation);
			}
		}
		return tb_realocationList;
	}

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

	public List<tb_realocation> searchRealTimeLocation(String input,String date) {
		String sql = null;
		Query query = null;
		sql = "SELECT t.id,t.tagid,t.x,t.y,t.z,t.layer,t.time,t.baoliu1,t.baoliu2,t.baoliu3,t.baoliu4 FROM tb_track_" +date+ " t,tb_person p WHERE t.tagid = p.p_tagid AND p.p_name = :p_tagid OR t.tagid = p.p_tagid AND t.tagid = :p_tagid";
		query = this.em.createNativeQuery(sql);
		query.setParameter("p_tagid", input);
		List resultList = query.getResultList();
		List<tb_realocation> tb_realocationList = new ArrayList<tb_realocation>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_realocation realocation = new tb_realocation();
				Object[] obj = (Object[]) resultList.get(i);
				realocation.setId((int) obj[0]);
				realocation.setTagid(obj[1] == null ? "" : obj[1].toString());
				realocation.setX(obj[2] == null ? "" : obj[2].toString());
				realocation.setY(obj[3] == null ? "" : obj[3].toString());
				realocation.setZ(obj[4] == null ? "" : obj[4].toString());
				realocation.setLayer(obj[5] == null ? "" : obj[5].toString());
				realocation.setTime(obj[6] == null ? "" : obj[6].toString());
				realocation.setFangkeid(obj[7] == null ? "" : obj[7].toString());
				realocation.setJingdu(obj[8] == null ? "" : obj[8].toString());
				realocation.setWeidu(obj[9] == null ? "" : obj[9].toString());
				realocation.setGaocheng(obj[10] == null ? "" : obj[10].toString());
				tb_realocationList.add(realocation);
			}
		}
		return tb_realocationList;
	}
	
	public int searchtablenameLocation(String input) {
		String sql = null;
		Query query = null;
		sql = "SELECT COUNT(`TABLE_NAME`) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'hxzkuwb' AND `TABLE_NAME` = :tablename";
		query = this.em.createNativeQuery(sql);
		query.setParameter("tablename", input);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	@Transactional
	public void realTimeLocation_delete(String[] checkVal,String date) {
		String sql = null;
		Query query = null;
		for (int i = 0; i < checkVal.length; i++) {
			int id = Integer.parseInt(checkVal[i]);
			sql = "DELETE FROM tb_track_" +date+ " WHERE id = :id";
			cs.tb_caozuo("tb_realocation", 2);
			query = this.em.createNativeQuery(sql);
			query.setParameter("id", id);
			query.executeUpdate();
		}
	}
	
	@Transactional
	public int realTimeLocation_deletea(String[] checkVal,String date) {
		String sql = null;
		Query query = null;
		int aaa = checkVal.length;
		for (int i = 0; i < checkVal.length; i++) {
			int id = Integer.parseInt(checkVal[i]);
			sql = "DELETE FROM tb_track_" +date+ " WHERE id = :id";
			cs.tb_caozuo("tb_realocation", 2);
			query = this.em.createNativeQuery(sql);
			query.setParameter("id", id);
			query.executeUpdate();
		}
		return aaa;
	}

	@Transactional
	public void realTimeLocation_deleteAll(String date) {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_track_" +date+ "";
		query = this.em.createNativeQuery(sql);
		query.executeUpdate();
	}
	
	@Transactional
	public int realTimeLocation_deleteAlla(String date) {
		String sql = null;
		Query query = null;
		sql = "DELETE FROM tb_track_" +date+ "";
		query = this.em.createNativeQuery(sql);
		int aaa = query.executeUpdate();
		return aaa;
	}

	@Transactional
	public int track_add(String date,tb_track track) {
		String sql = null;
		Query query = null;
		sql = "INSERT INTO tb_track_"+date+"(tagid,x,y,z,layer,time) VALUES(:tag_id,:x,:y,:z,:layer,now())";
		query = this.em.createNativeQuery(sql);
		query.setParameter("tag_id", track.getTagid());
		query.setParameter("x", track.getX());
		query.setParameter("y", track.getY());
		query.setParameter("z", track.getZ());
		query.setParameter("layer", track.getLayer());
		int executeUpdate = query.executeUpdate();
		return executeUpdate;
	}
	
	public List<tb_track> getTrack(String date,int page,int perPage) {
		// 日期格式 例20210101 
		String sql = null;
		Query query = null;
		sql = "SELECT id,tagid,x,y,z,layer,time FROM tb_track_"+ date +" LIMIT :start,:end";
		query = this.em.createNativeQuery(sql);
		query.setParameter("start", (page - 1) * perPage);
		query.setParameter("end", perPage);
		List resultList = query.getResultList();
		List<tb_track> tb_trackList = new ArrayList<tb_track>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_track track = new tb_track();
				Object[] obj = (Object[]) resultList.get(i);
				track.setId((int) obj[0]);
				track.setTagid(obj[1] == null ? "" : obj[1].toString());
				track.setX(obj[2] == null ? "" : obj[2].toString());
				track.setY(obj[3] == null ? "" : obj[3].toString());
				track.setZ(obj[4] == null ? "" : obj[4].toString());
				track.setLayer(obj[5] == null ? "" : obj[5].toString());
				track.setTime(obj[6] == null ? "" : obj[6].toString());
				tb_trackList.add(track);
			}
		}
		return tb_trackList;
	}
	
	public List<tb_track> getTracka(String date) {
		// 日期格式 例20210101 
		String sql = null;
		Query query = null;
		sql = "SELECT id,tagid,x,y,z,layer,time,baoliu1,baoliu2,baoliu3,baoliu4 FROM tb_track_"+ date +"";
		query = this.em.createNativeQuery(sql);
		List resultList = query.getResultList();
		List<tb_track> tb_trackList = new ArrayList<tb_track>();
		if (resultList.size() > 0) {
			for (int i = 0; i < resultList.size(); i++) {
				tb_track track = new tb_track();
				Object[] obj = (Object[]) resultList.get(i);
				track.setId((int) obj[0]);
				track.setTagid(obj[1] == null ? "" : obj[1].toString());
				track.setX(obj[2] == null ? "" : obj[2].toString());
				track.setY(obj[3] == null ? "" : obj[3].toString());
				track.setZ(obj[4] == null ? "" : obj[4].toString());
				track.setLayer(obj[5] == null ? "" : obj[5].toString());
				track.setTime(obj[6] == null ? "" : obj[6].toString());
				track.setFangkeid(obj[7] == null ? "" : obj[7].toString());
				track.setJingdu(obj[8] == null ? "" : obj[8].toString());
				track.setWeidu(obj[9] == null ? "" : obj[9].toString());
				track.setGaocheng(obj[10] == null ? "" : obj[10].toString());
				tb_trackList.add(track);
			}
		}
		return tb_trackList;
	}
	
	public List<String> getTrackDateList() {
		String sql = null;
		Query query = null;
		sql = "select right(table_name,8) from information_schema.tables where table_schema='hxzkuwb' and table_name like 'tb_track_%'";
		query = this.em.createNativeQuery(sql);
		List<String> resultList = query.getResultList();
		return resultList;
	}
	
	@Transactional
	public void tag_time(tb_tag tag) {
		String sql = null;
		Query query = null;
		sql = "UPDATE tb_tag SET addtime=:addtime WHERE tag_id=:tag_id";
		cs.tb_caozuo("tb_tag", 3);
		query = this.em.createNativeQuery(sql);
		query.setParameter("addtime", tag.getAddtime());
		query.setParameter("tag_id", tag.getTag_id());
		query.executeUpdate();
	}
	
	public List<String> panduanweiyi(String name){
		String sql = null;
		Query query = null;
		sql = "select p_tagid from tb_person where p_name = :name";
		query = this.em.createNativeQuery(sql);
		query.setParameter("name", name);
		List resultlist = query.getResultList();
		List<String> strs = new ArrayList<>();
		for (int i=0; i<resultlist.size(); i++) {
			strs.add(resultlist.get(i).toString());
		}
		return strs;
	}
}