<?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="org.jeecg.modules.projectManagement.mapper.FdProjectQuotationTemplateItemMapper">

    <select id="getListTemplateItem"
            resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
            a.id,
            a.text,
            a.project_quotation_template_item_id,
            a.fee_category1,
            a.fee_category2,
            a.memo,
            b.name as templateItemName
        FROM
            fd_project_quotation_template_item a
            LEFT JOIN fd_quotation_template_item b ON a.project_quotation_template_item_id = b.id
        WHERE
            a.quotation_id = '576207d726b0d5a944c3feaa86b35eff'
            AND a.project_quotation_template_id = '6654a5020536215e3f6c9d5bca8a8f46'
    </select>

    <select id="getTemplateItemMerge" resultType="java.util.Map">
        SELECT
            count(1) AS 'count',
            b.id As 'id'
        FROM
            fd_project_quotation_template_item a
            LEFT JOIN fd_quotation_template_item b ON a.project_quotation_template_item_id = b.id
        WHERE
            a.quotation_id = '576207d726b0d5a944c3feaa86b35eff'
            AND a.project_quotation_template_id = '6654a5020536215e3f6c9d5bca8a8f46'
        GROUP BY
            a.project_quotation_template_item_id
    </select>

    <select id="getByQuotationId1"
            resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
            a.quotation_id,
            a.project_quotation_template_id,
            a.fee_category1 + a.fee_category2 AS fee,
            b.type AS type
        FROM
            fd_project_quotation_template_item a
            LEFT JOIN fd_quotation_template b ON a.project_quotation_template_id = b.id
        WHERE
            a.quotation_id =#{0}
    </select>


    <update id="updateTemplateItemBatch" parameterType="java.util.List">
        <foreach collection="list" item="temp" index="index" open="" close="" separator=";">
            UPDATE fd_project_quotation_template_item
            <set>
                MASTER = #{temp.master},
                depart_id=#{temp.departId},
                depart_name=#{temp.departName},
                fee_category1_contract=#{temp.feeCategory1Contract},
                fee_category2_contract=#{temp.feeCategory2Contract}
            </set>
            WHERE
            id = #{temp.id}
        </foreach>
    </update>

    <select id="listOrderByCode" resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
        *,
        qt.name as quotationTemplateName
        FROM
        fd_project_quotation_template_item pqti
        LEFT JOIN fd_project_quotation_template pqt ON pqt.id = pqti.project_quotation_template_id
        LEFT JOIN fd_quotation_template qt ON qt.id = pqt.quotation_template_id
        WHERE
        pqti.del_flag='0' AND
        pqti.quotation_id = #{quotationId}
        <if test="quotationTemplateId!=null and quotationTemplateId!=''">
            and project_quotation_template_id=#{quotationTemplateId}
        </if>
        ORDER BY
          pqti.sort
    </select>
    <!--pqti.code+0 asc,CONVERT(pqti.code USING gbk)asc-->


    <update id="parentIdBatch" parameterType="java.util.List">
        <foreach collection="templateItemList" item="templateItem" index="index" open="" close="" separator=";">
            update fd_project_quotation_template_item
            <set>
                parent_id =#{templateItem.parentId}
            </set>
            <where>
                id=#{templateItem.id}
            </where>
        </foreach>
    </update>

    <select id="getItemAndTemp" resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
            *,
            qt.type
        FROM
            fd_project_quotation_template_item pqti
            LEFT JOIN fd_project_quotation_template pqt ON pqt.id = pqti.project_quotation_template_id
            LEFT JOIN fd_quotation_template qt ON qt.id = pqt.quotation_template_id
        WHERE
            pqti.del_flag='0' AND
            pqti.quotation_id = #{quotationId}
    </select>

    <update id="updateDelFlagByQuotationId">
        UPDATE fd_project_quotation_template_item SET del_flag='1' WHERE quotation_id=#{quotationId}
    </update>

    <!--收入确认选择合同时调用-->
    <select id="getTempItems" resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
            pqti.id,
            pqti.parent_id,
            pqti.NAME,
            pqti.text,
            pqti.deliverables,
            pqti.timeline,
            pqti.fee_category1_contract,
            pqti.fee_category2_contract,
            qt.type,
            pqt.name as quotationTemplateName
        FROM
          fd_project_quotation_template_item pqti
        LEFT JOIN fd_project_quotation_template pqt ON pqt.id = pqti.project_quotation_template_id
        LEFT JOIN fd_quotation_template qt ON qt.id = pqt.quotation_template_id
        WHERE
            qt.type IN ( '2', '5','6' )
            AND pqti.quotation_id = #{quotationId}
        ORDER BY
            qt.type,
	        pqt.sort,
            pqti.sort
    </select>

    <!--生物分析模板项目-->
    <select id="getBioTempItemsBycontractId" resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
        fpqt.name as quotationTemplateName,
        pqti.id,
        pqti.parent_id,
        pqti.NAME,
        pqti.text,
        pqti.deliverables,
        pqti.timeline,
        pqti.fee_category1_contract,
        pqti.fee_category2_contract,
        qt.type,
        pqt.name as quotationTemplateName
        FROM
        fd_project_quotation_template_item pqti
        LEFT JOIN fd_project_quotation_template pqt ON pqt.id = pqti.project_quotation_template_id
        LEFT JOIN fd_quotation_template qt ON qt.id = pqt.quotation_template_id
	    left join fd_project_quotation_template fpqt on pqti.project_quotation_template_id=fpqt.id
	    left join fd_contract fc  on fc.quotation_id=pqti.quotation_id
        WHERE
        qt.type IN ( '2','6')
        AND fc.id = #{contractId}
        ORDER BY
        qt.type,
        pqt.sort,
        pqti.sort
    </select>

    <select id="getItemAndTempByQuotation" resultType="org.jeecg.modules.projectManagement.entity.FdProjectQuotationTemplateItem">
        SELECT
        pqti.*,
        qt.type,
        pqt.name AS quotationTemplateName
        FROM
        fd_project_quotation_template_item pqti
        LEFT JOIN fd_project_quotation_template pqt ON pqt.id = pqti.project_quotation_template_id
        LEFT JOIN fd_quotation_template qt ON qt.id = pqt.quotation_template_id
        WHERE
        pqti.del_flag='0'
        <if test="quotationId!=null and quotationId!=''">
            AND pqti.quotation_id = #{quotationId}
        </if>
        <if test="types!=null and types.size>0">
            AND qt.type IN
            <foreach collection="types" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
    </select>
</mapper>