2020_src/epc/feeCloud-backend.git

2020-05-18 a18bfacbf56b401f6e0fdae8710fbca4df8cff77

commit \| author \| age
a18bfa	1	package cn.autoform.excel.impl;
Z	2
	3	import cn.autoform.bean.ExcelLog;
	4	import cn.autoform.db.entity.FormBaseEntity;
	5	import cn.autoform.db.entity.FormFieldEntity;
	6	import cn.autoform.db.exten.AllFormData;
	7	import cn.autoform.db.exten.ServicesFormData;
	8	import cn.autoform.factory.FormFactory;
	9	import cn.autoform.fw.exception.RowOverLengthException;
	10	import cn.autoform.util.form.entity.FormDataParam;
	11	import cn.autoform.util.form.paramap.FormDataEditMap;
	12	import cn.autoform.util.thread.Keys;
	13	import cn.autoform.util.thread.ThreadData;
	14	import cn.autoform.util.tool.JSONTool;
	15	import cn.autoform.web.client.FormClient;
	16	import cn.autoform.web.interceptor.spring.InitParamInterceptor;
	17	import cn.autoform.web.service.datamanagement.DataManagementService;
	18	import cn.autoform.web.service.formbase.FormBaseService;
	19	import jxl.Sheet;
	20	import jxl.Workbook;
	21	import jxl.read.biff.BiffException;
	22	import jxl.write.WritableFont;
	23	import jxl.write.WritableSheet;
	24	import jxl.write.WritableWorkbook;
	25	import lombok.Data;
	26	import org.springframework.beans.factory.annotation.Autowired;
	27	import org.springframework.context.annotation.Scope;
	28	import org.springframework.stereotype.Component;
	29	import org.springframework.web.multipart.MultipartFile;
	30
	31	import javax.annotation.Resource;
	32	import javax.servlet.http.HttpServletRequest;
	33	import javax.servlet.http.HttpServletResponse;
	34	import java.beans.Transient;
	35	import java.io.IOException;
	36	import java.io.OutputStream;
	37	import java.net.URLEncoder;
	38	import java.util.*;
	39	import java.util.concurrent.TimeoutException;
	40
	41	@Component
	42	@Scope("prototype")
	43	public class ImpExcelThread{
	44	// implements Runnable
	45	private Map<String,Object> cookie = new HashMap<>();
	46
	47	private static final String TAGATTRIBUTE = "tag_Attribute";
	48
	49
	50	@Autowired
	51	private ExcelServiceImpl excelService;
	52
	53	@Autowired
	54	private ExcelImpl excel;
	55
	56
	57	@Autowired
	58	private DataManagementService dataManagementService = null;
	59
	60	@Resource
	61	private FormDataEditMap formDataEditMap;
	62
	63	@Autowired
	64	private FormClient formClient;
	65
	66	@Autowired
	67	private FormFactory formFactory;
	68
	69	@Autowired
	70	private FormBaseService formBaseService = null;
	71
	72	/**
	73	* 表格主页面
	74	*/
	75	private static final String MAIN = "main";
	76
	77	/**
	78	* 主数据缓存
	79	*/
	80
	81	/**
	82	* 当前所有子表单别名
	83	*/
	84	Map<String,String> subs = new HashMap<>();
	85
	86	/**
	87	*
	88	*/
	89
	90
	91
	92	//判断是导入还是导出,true 为导入，false 为导出
	93	private Boolean flag;
	94
	95	private String formId;
	96
	97	private String tenantID;
	98
	99	private String userName;
	100
	101	private MultipartFile file;
	102
	103	private HttpServletRequest request;
	104	private HttpServletResponse response;
	105
	106	// @Override
	107	public void run(){
	108	//添加cookie
	109	System.out.println("-->添加cookie");
	110	this.cookie.keySet().stream().forEach(o->ThreadData.set(InitParamInterceptor.COOKIE_KEY.get(o),this.cookie.get(o)));
	111	System.out.println("进入线程--->");
	112	imExcel();
	113	}
	114
	115	/**
	116	* 导入
	117	*/
	118	@Transient
	119	public void imExcel() {
	120	System.out.println("当前公司id为"+ThreadData.get(Keys.COMPANY_ID));
	121	System.out.println("开始导入");
	122	//添加操作log
	123	Integer id = null;
	124	Workbook workbook = null;
	125	try {
	126	id = this.addLog();
	127	System.out.println("添加id为:"+id);
	128	//最终添加字段
	129	List<FormDataParam> formList = new ArrayList<>();
	130	//查询表单字段,比对格式用
	131	List<FormFieldEntity> rsList = getFormFields();
	132	//控件别名对应属性
	133	Map<String,FieldInfo> aliasing = getFieldInfo(JSONTool.toObj(rsList.get(0).getFieldset(),Map.class));
	134
	135
	136	//创建关联工作簿
	137	workbook = Workbook.getWorkbook(file.getInputStream());
	138	//取得主表页面
	139	Sheet sheet = workbook.getSheet(MAIN);
	140	//循环主表格行
	141	System.out.println("当前行列"+sheet.getRows()+"--"+sheet.getColumns());
	142	for(int c=1; c< sheet.getRows(); c++){
	143	FormDataParam formDataParam = new FormDataParam(this.formId,this.tenantID,this.userName);
	144	Map<String,Object> data = new HashMap<>();
	145	formDataParam.setData(data);
	146	//获取标题
	147	FieldInfo f = null;
	148	for(int r=0; r<sheet.getColumns(); r++){
	149	String title = sheet.getCell(r, 0).getContents();
	150	System.out.println("当前行:"+title);
	151	f = aliasing.get(title);
	152	f.setNum(Objects.toString(c));
	153	//当前添加主表数据
	154	String d = sheet.getCell(r, c).getContents();
	155	String val = getValue(d,f,rsList);
	156	data.put(f.getAlias(),val);
	157	}
	158	//添加当前行子表数据
	159	for(String p:subs.keySet()){
	160	try {
	161	data.put(subs.get(p),getSubList(p,workbook,f,aliasing,rsList));
	162	} catch (Exception e) {
	163	e.printStackTrace();
	164	}
	165	}
	166	formList.add(formDataParam);
	167	}
	168	System.out.print("最终导入数据:---->"+ JSONTool.toJson(formList));
	169	List<ServicesFormData> dataList = new ArrayList<>();
	170	formList.stream().forEach(j->dataList.add(new AllFormData(formDataEditMap.apply(j)).getFormData()));
	171	//处理数据，转换为费用云可用数据
	172	System.out.println("处理后数据:"+ JSONTool.toJson(dataList));
	173	Integer res = formClient.impDatas(dataList);
	174	if(res==1) {
	175	updateAddLog(id, 2, "操作成功");
	176	}else{
	177	updateAddLog(id,3,"业务系统添加数据异常");
	178	}
	179	} catch (IOException e) {
	180	System.out.print("导入失败");
	181	updateAddLog(id,3,e.getMessage());
	182	e.printStackTrace();
	183	} catch (BiffException e) {
	184	System.out.print("导入失败");
	185	updateAddLog(id,3,e.getMessage());
	186	e.printStackTrace();
	187	} catch (Exception e) {
	188	System.out.print("导入失败");
	189	updateAddLog(id,3,e.getMessage());
	190	e.printStackTrace();
	191	}finally {
	192	if(workbook != null) {
	193	workbook.close();
	194	}
	195	}
	196
	197
	198	}
	199
	200
	201	/**
	202	* 获取所有表单字段
	203	* @return
	204	*/
	205	public List<FormFieldEntity> getFormFields(){
	206	return dataManagementService.queryFormFields(formId, tenantID);
	207	}
	208
	209	/**
	210	* 添加操作纪录
	211	* @param
	212	*/
	213	public Integer addLog() throws TimeoutException {
	214	ExcelLog ex = new ExcelLog(formId,tenantID);
	215	ex.setFormName(file.getOriginalFilename());
	216	ex.setType(this.flag?"10":"20");
	217	ex.setTenantId(ThreadData.get(Keys.TENANT_ID));
	218	return formClient.addExcelLog(ex);
	219	}
	220
	221	/**
	222	* 取出子表单数据
	223	*/
	224	public List<Map<String,Object>> getSubList(String sheetPage,Workbook workbook,FieldInfo f,Map<String,FieldInfo> aliasing,List<FormFieldEntity> rsList) throws Exception {
	225	List<Map<String,Object>> subs = new ArrayList<>();
	226	System.out.print("当前子表单页:"+sheetPage);
	227	//获取子表单页
	228	Sheet sheet = workbook.getSheet(sheetPage);
	229	for(int c=1; c< sheet.getRows(); c++){
	230	//获取编号标题
	231	String d = sheet.getCell(0, c).getContents();
	232	Map<String,Object> data = new HashMap<>();
	233	for (int r = 1; r < sheet.getColumns(); r++) {
	234	if(Objects.equals(d,f.getNum())) {
	235	String title = sheet.getCell(r, 0).getContents();
	236	System.out.print("当前子表单行名称:"+title);
	237	FieldInfo fc = aliasing.get(title);
	238	//当前添加主表数据
	239	String val = getValue(sheet.getCell(r, c).getContents(),fc,rsList);
	240	data.put(fc.getAlias(),val);
	241	}else{
	242	break;
	243	}
	244	}
	245	subs.add(data);
	246	}
	247	return subs;
	248	}
	249
	250	/**
	251	* 修改log状态
	252	* @param id
	253	* @param state
	254	*/
	255	public void updateAddLog(Integer id,Integer state,String msg) {
	256	ExcelLog ex = new ExcelLog();
	257	ex.setId(id);
	258	ex.setState(state);
	259	ex.setOverTime(new Date());
	260	ex.setMag(msg);
	261	try {
	262	formClient.updateExcelLog(ex);
	263	} catch (TimeoutException e) {
	264	e.printStackTrace();
	265	}
	266	}
	267
	268	/**
	269	* 获取控件属性
	270	* @param
	271	*/
	272	public Map<String,FieldInfo> getFieldInfo(Map<String,Object> formList){
	273	Map<String,FieldInfo> m = new HashMap<>();
	274	formList.keySet().stream().forEach(o->{
	275	FieldInfo fi = new FieldInfo();
	276	Map<String,Object> item = JSONTool.toObj(JSONTool.toJson(formList.get(o)),Map.class);
	277	fi.setTag_Type(Objects.toString(item.get("tag_Type")));
	278	fi.setColumnOrderNum(Integer.parseInt(Objects.toString(item.get("columnOrderNum"),"0")));
	279	fi.setParentsubFormNum(Integer.parseInt(Objects.toString(item.get("parentsubFormNum"),"0")));
	280	Map<String,Object> attr = JSONTool.toObj(JSONTool.toJson(item.get(TAGATTRIBUTE)),Map.class);
	281	fi.setAlias(Objects.toString(attr.get("alias")));
	282	fi.setTitle(Objects.toString(attr.get("title")));
	283	fi.setMustcheck(Boolean.parseBoolean(Objects.toString(attr.get("mustcheck"),"false")));
	284	m.put(Objects.toString(attr.get("title")),fi);
	285	if(Objects.equals(Objects.toString(item.get("tag_Type")),"subform")){
	286	this.subs.put(Objects.toString(attr.get("title")),Objects.toString(attr.get("alias")));
	287	}
	288	});
	289	System.out.println("最终表单属性:"+ JSONTool.toJson(m));
	290	System.out.println("所有子表单:"+ JSONTool.toJson(this.subs));
	291	return m;
	292	}
	293
	294	/**
	295	* 控件校验
	296	*/
	297	public String getValue(String value,FieldInfo fieldInfo,List<FormFieldEntity> rsList) throws Exception {
	298	//首先校验必填
	299	if(fieldInfo.getMustcheck()){
	300	if(value ==null \|\| Objects.equals("",value)){
	301	throw new RowOverLengthException(fieldInfo.getTitle()+"列-值必填");
	302	}
	303	}
	304	//按控件类型校验
	305	if(fieldInfo.getTag_Type().equals("number")){
	306	if(!excel.isNumeric(value)){
	307	throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value);
	308	}else{
	309	return value;
	310	}
	311	}else if(fieldInfo.getTag_Type().equals("calendar")){
	312	if(!excel.isValidDate(value)){
	313	throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value);
	314	}else{
	315	return "20"+value;
	316	}
	317	//获取excel的下拉列表和树型的值
	318	}else if(fieldInfo.getTag_Type().equals("dropdownlist") \|\| fieldInfo.getTag_Type().equals("tree")){
	319	//调用获取Code 的方法
	320	String val = excel.getCode(rsList,value,fieldInfo.getColumnOrderNum(),fieldInfo.getTag_Type(),this.tenantID);
	321	if(val!=null && !Objects.equals(val,"")){
	322	return val;
	323	}else{
	324	throw new RowOverLengthException(fieldInfo.getTitle()+"列-值为:"+value+"->主数据缺失！");
	325	}
	326	}else if(fieldInfo.getTag_Type().equals("auto")){
	327	return "自动生成";
	328	}else{
	329	return value;
	330	}
	331	}
	332
	333
	334	@Data
	335	class FieldInfo{
	336
	337	//是否必填
	338	private Boolean mustcheck;
	339	//控件类型
	340	private String tag_Type;
	341	//控件别名
	342	private String alias;
	343	//数据源类型
	344	private String datasourcetype = null;
	345	//控件顺序序号
	346	private Integer columnOrderNum;
	347	//是否是子表单0为子表单，不为0则对应子表单顺序号
	348	private Integer parentsubFormNum;
	349	//标题
	350	private String title;
	351	//子表单关联id
	352	private String num;
	353	}
	354
	355	public void setFlag(Boolean flag) {
	356	this.flag = flag;
	357	}
	358
	359	public String getFormId() {
	360	return formId;
	361	}
	362
	363	public void setFormId(String formId) {
	364	this.formId = formId;
	365	}
	366
	367	public String getTenantID() {
	368	return tenantID;
	369	}
	370
	371	public void setTenantID(String tenantID) {
	372	this.tenantID = tenantID;
	373	}
	374
	375	public String getUserName() {
	376	return userName;
	377	}
	378
	379	public void setUserName(String userName) {
	380	this.userName = userName;
	381	}
	382
	383	public MultipartFile getFile() {
	384	return file;
	385	}
	386
	387	public void setFile(MultipartFile file) {
	388	this.file = file;
	389	}
	390
	391	public HttpServletRequest getRequest() {
	392	return request;
	393	}
	394
	395	public void setRequest(HttpServletRequest request) {
	396	this.request = request;
	397	}
	398
	399	public HttpServletResponse getResponse() {
	400	return response;
	401	}
	402
	403	public void setResponse(HttpServletResponse response) {
	404	this.response = response;
	405	}
	406
	407
	408	public Map<String, Object> getCookie() {
	409	return cookie;
	410	}
	411
	412	public void setCookie(Map<String, Object> cookie) {
	413	this.cookie = cookie;
	414	}
	415
	416
	417
	418	}