From 5a8a90c095280fbd2106869ecd2bad10e01a57a6 Mon Sep 17 00:00:00 2001
From: 101captain <237651143@qq.com>
Date: 星期四, 23 十二月 2021 18:10:22 +0800
Subject: [PATCH] 12/23 大屏修改
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActActivityDAO.java | 447 +++++++++++++++++++------------------------------------
1 files changed, 157 insertions(+), 290 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActActivityDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActActivityDAO.java
index 777d733..5a3d820 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActActivityDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComActActivityDAO.java
@@ -1,24 +1,24 @@
package com.panzhihua.service_community.dao;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+import com.panzhihua.common.model.vos.community.ComActActivityStatisticsVO;
+import org.apache.ibatis.annotations.Mapper;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.annotations.Select;
+import org.apache.ibatis.annotations.Update;
+
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.bigscreen.work.ScreenActActivityListDTO;
import com.panzhihua.common.model.vos.community.ComActActivityVO;
import com.panzhihua.common.model.vos.community.CommunityActivitiesVO;
-import com.panzhihua.common.model.vos.community.CommunityGovernanceTrendsVO;
import com.panzhihua.common.model.vos.community.screen.work.ActActivityListVO;
-import com.panzhihua.common.model.vos.partybuilding.PageActivityMembersVO;
import com.panzhihua.common.model.vos.screen.CarouselInfoVO;
import com.panzhihua.service_community.model.dos.ComActActivityDO;
-import org.apache.ibatis.annotations.Mapper;
-import org.apache.ibatis.annotations.Param;
-import org.apache.ibatis.annotations.Select;
-import org.apache.ibatis.annotations.Update;
-
-import java.util.Date;
-import java.util.List;
-import java.util.Map;
/**
* @program: springcloud_k8s_panzhihuazhihuishequ
@@ -28,313 +28,180 @@
**/
@Mapper
public interface ComActActivityDAO extends BaseMapper<ComActActivityDO> {
- // @Select("<script> " +
-// " <where>" +
-// "<if test='nameCn != null and nameCn.trim() != ""'>" +
-// " </if> " +
-// "<if test='nameCn != null and nameCn.trim() != ""'>" +
-// " </if> " +
-// "<if test='nameCn != null and nameCn.trim() != ""'>" +
-// " </if> " +
-// " </where>" +
-// "</script>")
- @Select("<script> " +
- "SELECT " +
- "a.id, " +
- "a.activity_name, " +
- "u.`name` sponsorName, " +
- "a.activity_addr, " +
- "a.`status`, " +
- "a.publish_at, " +
- "a.begin_at, " +
- "a.cover, " +
- "a.end_at, " +
- "a.volunteer_max, " +
- "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "+
- "count(if(s.is_volunteer=0,s.id,null))participant_now, "+
- "a.participant_max, " +
- "a.sign_up_begin, " +
- "a.sign_up_end, " +
- "ca.name as communityName " +
- "FROM " +
- "com_act_activity a " +
- "join sys_user u on a.sponsor_id=u.user_id " +
- "left join com_act_act_sign s on a.id=s.activity_id "+
- "left join com_act ca on a.community_id=ca.community_id "+
- " where 1=1 " +
+ // @Select("<script> " +
+ // " <where>" +
+ // "<if test='nameCn != null and nameCn.trim() != ""'>" +
+ // " </if> " +
+ // "<if test='nameCn != null and nameCn.trim() != ""'>" +
+ // " </if> " +
+ // "<if test='nameCn != null and nameCn.trim() != ""'>" +
+ // " </if> " +
+ // " </where>" +
+ // "</script>")
+ @Select("<script> " + "SELECT " + "a.id, " + "a.activity_name, " + "u.`name` sponsorName, "
+ + "a.activity_addr, " + "a.aattend_people,"
+ + "a.`status`, " + "a.is_qr_code, " + "a.publish_at, " + "a.begin_at, " + "a.cover, " + "a.end_at, " + "a.volunteer_max, "
+ + "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "
+ + "count(if(s.is_volunteer=0,s.id,null))participant_now, " + "a.participant_max, " + "a.sign_up_begin, "
+ + "a.sign_up_end, " + "ca.name as communityName " + "FROM " + "com_act_activity a "
+ + "left join sys_user u on a.sponsor_id=u.user_id " + "left join com_act_act_sign s on a.id=s.activity_id "
+ + "left join com_act ca on a.community_id=ca.community_id " + " where 1=1 " +
- "<if test='comActActivityVO.choice == 0 '>" +
- "AND a.community_id=#{comActActivityVO.communityId} " +
- " </if> " +
+ "<if test='comActActivityVO.choice == 0 '>" + "AND a.community_id=#{comActActivityVO.communityId} " + " </if> "
+ +
- "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName.trim() != ""'>" +
- " and a.activity_name like concat(#{comActActivityVO.activityName},'%') " +
- " </if> " +
- "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.isIng==null'>" +
- "AND a.`status` = #{comActActivityVO.status} " +
- " </if> " +
- "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.isIng!=null and comActActivityVO.isIng==1'>" +
- "AND a.`status` in (3,4) " +
- " </if> " +
- "<if test='comActActivityVO.isApplets != null and comActActivityVO.isApplets ==1'>" +
- "AND a.`status` != 1 AND a.`status` != 6 " +
- " </if> " +
- "<if test='comActActivityVO.beginAt != null '>" +
- "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} " +
- " </if> " +
- "<if test='comActActivityVO.type != null and comActActivityVO.type==1 '>" +
- "AND a.volunteer_max!=0 " +
- " </if> " +
- "<if test='comActActivityVO.type != null and comActActivityVO.type==2 '>" +
- "AND a.participant_max!=0 " +
- " </if> " +
- " group by a.id "+
- " order by a.status asc,a.publish_at desc "+
- "</script>")
- IPage<ComActActivityVO> pageActivity( Page page, @Param("comActActivityVO") ComActActivityVO comActActivityVO);
+ "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName.trim() != ""'>"
+ + " and a.activity_name like concat(#{comActActivityVO.activityName},'%') " + " </if> "
+ + "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.isIng==null'>"
+ + "AND a.`status` = #{comActActivityVO.status} " + " </if> "
+ + "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.isIng!=null and comActActivityVO.isIng==1'>"
+ + "AND a.`status` in (3,4) " + " </if> "
+ + "<if test='comActActivityVO.isApplets != null and comActActivityVO.isApplets ==1'>"
+ + "AND a.`status` != 1 AND a.`status` != 6 " + " </if> " + "<if test='comActActivityVO.beginAt != null '>"
+ + "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} "
+ + " </if> " + "<if test='comActActivityVO.type != null and comActActivityVO.type==1 '>"
+ + "AND a.volunteer_max!=0 " + " </if> "
+ + "<if test='comActActivityVO.type != null and comActActivityVO.type==2 '>" + "AND a.participant_max!=0 "
+ + " </if> " + " group by a.id " + " order by a.status asc,a.publish_at desc " + "</script>")
+ IPage<ComActActivityVO> pageActivity(Page page, @Param("comActActivityVO") ComActActivityVO comActActivityVO);
- @Select("SELECT " +
- "u.name sponsorName, " +
- "ca.name communityName, " +
- "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "+
- "count(if(s.is_volunteer=0,s.id,null))participant_now, "+
- "a.* " +
- "FROM " +
- "com_act_activity a " +
- "join sys_user u on a.sponsor_id=u.user_id "+
- "left join com_act_act_sign s on a.id=s.activity_id "+
- "left join com_act ca on a.community_id=ca.community_id "+
- "where a.id=#{id} "+
- " group by a.id "
- )
+ @Select("SELECT " + "u.name sponsorName, " + "ca.name communityName, "
+ + "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "
+ + "count(if(s.is_volunteer=0,s.id,null))participant_now, " + "a.* " + "FROM " + "com_act_activity a "
+ + "left join sys_user u on a.sponsor_id=u.user_id " + "left join com_act_act_sign s on a.id=s.activity_id "
+ + "left join com_act ca on a.community_id=ca.community_id " + "where a.id=#{id} " + " group by a.id ")
ComActActivityVO inforActivity(Long id);
- @Select("SELECT " +
- "a.* " +
- "FROM " +
- "com_act_act_sign s " +
- "JOIN com_act_activity a ON s.activity_id = a.id " +
- "WHERE " +
- "s.user_id = #{comActActivityVO.sponsorId} " +
- "ORDER BY " +
- "s.create_at DESC")
+ @Select("SELECT " + "a.* " + "FROM " + "com_act_act_sign s " + "JOIN com_act_activity a ON s.activity_id = a.id "
+ + "WHERE " + "s.user_id = #{comActActivityVO.sponsorId} " + "ORDER BY " + "s.create_at DESC")
IPage<ComActActivityVO> pageMyActivity(Page page, @Param("comActActivityVO") ComActActivityVO comActActivityVO);
- @Select("select " +
- "t.activity_name," +
- "t.id," +
- "t.end_at," +
- "t.begin_at," +
- "t.cover," +
- "t.status " +
- "from com_act_act_sign s " +
- "join sys_user u on s.user_id=u.user_id and u.phone=#{phone} and s.is_volunteer=1 " +
- "join com_act_activity t on s.activity_id=t.id and t.status=5")
+ @Select("select " + "t.activity_name," + "t.id," + "t.end_at," + "t.begin_at," + "t.cover," + "t.status "
+ + "from com_act_act_sign s "
+ + "join sys_user u on s.user_id=u.user_id and u.phone=#{phone} and s.is_volunteer=1 "
+ + "join com_act_activity t on s.activity_id=t.id and t.status=5")
List<ComActActivityVO> listActivityByPhone(String phone);
- @Select("select t.id from ( " +
- "SELECT " +
- "a.id, " +
- "a.activity_name, " +
- "COUNT(case when s.is_volunteer=1 then s.id else null end )volunteernum, " +
- "COUNT(case when s.is_volunteer=0 then s.id else null end)usernum, " +
- "a.participant_min, " +
- "a.volunteer_min " +
- "FROM " +
- "com_act_activity a " +
- "LEFT JOIN com_act_act_sign s on a.id=s.activity_id " +
- "WHERE " +
- "a.`status` in(3,4) and TIMESTAMPDIFF(MINUTE,SYSDATE(),a.sign_up_end)<=0 " +
- "GROUP BY a.id)t WHERE t.usernum<participant_min or t.volunteernum<volunteer_min")
+
+ @Select("select t.id from ( " + "SELECT " + "a.id, " + "a.activity_name, "
+ + "COUNT(case when s.is_volunteer=1 then s.id else null end )volunteernum, "
+ + "COUNT(case when s.is_volunteer=0 then s.id else null end)usernum, " + "a.participant_min, "
+ + "a.volunteer_min " + "FROM " + "com_act_activity a " + "LEFT JOIN com_act_act_sign s on a.id=s.activity_id "
+ + "WHERE " + "a.`status` in(3,4) and TIMESTAMPDIFF(MINUTE,SYSDATE(),a.sign_up_end)<=0 "
+ + "GROUP BY a.id)t WHERE t.usernum<participant_min or t.volunteernum<volunteer_min")
List<Long> selectTimedTaskActActivity();
- @Update("<script> " +
- "update com_act_activity set `status`=6 where id in " +
- "<foreach item=\"item\" collection=\"longs\" separator=\",\" open=\"(\" close=\")\" index=\"\"> "+
- "#{item} "+
- "</foreach> "+
- "</script>")
+ @Update("<script> " + "update com_act_activity set `status`=6 where id in "
+ + "<foreach item=\"item\" collection=\"longs\" separator=\",\" open=\"(\" close=\")\" index=\"\"> " + "#{item} "
+ + "</foreach> " + "</script>")
int updateIdBatch(@Param("longs") List<Long> longs);
- @Update("update com_act_activity set `status`=2 where `status`=1 and TIMESTAMPDIFF(MINUTE,SYSDATE(),publish_at)<=0")
+ @Update("update com_act_activity set `status`=3 where `status`=1 and TIMESTAMPDIFF(MINUTE,SYSDATE(),publish_at)<=0")
int updateStatusToNotBegin();
@Update("update com_act_activity set `status`=3 where `status`=2 and TIMESTAMPDIFF(MINUTE,SYSDATE(),sign_up_begin)<=0")
int updateStatusToBeginSign();
- @Update("update com_act_activity set `status`=5 where `status` in (3,4) and TIMESTAMPDIFF(MINUTE,SYSDATE(),sign_up_end)<=0")
+ @Update("update com_act_activity set `status`=5 where `status` in (3,4) and TIMESTAMPDIFF(MINUTE,SYSDATE(),end_at)<=0")
int updateStatusToBeginActiveOrEnd();
- @Select("<script> " +
- "select t.* from ( " +
- "SELECT " +
- "a.id, " +
- "a.activity_name, " +
- "u.`name` sponsorName, " +
- "a.activity_addr, " +
- "a.participant_max, " +
- "count(if(s.is_volunteer=1,null,s.id))participant_now, "+
- "a.volunteer_max, " +
- "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "+
- "a.`status`, " +
- "a.publish_at, " +
- "a.begin_at, " +
- "a.end_at, " +
- "a.sign_up_begin, " +
- "a.sign_up_end " +
- "FROM " +
- " com_act_activity a " +
- " LEFT JOIN sys_user u on a.sponsor_id=u.user_id " +
- " LEFT JOIN com_act_act_sign s on a.id=s.activity_id " +
- "WHERE " +
- " a.`status` = 1 and a.community_id=#{comActActivityVO.communityId} " +
- "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName !=""'>" +
- " and a.activity_name LIKE concat( #{comActActivityVO.activityName}, '%' ) " +
- " </if> " +
- "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.status !=1'>" +
- "AND a.`status` = 99 " +
- " </if> " +
- "<if test='comActActivityVO.beginAt != null '>" +
- "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} " +
- " </if> " +
- "group by a.id " +
- " " +
- " union all " +
- "select t1.* from ( "+
- " " +
- " SELECT " +
- "a.id, " +
- "a.activity_name, " +
- "u.`name` sponsorName, " +
- "a.activity_addr, " +
- "a.participant_max, " +
- "count(if(s.is_volunteer=1,null,s.id))participant_now, "+
- "a.volunteer_max, " +
- "count(if(s.is_volunteer=1,s.id,null))volunteer_now, "+
- "a.`status`, " +
- "a.publish_at, " +
- "a.begin_at, " +
- "a.end_at, " +
- "a.sign_up_begin, " +
- "a.sign_up_end " +
- "FROM " +
- " com_act_activity a " +
- " LEFT JOIN sys_user u on a.sponsor_id=u.user_id " +
- " LEFT JOIN com_act_act_sign s on a.id=s.activity_id " +
- "WHERE " +
- " a.`status` != 1 and a.community_id=#{comActActivityVO.communityId} " +
- "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName !=""'>" +
- " and a.activity_name LIKE concat( #{comActActivityVO.activityName}, '%' ) " +
- " </if> " +
- "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 '>" +
- "AND a.`status` = #{comActActivityVO.status} " +
- " </if> " +
- "<if test='comActActivityVO.beginAt != null '>" +
- "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} " +
- " </if> " +
- " group by a.id order by a.publish_at desc)t1 )t"+
- "</script>")
- IPage<ComActActivityVO> pageActivityCommunityBack(Page page, @Param("comActActivityVO") ComActActivityVO comActActivityVO);
+
+ @Select("<script> " + "select t.* from ( " + "SELECT " + "a.id, " + "a.activity_name, " + "u.`name` sponsorName, "
+ + "a.activity_addr, " + "a.participant_max, " + "count(if(s.is_volunteer=1,null,s.id))participant_now, "
+ + "a.volunteer_max, " + "count(if(s.is_volunteer=1,s.id,null))volunteer_now, " + "a.`status`, "
+ + "a.publish_at, " + "a.is_qr_code, " + "a.begin_at, " + "a.end_at, " + "a.sign_up_begin, " + "a.sign_up_end " + "FROM "
+ + " com_act_activity a " + " LEFT JOIN sys_user u on a.sponsor_id=u.user_id "
+ + " LEFT JOIN com_act_act_sign s on a.id=s.activity_id " + "WHERE "
+ + " a.`status` = 1 and a.community_id=#{comActActivityVO.communityId} "
+ + "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName !=""'>"
+ + " and a.activity_name LIKE concat( #{comActActivityVO.activityName}, '%' ) " + " </if> "
+ + "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 and comActActivityVO.status !=1'>"
+ + "AND a.`status` = 99 " + " </if> " + "<if test='comActActivityVO.beginAt != null '>"
+ + "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} "
+ + " </if> " + "group by a.id " + " " + " union all " + "select t1.* from ( " + " " + " SELECT " + "a.id, "
+ + "a.activity_name, " + "u.`name` sponsorName, " + "a.activity_addr, " + "a.participant_max, "
+ + "count(if(s.is_volunteer=1,null,s.id))participant_now, " + "a.volunteer_max, "
+ + "count(if(s.is_volunteer=1,s.id,null))volunteer_now, " + "a.`status`, " + "a.publish_at, " + "a.is_qr_code, " + "a.begin_at, "
+ + "a.end_at, " + "a.sign_up_begin, " + "a.sign_up_end " + "FROM " + " com_act_activity a "
+ + " LEFT JOIN sys_user u on a.sponsor_id=u.user_id " + " LEFT JOIN com_act_act_sign s on a.id=s.activity_id "
+ + "WHERE " + " a.`status` != 1 and a.community_id=#{comActActivityVO.communityId} "
+ + "<if test='comActActivityVO.activityName != null and comActActivityVO.activityName !=""'>"
+ + " and a.activity_name LIKE concat( #{comActActivityVO.activityName}, '%' ) " + " </if> "
+ + "<if test='comActActivityVO.status != null and comActActivityVO.status !=0 '>"
+ + "AND a.`status` = #{comActActivityVO.status} " + " </if> " + "<if test='comActActivityVO.beginAt != null '>"
+ + "AND a.begin_at <![CDATA[ >= ]]> #{comActActivityVO.beginAt} AND a.end_at <![CDATA[ <= ]]> #{comActActivityVO.endAt} "
+ + " </if> " + " group by a.id order by a.publish_at desc)t1 )t" + "</script>")
+ IPage<ComActActivityVO> pageActivityCommunityBack(Page page,
+ @Param("comActActivityVO") ComActActivityVO comActActivityVO);
+
+ @Select("select id from com_act_activity where `status` in (3,4) and TIMESTAMPDIFF(MINUTE,SYSDATE(),end_at)<=0")
+ List<Long> getActivityEndIds();
@Update("update com_act_activity set `status`=4 where `status`=3 and TIMESTAMPDIFF(MINUTE,SYSDATE(),sign_up_end)<=0")
int updateStatusToBeginAfterSingEnd();
- @Select("select GROUP_CONCAT(t.x1,',',t.x2,',',t.x3,',',t.x4) from ( " +
- "SELECT " +
- "(select COUNT(d.id) from com_act_discuss d where d.community_id=#{communityId})x1, " +
- "(select COUNT(w.id) from com_act_micro_wish w where w.community_id=#{communityId})x2, " +
- "(select COUNT(p.id) from com_act_easy_photo p where p.community_id=#{communityId})x3, " +
- " COUNT( a.id ) x4 " +
- "FROM " +
- " com_pb_activity a " +
- "WHERE " +
- " a.community_id =#{communityId})t")
+
+ @Select("select GROUP_CONCAT(t.x1,',',t.x2,',',t.x3,',',t.x4) from ( " + "SELECT "
+ + "(select COUNT(d.id) from com_act_discuss d where d.community_id=#{communityId})x1, "
+ + "(select COUNT(w.id) from com_act_micro_wish w where w.community_id=#{communityId})x2, "
+ + "(select COUNT(p.id) from com_act_easy_photo p where p.community_id=#{communityId})x3, "
+ + " COUNT( a.id ) x4 " + "FROM " + " com_pb_activity a " + "WHERE " + " a.community_id =#{communityId})t")
String selectCommunityGovernanceTrendsVO(Long communityId);
- @Select(" " +
- " SELECT " +
- " (select COUNT(d.id) from com_act_discuss d where DATE_FORMAT(d.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and d.community_id=#{communityId})discuss, " +
- " (select COUNT(w.id) from com_act_micro_wish w where DATE_FORMAT(w.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and w.community_id=#{communityId})microWish, " +
- " (select COUNT(p.id) from com_act_easy_photo p where DATE_FORMAT(p.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and p.community_id=#{communityId})eashPhoto, " +
- " (select COUNT(pa.id) from com_pb_activity pa where DATE_FORMAT(pa.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and pa.community_id=#{communityId})pbActivity, " +
- " COUNT( a.id ) actActivity " +
- " FROM " +
- " com_pb_activity a " +
- " WHERE " +
- " DATE_FORMAT( a.create_at, '%Y-%m-%d' )=DATE_FORMAT(#{date},'%Y-%m-%d') " +
- " AND a.community_id =#{communityId}")
- CommunityActivitiesVO selectCommunityActivitiesVO(@Param("communityId") Long communityId, @Param("date")Date date);
- @Select("SELECT COUNT(id) AS totalNum," +
- "(SELECT COUNT(id) FROM com_act_activity WHERE community_id = #{communityId} AND (status = 3 OR status = 4 OR status = 5) AND create_at LIKE CONCAT(#{nowDate},'%'))AS currentNum," +
- "(SELECT COUNT(id) FROM com_act_activity WHERE community_id = #{communityId} AND (status = 3 OR status = 4 OR status = 5) AND volunteer_max = 0)AS commonNum," +
- "(SELECT COUNT(aas.id) FROM com_act_activity a INNER JOIN com_act_act_sign aas ON a.id = aas.activity_id WHERE a.volunteer_max = 0 AND (a.status = 3 OR a.status = 4 OR a.status = 5) AND a.community_id=#{communityId})AS commonPeopleNum," +
- "(SELECT COUNT(id) FROM com_act_activity WHERE community_id = #{communityId} AND (status = 3 OR status = 4 OR status = 5) AND participant_max = 0)AS volunteerNum," +
- "(SELECT COUNT(aas.id) FROM com_act_activity a INNER JOIN com_act_act_sign aas ON a.id = aas.activity_id WHERE a.community_id=#{communityId} AND a.participant_max = 0 AND (a.status = 3 OR a.status = 4 OR a.status = 5))AS volunteerPeopleNum" +
- " FROM com_act_activity where community_id=#{communityId} AND (status = 3 OR status = 4 OR status = 5)")
- Map<String, Long> countByCommunityId(@Param("communityId")Long communityId,@Param("nowDate")String nowDate);
+ @Select(" " + " SELECT "
+ + " (select COUNT(d.id) from com_act_discuss d where DATE_FORMAT(d.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and d.community_id=#{communityId})discuss, "
+ + " (select COUNT(w.id) from com_act_micro_wish w where DATE_FORMAT(w.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and w.community_id=#{communityId})microWish, "
+ + " (select COUNT(p.id) from com_act_easy_photo p where DATE_FORMAT(p.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and p.community_id=#{communityId})eashPhoto, "
+ + " (select COUNT(pa.id) from com_pb_activity pa where DATE_FORMAT(pa.create_at,'%Y-%m-%d')=DATE_FORMAT(#{date},'%Y-%m-%d') and pa.community_id=#{communityId})pbActivity, "
+ + " COUNT( a.id ) actActivity " + " FROM " + " com_pb_activity a " + " WHERE "
+ + " DATE_FORMAT( a.create_at, '%Y-%m-%d' )=DATE_FORMAT(#{date},'%Y-%m-%d') "
+ + " AND a.community_id =#{communityId}")
+ CommunityActivitiesVO selectCommunityActivitiesVO(@Param("communityId") Long communityId, @Param("date") Date date);
- @Select("SELECT id,activity_name as content,IF(volunteer_max = 0,'居民活动','志愿者活动') AS typeName FROM com_act_activity " +
- "WHERE community_id = #{communityId} AND (status = 3 OR status = 4 OR status = 5) ORDER BY create_at DESC LIMIT #{pageSize}")
- List<CarouselInfoVO> screenActivity(@Param("communityId")Long communityId, @Param("pageSize")Integer pageSize);
+ @Select("SELECT " + " count( caa.id ) as totalNum, "
+ + " (select count(id) from com_act_activity where `status` IN (1,2,3,4,5) and volunteer_min = 0 and community_id = #{communityId}) as commonNum, "
+ + " (select count(id) from com_act_activity where `status` IN (1,2,3,4,5) and volunteer_min > 0 and community_id = #{communityId}) as volunteerNum, "
+ + " (select count(caas.user_id) from com_act_act_sign as caas left join com_act_activity as caa on caa.id = caas.activity_id where caa.`status` IN (1,2,3,4,5) and is_volunteer = 0 and caa.community_id = #{communityId}) as commonPeopleNum, "
+ + " (select count(caas.user_id) from com_act_act_sign as caas left join com_act_activity as caa on caa.id = caas.activity_id where caa.`status` IN (1,2,3,4,5) and is_volunteer = 1 and caa.community_id = #{communityId}) as volunteerPeopleNum, "
+ + " (select count(id) from com_act_activity where `status` IN (1,2,3,4,5) and community_id = #{communityId} and create_at between date_sub(curdate() - day(curdate()) +1,interval 1 month) and now()) as currentNum "
+ + "FROM " + " com_act_activity AS caa " + "WHERE "
+ + " caa.`status` IN (1,2,3,4,5) and community_id = #{communityId}")
+ Map<String, Long> countByCommunityId(@Param("communityId") Long communityId, @Param("nowDate") String nowDate);
+
+ @Select("SELECT id,activity_name as content,IF(volunteer_max = 0,'居民活动','志愿者活动') AS typeName FROM com_act_activity "
+ + "WHERE community_id = #{communityId} AND (status = 3 OR status = 4 OR status = 5) ORDER BY create_at DESC LIMIT #{pageSize}")
+ List<CarouselInfoVO> screenActivity(@Param("communityId") Long communityId, @Param("pageSize") Integer pageSize);
@Select("SELECT cover FROM com_act_activity WHERE community_id = #{communityId}")
- List<String> listImg(@Param("communityId")Long communityId);
+ List<String> listImg(@Param("communityId") Long communityId);
- @Select("<script> " +
- "SELECT " +
- " caa.id, " +
- " su.`name` AS userName , " +
- " su.image_url, " +
- " caa.sponsor_id, " +
- " caa.`status`, " +
- " caa.create_at, " +
- " caa.begin_at, " +
- " caa.end_at, " +
- " caa.sign_up_begin, " +
- " caa.sign_up_end, " +
- " caa.volunteer_min, " +
- " caa.volunteer_max, " +
- " caa.participant_min, " +
- " caa.participant_max, " +
- " caa.activity_name, " +
- " caa.cover, " +
- " caa.content, " +
- " caa.activity_addr " +
- "FROM " +
- " com_act_activity AS caa " +
- " LEFT JOIN sys_user AS su ON su.user_id = caa.sponsor_id" +
- " <where> "+
- "<if test='actActivityListDTO.communityId != null '>" +
- " and caa.community_id = #{actActivityListDTO.communityId} " +
- " </if> " +
- "<if test='actActivityListDTO.startTime!=null and actActivityListDTO.startTime != ""'>" +
- " AND DATE_FORMAT(caa.create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{actActivityListDTO.startTime} " +
- " </if> " +
- "<if test='actActivityListDTO.endTime!=null and actActivityListDTO.endTime != ""'>" +
- " AND DATE_FORMAT(caa.create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{actActivityListDTO.endTime} " +
- " </if> " +
- "<if test='actActivityListDTO.type!=null'>" +
- " AND caa.status = #{actActivityListDTO.status} " +
- " </if> " +
- " </where> "+
- "ORDER BY " +
- " caa.create_at DESC " +
- " </script>")
- IPage<ActActivityListVO> getScreenActActivityList(Page page, @Param("actActivityListDTO") ScreenActActivityListDTO actActivityListDTO);
+ @Select("<script> " + "SELECT " + " caa.id, " + " su.`name` AS userName , " + " su.image_url, "
+ + " caa.sponsor_id, " + " caa.`status`, " + " caa.create_at, " + " caa.begin_at, " + " caa.end_at, "
+ + " caa.sign_up_begin, " + " caa.sign_up_end, " + " caa.volunteer_min, " + " caa.volunteer_max, "
+ + " caa.participant_min, " + " caa.participant_max, " + " caa.activity_name, " + " caa.cover, "
+ + " caa.content, " + " caa.activity_addr " + "FROM " + " com_act_activity AS caa "
+ + " LEFT JOIN sys_user AS su ON su.user_id = caa.sponsor_id" + " <where> "
+ + "<if test='actActivityListDTO.communityId != null '>"
+ + " and caa.community_id = #{actActivityListDTO.communityId} " + " </if> "
+ + "<if test='actActivityListDTO.startTime!=null and actActivityListDTO.startTime != ""'>"
+ + " AND DATE_FORMAT(caa.create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> #{actActivityListDTO.startTime} "
+ + " </if> " + "<if test='actActivityListDTO.endTime!=null and actActivityListDTO.endTime != ""'>"
+ + " AND DATE_FORMAT(caa.create_at,'%Y-%m-%d %H:%i:%s') <![CDATA[ <= ]]> #{actActivityListDTO.endTime} "
+ + " </if> " + "<if test='actActivityListDTO.type!=null'>" + " AND caa.status = #{actActivityListDTO.type} "
+ + " </if> " + " </where> " + "ORDER BY " + " caa.create_at DESC " + " </script>")
+ IPage<ActActivityListVO> getScreenActActivityList(Page page,
+ @Param("actActivityListDTO") ScreenActActivityListDTO actActivityListDTO);
- @Select("SELECT " +
- " count( caas.id ) AS volunteerCount,( " +
- " SELECT " +
- " count( caas1.id ) " +
- " FROM " +
- " com_act_act_sign AS caas1 " +
- " LEFT JOIN sys_user AS su1 ON su1.user_id = caas1.user_id " +
- " WHERE " +
- " su1.is_volunteer = 0 " +
- " AND caas1.activity_id = #{activityId} " +
- " ) AS participantCount " +
- "FROM " +
- " com_act_act_sign AS caas " +
- " LEFT JOIN sys_user AS su ON su.user_id = caas.user_id " +
- "WHERE " +
- " su.is_volunteer = 1 " +
- " AND activity_id = #{activityId} ")
+ @Select("SELECT " + " count( caas.id ) AS volunteerCount,( " + " SELECT " + " count( caas1.id ) " + " FROM "
+ + " com_act_act_sign AS caas1 " + " LEFT JOIN sys_user AS su1 ON su1.user_id = caas1.user_id " + " WHERE "
+ + " su1.is_volunteer = 0 " + " AND caas1.activity_id = #{activityId} " + " ) AS participantCount "
+ + "FROM " + " com_act_act_sign AS caas " + " LEFT JOIN sys_user AS su ON su.user_id = caas.user_id " + "WHERE "
+ + " su.is_volunteer = 1 " + " AND activity_id = #{activityId} ")
ActActivityListVO getScreenActActivityPeopleCount(@Param("activityId") Long activityId);
+
+ ComActActivityStatisticsVO activityStatistics (@Param("activityId") Long activityId);
+
+ /**
+ * 查询置于顶部的进行中活动
+ * @param communityId 社区id
+ * @return 活动列表
+ */
+ List<ComActActivityVO> getIndexTopActivityList(@Param("communityId") Long communityId);
}
--
Gitblit v1.7.1