<?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.sg.picking.activiti.mapper.ShowListMapper">

    <sql id="classColumns">
		a.md_big_class AS "mdBigClass",
		a.md_the_class AS "mdTheClass",
		a.md_name AS "mdName",
		a.material_code AS "materialCode",
		a.material_description AS "materialDescription",
		a.extension_description AS "extensionDescription",
		a.md_units AS "mdUnits",
		a.md_count AS "mdCount",
		a.md_single_weight AS "mdSingleWeight",
		a.md_total_weight AS "mdTotalWeight",
		a.md_the_party AS "mdTheParty",
		a.md_text AS "mdText",
		a.md_total_amount AS "mdTotalAmount",
		a.block_id AS "blockId"
	</sql>


    <select id="get" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
        SELECT
        <include refid="classColumns"/>,
        FROM md_module_listing a
        WHERE a.id = #{id}
    </select>

    <select id="findList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
        SELECT
        <include refid="classColumns"/>
        FROM md_module_listing a
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
        </choose>
        <where>
            <trim prefixOverrides="AND |OR ">
                <if test="findList!=null and findList.size>0">
                    AND block_id in
                    <foreach collection="findList" index="index" item="item" open="(" separator="," close=")">
                        #{item}
                    </foreach>
                </if>
            </trim>
        </where>
        order by material_code,material_description
    </select>

    <select id="findAllList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
        SELECT
        <include refid="classColumns"/>
        FROM md_module_listing a
    </select>

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

    <!--逻辑删除-->
    <update id="deleteByLogic">
		UPDATE md_module_block SET
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>

    <insert id="insert">
		INSERT INTO md_acquisition_process(
			id,
			proc_ins_id,
			project_id,
			the_number,
			version,
			select_number,
			exportAfter,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{procInsId},
			#{projectId},
			#{theNumber},
			#{sVersion},
			#{selectNumber},
			'无',
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>

    <update id="update">
		UPDATE md_acquisition_process SET
			proc_ins_id = #{procInsId},
			project_id = #{projectId},
			the_number = #{theNumber},
			version = #{sVersion},
			select_number = #{selectNumber},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks}
		WHERE id = #{id}
	</update>

    <select id="findNumber" resultType="string">
		select the_number from md_acquisition_process where project_id = #{projectId} order by the_number DESC LIMIT 1;
	</select>

    <insert id="newSave">
		INSERT INTO md_picking_application(
			id,
			proc_ins_id,
			project_id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{procInsId},
			#{projectId},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>

    <update id="projectUpdate">
		update xm_project_details set earlyMaterial=1
		where id=#{projectId};
	</update>

    <select id="selectProjectId" resultType="string">
		select project_id from md_picking_application where id =#{id}
	</select>

    <select id="findNewList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
	SELECT
		id,
		procInsId,
		materialCode,
		extensionDescription,
		materialDescription,
		mdUnits,
		mdTotalAmount,
		mdTotalAmountAfter,
		mdSingleWeight,
		mdTotalWeight,
		mdTheParty,
		mdText
	from md_process_list where procInsId = #{procInsId}
	</select>

    <select id="findNewTemList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
	SELECT
		id,
		procInsId,
		materialCode,
		extensionDescription,
		materialDescription,
		mdUnits,
		mdTotalAmount,
		mdTotalAmountAfter,
		mdSingleWeight,
		mdTotalWeight,
		mdTheParty,
		mdText
	from md_temporary_list where procInsId = #{procInsId}
	</select>

    <select id="inventoryComparisonData" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
        SELECT
        id,
        procInsId,
        materialCode,
        extensionDescription,
        materialDescription,
        mdUnits,
        mdTotalAmount,
        mdSingleWeight,
        mdTotalWeight,
        mdTheParty,
        mdText
        from md_process_list where procInsId in
        (
        <foreach collection="list" item="item" separator=",">
            #{item}
        </foreach>
        )
    </select>

    <select id="findOneList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
	SELECT
		id,
		procInsId,
		materialCode,
		extensionDescription,
		materialDescription,
		mdUnits,
		mdTotalAmount,
		mdSingleWeight,
		mdTotalWeight,
		mdTheParty,
		mdText
	from md_process_list where id = #{id}
	</select>


    <select id="itemQuery" resultType="string">
		select select_number from md_acquisition_process where proc_ins_id = #{procInsId}
	</select>

    <update id="updateAmount">
		update md_process_list
		set mdTotalAmount = #{mdTotalAmount},
			mdTotalWeight=#{mdTotalWeight}
		where id = #{id};
	</update>


    <select id="findOneTemList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
	SELECT
		id,
		procInsId,
		materialCode,
		extensionDescription,
		materialDescription,
		mdUnits,
		mdTotalAmount,
		mdSingleWeight,
		mdTotalWeight,
		mdTheParty,
		mdText
	from md_temporary_list where id = #{id}
	</select>

    <update id="updateTemAmount">
		update md_temporary_list
		set mdTotalAmount = #{mdTotalAmount},
			mdTotalWeight=#{mdTotalWeight}
		where id = #{id};
	</update>

    <delete id="deleteTemData">
		delete from md_temporary_list where id=#{id};
	</delete>


    <delete id="deleteData">
		delete from md_process_list where id=#{id};
	</delete>

    <select id="inventoryQuery"
            resultType="com.jeeplus.modules.sg.managementcenter.materialInventory.entity.MaterialInventory">
        select materialCode,availableStockQuantity,materialDetail from xm_material_inventory
        where materialCode in
        (
        <foreach collection="list" item="item" separator=",">
            #{item.materialCode}
        </foreach>
        )
    </select>

    <select id="processListQuery" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ShowList">
        select materialCode,mdTotalAmount,extensionDescription,materialDescription from md_process_list
        where procInsId in
        (
        <foreach collection="list" item="item" separator=",">
            #{item}
        </foreach>
        )
    </select>

    <select id="acquisitionList" resultType="com.jeeplus.modules.sg.picking.activiti.entity.Acquisition">
        select
        a.id,
        a.proc_ins_id,
        b.require_name as projectId,
        b.pro_type as proType,
        a.version,
        a.the_number,
        a.select_number,
        a.exportStatus,
        a.exportAfter,
        a.exportTime,
        a.managerTime
        from md_acquisition_process as a
        left join xm_project_details as b
        on a.project_id = b.id
        where exportStatus = '1'
        <if test="projectId != null and projectId != ''">
            AND b.require_name LIKE concat('%',#{projectId},'%')
        </if>
        order by managerTime desc,exportAfter desc
    </select>

    <update id="updateManagerTime">
		update md_acquisition_process set managerTime = NOW(),exportStatus = 1 where proc_ins_id = #{procInsId};
	</update>

    <select id="selectAllId" resultType="java.lang.String">
		select
		a.id
		from md_acquisition_process as a
		where exportStatus = '1'
	</select>

    <update id="updateAcqStatus">
		update xm_project_details set acquisition_status = 1 where id = #{projectId};
	</update>

    <select id="findAcqStatus" resultType="java.lang.String">
		select acquisition_status from xm_project_details where id = #{projectId};
	</select>

    <select id="exportListing" resultType="com.jeeplus.modules.sg.picking.activiti.entity.ExportListing">
        select
        a.md_big_class AS "mdBigClass",
        a.md_the_class AS "mdTheClass",
        a.md_name AS "mdName",
        a.material_code AS "materialCode",
        a.material_description AS "materialDescription",
        a.extension_description AS "extensionDescription",
        a.md_units AS "mdUnits",
        a.md_count AS "mdCount",
        a.md_single_weight AS "mdSingleWeight",
        a.md_total_weight AS "mdTotalWeight",
        a.md_the_party AS "mdTheParty",
        a.md_text AS "mdText",
        a.md_total_amount AS "mdTotalAmount",
        a.block_id AS "blockId"
        from md_module_listing as a
        where block_id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by material_code
    </select>

    <select id="findMdImg" resultType="com.jeeplus.modules.sg.picking.activiti.entity.PickIng">
		select md_img as mdImg from md_module_block where id =  #{id};
	</select>
</mapper>