l1024-jav/src/main/java/top/lvzhiqiang/mapper/VideoInfoUncensoredMapper.java

package top.lvzhiqiang.mapper;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import top.lvzhiqiang.entity.VideoCast;
import top.lvzhiqiang.entity.VideoGenres;
import top.lvzhiqiang.entity.VideoInfo;
import top.lvzhiqiang.entity.VideoInfoUncensored;

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

/**
 * 电影信息流出Mapper
 *
 * @author lvzhiqiang
 * 2022/5/4 18:38
 */
public interface VideoInfoUncensoredMapper {

    /**
     * 删除所有
     */
    @Delete("DELETE FROM video_info_uncensored where 1=1")
    void deleteAll();

    /**
     * 批量新增
     *
     * @param videoInfoUncensoredList
     */
    @Insert({"<script>" +
            "INSERT INTO video_info_uncensored(name, identification_code, issue_date, img_url, video_url, main_who, status, create_time, modify_time) " +
            "VALUES " +
            "<foreach collection='list' item='viu' index=\"index\" separator=\",\">" +
            "   (#{viu.name}, #{viu.identificationCode}, #{viu.issueDate}, #{viu.imgUrl}, #{viu.videoUrl}, #{viu.mainWho}, #{viu.status}, #{viu.createTime}, now())" +
            " </foreach>" +
            "</script>"})
    int insertList(List<VideoInfoUncensored> videoInfoUncensoredList);

    /**
     * 根据状态查询识别码
     */
    @Select("SELECT identification_code FROM video_info_uncensored WHERE delete_flag = 1 and status = #{status}")
    List<String> findIcodeByStatus(Integer status);

    /**
     * 更新状态
     *
     * @param identificationCode
     * @param status
     * @return
     */
    @Update("update video_info_uncensored set status = #{status},modify_time = now() where identification_code = #{identificationCode}")
    int updateStatus(String identificationCode, Integer status);

    /**
     * 查询所有识别码
     */
    @Select("select distinct identification_code from video_info_uncensored")
    List<String> findAllIcode();

    /**
     * 根据条件查询
     */
    List<VideoInfo> getVideoInfoUncensoredList(Map<String, Object> params);

    /**
     * 根据识别码和类型查询
     *
     * @return
     */
    @Select({"<script>" +
            "select vi.*,vio.score,vio.comment from video_info_uncensored vi left join video_info_other vio on vi.identification_code = vio.identification_code and vio.delete_flag = 1 WHERE vi.delete_flag = 1" +
            "<if test=\"identificationCode != null and identificationCode != ''\">" +
            "   and vi.identification_code like concat('%',#{identificationCode},'%')" +
            "</if>" +
            "<if test=\"status != null and type != ''\">" +
            "   and vi.status = #{status}" +
            "</if>" +
            "<if test=\"order != null and order != ''\">" +
            "   order by vi.issue_date ${order}" +
            "</if>" +
            "</script>"})
    List<VideoInfoUncensored> findByCodeAndType(String identificationCode, Integer status, String order);

    @Update("update video_info_uncensored set delete_flag = 2,modify_time = now() where identification_code = #{identificationCode}")
    void delByCode(String identificationCode);

    @Update("update video_info_uncensored set length = #{length}, director = #{director}, maker = #{maker}, issuer = #{issuer}, genres = #{genres}, cast = #{cast}, status = #{status}, modify_time = now() where identification_code = #{identificationCode}")
    void updateJsoupInfoByCode(VideoInfoUncensored videoInfoUncensored);

    @Select("SELECT M.cast name,COUNT(M.cast) count " +
            "FROM (" +
            "         SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(viu.cast, ',', B.HELP_TOPIC_ID + 1), ',', - 1) AS cast" +
            "         FROM video_info_uncensored viu" +
            "                  JOIN MYSQL.HELP_TOPIC B" +
            "                       ON B.HELP_TOPIC_ID < (LENGTH(viu.cast) - LENGTH(REPLACE(viu.cast, ',', '')) + 1)" +
            "         WHERE genres != ''" +
            "     ) M " +
            "GROUP BY M.cast " +
            "ORDER BY COUNT(M.cast) DESC LIMIT 30")
    List<VideoCast> findCast();

    @Select("select main_who AS name, count(id) AS count from video_info_uncensored group by main_who order by count(id) desc")
    List<VideoGenres> findGenres();
}