<?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.ruixuan.jinniu.mapper.KuaishouProjectMapper">

    <resultMap type="com.ruixuan.jinniu.entity.KuaishouProject" id="KuaishouProjectResult">
        <result property="id" column="id"/>
        <result property="projectName" column="project_name"/>
        <result property="mediaCode" column="media_code"/>
        <result property="supplierCode" column="supplier_code"/>
        <result property="saleId" column="sale_id"/>
        <result property="saleName" column="sale_name"/>
        <result property="operatorId" column="operator_id"/>
        <result property="operatorName" column="operator_name"/>
        <result property="designResponsibleId" column="design_responsible_id"/>
        <result property="designResponsibleName" column="design_responsible_name"/>
        <result property="ocpxActionType" column="ocpx_action_type"/>
        <result property="advertiserName" column="advertiser_name"/>
        <result property="advertiserId" column="advertiser_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectKuaishouProjectVo">
        select id,
               project_name,
               media_code,
               supplier_code,
               sale_id,
               sale_name,
               operator_id,
               operator_name,
               design_responsible_id,
               design_responsible_name,
               ocpx_action_type,
               advertiser_name,
               advertiser_id,
               create_time,
               update_time
        from kuaishou_project
    </sql>

    <select id="selectKuaishouProjectList" parameterType="com.ruixuan.jinniu.entity.KuaishouProject"
            resultMap="KuaishouProjectResult">
        <include refid="selectKuaishouProjectVo"/>
        <where>
            <if test="projectName != null  and projectName != ''">
                and project_name like concat('%', #{projectName}, '%')
            </if>
            <if test="mediaCode != null  and mediaCode != ''">
                and media_code = #{mediaCode}
            </if>
            <if test="supplierCode != null  and supplierCode != ''">
                and supplier_code = #{supplierCode}
            </if>
            <if test="saleIdList != null and saleIdList.size() >0 ">
                and sale_id in
                <foreach collection="saleIdList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="saleName != null  and saleName != ''">
                and sale_name like concat('%', #{saleName}, '%')
            </if>
            <if test="operatorIdList != null and operatorIdList.size() >0 ">
                and operator_id in
                <foreach collection="operatorIdList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="operatorName != null  and operatorName != ''">
                and operator_name like concat('%', #{operatorName}, '%')
            </if>
            <if test="designIdList != null and designIdList.size() >0 ">
                and design_responsible_id in
                <foreach collection="designIdList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="designResponsibleName != null  and designResponsibleName != ''">
                and design_responsible_name like concat('%', #{designResponsibleName}, '%')
            </if>
            <if test="ocpxActionType != null  and ocpxActionType != ''">
                and ocpx_action_type = #{ocpxActionType}
            </if>
            <if test="advertiserName != null  and advertiserName != ''">
                and advertiser_name like concat('%', #{advertiserName}, '%')
            </if>
            <if test="advertiserId != null  and advertiserId != ''">
                and advertiser_id = #{advertiserId}
            </if>
        </where>
    </select>

    <select id="getMyProjects" parameterType="com.ruixuan.jinniu.entity.KuaishouProject"
            resultMap="KuaishouProjectResult">
        SELECT
        t1.id,
        t1.project_name,
        t1.media_code,
        t1.supplier_code,
        t1.sale_id,
        t1.sale_name,
        t1.operator_id,
        t1.operator_name,
        t1.design_responsible_id,
        t1.design_responsible_name,
        t1.ocpx_action_type,
        t1.advertiser_name,
        t1.advertiser_id,
        t1.create_time,
        t1.update_time
        from kuaishou_project t1
        LEFT JOIN kuaishou_project_member t2 ON t1.id = t2.project_id
        <where>
            <if test="operatorIdList != null and operatorIdList.size() > 0 ">
                AND t2.user_id IN
                <foreach collection="operatorIdList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="projectName != null  and projectName != ''">
                and t1.project_name like concat('%', #{projectName}, '%')
            </if>
        </where>
        GROUP BY t1.id
    </select>

    <select id="getMyProjectIdsByUserIds" resultType="Long">
        SELECT
        distinct t1.id
        from kuaishou_project t1
        LEFT JOIN kuaishou_project_member t2 ON t1.id = t2.project_id
        <where>
            <if test="userList != null and userList.size() > 0 ">
                AND t2.user_id IN
                <foreach collection="userList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="getProjectsByMedia" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        distinct t1.id as 'projectId',
        t1.project_name as 'projectName',
        t1.media_code as 'mediaCode',
        t1.advertiser_id as 'advertiserId',
        t1.advertiser_name as 'advertiserName'
        from kuaishou_project t1
        LEFT JOIN kuaishou_project_member t2 ON t1.id = t2.project_id
        <where>
            <if test="userList != null and userList.size() > 0 ">
                AND t2.user_id IN
                <foreach collection="userList" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
            </if>
            <if test="mediaCode != null  and mediaCode != ''">
                and t1.media_code = #{mediaCode}
            </if>
        </where>
        order by t1.id
    </select>

    <select id="selectKuaishouProjectById" parameterType="Long" resultMap="KuaishouProjectResult">
        <include refid="selectKuaishouProjectVo"/>
        where id = #{id}
    </select>

    <insert id="insertKuaishouProject" parameterType="com.ruixuan.jinniu.entity.KuaishouProject" useGeneratedKeys="true"
            keyProperty="id">
        insert into kuaishou_project
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="projectName != null">project_name,</if>
            <if test="mediaCode != null">media_code,</if>
            <if test="supplierCode != null">supplier_code,</if>
            <if test="saleId != null">sale_id,</if>
            <if test="saleName != null">sale_name,</if>
            <if test="operatorId != null">operator_id,</if>
            <if test="operatorName != null">operator_name,</if>
            <if test="designResponsibleId != null">design_responsible_id,</if>
            <if test="designResponsibleName != null">design_responsible_name,</if>
            <if test="ocpxActionType != null">ocpx_action_type,</if>
            <if test="advertiserName != null">advertiser_name,</if>
            <if test="advertiserId != null">advertiser_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="projectName != null">#{projectName},</if>
            <if test="mediaCode != null">#{mediaCode},</if>
            <if test="supplierCode != null">#{supplierCode},</if>
            <if test="saleId != null">#{saleId},</if>
            <if test="saleName != null">#{saleName},</if>
            <if test="operatorId != null">#{operatorId},</if>
            <if test="operatorName != null">#{operatorName},</if>
            <if test="designResponsibleId != null">#{designResponsibleId},</if>
            <if test="designResponsibleName != null">#{designResponsibleName},</if>
            <if test="ocpxActionType != null">#{ocpxActionType},</if>
            <if test="advertiserName != null">#{advertiserName},</if>
            <if test="advertiserId != null">#{advertiserId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>

    <update id="updateKuaishouProject" parameterType="com.ruixuan.jinniu.entity.KuaishouProject">
        update kuaishou_project
        <trim prefix="SET" suffixOverrides=",">
            <if test="projectName != null">project_name = #{projectName},</if>
            <if test="mediaCode != null">media_code = #{mediaCode},</if>
            <if test="supplierCode != null">supplier_code = #{supplierCode},</if>
            <if test="saleId != null">sale_id = #{saleId},</if>
            <if test="saleName != null">sale_name = #{saleName},</if>
            <if test="operatorId != null">operator_id = #{operatorId},</if>
            <if test="operatorName != null">operator_name = #{operatorName},</if>
            <if test="designResponsibleId != null">design_responsible_id = #{designResponsibleId},</if>
            <if test="designResponsibleName != null">design_responsible_name = #{designResponsibleName},</if>
            <if test="ocpxActionType != null">ocpx_action_type = #{ocpxActionType},</if>
            <if test="advertiserName != null">advertiser_name = #{advertiserName},</if>
            <if test="advertiserId != null">advertiser_id = #{advertiserId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteKuaishouProjectById" parameterType="Long">
        delete
        from kuaishou_project
        where id = #{id}
    </delete>


    <select id="getOcpxActionType" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ocpx_action_type AS 'code', ocpx_action_name AS 'name'
        FROM kuaishou_ocpx_action_type
        ORDER BY create_time
    </select>
</mapper>