<?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.yifu.cloud.plus.v1.yifu.insurances.mapper.insurances.TInsuranceTypeMapper">

    <resultMap id="BaseResultMap" type="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceType">
            <id property="id" column="ID" jdbcType="VARCHAR"/>
            <result property="name" column="NAME" jdbcType="VARCHAR"/>
            <result property="insuranceCompanyId" column="INSURANCE_COMPANY_ID" jdbcType="VARCHAR"/>
            <result property="receiptUnitName" column="RECEIPT_UNIT_NAME" jdbcType="VARCHAR"/>
            <result property="bankName" column="BANK_NAME" jdbcType="VARCHAR"/>
            <result property="bankNo" column="BANK_NO" jdbcType="VARCHAR"/>
            <result property="deleteFlag" column="DELETE_FLAG" jdbcType="TINYINT"/>
            <result property="createBy" column="CREATE_BY" jdbcType="VARCHAR"/>
            <result property="createName" column="CREATE_NAME" jdbcType="VARCHAR"/>
            <result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
            <result property="updateBy" column="UPDATE_BY" jdbcType="VARCHAR"/>
            <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
			<result property="isAdress" column="IS_ADRESS" jdbcType="VARCHAR"/>
			<result property="enable" column="ENABLE" jdbcType="VARCHAR"/>
			<result property="isJfcd" column="IS_JFCD" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,NAME,INSURANCE_COMPANY_ID,
        RECEIPT_UNIT_NAME,BANK_NAME,BANK_NO,
        DELETE_FLAG,CREATE_BY,CREATE_NAME,
        CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_ADRESS,ENABLE
    </sql>
	<select id="selectInsuranceTypeRateList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceTypeRate">
		select
			ID as id,
			INSURANCE_TYPE_ID as insuranceTypeId,
			MONTH as month,
			RATE as rate
		from
			t_insurance_type_rate
		where
			INSURANCE_TYPE_ID = #{id}
		and
			DELETE_FLAG = 0
		ORDER BY CREATE_TIME DESC
	</select>

	<select id="selectInsuranceTypeStandardList" resultType="com.yifu.cloud.plus.v1.yifu.insurances.entity.TInsuranceTypeStandard">
		select
			ID as id,
			INSURANCE_TYPE_ID as insuranceTypeId,
			BUY_STANDARD as buyStandard,
			MEDICAL_QUOTA as medicalQuota,
			DIE_DISABLE_QUOTA as dieDisableQuota,
		    ENABLE as enable
		from
			t_insurance_type_standard
		where
			INSURANCE_TYPE_ID = #{id}
		  and
			DELETE_FLAG = 0
		ORDER BY CREATE_TIME DESC
	</select>
	<select id="selectInsuranceTypePage" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceTypeVo">
		select
			a.ID  as id,
			a.NAME  as name ,
			b.COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_COMPANY_ID as insuranceCompanyId,
			a.RECEIPT_UNIT_NAME as receiptUnitName,
			a.BANK_NAME as bankName,
			a.BANK_NO as bankNo,
			a.AGE_LIMIT as ageLimit,
			a.IS_ADRESS as isAdress,a.ENABLE as enable,a.IS_JFCD as isJfcd
		from
			t_insurance_type a,
			t_insurance_company b
		where
			a.INSURANCE_COMPANY_ID = b.ID
		  and
			a.DELETE_FLAG = 0
		<if test="insuranceType.insuranceCompanyName != null and insuranceType.insuranceCompanyName.trim() != ''">
			and b.COMPANY_NAME like concat('%', #{insuranceType.insuranceCompanyName}, '%')
		</if>
		<if test="insuranceType.insuranceCompanyId != null and insuranceType.insuranceCompanyId.trim() != ''">
			and a.INSURANCE_COMPANY_ID = #{insuranceType.insuranceCompanyId}
		</if>
		<if test="insuranceType.name != null and insuranceType.name.trim() != ''">
			and a.NAME like concat('%',replace(replace(#{insuranceType.name},'_','\_'),'%','\%'),'%')
		</if>
		<if test="insuranceType.isAdress != null and insuranceType.isAdress.trim() != ''">
			and a.IS_ADRESS = #{insuranceType.isAdress}
		</if>
		<if test="insuranceType.enable != null and insuranceType.enable.trim() != ''">
			and a.ENABLE = #{insuranceType.enable}
		</if>
		<if test="insuranceType.isJfcd != null and insuranceType.isJfcd.trim() != ''">
			and a.IS_JFCD = #{insuranceType.isJfcd}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>

	<select id="selectInsuranceType" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceTypeExportVo">
		select
		a.NAME  as name ,
		b.COMPANY_NAME as insuranceCompanyName,
		a.BANK_NAME as bankName,
		a.BANK_NO as bankNo,
		a.RECEIPT_UNIT_NAME as receiptUnitName,
		if(a.AGE_LIMIT = '0','限制（16-70岁）','无') as ageLimit,
		if(a.IS_ADRESS = '0','是',if(ifnull(a.IS_ADRESS,3) = 3,'','否')) as isAdress,a.ENABLE as enable,a.IS_JFCD as isJfcd
		from
		t_insurance_type a,
		t_insurance_company b
		where
		a.INSURANCE_COMPANY_ID = b.ID
		and
		a.DELETE_FLAG = 0
		<if test="insuranceType.insuranceCompanyName != null and insuranceType.insuranceCompanyName.trim() != ''">
			and b.COMPANY_NAME = #{insuranceType.insuranceCompanyName}
		</if>
		<if test="insuranceType.name != null and insuranceType.name.trim() != ''">
			and a.NAME like concat('%',replace(replace(#{insuranceType.name},'_','\_'),'%','\%'),'%')
		</if>
		<if test="insuranceType.isAdress != null and insuranceType.isAdress.trim() != ''">
			and a.IS_ADRESS = #{insuranceType.isAdress}
		</if>
		<if test="insuranceType.enable != null and insuranceType.enable.trim() != ''">
			and a.ENABLE = #{insuranceType.enable}
		</if>
		<if test="insuranceType.isJfcd != null and insuranceType.isJfcd.trim() != ''">
			and a.IS_JFCD = #{insuranceType.isJfcd}
		</if>
		ORDER BY a.CREATE_TIME DESC
	</select>


    <select id="getInsuranceTypeDetailById" resultType="com.yifu.cloud.plus.v1.yifu.insurances.vo.InsuranceTypeVo">
		select
			a.ID  as id,
			a.NAME  as name ,
			b.COMPANY_NAME as insuranceCompanyName,
			a.INSURANCE_COMPANY_ID as insuranceCompanyId,
			a.RECEIPT_UNIT_NAME as receiptUnitName,
			a.BANK_NAME as bankName,
			a.BANK_NO as bankNo,
			a.AGE_LIMIT as ageLimit,
			a.IS_ADRESS as isAdress,a.ENABLE as enable,a.IS_JFCD as isJfcd
		from
			t_insurance_type a,
			t_insurance_company b
		where
			a.INSURANCE_COMPANY_ID = b.ID
		  and
		    a.ID = #{id}
		  and
			a.DELETE_FLAG = 0
	</select>

</mapper>
