<?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.dromara.zjk.mapper.ZjkExpertMoneyInfoMapper">

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkExpertMoneyInfo">
            <id property="moneyId" column="money_id" jdbcType="BIGINT"/>
            <result property="expertType" column="expert_type" jdbcType="VARCHAR"/>
            <result property="expertId" column="expert_id" jdbcType="VARCHAR"/>
            <result property="expertMoney" column="expert_money" jdbcType="BIGINT"/>
            <result property="expertName" column="expert_name" jdbcType="VARCHAR"/>
            <result property="expertUnit" column="expert_unit" jdbcType="VARCHAR"/>
            <result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
            <result property="assignId" column="ASSIGN_ID" jdbcType="OTHER"/>
            <result property="createDept" column="create_dept" jdbcType="BIGINT"/>
            <result property="createBy" column="create_by" jdbcType="BIGINT"/>
            <result property="createTime" column="create_time" jdbcType="DATE"/>
            <result property="updateBy" column="update_by" jdbcType="BIGINT"/>
            <result property="updateTime" column="update_time" jdbcType="DATE"/>
            <result property="remark" column="remark" jdbcType="VARCHAR"/>
            <result property="expertMoneyFlag" column="expert_money_flag" jdbcType="VARCHAR"/>
            <result property="expertMoneyFile" column="expert_money_file" jdbcType="VARCHAR"/>
            <result property="productId" column="product_id" jdbcType="VARCHAR"/>
            <result property="productType" column="product_type" jdbcType="VARCHAR"/>
            <result property="productName" column="product_name" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        money_id,expert_type,expert_id,
        expert_money,expert_name,expert_unit,
        tenant_id,ASSIGN_ID,create_dept,
        create_by,create_time,update_by,
        update_time,remark,expert_money_flag,
        expert_money_file,product_id,product_type,
        product_name
    </sql>
    <select id="selectMoneyInfoList" resultType="org.dromara.zjk.domain.vo.ZjkExpertMoneyInfoVO">
        select zemi.*
        from zjk_expert_money_info zemi
        where zemi."ASSIGN_ID" = 0
        <if test="bo.expertIdcard != null and bo.expertIdcard != '' ">
            and bo.id_card like '%' || #{bo.expertIdcard} ||'%'
        </if>
        <if test="bo.expertContact != null and bo.expertContact != '' ">
            and bo.contact like '%' || #{bo.expertContact} || '%'
        </if>
        <if test="bo.expertMoneyFlag != null and bo.expertMoneyFlag != '' ">
            and zemi.expert_money_flag = #{bo.expertMoneyFlag}
        </if>
        <if test="bo.payMethod != null and bo.payMethod != '' ">
            and zemi.pay_method like '%' || #{bo.payMethod} || '%'
        </if>
        <if test=" bo.status != null ">
            and zemi.status = #{bo.status}
        </if>
    </select>
    <select id="selectListByMonth" resultType="org.dromara.zjk.domain.vo.ZjkExpertMoneyInfoVO">
        select zemi.*,ze.expert_name,zemc.money_type,zemc.money_cycle
        from zjk_expert_money_info zemi
        inner join zjk_expert ze on ze.expert_id = zemi.expert_id
        inner join zjk_expert_money_config zemc on ze.position_title_level = zemc.money_id
        where zemi."ASSIGN_ID" = 0 and ze.delete_flag = 0 and zemi.expert_money is NOT null and zemi.tenant_id = #{tenantId}
        <if test="startTime != null">
            and zemi.review_date >= #{startTime}
        </if>
        <if test="endTime != null ">
            and zemi.review_date &lt;= #{endTime}
        </if>
    </select>
    <select id="selectTenantIds" resultType="java.lang.String">
        select tenant_id
        from zjk_expert_money_info
        group by tenant_id
    </select>
    <select id="getExpertList" resultType="org.dromara.zjk.domain.vo.ZjkExpertVo">
        select DISTINCT ze.expert_id,
               ze.expert_name,
               ze.contact,
               ze.work_unit
        from zjk_expert_money_info zem
                 left join zjk_expert ze ON zem.expert_id = ze.expert_id
        where zem.status = 1
          --取支付或支付的专家进行开票
          and zem.expert_money_flag != 1
          and zem.product_id = #{productId}
          and zem.product_type =#{reviewSn}
    </select>
</mapper>
