<?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="com.yd.dal.mapper.agms.AgmsPractitionerMapper">

    <select id="practitionerFileSharingList" resultType="com.yd.api.agms.vo.practitioner.PractitionerFileSharing">
        SELECT  s.id id,
                s.md_drop_option_id mdDropOptionId,
                s.file_content fileContent,
                s.is_active isActive,
                date_format(s.created_at, '%Y-%m-%d %H:%i:%s')createdAt,
                s.created_by createdBy,
                uc.name createdName,
                date_format(s.updated_at, '%Y-%m-%d %H:%i:%s') updatedAt,
                s.updated_by updatedBy,
                uu.name updatedName,
                s.title title,
                s.digest digest,
                s.author author,
                s.cover_url coverUrl,
                count(ost.id) totalNum
        <if test="practitionerId != null">
            ,(SELECT count(aost.id)
                    FROM ag_acl_customer_object_share_track aost
                WHERE aost.share_id IN (
                    SELECT aos.id
                        FROM ag_acl_customer_object_share aos
                    WHERE aos.item_id = s.id
                        AND aos.item_type = 1
                        AND aos.practitioner_id = #{practitionerId,jdbcType=BIGINT}
                )) num
        </if>
        FROM ag_acl_practitioner_file_sharing s
                LEFT JOIN  ag_md_drop_options o on o.id = s.md_drop_option_id
                LEFT JOIN  ag_acl_user uc on uc.id = s.created_by
                LEFT JOIN  ag_acl_user uu on uu.id = s.created_by
                LEFT JOIN ag_acl_customer_object_share os
                    LEFT JOIN ag_acl_customer_object_share_track ost ON ost.share_id = os.id
                    ON os.item_id = s.id
            <where>
                <if test="mdDropOptionIds != null">
                    <foreach collection="mdDropOptionIds" item="mdDropOptionId" index="index" open="(" close=")" separator="or">
                        find_in_set (#{mdDropOptionId,jdbcType=BIGINT},s.md_drop_option_id)
                    </foreach>
                </if>
                <if test="id != null">
                    AND s.id = #{id,jdbcType=BIGINT}
                </if>
                <if test="shareCode != null">
                    AND os.item_type = 1
                    AND os.share_code = #{shareCode,jdbcType=VARCHAR}
                </if>
                <if test="isActive != null">
                    AND s.is_active = #{isActive,jdbcType=INTEGER}
                </if>
            </where>
        GROUP BY s.id
        ORDER BY s.id DESC
    </select>
    <resultMap id="BaseResultMap" type="com.yd.api.agms.vo.practitioner.SharePractitionerInfo">
        <result column="customerId" jdbcType="BIGINT" property="customerId" />
        <result column="practitionerId" jdbcType="BIGINT" property="practitionerId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="mobileNo" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="insurerBranchName" jdbcType="VARCHAR" property="insurerBranchName" />
        <result column="headImagePath" jdbcType="VARCHAR" property="headImagePath" />
        <result column="qrCodePath" jdbcType="VARCHAR" property="qrCodePath" />
    </resultMap>
    <select id="findSharePractitioner" resultMap="BaseResultMap">
        SELECT p.customer_id customerId,
               p.id practitionerId,
               p.name name,
               p.mobile_no mobileNo,
               b.branch_name insurerBranchName,
               uh.file_path headImagePath,
               uw.file_path qrCodePath
        FROM ag_acl_customer_object_share os
                 LEFT JOIN ag_acl_practitioner p ON p.id = os.practitioner_id
                 LEFT JOIN ag_acl_insurer_branch b ON b.id = p.insurer_branch_id
                 LEFT JOIN ag_acl_file_upload uh ON uh.target_type = 1 AND uh.target_id = p.id AND uh.is_active = 1 AND uh.target_use_for = 1
                 LEFT JOIN ag_acl_file_upload uw ON uw.target_type = 1 AND uw.target_id = p.id AND uw.is_active = 1 AND uw.target_use_for = 9
        WHERE os.share_code = #{shareCode,jdbcType=VARCHAR}
    </select>
</mapper>