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!=&quot;&quot;'>\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);

}