<?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.sunxung.factoring.mapper.project.BusinessProcessingLogMapper">

	<resultMap type="BusinessProcessingLog" id="processingLogResult">
		<result property="id" column="id" />
		<result property="busnessKey" column="busness_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="userCreate" column="user_create" />
		<result property="userModified" column="user_modified" />
		<result property="gmtCreate" column="gmt_create" />
		<result property="gmtModified" column="gmt_modified" />
	</resultMap>

	<sql id="fields">
		p.id,
		p.busness_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.user_create,
		p.user_modified,
		p.gmt_create,
		p.gmt_modified
	</sql>

	<insert id="add" useGeneratedKeys="true" keyColumn="id" keyProperty="id" parameterType="BusinessProcessingLog">
		INSERT INTO business_processing_log
		(
			busness_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,user_create
		)
		VALUES
		(
			#{busnessKey},#{businessType},#{historicTaskId},#{taskName},#{taskDefKey},#{formBusinessId},#{assignee.id},#{assigneeType},
			#{startTime},#{endTime},#{overdueDays},#{workingDay},#{status},#{userCreate}
		)
	</insert>

	<update id="update" parameterType="BusinessProcessingLog">
		UPDATE business_processing_log
		SET
			form_business_id=#{formBusinessId},
			end_time=#{endTime},
			status=#{status},
			overdue_days=#{overdueDays},
			is_working_day=#{workingDay},
			user_modified=#{userModified}
		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
			historic_task_id
		FROM
			( SELECT p.id,p.historic_task_id + 0 AS historic_task_id FROM business_processing_log p WHERE p.busness_key = #{businessKey} AND p.business_type = #{businessType} ORDER BY ID ASC LIMIT 99999 ) r
		GROUP BY
			r.historic_task_id
		-->
		SELECT p.historic_task_id FROM business_processing_log p WHERE p.busness_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.busness_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.busness_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.busness_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.busness_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">
					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.busness_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" parameterType="SearchBusinessProcessingVo">
		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" parameterType="SearchBusinessProcessingVo">
		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.busness_key = #{businessKey}
		    AND p.business_type LIKE CONCAT(#{businessType},'%')
        ORDER BY
            p.id DESC
        LIMIT 1
	</select>
	<select id="listForNullValueOfEndTime"
			resultType="com.sunxung.factoring.entity.project.BusinessProcessingLog">
		SELECT
		<include refid="fields"/>
		FROM
		business_processing_log p
		WHERE
		p.busness_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.busness_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.busness_key = #{businessKey}
		AND p.business_type = #{businessType}
		and p.task_name = #{taskName}
		ORDER BY
		p.id DESC
		LIMIT 1
	</select>
</mapper>
