项目库导出

fuliqi

2024-12-02 1462476c93011079d6cec65be2877729571bba16

项目库导出

 business/src/main/java/com/ycl/domain/vo/ProjectVO.java

@@ -8,6 +8,8 @@
@Data
public class ProjectVO extends ProjectExcelTemplate {
    private Long id;
    //政策表id
    private Long policyId;
    /** 状态码 */
    private String projectColorCode;
    private List<Long> competentDepartmentList;

 business/src/main/java/com/ycl/service/ProjectInfoService.java

@@ -11,6 +11,7 @@
import com.ycl.domain.vo.IndexDTO;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@@ -80,5 +81,5 @@

    Result getManagerFlag(Integer recordId);

    void export(HttpServletResponse response, ProjectExportQuery query);
    void export(HttpServletResponse response, ProjectExportQuery query) throws IOException;
}

 business/src/main/java/com/ycl/service/impl/ProjectInfoServiceImpl.java

@@ -1,11 +1,15 @@
package com.ycl.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ZipUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ycl.common.base.Result;
import com.ycl.common.config.SysConfig;
import com.ycl.common.constant.Constants;
import com.ycl.common.enums.business.FileTypeEnum;
import com.ycl.common.enums.business.ImportanceTypeEnum;
import com.ycl.common.enums.business.ProjectCategoryEnum;
@@ -28,17 +32,25 @@
import com.ycl.service.FileService;
import com.ycl.service.ProjectInfoService;
import lombok.RequiredArgsConstructor;
import org.apache.commons.codec.Charsets;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.StandardCopyOption;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipOutputStream;

/**
 * 项目管理基础信息表 服务实现类
@@ -388,28 +400,122 @@
    }

    @Override
    public void export(HttpServletResponse response, ProjectExportQuery query) {
    public void export(HttpServletResponse response, ProjectExportQuery query) throws IOException {
        List<ProjectVO> data = baseMapper.selectProjectDetailByIds(query.getDataIdList());
        List<ProjectExcelTemplate> excelList = new ArrayList<>();
        //字典作翻译
        data.forEach(item->{
        data.forEach(item -> {
            ProjectExcelTemplate excel = new ProjectExcelTemplate();
            BeanUtils.copyProperties(item,excel);
            BeanUtils.copyProperties(item, excel);
            //项目阶段
            excel.setProjectPhase(ProjectCategoryEnum.getPhaseByProjectStatus(item.getProjectStatus(),item.getProcessId()!=null));
            excel.setProjectPhase(ProjectCategoryEnum.getPhaseByProjectStatus(item.getProjectStatus(), item.getProcessId() != null));
            excelList.add(excel);
        });
        //补充文件的字段
        Set<Integer> indexes = OutputExcelUtils.getSelectFields(query.getFieldList(), ProjectExcelTemplate.class);
        //不需要附件
        if(!query.getRequireFile()) {
        if (!query.getRequireFile()) {
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                EasyExcel.write(outputStream, ProjectExcelTemplate.class).includeColumnIndexes(indexes).sheet("项目列表").doWrite(excelList);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                throw new BaseException("导出失败：" + e.getMessage());
            }
        } else {
            //需要附件,导出为zip
            response.setContentType("application/zip");
            response.setCharacterEncoding(Charsets.UTF_8.name());
            String fileName = URLEncoder.encode("项目列表", Charsets.UTF_8);
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".zip");

            java.io.File tempDir = null;
            try {
                //查出附件
                List<File> projectInfoFiles = fileMapper.selectList(new QueryWrapper<File>()
                        .in("bus_id", data.stream().map(ProjectVO::getId).collect(Collectors.toList()))
                        .and(q -> q.eq("type", FileTypeEnum.PROJECT_INFO.getType()).or().eq("type", FileTypeEnum.DOCUMENT_INFO.getType()))
                );
                List<File> policyFiles = fileMapper.selectList(new QueryWrapper<File>()
                        .in("bus_id", data.stream().map(ProjectVO::getPolicyId).collect(Collectors.toList()))
                        .eq("type", FileTypeEnum.INVEST_POLICY.getType())
                );
                projectInfoFiles.addAll(policyFiles);
                projectInfoFiles.forEach(file -> {
                    data.forEach(item -> {
                        if (FileTypeEnum.PROJECT_INFO.equals(file.getType())) {
                            if (file.getBusId().equals(item.getId())) {
                                //审批计划书
                                item.setApprovalPlan(file.getOriginalName());
                            }
                        }else if(FileTypeEnum.DOCUMENT_INFO.equals(file.getType())){
                            if (file.getBusId().equals(item.getId())) {
                                //相关文书
                                item.setDocuments(file.getOriginalName());
                            }
                        }else if(FileTypeEnum.INVEST_POLICY.equals(file.getType())){
                            if (file.getBusId().equals(item.getPolicyId())) {
                                //符合产业政策附件
                                item.setPolicyComplianceAttachment(file.getOriginalName());
                            }
                        }
                    });

                });
                // 创建临时目录
                tempDir = Files.createTempDirectory("temp").toFile();

                java.io.File templateDir = new java.io.File(tempDir, "template");
                if (!templateDir.exists()) {
                    templateDir.mkdirs();
                }

                // 创建 Excel 文件
                java.io.File excelFile = new java.io.File(templateDir, "excel.xlsx");
                if (!excelFile.exists()) {
                    excelFile.createNewFile();
                }

                // 写入 Excel 模板数据
                try (FileOutputStream fileOutputStream = new FileOutputStream(excelFile, false)) {
                    EasyExcel.write(fileOutputStream, ProjectExcelTemplate.class).sheet("项目列表").doWrite(excelList);
                }

                // 创建附件目录
                java.io.File attachmentDir = new java.io.File(templateDir, "attachment");
                if (!attachmentDir.exists()) {
                    attachmentDir.mkdirs();
                }

                //将附件复制导目录下
                for (File item : projectInfoFiles) {
                    String url = item.getUrl();
                    if (StringUtils.isBlank(url)) continue;
                    url = url.replaceFirst(Constants.RESOURCE_PREFIX, SysConfig.getProfile());
                    // 直接使用File类处理系统路径
                    java.io.File sourceFile = new java.io.File(url);
                    if (!sourceFile.exists() || !sourceFile.isFile()) {
                        // 处理文件不存在或不是文件的情况
                        continue; // 或者记录日志、抛出异常等
                    }
                    // 目标文件路径，使用attachmentDir作为父目录，并保持原文件名
                    java.io.File targetFile = new java.io.File(attachmentDir, sourceFile.getName());
                    // 复制文件
                    try {
                        Files.copy(sourceFile.toPath(), targetFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
                    } catch (IOException e) {
                        log.error(e.getMessage());
                    }
                }
                // 打包 ZIP 文件
                java.io.File zipFile = ZipUtil.zip(templateDir);
                byte[] zipBytes = Files.readAllBytes(zipFile.toPath());

                // 将 ZIP 文件写入响应
                try (ServletOutputStream outputStream = response.getOutputStream()) {
                    outputStream.write(zipBytes);
                }
            } finally {
                OutputExcelUtils.deleteDirectoryOrFile(tempDir);
            }
        }
        //TODO：需要附件
    }
}

 business/src/main/resources/mapper/ProjectInfoMapper.xml

@@ -165,7 +165,27 @@
    </select>

    <select id="selectProjectDetailByIds" resultType="com.ycl.domain.vo.ProjectVO">
        select *
        select
        TPI.id as id,TPI.project_name,TPI.project_code,TPI.content,TPI.project_type,TPI.project_status,TPI.fund_type,TPI.invest_type,TPI.project_phase,
        TPI.tag,TPI.competent_department,TPI.area,TPI.management_centralization,TPI.project_approval_type,TPI.importance_type,TPI.year,TPI.year_invest_amount,TPI.create_project_time,TPI.plan_start_time,
        TPI.plan_complete_time,TPI.win_unit,TPI.win_amount,TPI.win_time,TPI.project_address,TPI.longitude,TPI.latitude,
        TPI.project_owner_unit,TPI.project_contact_person,TPI.contact,TPI.gmt_create,TPI.gmt_update,TPI.update_by,TPI.create_by,
        TPIF.id,TPIF.project_id,TPIF.total_investment,TPIF.principal,TPIF.government_investment_total,TPIF.central_investment_total,TPIF.central_budget_investment,TPIF.central_fiscal_investment,TPIF.central_special_bond_investment,
        TPIF.central_special_fund_investment,TPIF.provincial_investment_total,
        TPIF.provincial_budget_investment,TPIF.provincial_fiscal_investment,TPIF.provincial_special_fund_investment,TPIF.city_investment_total,TPIF.city_budget_investment,TPIF.city_fiscal_investment,TPIF.city_special_fund_investment,
        TPIF.county_investment_total,TPIF.county_budget_investment,TPIF.county_fiscal_investment,
        TPIF.county_special_fund_investment,TPIF.domestic_loan_total,TPIF.bank_loan,TPIF.foreign_investment_total,TPIF.enterprise_self_raised_total,TPIF.other_investment_total,TPIF.gmt_create,
        TPIF.create_by,TPIF.gmt_update,TPIF.update_by,
        TPII.id,TPII.project_id,TPII.be_cross_region,TPII.construction_location,TPII.detailed_address,TPII.be_compensation_project,TPII.compensation_reason,TPII.planned_start_date,
        TPII.expected_completion_date,TPII.national_industry_classification,TPII.industry_classification,TPII.project_nature,TPII.project_attribute,
        TPII.use_earth,TPII.content_scale,TPII.code,TPII.gmt_create,TPII.create_by,TPII.gmt_update, TPII.update_by,
        TPIPC.id as policyId,TPIPC.project_id,TPIPC.belongs_to_industry_adjustment_directory,TPIPC.belongs_to_western_encouraged_directory,TPIPC.not_banned_or_controlled_project,TPIPC.information_is_true,TPIPC.special_planning_compliance,
        TPIPC.annual_energy_consumption,TPIPC.annual_electricity_consumption,TPIPC.energy_check,TPIPC.no_only_check_type,TPIPC.remarks,TPIPC.gmt_create,
        TPIPC.gmt_update,TPIPC.create_by,TPIPC.update_by,
        TPURI.id,TPURI.project_id,TPURI.total_investment,TPURI.project_unit,TPURI.project_unit_type,
        TPURI.registration_type,TPURI.holding_situation,TPURI.certificate_type,TPURI.certificate_number,TPURI.registered_address,TPURI.registered_capital,
        TPURI.legal_representative,TPURI.fixed_phone,TPURI.legal_person_idcard,TPURI.project_contact_person,TPURI.phone,TPURI.contact_idcard,
        TPURI.wechat,TPURI.contact_address,TPURI.post_code,TPURI.email,TPURI.create_by,TPURI.update_by,TPURI.gmt_create,TPURI.gmt_update
        from t_project_info TPI
        left join t_project_investment_funding TPIF on TPI.id = TPIF.project_id and TPIF.deleted = 0
        left join t_project_investment_info TPII on TPI.id = TPII.project_id and TPII.deleted = 0

 business/src/main/resources/mapper/ProjectInvestmentFundingMapper.xml

@@ -30,9 +30,9 @@
        <result column="foreign_investment_total" property="foreignInvestmentTotal" />
        <result column="enterprise_self_raised_total" property="enterpriseSelfRaisedTotal" />
        <result column="other_investment_total" property="otherInvestmentTotal" />
        <result column="gmt_create_time" property="gmtCreateTime" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="create_by" property="createBy" />
        <result column="gmt_update_time" property="gmtUpdateTime" />
        <result column="gmt_update" property="gmtUpdate" />
        <result column="update_by" property="updateBy" />
    </resultMap>

@@ -70,9 +70,9 @@
            TPIF.foreign_investment_total,
            TPIF.enterprise_self_raised_total,
            TPIF.other_investment_total,
            TPIF.gmt_create_time,
            TPIF.gmt_create,
            TPIF.create_by,
            TPIF.gmt_update_time,
            TPIF.gmt_update,
            TPIF.update_by,
            TPIF.id
        FROM
@@ -110,9 +110,9 @@
            TPIF.foreign_investment_total,
            TPIF.enterprise_self_raised_total,
            TPIF.other_investment_total,
            TPIF.gmt_create_time,
            TPIF.gmt_create,
            TPIF.create_by,
            TPIF.gmt_update_time,
            TPIF.gmt_update,
            TPIF.update_by,
            TPIF.id
        FROM

 business/src/main/resources/mapper/ProjectInvestmentInfoMapper.xml

@@ -19,9 +19,9 @@
        <result column="use_earth" property="useEarth" />
        <result column="content_scale" property="contentScale" />
        <result column="code" property="code" />
        <result column="gmt_create_time" property="gmtCreateTime" />
        <result column="gmt_create" property="gmtCreate" />
        <result column="create_by" property="createBy" />
        <result column="gmt_update_time" property="gmtUpdateTime" />
        <result column="gmt_update" property="gmtUpdate" />
        <result column="update_by" property="updateBy" />
    </resultMap>

@@ -43,9 +43,9 @@
            TPII.use_earth,
            TPII.content_scale,
            TPII.code,
            TPII.gmt_create_time,
            TPII.gmt_create,
            TPII.create_by,
            TPII.gmt_update_time,
            TPII.gmt_update,
            TPII.update_by,
            TPII.id
        FROM
@@ -72,9 +72,9 @@
            TPII.use_earth,
            TPII.content_scale,
            TPII.code,
            TPII.gmt_create_time,
            TPII.gmt_create,
            TPII.create_by,
            TPII.gmt_update_time,
            TPII.gmt_update,
            TPII.update_by,
            TPII.id
        FROM

 business/src/main/resources/mapper/ProjectUnitRegistrationInfoMapper.xml

@@ -26,8 +26,8 @@
        <result column="email" property="email" />
        <result column="create_by" property="createBy" />
        <result column="update_by" property="updateBy" />
        <result column="gmt_create_time" property="gmtCreateTime" />
        <result column="gmt_update_time" property="gmtUpdateTime" />
        <result column="gmt_create" property="gmtCreateTime" />
        <result column="gmt_update" property="gmtUpdateTime" />
    </resultMap>


@@ -60,8 +60,8 @@
            TPURI.email,
            TPURI.create_by,
            TPURI.update_by,
            TPURI.gmt_create_time,
            TPURI.gmt_update_time,
            TPURI.gmt_create,
            TPURI.gmt_update,
            TPURI.id
        FROM
            t_project_unit_registration_info TPURI
@@ -94,8 +94,8 @@
            TPURI.email,
            TPURI.create_by,
            TPURI.update_by,
            TPURI.gmt_create_time,
            TPURI.gmt_update_time,
            TPURI.gmt_create,
            TPURI.gmt_update,
            TPURI.id
        FROM
            t_project_unit_registration_info TPURI

 common/src/main/java/com/ycl/common/utils/excel/OutputExcelUtils.java

@@ -120,7 +120,7 @@
        }
    }

    private static void deleteDirectoryOrFile(File file) {
    public static void deleteDirectoryOrFile(File file) {
        if (ObjectUtil.isNull(file)) {
            return;
        }