<?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.jeecg.module.custom.ocr.dataDao.OcrPictureMybatisDao">
	<resultMap id="BaseResultMap" type="org.jeecg.module.custom.ocr.dataobject.OcrPicture">
		<id column="ID" property="id" jdbcType="BIGINT"/>
		<result column="create_by" property="createBy" jdbcType="VARCHAR"/>
		<result column="create_time" property="createTime" jdbcType="BIGINT"/>
		<result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
		<result column="update_time" property="updateTime" jdbcType="BIGINT"/>
		<result column="classificationid" property="classificationid" jdbcType="VARCHAR"/>
		<result column="pictureclassid" property="pictureclassid" jdbcType="VARCHAR"/>
		<result column="pictureclassscores" property="pictureclassscores" jdbcType="VARCHAR"/>
		<result column="iztrueorfalse" property="iztrueorfalse" jdbcType="INTEGER"/>
		<result column="judgeid" property="judgeid" jdbcType="BIGINT"/>
		<result column="judgedesc" property="judgedesc" jdbcType="VARCHAR"/>
		<result column="tenant_id" property="tenantId" jdbcType="BIGINT"/>
		<result column="tenantstatus" property="tenantstatus" jdbcType="VARCHAR"/>
		<result column="plantCode" property="plantcode" jdbcType="VARCHAR"/>
		<result column="tenantname" property="tenantname" jdbcType="VARCHAR"/>
		<result column="pictureid" property="pictureid" jdbcType="VARCHAR"/>
		<result column="picturename" property="picturename" jdbcType="VARCHAR"/>
		<result column="imgName" property="imgname" jdbcType="VARCHAR"/>
		<result column="imgUrl" property="imgurl" jdbcType="VARCHAR"/>
		<result column="picturestatus" property="picturestatus" jdbcType="VARCHAR"/>
		<result column="projectId" property="projectid" jdbcType="VARCHAR"/>
		<result column="projectIdName" property="projectidname" jdbcType="VARCHAR"/>
		<result column="projectstatus" property="projectstatus" jdbcType="VARCHAR"/>
		<result column="planId" property="planid" jdbcType="VARCHAR"/>
		<result column="planName" property="planname" jdbcType="VARCHAR"/>
		<result column="planstatus" property="planstatus" jdbcType="VARCHAR"/>
		<result column="planchildid" property="planchildid" jdbcType="VARCHAR"/>
		<result column="planchildname" property="planchildname" jdbcType="VARCHAR"/>
		<result column="planchildstatus" property="planchildstatus" jdbcType="VARCHAR"/>
		<result column="upTime" property="uptime" jdbcType="BIGINT"/>
		<result column="genTime" property="gentime" jdbcType="BIGINT"/>
		<result column="space" property="space" jdbcType="VARCHAR"/>
		<result column="source" property="source" jdbcType="VARCHAR"/>
		<result column="upHead" property="uphead" jdbcType="VARCHAR"/>
		<result column="upuserid" property="upuserid" jdbcType="VARCHAR"/>
		<result column="upName" property="upname" jdbcType="VARCHAR"/>
		<result column="releaseArea" property="releasearea" jdbcType="VARCHAR"/>
		<result column="releaseProvince" property="releaseprovince" jdbcType="VARCHAR"/>
		<result column="remark" property="remark" jdbcType="VARCHAR"/>
		<result column="taskName" property="taskname" jdbcType="VARCHAR"/>
		<result column="taskstatus" property="taskstatus" jdbcType="VARCHAR"/>
		<result column="work_status" property="workStatus" jdbcType="VARCHAR"/>
		<result column="categoryid" property="categoryid" jdbcType="BIGINT"/>
		<result column="taskchildpictureid" property="taskchildpictureid" jdbcType="VARCHAR"/>
		<result column="field1" property="field1" jdbcType="VARCHAR"/>
		<result column="field2" property="field2" jdbcType="VARCHAR"/>
		<result column="field3" property="field3" jdbcType="VARCHAR"/>
		<result column="field4" property="field4" jdbcType="VARCHAR"/>
		<result column="field5" property="field5" jdbcType="VARCHAR"/>
		<result column="field6" property="field6" jdbcType="VARCHAR"/>
		<result column="field7" property="field7" jdbcType="VARCHAR"/>
		<result column="field8" property="field8" jdbcType="VARCHAR"/>
		<result column="field9" property="field9" jdbcType="VARCHAR"/>
		<result column="field10" property="field10" jdbcType="VARCHAR"/>
		<result column="field11" property="field11" jdbcType="BIGINT"/>
		<result column="field12" property="field12" jdbcType="VARCHAR"/>
		<result column="field13" property="field13" jdbcType="VARCHAR"/>
		<result column="field14" property="field14" jdbcType="VARCHAR"/>
		<result column="field15" property="field15" jdbcType="VARCHAR"/>
		<result column="field16" property="field16" jdbcType="VARCHAR"/>
		<result column="field17" property="field17" jdbcType="VARCHAR"/>
		<result column="field18" property="field18" jdbcType="VARCHAR"/>
		<result column="submit_date_timestamp" property="submitDateTimestamp" jdbcType="VARCHAR"/>
		<result column="photo_date_timestamp" property="photoDateTimestamp" jdbcType="VARCHAR"/>
		<result column="suspiciousfile" property="suspiciousfile" jdbcType="INTEGER"/>
		<result column="STATES" property="states" jdbcType="INTEGER"/>
		<result column="TASKID" property="taskId" jdbcType="VARCHAR"/>
		<result column="historyStates" property="historyStates" jdbcType="INTEGER"/>
		<result column="taskchildId" property="taskchildpictureid"/>

		<association property="pictureInfo" javaType="org.jeecg.module.custom.ocr.dataobject.OcrPictureInfo">
			<result column="pictureid" property="pictureId"/>
			<result column="img_size" property="imgSize"/>
			<result column="img_name" property="imgName"/>
			<result column="img_format" property="imgFormat"/>
			<result column="img_measure" property="imgMeasure"/>
			<result column="upload_time" property="uploadTime"/>
			<result column="create_time" property="createTime"/>
			<result column="img_space" property="imgSpace"/>
			<result column="tag_time" property="tagTime"/>
			<result column="source" property="source"/>
		</association>
	</resultMap>
	<sql id="Base_Column_List">
		ID AS id,
		create_by AS createBy,
		create_time AS createTime,
		update_by AS updateBy,
		update_time AS updateTime,
		classificationid AS classificationid,
		pictureclassid AS pictureclassid,
		pictureclassscores AS pictureclassscores,
		iztrueorfalse AS iztrueorfalse,
		judgeid AS judgeid,
		judgedesc AS judgedesc,
		tenant_id AS tenantId,
		tenantstatus AS tenantstatus,
		plantCode AS plantcode,
		tenantname AS tenantname,
		pictureid AS pictureid,
		picturename AS picturename,
		imgName AS imgname,
		imgUrl AS imgurl,
		picturestatus AS picturestatus,
		projectId AS projectid,
		projectIdName AS projectidname,
		projectstatus AS projectstatus,
		planId AS planid,
		planName AS planname,
		planstatus AS planstatus,
		planchildid AS planchildid,
		planchildname AS planchildname,
		planchildstatus AS planchildstatus,
		upTime AS uptime,
		genTime AS gentime,
		space AS space,
		source AS source,
		upHead AS uphead,
		upuserid AS upuserid,
		upName AS upname,
		releaseArea AS releasearea,
		releaseProvince AS releaseprovince,
		remark AS remark,
		taskName AS taskname,
		taskstatus AS taskstatus,
		work_status AS workStatus,
		categoryid AS categoryid,
		taskchildpictureid AS taskchildpictureid,
		field1 AS field1,
		field2 AS field2,
		field3 AS field3,
		field4 AS field4,
		field5 AS field5,
		field6 AS field6,
		field7 AS field7,
		field8 AS field8,
		field9 AS field9,
		field10 AS field10,
		field11 AS field11,
		field12 AS field12,
		field13 AS field13,
		field14 AS field14,
		field15 AS field15,
		field16 AS field16,
		field17 AS field17,
		field18 AS field18,
	    suspiciousfile AS suspiciousfile
	</sql>

	<sql id="Base_Where">
		<where>
			<trim prefixOverrides="and|or">
				<if test="id != null"> and ID ${id.dataOp} ${id.likestar}#{id.value}${id.likeend}</if>
				<if test="createBy != null"> and create_by ${createBy.dataOp} ${createBy.likestar}#{createBy.value}${createBy.likeend}</if>
				<if test="createTime != null"> and create_time ${createTime.dataOp} ${createTime.likestar}#{createTime.value}${createTime.likeend}</if>
				<if test="updateBy != null"> and update_by ${updateBy.dataOp} ${updateBy.likestar}#{updateBy.value}${updateBy.likeend}</if>
				<if test="updateTime != null"> and update_time ${updateTime.dataOp} ${updateTime.likestar}#{updateTime.value}${updateTime.likeend}</if>
				<if test="classificationid != null"> and classificationid ${classificationid.dataOp} ${classificationid.likestar}#{classificationid.value}${classificationid.likeend}</if>
				<if test="pictureclassid != null"> and pictureclassid ${pictureclassid.dataOp} ${pictureclassid.likestar}#{pictureclassid.value}${pictureclassid.likeend}</if>
				<if test="pictureclassscores != null"> and pictureclassscores ${pictureclassscores.dataOp} ${pictureclassscores.likestar}#{pictureclassscores.value}${pictureclassscores.likeend}</if>
				<if test="iztrueorfalse != null"> and iztrueorfalse ${iztrueorfalse.dataOp} ${iztrueorfalse.likestar}#{iztrueorfalse.value}${iztrueorfalse.likeend}</if>
				<if test="judgeid != null"> and judgeid ${judgeid.dataOp} ${judgeid.likestar}#{judgeid.value}${judgeid.likeend}</if>
				<if test="judgedesc != null"> and judgedesc ${judgedesc.dataOp} ${judgedesc.likestar}#{judgedesc.value}${judgedesc.likeend}</if>
				<if test="tenantId != null"> and tenant_id ${tenantId.dataOp} ${tenantId.likestar}#{tenantId.value}${tenantId.likeend}</if>
				<if test="tenantstatus != null"> and tenantstatus ${tenantstatus.dataOp} ${tenantstatus.likestar}#{tenantstatus.value}${tenantstatus.likeend}</if>
				<if test="plantcode != null"> and plantCode ${plantcode.dataOp} ${plantcode.likestar}#{plantcode.value}${plantcode.likeend}</if>
				<if test="tenantname != null"> and tenantname ${tenantname.dataOp} ${tenantname.likestar}#{tenantname.value}${tenantname.likeend}</if>
				<if test="pictureid != null"> and pictureid ${pictureid.dataOp} ${pictureid.likestar}#{pictureid.value}${pictureid.likeend}</if>
				<if test="picturename != null"> and picturename ${picturename.dataOp} ${picturename.likestar}#{picturename.value}${picturename.likeend}</if>
				<if test="imgname != null"> and imgName ${imgname.dataOp} ${imgname.likestar}#{imgname.value}${imgname.likeend}</if>
				<if test="imgurl != null"> and imgUrl ${imgurl.dataOp} ${imgurl.likestar}#{imgurl.value}${imgurl.likeend}</if>
				<if test="picturestatus != null"> and picturestatus ${picturestatus.dataOp} ${picturestatus.likestar}#{picturestatus.value}${picturestatus.likeend}</if>
				<if test="projectid != null"> and projectId ${projectid.dataOp} ${projectid.likestar}#{projectid.value}${projectid.likeend}</if>
				<if test="projectidname != null"> and projectIdName ${projectidname.dataOp} ${projectidname.likestar}#{projectidname.value}${projectidname.likeend}</if>
				<if test="projectstatus != null"> and projectstatus ${projectstatus.dataOp} ${projectstatus.likestar}#{projectstatus.value}${projectstatus.likeend}</if>
				<if test="planid != null"> and planId ${planid.dataOp} ${planid.likestar}#{planid.value}${planid.likeend}</if>
				<if test="planname != null"> and planName ${planname.dataOp} ${planname.likestar}#{planname.value}${planname.likeend}</if>
				<if test="planstatus != null"> and planstatus ${planstatus.dataOp} ${planstatus.likestar}#{planstatus.value}${planstatus.likeend}</if>
				<if test="planchildid != null"> and planchildid ${planchildid.dataOp} ${planchildid.likestar}#{planchildid.value}${planchildid.likeend}</if>
				<if test="planchildname != null"> and planchildname ${planchildname.dataOp} ${planchildname.likestar}#{planchildname.value}${planchildname.likeend}</if>
				<if test="planchildstatus != null"> and planchildstatus ${planchildstatus.dataOp} ${planchildstatus.likestar}#{planchildstatus.value}${planchildstatus.likeend}</if>
				<if test="uptime != null"> and upTime ${uptime.dataOp} ${uptime.likestar}#{uptime.value}${uptime.likeend}</if>
				<if test="gentime != null"> and genTime ${gentime.dataOp} ${gentime.likestar}#{gentime.value}${gentime.likeend}</if>
				<if test="space != null"> and space ${space.dataOp} ${space.likestar}#{space.value}${space.likeend}</if>
				<if test="source != null"> and source ${source.dataOp} ${source.likestar}#{source.value}${source.likeend}</if>
				<if test="uphead != null"> and upHead ${uphead.dataOp} ${uphead.likestar}#{uphead.value}${uphead.likeend}</if>
				<if test="upuserid != null"> and upuserid ${upuserid.dataOp} ${upuserid.likestar}#{upuserid.value}${upuserid.likeend}</if>
				<if test="upname != null"> and upName ${upname.dataOp} ${upname.likestar}#{upname.value}${upname.likeend}</if>
				<if test="releasearea != null"> and releaseArea ${releasearea.dataOp} ${releasearea.likestar}#{releasearea.value}${releasearea.likeend}</if>
				<if test="releaseprovince != null"> and releaseProvince ${releaseprovince.dataOp} ${releaseprovince.likestar}#{releaseprovince.value}${releaseprovince.likeend}</if>
				<if test="remark != null"> and remark ${remark.dataOp} ${remark.likestar}#{remark.value}${remark.likeend}</if>
				<if test="taskname != null"> and taskName ${taskname.dataOp} ${taskname.likestar}#{taskname.value}${taskname.likeend}</if>
				<if test="taskstatus != null"> and taskstatus ${taskstatus.dataOp} ${taskstatus.likestar}#{taskstatus.value}${taskstatus.likeend}</if>
				<if test="workStatus != null"> and work_status ${workStatus.dataOp} ${workStatus.likestar}#{workStatus.value}${workStatus.likeend}</if>
				<if test="categoryid != null"> and categoryid ${categoryid.dataOp} ${categoryid.likestar}#{categoryid.value}${categoryid.likeend}</if>
				<if test="taskchildpictureid != null"> and taskchildpictureid ${taskchildpictureid.dataOp} ${taskchildpictureid.likestar}#{taskchildpictureid.value}${taskchildpictureid.likeend}</if>
				<if test="field1 != null"> and field1 ${field1.dataOp} ${field1.likestar}#{field1.value}${field1.likeend}</if>
				<if test="field2 != null"> and field2 ${field2.dataOp} ${field2.likestar}#{field2.value}${field2.likeend}</if>
				<if test="field3 != null"> and field3 ${field3.dataOp} ${field3.likestar}#{field3.value}${field3.likeend}</if>
				<if test="field4 != null"> and field4 ${field4.dataOp} ${field4.likestar}#{field4.value}${field4.likeend}</if>
				<if test="field5 != null"> and field5 ${field5.dataOp} ${field5.likestar}#{field5.value}${field5.likeend}</if>
				<if test="field6 != null"> and field6 ${field6.dataOp} ${field6.likestar}#{field6.value}${field6.likeend}</if>
				<if test="field7 != null"> and field7 ${field7.dataOp} ${field7.likestar}#{field7.value}${field7.likeend}</if>
				<if test="field8 != null"> and field8 ${field8.dataOp} ${field8.likestar}#{field8.value}${field8.likeend}</if>
				<if test="field9 != null"> and field9 ${field9.dataOp} ${field9.likestar}#{field9.value}${field9.likeend}</if>
				<if test="field10 != null"> and field10 ${field10.dataOp} ${field10.likestar}#{field10.value}${field10.likeend}</if>
				<if test="field11 != null"> and field11 ${field11.dataOp} ${field11.likestar}#{field11.value}${field11.likeend}</if>
				<if test="field12 != null"> and field12 ${field12.dataOp} ${field12.likestar}#{field12.value}${field12.likeend}</if>
				<if test="field13 != null"> and field13 ${field13.dataOp} ${field13.likestar}#{field13.value}${field13.likeend}</if>
				<if test="field14 != null"> and field14 ${field14.dataOp} ${field14.likestar}#{field14.value}${field14.likeend}</if>
				<if test="field15 != null"> and field15 ${field15.dataOp} ${field15.likestar}#{field15.value}${field15.likeend}</if>
				<if test="field16 != null"> and field16 ${field16.dataOp} ${field16.likestar}#{field16.value}${field16.likeend}</if>
				<if test="field17 != null"> and field17 ${field17.dataOp} ${field17.likestar}#{field17.value}${field17.likeend}</if>
				<if test="field18 != null"> and field18 ${field18.dataOp} ${field18.likestar}#{field18.value}${field18.likeend}</if>
				<if test="suspiciousfile != null"> and suspiciousfile ${suspiciousfile.dataOp} ${suspiciousfile.likestar}#{suspiciousfile.value}${suspiciousfile.likeend}</if>
			</trim>
		</where>
		<if test="page != null">
			<if test="page.sortname != null"> order by ${page.sortname} ${page.sortorder}</if>
			limit ${page.start}, ${page.Pagesize}
		</if>
	</sql>

	<select id="getPackagePictureInfo" resultMap="BaseResultMap">
        SELECT t2.*,
               t4.STATES,
               t4.TASKID,
               t4.ID         taskchildId,
               COALESCE(
                       (SELECT t5.STATSHIS
                        FROM oa_userapprove_t t5
                        WHERE t5.FORMID = t4.ID
                        ORDER BY t5.CREATEDATE DESC
                        LIMIT 1),
                       1) AS historyStates
        FROM ocr_check_duplicate_result t1
                 LEFT JOIN ocr_picture t2 ON t1.picture_id = t2.ID
                 left join ocr_taskchild_picture t4 on t2.ID = t4.PICTUREID
        where t1.check_duplicate_id = #{packageId}
          and t2.suspiciousfile = 0
        <if test="pictureId != null and pictureId != ''">
            and t1.picture_id = #{pictureId}
        </if>
        ORDER BY t1.max_similarity desc, t1.similarity_group_order asc, t2.submit_date_timestamp asc
        LIMIT 1
    </select>

	<select id="selectByRemark" resultMap="BaseResultMap">
		SELECT
		<include refid="Base_Column_List"/>
		FROM
		ocr_picture
		WHERE `remark` = #{remark}
		LIMIT 1
	</select>

	<select id="getPictureApproveResult" resultType="java.lang.String">
        SELECT CONCAT('审批节点：', t1.TASKNAME, '，审批结果：', (CASE
                                                                   WHEN t1.STATES = 5 THEN '审批不通过'
                                                                   WHEN t1.STATES = 3 THEN '审批通过'
                                                                   ELSE '审批中' END), '，不通过原因：', t2.REASON,
                      '，备注：', t2.TASKCOMMENT)
        FROM ocr_taskchild_picture t1
                 LEFT JOIN oa_userapprove_t t2 ON t1.ID = t2.FORMID
        where t1.PICTUREID = #{id,jdbcType=BIGINT} and t2.STATSHIS = 3
    </select>
</mapper>
