<?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.jianshui.invoice.mapper.yuande.InvoiceFormMapper">

<!--两表联查-->
    <resultMap id="invoiceFromVoWithItem" type="com.jianshui.invoice.domain.dto.yuande.YuandeInvoiceFormDTO">
        <result property="customer__c" column="customer"></result>
        <result property="salesorderid__c" column="salesorderid"></result>
        <result property="fptt__c" column="fptt"></result>
        <result property="fkdwkhyh__c" column="fkdwkhyh"></result>
        <result property="fkdwyhzh__c" column="fkdwyhzh"></result>
        <result property="fkdwdz__c" column="fkdwdz"></result>
        <result property="fkdwdh__c" column="fkdwdh"></result>
        <result property="tyshxydm__c" column="tyshxydm"></result>
        <result property="kprq__c" column="kprq"></result>
        <result property="fphm__c" column="fphm"></result>
        <result property="field_91Rds__c" column="bz"></result>
        <result property="taxrate__c" column="taxrate"></result>
        <collection property="invoiceformitems" ofType="com.jianshui.invoice.domain.yuande.Invoiceformitem">
            <result property="quantity__c" column="quantity"></result>
            <result property="price__c" column="price"></result>
            <result property="field_E01xD__c" column="product"></result>
            <result property="unit__c" column="unit"></result>
        </collection>
    </resultMap>
    <select id="findInvoiceFormListWithInvoiceFromItem" resultMap="invoiceFromVoWithItem">
        select
        invoiceform.customer customer,
        invoiceform.salesorderid salesorderid,
        invoiceform.fptt fptt,
        invoiceform.fkdwkhyh fkdwkhyh,
        invoiceform.fkdwyhzh fkdwyhzh,
        invoiceform.fkdwdz fkdwdz,
        invoiceform.fkdwdh fkdwdh,
        invoiceform.tyshxydm tyshxydm,
        invoiceform.kprq kprq,
        invoiceform.fphm fphm,
        invoiceform.bz bz,
        invoiceform.taxrate taxrate,
        invoiceformitem.quantity quantity,
        invoiceformitem.price price,
        invoiceformitem.product product,
        invoiceformitem.unit unit
        from invoiceform  left join invoiceformitem
        on invoiceform.id = invoiceformitem.id
        <where>
            <if test="date1 != null || date1 != ''">
                and DATE(invoiceform.kprq) > #{date1}
            </if>
            <if test="date2 != null || date2 != ''">
                and #{date2} >= DATE(invoiceform.kprq)
            </if>
            and LENGTH(invoiceform.customer)>=3
            and invoiceform.tyshxydm is not null
        </where>
    </select>

<!--    单表查询开票信息表-->
    <resultMap id="invoiceFrom" type="com.jianshui.invoice.domain.dto.yuande.YuandeInvoiceFormDTO">
        <result property="customer__c" column="customer"></result>
        <result property="salesorderid__c" column="salesorderid"></result>
        <result property="fptt__c" column="fptt"></result>
        <result property="fkdwkhyh__c" column="fkdwkhyh"></result>
        <result property="fkdwyhzh__c" column="fkdwyhzh"></result>
        <result property="fkdwdz__c" column="fkdwdz"></result>
        <result property="fkdwdh__c" column="fkdwdh"></result>
        <result property="tyshxydm__c" column="tyshxydm"></result>
        <result property="kprq__c" column="kprq"></result>
        <result property="fphm__c" column="fphm"></result>
        <result property="field_91Rds__c" column="bz"></result>
        <result property="taxrate__c" column="taxrate"></result>
    </resultMap>
    <select id="findInvoiceFormList" resultMap="invoiceFrom">
        select
        customer,
        salesorderid,
        fptt,
        fkdwkhyh,
        fkdwyhzh,
        fkdwdz,
        fkdwdh,
        tyshxydm,
        kprq,
        fphm,
        bz,
        taxrate
        from invoiceform
        <where>
            <if test="date1 != null || date1 != ''">
                and DATE(invoiceform.kprq) > #{date1}
            </if>
            <if test="date2 != null || date2 != ''">
                and #{date2} >= DATE(invoiceform.kprq)
            </if>
            and LENGTH(invoiceform.customer)>=3
            and invoiceform.tyshxydm is not null
        </where>
    </select>
<!--    分页查询-->
    <select id="findInvoiceFormByPage" resultMap="invoiceFrom">
        select
        customer,
        salesorderid,
        fptt,
        fkdwkhyh,
        fkdwyhzh,
        fkdwdz,
        fkdwdh,
        tyshxydm,
        kprq,
        fphm,
        bz,
        taxrate
        from invoiceform
        <where>
            <if test="date1 != null || date1 != ''">
                and DATE(invoiceform.kprq) > #{date1}
            </if>
            <if test="date2 != null || date2 != ''">
                and #{date2} >= DATE(invoiceform.kprq)
            </if>
            and LENGTH(invoiceform.customer)>=3
            and invoiceform.tyshxydm is not null
        </where>
        limit #{currentPage},#{pageSize}
    </select>
<!--    查询总数-->
    <select id="findTotal" resultType="long">
        select count(*) from invoiceform
        <where>
            <if test="date1 != null || date1 != ''">
                and DATE(invoiceform.kprq) > #{date1}
            </if>
            <if test="date2 != null || date2 != ''">
                and #{date2} >= DATE(invoiceform.kprq)
            </if>
            and LENGTH(invoiceform.customer)>=3
            and invoiceform.tyshxydm is not null
        </where>
    </select>
</mapper>
