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

    <resultMap type="Invoice" id="InvoiceResult">
        <result property="id"    column="id"    />
        <result property="billInfoId"    column="bill_info_id"    />
        <result property="companyId"    column="company_id"    />
        <result property="systemOrderno"    column="system_orderno"    />
        <result property="outTradeOrderno"    column="out_trade_orderno"    />
        <result property="fpqqlsh"    column="fpqqlsh"    />
        <result property="invoiceType"    column="invoice_type"    />
        <result property="state"    column="state"    />
        <result property="cInvoiceid"    column="c_invoiceid"    />
        <result property="fpdm"    column="fpdm"    />
        <result property="fphm"    column="fphm"    />
        <result property="kprq"    column="kprq"    />
        <result property="buyerName"    column="buyer_name"    />
        <result property="buyerTaxnum"    column="buyer_taxnum"    />
        <result property="buyerAddress"    column="buyer_address"    />
        <result property="buyerTelephone"    column="buyer_telephone"    />
        <result property="buyerAccount"    column="buyer_account"    />
        <result property="buyerEmail"    column="buyer_email"    />
        <result property="buyerPhone"    column="buyer_phone"    />
        <result property="sellerName"    column="seller_name"    />
        <result property="sellerTaxnum"    column="seller_taxnum"    />
        <result property="sellerAddress"    column="seller_address"    />
        <result property="sellerTelephone"    column="seller_telephone"    />
        <result property="sellerAccount"    column="seller_account"    />
        <result property="message"    column="message"    />
        <result property="clerk"    column="clerk"    />
        <result property="payee"    column="payee"    />
        <result property="checker"    column="checker"    />
        <result property="taxfreeamt"    column="taxfreeamt"    />
        <result property="tax"    column="tax"    />
        <result property="taxamt"    column="taxamt"    />
        <result property="originFpdm"    column="origin_fpdm"    />
        <result property="originFphm"    column="origin_fphm"    />
        <result property="jym"    column="jym"    />
        <result property="qrCode"    column="qr_code"    />
        <result property="machineCode"    column="machine_code"    />
        <result property="cipherText"    column="cipher_text"    />
        <result property="invoicePdfUrl"    column="invoice_pdf_url"    />
        <result property="invoiceJpgUrl"    column="invoice_jpg_url"    />
        <result property="invoiceFileStorage"    column="invoice_file_storage"    />
        <result property="invoiceMsg"    column="invoice_msg"    />
        <result property="invoiceResultMsg"    column="invoice_result_msg"    />
        <result property="productOilFlag"    column="product_oil_flag"    />
        <result property="qdbz"    column="qdbz"    />
        <result property="cOfdUrl"    column="c_ofd_url"    />
        <result property="fjh"    column="fjh"    />
        <result property="terminalNumber"    column="terminal_number"    />
        <result property="deprecateType"    column="deprecate_type"    />
        <result property="deprecateReason"    column="deprecate_reason"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="jbrxm" column="jbrxm"/>
        <result property="jbrzjhm" column="jbrzjhm"/>
        <result property="jbrzjzldm" column="jbrzjzldm"/>
        <result property="jrznsrsbh" column="jrznsrsbh"/>
        <result property="jshj" column="jshj"/>
        <result property="tdyslxdm" column="tdyslxdm"/>
        <result property="tdywxx" column="tdywxx"/>
        <result property="ext" column="ext"/>
        <result property="filedata" column="filedata"/>
        <result property="zzfpdm" column="zzfpdm"/>
        <result property="zzfphm" column="zzfphm"/>
    </resultMap>

    <resultMap id="InvoiceInvoiceDetailResult" type="Invoice" extends="InvoiceResult">
        <collection property="invoiceDetailList" notNullColumn="sub_id" javaType="java.util.List" resultMap="InvoiceDetailResult" />
    </resultMap>

    <resultMap type="InvoiceDetail" id="InvoiceDetailResult">
        <result property="id"    column="sub_id"    />
        <result property="invoiceId"    column="sub_invoice_id"    />
        <result property="index"    column="sub_index"    />
        <result property="goodName"    column="sub_good_name"    />
        <result property="num"    column="sub_num"    />
        <result property="price"    column="sub_price"    />
        <result property="hsbz"    column="sub_hsbz"    />
        <result property="taxrate"    column="sub_taxrate"    />
        <result property="spec"    column="sub_spec"    />
        <result property="unit"    column="sub_unit"    />
        <result property="spbm"    column="sub_spbm"    />
        <result property="bmbbh"    column="sub_bmbbh"    />
        <result property="zsbm"    column="sub_zsbm"    />
        <result property="fphxz"    column="sub_fphxz"    />
        <result property="yhzcbs"    column="sub_yhzcbs"    />
        <result property="zzstsgl"    column="sub_zzstsgl"    />
        <result property="lslbs"    column="sub_lslbs"    />
        <result property="kce"    column="sub_kce"    />
        <result property="taxfreeamt"    column="sub_taxfreeamt"    />
        <result property="tax"    column="sub_tax"    />
        <result property="taxamt"    column="sub_taxamt"    />
        <result property="tspz"    column="sub_tspz"    />
        <result property="createBy"    column="sub_create_by"    />
        <result property="createTime"    column="sub_create_time"    />
        <result property="updateBy"    column="sub_update_by"    />
    </resultMap>

    <sql id="selectInvoiceVo">
        select id, bill_info_id, company_id, system_orderno, out_trade_orderno, fpqqlsh, invoice_type, state, c_invoiceid, fpdm, fphm, kprq, buyer_name, buyer_taxnum, buyer_address, buyer_telephone, buyer_account, buyer_email, buyer_phone, seller_name, seller_taxnum, seller_address, seller_telephone, seller_account, message, clerk, payee, checker, taxfreeamt, tax, taxamt, origin_fpdm, origin_fphm, jym, qr_code, machine_code, cipher_text, invoice_pdf_url, invoice_jpg_url,invoice_file_storage, invoice_msg, invoice_result_msg, product_oil_flag, qdbz, c_ofd_url, fjh, terminal_number,deprecate_type,deprecate_reason,create_by, create_time, update_by, update_time from invoice
    </sql>

    <select id="selectInvoiceList" parameterType="Invoice" resultMap="InvoiceResult">
        <include refid="selectInvoiceVo"/>
        <where>
            <if test="companyId != null "> and company_id = #{companyId}</if>
            <if test="systemOrderno != null  and systemOrderno != ''"> and system_orderno = #{systemOrderno}</if>
            <if test="outTradeOrderno != null  and outTradeOrderno != ''"> and out_trade_orderno = #{outTradeOrderno}</if>
            <if test="fpqqlsh != null  and fpqqlsh != ''"> and fpqqlsh = #{fpqqlsh}</if>
            <if test="invoiceType != null  and invoiceType != ''"> and invoice_type = #{invoiceType}</if>
            <if test="state != null "> and state = #{state}</if>
        </where>
    </select>

    <!--查询发票信息，目前用于控制台发票查询返回，整合selectInvoiceById、selectBySystemOrderNo、selectByFpdmFphm，有新加字段的时候一起改着这个SQL -->
    <select id="selectInvoiceAllIn" parameterType="Invoice" resultMap="InvoiceInvoiceDetailResult">
        SELECT
            a.id,
            a.bill_info_id,
            a.company_id,
            a.system_orderno,
            a.out_trade_orderno,
            a.fpqqlsh,
            a.invoice_type,
            a.state,
            a.c_invoiceid,
            a.fpdm,
            a.fphm,
            a.kprq,
            a.buyer_name,
            a.buyer_taxnum,
            a.buyer_address,
            a.buyer_telephone,
            a.buyer_account,
            a.buyer_email,
            a.buyer_phone,
            a.seller_name,
            a.seller_taxnum,
            a.seller_address,
            a.seller_telephone,
            a.seller_account,
            a.message,
            a.clerk,
            a.payee,
            a.checker,
            a.taxfreeamt,
            a.tax,
            a.taxamt,
            a.origin_fpdm,
            a.origin_fphm,
            a.jym,
            a.qr_code,
            a.machine_code,
            a.cipher_text,
            a.invoice_pdf_url,
            a.invoice_jpg_url,
            a.invoice_file_storage,
            a.invoice_msg,
            a.invoice_result_msg,
            a.product_oil_flag,
            a.qdbz,
            a.c_ofd_url,
            a.fjh,
            a.terminal_number,
            a.deprecate_type,
            a.deprecate_reason,
            a.create_by,
            a.create_time,
            a.update_by,
            a.update_time,
            b.id AS sub_id,
            b.invoice_id AS sub_invoice_id,
            b.INDEX AS sub_index,
            b.good_name AS sub_good_name,
            b.num AS sub_num,
            b.price AS sub_price,
            b.hsbz AS sub_hsbz,
            b.taxrate AS sub_taxrate,
            b.spec AS sub_spec,
            b.unit AS sub_unit,
            b.spbm AS sub_spbm,
            b.bmbbh AS sub_bmbbh,
            b.zsbm AS sub_zsbm,
            b.fphxz AS sub_fphxz,
            b.yhzcbs AS sub_yhzcbs,
            b.zzstsgl AS sub_zzstsgl,
            b.lslbs AS sub_lslbs,
            b.kce AS sub_kce,
            b.taxfreeamt AS sub_taxfreeamt,
            b.tax AS sub_tax,
            b.taxamt AS sub_taxamt,
            b.tspz AS sub_tspz,
            b.create_by AS sub_create_by,
            b.create_time AS sub_create_time,
            b.update_by AS sub_update_by
        FROM
            invoice a
                LEFT JOIN invoice_detail b ON b.invoice_id = a.id
        <where>
            <if test="companyId != null "> and a.company_id = #{companyId}</if>
            <if test="systemOrderno != null  and systemOrderno != ''"> and a.system_orderno = #{systemOrderno}</if>
            <if test="outTradeOrderno != null  and outTradeOrderno != ''"> and a.out_trade_orderno = #{outTradeOrderno}</if>
            <if test="fpqqlsh != null  and fpqqlsh != ''"> and a.fpqqlsh = #{fpqqlsh}</if>
        </where>
         order by a.id desc
    </select>

    <select id="selectInvoiceById" parameterType="Long" resultMap="InvoiceInvoiceDetailResult">
        select a.id, a.bill_info_id, a.company_id, a.system_orderno, a.out_trade_orderno, a.fpqqlsh, a.invoice_type, a.state, a.c_invoiceid, a.fpdm, a.fphm, a.kprq, a.buyer_name, a.buyer_taxnum, a.buyer_address, a.buyer_telephone, a.buyer_account, a.buyer_email, a.buyer_phone, a.seller_name, a.seller_taxnum, a.seller_address, a.seller_telephone, a.seller_account, a.message, a.clerk, a.payee, a.checker, a.taxfreeamt, a.tax, a.taxamt, a.origin_fpdm, a.origin_fphm, a.jym, a.qr_code, a.machine_code, a.cipher_text, a.invoice_pdf_url, a.invoice_jpg_url,a.invoice_file_storage, a.invoice_msg, a.invoice_result_msg, a.product_oil_flag, a.qdbz, a.c_ofd_url, a.fjh, a.terminal_number,a.deprecate_type,a.deprecate_reason, a.create_by, a.create_time, a.update_by, a.update_time,
 b.id as sub_id, b.invoice_id as sub_invoice_id, b.index as sub_index, b.good_name as sub_good_name, b.num as sub_num, b.price as sub_price, b.hsbz as sub_hsbz, b.taxrate as sub_taxrate, b.spec as sub_spec, b.unit as sub_unit, b.spbm as sub_spbm, b.bmbbh as sub_bmbbh, b.zsbm as sub_zsbm, b.fphxz as sub_fphxz, b.yhzcbs as sub_yhzcbs, b.zzstsgl as sub_zzstsgl, b.lslbs as sub_lslbs, b.kce as sub_kce, b.taxfreeamt as sub_taxfreeamt, b.tax as sub_tax, b.taxamt as sub_taxamt, b.tspz as sub_tspz, b.create_by as sub_create_by, b.create_time as sub_create_time, b.update_by as sub_update_by
        from invoice a
        left join invoice_detail b on b.invoice_id = a.id
        where a.id = #{id}
    </select>

    <select id="selectBySystemOrderNo" resultMap="InvoiceInvoiceDetailResult">
        select a.id, a.bill_info_id, a.company_id, a.system_orderno, a.out_trade_orderno, a.fpqqlsh, a.invoice_type, a.state, a.c_invoiceid, a.fpdm, a.fphm, a.kprq, a.buyer_name, a.buyer_taxnum, a.buyer_address, a.buyer_telephone, a.buyer_account, a.buyer_email, a.buyer_phone, a.seller_name, a.seller_taxnum, a.seller_address, a.seller_telephone, a.seller_account, a.message, a.clerk, a.payee, a.checker, a.taxfreeamt, a.tax, a.taxamt, a.origin_fpdm, a.origin_fphm, a.jym, a.qr_code, a.machine_code, a.cipher_text, a.invoice_pdf_url, a.invoice_jpg_url,a.invoice_file_storage, a.invoice_msg, a.invoice_result_msg, a.product_oil_flag, a.qdbz, a.c_ofd_url, a.fjh, a.terminal_number,a.deprecate_type,a.deprecate_reason, a.create_by, a.create_time, a.update_by, a.update_time,
               b.id as sub_id, b.invoice_id as sub_invoice_id, b.index as sub_index, b.good_name as sub_good_name, b.num as sub_num, b.price as sub_price, b.hsbz as sub_hsbz, b.taxrate as sub_taxrate, b.spec as sub_spec, b.unit as sub_unit, b.spbm as sub_spbm, b.bmbbh as sub_bmbbh, b.zsbm as sub_zsbm, b.fphxz as sub_fphxz, b.yhzcbs as sub_yhzcbs, b.zzstsgl as sub_zzstsgl, b.lslbs as sub_lslbs, b.kce as sub_kce, b.taxfreeamt as sub_taxfreeamt, b.tax as sub_tax, b.taxamt as sub_taxamt, b.tspz as sub_tspz, b.create_by as sub_create_by, b.create_time as sub_create_time, b.update_by as sub_update_by
        from invoice a
                 left join invoice_detail b on b.invoice_id = a.id
        where a.company_id = #{companyId} and a.system_orderno = #{system_orderno} order by a.id desc limit 1
    </select>

    <select id="selectByFpdmFphm" resultMap="InvoiceInvoiceDetailResult">
        select a.id, a.bill_info_id, a.company_id, a.system_orderno, a.out_trade_orderno, a.fpqqlsh, a.invoice_type, a.state, a.c_invoiceid, a.fpdm, a.fphm, a.kprq, a.buyer_name, a.buyer_taxnum, a.buyer_address, a.buyer_telephone, a.buyer_account, a.buyer_email, a.buyer_phone, a.seller_name, a.seller_taxnum, a.seller_address, a.seller_telephone, a.seller_account, a.message, a.clerk, a.payee, a.checker, a.taxfreeamt, a.tax, a.taxamt, a.origin_fpdm, a.origin_fphm, a.jym, a.qr_code, a.machine_code, a.cipher_text, a.invoice_pdf_url, a.invoice_jpg_url,a.invoice_file_storage, a.invoice_msg, a.invoice_result_msg, a.product_oil_flag, a.qdbz, a.c_ofd_url, a.fjh, a.terminal_number,a.deprecate_type,a.deprecate_reason, a.create_by, a.create_time, a.update_by, a.update_time,
               b.id as sub_id, b.invoice_id as sub_invoice_id, b.index as sub_index, b.good_name as sub_good_name, b.num as sub_num, b.price as sub_price, b.hsbz as sub_hsbz, b.taxrate as sub_taxrate, b.spec as sub_spec, b.unit as sub_unit, b.spbm as sub_spbm, b.bmbbh as sub_bmbbh, b.zsbm as sub_zsbm, b.fphxz as sub_fphxz, b.yhzcbs as sub_yhzcbs, b.zzstsgl as sub_zzstsgl, b.lslbs as sub_lslbs, b.kce as sub_kce, b.taxfreeamt as sub_taxfreeamt, b.tax as sub_tax, b.taxamt as sub_taxamt, b.tspz as sub_tspz, b.create_by as sub_create_by, b.create_time as sub_create_time, b.update_by as sub_update_by
        from invoice a
                 left join invoice_detail b on b.invoice_id = a.id
        where a.company_id = #{companyId} and a.fpdm = #{fpdm} and a.fphm = #{fphm}
    </select>

    <insert id="insertInvoice" parameterType="Invoice" useGeneratedKeys="true" keyProperty="id">
        insert into invoice
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="billInfoId != null">bill_info_id,</if>
            <if test="companyId != null">company_id,</if>
            <if test="systemOrderno != null and systemOrderno != ''">system_orderno,</if>
            <if test="outTradeOrderno != null and outTradeOrderno != ''">out_trade_orderno,</if>
            <if test="fpqqlsh != null and fpqqlsh != ''">fpqqlsh,</if>
            <if test="invoiceType != null and invoiceType != ''">invoice_type,</if>
            <if test="state != null">state,</if>
            <if test="cInvoiceid != null and cInvoiceid != ''">c_invoiceid,</if>
            <if test="fpdm != null and fpdm != ''">fpdm,</if>
            <if test="fphm != null and fphm != ''">fphm,</if>
            <if test="kprq != null">kprq,</if>
            <if test="buyerName != null and buyerName != ''">buyer_name,</if>
            <if test="buyerTaxnum != null and buyerTaxnum != ''">buyer_taxnum,</if>
            <if test="buyerAddress != null">buyer_address,</if>
            <if test="buyerTelephone != null">buyer_telephone,</if>
            <if test="buyerAccount != null">buyer_account,</if>
            <if test="buyerEmail != null">buyer_email,</if>
            <if test="buyerPhone != null">buyer_phone,</if>
            <if test="sellerName != null and sellerName != ''">seller_name,</if>
            <if test="sellerTaxnum != null and sellerTaxnum != ''">seller_taxnum,</if>
            <if test="sellerAddress != null">seller_address,</if>
            <if test="sellerTelephone != null">seller_telephone,</if>
            <if test="sellerAccount != null">seller_account,</if>
            <if test="message != null and message != ''">message,</if>
            <if test="clerk != null and clerk != ''">clerk,</if>
            <if test="payee != null and payee != ''">payee,</if>
            <if test="checker != null and checker != ''">checker,</if>
            <if test="taxfreeamt != null">taxfreeamt,</if>
            <if test="tax != null">tax,</if>
            <if test="taxamt != null">taxamt,</if>
            <if test="originFpdm != null">origin_fpdm,</if>
            <if test="originFphm != null">origin_fphm,</if>
            <if test="jym != null">jym,</if>
            <if test="qrCode != null">qr_code,</if>
            <if test="machineCode != null">machine_code,</if>
            <if test="cipherText != null">cipher_text,</if>
            <if test="invoicePdfUrl != null">invoice_pdf_url,</if>
            <if test="invoiceJpgUrl != null">invoice_jpg_url,</if>
            <if test="invoiceFileStorage != null">invoice_file_storage,</if>
            <if test="invoiceMsg != null">invoice_msg,</if>
            <if test="invoiceResultMsg != null">invoice_result_msg,</if>
            <if test="productOilFlag != null">product_oil_flag,</if>
            <if test="qdbz != null">qdbz,</if>
            <if test="cOfdUrl != null">c_ofd_url,</if>
            <if test="fjh != null">fjh,</if>
            <if test="terminalNumber != null">terminal_number,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="jbrxm != null">jbrxm,</if>
            <if test="jbrzjhm != null">jbrzjhm,</if>
            <if test="jbrzjzldm != null">jbrzjzldm,</if>
            <if test="jrznsrsbh != null">jrznsrsbh,</if>
            <if test="jshj != null">jshj,</if>
            <if test="tdyslxdm != null">tdyslxdm,</if>
            <if test="tdywxx != null">tdywxx,</if>
            <if test="ext != null">ext,</if>
            <if test="filedata != null">filedata,</if>
            <if test="zzfpdm != null">zzfpdm,</if>
            <if test="zzfphm != null">zzfphm,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="billInfoId != null">#{billInfoId},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="systemOrderno != null and systemOrderno != ''">#{systemOrderno},</if>
            <if test="outTradeOrderno != null and outTradeOrderno != ''">#{outTradeOrderno},</if>
            <if test="fpqqlsh != null and fpqqlsh != ''">#{fpqqlsh},</if>
            <if test="invoiceType != null and invoiceType != ''">#{invoiceType},</if>
            <if test="state != null">#{state},</if>
            <if test="cInvoiceid != null and cInvoiceid != ''">#{cInvoiceid},</if>
            <if test="fpdm != null and fpdm != ''">#{fpdm},</if>
            <if test="fphm != null and fphm != ''">#{fphm},</if>
            <if test="kprq != null">#{kprq},</if>
            <if test="buyerName != null and buyerName != ''">#{buyerName},</if>
            <if test="buyerTaxnum != null and buyerTaxnum != ''">#{buyerTaxnum},</if>
            <if test="buyerAddress != null">#{buyerAddress},</if>
            <if test="buyerTelephone != null">#{buyerTelephone},</if>
            <if test="buyerAccount != null">#{buyerAccount},</if>
            <if test="buyerEmail != null">#{buyerEmail},</if>
            <if test="buyerPhone != null">#{buyerPhone},</if>
            <if test="sellerName != null and sellerName != ''">#{sellerName},</if>
            <if test="sellerTaxnum != null and sellerTaxnum != ''">#{sellerTaxnum},</if>
            <if test="sellerAddress != null">#{sellerAddress},</if>
            <if test="sellerTelephone != null">#{sellerTelephone},</if>
            <if test="sellerAccount != null">#{sellerAccount},</if>
            <if test="message != null and message != ''">#{message},</if>
            <if test="clerk != null and clerk != ''">#{clerk},</if>
            <if test="payee != null and payee != ''">#{payee},</if>
            <if test="checker != null and checker != ''">#{checker},</if>
            <if test="taxfreeamt != null">#{taxfreeamt},</if>
            <if test="tax != null">#{tax},</if>
            <if test="taxamt != null">#{taxamt},</if>
            <if test="originFpdm != null">#{originFpdm},</if>
            <if test="originFphm != null">#{originFphm},</if>
            <if test="jym != null">#{jym},</if>
            <if test="qrCode != null">#{qrCode},</if>
            <if test="machineCode != null">#{machineCode},</if>
            <if test="cipherText != null">#{cipherText},</if>
            <if test="invoicePdfUrl != null">#{invoicePdfUrl},</if>
            <if test="invoiceJpgUrl != null">#{invoiceJpgUrl},</if>
            <if test="invoiceFileStorage != null">#{invoiceFileStorage},</if>
            <if test="invoiceMsg != null">#{invoiceMsg},</if>
            <if test="invoiceResultMsg != null">#{invoiceResultMsg},</if>
            <if test="productOilFlag != null">#{productOilFlag},</if>
            <if test="qdbz != null">#{qdbz},</if>
            <if test="cOfdUrl != null">#{cOfdUrl},</if>
            <if test="fjh != null">#{fjh},</if>
            <if test="terminalNumber != null">#{terminalNumber},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="jbrxm != null">#{jbrxm},</if>
            <if test="jbrzjhm != null">#{jbrzjhm},</if>
            <if test="jbrzjzldm != null">#{jbrzjzldm},</if>
            <if test="jrznsrsbh != null">#{jrznsrsbh},</if>
            <if test="jshj != null">#{jshj},</if>
            <if test="tdyslxdm != null">#{tdyslxdm},</if>
            <if test="tdywxx != null">#{tdywxx},</if>
            <if test="ext != null">#{ext},</if>
            <if test="filedata != null">#{filedata},</if>
            <if test="zzfpdm != null">#{zzfpdm},</if>
            <if test="zzfphm != null">#{zzfphm},</if>
         </trim>
    </insert>

    <update id="updateInvoice" parameterType="Invoice">
        update invoice
        <trim prefix="SET" suffixOverrides=",">
            <if test="billInfoId != null">bill_info_id = #{billInfoId},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="systemOrderno != null and systemOrderno != ''">system_orderno = #{systemOrderno},</if>
            <if test="outTradeOrderno != null and outTradeOrderno != ''">out_trade_orderno = #{outTradeOrderno},</if>
            <if test="fpqqlsh != null and fpqqlsh != ''">fpqqlsh = #{fpqqlsh},</if>
            <if test="invoiceType != null and invoiceType != ''">invoice_type = #{invoiceType},</if>
            <if test="state != null">state = #{state},</if>
            <if test="cInvoiceid != null and cInvoiceid != ''">c_invoiceid = #{cInvoiceid},</if>
            <if test="fpdm != null and fpdm != ''">fpdm = #{fpdm},</if>
            <if test="fphm != null and fphm != ''">fphm = #{fphm},</if>
            <if test="kprq != null">kprq = #{kprq},</if>
            <if test="buyerName != null and buyerName != ''">buyer_name = #{buyerName},</if>
            <if test="buyerTaxnum != null and buyerTaxnum != ''">buyer_taxnum = #{buyerTaxnum},</if>
            <if test="buyerAddress != null">buyer_address = #{buyerAddress},</if>
            <if test="buyerTelephone != null">buyer_telephone = #{buyerTelephone},</if>
            <if test="buyerAccount != null">buyer_account = #{buyerAccount},</if>
            <if test="buyerEmail != null">buyer_email = #{buyerEmail},</if>
            <if test="buyerPhone != null">buyer_phone = #{buyerPhone},</if>
            <if test="sellerName != null and sellerName != ''">seller_name = #{sellerName},</if>
            <if test="sellerTaxnum != null and sellerTaxnum != ''">seller_taxnum = #{sellerTaxnum},</if>
            <if test="sellerAddress != null">seller_address = #{sellerAddress},</if>
            <if test="sellerTelephone != null">seller_telephone = #{sellerTelephone},</if>
            <if test="sellerAccount != null">seller_account = #{sellerAccount},</if>
            <if test="message != null and message != ''">message = #{message},</if>
            <if test="clerk != null and clerk != ''">clerk = #{clerk},</if>
            <if test="payee != null and payee != ''">payee = #{payee},</if>
            <if test="checker != null and checker != ''">checker = #{checker},</if>
            <if test="taxfreeamt != null">taxfreeamt = #{taxfreeamt},</if>
            <if test="tax != null">tax = #{tax},</if>
            <if test="taxamt != null">taxamt = #{taxamt},</if>
            <if test="originFpdm != null">origin_fpdm = #{originFpdm},</if>
            <if test="originFphm != null">origin_fphm = #{originFphm},</if>
            <if test="jym != null">jym = #{jym},</if>
            <if test="qrCode != null">qr_code = #{qrCode},</if>
            <if test="machineCode != null">machine_code = #{machineCode},</if>
            <if test="cipherText != null">cipher_text = #{cipherText},</if>
            <if test="invoicePdfUrl != null">invoice_pdf_url = #{invoicePdfUrl},</if>
            <if test="invoiceJpgUrl != null">invoice_jpg_url = #{invoiceJpgUrl},</if>
            <if test="invoiceFileStorage != null">invoice_file_storage = #{invoiceFileStorage},</if>
            <if test="invoiceMsg != null">invoice_msg = #{invoiceMsg},</if>
            <if test="invoiceResultMsg != null">invoice_result_msg = #{invoiceResultMsg},</if>
            <if test="productOilFlag != null">product_oil_flag = #{productOilFlag},</if>
            <if test="qdbz != null">qdbz = #{qdbz},</if>
            <if test="cOfdUrl != null">c_ofd_url = #{cOfdUrl},</if>
            <if test="fjh != null">fjh = #{fjh},</if>
            <if test="terminalNumber != null">terminal_number = #{terminalNumber},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="jbrxm != null">jbrxm = #{jbrxm},</if>
            <if test="jbrzjhm != null">jbrzjhm = #{jbrzjhm},</if>
            <if test="jbrzjzldm != null">jbrzjzldm = #{jbrzjzldm},</if>
            <if test="jrznsrsbh != null">jrznsrsbh = #{jrznsrsbh},</if>
            <if test="jshj != null">jshj = #{jshj},</if>
            <if test="tdyslxdm != null">tdyslxdm = #{tdyslxdm},</if>
            <if test="tdywxx != null">tdywxx = #{tdywxx},</if>
            <if test="ext != null">ext = #{ext},</if>
            <if test="filedata != null">ext = #{filedata},</if>
            <if test="zzfpdm != null">ext = #{zzfpdm},</if>
            <if test="zzfphm != null">ext = #{zzfphm},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteInvoiceById" parameterType="Long">
        delete from invoice where id = #{id}
    </delete>

    <delete id="deleteInvoiceByIds" parameterType="String">
        delete from invoice where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <delete id="deleteInvoiceDetailByInvoiceIds" parameterType="String">
        delete from invoice_detail where invoice_id in
        <foreach item="invoiceId" collection="array" open="(" separator="," close=")">
            #{invoiceId}
        </foreach>
    </delete>

    <delete id="deleteInvoiceDetailByInvoiceId" parameterType="Long">
        delete from invoice_detail where invoice_id = #{invoiceId}
    </delete>

    <insert id="batchInvoiceDetail">
        insert into invoice_detail( id, invoice_id, `index`, good_name, num, price, hsbz, taxrate, spec, unit, spbm, bmbbh, zsbm, fphxz, yhzcbs, zzstsgl, lslbs, kce, taxfreeamt, tax, taxamt, tspz, create_by, create_time, update_by) values
		<foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.invoiceId}, #{item.index}, #{item.goodName}, #{item.num}, #{item.price}, #{item.hsbz}, #{item.taxrate}, #{item.spec}, #{item.unit}, #{item.spbm}, #{item.bmbbh}, #{item.zsbm}, #{item.fphxz}, #{item.yhzcbs}, #{item.zzstsgl}, #{item.lslbs}, #{item.kce}, #{item.taxfreeamt}, #{item.tax}, #{item.taxamt}, #{item.tspz}, #{item.createBy}, #{item.createTime}, #{item.updateBy})
        </foreach>
    </insert>

    <select id="selectInvoiceByBillInfoId" parameterType="Long" resultMap="InvoiceInvoiceDetailResult">
        select a.id, a.bill_info_id, a.company_id, a.system_orderno, a.out_trade_orderno, a.fpqqlsh, a.invoice_type, a.state, a.c_invoiceid, a.fpdm, a.fphm, a.kprq, a.buyer_name, a.buyer_taxnum, a.buyer_address, a.buyer_telephone, a.buyer_account, a.buyer_email, a.buyer_phone, a.seller_name, a.seller_taxnum, a.seller_address, a.seller_telephone, a.seller_account, a.message, a.clerk, a.payee, a.checker, a.taxfreeamt, a.tax, a.taxamt, a.origin_fpdm, a.origin_fphm, a.jym, a.qr_code, a.machine_code, a.cipher_text, a.invoice_pdf_url, a.invoice_jpg_url,a.invoice_file_storage, a.invoice_msg, a.invoice_result_msg, a.product_oil_flag, a.qdbz, a.c_ofd_url, a.fjh, a.terminal_number,a.deprecate_type,a.deprecate_reason, a.create_by, a.create_time, a.update_by, a.update_time,
               b.id as sub_id, b.invoice_id as sub_invoice_id, b.index as sub_index, b.good_name as sub_good_name, b.num as sub_num, b.price as sub_price, b.hsbz as sub_hsbz, b.taxrate as sub_taxrate, b.spec as sub_spec, b.unit as sub_unit, b.spbm as sub_spbm, b.bmbbh as sub_bmbbh, b.zsbm as sub_zsbm, b.fphxz as sub_fphxz, b.yhzcbs as sub_yhzcbs, b.zzstsgl as sub_zzstsgl, b.lslbs as sub_lslbs, b.kce as sub_kce, b.taxfreeamt as sub_taxfreeamt, b.tax as sub_tax, b.taxamt as sub_taxamt, b.tspz as sub_tspz, b.create_by as sub_create_by, b.create_time as sub_create_time, b.update_by as sub_update_by
        from invoice a
                 left join invoice_detail b on b.invoice_id = a.id
        where a.bill_info_id = #{id}
    </select>
</mapper>
