package cn.autoform.factory.product.impl; import cn.autoform.bean.BudgetData; import cn.autoform.bean.CodeAndValueBean; import cn.autoform.bean.FormTable; import cn.autoform.bean.page.PageResult; import cn.autoform.db.entity.*; import cn.autoform.db.exten.AllFormData; import cn.autoform.factory.FormFactory; import cn.autoform.factory.product.ProductMethod; import cn.autoform.fw.exception.DuplicateFormNameException; import cn.autoform.fw.exception.FormClientException; import cn.autoform.fw.exception.RowOverLengthException; import cn.autoform.fw.utility.ConstMap; import cn.autoform.fw.utility.FormDataUtil; import cn.autoform.util.form.AutoConditionUtil; import cn.autoform.util.tool.ObjectUtil; import cn.autoform.util.tool.PageUtil; import cn.autoform.util.tool.RequestContext; import cn.autoform.web.mapper.datamanagement.DataManagementMapper; import cn.autoform.web.mapper.formTable.FormTableMapper; import cn.autoform.web.service.datamanagement.DataManagementService; import cn.autoform.web.service.formbase.FormBaseService; import cn.autoform.web.service.masterdata.MasterDataService; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import javax.annotation.Resource; import java.io.UnsupportedEncodingException; import java.util.*; import java.util.concurrent.TimeoutException; @Component public class CloudProductMethod implements ProductMethod { public static final Map DBTYPE = new HashMap<>(); private static final Map DBTYPELENGTH = new HashMap<>(); private static final List FIELDLIST = new ArrayList<>(); @Autowired private FormFactory formFactory; @Autowired private MasterDataService masterDataService = null; static { // 单行文本 DBTYPE.put("text", ConstMap.VARCHAR150); // 单选按钮 DBTYPE.put("radio", ConstMap.VARCHAR50); // 下拉列表框 DBTYPE.put("dropdownlist", ConstMap.LONGTEXT); // 日期控件 DBTYPE.put("calendar", ConstMap.VARCHAR30); // 复选框 DBTYPE.put("checkbox", ConstMap.LONGTEXT); // 子表单 DBTYPE.put("subform", ConstMap.VARCHAR30); // 数字 DBTYPE.put("number", ConstMap.VARCHAR30); // 多行文本 DBTYPE.put("textarea", ConstMap.LONGTEXT); // HTML DBTYPE.put("htmlediter", ConstMap.LONGTEXT); // 条码 DBTYPE.put("barcode", ConstMap.VARCHAR30); // 二维码 DBTYPE.put("qrcode", ConstMap.VARCHAR200); // 图片 DBTYPE.put("image", ConstMap.LONGTEXT); // 文件上传 DBTYPE.put("fileupload", ConstMap.LONGTEXT); // 数型控件 DBTYPE.put("tree", ConstMap.LONGTEXT); // 金额组件 DBTYPE.put("amount", ConstMap.LONGTEXT); //自定义控件 DBTYPE.put("mytext",ConstMap.LONGTEXT); //自动生成编号控件 DBTYPE.put("auto",ConstMap.VARCHAR50); // 单行文本 DBTYPELENGTH.put("text", 150); // 单选按钮 DBTYPELENGTH.put("radio", 50); // 日期控件 DBTYPELENGTH.put("calendar", 30); // 复选框 // 子表单 DBTYPELENGTH.put("subform", 30); // 数字 DBTYPELENGTH.put("number", 30); // 条码 DBTYPELENGTH.put("barcode", 30); // 二维码 DBTYPELENGTH.put("qrcode", 200); // 表单固定字段 // FIELDLIST.add("DATAROWNUM"); FIELDLIST.add("TENANTID"); FIELDLIST.add("PARENTDATAROWNUM"); FIELDLIST.add("DELETEFLG"); FIELDLIST.add("CREATEUSER"); FIELDLIST.add("CREATEDATETIME"); FIELDLIST.add("UPDATEUSER"); FIELDLIST.add("ENCLOSUREINFO"); FIELDLIST.add("DATASOLIDDIFY"); FIELDLIST.add("processState"); FIELDLIST.add("processLog"); FIELDLIST.add("processTask"); FIELDLIST.add("processUser"); } @Resource private FormTableMapper formTable; @Autowired private FormBaseService formBaseService = null; @Autowired DataManagementMapper dataManagementMapper = null; @Autowired private DataManagementService dataManagementService = null; public String createForm(FormBaseEntity formBase, JSONObject jsonObj) throws Exception { String userName = jsonObj.getString("userName"); // 表单数据不存在，表单基本表插入 formBase.setCreateUser(userName); JSONObject filedset = JSONObject.fromObject(jsonObj.getString("filedset")); // 过滤选项卡。 formBase.setFieldset(filedset.toString()); formBaseService.addFormBase(formBase); AutoConditionUtil.filterOption(filedset); List fieldtypeList = new ArrayList<>(); Map> subFormObjcloumn = new HashMap<>(); Map> subFormObjFieldtype = new HashMap<>(); // 主表字段别名 List aliasValues = new ArrayList<>(); // 主表字段别名及字段类型 Map aliasValuesMap = new HashMap<>(); // Map<子表单号,该子表单字段别名> Map> aliasSubValues = new HashMap<>(); // <别名，类型> Map> aliasSubValuesMap = new HashMap<>(); // 子表单号 List subFormNums = new ArrayList<>(); // Map<子表单号，子表单别名> Map subFormNames = new HashMap<>(); Iterator it = filedset.keys(); //父类型 Map parentType = new HashMap<>(); // long asdasda = System.currentTimeMillis(); List fieldPropertyEntitys = new ArrayList<>(); while (it.hasNext()) { String currentKey = it.next().toString(); // 表单字段管理表插入 JSONObject currentJsonObj = filedset.getJSONObject(currentKey); Integer parentsubFormNum = currentJsonObj.getInt("parentsubFormNum"); Integer columnOrderNum = currentJsonObj.getInt("columnOrderNum"); parentType.put(columnOrderNum, currentJsonObj.getString(ConstMap.TAGTYPEKEY)); if(parentType.get(parentsubFormNum) != null && parentType.get(parentsubFormNum).equals("tab")){ parentsubFormNum = 0; } FormFieldEntity formFieldEntity = new FormFieldEntity(); // 表单ID formFieldEntity.setFormID(formBase.getFormID()); // 控件状态 true formFieldEntity.setFieldIsShow("true"); // 控件状态 true formFieldEntity.setSearchIsShow("true"); // 租户ID formFieldEntity.setTenantID(formBase.getTenantID()); // 字段顺番 formFieldEntity.setColumnOrderNum(currentJsonObj.getInt(ConstMap.COLUMNORDERNUMKEY)); // 字段类型 formFieldEntity.setFieldtype(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); // 字段属性管理表插入 JSONObject fieldPropertyObj = currentJsonObj.getJSONObject(ConstMap.TAGATTRIBUTE); String title = Objects.toString(fieldPropertyObj.get("title"),""); if("DATAROWNUM".equals(title)){ continue; } if (parentsubFormNum == 0 && !"tag".equals(currentJsonObj.getString(ConstMap.TAGTYPEKEY))) { // 通过字段顺番来定义数据表 fieldtypeList.add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); subFormNums.add(columnOrderNum); String subFormType = currentJsonObj.getString("tag_Type"); if (subFormType.equals("subform")) { subFormNames.put(columnOrderNum, Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); } // 添加别名 aliasValues.add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasValuesMap.put(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)), currentJsonObj.getString("tag_Type")); } else { if(subFormNums.contains(parentsubFormNum) ){ // 字段别名 List subFields = new ArrayList<>(); // 字段类型 List subFieldsType = new ArrayList<>(); // Map<子表单号，Map<别名，类型>> Map subMap = new HashMap<>(); if (aliasSubValues.get(parentsubFormNum) != null) { aliasSubValues.get(parentsubFormNum) .add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasSubValuesMap.get(parentsubFormNum).put( Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)), currentJsonObj.getString(ConstMap.TAGTYPEKEY)); subFormObjFieldtype.get(parentsubFormNum) .add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); } else { subFieldsType.add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); subFields.add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasSubValues.put(parentsubFormNum, subFields); subMap.put(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)), currentJsonObj.getString(ConstMap.TAGTYPEKEY)); aliasSubValuesMap.put(parentsubFormNum, subMap); subFormObjFieldtype.put(parentsubFormNum, subFieldsType); } subFormObjcloumn.put(parentsubFormNum, aliasSubValues.get(parentsubFormNum)); } } // 上层子表单 formFieldEntity.setParentsubFormNum(parentsubFormNum); // 表示顺序 formFieldEntity.setFieldOrderNum(currentJsonObj.getInt("fieldOrderNum")); // 字段别名 formFieldEntity.setFieldKey(currentJsonObj.getJSONObject(ConstMap.TAGATTRIBUTE).getString("alias")); // 作成者 formFieldEntity.setCreateUser(userName); formBaseService.addFormField(formFieldEntity); Iterator itSub = fieldPropertyObj.keys(); // List fieldPropertyEntitys = new ArrayList<>(); while (itSub.hasNext()) { String currentKeyInner = itSub.next().toString(); FieldPropertyEntity fieldPropertyEntity = new FieldPropertyEntity(); String value = fieldPropertyObj.getString(currentKeyInner); if("DATAROWNUM".equals(value)){ continue; } // 表单ID fieldPropertyEntity.setFormID(formBase.getFormID()); // 租户ID fieldPropertyEntity.setTenantID(formBase.getTenantID()); // 字段顺番 fieldPropertyEntity.setColumnOrderNum(currentJsonObj.getInt(ConstMap.COLUMNORDERNUMKEY)); // 属性名 fieldPropertyEntity.setProperty(currentKeyInner); // 属性值 fieldPropertyEntity.setValue(value); // 作成者 fieldPropertyEntity.setCreateUser(userName); fieldPropertyEntitys.add(fieldPropertyEntity); } } // long cx = System.currentTimeMillis(); //放这别动 formBaseService.addFormFieldProperty(fieldPropertyEntitys); // System.err.println("添加表单属性接口: " + (System.currentTimeMillis() - cx)); // System.err.println("整理数据及添加到 formBase" + (System.currentTimeMillis() - asdasda)); // long cvxcv = System.currentTimeMillis(); insertFormdataManager(fieldtypeList, subFormObjcloumn, subFormObjFieldtype, formBase.getFormID(), formBase.getTenantID(), userName, aliasValues, aliasSubValues, aliasValuesMap, aliasSubValuesMap, subFormNames); // System.err.println("真正添加数据接口" + (System.currentTimeMillis() - cvxcv)); return ""; } /** * 表单数据管理表插入操作 * * @param fieldtypeList * @param subFormObjcloumn * @param subFormObjFieldtype * @param tenantID * @throws RowOverLengthException * @throws DuplicateFormNameException */ private void insertFormdataManager(List fieldtypeList, Map> subFormObjcloumn, Map> subFormObjFieldtype, String formID, String tenantID, String userName, List aliasValues, Map> aliasSubValues, Map aliasValuesMap, Map> aliasSubValuesMap, Map subFormNames) throws RowOverLengthException, DuplicateFormNameException { FormdataManagerEntity formdataManagerEntity = new FormdataManagerEntity(); formdataManagerEntity.setFormID(formID); formdataManagerEntity.setTenantID(tenantID); // 非子表单 formdataManagerEntity.setSubFormFlg("0"); formdataManagerEntity.setFormdataNum(0); formdataManagerEntity.setDatarowNum(0); // 主表原字段别名 List oldField = new ArrayList<>(); // 查询主表单字段是否创建数据管理表 FormdataManagerEntity formdataManagerEntityRs = formBaseService.queryFormdataManagerNum(formdataManagerEntity); System.out.println("表单ID："+formID); if(formTable.getFormTable(formID)!=null){ // 数据表名 StringBuilder sbTableName = new StringBuilder(); sbTableName.append("formdata").append(FormDataUtil.getFormTableName(tenantID, formID)); String tableName = sbTableName.toString(); if (formBaseService.querySubFormName(tableName) != 0) { formBaseService.deleteformTable(formID); } formdataManagerEntity.setFormdatatable(tableName); formdataManagerEntity.setCreateUser(userName); formBaseService.addFormdataManager(formdataManagerEntity); // 表单数据表创建 String sql = createTable(tableName, fieldtypeList, aliasValues); //formBaseService.excuteFormDataTable(String.format(sql, tenantID, formID)); FormTable fo = new FormTable(); fo.setFormID(formID); fo.setJsonObj(String.format(sql, tenantID, formID)); fo.setFlag("cloud"); formTable.updateFormTable(fo); }else{ if (formdataManagerEntityRs == null) { // 数据表名 System.out.println("进入添加"); StringBuilder sbTableName = new StringBuilder(); sbTableName.append("formdata").append(FormDataUtil.getFormTableName(tenantID, formID)); String tableName = sbTableName.toString(); //先给注了,2018.2.5 优化接口yuezhw // if (formBaseService.querySubFormName(tableName) != 0) { // throw new DuplicateFormNameException(); // } formdataManagerEntity.setFormdatatable(tableName); formdataManagerEntity.setCreateUser(userName); formBaseService.addFormdataManager(formdataManagerEntity); // 表单数据表创建 String sql = createTable(tableName, fieldtypeList, aliasValues); //formBaseService.excuteFormDataTable(String.format(sql, tenantID, formID)); FormTable fo = new FormTable(); fo.setFormID(formID); fo.setJsonObj(String.format(sql, tenantID, formID)); fo.setFlag("cloud"); formTable.addFormTable(fo); } else { System.out.println("进入闯进"); // 已经创建过，则alter表单数据表 StringBuilder showTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ show columns from %s"); String sql = String.format(showTableSql.toString(), tenantID, formID, formdataManagerEntityRs.getFormdatatable()); // 原主表单字段及类型 List colunmsFieldEntity = formBaseService.showColumns(sql); Map fieldsLength = new HashMap<>(); // 更新后主表中不存在的子表单名 List unExistsSubFormName = new ArrayList<>(); for (ColunmsFieldEntity temp : colunmsFieldEntity) { String tempStr = temp.getField(); if (!FIELDLIST.contains(tempStr)) { if(!aliasValues.contains(tempStr) && !"DATAROWNUM".equals(tempStr)){ unExistsSubFormName.add("formdata_"+tenantID+"_"+formID+"_"+tempStr); } oldField.add(tempStr); String oldFieldLengthStr = temp.getType(); if (!"".equals(oldFieldLengthStr)) { fieldsLength.put(tempStr, oldFieldLengthStr); } } } // 不存在的子表单名，将formdatamanager表中的状态deleteFlg设置为1 for (String name : unExistsSubFormName) { StringBuilder alterDataStateSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */" + " update FORMDATAMANAGER set deleteflg = 1 WHERE FORMDATATABLE = '%s' AND TENANTID = '%s' AND FORMID = '%s'"); String alterSql = String.format(alterDataStateSql.toString(), tenantID, formID,name,tenantID,formID); formBaseService.excuteAlterDataState(alterSql); } String sqlalter = alterTable(formdataManagerEntityRs.getFormdatatable(), oldField, fieldtypeList, fieldsLength, aliasValues, aliasValuesMap); if (sqlalter != null) { formBaseService.excuteFormDataTable(String.format(sqlalter, tenantID, formID)); } } } if(!subFormObjcloumn.isEmpty() && !aliasSubValues.isEmpty()) subFormInsert(subFormObjcloumn, subFormObjFieldtype, formID, tenantID, userName, aliasSubValues, aliasSubValuesMap, subFormNames, oldField); } /** * 子表单插入 * * @param subFormObjcloumn * @param subFormObjFieldtype * @param formID * @param tenantID * @param userName * @throws RowOverLengthException * @throws DuplicateFormNameException */ private void subFormInsert(Map> subFormObjcloumn, Map> subFormObjFieldtype, String formID, String tenantID, String userName, Map> aliasSubValues, Map> aliasSubValuesMap, Map subFormNames, List oldFields) throws RowOverLengthException, DuplicateFormNameException { // 删除画面侧删除的子表单表 FormdataManagerEntity formdataManagerEntitySubTemp = new FormdataManagerEntity(); formdataManagerEntitySubTemp.setFormID(formID); formdataManagerEntitySubTemp.setTenantID(tenantID); // 子表单 formdataManagerEntitySubTemp.setSubFormFlg("1"); // 查询子表单字段是否创建数据管理表 Map formdataManagerEntityRsSubMap = formBaseService .queryFormdataManagerNum2(formdataManagerEntitySubTemp); // 子表单插入 Iterator it = subFormObjcloumn.keySet().iterator(); while (it.hasNext()) { Integer currentKey = it.next(); FormdataManagerEntity formdataManagerEntitySub = new FormdataManagerEntity(); formdataManagerEntitySub.setFormID(formID); formdataManagerEntitySub.setTenantID(tenantID); // 子表单 formdataManagerEntitySub.setSubFormFlg("1"); formdataManagerEntitySub.setFormdataNum(currentKey); StringBuilder subTableNameSub = new StringBuilder(); subTableNameSub.append("formdata") .append(FormDataUtil.getSubFormTableName(tenantID, formID, subFormNames.get(currentKey))); String tableNameSub = subTableNameSub.toString(); formdataManagerEntitySub.setFormdatatable(tableNameSub); // 查询子表单字段是否创建数据管理表 FormdataManagerEntity formdataManagerEntityRsSub = formBaseService .queryFormdataManager(formdataManagerEntitySub); if (formdataManagerEntityRsSub == null) { // 判断表名是否存在 // System.out.println("tablesub"+tableNameSub); // System.out.println("formdataManagerEntitySub"+JSONTool.toJson(formdataManagerEntitySub)); // System.out.println("formdataManagerEntityRsSub"+JSONTool.toJson(formdataManagerEntityRsSub)); if (formBaseService.querySubFormName(tableNameSub) != 0) { //将formdatamanager中该表状态设置为0 StringBuilder alterDataStateSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */" + " update FORMDATAMANAGER set deleteflg = 0 WHERE FORMDATATABLE = '%s' AND TENANTID = '%s' AND FORMID = '%s'"); String alterSql = String.format(alterDataStateSql.toString(), tenantID, formID,tableNameSub,tenantID,formID); formBaseService.excuteAlterDataState(alterSql); // 删除已经存在的子表单表 formdataManagerEntityRsSubMap.remove(currentKey); StringBuilder showTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ show columns from %s"); String sql = String.format(showTableSql.toString(), tenantID, formID,tableNameSub); // 原表结构 List colunmsFieldEntity = formBaseService.showColumns(sql); List oldSubField = new ArrayList<>(); Map fieldsLengths = new HashMap<>(); for (ColunmsFieldEntity temp : colunmsFieldEntity) { String tempStr = temp.getField(); if (!FIELDLIST.contains(tempStr)) { oldSubField.add(tempStr); String oldFieldLengthStr = temp.getType(); if (!"".equals(oldFieldLengthStr)) { fieldsLengths.put(tempStr, oldFieldLengthStr); } } } String sqlalter = alterSubTable(subFormObjcloumn.get(currentKey), oldSubField, subFormObjFieldtype.get(currentKey), fieldsLengths, aliasSubValuesMap.get(currentKey), tableNameSub); if (sqlalter != null) { formBaseService.excuteFormDataTable(String.format(sqlalter, tenantID, formID)); } } else{ formdataManagerEntitySub.setFormdatatable(tableNameSub); formdataManagerEntitySub.setDatarowNum(0); formdataManagerEntitySub.setCreateUser(userName); formBaseService.addFormdataManager(formdataManagerEntitySub); String subSql = createSubTable(subFormObjcloumn.get(currentKey), tableNameSub, subFormObjFieldtype.get(currentKey), aliasSubValues.get(currentKey)); // 子表单数据表创建 // System.out.println("执行的sql"+subSql); // System.out.println("删除已存在的表"); StringBuilder showTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ drop table if exists "+tableNameSub); formBaseService.excuteFormDataTable(String.format(showTableSql.toString(), tenantID, formID)); formBaseService.excuteFormDataTable(String.format(subSql, tenantID, formID)); } } else { // 删除已经存在的子表单表 formdataManagerEntityRsSubMap.remove(currentKey); StringBuilder showTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ show columns from %s"); String sql = String.format(showTableSql.toString(), tenantID, formID, formdataManagerEntityRsSub.getFormdatatable()); // 原表结构 List colunmsFieldEntity = formBaseService.showColumns(sql); List oldSubField = new ArrayList<>(); Map fieldsLengths = new HashMap<>(); for (ColunmsFieldEntity temp : colunmsFieldEntity) { String tempStr = temp.getField(); // 字段 if (!FIELDLIST.contains(tempStr)) { oldSubField.add(tempStr); String oldFieldLengthStr = temp.getType(); if (!"".equals(oldFieldLengthStr)) { fieldsLengths.put(tempStr, oldFieldLengthStr); } } } String sqlalter = alterSubTable(subFormObjcloumn.get(currentKey), oldSubField, subFormObjFieldtype.get(currentKey), fieldsLengths, aliasSubValuesMap.get(currentKey), tableNameSub); if (sqlalter != null) { formBaseService.excuteFormDataTable(String.format(sqlalter, tenantID, formID)); } } } // Iterator delI = formdataManagerEntityRsSubMap.keySet().iterator(); // 删除表 // while (delI.hasNext()) { // Integer key = delI.next(); // FormdataManagerEntity formdataManagerEntityDel = new // FormdataManagerEntity(); // formdataManagerEntityDel.setFormID(formID); // formdataManagerEntityDel.setTenantID(tenantID); // // 非子表单 // formdataManagerEntityDel.setSubFormFlg("1"); // formdataManagerEntityDel.setFormdataNum(key); // formBaseService.deleteFormdataManager(formdataManagerEntityDel); // StringBuilder showTableSql = new StringBuilder( // "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = // '%s' AND FORMID = '%s' */ DROP TABLE IF EXISTS %s;"); // String dropSql = String.format(showTableSql.toString(), tenantID, // formID, // formdataManagerEntityRsSubMap.get(key).getFormdatatable()); // // formBaseService.excuteFormDataTable(dropSql); // } } /** * 创建表单数据表 * * @param tableName * 表名 * @param fieldtypeList * 表字段类型 * @return 创建表语句 * @throws RowOverLengthException */ private String createSubTable(List subFormObjcloumn, String tableName, List fieldtypeList, List aliasValues) throws RowOverLengthException { if (!checkRowLength(fieldtypeList, 0)) { throw new RowOverLengthException(); } StringBuilder createTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ create table "); createTableSql.append(tableName); createTableSql.append("("); // createTableSql.append("DATAROWNUM int not null auto_increment,"); createTableSql.append("TENANTID char(50) not null,"); // createTableSql.append("SUBDATAROWNUM int not null,");//修改之前 createTableSql.append("DATAROWNUM int not null auto_increment,"); // 表单数据表创建 for (int i = 0; i < subFormObjcloumn.size(); i++) { if (DBTYPE.containsKey(fieldtypeList.get(i))) { if("DATAROWNUM".equals(subFormObjcloumn.get(i))){ }else{ createTableSql.append(subFormObjcloumn.get(i)); createTableSql.append(DBTYPE.get(fieldtypeList.get(i))); createTableSql.append(" NULL "); createTableSql.append(ConstMap.COMMA); } } } createTableSql.append("DELETEFLG char(1) comment '0未删除，1删除',"); createTableSql.append("CREATEUSER varchar(50),"); createTableSql.append("CREATEDATETIME TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',"); createTableSql.append("UPDATEUSER varchar(50),"); // createTableSql.append("DATAROWNUM int not null auto_increment,");修改之前 createTableSql.append("PARENTDATAROWNUM int not null,"); createTableSql.append("UPDATEDATETIME TIMESTAMP NOT NULL DEFAULT now() on update now(),"); // createTableSql.append("primary key (DATAROWNUM,TENANTID,SUBDATAROWNUM)");修改之前 createTableSql.append("primary key (DATAROWNUM,TENANTID,PARENTDATAROWNUM)"); createTableSql.append(");"); return createTableSql.toString(); } private String createTable(String tableName, List fieldtypeList, List aliasValues) throws RowOverLengthException { if (!checkRowLength(fieldtypeList, 0)) { throw new RowOverLengthException(); } StringBuilder createTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ create table "); createTableSql.append(tableName); createTableSql.append("("); createTableSql.append("DATAROWNUM int not null auto_increment ,"); createTableSql.append("TENANTID char(50) not null,"); createTableSql.append("PARENTDATAROWNUM int not null,"); // 表单数据表创建 for (int i = 0; i < aliasValues.size(); i++) { if (DBTYPE.containsKey(fieldtypeList.get(i))) { createTableSql.append(aliasValues.get(i)); createTableSql.append(DBTYPE.get(fieldtypeList.get(i))); createTableSql.append(" NULL "); createTableSql.append(ConstMap.COMMA); } } createTableSql.append("DELETEFLG char(1) comment '0未删除，1删除',"); createTableSql.append("CREATEUSER varchar(50),"); createTableSql.append("CREATEDATETIME TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',"); createTableSql.append("UPDATEUSER varchar(50),"); createTableSql.append("ENCLOSUREINFO longtext,"); createTableSql.append("DATASOLIDDIFY longtext,"); createTableSql.append("processState varchar(2) DEFAULT '10',"); createTableSql.append("processLog varchar(255),"); createTableSql.append("processTask varchar(255),"); createTableSql.append("processUser varchar(20),"); createTableSql.append("UPDATEDATETIME TIMESTAMP NOT NULL DEFAULT now() on update now(),"); createTableSql.append("primary key (DATAROWNUM,TENANTID,PARENTDATAROWNUM)"); createTableSql.append(");"); return createTableSql.toString(); } /** * 修改表结构 * * @param tableName * 表名 * @param fieldtypeList * 表字段类型 * @return 修改表语句 * @throws RowOverLengthException */ private String alterTable(String tableName, List oldField, List fieldtypeList, Map fieldsLength, List aliasValues, Map aliasValuesMap) throws RowOverLengthException { Map> optionColumns = genAddAndDelColumns(oldField, fieldtypeList, aliasValues); List deleteList = optionColumns.get("DELETE"); List addList = optionColumns.get("ADD"); // List updateList = optionColumns.get("UPDATE"); int maxLengthDel = 0; for (String delObj : deleteList) { if (fieldsLength.get(delObj) != null) { if (!fieldsLength.get(delObj).equals("longtext")) { Integer length = Integer.parseInt(fieldsLength.get(delObj).replaceAll("[^0-9]", "")); maxLengthDel += length; // 数据类型大小150 varchar(150) } } } if (!checkRowLength(fieldtypeList, maxLengthDel)) { throw new RowOverLengthException(); } StringBuilder alterTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ alter table "); alterTableSql.append(tableName); //表别名修改时，表中新增字段 // for (int i = 0; i < updateList.size(); i++) { // String field = updateList.get(i); // String type = aliasValuesMap.get(field); // if (DBTYPE.containsKey(type)) { // alterTableSql.append(" ADD COLUMN "); // alterTableSql.append(field); // alterTableSql.append(DBTYPE.get(type)); // alterTableSql.append(" NULL AFTER SUBDATAROWNUM"); // alterTableSql.append(ConstMap.COMMA); // } // } // 表单数据表列增加 for (int i = 0; i < addList.size(); i++) { String field = addList.get(i); String type = aliasValuesMap.get(field); if (DBTYPE.containsKey(type)) { alterTableSql.append(" ADD COLUMN "); alterTableSql.append(field); alterTableSql.append(DBTYPE.get(type)); alterTableSql.append(" NULL AFTER PARENTDATAROWNUM"); alterTableSql.append(ConstMap.COMMA); } } if (!addList.isEmpty() ) { //|| !updateList.isEmpty() alterTableSql.deleteCharAt(alterTableSql.length() - 1); } // 表结构没有更改 if (deleteList.isEmpty() && addList.isEmpty()) { return null; } alterTableSql.append(";"); return alterTableSql.toString(); } private String alterSubTable(List subFormObjcloumn, List oldField, List fieldtypeList, Map fieldsLength, Map aliasValuesMap, String subTableName) throws RowOverLengthException { Map> optionColumns = genAddAndDelColumns(oldField, fieldtypeList, subFormObjcloumn); List deleteList = optionColumns.get("DELETE"); List addList = optionColumns.get("ADD"); int maxLengthDel = 0; for (String delObj : deleteList) { if (fieldsLength.get(delObj) != null) { if (!fieldsLength.get(delObj).equals("longtext")) { Integer length = Integer.parseInt(fieldsLength.get(delObj).replaceAll("[^0-9]", "")); maxLengthDel += length; } } } if (!checkRowLength(fieldtypeList, maxLengthDel)) { throw new RowOverLengthException(); } StringBuilder alterTableSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ alter table "); alterTableSql.append(subTableName); // 表单数据表列增加 for (int i = 0; i < addList.size(); i++) { String field = addList.get(i); String type = aliasValuesMap.get(field); if (DBTYPE.containsKey(type)) { alterTableSql.append(" ADD COLUMN "); alterTableSql.append(field); alterTableSql.append(DBTYPE.get(type)); alterTableSql.append(" NULL AFTER PARENTDATAROWNUM"); alterTableSql.append(ConstMap.COMMA); } } if (!addList.isEmpty()) { alterTableSql.deleteCharAt(alterTableSql.length() - 1); } // 表结构没有更改 if (deleteList.isEmpty() && addList.isEmpty()) { return null; } alterTableSql.append(";"); return alterTableSql.toString(); } /** * 差分新旧字段，生成需要增加和删除的表字段 * * @return */ private Map> genAddAndDelColumns(List oldField, List fieldtypeList, List subFormObjcloumn) { Map> map = new HashMap<>(); List needToDel = new ArrayList<>(); List needToAdd = new ArrayList<>(); List needToUpd = new ArrayList<>(); int count = 0; for (String alias : subFormObjcloumn) { // 哥，勿删！ if (DBTYPE.containsKey(fieldtypeList.get(count))) { if (!oldField.contains(alias)) { needToAdd.add(alias); } else { needToUpd.add(alias); } } count++; } // for (String alias : subFormObjcloumn) { // if (DBTYPE.containsKey(fieldtypeList.get(count))) { // if (!oldField.contains(alias)) { // needToAdd.add(alias); //// // needToUpd.add(alias); // } // } // count++; // } // // for (String old : oldField) { // if (!subFormObjcloumn.contains(old)) { // needToDel.add(old); // } // } map.put("DELETE", needToDel); map.put("ADD", needToAdd); map.put("UPDATE", needToUpd); return map; } private boolean checkRowLength(List fieldtypeList, Integer oldLength) { int rowLength = 0; for (String type : fieldtypeList) { if (DBTYPELENGTH.containsKey(type)) { rowLength += DBTYPELENGTH.get(type); } } if ((rowLength + oldLength) > ConstMap.MAXROWLENGTH) { return false; } return true; } // 更新 public void formBaseUpdate(FormBaseEntity formBase, JSONObject jsonObj, JSONArray linkages) throws RowOverLengthException, DuplicateFormNameException { String formMode = jsonObj.getString("formMode"); String userName = jsonObj.getString("userName"); // 更新用户 formBase.setUpdateUser(userName); // 表单基本表更新操作 formBaseService.updateFormBaseInfo(formBase); // 删除表单字段 formBaseService.deleteFormField(formBase.getFormID(), formBase.getTenantID()); // 删除表单属性 formBaseService.deleteFormFieldProperty(formBase.getFormID(), formBase.getTenantID()); List cloumnOrderNumList = new ArrayList<>(); List fieldtypeList = new ArrayList<>(); Map> subFormObjcloumn = new HashMap<>(); Map> subFormObjFieldtype = new HashMap<>(); // Map<子表单号，子表单别名> Map subFormNames = new HashMap<>(); // 更新后字段别名 List aliasValues = new ArrayList<>(); // 更新后字段别名及类型例：{子表单=subForm , ...} Map aliasValuesMap = new HashMap<>(); // 例：{1={子表单1 ,子表单2, ...}} Map> aliasSubValues = new HashMap<>(); // 例：{1={子表单=subForm , ...}} Map> aliasSubValuesMap = new HashMap<>(); // 子表单号 List subFormNums = new ArrayList<>(); JSONObject filedset = JSONObject.fromObject(formBase.getFieldset()); Iterator it = filedset.keys(); while (it.hasNext()) { String currentKey = it.next().toString(); // 表单字段管理表插入 JSONObject currentJsonObj = filedset.getJSONObject(currentKey); Integer parentsubFormNum = currentJsonObj.getInt("parentsubFormNum"); Integer columnOrderNum = currentJsonObj.getInt("columnOrderNum"); FormFieldEntity formFieldEntity = new FormFieldEntity(); // 表单ID formFieldEntity.setFormID(formBase.getFormID()); // 租户ID formFieldEntity.setTenantID(formBase.getTenantID()); // 控件状态 true formFieldEntity.setFieldIsShow("true"); // 控件状态 true formFieldEntity.setSearchIsShow("true"); // 字段顺番 formFieldEntity.setColumnOrderNum(currentJsonObj.getInt(ConstMap.COLUMNORDERNUMKEY)); // 字段类型 formFieldEntity.setFieldtype(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); // 字段属性管理表插入 JSONObject fieldPropertyObj = currentJsonObj.getJSONObject(ConstMap.TAGATTRIBUTE); if (parentsubFormNum == 0) { // 通过字段顺番来定义数据表 cloumnOrderNumList.add(currentJsonObj.getInt(ConstMap.COLUMNORDERNUMKEY)); fieldtypeList.add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); subFormNums.add(columnOrderNum); String subFormType = currentJsonObj.getString("tag_Type"); if (subFormType.equals("subform")) { subFormNames.put(columnOrderNum, Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); } // 添加主表别名 aliasValues.add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasValuesMap.put(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)),currentJsonObj.getString("tag_Type")); } else { for (Integer subFormNum : subFormNums) { if (Objects.equals(subFormNum, parentsubFormNum)) { List subFields = new ArrayList<>(); List subFieldsType = new ArrayList<>(); Map subMap = new HashMap<>(); if (aliasSubValues.get(parentsubFormNum) != null) { aliasSubValues.get(parentsubFormNum) .add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasSubValuesMap.get(parentsubFormNum).put( Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)), currentJsonObj.getString("tag_Type")); subFormObjFieldtype.get(parentsubFormNum) .add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); } else { subMap.put(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS)), currentJsonObj.getString("tag_Type")); subFieldsType.add(currentJsonObj.getString(ConstMap.TAGTYPEKEY)); subFields.add(Objects.toString(fieldPropertyObj.get(ConstMap.ALIAS))); aliasSubValues.put(parentsubFormNum, subFields); aliasSubValuesMap.put(parentsubFormNum, subMap); subFormObjFieldtype.put(parentsubFormNum, subFieldsType); } subFormObjcloumn.put(parentsubFormNum, aliasSubValues.get(parentsubFormNum)); } } } // 上层子表单 formFieldEntity.setParentsubFormNum(parentsubFormNum); // 表示顺序 formFieldEntity.setFieldOrderNum(currentJsonObj.getInt("fieldOrderNum")); // 字段别名 formFieldEntity.setFieldKey(currentJsonObj.getJSONObject(ConstMap.TAGATTRIBUTE).getString("alias")); // 作成者 formFieldEntity.setCreateUser(userName); formBaseService.addFormField(formFieldEntity); Iterator itSub = fieldPropertyObj.keys(); List fieldPropertyEntitys = new ArrayList<>(); while (itSub.hasNext()) { currentKey = itSub.next().toString(); FieldPropertyEntity fieldPropertyEntity = new FieldPropertyEntity(); String value = fieldPropertyObj.getString(currentKey); // 表单ID fieldPropertyEntity.setFormID(formBase.getFormID()); // 租户ID fieldPropertyEntity.setTenantID(formBase.getTenantID()); // 字段顺番 fieldPropertyEntity.setColumnOrderNum(currentJsonObj.getInt(ConstMap.COLUMNORDERNUMKEY)); // 属性名 fieldPropertyEntity.setProperty(currentKey); // 属性值 fieldPropertyEntity.setValue(value); // 作成者 fieldPropertyEntity.setCreateUser(userName); fieldPropertyEntitys.add(fieldPropertyEntity); } formBaseService.addFormFieldProperty(fieldPropertyEntitys); } insertFormdataManager(fieldtypeList, subFormObjcloumn, subFormObjFieldtype, formBase.getFormID(), formBase.getTenantID(), userName, aliasValues, aliasSubValues, aliasValuesMap, aliasSubValuesMap,subFormNames); // if (!"5".equals(formMode)) { // } if (!"5".equals(formMode)) { // 查看表单集合内是否存在formSetID FormSetEntity createFormSetInfo = new FormSetEntity(); createFormSetInfo.setFormSetID(jsonObj.getString("formSetID")); createFormSetInfo.setTenantID(formBase.getTenantID()); createFormSetInfo.setFormName(formBase.getFormName()); createFormSetInfo.setUpdateUser(userName); formBaseService.updateFormSetInfo(createFormSetInfo); FormVersionEntity formVersionEntity = new FormVersionEntity(); formVersionEntity.setFormID(formBase.getFormID()); formVersionEntity.setFormSetID(jsonObj.getString("formSetID")); formVersionEntity.setFormVersion(jsonObj.getString("formVer")); formVersionEntity.setTenantID(formBase.getTenantID()); formBaseService.updateFormVersion(formVersionEntity); if (!StringUtils.isEmpty(formBase.getValidationRuleGroupID())) { formBaseService.deleteFormValidation(formBase.getValidationRuleGroupID()); List listFormValidationEntity = new ArrayList<>(); for (int i = 0; i < linkages.size(); i++) { FormValidationEntity entity = new FormValidationEntity(); entity.setFormID(formBase.getFormID()); entity.setOrderNum(i + 1); entity.setValidationRuleGroupID(formBase.getValidationRuleGroupID()); entity.setTenantID(formBase.getTenantID()); entity.setRuleset(linkages.getString(i)); entity.setCreateUser(userName); entity.setUpdateUser(userName); listFormValidationEntity.add(entity); } formBaseService.insertFormValidation(listFormValidationEntity); } } } public T removeFormData(String formID, String tenantID, JSONArray datarowNumArray, String userName) { List formDataMangers = dataManagementMapper.queryFormdataMangers(formID, tenantID); String tableName = formDataMangers.get(0).getFormdatatable(); StringBuilder updateSql = new StringBuilder(); updateSql.append( "/*!mycat:sql= select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ UPDATE "); updateSql.append(tableName); updateSql.append(" SET DELETEFLG = '1' WHERE "); updateSql.append(" TENANTID = '%s' AND DATAROWNUM IN ("); for (Object dataRowNum : datarowNumArray) { updateSql.append(dataRowNum); updateSql.append(ConstMap.COMMA); } updateSql.deleteCharAt(updateSql.length() - 1); updateSql.append(")"); String sql = String.format(updateSql.toString(), tenantID, formID, tenantID); dataManagementMapper.removeFormData(sql); return null; } /** * 查询分页数据 * * @param formID * @param tenantID * @return */ @Override public PageResult> queryFormDataPage(String formID, String tenantID, int pageNum, int pageSize, String where, boolean dataPower) { String historyFormId = RequestContext.setFormId(formID); List formFieldsList = dataManagementMapper.queryFormFields3(formID, tenantID); if(ObjectUtil.empty(formFieldsList)) return new PageResult<>(null); PageUtil.initPage(); PageResult> page = new PageResult<>( dataManagementMapper.queryFormDataPage( formFieldsList.stream().filter(form->form.getParentsubFormNum() == 0 && DBTYPE.containsKey(form.getFieldtype())).toArray(FormFieldEntity[]::new), where)); RequestContext.setFormId(historyFormId); return page; } @Override public List> queryFormDataList(String formID, String tenantID, String where){ List formFieldsList = dataManagementMapper.queryFormFields3(formID, tenantID); if(ObjectUtil.empty(formFieldsList)) return new ArrayList<>(); return dataManagementMapper.queryFormDataPage( formFieldsList.stream().filter(form->form.getParentsubFormNum() == 0 && DBTYPE.containsKey(form.getFieldtype())).toArray(FormFieldEntity[]::new), where); } /** * 查询数据列表 */ public List> queryFormDataList(String formID, String tenantID) { // 取得表单数据管理 List formDataMangerList = dataManagementMapper.queryFormdataMangers(formID, tenantID); if (formDataMangerList == null || formDataMangerList.isEmpty()) { return null; } List formFieldsList = dataManagementMapper.queryFormFields3(formID, tenantID); StringBuilder sqlSelectMain = new StringBuilder( "/*!mycat:sql= select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ SELECT %s FROM "); for (FormDataMangerEntity formDataMangerEntity : formDataMangerList) { if (formDataMangerEntity.getFormdataNum() == 0) { sqlSelectMain.append(formDataMangerEntity.getFormdatatable()); } } StringBuilder sqlSelcetFields = new StringBuilder(); sqlSelcetFields.append(" DATAROWNUM,"); for (FormFieldEntity formFieldEntity : formFieldsList) { if (formFieldEntity.getParentsubFormNum() == 0 && DBTYPE.containsKey(formFieldEntity.getFieldtype())) { if (!ConstMap.SUBFORM.equals(formFieldEntity.getFieldtype())) { String fieldName = formFieldEntity.getFieldKey(); String fieldName1 = ConstMap.FIELD + formFieldEntity.getColumnOrderNum(); sqlSelcetFields.append(" IFNULL("); sqlSelcetFields.append(fieldName); sqlSelcetFields.append(" , '') AS "); sqlSelcetFields.append(fieldName1); sqlSelcetFields.append(","); } else { sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append(" AS"); sqlSelcetFields.append(" FIELD"); sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append(","); } } } sqlSelcetFields.deleteCharAt(sqlSelcetFields.length() - 1); StringBuilder whereSql = new StringBuilder(" WHERE "); whereSql.append(" deleteFlg = '0'"); whereSql.append(" AND TENANTID = '"); whereSql.append(tenantID); whereSql.append("'"); sqlSelectMain.append(whereSql); String executeSql = String.format(sqlSelectMain.toString(), tenantID, formID, sqlSelcetFields.toString()); return dataManagementMapper.queryFormdatas(executeSql); } /** * 查询单条数据详情 * * @param formID * @param tenantID * @param datarowNum * @param formdataNum * @return * @throws TimeoutException * @throws FormClientException * @throws Exception */ @SuppressWarnings("unchecked") public Map queryFormDataList(String formID, String tenantID, Integer datarowNum, String formdataNum) throws TimeoutException, FormClientException { Map rs = new HashMap<>(); // 取得表单数据管理 List formDataMangerList = dataManagementMapper.queryFormdataMangers(formID, tenantID); List formFieldsList = dataManagementMapper.queryFormFields3(formID, tenantID); StringBuilder sqlSelectMain = new StringBuilder( "/*!mycat:sql= select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ SELECT %s FROM "); List mytexts = new ArrayList<>(); List columNums = new ArrayList<>(); for (FormDataMangerEntity formDataMangerEntity : formDataMangerList) { if (formDataMangerEntity.getFormdataNum() == 0) { sqlSelectMain.append(formDataMangerEntity.getFormdatatable()); } } StringBuilder sqlSelcetFields = new StringBuilder(); sqlSelcetFields.append(" DATAROWNUM,"); for (FormFieldEntity formFieldEntity : formFieldsList) { if("mytext".equals(formFieldEntity.getFieldtype())){ mytexts.add(formFieldEntity.getFieldKey()); columNums.add(formFieldEntity.getColumnOrderNum()); } if (formFieldEntity.getParentsubFormNum() == 0 && DBTYPE.containsKey(formFieldEntity.getFieldtype())) { if (!ConstMap.SUBFORM.equals(formFieldEntity.getFieldtype()) && !ConstMap.TAB.equals(formFieldEntity.getFieldtype())) { String fieldName = formFieldEntity.getFieldKey(); sqlSelcetFields.append(" IFNULL("); sqlSelcetFields.append(fieldName); sqlSelcetFields.append(" , '') AS '"); sqlSelcetFields.append(fieldName); sqlSelcetFields.append("',"); } else { sqlSelcetFields.append("'"); sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append("'"); sqlSelcetFields.append(" AS "); sqlSelcetFields.append(ConstMap.FIELD + formFieldEntity.getColumnOrderNum()); sqlSelcetFields.append(","); } } } sqlSelcetFields.deleteCharAt(sqlSelcetFields.length() - 1); StringBuilder whereSql = new StringBuilder(" WHERE "); whereSql.append(" deleteFlg = '0'"); whereSql.append(" AND TENANTID = '"); whereSql.append(tenantID); whereSql.append("'"); whereSql.append(" AND PARENTDATAROWNUM = 0 AND "); whereSql.append(" DATAROWNUM = "); whereSql.append(datarowNum); sqlSelectMain.append(whereSql); String executeSql = String.format(sqlSelectMain.toString(), tenantID, formID, sqlSelcetFields.toString()); Map rsMapMain = dataManagementMapper.queryFormdatas(executeSql).get(0); //如果是关联表单，实时查询关联表单的值 if(mytexts!= null && !mytexts.isEmpty()){ for(int i=0; i< mytexts.size(); i++){ String myValue = rsMapMain.get(mytexts.get(i)).toString(); if(StringUtils.isEmpty(myValue)){ continue; } Map proValue = new HashMap<>(); dataManagementMapper.queryFormFieldProperty(formID,tenantID,columNums.get(i)) .stream() // .filter(field ->"chooseForm".equals(field.getProperty())) .forEach(field ->{proValue.put(field.getProperty(), field.getValue());}); String reType = proValue.get("relevancesourcetype").toString(); if(reType == null || "".equals(reType)) continue; if(reType.equals("chooseForm")){ Object value = proValue.get("chooseForm"); String chooseId = JSONObject.fromObject(JSONObject.fromObject(value.toString()).getString("formID")).getString("formID"); IOType flag = JSONObject.fromObject(JSONObject.fromObject(value.toString()).getString("formID")).getString("bussinessSystem").equals("services") ? IOType.services :IOType.cloud; String where = "DATAROWNUM in(" + myValue + ")"; rsMapMain.put(mytexts.get(i), this.formFactory.createFormMethod(flag).queryFormDataPage(chooseId, tenantID ,1 ,100, where, false)); }else{ Object value = proValue.get("fundata"); String key = JSONObject.fromObject(value.toString()).getString("key"); // AssociatedFormData associatedFormData = new AssociatedFormData(); // associatedFormData.setKey(key); // associatedFormData.setPageNum(1); // associatedFormData.setPageSize(10000); // SeacheConditions seacheConditions = new SeacheConditions(); // associatedFormData.setSearch(seacheConditions); BudgetData budgetData = new BudgetData(); this.dataManagementService.getAssociateData(budgetData); } } } //主表单添加数据 rs.put("main", rsMapMain); if (formdataNum != null && !"".equals(formdataNum)) { String[] formdataNumArray = formdataNum.split(",", 0); Map>> rsSubMap = new HashMap<>(); for (String formdataNumObj : formdataNumArray) { List> rsMap = dataManagementService.queryCloudSubFormDataList(formID, tenantID,Integer.parseInt(formdataNumObj), datarowNum); if (rsMap != null) { rsSubMap.put(Integer.parseInt(formdataNumObj), rsMap); } else { JSONArray rsNull = new JSONArray(); rsNull.add(new JSONObject()); rsSubMap.put(Integer.parseInt(formdataNumObj), rsNull); } } rs.put("subs", rsSubMap); } return rs; } @Override public IOType getProductMethodThod() { return IOType.cloud; } @Override public List getFormFieldMainValue(String formID, String tenantID, String field, String tableName) throws Exception { StringBuilder StringBuilder = new StringBuilder( "/*!mycat:sql= select TENANTID from FORMDATAMANAGER WHERE TENANTID = '%s' AND FROMID = '%s' */ SELECT a.%s FROM %s a"); String excuteSql = String.format(StringBuilder.toString(), tenantID, formID, field, tableName); List aa = this.dataManagementService.queryCloudMainValue(excuteSql); return aa; } @Override public List getMasterBaseDataListService(String tenantID, String name) { return masterDataService.queryMasterDefines(tenantID,name); } @Override public List getMasterBaseElementListService(String formID, String tenantID, String code) { return masterDataService.queryMasterElements(tenantID,code); } @Override public Object getMasterBaseElementListServiceForNew(String tenantID, String code, String callbackId,String codes,String value,String isAll) { JSONObject jsonObj = JSONObject.fromObject(code); String defindCode = null; String eleCode = null; if (jsonObj.has("defindCode")) { defindCode = jsonObj.getString("defindCode"); } if (jsonObj.has("eleCode")) { eleCode = jsonObj.getString("eleCode"); } if (defindCode != null && eleCode != null) { String eleName = null; if (jsonObj.has(ConstMap.ELENAME)) { eleName = jsonObj.getString(ConstMap.ELENAME); } String parentCode = null; if (jsonObj.has("parentCode")) { parentCode = jsonObj.getString("parentCode"); } Map rsMap = new HashMap<>(); // 单行主数据 if (parentCode == null && eleName == null) { List masterElementEntitys = masterDataService.queryMasterValues(tenantID, defindCode, eleCode); List addList = new ArrayList<>(); for (int i = 0; i < masterElementEntitys.size(); i++) { CodeAndValueBean codeAndvaule = new CodeAndValueBean(); codeAndvaule.setCode(masterElementEntitys.get(i).getEle_code()); codeAndvaule.setValue(masterElementEntitys.get(i).getEle_value()); addList.add(codeAndvaule); } rsMap.put(callbackId, addList); return rsMap; } else if (parentCode == null) { // Radio CheckBox DropDownList List masterDataCode = masterDataService.queryMasterValues(tenantID, defindCode, eleCode); List masterDataName = masterDataService.queryMasterValues(tenantID, defindCode, eleName); List addList = new ArrayList<>(); if (masterDataCode.size() == masterDataName.size()) { for (int i = 0; i < masterDataCode.size(); i++) { CodeAndValueBean codeAndvaule = new CodeAndValueBean(); codeAndvaule.setCode(masterDataCode.get(i).getEle_value()); codeAndvaule.setValue(masterDataName.get(i).getEle_value()); addList.add(codeAndvaule); } } rsMap.put(callbackId, addList); return rsMap; } else { // 树形主数据 List masterDataCode = masterDataService.queryMasterValues(tenantID, defindCode, eleCode); List masterDataName = masterDataService.queryMasterValues(tenantID, defindCode, eleName); List masterDataParent = masterDataService.queryMasterValues(tenantID, defindCode, parentCode); List addList = new ArrayList<>(); if (masterDataCode.size() == masterDataName.size() && masterDataCode.size() == masterDataParent.size()) { for (int i = 0; i < masterDataCode.size(); i++) { CodeAndValueBean codeAndvaule = new CodeAndValueBean(); codeAndvaule.setCode(masterDataCode.get(i).getEle_value()); codeAndvaule.setValue(masterDataName.get(i).getEle_value()); codeAndvaule.setPid(masterDataParent.get(i).getEle_value()); addList.add(codeAndvaule); } } rsMap.put(callbackId, addList); return rsMap; } } return null; } @Override public Object addFormData(AllFormData cFormData) { String userName = "转码错误"; try { userName = java.net.URLDecoder.decode(cFormData.getUserName(),"utf-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } String mainFormSql = genInsertSqlMain(cFormData.getInsertFields(), cFormData.getInsertvalues(), cFormData.getDataRowNum(),userName, cFormData.getFormID(), cFormData.getTenantID()); mainFormSql = String.format(mainFormSql, cFormData.getTenantID(), cFormData.getFormID(), cFormData.getFormName(), cFormData.getTenantID()); dataManagementService.excuteFormDataInsert(mainFormSql); List subFormSql = genInsertSqlSub(cFormData.getInsertFieldsSub(), cFormData.getInsertvaluesSub(), cFormData.getTableNamsMap(), cFormData.getTenantID(), cFormData.getFormID(), cFormData.getDataRowNum(), cFormData.getUserName()); for (String sql : subFormSql) { dataManagementService.excuteFormDataInsert(sql); } return null; } /** * 生成主表单SQL文 * * @param insertFields 插入的列 * @param insertvalues 插入的值 * @return */ private String genInsertSqlMain(List insertFields, List insertvalues, Integer dataRowNum, String userName, String formID, String tenantID) { StringBuilder sbSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FROMID = '%s' */ INSERT INTO %s ( "); sbSql.append("DATAROWNUM"); sbSql.append(ConstMap.COMMA); sbSql.append(ConstMap.TENANTIDKEY); sbSql.append(ConstMap.COMMA); sbSql.append("PARENTDATAROWNUM"); sbSql.append(ConstMap.COMMA); for (String insertField : insertFields) { sbSql.append(insertField); sbSql.append(ConstMap.COMMA); } sbSql.append(" DELETEFLG,CREATEUSER,CREATEDATETIME,UPDATEUSER,UPDATEDATETIME "); sbSql.append(") VALUES ("); // DATAROWNUM sbSql.append(dataRowNum); sbSql.append(ConstMap.COMMA); // TENANTID sbSql.append("'%s'"); sbSql.append(ConstMap.COMMA); // subdatarowNum sbSql.append(0); sbSql.append(ConstMap.COMMA); int i = 0; for (String insertValue : insertvalues) { insertValue = insertValue.replace("%", "%%");//后加上来的。 if(insertValue.equals(ConstMap.AUTOINCREMENT)){ insertValue = dataManagementService.getAutoNumber(formID, tenantID.toString(), insertFields.get(i), dataRowNum); } sbSql.append("'"); sbSql.append(insertValue); sbSql.append("'"); sbSql.append(ConstMap.COMMA); i++; } sbSql.append("'0','"); sbSql.append(userName); sbSql.append("',"); sbSql.append("CURRENT_TIMESTAMP(),'"); sbSql.append(userName); sbSql.append("',"); sbSql.append("CURRENT_TIMESTAMP()"); sbSql.append(");"); return sbSql.toString(); } /** * 生成子表单SQL文 * * @param insertFieldsSub 插入的列 * @param insertvaluesSub 插入的值 * @return */ private List genInsertSqlSub(Map>> insertFieldsSub, Map>> insertvaluesSub, Map tableNamsMap, String tenantID, String formID, Integer dataRowNum, String userName) { Iterator keys = insertFieldsSub.keySet().iterator(); List allList = new ArrayList<>(); while (keys.hasNext()) { Integer key = keys.next(); List sql = genSubSql(tableNamsMap.get(key).getFormdatatable(), insertFieldsSub.get(key), insertvaluesSub.get(key), tenantID, formID, dataRowNum, userName); allList.addAll(sql); } return allList; } /** * * @param tableName * @param insertFieldsBig * @param insertvaluesBig * @param tenantID * @param formID * @param dataRowNum * @return */ private List genSubSql(String tableName, List> insertFieldsBig, List> insertvaluesBig, String tenantID, String formID, Integer dataRowNum, String userName) { List sqlList = new ArrayList<>(); for (int i = 0; i < insertFieldsBig.size(); i++) { StringBuilder sbSql = new StringBuilder(); List insertFields = insertFieldsBig.get(i); List insertvalues = insertvaluesBig.get(i); sbSql.append("/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '"); sbSql.append(tenantID); sbSql.append("' AND FROMID = '"); sbSql.append(formID); sbSql.append("' */ "); sbSql.append(" INSERT INTO "); sbSql.append(tableName); sbSql.append("( "); // sbSql.append("DATAROWNUM ");//修改之前的 sbSql.append("PARENTDATAROWNUM"); sbSql.append(ConstMap.COMMA); sbSql.append(ConstMap.TENANTIDKEY); sbSql.append(ConstMap.COMMA); // sbSql.append("PARENTDATAROWNUM");//修改之前的 // sbSql.append("DATAROWNUM ");跟下面一起注的 // sbSql.append(ConstMap.COMMA); for (String insertField : insertFields) { if(!"DATAROWNUM".equals(insertField)){ sbSql.append(insertField); sbSql.append(ConstMap.COMMA); } } sbSql.append(" DELETEFLG,CREATEUSER,CREATEDATETIME,UPDATEUSER,UPDATEDATETIME "); sbSql.append(" ) VALUES ("); // DATAROWNUM sbSql.append(dataRowNum); sbSql.append(ConstMap.COMMA); // TENANTID sbSql.append("'"); sbSql.append(tenantID); sbSql.append("'"); sbSql.append(ConstMap.COMMA); // subdatarowNum // sbSql.append(i + 1);//修改之前的 // sbSql.append("''"); // sbSql.append(ConstMap.COMMA); for (String insertValue : insertvalues) { if(insertValue.equals(ConstMap.AUTOINCREMENT)){ insertValue = dataManagementService.getAutoNumber(formID, tenantID.toString(), insertFields.get(i), dataRowNum); } sbSql.append("'"); sbSql.append(insertValue); sbSql.append("'"); sbSql.append(ConstMap.COMMA); } sbSql.append("'0','"); sbSql.append(userName); sbSql.append("',"); sbSql.append("CURRENT_TIMESTAMP(),'"); sbSql.append(userName); sbSql.append("',"); sbSql.append("CURRENT_TIMESTAMP()"); sbSql.append(");"); sqlList.add(sbSql.toString()); } return sqlList; } /** * 生成主表单SQL文 * * @param insertFields 插入的列 * @param insertvalues 插入的值 * @return */ private String genUpdateSqlMain(List insertFields, List insertvalues, Integer dataRowNum, String userName) { StringBuilder sbSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FROMID = '%s' */ UPDATE %s "); sbSql.append(" SET "); int index = 0; for (String insertField : insertFields) { sbSql.append(insertField); sbSql.append(" = '"); String insertValues = insertvalues.get(index); insertValues = insertValues.replace("%", "%%"); sbSql.append(insertValues); sbSql.append("'"); sbSql.append(ConstMap.COMMA); index++; } sbSql.append(" UPDATEUSER = '"); sbSql.append(userName); sbSql.append("' ,UPDATEDATETIME = CURRENT_TIMESTAMP() "); sbSql.append("WHERE DATAROWNUM = "); sbSql.append(dataRowNum); sbSql.append(" AND TENANTID = "); sbSql.append(" '%s';"); return sbSql.toString(); } @Override public void updataFormData(AllFormData formData) { String mainFormSql = genUpdateSqlMain(formData.getInsertFields(), formData.getInsertvalues(), formData.getDataRowNum(), formData.getUserName()); mainFormSql = String.format(mainFormSql, formData.getTenantID(), formData.getFormID(), formData.getTableNamsMap().get(0).getFormdatatable(), formData.getTenantID()); dataManagementService.updateFormData(mainFormSql); // 先删除子表单数据后插入 Iterator tableName = formData.getTableNamsMap().keySet().iterator(); while (tableName.hasNext()) { Integer key = tableName.next(); if (key != 0) { String tableNameStr = formData.getTableNamsMap().get(key).getFormdatatable(); StringBuilder sbDelSql = new StringBuilder( "/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FROMID = '%s' */ DELETE FROM %s WHERE "); sbDelSql.append("TENANTID = '%s' AND PARENTDATAROWNUM = "); sbDelSql.append(formData.getDataRowNum()); sbDelSql.append(" ;"); String delSubSql = String.format(sbDelSql.toString(), formData.getTenantID(), formData.getFormID(), tableNameStr, formData.getTenantID(), formData.getDataRowNum()); dataManagementService.removeFormData(delSubSql); } } List subFormSql = genInsertSqlSub(formData.getInsertFieldsSub(), formData.getInsertvaluesSub(), formData.getTableNamsMap(), formData.getTenantID(), formData.getFormID(), formData.getDataRowNum(), formData.getUserName()); for (String sql : subFormSql) { dataManagementService.excuteFormDataInsert(sql); } } @Override public List> getSubFormDataListService(String formID, String tenantID, Integer formdataNum, Integer datarowNum) { // 取得表单数据管理 List formDataMangerList = dataManagementMapper.queryFormdataMangers1(formID, tenantID, formdataNum); if (formDataMangerList.isEmpty()) { return null; } List formFieldsList = dataManagementMapper.queryFormFields3(formID, tenantID); StringBuilder sqlSelectMain = new StringBuilder( "/*!mycat:sql= select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FROMID = '%s' */ SELECT %s FROM "); for (FormDataMangerEntity formDataMangerEntity : formDataMangerList) { if (formDataMangerEntity.getFormdataNum() == formdataNum) { sqlSelectMain.append(formDataMangerEntity.getFormdatatable()); } } StringBuilder sqlSelcetFields = new StringBuilder(); // sqlSelcetFields.append(" DATAROWNUM,"); // Map fieldMap = new HashMap<>(); for (FormFieldEntity formFieldEntity : formFieldsList) { if (formFieldEntity.getParentsubFormNum() == formdataNum && DBTYPE.containsKey(formFieldEntity.getFieldtype())) { if (!ConstMap.SUBFORM.equals(formFieldEntity.getFieldtype())) { String fieldName = ConstMap.FIELD + formFieldEntity.getColumnOrderNum(); sqlSelcetFields.append(" IFNULL("); sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append(" , '') AS "); sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append(","); // fieldMap.put(formFieldEntity.getFieldKey(), formFieldEntity.getFieldKey()); } else { sqlSelcetFields.append(formFieldEntity.getColumnOrderNum()); sqlSelcetFields.append(" AS "); sqlSelcetFields.append(formFieldEntity.getFieldKey()); sqlSelcetFields.append(","); } } } sqlSelcetFields.deleteCharAt(sqlSelcetFields.length() - 1); StringBuilder whereSql = new StringBuilder(" WHERE "); whereSql.append(" deleteFlg = '0'"); whereSql.append(" AND PARENTDATAROWNUM = "); whereSql.append(datarowNum); whereSql.append(" AND TENANTID = '"); whereSql.append(tenantID); whereSql.append("'"); sqlSelectMain.append(whereSql); String sql = String.format(sqlSelectMain.toString(), tenantID, formID,sqlSelcetFields.toString()); List> result = this.dataManagementMapper.queryFormdatas(sql); Collections.reverse(result); return result; } @SuppressWarnings("unchecked") @Override public List queryMasterValues(String tenantID, String defineCode, String eleCode) { return masterDataService.queryMasterValues(tenantID, defineCode, eleCode); } @Override public String getUserName(String formID, String tenantID, Integer dataRowNum) { return this.dataManagementMapper.getUserName(formID,tenantID,dataRowNum); } }