<?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.PurchaseBindMapper">

    <insert id="insertMachine">

    </insert>

    <insert id="add">
        insert into purchase_macode_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="taskId != null">task_id,</if>
            <if test="typeId != null">type_id,</if>
            <if test="maCode != null">ma_code,</if>
            <if test="qrCode != null">qr_code,</if>
            <if test="fixCode != null">fix_code,</if>
            <if test="codeType != null">code_type,</if>
            <if test="status != null">status,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="companyId != null">company_id,</if>
            <if test="outFacCode != null">out_fac_code,</if>
            <if test="qrUrl != null">qr_url,</if>
            del_flag
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="taskId != null">#{taskId},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="maCode != null">#{maCode},</if>
            <if test="qrCode != null">#{qrCode},</if>
            <if test="fixCode != null">#{fixCode},</if>
            <if test="codeType != null">#{codeType},</if>
            <if test="status != null">#{status},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="outFacCode != null">#{outFacCode},</if>
            <if test="qrUrl != null">#{qrUrl},</if>
            0
        </trim>
    </insert>

    <insert id="insert">
        INSERT INTO purchase_macode_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="qrCode != null">
                qr_code,
            </if>
            <if test="typeId != null">
                type_id,
            </if>
            <if test="qrUrl != null">
                qr_url,
            </if>
            <if test="taskId != null">
                task_id,
            </if>
            <if test="createBy != null">
                create_by,
            </if>
            create_time,
            del_flag,
            status
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
        <if test="qrCode != null">
            #{qrCode},
        </if>
        <if test="typeId != null">
            #{typeId},
        </if>
        <if test="qrUrl != null">
            #{qrUrl},
        </if>
        <if test="taskId != null">
            #{taskId},
        </if>
        <if test="createBy != null">
            #{createBy},
        </if>
        NOW(),
        0,
        0
        </trim>
    </insert>

    <update id="updateNum">
        UPDATE purchase_check_details
        SET bind_num = COALESCE(bind_num, 0) + COALESCE(#{num}, 0)
        WHERE task_id = #{dto.taskId}
          AND type_id = #{dto.typeId}
          AND #{num} IS NOT NULL
    </update>

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

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

    <update id="updateStatus">
        UPDATE purchase_check_details
        SET is_download = '1'
        WHERE id = #{purchaseId}
        <if test="id != null and id != ''">
            and task_id = #{id}
        </if>
    </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,
            GROUP_CONCAT( mt.manage_type ) AS manageType,
            SUM( pcd.check_num ) AS purchaseNum,
            COUNT(CASE WHEN pcd.status != 3 THEN 1 END) AS exceptionNum,
            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="selectPurchaseCheckInfoById" resultType="com.bonus.material.purchase.domain.vo.PurchaseVo">
        SELECT
            pm.task_id AS taskId,
            pcd.id AS purchaseId,
            mt1.type_name AS materialName,
            mt.type_name AS materialModel,
            pm.ma_code AS maCode,
            pm.create_by AS createBy,
            pm.create_time AS createTime,
            pm.type_id AS typeId,
            pm.out_fac_code AS outFacCode,
            pcd.production_time AS productDate,
            pm.qr_code AS qrCode,
            pm.qr_url AS qrUrl,
            pcd.check_num AS purchaseNum,
            pcd.check_num AS checkNum,
            pcd.bind_num AS bindNum,
            pcd.status AS status
        FROM
            purchase_macode_info pm
                LEFT JOIN purchase_check_details pcd ON pm.task_id = pcd.task_id
                AND pm.type_id = pcd.type_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
        WHERE
            1=1 and pm.qr_code IS NULL
        <if test="taskId != null">
            AND pm.task_id = #{taskId}
        </if>
        <if test="typeId != null">
            AND pm.type_id = #{typeId}
        </if>
        <if test="purchaseId != null">
            AND pcd.id = #{purchaseId}
        </if>
        <if test="keyWord != null  and keyWord != ''">
            AND (
            pm.ma_code LIKE CONCAT('%',#{keyWord},'%')
            OR pm.out_fac_code LIKE CONCAT('%',#{keyWord},'%')
            OR pm.qr_code LIKE CONCAT('%',#{keyWord},'%')
            )
        </if>
    </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,
            pm.ma_code AS maCode,
            pm.out_fac_code AS outFacCode,
            pm.qr_code AS qrCode
        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
                LEFT JOIN purchase_macode_info pm on pcd.task_id = pm.task_id and pm.type_id = pcd.type_id
        where 1 = 1
        <if test="typeId != null and typeId != ''">
            AND mt.type_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
            qr_code as qrCode
        FROM
            purchase_macode_info
        WHERE
            DATE_FORMAT(create_time, '%Y-%m') = #{genMonth} and ma_code is null
        ORDER BY
            qr_code DESC
    </select>

    <select id="selectByCode" resultType="com.bonus.common.biz.domain.purchase.PurchaseDto">
        SELECT
            task_id as taskId,
            type_id as typeId,
            ma_code as maCode
        FROM
            purchase_macode_info
        where del_flag = '0' and ma_code = #{maCode}
    </select>

    <select id="selectStatus" resultType="java.lang.Integer">
        SELECT
               is_download
        FROM purchase_check_details
        WHERE id = #{purchaseId}
    </select>

    <select id="getDetailById" 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,
        pm.ma_code AS maCode,
        pm.out_fac_code AS outFacCode,
        pm.qr_code AS qrCode
        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
        LEFT JOIN purchase_macode_info pm on pcd.task_id = pm.task_id and pm.type_id = pcd.type_id
        where 1 = 1
        <if test="typeId != null and typeId != ''">
            AND mt.id = #{typeId}
        </if>
        <if test="taskId != null">
            AND pcd.task_id = #{taskId}
        </if>
        <if test="purchaseId != null">
            AND pcd.id = #{purchaseId}
        </if>
    </select>

    <select id="getMachineById" resultType="com.bonus.material.purchase.domain.PurchaseCheckDetails">
        SELECT
        pmi.id as id,
	    pmi.task_id as taskId,
	    pmi.type_id as typeId,
	    pmi.ma_code as maCode
        FROM
	    purchase_macode_info pmi
	    LEFT JOIN ma_machine mm ON pmi.ma_code = mm.ma_code
	    AND mm.type_id = #{typeId}
        WHERE
	    pmi.task_id = #{taskId}
	    AND pmi.type_id = #{typeId}
	    AND mm.ma_code IS NULL
        AND pmi.ma_code IS NOT NULL
    </select>

</mapper>