<?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="com.xjrsoft.module.textbook.mapper.TextbookWarehouseRecordMapper">
    <select id="getPage" parameterType="com.xjrsoft.module.textbook.dto.TextbookWarehouseRecordPageDto" resultType="com.xjrsoft.module.textbook.vo.TextbookWarehouseRecordPageVo">
        SELECT t1.id,
        t1.sort_code,
        t1.price,
        t1.discount,
        t3.issn,
        t3.book_name,
        t3.publishing_house,
        t3.editor_in_chief,
        t4.group_name,
        t5.name AS course_name,
        t6.name AS textbook_type_cn,
        t3.specifications_models,
        t1.create_date,
        t2.name AS warehouse_user,
        t1.warehouse_number,
        t9.name as baseSemesterIdCn,
        (SELECT GROUP_CONCAT(c2.name)
        FROM textbook_class_relation c1
        LEFT JOIN base_class c2 ON c1.class_id = c2.id
        WHERE c1.textbook_id = t1.textbook_id) AS use_class,
        t7.name                        AS use_grade
        FROM textbook_warehouse_record t1
        LEFT JOIN xjr_user t2 ON t1.create_user_id = t2.id
        LEFT JOIN textbook t3 ON t1.textbook_id = t3.id
        LEFT JOIN subject_group t4 ON t3.subject_group_id = t4.id
        LEFT JOIN base_course_subject t5 ON t3.course_subject_id = t5.id
        LEFT JOIN xjr_dictionary_detail t6 ON t3.textbook_type = t6.code AND t6.item_id = 1739209191193636865
        LEFT JOIN base_grade t7 ON t3.grade_id = t7.id
        LEFT JOIN base_semester t9 ON t9.id = t3.base_semester_id
        WHERE t1.delete_mark = 0
        <if test="dto.textbookType != null and dto.textbookType != ''">
            and t3.textbook_type = #{dto.textbookType}
        </if>
        <if test="dto.baseSemesterId != null">
            and t3.base_semester_id = #{dto.baseSemesterId}
        </if>
        <if test="dto.publishingHouse != null">
            and t3.publishing_house = like concat('%',#{dto.publishingHouse}, '%')
        </if>
        <if test="dto.subjectGroupId != null">
            and t3.subject_group_id = #{dto.subjectGroupId}
        </if>
        <if test="dto.courseSubjectId != null">
            and t3.course_subject_id = #{dto.courseSubjectId}
        </if>
        <if test="dto.classId != null">
            and t8.class_id = #{dto.classId}
        </if>
        <if test="dto.bookName != null and dto.bookName != ''">
            and t3.book_name like concat('%',#{dto.bookName}, '%')
        </if>
        <if test="dto.issn != null and dto.issn != ''">
            and t3.issn = #{dto.issn}
        </if>
        <if test="dto.specificationsModels != null and dto.specificationsModels != ''">
            and t3.specifications_models like concat('%',#{dto.specificationsModels}, '%')
        </if>
        <if test="dto.isTextbookPlan != null and dto.isTextbookPlan != ''">
            and t3.is_textbook_plan = #{dto.isTextbookPlan}
        </if>
        ORDER BY t1.create_date desc
    </select>
    <select id="getList" parameterType="com.xjrsoft.module.textbook.dto.TextbookWarehouseRecordExportDto" resultType="com.xjrsoft.module.textbook.vo.TextbookWarehouseRecordPageVo">
        SELECT t1.id, t1.sort_code, t3.issn,t3.book_name,t3.publishing_house,t3.editor_in_chief,t4.group_name,
        t5.name AS course_name,t6.name AS textbook_type_cn,t3.specifications_models,t1.create_date,t2.name AS warehouse_user,t1.warehouse_number FROM textbook_warehouse_record t1
        LEFT JOIN xjr_user t2 ON t1.create_user_id = t2.id
        LEFT JOIN textbook t3 ON t1.textbook_id = t3.id
        LEFT JOIN subject_group t4 ON t3.subject_group_id = t4.id
        LEFT JOIN base_course_subject t5 ON t3.course_subject_id = t5.id
        LEFT JOIN xjr_dictionary_detail t6 ON t3.textbook_type = t6.code AND t6.item_id = 1739209191193636865
        WHERE t1.delete_mark = 0
        <if test="dto.ids != null and dto.ids.size() > 0">
            and t1.id in <foreach item="id" index="index" collection="dto.ids" open="(" close=")"
                                  separator=",">
            #{id}
        </foreach>
        </if>
        ORDER BY t1.create_date desc
    </select>
</mapper>