<?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.qualityControl.mapper.FdQcBiometricSampleAnalysisMapper">

    <select id="queryQcBiometricByConId" resultType="org.jeecg.modules.qualityControl.entity.FdQcBiometricSampleAnalysis">
        SELECT
            pf.id AS pkProjectFile,
            pf.contract_id AS pkContract,
            pf.project_name AS projectName,
            pf.customer_id AS pkBidParty,
            pf.customer_name AS bidPartyName,
            pf.abbreviation AS bidPartyNameAbbreviation,
            pf.contract_party AS pkContractParty,
            c.`code` AS contractCode,
            c.sign_date AS contractSignDate,
            pf.investigator AS bioanalyticalPi,
            pf.assistant AS associateBioanalyticalPi,
            pf.matrix_type AS matrix,
            c.project_description as contractDescription,
            pf.clinical_pi as clinicalPi,
            p.name as clinicalPiName,
            pf.project_file_no
        FROM
            fd_project_file pf
            LEFT JOIN fd_contract c ON c.id = pf.contract_id
            LEFT JOIN fd_personnel p on pf.clinical_pi=p.id
        WHERE
            c.del_flag = '0'
            and pf.id=#{0}
    </select>

    <select id="findByPage" resultType="org.jeecg.modules.qualityControl.entity.FdQcBiometricSampleAnalysis">
        SELECT
        *,
        pf.personnel_with_authority_ids as personnelWithAuthorityIds,
        fqs.code as sampleCode,
        fp.name as responsibleQcName
        FROM fd_qc_biometric_sample_analysis qsa
        LEFT JOIN fd_project_file pf on qsa.pk_project_file=pf.id
        LEFT JOIN fd_qc_sample fqs on fqs.pk_qc_sample=qsa.id
        left join fd_personnel fp on fp.id=qsa.responsible_qc
        ${ew.customSqlSegment}
    </select>

    <select id="getBySampleCodeAndType" resultType="org.jeecg.modules.qualityControl.entity.FdQcBiometricSampleAnalysis">
        SELECT
        *,
        fqs.code as sampleCode
        FROM fd_qc_biometric_sample_analysis qsa
        LEFT JOIN fd_qc_sample fqs on fqs.pk_qc_sample=qsa.id
        WHERE
        qsa.del_flag='0'
        and qsa.pk_project_file=#{fdQcBiometricSampleAnalysis.pkProjectFile}
        AND qsa.analysis_type=#{fdQcBiometricSampleAnalysis.analysisType}
        AND fqs.code=#{fdQcBiometricSampleAnalysis.sampleCode}
    </select>
</mapper>