1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
package cn.wisenergy.chnmuseum.party.mapper;
import cn.wisenergy.chnmuseum.party.model.Asset;
import cn.wisenergy.chnmuseum.party.model.ExhibitionBoard;
import cn.wisenergy.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} 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 "
+ "<if test='learningContentId != null'>and lcb.learning_content_id = #{learningContentId} </if>"
+ "<if test='nameOrCode != null'>and eb.name 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 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 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 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 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 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 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);
}