LearningContentBoardMapper.java

package cn.chnmuseum.party.mapper;

import cn.chnmuseum.party.model.Asset;
import cn.chnmuseum.party.model.ExhibitionBoard;
import cn.chnmuseum.party.model.LearningContentBoard;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 学习内容展板 Mapper 接口
 * </p>
 *
 * @author Danny Lee
 * @since 2021-03-16
 */
public interface LearningContentBoardMapper extends BaseMapper<LearningContentBoard> {

    @Select("SELECT lcb.*, eb.`name` as exhibition_board_name " +
            "FROM learning_content_board lcb, exhibition_board eb " +
            "WHERE lcb.exhibition_board_id = eb.id " +
            "and lcb.learning_content_id = #{learningContentId} " +
            "and eb.is_published = 1 and eb.is_deleted = 0 " +
            "order by lcb.sortorder desc")
    List<LearningContentBoard> selectBoardListByLearningContentId(String learningContentId);

    @Select("<script>"
            + "SELECT eb.* FROM learning_content_board lcb, exhibition_board eb "
            + "WHERE lcb.exhibition_board_id = eb.id "
            + "and eb.is_published = 1 and is_deleted = 0 "
            + "<if test='learningContentId != null'>and lcb.learning_content_id = #{learningContentId} </if>"
            + "<if test='nameOrCode != null'>"
            +"AND ( eb.name LIKE CONCAT('%', #{nameOrCode}, '%') OR eb.name_pin_yin LIKE CONCAT('%', #{nameOrCode}, '%') " +
            "OR eb.name_first_pin_yin LIKE CONCAT('%', #{nameOrCode}, '%') " +
            "OR eb.serial_no  LIKE CONCAT('%', #{nameOrCode}, '%'))"
            + "</if>"
            + "order by lcb.sortorder desc"
            + "</script>"
    )
    IPage<ExhibitionBoard> selectBoardPageByLearningContentId(Page<?> page, String learningContentId, String nameOrCode);

    @Select("SELECT t.* FROM " +
            "(" +
            "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover FROM learning_content_board lcb, learning_content lc, exhibition_board eb, video_content vc, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.video_content_id = vc.id "
            + "and vc.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 and vc.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'THIS_ORGAN' "
            + "and lc.organ_code = #{organCode} "
            + "UNION "
            + "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover FROM learning_content_board lcb, learning_content lc, exhibition_board eb, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_cat = 'EXHIBITION_BOARD_DATUM' "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'THIS_ORGAN' "
            + "and lc.organ_code = #{organCode} "

            + "UNION "

            + "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover "
            + "FROM learning_content_board lcb, learning_content lc, exhibition_board eb, video_content vc, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.video_content_id = vc.id "
            + "and vc.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 and vc.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'THIS_ORGAN_SUB'"
            + "and lc.organ_code like CONCAT(#{organCode},'%') "
            + "UNION "
            + "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover FROM learning_content_board lcb, learning_content lc, exhibition_board eb, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_cat = 'EXHIBITION_BOARD_DATUM' "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'THIS_ORGAN_SUB' "
            + "and lc.organ_code = #{organCode} "

            + "UNION "

            + "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover FROM learning_content_board lcb, learning_content lc, exhibition_board eb, video_content vc, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.video_content_id = vc.id "
            + "and vc.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 and vc.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'ALL_PLAT'"
            + "UNION "
            + "SELECT a.*, eb.id exhibition_board_id, eb.name exhibition_board_name, eb.cover exhibition_board_cover "
            + "FROM learning_content_board lcb, learning_content lc, exhibition_board eb, asset a "
            + "WHERE lcb.learning_content_id = lc.id "
            + "and lcb.exhibition_board_id = eb.id "
            + "and eb.id = a.ref_item_id "
            + "and lc.is_published = 1 and eb.is_published = 1 and eb.is_deleted = 0 "
            + "and a.is_published = 1 "
            + "and a.file_cat = 'EXHIBITION_BOARD_DATUM' "
            + "and a.file_type = 'VIDEO' "
            + "and lc.applicable_scope = 'ALL_PLAT'" +
            ") t"
    )
    IPage<Asset> selectAssetPageByOrganCode(Page<?> page, String organCode);

}