api/src/main/java/com/xjrsoft/module/dataexpert/controller/DataExpertTemplateController.java

package com.xjrsoft.module.dataexpert.controller;

import cn.dev33.satoken.annotation.SaCheckPermission;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.xjrsoft.common.model.result.RT;
import com.xjrsoft.common.page.ConventPage;
import com.xjrsoft.common.page.PageOutput;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.dataexpert.dto.AddDataExpertTemplateDto;
import com.xjrsoft.module.dataexpert.dto.DataExpertConditionsDto;
import com.xjrsoft.module.dataexpert.dto.DataExpertDto;
import com.xjrsoft.module.dataexpert.dto.DataExpertTemplateListDto;
import com.xjrsoft.module.dataexpert.dto.DataExpertTemplatePageDto;
import com.xjrsoft.module.dataexpert.dto.UpdateDataExpertTemplateDto;
import com.xjrsoft.module.dataexpert.entity.DataExpertSource;
import com.xjrsoft.module.dataexpert.entity.DataExpertTemplate;
import com.xjrsoft.module.dataexpert.service.IDataExpertSourceService;
import com.xjrsoft.module.dataexpert.service.IDataExpertTemplateService;
import com.xjrsoft.module.dataexpert.vo.DataExpertTemplateFieldVo;
import com.xjrsoft.module.dataexpert.vo.DataExpertTemplatePageVo;
import com.xjrsoft.module.dataexpert.vo.DataExpertTemplateVo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.validation.Valid;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
* @title: 数据导出-数据模板
* @Author dzx
* @Date: 2024-04-19
* @Version 1.0
*/
@RestController
@RequestMapping("/dataexpert" + "/dataExpertTemplate")
@Api(value = "/dataexpert"  + "/dataExpertTemplate",tags = "数据导出-数据模板代码")
@AllArgsConstructor
public class DataExpertTemplateController {

    private final IDataExpertSourceService dataExpertSourceService;
    private final IDataExpertTemplateService dataExpertTemplateService;

    @GetMapping(value = "/page")
    @ApiOperation(value="数据导出-数据模板列表(分页)")
    @SaCheckPermission("dataexperttemplate:detail")
    public RT<PageOutput<DataExpertTemplatePageVo>> page(@Valid DataExpertTemplatePageDto dto){

        MPJLambdaWrapper<DataExpertTemplate> select = new MPJLambdaWrapper<DataExpertTemplate>()
                .leftJoin(DataExpertSource.class, DataExpertSource::getId, DataExpertTemplate::getDataExpertSourceId)
                .orderByDesc(DataExpertTemplate::getId)
                .like(StrUtil.isNotEmpty(dto.getName()), DataExpertTemplate::getName, dto.getName())
                .selectAs(DataExpertSource::getName, DataExpertTemplatePageVo::getDataExpertSourceName)
                .selectAs(DataExpertTemplate::getId, DataExpertTemplatePageVo::getId)
                .select(DataExpertTemplate.class, x -> VoToColumnUtil.fieldsToColumns(DataExpertTemplatePageVo.class).contains(x.getProperty()));
        IPage<DataExpertTemplatePageVo> page = dataExpertTemplateService.selectJoinListPage(ConventPage.getPage(dto), DataExpertTemplatePageVo.class, select);
        PageOutput<DataExpertTemplatePageVo> pageOutput = ConventPage.getPageOutput(page, DataExpertTemplatePageVo.class);
        return RT.ok(pageOutput);
    }

    @GetMapping(value = "/info")
    @ApiOperation(value="根据id查询数据导出-数据模板信息")
    @SaCheckPermission("dataexperttemplate:detail")
    public RT<DataExpertTemplateVo> info(@RequestParam Long id){
        DataExpertTemplate dataExpertTemplate = dataExpertTemplateService.getById(id);
        if (dataExpertTemplate == null) {
           return RT.error("找不到此数据！");
        }
        return RT.ok(BeanUtil.toBean(dataExpertTemplate, DataExpertTemplateVo.class));
    }

    @GetMapping(value = "/list")
    @ApiOperation(value="根据数据源id查询数据导出-数据模板信息")
    @SaCheckPermission("dataexperttemplate:detail")
    public RT<List<DataExpertTemplateVo>> list(@Valid DataExpertTemplateListDto dto){
        List<DataExpertTemplate> list = dataExpertTemplateService.list(
            new QueryWrapper<DataExpertTemplate>().lambda()
            .eq(DataExpertTemplate::getDataExpertSourceId, dto.getDataExpertSourceId())
        );
        return RT.ok(BeanUtil.copyToList(list, DataExpertTemplateVo.class));
    }


    @PostMapping
    @ApiOperation(value = "新增数据导出-数据模板")
    @SaCheckPermission("dataexperttemplate:add")
    public RT<Boolean> add(@Valid @RequestBody AddDataExpertTemplateDto dto){
        DataExpertTemplate dataExpertTemplate = BeanUtil.toBean(dto, DataExpertTemplate.class);
        boolean isSuccess = dataExpertTemplateService.save(dataExpertTemplate);
        return RT.ok(isSuccess);
    }

    @PutMapping
    @ApiOperation(value = "修改数据导出-数据模板")
    @SaCheckPermission("dataexperttemplate:edit")
    public RT<Boolean> update(@Valid @RequestBody UpdateDataExpertTemplateDto dto){
        DataExpertTemplate dataExpertTemplate = BeanUtil.toBean(dto, DataExpertTemplate.class);
        return RT.ok(dataExpertTemplateService.updateById(dataExpertTemplate));

    }

    @DeleteMapping
    @ApiOperation(value = "删除数据导出-数据模板")
    @SaCheckPermission("dataexperttemplate:delete")
    public RT<Boolean> delete(@Valid @RequestBody List<Long> ids){
        return RT.ok(dataExpertTemplateService.removeBatchByIds(ids));
    }

    @GetMapping(value = "/field-info")
    @ApiOperation(value="根据id查询数据导出-数据模板信息")
    @SaCheckPermission("dataexperttemplate:detail")
    public RT<List<DataExpertTemplateFieldVo>> getFieldInfo(@RequestParam Long id){
        return RT.ok(dataExpertTemplateService.getFieldList(id));
    }


    @PostMapping("/export-query")
    @ApiOperation(value = "导出")
    public ResponseEntity<byte[]> exportData(@Valid @RequestBody DataExpertDto dto) throws SQLException, IOException {
        //拼接字段
        List<String> fields = new ArrayList<>();
        Map<Integer, String> showNameMap = new HashMap<>();
        List<String> titleList = new ArrayList<>();
        List<DataExpertTemplateFieldVo> fieldList = dto.getFieldList();
        DataExpertSource expertSource;
        String conditions = "";
        if(dto.getDataExpertSourceId() != null){
            expertSource = dataExpertSourceService.getById(dto.getDataExpertSourceId());
            for (int i = 0; i < fieldList.size(); i ++){
                DataExpertTemplateFieldVo fieldVo = fieldList.get(i);
                showNameMap.put(i, fieldVo.getShowName());
                fields.add("ifnull(" + fieldVo.getFieldName() + ", '')");
                titleList.add(fieldVo.getShowName());
            }
        }else{
            //查出想要的数据
            DataExpertTemplate template = dataExpertTemplateService.getById(dto.getDataExpertTemplateId());
            expertSource = dataExpertSourceService.getById(template.getDataExpertSourceId());
            String fieldJsonStr = template.getFieldJson();
            JsonParser parser = new JsonParser();
            JsonArray fieldJson = parser.parse(fieldJsonStr).getAsJsonArray();
            int i = 0;
            for (JsonElement jsonElement : fieldJson) {
                JsonObject jsonObject = jsonElement.getAsJsonObject();
                showNameMap.put(i, jsonObject.get("name").getAsString());
                fields.add("ifnull(" + jsonObject.get("field").getAsString() + ", '')");
                titleList.add(jsonObject.get("name").getAsString());
                i ++;
            }
        }
        if(dto.getIds() != null && !dto.getIds().isEmpty()){
            String ids = dto.getIds().toString().substring(1, dto.getIds().toString().length() - 1).replaceAll(",","','");
            conditions += " and id in ('" + ids + "')";
        }
        if(dto.getConditions() != null && dto.getConditions().size() != 0){
            for (DataExpertConditionsDto condition : dto.getConditions()) {
                if(condition.getKeyType() == 1){
                    conditions += " and " + condition.getKeyName() + " like '%" + condition.getKeyValue() + "%'";
                }else if(condition.getKeyType() == 2 && condition.getKeyValue() != null && !"".equals(condition.getKeyValue())){
                    conditions += " and " + condition.getKeyName() + " in (";
                    String[] keyValues = condition.getKeyValue().split(",");
                    for(int k = 0; k < keyValues.length; k ++){
                        if(k > 0){
                            conditions += ",";
                        }
                        conditions += "'" + keyValues[k] + "'";
                    }
                    conditions += ")";
                }
            }
        }

        //查出导出的数据并进行组装
        List<String[]> dataList = dataExpertTemplateService.getDataList(fields.toString().substring(1, fields.toString().length() - 1), expertSource.getViewName(), conditions);

        List<String[]> allDataList = new ArrayList<>();
        allDataList.add(titleList.toArray(new String[titleList.size()]));
        allDataList.addAll(dataList);

        String sheetName = "数据";
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.createFreezePane(0, 1, 0, 1);
        for (int i = 0; i < allDataList.size(); i ++){
            Row row = sheet.createRow(i);
            String[] data = allDataList.get(i);
            for (int j = 0; j < data.length; j ++ ){
                Cell cell = row.createCell(j);
                cell.setCellValue(data[j]);
            }
        }

        String fileName = "导出结果" + ExcelTypeEnum.XLSX.getValue();
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        workbook.write(bot);
        return RT.fileStream(bot.toByteArray(), fileName);
    }

}