package com.yc.action.grid;
|
|
import com.yc.action.BaseAction;
|
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.GridServiceIfc;
|
import com.yc.utils.Page;
|
import com.yc.utils.SessionKey;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.context.annotation.Scope;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.stereotype.Component;
|
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.nio.charset.StandardCharsets;
|
import java.util.*;
|
import java.util.concurrent.atomic.AtomicInteger;
|
import java.util.stream.Collectors;
|
|
/**
|
* 处理496多表excel导出
|
*/
|
@Component()
|
@Scope("prototype")
|
public class Export496 extends BaseAction {
|
@Autowired
|
GridServiceIfc gridService;
|
@Autowired
|
GTGrid gtGrid;
|
public void export(HttpServletRequest request, HttpServletResponse resp,Page page) {
|
if(request.getSession().getAttribute(SessionKey.USERCODE)==null){
|
this.printJson(resp, "error;会话过期，请重新登录！");
|
return;
|
}
|
//以9676为例
|
//1--读取9771的配置信息，决定如何加载子功能号数据
|
BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
|
List<T9771Entity> list9771 = baseService.getJdbcTemplate().query("set nocount on \n" +
|
" declare @formid int=?,@myCount int\n" +
|
" select @myCount=count(1) from _sys_TabPageFormid where formid=@formid \n" +
|
" if @myCount=0 \n" +
|
" begin \n" +
|
" raiserror('%d功能号在9771查找不到有对应主功能号，请到9771维护',16,1,@formid)\n" +
|
" return\n" +
|
" end \n" +
|
" if @myCount>1 \n" +
|
" begin \n" +
|
" raiserror('%d功能号在9771存在多条对应主功能号记录',16,1,@formid)\n" +
|
" return\n" +
|
" end \n" +
|
" select mainformid,mainformname,sortBy,a.formid,a.formname,formtype,labelName,a.fT,a.fK,a.seekGroupID,tabID,\n" +
|
" case when a.ft=mainformid then 1 else 0 end as alone,b.hdtable\n" +
|
" from _sys_TabPageFormid a join gform b on a.formid=b.formid \n" +
|
" where mainformid=(select top 1 mainformid from _sys_TabPageFormid where formid=@formid) and isnull(Actived,0)=1 order by SortBy asc\n", new BeanPropertyRowMapper<>(T9771Entity.class),page.getFormid());
|
if(list9771!=null&&list9771.size()>0){
|
//2---取所有功能号id，取出对应9802数据
|
final StringJoiner formidsJoiner=new StringJoiner(",");//取得9676,9677,9678,9679
|
final StringBuilder mainKey=new StringBuilder(";");//主功能号的主键
|
list9771.stream()
|
.forEach(x->{
|
formidsJoiner.add(x.getFormid()+"");
|
if(x.formid.equals(x.mainformid)){
|
mainKey.append(x.fK);
|
}
|
});
|
List<T9802Entity> list9802 = baseService.getJdbcTemplate().query(" select a.formid,a.fieldid,a.fieldname,gridcaption,displayformat,isnull(gridControlType,0) as gridControlType,isnull(controlType,0) as controlType,isnull(rowNo,0) as rowNo,isnull(colNo,0) as colNo,isnull(lengthNum,0) as lengthNum,isnull(heightNum,0) as heightNum,isnull(exportTitle,0) as exportTitle,showFieldValueExpression,isnull(sumField,0) as sumField,isnull(activefuns,0) as activefuns,funclinkname,b.index1 from gField a join gform b on a.formid=b.formid\n" +
|
" where a.formid in(" + formidsJoiner.toString() + ") and isnull(isExport,0)=1 and isnull(headflag,0)=0 \n" +
|
" order by a.formid,a.StatisID asc", new BeanPropertyRowMapper<>(T9802Entity.class));
|
//3---取list9771中alone=1的功能号
|
final List<Exprot496Entiry> exprot496list=new ArrayList<>();
|
list9771.stream()
|
.filter(x->x.getAlone()==1)
|
.forEach(alone->{
|
|
if(!alone.formid.equals(alone.mainformid)) {
|
//不是第一个主功能号，取所有关联子功能数据
|
StringJoiner fkKey=new StringJoiner(",");
|
list9771.stream().forEach(z -> {
|
if (alone.formid.equals(z.fT)) {
|
fkKey.add(z.fK);
|
}
|
});
|
alone.subFkKey =fkKey.toString();
|
}
|
Exprot496Entiry entiry = loadData(request,list9802, alone, page);
|
//判断是否有关联的子功能，有则全都加载过来
|
if(!alone.formid.equals(alone.mainformid)){
|
//不是第一个主功能号，取所有关联子功能数据
|
list9771.stream().forEach(z->{
|
if(alone.formid.equals(z.fT)) {
|
//表示所属的子功能号，需要取数
|
//判断关联的主外键情况:
|
// 1,一种情况是主功能号用的主键在如果下面的子功能号都存在，取数可以直接用这个主键把所有相关的数据都取回来，再过滤。这样查数只需要一次性取回来。提高性能
|
//2--子功能号的主外键关系没用到主功能号的主键，这种情况需要从已查出来的数据取得所有相关的主键数据，再一次性取数回来
|
String finalWhere=page.getWhere();
|
if(!z.fK.contains(mainKey.toString())){
|
//判断是否存在多个键值 acccode;rowid，因为rowid是限制匹配取数，需要去掉
|
String[] fkKey = z.fK.toLowerCase().split(";");//主功能号字段
|
String[] pKKey = z.seekGroupID.toLowerCase().split(";");//子功能号字段
|
String subFkKey="";
|
String subPkKey="";
|
if(fkKey.length!=pKKey.length){
|
throw new ApplicationException(String.format("9771中功能号%s【FK，seekGroupID】参数个数不相等",z.formid));
|
}
|
if(fkKey.length==1){
|
//默认数目要相等
|
subFkKey=fkKey[0];
|
subPkKey=pKKey[0];
|
}else {
|
//多个key，则去掉rowid，detailrowid等特定字段
|
String fk=z.fK.toLowerCase();
|
String pk=z.seekGroupID.toLowerCase();
|
for (int i = 0; i < fkKey.length; i++) {
|
if (fkKey[i].equals("rowid")||fkKey[i].equals("detailrowid")) {
|
fk=fk.replace(fkKey[i],"");
|
pk=pk.replace(fkKey[i],"");
|
}
|
}
|
fk=fk.replaceAll(";","");
|
pk=pk.replaceAll(";","");
|
subFkKey=fk;
|
subPkKey=pk;
|
}
|
final String finalSubKey=subFkKey;
|
final StringJoiner valuekeys=new StringJoiner(",");
|
entiry.data.stream().map(x->x.get(finalSubKey)).forEach(sub->{
|
valuekeys.add("'"+sub+"'");
|
});
|
finalWhere=subPkKey+" in("+ valuekeys.toString()+")";
|
}
|
page.setWhere(finalWhere);
|
Exprot496Entiry subList = loadData(request,list9802, z, page);
|
entiry.subList.add(subList);
|
}
|
});
|
|
}
|
exprot496list.add(entiry);
|
|
});
|
toExcel(request,resp,exprot496list);
|
}
|
}
|
|
/**
|
* 导出为excel文件
|
*/
|
private void toExcel(HttpServletRequest request, HttpServletResponse resp,List<Exprot496Entiry> exprot496list){
|
|
SXSSFWorkbook wb = new SXSSFWorkbook(500);
|
String hotsUrl=SettingKey.getHostUrl(request);;
|
String dbid =request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"";
|
for (Exprot496Entiry entiry : exprot496list) {
|
PoiExcelWriter poi = new PoiExcelWriter();
|
poi.writeExcelBy496(wb, entiry, hotsUrl, dbid);
|
}
|
String fileName = exprot496list.get(0).mainFormName;
|
Integer formid = exprot496list.get(0).mainFormid;
|
Page page= exprot496list.get(0).getPage();
|
exprotExcelFile(request, resp, formid,"496", page,wb, fileName);
|
}
|
|
|
|
/**
|
* 根据9771，9802定义，拼接得到sql,取数
|
* @param list9802
|
* @param alone
|
* @param where
|
* @return
|
*/
|
private Exprot496Entiry loadData(HttpServletRequest request,List<T9802Entity> list9802,T9771Entity alone,Page page){
|
//取9676字段列表数据
|
final StringJoiner fieldsJoiner=new StringJoiner(",");//字段列表
|
final StringJoiner expressionJoiner=new StringJoiner(";");//权限表达式
|
final StringJoiner picFields=new StringJoiner(";");//图片字段
|
final StringJoiner titleToFile=new StringJoiner(";");//图片字段作为文件名称
|
final StringJoiner tbColsJoiner=new StringJoiner(",");//计算用-汇总列字段
|
final StringJoiner tbColsExcelJoiner=new StringJoiner(";");//excel导出用-汇总列字段
|
//---排序
|
final StringJoiner orderByJoiner=new StringJoiner(",");//排序字段
|
final StringJoiner fristField=new StringJoiner("");
|
AtomicInteger fumIndex = new AtomicInteger();//标记导出时候字段位置
|
AtomicInteger tabColIndex = new AtomicInteger();//标记导出字段在汇总列的位置
|
fumIndex.set(0);
|
tabColIndex.set(0);
|
Exprot496Entiry entiry=new Exprot496Entiry();
|
List<T9802Entity> collect = list9802.stream().filter(z -> z.getFormid().equals(alone.getFormid())).collect(Collectors.toList());
|
Map<String, String> dyTitleBy496 = getDyTitleBy496(page, alone.formtype.equals(16) ? 0 : 1, alone.formid);//取得当前功能号的标题
|
collect.forEach(k->{
|
fieldsJoiner.add(k.fieldid.toLowerCase());
|
if(
|
(k.gridControlType!=null&&(k.gridControlType.equals(9)||k.gridControlType.equals(19)||k.gridControlType.equals(40)))
|
||
|
(k.controlType!=null&&(k.controlType.equals(9)||k.controlType.equals(19)||k.controlType.equals(40)))
|
) {
|
//面板或表格设置了图片类型
|
picFields.add(k.fieldid);
|
}
|
//处理动态标题
|
if(StringUtils.isNotBlank((dyTitleBy496.get(k.fieldid.toLowerCase())))){
|
k.fieldname=dyTitleBy496.get(k.fieldid.toLowerCase());
|
}else {
|
k.fieldname=StringUtils.isNotBlank(k.gridcaption)?k.gridcaption:k.fieldname;
|
}
|
//exportTitle==1表示以字段名称作为文件名
|
if(k.exportTitle.equals(1)){
|
titleToFile.add(k.fieldid);
|
}
|
//--处理排序
|
if(orderByJoiner.length()==0) {
|
if (tabColIndex.get() == 0 && StringUtils.isBlank(k.index1)) {
|
fristField.add(k.fieldid);
|
orderByJoiner.add(k.fieldid+" ");
|
} else if (tabColIndex.get() == 0 && StringUtils.isNotBlank(k.index1)) {
|
fristField.add(k.fieldid);
|
orderByJoiner.add(k.index1+" ");
|
}
|
}
|
//--处理汇总列
|
if(k.activefuns==1&&k.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(";");
|
tbColsJoiner.add(k.fieldid.toLowerCase()+"#"+k.sumField+":"+ (StringUtils.isNotBlank(k.funclinkname)?k.funclinkname.replaceAll(",", "@p@"):""));
|
tbColsExcelJoiner.add(k.fieldid.toLowerCase()+"#"+k.sumField+"|"+ (StringUtils.isNotBlank(k.funclinkname)?k.funclinkname.replaceAll(",", "@p@"):"")+"|"+fumIndex.getAndIncrement()+"_T_"+tabColIndex.getAndIncrement()+"|"+(StringUtils.isNotBlank(k.displayformat)?k.displayformat.replaceAll("-", "~"):",0.00"));
|
}else {
|
fumIndex.getAndIncrement();
|
}
|
//处理权限表达式
|
if(StringUtils.isNotBlank(k.showFieldValueExpression)){
|
expressionJoiner.add(k.fieldid.toLowerCase()+"|"+k.showFieldValueExpression);
|
}
|
});
|
//1，当前功能号是子功能号：把关联的字段也放进去查询，避免这些字段没选上导出，到时处理不了关系
|
//2--当前功能号是主功能号，需要提前把子功能号的fk字段取回来，让主功能号查询
|
//需要去重处理
|
|
fieldsJoiner.add(alone.seekGroupID.replaceAll(";",","));
|
if(StringUtils.isNotBlank(alone.subFkKey)){
|
fieldsJoiner.add(alone.subFkKey.replaceAll(";",","));
|
}
|
String sqlList = Arrays.stream(fieldsJoiner.toString().split(",")).distinct().collect(Collectors.joining(","));
|
//--处理权限表达式
|
Page newpage=new Page();
|
newpage.setExpr(expressionJoiner.toString());
|
newpage.setTbCols(Base64.getEncoder().encodeToString(tbColsJoiner.toString().getBytes(StandardCharsets.UTF_8)));
|
newpage.setTbCols(gridService.proccTbCols(newpage));
|
String sql = "["+sqlList.replaceAll(",","],[")+"]";
|
sql= this.exprTOSql(newpage, sql);
|
//取9676的数据
|
try {
|
SpObserver.setDBtoInstance("_" + env.get(SessionKey.DATA_BASE_ID));
|
newpage.setUserCode(page.getUserCode());
|
newpage.setUserName(page.getUserName());
|
newpage.setSql(sql);
|
newpage.setTableName(alone.getHdtable());
|
String dataGroup = gtGrid.prossDataGroup(request, page.getFormid(), 0);//数据组权限
|
newpage.setWhere(page.getWhere()+dataGroup);
|
newpage.setFormid(alone.getFormid());
|
newpage.setPageSize(Integer.MAX_VALUE);
|
newpage.setAutopaging(2);//不分页
|
newpage.setId(fristField.toString());
|
newpage.setPageNum(1);
|
newpage.setOrderBy(orderByJoiner.length()>0?orderByJoiner.toString().replaceAll(";",","):"");
|
gridService.loadAll(newpage);// 第二步，调用相对应业务类取得数据,分页功能
|
}catch (Exception ex) {
|
throw ex;
|
}finally {
|
SpObserver.setDBtoInstance();
|
}
|
// List<Map<String, Object>> list = baseService.getJdbcTemplate().queryForList(" select " + sql+ " from " + alone.getHdtable() + " " + where);
|
|
entiry.data=newpage.getData();//自身数据
|
entiry.page=newpage;
|
entiry.metaData=collect;
|
entiry.mainFormName=alone.mainformname;
|
entiry.mainFormid=alone.mainformid;
|
entiry.picFields=picFields.toString();
|
entiry.formid=alone.formid;
|
entiry.formName= StringUtils.isNotBlank(alone.labelName)?alone.labelName:alone.formname;
|
entiry.titleToFileName=titleToFile.toString();
|
entiry.fk=alone.fK;
|
entiry.seekGroupID=alone.seekGroupID;
|
entiry.sheetName=alone.labelName==null?alone.formname: alone.labelName;
|
entiry.exprotType=alone.formtype.equals(16)?ExprotType.Panel:ExprotType.Grid;
|
entiry.tbCols=tbColsExcelJoiner.toString();
|
return entiry;
|
}
|
}
|
