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

    <select id="queryQaBiologicalByConId" resultType="org.jeecg.modules.qualityControl.entity.FdQaBiologicalAnalysisVerification">
            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.contract_type AS analysisType,
                pf.bioanalytical_pm AS bioanalyticalPi,
                pf.assistant AS associateBioanalyticalPi,
                pf.matrix_type AS matrix,
                pf.investigator
            FROM
                fd_project_file pf
                LEFT JOIN fd_contract c ON c.id = pf.contract_id
            WHERE
                c.del_flag = '0'
            and pf.contract_id=#{0}
    </select>

    <select id="getByDict" resultType="java.lang.String">
        SELECT
            sdi.item_text
            FROM
            sys_dict_item sdi
            WHERE sdi.item_value=#{0}
            and sdi.dict_id = "520e23f508571ee73632a23582837bdc"
    </select>

    <select id="getApproveByDict" resultType="java.lang.String">
        SELECT
            sdi.item_text
            FROM
            sys_dict_item sdi
            WHERE sdi.item_value=#{0}
            and sdi.dict_id = "94709ebaa2becb86efa17eed8643531e"
    </select>

    <select id="getQAReviewByDict" resultType="java.lang.String">
        SELECT
            sdi.item_text
            FROM
            sys_dict_item sdi
            WHERE sdi.item_value=#{0}
            and sdi.dict_id = "e28e79d29d9bbec3136ceaf8bcf86e93"
    </select>

    <select id="getQCReviewByDict" resultType="java.lang.String">
        SELECT
            sdi.item_text
            FROM
            sys_dict_item sdi
            WHERE sdi.item_value=#{0}
            and sdi.dict_id = "b8d1dc8beaf88601736869134d0d315e"
    </select>

    <select id="findByPage" resultType="org.jeecg.modules.qualityControl.entity.FdQaBiologicalAnalysisVerification">
        SELECT
        *,
        pf.personnel_with_authority_ids as personnelWithAuthorityIds,
        fqv.code as verifiCode,
        fp.name as responsibleQaName
        FROM fd_qa_biological_analysis_verification qbav
        LEFT JOIN fd_project_file pf on qbav.pk_project_file=pf.id
        LEFT JOIN fd_qa_validation fqv on fqv.pk_qa_validation=qbav.id
        left join fd_personnel fp on fp.id=qbav.responsible_qa
        ${ew.customSqlSegment}
    </select>

    <select id="getByVerifiCodeAndType" resultType="org.jeecg.modules.qualityControl.entity.FdQaBiologicalAnalysisVerification">
        SELECT
        *,
        fqv.code as verifiCode
        FROM fd_qa_biological_analysis_verification qbav
        LEFT JOIN fd_qa_validation fqv on fqv.pk_qa_validation=qbav.id
        WHERE
        qbav.del_flag='0'
        and qbav.pk_project_file=#{fdQaBiologicalAnalysisVerification.pkProjectFile}
        AND qbav.analysis_type=#{fdQaBiologicalAnalysisVerification.analysisType}
        AND fqv.code=#{fdQaBiologicalAnalysisVerification.verifiCode}
        AND qbav.verification_type=#{fdQaBiologicalAnalysisVerification.verificationType}
    </select>
</mapper>