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

    <select id="findByPage" resultType="org.jeecg.modules.projectManagement.entity.FdBioanalysisSampleMilestones">
        SELECT
            bsm.id,
            bsm.bioanalysis_code,
            bsm.create_time,
            bsm.status,
            bsm.overall_progress,
            bsm.milestone_code,
            con.code AS contractCode,
            con.contract_party,
            con.contract_type,
            cus.NAME AS bidPartyName,
            cus.abbreviation,
            pf.compound,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            pf.project_manager_id as projectManagerId,
            pf.bioanalytical_pm as bioanalysisIdPM,
            pf.clinical_pm as clinicalIdPM,
            bsm.project_file_no
        FROM
            fd_bioanalysis_sample_milestones bsm
            LEFT JOIN fd_contract con ON bsm.pk_contract = con.id
            LEFT JOIN fd_customer cus ON con.pk_customer_id = cus.id
            left jOIN fd_project_file pf on pf.id=bsm.pk_project_file
            ${ew.customSqlSegment}
    </select>


    <select id="queryBioanalysisByConId" resultType="org.jeecg.modules.projectManagement.entity.FdBioanalysisSampleMilestones">
            SELECT
                pf.id AS pkProjectFile,
                pf.contract_id AS pkContract,
                pf.project_name AS projectName,
                pf.customer_id AS pkBidParty,
                pf.contract_party AS contractPartyName,
                c.`code` AS contractCode,
                pf.customer_name AS bidPartyName,
                cus.abbreviation AS bidPartyNameAbbreviation,
                pf.contract_type AS contractType,
                pf.compound AS compound,
                pf.sign_date AS signDate,
                pf.bioanalysis_code AS bioanalysisCode,
                pf.bioanalysis_confirm_item_code AS bioanalysisConfirmItemCode,
                pf.bioanalysis_validation_code AS bioanalysisValidationCode,
                pf.biological_analysis_sample_analysis_code AS biologicalAnalysisSampleAnalysisCode,
                p1.`name` AS bioanalyticalPm,
                p2.`name` AS assistant,
                pf.matrix_type AS matrixType,
                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
                LEFT JOIN fd_personnel p1 ON p1.id = pf.investigator
                LEFT JOIN fd_personnel p2 ON p2.id = pf.assistant
            WHERE
                c.del_flag = '0'
                and pf.id=#{0}
    </select>

    <update id="auditBatch" parameterType="java.util.List">
        update fd_bioanalysis_sample_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_bioanalysis_sample_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
        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
    </select>


    <select id="getMainExport" resultType="org.jeecg.modules.projectManagement.entity.FdBioanalysisSampleMilestones">
        SELECT
            bsm.id,
            bsm.bioanalysis_code,
            bsm.create_time,
            bsm.status,
            bsm.overall_progress,
            bsm.milestone_code,
            con.code AS contractCode,
            con.contract_party,
            con.contract_type,
            cus.NAME AS bidPartyName,
            cus.abbreviation,
            pf.compound,
            pf.personnel_with_authority_ids as personnelWithAuthorityIds,
            pf.project_manager_id as projectManagerId,
            pf.bioanalytical_pm as bioanalysisIdPM,
            pf.clinical_pm as clinicalIdPM,
            bsm.project_file_no
        FROM
            fd_bioanalysis_sample_milestones bsm
            LEFT JOIN fd_contract con ON bsm.pk_contract = con.id
            LEFT JOIN fd_customer cus ON con.pk_customer_id = cus.id
            left jOIN fd_project_file pf on pf.id=bsm.pk_project_file
        WHERE
          pf.status <![CDATA[ <> ]]> '2'
    </select>

</mapper>