<?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.ZjkInvoiceMapper">


    <select id="queryPageList" resultType="org.dromara.zjk.domain.vo.ZjkInvoiceVo">
        SELECT
        zi.ID,
        zi.invoice_number,
        zi.invoice_money,
        zi.start_date,
        ze.expert_name as expert_name,
        zi.expert_phone,
        zi.expert_id_number,
        zi.check_status,
        zemc.money_type as expertTitleLevel,
        zi.invoice_attached,
        so.original_name AS invoiceAttachedName,
        so.url AS invoiceAttachedAddr
        FROM
        zjk_invoice zi
            left join zjk_expert ze ON ze.expert_id = zi.expert_name
        LEFT JOIN sys_oss so ON so.oss_id = zi.invoice_attached
        LEFT JOIN zjk_expert_money_config zemc ON zemc.money_id = zi.expert_title_level and zemc.ASSIGN_ID =0 <!--逻辑删除标志,0为正常 -->
        <where>
            <if test="bo.expertName != null and  bo.expertName != ''">
                and zi.expert_name like '%' || #{bo.expertName} || '%'
            </if>
            <if test="bo.expertPhone != null and  bo.expertPhone != ''">
                and zi.expert_phone like '%' || #{bo.expertPhone} || '%'
            </if>
            <if test="bo.expertIdNumber != null and  bo.expertIdNumber != ''">
                and zi.expert_id_number like '%' || #{bo.expertIdNumber} || '%'
            </if>
            <if test="bo.expertTitleLevel != null and  bo.expertTitleLevel != ''">
                and zi.expert_title_level like '%' || #{bo.expertTitleLevel} || '%'
            </if>
        </where>
    </select>


    <select id="selectInfoById" resultType="org.dromara.zjk.domain.vo.ZjkInvoiceVo"
            parameterType="java.lang.Long">
        SELECT
            zi.*,
            zemc.money_type as expertTitleLevel,
            so.original_name AS invoiceAttachedName,
            so.url AS invoiceAttachedAddr

        FROM
            zjk_invoice zi
        LEFT JOIN sys_oss so ON so.oss_id = zi.invoice_attached
        LEFT JOIN zjk_expert_money_config zemc ON zemc.money_id = zi.expert_title_level AND zemc.ASSIGN_ID =0 <!--逻辑删除标志,0为正常 -->
        WHERE zi.id = #{id}
    </select>

    <select id="selectTotalMoney" resultType="java.math.BigDecimal">
        SELECT
            SUM(invoice_money) as totalMoney
        FROM
            zjk_invoice
        WHERE invoice_money IS NOT NULL
    </select>
</mapper>
