package org.springblade.flow.visual.onlinedev.common.formdata;

import java.io.File;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.springblade.bi.client.model.CubeDataModel;
import org.springblade.bi.client.model.DataSourceModel;
import org.springblade.bi.client.model.PageModel;
import org.springblade.core.tool.support.Kv;
import org.springblade.cube.enums.JoinTypeEnum;
import org.springblade.flow.base.ActionResult;
import org.springblade.flow.model.visiual.ColumnDataModel;
import org.springblade.flow.model.visiual.JnpfKeyConsts;
import org.springblade.flow.util.DateUtil;
import org.springblade.flow.util.JsonUtil;
import org.springblade.flow.util.StringUtil;
import org.springblade.flow.visual.base.entity.VisualdevEntity;
import org.springblade.flow.visual.base.service.VisualdevService;
import org.springblade.flow.visual.onlinedev.common.dynamic.DataSourceService;
import org.springblade.flow.visual.onlinedev.common.dynamic.DynamicDataService;
import org.springblade.flow.visual.onlinedev.common.enums.DsType;
import org.springblade.flow.visual.onlinedev.common.enums.SearchType;
import org.springblade.flow.visual.onlinedev.common.excel.ExcelFormParam.ExcelFieldIndex;
import org.springblade.flow.visual.onlinedev.common.excel.ExcelRead4MultipleTableListener;
import org.springblade.flow.visual.onlinedev.common.excel.ExcelRead4SingleTableListener;
import org.springblade.flow.visual.onlinedev.common.formdata.UpdateBean.UpdateColumn;
import org.springblade.flow.visual.onlinedev.common.relation.RelationBean;
import org.springblade.flow.visual.onlinedev.common.relation.RelationField;
import org.springblade.flow.visual.onlinedev.common.relation.RelationKey;
import org.springblade.flow.visual.onlinedev.common.relation.RelationUtil;
import org.springblade.flow.visual.onlinedev.model.PaginationModel;
import org.springblade.flow.visual.onlinedev.model.VisualdevModelDataCrForm;
import org.springblade.flow.visual.onlinedev.model.VisualdevModelDataUpForm;
import org.springblade.flow.visual.onlinedev.model.OnlineDevListModel.VisualColumnSearchVO;
import org.springblade.sqlfly.config.Column;
import org.springblade.sqlfly.opt.IFly;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.xiaoymin.knife4j.core.util.StrUtil;

import cn.hutool.core.collection.CollUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * 表单CRUD操作 - 有表操作
 * 
 * @author william
 * @date 2022年8月16日 上午11:05:13
 */
@Slf4j
@Service
public class FormDataWithTableService {
	@Autowired
	private VisualdevService visualdevService;

	@Autowired
	private DynamicDataService dynamicDataService;

	@Autowired
	private DataSourceService dataSourceService;
	
	/**
	 * 获取某个表单对应的主表的主键字段在数据集中的ID
	 * 
	 * @param modelId
	 * @return
	 */
	public Integer getMainTablePrimaryKeyId(String modelId) {
		VisualdevEntity formInfo = visualdevService.getInfo(modelId);
		DataSourceModel ds = dataSourceService.getReportDatasource(formInfo.getDbLinkId());
		Integer primaryKeyId = null;
		List<RelationBean> relationTree = dataSourceService.getRelationTree(formInfo.getTables(),
				formInfo.getDbLinkId());
		RelationBean parentRf = relationTree.get(0);
		IFly fly = IFly.get(ds);
		Column primaryColumn = fly.getPrimaryColumn(parentRf.getTable());
		if (primaryColumn != null) {
			String primaryKey = primaryColumn.getColumnName();
			RelationField pkRf = RelationUtil.getRelationFieldByFieldAndTable(primaryKey, parentRf.getTable(),
					parentRf.getFields());
			if (pkRf != null) {
				primaryKeyId = Integer.valueOf(pkRf.getFieldId());
			}
		}
		return primaryKeyId;
	}

	/**
	 * 分页查询表单数据，支持条件搜索
	 * 
	 * @param visualdevEntity
	 * @param paginationModel
	 * @return
	 */
	public ActionResult<PageModel<JSONObject>> list(VisualdevEntity formInfo, PaginationModel paginationModel) {
		DataSourceModel ds = dataSourceService.getReportDatasource(formInfo.getDbLinkId());
		if (ds == null) {
			return ActionResult.fail("数据集不存在，数据集ID是：" + formInfo.getDbLinkId());
		}

		List<RelationBean> relationTree = dataSourceService.getRelationTree(formInfo.getTables(),
				formInfo.getDbLinkId());
		String sql = buildListSql(relationTree, ds);
		log.info("list sql is " + sql);

//		if (StringUtil.isNotEmpty(paginationModel.getJson())) {
//			// 有条件查询
//		}
		CubeDataModel cubeData = dataSourceService.getCubeData(formInfo.getDbLinkId());
		DataSourceModel dataSource = dataSourceService.getReportDatasource(String.valueOf(cubeData.getId()));
		Page<JSONObject> page = new Page<JSONObject>(paginationModel.getCurrentPage(), paginationModel.getPageSize())
				.setSearchCount(true);
		PageModel<JSONObject> pageData = dynamicDataService.selectPage(page, dataSource, sql);

		Integer primaryKeyId = null;
		RelationBean parentRf = relationTree.get(0);
		IFly fly = IFly.get(ds);
		Column primaryColumn = fly.getPrimaryColumn(parentRf.getTable());
		if (primaryColumn != null) {
			String primaryKey = primaryColumn.getColumnName();
			RelationField pkRf = RelationUtil.getRelationFieldByFieldAndTable(primaryKey, parentRf.getTable(),
					parentRf.getFields());
			if (pkRf != null) {
				primaryKeyId = Integer.valueOf(pkRf.getFieldId());
			}
		}

		return ActionResult.success(pageData, Kv.create().set("idKey", primaryKeyId));
	}

	/**
	 * 构造完整的查询语句
	 * 
	 * @param relationTree
	 * @return
	 */
	private String buildListSql(List<RelationBean> relationTree, DataSourceModel ds) {
		StringBuffer sqlBuffer = new StringBuffer();
		String fieldSql = buildFieldSql(null, relationTree, ds).toString();
		// 去掉最后一个逗号
		fieldSql = fieldSql.substring(0, fieldSql.length() - 1);
		sqlBuffer.append("select " + fieldSql);
		sqlBuffer.append(" from  " + buildFromSql(null, null, relationTree));
		sqlBuffer.append(" where 1=1 ");
		return sqlBuffer.toString();
	}

	/**
	 * 构造所有字段的查询语句片段
	 * 
	 * @param sqlBuffer
	 * @param relationTree
	 * @return
	 */
	private StringBuffer buildFieldSql(StringBuffer sqlBuffer, List<RelationBean> relationTree, DataSourceModel ds) {
		if (sqlBuffer == null) {
			sqlBuffer = new StringBuffer();
		}
		for (int i = 0, size = relationTree.size(); i < size; i++) {
			RelationBean relation = relationTree.get(i);
			StringBuffer singleSqlBuffer = buildFieldSqlForSingle(relation, ds);
			if (singleSqlBuffer.length() > 0) {
				sqlBuffer.append(singleSqlBuffer);
				sqlBuffer.append(",");
			}

			if (CollUtil.isNotEmpty(relation.getChildren())) {
				// 递归继续拼接
				buildFieldSql(sqlBuffer, relation.getChildren(), ds);
			}
		}
		return sqlBuffer;
	}

	/**
	 * 构造单表字段的查询语句片段
	 * 
	 * @param relation
	 * @return
	 */
	private StringBuffer buildFieldSqlForSingle(RelationBean relation, DataSourceModel ds) {
		StringBuffer sqlBuffer = new StringBuffer();
		List<RelationField> fieldList = relation.getFields();
		for (int i = 0, size = fieldList.size(); i < size; i++) {
			RelationField field = fieldList.get(i);
			sqlBuffer.append(field.getTableAliasName() + "." + field.getField());
			sqlBuffer.append(" as \"" + field.getFieldId() + "\"");

			if (i < size - 1) {
				sqlBuffer.append(",");
			}
		}
		return sqlBuffer;
	}

	/**
	 * 构造from后面的的查询语句片段
	 * 
	 * @param sqlBuffer
	 * @param parent
	 * @param relationTree
	 * @return
	 */
	private StringBuffer buildFromSql(StringBuffer sqlBuffer, RelationBean parent, List<RelationBean> relationTree) {
		if (sqlBuffer == null) {
			sqlBuffer = new StringBuffer();
		}
		for (int i = 0, size = relationTree.size(); i < size; i++) {
			RelationBean relation = relationTree.get(i);
			if (CollUtil.isEmpty(relation.getKeys())) {
				// 当前是主表
				sqlBuffer.append(relation.getTable() + " " + relation.getTableName() + " ");
			} else {
				// 当前是子表
				JoinTypeEnum joinType = JoinTypeEnum.valueOf(relation.getJoinType());
				String joinSql = joinType.getSql();
				sqlBuffer.append(joinSql + relation.getTable() + " " + relation.getTableName() + " on ");

				List<RelationKey> keys = relation.getKeys();
				for (RelationKey key : keys) {
					sqlBuffer.append(parent.getTableName() + "." + key.getSourceKey());
					sqlBuffer.append(" = ");
					sqlBuffer.append(relation.getTableName() + "." + key.getTargetKey());
					sqlBuffer.append(" ");
				}
			}

			if (CollUtil.isNotEmpty(relation.getChildren())) {
				// 递归继续拼接
				buildFromSql(sqlBuffer, relation, relation.getChildren());
			}
		}
		return sqlBuffer;
	}

	/**
	 * 构造查询条件
	 * 
	 * @param formInfo
	 * @param paginationModel
	 * @return
	 */
	private String buildWhereSql(VisualdevEntity formInfo, PaginationModel paginationModel) {
		if (StringUtil.isBlank(paginationModel.getJson())) {
			// 没有查询条件
			return "";
		}

		ColumnDataModel colData = JsonUtil.getJsonToBean(formInfo.getColumnData(), ColumnDataModel.class);
		List<VisualColumnSearchVO> searchVOList = JsonUtil.getJsonToList(colData.getSearchList(),
				VisualColumnSearchVO.class);
		Map<String, Object> keyJsonMap = JsonUtil.stringToMap(paginationModel.getJson());
		searchVOList = searchVOList.stream().map(searchVO -> {
			searchVO.setValue(keyJsonMap.get(searchVO.getVModel()));
			return searchVO;
		}).filter(vo -> vo.getValue() != null).collect(Collectors.toList());

		// 左查询
		boolean b = false;
		if (colData.getTreeRelation() != null) {
			b = keyJsonMap.keySet().stream()
					.anyMatch(t -> t.equalsIgnoreCase(String.valueOf(colData.getTreeRelation())));
		}
		if (b && keyJsonMap.size() > searchVOList.size()) {
			String relation = String.valueOf(colData.getTreeRelation());
			VisualColumnSearchVO vo = new VisualColumnSearchVO();
			vo.setSearchType(1);
			vo.setVModel(relation);
			vo.setValue(keyJsonMap.get(relation));
			searchVOList.add(vo);
		}

		if (CollUtil.isEmpty(searchVOList)) {
			return "";
		}

		StringBuffer sqlBuffer = new StringBuffer();
		for (int i = 0, size = searchVOList.size(); i < size; i++) {
			VisualColumnSearchVO vo = searchVOList.get(i);
			// 多选框默认添加多选属性
			if (vo.getConfig().getJnpfKey().equals(JnpfKeyConsts.CHECKBOX)) {
				vo.setMultiple(true);
			}
			SearchType type = SearchType.reslove(vo.getSearchType());
			if (type == SearchType.EQUAL) {

			} else if (type == SearchType.LIKE) {

			} else if (type == SearchType.RANGE) {

			}
		}

		return sqlBuffer.toString();
	}

	/**
	 * 多表批量导入数据
	 * 
	 * @param cubeData 数据集信息
	 * @param withHeader 是否带表头
	 * @param formInfo 表单信息
	 * @param indexList 字段和excel索引映射关系
	 * @param excelFile excel文件
	 * @return
	 * @throws SQLException 
	 */
	public ActionResult<String> batchAddForMultipleTable(CubeDataModel cubeData, boolean withHeader,
			VisualdevEntity formInfo, List<ExcelFieldIndex> indexList, File excelFile) throws SQLException {
		DataSourceModel ds = dataSourceService.getReportDatasource(String.valueOf(cubeData.getId()));
		ExcelRead4MultipleTableListener listener = new ExcelRead4MultipleTableListener(this, dataSourceService,
				cubeData, dynamicDataService, ds, formInfo, indexList);
		EasyExcel.read(excelFile, listener).sheet().headRowNumber(withHeader ? 1 : 0).doRead();
		return ActionResult.success("共导入" + listener.getTableTotal() + "张表，" + listener.getDataTotal() + "条数据");
	}

	/**
	 * 单表批量导入数据
	 * 
	 * @param cubeData 数据集信息
	 * @param withHeader 是否带表头
	 * @param formInfo 表单信息
	 * @param indexList 字段和excel索引映射关系
	 * @param excelFile excel文件
	 * @return
	 * @throws SQLException 
	 */
	public ActionResult<String> batchAddForSingleTable(CubeDataModel cubeData, boolean withHeader,
			VisualdevEntity formInfo, List<ExcelFieldIndex> indexList, File excelFile) throws SQLException {
		DataSourceModel ds = dataSourceService.getReportDatasource(String.valueOf(cubeData.getId()));
		ExcelRead4SingleTableListener listener = new ExcelRead4SingleTableListener(this, dataSourceService, cubeData,
				dynamicDataService, ds, formInfo, indexList);
		EasyExcel.read(excelFile, listener).sheet().headRowNumber(withHeader ? 1 : 0).doRead();
		return ActionResult.success("共导入1张表" + listener.getTotal() + "条数据");
	}

	/**
	 * 提交表单数据入库
	 * 
	 * @param modelId  表单ID
	 * @param visualdevModelDataCrForm 前端提交的数据参数
	 * @return
	 */
	public ActionResult<String> add(String modelId, VisualdevModelDataCrForm visualdevModelDataCrForm) {
		VisualdevEntity formInfo = visualdevService.getInfo(modelId);
		if (formInfo == null) {
			return ActionResult.fail("表单不存在");
		}

		if (StringUtil.isBlank(formInfo.getDbLinkId())) {
			return ActionResult.fail("缺少数据集ID");
		}

		CubeDataModel cubeData = dataSourceService.getCubeData(formInfo.getDbLinkId());
		DataSourceModel ds = dataSourceService.getReportDatasource(cubeData.getId() + "");
		List<RelationBean> relationTree = dataSourceService.getRelationTree(formInfo.getTables(),
				formInfo.getDbLinkId());
		Map<String, Object> formDataMap = JsonUtil.stringToMap(visualdevModelDataCrForm.getData());
//		log.info("formDataMap is " + JSON.toJSONString(formDataMap, true));
		recursionInsertData(cubeData, ds, relationTree, formDataMap, null);
		return ActionResult.success("操作成功");
	}

	/**
	 * 递归插入数据
	 * 
	 * @param cubeData 数据集
	 * @param ds 数据库连接对象
	 * @param relationTree 表关联及字段信息
	 * @param formDataMap 前端提交的所有数据集合
	 * @param relationDataMap 父表关联字段数据
	 */
	private void recursionInsertData(CubeDataModel cubeData, DataSourceModel ds, List<RelationBean> relationTree,
			Map<String, Object> formDataMap, HashMap<String, HashMap<String, Object>> relationDataMap) {
		try {
			for (RelationBean relation : relationTree) {
				String table = relation.getTable();// 表名
				IFly fly = IFly.get(ds);
				Column primaryColumn = fly.getPrimaryColumn(table);
				boolean hasPrimaryKey = primaryColumn != null;
				String tableKey = hasPrimaryKey ? primaryColumn.getColumnName() : null;

				HashMap<String, Object> tableData = new HashMap<>();// 当前表要入库的字段和数据
				List<RelationField> fieldList = relation.getFields();
				StringBuffer sqlBuffer = new StringBuffer("insert into " + table + " ");
				StringBuffer fieldSql = new StringBuffer("(");
				StringBuffer valueSql = new StringBuffer("values(");
				for (int i = 0, size = fieldList.size(); i < size; i++) {
					RelationField field = fieldList.get(i);

					String value = null;
					String fieldId = String.valueOf(field.getFieldId());
					String fieldName = field.getField();
					if (formDataMap.containsKey(fieldId)) {
						// 从前端输入内容取值
						Object data = formDataMap.get(fieldId);
						value = data == null || data.toString().equalsIgnoreCase("[]") ? null : data.toString();
					}

					// 某个字段是否存在关联字段，如果存在,则从父表的关联字段取值
					if (CollUtil.isNotEmpty(relationDataMap) && relationDataMap.containsKey(table)) {
						HashMap<String, Object> map = relationDataMap.get(table);
						if (CollUtil.isNotEmpty(map) && map.containsKey(fieldName)) {
							Object tempValue = map.get(fieldName);
							if (tempValue != null) {
								// 该字段为关联字段，数据应该来源于父表
								String valueFromParent = tempValue.toString();
								value = valueFromParent;
							}
						}
					}

					if (StrUtil.isBlank(tableKey) || !fieldName.equalsIgnoreCase(tableKey)) {
						// 没有主键或者输入的字段不是主键则拼接，否则如果是主键则预生成
						fieldSql.append(field.getField());
						tableData.put(fieldName, value);

						if (value != null) {
							String dataType = field.getDataType();
							if (dataType.equalsIgnoreCase("varchar")) {
								value = "'" + value + "'";
							} else if (dataType.equalsIgnoreCase("datetime")) {
								// 时间日期类型 前端统一传递的都是时间戳
								long timestamp = Long.decode(value);
								value = DateUtil.daFormat(timestamp);
								value = "'" + value + "'";
								DsType dsType = DsType.reslove(ds.getDsType());
								if (dsType == DsType.Oracle) {
									value = "to_date(" + value + ",'yyyy-mm-dd HH24:mi:ss')";
								}
							}
						}
						valueSql.append(value);

						fieldSql.append(",");
						valueSql.append(",");
					}
				}

				// 需要删除sql语句中 多余的逗号
				String fsql = fieldSql.toString();
				fieldSql = new StringBuffer();
				fieldSql.append(fsql.substring(0, fsql.toString().length() - 1));

				String vsql = valueSql.toString();
				valueSql = new StringBuffer();
				valueSql.append(vsql.toString().substring(0, vsql.toString().length() - 1));

				// 当前表有主键
				if (hasPrimaryKey && !primaryColumn.getJavaType().equalsIgnoreCase("Integer")) {
					// 主键为非自增整型，则预生成主键并入库
					long tableKeyValue = IdWorker.getId();
					fieldSql.append("," + tableKey);
					valueSql.append("," + tableKeyValue);
					tableData.put(tableKey, tableKeyValue);
				} else {
					// 主键为自增则忽略
				}

				fieldSql.append(") ");
				valueSql.append(") ");

				sqlBuffer.append(fieldSql);
				sqlBuffer.append(valueSql);

				String sql = sqlBuffer.toString();
				log.info("insert sql is : " + sql);
				DataSourceModel dataSource = dataSourceService.getReportDatasource(String.valueOf(cubeData.getId()));
				dynamicDataService.executeSql(dataSource, sql);

				if (CollUtil.isNotEmpty(relation.getChildren())) {
					// 插入子表数据
					HashMap<String, HashMap<String, Object>> temp = dataSourceService
							.getParentTableRelationData(cubeData, table, tableData);
					recursionInsertData(cubeData, ds, relation.getChildren(), formDataMap, temp);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除表单数据
	 * 
	 * @param formInfo
	 * @param idList
	 * @return
	 */
	public ActionResult<String> delete(VisualdevEntity formInfo, List<String> idList) {
		String cubeId = formInfo.getDbLinkId();
		CubeDataModel cubeData = dataSourceService.getCubeData(cubeId);
		DataSourceModel ds = dataSourceService.getReportDatasource(cubeId);
		DsType dbType = DsType.reslove(ds.getDsType());
		if (dbType == DsType.Clickhouse) {
			return ActionResult.fail("暂不支持Clickhouse数据的删除操作");
		}

		List<RelationBean> relationTree = dataSourceService.getRelationTree(formInfo.getTables(),
				formInfo.getDbLinkId());
		String mainTable = relationTree.get(0).getTable();
		IFly fly = IFly.get(ds);
		Column primaryColumn = fly.getPrimaryColumn(mainTable);
		if (primaryColumn == null) {
			return ActionResult.fail("删除失败，该表不存在主键，表名是：" + mainTable);
		}

		boolean hasLinkTable = RelationUtil.hasLinkTable(cubeData);
		if (!hasLinkTable) {
			// 单表删除
			return deleteForSingleTable(mainTable, ds, idList);
		}

		// 多表删除
		return deleteForMultiTable(ds, relationTree, idList, primaryColumn, relationTree.get(0));
	}

	/**
	 * 单表批量删除
	 * 
	 * @param formInfo
	 * @param idList
	 * @return
	 */
	private ActionResult<String> deleteForSingleTable(String tableName, DataSourceModel ds, List<String> idList) {
		try {
			IFly fly = IFly.get(ds);
			Column primaryColumn = fly.getPrimaryColumn(tableName);
			StringBuffer sqlBuffer = new StringBuffer();
			String idsStr = idList.stream().collect(Collectors.joining(","));
			if (primaryColumn.getJavaType().equalsIgnoreCase("String")) {
				// 主键为字符串类型
				idsStr = idList.stream().collect(Collectors.joining("\", \""));
				idsStr = "\"" + idsStr + "\"";
			}
			sqlBuffer.append("delete from " + tableName + " ");
			sqlBuffer.append("where " + primaryColumn.getColumnName() + " ");
			sqlBuffer.append("in (" + idsStr + ")");
			String sql = sqlBuffer.toString();
			log.info("delete sql is : " + sql);
			dynamicDataService.executeSql(ds, sql);
			return ActionResult.success("删除成功");
		} catch (Exception e) {
		}
		return ActionResult.fail("删除失败");
	}

	/**
	 * 多表删除数据，支持批量操作
	 * 
	 * @param tableName
	 * @param ds
	 * @param idList
	 * @return
	 */
	private ActionResult<String> deleteForMultiTable(DataSourceModel ds, List<RelationBean> relationTree,
			List<String> idList, Column primaryColumn, RelationBean mainTableBean) {
		try {
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append("delete " + mainTableBean.getTableName() + ",");
			StringBuffer afterDelete = new StringBuffer();
			StringBuffer joinBuffer = new StringBuffer();
			buildDeleteStringBuffer(mainTableBean.getTableName(), relationTree, afterDelete, joinBuffer);

			String deleteSql = afterDelete.toString();
			sqlBuffer.append(deleteSql.substring(0, deleteSql.length() - 1));

			StringBuffer fromBuffer = new StringBuffer();
			fromBuffer.append(" from " + mainTableBean.getTable());
			fromBuffer.append(" " + mainTableBean.getTableName() + " ");

			sqlBuffer.append(fromBuffer);
			sqlBuffer.append(joinBuffer);

			StringBuffer whereBuffer = new StringBuffer();
			whereBuffer.append(" where " + mainTableBean.getTableName() + "." + primaryColumn.getColumnName());
			String idsStr = idList.stream().collect(Collectors.joining(","));
			if (primaryColumn.getJavaType().equalsIgnoreCase("String")) {
				// 主键为字符串类型
				idsStr = idList.stream().collect(Collectors.joining("\", \""));
				idsStr = "\"" + idsStr + "\"";
			}
			whereBuffer.append(" in (" + idsStr + ")");
			sqlBuffer.append(whereBuffer);
			String sql = sqlBuffer.toString();
			log.info("batch delete sql is : " + sql);
			dynamicDataService.executeSql(ds, sql);
			return ActionResult.fail("批量删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ActionResult.fail("批量删除失败");
	}

	private void buildDeleteStringBuffer(String parentTableAlias, List<RelationBean> relationTree,
			StringBuffer afterDelete, StringBuffer joinBuffer) {

		for (RelationBean relation : relationTree) {
			if (relation.getRelationTable().equalsIgnoreCase(RelationUtil.ROOT_TABLE_PARENT)) {
				// 当前是主表
				buildDeleteStringBuffer(relation.getTableName(), relation.getChildren(), afterDelete, joinBuffer);
				return;
			}
			afterDelete.append(relation.getTableName() + ",");
			joinBuffer.append("left join " + relation.getTable() + " " + relation.getTableName() + " ");

			StringBuffer onBuffer = new StringBuffer();
			onBuffer.append("on ");

			List<RelationKey> relationKeyList = relation.getKeys();
			for (int i = 0, size = relationKeyList.size(); i < size; i++) {
				RelationKey rk = relationKeyList.get(i);
				String sourceKey = rk.getSourceKey();
				String targetKey = rk.getTargetKey();
				if (i != 0) {
					onBuffer.append(" and ");
				}
				onBuffer.append(parentTableAlias + "." + sourceKey);
				onBuffer.append(" = ");
				onBuffer.append(relation.getTableName() + "." + targetKey);
				onBuffer.append(" ");
			}
			joinBuffer.append(onBuffer);

			if (CollUtil.isNotEmpty(relation.getChildren())) {
				buildDeleteStringBuffer(relation.getTableName(), relation.getChildren(), afterDelete, joinBuffer);
			}
		}
	}

	/**
	 * 更新数据
	 * 
	 * @param formInfo 表单信息
	 * @param id 数据主键
	 * @param visualdevModelDataUpForm 新数据
	 * @return
	 */
	public ActionResult<String> update(VisualdevEntity formInfo, String id,
			VisualdevModelDataUpForm visualdevModelDataUpForm) {
		String cubeId = formInfo.getDbLinkId();
		DataSourceModel ds = dataSourceService.getReportDatasource(cubeId);
		DsType dbType = DsType.reslove(ds.getDsType());
		if (dbType == DsType.Clickhouse) {
			return ActionResult.fail("暂不支持Clickhouse数据更新");
		}

		List<RelationBean> relationTree = dataSourceService.getRelationTree(formInfo.getTables(),
				formInfo.getDbLinkId());
		String mainTable = relationTree.get(0).getTable();
		IFly fly = IFly.get(ds);
		Column primaryColumn = fly.getPrimaryColumn(mainTable);
		if (primaryColumn == null) {
			return ActionResult.fail("更新失败，该表不存在主键，表名是：" + mainTable);
		}
		
		Map<String, Object> formDataMap = JsonUtil.stringToMap(visualdevModelDataUpForm.getData());
		List<UpdateBean> updateList = new ArrayList<>();
		DsType dsType = DsType.reslove(ds.getDsType());
		recursionBuildUpdate(ds, dsType, id, formDataMap, null, null, relationTree, updateList);
		updateList.forEach(up -> {
			String sql = up.getUpdateSql();
			log.info("update sql is : " + sql);
			dynamicDataService.executeSql(ds, sql);
		});
		return ActionResult.success("更新成功");
	}

	/**
	 * 递归构造更新对象
	 * 
	 * @param ds 数据库连接
	 * @param dsType 数据库类型
	 * @param mainTableId 主表的主键
	 * @param formDataMap 前端提交的要更新的数据
	 * @param preTableOldDataList 上个表的旧数据
	 * @param preTableNewDataList 上个表的新数据
	 * @param relationTree 数据集关联关系的树形结构
	 * @param updateList 要构造的更新条件结构列表
	 */
	private void recursionBuildUpdate(DataSourceModel ds, DsType dsType, String mainTableId,
			Map<String, Object> formDataMap, List<Map<String, Object>> preTableOldDataList,
			List<UpdateColumn> preTableNewDataList, List<RelationBean> relationTree, List<UpdateBean> updateList) {
		for (RelationBean relation : relationTree) {
			UpdateBean updateBean = new UpdateBean(relation.getTable(), dsType);
			updateList.add(updateBean);

			// 当前表的新的数据
			List<UpdateColumn> newColumnList = new ArrayList<>();
			List<RelationField> fieldList = relation.getFields();
			for (int i = 0, size = fieldList.size(); i < size; i++) {
				// 构造当前表要更新的数据
				RelationField field = fieldList.get(i);
				String column = field.getField();
				String dataType = field.getDataType();
				Integer fieldId = field.getFieldId();
				Object value = null;
				// 前端是否输入的包含当前字段
				boolean isFromInput = formDataMap.containsKey(String.valueOf(fieldId));
				if (isFromInput) {
					value = formDataMap.get(String.valueOf(fieldId));
				}
				// 当前字段是否为关联字段，如果是关联字段则数据来自父表，并且忽略前端直接输入的内容
				boolean isRelationColumn = false;

				// 判断父表新数据
				if (CollUtil.isNotEmpty(preTableNewDataList)) {
					// 某个字段是否存在关联字段，如果存在,则从父表新数据中取值
					List<RelationKey> relationKeyList = relation.getKeys();
					for (RelationKey rk : relationKeyList) {
						String sourceKey = rk.getSourceKey();
						String targetKey = rk.getTargetKey();
						if (column.equalsIgnoreCase(targetKey)) {
							// 关联字段，值应该来源于父表
							isRelationColumn = true;
							for (UpdateColumn preNewData : preTableNewDataList) {
								if (preNewData.getColumn().equalsIgnoreCase(sourceKey)) {
									value = preNewData.getValue();
									break;
								}
							}
						}
					}
				}

				if (isFromInput || isRelationColumn) {
					// 该字段的值来自前端或者来自父表的关联字段，则更新，否则不更新
					UpdateColumn updateColumn = new UpdateBean.UpdateColumn(column, value, dataType);
					newColumnList.add(updateColumn);
					updateBean.addSetColumn(updateColumn);
				}
			}

			// 当前表的旧数据
			List<Map<String, Object>> curTableOldDataList = new ArrayList<>();
			IFly fly = IFly.get(ds);
			if (StrUtil.isNotBlank(mainTableId)) {
				// 首次循环，说明是主表
				Column primaryColumn = fly.getPrimaryColumn(relation.getTable());
				StringBuffer selectSql = new StringBuffer();
				selectSql.append("select * from " + relation.getTable());
				selectSql.append(" where " + primaryColumn.getColumnName() + "=?");
				Map<String, Object> rowData = fly.getMap(selectSql.toString(), mainTableId);
				curTableOldDataList.add(rowData);

				String dataType = null;
				if (primaryColumn.getJavaType().equalsIgnoreCase("String")) {
					dataType = "varchar";
				} else if (primaryColumn.getJavaType().equalsIgnoreCase("Date")) {
					dataType = "datetime";
				} else {
					dataType = "decimal";
				}
				// 当前表的更新条件字段信息
				UpdateColumn whereColumn = new UpdateColumn(primaryColumn.getColumnName(), mainTableId, dataType);
				updateBean.addWhereColumn(whereColumn);
			} else {
				// 不是主表，则根据条件查询旧数据
				if (CollUtil.isNotEmpty(preTableOldDataList)) {
					// 条件来源于上个表的关联字段数据
					List<String> whereColumnList = new ArrayList<>();
					List<Object> whereObjectList = new ArrayList<>();
					List<RelationKey> relationKeyList = relation.getKeys();
					StringBuffer selectSql = new StringBuffer();
					selectSql.append("select * from " + relation.getTable());
					selectSql.append(" where 1=1 ");
					for (RelationKey rk : relationKeyList) {
						String targetKey = rk.getTargetKey();
						selectSql.append(" and " + targetKey + " = ?");
						String sourceKey = rk.getSourceKey();
						for (Map<String, Object> map : preTableOldDataList) {
							Iterator<String> iterator = map.keySet().iterator();
							while (iterator.hasNext()) {
								String k = iterator.next();
								if (k.equalsIgnoreCase(sourceKey)) {
									Object v = map.get(k);
									whereObjectList.add(v);
									whereColumnList.add(targetKey);
								}
							}
						}
					}

					Object[] objs = whereObjectList.toArray(new Object[whereObjectList.size()]);
					List<Map<String, Object>> rowDataList = fly.getMapList(selectSql.toString(), objs);
					if (CollUtil.isNotEmpty(rowDataList)) {
						curTableOldDataList.addAll(rowDataList);
					}

					for (int n = 0, size = whereColumnList.size(); n < size; n++) {
						String columnName = whereColumnList.get(n);
						Object columnValue = whereObjectList.get(n);

						RelationField tempRf = RelationUtil.findByColumnName(columnName, fieldList);
						String dataType = tempRf != null ? tempRf.getDataType() : "varchar";
						UpdateColumn whereColumn = new UpdateColumn(columnName, columnValue, dataType);
						updateBean.addWhereColumn(whereColumn);
					}
				}
			}

			if (CollUtil.isNotEmpty(relation.getChildren())) {
				// 有子表，则继续递归
				recursionBuildUpdate(ds, dsType, null, formDataMap, curTableOldDataList, newColumnList,
						relation.getChildren(), updateList);
			}
		}
	}

//	private ActionResult<String> updateForSingleTable(Column primaryColumn, String tableName, DataSourceModel ds,
//			Map<String, Object> formDataMap, String id) {
//		try {
//			DsType dsType = DsType.reslove(ds.getDsType());
//			StringBuffer sqlBuffer = new StringBuffer();
//			sqlBuffer.append("update " + tableName + " ");
//
//			StringBuffer valueSql = new StringBuffer("set ");
//			formDataMap.forEach((key, object) -> {
//				String value = object == null || object.toString().equalsIgnoreCase("[]") ? null : object.toString();
//				CubeTableColumnModel field = dataSourceService.getFieldInfo(Integer.parseInt(key));
//				String fieldName = field.getColumnName();
//
//				if (value != null) {
//					ViewColumnTypeEnum dataType = ViewColumnTypeEnum.resolve(field.getViewDataType());
//					if (dataType == ViewColumnTypeEnum.STRING) {
//						value = "'" + value + "'";
//					} else if (dataType == ViewColumnTypeEnum.DATETIME) {
//						// 时间日期类型 前端统一传递的都是时间戳
//						long timestamp = Long.decode(value);
//						value = DateUtil.daFormat(timestamp);
//						value = "'" + value + "'";
//						if (dsType == DsType.Oracle) {
//							value = "to_date(" + value + ",'yyyy-mm-dd HH24:mi:ss')";
//						}
//					}
//				}
//				if (!primaryColumn.getColumnName().equalsIgnoreCase(fieldName)) {
//					// 不是主键
//					valueSql.append(fieldName + "=" + value);
//					valueSql.append(",");
//				}
//			});
//
//			String vsql = valueSql.toString();
//			vsql = vsql.substring(0, vsql.length() - 1);
//			sqlBuffer.append(vsql);
//			sqlBuffer.append(" where " + primaryColumn.getColumnName() + "=");
//			String symbol = "";
//			if (primaryColumn.getJavaType().equalsIgnoreCase("String")) {
//				// 主键是字符串
//				symbol = "'";
//			}
//			sqlBuffer.append(symbol + id + symbol + " ");
//
//			String sql = sqlBuffer.toString();
//			log.info("update sql of single table is " + sql);
//			dynamicDataService.executeSql(ds, sql);
//			return ActionResult.success("更新成功");
//		} catch (Exception e) {
//		}
//		return ActionResult.fail("更新失败");
//	}

	/**
	 * 获取某个表的主键字段
	 * 
	 * @param link
	 * @param tableName
	 * @return
	 */
	public String getKey(DataSourceModel ds, String tableName) {
		IFly fly = IFly.get(ds);
		Column primaryColumn = fly.getPrimaryColumn(tableName);
		return primaryColumn != null ? primaryColumn.getColumnName() : null;
	}

}
