<?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.part.mapper.PartLeaseMapper">

    <select id="selectPartLeaseInfoById" resultType="com.bonus.material.part.domain.PartLeaseInfo">
        SELECT
        task_id as taskId,
        code as code,
        month_order as monthOrder,
        creator as creator
        FROM
        pa_collar_apply
        WHERE
        task_id = #{id}
    </select>

    <select id="selectPartLeaseInfoList" resultType="com.bonus.material.part.domain.PartLeaseInfo">
        SELECT
        p1.task_id as taskId,
        su1.nick_name as creator,
        p1.create_time as createTime,
        GROUP_CONCAT( mp.pa_name ) as partName,
        p1.`code` as code,
        GROUP_CONCAT(DISTINCT su.nick_name) as auditor,
        MAX(p2.audit_time) as auditTime,
        p1.task_status as taskStatus,
        case p1.task_status
        WHEN 0 THEN '待审核'
        WHEN 1 THEN '已审核'
        WHEN 2 THEN '已驳回'
        ELSE '未知'
        END as taskStatusName,
        SUM(p2.pre_num) as preNum
        FROM
        pa_collar_apply p1
        LEFT JOIN pa_collar_apply_details p2 ON p1.task_id = p2.task_id
        LEFT JOIN ma_part_type mp ON p2.part_id = mp.pa_id and mp.del_flag = '0'
        LEFT JOIN sys_user su on p2.auditor = su.user_id
        LEFT JOIN sys_user su1 on p1.creator = su1.user_id
        <where>
            <if test="taskStatus != null">
                and p1.task_status = #{taskStatus}
            </if>
            <if test="appTaskStatus != null and appTaskStatus == 0">
                and p1.task_status = #{appTaskStatus}
            </if>
            <if test="appTaskStatus != null and appTaskStatus == 1">
                and (p1.task_status = 1 or p1.task_status = 2)
            </if>
            <if test="keyWord != null  and keyWord != ''">
                and (
                p1.creator like concat('%', #{keyWord}, '%') or
                p1.`code` like concat('%', #{keyWord}, '%') or
                p1.auditor like concat('%', #{keyWord}, '%')
                )
            </if>
            <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
                <![CDATA[and DATE_FORMAT( p1.create_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
            </if>
        </where>
        GROUP BY p1.task_id
        ORDER BY p1.create_time desc
    </select>

    <insert id="insertPartLeaseInfo" useGeneratedKeys="true" keyProperty="taskId">
        insert into pa_collar_apply(code, month_order, creator, create_time, task_status, remarks, company_id)
        values (#{code}, #{monthOrder}, #{creator}, #{createTime}, 0, #{remarks}, #{companyId})
    </insert>

    <delete id="removePartLeaseInfoByIds">
        delete from pa_collar_apply where task_id in
        <foreach item="taskId" collection="array" open="(" separator="," close=")">
            #{taskId}
        </foreach>
    </delete>

    <select id="selectPartLeaseDetailsList" resultType="com.bonus.material.part.domain.PartLeaseDetails">
        SELECT
        pcad.id as id,
        pcad.task_id as taskId,
        pcad.part_id as partId,
        mp3.pa_name as partType,
        mp2.pa_name as partName,
        mp1.pa_name as partModel,
        pcad.pre_num as preNum,
        pcad.status as status,
        mp1.buy_price as bugPrice,
        dept.companyName as deptName,
        dept1.companyName as sendDeptName
        FROM
        pa_collar_apply_details pcad
        LEFT JOIN ma_part_type mp1 ON pcad.part_id = mp1.pa_id
        AND mp1.del_flag = '0'
        LEFT JOIN ma_part_type mp2 ON mp1.parent_id = mp2.pa_id
        AND mp2.del_flag = '0'
        LEFT JOIN ma_part_type mp3 ON mp2.parent_id = mp3.pa_id
        AND mp3.del_flag = '0'
        LEFT JOIN pa_collar_apply p1 on pcad.task_id = p1.task_id
        LEFT JOIN sys_user su on p1.creator = su.user_id
        LEFT JOIN (
        SELECT
        sd.dept_id AS deptId,
        sd1.dept_name AS companyName
        FROM
        sys_dept sd
        JOIN (
        SELECT
        CASE
        ancestors
        WHEN ( '0' ) THEN
        dept_id ELSE SUBSTRING_INDEX( SUBSTRING_INDEX( ancestors, ',', 2 ), ',', - 1 )
        END AS first_ancestor,
        dept_id
        FROM
        sys_dept
        ) AS subquery ON sd.dept_id = subquery.dept_id
        LEFT JOIN sys_dept sd1 ON sd1.dept_id = subquery.first_ancestor
        ) dept ON dept.deptId = su.dept_id
        LEFT JOIN sys_user su1 on pcad.auditor = su1.user_id
        LEFT JOIN (
        SELECT
        sd.dept_id AS deptId,
        sd1.dept_name AS companyName
        FROM
        sys_dept sd
        JOIN (
        SELECT
        CASE
        ancestors
        WHEN ( '0' ) THEN
        dept_id ELSE SUBSTRING_INDEX( SUBSTRING_INDEX( ancestors, ',', 2 ), ',', - 1 )
        END AS first_ancestor,
        dept_id
        FROM
        sys_dept
        ) AS subquery ON sd.dept_id = subquery.dept_id
        LEFT JOIN sys_dept sd1 ON sd1.dept_id = subquery.first_ancestor
        ) dept1 ON dept1.deptId = su1.dept_id
        <where>
            <if test="taskId != null ">
                and pcad.task_id = #{taskId}
            </if>
            <if test="partId != null ">
                and pcad.part_id = #{partId}
            </if>
        </where>
    </select>
    <select id="selectPartTreeList" resultType="com.bonus.material.ma.domain.PartType">
        select pa_id as id, pa_name as paName, storage_num as storageNum,
        level as level,
        parent_id as parentId
        from ma_part_type
        <where>
            del_flag = '0'
            <if test="level != null  and level != ''">
                and level = #{level}
            </if>
            <if test="id != null">
                and parent_id = #{id}
            </if>
            <if test="paName != null and paName != ''">
                and pa_name = #{partName}
            </if>
        </where>
    </select>

    <select id="getMonthMaxOrderByDate" resultType="java.lang.Integer">
        select COALESCE(max(month_order), 0) from pa_collar_apply
        where
            month(create_time) = #{month} and year(create_time) = #{year}
    </select>

    <select id="selectPartTreeListByLevel" resultType="com.bonus.material.ma.domain.PartType">
        select pa_id as id, pa_name as paName, storage_num as storageNum,
        level as level,
        parent_id as parentId
        from ma_part_type
        <where>
            del_flag = '0'
            <if test="id != null">
                and pa_id = #{id}
            </if>
        </where>
    </select>

    <select id="selectPerson" resultType="com.bonus.material.part.domain.PartLeaseDetails">
        SELECT
            part_id as partId,
            part_num as partNum
        FROM
            pa_person_storage
        WHERE
            part_id = #{partId}
          AND creator = #{createBy}
    </select>
    <select id="selectTypeKeeperByTypeId" resultType="com.bonus.material.part.domain.MaPartTypeKeeper">
        SELECT
            type_id as typeId,
            user_id as userId
        FROM
            ma_part_type_keeper
        WHERE
            type_id = #{typeId}
    </select>

    <select id="selectUserList" resultType="com.bonus.material.ma.domain.PartType">
        SELECT DISTINCT
            m.pa_id AS thirdId,
            m.pa_name AS paName,
            m1.pa_id AS secondId,
            m1.pa_name AS partName,
            m1.LEVEL AS secondLevel,
            m2.pa_id AS firstId,
            m2.pa_name As partType,
            m2.LEVEL AS firstLevel,
            m.parent_id as parentId,
            m.unit_name as unitName,
            m.storage_num as storageNum,
            m.buy_price as buyPrice,
            m.LEVEL as thirdLevel,
            m.remark as remark
        FROM
            ma_part_type m
                LEFT JOIN ma_part_type m1 ON m.parent_id = m1.pa_id
                and m1.del_flag = '0'
                LEFT JOIN ma_part_type m2 ON m1.parent_id = m2.pa_id
                and m2.del_flag = '0'
                <if test="userId != null">
                    right join ma_part_type_keeper mp ON m.pa_id = mp.type_id
                    and mp.user_id = #{userId}
                </if>
        WHERE m.del_flag = '0'
        <if test="level == 2 and id != null">
            and m1.parent_id = #{id}
        </if>
        <if test="level == 3 and id != null">
            and m.parent_id = #{id}
        </if>
    </select>

    <insert id="insertPartLeaseDetails">
        insert into pa_collar_apply_details(task_id, part_id, pre_num, al_num, remarks)
        values (#{taskId}, #{partId}, #{preNum}, #{alNum}, #{remarks})
    </insert>

    <insert id="batchPartLeaseDetails">
        insert into pa_collar_apply_details(task_id, part_id, pre_num, al_num, remarks, status)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.taskId}, #{item.partId}, #{item.preNum}, #{item.alNum}, #{item.remarks}, 0)
        </foreach>
    </insert>

    <insert id="insertPersonStockNum">
        insert into pa_person_storage(task_id, part_id, part_num, part_price, creator, create_time)
        values (#{taskId}, #{partId}, #{preNum}, #{bugPrice}, #{createBy}, #{createTime})
    </insert>

    <insert id="insertTypeKeeper">
        insert into ma_part_type_keeper(type_id, user_id, create_by, create_time)
        values
        (#{typeId}, #{userId}, #{createBy}, #{createTime})
    </insert>

    <delete id="deleteTypeKeeper">
        delete from ma_part_type_keeper where type_id = #{typeId} and user_id = #{userId}
    </delete>

    <update id="updatePartLeaseInfo">
        update pa_collar_apply
        <set>
            <if test="code != null and code != ''">
                `code` = #{code},
            </if>
            <if test="auditor !=null">
                auditor = #{auditor},
            </if>
            <if test="audit_time != null">
                audit_time = #{auditTime},
            </if>
            <if test="status != null">
                `status` = #{status},
            </if>
            <if test="remarks != null and remarks != ''">
                remarks = #{remarks}
            </if>
        </set>
        where task_id = #{taskId}
    </update>

    <update id="removePartLeaseDetailsByIds">
        delete pa_collar_apply_details where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="updatePartLeaseDetails">
        update pa_collar_apply_details
        <set>
            <if test="alNum != null">
                al_num = #{preNum},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="updateBy != null">
                auditor = #{updateBy},
            </if>
            audit_time = #{updateTime}
        </set>
        where id = #{id}
    </update>

    <update id="updateMaTypeStockNum">
        update
            ma_part_type
        set
            storage_num = storage_num - #{preNum},
            update_by = #{updateBy},
            update_time = #{updateTime}
        where
            pa_id = #{partId}
    </update>

    <update id="updatePersonStockNum">
        update
            pa_person_storage
        set
            part_num = part_num + #{preNum}
        where
            part_id = #{partId}
          <if test="createBy != null">
              and creator = #{createBy}
          </if>
    </update>

    <update id="updateTaskStatus">
        update
            pa_collar_apply
        set
            task_status = #{status}
        where
            task_id = #{taskId}
    </update>
</mapper>