模板导入便民服务

Null

2021-03-12 5abbd247039800d7540e30bcbb4ef79f480a40e8

模板导入便民服务

 springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/CommunityConvenientApi.java

@@ -1,20 +1,26 @@
package com.panzhihua.applets_backstage.api;

import com.alibaba.excel.EasyExcel;
import com.jcraft.jsch.SftpException;
import com.panzhihua.common.controller.BaseController;
import com.panzhihua.common.listen.ComCvtServeExcelListen;
import com.panzhihua.common.model.dtos.community.*;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComActVO;
import com.panzhihua.common.model.vos.community.ComCvtBusinessVO;
import com.panzhihua.common.model.vos.community.ComCvtCategoryVO;
import com.panzhihua.common.model.vos.community.ComCvtServeVO;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.common.service.community.CommunityService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;

/**
 * @description: 社区便民服务商家接口
@@ -29,6 +35,23 @@

    @Resource
    private CommunityService communityService;

    @Value("${excel.convenientUrl}")
    private String excelConvenientUrl="http://panzhihua.nhys.cdnhxx.com/web/bianminfuwudaoru.xlsx";
    // FTP 登录用户名
    @Value("${ftp.username}")
    private String userName;
    // FTP 登录密码
    @Value("${ftp.password}")
    private String password;
    // FTP 服务器地址IP地址
    @Value("${ftp.host}")
    private String host;
    // FTP 端口
    @Value("${ftp.port}")
    private int port;
    @Value("${ftp.url}")
    private String url;

    @ApiOperation(value = "分页查询便民服务商家",response = ComCvtBusinessVO.class)
    @PostMapping("/business/page")
@@ -148,4 +171,26 @@
    public R deleteComCvtServe(@RequestParam("id") Long id) {
        return communityService.deleteComCvtServe(id);
    }

    @ApiOperation(value = "下载模板-便民服务")
    @GetMapping(value = "/serve/download/template")
    public R downloadTemplate(HttpServletResponse response) throws IOException, SftpException {
        return R.ok(excelConvenientUrl);
    }

    @ApiOperation(value = "excel导入便民服务")
    @PostMapping(value = "/serve/import", consumes = "multipart/*", headers = "content-type=multipart/form-date")
    public R downloadTemplate(@RequestParam MultipartFile file, HttpServletRequest request) {
        String fileName = file.getOriginalFilename();  //获取文件名
        log.info("传入文件名字【{}】",fileName);
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            EasyExcel.read(inputStream, ComCvtServeExcelVO.class, new ComCvtServeExcelListen(communityService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("导入模板失败【{}】", e.getMessage());
        }
        return R.ok();
    }
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComCvtServeExcelListen.java

New file
@@ -0,0 +1,56 @@
package com.panzhihua.common.listen;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSONObject;
import com.panzhihua.common.exceptions.ServiceException;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComCvtServeExcelVO;
import com.panzhihua.common.service.community.CommunityService;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * @description: 便民服务导入监听
 * @author: Null
 * @date: 2021/3/11 13:26
 */
@Slf4j
public class ComCvtServeExcelListen extends AnalysisEventListener<ComCvtServeExcelVO> {

    private CommunityService communityService;


    public ComCvtServeExcelListen(CommunityService communityService){
        this.communityService = communityService;
    }


    private static final int BATCH_COUNT = 100;
    private List<ComCvtServeExcelVO> list = new ArrayList<>();
    @Override
    public void invoke(ComCvtServeExcelVO comCvtServeExcelVO, AnalysisContext analysisContext) {
        list.add(comCvtServeExcelVO);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(list.size() >= BATCH_COUNT){
            log.info("excel导入数据【{}】", JSONObject.toJSONString(list));
            R r = this.communityService.listSaveConvenientServeExcelVO(list);
            if (!R.isOk(r)) {
                throw new ServiceException(r.getMsg());
            }
            list.clear();           //清空list
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("excel导入数据【{}】", JSONObject.toJSONString(list));
        R r = this.communityService.listSaveConvenientServeExcelVO(list);//确保最后遗留的数据保存在数据库中
        if (!R.isOk(r)) {
            throw new ServiceException(r.getMsg());
        }
    }
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComCvtServeExcelVO.java

New file
@@ -0,0 +1,47 @@
package com.panzhihua.common.model.vos.community;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @description: 批量导入便民服务
 * @author: Null
 * @date: 2021/3/11 17:11
 */
@Data
public class ComCvtServeExcelVO implements Serializable {
    /**
     * 服务分类
     */
    @ExcelProperty(value = "服务分类" ,index = 0)
    private String categoryName;
    /**
     * 服务名称
     */
    @ExcelProperty(value = "服务名称" ,index = 1)
    private String serviceName;
    /**
     * 服务价格（元）
     */
    @ExcelProperty(value = "服务价格（元）" ,index = 2)
    private BigDecimal servicePrice;
    /**
     * 服务描述
     */
    @ExcelProperty(value = "服务描述" ,index = 3)
    private String serviceDesc;
    /**
     * 商家名称
     */
    @ExcelProperty(value = "商家名称" ,index = 4)
    private String businessName;
    /**
     * 备注
     */
    @ExcelProperty(value = "备注" ,index = 5)
    private String remark;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java

@@ -7,9 +7,12 @@
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.SystemmanagementConfigVO;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.common.model.vos.partybuilding.PartyBuildingMemberExcelVO;
import com.panzhihua.common.model.vos.user.UserPhoneVO;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @program: springcloud_k8s_panzhihuazhihuishequ
@@ -1087,4 +1090,11 @@
     */
    @GetMapping("/convenient/business/serve/get")
    R getComCvtBusinessServeDetail(@RequestParam("id")Long id);

    /**
     * 批量导入便民服务
     * @param list 便民服务集合
     */
    @PostMapping("/convenient/serve/import")
    R listSaveConvenientServeExcelVO(@RequestBody List<ComCvtServeExcelVO> list,@RequestParam("communityId") Long communityId);
}

 springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityConvenientApi.java

@@ -1,5 +1,7 @@
package com.panzhihua.community_backstage.api;

import com.alibaba.excel.EasyExcel;
import com.jcraft.jsch.SftpException;
import com.panzhihua.common.controller.BaseController;
import com.panzhihua.common.model.dtos.community.ComCvtBusinessDTO;
import com.panzhihua.common.model.dtos.community.ComCvtServeDTO;
@@ -7,17 +9,21 @@
import com.panzhihua.common.model.dtos.community.PageComCvtServeDTO;
import com.panzhihua.common.model.vos.LoginUserInfoVO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComActVO;
import com.panzhihua.common.model.vos.community.ComCvtBusinessVO;
import com.panzhihua.common.model.vos.community.ComCvtCategoryVO;
import com.panzhihua.common.model.vos.community.ComCvtServeVO;
import com.panzhihua.common.model.vos.community.*;
import com.panzhihua.common.service.community.CommunityService;
import com.panzhihua.common.listen.ComCvtServeExcelListen;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;

/**
 * @description: 社区便民服务商家接口
@@ -32,6 +38,23 @@

    @Resource
    private CommunityService communityService;

    @Value("${excel.convenientUrl}")
    private String excelConvenientUrl="http://panzhihua.nhys.cdnhxx.com/web/bianminfuwudaoru.xlsx";
    // FTP 登录用户名
    @Value("${ftp.username}")
    private String userName;
    // FTP 登录密码
    @Value("${ftp.password}")
    private String password;
    // FTP 服务器地址IP地址
    @Value("${ftp.host}")
    private String host;
    // FTP 端口
    @Value("${ftp.port}")
    private int port;
    @Value("${ftp.url}")
    private String url;

    @ApiOperation(value = "分页查询便民服务商家",response = ComCvtBusinessVO.class)
    @PostMapping("/business/page")
@@ -115,4 +138,26 @@
    public R deleteComCvtServe(@RequestParam("id") Long id) {
        return communityService.deleteComCvtServe(id);
    }

    @ApiOperation(value = "下载模板-便民服务")
    @GetMapping(value = "/serve/download/template")
    public R downloadTemplate(HttpServletResponse response) throws IOException, SftpException {
        return R.ok(excelConvenientUrl);
    }

    @ApiOperation(value = "excel导入便民服务")
    @PostMapping(value = "/serve/import", consumes = "multipart/*", headers = "content-type=multipart/form-date")
    public R downloadTemplate(@RequestParam MultipartFile file, HttpServletRequest request) {
        String fileName = file.getOriginalFilename();  //获取文件名
        log.info("传入文件名字【{}】",fileName);
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            EasyExcel.read(inputStream, ComCvtServeExcelVO.class, new ComCvtServeExcelListen(communityService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("导入模板失败【{}】", e.getMessage());
        }
        return R.ok();
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ConvenientApi.java

@@ -1,7 +1,10 @@
package com.panzhihua.service_community.api;

import com.panzhihua.common.exceptions.PartyBuildingMemberException;
import com.panzhihua.common.model.dtos.community.*;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComCvtServeExcelVO;
import com.panzhihua.common.model.vos.partybuilding.PartyBuildingMemberExcelVO;
import com.panzhihua.service_community.service.ComCvtBusinessService;
import com.panzhihua.service_community.service.ComCvtCategoryService;
import com.panzhihua.service_community.service.ComCvtServeService;
@@ -10,6 +13,7 @@
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

/**
 * @description: 便民服务接口
@@ -225,4 +229,13 @@
    public R getComCvtBusinessServeDetail(@RequestParam("id")Long id){
        return comCvtBusinessService.getComCvtBusinessServeDetail(id);
    };

    /**
     * 批量新增商家服务
     * @param list 服务集合
     */
    @PostMapping("/serve/import")
    public R listSaveConvenientServeExcelVO(@RequestBody List<ComCvtServeExcelVO> list,@RequestParam("communityId") Long communityId){
        return comCvtServeService.listSaveConvenientServeExcelVO(list,communityId);
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComCvtServeService.java

@@ -4,6 +4,7 @@
import com.panzhihua.common.model.dtos.community.PageComCvtServeDTO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComCvtBusinessCategoryServeVO;
import com.panzhihua.common.model.vos.community.ComCvtServeExcelVO;

import java.util.List;

@@ -55,4 +56,11 @@
     * @return
     */
    List<ComCvtBusinessCategoryServeVO> getBusinessCategoryServeVOS(Long businessId);

    /**
     * 导入便民服务
     * @param list
     * @return
     */
    R listSaveConvenientServeExcelVO(List<ComCvtServeExcelVO> list,Long communityId);
}

 springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java

@@ -3,14 +3,18 @@
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.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.panzhihua.common.model.dtos.community.ComCvtServeDTO;
import com.panzhihua.common.model.dtos.community.PageComCvtServeDTO;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.community.ComCvtBusinessCategoryServeVO;
import com.panzhihua.common.model.vos.community.ComCvtServeExcelVO;
import com.panzhihua.common.model.vos.community.ComCvtServeVO;
import com.panzhihua.service_community.dao.ComCvtBusinessAreaDAO;
import com.panzhihua.service_community.dao.ComCvtBusinessDAO;
import com.panzhihua.service_community.dao.ComCvtCategoryDAO;
import com.panzhihua.service_community.dao.ComCvtServeDAO;
import com.panzhihua.service_community.model.dos.ComCvtBusinessAreaDO;
import com.panzhihua.service_community.model.dos.ComCvtBusinessDO;
import com.panzhihua.service_community.model.dos.ComCvtCategoryDO;
import com.panzhihua.service_community.model.dos.ComCvtServeDO;
@@ -21,7 +25,9 @@

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
@@ -30,7 +36,7 @@
 * @date: 2021/3/12 8:58
 */
@Service
public class ComCvtServeServiceImpl implements ComCvtServeService {
public class ComCvtServeServiceImpl extends ServiceImpl<ComCvtServeDAO, ComCvtServeDO> implements ComCvtServeService {

    @Resource
    private ComCvtServeDAO comCvtServeDAO;
@@ -135,6 +141,7 @@

    /**
     * 按分类筛选
     *
     * @param comCvtServeDOS
     * @param categoryId
     * @return
@@ -149,4 +156,49 @@
        });
        return comCvtServeVOS;
    }

    @Override
    public R listSaveConvenientServeExcelVO(List<ComCvtServeExcelVO> list, Long communityId) {
        List<ComCvtServeDO> comCvtServeDOS = new ArrayList<>();
        List<ComCvtCategoryDO> categoryDOS = comCvtCategoryDAO.selectList(new QueryWrapper<ComCvtCategoryDO>().lambda().orderByDesc(ComCvtCategoryDO::getWeight));
        Map<String, Long> categoryMap = getCategoryMap(categoryDOS);
        List<ComCvtBusinessDO> comCvtBusinessDOS;
        if (null != communityId && communityId != 0) {
            comCvtBusinessDOS = comCvtBusinessDAO.selectList(new QueryWrapper<ComCvtBusinessDO>().lambda().eq(ComCvtBusinessDO::getCommunityId, communityId));
        } else {
            comCvtBusinessDOS = comCvtBusinessDAO.selectList(null);
        }
        Map<String, Long> businessMap = getBusinessMap(comCvtBusinessDOS);
        list.forEach(comCvtServeExcelVO -> {
            if (categoryMap.containsKey(comCvtServeExcelVO.getCategoryName()) && businessMap.containsKey(comCvtServeExcelVO.getBusinessName())) {
                ComCvtServeDO comCvtServeDO = new ComCvtServeDO();
                BeanUtils.copyProperties(comCvtServeExcelVO, comCvtServeDO);
                comCvtServeDO.setCategoryId(categoryMap.get(comCvtServeExcelVO.getCategoryName()));
                comCvtServeDO.setBusinessId(businessMap.get(comCvtServeExcelVO.getBusinessName()));
                comCvtServeDOS.add(comCvtServeDO);
            }
        });
        this.saveBatch(comCvtServeDOS);
        return R.ok();
    }

    private Map<String, Long> getCategoryMap(List<ComCvtCategoryDO> categoryDOS) {
        Map<String, Long> categoryMap = new HashMap<>();
        if (!ObjectUtils.isEmpty(categoryDOS)) {
            categoryDOS.forEach(categoryDO -> {
                categoryMap.put(categoryDO.getName(), categoryDO.getId());
            });
        }
        return categoryMap;
    }

    private Map<String, Long> getBusinessMap(List<ComCvtBusinessDO> comCvtBusinessDOS) {
        Map<String, Long> businessMap = new HashMap<>();
        if (!ObjectUtils.isEmpty(comCvtBusinessDOS)) {
            comCvtBusinessDOS.forEach(businessDO -> {
                businessMap.put(businessDO.getName(), businessDO.getId());
            });
        }
        return businessMap;
    }
}