<?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.BackReceiveMapper">
  <resultMap id="BaseResultMap" type="com.bonus.sgzb.app.domain.BmAgreementInfo">
    <!--@mbg.generated-->
    <!--@Table bm_agreement_info-->
    <id column="agreement_id" jdbcType="INTEGER" property="agreementId" />
    <result column="agreement_code" jdbcType="VARCHAR" property="agreementCode" />
    <result column="sign_time" jdbcType="VARCHAR" property="signTime" />
    <result column="unit_id" jdbcType="INTEGER" property="unitId" />
    <result column="project_id" jdbcType="INTEGER" property="projectId" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="lease_day" jdbcType="INTEGER" property="leaseDay" />
    <result column="plan_start_time" jdbcType="TIMESTAMP" property="planStartTime" />
    <result column="contract_code" jdbcType="VARCHAR" property="contractCode" />
    <result column="auth_person" jdbcType="VARCHAR" property="authPerson" />
    <result column="phone" jdbcType="VARCHAR" property="phone" />
    <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" />
    <result column="unit_id" jdbcType="INTEGER" property="unitId" />
    <result column="unit_name" jdbcType="VARCHAR" property="unitName" />
    <result column="lot_id" jdbcType="INTEGER" property="lotId" />
    <result column="lot_name" jdbcType="VARCHAR" property="lotName" />

  </resultMap>
  <insert id="addWxTask" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    insert into tm_task
    (
    <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="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>
    create_time
    ) values (
    <if test="taskType != null">
      #{taskType},
    </if>
    <if test="taskStatus != null">
      #{taskStatus},
    </if>
    <if test="code != null and code != ''">
      #{code},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="updateBy != null and updateBy != ''">
      #{updateBy},
    </if>
    <if test="updateTime != null">
      #{updateTime},
    </if>
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertTt" keyColumn="task_id" keyProperty="taskId" parameterType="com.bonus.sgzb.app.domain.TmTask" useGeneratedKeys="true">
    insert into tm_task
    (
    <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="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>
    create_time
    ) values (
    <if test="taskType != null">
      #{taskType},
    </if>
    <if test="taskStatus != null">
      #{taskStatus},
    </if>
    <if test="code != null and code != ''">
      #{code},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="updateBy != null and updateBy != ''">
      #{updateBy},
    </if>
    <if test="updateTime != null">
      #{updateTime},
    </if>
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="addWxTaskAgreement">
    insert into tm_task_agreement
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="agreementId != null">
      agreement_id,
    </if>
    <if test="createBy != null and createBy != ''">
      create_by,
    </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>
    create_time
    ) values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="agreementId != null">
      #{agreementId},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="updateBy != null and updateBy != ''">
      #{updateBy},
    </if>
    <if test="updateTime != null">
      #{updateTime},
    </if>
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertTta">
    insert into tm_task_agreement
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="agreementId != null">
      agreement_id,
    </if>
    create_time
    ) values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="agreementId != null">
      #{agreementId},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertRad">
    insert into repair_apply_details
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="maId != null">
      ma_id,
    </if>
    <if test="typeId != null">
      type_id,
    </if>
    <if test="backNum != null">
      repair_num,
    </if>
    <if test="status != null">
      status,
    </if>
    <if test="createBy != null and createBy != ''">
      create_by,
    </if>
    <if test="createBy != null and createBy != ''">
      update_by,
    </if>
    update_time,
    <if test="remark != null and remark != ''">
      remark,
    </if>
    <if test="companyId != null">
      company_id,
    </if>
    <if test="id != null">
      back_id,
    </if>
    create_time
    )
    values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="maId != null">
      #{maId},
    </if>
    <if test="typeId != null">
      #{typeId},
    </if>
    <if test="backNum != null">
      #{backNum},
    </if>
    <if test="status != null">
      #{status},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    NOW(),
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    <if test="id != null">
      #{id},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertCheckDetails">
    insert into back_check_details
    (
    <if test="parentId != null">
      parent_id,
    </if>
    <if test="typeId != null">
      type_id,
    </if>
    <if test="maId != null">
      ma_id,
    </if>
    <if test="backNum != null">
      back_num,
    </if>
    <if test="backStatus != null">
      back_status,
    </if>
    <if test="createBy != null and createBy != ''">
      create_by,
    </if>
    <if test="createBy != null and createBy != ''">
      update_by,
    </if>
    update_time,
    <if test="remark != null and remark != ''">
      remark,
    </if>
    <if test="companyId != null">
      company_id,
    </if>
    create_time
    )
    values (
    <if test="parentId != null">
      #{parentId},
    </if>
    <if test="typeId != null">
      #{typeId},
    </if>
    <if test="maId != null">
      #{maId},
    </if>
    <if test="backNum != null">
      #{backNum},
    </if>
    <if test="backStatus != null">
      #{backStatus},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    NOW(),
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="setInputApplyDetails">
    insert into input_apply_details
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="maId != null">
      ma_id,
    </if>
    <if test="typeId != null">
      type_id,
    </if>
    <if test="parentId != null">
      parent_id,
    </if>
    <if test="inputNum != null">
      input_num,
    </if>
     input_type,
    <if test="createBy != null and createBy != ''">
      create_by,
    </if>
    <if test="createBy != null and createBy != ''">
      update_by,
    </if>
    update_time,
    <if test="remark != null and remark != ''">
      remark,
    </if>
    <if test="companyId != null">
      company_id,
    </if>
    create_time
    )
    values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="maId != null">
      #{maId},
    </if>
    <if test="typeId != null">
      #{typeId},
    </if>
    <if test="parentId != null">
      #{parentId},
    </if>
    <if test="inputNum != null">
      #{inputNum},
    </if>
    '2',
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    NOW(),
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertIad">
    insert into input_apply_details
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="maId != null">
      ma_id,
    </if>
    <if test="typeId != null">
      type_id,
    </if>
    <if test="parentId != null">
      parent_id,
    </if>
    <if test="backNum != null">
      input_num,
    </if>
    input_type,
    <if test="createBy != null and createBy != ''">
      create_by,
    </if>
    <if test="createBy != null and createBy != ''">
      update_by,
    </if>
    update_time,
    <if test="remark != null and remark != ''">
      remark,
    </if>
    <if test="companyId != null">
      company_id,
    </if>
    create_time
    )
    values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="maId != null">
      #{maId},
    </if>
    <if test="typeId != null">
      #{typeId},
    </if>
    <if test="parentId != null">
      #{parentId},
    </if>
    <if test="backNum != null">
      #{backNum},
    </if>
    '2',
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    NOW(),
    <if test="remark != null and remark != ''">
      #{remark},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>

  <insert id="insertSad">
    insert into scrap_apply_details
    (
    <if test="taskId != null">
      task_id,
    </if>
    <if test="parentId != null">
      parent_id,
    </if>
    <if test="maId != null">
      ma_id,
    </if>
    <if test="typeId != null">
      type_id,
    </if>
    <if test="backNum != null">
      scrap_num,
    </if>
      scrap_source,
      status,
    <if test="createBy != null and createBy != ''">
      create_by,
    </if>
    <if test="companyId != null">
      company_id,
    </if>
    create_time
    )
    values (
    <if test="taskId != null">
      #{taskId},
    </if>
    <if test="parentId != null">
      #{parentId},
    </if>
    <if test="maId != null">
      #{maId},
    </if>
    <if test="typeId != null">
      #{typeId},
    </if>
    <if test="backNum != null">
      #{backNum},
    </if>
            '1',
            '0',
    <if test="createBy != null and createBy != ''">
      #{createBy},
    </if>
    <if test="companyId != null">
      #{companyId},
    </if>
    NOW()
    )
  </insert>
  <insert id="insStlInfoTwo">
    insert into slt_agreement_info (agreement_id,type_id,ma_id,num,start_time,status,lease_id,lease_price,buy_price,is_slt,company_id,lease_type)
    values (#{info.agreementId},#{info.typeId},#{info.maId},#{many},#{info.startTime},#{info.status},#{info.leaseId},#{info.leasePrice},#{info.buyPrice},'0',#{info.companyId},#{info.leaseType});
  </insert>


  <update id="updateTaskStatus">
    update tm_task
    set task_status=#{status}
    where task_id = #{taskId}
  </update>
  <update id="updateMaStatus">
    update ma_machine
    set ma_status=#{maStatus}
    where ma_id = #{maId}
  </update>

  <update id="updateMt">
    UPDATE ma_type
    SET num = (IFNULL(num, 0)) + #{backNum}
    WHERE type_id = #{typeId}
  </update>
  <update id="updateStlInfo">
    update slt_agreement_info
    set end_time = now(),
        back_id = #{record.parentId},
        status = '1'
    where id = #{info.id}
  </update>
  <update id="updateStlInfoTwo">
    update slt_agreement_info
    set num = #{backNum},
        end_time = now(),
        back_id = #{record.parentId},
        status = '1'
    where id = #{info.id}
  </update>

  <update id="finishBackCheckDetails">
    update back_check_details set is_finished = 1 where parent_id = #{parentId} and type_id = #{typeId} and (is_finished is null or is_finished != 1)
  </update>

  <delete id="deleteCheckDetails">
    delete from back_check_details where parent_id = #{parentId} and type_id = #{typeId} and (is_finished is null or is_finished != 1)
    <if test="maId != null and maId != ''">
      and ma_id = #{maId}
    </if>
  </delete>

  <select id="getbackReceiveList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      bai.id,
      bai.`code`,
      bai.task_id AS taskId,
      bai.back_person AS backPerson,
      bai.phone,
      bpl.lot_id,
      bpl.lot_name AS lotName,
      bui.unit_id AS unitId,
      bui.unit_name AS unitName,
      bai.back_time AS backTime,
      bagi.agreement_code AS agreementCode,
      tt.task_status AS taskStatus,
      sd.`name` AS taskName,
      tta.agreement_id AS agreementId,
      GROUP_CONCAT( DISTINCT mt2.type_id ) AS typeId,
      GROUP_CONCAT(bad.id) as badId,
      GROUP_CONCAT( mt2.type_name, '' ) AS typeName
    FROM
      back_apply_info bai
        LEFT JOIN back_apply_details bad ON bad.parent_id = bai.id
        LEFT JOIN tm_task tt ON tt.task_id = bai.task_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = tt.task_id
        LEFT JOIN bm_agreement_info bagi ON bagi.agreement_id = tta.agreement_id
        LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bagi.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bagi.unit_id
        LEFT JOIN sys_user us ON us.user_id = bai.create_by
        LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        LEFT JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        LEFT JOIN sys_dic sd ON sd.id = tt.task_status
    WHERE
      tt.task_status IN ( 37, 38, 39, 40 )
      <if test="keyWord != null and keyWord != ''">
        and bai.code like concat('%', #{keyWord}, '%')
      </if>
      <if test="unitId != null and unitId != ''">
        and bui.unit_id = #{unitId}
      </if>
      <if test="lotId != null and lotId != ''">
        and bpl.lot_id = #{lotId}
      </if>
      <if test="agreementCode != null and agreementCode != ''">
        and bagi.agreement_code like concat('%', #{agreementCode}, '%')
      </if>
      <if test="taskStatus != null and taskStatus != ''">
        and tt.task_status = #{taskStatus}
      </if>
      <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
        AND bai.back_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
      </if>
    GROUP BY
      bai.id,
      us.user_name,
      bai.phone,
      bpl.lot_name,
      bui.unit_name,
      bagi.plan_start_time
    <if test="typeId != null and typeId != ''">
      HAVING
      FIND_IN_SET(#{typeId}, GROUP_CONCAT(DISTINCT bad.type_id))
    </if>
    ORDER BY
      tt.task_status ASC,
      bai.create_time DESC
  </select>

  <select id="receiveView4FrontEnd" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      bai.id,
      bai.task_id as taskId,
      bai.back_person as backPerson,
      tta.agreement_id as agreementId,
      mt.type_id as modelId,
      mt2.type_id as typeId,
      mt.type_name as typeCode,
      mt.unit_name as unitName,
      mt2.type_name AS typeName,
      bad.pre_num as preNum,
      bad.audit_num as auditNum,
      bad.status as status,
      CASE WHEN ((IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) > (bad.audit_num-IFNULL(bb.finished_back_num,0)-IFNULL(aa.back_num,0)))
          THEN (bad.audit_num-IFNULL(bb.finished_back_num,0)-IFNULL(aa.back_num,0))
          ELSE
          (CASE WHEN (IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) &lt; 0 THEN 0 ELSE (IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) END)
          END as max_back_num,
      IFNULL(aa.back_num,0) as in_completed_back_num,
      IFNULL(bb.finished_back_num, 0) as finished_back_num,
      mt.manage_type as manageType,
      mtk.user_id,
      CONCAT('NSJJ',mt.`code`,mt.model_code) as `code`
    FROM
      back_apply_details bad
        LEFT JOIN back_apply_info bai on bai.id=bad.parent_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=bai.task_id
        LEFT JOIN (select agreement_id,type_id, sum(IFNULL(num, 0)) as num from slt_agreement_info where status=0
            group by agreement_id,type_id) sai on tta.agreement_id=sai.agreement_id and sai.type_id=bad.type_id
        LEFT JOIN ma_type mt on mt.type_id=bad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id=mt.parent_id
        LEFT JOIN ma_type_keeper mtk on mt.type_id=mtk.type_id
        LEFT JOIN (
        SELECT
          type_id as typeId,
          SUM(IFNULL(back_num,0)) as back_num
        FROM
          back_check_details
        WHERE
          parent_id=#{id} and is_finished is null
        GROUP BY type_id
      ) aa on aa.typeId=bad.type_id
        LEFT JOIN (
            SELECT
            type_id as typeId,
            SUM(IFNULL(back_num,0)) as finished_back_num
            FROM
            back_check_details
            WHERE
            parent_id=#{id} and is_finished = 1
            GROUP BY type_id
        ) bb on bb.typeId=bad.type_id
    WHERE
      bai.id=#{id}
      <if test="typeId != null and typeId != ''">
        and mt2.type_id = #{typeId}
      </if>
      <if test="modelId != null and modelId != ''">
        and mt.type_id = #{modelId}
      </if>
  </select>

  <select id="receiveView4Backend" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
    bai.id,
    bai.task_id as taskId,
    tta.agreement_id as agreementId,
    mt.type_id as modelId,
    mt2.type_id as typeId,
    mt.type_name as typeCode,
    mt.unit_name as unitName,
    mt2.type_name AS typeName,
    bad.pre_num as preNum,
    bad.audit_num as auditNum,
    bad.status as status,
    CASE WHEN ((IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) > (bad.audit_num-IFNULL(bb.finished_back_num,0)-IFNULL(aa.back_num,0)))
        THEN (bad.audit_num-IFNULL(bb.finished_back_num,0)-IFNULL(aa.back_num,0))
        ELSE
        (CASE WHEN (IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) &lt; 0 THEN 0 ELSE (IFNULL(sai.num, 0)-IFNULL(aa.back_num,0)) END)
        END as num,
    IFNULL(sai.num, 0) as max_back_num_backend,
    mt.manage_type as manageType,
    mtk.user_id,
    CONCAT('NSJJ',mt.`code`,mt.model_code) as `code`
    FROM
    back_apply_details bad
    LEFT JOIN back_apply_info bai on bai.id=bad.parent_id
    LEFT JOIN tm_task_agreement tta on tta.task_id=bai.task_id
    LEFT JOIN (select agreement_id,type_id, sum(IFNULL(num, 0)) as num from slt_agreement_info where status=0
        group by agreement_id,type_id) sai on tta.agreement_id=sai.agreement_id and sai.type_id=bad.type_id
    LEFT JOIN ma_type mt on mt.type_id=bad.type_id
    LEFT JOIN ma_type mt2 ON mt2.type_id=mt.parent_id
    LEFT JOIN ma_type_keeper mtk on mt.type_id=mtk.type_id
    LEFT JOIN (
        SELECT
        type_id as typeId,
        SUM(IFNULL(back_num,0)) as back_num
        FROM
        back_check_details
        WHERE
        parent_id=#{parentId} and is_finished is null
        GROUP BY type_id
        ) aa on aa.typeId=bad.type_id
    LEFT JOIN (
      SELECT
      type_id as typeId,
      SUM(IFNULL(back_num,0)) as finished_back_num
      FROM
      back_check_details
      WHERE
      parent_id=#{parentId} and is_finished = 1
      GROUP BY type_id
      ) bb on bb.typeId=bad.type_id
    WHERE
    bai.id=#{parentId}
  </select>

  <select id="getmaChineByCt" resultType="java.lang.Integer">
      SELECT
        CASE mt.manage_type
          WHEN 0 THEN
            IFNULL(subquery0.num, 0)
          ELSE
            IFNULL(mt.num, 0)
          END as num
      FROM ma_type mt
             left join (SELECT mt.type_id,
                               mt2.type_name AS             typeName,
                               mt.type_name  AS             typeModelName,
                               count(mm.ma_id) num
                        FROM ma_machine mm
                               LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
                               LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                        WHERE mm.ma_code is not null and mm.ma_status in (15)
                        GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = mt.type_id
      WHERE mt.del_flag = '0' and mt.type_id = #{typeId}
  </select>

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

  <select id="getHgList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      tta.agreement_id as agreementId,
      bai.id,
      bai.task_id as taskId,
      bcd.type_id as typeId,
      bcd.back_num as backNum,
      bcd.parent_id as parentId,
      bcd.create_by as createBy,
      bcd.ma_id as maId,
      bai.company_id as companyId
    FROM
      back_check_details bcd
        LEFT JOIN back_apply_info bai on bai.id=bcd.parent_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=bai.task_id
    WHERE
      parent_id=#{parentId}
      and bcd.type_id=#{typeId}
      and bcd.back_status='1' and (bcd.is_finished is null or bcd.is_finished != 1)
  </select>

  <select id="getWxList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      tta.agreement_id as agreementId,
      bai.id,
      bai.task_id as taskId,
      bcd.type_id as typeId,
      bcd.back_num as backNum,
      bcd.parent_id as parentId,
      bcd.create_by as createBy,
      bcd.ma_id as maId,
      bai.company_id as companyId
    FROM
      back_check_details bcd
        LEFT JOIN back_apply_info bai on bai.id=bcd.parent_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=bai.task_id
    WHERE
      parent_id=#{parentId}
      and bcd.type_id=#{typeId}
      and bcd.back_status='2' and (bcd.is_finished is null or bcd.is_finished != 1)
  </select>

  <select id="getBfList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      tta.agreement_id as agreementId,
      bai.id,
      bai.task_id as taskId,
      bcd.type_id as typeId,
      bcd.back_num as backNum,
      bcd.parent_id as parentId,
      bcd.create_by as createBy,
      bcd.ma_id as maId,
      bai.company_id as companyId
    FROM
      back_check_details bcd
        LEFT JOIN back_apply_info bai on bai.id=bcd.parent_id
        LEFT JOIN tm_task_agreement tta on tta.task_id=bai.task_id
    WHERE
      parent_id=#{parentId}
      and bcd.type_id=#{typeId}
      and bcd.back_status='3' and (bcd.is_finished is null or bcd.is_finished != 1)
  </select>

  <select id="codeQuery" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      mt.type_id as typeId,
      mm.ma_id as maId,
      mm.ma_code as maCode,
      mt.type_name as typeCode,
      mt2.type_name as typeName,
      sd.`name` as sdStatus
    FROM
      ma_machine mm
        LEFT JOIN ma_type mt on mt.type_id=mm.type_id
        LEFT JOIN ma_type mt2 on mt2.type_id=mt.parent_id
        LEFT JOIN sys_dic sd on mm.ma_status=sd.id
    WHERE
      ma_code LIKE concat('%', #{maCode}, '%')
      and sd.id='16'
  </select>

  <select id="qrcodeQuery" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      mt.type_id as typeId,
      mm.ma_id as maId,
      mm.ma_code as maCode,
      mt.type_name as typeCode,
      mt2.type_name as typeName,
      sd.`name` as sdStatus,
      sd.id as sdId
    FROM
      ma_machine mm
        LEFT JOIN slt_agreement_info sai on mm.type_id = sai.type_id and mm.ma_id = sai.ma_id
        LEFT JOIN ma_type mt on mt.type_id=mm.type_id
        LEFT JOIN ma_type mt2 on mt2.type_id=mt.parent_id
        LEFT JOIN sys_dic sd on mm.ma_status=sd.id
    WHERE
      mm.qr_code =#{qrCode} and sai.agreement_id = #{agreementId}
  </select>
    <select id="backReceiveRecord" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
      SELECT
        mt2.type_name AS typeName,
        mt1.type_name AS typeCode,
        ifnull(bcd.back_num, 0) AS backNum,
        mm.ma_code as maCode,
        bcd.create_time AS backTime,
        bcd.type_id as modelId,
        bcd.back_status AS backStatus
      FROM
        back_check_details bcd
          LEFT JOIN ma_type mt1 ON mt1.type_id = bcd.type_id
          LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
          LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
      WHERE
        bcd.parent_id = #{parentId}
      <if test="maCode != null and maCode != ''">
        and mm.ma_code like concat('%', #{maCode}, '%')
      </if>
      ORDER BY
        bcd.create_time DESC
    </select>
  <select id="selectCodeByMaIdAndTaskId" resultType="java.lang.Integer">
    SELECT
      COUNT(*)
    FROM
      back_check_details cbd
        LEFT JOIN back_apply_info bai ON bai.id = cbd.parent_id
    WHERE
      cbd.ma_id = #{maId}
      AND bai.task_id = #{taskId}
  </select>

  <select id="rfidCodeQuery" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      mt.type_id as typeId,
      mm.ma_id as maId,
      mm.ma_code as maCode,
      mt.type_name as typeCode,
      mt2.type_name as typeName,
      sd.`name` as sdStatus,
      mt.manage_type as manageType
    FROM
      ma_machine mm
        LEFT JOIN ma_type mt on mt.type_id=mm.type_id
        LEFT JOIN ma_type mt2 on mt2.type_id=mt.parent_id
        LEFT JOIN sys_dic sd on mm.ma_status=sd.id
    WHERE
      rfid_code =#{rfidCode}
  </select>
  <select id="getStlInfo" resultType="com.bonus.sgzb.base.api.domain.SltAgreementInfo">
    SELECT
      id,
      agreement_id AS agreementId,
      type_id AS typeId,
      ma_id AS maId,
      num AS num,
      start_time AS startTime,
      end_time AS endTime,
      status AS status,
      lease_id AS leaseId,
      lease_type AS leaseType,
      back_id AS backId,
      lease_price AS leasePrice,
      buy_price AS buyPrice,
      company_id AS companyId
    FROM
      slt_agreement_info
    WHERE
      agreement_id = #{agreementId}
      AND type_id = #{typeId}
      AND status = 0
      <if test="maId != null">
        AND ma_id = #{maId}
      </if>
    order by start_time asc
  </select>
  <select id="getAllList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      tta.agreement_id AS agreementId,
      bai.id,
      bai.task_id AS taskId,
      bcd.type_id AS typeId,
      SUM(bcd.back_num) AS backNum,
      bcd.parent_id AS parentId,
      bcd.create_by AS createBy,
      bcd.ma_id AS maId,
      bai.company_id AS companyId
    FROM
      back_check_details bcd
        LEFT JOIN back_apply_info bai ON bai.id = bcd.parent_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = bai.task_id
    WHERE
      parent_id = #{parentId} and (bcd.is_finished is null or bcd.is_finished != 1)
    GROUP By bcd.type_id,bcd.ma_id
  </select>
  <select id="getBackMachine" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
    mt2.type_name AS typeName,
    mt.type_name AS typeModelName,
    mt.type_id AS typeId,
    mm.ma_code AS maCode,
    mm.ma_id AS maId,
    sd.NAME AS maStatus
    FROM
    ma_type mt
    LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
    LEFT JOIN ma_machine mm ON mt.type_id = mm.type_id
    LEFT JOIN slt_agreement_info sai ON sai.ma_id = mm.ma_id
    LEFT JOIN sys_dic sd ON sd.id = mm.ma_status
    WHERE
    mm.ma_status = '16'
    AND mt.type_id = #{typeId}
    AND sai.`status` = 0
    AND sai.agreement_id = #{agreementId}
    <if test="maCode != null and maCode !=''">
      AND mm.ma_code like concat('%', #{maCode}, '%')
    </if>
  </select>
  <select id="getRecord" resultType="com.bonus.sgzb.app.domain.TmTask">
    SELECT
      mt2.type_name AS typeName,
      mt1.type_name AS typeCode,
      bcd.back_num as backNum,
      bcd.create_time AS backTime,
      bcd.back_status AS backStatus
    FROM
      back_check_details bcd
        LEFT JOIN ma_type mt1 ON mt1.type_id = bcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
    WHERE
      bcd.parent_id = #{parentId}
    ORDER BY
      bcd.create_time DESC
  </select>
  <select id="getMaId" resultType="java.lang.Integer">
    select ma_id from back_check_details where parent_id = #{parentId} and type_id = #{typeId}
  </select>
  <select id="selectTaskStatus" resultType="java.lang.Integer">
    select task_status from tm_task where task_id = #{taskId}
  </select>
  <select id="selectNumByTypeId" resultType="java.lang.Integer">
    SELECT
      ROUND(SUM( res.auditNum )- SUM( res.backNum ),0) AS backNum
    FROM
      (
        SELECT
          bad.audit_num AS auditNum,
          0 AS backNum
        FROM
          back_apply_details bad
        WHERE
          bad.parent_id = #{parentId}
          AND bad.type_id = #{typeId} UNION
        SELECT
          0 AS auditNum,
          SUM(
                  IFNULL( bcd.back_num, 0 )) AS backNum
        FROM
          back_check_details bcd
        WHERE
          bcd.parent_id = #{parentId}
          AND bcd.type_id = #{typeId}
      ) res
    HAVING backNum > -1
  </select>
  <select id="backReceiveRecordWeb" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
    mt2.type_name AS typeName,
    mt1.type_name AS typeCode,
    ifnull(sum( bcd.back_num ), 0) AS backNum,
    SUM( CASE WHEN bcd.back_status = 1 THEN bcd.back_num ELSE 0 END ) AS hgNum,
    SUM( CASE WHEN bcd.back_status = 2 THEN bcd.back_num ELSE 0 END ) AS wxNum,
    SUM( CASE WHEN bcd.back_status = 3 THEN bcd.back_num ELSE 0 END ) AS bfNum,
    mm.ma_code AS maCode,
    bcd.create_time AS backTime,
    bcd.type_id as modelId,
    bcd.back_status AS backStatus
    FROM
    back_check_details bcd
    LEFT JOIN ma_type mt1 ON mt1.type_id = bcd.type_id
    LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
    LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
    WHERE
    bcd.parent_id = #{parentId}
    AND bcd.type_id = #{typeId}
    <if test="maCode != null and maCode != ''">
      and mm.ma_code like concat('%', #{maCode}, '%')
    </if>
    GROUP BY
    bcd.type_id
    ORDER BY
    bcd.create_time DESC
  </select>
  <select id="getCtList" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      bad.type_id as typeId,
      ifnull(bad.audit_num,0) as backNum,
      mt.manage_type as manageType
    FROM
      back_apply_details bad
        LEFT JOIN ma_type mt ON bad.type_id = mt.type_id
    WHERE
      bad.parent_id = #{parentId}
  </select>
  <select id="backReceiveRecordWebPt" resultType="com.bonus.sgzb.base.api.domain.BackApplyInfo">
    SELECT
      mt2.type_name AS typeName,
      mt1.type_name AS typeCode,
      ifnull( bcd.back_num, 0 ) AS backNum,
      mm.ma_code AS maCode,
      bcd.create_time AS backTime,
      bcd.type_id AS modelId,
      bcd.back_status AS backStatus
    FROM
      back_check_details bcd
        LEFT JOIN ma_type mt1 ON mt1.type_id = bcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
    WHERE
      bcd.parent_id = #{parentId}
      AND bcd.type_id = #{typeId}
    <if test="maCode != null and maCode != ''">
      and mm.ma_code like concat('%', #{maCode}, '%')
    </if>
    ORDER BY
      bcd.create_time DESC
  </select>

</mapper>