<?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.TaskMapper">
  <resultMap id="BaseResultMap" type="org.jeecg.module.custom.ocr.dataobject.Task">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="tenant_code" jdbcType="VARCHAR" property="tenantCode" />
    <result column="access_key" jdbcType="VARCHAR" property="accessKey" />
    <result column="timestamp" jdbcType="BIGINT" property="timestamp" />
    <result column="signature" jdbcType="VARCHAR" property="signature" />
    <result column="tenant_no" jdbcType="BIGINT" property="tenantNo" />
    <result column="task_no" jdbcType="BIGINT" property="taskNo" />
    <result column="account_no" jdbcType="BIGINT" property="accountNo" />
    <result column="account_name" jdbcType="VARCHAR" property="accountName" />
    <result column="project_no" jdbcType="BIGINT" property="projectNo" />
    <result column="project_name" jdbcType="VARCHAR" property="projectName" />
    <result column="task_form_no" jdbcType="BIGINT" property="taskFormNo" />
    <result column="plan_no" jdbcType="BIGINT" property="planNo" />
    <result column="plan_name" jdbcType="VARCHAR" property="planName" />
    <result column="plan_child_no" jdbcType="BIGINT" property="planChildNo" />
    <result column="plan_child_name" jdbcType="VARCHAR" property="planChildName" />
    <result column="task_id" jdbcType="BIGINT" property="taskId" />
    <result column="task_name" jdbcType="VARCHAR" property="taskName" />
    <result column="user_no" jdbcType="BIGINT" property="userNo" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="submit_time" jdbcType="BIGINT" property="submitTime" />
    <result column="live_photo" jdbcType="VARCHAR" property="livePhoto" />
    <result column="dynamic_fields" jdbcType="VARCHAR" property="dynamicFields" />
  </resultMap>

  <sql id="Base_Column_List">
    tenant_code, access_key, timestamp, signature, tenant_no, task_no, account_no,
    account_name, project_no, project_name, task_form_no, plan_no, plan_name,
    plan_child_no, plan_child_name, task_id, task_name, user_no, user_name,
    submit_time, live_photo, dynamic_fields
  </sql>

  <insert id="save" parameterType="org.jeecg.module.custom.ocr.dataobject.Task" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO task (
    <include refid="Base_Column_List" />
    ) VALUES (
    #{tenantCode},
    #{accessKey},
    #{timestamp},
    #{signature},
    #{tenantNo},
    #{taskNo},
    #{accountNo},
    #{accountName},
    #{projectNo},
    #{projectName},
    #{taskFormNo},
    #{planNo},
    #{planName},
    #{planChildNo},
    #{planChildName},
    #{taskId},
    #{taskName},
    #{userNo},
    #{userName},
    #{submitTime},
    #{livePhoto},
    #{dynamicFields}
    )
  </insert>

  <delete id="deleteByTaskNo" parameterType="java.lang.Long">
    DELETE FROM task WHERE task_no = #{taskNo}
  </delete>

  <delete id="deleteByTaskNos" parameterType="java.lang.Long">
    DELETE FROM task WHERE task_no IN
    <foreach collection="taskNos" item="taskNo" open="(" separator="," close=")">
      #{taskNo}
    </foreach>
  </delete>

  <select id="selectByAccountNoAndQueryConfig" resultType="org.jeecg.module.custom.ocr.dataobject.Task">
    SELECT *
    FROM task t
    WHERE
      UNIX_TIMESTAMP(STR_TO_DATE(
              JSON_UNQUOTE(JSON_EXTRACT(t.dynamic_fields, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(#{queryConfig}, '$.validateTimeColumn'))))),
              '%Y-%m-%d %H:%i:%s'
                     )) * 1000 BETWEEN
        UNIX_TIMESTAMP(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(#{queryConfig}, '$.startTime')), '%Y-%m-%d %H:%i:%s')) * 1000
        AND
        UNIX_TIMESTAMP(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(#{queryConfig}, '$.endTime')), '%Y-%m-%d %H:%i:%s')) * 1000
      AND EXISTS (
      SELECT 1
      FROM JSON_TABLE(
                   JSON_EXTRACT(#{queryConfig}, '$.validateColumn'),
                   '$[*]' COLUMNS (col VARCHAR(255) PATH '$')
           ) AS jt
      WHERE JSON_EXTRACT(t.dynamic_fields, CONCAT('$.', jt.col)) IS NOT NULL
        AND JSON_LENGTH(JSON_EXTRACT(t.dynamic_fields, CONCAT('$.', jt.col))) > 0
    )
  </select>

  <select id="selectByQueryConfig" parameterType="map" resultType="org.jeecg.module.custom.ocr.dataobject.Task">
    SELECT *
    FROM task
    WHERE
    <foreach collection="queryConfig.crossProjectLimit" item="limit" open="(" close=")" separator=" OR ">
      project_no = #{limit.project}
      AND ${limit.field4.name} BETWEEN #{queryConfig.startTime} AND #{queryConfig.endTime}
    </foreach>
  </select>

</mapper>
