<?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.FdClinicalProjectMilestonesMapper">
    <select id="findByPage" resultType="org.jeecg.modules.projectManagement.entity.FdClinicalProjectMilestones">
       SELECT
            cpm.overall_progress,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            cpm.id,
            cpm.clinical_code,
            cpm.create_time,
            cpm.status,
            cpm.bid_party_name,
            pf.project_manager_id,
            cpm.milestone_code,
            con.code as contractCode,
            con.contract_party,
            con.contract_type,
            pf.compound,
            cpm.project_file_no,
            pf.clinical_pm as clinicalIdPM
        FROM
            fd_clinical_project_milestones cpm
            LEFT JOIN fd_contract con ON cpm.pk_contract = con.id
            left jOIN fd_project_file pf on pf.id=cpm.pk_project_file
             ${ew.customSqlSegment}
    </select>

    <select id="getByMilestonesId" resultType="org.jeecg.modules.projectManagement.entity.FdClinicalProjectMilestones">
        SELECT
            a.pk_milestone_template,
            b.`name` as milestoneTemplateName
        FROM
            fd_clinical_project_milestones a
            LEFT JOIN fd_milestone_template b ON a.pk_milestone_template = b.id
        WHERE
            a.del_flag='0' and
            a.id =#{0}
    </select>

    <!--临床项目中根据合同id查询数据-->
    <select id="getClinicalByConId" resultType="org.jeecg.modules.projectManagement.entity.FdClinicalProjectMilestones">
        SELECT
            pf.id as pkProjectFile,
            pf.contract_id as pkContract,
            pf.sign_date as signDate,
            pf.contract_party as pkContractParty,
            pf.customer_id as pkBidParty,
            pf.customer_name as bidPartyName,
            pf.contract_type as contractType,
            pf.clinical_code as clinicalCode,
            pf.project_name as projectName,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            c.code as contractCode,
            cus.abbreviation as bidPartyNameAbbreviation,
            pf.compound,
            c.quotation_id,
            pf.project_file_no
        FROM
            fd_project_file pf
            LEFT JOIN fd_contract c ON c.id = pf.contract_id
            left join fd_customer cus on cus.id=pf.customer_id
        where
            c.del_flag='0'
            and pf.id=#{0}
    </select>


    <update id="auditBatch" parameterType="java.util.List">
        update fd_clinical_project_milestones 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_clinical_project_milestones set status = '1' where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </update>

    <select id="getFileContractList" resultType="org.jeecg.modules.projectManagement.entity.FdContract">
        SELECT
            c.id,
            c.CODE,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            pf.project_file_no as projectFileNo,
            pf.id projectFileId
        FROM
            fd_project_file pf
            LEFT JOIN fd_contract c ON c.id = pf.contract_id
        WHERE
            pf.del_flag = '0'
            and pf.status='1'
        ORDER BY
            pf.create_time DESC
    </select>

    <select id="getProjectQuotationByContractId" resultType="org.jeecg.modules.projectManagement.entity.FdProjectFile">
        SELECT
            *
        FROM
            fd_project_file pf
            LEFT JOIN fd_contract c ON c.id = pf.contract_id
            where pf.del_flag='0' and c.id=#{0}
    </select>

    <select id="getMainExport" resultType="org.jeecg.modules.projectManagement.entity.FdClinicalProjectMilestones">
        SELECT
            cpm.overall_progress,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            cpm.id,
            cpm.clinical_code,
            cpm.create_time,
            cpm.status,
            cpm.bid_party_name,
            pf.project_manager_id,
            cpm.milestone_code,
            con.code as contractCode,
            con.contract_party,
            con.contract_type,
            pf.compound,
            cpm.project_file_no,
            pf.clinical_pm as clinicalIdPM,
            cpm.hospital_progress
        FROM
            fd_clinical_project_milestones cpm
            LEFT JOIN fd_contract con ON cpm.pk_contract = con.id
            left jOIN fd_project_file pf on pf.id=cpm.pk_project_file
        WHERE
            cpm.del_flag='0' AND
            pf.status <![CDATA[ <> ]]> '2'
            <if test="fdClinicalProjectMilestones.milestonesType!=null and fdClinicalProjectMilestones.milestonesType!=''">
                and cpm.milestones_type=#{fdClinicalProjectMilestones.milestonesType}
            </if>
    </select>

</mapper>