<?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.meta.MdDropOptionsMapper">
  <resultMap id="BaseResultMap" type="com.yd.dal.entity.meta.MdDropOptions">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="drop_master_id" jdbcType="BIGINT" property="dropMasterId" />
    <result column="drop_option_name" jdbcType="VARCHAR" property="dropOptionName" />
    <result column="drop_option_code" jdbcType="VARCHAR" property="dropOptionCode" />
    <result column="drop_option_order" jdbcType="INTEGER" property="dropOptionOrder" />
    <result column="drop_option_score" jdbcType="INTEGER" property="dropOptionScore" />
    <result column="drop_option_remark" jdbcType="VARCHAR" property="dropOptionRemark" />
    <result column="is_active" jdbcType="INTEGER" property="isActive" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="created_by" jdbcType="BIGINT" property="createdBy" />
  </resultMap>
  <sql id="Base_Column_List">
    id, drop_master_id, drop_option_name, drop_option_code, drop_option_order, drop_option_score, 
    drop_option_remark, is_active, created_at, created_by
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from ag_md_drop_options
    where id = #{id,jdbcType=BIGINT}
  </select>

    <select id="findByMasterCode" resultType="com.yd.dal.entity.meta.MdDropOptions">
      select
      o.id id,
      o.drop_master_id dropMasterId,
      o.drop_option_name dropOptionName,
      o.drop_option_code dropOptionCode,
      o.drop_option_order dropOptionOrder,
      o.drop_option_score dropOptionScore,
      o.drop_option_remark dropOptionRemark,
      o.is_active isActive,
      o.created_at createdAt,
      o.created_by createdBy
      from ag_md_drop_master m left join ag_md_drop_options o on m.id = o.drop_master_id and o.is_active = 1
      where m.scenario_code = #{masterCode,jdbcType=BIGINT}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from ag_md_drop_options
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.meta.MdDropOptions" useGeneratedKeys="true">
    insert into ag_md_drop_options (drop_master_id, drop_option_name, drop_option_code, 
      drop_option_order, drop_option_score, drop_option_remark, 
      is_active, created_at, created_by
      )
    values (#{dropMasterId,jdbcType=BIGINT}, #{dropOptionName,jdbcType=VARCHAR}, #{dropOptionCode,jdbcType=VARCHAR}, 
      #{dropOptionOrder,jdbcType=INTEGER}, #{dropOptionScore,jdbcType=INTEGER}, #{dropOptionRemark,jdbcType=VARCHAR}, 
      #{isActive,jdbcType=INTEGER}, #{createdAt,jdbcType=TIMESTAMP}, #{createdBy,jdbcType=BIGINT}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.meta.MdDropOptions" useGeneratedKeys="true">
    insert into ag_md_drop_options
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="dropMasterId != null">
        drop_master_id,
      </if>
      <if test="dropOptionName != null">
        drop_option_name,
      </if>
      <if test="dropOptionCode != null">
        drop_option_code,
      </if>
      <if test="dropOptionOrder != null">
        drop_option_order,
      </if>
      <if test="dropOptionScore != null">
        drop_option_score,
      </if>
      <if test="dropOptionRemark != null">
        drop_option_remark,
      </if>
      <if test="isActive != null">
        is_active,
      </if>
      <if test="createdAt != null">
        created_at,
      </if>
      <if test="createdBy != null">
        created_by,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="dropMasterId != null">
        #{dropMasterId,jdbcType=BIGINT},
      </if>
      <if test="dropOptionName != null">
        #{dropOptionName,jdbcType=VARCHAR},
      </if>
      <if test="dropOptionCode != null">
        #{dropOptionCode,jdbcType=VARCHAR},
      </if>
      <if test="dropOptionOrder != null">
        #{dropOptionOrder,jdbcType=INTEGER},
      </if>
      <if test="dropOptionScore != null">
        #{dropOptionScore,jdbcType=INTEGER},
      </if>
      <if test="dropOptionRemark != null">
        #{dropOptionRemark,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        #{createdBy,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.meta.MdDropOptions">
    update ag_md_drop_options
    <set>
      <if test="dropMasterId != null">
        drop_master_id = #{dropMasterId,jdbcType=BIGINT},
      </if>
      <if test="dropOptionName != null">
        drop_option_name = #{dropOptionName,jdbcType=VARCHAR},
      </if>
      <if test="dropOptionCode != null">
        drop_option_code = #{dropOptionCode,jdbcType=VARCHAR},
      </if>
      <if test="dropOptionOrder != null">
        drop_option_order = #{dropOptionOrder,jdbcType=INTEGER},
      </if>
      <if test="dropOptionScore != null">
        drop_option_score = #{dropOptionScore,jdbcType=INTEGER},
      </if>
      <if test="dropOptionRemark != null">
        drop_option_remark = #{dropOptionRemark,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        is_active = #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        created_at = #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        created_by = #{createdBy,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.meta.MdDropOptions">
    update ag_md_drop_options
    set drop_master_id = #{dropMasterId,jdbcType=BIGINT},
      drop_option_name = #{dropOptionName,jdbcType=VARCHAR},
      drop_option_code = #{dropOptionCode,jdbcType=VARCHAR},
      drop_option_order = #{dropOptionOrder,jdbcType=INTEGER},
      drop_option_score = #{dropOptionScore,jdbcType=INTEGER},
      drop_option_remark = #{dropOptionRemark,jdbcType=VARCHAR},
      is_active = #{isActive,jdbcType=INTEGER},
      created_at = #{createdAt,jdbcType=TIMESTAMP},
      created_by = #{createdBy,jdbcType=BIGINT}
    where id = #{id,jdbcType=BIGINT}
  </update>
    <select id="selectByObj" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_md_drop_options
      <where>
        <if test="id != null">
          id = #{id,jdbcType=BIGINT}
        </if>
        <if test="dropMasterId != null">
          and drop_master_id = #{dropMasterId,jdbcType=BIGINT}
        </if>
        <if test="dropOptionName != null">
          and drop_option_name = #{dropOptionName,jdbcType=VARCHAR}
        </if>
        <if test="dropOptionCode != null">
          and drop_option_code = #{dropOptionCode,jdbcType=VARCHAR}
        </if>
        <if test="dropOptionOrder != null">
          and drop_option_order = #{dropOptionOrder,jdbcType=INTEGER}
        </if>
        <if test="dropOptionScore != null">
          and drop_option_score = #{dropOptionScore,jdbcType=INTEGER}
        </if>
        <if test="dropOptionRemark != null">
          and drop_option_remark = #{dropOptionRemark,jdbcType=VARCHAR}
        </if>
        <if test="isActive != null">
          and is_active = #{isActive,jdbcType=INTEGER}
        </if>
        <if test="createdAt != null">
          and created_at = #{createdAt,jdbcType=TIMESTAMP}
        </if>
        <if test="createdBy != null">
          and created_by = #{createdBy,jdbcType=BIGINT}
        </if>
      </where>
    </select>
    <select id="findByDropMasterCode" resultMap="BaseResultMap">
        SELECT o.*
        FROM ag_md_drop_options o
         LEFT JOIN ag_md_drop_master m ON m.id = o.drop_master_id
        WHERE m.scenario_code LIKE #{dropMasterCode,jdbcType=VARCHAR}
    </select>
    <select id="findByMasterCodeAndOrderId" resultType="com.yd.dal.entity.meta.MdDropOptions">
      select
        o.id id,
        o.drop_master_id dropMasterId,
        o.drop_option_name dropOptionName,
        o.drop_option_code dropOptionCode,
        o.drop_option_order dropOptionOrder,
        o.drop_option_score dropOptionScore,
        o.drop_option_remark dropOptionRemark,
        o.is_active isActive,
        o.created_at createdAt,
        o.created_by createdBy
      from ag_md_drop_master m left join ag_md_drop_options o on m.id = o.drop_master_id and o.is_active = 1 and o.drop_option_order = #{orderId,jdbcType=INTEGER}
      where m.scenario_code = #{masterCode,jdbcType=BIGINT}
    </select>
    <select id="selectByMasterCodeAndOptionsCode" resultMap="BaseResultMap">
      SELECT o.*
      FROM ag_md_drop_options o
      LEFT JOIN ag_md_drop_master m ON m.id = o.drop_master_id
      WHERE m.scenario_code = #{masterCode,jdbcType=VARCHAR,jdbcType=VARCHAR}
      and o.drop_option_code = #{optionsCode,jdbcType=VARCHAR}
      and o.is_active=1
      and m.is_active=1
    </select>
    <select id="findByMasterCodeAndOptionsCode" resultType="com.yd.dal.entity.meta.MdDropOptions">
      select
        o.id id,
        o.drop_master_id dropMasterId,
        o.drop_option_name dropOptionName,
        o.drop_option_code dropOptionCode,
        o.drop_option_order dropOptionOrder,
        o.drop_option_score dropOptionScore,
        o.drop_option_remark dropOptionRemark,
        o.is_active isActive,
        o.created_at createdAt,
        o.created_by createdBy
      from ag_md_drop_master m left join ag_md_drop_options o on m.id = o.drop_master_id and o.is_active = 1 and o.drop_option_code = #{optionsCode,jdbcType=VARCHAR}
      where m.scenario_code = #{masterCode,jdbcType=BIGINT}
    </select>
    <select id="findByMasterIdsE" resultType="com.yd.dal.entity.meta.OptionsEGolden">
      select
        t.DRP_OPT_ID as optId,
        t.FK_DRP_MST_ID as mstId,
        t.DRP_OPT_NAME as optName,
        t.DRP_OPT_ATTR as optAttr,
        t.DRP_OPT_ORDER as optOrder
      from drp002 t where t.FK_DRP_MST_ID in
      <foreach collection="materIds" index="index" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </select>
    <select id="findAllTitleEG" resultType="com.yd.dal.entity.meta.OptionsEGolden">
      select
        t.DRP_TITLE_CODE as titleCode,
        t.DRP_TITLE_NAME as titleName,
        t.DRP_TITLE_LEVEL as titleLevel
      from drp003 t
    </select>
    <select id="findSubordinateSystemInfoByIdEG"
            resultType="com.yd.api.practitioner.vo.organization.PractitionerSubordinateSystemInfo">
      select t.SPC_DIV_ID as idEG,
             t.FK_BUS_ID as deptIdEG,
             t.SPC_DIV_CODE as subordinateSystemCode,
             t.SPC_DIV_NAME as name,
             t.SPC_DIV_REMARK as remark
      from spc004 t where t.SPC_DIV_CANCEL = 0 and t.SPC_DIV_ID in
      <foreach collection="subordinateSystemIds" index="index" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </select>
  <select id="findSubordinateSystemInfoEG"
          resultType="com.yd.api.practitioner.vo.organization.PractitionerSubordinateSystemInfo">
    select t.SPC_DIV_ID as idEG,
           t.FK_BUS_ID as deptIdEG,
           t.SPC_DIV_CODE as subordinateSystemCode,
           t.SPC_DIV_NAME as name,
           t.SPC_DIV_REMARK as remark
    from spc004 t where t.SPC_DIV_CANCEL = 0;
  </select>
  <select id="findBranchDeptInfoByIdEG"
          resultType="com.yd.api.practitioner.vo.organization.InsurerBranchDeptInfo">
    select t.FK_AREA_ID as insurerBranchIdEG,
           t.SPC_BUS_ID as idEG,
           t.SPC_BUS_CODE as deptCode,
           t.SPC_BUS_NAME as name,
           t.SPC_BUS_TEL_NUM as contactNo,
           t.SPC_BUS_REMARK as remark,
           t.SPC_BUS_ADDRESS as address
    from spc002 t where t.SPC_BUS_CANCEL = 0 and t.SPC_BUS_ID in
    <foreach collection="branchDeptIds" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
  </select>

  <select id="findBranchDeptInfoEG" resultType="com.yd.api.practitioner.vo.organization.InsurerBranchDeptInfo">
    select t.FK_AREA_ID as insurerBranchIdEG,
           t.SPC_BUS_ID as idEG,
           t.SPC_BUS_CODE as deptCode,
           t.SPC_BUS_NAME as name,
           t.SPC_BUS_TEL_NUM as contactNo,
           t.SPC_BUS_REMARK as remark,
           t.SPC_BUS_ADDRESS as address
    from spc002 t where t.SPC_BUS_CANCEL = 0;
  </select>

  <select id="findInsurerBranchInfoByIdEG"
          resultType="com.yd.api.practitioner.vo.organization.InsurerBranchInfo">
    select
      t.SPC_AREA_ID as insurerBranchIdEG,
      t.SPC_AREA_NAME as branchName,
      t.SPC_AREA_CODE as branchCode,
      t.SPC_START_DATE as effectiveStartDate,
      t.SPC_END_DATE as effectiveEndDate,
      t.SPC_ADDRESS as address,
      t.SPC_TEL_NUM as contactNo
    from spc001 t where t.SPC_CANCEL = 0 and t.SPC_AREA_ID in
    <foreach collection="insurerBranchIds" index="index" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
  </select>

  <select id="findInsurerBranchInfoEG" resultType="com.yd.api.practitioner.vo.organization.InsurerBranchInfo">
    select
      t.SPC_AREA_ID as insurerBranchIdEG,
      t.SPC_AREA_NAME as branchName,
      t.SPC_AREA_CODE as branchCode,
      t.SPC_START_DATE as effectiveStartDate,
      t.SPC_END_DATE as effectiveEndDate,
      t.SPC_ADDRESS as address,
      t.SPC_TEL_NUM as contactNo
    from spc001 t where t.SPC_CANCEL = 0;
  </select>
    <select id="findByDropMasterCodes" resultType="com.yd.dal.entity.meta.MdDropOptions">
    select
      o.id id,
      o.drop_master_id dropMasterId,
      o.drop_option_name dropOptionName,
      o.drop_option_code dropOptionCode,
      o.drop_option_order dropOptionOrder,
      o.drop_option_score dropOptionScore,
      o.drop_option_remark dropOptionRemark,
      o.is_active isActive,
      o.created_at createdAt,
      o.created_by createdBy
      from ag_md_drop_master m left join ag_md_drop_options o on m.id = o.drop_master_id and o.is_active = 1
      where m.scenario_code in
      <foreach collection="masterCodeList" index="index" item="item" open="(" separator="," close=")">
        #{item}
      </foreach>
    </select>
</mapper>