package com.yc.action.grid;
|
|
import com.yc.api.utils.SQLUtils;
|
import com.yc.api.utils.VersionUtils;
|
import com.yc.exception.ApplicationException;
|
import com.yc.factory.FactoryBean;
|
import com.yc.multiData.SpObserver;
|
import com.yc.sdk.shopping.util.SettingKey;
|
import com.yc.service.BaseService;
|
import com.yc.service.grid.Grid38ServiceIfc;
|
import com.yc.service.grid.GridData;
|
import com.yc.service.grid.GridServiceIfc;
|
import com.yc.service.new38type.Type38Ifc;
|
import com.yc.service.panel.v2.PanelBean;
|
import com.yc.service.panel.v2.TypeControlDao;
|
import com.yc.utils.EncodeUtil;
|
import com.yc.utils.JOSNUtils;
|
import com.yc.utils.Page;
|
import com.yc.utils.SessionKey;
|
import org.apache.commons.lang.RandomStringUtils;
|
import org.apache.commons.lang.StringUtils;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.ui.Model;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import javax.servlet.http.HttpSession;
|
import java.io.UnsupportedEncodingException;
|
import java.text.DecimalFormat;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.StringJoiner;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
/***
|
* 处理代码, gt-grid提交所有变化过的数据
|
*
|
* @author 邓文峰 2010-3-24
|
**/
|
|
@Controller
|
@RequestMapping("/gt38Grid.do")
|
public class GT38Grid extends com.yc.action.BaseAction {
|
private int formID;// 功能号
|
@Autowired
|
private Grid38ServiceIfc gridService;// 表格处理的业务类
|
@Autowired
|
private GridServiceIfc grids;// 表格处理的业务类
|
@Autowired
|
TypeControlDao type38Ifc;
|
@Autowired
|
TypeControlDao typeControlDao;
|
@Autowired
|
Type38Ifc type38;
|
String StatisNames = "";
|
StringBuilder str1 = new StringBuilder();
|
Page page1 = new Page();
|
/**
|
* 正则表达式 是否有 sum,count,avg,min,max 这个字符
|
*/
|
private static Pattern SUM_COUNT_AVG_MIN_MAX_PATTERN = Pattern.compile("[^\\w](sum|avg|min|max|count)[^\\w]");
|
/**
|
* 正则表达式 是否有 as 这个字符
|
*/
|
private static Pattern AS_PATTERN = Pattern.compile("[^\\w]as\\s+");
|
/**
|
* 正则表达式 以@开头的单词
|
*/
|
private static Pattern START_WOROD_PATTERN = Pattern.compile("@.*?\\w+");
|
|
@RequestMapping(params = "m=get")
|
// Integer grapht 增加一个参数作为图表统计调用
|
public String createGrid(Integer grapht, Model model,
|
HttpServletRequest request, HttpServletResponse resp)
|
throws Exception {
|
// String
|
// datalist="4.按仓库、商品规格汇总,companyid,分组|4.按仓库、商品规格汇总,freestockSQM,汇总|4.按仓库、商品规格汇总,gradename2,分组|4.按仓库、商品规格汇总,special,分组|4.按仓库、商品规格汇总,stCode,分组|4.按仓库、商品规格汇总,stname,分组";
|
try {
|
String root = request.getSession().getServletContext().getRealPath("/")
|
+ "/";
|
int formid = -1;
|
int pos = request.getParameter("pos") == null ? 1 : Integer.parseInt(request.getParameter("pos"));
|
try {
|
formid = Integer.parseInt(EncodeUtil.base64Decode(request.getParameter("formIdHid")));
|
} catch (NumberFormatException e1) {
|
log.debug(request.getParameter("formIdHid") + "转换数字格式出错",
|
e1.getCause());
|
}
|
String StatisNames = EncodeUtil.base64Decode(request
|
.getParameter("StatisNames"));
|
String StatisID = EncodeUtil.base64Decode(request
|
.getParameter("StatisID"));
|
String datalist = "";
|
List<Map<String, Object>> list = gridService.selectdata(formid,
|
StatisID);
|
Map<String, Object> mapObject = null;
|
PanelBean panelBean = new PanelBean();
|
if (list == null || list.size() == 0) {
|
throw new ApplicationException("请在【9828】维护好查询参数!");
|
}
|
for (int i = 0; i < list.size(); i++) {
|
mapObject = list.get(i);
|
datalist += typeControlDao.getSelectAndCheckBoxss(
|
GridUtils.prossRowSetDataType_String(mapObject, "StatisID"),
|
GridUtils.prossRowSetDataType_String(mapObject, "FieldID"),
|
GridUtils.prossRowSetDataType_String(mapObject, "FieldAlias"),
|
GridUtils.prossRowSetDataType_String(mapObject, "StatisType"),
|
GridUtils.prossRowSetDataType_String(mapObject, "Sequence"),
|
GridUtils.prossRowSetDataType_String(mapObject, "DisplayWidth"),
|
GridUtils.prossRowSetDataType_String(mapObject, "displayformat"),
|
GridUtils.prossRowSetDataType_String(mapObject, "displayYN"),
|
GridUtils.prossRowSetDataType_String(mapObject, "isFilterZero"),
|
GridUtils.prossRowSetDataType_String(mapObject, "jionFlag"),
|
GridUtils.prossRowSetDataType_String(mapObject, "jionFlagGroup"),
|
GridUtils.prossRowSetDataType_String(mapObject, "conFlag"),
|
GridUtils.prossRowSetDataType_String(mapObject, "modfvalues"),
|
GridUtils.prossRowSetDataType_String(mapObject, "modfvalues2"),
|
GridUtils.prossRowSetDataType_String(mapObject, "fieldCaption"), panelBean)
|
;
|
}
|
|
this.StatisNames = StatisID;
|
try {
|
datalist = java.net.URLDecoder.decode(datalist.replace("%", "@~"),
|
"utf-8");
|
} catch (UnsupportedEncodingException e) {
|
log.debug(e.getCause().getMessage());
|
}
|
String encodeWhere = request.getParameter("where");
|
GridData data = new GridData();
|
//保存不解码的where,直接传回前端
|
data.setWhere38(encodeWhere);
|
String where = EncodeUtil.base64Decode(encodeWhere);
|
gridService.create38Grid(data, formid, root,
|
this.setWhere(this.replaceBlank(datalist)), StatisID);
|
data.setDatalist(this.replaceBlank(datalist));
|
data.setWhere(where);
|
Grid grid = new Grid();
|
grid.setFormID(formid);
|
grid.setWinType(data.winType + "");
|
grid.setField(data.field);
|
grid.setWhere(data.where);
|
grid.setDatalist(data.datalist);
|
grid.setTbCols(data.totalCols);
|
grid.setPos(pos);
|
grid.setQueryStringBy38(request.getQueryString());
|
grid.setTotalRowNum(request.getParameter("totalRowNum") == null ? 0 : Integer.parseInt(request.getParameter("totalRowNum")));
|
grid.setTotalPageNum(request.getParameter("totalPageNum") == null ? 0 : Integer.parseInt(request.getParameter("totalPageNum")));
|
Object object = loadByFunc(model, data, grid, request, resp, grapht, formid,
|
StatisNames, StatisID);
|
if (VersionUtils.getAPPTypeName(request) != null) {//APP端
|
Page page = (Page) object;
|
Map<String, Object> map = new HashMap<>();
|
map.put("data", page.getData());//数据
|
map.put("cols", data.getCols_app());//列名
|
map.put("align", page.getResultMap());//对齐方式
|
map.put("summary", page.getTbColsOut());//页脚汇总
|
this.printJson(resp, GridUtils.toJson(map));
|
return null;
|
} else {
|
return String.valueOf(object);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
this.printJson(resp, e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
|
}
|
return null;
|
}
|
|
/**
|
* 生成38类型需要表格的标题列 StatisID,fieldid,StatisType|StatisID,fieldid,StatisType
|
**/
|
private String setWhere(String data) {
|
if ("".equalsIgnoreCase(data))
|
return "";
|
StringBuilder sb = new StringBuilder();
|
String[] t = data.split("'");
|
int i = 0;
|
for (String s : t) {
|
String[] st = s.split("#P#");
|
if (i != 0)
|
sb.append(" or ");
|
sb.append(" (StatisID='" + st[0]).append("' and ")
|
.append(" fieldid= '" + st[1]).append("') ");
|
i++;
|
}
|
return sb.toString();
|
}
|
|
/**
|
* 生成38类型显示所需要的数据 StatisID,fieldid,StatisType|StatisID,fieldid,StatisType
|
**/
|
private String[] setShow(String data) {
|
if ("".equalsIgnoreCase(data))
|
return new String[]{"*", ""};
|
String[] t = data.split("'");
|
int i = 0;
|
int j = 0;
|
String sum = "";
|
String order = "";
|
StringJoiner orderfield = new StringJoiner(",");//排序的字段
|
String group = "";
|
String as = "";
|
String cv1 = "";
|
int index = 0;
|
int y = 0;
|
int z = 0;
|
for (String s : t) {
|
|
String[] st = s.split("#P#");
|
if ("分组".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))
|
|| "".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
if (i != 0) {
|
sum += ",";
|
order += ",";
|
group += ",";
|
}
|
if (z != 0 && i == 0) {
|
sum += ",";
|
order += ",";
|
z = 0;
|
}
|
boolean groupFlg = true;
|
boolean orderFlg = true;
|
//存在有sum count avg min max,不能放在group by中 by danaus 18-10-26
|
|
Matcher matcher = SUM_COUNT_AVG_MIN_MAX_PATTERN.matcher(st[1]);
|
if (matcher.find()) {//存在则在最后不需要增加这个值
|
//group += st[1];
|
//存在有 as +字段的情况需要去掉
|
groupFlg = false;
|
}
|
|
|
matcher = AS_PATTERN.matcher(st[1]);
|
if (matcher.find()) {//存在 as 字段别名的情况,
|
//String filed=st[1].substring(matcher.end(),st[1].length());// accessoriesAmount
|
String sql = st[1].substring(0, matcher.start());
|
order += sql + " asc ";
|
orderfield.add(sql);
|
orderFlg = false;
|
if (groupFlg) {
|
group += sql + " ";
|
groupFlg = false;
|
}
|
|
}
|
if (orderFlg){
|
order += st[1] + " asc ";
|
orderfield.add(st[1]);
|
}
|
if (groupFlg) group += st[1];
|
sum += st[1];
|
i++;
|
} else if ("汇总".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
|
Matcher matcher = AS_PATTERN.matcher(st[1]);
|
String filed;
|
String sumsql;
|
// case when isnull(acctcode,'') in ('5101004','1403004') then isnull(InAmount,0) else 0 end as accessoriesAmount
|
//1,去掉 as accessoriesAmount
|
//--> case when isnull(acctcode,'') in ('5101004','1403004') then isnull(InAmount,0) else 0 end
|
//3,sum组装起来 sum(isnull(case when isnull(acctcode,'') in ('5101004','1403004') then isnull(InAmount,0) else 0 end ,0)
|
//4,最后再加上之前的去掉的 as accessoriesAmount,变成:sum(isnull(case when isnull(acctcode,'') in ('5101004','1403004') then isnull(InAmount,0) else 0 end ,0)) as accessoriesAmount
|
if (matcher.find()) {//存在 as 字段别名的情况,在sum的情况下需要先去掉,组成好再重新加上
|
filed = st[1].substring(matcher.end(), st[1].length());// accessoriesAmount
|
sumsql = st[1].substring(0, matcher.start());
|
}
|
// else if(st[1].indexOf("case")>-1){// 自定义字段列名,但又不是写 as 列名的情况
|
//
|
// // case when isnull(acctcode,'') in ('5101004','1403004') then isnull(InAmount,0) else 0 end
|
// //filed="filed"+RandomStringUtils.randomNumeric(2);//生成一个随机字段名
|
// //sumsql=st[1];
|
//
|
// }
|
else {
|
filed = st[1];
|
sumsql = st[1];
|
}
|
|
if (j != 0 || sum.length() > 0) {
|
sum += ",";
|
sum += " sum(isnull(" + sumsql + ",0)) as " + filed;
|
order += ",";
|
order += " sum(isnull(" + sumsql + ",0)) asc";
|
j++;
|
} else {
|
sum += " sum(isnull(" + sumsql + ",0)) as " + filed;
|
order += " sum(isnull(" + sumsql + ",0)) asc";
|
}
|
orderfield.add(sumsql);
|
z++;
|
} else if ("自定义".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
//case when sum(isnull(balance,0)) >0 then sum(isnull(balance,0)) else 0 end as balance3
|
if (j != 0 || sum.length() > 0) {
|
as = "";
|
as += st[1];
|
index = as.toLowerCase().lastIndexOf("end");
|
cv1 = as.substring(0, index + 4).trim();
|
|
sum += ",";
|
sum += st[1];
|
order += ",";
|
order += cv1 + " asc";
|
y++;
|
j++;
|
|
} else {
|
as = "";
|
as += st[1];
|
index = as.indexOf("end");
|
cv1 = as.toLowerCase().substring(0, index + 4);
|
sum += st[1] + "as" + cv1;
|
order += cv1 + " asc";
|
}
|
orderfield.add(cv1);
|
z++;
|
} else if ("最大".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
if (j != 0 || sum.length() > 0) {
|
sum += ",";
|
sum += " max(isnull(" + st[1] + ",0)) as " + st[1];
|
order += ", ";
|
order += " max(isnull(" + st[1] + ",0)) asc";
|
j++;
|
} else {
|
sum += " max(isnull(" + st[1] + ",0)) as " + st[1];
|
order += " max(isnull(" + st[1] + ",0)) asc";
|
}
|
orderfield.add(st[1]);
|
z++;
|
} else if ("最小".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
if (j != 0 || sum.length() > 0) {
|
sum += ", ";
|
sum += " min(isnull(" + st[1] + ",0)) as " + st[1];
|
order += ", ";
|
order += " min(isnull(" + st[1] + ",0)) asc";
|
j++;
|
} else {
|
sum += " min(isnull(" + st[1] + ",0)) as " + st[1];
|
order += " min(isnull(" + st[1] + ",0)) asc";
|
j++;
|
}
|
orderfield.add(st[1]);
|
z++;
|
} else if ("平均".equalsIgnoreCase(st[3].replaceAll(" ", ""))) {
|
if (j != 0 || sum.length() > 0) {
|
sum += ", ";
|
sum += " avg(isnull(" + st[1] + ",0)) as " + st[1];
|
order += ", ";
|
order += " avg(isnull(" + st[1] + ",0)) asc";
|
j++;
|
} else {
|
sum += " avg(isnull(" + st[1] + ",0)) as " + st[1];
|
order += " avg(isnull(" + st[1] + ",0)) asc";
|
j++;
|
}
|
orderfield.add(st[1]);
|
z++;
|
} else if ("个数".equalsIgnoreCase(st[3].replaceAll("[\\s]", ""))) {
|
if (j != 0 || sum.length() > 0) {
|
sum += ", ";
|
sum += " count(isnull(" + st[1] + ",0)) as " + st[1];
|
order += ", ";
|
|
order += " count(isnull(" + st[1] + ",0)) asc";
|
|
j++;
|
} else {
|
sum += " count(isnull(" + st[1] + ",0)) as " + st[1];
|
order += " count(isnull(" + st[1] + ",0)) asc";
|
|
j++;
|
}
|
orderfield.add(st[1]);
|
z++;
|
}
|
|
// select a,b,c from t
|
// select case when esle end sum(a) as a,max(b) as b ....
|
|
}
|
return new String[]{sum.toLowerCase(), order, group,orderfield.toString()};
|
}
|
|
private String selectdata(HttpSession session, HttpServletRequest request,
|
int formid, String StatisNames) throws ApplicationException {
|
|
Map<String, String> sql = new HashMap<>(8);
|
Map<String, String> JionFlagMap = new HashMap<>(8);
|
List<Map<String, Object>> list = gridService.selectdata(formid,
|
StatisNames);
|
//1.组装值sql
|
for (Map map : list) {
|
StringBuffer havingstr = new StringBuffer();
|
//针对 between and 或 not between and 的情况 作判断 ,因为需要取第二个值来组装sql
|
boolean isbetween = false;
|
//分组号
|
int jionFlagGroup = 0;
|
//关系符
|
String jionflag = "";
|
//连接符
|
String conFlag = "";
|
int isFilterZeros = GridUtils.prossRowSetDataType_Int(map, "isFilterZero");
|
try {
|
//分组号
|
jionFlagGroup = GridUtils.prossRowSetDataType_Int(map, "jionFlagGroup");
|
//关系符
|
jionflag = GridUtils.prossRowSetDataType_String(map, "jionFlag");
|
//连接符
|
conFlag = GridUtils.prossRowSetDataType_String(map, "conFlag");
|
} catch (Exception e) {
|
throw new ApplicationException("jionFlagGroup,jionFlag,conFlag没进行设置,请检查里面设置");
|
}
|
if (isFilterZeros == 0 && "".equals(conFlag.trim())) continue;
|
String StatisType = GridUtils.prossRowSetDataType_String(map, "StatisType");
|
if (StatisType.equals(""))
|
StatisType = "分组";
|
String FieldID = GridUtils.prossRowSetDataType_String(map, "FieldID");
|
Matcher matcher = AS_PATTERN.matcher(FieldID);
|
//存在 as 字段别名的情况,在sum的情况下需要先去掉,组成好再重新加上
|
if (matcher.find()) {
|
FieldID = FieldID.substring(0, matcher.start());
|
}
|
//处理过滤空值和0
|
if (isFilterZeros == 1) {
|
havingstr.append(" ( ");
|
if (StatisType.equalsIgnoreCase("分组")) {
|
havingstr.append(" isnull(" + FieldID + ",'') <> '') ");
|
} else if (StatisType.equalsIgnoreCase("汇总")) {
|
havingstr.append(" sum(");
|
havingstr.append("isnull(" + FieldID + ",0)) <> 0) ");
|
} else if (StatisType.equalsIgnoreCase("最大")) {
|
havingstr.append(" max(");
|
havingstr.append(" isnull(" + FieldID + ",0)) <> 0) ");
|
} else if (StatisType.equalsIgnoreCase("最小")) {
|
havingstr.append(" min(");
|
havingstr.append(" isnull(" + FieldID + ",0)) <> 0) ");
|
} else if (StatisType.equalsIgnoreCase("平均")) {
|
havingstr.append(" avg(");
|
havingstr.append(" isnull(" + FieldID + ",0)) <> 0) ");
|
} else if (StatisType.equalsIgnoreCase("个数")) {
|
havingstr.append(" count(");
|
havingstr.append(" isnull(" + FieldID + ",0)) <> 0) ");
|
} else if (StatisType.equalsIgnoreCase("自定义")) {
|
havingstr.append(FieldID + " <> 0) ");
|
}
|
}
|
//设置了连接符和比较符及选上过滤空值的情况
|
if (jionflag != null && !jionflag.trim().equals("") && conFlag != null
|
&& !conFlag.trim().equals("")) {
|
if (isFilterZeros == 1)
|
havingstr.append(" and (");
|
else
|
havingstr.append(" (");
|
|
if (StatisType.equalsIgnoreCase("分组")) {
|
|
havingstr.append(" isnull(" + FieldID + ",'') ");
|
} else if (StatisType.equalsIgnoreCase("汇总")) {
|
|
havingstr.append(" sum(");
|
havingstr.append(" isnull(" + FieldID + ",0)) ");
|
} else if (StatisType.equalsIgnoreCase("最大")) {
|
|
havingstr.append(" max(");
|
havingstr.append(" isnull(" + FieldID + ",0)) ");
|
} else if (StatisType.equalsIgnoreCase("最小")) {
|
|
havingstr.append(" min(");
|
havingstr.append(" isnull(" + FieldID + ",0)) ");
|
} else if (StatisType.equalsIgnoreCase("平均")) {
|
|
havingstr.append(" avg(");
|
havingstr.append(" isnull(" + FieldID + ",0)) ");
|
} else if (StatisType.equalsIgnoreCase("个数")) {
|
|
havingstr.append(" count(");
|
havingstr.append(" isnull(" + FieldID + ",0)) ");
|
} else if (StatisType.equalsIgnoreCase("自定义")) {
|
|
havingstr.append(FieldID + "");
|
}
|
|
|
String modfvalues = (String) map.get("modfvalues");
|
String modfvalues2 = (String) map.get("modfvalues2");
|
java.util.regex.Matcher propsMatcher = START_WOROD_PATTERN.matcher(modfvalues);
|
while (propsMatcher.find()) {
|
try {
|
String tem = (String) session.getAttribute(propsMatcher.group().toLowerCase());
|
modfvalues = modfvalues.replaceAll(propsMatcher.group(), tem);
|
} catch (Exception e) {
|
throw new ApplicationException(propsMatcher.group() + "不存在于会话中,请检查数据组权限设置");
|
}
|
|
}
|
if (conFlag.toLowerCase().indexOf("between") > -1 ||
|
conFlag.toLowerCase().indexOf("not between") > -1
|
) {
|
isbetween = true;
|
}
|
if (isbetween && (modfvalues2 == null || modfvalues2.length() == 0)) {
|
throw new ApplicationException("between and 或 not between and 的第二个值没有设置,请检查[功能号:9936(检查对象内容)]里面设置");
|
}
|
if (isbetween) {
|
propsMatcher = START_WOROD_PATTERN.matcher(modfvalues2);
|
while (propsMatcher.find()) {
|
modfvalues2 = modfvalues2.replaceAll(propsMatcher.group(), (String) session.getAttribute(propsMatcher.group().toLowerCase()));
|
}
|
havingstr.append(" " + conFlag)
|
.append(" " + modfvalues)
|
.append(" and ")
|
.append(modfvalues2);
|
} else if (conFlag.toLowerCase().indexOf("like") > -1 ||
|
conFlag.toLowerCase().indexOf("not like") > -1) {
|
//处理有单引号情况 'dddd'
|
modfvalues = modfvalues.replaceAll("'", "");
|
havingstr.append(" " + conFlag)
|
.append(" '%" + modfvalues + "%'");
|
|
} else if (conFlag.toLowerCase().indexOf("in") > -1 ||
|
conFlag.toLowerCase().indexOf("not in") > -1) {
|
//*****处理广陶 CS版兼容问题,增加单引号 by 2016-3-4
|
String temp = "";
|
String[] arr = modfvalues.split(",");
|
for (String r : arr) {
|
if (r.startsWith("'") && r.endsWith("'"))
|
temp += r + ",";
|
else {
|
// System.out.println("r---->>>>>>>>>>>"+r);
|
if (r.startsWith("'") || r.endsWith("'"))//存在一种情况需要去掉单引号
|
temp += "'" + r.replaceAll("'", "") + "',";
|
else
|
temp += "'" + r + "',";
|
}
|
}
|
havingstr.append(" " + conFlag)
|
.append(" (" + temp.substring(0, temp.length() - 1) + ")");
|
|
} else {
|
havingstr.append(" " + conFlag)
|
.append(" " + modfvalues);
|
}
|
havingstr.append(" )");//当前项结束
|
}
|
|
String temp = sql.get(jionFlagGroup + "");
|
if (temp == null) {
|
sql.put(jionFlagGroup + "", havingstr.toString());
|
JionFlagMap.put(jionFlagGroup + "", "".equals(jionflag.trim()) ? " and " : jionflag.trim());
|
} else {//拼接
|
if ("".equals(jionflag.trim())) {
|
jionflag = " and ";
|
}
|
sql.put(jionFlagGroup + "", sql.get(jionFlagGroup + "") + "" + jionflag + havingstr.toString());
|
}
|
}
|
//2.再拼接最终sql的顺序,根据分组和连接符拼接
|
String st = "";
|
int index = 0;
|
for (Map.Entry<String, String> entry : sql.entrySet()) {
|
String temJionFlag = "";
|
if (index > 0) {
|
temJionFlag = JionFlagMap.get(entry.getKey());
|
st = st + " " + temJionFlag + " (" + entry.getValue() + ")";
|
} else {
|
st += entry.getValue();
|
}
|
index++;
|
}
|
return st.equals("") ? "" : (" having (" + st + ")");
|
}
|
|
private void prossDisplayFormat(Page page) {
|
List list = page.getData();
|
if (list == null || list.size() < 0) {
|
return;
|
}
|
Map<String, Map<String, Object>> resultMap = (Map<String, Map<String, Object>>) page.getResultMap();
|
for (int i = 0; i < list.size(); i++) {
|
//每行
|
Map<String, Object> map = (Map<String, Object>) list.get(i);
|
if (map.containsKey("_ROWNO")) {
|
map.remove("_ROWNO");
|
}
|
for (Map.Entry<String, Object> entry : map.entrySet()) {
|
Map<String, Object> map2 = resultMap.get(entry.getKey().toLowerCase());
|
if (map2 != null) {
|
String displayFormat = GridUtils.prossRowSetDataType_String(map2, "displayformat");
|
if (StringUtils.isNotBlank(displayFormat)) {
|
if (displayFormat.indexOf("-") > 0) {//表示日期格式
|
String displayformat[] = (map2.get("displayformat") + "").split("-");
|
if (displayformat.length >= 2) {
|
SimpleDateFormat formatter = new SimpleDateFormat((map2.get("displayformat") + ""));
|
String dateString = formatter.format(entry.getValue());
|
entry.setValue(dateString);
|
}
|
} else {
|
if (displayFormat.startsWith(",0")) {
|
|
displayFormat = displayFormat.replace(",0", ",###");
|
} else if (displayFormat.startsWith(",.")) {
|
displayFormat = displayFormat.replace(",", ",###");
|
}
|
if (entry.getValue() != null) {
|
entry.setValue(new DecimalFormat(displayFormat).format(Double.parseDouble(entry.getValue() + "")));
|
} else {
|
entry.setValue(entry.getValue());
|
}
|
}
|
|
}
|
}
|
}
|
}
|
//处理页脚汇总
|
String tbColsOut = page.getTbColsOut();
|
String newtbCols = page.getNewTbCols();
|
if (StringUtils.isNotBlank(tbColsOut) && !tbColsOut.contains("replace")) {
|
tbColsOut = tbColsOut.replaceAll("\\.#", "#");
|
Map<String, Map<String, Object>> map = (Map<String, Map<String, Object>>) page.getResultMap();
|
String[] strings = tbColsOut.split("#p#");
|
String[] fileds = newtbCols.split(";");//取汇总字段
|
int index = 0;
|
for (String str : fileds) {
|
String[] cols = str.split("=");
|
Map<String, Object> formatMap = map.get(cols[0]);
|
String displayFormat = GridUtils.prossRowSetDataType_String(formatMap, "displayformat");
|
if (StringUtils.isNotBlank(displayFormat)) {
|
if (displayFormat.startsWith(",0")) {
|
displayFormat = displayFormat.replace(",0", ",###");
|
} else if (displayFormat.startsWith(",.")) {
|
displayFormat = displayFormat.replace(",", ",###");
|
}
|
newtbCols = newtbCols.replace("#" + index, new DecimalFormat(displayFormat).format(Double.parseDouble(strings[index])));
|
} else {
|
newtbCols = newtbCols.replace("#" + index, new DecimalFormat("0.00").format(Double.parseDouble(strings[index])));
|
}
|
index++;
|
}
|
page.setTbColsOut(newtbCols);
|
} else {
|
page.setTbColsOut("");
|
}
|
}
|
|
/**
|
* 38类型窗体调用函数
|
*/
|
@RequestMapping(params = "m=load")
|
public Object loadByFunc(Model model, GridData data, Grid grid,
|
HttpServletRequest request, HttpServletResponse resp,
|
Integer grapht, int formid, String StatisNames, String StatisID) throws Exception {
|
Page page = null;
|
if (request.getParameter("TGData") == null) {
|
page = JOSNUtils.setPageInfoData(data);// 第一步,照写
|
} else {
|
page = this.setPageInfoTree(request);// 第一步,照写
|
if(data!=null&& org.apache.commons.lang3.StringUtils.isNotBlank(data.getExpr())){
|
page.setExpr(data.getExpr());
|
}
|
}
|
page.setTbCols(EncodeUtil.base64Encode(data.getTotalCols()));
|
this.formID = grid.getFormID();
|
env = this.initEnv(request, this.formID, "", false);
|
String[] temp = this.setShow(grid.getDatalist());// 参数
|
page.setOrderBy(temp[1]);
|
HttpSession session = request.getSession();
|
String having = this.selectdata(session, request, formid, StatisID);//处理having
|
if (temp.length >= 3) {
|
page.setGroupby("".equalsIgnoreCase(temp[2]) ? "" : " group by "
|
+ temp[2] + " " + having);
|
} else {
|
request.setAttribute("message", "没有定义输出的显示栏位!!!");
|
}
|
/**/
|
page.setWhere(grid.getWhere().replaceAll("\\\\", ""));
|
page.setFlag(grid.getFlag());
|
String[] t = splitTable(grid.getWinType());
|
page.setTableName(t[0]);
|
page.setParms(t[1]);
|
GTGrid gtGrid = (GTGrid) FactoryBean.getBean("GTGrid");
|
String dataGroup = "";
|
dataGroup = gtGrid.prossDataGroup(request, this.formID, 0);
|
if ("".equals(page.getWhere())) {
|
page.setWhere(" 1 =1 " + dataGroup);
|
} else {
|
page.setWhere(page.getWhere() + dataGroup);
|
}
|
page.setDataGroup(dataGroup);
|
page.setType("38",data.formID);
|
if(org.apache.commons.lang3.StringUtils.isBlank(page.getExpr())){
|
BaseService baseService= (BaseService) FactoryBean.getBean("BaseService");
|
final String object = baseService.getJdbcTemplate().queryForObject("set nocount on\n SELECT stuff((SELECT';' + lower(fieldid)+'|'+ showFieldValueExpression\n" +
|
"from gfield where formid=? and headflag=0 and isnull(showFieldValueExpression,'')<>''\n" +
|
"FOR XML PATH ('')),1,1,'') ", String.class, grid.getFormID());
|
if(object!=null) {
|
page.setExpr(object);
|
}
|
}
|
page.setSql(this.exprTOSql(page, temp[0]));
|
request.setAttribute("StatisNames", StatisNames);
|
request.setAttribute("formid", formid);
|
request.setAttribute("where", data.getWhere38());
|
String dbid = request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + "";
|
createDyTitle(data, request, dbid);
|
try {
|
SpObserver.setDBtoInstance("_" + dbid);
|
page.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
|
page.setUserName(request.getSession().getAttribute(SessionKey.USER_NAME) + "");
|
page.setFormid(grid.getFormID());
|
if (VersionUtils.getAPPTypeName(request) != null) {
|
page.setPageNum(grid.getPos());
|
}
|
type38Ifc.loadAllByFunc(page, env, false, StatisID, formid);// 第二步,调用相对应业务类取得数据
|
if (page.getPos() > 0) {
|
//表示不是第一页,分页功能不会再执行计总数和总页面和汇总操作,所以需要把前端传过来的做一个保存
|
page.setTotalPageNum(grid.getTotalPageNum());
|
page.setTotalRowNum(grid.getTotalRowNum());
|
}
|
} catch (Exception e) {
|
//e.printStackTrace();
|
/* this.print(resp, e.getMessage().split(";")[2]); */
|
request.setAttribute("message", e.getCause() != null ? e.getCause().getMessage() : e.getMessage());
|
//return null;
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
//区分是app端还是pc端调用
|
if (VersionUtils.getAPPTypeName(request) != null) {
|
//处理格式,app不需要再处理一次格式
|
prossDisplayFormat(page);
|
|
return page;
|
} else {
|
StringBuilder str = new StringBuilder();
|
if (page.getPos() == 0) {
|
String sql = "select optype,frozencols from gform where formid=" + formid + "";
|
List<Map<String, Object>> list38 = type38.girddata(sql);
|
Map<String, Object> map38 = null;
|
int frozencols = 0;
|
for (int i = 0; i < list38.size(); i++) {
|
map38 = list38.get(i);
|
request.setAttribute("optype", GridUtils.prossRowSetDataType_Int(map38, "optype"));
|
request.getSession().setAttribute("optype38", GridUtils.prossRowSetDataType_Int(map38, "optype"));
|
frozencols = map38.get("frozencols") == null ? 0 : Integer
|
.parseInt(map38.get("frozencols") + "");
|
}
|
String cosconfig[] = data.colsConfig.split("/>");
|
str.append(
|
//Sorted='1' SortCols='bccname,amount' SortTypes='0,0'
|
"<Grid><Cfg id='T_"
|
+ formid
|
+ "'/><Cfg isNine='null' Paging='3' NoPager='1' Sorting='0' /><Cfg Deleting='0'/><Cfg SelectingText='1'/> <Cfg MinTagHeight='200' ConstHeight='1' ConstWidth='1' CacheTimeout='10' Cache='3' FilterLap='1' gType='38'/><Cfg Code='STACPYRJRZDNTB'/>")
|
.append("<Cfg totalCols='").append(data.totalCols)
|
.append("'/>");
|
if (frozencols == 0) {
|
str.append("<Cols>").append(data.colsConfig)
|
.append("</Cols><Head>");
|
} else if (frozencols < cosconfig.length) {
|
str.append("<LeftCols>");
|
for (int i = 0; i < frozencols; i++) {
|
str.append(cosconfig[i] + "/>");
|
}
|
str.append("</LeftCols>").append("<Cols>");
|
for (int i = 0; i < cosconfig.length - frozencols; i++) {
|
str.append(cosconfig[i + frozencols] + "/>");
|
}
|
str.append("</Cols><Head>");
|
|
} else if (frozencols == cosconfig.length) {
|
str.append("<LeftCols>");
|
for (int i = 0; i < frozencols; i++) {
|
str.append(cosconfig[i] + "/>");
|
}
|
str.append("</LeftCols><Head>");
|
} else if (frozencols > cosconfig.length) {
|
str.append("<Cols>").append(data.colsConfig)
|
.append("</Cols><Head>");
|
}
|
str.append(data.header).append("</Head><Body><B Pos='" + page.getPos() + "'>");
|
str.append(JOSNUtils.toJosn38(page)).append("</B>");
|
int lastSize=page.getTotalRowNum()-(page.getTotalPageNum()*page.getPageSize())+page.getPageSize();
|
for(int i=1;i<page.getTotalPageNum();i++) {
|
if(i<(page.getTotalPageNum()-1)) {
|
str.append("<B Count='" + page.getPageSize() + "'/>");
|
}else {
|
str.append("<B Count='" + lastSize+ "'/>");
|
}
|
}
|
str.append("</Body>");
|
if (data.totalCols.length() == 0) {
|
str.append("<Toolbar Cells='Pager,Formula,Scales,Help,Empty' PagerType='Pager' PagerEditWidth='80' Formula=\"'共: <b>'+count()+'</b>条'\" Scales='2' Calc='0' Styles='0' Sizes='0' Empty='0'/></Grid>");
|
} else {
|
str.append("<Foot><I id='Fix1' CanEdit='0'/></Foot><Toolbar Cells='Pager,Formula,Scales,Help,Empty' PagerType='Pager' PagerEditWidth='80' Formula=\"'共: <b>'+count()+'</b>条'\" Scales='2' Calc='0' Styles='0' Sizes='0' Empty='0'/></Grid>");
|
}
|
} else {
|
str.append("<Grid><Body><B Pos='" + page.getPos() + "'>");
|
str.append(JOSNUtils.toJosn38(page)).append("</B></Body></Grid>");
|
}
|
model.addAttribute("data", str.toString().replaceAll("\"", "\\\\\"").replaceAll("\\t|\\n", ""));//"号要转义
|
model.addAttribute("StatisID", StatisID);
|
model.addAttribute("tabColsOut", page.getTbColsOut());//汇总列数据
|
model.addAttribute("pageUrlPams", procssQueryString(grid, page));
|
model.addAttribute("pageUrlPams2", grid.getQueryStringBy38());
|
return "/public/template/grid38";
|
}
|
}
|
|
private String procssQueryString(Grid grid, Page page) {
|
if (grid.getQueryStringBy38().contains("&pos=")) {
|
return grid.getQueryStringBy38().replaceAll("&pos=\\d", "&pos=" + (grid.getPos() + 1));
|
//.replaceAll("&totalRowNum=\\d","&totalRowNum="+ page.getTotalRowNum())
|
//.replaceAll("&totalPageNum=\\d","&totalPageNum="+page.getTotalPageNum());
|
} else {
|
return grid.getQueryStringBy38() + "&pos=" + (grid.getPos() + 1) + "&totalRowNum=" + page.getTotalRowNum() + "&totalPageNum=" + page.getTotalPageNum();
|
}
|
|
}
|
|
public void createDyTitle(GridData data, HttpServletRequest request, String dbid) {
|
//处理动态标题
|
String[] capTionArray = StringUtils.split(data.getGridcaption(), "|");
|
Map<String, String> titleMap = new HashMap<>();//保存sql的原sql,用作之后替换data.header用
|
String sqlStr = "";
|
//组装sql,一次性查询
|
for (String str : capTionArray) {
|
if (StringUtils.isNotEmpty(str) && str.startsWith("!")) {
|
String randomStr = RandomStringUtils.randomAlphabetic(5);
|
str=str.replaceAll("#",",");
|
if (StringUtils.isNotEmpty(str)) {
|
titleMap.put(randomStr, str);
|
sqlStr += "'" + randomStr + "='+isnull(" + str.replace("!", "").replaceAll("select", "") + ",'''''')+';'+";
|
|
|
}
|
|
}
|
}
|
if (sqlStr.endsWith("+")) {
|
sqlStr = sqlStr.substring(0, sqlStr.length() - 5);
|
}
|
if (StringUtils.isNotEmpty(sqlStr)) {
|
//从where取值,替换参数
|
sqlStr = SQLUtils.ReplaceSQLParameter(sqlStr, SQLUtils.processStringObject(data.getWhere(), "and", "="));
|
sqlStr = SQLUtils.prossSessionInfo(request, sqlStr);
|
//sqlStr = sqlStr.replaceAll("#", "&");
|
//sqlStr>>select '!select dbo.fGetDynamicPeriodid(120301 , ''&mycompanyid&'',''&beginday&'',7)='+dbo.fGetDynamicPeriodid(120301 , 'LPCZ','2019-01-01',7)+';!select dbo.fGetDynamicPeriodid(120301, ''&mycompanyid&'',''&beginday&'',10 )='+ dbo.fGetDynamicPeriodid(120301, 'LPCZ','2019-01-01',10 )
|
String result = null;
|
try {
|
SpObserver.setDBtoInstance("_" + dbid);
|
result = grids.getSimpleJdbcTemplate().queryForObject(" select " + sqlStr, String.class);
|
} finally {
|
SpObserver.setDBtoInstance();
|
}
|
if (StringUtils.isNotEmpty(result)) {
|
Map<String, String> map = SQLUtils.processStringObject(result, ";", "=");
|
String cols_app=data.getCols_app()+"";
|
for (Map.Entry<String, String> entry : map.entrySet()) {
|
cols_app=cols_app.replace(titleMap.get(entry.getKey()), entry.getValue());//app端也需要处理
|
data.header = data.header.replaceAll("#",",").replace(titleMap.get(entry.getKey()), entry.getValue());//把取得的值更新回标题定义
|
}
|
data.setCols_app(cols_app);
|
}
|
}
|
}
|
|
@RequestMapping(params = "m=print38Excel")
|
protected void print2Excel(Integer grapht, Model model,
|
HttpServletRequest request, HttpServletResponse resp)
|
throws Exception {
|
// 为了组成page
|
String root = request.getSession().getServletContext().getRealPath("/")
|
+ "/";
|
int formid = -1;
|
|
try {
|
formid = Integer.parseInt(new String(com.yc.utils.Base64
|
.decode(request.getParameter("formIdHid").getBytes())));
|
/* PanelServiceImpl panel=new PanelServiceImpl(); */
|
} catch (NumberFormatException e1) {
|
log.debug(request.getParameter("formIdHid") + "转换数字格式出错",
|
e1.getCause());
|
}
|
String StatisNames = null;
|
String sn = request.getParameter("StatisNames");
|
String StatisID = request.getParameter("StatisID");
|
String tabColsOut = request.getParameter("tabColsOut");
|
if (sn != null) {
|
sn = sn.replaceAll("%2B", "+");
|
sn = sn.replaceAll("%2F", "/");
|
sn = sn.replaceAll("%3D", "=");
|
StatisNames = EncodeUtil.base64Decode(sn);
|
}
|
|
// String StatisNames = new String(com.yc.utils.Base64.decode(request
|
// .getParameter("StatisNames").getBytes()), "utf-8");
|
String datalist = "";
|
List<Map<String, Object>> list = gridService.selectdata1(formid,
|
StatisID);
|
Map<String, Object> mapObject1 = null;
|
PanelBean panelBean = new PanelBean();
|
for (int i = 0; i < list.size(); i++) {
|
mapObject1 = list.get(i);
|
try {
|
datalist += typeControlDao.getSelectAndCheckBoxss(
|
GridUtils.prossRowSetDataType_String(mapObject1, "StatisID"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "FieldID"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "FieldAlias"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "StatisType"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "Sequence"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "DisplayWidth"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "displayformat"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "displayYN"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "isFilterZero"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "jionFlag"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "jionFlagGroup"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "conFlag"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "modfvalues"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "modfvalues2"),
|
GridUtils.prossRowSetDataType_String(mapObject1, "fieldCaption"), panelBean);
|
} catch (Exception e) {
|
throw new ApplicationException(e.getMessage());
|
}
|
}
|
try {
|
datalist = java.net.URLDecoder.decode(datalist.replace("%", "@~"),
|
"utf-8");
|
} catch (UnsupportedEncodingException e) {
|
log.debug(e.getCause().getMessage());
|
}
|
|
try {
|
String where = new String(com.yc.utils.Base64.decode(request
|
.getParameter("where").getBytes()), "utf-8");
|
GridData data = new GridData();
|
gridService.create38Grid(data, formid, root,
|
this.setWhere(this.replaceBlank(datalist)), StatisID);
|
data.setDatalist(this.replaceBlank(datalist));
|
data.setWhere(where);
|
Grid grid = new Grid();
|
grid.setFormID(data.formID);
|
grid.setWinType(data.winType + "");
|
grid.setField(data.field);
|
grid.setWhere(data.where);
|
grid.setDatalist(data.datalist);
|
Page page = JOSNUtils.setPageInfoData(data);// 第一步,照写
|
this.formID = grid.getFormID();
|
env = this.initEnv(request, this.formID, "", false);
|
String[] temp1 = this.setShow(grid.getDatalist());// 参数
|
page.setOrderBy(temp1[1]);
|
HttpSession session = request.getSession();
|
String having = this.selectdata(session, request, formid,
|
StatisNames);
|
if (temp1.length >= 3) {
|
page.setGroupby("".equalsIgnoreCase(temp1[2]) ? ""
|
: " group by " + temp1[2] + " " + having);
|
} else {
|
request.setAttribute("message", "没有定义输出的显示栏位!!!");
|
}
|
/**/
|
page.setWhere(grid.getWhere().replaceAll("\\\\", ""));
|
page.setFlag(grid.getFlag());
|
String[] t = splitTable(grid.getWinType());
|
page.setTableName(t[0]);
|
page.setParms(t[1]);
|
// 库存查询(1)-companyid#公司|编号;companyname#公司|名称;plantname#plantname;stcode#仓库|编号;matcode#物料|编码;unlimitstock#库存|实际库存#,0.00;ontransstock#库存|在途#,0.00;-18-unlimitstock#1||5|,0.00;ontransstock#1||6|,0.00;
|
page.setUserCode(request.getSession().getAttribute(SessionKey.USERCODE) + "");
|
page.setUserName(request.getSession().getAttribute(SessionKey.USER_NAME) + "");
|
page.setFormid(grid.getFormID());
|
page.setPageSize(1000000);
|
page.setSql(this.exprTOSql(page, temp1[0]));
|
type38Ifc.loadAllByFunc(page, env, false, StatisID, formid);// 第二步,调用相对应业务类取得数据
|
page.setTbColsOut(tabColsOut);
|
// 读取当前功能号哪些字段是需要导出的
|
String titles = StatisNames;
|
String formatStrs = "";
|
String excelcount = "";
|
int execlIndex = 0;
|
int tabColIndex = 0;//汇总列字段在38类型配置位置
|
int z = 0;
|
Map<String, Object> mapObject = null;
|
for (int i = 0; i < list.size(); i++) {
|
mapObject = list.get(i);
|
if ((Integer) mapObject.get("displayYN") == 1) {
|
execlIndex = i;
|
if (z != 0) {
|
execlIndex = execlIndex - z;
|
}
|
String fieldalias = mapObject.get("fieldalias") + "";
|
String filedTitle = org.apache.commons.lang3.StringUtils.isBlank(fieldalias) ? mapObject
|
.get("FieldCaption") + "" : mapObject
|
.get("fieldalias") + "";
|
// 处理自定义字段名称的情况 : case when xxxx as doccode,取doccode作为名称
|
final String[] byAsOfEnd = (mapObject.get("fieldid") + "").toLowerCase()
|
.split("\\s*\\b(?i)as\\b\\s*");
|
String filedId = "";
|
if (byAsOfEnd.length > 1) {
|
filedId = byAsOfEnd[1];
|
} else {
|
filedId = byAsOfEnd[0];
|
}
|
boolean isNotDisplayformat=org.apache.commons.lang3.StringUtils.isBlank(GridUtils.prossRowSetDataType_String(mapObject,"displayformat"));
|
if (isNotDisplayformat) {
|
formatStrs += filedId + "#" + filedTitle + ";";
|
} else {
|
formatStrs += filedId + "#" + filedTitle + "#"
|
+ mapObject.get("displayformat") + ""
|
+ ";";
|
}
|
if ((mapObject.get("StatisType") + "").equals("自定义")) {
|
|
if ((mapObject.get("sumfield") + "").equals("1")) {
|
excelcount += filedId
|
+ "#1||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("2")) {
|
excelcount += filedId
|
+ "#2||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("3")) {
|
excelcount += filedId
|
+ "#3||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("6")) {
|
excelcount += filedId
|
+ "#6||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("7")) {
|
excelcount += filedId
|
+ "#7||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("5")) {
|
excelcount += filedId
|
+ "#5|"
|
+ filedId
|
+ ":"
|
+ (mapObject.get("funclinkname") == null
|
|| mapObject.get("funclinkname")
|
.equals("") ? ""
|
: mapObject.get("funclinkname")
|
+ "")
|
+ "|"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
}
|
|
} else {
|
if ((mapObject.get("sumfield") + "").equals("1")) {
|
excelcount += filedId
|
+ "#1||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("2")) {
|
excelcount += filedId
|
+ "#2||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("3")) {
|
excelcount += filedId
|
+ "#3||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("6")) {
|
excelcount += filedId
|
+ "#6||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat ? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("7")) {
|
excelcount += filedId
|
+ "#7||"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
} else if ((mapObject.get("sumfield") + "").equals("5")) {
|
excelcount += filedId
|
+ "#5|"
|
+ filedId
|
+ ":"
|
+ (mapObject.get("funclinkname") == null
|
|| mapObject.get("funclinkname")
|
.equals("") ? ""
|
: mapObject.get("funclinkname")
|
+ "")
|
+ "|"
|
+ execlIndex+"_T_"+tabColIndex
|
+ "|"
|
+ (isNotDisplayformat? ",0.00"
|
: mapObject.get("displayformat")
|
+ "").replace(" ", "")
|
+ ";";
|
}
|
|
}
|
if(GridUtils.prossRowSetDataType_Int(mapObject,"sumfield")>0) {
|
tabColIndex++;
|
}
|
} else {
|
z++;
|
}
|
}
|
titles = titles + ";pb#" + formatStrs + ";pb#" + "38" + ";pb#" + excelcount;
|
|
if (request.getSession().getAttribute(SessionKey.USERCODE) == null) {
|
this.printJson(resp, "error;会话过期,请重新登录!");
|
return;
|
}
|
if ("".equalsIgnoreCase(titles) || titles == null) {
|
this.printJson(resp, "error;未在9802设置有需要导出的内容或找不到相关的文件");
|
return;
|
}
|
Map<String, Object> map = null;
|
String[] panel_name = null;
|
String[] panel_tfiled = null;
|
String local = "";
|
page.setExcelTitle("fg==");
|
Map<String, String> gmap = getTitle(page, 1);// 1表示取格线
|
|
String[] str = titles.split(";pb#");
|
String[] temp = str[1].split(";");
|
int len = temp.length;
|
String[] name = new String[len];// 字段说明
|
String[] filed = new String[len];// 字段名
|
setDyTtile(gmap, temp, name, filed);
|
// 然后写入excel文件
|
page.setExcelTitle(str.length == 4 ? str[3] : "");
|
String dbid = request.getSession().getAttribute(SessionKey.DATA_BASE_ID) + "";
|
|
PoiExcelWriter poi = new PoiExcelWriter();
|
String hostUrl = SettingKey.getHostUrl(request);
|
Map m = poi.writeExcel(dbid, str[0], page, name, filed,
|
panel_name, panel_tfiled,
|
map != null ? (List<?>) map.get("plist") : null, str[0],
|
local, "", hostUrl);
|
SXSSFWorkbook wb = (SXSSFWorkbook) m.get("wb");
|
exprotExcelFile(request, resp,formid,"38", page, wb, str[0]);
|
} catch (ParseException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
this.printJson(resp, "error;" + e.getMessage());
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
this.printJson(resp, "error;" + e.getMessage());
|
}
|
|
}
|
|
/**
|
* 把字符串进行分割处理
|
*
|
* @param winType\ field 形式:frptmatstorage|companyid;stcode;matcode;
|
* @return String 形式:frptmatstorage
|
*/
|
private String[] splitTable(String winType) {
|
String[] temp = grids.getTableName(this.formID, winType).split("\\|");
|
return new String[]{temp[0], temp[1]};
|
}
|
|
// 去除字符串中的换行符等
|
public String replaceBlank(String str) {
|
if (str == null) {
|
return "";
|
}
|
Pattern p = Pattern.compile("\t|\r|\n");
|
Matcher m = p.matcher(str);
|
return m.replaceAll(" ");
|
}
|
}
|