<?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.material.fieldMaintenance.mapper.FieldMaintenanceMapper">
    <insert id="insert">
        INSERT INTO field_apply_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskId != null">task_id,</if>
            <if test="agreementId != null">agreement_id,</if>
            <if test="fieldPerson != null">field_person,</if>
            <if test="phone != null">phone,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="taskId != null">#{taskId},</if>
            <if test="agreementId != null">#{agreementId},</if>
            <if test="fieldPerson != null">#{fieldPerson},</if>
            <if test="phone != null">#{phone},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
        </trim>
    </insert>

    <update id="deleteFieldApplyInfoById" parameterType="java.lang.Integer">
        UPDATE field_apply_info
        SET is_valid    = 0,
            update_time = NOW()
        WHERE id = #{id}
    </update>

    <select id="selectFieldApplyInfoList"
            resultType="com.bonus.material.fieldMaintenance.domain.FieldApplyInfo">
        SELECT
        fai.id AS id,
        fai.task_id AS taskId,
        fai.agreement_id AS agreementId,
        tt.`code` as code,
        fai.field_person AS fieldPerson,
        fai.phone AS phone,
        bp.pro_id AS proId,
        bp.pro_name AS proName,
        bu.unit_id AS unitId,
        bu.unit_name AS unitName,
        tt.task_status AS taskStatus,
        fai.create_by AS createBy,
        fai.create_time AS createTime,
        mt.typeIds,
        mt.typeNames,
        mt.num
        FROM field_apply_info fai
        LEFT JOIN tm_task tt ON tt.task_id = fai.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 AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id AND bu.del_flag = '0'
        LEFT JOIN (
        SELECT
        bad.parent_id,
        GROUP_CONCAT(DISTINCT mt2.type_id) AS typeIds,
        GROUP_CONCAT(DISTINCT mt2.type_name) AS typeNames,
        SUM(bad.pre_num) AS num
        FROM field_apply_details bad
        LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id AND mt2.del_flag = '0'
        GROUP BY bad.parent_id
        ) mt ON mt.parent_id = fai.id
        <where>
            <if test="keyWord != null">
                and (tt.`code` like concat('%', #{keyWord}, '%') or bp.pro_name like concat('%', #{keyWord}, '%')
                or bu.unit_name like concat('%', #{keyWord}, '%') or fai.create_by like concat('%', #{keyWord}, '%'))
            </if>
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                <![CDATA[and DATE_FORMAT( fai.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
            </if>
            <if test="taskStatus != null">
                and tt.task_status = #{taskStatus}
            </if>
            and is_valid = 1
        </where>
        ORDER BY fai.create_time desc
    </select>
    <select id="selectFieldApplyInfoById"
            resultType="com.bonus.material.fieldMaintenance.domain.FieldApplyInfo">
        SELECT
        fai.id AS id,
        fai.task_id AS taskId,
        tt.`code` as code,
        fai.field_person AS fieldPerson,
        fai.phone AS phone,
        bp.pro_id AS proId,
        bp.pro_name AS proName,
        bu.unit_id AS unitId,
        bu.unit_name AS unitName,
        tt.task_status AS taskStatus,
        fai.create_by AS createBy,
        fai.create_time AS createTime,
        mt.typeIds,
        mt.typeNames
        FROM field_apply_info fai
        LEFT JOIN tm_task tt ON tt.task_id = fai.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 AND bagi.`status` = '1'
        LEFT JOIN bm_project bp ON bp.pro_id = bagi.project_id AND bp.del_flag = '0'
        LEFT JOIN bm_unit bu ON bu.unit_id = bagi.unit_id AND bu.del_flag = '0'
        LEFT JOIN (
        SELECT
        bad.parent_id,
        GROUP_CONCAT(DISTINCT mt2.type_id) AS typeIds,
        GROUP_CONCAT(DISTINCT mt2.type_name) AS typeNames
        FROM field_apply_details bad
        LEFT JOIN ma_type mt1 ON mt1.type_id = bad.type_id AND mt1.del_flag = '0'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id AND mt2.del_flag = '0'
        GROUP BY bad.parent_id
        ) mt ON mt.parent_id = fai.id
        <where>
            fai.id =#{id}
        </where>
    </select>

    <select id="selectFieldApplyDetailsList"
            resultType="com.bonus.material.fieldMaintenance.domain.FieldApplyDetails">
        SELECT
        fad.id AS id,
        fad.CODE AS CODE,
        fad.parent_id AS parentId,
        fad.type_id AS typeId,
        mt2.type_name AS materialType,
        mt.type_name AS typeModel,
        mt1.type_name AS typeName,
        mt.unit_name AS unitName,
        mt.unit_value AS unitValue,
        mt.manage_type AS manageType,
        SUM(fad.pre_num) AS preNum,
        fad.use_num AS num,
        fad.STATUS AS STATUS,
        fad.create_by AS createBy,
        fad.create_time AS createTime,
        fad.update_by AS updateBy,
        fad.update_time AS updateTime,
        fad.remark AS remark,
        fad.ap_detection AS apDetection,
        fad.bad_num AS badNum,
        fad.good_num AS goodNum,
        mt.manage_type AS manageType
        FROM
        field_apply_details fad
        LEFT JOIN ma_type mt ON mt.type_id = fad.type_id and mt.del_flag = 0
        LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id and mt1.del_flag = 0
        LEFT JOIN ma_type mt2 ON mt1.parent_id = mt2.type_id and mt2.del_flag = 0
        WHERE
        fad.parent_id = #{parentId}
        <if test="keyWord != null and keyWord != ''">
            AND (
            mt1.type_name like concat('%', #{keyWord}, '%')
            OR mt.type_name like concat('%', #{keyWord}, '%')
            OR mt2.type_name like concat('%', #{keyWord}, '%')
            )
        </if>
        GROUP BY fad.type_id,mt.manage_type
    </select>


</mapper>