设备名单查询，删除名单查询以及流水信息查询 分配时间判断细节调整
diff --git a/src/main/java/com/supwisdom/dlpay/mainservice/dao/impl/DoordtlDaoImpl.java b/src/main/java/com/supwisdom/dlpay/mainservice/dao/impl/DoordtlDaoImpl.java
index 5978855..78b475f 100644
--- a/src/main/java/com/supwisdom/dlpay/mainservice/dao/impl/DoordtlDaoImpl.java
+++ b/src/main/java/com/supwisdom/dlpay/mainservice/dao/impl/DoordtlDaoImpl.java
@@ -20,7 +20,7 @@
@Override
public List<TDoordtlInfo> findSystemExportExcelDoordtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String regionid, String devname, String streamstatus) {
try {
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
" to_char(to_date(a.coldate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.coltime,'hh24miss'),'hh24:mi:ss') coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a "
@@ -28,8 +28,11 @@
+ " left join tb_region d on b.regionid=d.regionid "
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("") && !endTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -60,6 +63,8 @@
Query query = entityManager.createNativeQuery(queryString, TDoordtlInfo.class);
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -92,7 +97,7 @@
@Override
public List<TDoordtlInfo> findOperatorExportExcelDoordtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String regionid, String devname, String streamstatus, String operRegionid) {
try {
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
" to_char(to_date(a.coldate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.coltime,'hh24miss'),'hh24:mi:ss') coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a "
@@ -100,8 +105,11 @@
+ " left join tb_region d on b.regionid=d.regionid "
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -133,6 +141,8 @@
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -167,7 +177,7 @@
@Override
public List<TDoordtlInfo> findBuildingOperExportExcelDoorDtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String devname, String streamstatus, String operid) {
try{
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,to_char(to_date(a.transdate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.transtime,'hh24miss'),'hh24:mi:ss') transdate, " +
" to_char(to_date(a.coldate ,'yyyymmdd'),'yyyy-mm-dd')||' '||to_char(to_timestamp(a.coltime,'hh24miss'),'hh24:mi:ss') coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a "
@@ -176,8 +186,11 @@
+ " left join t_building e on b.buildingid=e.buildingid "
+ " left join t_operBuilding f on e.buildingid = f.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid and f.operid = :operid ";
- if (!startTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -199,6 +212,8 @@
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -229,14 +244,17 @@
@Transactional
@Override
public Pagination findSystemTDoordtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String devname, String streamstatus, int pageNo, int pageSize, String regionid) {
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a left join t_card c on a.custid=c.custid, t_nc_device b "
+ " left join tb_region d on b.regionid=d.regionid "
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("") && !endTranDate.equals("")) {
- queryString += " and transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -267,6 +285,8 @@
Query query = entityManager.createNativeQuery(queryString, TDoordtlInfo.class);
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -314,8 +334,11 @@
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -346,6 +369,8 @@
query.setMaxResults(5);
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -383,15 +408,18 @@
@Override
public Pagination findOperatorTDoordtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String devname, String streamstatus, int pageNo, int pageSize, String regionid,String operRegionid) {
try{
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a "
+ " left join t_card c on a.custid=c.custid,t_nc_device b "
+ " left join tb_region d on b.regionid=d.regionid "
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("")) {
- queryString += " and transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -423,6 +451,8 @@
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -476,8 +506,11 @@
+ " left join t_building e on b.buildingid=e.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid ";
- if (!startTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate ";
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -508,6 +541,8 @@
query.setMaxResults(5);
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -547,7 +582,7 @@
@Override
public Pagination findBuildingOperTDoordtl(String startTranDate, String endTranDate, String startTranTime, String endTranTime, String cardno, String custname, String buildingid, String devname, String streamstatus, int pageNo, int pageSize, String operid) {
try{
- String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,c.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ String queryString = "select a.doorseqno,b.deviceid,b.devname,a.custname,c.cardno,a.cardphyid,c.bankcardno,a.transdate||a.transtime transdate, a.coldate||a.coltime coldate,"
+ " case when a.status='' then '失败' else '成功' end status,a.dtldesc "
+ " from t_doordtl a "
+ " left join t_card c on a.custid=c.custid,t_nc_device b "
@@ -555,8 +590,11 @@
+ " left join t_building e on b.buildingid=e.buildingid "
+ " left join t_operBuilding f on e.buildingid = f.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid and f.operid = :operid ";
- if (!startTranDate.equals("")) {
- queryString += " and transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate " ;
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -578,6 +616,8 @@
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
@@ -626,8 +666,11 @@
+ " left join t_operBuilding f on e.buildingid = f.buildingid "
+ " where 1>0 and cast(substr(a.doorid,5) as integer) = b.deviceid and f.operid = :operid ";
- if (!startTranDate.equals("")) {
- queryString += " and a.transdate between :startTranDate and :endTranDate ";
+ if (!startTranDate.equals("")){
+ queryString += " and transdate >= :startTranDate ";
+ }
+ if (!endTranDate.equals("")){
+ queryString += " and transdate <= :endTranDate " ;
}
if (!cardno.equals("")) {
queryString += " and a.cardno like :cardno ";
@@ -648,6 +691,8 @@
query.setMaxResults(5);
if (!startTranDate.equals("")) {
query.setParameter("startTranDate", startTranDate);
+ }
+ if (!endTranDate.equals("")){
query.setParameter("endTranDate", endTranDate);
}
if (!cardno.equals("")) {
diff --git a/src/main/resources/templates/doorlist/deleteDoorlist.html b/src/main/resources/templates/doorlist/deleteDoorlist.html
index 0f78960..743ae48 100644
--- a/src/main/resources/templates/doorlist/deleteDoorlist.html
+++ b/src/main/resources/templates/doorlist/deleteDoorlist.html
@@ -155,12 +155,14 @@
<el-table-column
prop="rectime"
label="接收时间"
- width="200">
+ width="200"
+ :formatter="rectimeform">
</el-table-column>
<el-table-column
prop="synctime"
label="同步时间"
- width="200">
+ width="200"
+ :formatter="synctimeform">
</el-table-column>
<el-table-column
prop="opername"
@@ -248,6 +250,16 @@
del: function () {
deleteDoorlist_deletedata();
},
+ rectimeform:function(row,column,cellValue){
+ if(row.rectime != '') {
+ return deleteDoorlist_timeformatter(row.rectime);
+ }
+ },
+ synctimeform:function(row,column,cellValue){
+ if(row.synctime != '') {
+ return deleteDoorlist_timeformatter(row.synctime);
+ }
+ },
syncform:function (row) {
if (row.syncflag == 'N') {
return '未同步';
@@ -536,4 +548,14 @@
}
}
+
+ function deleteDoorlist_timeformatter(time){
+
+ return time.substr(0,4)+'-'+time.substr(4,2)+'-'+time.substr(6,2)+' '
+ +time.substr(8,2)+':'+time.substr(10,2)+':'+time.substr(12,2);
+// console.log([year,month,date].join('-')+' '+[hour,moment,second].join(':'));
+// return [year,month,date].join('-')+' '+[hour,moment,second].join(':');
+
+ }
+
</script>
\ No newline at end of file
diff --git a/src/main/resources/templates/doorlist/searchDoorlist.html b/src/main/resources/templates/doorlist/searchDoorlist.html
index 0ac6719..95501f7 100644
--- a/src/main/resources/templates/doorlist/searchDoorlist.html
+++ b/src/main/resources/templates/doorlist/searchDoorlist.html
@@ -540,14 +540,14 @@
function searchDoorlist_commonQuery(_self, searchDoor, pageno, pagesize) {
var startStr = '';
var endStr = '';
- if (searchDoor.allocatStartDate != '') {
- if (searchDoor.allocatEndDate == '') {
- layer.msg('请选择结束时间', {icon: 2, time: 1000});
- return;
- } else {
- startStr = searchDoor.allocatStartDate.Format('yyyyMMdd');
- endStr = searchDoor.allocatEndDate.Format('yyyyMMdd');
- }
+ if (searchDoor.allocatStartDate != '' && searchDoor.allocatStartDate != undefined) {
+
+ startStr = searchDoor.allocatStartDate.Format('yyyyMMdd');
+ }
+ if (searchDoor.allocatEndDate != '' && searchDoor.allocatEndDate != undefined) {
+
+ endStr = searchDoor.allocatEndDate.Format('yyyyMMdd');
+
}
var url = "[[@{/doorlistMgr/getAllCardList?}]]" + "&allocatStartDate=" + startStr + "&allocatEndDate=" + endStr +
"&devname=" + searchDoor.devname + "&custname=" + searchDoor.custname