<?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.bonus.sgzb.app.mapper.TmTaskMapper">

  <resultMap id="BaseResultMap" type="com.bonus.sgzb.app.domain.TmTask">
    <!--@Table tm_task-->
    <id column="task_id" jdbcType="BIGINT" property="taskId" />
    <result column="task_type" jdbcType="INTEGER" property="taskType" />
    <result column="task_status" jdbcType="INTEGER" property="taskStatus" />
    <result column="code" jdbcType="VARCHAR" property="code" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="company_id" jdbcType="INTEGER" property="companyId" />
  </resultMap>

  <resultMap id="ResultMap" type="com.bonus.sgzb.app.domain.TmTask">
    <!--@Table tm_task-->
    <id column="task_id" jdbcType="BIGINT" property="taskId" />
    <result column="task_type" jdbcType="INTEGER" property="taskType" />
    <result column="task_status" jdbcType="INTEGER" property="taskStatus" />
    <result column="code" jdbcType="VARCHAR" property="code" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
    <result column="company_id" jdbcType="INTEGER" property="companyId" />

    <collection property="leaseApplyDetails" ofType="com.bonus.sgzb.app.domain.TmTask">
      <id column="dId" property="id" jdbcType="INTEGER" />
      <result column="code" jdbcType="VARCHAR" property="code" />
      <result column="create_by" jdbcType="VARCHAR" property="createBy" />
      <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
      <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
      <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
      <result column="remark" jdbcType="VARCHAR" property="remark" />
      <result column="company_id" jdbcType="INTEGER" property="companyId" />
    </collection>

  </resultMap>

  <sql id="Base_Column_List">
    task_id, task_type, task_status, code, create_by, create_time, update_by, update_time,
    remark, company_id
  </sql>

  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from tm_task
    where task_id = #{taskId,jdbcType=BIGINT}
  </select>

  <update id="deleteTaskByPrimaryKey" >
    update tm_task set `status` = '0'
    where task_id = #{taskId}
  </update>

  <update id="deleteTaskInfoByTaskId" >
    delete from lease_apply_info
    where task_id = #{taskId}
  </update>

    <delete id="deleteDetailsByTaskId" parameterType="java.lang.String">
        delete from lease_apply_details where id = #{id}
    </delete>

  <delete id="deleteDetailsByParentId" parameterType="java.lang.String">
    delete from lease_apply_details where parennt_id = #{parentId}
  </delete>
  <delete id="deleteTaskAgreementByTaskId">
    delete from tm_task_agreement where task_id = #{taskId}
  </delete>

  <insert id="insert" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    insert into tm_task (task_type, task_status, code, create_by, create_time, update_by, update_time, remark, company_id)
    values (#{taskType,jdbcType=INTEGER}, #{taskStatus,jdbcType=INTEGER}, #{code,jdbcType=VARCHAR}, #{createBy,jdbcType=VARCHAR},
    #{createTime,jdbcType=TIMESTAMP}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, #{remark,jdbcType=VARCHAR}, #{companyId,jdbcType=INTEGER})
  </insert>

  <insert id="insertSelective" keyColumn="task_id" keyProperty="id" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    insert into tm_task
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="taskType != null">
        task_type,
      </if>
      <if test="taskStatus != null">
        task_status,
      </if>
      <if test="code != null and code != ''">
        code,
      </if>
      <if test="createBy != null and createBy != ''">
        create_by,
      </if>
        create_time,
      <if test="updateBy != null and updateBy != ''">
        update_by,
      </if>
        update_time,
      <if test="remark != null and remark != ''">
        remark,
      </if>
      <if test="companyId != null">
        company_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="taskType != null">
        #{taskType,jdbcType=INTEGER},
      </if>
      <if test="taskStatus != null">
        #{taskStatus,jdbcType=INTEGER},
      </if>
      <if test="code != null and code != ''">
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="createBy != null and createBy != ''">
        #{createBy,jdbcType=VARCHAR},
      </if>
        now(),
      <if test="updateBy != null and updateBy != ''">
        #{updateBy,jdbcType=VARCHAR},
      </if>
        now(),
      <if test="remark != null and remark != ''">
        #{remark,jdbcType=VARCHAR},
      </if>
      <if test="companyId != null">
        #{companyId,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.bonus.sgzb.app.domain.TmTask">
    update tm_task
    <set>
      <if test="taskType != null">
        task_type = #{taskType,jdbcType=INTEGER},
      </if>
      <if test="taskStatus != null">
        task_status = #{taskStatus,jdbcType=INTEGER},
      </if>
      <if test="updateBy != null and updateBy != ''">
        update_by = #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="remark != null and remark != ''">
        remark = #{remark,jdbcType=VARCHAR},
      </if>
    </set>
    where task_id = #{taskId,jdbcType=BIGINT}
  </update>

  <update id="updateByPrimaryKey" parameterType="com.bonus.sgzb.app.domain.TmTask">
    update tm_task
    set task_type = #{taskType,jdbcType=INTEGER},
      task_status = #{taskStatus,jdbcType=INTEGER},
      code = #{code,jdbcType=VARCHAR},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      remark = #{remark,jdbcType=VARCHAR},
      company_id = #{companyId,jdbcType=INTEGER}
    where task_id = #{taskId,jdbcType=BIGINT}
  </update>

  <update id="updateBatch" parameterType="java.util.List">
    <!--@mbg.generated-->
    update tm_task
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="task_type = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.taskType,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="task_status = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.taskStatus,jdbcType=INTEGER}
        </foreach>
      </trim>
      <trim prefix="code = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.code,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="create_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.createBy,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="create_time = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.createTime,jdbcType=TIMESTAMP}
        </foreach>
      </trim>
      <trim prefix="update_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.updateBy,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="update_time = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.updateTime,jdbcType=TIMESTAMP}
        </foreach>
      </trim>
      <trim prefix="remark = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.remark,jdbcType=VARCHAR}
        </foreach>
      </trim>
      <trim prefix="company_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.companyId,jdbcType=INTEGER}
        </foreach>
      </trim>
    </trim>
    where task_id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.taskId,jdbcType=BIGINT}
    </foreach>
  </update>

  <update id="updateBatchSelective" parameterType="java.util.List">
    <!--@mbg.generated-->
    update tm_task
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="task_type = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.taskType != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.taskType,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="task_status = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.taskStatus != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.taskStatus,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
      <trim prefix="code = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.code != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.code,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="create_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.createBy != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.createBy,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="create_time = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.createTime != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.createTime,jdbcType=TIMESTAMP}
          </if>
        </foreach>
      </trim>
      <trim prefix="update_by = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.updateBy != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.updateBy,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="update_time = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.updateTime != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.updateTime,jdbcType=TIMESTAMP}
          </if>
        </foreach>
      </trim>
      <trim prefix="remark = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.remark != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.remark,jdbcType=VARCHAR}
          </if>
        </foreach>
      </trim>
      <trim prefix="company_id = case" suffix="end,">
        <foreach collection="list" index="index" item="item">
          <if test="item.companyId != null">
            when task_id = #{item.taskId,jdbcType=BIGINT} then #{item.companyId,jdbcType=INTEGER}
          </if>
        </foreach>
      </trim>
    </trim>
    where task_id in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
      #{item.taskId,jdbcType=BIGINT}
    </foreach>
  </update>

  <insert id="batchInsert" keyColumn="task_id" keyProperty="taskId" parameterType="map" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into tm_task
    (task_type, task_status, code, create_by, create_time, update_by, update_time, remark,
      company_id)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.taskType,jdbcType=INTEGER}, #{item.taskStatus,jdbcType=INTEGER}, #{item.code,jdbcType=VARCHAR},
        #{item.createBy,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateBy,jdbcType=VARCHAR},
        #{item.updateTime,jdbcType=TIMESTAMP}, #{item.remark,jdbcType=VARCHAR}, #{item.companyId,jdbcType=INTEGER}
        )
    </foreach>
  </insert>

  <insert id="insertOrUpdate" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into tm_task
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="taskId != null">
        task_id,
      </if>
      task_type,
      task_status,
      code,
      create_by,
      create_time,
      update_by,
      update_time,
      remark,
      company_id,
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="taskId != null">
        #{taskId,jdbcType=BIGINT},
      </if>
      #{taskType,jdbcType=INTEGER},
      #{taskStatus,jdbcType=INTEGER},
      #{code,jdbcType=VARCHAR},
      #{createBy,jdbcType=VARCHAR},
      #{createTime,jdbcType=TIMESTAMP},
      #{updateBy,jdbcType=VARCHAR},
      #{updateTime,jdbcType=TIMESTAMP},
      #{remark,jdbcType=VARCHAR},
      #{companyId,jdbcType=INTEGER},
    </trim>
    on duplicate key update
    <trim suffixOverrides=",">
      <if test="taskId != null">
        task_id = #{taskId,jdbcType=BIGINT},
      </if>
      task_type = #{taskType,jdbcType=INTEGER},
      task_status = #{taskStatus,jdbcType=INTEGER},
      code = #{code,jdbcType=VARCHAR},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      remark = #{remark,jdbcType=VARCHAR},
      company_id = #{companyId,jdbcType=INTEGER},
    </trim>
  </insert>

  <insert id="insertOrUpdateSelective" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into tm_task
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="taskId != null">
        task_id,
      </if>
      <if test="taskType != null">
        task_type,
      </if>
      <if test="taskStatus != null">
        task_status,
      </if>
      <if test="code != null and code != ''">
        code,
      </if>
      <if test="createBy != null and createBy != ''">
        create_by,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="updateBy != null and updateBy != ''">
        update_by,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
      <if test="remark != null and remark != ''">
        remark,
      </if>
      <if test="companyId != null">
        company_id,
      </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="taskId != null">
        #{taskId,jdbcType=BIGINT},
      </if>
      <if test="taskType != null">
        #{taskType,jdbcType=INTEGER},
      </if>
      <if test="taskStatus != null">
        #{taskStatus,jdbcType=INTEGER},
      </if>
      <if test="code != null and code != ''">
        #{code,jdbcType=VARCHAR},
      </if>
      <if test="createBy != null and createBy != ''">
        #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null">
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null and updateBy != ''">
        #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null">
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="remark != null and remark != ''">
        #{remark,jdbcType=VARCHAR},
      </if>
      <if test="companyId != null">
        #{companyId,jdbcType=INTEGER},
      </if>
    </trim>
    on duplicate key update
    <trim suffixOverrides=",">
      <if test="taskId != null">
        task_id = #{taskId,jdbcType=BIGINT},
      </if>
      <if test="taskType != null">
        task_type = #{taskType,jdbcType=INTEGER},
      </if>
      <if test="taskStatus != null">
        task_status = #{taskStatus,jdbcType=INTEGER},
      </if>
      <if test="code != null and code != ''">
        code = #{code,jdbcType=VARCHAR},
      </if>
      <if test="createBy != null and createBy != ''">
        create_by = #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null">
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null and updateBy != ''">
        update_by = #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="remark != null and remark != ''">
        remark = #{remark,jdbcType=VARCHAR},
      </if>
      <if test="companyId != null">
        company_id = #{companyId,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>

    <insert id="insertAgreement">
        INSERT INTO tm_task_agreement ( `task_id`, `agreement_id`, `create_by`, `create_time`, `company_id` )
        VALUES(#{id},#{agreementId},#{createBy},NOW(),#{companyId})
    </insert>


  <update id="updateAgreementByTask">
      update tm_task_agreement set agreement_id = #{agreementId}, update_time = now()
      where task_id = #{taskId}
  </update>

  <select id="getAuditListByLeaseTmTask" resultType="com.bonus.sgzb.app.domain.TmTask">
      SELECT DISTINCT
      tt.*, su.phonenumber AS phoneNumber, sd.dept_name as deptName,
      bpl.lot_id as proId,bpl.lot_name as proName,
      bui.unit_id as unitId,bui.unit_name as unitName,
      lai.lease_person as leasePerson, lai.phone as leasePhone, tt.create_by as applyFor,d.`name` as taskName,lai.lease_type as leaseType,lai.estimate_lease_time as estimateLeaseTime,
      case when d.id = '31' then lai.company_audit_remark
      when d.id = '32' then lai.dept_audit_remark
      when d.id = '33' then lai.direct_audit_remark
      when d.id = '98' then lai.company_audit_remark
      when d.id = '99' then lai.dept_audit_remark
      when d.id = '100' then lai.direct_audit_remark
      end examineStatus,
      d.id as examineStatusId,
      bai.agreement_code as agreementCode,
      tt.create_time as createTimes, tt.update_time as updateTimes
      FROM
      tm_task tt
      LEFT JOIN sys_user su ON tt.create_by = su.user_name
      LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
      LEFT JOIN tm_task_agreement tta ON tt.task_id = tta.task_id
      LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
      LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
      LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
      LEFT JOIN lease_apply_info lai ON lai.task_id = tt.task_id
      LEFT JOIN sys_dic d ON d.id = tt.task_status
      WHERE
      tt.task_type = '29' and tt.status = '1'
      <if test="record.taskId != null and record.taskId != '' ">
          AND tt.task_id = #{record.taskId}
      </if>

      <if test="record.startTime != null and record.startTime != '' and record.endTime != null and record.endTime != '' ">
          AND tt.update_time BETWEEN CONCAT(#{record.startTime}, ' 00:00:00') AND CONCAT(#{record.endTime}, ' 23:59:59')
      </if>

      <if test="record.unitId != null and record.unitId != ''">
          AND bui.unit_id = #{record.unitId}
      </if>

      <if test="record.projectId != null and record.projectId != ''">
          AND bpl.lot_id = #{record.projectId}
      </if>

      <if test="record.keyWord != null and record.keyWord != ''">
          AND (bai.agreement_code like concat('%', #{record.keyWord}, '%') or
               tt.code like concat('%', #{record.keyWord}, '%'))
      </if>
      GROUP BY tt.task_id
      ORDER BY tt.update_time DESC
  </select>

  <select id="getAuditManageListByLeaseTmTask" resultType="com.bonus.sgzb.app.domain.TmTask">
      SELECT DISTINCT
      tt.*, su.phonenumber AS phoneNumber, sd.dept_name as deptName,
      bpl.lot_id as proId,bpl.lot_name as proName,
      bui.unit_id as unitId,bui.unit_name as unitName,
      lai.lease_person as leasePerson, lai.phone as leasePhone,lai.lease_type as leaseType, lai.estimate_lease_time as estimateLeaseTime,tt.create_by as applyFor,d.`name` as taskName,
      case when d.id = '31' then lai.company_audit_remark
      when d.id = '32' then lai.dept_audit_remark
      when d.id = '33' then lai.direct_audit_remark
      when d.id = '98' then lai.company_audit_remark
      when d.id = '99' then lai.dept_audit_remark
      when d.id = '100' then lai.direct_audit_remark
      end examineStatus,
      d.id as examineStatusId,
      bai.agreement_code as agreementCode,
      tt.create_time as createTimes, tt.update_time as updateTimes
      FROM
      tm_task tt
      LEFT JOIN sys_user su ON tt.create_by = su.user_name
      LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
      LEFT JOIN tm_task_agreement tta ON tt.task_id = tta.task_id
      LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
      LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
      LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
      LEFT JOIN lease_apply_info lai ON lai.task_id = tt.task_id
      LEFT JOIN sys_dic d ON d.id = tt.task_status
      WHERE
      tt.task_type = '29' and tt.status = '1'
      <if test="record.taskId != null and record.taskId != '' ">
          AND tt.task_id = #{record.taskId}
      </if>

      <if test="record.startTime != null and record.startTime != '' and record.endTime != null and record.endTime != '' ">
          AND tt.update_time BETWEEN CONCAT(#{record.startTime}, ' 00:00:00') AND CONCAT(#{record.endTime}, ' 23:59:59')
      </if>

      <if test="record.unitId != null and record.unitId != ''">
          AND bui.unit_id = #{record.unitId}
      </if>

      <if test="record.projectId != null and record.projectId != ''">
          AND bpl.lot_id = #{record.projectId}
      </if>

      <if test="record.keyWord != null and record.keyWord != ''">
          AND bai.agreement_code like concat('%', #{record.keyWord}, '%')
      </if>
      GROUP BY tt.task_id
      ORDER BY tt.update_time DESC
  </select>

  <select id="getAuditListByLeaseInfo" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT
      lai.*
    FROM
      lease_apply_info lai
    WHERE
      lai.task_id = #{record.taskId} AND lai.`code` = #{record.code}
  </select>
  <select id="getAuditManageListByLeaseInfo" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT
      lai.*
    FROM
      lease_apply_info lai
    WHERE
      lai.task_id = #{record.taskId} AND lai.`code` = #{record.code}
  </select>

  <select id="getLeaseApplyDetails" resultType="com.bonus.sgzb.app.domain.LeaseApplyDetails">
    SELECT
        lad.*, mt.type_name AS typeModelName, mt1.type_name AS typeName,mt.unit_name as unitName, mt.manage_type as manageType,
        case WHEN mt.manage_type = '0' then '编号' else '计数' end manageTypeName,
        mt.num, (lad.pre_num - IF(lad.al_num IS NULL,'0',lad.al_num)) AS outNum,mm.ma_code as maCode
    FROM
        lease_apply_details lad
        LEFT JOIN ma_type mt ON lad.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        LEFT JOIN ma_machine mm ON lad.type_id = mm.type_id
    WHERE
        lad.parennt_id = #{record.id} AND lad.company_id = #{record.companyId}
    GROUP BY
      lad.id
  </select>
  <select id="getLeaseApplyManageDetails" resultType="com.bonus.sgzb.app.domain.LeaseApplyDetails">
    SELECT
    lad.*, mt.type_name AS typeModelName, mt1.type_name AS typeName,mt.unit_name as unitName, mt.manage_type as manageType,
    case WHEN mt.manage_type = '0' then '编号' else '计数' end manageTypeName,
    mt.num, (lad.pre_num - IF(lad.al_num IS NULL,'0',lad.al_num)) AS outNum,mm.ma_code as maCode
    FROM
    lease_apply_details lad
    LEFT JOIN ma_type mt ON lad.type_id = mt.type_id
    LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
    LEFT JOIN ma_machine mm ON lad.type_id = mm.type_id
    WHERE
    lad.parennt_id = #{record.id}
    <if test="record.companyId != null and record.companyId != ''">
      AND lad.company_id = #{record.companyId}
    </if>
    GROUP BY
    lad.id
  </select>

    <select id="selectNumByMonth" resultType="java.lang.Integer">
        select count(*) from tm_task where DATE_FORMAT(create_time,'%y%m') = DATE_FORMAT(#{date},'%y%m') AND task_type='29'
    </select>


    <select id="getLeaseListTmTask" resultType="com.bonus.sgzb.app.domain.TmTask">
      SELECT DISTINCT
        tt.*, su.phonenumber AS phoneNumber, sd.dept_name as deptName,
        bpi.lot_id as proId,   bpi.pro_id as  projectId,bpi.lot_name as proName,
        bui.unit_id as unitId,bui.unit_name as unitName,
        tt.create_by as applyFor,d.`name` as taskName,lai.cost_bearing_party as costBearingParty,lai.cost_bearing_party as costBearingParty,

        su2.user_name as companyAuditBy,
        lai.company_audit_time as companyAuditTime,
        lai.company_audit_remark as companyAuditRemark,

        su3.user_name as deptAuditBy,
        lai.dept_audit_time as deptAuditTime,
        lai.dept_audit_remark as deptAuditRemark,

        su4.user_name as directAuditBy,
        lai.direct_audit_time as directAuditTime,
        lai.direct_audit_remark as directAuditRemark,
        lai.lease_type as leaseType,
        lai.estimate_lease_time as estimateLeaseTime,

        d.id  as examineStatusId,
        bai.agreement_code as agreementCode,
        tt.create_time as createTimes, tt.update_time as updateTimes,
        bai.agreement_id as agreementId
      FROM
        tm_task tt
          LEFT JOIN sys_user su ON tt.create_by = su.user_name
          LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
          LEFT JOIN tm_task_agreement tta ON tt.task_id = tta.task_id
          LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
          LEFT JOIN bm_project_lot bpi ON bpi.lot_id = bai.project_id
          LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
          LEFT JOIN sys_dic d ON d.id = tt.task_status
          LEFT JOIN lease_apply_info lai ON lai.task_id = tt.task_id

          LEFT JOIN sys_user su2 ON lai.company_audit_by = su2.user_id
          LEFT JOIN sys_user su3 ON lai.dept_audit_by = su3.user_id
          LEFT JOIN sys_user su4 ON lai.direct_audit_by = su4.user_id
        WHERE tt.task_id = #{taskId} limit 1
    </select>

  <select id="getLeaseListByLeaseInfo" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT
      lai.*
    FROM
      lease_apply_info lai
    WHERE
      lai.task_id = #{taskId}
  </select>


  <update id="updateTmTaskAuditStatus">
    UPDATE tm_task SET task_status = #{record.taskStatus} WHERE task_id = #{record.taskId}
  </update>

  <update id="updateLeaseApplyInfoAuditInfo">
    UPDATE
        lease_apply_info
    set
    <trim suffixOverrides=",">
      <if test="record.examineStatusId == 30 and record.examineStatusId == '30'">
        company_audit_by = #{record.companyAuditBy},
        company_audit_time = now(),
        company_audit_remark = #{record.companyAuditRemark},
      </if>
      <if test="record.examineStatusId == 31 and record.examineStatusId == '31'">
        dept_audit_by = #{record.companyAuditBy},
        dept_audit_time = now(),
        dept_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 32 and record.examineStatusId == '32'">
        direct_audit_by = #{record.companyAuditBy},
        direct_audit_time = now(),
        direct_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 33 and record.examineStatusId == '33'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 34 and record.examineStatusId == '34'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 35 and record.examineStatusId == '35'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 117 and record.examineStatusId == '117'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
    </trim>
    WHERE
        task_id = #{record.taskId} and company_id = #{record.companyId}
  </update>

  <update id="updateLeaseApplyInfoRejectInfo">
    UPDATE
    lease_apply_info
    set
    <trim suffixOverrides=",">
      <if test="record.examineStatusId == 98 and record.examineStatusId == '98'">
        company_audit_by = #{record.companyAuditBy},
        company_audit_time = now(),
        company_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 99 and record.examineStatusId == '99'">
        dept_audit_by = #{record.companyAuditBy},
        dept_audit_time = now(),
        dept_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 100 and record.examineStatusId == '100'">
        direct_audit_by = #{record.companyAuditBy},
        direct_audit_time = now(),
        direct_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
    </trim>
    WHERE
    task_id = #{record.taskId} and company_id = #{record.companyId}
  </update>

  <update id="updateLeaseApplyDetailsAuditInfo">
    update
        lease_apply_details
    set
    <trim suffixOverrides=",">
      <if test="record.auditNum != null and record.auditNum != '' ">
        audit_num = #{record.auditNum},
      </if>
      <if test="record.alNum != null and record.alNum != '' ">
        al_num = #{record.alNum},
      </if>
      <if test="record.status!= null and record.status!= '' ">
        status = #{record.status},
      </if>
      <if test="record.updateBy != null and record.updateBy != '' ">
        update_by = #{record.updateBy},
      </if>
        update_time = now()
    </trim>
    where
        parennt_id = #{record.parenntId}
  </update>

  <update id="updateLeaseApplyDetailsRejectInfo">
    update
    lease_apply_details
    set
    <trim suffixOverrides=",">
      <if test="record.auditNum != null and record.auditNum != '' ">
        audit_num = #{record.auditNum},
      </if>
      <if test="record.alNum != null and record.alNum != '' ">
        al_num = #{record.alNum},
      </if>
      <if test="record.status!= null and record.status!= '' ">
        status = #{record.status},
      </if>
      <if test="record.updateBy != null and record.updateBy != '' ">
        update_by = #{record.updateBy},
      </if>
      update_time = now()
    </trim>
    where
    parennt_id = #{record.parenntId}
  </update>

  <update id="updateLeaseInfo">
    UPDATE lease_apply_info SET lease_person = #{leasePerson}, phone = #{phone}, remark = #{remark}
    WHERE task_id = #{taskId}
  </update>
  <update id="updateLeaseApplyInfoRejectInfoCq">
    UPDATE
    lease_apply_info
    set
    <trim suffixOverrides=",">
      <if test="record.examineStatusId == 98 and record.examineStatusId == '98'">
        company_audit_by = #{record.companyAuditBy},
        company_audit_time = now(),
        company_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 99 and record.examineStatusId == '99'">
        dept_audit_by = #{record.companyAuditBy},
        dept_audit_time = now(),
        dept_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 100 and record.examineStatusId == '100'">
        direct_audit_by = #{record.companyAuditBy},
        direct_audit_time = now(),
        direct_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
    </trim>
    WHERE
    task_id = #{record.taskId} and company_id is null
  </update>
  <update id="updateLeaseApplyInfoAuditInfoCq">
    UPDATE
    lease_apply_info
    set
    <trim suffixOverrides=",">
      <if test="record.examineStatusId == 30 and record.examineStatusId == '30'">
        company_audit_by = #{record.companyAuditBy},
        company_audit_time = now(),
        company_audit_remark = #{record.companyAuditRemark},
      </if>
      <if test="record.examineStatusId == 31 and record.examineStatusId == '31'">
        dept_audit_by = #{record.companyAuditBy},
        dept_audit_time = now(),
        dept_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 32 and record.examineStatusId == '32'">
        direct_audit_by = #{record.companyAuditBy},
        direct_audit_time = now(),
        direct_audit_remark = #{record.companyAuditRemark},
        status = #{record.status},
      </if>
      <if test="record.examineStatusId == 33 and record.examineStatusId == '33'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 34 and record.examineStatusId == '34'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 35 and record.examineStatusId == '35'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
      <if test="record.examineStatusId == 117 and record.examineStatusId == '117'">
        update_by = #{record.companyAuditBy},
        update_time = now(),
      </if>
    </trim>
    WHERE
    task_id = #{record.taskId} and company_id is null
  </update>

  <select id="getAgreementIdByUnit" resultType="java.lang.Integer">
    select agreement_id from bm_agreement_info
    where unit_id = #{unitId} and project_id = #{projectId}
  </select>

  <select id="getListSome" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT * FROM `lease_apply_info`
    WHERE task_id = #{taskId}
  </select>

  <select id="getListSomeol" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT * FROM `lease_apply_info`
    WHERE task_id = #{taskId} AND `status` ='1' and company_id = #{id}
  </select>

  <select id="getKgByMaType" resultType="java.lang.Integer">
      select mtk.user_id as userId
      from ma_type_keeper mtk
      where mtk.type_id = #{typeId}
  </select>
  <select id="getLeaseApplyInfo" resultType="com.bonus.sgzb.app.domain.LeaseApplyInfo">
    SELECT * FROM `lease_apply_info`
    WHERE task_id = #{taskId}
  </select>
  <select id="getAuditListByLeaseTmTaskByPeople" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT DISTINCT
    tt.*, su.phonenumber AS phoneNumber, sd.dept_name as deptName,
    bpl.lot_id as proId,bpl.lot_name as proName,
    bui.unit_id as unitId,bui.unit_name as unitName,
    lai.lease_person as leasePerson, lai.phone as leasePhone, tt.create_by as applyFor,d.`name` as taskName,lai.lease_type as leaseType,lai.estimate_lease_time as estimateLeaseTime,
    case when d.id = '31' then lai.company_audit_remark
    when d.id = '32' then lai.dept_audit_remark
    when d.id = '33' then lai.direct_audit_remark
    when d.id = '98' then lai.company_audit_remark
    when d.id = '99' then lai.dept_audit_remark
    when d.id = '100' then lai.direct_audit_remark
    end examineStatus ,
    d.id as examineStatusId,
    bai.agreement_code as agreementCode,
    tt.create_time as createTimes, tt.update_time as updateTimes
    FROM
    tm_task tt
    LEFT JOIN sys_user su ON tt.create_by = su.user_name
    LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id
    LEFT JOIN tm_task_agreement tta ON tt.task_id = tta.task_id
    LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
    LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
    LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
    LEFT JOIN lease_apply_info lai ON lai.task_id = tt.task_id
    LEFT JOIN sys_dic d ON d.id = tt.task_status
    WHERE
    tt.task_type = '29' and tt.status = '1' and tt.create_by = #{record.createBy}
    <if test="record.taskId != null and record.taskId != '' ">
      AND tt.task_id = #{record.taskId}
    </if>

    <if test="record.startTime != null and record.startTime != '' and record.endTime != null and record.endTime != '' ">
      AND tt.update_time BETWEEN CONCAT(#{record.startTime}, ' 00:00:00') AND CONCAT(#{record.endTime}, ' 23:59:59')
    </if>

    <if test="record.unitId != null and record.unitId != ''">
      AND bui.unit_id = #{record.unitId}
    </if>

    <if test="record.projectId != null and record.projectId != ''">
      AND bpl.lot_id = #{record.projectId}
    </if>

    <if test="record.keyWord != null and record.keyWord != ''">
      AND (bai.agreement_code like concat('%', #{record.keyWord}, '%') or
           tt.code like concat('%', #{record.keyWord}, '%'))
    </if>
    GROUP BY tt.task_id
    ORDER BY tt.update_time DESC
  </select>
  <select id="getLeaseApplyDetailsByApply" resultType="com.bonus.sgzb.app.domain.LeaseApplyDetails">
    SELECT
      lad.*, mt.type_name AS typeModelName, mt1.type_name AS typeName,mt.unit_name as unitName, mt.manage_type as manageType,
      case WHEN mt.manage_type = '0' then '编号' else '计数' end manageTypeName,
      IFNULL(mt.num,0) AS num, (lad.pre_num - IF(lad.al_num IS NULL,'0',lad.al_num)) AS outNum,mm.ma_code as maCode
    FROM
      lease_apply_details lad
        LEFT JOIN ma_type mt ON lad.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        LEFT JOIN ma_machine mm ON lad.type_id = mm.type_id
    WHERE
      lad.parennt_id = #{record.id}
    GROUP BY
      lad.id
  </select>
  <select id="getLeaseApplyDetailsCq" resultType="com.bonus.sgzb.app.domain.LeaseApplyDetails">
    SELECT
      lad.*, mt.type_name AS typeModelName, mt1.type_name AS typeName,mt.unit_name as unitName, mt.manage_type as manageType,
      case WHEN mt.manage_type = '0' then '编号' else '计数' end manageTypeName,
      mt.num, (lad.pre_num - IF(lad.al_num IS NULL,'0',lad.al_num)) AS outNum,mm.ma_code as maCode
    FROM
      lease_apply_details lad
        LEFT JOIN ma_type mt ON lad.type_id = mt.type_id
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
        LEFT JOIN ma_machine mm ON lad.type_id = mm.type_id
    WHERE
      lad.parennt_id = #{record.id}
    GROUP BY
      lad.id
  </select>
  <select id="getLeaseOutListByUser" resultType="com.bonus.sgzb.app.domain.TmTask">
      SELECT
      tt.*,
      lai.id AS id,
      bpl.lot_id AS proId,
      bpl.lot_name AS proName,
      bui.unit_id AS unitId,
      bui.unit_name AS unitName,
      lai.lease_person AS leasePerson,
      lai.phone AS leasePhone,
      tt.create_by AS applyFor,
      d.`name` AS taskName,
      lai.lease_type AS leaseType,
      d.id AS examineStatusId,
      bai.agreement_code AS agreementCode,
      tt.create_time AS createTimes,
      IFNULL(sum(lad.pre_num),0) as preCountNum,
      IFNULL(sum(lad.al_num),0) as alNum,
      tt.update_time AS updateTimes
      from
      lease_apply_info lai
      LEFT JOIN tm_task tt on lai.task_id = tt.task_id
      LEFT JOIN sys_dic d ON d.id = tt.task_status
      LEFT JOIN tm_task_agreement tta ON lai.task_id = tta.task_id
      LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
      LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
      LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
      LEFT JOIN lease_apply_details lad on lai.id = lad.parennt_id
      LEFT JOIN ma_type_keeper mtk on lad.type_id = mtk.type_id
      WHERE tt.task_status in(33,34,35)
      <if test="userId != 1">
          and mtk.user_id = #{userId}
      </if>
      <if test="code != null and code != ''">
          and tt.code like concat('%', #{code}, '%')
      </if>
      <if test="unitId != null">
        and bui.unit_id = #{unitId}
      </if>
      <if test="proId != null">
        and bpl.lot_id = #{proId}
      </if>
      <if test="taskStatus != null">
        and tt.task_status = #{taskStatus}
      </if>
      GROUP BY lai.id
      ORDER BY tt.task_status,tt.create_time desc
  </select>
  <select id="getLeaseDetailByParentId" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
    lai.task_id as taskId,
    lad.id as id,
    lad.parennt_id as parentId,
    lad.pre_num - ifnull(lad.al_num,0) as outNum,
    lad.pre_num as preCountNum,
    lad.status as status,
    ifnull(lad.al_num,0) as alNum,
    mt2.type_name as typeName,
    mt.type_name as typeModelName,
    mt.manage_type as manageType,
    mt.num as num,
    su.nick_name as userName,
    lad.type_id as typeId
    FROM
    lease_apply_details lad
    LEFT JOIN lease_apply_info lai on lad.parennt_id = lai.id
    LEFT JOIN ma_type mt on lad.type_id = mt.type_id
    LEFT JOIN ma_type mt2 on mt.parent_id = mt2.type_id
    LEFT JOIN ma_type_keeper mtk on lad.type_id = mtk.type_id
    LEFT JOIN sys_user su on mtk.user_id = su.user_id
    WHERE
    lad.parennt_id = #{id}
    <if test="userId != 1">
      and mtk.user_id = #{userId}
    </if>
    <if test="userName != null and userName != ''">
      and su.user_name like concat('%', #{userName}, '%')
    </if>
    <if test="typeId != null and typeId != ''">
      and lad.type_id = #{typeId}
    </if>
  </select>
  <select id="getMaTypeDetails" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
      mws2.type_id AS typeId,
      mws2.part_num AS partNum
    FROM
      ma_type mt1
        LEFT JOIN ma_whole_set mws2 ON mt1.type_id = mws2.parent_id
    WHERE
      mt1.type_id = #{typeId}
  </select>
  <select id="getMachineParts" resultType="com.bonus.sgzb.base.api.domain.MachinePart">
    SELECT
      mt.type_name AS typeModelName,
      mt.type_id as modelId,
      mt.unit_name as unitName,
      m2.type_name As typeName
    FROM
      ma_type mt
        LEFT JOIN ma_type m2 ON mt.parent_id = m2.type_id
    WHERE
      mt.type_id = #{typeId}
  </select>
  <select id="getMaTypeDetailsByTypeId" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
      mws2.type_id AS typeId,
      mws2.part_num AS partNum
    FROM
      ma_type mt1
        LEFT JOIN ma_whole_set mws2 ON mt1.type_id = mws2.parent_id
    WHERE
      mt1.type_id = #{modelId}
  </select>
  <select id="getManageTypeByTypeId" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
      mt2.manage_type as manageType,
      mws.type_id as typeId,
      mt2.type_name as typeModelName,
      mt3.type_name as typeName,
      (lad.pre_num - ifnull(lad.al_num,0)) * mws.part_num as outNum,
      ifnull(lad.al_num,0) * mws.part_num  as alNum
    FROM
      ma_whole_set mws
        LEFT JOIN lease_apply_details lad on lad.type_id = mws.parent_id
        LEFT JOIN lease_apply_info lai on lad.parennt_id = lai.id
        LEFT JOIN ma_type mt ON mt.type_id = mws.parent_id
        LEFT JOIN ma_type mt2 on mws.type_id = mt2.type_id
        LEFT JOIN ma_type mt3 on mt3.type_id = mt2.parent_id
    WHERE
      mt.type_id = #{typeId} and lad.parennt_id = #{parentId}
  </select>
  <select id="getCountMachine" resultType="java.lang.Integer">
    SELECT
      ifnull(sum( back_num ),0)
    FROM
      back_check_details
    WHERE
      parent_id = #{id}
  </select>
  <select id="getCountMachineByPidAndTid" resultType="java.lang.Integer">
    SELECT
      ifnull(sum( back_num ),0)
    FROM
      back_check_details
    WHERE
      parent_id = #{parentId} and type_id = #{modelId}
  </select>
    <select id="selectTaskNumByMonths" resultType="java.lang.String">
      SELECT SUBSTRING(`code`, - 4) as code
      FROM tm_task
      WHERE DATE_FORMAT(create_time, '%y%m') = DATE_FORMAT(#{date}, '%y%m')
        AND task_type = #{taskType}
      ORDER BY create_time DESC LIMIT 1
    </select>
</mapper>
