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

package top.lvzhiqiang.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import top.lvzhiqiang.entity.*;

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

/**
 * 照片信息Mapper
 *
 * @author lvzhiqiang
 * 2024/8/26 11:02
 */
public interface PictureInfoMapper {


    @Select("select main_who AS name, count(id) AS count from video_info_infantry where type = #{infantryType} group by main_who order by count(id) desc")
    List<VideoCast> findCast(String infantryType);

    @Select("select fic.category_name AS name, count(fi.category_id) AS count " +
            "from file_image fi " +
            "         left join file_image_category fic on fi.category_id = fic.id " +
            "group by fi.category_id " +
            "order by count(fi.category_id) desc ")
    List<VideoGenres> findUploadGenres();

    @Select({"<script>" +
            "select fi.*,fic.category_name from file_image fi left join file_image_category fic on fi.category_id = fic.id WHERE 1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and fi.remark like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"genres != null and genres != ''\">" +
            "   and fic.category_name = #{genres}" +
            "</if>" +
            "</script>"})
    List<FileImage> getUploadImageInfoList(Map<String, Object> params);

    @Insert("INSERT INTO file_image(old_name, new_name, category_id, size, path, remark, create_time, modify_time) " +
            "VALUES (#{oldName}, #{newName}, #{categoryId}, #{size}, #{path}, #{remark}, now(), now())")
    int insertFileImage(FileImage fileImage);

    @Update("update file_image set old_name=#{oldName},new_name=#{newName},category_id=#{categoryId},size=#{size},path=#{path},remark=#{remark},modify_time=now() where id=#{id}")
    int updateFileImage(FileImage fileImage);

    @Select({"<script>" +
            "select a.*,b.category_name from file_image a left join file_image_category b on a.category_id = b.id WHERE a.delete_flag = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and (a.old_name like concat('%',#{keyword},'%') or a.remark like concat('%',#{keyword},'%'))" +
            "</if>" +
            "<if test=\"categoryField != null and categoryField != ''\">" +
            "   and a.category_id = #{categoryField}" +
            "</if>" +
            " order by ${sortField} ${sort}" +
            "</script>"})
    List<FileImage> findImageList(Map<String, Object> params);

    @Select("select * from file_image where id = #{id}")
    FileImage findFileImageById(Long id);

    @Select("delete from file_image where id = #{id}")
    FileImage deleteFileImageById(Long id);

    @Select("SELECT * FROM file_crawler_image_log WHERE category_id = #{categoryId} and delete_flag = 1 order by publish_time desc limit 1")
    FileCrawlerImageLog findLatestCrawlerImage(Integer categoryId);

    @Insert("INSERT ignore INTO file_crawler_image(old_name, new_name, category_id, log_id, size, path, remark, orgin_url, sort, modify_time) " +
            "VALUES (#{oldName}, #{newName}, #{categoryId}, #{logId}, #{size}, #{path}, #{remark}, #{orginUrl}, #{sort}, now())")
    int insertIgnoreFileImage(FileImage fileImage);

    @Insert("INSERT INTO file_crawler_image_log(id, main_url, main_title, category_id, status, failure_cause, publish_time, create_time, modify_time) " +
            "VALUES (#{id}, #{mainUrl}, #{mainTitle}, #{categoryId}, #{status}, #{failureCause}, #{publishTime}, now(), now()) " +
            "ON DUPLICATE KEY UPDATE status=values(status),failure_cause=values(failure_cause),modify_time=now()")
    void insertOrUpdateFileCrawlerImageLog(FileCrawlerImageLog crawlerImageLog);

    @Insert("INSERT ignore INTO file_crawler_image_log(id, main_url, main_title, category_id, status, failure_cause, publish_time, create_time, modify_time) " +
            "VALUES (#{id}, #{mainUrl}, #{mainTitle}, #{categoryId}, #{status}, #{failureCause}, #{publishTime}, now(), now())")
    int insertIgnoreFileCrawlerImageLog(FileCrawlerImageLog crawlerImageLog);

    @Select({"<script>" +
            "select fi.*,fcic.category_name from file_crawler_image fi left join file_crawler_image_category fcic on fi.category_id = fcic.id " +
            "left join file_crawler_image_log fcil on fi.log_id = fcil.id WHERE 1 = 1" +
            "<if test=\"keyword != null and keyword != ''\">" +
            "   and fi.remark like concat('%',#{keyword},'%')" +
            "</if>" +
            "<if test=\"genres != null and genres != ''\">" +
            "   and fcic.category_name = #{genres}" +
            "</if>" +
            "</script>"})
    List<FileCrawlerImage> getCrawlerImageInfoList(Map<String, Object> params);

    @Select({"<script>" +
            "select * from file_crawler_image_log WHERE delete_flag = 1" +
            "<if test=\"categoryId != null\">" +
            "   and category_id = #{categoryId}" +
            "</if>" +
            "<if test=\"id != null and id != ''\">" +
            "   and id = #{id}" +
            "</if>" +
            "<if test=\"mainUrl != null and mainUrl != ''\">" +
            "   and main_url = #{mainUrl}" +
            "</if>" +
            "<if test=\"status != null\">" +
            "   and status = #{status}" +
            "</if>" +
            " order by publish_time asc" +
            "</script>"})
    List<FileCrawlerImageLog> findJsoupFulibaPicDetailListByParams(Map<String, Object> params);
}