<?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.kuaishou.modules.label.mapper.MaterialLabelMapper">


  <resultMap id="BaseResultMap" type="cn.com.ctop.kuaishou.modules.label.entity.LabelStandardInfo">
    <id column="id" property="id"/>
    <result column="signature" property="signature"/>
    <result column="charge_date" property="chargeDate"/>
    <result column="activation_thirty_days" property="activationThirtyDays"/>
    <result column="activation_two_days" property="activationTwoDays"/>
    <result column="form_count_thirty_days" property="formCountThirtyDays"/>
    <result column="form_count_two_days" property="formCountTwoDays"/>
    <result column="play_3s_ratio" property="play3sRatio"/>
    <result column="conversion_ratio" property="conversionRatio"/>
    <result column="behavior_ration" property="behaviorRation"/>
    <result column="event_next_day_stay_ratio" property="eventNextDayStayRatio"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
  </resultMap>



  <resultMap id="TagResultMap" type="cn.com.ctop.common.module.entity.TagInfo">
    <id column="id" property="id"/>
    <result column="tag_name" property="tagName"/>
    <result column="tag_code" property="tagCode"/>
  </resultMap>

  <resultMap id="MaterialLabelResultMap" type="cn.com.ctop.kuaishou.modules.label.entity.MaterialLabelInfo">
    <id column="id" property="id"/>
    <result column="code" property="code"/>
    <result column="slogansCount" property="slogansCount"/>
    <result column="offline_flag" property="offlineFlag"/>
    <result column="confirm_time" property="confirmTime"/>
    <result column="tag_code" property="tagCode"/>
    <result column="tag_name" property="tagName"/>
    <result column="tag_id" property="tagId"/>
    <result column="clip_id" property="clipId"/>
    <result column="shot_id" property="shotId"/>
    <result column="plan_id" property="planId"/>
    <result column="status" property="status"/>
    <result column="user_id" property="userId"/>
    <result column="material_name" property="materialName"/>
    <result column="project_id" property="projectId"/>
    <result column="create_time" property="createTime"/>
  </resultMap>

  <select id="getMateriaSignature" resultType="String">
        SELECT  signature from ctop_etl_kuaishou_video_base_info t  GROUP BY t.signature
    </select>

    <select id="getMateriaSignatureCharge" resultType="String">
        SELECT  signature from ctop_kuaishou_report_daily_material t  where t.stat_date=#{date}  GROUP BY t.signature
    </select>

  <select id="getMaterialFirstDate" parameterType="String" resultType="String">
        SELECT  MIN(stat_date) from ctop_kuaishou_report_daily_material t where t.charge>0 and  t.signature=#{signature}
    </select>

  <select id="getLabermessage"  resultMap="BaseResultMap">
        SELECT  SUM(activation) as 'activation_thirty_days',SUM(form_count) as 'form_count_thirty_days',
        SUM(play_3s_ratio*aclick)/SUM(aclick) as 'play_3s_ratio',
        SUM(activation)/SUM(bclick) as 'conversion_ratio',
        SUM(bclick)/SUM(aclick) as 'behavior_ration',
        SUM(event_next_day_stay)/SUM(activation) as 'event_next_day_stay_ratio'
        from ctop_kuaishou_report_daily_material t
        where  t.signature=#{signature}
        and t.stat_date &gt;=#{startDate}
        and t.stat_date &lt;=#{endDate}
    </select>

  <select id="getLabermessageByTwoDays"  resultMap="BaseResultMap">
        SELECT  SUM(activation) as 'activation_two_days',SUM(form_count) as 'form_count_two_days'
        from ctop_kuaishou_report_daily_material t
        where  t.signature=#{signature}
        and t.stat_date &gt;=#{startDate}
        and t.stat_date &lt;=#{endDate}
    </select>

  <insert id="saveLabelMessage" parameterType="cn.com.ctop.kuaishou.modules.label.entity.LabelStandardInfo">
        insert into ctop_label_standard (signature,charge_date,activation_thirty_days,activation_two_days,form_count_thirty_days,
        form_count_two_days,play_3s_ratio,conversion_ratio,behavior_ration,event_next_day_stay_ratio,create_time,update_time)
        values
        (#{signature},#{chargeDate},#{activationThirtyDays},#{activationTwoDays},#{formCountThirtyDays},#{formCountTwoDays},#{play3sRatio},#{conversionRatio},
        #{behaviorRation},#{eventNextDayStayRatio},#{createTime},#{updateTime})
    </insert>

  <select id="getMaterialRecord" resultMap="BaseResultMap">
        select * from ctop_label_standard where signature=#{signature}
    </select>

  <select id="getAllMaterialData" resultMap="BaseResultMap">
         select * from ctop_label_standard
    </select>

    <select id="getAllMaterialDataCharge" resultMap="BaseResultMap">
         select * from ctop_label_standard t where t.create_time=#{date}
    </select>

  <update id="updateMaterialRecord">
        update ctop_label_standard set charge_date=#{chargeDate},activation_thirty_days=#{activationThirtyDays},activation_two_days=#{activationTwoDays},
        form_count_thirty_days=#{formCountThirtyDays},form_count_two_days=#{formCountTwoDays},play_3s_ratio=#{play3sRatio},conversion_ratio=#{conversionRatio},
        conversionRatio=#{behaviorRation},event_next_day_stay_ratio=#{eventNextDayStayRatio},update_time=#{updateTime} where signature=#{signature}
    </update>

  <select id="getTagInfo"  resultMap="TagResultMap">
        select id,tag_name,tag_code from ctop_tag_info t
    </select>

  <select id="getDataByCodeAndSignature" resultType="int">
        select count(*) from ctop_material_tag_info t where t.code=#{signature} and t.tag_code=#{tagCode}
    </select>

  <insert id="saveMaterialTag" >
        insert into ctop_material_tag_info (code,status,tag_name,tag_code,user_id,tag_id,category_id,create_time) values
        (#{code},'1',#{tagName},#{tagCode},null,#{tagId},#{categoryId},#{createTime})
    </insert>

  <select id="getMaterialLabelData" resultMap="MaterialLabelResultMap">
    SELECT
    matInfo.id,
    matInfo.code,
    matInfo.project_id,
    t.slogansCount,
    matInfo.offline_flag,
    matInfo.confirm_time,
    tagInfo.tag_code,
    tagInfo.tag_name,
    tagInfo.id AS 'tag_id',
    ascription.clip_id,
    ascription.shot_id,
    ascription.plan_id,
    matInfo. STATUS,
    matInfo.user_id,
    matInfo.material_name,
    matInfo.project_id,
    matInfo.create_time
    FROM
    ctop_material_info matInfo
    LEFT JOIN (
    SELECT
    video_code,
    count(*) AS slogansCount
    FROM
    ctop_bytedance_video_slogen_info
    WHERE
    STATUS = 1
    GROUP BY
    video_code
    ) t ON matInfo. CODE = t.video_code
    LEFT JOIN (
    SELECT
    id,
    t.code,
    group_concat(tag_code SEPARATOR ';') AS 'tag_code',
    group_concat(tag_name SEPARATOR ';') AS 'tag_name'
    FROM
    ctop_material_tag_info t
    GROUP BY
    code
    ) tagInfo ON tagInfo.code = matInfo.code
    LEFT JOIN ctop_material_ascription ascription ON matInfo.code = ascription.material_id
    <where>
      <if test="date!=null and date!=''">
        and (tagInfo.create_time &gt;=#{date} or tagInfo.update_time &gt;=#{date})
      </if >
    </where>
  </select>


</mapper>