package com.panzhihua.service_community.dao;

import java.util.List;

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.bigscreen.BigScreenStatisticAgeGenderDTO;
import com.panzhihua.common.model.dtos.community.bigscreen.BigScreenStatisticPartyActivityDTO;
import com.panzhihua.common.model.dtos.community.bigscreen.PageBigScreenStatisticPartyOrg;
import com.panzhihua.common.model.vos.community.bigscreen.*;
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 BigScreenDAO extends BaseMapper<EldersAuthDO> {

    @Select("<script> " + " SELECT org.id, org.name, COUNT(pm.id) as total " + " FROM com_pb_org  org "
        + " LEFT JOIN com_pb_member pm ON org.id = pm.org_id " + " <where> " + " org.status = 1 "
        + "<if test='pageDTO.communityId != null'>" + "  AND org.community_id = ${pageDTO.communityId} " + "</if> "
        + " </where>" + " GROUP BY org.id " +
        // " ORDER BY org.${pageDTO.sortBy} ${pageDTO.order} " +
        " ORDER BY total desc  " + "</script>")
    IPage<BigScreenStatisticPartyOrg> partyOrgMembers(Page page,
        @Param("pageDTO") PageBigScreenStatisticPartyOrg pageBigScreenStatisticPartyOrg);

    @Select("<script> " + " SELECT " + " SUM(CASE lev WHEN '30以内' THEN total ELSE 0 END) as 'thirtyTotal',    "
        + " SUM(CASE lev WHEN '31-60' THEN total ELSE 0 END) as 'sixtyTotal',    "
        + " SUM(CASE lev WHEN '60以上' THEN total ELSE 0 END) as 'biggerTotal'    " + " FROM (    " + "    SELECT     "
        + "    abt.lev, COUNT(abt.id) AS total    " + "    FROM ( " + " SELECT   id_card, id,age, "
        + "  case when age &lt;= 30 then '30以内' " + "  when age &gt; 30 and age &lt;= 60 then '31-60' "
        + "  when age &gt; 60  then '60以上' " + "  ELSE '' " + "  END AS lev " + " FROM ( "
        + "  SELECT id, id_card, ROUND(DATEDIFF(CURDATE(), STR_TO_DATE(SUBSTRING(id_card,7,15), '%Y%m%d'))/365.2422) AS age    "
        + "  FROM com_pb_member  " + " <where> " + " audit_result = 1 " + "<if test='queryDTO.communityId != null'> "
        + " AND  community_id = ${queryDTO.communityId} " + "</if> " + " </where>" + "  ) t     " + " )abt     "
        + " GROUP BY abt.lev    " + " ) t2  " + "</script>")
    BigScreenStatisticAgeGender age(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);

    @Select("<script> " + " SELECT " + " SUM(CASE gender WHEN '男' THEN total ELSE 0 END) as 'manTotal', "
        + " SUM(CASE gender WHEN '女' THEN total ELSE 0 END) as 'womeTotal' " + " FROM ( "
        + "  SELECT CASE WHEN t.sxi = 0 THEN '女' WHEN t.sxi = 1 THEN '男' ELSE '未知' END AS gender, t.total "
        + "  FROM ( "
        + "  SELECT CONVERT(SUBSTRING(id_card,'17',1), UNSIGNED INTEGER)%2 AS sxi, COUNT(id_card) AS total "
        + "  FROM com_pb_member " + " <where> " + " audit_result = 1 " + "<if test='queryDTO.communityId != null'>"
        + "  AND community_id = ${queryDTO.communityId} " + "</if> " + " </where>" + "  GROUP BY sxi  " + "  )t  "
        + " ) t2  " + "</script>")
    BigScreenStatisticAgeGender
        gender(@Param("queryDTO") BigScreenStatisticAgeGenderDTO bigScreenStatisticAgeGenderDTO);

    @Select("<script> " + "  SELECT   " + " SUM(CASE TYPE WHEN 1 THEN total ELSE 0 END) as 'orgCount',  "
        + " SUM(CASE TYPE WHEN 2 THEN total ELSE 0 END) as 'memberCount',  "
        + " SUM(CASE TYPE WHEN 3 THEN total ELSE 0 END) as 'activityCount',  "
        + " SUM(CASE TYPE WHEN 4 THEN total ELSE 0 END) as 'dynCount',SUM(CASE TYPE WHEN 5 THEN total ELSE 0 END) as 'committeeCount'  " + " FROM (  "
        + " SELECT 1 AS TYPE , COUNT(id) total  " + " FROM com_pb_org org   "
        + " WHERE org.community_id = ${communityId} AND STATUS=1  " + " UNION ALL   "
        + " SELECT 2 AS TYPE , COUNT(id) total  " + " FROM  com_pb_member   "
        + " WHERE community_id = ${communityId} AND audit_result = 1  " + " UNION ALL   "
        + " SELECT 3 AS TYPE , COUNT(id) total  " + " FROM  com_pb_activity  "
        + " WHERE community_id = ${communityId} AND STATUS IN (2,3,4,5)  " + " UNION ALL   "
        + " SELECT 4 AS TYPE , COUNT(id) total  " + " FROM  com_pb_dyn   "
        + " WHERE community_id = ${communityId} AND TYPE = 1  UNION ALL " +
            "select 5 AS TYPE,count(id) total      from com_pb_member_role " +
            "where community_id = ${communityId}" + ") t" + "</script>")
    BigScreenStatisticPartyBuild partybuild(@Param("communityId") Long communityId);

    @Select("<script> " + " SELECT pm.id, pm.member_id, pm.user_id, pm.name, " + " case "
        + " when me.photo_path is NOT NULL then  me.photo_path  "
        + " when CAST(SUBSTRING(me.id_card , 17, 1) AS SIGNED)%2=1 then 'https://www.psciio.com/idcard/tst/idcard/a723e5bcbd594a48956ef9354c8b477c.jpg' "
        + " ELSE 'https://www.psciio.com/idcard/tst/idcard/cfb9b58913074068bba8c7002f91e7ff.jpg' END AS photo_path "
        + " ,COUNT(pa.id) AS total   "
        + " FROM com_pb_activity_member  pm  LEFT JOIN com_pb_activity pa  ON pm.activity_id = pa.id  "
        + " LEFT JOIN com_pb_member me ON pm.member_id = me.id  "
        + " WHERE pa.community_id = ${communityId}  AND me.id IS NOT null  " + " GROUP BY pm.member_id  "
        + " ORDER BY total DESC  " + " LIMIT 3  " + "</script>")
    List<PartyBuildingMemberVO> bigscreenPartyactivitytopuser(@Param("communityId") Long communityId);

    @Select("<script> " + "  SELECT id AS member_id, user_id, NAME, " + " case "
        + " when photo_path IS NOT NULL then  photo_path  "
        + " when CAST(SUBSTRING(id_card , 17, 1) AS SIGNED)%2=1 then 'https://www.psciio.com/idcard/tst/idcard/a723e5bcbd594a48956ef9354c8b477c.jpg' "
        + " ELSE 'https://www.psciio.com/idcard/tst/idcard/cfb9b58913074068bba8c7002f91e7ff.jpg' END AS photo_path , "
        + " 0 AS total  " + " FROM  com_pb_member " + " WHERE community_id = ${communityId}  "
        + " ORDER BY join_time ASC  " + " LIMIT 3  " + "</script>")
    List<PartyBuildingMemberVO> defaultPartyactivity(@Param("communityId") Long communityId);

//    @Select("<script> " + " SELECT  m.idxName, case  when d.total IS NOT NULL then d.total ELSE 0 END AS total "
//        + " FROM  " + " ( " + " SELECT "
//        + " DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as idxName " + "FROM " + " (  "
//        + " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH ) " + " FROM com_pb_activity LIMIT 12 " + " )d "
//        + " ORDER BY idxName " + ")m LEFT JOIN  " + "( " + "SELECT   "
//        + " date_format(activity_time_begin, '%Y-%m') AS idxName, " + " COUNT(id) AS total "
//        + " FROM com_pb_activity    " + " WHERE  "
//        + " community_id = ${queryDTO.communityId}  AND STATUS IN ( 3, 4, 5 ) AND  "
//        + " activity_time_begin &lt; DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND "
//        + " activity_time_begin &gt; DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d')  "
//        + " GROUP BY idxName " + " ORDER BY idxName ASC   " + " ) d ON m.idxName = d.idxName "
//        + " WHERE m.idxName > '2020-12-31' " + " ORDER BY m.idxName  " + "</script>")
//    List<BigScreenStatisticPartyActivity>
//        partyactivity(@Param("queryDTO") BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);
//
//    @Select("<script> " + "SELECT " + " m.idxName, " + "CASE " + "   " + "  WHEN d.total IS NOT NULL THEN "
//        + "  d.total ELSE 0  " + " END AS total  " + "FROM " + " ( " + " SELECT "
//        + "  DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 DAY ), '%Y-%m-%d' ) AS idxName  " + " FROM "
//        + "  ( SELECT @cdate := date_add( #{queryDTO.endTime}, INTERVAL 1 DAY ) FROM com_pb_activity LIMIT 7 ) d  "
//        + " ORDER BY " + "  idxName  " + " ) m " + " LEFT JOIN ( " + " SELECT "
//        + "  date_format( activity_time_begin, '%Y-%m-%d' ) AS idxName, " + "  COUNT( id ) AS total  " + " FROM "
//        + "  com_pb_activity  " + " WHERE " + "  community_id = ${queryDTO.communityId}  "
//        + "  AND STATUS IN ( 3, 4, 5 )  "
//        + "  AND activity_time_begin &lt; DATE_FORMAT( STR_TO_DATE( #{queryDTO.startTime}, '%Y-%m-%d' ), '%Y-%m-%d' )  "
//        + "  AND activity_time_begin &gt; DATE_FORMAT( STR_TO_DATE( #{queryDTO.endTime}, '%Y-%m-%d' ), '%Y-%m-%d' )  "
//        + " GROUP BY " + "  idxName  " + " ORDER BY " + "  idxName ASC  " + " ) d ON m.idxName = d.idxName   "
//        + "ORDER BY " + " m.idxName" + " </script>")
//    List<BigScreenStatisticPartyActivity>
//        partyactivityByWeek(@Param("queryDTO") BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);





    @Select("<script> " + " SELECT  m.idxName, case  when d.total IS NOT NULL then d.total ELSE 0 END AS total "
            + " FROM  " + " ( " + " SELECT "
            + " DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') as idxName " + "FROM " + " (  "
            + " SELECT @cdate := date_add(now(), INTERVAL 1 MONTH ) " + " FROM com_act_activity LIMIT 12 " + " )d "
            + " ORDER BY idxName " + ")m LEFT JOIN  " + "( " + "SELECT   "
            + " date_format(begin_at, '%Y-%m') AS idxName, " + " COUNT(id) AS total "
            + " FROM com_act_activity    " + " WHERE  "
            + " community_id = ${queryDTO.communityId}  AND STATUS IN ( 3, 4, 5 ,6) AND  "
            + " begin_at &lt; DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL 1 MONTH ), '%Y-%m-%d' ) AND "
            + " begin_at &gt; DATE_FORMAT( DATE_ADD( STR_TO_DATE(#{queryDTO.date}, '%Y-%m-%d'), INTERVAL - 12 MONTH ), '%Y-%m-%d')  "
            + " GROUP BY idxName " + " ORDER BY idxName ASC   " + " ) d ON m.idxName = d.idxName "
            + " WHERE m.idxName > '2020-12-31' " + " ORDER BY m.idxName  " + "</script>")
    List<BigScreenStatisticPartyActivity>
    partyactivity(@Param("queryDTO") BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);

    @Select("<script> " + "SELECT " + " m.idxName, " + "CASE " + "   " + "  WHEN d.total IS NOT NULL THEN "
            + "  d.total ELSE 0  " + " END AS total  " + "FROM " + " ( " + " SELECT "
            + "  DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 DAY ), '%Y-%m-%d' ) AS idxName  " + " FROM "
            + "  ( SELECT @cdate := date_add( #{queryDTO.endTime}, INTERVAL 1 DAY ) FROM com_act_activity LIMIT 7 ) d  "
            + " ORDER BY " + "  idxName  " + " ) m " + " LEFT JOIN ( " + " SELECT "
            + "  date_format( begin_at, '%Y-%m-%d' ) AS idxName, " + "  COUNT( id ) AS total  " + " FROM "
            + "  com_act_activity  " + " WHERE " + "  community_id = ${queryDTO.communityId}  "
            + "  AND STATUS IN ( 3, 4, 5 ,6)  "
            + "  AND begin_at &lt; DATE_FORMAT( STR_TO_DATE( #{queryDTO.startTime}, '%Y-%m-%d' ), '%Y-%m-%d' )  "
            + "  AND begin_at &gt; DATE_FORMAT( STR_TO_DATE( #{queryDTO.endTime}, '%Y-%m-%d' ), '%Y-%m-%d' )  "
            + " GROUP BY " + "  idxName  " + " ORDER BY " + "  idxName ASC  " + " ) d ON m.idxName = d.idxName   "
            + "ORDER BY " + " m.idxName" + " </script>")
    List<BigScreenStatisticPartyActivity>
    partyactivityByWeek(@Param("queryDTO") BigScreenStatisticPartyActivityDTO bigScreenStatisticPartyActivityDTO);





}