<?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="cn.com.ctop.common.module.mapper.MaterialAscriptionMapper">

    <select id="selectByMaterialId" resultType="com.alibaba.fastjson.JSONObject">
        select (case
                    when clip_id != ""
        then (select realname from sys_user where id = clip_id)
                    else ""
            end
                   )    clipName, /*剪辑人姓名*/

               clip_id  clipId,
               (case
                    when shot_id != ""
        then (select realname from sys_user where id = shot_id)
                    else ""
                   end
                   )    shotName, /*拍摄人姓名*/
               shot_id  shotId,

               (case
                    when plan_id != ""
        then (select realname from sys_user where id = plan_id)
                    else ""
                   end
                   )    planName, /*策划人姓名*/
               plan_id  planId,

               (case
                    when plane_id != ""
        then (select realname from sys_user where id = plane_id)
                    else ""
                   end
                   )    planeName, /*平面姓名*/
               plane_id planeId
        from ctop_material_ascription
        where material_id = #{materialId}
        order by create_time desc limit 1

    </select>
    <select id="getUserIdByCode" resultType="java.lang.String" parameterType="java.lang.String">
        SELECT user_id
        FROM ctop_material_info
        WHERE code = #{materialId} limit  1
    </select>

    <update id="updateBySignature">
        UPDATE ctop_material_ascription
        set code =#{md5}
        WHERE code = #{signature}
    </update>

</mapper>
