议事用户导出

lin

2024-04-01 03fef57e7e061148a5ced5c83f79c61a68f2ffa3

议事用户导出

 src/main/java/org/springblade/modules/discuss/excel/UserTopicsExcel.java

@@ -1,5 +1,6 @@
package org.springblade.modules.discuss.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
@@ -28,34 +29,29 @@

    private static final long serialVersionUID = 2L;

    @ExcelProperty( "姓名")
    @ExcelProperty( "业主姓名")
    private String name;

    @ExcelProperty( "手机号")
    @ExcelProperty( "业主电话")
    private String phone;

    @ExcelProperty( "地块")
    private String districtName;

    @ExcelProperty( "栋号")
    private String building;

    @ExcelProperty( "房号")
    private String room;

    @ExcelProperty( "地址")
    private String addressName;

    @ExcelProperty( "小区")
    private String aoiName;

    @ExcelProperty( "面积")
    private String area;
    private BigDecimal area;

    @ExcelProperty( "地块")
    private String remark;

    @ExcelProperty( "投票项")
    @ExcelProperty( "候选人")
    private String optionContent;

    @ExcelProperty( "houseCode")
    private String houseCode;


    @ExcelProperty(value = "创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;


}

 src/main/java/org/springblade/modules/discuss/excel/holdExcel.java

File was renamed from src/main/java/org/springblade/modules/discuss/excel/householdExcel.java
@@ -1,15 +1,14 @@
package org.springblade.modules.discuss.excel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

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

/**
 * HouseExcel
@@ -20,7 +19,7 @@
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class householdExcel implements Serializable {
public class holdExcel implements Serializable {

    private static final long serialVersionUID = 2L;

@@ -31,7 +30,7 @@
    private String phone;

    @ExcelProperty( "地块")
    private String remark;
    private String districtName;

    @ExcelProperty( "栋号")
    private String building;
@@ -41,7 +40,7 @@


    @ExcelProperty( "地址")
    private String addressName;
    private String houseName;

//    @ExcelProperty( "小区")
//    private String aoiName;
@@ -58,8 +57,9 @@
    @ExcelProperty( "议事规则")
    private String rulesOfProcedure;

//    @ExcelProperty( "投票项")
//    private String optionContent;
    @ExcelProperty( "投票项")
    @ExcelIgnore
    private String optionContent;


//    @ExcelProperty(value = "创建时间")

 src/main/java/org/springblade/modules/discuss/mapper/UserTopicsMapper.java

@@ -20,11 +20,10 @@
import org.springblade.modules.discuss.dto.UserTopicsDTO;
import org.springblade.modules.discuss.entity.UserTopicsEntity;
import org.springblade.modules.discuss.excel.UserTopicsExcel;
import org.springblade.modules.discuss.excel.householdExcel;
import org.springblade.modules.discuss.excel.holdExcel;
import org.springblade.modules.discuss.vo.UserTopicsVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springblade.modules.house.vo.HouseholdVO;

import java.util.List;

@@ -59,5 +58,5 @@

    UserTopicsVO getresult(UserTopicsVO userTopics);

    List<householdExcel> getHouseholdList(UserTopicsVO userTopics);
    List<holdExcel> getHouseholdList(UserTopicsVO userTopics);
}

 src/main/java/org/springblade/modules/discuss/mapper/UserTopicsMapper.xml

@@ -190,7 +190,10 @@
        jhs.remark,
        jda.address_code houseCode,
        jda.aoi_name,
        jt.option_content
        jt.option_content,
        jhs.building,
        jhs.room,
        jhs.district_name
        FROM
        jczz_user_topics as jut
        LEFT JOIN jczz_household jh ON jh.id = jut.household_id and jh.is_deleted = 0
@@ -199,6 +202,7 @@
        LEFT JOIN blade_user bu ON jh.associated_user_id = bu.id AND bu.is_deleted = 0
        LEFT JOIN jczz_house jhs on jhs.house_code= jda.address_code
        <where>
            and jt.discuss_content = '候选人'
            <if test="id != null ">and jut.id = #{id}</if>
            <if test="name != null and name != ''">
                and bu.name like concat('%',#{name},'%')
@@ -226,29 +230,34 @@
        </where>

    </select>
    <select id="getHouseholdList" resultType="org.springblade.modules.discuss.excel.householdExcel">
    <select id="getHouseholdList" resultType="org.springblade.modules.discuss.excel.holdExcel">
        SELECT
        DISTINCT(jh.id),
        jh.*,
        jh.room,
        jh.building,
        jh.house_name,
        jh.district_name,
        (
        SELECT
        group_concat( jt.option_content ) AS optionContent
        group_concat( jt.id ) AS optionContent
        FROM
        jczz_user_topics jut
        LEFT JOIN jczz_topics jt ON jt.id = jut.topics_id
        WHERE
        jut.house_code = jh.house_code
        AND jut.article_id = #{articleId}
        ) selectd
        ) optionContent,
        (SELECT jhd.name from jczz_household jhd where  jhd.house_code = jh.house_code AND jhd.is_deleted = 0 and jhd.relationship = 1 limit 1 ) name,
        (SELECT jhd.phone_number from jczz_household jhd where  jhd.house_code = jh.house_code AND jhd.is_deleted = 0 and jhd.relationship = 1 limit 1 ) phone
        FROM
        jczz_house jh
        LEFT JOIN jczz_household jhd ON jhd.house_code = jh.house_code
        AND jh.is_deleted = 0
        LEFT JOIN jczz_user_topics jut on jut.household_id = jhd.id
        <where>
            <if test="districtIdList != null and districtIdList.size() > 0">
            <if test="aoiCodeList != null and aoiCodeList.size() > 0">
                and jh.district_code in
                <foreach collection="districtIdList" item="code" open="(" close=")" separator=",">
                <foreach collection="aoiCodeList" item="code" open="(" close=")" separator=",">
                    #{code}
                </foreach>
            </if>

 src/main/java/org/springblade/modules/discuss/service/impl/UserTopicsServiceImpl.java

@@ -16,9 +16,9 @@
 */
package org.springblade.modules.discuss.service.impl;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
@@ -28,18 +28,18 @@
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.jetbrains.annotations.Nullable;
import org.springblade.common.constant.CommonConstant;
import org.springblade.common.utils.SpringUtils;
import org.springblade.core.redis.cache.BladeRedis;
import org.springblade.core.secure.utils.AuthUtil;
import org.springblade.core.tool.utils.Func;
import org.springblade.core.tool.utils.SpringUtil;
import org.springblade.modules.discuss.entity.PublicDiscussEntity;
import org.springblade.modules.discuss.entity.TopicsEntity;
import org.springblade.modules.discuss.entity.UserTopicsEntity;
import org.springblade.modules.discuss.excel.UserTopicsExcel;
import org.springblade.modules.discuss.excel.householdExcel;
import org.springblade.modules.discuss.excel.holdExcel;
import org.springblade.modules.discuss.mapper.UserTopicsMapper;
import org.springblade.modules.discuss.service.IPublicDiscussService;
import org.springblade.modules.discuss.service.ITopicsService;
@@ -48,12 +48,9 @@
import org.springblade.modules.discuss.vo.UserTopicsVO;
import org.springblade.modules.district.entity.DistrictEntity;
import org.springblade.modules.district.service.IDistrictService;
import org.springblade.modules.house.entity.HouseEntity;
import org.springblade.modules.house.entity.HouseholdEntity;
import org.springblade.modules.house.excel.HouseHoldExcel;
import org.springblade.modules.house.service.IHouseService;
import org.springblade.modules.house.service.IHouseholdService;
import org.springblade.modules.house.vo.HouseholdVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -109,17 +106,17 @@
    @Override
    @Transactional(rollbackFor = Exception.class)
    public String batchSave(TopicsVO topics) throws Exception {
        if (StringUtils.isNotBlank(topics.getPhone())) {
            Object validateCode = redisTemplate.get(SMS_VALIDATE_PHONE + topics.getPhone());
            if (validateCode == null) {
                return "验证码已过期";
            }
            if (!validateCode.toString().equals(topics.getCode())) {
                return "验证码错误";
            }
            //删除验证码
            redisTemplate.del(SMS_VALIDATE_PHONE + topics.getPhone());
        }
//        if (StringUtils.isNotBlank(topics.getPhone())) {
//            Object validateCode = redisTemplate.get(SMS_VALIDATE_PHONE + topics.getPhone());
//            if (validateCode == null) {
//                return "验证码已过期";
//            }
//            if (!validateCode.toString().equals(topics.getCode())) {
//                return "验证码错误";
//            }
//            //删除验证码
//            redisTemplate.del(SMS_VALIDATE_PHONE + topics.getPhone());
//        }
        // 判断是否一户一票 还是一人一票
        List<TopicsVO> topicsList = topics.getChildren();
        IHouseholdService householdService = SpringUtils.getBean(IHouseholdService.class);
@@ -267,85 +264,96 @@

    @Override
    public void handleExcel(OutputStream out, UserTopicsVO userTopics) {
        ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
        //设置内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//居中
        contentStyle.setWrapped(true);//自动换行
        //设置头部样式
        WriteCellStyle headerStyle = new WriteCellStyle();
        headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headerStyle, contentStyle);
        WriteSheet proposalSheet = EasyExcelFactory.writerSheet(0, "委员提案").head(HouseHoldExcel.class).registerWriteHandler(horizontalCellStyleStrategy).build();
        WriteSheet publicopinionSheet = EasyExcelFactory.writerSheet(1, "社情民意").head(HouseHoldExcel.class).registerWriteHandler(horizontalCellStyleStrategy).build();
        excelWriter.write(getProposal(userTopics), proposalSheet);
        excelWriter.write(getPublicopinion(userTopics), publicopinionSheet);

        // 设置excel工作簿
        ExcelWriter excelWriter = EasyExcel.write(out)
            .build();

//        List<householdExcel> userList = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            HouseHoldExcel houseHoldExcel = new HouseHoldExcel();
//            houseHoldExcel.setHouseCode(i+"1");
//        }
        //设置自动换行

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setWrapped(true);

        List<holdExcel> proposal = getProposal(userTopics);
        ExcelWriterSheetBuilder userExcelSheet = new ExcelWriterSheetBuilder();
        userExcelSheet.registerWriteHandler(new HorizontalCellStyleStrategy(null,contentWriteCellStyle));
        userExcelSheet.sheetName("小区投票人员");
        userExcelSheet.sheetNo(0);
        userExcelSheet.head(holdExcel.class);
        excelWriter.write(proposal, userExcelSheet.build());


//        List<householdExcel> classInfoList = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            HouseHoldExcel houseHoldExcel = new HouseHoldExcel();
//            houseHoldExcel.setHouseCode(i+"1");
//        }
        List<UserTopicsExcel> publicopinion = getPublicopinion(userTopics);
        ExcelWriterSheetBuilder classExcelSheet = new ExcelWriterSheetBuilder();
        classExcelSheet.registerWriteHandler(new HorizontalCellStyleStrategy(null,contentWriteCellStyle));
        classExcelSheet.sheetName("投票记录");
        classExcelSheet.sheetNo(1);
        classExcelSheet.head(UserTopicsExcel.class);
        excelWriter.write(publicopinion, classExcelSheet.build());

        excelWriter.finish();


//        ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
//        //设置内容样式
//        WriteCellStyle contentStyle = new WriteCellStyle();
//        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//居中
//        contentStyle.setWrapped(true);//自动换行
//        //设置头部样式
//        WriteCellStyle headerStyle = new WriteCellStyle();
//        headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        //设置策略
//        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headerStyle, contentStyle);
//        WriteSheet proposalSheet = EasyExcelFactory.writerSheet(0, "委员提案").head(HouseHoldExcel.class).registerWriteHandler(horizontalCellStyleStrategy).build();
//        WriteSheet publicopinionSheet = EasyExcelFactory.writerSheet(1, "社情民意").head(HouseHoldExcel.class).registerWriteHandler(horizontalCellStyleStrategy).build();
//        excelWriter.write(getProposal(userTopics), proposalSheet);
//        excelWriter.write(getPublicopinion(userTopics), publicopinionSheet);


    }

    //首页-房屋记录
    private List<householdExcel> getProposal(UserTopicsVO userTopics) {
        List<householdExcel> list = new ArrayList<>();
    private List<holdExcel> getProposal(UserTopicsVO userTopics) {
        List<holdExcel> list = new ArrayList<>();
        JSONArray objects1 = JSON.parseArray(userTopics.getDistrictId());
        List<String> collect1 = objects1.stream().map(item -> (String)item).collect(Collectors.toList());
        List<String> collect1 = objects1.stream().map(item -> (String) item).collect(Collectors.toList());
        IDistrictService bean3 = SpringUtils.getBean(IDistrictService.class);
        List<DistrictEntity> list3 = bean3.list(Wrappers.<DistrictEntity>lambdaQuery().in(DistrictEntity::getId, collect1));
        List<String> aoiCodeList = list3.stream().map(item -> item.getAoiCode() ).collect(Collectors.toList());
        List<String> aoiCodeList = list3.stream().map(item -> item.getAoiCode()).collect(Collectors.toList());
        userTopics.setAoiCodeList(aoiCodeList);
        List<householdExcel> householdList = baseMapper.getHouseholdList(userTopics);
//
//        householdExcel vo = new householdExcel();
//        list.add(vo);
//        IHouseService bean = SpringUtils.getBean(IHouseService.class);
//        JSONArray objects = JSON.parseArray(userTopics.getDistrictId());
//
//        List<HouseEntity> list1 = bean.list(Wrappers.<HouseEntity>lambdaQuery().in(HouseEntity::getDistrictCode, aoiCodeList));
//
//        IHouseholdService bean1 = SpringUtils.getBean(IHouseholdService.class);
//
//        IUserTopicsService bean2 = SpringUtils.getBean(IUserTopicsService.class);
//
//        for (HouseEntity houseEntity : list1) {
//            householdExcel householdExcel = new householdExcel();
//            householdExcel.setArea(houseEntity.getArea());
//            householdExcel.setBuilding(houseEntity.getBuilding());
//            householdExcel.setRoom(houseEntity.getRoom());
//            householdExcel.setRemark(houseEntity.getRemark());
//            // 查询住户
//            List<HouseholdEntity> list2 = bean1.list(Wrappers.<HouseholdEntity>lambdaQuery()
//                .eq(HouseholdEntity::getHouseCode, houseEntity.getHouseCode())
//                .eq(HouseholdEntity::getIsDeleted, 0)
//                .ne(HouseholdEntity::getRelationship, 18));
//            // 判断住户是否有投票
//            for (HouseholdEntity householdEntity : list2) {
//                householdExcel.setName(householdEntity.getName());
//                householdExcel.setPhone(householdEntity.getPhoneNumber());
//                long count = bean2.count(Wrappers.<UserTopicsEntity>lambdaQuery()
//                    .eq(UserTopicsEntity::getHouseholdId, householdEntity.getId()));
//                if (count > 0) {
//                    // 查询选择的结果
//                    userTopics.setDiscussContent("管理规约");
//                    userTopics.setHouseholdId(householdEntity.getId());
//                    UserTopicsVO getresult = baseMapper.getresult(userTopics);
//                    if (getresult != null) {
//                        householdExcel.setManagementRegulations(getresult.getOptionContent());
//                    }
//                    userTopics.setDiscussContent("议事规则");
//                    UserTopicsVO getresult2 = baseMapper.getresult(userTopics);
//                    if (getresult2 != null) {
//                        householdExcel.setRulesOfProcedure(getresult2.getOptionContent());
//                    }
//                    householdExcel.setVoteFlag("是");
//                    break;
//                } else {
//                    householdExcel.setVoteFlag("否");
//                }
//            }
            // 添加
            list.addAll(householdList);
//        }
        List<holdExcel> householdList = baseMapper.getHouseholdList(userTopics);
        householdList.stream().forEach(item -> {
            if (StringUtils.isBlank(item.getOptionContent())) {
                item.setVoteFlag("否");
            } else {
                ITopicsService bean = SpringUtils.getBean(ITopicsService.class);

                List<Long> longs = Func.toLongList(item.getOptionContent());
                for (Long aLong : longs) {
                    TopicsEntity topics = bean.getById(aLong);
                    if (StringUtils.isNotBlank(topics.getDiscussContent()) && topics.getDiscussContent().equals("管理规约")) {
                        item.setManagementRegulations(topics.getOptionContent());
                    }
                    if (StringUtils.isNotBlank(topics.getDiscussContent()) && topics.getDiscussContent().equals("议事规则")) {
                        item.setRulesOfProcedure(topics.getOptionContent());
                    }
                }

                item.setVoteFlag("是");
            }
        });
        // 添加
        list.addAll(householdList);
        return list;
    }

@@ -353,8 +361,6 @@
    private List<UserTopicsExcel> getPublicopinion(UserTopicsVO userTopics) {
        List<UserTopicsExcel> list = new ArrayList<>();
        List<UserTopicsExcel> userTopicsExcels = baseMapper.getresultTwo(userTopics);
        UserTopicsExcel vo = new UserTopicsExcel();
        list.add(vo);
        list.addAll(userTopicsExcels);
        return list;
    }