<?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.customer.mapper.FctCsIrregularMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="org.springblade.customer.entity.FctCsIrregular">
        <result column="SPCDOENAME" property="spcdoename" />
        <result column="SPCODE" property="spcode" />
        <result column="SERVICENAME" property="servicename" />
        <result column="SERVICECODE" property="servicecode" />
        <result column="SPNAME" property="spname" />
        <result column="PRODUCTCODE" property="productcode" />
        <result column="SPNAME2" property="spname2" />
        <result column="PRODUCTID" property="productid" />
        <result column="COMPLAINT_TYPENAME" property="complaintTypename" />
        <result column="COMPLAINT_TYPE" property="complaintType" />
        <result column="IRREGULAR" property="irregular" />
        <result column="PROVINCE" property="province" />
        <result column="AREA" property="area" />
        <result column="PARTNER_TYPE" property="partnerType" />
        <result column="WHOLENET" property="wholenet" />
        <result column="START_TIME" property="startTime" />
        <result column="COMPLAINT_NUM" property="complaintNum" />
        <result column="COMPLAINT_DETAIL" property="complaintDetail" />
        <result column="SPCODE2" property="spcode2" />
        <result column="SPCODENAME2" property="spcodename2" />
        <result column="FINISH_TIME" property="finishTime" />
        <result column="COMPLAINT_NO" property="complaintNo" />
        <result column="COMPLAINT_ITEMNO" property="complaintItemno" />
        <result column="STATUS" property="status" />
        <result column="SPID" property="spid" />
        <result column="THEDAY" property="theday" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        SPCDOENAME, SPCODE, SERVICENAME, SERVICECODE, SPNAME, PRODUCTCODE, SPNAME2, PRODUCTID, COMPLAINT_TYPENAME,
        COMPLAINT_TYPE, IRREGULAR, PROVINCE, AREA, PARTNER_TYPE, WHOLENET, START_TIME, COMPLAINT_NUM, COMPLAINT_DETAIL,
        SPCODE2, SPCODENAME2, FINISH_TIME, COMPLAINT_NO, COMPLAINT_ITEMNO, STATUS, SPID, THEDAY
    </sql>



    <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO FCT_CS_IRREGULAR
        (
        SPCDOENAME,
        SPCODE,
        SERVICENAME,
        SERVICECODE,
        SPNAME,
        PRODUCTCODE,
        SPNAME2,
        PRODUCTID,
        COMPLAINT_TYPENAME,
        COMPLAINT_TYPE,
        IRREGULAR,
        PROVINCE,
        AREA,
        PARTNER_TYPE,
        WHOLENET,
        START_TIME,
        COMPLAINT_NUM,
        COMPLAINT_DETAIL,
        SPCODE2,
        SPCODENAME2,
        FINISH_TIME,
        COMPLAINT_NO,
        COMPLAINT_ITEMNO,
        STATUS,
        BATCH_NUM
        )
        SELECT a.*
        FROM(
        <foreach item="item" index="index" collection="list" separator="union all">
            SELECT
            #{item.spcdoename,jdbcType=VARCHAR} as SPCDOENAME,
            #{item.spcode,jdbcType=VARCHAR} as SPCODE,
            #{item.servicename,jdbcType=VARCHAR} as SERVICENAME,
            #{item.servicecode,jdbcType=VARCHAR} as SERVICECODE,
            #{item.spname,jdbcType=VARCHAR} as SPNAME,
            #{item.productcode,jdbcType=VARCHAR} as PRODUCTCODE,
            #{item.spname2,jdbcType=VARCHAR} as SPNAME2,
            #{item.productid,jdbcType=VARCHAR} as PRODUCTID,
            #{item.complaintTypename,jdbcType=VARCHAR} as COMPLAINT_TYPENAME,
            #{item.complaintType,jdbcType=VARCHAR} as COMPLAINT_TYPE,
            #{item.irregular,jdbcType=VARCHAR} as IRREGULAR,
            #{item.province,jdbcType=VARCHAR} as PROVINCE,
            #{item.area,jdbcType=VARCHAR} as AREA,
            #{item.partnerType,jdbcType=VARCHAR} as PARTNER_TYPE,
            #{item.wholenet,jdbcType=VARCHAR} as WHOLENET,
            #{item.startTime,jdbcType=VARCHAR} as START_TIME,
            #{item.complaintNum,jdbcType=VARCHAR} as COMPLAINT_NUM,
            #{item.complaintDetail,jdbcType=VARCHAR} as COMPLAINT_DETAIL,
            #{item.spcode2,jdbcType=VARCHAR} as SPCODE2,
            #{item.spcodename2,jdbcType=VARCHAR} as SPCODENAME2,
            #{item.finishTime,jdbcType=VARCHAR} as FINISH_TIME,
            #{item.complaintNo,jdbcType=VARCHAR} as COMPLAINT_NO,
            #{item.complaintItemno,jdbcType=VARCHAR} as COMPLAINT_ITEMNO,
            #{item.status,jdbcType=VARCHAR} as STATUS,
            #{item.batchNum,jdbcType=NUMERIC} as BATCH_NUM
            from dual
        </foreach>
        ) a
    </insert>

    <delete id="deleteOldData">
        Delete from FCT_CS_IRREGULAR
        where spcode=#{spcode}
        and replace(FINISH_TIME, '-','') between #{sTime} and #{eTime}
    </delete>

    <update id="setSpid">
        update FCT_CS_IRREGULAR a
        set spid=(select min(spid) from DIM_PRODUCT_CATEGORY b where a.PRODUCTID=b.VACPRODUCTID)
        where spid is null and theday is null
    </update>

    <update id="setTheday">
        update FCT_CS_IRREGULAR
        set theday = replace(FINISH_TIME, '-','')
        where theday is null
    </update>

    <update id="deleteInvisibleDatay">
        update FCT_CS_IRREGULAR set STATUS='有效'
        where theday between #{sTime} and #{eTime} and STATUS like '有效%'
    </update>

    <update id="deleteInvisibleDataw">
        update FCT_CS_IRREGULAR set STATUS='无效'
        where theday between #{sTime} and #{eTime} and STATUS like '无效%'
    </update>

    <update id="deleteInvisibleDatas">
        update FCT_CS_IRREGULAR set STATUS='申诉中'
        where theday between #{sTime} and #{eTime} and STATUS like '申诉中%'
    </update>

    <update id="callData1">
        call PD_CS_IRREGULAR_SLICE(#{sTime},'MONTH')
    </update>

    <update id="callData12">
        call PD_CS_IRREGULAR_MONTH(#{month})
    </update>
</mapper>
