<?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.clean.mapper.KuaishouAccountCleanTemplateMapper">

    <resultMap id="templateResultMap" type="cn.com.ctop.kuaishou.modules.clean.entity.KuaiShouAccountCleanTemplateInfo">
        <id column="id" property="id"/>
        <result column="template_name" property="templateName"/>
        <result column="applied_media" property="appliedMedia"/>
        <result column="clean_time" property="cleanTime"/>
        <result column="optimization_objective" property="optimizationObjective"/>
        <result column="charge" property="charge"/>
        <result column="conversion" property="conversion"/>
        <result column="promotion_group_status" property="promotionGroupStatus"/>
        <result column="perform_operations" property="performOperations"/>
        <result column="status" property="status"/>
        <result column="create_user" property="createUser"/>
        <result column="create_id" property="createId"/>
        <result column="create_time" property="createTime"/>
        <result column="account_id" property="accountId"/>
        <result column="user_name" property="userName"/>
        <result column="account_status" property="accountStatus"/>
        <result column="account_name" property="accountName"/>
    </resultMap>

    <resultMap id="logResultMap" type="cn.com.ctop.kuaishou.modules.clean.entity.KuaiShouAccountCleanLogInfo">
        <id column="id" property="id"/>
        <result column="account_id" property="accountId"/>
        <result column="template_id" property="templateId"/>
        <result column="create_template_id" property="createTemplateId"/>
        <result column="user_name" property="userName"/>
        <result column="operation_type" property="operationType"/>
        <result column="create_time" property="createTime"/>
        <result column="executive_information" property="executiveInformation"/>
    </resultMap>
    <!--保存清理模板-->
    <insert id="saveAccountCleanTemplate" parameterType="cn.com.ctop.kuaishou.modules.clean.entity.KuaiShouAccountCleanTemplateInfo">
    insert into ctop_kuaishou_account_clean_template values (#{id},#{templateName},#{appliedMedia},#{cleanTime},#{optimizationObjective},#{charge},
    #{conversion},#{promotionGroupStatus},#{performOperations},#{status},#{createUser},#{createId},#{createTime})
  </insert>

    <!--根据创建人查询模板-->
    <select id="getTemplateList" resultMap="templateResultMap">
        select t.id,t.template_name,t.applied_media,t.create_user,t.status from ctop_kuaishou_account_clean_template t
        <where>
            <if test="templateName!=null and templateName!=''">
                t.template_name like CONCAT(CONCAT('%', #{templateName}), '%')
            </if>

            <if test="createUser!=null and createUser!=''">
                and t.create_user=#{createUser}
            </if>
            <if test="createIds!=null and createIds.size() > 0">
                and t.create_id in
                <foreach item="id" index="index" collection="createIds"
                         open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getInfoById" parameterType="String" resultMap="templateResultMap">
      select  t.id,t.template_name,t.clean_time,t.optimization_objective,t.charge,t.conversion,t.promotion_group_status,t.perform_operations  from ctop_kuaishou_account_clean_template t
      where t.id=#{templateId}
   </select>

    <select id="getAccountAssociatedSize" resultType="int">
        select count(*) from ctop_kuaishou_account_clean_center where account_id=#{accountId}
  </select>

    <insert id="savePushTemplateAssociated">
    insert into ctop_kuaishou_account_clean_center (account_id,template_id,create_time)values (#{accountId},#{templateId},#{createTime})
  </insert>

    <update id="updateTemplate">
    update ctop_kuaishou_account_clean_template set
    template_name=#{templateName},clean_time=#{cleanTime},
    optimization_objective=#{optimizationObjective},charge=#{charge},
    conversion=#{conversion},promotion_group_status=#{promotionGroupStatus},
    perform_operations=#{performOperations}
    where id=#{id}
  </update>
    <update id="updateTemplateStatus">
    update ctop_kuaishou_account_clean_template set status=#{status}
    where id=#{templateId}
  </update>

    <delete id="removeBinding">
    delete  from ctop_kuaishou_account_clean_center where account_id=#{accountId} and template_id=#{templateId}
  </delete>

    <select id="getCleanUpLog" parameterType="cn.com.ctop.kuaishou.modules.clean.entity.KuaiShouAccountCleanLogInfo"
            resultMap="logResultMap">
        select * from ctop_kuaishou_account_clean_logs t
        <where>
            <if test="templateId!=null and templateId!=''">
                t.template_id=#{templateId}
            </if>

            <if test="userId!=null and userId!=''">
                and t.create_template_id =#{userId}
            </if>
        </where>
    </select>


    <!--根据账户id 运营名称 查询个人模板信息-->
    <select id="getMyselfTemplateList" resultMap="templateResultMap">
        select t.status,t.id,t.template_name,t.applied_media,t2.account_id,t3.auth_name as
        account_name,t3.account_status,t.create_user as 'user_name' from ctop_kuaishou_account_clean_template t
        LEFT JOIN ctop_kuaishou_account_clean_center t2 on t.id=t2.template_id
        LEFT JOIN ctop_user_allocation t3 on t2.account_id=t3.account_id
        <where>
            t2.account_id is not null
            <if test="accountId!=null and accountId!=''">
                and t2.account_id=#{accountId}
            </if>

            <if test="userName!=null and userName!=''">
                and t.create_user like concat('%',#{userName},'%')
            </if>
            <if test="createId!=null and createId!=''">
                and t.create_id=#{createId}
            </if>
        </where>

    </select>

    <select id="getTemplateListBystatus" resultMap="templateResultMap">
       select  t.id,t.template_name,t.clean_time,t.optimization_objective,t.charge,t.conversion,t.promotion_group_status,t.perform_operations,t2.account_id,t.create_id,t.create_user  from ctop_kuaishou_account_clean_template t
       left join  ctop_kuaishou_account_clean_center t2 on t.id=t2.template_id where t.status='0'
  </select>

    <select id="getGroupMessage" resultType="String">

        SELECT t.unit_id from ctop_kuaishou_group t LEFT JOIN ctop_kuaishou_report_daily_group t2
        on t.unit_id=t2.unit_id
        where t.account_id=#{template.accountId}
        and t.group_create_time &lt;= #{startDate}
        and t2.stat_date &gt;= #{startDate}
        and t.unit_id=#{unitId}
        <if test='template.promotionGroupStatus!=null and template.promotionGroupStatus!="" and template.promotionGroupStatus!="3"'>
            and t.put_status=#{template.promotionGroupStatus}
        </if>
        group by t.unit_id
        having sum(t2.charge) &lt;= #{template.charge}
            <if test='optimizationObjective=="activation"'>
                /* 激活数*/
               and sum(t2.activation) &lt; #{template.conversion}
            </if>


            <if test='optimizationObjective=="formSize"'>
                /*表单数*/
                and  sum(t2.form_count) &lt; #{template.conversion}
            </if>

            <if test='optimizationObjective=="pay"'>
                /*付费数*/
                and  sum(t2.event_pay) &lt; #{template.conversion}
            </if>


            <if test='optimizationObjective=="evocativeApplication"'>
                /*唤起应用*/
                and sum(t2.event_app_invoked) &lt; #{template.conversion}
            </if>

            <if test='optimizationObjective=="registered"'>
                /*注册*/
                and sum(t2.event_register) &lt; #{template.conversion}
            </if>

    </select>

    <select id="getUserNameByAccountId" resultType="Map">
       select t.user_name as userName,t.auth_name as authName from ctop_user_allocation t where t.account_id=#{accountId}
  </select>

    <insert id="saveCleamLog">
    insert into ctop_kuaishou_account_clean_logs (template_id,account_id,create_template_id,user_name,operation_type,create_time,executive_information) values (#{templateId},#{accountId},#{createTemplateId},#{userName},#{operationType},#{createTime},#{executiveInformation})
  </insert>

    <select id="getUserRealName" resultType="String">
        select t.realname from sys_user t where t.id=#{userId}
    </select>

    <select id="getTemplateName" parameterType="String" resultType="int">
         select count(*) from ctop_kuaishou_account_clean_template t  where t.template_name=#{templateName}
    </select>

    <select id="getGroupByAccount" resultType="Map">
        select  t.unit_id as 'unitId',t.ocpx_action_type  as 'type' from ctop_kuaishou_group t  where t.account_id=#{accountId}
    </select>
    <select id="getProjectId" resultType="String">
        SELECT t.project_id from ctop_user_allocation t where t.account_id=#{accountId}
    </select>
</mapper>