<?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.springblade.audit.mapper.IntTmpAccountListMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="org.springblade.audit.entity.IntTmpAccountList">
        <result column="THEMONTH" property="themonth" />
        <result column="VACPRODUCTID" property="vacproductid" />
        <result column="USERID" property="userid" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        THEMONTH, VACPRODUCTID, USERID
    </sql>

    <insert id="saveToIntAccount">
        INSERT INTO INT_ACCOUNT_LIST ( THEMONTH, USERID, VACPRODUCTID, spid, province, area ) SELECT DISTINCT
        a.THEMONTH,
        a.USERID,
        a.VACPRODUCTID,
        b.spid,
        c.province,
        c.area
        FROM
            INT_TMP_ACCOUNT_LIST a
            INNER JOIN ( SELECT min( spid ) spid, vacproductid FROM dim_product_category WHERE vacproductid IS NOT NULL GROUP BY vacproductid ) b ON a.VACPRODUCTID = b.vacproductid
            LEFT JOIN ETL_VW_TB_CALLER_AREACODE c ON substr( a.USERID, 1, 7 )= c.CALLERHEAD
        WHERE
            a.themonth = #{theMonth}
    </insert>


    <update id="settingFileTag">
        update INT_IMPORT_FLAG set FLAG_ACCOUNT=1 where themonth=#{theMonth}
    </update>

    <delete id="removeTemporaryData">
       DELETE
       FROM
          INT_TMP_ACCOUNT_LIST
       WHERE
          themonth = #{theMonth}
    </delete>

    <select id="callThisMonth">
        call pd_partition_month('INT_ACCOUNT_LIST',#{theMonth})
    </select>

    <delete id="removePhoneIsBad">
        DELETE
        FROM
            INT_TMP_ACCOUNT_LIST
        WHERE
            (userid LIKE 'NULL%' OR userid NOT LIKE '1%' OR length( userid )!= 11)
        AND themonth = #{theMonth}
    </delete>
</mapper>
