<?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.CourseAuditRecordMapper">

    <select id="queryList" resultType="java.util.Map">
        SELECT
            t1.ID as id,
            t1.USERFILE_ID AS fileId,
            t2.FILE_NAME AS fileName,
            rf.FILE_URL AS fileUrl,
            t2.CREATE_TIME as uploadTime,
            t2.EXTEND_NAME AS suffix,
            IFNULL (t1.VERIFY_STATUS,'') as verifyStatus,
            IFNULL(( SELECT dd1.DICT_LABEL FROM (SELECT d1.DICT_LABEL,d1.DICT_VALUE  FROM  DEV_DICT d1 WHERE PARENT_ID = (SELECT d2.ID  FROM  DEV_DICT d2 WHERE d2.DICT_VALUE='COURSE_VERIFY_STATUS') )dd1 WHERE dd1.DICT_VALUE = TRIM(t1.VERIFY_STATUS) ),'') AS verifyStatusName,
            IFNULL (t1.COLLEGE_ID,'') AS collegeId,
            IFNULL (t4.NAME,'') AS collegeIdName,
            IFNULL (t1.COLLEGE_TWO_ID,'') AS collegeTwoId,
            IFNULL (t42.NAME,'') AS collegeTwoIdName,
            IFNULL (t1.COLLEGE_THREE_ID,'') AS collegeThreeId,
            IFNULL (t43.NAME,'') AS collegeThreeIdName,
            CONCAT(t1.COLLEGE_ID,',',t1.COLLEGE_TWO_ID,',',t1.COLLEGE_THREE_ID) AS collegeAllId,
            IFNULL (t1.MAJOR_ID,'') AS majorId,
            IFNULL (t5.major_name,'') AS majorIdName,
            IFNULL (t1.COURSE_TYPE,'') AS courseType,
            IFNULL(( SELECT dd1.DICT_LABEL FROM (SELECT d1.DICT_LABEL,d1.DICT_VALUE  FROM  DEV_DICT d1 WHERE PARENT_ID = (SELECT d2.ID  FROM  DEV_DICT d2 WHERE d2.DICT_VALUE='COURSE_TYPE') )dd1 WHERE dd1.DICT_VALUE = TRIM(t1.COURSE_TYPE) ),'') AS courseTypeName,
            IFNULL (t1.COURSE_ID,'') as courseId,
            IFNULL (t3.COURSE_NAME,'') AS courseIdName
        FROM COURSE_AUDIT_RECORD t1
        LEFT JOIN RESOURCE_USERFILE t2 ON t2.USER_FILE_ID = t1.USERFILE_ID AND t2.DELETE_FLAG ='0'
        LEFT JOIN RESOURCE_FILE rf ON t2.FILE_ID= rf.FILE_ID
        LEFT JOIN COURSE_INFO t3 ON t1.COURSE_ID =t3.COURSE_ID AND t3.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t4 ON t1.COLLEGE_ID=t4.id AND t4.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN major t5 ON t1.MAJOR_ID=t5.id AND t5.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t42 ON t1.COLLEGE_TWO_ID=t42.id AND t42.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t43 ON t1.COLLEGE_THREE_ID=t43.id AND t43.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN DEV_FILE t6 ON t1.COVER_IMAGE=t6.ID AND t6.DELETE_FLAG ='NOT_DELETE'
        WHERE t1.DELETE_FLAG ='NOT_DELETE'
        <if test="param.verifyStatus!=null and param.verifyStatus != ''">
            and t1.VERIFY_STATUS=#{param.verifyStatus}
        </if>
        <if test="param.verifyStatusList !=null and param.verifyStatusList.size()>0">
            and t1.VERIFY_STATUS in
            <foreach collection=" param.verifyStatusList" close=")" index="index" item="item" open="(" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="param.fileName!=null and param.fileName != ''">
            and t2.FILE_NAME like CONCAT('%', #{param.fileName}, '%')
        </if>
        <if test="param.collegeId!=null and param.collegeId != ''">
            and t1.COLLEGE_ID=#{param.collegeId}
        </if>
        <if test="param.collegeTwoId!=null and param.collegeTwoId != ''">
            and t1.COLLEGE_TWO_ID=#{param.collegeTwoId}
        </if>
        <if test="param.collegeThreeId!=null and param.collegeThreeId != ''">
            and t1.COLLEGE_THREE_ID=#{param.collegeThreeId}
        </if>
        <if test="param.majorId!=null and param.majorId != ''">
            and t1.MAJOR_ID=#{param.majorId}
        </if>
        <if test="param.courseType!=null and param.courseType != ''">
            and t1.COURSE_TYPE=#{param.courseType}
        </if>
        <if test="param.suffix!=null and param.suffix != ''">
            and t1.EXTEND_NAME like CONCAT('%',#{param.suffix}, '%')
        </if>
        order by t1.ID desc
    </select>

    <select id="queryResourceCentreList" resultType="java.util.Map">
        SELECT
        t1.ID as id,
        t1.USERFILE_ID AS fileId,
        t2.FILE_NAME AS fileName,
        t2.CREATE_TIME as uploadTime,
        IFNULL (t1.VERIFY_STATUS,'') as verifyStatus,
        IFNULL(( SELECT dd1.DICT_LABEL FROM (SELECT d1.DICT_LABEL,d1.DICT_VALUE  FROM  DEV_DICT d1 WHERE PARENT_ID = (SELECT d2.ID  FROM  DEV_DICT d2 WHERE d2.DICT_VALUE='COURSE_VERIFY_STATUS') )dd1 WHERE dd1.DICT_VALUE = TRIM(t1.VERIFY_STATUS) ),'') AS verifyStatusName,
        IFNULL (t1.COLLEGE_ID,'') AS collegeId,
        IFNULL (t4.NAME,'') AS collegeIdName,
        t1.VIEW_COUNT as viewCount,
        IFNULL (CAST(t6.DOWNLOAD_PATH AS VARCHAR),'') AS coverImagePath
        FROM COURSE_AUDIT_RECORD t1
        LEFT JOIN RESOURCE_USERFILE t2 ON t2.USER_FILE_ID = t1.USERFILE_ID AND t2.DELETE_FLAG ='0'
        LEFT JOIN COURSE_INFO t3 ON t1.COURSE_ID =t3.COURSE_ID AND t3.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t4 ON t1.COLLEGE_ID=t4.id AND t4.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN major t5 ON t1.MAJOR_ID=t5.id AND t5.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t42 ON t1.COLLEGE_TWO_ID=t42.id AND t42.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t43 ON t1.COLLEGE_THREE_ID=t43.id AND t43.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN DEV_FILE t6 ON t1.COVER_IMAGE=t6.ID AND t6.DELETE_FLAG ='NOT_DELETE'
        WHERE t1.DELETE_FLAG ='NOT_DELETE'
        <if test="param.verifyStatus!=null and param.verifyStatus != ''">
            and t1.VERIFY_STATUS=#{param.verifyStatus}
        </if>
        <if test="param.verifyStatusList !=null and param.verifyStatusList.size()>0">
            and t1.VERIFY_STATUS in
            <foreach collection=" param.verifyStatusList" close=")" index="index" item="item" open="(" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="param.queryInfo!=null and param.queryInfo != ''">
            AND (t2.FILE_NAME LIKE CONCAT('%',#{param.queryInfo}, '%') OR t2.FILE_NAME_PINYIN LIKE CONCAT('%',#{param.queryInfoPinyin}, '%') OR t1.KEYWORD_VALUE LIKE CONCAT('%',#{param.queryInfo}, '%') OR t1.KEYWORD_PINYIN LIKE CONCAT('%',#{param.queryInfoPinyin}, '%') OR t7.NAME LIKE CONCAT('%',#{param.queryInfo}, '%'))
        </if>
        <if test="param.collegeId!=null and param.collegeId != ''">
            and t1.COLLEGE_ID=#{param.collegeId}
        </if>
        <if test="param.collegeTwoId!=null and param.collegeTwoId != ''">
            and t1.COLLEGE_TWO_ID=#{param.collegeTwoId}
        </if>
        <if test="param.collegeThreeId!=null and param.collegeThreeId != ''">
            and t1.COLLEGE_THREE_ID=#{param.collegeThreeId}
        </if>
        <if test="param.majorId!=null and param.majorId != ''">
            and t1.MAJOR_ID=#{param.majorId}
        </if>
        <if test="param.courseType!=null and param.courseType != ''">
            and t1.COURSE_TYPE=#{param.courseType}
        </if>
        <if test="param.suffix!=null and param.suffix != ''">
            and t1.EXTEND_NAME like CONCAT('%',#{param.suffix}, '%')
        </if>
        <if test="param.funcType!=null and param.funcType != ''">
            <if test=" param.funcType == 1">
                and t1.IS_RECOMMEND='1'
            </if>

        </if>
        <if test="param.sortflag !=null and param.sortflag != ''">
            <if test=" param.sortflag == 0">
                order by t1.ID desc
            </if>
            <if test=" param.sortflag == 1">
                ORDER BY t1.IS_HOT=1 DESC, t1.VIEW_COUNT DESC
            </if>
        </if>
    </select>

    <select id="queryInfo" resultType="java.util.Map">
        SELECT
            t1.ID as id,
            t1.USERFILE_ID AS fileId,
            t2.FILE_NAME AS fileName,
            t2.CREATE_TIME as uploadTime,
            t2.EXTEND_NAME AS suffix,
            IFNULL (t1.VERIFY_STATUS,'') as verifyStatus,
            IFNULL(( SELECT dd1.DICT_LABEL FROM (SELECT d1.DICT_LABEL,d1.DICT_VALUE  FROM  DEV_DICT d1 WHERE PARENT_ID = (SELECT d2.ID  FROM  DEV_DICT d2 WHERE d2.DICT_VALUE='COURSE_VERIFY_STATUS') )dd1 WHERE dd1.DICT_VALUE = TRIM(t1.VERIFY_STATUS) ),'') AS verifyStatusName,
            t1.VIEW_COUNT as viewCount,
            IFNULL (t1.COLLEGE_ID,'') AS collegeId,
            IFNULL (t4.NAME,'') AS collegeIdName,
            IFNULL (t1.COLLEGE_TWO_ID,'') AS collegeTwoId,
            IFNULL (t42.NAME,'') AS collegeTwoIdName,
            IFNULL (t1.COLLEGE_THREE_ID,'') AS collegeThreeId,
            IFNULL (t43.NAME,'') AS collegeThreeIdName,
            CONCAT(t1.COLLEGE_ID,',',t1.COLLEGE_TWO_ID,',',t1.COLLEGE_THREE_ID) AS collegeAllId,
            IFNULL (t1.MAJOR_ID,'') AS majorId,
            IFNULL (t5.major_name,'') AS majorIdName,
            IFNULL (t1.COURSE_TYPE,'') AS courseType,
            IFNULL(( SELECT dd1.DICT_LABEL FROM (SELECT d1.DICT_LABEL,d1.DICT_VALUE  FROM  DEV_DICT d1 WHERE PARENT_ID = (SELECT d2.ID  FROM  DEV_DICT d2 WHERE d2.DICT_VALUE='COURSE_TYPE') )dd1 WHERE dd1.DICT_VALUE = TRIM(t1.COURSE_TYPE) ),'') AS courseTypeName,
            IFNULL (t1.COURSE_ID,'') as courseId,
            IFNULL (t3.COURSE_NAME,'') AS courseIdName,
            IFNULL (t1.RESOURCE_DESC,'') AS resourceDesc,
            IFNULL (t1.COVER_IMAGE,'') AS coverImage,
            IFNULL (CAST(t6.DOWNLOAD_PATH AS VARCHAR),'') AS coverImagePath
        FROM COURSE_AUDIT_RECORD t1
        LEFT JOIN RESOURCE_USERFILE t2 ON t2.USER_FILE_ID = t1.USERFILE_ID AND t2.DELETE_FLAG ='0'
        LEFT JOIN COURSE_INFO t3 ON t1.COURSE_ID =t3.COURSE_ID AND t3.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t4 ON t1.COLLEGE_ID=t4.id AND t4.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN major t5 ON t1.MAJOR_ID=t5.id AND t5.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t42 ON t1.COLLEGE_TWO_ID=t42.id AND t42.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN college t43 ON t1.COLLEGE_THREE_ID=t43.id AND t43.DELETE_FLAG ='NOT_DELETE'
        LEFT JOIN DEV_FILE t6 ON t1.COVER_IMAGE=t6.ID AND t6.DELETE_FLAG ='NOT_DELETE'
        WHERE t1.DELETE_FLAG ='NOT_DELETE'
        <if test="id!=null and id != ''">
            and t1.ID =#{id}
        </if>
    </select>
</mapper>
