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.ExcelElderAuthDTO;
|
import com.panzhihua.common.model.dtos.community.PageComEldersRecordsDTO;
|
import com.panzhihua.common.model.dtos.community.PageEldersAuthDTO;
|
import com.panzhihua.common.model.dtos.community.PageEldersAuthRecordDTO;
|
import com.panzhihua.common.model.vos.community.ComEldersAuthRecordForCommunityVO;
|
import com.panzhihua.common.model.vos.community.ComEldersAuthRecordVO;
|
import com.panzhihua.common.model.vos.community.EldersAuthDetailsVO;
|
import com.panzhihua.common.model.vos.community.EldersAuthVO;
|
import com.panzhihua.common.model.vos.elders.ComEldersAuthHistoryVO;
|
import com.panzhihua.common.model.vos.user.SysUserVO;
|
import com.panzhihua.service_community.model.dos.EldersAuthDO;
|
|
/**
|
* DAO
|
*
|
* @author cedoo email:cedoo(a)qq.com
|
* @version 1.0
|
* @since 1.0
|
*/
|
@Mapper
|
public interface EldersAuthDAO extends BaseMapper<EldersAuthDO> {
|
|
@Select("<script> " + "SELECT ea.id, ea.create_at, ea.create_by, ea.update_by, ea.update_at,"
|
+ " ea.auth_user_name , ea.id_card, ea.birth_day, ea.family_user_id ,"
|
+ " ea.domicile, ea.submit_user_id, u.phone as submitUserAccount , ea.video_url, "
|
+ " u.name as submitUserName, ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) as age \n" + "FROM " + " ("
|
+ " SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth\n" + " FROM com_elders_auth \n"
|
+ " GROUP BY id_card, yearMonth\n" + " ) t " + " LEFT JOIN com_elders_auth ea ON t.id = ea.id "
|
+ " LEFT JOIN sys_user u ON ea.submit_user_id = u.user_id\n"
|
+ " LEFT JOIN com_act ac ON u.community_id = ac.community_id\n" + " <where> " +
|
|
"<if test='pageEldersAuthDTO.communityId != null'>"
|
+ " AND ac.community_id = ${pageEldersAuthDTO.communityId} " + " </if> " +
|
|
"<if test='pageEldersAuthDTO.sumitUserId != null'>"
|
+ " AND ea.submit_user_id = #{pageEldersAuthDTO.sumitUserId} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.idCard != null'>" + " AND ea.id_card = #{pageEldersAuthDTO.idCard} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.authUserName != null'>"
|
+ " AND ea.auth_user_name like concat('%', #{pageEldersAuthDTO.authUserName}, '%' ) " + " </if> "
|
+ "<if test='pageEldersAuthDTO.type == 1 '>"
|
+ " AND DATE_FORMAT(ea.create_at, '%c') = #{pageEldersAuthDTO.month} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.type == 2 '>" + "<if test='pageEldersAuthDTO.createAtStart != null'>"
|
+ " AND ea.create_at <![CDATA[>=]]> #{pageEldersAuthDTO.createAtStart} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.createAtEnd != null'>"
|
+ " AND ea.create_at <![CDATA[<=]]> #{pageEldersAuthDTO.createAtEnd} " + " </if> " + " </if> " + " </where>"
|
+ " ORDER BY ea.${pageEldersAuthDTO.sortBy} ${pageEldersAuthDTO.order} " + "</script>")
|
IPage<EldersAuthVO> selectByPage(Page page, @Param("pageEldersAuthDTO") PageEldersAuthDTO pageEldersAuthDTO);
|
|
@Select("<script> " + "SELECT ea.id, ea.create_at, ea.create_by, ea.update_by, ea.update_at,"
|
+ " ea.auth_user_name , ea.id_card, ea.birth_day, ea.family_user_id,ea.type, "
|
+ " ea.domicile, ea.submit_user_id, u.phone as submitUserAccount , ea.video_url , ea.family_user_id, "
|
+ " u.name as submitUserName, ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) as age \n"
|
+ "FROM com_elders_auth ea LEFT JOIN sys_user u ON ea.submit_user_id = u.user_id\n" + " where ea.id = #{id}"
|
+ "</script>")
|
EldersAuthDetailsVO selectDetails(@Param("id") Long id);
|
|
@Select("<script> " + " SELECT \n"
|
+ " (@rownum:=@rownum+1) as seq, '高龄补贴' AS subsidy, '' AS batch, ea.auth_user_name AS NAME, ea.id_card, \n"
|
+ " CASE\n" + " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) > 99 THEN \"100周岁（含）以上\"\n"
|
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) > 89 THEN \"90-99周岁\"\n"
|
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) >= 80 THEN \"80-89周岁\"\n" + " ELSE \"未达到年龄\"\n"
|
+ " END AS TYPE, DATE_FORMAT(ea.create_at, '%Y-%m') AS yearMonth,\n" + " CASE\n"
|
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) > 99 THEN \"800\"\n"
|
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) > 89 THEN \"200\"\n"
|
+ " WHEN ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) >= 80 THEN \"50\"\n" + " ELSE \"未达到年龄\"\n"
|
+ " END AS payCount,\n"
|
+ " ac.name AS communityName, '' AS 'group', '' AS telphone , ROUND(DATEDIFF(CURDATE(), ea.birth_day)/365.2422) AS age\n"
|
+ " FROM \n" + " (" + " SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth, @rownum:=0 \n"
|
+ " FROM com_elders_auth \n" + " GROUP BY id_card, yearMonth\n" + " ) t \n"
|
+ " LEFT JOIN com_elders_auth ea ON t.id = ea.id \n"
|
+ " LEFT JOIN sys_user u ON ea.submit_user_id = u.user_id\n"
|
+ " LEFT JOIN com_act ac ON u.community_id = ac.community_id\n" + " <where> " +
|
|
"<if test='pageEldersAuthDTO.communityId != null'>"
|
+ " AND ac.community_id = ${pageEldersAuthDTO.communityId} " + " </if> " +
|
|
"<if test='pageEldersAuthDTO.ids != null and pageEldersAuthDTO.ids.length>0 '>" + " AND ea.id in "
|
+ "<foreach collection='pageEldersAuthDTO.ids' item='id' index='index' open='(' close=')' separator=',' >\n"
|
+ " #{id}" + "</foreach>" + " </if> " +
|
|
"<if test='pageEldersAuthDTO.ids == null or pageEldersAuthDTO.ids.length==0 '>"
|
+ "<if test='pageEldersAuthDTO.sumitUserId != null'>"
|
+ " AND ea.submit_user_id = #{pageEldersAuthDTO.sumitUserId} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.idCard != null'>" + " AND ea.id_card = #{pageEldersAuthDTO.idCard} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.authUserName != null'>"
|
+ " AND ea.auth_user_name like concat('%', #{pageEldersAuthDTO.authUserName}, '%' ) " + " </if> "
|
+ "<if test='pageEldersAuthDTO.type == 1 '>"
|
+ " AND DATE_FORMAT(ea.create_at, '%c') = #{pageEldersAuthDTO.month} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.type == 2 '>" + "<if test='pageEldersAuthDTO.createAtStart != null'>"
|
+ " AND ea.create_at <![CDATA[>=]]> #{pageComShopOrderSearchDTO.createAtStart} " + " </if> "
|
+ "<if test='pageEldersAuthDTO.createAtEnd != null'>"
|
+ " AND ea.create_at <![CDATA[<=]]> #{pageComShopOrderSearchDTO.createAtEnd} " + " </if> " + " </if> "
|
+ " </if> " + " </where>" + " ORDER BY ea.${pageEldersAuthDTO.sortBy} ${pageEldersAuthDTO.order} "
|
+ "</script>")
|
List<ExcelElderAuthDTO> selectByParams(@Param("pageEldersAuthDTO") PageEldersAuthDTO pageEldersAuthDTO);
|
|
@Select("<script> " + "SELECT CONCAT(ap.province_name, ad.district_name) AS domicile\n"
|
+ "FROM com_mng_struct_area_province ap LEFT JOIN com_mng_struct_area_city ct ON ap.province_adcode = ct.province_adcode\n"
|
+ " LEFT JOIN com_mng_struct_area_district ad ON ct.city_adcode = ad.city_adcode "
|
+ " WHERE substring(ad.district_adcode, 1, 6) = substring(#{idCard}, 1, 6) " + "</script>")
|
String selectDomicile(@Param("idCard") String idCard);
|
|
@Select("<script> "
|
+ " SELECT distinct t.id, t.yearMonth, au.`type`, DATE_FORMAT(au.create_at,'%Y-%m-%d') AS create_at,\n"
|
+ " case " + " when fed.id IS NOT NULL then true\n" + " ELSE false " + " END hasFeed "
|
+ " FROM ( SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth\n" + " FROM com_elders_auth \n"
|
+ " GROUP BY id_card, yearMonth\n" + " ) t LEFT JOIN com_elders_auth au ON t.id = au.id\n"
|
+ " LEFT JOIN com_elders_auth_feedback fed ON t.id = fed.AUTH_ID\n" + " <where>"
|
+ " au.family_user_id = #{pageComEldersRecordsDTO.bigAgeid} " + "</where>"
|
+ "<if test='pageComEldersRecordsDTO.sortColumns!=null'>\n"
|
+ "ORDER BY ${pageComEldersRecordsDTO.sortColumns} ${pageComEldersRecordsDTO.sortType}\n" + "</if> "
|
+ "</script>")
|
List<ComEldersAuthRecordVO> selectEldersRecordByPage(Page page,
|
@Param("pageComEldersRecordsDTO") PageComEldersRecordsDTO pageComEldersRecordsDTO);
|
|
@Select("<script> " + " SELECT DISTINCT a.name"
|
+ " FROM com_elders_auth ea LEFT JOIN com_elders_auth_user au ON ea.family_user_id = au.id \n"
|
+ "LEFT JOIN com_act a ON au.community_id = a.community_id\n" + " WHERE ea.id = #{id} " + "</script>")
|
String selectCommunityNameByAuth(@Param("id") Long id);
|
|
@Select("select user_id,id_card,name from sys_user where user_id = #{userId}")
|
SysUserVO getSysUserByUserId(@Param("userId") Long userId);
|
|
// @Select("<script> "
|
// + " SELECT e.id,e.CREATE_BY,e.create_at, e.update_by, e.update_at, e.id_card, e.auth_user_name, e.video_url,"
|
// + " e.submit_user_id, e.domicile, e.STATUS, e.birth_day, e.type, e.age, e.verification_result, u.name as sumitUserName, u.phone as sumitUserAccount "
|
// + " FROM ( " + " SELECT MAX(id) AS id , DATE_FORMAT(create_at, '%Y-%m') AS yearMonth "
|
// + " FROM com_elders_auth " + " GROUP BY id_card, yearMonth " + " ) t "
|
// + " left join com_elders_auth e on t.id = e.id " + " LEFT JOIN sys_user u ON e.submit_user_id = u.user_id "
|
// + "<where>" + " <if test='pageEldersAuthRecordDTO.name!=null'>\n"
|
// + " AND e.auth_user_name like concat('%', #{pageEldersAuthRecordDTO.name}, '%')" + "</if>"
|
// + " <if test='pageEldersAuthRecordDTO.idCard!=null and pageEldersAuthRecordDTO.idCard!=""'>\n"
|
// + " AND e.id_card = #{pageEldersAuthRecordDTO.idCard}\n" + "</if>"
|
// + " <if test='pageEldersAuthRecordDTO.type!=null'>\n" + " AND e.type = #{pageEldersAuthRecordDTO.type}\n"
|
// + "</if>" + " <if test='pageEldersAuthRecordDTO.month!=null'>\n"
|
// + " AND month(e.create_at) = #{pageEldersAuthRecordDTO.month}\n" + "</if>\n" + "</where>\n"
|
// + "<if test='pageEldersAuthRecordDTO.sortColumns!=null'>\n"
|
// + "ORDER BY e.${pageEldersAuthRecordDTO.sortColumns} ${pageEldersAuthRecordDTO.sortType}\n" + "</if> "
|
// + "</script>")
|
IPage<ComEldersAuthRecordForCommunityVO> selectCommunityEldersRecordByPage(Page page,
|
@Param("pageEldersAuthRecordDTO") PageEldersAuthRecordDTO pageEldersAuthRecordDTO);
|
|
/**
|
* 根据社区id查询当前社区有多少人未认证和认证
|
* @param communityId 社区id
|
* @return 前社区有多少人未认证和认证
|
*/
|
Map<String,Object> getEldersAuthStatistics(@Param("communityId") Long communityId);
|
|
/**
|
* 查询社区上月所有视频认证记录
|
* @param communityId 社区id
|
* @return 上月所有视频认证记录
|
*/
|
List<ComEldersAuthHistoryVO> getEldersAuthRecords(@Param("communityId") Long communityId);
|
|
}
|
