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

    <select id="findByPage" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
         SELECT
            fc.id,
            fc.`code`,
            fc.create_time,
            fc.source_quotation_code AS sourceQuotationCode,
            fc.contract_type,
            fc.project_name AS projectName,
            fcb.id AS bidParty,
            fc.contract_party AS contractParty,
            fc.quotation_id AS quotationId,
            fc.quotation_date AS quotationDate,
            fc.`status` AS STATUS,
            fcr.NAME AS customerName,
            fc.is_save,
            fc.is_import,
            fc.is_upload_enclosure,
            fpq.relation_code,
            fpq.relation_start,
            fc.sign_date
        FROM
            fd_contract fc
            LEFT JOIN fd_contract_bidparty fcb ON fc.id = fcb.contract_id
            LEFT JOIN fd_project_quotation fpq ON fc.quotation_id = fpq.id
            LEFT JOIN fd_customer fcr ON fcr.id = fc.pk_customer_id
             ${ew.customSqlSegment}
    </select>

    <select id="getContractExport" parameterType="java.util.List" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
            fc.id,
            fc.`code`,
            fc.create_time,
            fc.source_quotation_code AS sourceQuotationCode,
            fc.contract_type,
            fc.project_name AS projectName,
            fcb.id AS bidParty,
            fc.contract_party AS contractParty,
            fc.quotation_id AS quotationId,
            fc.quotation_date AS quotationDate,
            fc.`status` AS STATUS,
            fcr.NAME AS customerName,
            fc.is_save,
            fc.is_import,
            fc.is_upload_enclosure,
            fpq.relation_code,
            fpq.relation_start,
            fc.sign_date,
            fc.currency_type_name,
            fc.contract_type,
            fc.quotation_id
        FROM
            fd_contract fc
            LEFT JOIN fd_contract_bidparty fcb ON fc.id = fcb.contract_id
            LEFT JOIN fd_project_quotation fpq ON fc.quotation_id = fpq.id
            LEFT JOIN fd_customer fcr ON fcr.id = fc.pk_customer_id
        where
            fc.del_flag = '0'
            AND fc.id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
        ORDER BY fc.create_time DESC
    </select>

    <select id="listByRelationCode" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        select * FROM fd_contract fc
        LEFT JOIN fd_project_quotation fpq ON fc.quotation_id = fpq.id
        WHERE fc.del_flag='0' AND fpq.relation_code=#{fdContract.relationCode}
    </select>

    <select id="joinQuotationByContract" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT *,fpq.relation_code,fpq.relation_start FROM fd_contract fc
        LEFT JOIN fd_project_quotation fpq ON fc.quotation_id = fpq.id
        WHERE fc.del_flag='0'
        <if test="fdContract.id!=null and fdContract.id!=''">
          and fc.id=#{fdContract.id}
        </if>
    </select>

    <select id="findQuotationTemplate" resultType="org.jeecg.modules.basedata.entity.FdQuotationTemplate">
        SELECT
            b.id,
            a.type,
            a.`name`,
            a.description,
            a.fee_category1,
            a.fee_category2,
            a.version,
            a.`enable`,
            a.del_flag
        FROM
            fd_quotation_template a
            INNER JOIN fd_project_quotation_template b ON b.quotation_template_id = a.id
            WHERE
                b.quotation_id =#{0}
    </select>


    <update id="closeBatch" parameterType="java.util.List">
        update fd_contract set status = '2' where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>


    <update id="auditBatch" parameterType="java.util.List">
        update fd_contract set status = '0' where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>


    <update id="reviewBatch" parameterType="java.util.List">
        update fd_contract set status = '1' where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>

    <select id="contractReport" resultType="org.jeecg.modules.projectManagement.entity.FdContract" >
        SELECT
            c.CODE ,
            c.total_amount,
            p.name as bdName,
            c.contract_party,
            c.create_time
        FROM
            fd_contract c
            LEFT JOIN fd_personnel p on p.id = c.business_manager
        WHERE
          1=1
          and c.del_flag='0'
          <if test="startDate !=null and startDate!='' and startDate!='null'">
                <![CDATA[ and c.create_time >=#{startDate} ]]>
          </if>

          <if test="endDate !=null and endDate!='' and endDate!='null'">
                <![CDATA[ and c.create_time <=#{endDate} ]]>
          </if>
        ORDER BY c.create_time DESC
    </select>

    <update id="updateStatusShared" parameterType="java.util.List">
        update fd_contract set status = #{status} where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>

    <select id="totalProportionContract" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
        fper.name as projectManagerName,
        count(fp.code) as translate
        FROM
        fd_contract as fp
        left join fd_project_quotation fpq on fpq.id=fp.quotation_id
        LEFT JOIN fd_personnel fper on fper.id=fp.project_manager_id
        where
        fp.del_flag='0'

        <if test="quotationDate!=null">
            <![CDATA[   and DATE_FORMAT(fp.create_time, '%Y-%m-%d')>=  DATE_FORMAT(#{quotationDate}, '%Y-%m-%d')]]>
        </if>
        <if test="endDate!=null">
            <![CDATA[  and DATE_FORMAT(fp.create_time, '%Y-%m-%d') <= DATE_FORMAT(#{endDate}, '%Y-%m-%d') ]]>
        </if>
        <if test="fdContract.biologicalAnalysisLaboratoryIds!=null and fdContract.biologicalAnalysisLaboratoryIds.size > 0">
            AND fpq.biological_analysis_laboratory in
            <foreach collection="fdContract.biologicalAnalysisLaboratoryIds" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="fdContract.hospitalListIds!=null and fdContract.hospitalListIds.size > 0">
            AND (
            <foreach collection="fdContract.hospitalListIds" index="index" item="hospital">
                <if test="index>0">
                    or
                </if>
                fpq.hospital_list LIKE "%"#{hospital}"%"
            </foreach>
            )
        </if>
        <if test="fdContract.projectManagerIds!=null and fdContract.projectManagerIds.size > 0">
            and fp.project_manager_id in
            <foreach collection="fdContract.projectManagerIds" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="fdContract.quotationTypeIds!=null and fdContract.quotationTypeIds.size > 0">
            and fpq.quotation_type in
            <foreach collection="fdContract.quotationTypeIds" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="fdContract.createByIds!=null and fdContract.createByIds.size > 0">
            and fpq.create_by IN
            <foreach collection="fdContract.createByIds" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="fdContract.contractTypeIds!=null and fdContract.contractTypeIds.size > 0">
            AND (
            <foreach collection="fdContract.contractTypeIds" index="index" item="contractType">
                <if test="index>0">
                    or
                </if>
                fp.contract_type LIKE "%"#{contractType}"%"
            </foreach>
            )
        </if>
        group by fp.project_manager_id,fper.name
    </select>

    <!--fc.id,-->
    <!--fc.code,-->
    <!--fc.project_name as name,-->
    <!--fc.final_total_amount as finalTotalAmount,-->
    <select id="getReportPageContract" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
        fc.*,
        fpq.biological_analysis_laboratory_name,
        fpq.hospital_list_name,
        fpq.quotation_type_name,
        fc.contract_type_name,
        fp.NAME AS projectManagerName,
        su.realname AS createByName,
        fcr.name as customerName
        FROM
        fd_contract fc
        LEFT JOIN  fd_project_quotation fpq ON fc.quotation_id=fpq.id
        LEFT JOIN fd_personnel fp ON fc.project_manager_id = fp.id
        LEFT JOIN sys_user su ON fpq.create_by = su.username
        LEFT JOIN fd_customer fcr ON fcr.id = fc.pk_customer_id
        ${ew.customSqlSegment}
    </select>

    <select id="getContractGroupById" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
            fpf.contract_id as id,
            fc.CODE,
            fpf.id as projectFileId
        FROM
            fd_project_file fpf
            LEFT JOIN fd_contract fc ON fc.id = fpf.contract_id
        WHERE
            fpf.`status` = '1'
            AND fpf.del_flag = '0'
        GROUP BY
            fpf.contract_id,
            fc.CODE
        ORDER BY
            fpf.create_time DESC
    </select>

    <select id="getContractAndFild" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
            fc.*
        FROM
            fd_project_file fpf
            LEFT JOIN fd_contract fc ON fpf.contract_id = fc.id
        WHERE
            fpf.del_flag = '0'
            AND fpf.`status` <![CDATA[ <> ]]> '0'
            GROUP BY fc.code
            ORDER BY fc.create_time DESC
    </select>
</mapper>