<?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="digital.laboratory.platform.entrustment.mapper.EntrustmentMapper">

    <resultMap id="entrustmentMap" type="digital.laboratory.platform.entrustment.entity.Entrustment">
        <id property="id" column="id"/>
        <result property="caseId" column="case_id"/>
        <result property="entrustmentNo" column="entrustment_no"/>
        <result property="entrustmentType" column="entrustment_type"/>
        <result property="businessType" column="business_type"/>
        <result property="processInstanceId" column="process_instance_id"/>
        <result property="identificationId" column="identification_id"/>
        <result property="entrustmentTime" column="entrustment_time"/>
        <result property="submitter" column="submitter"/>
        <result property="submitTime" column="submit_time"/>
        <result property="clientOrgId" column="client_org_id"/>
        <result property="clientOrgName" column="client_org_name"/>
        <result property="clientAddress" column="client_address"/>
        <result property="clientPostcode" column="client_postcode"/>
        <result property="clientTel" column="client_tel"/>
        <result property="clientFax" column="client_fax"/>
        <result property="identificationDomain" column="identification_domain"/>
        <result property="identificationOrgName" column="identification_org_name"/>
        <result property="qualitativeAnalysis" column="qualitative_analysis"/>
        <result property="quantitativeAnalysis" column="quantitative_analysis"/>
        <result property="otherIdentificationRequests" column="other_identification_requests"/>
        <result property="candidateDrugs" column="candidate_drugs"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result property="isClientSpecifiedMethod" column="is_client_specified_method"/>
        <result property="clientSpecifiedMethod" column="client_specified_method"/>
        <result property="destructiveAnalysis" column="destructive_analysis"/>
        <result property="otherIdentificationNotes" column="other_identification_notes"/>
        <result property="reidentification" column="reidentification"/>
        <result property="oldIdentificationOrgName" column="old_identification_org_name"/>
        <result property="oldIdentificationDocument" column="old_identification_document"/>
        <result property="oldIdentificationResult" column="old_identification_result"/>
        <result property="reidentificationReason" column="reidentification_reason"/>
        <result property="avoidIdentifier" column="avoid_identifier"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result property="specifiedIdentifier" column="specified_identifier"/>
        <result property="otherAgreement" column="other_agreement"/>
        <result property="comments" column="comments"/>
        <result property="checkCandidateUser" column="check_candidate_user"/>
        <result property="checkClaimUser" column="check_claim_user"/>
        <result property="checkClaimTime" column="check_claim_time"/>
        <result property="checkUser" column="check_user"/>
        <result property="checkTime" column="check_time"/>
        <result property="checkComments" column="check_comments"/>
        <result property="checkTimes" column="check_times"/>
        <result property="checkAllIdentificationMaterialConfirmed"
                column="check_all_identification_material_confirmed"/>
        <result property="approveCandidateUser" column="approve_candidate_user"/>
        <result property="approveClaimUser" column="approve_claim_user"/>
        <result property="approveClaimTime" column="approve_claim_time"/>
        <result property="approveUser" column="approve_user"/>
        <result property="approveTime" column="approve_time"/>
        <result property="approveComments" column="approve_comments"/>
        <result property="approveTimes" column="approve_times"/>
        <result property="deliverConfirmUser" column="deliver_confirm_user"/>
        <result property="deliverConfirmTime" column="deliver_confirm_time"/>
        <result property="deliverConfirmComments" column="deliver_confirm_comments"/>
        <result property="deliverTime" column="deliver_time"/>
        <result property="deliverSubmitter" column="deliver_submitter"/>
        <result property="deliverer1Name" column="deliverer1_name"/>
        <result property="deliverer1Position" column="deliverer1_position"/>
        <result property="deliverer1Cert" column="deliverer1_cert"/>
        <result property="deliverer1Id" column="deliverer1_id"/>
        <result property="deliverer1Phone" column="deliverer1_phone"/>
        <result property="deliverer2Name" column="deliverer2_name"/>
        <result property="deliverer2Position" column="deliverer2_position"/>
        <result property="deliverer2Cert" column="deliverer2_cert"/>
        <result property="deliverer2Id" column="deliverer2_id"/>
        <result property="deliverer2Phone" column="deliverer2_phone"/>
        <result property="acceptNo" column="accept_no"/>
        <result property="acceptUser" column="accept_user"/>
        <result property="acceptTime" column="accept_time"/>
        <result property="acceptComments" column="accept_comments"/>
        <result property="identificationItemsConfirmPrinted" column="identification_items_confirm_printed"/>
        <result property="processInfo" column="process_info"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result property="reportReceiveMode" column="report_receive_mode"/>
        <result property="reportReceiver1UserId" column="report_receiver1_user_id"/>
        <result property="reportReceiver1Name" column="report_receiver1_name"/>
        <result property="reportReceiver1Position" column="report_receiver1_position"/>
        <result property="reportReceiver1Cert" column="report_receiver1_cert"/>
        <result property="reportReceiver1Id" column="report_receiver1_id"/>
        <result property="reportReceiver1Phone" column="report_receiver1_phone"/>
        <result property="reportReceiver2UserId" column="report_receiver2_user_id"/>
        <result property="reportReceiver2Name" column="report_receiver2_name"/>
        <result property="reportReceiver2Position" column="report_receiver2_position"/>
        <result property="reportReceiver2Cert" column="report_receiver2_cert"/>
        <result property="reportReceiver2Id" column="report_receiver2_id"/>
        <result property="reportReceiver2Phone" column="report_receiver2_phone"/>
        <result property="reportSenderUserId" column="report_sender_user_id"/>
        <result property="reportSentTime" column="report_sent_time"/>
        <result property="reportReceiver1Signature" column="report_receiver1_signature"/>
        <result property="reportReceiver2Signature" column="report_receiver2_signature"/>
        <result property="sampleReceiveMode" column="sample_receive_mode"/>
        <result property="sampleReceiver1UserId" column="sample_receiver1_user_id"/>
        <result property="sampleReceiver1Name" column="sample_receiver1_name"/>
        <result property="sampleReceiver1Position" column="sample_receiver1_position"/>
        <result property="sampleReceiver1Cret" column="sample_receiver1_cret"/>
        <result property="sampleReceiver1Id" column="sample_receiver1_id"/>
        <result property="sampleReceiver1Phone" column="sample_receiver1_phone"/>
        <result property="sampleReceiver2UserId" column="sample_receiver2_user_id"/>
        <result property="sampleReceiver2Name" column="sample_receiver2_name"/>
        <result property="sampleReceiver2Position" column="sample_receiver2_position"/>
        <result property="sampleReceiver2Cret" column="sample_receiver2_cret"/>
        <result property="sampleReceiver2Id" column="sample_receiver2_id"/>
        <result property="sampleReceiver2Phone" column="sample_receiver2_phone"/>
        <result property="sampleSenderUserId" column="sample_sender_user_id"/>
        <result property="sampleSentTime" column="sample_sent_time"/>
        <result property="sampleReceiver1PersonSignature" column="sample_receiver1_person_signature"/>
        <result property="sampleReceiver2PersonSignnature" column="sample_receiver2_person_signnature"/>
        <result property="status" column="status"/>
        <result property="previousStatus" column="previous_status"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="returnOrNot" column="return_or_not"/>
        <result property="materialImageFlag" column="material_image_flag"/>
        <result property="pushFlag" column="push_flag"/>
    </resultMap>

    <resultMap id="entrustmentVO" type="digital.laboratory.platform.entrustment.vo.EntrustmentVO"
               extends="entrustmentMap">
        <result property="caseName" column="case_name"/>
        <result property="caseNo" column="case_no"/>
        <result property="caseBrief" column="case_brief"/>
        <result property="happenTime" column="happen_time"/>
        <result property="submitterName" column="submitter_name"/>
        <result property="checkClaimUserName" column="check_claim_user_name"/>
        <result property="checkUserName" column="check_user_name"/>
        <result property="approveClaimUserName" column="approve_claim_user_name"/>
        <result property="approveUserName" column="approve_user_name"/>
        <result property="deliverConfirmUserName" column="deliver_confirm_user_name"/>
        <result property="deliverSubmitterName" column="deliver_submitter_name"/>
        <result property="acceptUserName" column="accept_user_name"/>

        <result property="createUserName" column="create_user_name"/>
        <result property="updateUserName" column="update_user_name"/>

        <result property="identificationMaterialCount" column="identification_material_count"/>

        <result property="identificationNo" column="identification_no"/>

    </resultMap>

    <sql id="BaseColumnList">
        e.id,
        e.case_id,
        e.entrustment_no,
        e.third_sys_entrust_no,
        e.entrustment_type,
        e.business_type,
        e.process_instance_id,
        e.identification_id,
        e.entrustment_time,
        e.submitter,
        e.submit_time,
        e.client_org_id,
        e.client_org_name,
        e.client_address,
        e.client_postcode,
        e.client_tel,
        e.client_fax,
        e.identification_domain,
        e.identification_org_name,
        e.qualitative_analysis,
        e.quantitative_analysis,
        e.other_identification_requests,
        e.candidate_drugs,
        e.is_client_specified_method,
        e.client_specified_method,
        e.destructive_analysis,
        e.other_identification_notes,
        e.reidentification,
        e.old_identification_org_name,
        e.old_identification_document,
        e.old_identification_result,
        e.reidentification_reason,
        e.avoid_identifier,
        e.specified_identifier,
        e.other_agreement,
        e.comments,
        e.check_candidate_user,
        e.check_claim_user,
        e.check_claim_time,
        e.check_user,
        e.check_time,
        e.check_comments,
        e.check_times,
        e.check_all_identification_material_confirmed,
        e.approve_candidate_user,
        e.approve_claim_user,
        e.approve_claim_time,
        e.approve_user,
        e.approve_time,
        e.approve_comments,
        e.approve_times,
        e.deliver_confirm_user,
        e.deliver_confirm_time,
        e.deliver_confirm_comments,
        e.deliver_time,
        e.deliver_submitter,
        e.deliverer1_name,
        e.deliverer1_position,
        e.deliverer1_cert,
        e.deliverer1_id,
        e.deliverer1_phone,
        e.deliverer2_name,
        e.deliverer2_position,
        e.deliverer2_cert,
        e.deliverer2_id,
        e.deliverer2_phone,
        e.accept_claim_user,
        e.accept_claim_time,
        e.accept_no,
        e.accept_user,
        e.accept_time,
        e.accept_comments,
        e.identification_items_confirm_printed,
        e.process_info,
        e.report_receive_mode,
        e.report_receiver1_user_id,
        e.report_receiver1_name,
        e.report_receiver1_position,
        e.report_receiver1_cert,
        e.report_receiver1_id,
        e.report_receiver1_phone,
        e.report_receiver2_user_id,
        e.report_receiver2_name,
        e.report_receiver2_position,
        e.report_receiver2_cert,
        e.report_receiver2_id,
        e.report_receiver2_phone,
        e.report_sender_user_id,
        e.report_sent_time,
        e.report_receiver1_signature,
        e.report_receiver2_signature,
        e.sample_receive_mode,
        e.sample_receiver1_user_id,
        e.sample_receiver1_name,
        e.sample_receiver1_position,
        e.sample_receiver1_cret,
        e.sample_receiver1_id,
        e.sample_receiver1_phone,
        e.sample_receiver2_user_id,
        e.sample_receiver2_name,
        e.sample_receiver2_position,
        e.sample_receiver2_cret,
        e.sample_receiver2_id,
        e.sample_receiver2_phone,
        e.sample_sender_user_id,
        e.sample_sent_time,
        e.sample_receiver1_person_signature,
        e.sample_receiver2_person_signnature,
        e.status,
        e.previous_status,
        e.create_time,
        e.create_by,
        e.update_time,
        e.update_by,
        e.entrustment_letter_path,
        e.identification_items_confirm_path,
        e.accept_need_professional,
        e.data_sources,
        e.accept_no_third_sys,
        e.entrust_requirement,
        e.post_address,
        e.return_or_not,
        e.is_trans,
        e.material_image_flag,
        e.push_flag,
        e.platform_flag
    </sql>

    <sql id="getEntrustmentVOSQL">

        SELECT
        <include refid="BaseColumnList"></include>,
        c.case_name,
        c.case_no,
        c.case_brief,
        c.happen_time,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.submitter
        ) AS submitter_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.check_claim_user
        ) AS check_claim_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.check_user
        ) AS check_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.approve_claim_user
        ) AS approve_claim_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.approve_user
        ) AS approve_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.deliver_confirm_user
        ) AS deliver_confirm_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.deliver_submitter
        ) AS deliver_submitter_name,
        -- (
        -- SELECT user.name
        -- FROM dlp_base.sys_user user
        -- WHERE user.user_id=e.accept_claim_user
        -- ) AS accept_claim_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.accept_user
        ) AS accept_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.report_sender_user_id
        ) AS report_sender_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.sample_sender_user_id
        ) AS sample_sender_user_name,

        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.create_by
        ) AS create_user_name,
        (
        SELECT user.name
        FROM dlp_base.sys_user user
        WHERE user.user_id=e.update_by
        ) AS update_user_name,

        (
        SELECT COUNT(*)
        FROM b_entrustment_identification_material eim
        WHERE eim.entrustment_id=e.id
        ) AS identification_material_count,

        (
        select identification_no
        from b_identification i
        where i.id = e.identification_id
        ) as identification_no,
        (
        SELECT COUNT(*) FROM b_entrust_material_checkout_result emr WHERE emr.entrust_id = e.id
        ) AS checkout_result_count

        FROM b_entrustment e
        LEFT JOIN b_case_event c on e.case_id=c.id

    </sql>

    <!-- 取委托列表分页 -->
    <select id="getEntrustmentVOPage" resultMap="entrustmentVO"
            resultType="digital.laboratory.platform.entrustment.vo.EntrustmentVO">
        <include refid="getEntrustmentVOSQL"/>
        ${ew.customSqlSegment}
    </select>

    <!-- 取委托列表 -->
    <select id="getEntrustmentVOList" resultMap="entrustmentVO"
            resultType="digital.laboratory.platform.entrustment.vo.EntrustmentVO">
        <include refid="getEntrustmentVOSQL"/>
        ${ew.customSqlSegment}
    </select>

    <!-- 取指定 Id 的委托 -->
    <select id="getEntrustmentVOById" resultMap="entrustmentVO"
            resultType="digital.laboratory.platform.entrustment.vo.EntrustmentVO">
        <include refid="getEntrustmentVOSQL"/>
        where e.id=#{id}
    </select>

    <!-- 取最大委托号, 目的是为了生成新的委托号 -->
    <select id="getMaxEntrustmentNo" parameterType="String" resultType="java.lang.String">
        SELECT entrustment_no
        FROM b_entrustment
        WHERE entrustment_no LIKE CONCAT(#{prefix}, '%')
        ORDER BY CAST(SUBSTRING_INDEX(entrustment_no, '-', -1) AS UNSIGNED) DESC LIMIT 1
    </select>
    <select id="getMaxEntrustmentNoNew" parameterType="String" resultType="java.lang.String">
        SELECT entrustment_no
        FROM b_entrustment
        WHERE entrustment_no LIKE CONCAT(#{prefix}, '%')
        ORDER BY CAST(SUBSTRING_INDEX(entrustment_no, '-', -1) AS UNSIGNED) DESC LIMIT 1
    </select>
    <select id="getMaxEntrustAcceptNo" parameterType="String" resultType="java.lang.String">
        SELECT MAX(CAST(SUBSTRING(accept_no, 6) AS UNSIGNED)) AS maxno
        FROM b_entrustment
        WHERE accept_no LIKE concat(#{prefix}, '%')
    </select>

    <!-- 通过前端提供的字典ID 获取对应的检材类别 -->

    <select id="getTypeForDictionary" resultType="String">
        SELECT d.label
        from dlp_base.sys_dictionary d
        where d.id = #{id}
    </select>

    <!--  根据检材结果获取委托列表  -->
    <select id="getEntrustmentVOListByResult" resultMap="entrustmentVO">
        <include refid="getEntrustmentVOSQL"/>
        LEFT JOIN b_entrust_material_checkout_result emr ON e.id = emr.entrustment_id
    </select>
</mapper>
