<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="vip.xiaonuo.disk.mapper.MaterrialLibraryMapper">


    <select id="page" resultType="vip.xiaonuo.disk.vo.materrial.MaterrialLibraryVo">
         SELECT a.ID,
               a.COURSE_ID courseId,
               a.DELETE_FLAG deleteFlag,
               a.USERFILE_ID userfileId,
               f.FILE_ID fileId,
               f.FILE_NAME fileName,
               a.COLLEGE_ID collegeId,
               b.name collegeName,
               a.MAJOR_ID,
               e.major_name majorName,
               a.COLLEGE_TWO_ID,
               c.name collegeTwoName,
               a.COLLEGE_THREE_ID,
               d.name collegeThreeName,
               a.RESOURCE_DESC resourceDesc,
               a.CREATE_TIME createTime,
               a.CREATE_USER createUser,
               a.UPDATE_TIME updateTime,
               a.UPDATE_USER updateUser
        FROM SYSDBA."materrial_library" a
         LEFT JOIN college b ON   a.COLLEGE_ID = b.id
         LEFT JOIN college c ON   a.COLLEGE_TWO_ID = c.id
         LEFT JOIN college d ON   a.COLLEGE_THREE_ID = d.id
         LEFT JOIN major e   ON   a.MAJOR_ID = d.id
         LEFT JOIN RESOURCE_USERFILE f ON a.USERFILE_ID=f.USER_FILE_ID
        <where>
            <if test="materrialLibraryPageParam.fileName!=null">
                f.FILE_NAME like concat('%',#{materrialLibraryPageParam.fileName},'%')
            </if>
        </where>
        order by a.UPDATE_TIME desc
    </select>

    <select id="detail" resultType="vip.xiaonuo.disk.vo.materrial.MaterrialLibraryVo">
        SELECT a.ID,
        a.COURSE_ID courseId,
        a.DELETE_FLAG deleteFlag,
        a.USERFILE_ID userfileId,
        f.FILE_ID fileId,
        f.FILE_NAME fileName,
        a.COLLEGE_ID collegeId,
        b.name collegeName,
        a.MAJOR_ID,
        e.major_name majorName,
        a.COLLEGE_TWO_ID,
        c.name collegeTwoName,
        a.COLLEGE_THREE_ID,
        d.name collegeThreeName,
        a.RESOURCE_DESC resourceDesc,
        a.CREATE_TIME createTime,
        a.CREATE_USER createUser,
        a.UPDATE_TIME updateTime,
        a.UPDATE_USER updateUser
        FROM SYSDBA."materrial_library" a
        LEFT JOIN college b ON   a.COLLEGE_ID = b.id
        LEFT JOIN college c ON   a.COLLEGE_ID = c.id
        LEFT JOIN college d ON   a.COLLEGE_ID = d.id
        LEFT JOIN major e   ON   a.MAJOR_ID = d.id
        LEFT JOIN RESOURCE_USERFILE f ON a.USERFILE_ID=f.USER_FILE_ID
        where a.ID=#{id}
    </select>

</mapper>
