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_kaoqing;
|
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 = perPage;
|
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";
|
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 = perPage;
|
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";
|
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_name,num FROM tb_tongji";
|
query = this.em.createNativeQuery(sql);
|
List resultList = query.getResultList();
|
List<String> rMap = new ArrayList<String>();
|
List<Integer> reMap = new ArrayList<Integer>();
|
for (int i = 0; i < resultList.size(); i++) {
|
Object[] obj = (Object[]) resultList.get(i);
|
rMap.add(obj[0].toString());
|
reMap.add(Integer.parseInt(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 ORDER BY c DESC";
|
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;
|
}
|
|
}
|
