dzhh-backend/.svn/pristine/b3/b37ef100baaca32d74f591b79b4d285587afb367.svn-base

package org.jeecg.modules.online.cgreport.controller;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.AutoLog;
import org.jeecg.common.system.base.controller.JeecgController;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.modules.online.cgreport.entity.ChartsData;
import org.jeecg.modules.online.cgreport.entity.CommonConstants;
import org.jeecg.modules.online.cgreport.entity.DiagramConfiguration;
import org.jeecg.modules.online.cgreport.entity.DiagramFieldConfiguration;
import org.jeecg.modules.online.cgreport.service.IDiagramConfigurationService;
import org.jeecg.modules.online.cgreport.service.IDiagramFieldConfigurationService;
import org.jeecg.modules.system.util.OnlineUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.googlecode.aviator.AviatorEvaluator;
import com.googlecode.aviator.Expression;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;

/**
 * @Description: 图表配置
 */
@Slf4j
@Api(tags = "图表配置")
@RestController
@RequestMapping("/diagram/diagramConfiguration")
public class DiagramConfigurationController
		extends JeecgController<DiagramConfiguration, IDiagramConfigurationService> {

	@Autowired
	private IDiagramConfigurationService diagramConfigurationService;
	@Autowired
	private IDiagramFieldConfigurationService diagramFieldConfigurationService;

	/**
	 * 分页列表查询
	 *
	 * @param diagramConfiguration
	 * @param pageNo
	 * @param pageSize
	 * @param req
	 * @return
	 */
	@AutoLog(value = "图表配置-分页列表查询")
	@ApiOperation(value = "图表配置-分页列表查询", notes = "图表配置-分页列表查询")
	@GetMapping(value = "/list")
	public Result<?> queryPageList(DiagramConfiguration diagramConfiguration,
			@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
			@RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize, HttpServletRequest req) {
		QueryWrapper<DiagramConfiguration> queryWrapper = QueryGenerator.initQueryWrapper(diagramConfiguration, req.getParameterMap());
		Page<DiagramConfiguration> page = new Page<DiagramConfiguration>(pageNo, pageSize);
		IPage<DiagramConfiguration> pageList = diagramConfigurationService.page(page, queryWrapper);
		List<DiagramConfiguration> records = pageList.getRecords();
		if (records != null && !records.isEmpty()) {
			for (DiagramConfiguration record : records) {
				QueryWrapper<DiagramFieldConfiguration> qw = new QueryWrapper<DiagramFieldConfiguration>();
				qw.eq("DIAGRAM_CODE", record.getCode()).orderByAsc("ORDER_NUM");
				List<DiagramFieldConfiguration> diagramFieldConfigurationList = this.diagramFieldConfigurationService
						.list(qw);
				record.setDiagramFieldConfigurationList(diagramFieldConfigurationList);
			}
		}
		return Result.OK(pageList);
	}

	/**
	 * 添加
	 *
	 * @param diagramConfiguration
	 * @return
	 */
	@AutoLog(value = "图表配置-添加")
	@ApiOperation(value = "图表配置-添加", notes = "图表配置-添加")
	@PostMapping(value = "/add")
	public Result<?> add(@RequestBody DiagramConfiguration diagramConfiguration) {
		// 校验code是否重复
		Result<?> result = this.duplicateCheck(diagramConfiguration.getCode());
		if (result != null) {
			return result;
		}
		// 保存主表信息
		try {
			this.diagramConfigurationService.add(diagramConfiguration);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(CommonConstants.MSG_ERROR_OPERATE, e);
			return Result.error(e.getMessage());
		}
		// 渲染客户端
		return Result.OK(CommonConstants.MSG_SUCCESS_ADD, null);
	}

	/**
	 * 编辑
	 *
	 * @param diagramConfiguration
	 * @return
	 */
	@AutoLog(value = "图表配置-编辑")
	@ApiOperation(value = "图表配置-编辑", notes = "图表配置-编辑")
	@PostMapping(value = "/edit")
	public Result<?> edit(@RequestBody DiagramConfiguration diagramConfiguration) {
		try {
			this.diagramConfigurationService.edit(diagramConfiguration);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(CommonConstants.MSG_ERROR_OPERATE, e);
			return Result.error(e.getMessage());
		}
		return Result.OK(CommonConstants.MSG_SUCCESS_EDIT, null);
	}

	/**
	 * 通过id删除
	 *
	 * @param id
	 * @return
	 */
	@AutoLog(value = "图表配置-通过id删除")
	@ApiOperation(value = "图表配置-通过id删除", notes = "图表配置-通过id删除")
	@PostMapping(value = "/delete")
	public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
		try {
			this.diagramConfigurationService.deleteById(id);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(CommonConstants.MSG_ERROR_OPERATE, e);
			return Result.error(e.getMessage());
		}
		return Result.OK(CommonConstants.MSG_SUCCESS_DELTET, null);
	}

	/**
	 * 批量删除
	 *
	 * @param ids
	 * @return
	 */
	@AutoLog(value = "图表配置-批量删除")
	@ApiOperation(value = "图表配置-批量删除", notes = "图表配置-批量删除")
	@PostMapping(value = "/deleteBatch")
	public Result<?> deleteBatch(@RequestParam(name = "ids", required = true) String ids) {
		try {
			this.diagramConfigurationService.deleteBatch(ids);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(CommonConstants.MSG_ERROR_OPERATE, e);
			return Result.error(e.getMessage());
		}
		return Result.OK(CommonConstants.MSG_SUCCESS_DELTET_BATCH, null);
	}

	/**
	 * 通过id查询
	 *
	 * @param id
	 * @return
	 */
	@AutoLog(value = "图表配置-通过id查询")
	@ApiOperation(value = "图表配置-通过id查询", notes = "图表配置-通过id查询")
	@GetMapping(value = "/queryById")
	public Result<?> queryById(@RequestParam(name = "id", required = true) String id) {
		DiagramConfiguration diagramConfiguration = diagramConfigurationService.getById(id);
		if (diagramConfiguration == null) {
			return Result.error(CommonConstants.MSG_ERROR_NO_DATA_FOUND + "[ " + id + " ]");
		}
		return Result.OK(diagramConfiguration);
	}

	/**
	 * 导出excel
	 *
	 * @param request
	 * @param diagramConfiguration
	 */
	@RequestMapping(value = "/exportXls")
	public ModelAndView exportXls(HttpServletRequest request, DiagramConfiguration diagramConfiguration) {
		return super.exportXls(request, diagramConfiguration, DiagramConfiguration.class, "图表配置");
	}

	/**
	 * 通过excel导入数据
	 *
	 * @param request
	 * @param response
	 * @return
	 */

	@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
	public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
		return super.importExcel(request, response, DiagramConfiguration.class);
	}

	/**
	 * 获取图表数据 【注意】当 dataType="sql" 时，遇到 插入、删除、更新（查询数据报错） 【目的】阻止除RUD语句执行，破坏数据
	 * 
	 * @param code
	 * @return
	 */
	// @AutoLog(value = "图表配置-获取图表数据")
	@ApiOperation(value="图表配置-获取图表数据", notes="图表配置-获取图表数据")
	@RequestMapping(value = "/getChartsData", method = RequestMethod.GET)
	@Transactional(readOnly = true)
	public Result<?> getChartsData(@RequestParam(name = "code", required = true) String code,
								   @RequestParam(name = "params", required = false) String params) {
		// 查询主表信息
		QueryWrapper<DiagramConfiguration> diagramConfigurationQueryWrapper = new QueryWrapper<>();
		diagramConfigurationQueryWrapper.eq("code", code);
		DiagramConfiguration diagramConfiguration = this.diagramConfigurationService
				.getOne(diagramConfigurationQueryWrapper);
		if (diagramConfiguration == null) {
			return Result.error(CommonConstants.MSG_ERROR_NO_DATA_FOUND + "[ " + code + " ]");
		}

		// 根据sql查询数据
		List dataList = Collections.emptyList();
		if (CommonConstants.DATA_TYPE_SQL.equals(diagramConfiguration.getDataType())) {

			String cgrSql = diagramConfiguration.getCgrSql();

			// 向SQL拼装查询条件
			cgrSql = this.assemblySql(cgrSql, params);

			dataList = this.diagramConfigurationService.selectBySql(cgrSql);
		}

		// 获取子表信息
		QueryWrapper<DiagramFieldConfiguration> diagramFieldConfigurationQueryWrapper = new QueryWrapper<>();
		diagramFieldConfigurationQueryWrapper.eq("DIAGRAM_CODE", code);
		diagramFieldConfigurationQueryWrapper.orderByAsc("order_num");
		List<DiagramFieldConfiguration> diagramFieldConfigurationList = this.diagramFieldConfigurationService
				.list(diagramFieldConfigurationQueryWrapper);
		Map<String, Expression> expressions = new HashMap<>();
		Map<String, String> aggregates = new HashMap<>();
		
		Map<Object,Map<String, Object>> aggregateDataMap = new HashMap<Object,Map<String, Object>>();
		
		String xField = diagramConfiguration.getXaxisField();
		String[] groupFields = diagramConfiguration.getGroupField().split(",");
		
		for(DiagramFieldConfiguration dfc : diagramFieldConfigurationList){
			if(StringUtils.isNotEmpty(dfc.getFunc())){
				String expression = dfc.getFunc();
				Expression compiledExp = AviatorEvaluator.compile(expression);
				expressions.put(dfc.getFieldName(), compiledExp);
			}
			if(StringUtils.isNotEmpty(dfc.getAggregateType())){
				aggregates.put(dfc.getFieldName(), dfc.getAggregateType());
			}
			if(ArrayUtils.contains(groupFields, dfc.getFieldName())){
				aggregates.put(dfc.getFieldName(), dfc.getAggregateType());
			}
		}
		if(expressions.size()>0 || diagramConfiguration.getAggregate()){
			for(Object data : dataList){
				if(data instanceof Map){
					for(Entry<String, Expression> entry : expressions.entrySet()){
						Map<String,Object> env = new HashMap<String,Object>();
						env.putAll((Map<String,Object>) data);
						((Map<String,Object>) data).put(entry.getKey(), entry.getValue().execute(env));
					}
					if(diagramConfiguration.getAggregate()){
						Object xValue = ((Map) data).get(xField);
						if(aggregateDataMap.get(xValue) == null){
							Map<String, Object> aggregateData = new HashMap<>();
							aggregateData.put(xField, xValue);
							for(Entry<String, String> yField : aggregates.entrySet()){
								aggregateData.put(yField.getKey(),BigDecimal.ZERO);
							}
							aggregateDataMap.put(xValue, aggregateData);
						}
						
						Map<String, Object> aggregateData = aggregateDataMap.get(xValue);
						for(Entry<String, String> yField : aggregates.entrySet()){
							aggregateData.put(yField.getKey(), OnlineUtil.calculation(aggregateData.get(yField.getKey()), ((Map) data).get(yField.getKey()), yField.getValue()));
						}
						aggregateDataMap.put(xValue, aggregateData);
					}
				}
			}
		}

		// 处理树形表格数据
		if (diagramConfiguration.getGraphType().equals("treeTable")) {
			String unfoldFieldName = diagramFieldConfigurationList.get(0).getFieldName();
			dataList = this.diagramConfigurationService.handelTreeTableData(dataList, unfoldFieldName, diagramConfiguration.getPid());
		}

		Map<String,Collection> result = new HashMap<String, Collection>();
		result.put("data", dataList);
		result.put("aggregate", aggregateDataMap.values());
		// 封装数据
		ChartsData<Object, DiagramConfiguration, DiagramFieldConfiguration> chartsData = new ChartsData<>();
		chartsData.setData(result);
		chartsData.setDictOptions(null);
		chartsData.setHead(diagramConfiguration);
		chartsData.setItems(diagramFieldConfigurationList);

		return Result.ok(chartsData);
	}


	/**
	 * sql预解析 【注意】遇到 插入、删除、更新（会提示解析失败） 【目的】阻止除RUD语句执行，破坏数据
	 * 
	 * @param sql
	 * @return
	 */
	// @AutoLog(value = "图表配置-sql预解析")
	@ApiOperation(value="图表配置-sql预解析", notes="图表配置-sql预解析")
	@RequestMapping(value = "/parseSql", method = RequestMethod.POST)
	@Transactional(readOnly = true)
	public Result<?> parseSql(@RequestParam(name = "sql", required = true) String sql) {
		Set<String> keys = null;
		try {
			List<Map<String,String>> result = this.diagramConfigurationService.selectBySql(sql);
			if(result == null || result.size() == 0){
				log.error(CommonConstants.MSG_ERROR_NO_DATA_FOUND);
				return Result.error(CommonConstants.MSG_ERROR_NO_DATA_FOUND);
			}else{
				Map<String,String> map = result.get(0);
				keys = map.keySet();
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(CommonConstants.MSG_ERROR_PARSING, e);
			return Result.error(CommonConstants.MSG_ERROR_PARSING);
		}
		return Result.OK(CommonConstants.MSG_SUCCESS_PARSING,keys);
	}

	/**
	 * 远程code重复校验
	 * 
	 * @param code
	 * @return
	 */
	@AutoLog(value = "图表配置-code重复校验")
	@ApiOperation(value = "图表配置-code重复校验", notes = "图表配置-code重复校验")
	@RequestMapping(value = "/codeCheck", method = RequestMethod.GET)
	public Result<?> codeCheck(@RequestParam(name = "code", required = true) String code) {
		Result<?> result = this.duplicateCheck(code);
		if (result != null) {
			return result;
		}
		return Result.OK();
	}

	/**
	 * code重复校验
	 * 
	 * @param code
	 * @return
	 */
	private Result<?> duplicateCheck(String code) {
		QueryWrapper<DiagramConfiguration> queryWrapper = new QueryWrapper<>();
		queryWrapper.eq("code", code);
		List<DiagramConfiguration> diagramConfigurationList = this.diagramConfigurationService.list(queryWrapper);
		if (diagramConfigurationList != null && !diagramConfigurationList.isEmpty()) {
			return Result.error(CommonConstants.MSG_EXIST + "[ " + code + " ]");
		}
		return null;
	}

	/**
	 * 组装SQL，用于图表查询
	 * @param sql
	 * @param params
	 * @return
	 */
	private static String assemblySql(String sql, String params) {
		if (org.springframework.util.StringUtils.isEmpty(params)) {
			return sql;
		} else {
			// 拼装sql
			StringBuilder sbe = new StringBuilder("select * from (");
			sbe.append(sql);
			sbe.append(") tt where 1 = 1 ");

			JSONArray jsonArray = null;
			try {
				jsonArray = JSONArray.parseArray(URLDecoder.decode(params, "UTF-8"));
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			for (int i = 0; i < jsonArray.size(); i++) {

				JSONObject jsonObject = jsonArray.getJSONObject(i);

				String fieldName = jsonObject.getString("fieldName");
				String fieldType = jsonObject.getString("fieldType");
				String searchMode = jsonObject.getString("searchMode");
				if (searchMode.equals("single")) {
					if (fieldType.equals("String")) {
						String value = jsonObject.getString("value");
						if (!org.springframework.util.StringUtils.isEmpty(value)) {
							sbe.append(" and " + fieldName + " = '" + value + "'");
						}
					} else {
						Integer value = jsonObject.getInteger("value");
						if (value != null) {
							sbe.append(" and " + fieldName + " = " + value);
						}
					}
				} else {
					if (fieldType.equals("String")) {
						JSONArray valueArr = jsonObject.getJSONArray("value");
						String startValue = valueArr.getString(0);
						String endValue = valueArr.getString(1);

						if (!org.springframework.util.StringUtils.isEmpty(startValue)) {
							sbe.append(" and " + fieldName + " >= '" + startValue + "'" );
						}

						if (!org.springframework.util.StringUtils.isEmpty(endValue)) {
							sbe.append(" and " + fieldName + " <= '" + endValue + "'" );
						}
					} else {
						JSONArray valueArr = jsonObject.getJSONArray("value");
						Integer startValue = valueArr.getInteger(0);
						Integer endValue = valueArr.getInteger(1);

						if (startValue != null) {
							sbe.append(" and " + fieldName + " >= " + startValue);
						}

						if (endValue != null) {
							sbe.append(" and " + fieldName + " <= " + endValue);
						}
					}
				}
			}
			
			return sbe.toString();
		}
	}
}