From 31400fffd7b31397d6d8882d3927e7b870924233 Mon Sep 17 00:00:00 2001
From: fs-danaus <danaus314@qq.com>
Date: 星期四, 14 九月 2023 11:16:52 +0800
Subject: [PATCH] 优化excel导出图片及增加下载原图功能
---
src/com/yc/action/grid/GTGrid.java | 31 ++--
src/com/yc/action/grid/TreeGrid.java | 4
src/com/yc/open/qiyunfang/service/QiyunfangImpl.java | 95 ++++++++-------
src/com/yc/open/jinwu/service/JinWuImpl.java | 2
src/com/yc/open/qiyunfang/controller/CashFlowDetailsEntry.java | 2
src/com/yc/open/controller/EchoController.java | 112 ++++++++++++++++++
src/com/yc/action/BaseAction.java | 14 +
src/com/yc/action/grid/PoiExcelWriter.java | 84 +++++++++----
src/com/yc/log/ErrorLoggerBean.java | 4
src/com/yc/open/qiyunfang/controller/T150102Entry.java | 7 +
src/com/yc/utils/JsonUtil.java | 2
11 files changed, 252 insertions(+), 105 deletions(-)
diff --git a/src/com/yc/action/BaseAction.java b/src/com/yc/action/BaseAction.java
index 4d73d13..b28511c 100644
--- a/src/com/yc/action/BaseAction.java
+++ b/src/com/yc/action/BaseAction.java
@@ -542,7 +542,8 @@
}
@SuppressWarnings("unchecked")
protected void print2Excel(HttpServletRequest request,HttpServletResponse resp, Page page, Object object,int formid,String type) {
- try {
+ SXSSFWorkbook wb=null;
+ try {
//璇诲彇褰撳墠鍔熻兘鍙峰摢浜涘瓧娈垫槸闇�瑕佸鍑虹殑
String s=FileUtil.readFile(request.getServletContext().getRealPath("/")+request.getSession().getAttribute(SessionKey.PAGE_PATH)+File.separator+formid+File.separator+type+File.separator+"data");//
if(request.getSession().getAttribute(SessionKey.USERCODE)==null){
@@ -597,7 +598,7 @@
Map m=poi.writeExcel( dbid,str[0], page, name, filed,panel_name, panel_tfiled,
map!=null?(List<?>)map.get("plist"):null,str[0],local,
map!=null?(((String)map.get("desc")).split("-").length==5?this.replaceBlank(((String)map.get("desc")).split("-")[4]):""):"",hostUrl);
- SXSSFWorkbook wb=(SXSSFWorkbook)m.get("wb");
+ wb=(SXSSFWorkbook)m.get("wb");
String fileName=m.get("title")+"";
if(wb!=null){
File file1=new File(request.getServletContext().getRealPath("/")+"excel"+File.separator+dbid+File.separator+formid+File.separator+type);
@@ -633,14 +634,19 @@
excelRecords(formid,type,map,file,request,page);//瀵煎嚭鏁版嵁瀹¤璁板綍鎵ц鏂规硶
wb.dispose();
}
- page = null;
}catch (ParseException e) {
e.printStackTrace();
this.printJson(resp, "error;"+e.getMessage());
} catch (Exception e) {
e.printStackTrace();
this.printJson(resp, "error;"+e.getMessage());
- }
+ }finally {
+ try {
+ wb.close();
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
}
/**
diff --git a/src/com/yc/action/grid/GTGrid.java b/src/com/yc/action/grid/GTGrid.java
index bef5545..fe16c62 100644
--- a/src/com/yc/action/grid/GTGrid.java
+++ b/src/com/yc/action/grid/GTGrid.java
@@ -4420,31 +4420,28 @@
Page page = new Page();
try {
page.setTableName(json.getFunName());
- if (request.getHeader("x-app-type") != null && !"".equalsIgnoreCase(json.getFields()))
+ //缁熶竴鍙栨寚瀹氱殑瀛楁杈撳嚭锛屼笉鍐嶅尯鍒�
+ /* if (request.getHeader("x-app-type") != null && !"".equalsIgnoreCase(json.getFields()))
page.setSql(json.getFields().replaceAll(";", ","));
else
- page.setSql("*");// 鍙栨墍鏈�
+ page.setSql("*");// 鍙栨墍鏈�*/
+ page.setSql(json.getFields().replaceAll(";", ","));
+
page.setWhere(json.getParm());
page.setParms(json.getFields());
SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
gridService.loadAllByFunc_for(page);// 绗簩姝ワ紝璋冪敤鐩稿搴斾笟鍔＄被鍙栧緱鏁版嵁
- String data = JsonUtil.list2json(page.getData());
- if (request.getHeader("x-app-type") != null) {
- Gson gsons = new GsonBuilder().setDateFormat(
- "yyyy-MM-dd").disableHtmlEscaping().create();
- this.print(resp, gsons.toJson(page.getData()));
- } else
- this.printJson(resp, data);// 绗笁姝ワ紝閫氳繃Response鍚戦〉闈㈣浆鎹负json鏍煎紡杈撳嚭Page绫绘暟鎹�
+ Gson gsons = new GsonBuilder().setDateFormat(
+ "yyyy-MM-dd").disableHtmlEscaping().serializeNulls().create();
+ this.print(resp, gsons.toJson(page.getData()));
} catch (DataAccessException e) {
- if (request.getHeader("x-app-type") != null && "ionic".equals(request.getHeader("x-app-type"))) {
- resp.setStatus(500);
- this.printJson(resp, "{\"error\":\"" + this.getErrorMsg(e) + "\"}");
- } else
+ // resp.setStatus(500);
+ this.printJson(resp, "{\"error\":\"" + this.getErrorMsg(e) + "\"}");
+ /*if (request.getHeader("x-app-type") != null && "ionic".equals(request.getHeader("x-app-type"))) {
+ } else {
this.printJson(resp, "error;" + this.getErrorMsg(e));
- } finally {
- gson = null;
- json = null;
- page = null;
+ }*/
+ }finally {
SpObserver.setDBtoInstance();
}
diff --git a/src/com/yc/action/grid/PoiExcelWriter.java b/src/com/yc/action/grid/PoiExcelWriter.java
index c35e737..075d813 100644
--- a/src/com/yc/action/grid/PoiExcelWriter.java
+++ b/src/com/yc/action/grid/PoiExcelWriter.java
@@ -7,6 +7,7 @@
import com.yc.service.BaseService;
import com.yc.utils.Page;
import org.apache.commons.lang3.StringUtils;
+import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
@@ -14,6 +15,7 @@
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.LobRetrievalFailureException;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import java.io.ByteArrayOutputStream;
@@ -52,8 +54,8 @@
if (datas == null) {
throw new IllegalArgumentException("鍐檈xcel娴侀渶瑕丩ist鍙傛暟!");
}
+ SXSSFWorkbook wb = new SXSSFWorkbook(500);
try {
- SXSSFWorkbook wb = new SXSSFWorkbook(500);
SXSSFSheet ws = wb.createSheet("sheet1");
formatd = (XSSFDataFormat) wb.createDataFormat();
int rowNum = 0; //瑕佸啓鐨勮
@@ -186,6 +188,11 @@
//-----------
} catch (Exception e) {
+ try {
+ wb.close();
+ } catch (IOException ex) {
+ throw new RuntimeException(ex);
+ }
throw new ApplicationException(e.getMessage());
}
@@ -230,28 +237,34 @@
fileds+=",FileType, null as pic";//SmallPicture,OriginalPicture
}
List<AttendEntity> maps=new ArrayList<>();
- baseService.getSimpleJdbcTemplate().query("set nocount on\n " +
- " select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid" + fileds + " from _sys_Attachment9 where unid in(" + joiner.toString() + ") \n" +
- " \nif @@ROWCOUNT=0 begin\n" +
- " select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid" + fileds + " from _sys_Attachment where unid in(" + joiner.toString() + ") end\n",
+ baseService.getSimpleJdbcTemplate().query(" declare @table table(unid varchar(150),FileType varchar(10),pic image)\n" +
+ " insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid" + fileds + " from _sys_Attachment9\n" +
+ " where unid in("+joiner.toString()+")\n" +
+ " if @@ROWCOUNT=0 \n" +
+ " insert into @table select cast(unid as varchar(100))+';'+cast(seq as varchar(10)) as unid" + fileds + " from _sys_Attachment\n" +
+ " where unid in("+joiner.toString()+")\n" +
+ " select * from @table",
new AbstractLobStreamingResultSetExtractor<List<AttendEntity>>() {
@Override
+ protected void handleNoRowFound() throws LobRetrievalFailureException {
+ //鏈夊彲鑳借繑鍥炵┖闆嗗悎锛屽湪杩欓噷涓嶉渶瑕佸鐞� by danaus 2023-09-09 14:07
+ }
+ @Override
protected void streamData(ResultSet resultSet) throws SQLException, IOException, DataAccessException {
- do{
- AttendEntity attendEntity = new AttendEntity();
- attendEntity.setFileType(resultSet.getString("FileType"));
- attendEntity.setUnid(resultSet.getString("unid"));
- attendEntity.setData(resultSet.getBytes("pic"));
- maps.add(attendEntity);
- }while (resultSet.next());
+ if(!resultSet.wasNull()) {
+ do {
+ AttendEntity attendEntity = new AttendEntity();
+ attendEntity.setFileType(resultSet.getString("FileType"));
+ attendEntity.setUnid(resultSet.getString("unid"));
+ attendEntity.setData(resultSet.getBytes("pic"));
+ maps.add(attendEntity);
+ } while (resultSet.next());
+ }
}
});
if (maps != null && maps.size() > 0) {
for (AttendEntity attendEntity: maps) {
- //AttendEntity attendEntity=new AttendEntity();
- // attendEntity.setFileType(map.get( "FileType")+"");
- //attendEntity.setData((byte[]) map.get("file"));
fileType.put(attendEntity.getUnid(), attendEntity);
}
}
@@ -528,8 +541,12 @@
SXSSFRow rows = ws.getRow(rowindex);
if (rows == null)
rows = ws.createRow(rowindex);
- SXSSFCell cell = rows.createCell(cellindex);
- cell.setCellValue(titles);
+ SXSSFCell cell = rows.getCell(cellindex);
+ if(cell==null)
+ cell = rows.createCell(cellindex);
+ if(StringUtils.isNotBlank(titles)) {
+ cell.setCellValue(titles);
+ }
if (cellStyle != null)
cell.setCellStyle(cellStyle);
@@ -584,29 +601,24 @@
String[] strArray = uuid.split(";");
for (int i = 1; i < strArray.length; i++) {
- /* String txt = strArray[0] + ";" + strArray[i];//鍥剧墖鐩存帴浠庢暟鎹簱杩斿洖鍙栧緱锛屼笉閫氳繃姣忎釜浠庣綉缁滆鍙栧洖鏉� by danaus 2023-09-08 11:50
+ String txt = strArray[0] + ";" + strArray[i];
//|/getImage.do?type=0&uuid=19D7C74C-FA55-4027-ADCB-BDDEBD3C42C5;15522;png|48|48|
- String imgUrl = null;
- try {
+ String imgUrl = null;
if (txt.contains("|/getImage.do")) {
String[] uuids = txt.split("\\|");
java.util.regex.Matcher propsMatcher = p.matcher(uuids[1]);
while (propsMatcher.find()) {
txt = propsMatcher.group().replace("uuid=", "");
}
- txt = txt.replace(";", "@p@_80x80").replace(";", ".");
+ txt = txt.replace(";", "@p@").replace(";", ".");
} else {
//txt = txt.replace(";", "@p@") + ".jpg";
String fileExt="."+fileType.get(txt).getFileType();
- txt=txt.replace(";", "@p@_80x80")+fileExt ;
+ txt=txt.replace(";", "@p@")+fileExt ;
}
imgUrl = imageHostUrl + "/uploads/attachment/" + dbid + "/" + formid + "/" + txt;
- imgByte = getImage(imgUrl);
- } catch (IOException e) {
- e.printStackTrace();
- throw new ApplicationException(e.getMessage());
- }*/
+
if (imgByte != null && imgByte.length > 0) {
SXSSFRow row = ws.getRow(rowNum);
if (row == null)
@@ -636,13 +648,27 @@
double a = standardWidth / cellWidth;
double b = standardHeight / cellHeight;
- Picture picture = drawing.createPicture(anchor, addPicture);
+ Picture picture =drawing.createPicture(anchor, addPicture);
if (strArray.length > 2) {
picture.resize(0.1);
} else {
picture.resize(a, b);
}
-
+ // 鍦ㄥ崟鍏冩牸涓缃秴閾炬帴
+ Cell hyperLinkCell = row.createCell(j+1);
+ hyperLinkCell.setCellValue("鎵撳紑");
+ Hyperlink hyperlink = wb.getCreationHelper().createHyperlink(HyperlinkType.URL);
+ hyperlink.setAddress(imgUrl); // 鎸囧畾鐨刄RL閾炬帴
+ //澧炲姞瓒呴摼鎺ユ墦寮�鍘熷浘鎴栧叾浠栦笉鏄浘鐗囩殑闄勪欢
+ // 鍒涘缓瓒呴摼鎺ュ苟璁剧疆璧峰浣嶇疆鍜� URL
+ hyperLinkCell.setHyperlink(hyperlink);
+ // 鍒涘缓涓�涓牱寮忔潵鎸囩ず瓒呴摼鎺�
+ CellStyle hlinkStyle = wb.createCellStyle();
+ Font hlinkFont = wb.createFont();
+ hlinkFont.setUnderline(Font.U_SINGLE);
+ hlinkFont.setColor(IndexedColors.BLUE.getIndex());
+ hlinkStyle.setFont(hlinkFont);
+ hyperLinkCell.setCellStyle(hlinkStyle);
} else {
setCell("", ws, rowNum, j, null);
}
diff --git a/src/com/yc/action/grid/TreeGrid.java b/src/com/yc/action/grid/TreeGrid.java
index fdf8f9d..8b3940a 100644
--- a/src/com/yc/action/grid/TreeGrid.java
+++ b/src/com/yc/action/grid/TreeGrid.java
@@ -4,7 +4,6 @@
package com.yc.action.grid;
import com.yc.action.BaseAction;
-import com.yc.action.build.DTO;
import com.yc.exception.ApplicationException;
import com.yc.factory.FactoryBean;
import com.yc.multiData.SpObserver;
@@ -1389,6 +1388,9 @@
exportInfo.append(id.toLowerCase()).append("#").append(temp.replaceAll("-", "_")).append("#").append(displayformat.replaceAll("-", "~")).append(";");//-杞垚~鏄负浜嗗吋瀹逛箣鍓嶇殑浠ｇ爜
}else {
exportInfo.append(id.toLowerCase()).append("#").append(temp.replaceAll("-", "_")).append(";");
+ if(type==9||type==19||type==40){
+ exportInfo.append(id.toLowerCase()).append("#").append(temp.replaceAll("-", "_")+"涓嬭浇").append(";");
+ }
}
if (SumField > 0) {
SumFieldInfo.append(id.toLowerCase()).append("#").append(SumField).append("|").append(funclinkname).append("|").append(fumIndex).append("_T_").append(tabColIndex-1).append("|").append((displayformat != null && !displayformat.isEmpty()) ? displayformat.replaceAll("-", "~") : "0").append(";");
diff --git a/src/com/yc/log/ErrorLoggerBean.java b/src/com/yc/log/ErrorLoggerBean.java
index 4138458..1a5f74d 100644
--- a/src/com/yc/log/ErrorLoggerBean.java
+++ b/src/com/yc/log/ErrorLoggerBean.java
@@ -8,6 +8,7 @@
import com.yc.utils.Page;
import org.aspectj.lang.ProceedingJoinPoint;
+import java.util.HashMap;
import java.util.Map;
@@ -47,6 +48,9 @@
private String conRevtPage(Page page) {
Map exceptoinMmap = page.getTbColsMap();
+ if(exceptoinMmap==null){
+ exceptoinMmap=new HashMap();
+ }
exceptoinMmap.put("@TableName", page.getTableName());
exceptoinMmap.put("@FieldList1", page.getSql());
exceptoinMmap.put("@Where", page.getWhere());
diff --git a/src/com/yc/open/controller/EchoController.java b/src/com/yc/open/controller/EchoController.java
index d6771e2..8a75214 100644
--- a/src/com/yc/open/controller/EchoController.java
+++ b/src/com/yc/open/controller/EchoController.java
@@ -1,22 +1,36 @@
package com.yc.open.controller;
+import com.alibaba.fastjson.JSON;
import com.yc.action.BaseAction;
import com.yc.action.build.FormidVersion;
+import com.yc.api.utils.SQLUtils;
+import com.yc.exception.CallBackMessage;
import com.yc.factory.FactoryBean;
import com.yc.multiData.MultiDataSource;
+import com.yc.multiData.SpObserver;
import com.yc.open.utils.ApiUtils;
import com.yc.open.utils.CallBackMessageToObject;
import com.yc.sdk.WebSocketMessage.action.WebSocketMessageServer;
+import com.yc.service.BaseService;
+import com.yc.utils.SessionKey;
+import net.coobird.thumbnailator.Thumbnails;
+import org.springframework.dao.DataAccessException;
+import org.springframework.jdbc.LobRetrievalFailureException;
+import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
+import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Map;
-import java.util.Random;
+import java.awt.image.BufferedImage;
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
+import java.io.IOException;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.*;
/**
* 娴嬭瘯鐢ㄤ緥
@@ -126,4 +140,94 @@
});
return callBackMessage.sendSuccessMessage(this+"=>"+id);
}
+ /**
+ * 閲嶆柊鎶婇檮浠惰〃鐨勭缉鐣ュ浘鏇存柊涓�娆★紝鍥犱负鏈夊緢澶氱缉鐣ュ浘鏃ф暟鎹槸鏈夐棶棰�
+ * @return
+ */
+ @RequestMapping(value = "/attachn/refreshAttachments.do",method = RequestMethod.GET)
+ public @ResponseBody Object refreshAttachmentFromOriginalPictureToSmallPicture(HttpServletRequest request){
+ CallBackMessage callBackMessage = new CallBackMessage();
+ try {
+ SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
+ BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
+ Map result= new HashMap();
+ long start=System.currentTimeMillis();
+ List<Map<String, Object>> list = baseService.getSimpleJdbcTemplate().queryForList("select unid,seq from _sys_Attachment9 where filetype in('jpg','png','JPG','PNG') \n" +
+ "union \n" +
+ "select unid,seq from _sys_Attachment where filetype in('jpg','png','JPG','PNG')\n");
+ if(list!=null&&list.size()>0) {
+ List<List<Map<String, Object>>> lists = SQLUtils.splitList(list, 50);//鍒嗛〉澶勭悊锛屼竴娆″鐞�20鏉�
+ lists.stream().forEach(subList->{
+ final StringBuilder sql=new StringBuilder("");
+ subList.stream().forEach(x -> {
+ String unid = x.get("unid") + "";
+ String seq = x.get("seq") + "";
+ baseService.getJdbcTemplate().query(" declare @unid varchar(100)='" + unid + "',@seq int=" + seq +
+ " \n declare @table table(fileType varchar(10),OriginalPicture image,ctype varchar(10))\n" +
+ " insert into @table select filetype,OriginalPicture,'9' as ctype from _sys_Attachment9\n" +
+ " where unid=@unid and seq=@seq\n" +
+ " if @@ROWCOUNT=0 \n" +
+ " insert into @table select filetype,OriginalPicture,'' as ctype from _sys_Attachment\n" +
+ " where unid=@unid and seq=@seq\n" +
+ "select * from @table", new AbstractLobStreamingResultSetExtractor() {
+ @Override
+ protected void handleNoRowFound() throws LobRetrievalFailureException {
+ System.out.println(unid + ";" + seq + "-娌℃暟鎹�");
+ }
+ @Override
+ protected void streamData(ResultSet resultSet) throws SQLException, IOException, DataAccessException {
+ ByteArrayOutputStream outputStream = null;
+ ByteArrayInputStream bais =null;
+ try {
+ do {
+ byte[] byteData = resultSet.getBytes("OriginalPicture");//鍘熷浘
+ String fileType = resultSet.getString("filetype");//鍥剧墖绫诲瀷
+ String type=resultSet.getString("ctype");//9杩樻槸19绫诲瀷
+ bais = new ByteArrayInputStream(byteData);
+ BufferedImage image = ImageIO.read(bais);
+ outputStream = new ByteArrayOutputStream();
+ Thumbnails.of(image).scale(0.25).outputQuality(1).outputFormat(fileType).toOutputStream(outputStream);
+ byte[] outData = outputStream.toByteArray();
+ // 灏嗕簩杩涘埗鏁版嵁杞崲涓哄崄鍏繘鍒跺瓧绗︿覆
+ StringBuilder hexString = new StringBuilder();
+ for (byte b : outData) {
+ hexString.append(String.format("%02x", b));
+ }
+
+ sql.append("\n update a set SmallPicture =0x"+hexString.toString()+" from _sys_Attachment"+type+" a where unid='" + unid + "' and seq=" + seq);
+ } while (resultSet.next());
+ } catch (Exception e) {
+ e.printStackTrace();
+ }finally {
+ if(outputStream!=null) outputStream.close();
+ if(bais!=null) bais.close();
+ }
+ }
+ });
+ });
+ try {
+ SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
+ baseService.getJdbcTemplate().update(sql.toString());
+ log.warn("鎵归噺鏇存柊50鏉℃垚鍔�");
+ } catch (Exception e) {
+ e.printStackTrace();
+ } finally {
+ SpObserver.setDBtoInstance();
+ }
+ });
+ }
+ long end=System.currentTimeMillis();
+ result.put("time",(end-start)/(3600000));
+ result.put("count",list==null?0:list.size());
+ log.warn(JSON.toJSONString(result));
+ callBackMessage.setInfo(result).sendSuccessMessageByDefault();
+ return callBackMessage.toJSONObject();
+ } catch (Exception e) {
+ callBackMessage.sendErrorMessage(e.getMessage());
+ return callBackMessage.toJSONObject();
+ } finally {
+ SpObserver.setDBtoInstance();
+ }
+ }
+
}
diff --git a/src/com/yc/open/jinwu/service/JinWuImpl.java b/src/com/yc/open/jinwu/service/JinWuImpl.java
index d67f52b..8ebc38f 100644
--- a/src/com/yc/open/jinwu/service/JinWuImpl.java
+++ b/src/com/yc/open/jinwu/service/JinWuImpl.java
@@ -304,7 +304,7 @@
" from t640210H a join t640210D b on a.DocCode=b.DocCode " +
//" join t110503 d on d.MatCode=b.matcode "+
" join t110501 e on e.MatGroup=b.MatGroup "+
- " where b.docCode='"+docCode+"' and (isnull(b.YonYa_PushStatus,0)=0 or b.YonYa_PushStatus=-1 ) \n";
+ " where b.docCode='"+docCode+"' and isnull(a.isActivePush,0) = 0 and (isnull(b.YonYa_PushStatus,0)=0 or b.YonYa_PushStatus=-1 ) \n";
try {
//log.info("640210sql>>"+sql);
List<Map<String,Object>> list= this.jdbcTemplate.queryForList(sql);
diff --git a/src/com/yc/open/qiyunfang/controller/CashFlowDetailsEntry.java b/src/com/yc/open/qiyunfang/controller/CashFlowDetailsEntry.java
index bca26c0..9934556 100644
--- a/src/com/yc/open/qiyunfang/controller/CashFlowDetailsEntry.java
+++ b/src/com/yc/open/qiyunfang/controller/CashFlowDetailsEntry.java
@@ -22,5 +22,7 @@
String ENTRYFLAG;//鍒嗗綍鏍囪瘑 鍑瘉鍙穇鍒嗗綍琛屽彿: 10002_001
@JsonProperty("TXT")
String TXT;//鍒嗗綍鏂囨湰(鎽樿)
+ @JsonProperty("INTERCO")
+ String INTERCO;
}
diff --git a/src/com/yc/open/qiyunfang/controller/T150102Entry.java b/src/com/yc/open/qiyunfang/controller/T150102Entry.java
index a7637b2..40c9747 100644
--- a/src/com/yc/open/qiyunfang/controller/T150102Entry.java
+++ b/src/com/yc/open/qiyunfang/controller/T150102Entry.java
@@ -39,5 +39,10 @@
String RHCUR;//璁拌处鏈竵甯佺
@JsonProperty("HSL")
Double HSL;//璁拌处鏈竵閲戦
-
+ /* @JsonProperty("SUBTABLE")//TODO 鏆傛椂涓嶅紑鍚�
+ String SUBTABLE;//涓氬姟娴佸瓧娈�
+ @JsonProperty("AMOUNT")
+ Double AMOUNT;
+ @JsonProperty("MANDT")
+ Integer MANDT;*/
}
diff --git a/src/com/yc/open/qiyunfang/service/QiyunfangImpl.java b/src/com/yc/open/qiyunfang/service/QiyunfangImpl.java
index 30294b3..2b78190 100644
--- a/src/com/yc/open/qiyunfang/service/QiyunfangImpl.java
+++ b/src/com/yc/open/qiyunfang/service/QiyunfangImpl.java
@@ -20,26 +20,26 @@
" declare @BaseCurrencyIsoCode varchar(20) \n" +
" select @BaseCurrencyIsoCode = CurrencyIsoCode from t110703 where isnull(domestic,0) = 1 \n" +
" select a.periodid as TIME, --鏃堕棿,浼氳鏈熼棿\n" +
- " a.companyid as ENTITY, --鍏徃鍚嶇О,杩欓噷浼犲叕鍙哥紪鍙穃n" +
- " a.docnum as ENTRYFLAG, --鍑瘉鏍囪瘑,鍑瘉鍙�+鍒嗗綍琛岄」鐩彿\n" +
- " a.acctcode as D_ACCOUNT, --绉戠洰鍚嶇О,瀹為檯涓婃槸绉戠洰缂栧彿\n" +
- " a.TradePartnerCode as INTERCO, --璐告槗浼欎即,鍐呴儴鍗曚綅缂栧彿(鐢ㄤ簬闆嗗洟鍐呴儴鍏宠仈浜ゆ槗)\n" +
- " a.CltCode as KUNNR, --瀹㈡埛缂栧彿\n" +
- " a.VndCode as LIFNR, --渚涘簲鍟嗙紪鍙穃n" +
- " a.FundTypeCode as XREF2, --SAP娆鹃」鎬ц川(瑙丼AP娆鹃」鎬ц川鍙傝��)\n" +
- " a.FAlterId as ANBWA, --璧勪骇鍙樺姩绫诲瀷(涓庡浐瀹氳祫浜с�佹棤褰㈣祫浜с�佸潖璐﹀噯澶囥�佽穼浠峰噯澶囩殑鍙樺姩鐩稿叧,瑙丼AP璧勪骇浜ゆ槗绫诲瀷)\n" +
- " case isnull(a.DcFlag,'') when '鍊�' then 'S' else 'H' end as DRCRK, --鍊熻捶鏍囪瘑锛圫-鍊熸柟銆丠璐锋柟锛塡n" +
- " a.MatCode as MATNR , --鐗╂枡缂栧彿\n" +
- " d.CurrencyIsoCode as RWCUR, --浜ゆ槗甯佸竵绉�(WSL)\n" +
- " case a.dcflag when '鍊�' then a.AmountDebit else a.AmountCredit end as WSL, --浜ゆ槗鍘�(WSL)\n" +
- " @BaseCurrencyIsoCode as RHCUR, --璁拌处鏈竵甯佺\n" +
- " case a.dcflag when '鍊�' then a.NatAmountDebit else a.NatAmountCredit end as HSL --璁拌处鏈竵閲戦\n" +
- " --a.DocWord, a.dcflag,b.acctname,c.companyname,\n" +
- " --a.cv1,a.cv1name,a.cv2,a.cv2name,a.cv3,a.cv3name,a.cv4,a.cv4name,a.cv5,a.cv5name,a.Currency \n" +
+ " a.companyid as ENTITY, --鍏徃鍚嶇О,杩欓噷浼犲叕鍙哥紪鍙穃n" +
+ " a.docnum as ENTRYFLAG, --鍑瘉鏍囪瘑,鍑瘉鍙�+鍒嗗綍琛岄」鐩彿\n" +
+ " case when a.acctcode like '6699%' then 'A'+ a.acctcode else a.acctcode end as D_ACCOUNT, --绉戠洰鍚嶇О,瀹為檯涓婃槸绉戠洰缂栧彿\n" +
+ " a.TradePartnerCode as INTERCO, --璐告槗浼欎即,鍐呴儴鍗曚綅缂栧彿(鐢ㄤ簬闆嗗洟鍐呴儴鍏宠仈浜ゆ槗)\n" +
+ " a.CltCode as KUNNR, --瀹㈡埛缂栧彿\n" +
+ " a.VndCode as LIFNR, --渚涘簲鍟嗙紪鍙穃n" +
+ " a.FundTypeCode as XREF2, --SAP娆鹃」鎬ц川(瑙丼AP娆鹃」鎬ц川鍙傝��)\n" +
+ " a.FAlterId as ANBWA, --璧勪骇鍙樺姩绫诲瀷(涓庡浐瀹氳祫浜с�佹棤褰㈣祫浜с�佸潖璐﹀噯澶囥�佽穼浠峰噯澶囩殑鍙樺姩鐩稿叧,瑙丼AP璧勪骇浜ゆ槗绫诲瀷)\n" +
+ " case isnull(a.DcFlag,'') when '鍊�' then 'S' else 'H' end as DRCRK, --鍊熻捶鏍囪瘑锛圫-鍊熸柟銆丠璐锋柟锛塡n" +
+ " a.MatCode as MATNR , --鐗╂枡缂栧彿\n" +
+ " d.CurrencyIsoCode as RWCUR, --浜ゆ槗甯佸竵绉�(WSL)\n" +
+ " case a.dcflag when '鍊�' then a.AmountDebit else 0.0 - isnull(a.AmountCredit,0) end as WSL, --浜ゆ槗鍘�(WSL)\n" +
+ " @BaseCurrencyIsoCode as RHCUR, --璁拌处鏈竵甯佺\n" +
+ " case a.dcflag when '鍊�' then a.NatAmountDebit else 0.0 - isnull(a.NatAmountCredit,0) end as HSL , --璁拌处鏈竵閲戦\n" +
+ " '800' as MANDT , --绯荤粺鏍囪瘑\n" +
+ " case a.dcflag when '鍊�' then a.NatAmountDebit else 0.0 - isnull(a.NatAmountCredit,0) end as AMOUNT --鍑瘉閲戦,鍑瘉閲戦锛岀瓑浜嶩SL\n" +
" from t150102 a join v110709 b on a.companyid = b.companyid and a.AcctCode = b.Acctcode\n" +
" join oCompany c on a.CompanyID = b.companyid \n" +
" join t110703 d on a.Currency = d.currency\n" +
- " where (isnull(@periodid,'') = '' or a.periodid = @periodid)\n" +
+ " where a.periodid = @periodid \n" +
" and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)\n" +
" and (isnull(@companyname,'') = '' or c.Companyname like '%'+ @companyname + '%')\n" +
" and (isnull(@acctcode,'') = '' or a.acctcode = @acctcode)\n" +
@@ -48,7 +48,8 @@
" and (isnull(@cv2,'') = '' or a.cv2 = @cv2)\n" +
" and (isnull(@cv3,'') = '' or a.cv3 = @cv3)\n" +
" and (isnull(@cv4,'') = '' or a.cv4 = @cv4)\n" +
- " and (isnull(@cv5,'') = '' or a.cv5 = @cv5)", new BeanPropertyRowMapper<>(T150102Entry.class));
+ " and (isnull(@cv5,'') = '' or a.cv5 = @cv5)\n" +
+ " and a.DocType <> '鏈堟湯缁撹浆' --鎺掗櫎缁撹浆鎹熺泭", new BeanPropertyRowMapper<>(T150102Entry.class));
}
@Override
@@ -79,7 +80,7 @@
" a.Amount5 as AMT_5 , --25-36涓湀(閲戦)\n" +
" a.Amount6 as AMT_6 , --37-48涓湀(閲戦)\n" +
" a.Amount7 as AMT_7 --48涓湀浠ヤ笂(閲戦)\n" +
- " from f140933(@companyid,@periodid,'','','',30,180,360,720,1080,1440) a", new BeanPropertyRowMapper<>(InventoryAgeDataEntry.class));
+ " from f140933(@companyid,@periodid,'','','',30,150,180,360,360,360) a", new BeanPropertyRowMapper<>(InventoryAgeDataEntry.class));
}
@Override
public List<CustomerDataEntry> getCustomerData() {
@@ -129,13 +130,16 @@
@Override
public List<CashFlowDetailsEntry> getCashFlowDetails(Post150102Entry entry) {
return this.jdbcTemplate.query(" declare @periodid varchar(20) =" + GridUtils.prossSqlParm(entry.getPeriodid()) + ",@companyid varchar(20) =" + GridUtils.prossSqlParm(entry.getCompanyid()) +"\n"+
- " select a.PeriodId as TIME, --鏃堕棿,浼氳鏈熼棿\n" +
- " a.CompanyId as ENTITY , --鍏徃鍚嶇О,瀹為檯鏄叕鍙哥紪鍙穃n" +
- " a.FluxId as ACCOUNT , --鐜伴噾娴侀噺缂栫爜,闇�瑕佽窡SAP鐨勭幇閲戞祦缂栫爜淇濇寔涓�鑷碶n" +
- " a.BusinessPartnerCode as BP , --瀹㈠晢缂栫爜\n" +
- " a.FluxAmount as AMOUNT , --閲戦\n" +
- " isnull(a.DocCode,'')+'-'+isnull(a.DocRowId,'') as ENTRYFLAG , --鍒嗗綍鏍囪瘑,鍑瘉鍙�+鍒嗗綍琛屽彿\n" +
- " a.Resume as TXT --鍒嗗綍鏂囨湰(鎽樿) \n" +
+ " select a.PeriodId as TIME, --鏃堕棿,浼氳鏈熼棿\n" +
+ " a.CompanyId as ENTITY , --鍏徃鍚嶇О,瀹為檯鏄叕鍙哥紪鍙穃n" +
+ " a.FluxId as ACCOUNT , --鐜伴噾娴侀噺缂栫爜,闇�瑕佽窡SAP鐨勭幇閲戞祦缂栫爜淇濇寔涓�鑷碶n" +
+ " a.BusinessPartnerCode as BP , --瀹㈠晢缂栫爜\n" +
+ " --AMOUNT 閲戦,鎶� B銆丏銆丗寮�澶寸殑鐜伴噾娴侀」鐩彉鎴愯礋鏁�,杩欎簺鏄睘浜庢敮鍑猴紝鍥犱负鍦⊿AP涓敤璐熸暟鏄剧ず鐨刓n" +
+ " case when a.FluxId in ('B1','B2','B3','B4','B5','D1','D2','D3','D4','F1','F2','F3') \n" +
+ " then 0.0 - isnull(a.FluxAmount,0) else a.FluxAmount end as AMOUNT , \n" +
+ " isnull(a.DocCode,'')+'-'+isnull(a.DocRowId,'') as ENTRYFLAG , --鍒嗗綍鏍囪瘑,鍑瘉鍙�+鍒嗗綍琛屽彿\n" +
+ " a.Resume as TXT, --鍒嗗綍鏂囨湰(鎽樿) \n" +
+ " a.TradePartnerCode as INTERCO --璐告槗浼欎即,鍐呴儴鍗曚綅缂栧彿(鐢ㄤ簬闆嗗洟鍐呴儴鍏宠仈浜ゆ槗)\n" +
" from t150107 a\n" +
" where (isnull(@periodid,'') = '' or a.periodid = @periodid)\n" +
" and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)", new BeanPropertyRowMapper<>(CashFlowDetailsEntry.class));
@@ -182,33 +186,30 @@
@Override
public List<VoucherOutstandingEntry> getVoucherOutstanding(Post150102Entry entry) {
return this.jdbcTemplate.query(" declare @periodid varchar(20) =" + GridUtils.prossSqlParm(entry.getPeriodid()) + ",@companyid varchar(20) =" + GridUtils.prossSqlParm(entry.getCompanyid()) + "\n"+
- " declare @AccountsReceivableAcctCode varchar(20) --搴旀敹甯愭绉戠洰\n" +
- " select @AccountsReceivableAcctCode = AccountsReceivableAcctCode from t111601 \n" +
- " \n" +
- " declare @BaseCurrencyIsoCode varchar(20) \n" +
+ " declare @BaseCurrencyIsoCode varchar(20) \n" +
" select @BaseCurrencyIsoCode = CurrencyIsoCode from t110703 where isnull(domestic,0) = 1 \n" +
" select a.periodid as TIME, --鏃堕棿,浼氳鏈熼棿\n" +
- " a.companyid as ENTITY, --鍏徃鍚嶇О,杩欓噷浼犲叕鍙哥紪鍙穃n" +
- " a.acctcode as RACCT, --绉戠洰缂栫爜\n" +
- " a.CltCode as KUNNR , --瀹㈡埛缂栫爜\n" +
- " a.VndCode as LIFNR , --渚涘簲鍟嗙紪鐮乗n" +
- " a.FundTypeCode as XREF2 , --SAP娆鹃」鎬ц川鎻忚堪\n" +
- " a.docnum as BELNR, --SAP鍑瘉鍙穃n" +
- " a.DocRowID as BUZEI , --SAP鍑瘉琛孿n" +
- " d.CurrencyIsoCode as RTCUR, --鍘熷竵甯佺 \n" +
- " @BaseCurrencyIsoCode as RHCUR , --鏈竵甯佺\n" +
- " case isnull(a.DcFlag,'') when '鍊�' then a.AmountDebit else a.AmountCredit end as TSL , --鍘熷竵閲戦\n" +
- " case isnull(a.DcFlag,'') when '鍊�' then a.NatAmountDebit else a.NatAmountCredit end as HSL, --鏈竵閲戦\n" +
- " a.BillingNetDate as NETDT, --璐﹂緞鍒版湡鏃ワ紝璐﹂緞鍒版湡鏃ワ紙鍔犱笂淇＄敤鏈熶箣鍚庣殑璐﹂緞璧风畻鏃ワ級\n" +
- " a.DocDate as BLDAT , --鍑瘉鏃ユ湡\n" +
- " a.BillRealDate as REALDATE ,-- 璐﹂緞璁＄畻寮�濮嬫棩锛堢瓑浜庤处榫勫埌鏈熸棩锛塡n" +
- " a.CurrencyRate as KURSK --姹囩巼,鎶樼畻姹囩巼\n" +
+ " a.companyid as ENTITY, --鍏徃鍚嶇О,杩欓噷浼犲叕鍙哥紪鍙穃n" +
+ " a.acctcode as RACCT, --绉戠洰缂栫爜\n" +
+ " a.CltCode as KUNNR , --瀹㈡埛缂栫爜\n" +
+ " a.VndCode as LIFNR , --渚涘簲鍟嗙紪鐮乗n" +
+ " a.FundTypeCode as XREF2 , --SAP娆鹃」鎬ц川鎻忚堪\n" +
+ " a.docnum as BELNR, --SAP鍑瘉鍙穃n" +
+ " a.DocRowID as BUZEI , --SAP鍑瘉琛孿n" +
+ " d.CurrencyIsoCode as RTCUR, --鍘熷竵甯佺 \n" +
+ " @BaseCurrencyIsoCode as RHCUR , --鏈竵甯佺\n" +
+ " case isnull(a.DcFlag,'') when '鍊�' then a.AmountDebit else a.AmountCredit end as TSL , --鍘熷竵閲戦\n" +
+ " case isnull(a.DcFlag,'') when '鍊�' then a.NatAmountDebit else a.NatAmountCredit end as HSL, --鏈竵閲戦\n" +
+ " case when a.BillRealDate is not null then a.BillingNetDate else a.DocDate end as NETDT, --璐﹂緞鍒版湡鏃ワ紝璐﹂緞鍒版湡鏃ワ紙鍔犱笂淇＄敤鏈熶箣鍚庣殑璐﹂緞璧风畻鏃ワ級\n" +
+ " a.DocDate as BLDAT , --鍑瘉鏃ユ湡\n" +
+ " case when a.BillRealDate is not null then a.BillRealDate else case when a.BillRealDate is not null then a.BillingNetDate else a.DocDate end end as REALDATE ,-- 璐﹂緞璁＄畻寮�濮嬫棩锛堢瓑浜庤处榫勫埌鏈熸棩锛塡n" +
+ " a.CurrencyRate as KURSK --姹囩巼,鎶樼畻姹囩巼\n" +
" from t150102 a join v110709 b on a.companyid = b.companyid and a.AcctCode = b.Acctcode\n" +
" join oCompany c on a.CompanyID = b.companyid \n" +
" join t110703 d on a.Currency = d.currency\n" +
" where a.periodid = @periodid \n" +
- " and a.AcctCode = isnull(@AccountsReceivableAcctCode,'')\n" +
" and (isnull(@companyid,'') = '' or a.CompanyID = @companyid)\n" +
- " and isnull(FaPiaoMatchedAmount,0) <> (case isnull(a.DcFlag,'') when '鍊�' then isnull(a.AmountDebit,0) else isnull(a.AmountCredit,0) end) --宸茬粡鍖归厤鐨勯噾棰漒n", new BeanPropertyRowMapper<>(VoucherOutstandingEntry.class));
+ " and isnull(FaPiaoMatchedAmount,0) <> (case isnull(a.DcFlag,'') when '鍊�' then isnull(a.AmountDebit,0) else isnull(a.AmountCredit,0) end) --宸茬粡鍖归厤鐨勯噾棰漒n" +
+ " and isnull(b.isOpenItemMngment,0) = 1", new BeanPropertyRowMapper<>(VoucherOutstandingEntry.class));
}
}
diff --git a/src/com/yc/utils/JsonUtil.java b/src/com/yc/utils/JsonUtil.java
index 8aa3ca1..6662994 100644
--- a/src/com/yc/utils/JsonUtil.java
+++ b/src/com/yc/utils/JsonUtil.java
@@ -248,7 +248,7 @@
// }finally{
// SpObserver.setDBtoInstance();
// }
- if(fileType!=null) {
+ if(fileType!=null&&fileType.get(temp)!=null) {
temp += ";" + fileType.get(temp).getFileType();
}
return "|/getImage.do?type=0&uuid=" + temp + JsonUtil.IMGRANG;
--
Gitblit v1.8.0