eCoWorksV3.git - Gitblit

fs-danaus

2024-08-09 7204e3dff0490732e861ccd1338e3e3c31d768c6

提交 \| 用户 \| age
0924c5	1	package com.yc.action.grid;
F	2
	3	import com.yc.action.BaseAction;
	4	import com.yc.exception.ApplicationException;
	5	import com.yc.factory.FactoryBean;
	6	import com.yc.multiData.SpObserver;
	7	import com.yc.sdk.shopping.util.SettingKey;
	8	import com.yc.service.BaseService;
	9	import com.yc.service.grid.GridServiceIfc;
	10	import com.yc.utils.Page;
	11	import com.yc.utils.SessionKey;
	12	import org.apache.commons.lang3.StringUtils;
	13	import org.apache.poi.xssf.streaming.SXSSFWorkbook;
	14	import org.springframework.beans.factory.annotation.Autowired;
	15	import org.springframework.context.annotation.Scope;
	16	import org.springframework.jdbc.core.BeanPropertyRowMapper;
	17	import org.springframework.stereotype.Component;
	18
	19	import javax.servlet.http.HttpServletRequest;
	20	import javax.servlet.http.HttpServletResponse;
	21	import java.nio.charset.StandardCharsets;
	22	import java.util.*;
	23	import java.util.concurrent.atomic.AtomicInteger;
	24	import java.util.stream.Collectors;
	25
	26	/**
	27	* 处理496多表excel导出
	28	*/
	29	@Component()
	30	@Scope("prototype")
	31	public class Export496 extends BaseAction {
	32	@Autowired
	33	GridServiceIfc gridService;
	34	@Autowired
	35	GTGrid gtGrid;
	36	public void export(HttpServletRequest request, HttpServletResponse resp,Page page) {
	37	if(request.getSession().getAttribute(SessionKey.USERCODE)==null){
	38	this.printJson(resp, "error;会话过期，请重新登录！");
	39	return;
	40	}
	41	//以9676为例
	42	//1--读取9771的配置信息，决定如何加载子功能号数据
	43	BaseService baseService = (BaseService) FactoryBean.getBean("BaseService");
c1439b	44	List<T9771Entity> list9771 = baseService.getJdbcTemplate().query("set nocount on \n" +
F	45	" declare @formid int=?,@myCount int\n" +
	46	" select @myCount=count(1) from _sys_TabPageFormid where formid=@formid \n" +
	47	" if @myCount=0 \n" +
	48	" begin \n" +
	49	" raiserror('%d功能号在9771查找不到有对应主功能号，请到9771维护',16,1,@formid)\n" +
	50	" return\n" +
	51	" end \n" +
	52	" if @myCount>1 \n" +
	53	" begin \n" +
	54	" raiserror('%d功能号在9771存在多条对应主功能号记录',16,1,@formid)\n" +
	55	" return\n" +
	56	" end \n" +
	57	" select mainformid,mainformname,sortBy,a.formid,a.formname,formtype,labelName,a.fT,a.fK,a.seekGroupID,tabID,\n" +
0924c5	58	" case when a.ft=mainformid then 1 else 0 end as alone,b.hdtable\n" +
F	59	" from _sys_TabPageFormid a join gform b on a.formid=b.formid \n" +
c1439b	60	" 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());
0924c5	61	if(list9771!=null&&list9771.size()>0){
F	62	//2---取所有功能号id，取出对应9802数据
b34175	63	final StringJoiner formidsJoiner = new StringJoiner(",");//取得9676,9677,9678,9679
F	64	final StringBuilder mainKey = new StringBuilder(";");//主功能号的主键
	65	//处理关联功能号之间的字段和主功能号不同的情况，虽要取得主功能号里对应的数据传给子功能号进行where条件查询
	66	final StringBuilder mainPk = new StringBuilder();
	67	list9771.stream()
	68	.forEach(x -> {
	69	formidsJoiner.add(x.getFormid() + "");
	70	if (x.formid.equals(x.mainformid)) {
0924c5	71	mainKey.append(x.fK);
b34175	72	mainPk.append(x.fK);
0924c5	73	}
F	74	});
	75	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" +
	76	" where a.formid in(" + formidsJoiner.toString() + ") and isnull(isExport,0)=1 and isnull(headflag,0)=0 \n" +
	77	" order by a.formid,a.StatisID asc", new BeanPropertyRowMapper<>(T9802Entity.class));
	78	//3---取list9771中alone=1的功能号
	79	final List<Exprot496Entiry> exprot496list=new ArrayList<>();
	80	list9771.stream()
	81	.filter(x->x.getAlone()==1)
b34175	82	.forEach(alone-> {
0924c5	83
b34175	84	// if(!alone.formid.equals(alone.mainformid)) {
F	85	//不是第一个主功能号，取所有关联子功能数据，关联字段名称一起查询
	86	StringJoiner MainfkKey = new StringJoiner(",");
	87	list9771.stream().forEach(z -> {
	88	if (alone.formid.equals(z.fT)) {
	89	MainfkKey.add(z.fK);
	90	}
	91	});
	92	alone.subFkKey = MainfkKey.toString();
	93	// }
97a11f	94	Exprot496Entiry entiry = loadData(request, list9802, alone, page, mainPk, exprot496list,null);
b34175	95	//判断是否有关联的子功能，有则全都加载过来
F	96	if (!alone.formid.equals(alone.mainformid)) {
0924c5	97	//不是第一个主功能号，取所有关联子功能数据
F	98	list9771.stream().forEach(z -> {
	99	if (alone.formid.equals(z.fT)) {
b34175	100	//表示所属的子功能号，需要取数
F	101	//判断关联的主外键情况:
	102	// 1,一种情况是主功能号用的主键在如果下面的子功能号都存在，取数可以直接用这个主键把所有相关的数据都取回来，再过滤。这样查数只需要一次性取回来。提高性能
	103	//2--子功能号的主外键关系没用到主功能号的主键，这种情况需要从已查出来的数据取得所有相关的主键数据，再一次性取数回来
	104	String finalWhere = page.getWhere();
0924c5	105	if(!z.fK.contains(mainKey.toString())){
F	106	//判断是否存在多个键值 acccode;rowid，因为rowid是限制匹配取数，需要去掉
	107	String[] fkKey = z.fK.toLowerCase().split(";");//主功能号字段
	108	String[] pKKey = z.seekGroupID.toLowerCase().split(";");//子功能号字段
	109	String subFkKey="";
	110	String subPkKey="";
	111	if(fkKey.length!=pKKey.length){
	112	throw new ApplicationException(String.format("9771中功能号%s【FK，seekGroupID】参数个数不相等",z.formid));
	113	}
	114	if(fkKey.length==1){
	115	//默认数目要相等
	116	subFkKey=fkKey[0];
	117	subPkKey=pKKey[0];
	118	}else {
	119	//多个key，则去掉rowid，detailrowid等特定字段
	120	String fk=z.fK.toLowerCase();
	121	String pk=z.seekGroupID.toLowerCase();
	122	for (int i = 0; i < fkKey.length; i++) {
	123	if (fkKey[i].equals("rowid")\|\|fkKey[i].equals("detailrowid")) {
	124	fk=fk.replace(fkKey[i],"");
	125	pk=pk.replace(fkKey[i],"");
	126	}
	127	}
	128	fk=fk.replaceAll(";","");
	129	pk=pk.replaceAll(";","");
	130	subFkKey=fk;
	131	subPkKey=pk;
	132	}
	133	final String finalSubKey=subFkKey;
	134	final StringJoiner valuekeys=new StringJoiner(",");
	135	entiry.data.stream().map(x->x.get(finalSubKey)).forEach(sub->{
	136	valuekeys.add("'"+sub+"'");
	137	});
	138	finalWhere=subPkKey+" in("+ valuekeys.toString()+")";
	139	}
	140	page.setWhere(finalWhere);
97a11f	141	Exprot496Entiry subList = loadData(request, list9802, z, page, mainPk, exprot496list,entiry);
0924c5	142	entiry.subList.add(subList);
F	143	}
	144	});
	145
	146	}
	147	exprot496list.add(entiry);
	148
	149	});
	150	toExcel(request,resp,exprot496list);
	151	}
	152	}
	153
	154	/**
	155	* 导出为excel文件
	156	*/
	157	private void toExcel(HttpServletRequest request, HttpServletResponse resp,List<Exprot496Entiry> exprot496list){
	158
	159	SXSSFWorkbook wb = new SXSSFWorkbook(500);
	160	String hotsUrl=SettingKey.getHostUrl(request);;
	161	String dbid =request.getSession().getAttribute(SessionKey.DATA_BASE_ID)+"";
	162	for (Exprot496Entiry entiry : exprot496list) {
	163	PoiExcelWriter poi = new PoiExcelWriter();
	164	poi.writeExcelBy496(wb, entiry, hotsUrl, dbid);
	165	}
	166	String fileName = exprot496list.get(0).mainFormName;
	167	Integer formid = exprot496list.get(0).mainFormid;
	168	Page page= exprot496list.get(0).getPage();
	169	exprotExcelFile(request, resp, formid,"496", page,wb, fileName);
	170	}
	171
	172
	173	/**
	174	* 根据9771，9802定义，拼接得到sql,取数
b34175	175	*
F	176	* @param where
0924c5	177	* @param list9802
F	178	* @param alone
b34175	179	* @param mainPk
F	180	* @param exprot496list
0924c5	181	* @return
F	182	*/
97a11f	183	private Exprot496Entiry loadData(HttpServletRequest request, List<T9802Entity> list9802, T9771Entity alone, Page page, StringBuilder mainPk, List<Exprot496Entiry> exprot496list,Exprot496Entiry currentExprot496Entiry) {
0924c5	184	//取9676字段列表数据
b34175	185	final StringJoiner fieldsJoiner = new StringJoiner(",");//字段列表
F	186	final StringJoiner expressionJoiner = new StringJoiner(";");//权限表达式
	187	final StringJoiner picFields = new StringJoiner(";");//图片字段
	188	final StringJoiner titleToFile = new StringJoiner(";");//图片字段作为文件名称
	189	final StringJoiner tbColsJoiner = new StringJoiner(",");//计算用-汇总列字段
	190	final StringJoiner tbColsExcelJoiner = new StringJoiner(";");//excel导出用-汇总列字段
0924c5	191	//---排序
b34175	192	final StringJoiner orderByJoiner = new StringJoiner(",");//排序字段
F	193	final StringJoiner fristField = new StringJoiner("");
0924c5	194	AtomicInteger fumIndex = new AtomicInteger();//标记导出时候字段位置
F	195	AtomicInteger tabColIndex = new AtomicInteger();//标记导出字段在汇总列的位置
	196	fumIndex.set(0);
	197	tabColIndex.set(0);
	198	Exprot496Entiry entiry=new Exprot496Entiry();
	199	List<T9802Entity> collect = list9802.stream().filter(z -> z.getFormid().equals(alone.getFormid())).collect(Collectors.toList());
	200	Map<String, String> dyTitleBy496 = getDyTitleBy496(page, alone.formtype.equals(16) ? 0 : 1, alone.formid);//取得当前功能号的标题
	201	collect.forEach(k->{
	202	fieldsJoiner.add(k.fieldid.toLowerCase());
	203	if(
	204	(k.gridControlType!=null&&(k.gridControlType.equals(9)\|\|k.gridControlType.equals(19)\|\|k.gridControlType.equals(40)))
	205	\|\|
	206	(k.controlType!=null&&(k.controlType.equals(9)\|\|k.controlType.equals(19)\|\|k.controlType.equals(40)))
	207	) {
	208	//面板或表格设置了图片类型
	209	picFields.add(k.fieldid);
	210	}
	211	//处理动态标题
	212	if(StringUtils.isNotBlank((dyTitleBy496.get(k.fieldid.toLowerCase())))){
	213	k.fieldname=dyTitleBy496.get(k.fieldid.toLowerCase());
	214	}else {
	215	k.fieldname=StringUtils.isNotBlank(k.gridcaption)?k.gridcaption:k.fieldname;
	216	}
	217	//exportTitle==1表示以字段名称作为文件名
	218	if(k.exportTitle.equals(1)){
	219	titleToFile.add(k.fieldid);
	220	}
	221	//--处理排序
	222	if(orderByJoiner.length()==0) {
	223	if (tabColIndex.get() == 0 && StringUtils.isBlank(k.index1)) {
	224	fristField.add(k.fieldid);
	225	orderByJoiner.add(k.fieldid+" ");
	226	} else if (tabColIndex.get() == 0 && StringUtils.isNotBlank(k.index1)) {
	227	fristField.add(k.fieldid);
	228	orderByJoiner.add(k.index1+" ");
	229	}
	230	}
	231	//--处理汇总列
	232	if(k.activefuns==1&&k.sumField!=0){
	233	//生效且选了汇总方式
	234	//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(";");
	235	tbColsJoiner.add(k.fieldid.toLowerCase()+"#"+k.sumField+":"+ (StringUtils.isNotBlank(k.funclinkname)?k.funclinkname.replaceAll(",", "@p@"):""));
	236	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"));
	237	}else {
	238	fumIndex.getAndIncrement();
	239	}
	240	//处理权限表达式
b34175	241	if (StringUtils.isNotBlank(k.showFieldValueExpression)) {
F	242	expressionJoiner.add(k.fieldid.toLowerCase() + "\|" + k.showFieldValueExpression);
0924c5	243	}
b34175	244	});
0924c5	245	//1，当前功能号是子功能号：把关联的字段也放进去查询，避免这些字段没选上导出，到时处理不了关系
F	246	//2--当前功能号是主功能号，需要提前把子功能号的fk字段取回来，让主功能号查询
	247	//需要去重处理
b34175	248	if (fieldsJoiner.length() == 0) {
F	249	//没设置导出字关段则取关联字段作为导出字段
	250	fieldsJoiner.add(alone.seekGroupID.replaceAll(";", ","));
0924c5	251
b34175	252	}
F	253	String tempFields = "," + fieldsJoiner.toString().toLowerCase() + ",";
	254	if (!tempFields.contains("," + alone.seekGroupID.toLowerCase() + ",")) {
	255	fieldsJoiner.add(alone.seekGroupID.replaceAll(";", ","));
	256	}
	257	if (StringUtils.isNotBlank(alone.subFkKey)) {
97a11f	258	String[] subFkKeys = alone.subFkKey.replaceAll(";", ",").split(",");
b34175	259	for (String subFkKey : subFkKeys) {
F	260	tempFields = "," + fieldsJoiner.toString().toLowerCase() + ",";
	261	if (!tempFields.contains("," + subFkKey.toLowerCase() + ",")) {
	262	fieldsJoiner.add(subFkKey);
	263	}
	264	}
	265	}
	266	if (mainPk != null && mainPk.length() > 0) {
	267	//判断当前功能号的where是否需要替换
	268	if (!alone.seekGroupID.equalsIgnoreCase(mainPk.toString())) {
	269	//不相同,需要从主功能号里取数拼接where, 第一个是主功能号数据
	270	if (exprot496list.size() > 0) {
97a11f	271	String[] seekGroup= alone.seekGroupID.toLowerCase().replaceAll("rowid","").replaceAll("detailrowid","").split(";");//去掉rowid,detailrowid,因为这些只是限制条件，实际导出所有，所以取数可以过滤这些特定字段(rowid,detailrowid)
F	272	StringJoiner where=new StringJoiner(" and ");
	273	Exprot496Entiry entiry496=exprot496list.get(0);
	274	if(currentExprot496Entiry!=null){
	275	entiry496=currentExprot496Entiry;
	276	}
	277	for(String str:seekGroup) {
	278	where.add(str+ "=" + GridUtils.prossSqlParm(entiry496.data.get(0).get(str) + ""));
	279	}
	280	page.setWhere(where.toString());
b34175	281	}
F	282	}
0924c5	283	}
F	284	String sqlList = Arrays.stream(fieldsJoiner.toString().split(",")).distinct().collect(Collectors.joining(","));
	285	//--处理权限表达式
b34175	286	Page newpage = new Page();
0924c5	287	newpage.setExpr(expressionJoiner.toString());
F	288	newpage.setTbCols(Base64.getEncoder().encodeToString(tbColsJoiner.toString().getBytes(StandardCharsets.UTF_8)));
	289	newpage.setTbCols(gridService.proccTbCols(newpage));
b34175	290	String sql = "[" + sqlList.replaceAll(",", "],[") + "]";
F	291	sql = this.exprTOSql(newpage, sql);
0924c5	292	//取9676的数据
F	293	try {
	294	SpObserver.setDBtoInstance("_" + env.get(SessionKey.DATA_BASE_ID));
	295	newpage.setUserCode(page.getUserCode());
	296	newpage.setUserName(page.getUserName());
	297	newpage.setSql(sql);
	298	newpage.setTableName(alone.getHdtable());
	299	String dataGroup = gtGrid.prossDataGroup(request, page.getFormid(), 0);//数据组权限
	300	newpage.setWhere(page.getWhere()+dataGroup);
	301	newpage.setFormid(alone.getFormid());
	302	newpage.setPageSize(Integer.MAX_VALUE);
3b6cb4	303	newpage.setAutopaging(2);//不分页
0924c5	304	newpage.setId(fristField.toString());
F	305	newpage.setPageNum(1);
97a11f	306	newpage.setOrderBy(orderByJoiner.length() > 0 ? orderByJoiner.toString().replaceAll(";", ",") : alone.seekGroupID.replaceAll(";",","));
0924c5	307	gridService.loadAll(newpage);// 第二步，调用相对应业务类取得数据,分页功能
c1439b	308	}catch (Exception ex) {
F	309	throw ex;
	310	}finally {
0924c5	311	SpObserver.setDBtoInstance();
F	312	}
	313	// List<Map<String, Object>> list = baseService.getJdbcTemplate().queryForList(" select " + sql+ " from " + alone.getHdtable() + " " + where);
	314
	315	entiry.data=newpage.getData();//自身数据
	316	entiry.page=newpage;
b34175	317	entiry.metaData = collect;
F	318	entiry.mainFormName = alone.mainformname;
	319	entiry.mainFormid = alone.mainformid;
	320	entiry.picFields = picFields.toString();
	321	entiry.formid = alone.formid;
	322	entiry.formName = StringUtils.isNotBlank(alone.labelName) ? alone.labelName : alone.formname;
	323	entiry.titleToFileName = titleToFile.toString();
	324	entiry.fk = alone.fK;
	325	entiry.seekGroupID = alone.seekGroupID;
7204e3	326	entiry.sheetName = StringUtils.isBlank(alone.labelName) ? alone.formname : alone.labelName;
b34175	327	if (entiry.sheetName.contains("/")) {
F	328	entiry.sheetName = entiry.sheetName.replaceAll("/", "-");
	329	}
	330	entiry.exprotType = alone.formtype.equals(16) ? ExprotType.Panel : ExprotType.Grid;
	331	entiry.tbCols = tbColsExcelJoiner.toString();
0924c5	332	return entiry;
F	333	}
	334	}