From 5abbd247039800d7540e30bcbb4ef79f480a40e8 Mon Sep 17 00:00:00 2001 From: Null <281575458@qq.com> Date: 星期五, 12 三月 2021 17:59:18 +0800 Subject: [PATCH] 模板导入便民服务 --- springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java | 10 ++ springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/CommunityConvenientApi.java | 53 +++++++++ springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityConvenientApi.java | 53 +++++++++ springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComCvtServeExcelListen.java | 56 +++++++++++ springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComCvtServeService.java | 8 + springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ConvenientApi.java | 13 ++ springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComCvtServeExcelVO.java | 47 +++++++++ springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java | 54 ++++++++++ 8 files changed, 285 insertions(+), 9 deletions(-) diff --git a/springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/CommunityConvenientApi.java b/springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/CommunityConvenientApi.java index 5c1d624..cf5e598 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/applets_backstage/src/main/java/com/panzhihua/applets_backstage/api/CommunityConvenientApi.java +++ b/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(); + } } diff --git a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComCvtServeExcelListen.java b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComCvtServeExcelListen.java new file mode 100644 index 0000000..23b205d --- /dev/null +++ b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/listen/ComCvtServeExcelListen.java @@ -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()); + } + } +} diff --git a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComCvtServeExcelVO.java b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComCvtServeExcelVO.java new file mode 100644 index 0000000..a9ea31d --- /dev/null +++ b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComCvtServeExcelVO.java @@ -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; +} diff --git a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java b/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java index 58d4edc..85b5738 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java +++ b/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); } diff --git a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityConvenientApi.java b/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityConvenientApi.java index 69a9a62..264f390 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/api/CommunityConvenientApi.java +++ b/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(); + } } diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ConvenientApi.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ConvenientApi.java index 99498dd..745830e 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/api/ConvenientApi.java +++ b/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); + } } diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComCvtServeService.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComCvtServeService.java index 8962aa7..aaacc57 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComCvtServeService.java +++ b/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); } diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java index cae6884..a1b87e3 100644 --- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComCvtServeServiceImpl.java +++ b/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; + } } -- Gitblit v1.7.1