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

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkInvoiceReport">
        <id property="id" column="ID" jdbcType="VARCHAR"/>
        <result property="reportName" column="report_name" jdbcType="VARCHAR"/>
        <result property="reportCode" column="report_code" jdbcType="VARCHAR"/>
        <result property="reportType" column="report_type" jdbcType="INTEGER"/>
        <result property="dateRangeStart" column="date_range_start" jdbcType="TIMESTAMP"/>
        <result property="dateRangeEnd" column="date_range_end" jdbcType="TIMESTAMP"/>
        <result property="createDept" column="create_dept" jdbcType="VARCHAR"/>
        <result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
        <result property="createBy" column="CREATE_BY" jdbcType="BIGINT"/>
        <result property="createTime" column="CREATE_TIME" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="UPDATE_BY" jdbcType="BIGINT"/>
        <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,report_name,report_code,
        report_type,date_range_start,date_range_end,
        create_dept,tenant_id,CREATE_BY,
        CREATE_TIME,UPDATE_BY,UPDATE_TIME
    </sql>
    <select id="queryPageList" resultType="org.dromara.zjk.domain.vo.ZjkInvoiceReportVO">
        SELECT id,
               report_name,
               report_code,
               case when report_type = 0 then '月报表' else '年报表' end as report_type,
               case when report_type = 0 then '每月' else '每年' end     as typeDesc,
               TO_CHAR(date_range_start, 'YYYY/MM/DD') || '~' || TO_CHAR(date_range_end, 'YYYY/MM/DD')
                                                                         AS date_range
        FROM zjk_invoice_report order by  "CREATE_TIME" desc
    </select>
    <select id="getInfoList" resultType="org.dromara.zjk.domain.vo.FpReportInVO">
        select pp.* from (
        SELECT
        emi.settlement_date,
        emi.product_name AS productName,
        CASE
        WHEN emi.product_type = '1' THEN '立项'
        WHEN emi.product_type = '2' THEN '招标'
        WHEN emi.product_type = '3' THEN '初验'
        WHEN emi.product_type = '4' THEN '终验'
        ELSE emi.product_type
        END AS reviewSn,
        emi.expert_name AS expertName,
        emi.settlement_sn AS settlementSn,
        emi.expert_money AS settlementMoney,
        CASE
        WHEN COUNT(inv.id) > 0 THEN '已开票'
        ELSE '未开票'
        END AS invStu,
        COALESCE(SUM(inv.invoice_money), 0) AS invoiceMoney
        FROM
        zjk_expert_money_info emi
        LEFT JOIN
        zjk_invoice inv ON emi.money_id = inv.settle_id
        WHERE
        --已结算且已支付
        emi.status = 1 and emi.expert_money_flag=2
        <if test="dto.start != null and dto.end != null">
            and emi.settlement_date BETWEEN #{dto.start} AND #{dto.end}
        </if>
        <if test="dto.productName != null and dto.productName != ''">
            and emi.product_name = #{dto.productName}
        </if>
        <if test="dto.reviewSn != null and dto.reviewSn != ''">
            and emi.product_type = #{dto.reviewSn}
        </if>
        <if test="dto.expertName != null and dto.expertName != ''">
            and emi.expert_name = #{dto.expertName}
        </if>

        GROUP BY
        emi.product_name,
        emi.product_type,
        emi.expert_name,
        emi.settlement_sn,
        emi.expert_money,
        emi.settlement_date ) pp
        where 1=1
        <if test="dto.invStu != null and dto.invStu != '' and dto.invStu == 1">
            and pp.invStu='已开票'
        </if>
        <if test="dto.invStu != null and dto.invStu != '' and dto.invStu == 0">
            and pp.invStu='未开票'
        </if>
        order by pp.settlement_date desc
    </select>
    <select id="queryList" resultType="org.dromara.zjk.domain.vo.FpReportInVO">
        select pp.* from (
        SELECT
        emi.settlement_date,
        emi.product_name AS productName,
        CASE
        WHEN emi.product_type = '1' THEN '立项'
        WHEN emi.product_type = '2' THEN '招标'
        WHEN emi.product_type = '3' THEN '初验'
        WHEN emi.product_type = '4' THEN '终验'
        ELSE emi.product_type
        END AS reviewSn,
        emi.expert_name AS expertName,
        emi.settlement_sn AS settlementSn,
        emi.expert_money AS settlementMoney,
        CASE
        WHEN COUNT(inv.id) > 0 THEN '已开票'
        ELSE '未开票'
        END AS invStu,
        COALESCE(SUM(inv.invoice_money), 0) AS invoiceMoney
        FROM
        zjk_expert_money_info emi
        LEFT JOIN
        zjk_invoice inv ON emi.money_id = inv.settle_id
        WHERE
        --已结算且已支付
        emi.status = 1 and emi.expert_money_flag=2
        <if test="dto.start != null and dto.end != null">
            and emi.settlement_date BETWEEN #{dto.start} AND #{dto.end}
        </if>
        <if test="dto.productName != null and dto.productName != ''">
            and emi.product_name = #{dto.productName}
        </if>
        <if test="dto.reviewSn != null and dto.reviewSn != ''">
            and emi.product_type = #{dto.reviewSn}
        </if>
        <if test="dto.expertName != null and dto.expertName != ''">
            and emi.expert_name = #{dto.expertName}
        </if>
        GROUP BY
        emi.product_name,
        emi.product_type,
        emi.expert_name,
        emi.settlement_sn,
        emi.expert_money,
        emi.settlement_date ) pp
        where 1=1
        <if test="dto.invStu != null and dto.invStu != '' and dto.invStu == 1">
            and pp.invStu='已开票'
        </if>
        <if test="dto.invStu != null and dto.invStu != '' and dto.invStu == 0">
            and pp.invStu='未开票'
        </if>
        order by pp.settlement_date desc
    </select>
    <select id="getTenantIds" resultType="java.lang.String">
        select   tenant_id from sys_tenant where status=0 and del_flag=0 and domain is not null
    </select>
</mapper>
