<?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.IntProvinceIrregularMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="org.springblade.audit.entity.IntProvinceIrregular">
        <result column="THEMONTH" property="themonth" />
        <result column="TS_ID" property="tsId" />
        <result column="SPNAME" property="spname" />
        <result column="SPID" property="spid" />
        <result column="USERID" property="userid" />
        <result column="ACC_MONTH" property="accMonth" />
        <result column="PROVINCE" property="province" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        THEMONTH, TS_ID, SPNAME, SPID, USERID, ACC_MONTH, PROVINCE
    </sql>

    <update id="giveSpidSoul">
        UPDATE INT_PROVINCE_IRREGULAR a SET spid =( SELECT max( spid ) FROM dim_product_category b WHERE a.spname = b.spname )
        WHERE
        spid IS NULL
        AND themonth = #{theMonth}
    </update>

    <update id="giveSpidSoulTwo">
        UPDATE INT_PROVINCE_IRREGULAR a
        SET spid = ( SELECT max( spid ) FROM DIM_CS_PRM_SPNAME b WHERE a.spname = b.PRM_SPNAME )
        WHERE
        themonth = #{theMonth}
        AND spid IS NULL
    </update>

    <update id="settingFileTag">
        UPDATE INT_IMPORT_FLAG
        SET FLAG_PROVINCE_IRREGULAR = 1
        WHERE
            themonth = #{theMonth}
    </update>

    <delete id="deleteRepeatData">
        DELETE
        FROM
            INT_PROVINCE_IRREGULAR
        WHERE
            themonth = #{theMonth}
        AND rowid NOT IN ( SELECT min( rowid ) FROM INT_PROVINCE_IRREGULAR WHERE themonth = #{theMonth} GROUP BY userid, spid, ACC_MONTH HAVING count(*)> 1 )
        AND ( userid, spid, ACC_MONTH ) IN ( SELECT userid, spid, ACC_MONTH FROM INT_PROVINCE_IRREGULAR WHERE themonth = #{theMonth} GROUP BY userid, spid, ACC_MONTH HAVING count(*)> 1 )
    </delete>

</mapper>
