package com.panzhihua.service_community.dao;
|
|
import java.util.List;
|
import java.util.Map;
|
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import com.panzhihua.common.model.dtos.community.easyPhoto.ExportEasyPhotoDTO;
|
import com.panzhihua.common.model.vos.community.ComActEasyPhotoVO;
|
import com.panzhihua.common.model.vos.community.TodoEventsVO;
|
import com.panzhihua.common.model.vos.community.easyPhoto.ComActEasyPhotoCountVO;
|
import com.panzhihua.common.model.vos.community.easyPhoto.ComActEasyPhotoStatisticsVO;
|
import com.panzhihua.common.model.vos.community.easyPhoto.ExportEasyPhotoVO;
|
import com.panzhihua.common.model.vos.screen.EastPhotoTypeVO;
|
import com.panzhihua.common.model.vos.screen.EastPhotoVO;
|
import com.panzhihua.service_community.model.dos.ComActEasyPhotoDO;
|
|
/**
|
* @program: springcloud_k8s_panzhihuazhihuishequ
|
* @description: 随手拍
|
* @author: huang.hongfa weixin hhf9596 qq 959656820
|
* @create: 2020-12-07 14:22
|
**/
|
@Mapper
|
public interface ComActEasyPhotoDAO extends BaseMapper<ComActEasyPhotoDO> {
|
@Select("<script> " + "select t.* from (" + "SELECT " + "p.id, " + "u.`name` sponsor_name, " + "u.phone phone, "
|
+ "count(pu.id)giveThumbsUpNum, " + "u.image_url, " + "p.happen_addr, " + "su.`name` handler_name, "
|
+ "p.`status`, " + "p.`detail`, " + "p.`is_need_feed_back`, " + "p.`photo_path_list`, " + "p.create_at, "
|
+ "p.feedback_at, " + "p.is_report, " + "p.is_publicity, " + "p.activity_type, " + "p.activity_id, "
|
+ "p.activity_amount, " + "p.examine_at " + "FROM " + "com_act_easy_photo p "
|
+ "JOIN sys_user u ON p.sponsor_id = u.user_id "
|
+ "<if test='comActEasyPhotoVO.sponsorName != null and comActEasyPhotoVO.sponsorName.trim() != ""'>"
|
+ "AND u.`name` like concat(#{comActEasyPhotoVO.sponsorName},'%') " + " </if> "
|
+ "<if test='comActEasyPhotoVO.userId != null and comActEasyPhotoVO.userId != 0'>"
|
+ "AND u.`user_id` = #{comActEasyPhotoVO.userId} " + " </if> "
|
+ "left join com_act_easy_photo_user pu ON p.id = pu.easy_photo_id "
|
+ "<if test='comActEasyPhotoVO.handlerName == null or comActEasyPhotoVO.handlerName.trim() == ""'>"
|
+ "left JOIN sys_user su ON p.handler_id = su.user_id " + " </if> "
|
+ "<if test='comActEasyPhotoVO.handlerName != null and comActEasyPhotoVO.handlerName.trim() != ""'>"
|
+ " JOIN sys_user su ON p.handler_id = su.user_id " + " </if> "
|
+ "<if test='comActEasyPhotoVO.handlerName != null and comActEasyPhotoVO.handlerName.trim() != ""'>"
|
+ "AND su.`name` like concat(#{comActEasyPhotoVO.handlerName},'%') " + " </if> "
|
+ "where p.del_tag = 0 and p.status !=2"
|
+ "<if test='comActEasyPhotoVO.communityId != null and comActEasyPhotoVO.communityId != 0'>"
|
+ " and p.community_id=#{comActEasyPhotoVO.communityId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.happenAddr != null and comActEasyPhotoVO.happenAddr.trim() != ""'>"
|
+ "AND p.happen_addr like concat(#{comActEasyPhotoVO.happenAddr},'%') " + " </if> "
|
+ "<if test='comActEasyPhotoVO.status != null and comActEasyPhotoVO.status!=0' >"
|
+ "AND p.`status` = #{comActEasyPhotoVO.status} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.createBegin != null '>"
|
+ "AND p.create_at BETWEEN #{comActEasyPhotoVO.createBegin} " + "AND #{comActEasyPhotoVO.createEnd}"
|
+ " </if> " + "<if test='comActEasyPhotoVO.sponsorId != null and comActEasyPhotoVO.sponsorId != 0'>"
|
+ "AND p.sponsor_id =#{comActEasyPhotoVO.sponsorId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.activityType != null'>"
|
+ "AND p.activity_type =#{comActEasyPhotoVO.activityType} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.easyType != null and comActEasyPhotoVO.easyType == 1'>"
|
+ " AND (p.status = 1 or (p.status > 1 and is_need_feed_back = 0)) " + " </if> "
|
+ "<if test='comActEasyPhotoVO.easyType != null and comActEasyPhotoVO.easyType == 2'>"
|
+ " AND p.status > 1 and is_need_feed_back = 1 " + " </if> " + "group by p.id " +
|
|
"union all " +
|
|
"SELECT " + "p.id, " + "u.`name` sponsor_name, " + "u.phone phone, " + "count(pu.id)giveThumbsUpNum, "
|
+ "u.image_url, " + "p.happen_addr, " + "su.`name` handler_name, " + "p.`status`, " + "p.`detail`, "
|
+ "p.`is_need_feed_back`, " + "p.`photo_path_list`, " + "p.create_at, " + "p.feedback_at, " + "p.is_report, "
|
+ "p.is_publicity, " + "p.activity_type, " + "p.activity_amount, " + "p.activity_id, " + "p.examine_at "
|
+ "FROM " + "com_act_easy_photo p " + "JOIN sys_user u ON p.sponsor_id = u.user_id "
|
+ "<if test='comActEasyPhotoVO.sponsorName != null and comActEasyPhotoVO.sponsorName.trim() != ""'>"
|
+ "AND u.`name` like concat(#{comActEasyPhotoVO.sponsorName},'%') " + " </if> "
|
+ "<if test='comActEasyPhotoVO.userId != null and comActEasyPhotoVO.userId != 0'>"
|
+ "AND u.`user_id` = #{comActEasyPhotoVO.userId} " + " </if> "
|
+ "left join com_act_easy_photo_user pu ON p.id = pu.easy_photo_id "
|
+ "<if test='comActEasyPhotoVO.handlerName == null or comActEasyPhotoVO.handlerName.trim() == ""'>"
|
+ "left JOIN sys_user su ON p.handler_id = su.user_id " + " </if> "
|
+ "<if test='comActEasyPhotoVO.handlerName != null and comActEasyPhotoVO.handlerName.trim() != ""'>"
|
+ " JOIN sys_user su ON p.handler_id = su.user_id " + " </if> "
|
+ "<if test='comActEasyPhotoVO.handlerName != null and comActEasyPhotoVO.handlerName.trim() != ""'>"
|
+ "AND su.`name` like concat(#{comActEasyPhotoVO.handlerName},'%') " + " </if> "
|
+ "where p.del_tag = 0 and p.status=2 and p.is_need_feed_back=1 "
|
+ "<if test='comActEasyPhotoVO.backUserId != null and comActEasyPhotoVO.backUserId != 0'>"
|
+ "and p.handler_id=#{comActEasyPhotoVO.backUserId}" + " </if> "
|
+ "<if test='comActEasyPhotoVO.communityId != null and comActEasyPhotoVO.communityId != 0'>"
|
+ " and p.community_id=#{comActEasyPhotoVO.communityId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.happenAddr != null and comActEasyPhotoVO.happenAddr.trim() != ""'>"
|
+ "AND p.happen_addr like concat(#{comActEasyPhotoVO.happenAddr},'%') " + " </if> "
|
+ "<if test='comActEasyPhotoVO.status != null and comActEasyPhotoVO.status!=0' >"
|
+ "AND p.`status` = #{comActEasyPhotoVO.status} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.createBegin != null '>"
|
+ "AND p.create_at BETWEEN #{comActEasyPhotoVO.createBegin} " + "AND #{comActEasyPhotoVO.createEnd}"
|
+ " </if> " + "<if test='comActEasyPhotoVO.sponsorId != null and comActEasyPhotoVO.sponsorId != 0'>"
|
+ "AND p.sponsor_id =#{comActEasyPhotoVO.sponsorId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.activityType != null'>"
|
+ "AND p.activity_type =#{comActEasyPhotoVO.activityType} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.easyType != null and comActEasyPhotoVO.easyType == 1'>"
|
+ " AND (p.status = 1 or (p.status > 1 and is_need_feed_back = 0)) " + " </if> "
|
+ "<if test='comActEasyPhotoVO.easyType != null and comActEasyPhotoVO.easyType == 2'>"
|
+ " AND p.status > 1 and is_need_feed_back = 1 " + " </if> " + " group by p.id "
|
+ ")t order by t.create_at desc " + "</script>")
|
IPage<ComActEasyPhotoVO> pageEasyPhoto(Page page, @Param("comActEasyPhotoVO") ComActEasyPhotoVO comActEasyPhotoVO);
|
|
IPage<ComActEasyPhotoVO> pageEasyPhotoAdmin(Page page,
|
@Param("comActEasyPhotoVO") ComActEasyPhotoVO comActEasyPhotoVO);
|
|
@Select("SELECT " + "p.id, " + "p.sponsor_id, " + "p.feedback_at, " + "p.examine_at, " + "p.create_at, "
|
+ "u.`name` sponsorName, " + "u.nick_name userNickName, " + "su.`name` handlerName, "
|
+ "su1.`name` completeName, " + "u.`image_url`, " + "u.phone, " + "p.detail, " + "p.is_need_feed_back, "
|
+ "p.examine_at, " + "p.status, " + "p.reject_reason, " + "p.is_hide, " + "p.happen_addr, " + "p.addr_remark, "
|
+ "su.`name`, " + "p.handle_result, " + "p.photo_path_list, " + "p.activity_type, " + "p.activity_id, "
|
+ "p.activity_amount, " + "count(pu.id)giveThumbsUpNum, " + "p.handle_photo_list," + "c.name communityName,"
|
+ "p.transfer_time," + "p.is_report_urban," + "p.transfer_reason," + "ca.`name` as classifyName,"
|
+ "p.urban_status " + "FROM " + "com_act_easy_photo p " + "JOIN sys_user u on p.sponsor_id=u.user_id "
|
+ "LEFT JOIN sys_user su on p.handler_id=su.user_id " + "LEFT JOIN sys_user su1 on p.complete_id=su1.user_id "
|
+ "LEFT JOIN com_act_easy_photo_user pu on p.id=pu.easy_photo_id "
|
+ "LEFT JOIN com_act c on p.community_id=c.community_id "
|
+ "left join com_act_easy_photo_classify ca on ca.id = p.classify_id " + "WHERE p.del_tag = 0 and p.id=#{id}")
|
ComActEasyPhotoVO detailEasyPhoto(Long id);
|
|
@Select("SELECT " + "'1' type " + "FROM " + " com_act_easy_photo p " + "WHERE "
|
+ " p.del_tag = 0 and p.community_id = #{communityId} " + "AND p.STATUS = 1 " + " " + "union all " + " "
|
+ "SELECT " + "'1' type " + "FROM " + " com_act_easy_photo p " + "WHERE "
|
+ " p.del_tag = 0 and p.community_id = #{communityId} "
|
+ "AND p.`status` = 2 and p.is_need_feed_back=1 and p.handler_id=#{userId} ")
|
List<TodoEventsVO> selectNeedToDo(@Param("communityId") Long communityId, @Param("userId") Long userId);
|
|
@Select("<script> " + "SELECT " + "p.id, " + "u.`name` sponsor_name, " + "u.`nick_name` userNickName, "
|
+ "u.phone sponsor_phone, " + "count(DISTINCT pu.id)giveThumbsUpNum, " + "count(DISTINCT c.id)commentNum, "
|
+ "u.image_url, " + "p.happen_addr, " + "su.`name` handler_name, " + "p.`status`, " + "p.`is_hide`, "
|
+ "p.`detail`, " + "p.`is_need_feed_back`, " + "p.`photo_path_list`, " + "p.create_at, " + "p.feedback_at, "
|
+ "p.activity_type, " + "p.activity_amount, " + "p.classify_id, " + "class.`name` as classifyName, "
|
+ "p.examine_at " + "FROM " + "com_act_easy_photo p " + "JOIN sys_user u ON p.sponsor_id = u.user_id "
|
+ "left join com_act_easy_photo_user pu ON p.id = pu.easy_photo_id "
|
+ "left JOIN sys_user su ON p.handler_id = su.user_id "
|
+ "left JOIN com_act_easy_photo_comment c ON p.id = c.easy_photo_id "
|
+ "left JOIN com_act_easy_photo_classify class ON p.classify_id = class.id " + " <where>"
|
+ " p.del_tag = 0 and p.is_publicity = 1 "
|
+ "<if test='comActEasyPhotoVO.communityId != null and comActEasyPhotoVO.communityId != 0'>"
|
+ " and p.community_id=#{comActEasyPhotoVO.communityId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.status != null and comActEasyPhotoVO.status!=0' >"
|
+ "AND p.`status` = #{comActEasyPhotoVO.status} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.sponsorId != null and comActEasyPhotoVO.sponsorId != 0'>"
|
+ "AND p.sponsor_id =#{comActEasyPhotoVO.sponsorId} " + " </if> "
|
+ "<if test='comActEasyPhotoVO.sponsorId == null or comActEasyPhotoVO.sponsorId==0'>"
|
+ "AND p.`status` in (2,4) " + " </if> "
|
+ "<if test='comActEasyPhotoVO.keyWord != null and comActEasyPhotoVO.keyWord != ""'>"
|
+ "AND (class.`name` like concat(#{comActEasyPhotoVO.keyWord},'%') or p.detail like concat(#{comActEasyPhotoVO.keyWord},'%')) "
|
+ " </if> " + "group by p.id " + "order by p.create_at desc " + " </where>" + "</script>")
|
IPage<ComActEasyPhotoVO> pageEasyPhotoApplets(Page page,
|
@Param("comActEasyPhotoVO") ComActEasyPhotoVO comActEasyPhotoVO);
|
|
@Select("SELECT t.name,COUNT(p.id) as num FROM com_act_easy_photo_type t LEFT JOIN com_act_easy_photo_type_relation r ON t.id = r.easy_type_id "
|
+ "LEFT JOIN com_act_easy_photo p ON r.easy_id = p.id AND p.handle_status = 1 AND p.community_id = #{communityId} "
|
+ "GROUP BY t.name ")
|
List<EastPhotoTypeVO> countDeal(@Param("communityId") Long communityId);
|
|
@Select("SELECT t.name,COUNT(p.id) as num FROM com_act_easy_photo_type t LEFT JOIN com_act_easy_photo_type_relation r ON t.id = r.easy_type_id "
|
+ "LEFT JOIN com_act_easy_photo p ON r.easy_id = p.id AND p.is_publicity = 1 AND p.community_id = #{communityId} "
|
+ "GROUP BY t.name ")
|
List<EastPhotoTypeVO> countPub(@Param("communityId") Long communityId);
|
|
@Select("SELECT t.name,COUNT(p.id) as num FROM com_act_easy_photo_type t LEFT JOIN com_act_easy_photo_type_relation r ON t.id = r.easy_type_id "
|
+ "LEFT JOIN com_act_easy_photo p ON r.easy_id = p.id AND p.status = 4 where p.community_id = #{communityId} "
|
+ "GROUP BY t.name ")
|
List<EastPhotoTypeVO> countApproved(@Param("communityId") Long communityId);
|
|
@Select("SELECT COUNT(id) AS noneDeal," + "("
|
+ "SELECT COUNT(id) FROM com_act_easy_photo WHERE community_id = 2 AND create_at LIKE concat(#{nowDate},'%') "
|
+ ") AS currentNum," + "("
|
+ "(SELECT COUNT(id) FROM com_act_easy_photo WHERE community_id = #{communityId} AND STATUS = 4 AND handle_status = 2)*100/(SELECT COUNT(id) FROM com_act_easy_photo WHERE community_id = #{communityId} AND handle_status = 2) "
|
+ ") AS pubPoint,"
|
+ "(SELECT AVG(TIMESTAMPDIFF(HOUR,create_at,feedback_at)) FROM com_act_easy_photo WHERE community_id = #{communityId} and handle_status = 2 ) AS dealTime "
|
+ "FROM com_act_easy_photo where community_id = #{communityId}")
|
Map<String, Object> countByCommunityId(@Param("communityId") Long communityId, @Param("nowDate") String nowDate);
|
|
@Select("SELECT COUNT(id) AS total, "
|
+ "(SELECT COUNT(id) FROM com_act_easy_photo WHERE community_id = #{communityId} AND #{start} < create_at and create_at < #{end} ) AS adds, "
|
+ "(SELECT COUNT(id) FROM com_act_easy_photo WHERE community_id =#{communityId} AND handle_status = 2 AND #{start} < create_at and create_at < #{end}) AS deal "
|
+ "FROM com_act_easy_photo WHERE community_id = #{communityId} AND #{start} < create_at and create_at < #{end} ")
|
EastPhotoVO countByTime(@Param("start") String start, @Param("end") String end, @Param("nowDate") String nowDate,
|
@Param("communityId") Long communityId);
|
|
// @Select("SELECT " +
|
// " count( id ) AS examineNum, " +
|
// " ( SELECT count( id ) FROM com_act_easy_photo WHERE `status` = 2 AND community_id = #{communityId} and del_tag =
|
// 0 ) AS verificationNum, " +
|
// " ( SELECT count( id ) FROM com_act_easy_photo WHERE `status` = 3 AND community_id = #{communityId} and del_tag =
|
// 0 ) AS rejectNum, " +
|
// " ( SELECT count( id ) FROM com_act_easy_photo WHERE `status` = 4 AND community_id = #{communityId} and del_tag =
|
// 0 ) AS completeNum " +
|
// "FROM " +
|
// " com_act_easy_photo " +
|
// "WHERE " +
|
// " `status` = 1 " +
|
// " AND community_id = #{communityId} and del_tag = 0")
|
ComActEasyPhotoStatisticsVO getEasyPhotoStatistics(@Param("communityId") Long communityId);
|
|
ComActEasyPhotoCountVO easyPhotoCount(ComActEasyPhotoVO comActEasyPhotoVO);
|
|
List<ExportEasyPhotoVO> export(ExportEasyPhotoDTO exportEasyPhotoDTO);
|
|
List<ExportEasyPhotoVO> exportByIds(List<Integer> ids);
|
|
/**
|
* 查询社区待处理随手拍id集合
|
*
|
* @param communityId
|
* 社区id
|
* @return 社区待处理随手拍id集合
|
*/
|
List<Long> easyPhotoNoHandleIds(@Param("communityId") Long communityId);
|
}
|
