党员统计接口开发

luoyuanqiao

2022-02-24 263f08d4fc97380549c10e50f8207107a49a9391

党员统计接口开发

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/partybuilding/ComDataStatisticsOrgDto.java

New file
@@ -0,0 +1,35 @@
package com.panzhihua.common.model.dtos.partybuilding;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @author lyq
 * 查询党建数据统计请求参数
 */
@Data
@ApiModel("查询党建数据统计请求参数")
public class ComDataStatisticsOrgDto {

    @ApiModelProperty(value = "党员列表分页参数-当前页数")
    private Long pageNum = 1L;

    @ApiModelProperty(value = "党员列表分页参数-每页记录数")
    private Long pageSize = 10L;

    @ApiModelProperty(value = "党组织id",required = true)
    private Long orgId;

    @ApiModelProperty(value = "查询年份",required = true)
    private Integer year;

    @ApiModelProperty(value = "查询开始时间",hidden = true)
    private String startTime;

    @ApiModelProperty(value = "查询结束时间",hidden = true)
    private String endTime;

    @ApiModelProperty(value = "社区id", hidden = true)
    private Long communityId;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/partybuilding/ComDataStatisticsOrgVo.java

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

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.List;

/**
 * @author lyq
 * 查询党建数据统计党组织返回参数
 */
@Data
@ApiModel("查询党建数据统计党组织返回参数")
public class ComDataStatisticsOrgVo {

    @ApiModelProperty(value = "党员人数")
    private Integer partyMemberNum;

    @ApiModelProperty(value = "正式党员人数")
    private Integer formalPartyMemberNum;

    @ApiModelProperty(value = "预备党员人数")
    private Integer preparePartyMemberNum;

    @ApiModelProperty(value = "体育类党员人数")
    private Integer tyNum;

    @ApiModelProperty(value = "美术类党员人数")
    private Integer msNum;

    @ApiModelProperty(value = "音乐类党员人数")
    private Integer yyNum;

    @ApiModelProperty(value = "舞蹈类党员人数")
    private Integer wdNum;

    @ApiModelProperty(value = "其他类党员人数")
    private Integer qtNum;

    @ApiModelProperty(value = "无类型党员人数")
    private Integer wuNum;

    @ApiModelProperty(value = "党员列表")
    private List<PartyBuildingMemberVO> partyMemberList;

    @ApiModelProperty(value = "参与活动次数")
    private Integer participateActivityNum;

    @ApiModelProperty(value = "参与活动时长")
    private Integer participateActivityDuration;

    @ApiModelProperty(value = "参与志愿者活动次数")
    private Integer participateVolunteerActivityNum;

    @ApiModelProperty(value = "参与志愿者活动时长")
    private Integer participateVolunteerActivityDuration;

    @ApiModelProperty(value = "参与党员活动次数")
    private Integer participatePartyActivityNum;

    @ApiModelProperty(value = "参与党员活动时长")
    private Integer participatePartyActivityDuration;

    @ApiModelProperty(value = "月份统计list")
    private List<String> monthList;

    @ApiModelProperty(value = "完成微心愿折线统计数据")
    private List<String> completeWishList;

    @ApiModelProperty(value = "累计微心愿折线统计数据")
    private List<String> cumulativeWishList;

    @ApiModelProperty(value = "完成随手拍折线统计数据")
    private List<String> completeEasyList;

    @ApiModelProperty(value = "累计随手拍折线统计数据")
    private List<String> cumulativeEasyList;

    @ApiModelProperty(value = "完成微心愿数量")
    private Integer wishNum;

    @ApiModelProperty(value = "完成随手拍数量")
    private Integer easyNum;
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/partybuilding/PartyBuildingService.java

@@ -547,4 +547,12 @@
     */
    @PostMapping("/partybuildIng/pageDataStatisticsMember")
    R pageDataStatisticsMember(@RequestBody PageComDataStatisticsMemberDto statisticsMemberDto);

    /**
     * 根据组织id查询组织下统计数据
     * @param statisticsOrgDto  请求参数
     * @return  组织下统计数据
     */
    @PostMapping("/partybuildIng/getOrgDataStatistics")
    R getOrgDataStatistics(@RequestBody ComDataStatisticsOrgDto statisticsOrgDto);
}

 springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/utlis/DateUtils.java

@@ -1038,4 +1038,95 @@
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        return format_yyymmdd.format(calendar.getTime());
    }

    /**
     * 获取某年的月份最后一天时间
     * @param year  年份
     * @return  月份list
     */
    public static List<String> getYearLastMonths(Integer year){
        List<String> list = new ArrayList<>();
        list.add(getLastDayOfMonth(year,1));
        list.add(getLastDayOfMonth(year,2));
        list.add(getLastDayOfMonth(year,3));
        list.add(getLastDayOfMonth(year,4));
        list.add(getLastDayOfMonth(year,5));
        list.add(getLastDayOfMonth(year,6));
        list.add(getLastDayOfMonth(year,7));
        list.add(getLastDayOfMonth(year,8));
        list.add(getLastDayOfMonth(year,9));
        list.add(getLastDayOfMonth(year,10));
        list.add(getLastDayOfMonth(year,11));
        list.add(getLastDayOfMonth(year,12));
        return list;
    }

    /**
     * 获取某年的月份第一天时间
     * @param year  年份
     * @return  月份list
     */
    public static List<String> getYearFirstMonths(Integer year){
        List<String> list = new ArrayList<>();
        list.add(getFirstDayOfMonth(year,1));
        list.add(getFirstDayOfMonth(year,2));
        list.add(getFirstDayOfMonth(year,3));
        list.add(getFirstDayOfMonth(year,4));
        list.add(getFirstDayOfMonth(year,5));
        list.add(getFirstDayOfMonth(year,6));
        list.add(getFirstDayOfMonth(year,7));
        list.add(getFirstDayOfMonth(year,8));
        list.add(getFirstDayOfMonth(year,9));
        list.add(getFirstDayOfMonth(year,10));
        list.add(getFirstDayOfMonth(year,11));
        list.add(getFirstDayOfMonth(year,12));
        return list;
    }

    /**
     * 获取某年某月最后一天
     * @param year  年份
     * @param month 月份
     * @return  某年某月最后一天
     */
    public static String getLastDayOfMonth(int year,int month)
    {
        Calendar cal = Calendar.getInstance();
        //设置年份
        cal.set(Calendar.YEAR,year);
        //设置月份
        cal.set(Calendar.MONTH, month-1);
        //获取某月最大天数
        int lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        //设置日历中月份的最大天数
        cal.set(Calendar.DAY_OF_MONTH, lastDay);
        //格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String lastDayOfMonth = sdf.format(cal.getTime());
        return lastDayOfMonth;
    }

    /**
     *  获取某年某月的第一天
     * @param year  年份
     * @param month 月份
     * @return  某年某月第一天
     */
    public static String getFirstDayOfMonth(int year,int month)
    {
        Calendar cal = Calendar.getInstance();
        //设置年份
        cal.set(Calendar.YEAR,year);
        //设置月份
        cal.set(Calendar.MONTH, month-1);
        //获取某月最小天数
        int firstDay = cal.getActualMinimum(Calendar.DAY_OF_MONTH);
        //设置日历中月份的最小天数
        cal.set(Calendar.DAY_OF_MONTH, firstDay);
        //格式化日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String firstDayOfMonth = sdf.format(cal.getTime());

        return firstDayOfMonth;
    }
}

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

@@ -11,6 +11,7 @@
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.panzhihua.common.model.dtos.partybuilding.ComDataStatisticsOrgDto;
import com.panzhihua.common.model.dtos.partybuilding.PageComDataStatisticsMemberDto;
import com.panzhihua.common.model.vos.user.SysTemplateConfigVO;
import com.panzhihua.common.service.partybuilding.ComDataStatisticsFeign;
@@ -792,4 +793,10 @@
        statisticsMemberDto.setCommunityId(this.getCommunityId());
        return partyBuildingService.pageDataStatisticsMember(statisticsMemberDto);
    }

    @ApiOperation(value = "党员数据统计-根据党组织统计数据",response = ComDataStatisticsOrgVo.class)
    @PostMapping("/dataStatistics/org")
    public R getOrgDataStatistics(@RequestBody ComDataStatisticsOrgDto statisticsOrgDto) {
        return partyBuildingService.getOrgDataStatistics(statisticsOrgDto);
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/java/com/panzhihua/service_dangjian/api/PartyBuildIngApi.java

@@ -715,4 +715,9 @@
    public R pageDataStatisticsMember(@RequestBody PageComDataStatisticsMemberDto statisticsMemberDto) {
        return comPbMemberService.pageDataStatisticsMember(statisticsMemberDto);
    }

    @PostMapping("/getOrgDataStatistics")
    public R getOrgDataStatistics(@RequestBody ComDataStatisticsOrgDto statisticsOrgDto) {
        return comPbMemberService.getOrgDataStatistics(statisticsOrgDto);
    }
}

 springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/java/com/panzhihua/service_dangjian/dao/ComPbMemberDAO.java

@@ -1,6 +1,7 @@
package com.panzhihua.service_dangjian.dao;

import java.util.List;
import java.util.Map;

import com.panzhihua.common.model.dtos.partybuilding.PageComDataStatisticsMemberDto;
import com.panzhihua.common.model.vos.partybuilding.*;
@@ -116,4 +117,42 @@
     * @return  人口活动统计数据
     */
    ComDataStatisticsMemberVo getMemberStatistics(@Param("userId") Long userId,@Param("communityId") Long communityId);

    /**
     * 查询社区党组织下的统计数据
     * @param communityId   社区id
     * @param orgIds    党组织id集合
     * @return  社区党组织下统计数据
     */
    ComDataStatisticsOrgVo getOrgDataStatistics(@Param("communityId") Long communityId,@Param("orgIds") List<Long> orgIds);

    /**
     * 查询社区党组织左下统计数据
     * @param communityId   社区id
     * @param orgIds    党组织id集合
     * @param startTime 查询开始时间
     * @param endTime   查询结束时间
     * @return  社区党组织左下统计数据
     */
    ComDataStatisticsOrgVo getOrgDataStatisticsLeftDown(@Param("communityId") Long communityId,@Param("orgIds") List<Long> orgIds
            ,@Param("startTime") String startTime,@Param("endTime") String endTime);

    /**
     * 分页查询社区党组织下党员
     * @param page   分页参数
     * @param communityId   社区id
     * @param orgIds    党组织id集合
     * @return  党员列表
     */
    IPage<PartyBuildingMemberVO> getOrgDataStatisticsMember(Page page,@Param("communityId") Long communityId,@Param("orgIds") List<Long> orgIds);

    /**
     * 查询社区党组织下某个时间段统计数据
     * @param communityId   社区id
     * @param orgIds    党组织id集合
     * @param startDate  查询开始时间
     * @param endDate  查询结束时间
     * @return  社区党组织下某个时间段统计数据
     */
    Map<String,Object> getOrgDataStatisticsRightDown(@Param("communityId") Long communityId,@Param("orgIds") List<Long> orgIds,@Param("startDate") String startDate,@Param("endDate") String endDate);
}

 springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/java/com/panzhihua/service_dangjian/service/ComPbMemberService.java

@@ -3,6 +3,7 @@
import java.util.List;

import com.baomidou.mybatisplus.extension.service.IService;
import com.panzhihua.common.model.dtos.partybuilding.ComDataStatisticsOrgDto;
import com.panzhihua.common.model.dtos.partybuilding.PageComDataStatisticsMemberDto;
import com.panzhihua.common.model.vos.R;
import com.panzhihua.common.model.vos.partybuilding.*;
@@ -200,4 +201,6 @@
     * @return  党员数据统计
     */
    R pageDataStatisticsMember(PageComDataStatisticsMemberDto statisticsMemberDto);

    R getOrgDataStatistics(ComDataStatisticsOrgDto statisticsOrgDto);
}

 springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/java/com/panzhihua/service_dangjian/service/impl/ComPbMemberServiceImpl.java

@@ -3,11 +3,14 @@
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.annotation.Resource;

import com.panzhihua.common.model.dtos.partybuilding.ComDataStatisticsOrgDto;
import com.panzhihua.common.model.dtos.partybuilding.PageComDataStatisticsMemberDto;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.beans.BeanUtils;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Service;
@@ -687,4 +690,75 @@
        return R.ok(memberPage);
    }

    @Override
    public R getOrgDataStatistics(ComDataStatisticsOrgDto statisticsOrgDto) {
        List<Long> orgIds = new ArrayList<>();
        Long communityId = statisticsOrgDto.getCommunityId();
        ComDataStatisticsOrgVo statisticsOrgVo = new ComDataStatisticsOrgVo();
        ComPbOrgDO pbOrgDO = comPbOrgDAO.selectById(statisticsOrgDto.getOrgId());
        if(pbOrgDO == null){
            return R.fail("未查询到党组织");
        }
        //拼接党组织id
        if(pbOrgDO.getOneId() != null){
            orgIds.add(pbOrgDO.getOneId());
        }
        if(pbOrgDO.getTwoId() != null){
            orgIds.add(pbOrgDO.getTwoId());
        }
        if(pbOrgDO.getThirdId() != null){
            orgIds.add(pbOrgDO.getThirdId());
        }
        if(pbOrgDO.getFourId() != null){
            orgIds.add(pbOrgDO.getFourId());
        }
        if(pbOrgDO.getFiveId() != null){
            orgIds.add(pbOrgDO.getFiveId());
        }
        //拼接查询开始结束时间
        String startTime = statisticsOrgDto.getYear() + "-01-01 00:00:00";
        String endTime = statisticsOrgDto.getYear() + "-12-31 23:59:59";

        ComDataStatisticsOrgVo statisticsVo = new ComDataStatisticsOrgVo();
        //查询统计左上数据
        statisticsVo = comPbMemberDAO.getOrgDataStatistics(communityId,orgIds);
        if(statisticsVo != null){
            BeanUtils.copyProperties(statisticsVo,statisticsOrgVo);
        }

        //查询左下数据
        statisticsVo = comPbMemberDAO.getOrgDataStatisticsLeftDown(communityId,orgIds,startTime,endTime);
        if(statisticsVo != null){
            BeanUtils.copyProperties(statisticsVo,statisticsOrgVo);
        }

        //查询党员数据
        IPage<PartyBuildingMemberVO> partyMemberPage = comPbMemberDAO.getOrgDataStatisticsMember(new Page(statisticsOrgDto.getPageNum()
                ,statisticsOrgDto.getPageSize()),communityId,orgIds);
        if(partyMemberPage.getRecords() != null && !partyMemberPage.getRecords().isEmpty()){
            statisticsOrgVo.setPartyMemberList(partyMemberPage.getRecords());
        }

        List<String> startDateList = DateUtils.getYearFirstMonths(statisticsOrgDto.getYear());
        List<String> endDateList = DateUtils.getYearLastMonths(statisticsOrgDto.getYear());
        List<String> completeWishList = new ArrayList<>();
        List<String> cumulativeWishList = new ArrayList<>();
        List<String> completeEasyList = new ArrayList<>();
        List<String> cumulativeEasyList = new ArrayList<>();
        for (int i = 0; i < startDateList.size(); i++) {
            Map<String,Object> statisticsMap = comPbMemberDAO.getOrgDataStatisticsRightDown(communityId,orgIds, startDateList.get(i), endDateList.get(i));
            if(statisticsMap != null){
                completeWishList.add(statisticsMap.get("completeWishNum").toString());
                cumulativeWishList.add(statisticsMap.get("cumulativeWishNum").toString());
                completeEasyList.add(statisticsMap.get("completeEasyNum").toString());
                cumulativeEasyList.add(statisticsMap.get("completeEasyNum").toString());
            }
        }
        statisticsOrgVo.setCompleteEasyList(completeEasyList);
        statisticsOrgVo.setCompleteWishList(completeWishList);
        statisticsOrgVo.setCumulativeWishList(cumulativeWishList);
        statisticsOrgVo.setCumulativeEasyList(cumulativeEasyList);
        return R.ok(statisticsOrgVo);
    }

}

 springcloud_k8s_panzhihuazhihuishequ/service_partybuilding/src/main/resources/mapper/ComPbMemberMapper.xml

@@ -214,5 +214,241 @@
        WHERE
            cpm.user_id = #{userId}
    </select>

    <select id="getOrgDataStatistics" resultType="com.panzhihua.common.model.vos.partybuilding.ComDataStatisticsOrgVo">
        SELECT
            count( id ) AS tyNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND specialty_category = 2
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS wdNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND specialty_category = 3
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS yyNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND specialty_category = 4
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS msNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND specialty_category = 5
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS qtNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND specialty_category IS NULL
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS wuNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND type = 1
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS preparePartyMemberNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND type = 2
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS formalPartyMemberNum,
            ( SELECT count( id ) FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId}
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS partyMemberNum
        FROM
            com_pb_member
        WHERE
            audit_result = 1
            AND community_id = #{communityId}
            AND specialty_category = 1
            AND org_id in
            <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
    </select>

    <select id="getOrgDataStatisticsLeftDown" resultType="com.panzhihua.common.model.vos.partybuilding.ComDataStatisticsOrgVo">
        SELECT
            count( id ) AS participateVolunteerActivityNum,
            (
            SELECT
                IFNULL( sum( caa.duration ), 0 )
            FROM
                com_act_act_sign AS caas
                LEFT JOIN com_act_activity AS caa ON caa.id = caas.activity_id
            WHERE
                caas.`status` = 1
                AND caas.is_volunteer = 1
                AND caas.user_id IN ( SELECT user_id FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND user_id IS NOT NULL
                    AND org_id in
                    <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                )
                AND caas.create_at BETWEEN #{startTime}
                AND #{endTime}
            ) AS participateVolunteerActivityDuration,
            (
            SELECT
                count( cpam.id )
            FROM
                com_pb_activity_member AS cpam
                LEFT JOIN com_pb_member AS cpm ON cpm.id = cpam.member_id
            WHERE
                cpm.audit_result = 1
                AND cpm.community_id = #{communityId}
                AND cpam.create_at BETWEEN #{startTime}
                AND #{endTime}
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            ) AS participatePartyActivityNum
        FROM
            com_act_act_sign
        WHERE
            `status` = 1
            AND is_volunteer = 1
            AND user_id IN ( SELECT user_id FROM com_pb_member WHERE audit_result = 1 AND community_id = #{communityId} AND user_id IS NOT NULL
                AND org_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            )
            AND create_at BETWEEN #{startTime}
            AND #{endTime}
    </select>

    <select id="getOrgDataStatisticsMember" resultType="com.panzhihua.common.model.vos.partybuilding.PartyBuildingMemberVO">
        select
        m.id, m.org_id, m.name, m.id_card, m.photo_path, m.join_time, m.employment_time, m.audit_result, m.create_at, m.update_at,
        m.community_id, m.user_id, m.refuse_reason, m.phone, m.type, m.position, m.check_unit_id, m.function, m.specialty_category,
        m.specialty_name, m.position_two, o.`name` orgName,cpcu.name as checkUnitName,
        TIMESTAMPDIFF(
        YEAR,
        m.employment_time,
        DATE_FORMAT( NOW(), '%Y-%m-%d' )) as partyAge,
        CASE

        WHEN u.id_card IS NULL THEN
        '未注册' ELSE '已注册'
        END status
        from com_pb_member as m
        LEFT JOIN sys_user u ON m.id_card = u.id_card
        LEFT JOIN com_pb_org o ON m.org_id = o.id
        LEFT JOIN com_pb_check_unit as cpcu ON m.check_unit_id = cpcu.id
        <where>
            and m.audit_result = 1
            and m.community_id = #{communityId}
            and m.org_id in
            <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </where>
    </select>

    <select id="getOrgDataStatisticsRightDown" resultType="map">
        SELECT
            count( camw.id ) AS completeWishNum,(
            SELECT
                count( camw.id )
            FROM
                com_act_micro_wish AS camw
            WHERE
                camw.community_id = #{communityId}
                AND camw.`status` = 6
                AND caep.evaluate_at <![CDATA[ <= ]]> #{endDate}
                AND sponsor_id IN (
                SELECT
                    user_id
                FROM
                    com_pb_member
                WHERE
                    community_id = #{communityId}
                    AND audit_result = 1
                    AND user_id IS NOT NULL
                AND org_id IN
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
                )) AS cumulativeWishNum,
            (SELECT
                count( caep.id )
            FROM
                com_act_easy_photo AS caep
            WHERE
                caep.community_id = #{communityId}
                AND caep.`status` >= 4
                AND caep.evaluate_at <![CDATA[ <= ]]> #{endDate}
                AND caep.evaluate_at <![CDATA[ >= ]]> #{startDate}
                AND caep.sponsor_id IN (
                SELECT
                    user_id
                FROM
                    com_pb_member
                WHERE
                    community_id = #{communityId}
                    AND audit_result = 1
                    AND user_id IS NOT NULL
                AND org_id IN
        <foreach collection="orgIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
                ) )    as completeEasyNum,
                (SELECT
                count( caep.id )
            FROM
                com_act_easy_photo AS caep
            WHERE
                caep.community_id = #{communityId}
                AND caep.`status` >= 4
                AND camw.evaluate_at <![CDATA[ <= ]]> #{endDate}
                AND caep.sponsor_id IN (
                SELECT
                    user_id
                FROM
                    com_pb_member
                WHERE
                    community_id = #{communityId}
                    AND audit_result = 1
                    AND user_id IS NOT NULL
                AND org_id IN
        <foreach collection="orgIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
                ) ) as cumulativeEasyNum
        FROM
            com_act_micro_wish AS camw
        WHERE
            camw.community_id = #{communityId}
            AND camw.`status` = 6
            AND camw.evaluate_at <![CDATA[ <= ]]> #{endDate}
            AND camw.evaluate_at <![CDATA[ >= ]]> #{startDate}
            AND sponsor_id IN (
            SELECT
                user_id
            FROM
                com_pb_member
            WHERE
                community_id = #{communityId}
                AND audit_result = 1
            AND user_id IS NOT NULL
            AND org_id IN
        <foreach collection="orgIds" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
            )
    </select>
</mapper>