package com.winsun.migration.mapper.db2;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface OldDatabases {

    @Select("select " +
            " ys.id, " +
            " ys.account, " +
            " ys.password, " +
            " ys.name, " +
            " (case ys.sex when '男' then 1 when '女' then 2 end) as sex, " +
            " ys.account as phone, " +
            " (case gur.role_id when  '6f5b4a62410c457f8b643c358b03d73c ' then 243 when  '1 'then 10 when  '4545be257d944d77afc3f98ce982e615 ' then 241 " +
            " when 'f85e3ae76a73456c9849bc84f5478fef ' then 242 else 244 end) as role_id, " +
            "  '24 ' dept_id," +
            " (case ys.state when  '0 ' then 1 when  '1 ' then 1 when  '2 ' then 2 when  '3 ' then 4 when  '4 ' then 3 end) as state, " +
            " ys.subst_name, " +
            " ys.is_active, " +
            " ys.school_remark, " +
            " ys.is_protocol, " +
            " ys.yzf_authen_state, " +
            " ys.staffPhoneNum, " +
            " ys.open_id, " +
            " ys.wx_head_img_url, " +
            " ys.wx_nick_name, " +
            " ys.grade, " +
            " ys.id_card, " +
            " xsr.parent_user_id, " +
            " xsr.position, " +
            " xsr.parent_ids " +
            " from " +
            " yx_sales ys " +
            " left join " +
            " gk_user_role_rel gur on ys.id = gur.user_id " +
            " LEFT JOIN " +
            " xz_sales_relation xsr on ys.id = xsr.user_id " +
//           " where " +
//            " ys.id in ('12860') " +
            " GROUP BY ys.id ")
    public List<Map<String, Object>> getUser();

    @Select({"select  " +
            "  yai.id,  " +
            "  yai.title,  " +
            "  yai.activityDesc,  " +
            "  yai.integral,  " +
            "  yai.imageUrl,  " +
            "  (case yai.activityType when 1 then '团队活动' when 2 then '个人活动' end) activity_type,  " +
            "  yat.type_name as subclass,  " +
            "  yai.act_range,  " +
            "  yai.state as `status`,  " +
            "  yai.sort as sort,   " +
            "  yai.createDate,  " +
            "  ys.`name`,  " +
            "  yai.flag as del_flag  " +
            "from  " +
            "  yx_activity_integral yai  " +
            "left join  " +
            "  yx_activity_type yat  " +
            "on  " +
            "  yai.type_id = yat.id  " +
            "left join  " +
            "  (select  " +
            "  *  " +
            "  from   " +
            "  yx_sales   " +
            "  where state != 4) ys  " +
            "on  " +
            "  yai.createOp = ys.account;  "})
    public List<Map<String, Object>> getActivity();

    @Select({"select " +
            "  yaat.id, " +
            "  yaat.activityId, " +
            "  yaat.Integral, " +
            "  yaat.state,  " +
            "  yaat.opinions, " +
            "  yaat.imageUrl_old, " +
            "  yaat.messageInfo, " +
            "  yaat.createDate, " +
            "  yaat.account, " +
            "  ys.id as create_id,  " +
            "  ys.name as creator, " +
            "  yaat.update_date " +
            "from " +
            "  yx_activity_accede_to yaat " +
            "left join " +
            "  (select " +
            "    *" +
            "  from " +
            "    yx_sales " +
            "   where state != 4) ys " +
            " on " +
            "  yaat.account = ys.account "})
    public List<Map<String, Object>> getActivityRecord();

    /**
     * 查询重复记录
     * @param id  记录主键
     * @param activityId  活动id
     * @param account  账号
     * @return 总数记录
     */
    @Select("select count(1) as count from yx_activity_accede_to where id <> #{id} " +
            "and activityId = #{activityId} and account = #{account}")
    public Map<String, Object> getActivityRecordCount(@Param("id") Integer id,
                                          @Param("activityId") Integer activityId,
                                          @Param("account") String account);

}
