<?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="org.jeecg.modules.orderplatform.mapper.PlatformMaterialMapper">

    <select id="queryMaterialsByDesignUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t2.id as orderId,
            t2.order_title as orderTitle,
            (SELECT realname from sys_user where id=t2.create_by) as createBy,
            (SELECT realname from sys_user where id=t2.design) as designLeader,
            t2.start_date as startDate,
            t2.end_date as endDate,
            t2.attachment_url as attachmentUrl,
            t2.attachment_name as attachmentName,
            t2.order_remark as orderRemark,
            (SELECT project_name from ctop_project where id=t1.project_id) as projectName,
            t1.id as materialId,
            t1.project_id as projectId,
            t1.material_code as materialCode,
            t1.material_name as materialName,
            t1.material_type as materialType,
            t1.material_status as materialStatus,
            t1.script_id as scriptId,
            t1.script_status  as scriptStatus,
            t1.script_advise as scriptAdvise,
            t1.video_id as videoId,
            t1.video_status as videoStatus,
            t1.video_advise as videoAdvise,
            t1.shot,
            (SELECT realname from sys_user where id=t1.shot) as shotName,
            t1.clip,
            (SELECT realname from sys_user where id=t1.clip) as clipName,
            t1.plan,
            (SELECT realname from sys_user where id=t1.plan) as planName
        FROM
            ctop_platform_material t1
                LEFT JOIN ctop_platform_order t2 ON t1.order_code = t2.order_code
        WHERE
           t1.material_status in
        <foreach item="item" index="index" collection="materialStatus"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
           And (plan = #{designUserId}
           OR clip = #{designUserId}
           OR shot = #{designUserId})
    </select>

    <select id="queryMaterialsByDesignLeader" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t2.id as orderId,
        t2.order_title as orderTitle,
        (SELECT realname from sys_user where id=t2.create_by) as createBy,
        (SELECT realname from sys_user where id=t2.design) as designLeader,
        t2.start_date as startDate,
        t2.end_date as endDate,
        t2.attachment_url as attachmentUrl,
        t2.attachment_name as attachmentName,
        t2.order_remark as orderRemark,
        (SELECT project_name from ctop_project where id=t1.project_id) as projectName,
        t1.id as materialId,
        t1.project_id as projectId,
        t1.material_code as materialCode,
        t1.material_name as materialName,
        t1.material_type as materialType,
        t1.material_status as materialStatus,
        t1.script_id as scriptId,
        t1.script_status  as scriptStatus,
        t1.script_advise as scriptAdvise,
        t1.video_id as videoId,
        t1.video_status as videoStatus,
        t1.video_advise as videoAdvise,
        t1.shot,
        (SELECT realname from sys_user where id=t1.shot) as shotName,
        t1.clip,
        (SELECT realname from sys_user where id=t1.clip) as clipName,
        t1.plan,
        (SELECT realname from sys_user where id=t1.plan) as planName
        FROM
        ctop_platform_material t1
        LEFT JOIN ctop_platform_order t2 ON t1.order_code = t2.order_code
        WHERE
        t1.material_status in
        <foreach item="item" index="index" collection="materialStatus"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        And t2.design=#{designLeader}
    </select>

    <select id="queryMaterialsByOperatorUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t2.id as orderId,
            t2.order_code as orderCode,
            t2.order_title as orderTitle,
            (SELECT realname from sys_user where id=t2.create_by) as createBy,
            (SELECT realname from sys_user where id=t2.design) as designLeader,
            t2.start_date as startDate,
            t2.end_date as endDate,
            t2.attachment_url as attachmentUrl,
            t2.attachment_name as attachmentName,
            t2.order_remark as orderRemark,
            (SELECT project_name from ctop_project where id=t1.project_id) as projectName,
            t1.id as materialId,
            t1.material_code as materialCode,
            t1.material_name as materialName,
            t1.material_type as materialType,
            t1.material_status as materialStatus,
            t1.script_id as scriptId,
            t1.script_status as scriptStatus,
            t1.script_advise as scriptAdvise,
            t1.video_id as videoId,
            t1.video_status as videoStatus,
            t1.video_advise as videoAdvise,
            t1.shot,
            (SELECT realname from sys_user where id=t1.shot) as shotName,
            t1.clip,
            (SELECT realname from sys_user where id=t1.clip) as clipName,
            t1.plan,
            (SELECT realname from sys_user where id=t1.plan) as planName
        FROM
            ctop_platform_material t1
                LEFT JOIN ctop_platform_order t2 ON t1.order_code = t2.order_code
        WHERE
            t1.material_status in(2,5)
          and t1.project_id in(SELECT DISTINCT project_id from ctop_project_member WHERE user_id=#{operatorUserId})
    </select>

    <select id="queryScriptDetailById" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t1.script_id as scriptId,
            t1.script_status as scriptStatus,
            t1.script_advise as scriptAdvise,
            (SELECT project_name from ctop_project where id=t1.project_id) as projectName,
            t1.material_name as materialName,
            (SELECT realname from sys_user where id=t1.plan) as planName,
            t2.name as scriptName,
            t2.download_url as url
        FROM
            ctop_platform_material t1
            LEFT JOIN ctop_platform_script t2 ON t1.script_id = t2.id
        where t2.id=#{scriptId}
    </select>

    <select id="queryVideoDetailById" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t.md5,
            t.url,
            t1.video_id as videoId,
            t1.video_status as videoStatus,
            t1.video_advise as videoAdvise,
            t.duration,
            t.width,
            t.height,
            t.size,
            t1.project_id as projectId,
            t1.clip,
            t1.shot,
            t1.plan,
            (SELECT realname from sys_user where id=t1.clip limit 1) as clipName,
            (SELECT realname from sys_user where id=t1.shot limit 1) as shotName,
            (SELECT realname from sys_user where id=t1.plan limit 1) as planName,
            (SELECT GROUP_CONCAT(tag_name) from ctop_material_tag_info where code=t.md5 and status=1) as tag,
            (SELECT GROUP_CONCAT(url) from ctop_material_video_cover where video_md5=t.md5) as imageUrl
        FROM
            ctop_platform_video t
                LEFT JOIN  ctop_platform_material t1 on t.md5=t1.video_id
        where t.md5=#{md5}
          and t.is_delete=0
        limit 1
    </select>

    <select id="queryByCode" resultType="org.jeecg.modules.orderplatform.entity.PlatformMaterial">
        select * from ctop_platform_material where material_code=#{materialCode}
    </select>

    <select id="queryCountByMaterialAndVideo" resultType="java.lang.Integer">
        SELECT
            count(id)
        FROM
            ctop_platform_material
        WHERE
            material_code != #{materialCode}
          AND video_id = #{videoMd5}
    </select>
    <select id="queryMaterialRelatePersonByMaterialCode" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t1.id AS materialId,
            t2.id AS orderId,
            t1.material_name AS materialName,
            t2.order_title AS orderTitle,
            t1.script_advise AS scriptAdvise,
            t1.video_advise AS videoAdvise,
            t1.plan,
            t1.shot,
            t1.clip,
            t2.design,
            t2.create_by as operator,
            (SELECT realname from sys_user where id=t1.clip limit 1) as clipName,
            (SELECT realname from sys_user where id=t1.shot limit 1) as shotName,
            (SELECT realname from sys_user where id=t1.plan limit 1) as planName,
            (SELECT realname from sys_user where id=t2.design limit 1) as designName,
            (SELECT realname from sys_user where id=t2.create_by limit 1) as operatorName,
            t3. NAME AS scriptName,
            t4. NAME AS videoName
        FROM
            ctop_platform_material t1
                LEFT JOIN ctop_platform_order t2 ON t1.order_code = t2.order_code
                LEFT JOIN ctop_platform_script t3 ON t1.script_id = t3.id
                LEFT JOIN ctop_platform_video t4 ON t1.video_id = t4.md5
        where t1.material_code=#{materialCode}
        limit 1
    </select>

    <select id="queryOrderRelatePersonByMaterialCode" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            id AS orderId,
            order_title AS orderTitle,
            order_advice as orderAdvice,
            t1.design,
            t1.create_by as operator,
            (SELECT realname from sys_user where id=t1.create_by limit 1) as operatorName,
            (SELECT realname from sys_user where id=t1.design limit 1) as designName
        FROM
            ctop_platform_order t1
        WHERE
            order_code = #{orderCode}
        limit 1
    </select>
</mapper>