<?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="com.trade.admin.workflow.mapper.BusinessProcessingLogMapper">
    <resultMap id="processingLogResult" type="com.trade.admin.workflow.domain.BusinessProcessingLog">
        <result property="id" column="id"/>
        <result property="businessKey" column="business_key"/>
        <result property="businessType" column="business_type"/>
        <result property="historicTaskId" column="historic_task_id"/>
        <result property="taskName" column="task_name"/>
        <result property="taskDefKey" column="task_def_key"/>
        <result property="formBusinessId" column="form_business_id"/>
        <result property="assignee.id" column="assignee_id"/>
        <result property="assigneeType" column="assignee_type"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="overdueDays" column="overdue_days"/>
        <result property="workingDay" column="is_working_day"/>
        <result property="status" column="status"/>
        <result property="createBy" column="create_by"/>
        <result property="updateBy" column="update_by"/>
        <result property="gmtCreate" column="gmt_create"/>
        <result property="gmtModified" column="gmt_modified"/>
    </resultMap>

    <sql id="fields">
        p.id,
		p.business_key,
		p.business_type,
		p.historic_task_id,
		p.task_name,
		p.task_def_key,
		p.form_business_id,
		p.assignee_id,
		p.assignee_type,
		p.start_time,
		p.end_time,
		p.overdue_days,
		p.is_working_day,
		p.status,
		p.create_by,
		p.update_by,
		p.gmt_create,
		p.gmt_modified
    </sql>

    <insert id="add" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        INSERT INTO business_processing_log
        (business_key, business_type, historic_task_id, task_name, task_def_key, form_business_id, assignee_id,
         assignee_type, start_time, end_time, overdue_days, is_working_day, status, create_by)
        VALUES (#{businessKey}, #{businessType}, #{historicTaskId}, #{taskName}, #{taskDefKey}, #{formBusinessId},
                #{assignee.id}, #{assigneeType}, #{startTime}, #{endTime}, #{overdueDays}, #{workingDay}, #{status},
                #{createBy})
    </insert>

    <update id="update">
        UPDATE business_processing_log
        SET form_business_id=#{formBusinessId},
            end_time=#{endTime},
            status=#{status},
            overdue_days=#{overdueDays},
            is_working_day=#{workingDay},
            update_by=#{updateBy}
        WHERE id = #{id}
    </update>
    <update id="updateAssigneeId">
        UPDATE business_processing_log
        SET assignee_id = #{assignee.id}
        WHERE id = #{id}
    </update>

    <select id="get" parameterType="java.lang.Long" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p
        WHERE p.id=#{value}
    </select>

    <!--
        MySQL数字字符串默认排序按照数字顺序，比如200在1之前，为了按照数字大小排序，需要将数字字符串转化为数字
        方法一：ORDER BY '123'+0;（首推）
        方法二：ORDER BY CAST('123' AS SIGNED);
        方法三：ORDER BY CONVERT('123',SIGNED);
    -->
    <select id="findTaskByBusinessLike" resultType="java.lang.String">
        SELECT p.historic_task_id
        FROM business_processing_log p
        WHERE p.business_key = #{businessKey}
          AND p.business_type LIKE CONCAT(#{businessType},'%')
        GROUP BY p.historic_task_id
        ORDER BY p.id ASC
    </select>

    <select id="findTaskByBusiness" resultType="java.lang.String">
        SELECT p.historic_task_id
        FROM business_processing_log p
        WHERE p.business_key = #{businessKey}
          AND p.business_type = #{businessType}
        GROUP BY p.historic_task_id
        ORDER BY p.id ASC
    </select>

    <select id="findPendingTaskByBusiness" resultType="java.lang.String">
        SELECT historic_task_id
        FROM (SELECT p.id,
                     p.historic_task_id
              FROM business_processing_log p
              WHERE p.business_key = #{businessKey}
                AND p.business_type = #{businessType}
                AND p.form_business_id IS NULL
                AND p.end_time IS NULL
              ORDER BY ID ASC
              LIMIT 99999) r
        GROUP BY r.historic_task_id
    </select>

    <select id="findPendingTaskByBusinessKeys" resultType="java.lang.String">
        SELECT
        historic_task_id
        FROM
        business_processing_log p
        WHERE
        p.task_def_key = #{taskDefKey}
        AND p.business_type = #{businessType}
        <if test="assigneeType != null and assigneeType !=''">
            AND p.assignee_type = #{assigneeType}
        </if>
        AND p.form_business_id IS NULL
        AND p.end_time IS NULL
        <if test="null != businessKeys and businessKeys.size()>0">
            AND p.business_key in
            <foreach collection="businessKeys" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        p.historic_task_id
    </select>

    <select id="getPendingByTaskId" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p
        WHERE p.historic_task_id = #{historicTaskId}
        AND p.assignee_type = #{assigneeType}
        ORDER BY p.id DESC
        LIMIT 1
    </select>

    <select id="findLogsByBusiness" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM
        business_processing_log p
        WHERE p.business_key = #{businessKey}
        AND p.business_type LIKE CONCAT(#{businessType},'%')
        AND p.historic_task_id = #{historicTaskId}
        <if test="assigneeType != null and assigneeType !=''">
            AND p.assignee_type = #{assigneeType}
        </if>
        <choose>
            <when test="limit != null and limit == true">
                ORDER BY p.gmt_create DESC
                LIMIT 1
            </when>
            <otherwise>
                ORDER BY p.gmt_create ASC
            </otherwise>
        </choose>
    </select>

    <select id="findCompletedByBusiness" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM
        business_processing_log p
        WHERE
        p.business_key = #{businessKey}
        <if test="businessType == 'FinancialCustomer'">
            AND p.business_type = #{businessType}
        </if>
        <if test="businessType != 'FinancialCustomer'">
            AND p.business_type LIKE CONCAT(#{businessType},'%')
        </if>
        AND p.form_business_id IS NOT NULL
        AND p.end_time IS NOT NULL
        ORDER BY
        p.gmt_create ASC
    </select>

    <select id="findBusinessProgressStatistics" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p
        INNER JOIN
        (SELECT MAX(id) AS id FROM business_processing_log GROUP BY historic_task_id) o2
        ON p.id = o2.id
        <where>
            ISNULL(p.end_time) AND p.assignee_type = #{assigneeType}
            <if test="businessType != null and businessType !='' ">
                AND p.business_type = #{businessType}
            </if>
            <if test="null != userIdAllList and userIdAllList.size()>0">
                AND p.assignee_id in
                <foreach collection="userIdAllList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            OR(
            <if test="startTime != null">
                <![CDATA[ p.end_time >= #{startTime}]]>
            </if>
            <if test="endTime != null">
                <![CDATA[AND p.end_time <= #{endTime}]]>
            </if>
            AND p.assignee_type = #{assigneeType}
            <if test="businessType != null and businessType !='' ">
                AND p.business_type = #{businessType}
            </if>
            <if test="null != userIdAllList and userIdAllList.size()>0">
                AND p.assignee_id in
                <foreach collection="userIdAllList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            )

        </where>
    </select>

    <select id="findBusinessProgressByIds" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p
        <where>
            <if test="null != businessProcessingLogIdList and businessProcessingLogIdList.size()>0">
                AND p.id in
                <foreach collection="businessProcessingLogIdList" item="item" index="index" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="findByHistoricTaskId" parameterType="java.lang.String" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p WHERE P.historic_task_id = #{taskId}
    </select>

    <select id="getByBusinessTypeBusinessKey" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM
        business_processing_log p
        WHERE
        p.business_key = #{businessKey}
        AND p.business_type LIKE CONCAT(#{businessType},'%')
        ORDER BY
        p.id DESC
        LIMIT 1
    </select>

    <select id="listForNullValueOfEndTime" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM
        business_processing_log p
        WHERE
        p.business_key = #{businessKey}
        AND p.business_type = #{businessType}
        AND p.end_time IS NULL
    </select>

    <select id="getbyFormBusinessIdBusinessKeyTaskDefKeyBusinessType" resultMap="processingLogResult">
        select
        <include refid="fields"/>
        from
        business_processing_log p
        WHERE
        p.business_key = #{businessKey}
        AND p.business_type = #{businessType}
        and p.form_business_id = #{formBusinessId}
        and p.task_def_key = #{taskDefKey}
    </select>

    <select id="getLastByBusinessKeyBusinessTypeTaskName" resultMap="processingLogResult">
        select
        <include refid="fields"/>
        from
        business_processing_log p
        WHERE
        p.business_key = #{businessKey}
        AND p.business_type = #{businessType}
        and p.task_name = #{taskName}
        ORDER BY
        p.id DESC
        LIMIT 1
    </select>

    <select id="findLatestByTaskDefKey" resultMap="processingLogResult">
        SELECT
        <include refid="fields"/>
        FROM business_processing_log p
        <where>
            <if test="taskDefKey">
                AND p.task_def_key = #{taskDefKey}
            </if>
            <if test="businessType">
                AND p.business_type = #{businessType}
            </if>
            <if test="businessKey">
                AND p.business_key = #{businessKey}
            </if>
        </where>
        ORDER BY p.id DESC LIMIT 1
    </select>

    <select id="getCLogByBusinessKeyBusinessType" resultMap="processingLogResult">

        SELECT
        <include refid="fields"/>
        FROM (
        SELECT a.*
        FROM business_processing_log a
        WHERE a.business_key = #{businessKey}
        AND a.business_type LIKE CONCAT(#{businessType},'%')
        AND a.assignee_id = 1
        AND a.end_time is not null
        ORDER BY gmt_create DESC limit 99999
        ) p
        GROUP BY p.task_def_key
        ORDER BY p.gmt_create ASC
    </select>
</mapper>
