java/zhihuishenqu.git

			@@ -5,6 +5,7 @@
			import java.util.Map;

			import com.panzhihua.common.model.vos.community.ComActActivityStatisticsVO;
			import com.panzhihua.common.model.vos.community.ComActActivityTypeVO;
			import org.apache.ibatis.annotations.Mapper;
			import org.apache.ibatis.annotations.Param;
			import org.apache.ibatis.annotations.Select;
			@@ -44,7 +45,7 @@
			+ "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 sys_user u on a.sponsor_id=u.user_id " + "left join (select * from com_act_act_sign where `status` = 1) 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> "
			@@ -61,23 +62,29 @@
			+ "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 test='comActActivityVO.type != null and comActActivityVO.type==2 '>" + "AND a.volunteer_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 "
			+ "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 " + "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 ")

			@Select("SELECT " + "a.* " + "FROM " + "com_act_act_sign s " + "JOIN com_act_activity a ON s.activity_id = a.id "
			/**
			* 活动详情
			* @param id
			* @return
			*/
			ComActActivityVO inforActivity(@Param("id") Long id);

			@Select("SELECT " + "a.* " + "FROM " + "(select * from com_act_act_sign where `status` = 1) 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 "
			+ "from (select * from com_act_act_sign where `status` = 1) 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);
			@@ -85,7 +92,7 @@
			@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 "
			+ "a.volunteer_min " + "FROM " + "com_act_activity a " + "LEFT JOIN (select * from com_act_act_sign where `status` = 1) 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();
			@@ -104,33 +111,38 @@
			@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.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("<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>")
			/**
			* 分页查询社区活动
			* @param page
			* @param comActActivityVO
			* @return
			*/
			IPage<ComActActivityVO> pageActivityCommunityBack(@Param("page") 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();
			@@ -158,8 +170,8 @@
			@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(caas.user_id) from (select * from com_act_act_sign where `status` = 1) 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 (select * from com_act_act_sign where `status` = 1) 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}")
			@@ -190,9 +202,9 @@
			@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 "
			+ " (select * from com_act_act_sign where `status` = 1) 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 "
			+ "FROM " + " (select * from com_act_act_sign where `status` = 1) 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);

			@@ -204,4 +216,19 @@
			* @return 活动列表
			*/
			List<ComActActivityVO> getIndexTopActivityList(@Param("communityId") Long communityId);

			/**
			* 获取活动类型（目前只有志愿者活动需要获取）
			* @param communityId
			* @param type
			* @return
			*/
			List<ComActActivityTypeVO> listActivityType(@Param("communityId") Long communityId, @Param("type") Integer type);

			/**
			* 添加活动类型
			* @param comActActivityTypeVO
			* @return
			*/
			int addActivityType(@Param("comActActivityTypeVO") ComActActivityTypeVO comActActivityTypeVO);
			}