<?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.purchase.mapper.PurchaseStorageMapper">

    <insert id="insertMachine">
        insert into ma_machine
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="typeId != null">type_id,</if>
            <if test="maCode != null">ma_code,</if>
            ma_status,
            <if test="qrCode != null">qr_code,</if>
            <if test="productDate != null">out_fac_time,</if>
            <if test="outFacCode != null">out_fac_code,</if>
            <if test="taskId != null and taskId != ''">buy_task,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeId != null">#{typeId},</if>
            <if test="maCode != null">#{maCode},</if>
            0,
            <if test="qrCode != null">#{qrCode},</if>
            <if test="productDate != null">#{productDate},</if>
            <if test="outFacCode != null">#{outFacCode},</if>
            <if test="taskId != null and taskId != ''">#{taskId},</if>
            now()
        </trim>
    </insert>

    <update id="updateNum">
        UPDATE purchase_check_details
        SET input_num = ifnull(input_num, 0) + ifnull(#{bindNum},0),input_time = now()
        WHERE id = #{id}
    </update>

    <update id="updateStatusById">
        UPDATE purchase_check_details
        SET `status` =  #{updatedStatus}
        WHERE id = #{id}
    </update>

    <update id="updateInputStatusById">
        UPDATE purchase_check_details SET `status` = if(check_num = input_num,  #{updatedStatus}, `status`) WHERE id = #{id}
    </update>

    <update id="updateStorageNum">
        UPDATE ma_type SET storage_num = ifnull(#{inputNum},0) + ifnull(storage_num, 0) WHERE type_id = #{typeId}
    </update>

    <update id="updateMachineByCode">
        UPDATE ma_machine SET ma_status = 1 WHERE ma_code = #{maCode} and type_id = #{typeId}
    </update>

    <update id="updatePurchaseBindByTaskIdAndTypeId">
        UPDATE purchase_check_details SET `status` = #{status} WHERE task_id = #{taskId} and type_id = #{typeId}
    </update>

    <update id="updatePurchaseBindBy">
        UPDATE purchase_check_details SET `status` = if(bind_num = input_num,  #{status}, `status`) WHERE task_id = #{taskId} and type_id = #{typeId}
    </update>

    <select id="selectAll" resultType="com.bonus.material.purchase.domain.vo.PurchaseVo">
        SELECT
            pci.task_id as taskId,
            pci.arrival_time as arrivalTime,
            tt.`code` as purchaseCode,
            GROUP_CONCAT( mt.type_name ) AS purchaseMaterial,
            COALESCE(SUM(pcd.bind_num), SUM(pcd.check_num)) AS purchaseNum,
            pci.purchaser as purchaserName,
            pci.create_by as createBy,
            pci.create_time as createTime,
            tt.task_status as STATUS,
            pci.remark as remark
        FROM
            purchase_check_info pci
                LEFT JOIN purchase_check_details pcd ON pcd.task_id = pci.task_id
                LEFT JOIN tm_task tt ON pci.task_id = tt.task_id
                LEFT JOIN ma_type mt ON pcd.type_id = mt.type_id
        where 1 = 1
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            <![CDATA[ AND DATE_FORMAT( pci.arrival_time, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime} ]]>
        </if>
        <if test="keyWord != null  and keyWord != ''">
            AND (
            tt.`code` LIKE CONCAT('%',#{keyWord},'%')
            OR pci.remark LIKE CONCAT('%',#{keyWord},'%')
            )
        </if>
        <if test="status != null and status != ''">
            AND pcd.`status` = #{status}
        </if>
        GROUP BY
        pci.task_id
        ORDER BY
        pci.arrival_time DESC
    </select>


    <select id="getDetails" resultType="com.bonus.material.purchase.domain.vo.PurchaseVo">
        SELECT
            pcd.task_id AS taskId,
            pcd.id AS purchaseId,
            mt1.type_name AS materialName,
            mt.type_name AS materialModel,
            pcd.check_num AS purchaseNum,
            pcd.check_num AS checkNum,
            pcd.bind_num AS bindNum,
            pcd.type_id as typeId,
            mt.unit_name AS unitName,
            ms.supplier AS supplierName,
            pcd.production_time AS productDate,
            mt.manage_type AS manageType,
            pcd.`status` AS STATUS
        FROM
            purchase_check_details pcd
                LEFT JOIN tm_task tt ON pcd.task_id = tt.task_id
                LEFT JOIN purchase_check_info pci ON pci.task_id = pcd.task_id
                LEFT JOIN ma_type mt ON pcd.type_id = mt.type_id
                LEFT JOIN ma_type mt1 ON mt.parent_id = mt1.type_id
                LEFT JOIN ma_supplier_info ms ON pcd.supplier_id = ms.supplier_id
        where 1 = 1
        <if test="typeId != null and typeId != ''">
            AND mt.id = #{typeId}
        </if>
        <if test="supplierId != null and supplierId != ''">
            AND ms.supplier_id = #{supplierId}
        </if>
        <if test="productDate != null and productDate != ''">
            <![CDATA[ AND DATE_FORMAT( pcd.production_time, '%Y-%m-%d' ) = #{productDate} ]]>
        </if>
        <if test="taskId != null">
            AND pcd.task_id = #{taskId}
        </if>
        <if test="purchaseId != null">
            AND pcd.id = #{purchaseId}
        </if>
        GROUP BY
        pcd.type_id
    </select>

    <select id="select" resultType="com.bonus.material.purchase.domain.vo.PurchaseVo">
        SELECT
            pcd.task_id as taskId,
            pcd.status as STATUS
        FROM
            purchase_check_details pcd
        where pcd.task_id = #{taskId}
    </select>

    <select id="selectIdByTaskId" resultType="java.lang.Long">
        SELECT id FROM purchase_check_details WHERE task_id = #{taskId} and type_id = #{typeId} limit 1
    </select>
</mapper>