<?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.jeeplus.modules.supply.materialsProvidedByAForReview.mapper.MaterialsProvidedByAForReviewMapper">

    <sql id="infoColumns">
        a.id AS "id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.stock_out_number AS "stockOutNumber",
        a.wbs_element AS "wbsElement",
        a.project_name AS "projectName",
        a.project_definition AS "projectDefinition",
        a.material_number AS "materialNumber",
        a.material_description AS "materialDescription",
        a.batch AS "batch",
        a.unit AS "unit",
        a.actual_quantity_issued AS "actualQuantityIssued",
        a.unit_price AS "unitPrice",
        a.money AS "money",
        a.posting_account_date AS "postingAccountDate",
        DATE_FORMAT(a.posting_account_date,'%Y-%m-%d') AS "postingAccountDateStr",
        a.supplier_name AS "supplierName",
        a.factory AS "factory",
        a.picking_number AS "pickingNumber",
        a.picking_create_by AS "pickingCreateBy",
        a.picking_department AS "pickingDepartment",
        a.reserved_number AS "reservedNumber",
        a.repertory_place AS "repertoryPlace",
        a.project_package_name AS "projectPackageName",
        a.stock_out_person AS "stockOutPerson",
        a.wbs_element_description AS "wbsElementDescription",
        a.project_second_classification AS "projectSecondClassification",
        a.project_thirdly_classification AS "projectThirdlyClassification",
        a.order_number AS "orderNumber",
        a.assess_type AS "assessType",
        a.stock_out_import_date AS "stockOutImportDate",
        DATE_FORMAT(a.stock_out_import_date,'%Y-%m-%d') AS "stockOutImportDateStr",
        a.accountant_certificate AS "accountantCertificate",
        a.reply_proof AS "replyProof",
        a.materials_type AS "materialsType"
    </sql>

    <select id="findList" resultType="com.jeeplus.modules.supply.materialsProvidedByAForReview.entity.MaterialsProvidedByAForReview">
        SELECT
        <include refid="infoColumns"/>
        ,a.submittals_id as "submittalsId"

        FROM supply_stock_out a
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
            ${dataScope}
            <if test="projectDefinition != null and projectDefinition != ''">
                AND a.project_definition LIKE CONCAT('%',#{projectDefinition},'%')
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND a.material_number LIKE CONCAT('%',#{materialNumber},'%')
            </if>
            <if test="submittalsId != null and submittalsId != ''">
                AND a.submittals_id LIKE CONCAT('%',#{submittalsId},'%')
            </if>

        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.stock_out_number asc,a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <!--物理删除-->
    <update id="delete">
        DELETE
        FROM supply_stock_out
        WHERE id = #{id}
    </update>

    <!--物理删除-->
    <update id="deleteByIdList">
        DELETE
        FROM supply_stock_out
        WHERE id in
        <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <!--批量新增-->
    <insert id="insertList">
        insert into supply_stock_out (
        id,
        stock_out_number,
        wbs_element,
        project_name,
        project_definition,
        material_number,
        material_description,
        batch,
        unit,
        actual_quantity_issued,
        unit_price,
        money,
        posting_account_date,
        supplier_name,
        factory,
        picking_number,
        picking_create_by,
        picking_department,
        reserved_number,
        repertory_place,
        project_package_name,
        stock_out_person,
        wbs_element_description,
        project_second_classification,
        project_thirdly_classification,
        order_number,
        assess_type,
        remarks,
        stock_out_import_date,
        accountant_certificate,
        reply_proof,
        create_by,
        create_date,
        update_by,
        update_date,
        materials_type
        )
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (
            #{item.id},
            #{item.stockOutNumber},
            #{item.wbsElement},
            #{item.projectName},
            #{item.projectDefinition},
            #{item.materialNumber},
            #{item.materialDescription},
            #{item.batch},
            #{item.unit},
            #{item.actualQuantityIssued},
            #{item.unitPrice},
            #{item.money},
            #{item.postingAccountDate},
            #{item.supplierName},
            #{item.factory},
            #{item.pickingNumber},
            #{item.pickingCreateBy},
            #{item.pickingDepartment},
            #{item.reservedNumber},
            #{item.repertoryPlace},
            #{item.projectPackageName},
            #{item.stockOutPerson},
            #{item.wbsElementDescription},
            #{item.projectSecondClassification},
            #{item.projectThirdlyClassification},
            #{item.orderNumber},
            #{item.assessType},
            #{item.remarks},
            #{item.stockOutImportDate},
            #{item.accountantCertificate},
            #{item.replyProof},
            #{item.createBy.id},
            #{item.createDate},
            #{item.updateBy.id},
            #{item.updateDate},
            #{item.materialsType}
            )
        </foreach>
    </insert>

</mapper>
