流水查询
diff --git a/src/main/java/com/supwisdom/dlpay/atte/bean/AtteDtl.java b/src/main/java/com/supwisdom/dlpay/atte/bean/AtteDtl.java
index 97747aa..46e5701 100644
--- a/src/main/java/com/supwisdom/dlpay/atte/bean/AtteDtl.java
+++ b/src/main/java/com/supwisdom/dlpay/atte/bean/AtteDtl.java
@@ -8,6 +8,7 @@
private String attedate;
private String attetime;
@Id
+ private String id;
private String refno;
private String custname;
private String custid;
@@ -96,4 +97,12 @@
public void setDeptcode(String deptcode) {
this.deptcode = deptcode;
}
+
+ public String getId() {
+ return id;
+ }
+
+ public void setId(String id) {
+ this.id = id;
+ }
}
diff --git a/src/main/java/com/supwisdom/dlpay/atte/dao/impl/AtteClassDaoImpl.java b/src/main/java/com/supwisdom/dlpay/atte/dao/impl/AtteClassDaoImpl.java
index 70edde5..eba37a7 100644
--- a/src/main/java/com/supwisdom/dlpay/atte/dao/impl/AtteClassDaoImpl.java
+++ b/src/main/java/com/supwisdom/dlpay/atte/dao/impl/AtteClassDaoImpl.java
@@ -430,7 +430,7 @@
@Override
public Pagination getDtlWithPage(AttedtlSearchBean searchBean, int pageNo, int pageSize) {
- String sql=" select v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
+ String sql=" select v.dtlid id,v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
sql+=" from v_atte_dtl v left join t_nc_device d on cast( v.deviceid as INTEGER) = d.deviceid left join t_customer c on c.custid=v.custid where 1=1 ";
if(!StringUtil.isEmpty(searchBean.getStartdate())&&!StringUtil.isEmpty(searchBean.getEnddate())){
sql+=" and v.transdate between :sdate and :edate ";
@@ -485,7 +485,7 @@
}
public int getDtlCount(AttedtlSearchBean searchBean){
- String sql=" select v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
+ String sql=" select v.dtlid id,v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
sql+=" from v_atte_dtl v left join t_nc_device d on cast( v.deviceid as INTEGER) = d.deviceid left join t_customer c on c.custid=v.custid where 1=1 ";
if(!StringUtil.isEmpty(searchBean.getStartdate())&&!StringUtil.isEmpty(searchBean.getEnddate())){
sql+=" and v.transdate between :sdate and :edate ";
@@ -557,7 +557,7 @@
@Override
public List<AtteDtl> getDtlList(AttedtlSearchBean searchBean) {
- String sql=" select v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
+ String sql=" select v.dtlid id,v.transdate attedate,v.transtime attetime,v.doorseqno refno,v.custname,v.custid,c.custtypeid,d.devname devicename,d.deviceid,v.cardno,c.deptcode " ;
sql+=" from v_atte_dtl v left join t_nc_device d on cast( v.deviceid as INTEGER) = d.deviceid left join t_customer c on c.custid=v.custid where 1=1 ";
sql+=" and exists (select DISTINCT r.deviceid from t_dev_rule r where r.deviceid=cast(v.deviceid as INTEGER) and r.ruleid in (select ar.id from t_atte_rule ar where ar.custtypeid=cast(c.custtypeid as INTEGER) and ar.deptcode=c.deptcode and ar.status=1 )) ";
if(!StringUtil.isEmpty(searchBean.getStartdate())&&!StringUtil.isEmpty(searchBean.getEnddate())){
diff --git a/src/main/resources/data.sql b/src/main/resources/data.sql
index 83baf7d..2ef0d00 100644
--- a/src/main/resources/data.sql
+++ b/src/main/resources/data.sql
@@ -225,23 +225,26 @@
INSERT INTO "tb_dept"("deptcode", "deptname", "deptno", "ename", "fdeptcode", "lastsaved", "status") VALUES ('0', '根部门', 'root', NULL, '-1', NULL, 'normal');
--atte
-create view v_atte_dtl as SELECT t.transdate,
- t.transtime,
- substr((t.doorid)::text, 5) AS deviceid,
- t.doorseqno,
- t.cardno,
- t.cardphyid,
- t.bankcardno,
- t.custid,
- t.custname,
- t.status,
- t.devtype
- FROM t_doordtl t
- WHERE ((substr((t.doorid)::text, 5))::integer IN ( SELECT DISTINCT d.deviceid
+create view v_atte_dtl as SELECT t.transdate,
+ t.transtime,
+ substr((t.doorid)::text, 5) AS deviceid,
+ t.doorseqno,
+ t.cardno,
+ t.cardphyid,
+ t.bankcardno,
+ t.custid,
+ t.custname,
+ t.status,
+ t.devtype,
+ ((((t.transdate)::text || (t.factoryid)::text) || (t.doorid)::text) || (t.doorseqno)::text) AS dtlid
+ FROM t_doordtl t
+ WHERE (((substr((t.doorid)::text, 5))::integer IN ( SELECT DISTINCT d.deviceid
FROM (t_dev_rule d
LEFT JOIN t_atte_rule r ON ((d.ruleid = r.id)))
- WHERE (r.status = 1)))
- ORDER BY t.doorseqno;
+ WHERE (r.status = 1))) AND (EXISTS ( SELECT t_customer.custid
+ FROM t_customer
+ WHERE ((t_customer.custid)::text = (t.custid)::text))))
+ ORDER BY t.doorseqno
INSERT INTO "tb_businesspara" VALUES ('atte_bmin', '60');
INSERT INTO "tb_businesspara" VALUES ('atte_min', '60');