uas-eis/src/main/java/com/uas/eis/dto/FindOrderRelationInvoiceDTO.java

package com.uas.eis.dto;

import lombok.Data;
import java.util.List;

@Data
public class FindOrderRelationInvoiceDTO {
    private String sid;//发票流水号，返回合并冲销的开票单为空
    private String orderNo;//开票单号
    private String createtime;//开票单的创建时间
    private Number invoiceType;//发票类型，合并冲销时为空；1：蓝票 2：红票
    private String invoiceLine;//发票种类，合并冲销时为空 p：普通发票（电票） c：普通发票（纸票） s：专用发票（纸票） b：专用发票（电子） e：收购发票（电票） f：收购发票（纸质） r：普通发票（卷式） bs：数电专票（电子） pc：数电普票（电子） es：数电专票（纸质） ec：数电普票（纸质）
    private String specificFactor;//1 成品油、35 矿产品；普通发票为空
    private Number invoiceState;//开票状态，合并冲销时为空 1：开票成功 2：开票失败
    private String exceptionMessage;//开票异常信息，开票失败状态时返回
    private String invoiceCode;//发票代码，仅开票成功会返回，发票种类为bs和pc时为空
    private String invoiceNumber;//发票号码，仅开票成功会返回，发票种类为bs和pc时为空
    private String allElectronicInvoiceNumber;//数电发票号码，仅数电发票开票成功会返回
    private String oldInvoiceCode;//原发票代码（红字发票开票成功后若有值回传），发票种类为bs和pc时为空
    private String oldInvoiceNumber;//原发票号码（红字发票开票成功后若有值回传），发票种类为bs和pc时为空
    private String oldAllElectronicInvoiceNu;//原数电发票号码（数电发票开票成功后若有值回传）
    private String invoiceTime;//开票日期，仅开票成功会返回
    private String invalidTime;//作废日期，暂时未用到
    private String rushRedState;//发票冲红状态（0 未冲红，1 全部冲红，2 部分冲红)
    private String pdfUrl;//发票pdf地址，仅开票成功时返回
    private String ofdUrl;//发票ofd地址，仅开票成功时返回
    private String imageUrl;//发票jpg地址，仅开票成功时返回
    private Number calcExcludingTaxAmount;//不含税金额，开票失败时不返回，含税金额和不含税金额任一数值返回为0，即为合并冲销
    private Number calcTaxAmount;//税额，开票失败时不返回
    private Number caleOrderAmount;//含税金额，开票失败时不返回，含税金额和不含税金额任一数值返回为0，即为合并冲销
    private String sellerTaxNo;//销方税号
    private String sellerName;//销方名称，仅开票成功会返回
    private String taxNo;//购方税号，仅开票成功会返回
    private String buyerName;//购方名称，仅开票成功会返回
    private String remark;//发票备注
    private String clerker;//开票员
    private String payee;//收款人
    private String checker;//复核人
    private String clerkId;//所属开票员id
    private String deptId;//所属部门id
    private String bField1;//主表自定义字段1
    private String bField2;//主表自定义字段2
    private String bField3;//主表自定义字段3
    private List<OrderDetailCallBackVOSDTO> orderDetailCallBackVOS;//发票来源的订单明细list
    private List<RelateRedInvoiceDTO> relateRedInvoiceList;//发票来源的订单明细list

    public String toMergeSql(int ioid,int detno){
        String sql = "merge into (" +
                "    select ei_id,ei_indate,ei_updatedate,ei_detno,ei_ioid,ei_iocode,ei_biid,ei_bicode " +
                "        ,ei_sid,ei_orderno,ei_createtime,ei_invoicetype,ei_invoiceline,ei_specificfactor,ei_invoicestate,ei_exceptionmessage,ei_invoicecode,ei_invoicenumber " +
                "        ,ei_allelectronicinvoicenumber,ei_oldinvoicecode,ei_oldinvoicenumber,ei_oldallelectronicinvoicenu,ei_invoicetime,ei_invalidtime,ei_rushredstate,ei_pdfurl,ei_ofdurl " +
                "        ,ei_imageurl,ei_calcexcludingtaxamount,ei_calctaxamount,ei_caleorderamount,ei_sellertaxno,ei_sellername,ei_taxno,ei_buyername,ei_remark,ei_clerker " +
                "        ,ei_payee,ei_checker,ei_bfield1,ei_bfield2,ei_bfield3 " +
                "    from elecinvoice where ei_ioid = " +ioid+
                " ) a USING ( " +
                "    select io_id,io_code,io_biid,io_bicode, "+detno+" eiDetno " +
                "        ,"+stringField(getSid())+" eiSid,"+stringField(getOrderNo())+" eiOrderno,"+timeField(getCreatetime())+" eiCreatetime,"+numberField(getInvoiceType())+" eiInvoicetype,"+stringField(getInvoiceLine())+" eiinvoiceline,"+stringField(getSpecificFactor())+" eiSpecificfactor,"+numberField(getInvoiceState())+" eiInvoicestate" +
                "        ,"+stringField(getExceptionMessage())+" eiExceptionmessage,"+stringField(getInvoiceCode())+" eiInvoicecode,"+stringField(getInvoiceNumber())+" eiInvoicenumber " +
                "        ,"+stringField(getAllElectronicInvoiceNumber())+" eiAllelectronicinvoicenumber,"+stringField(getOldInvoiceCode())+" eiOldinvoicecode,"+stringField(getOldInvoiceNumber())+" eiOldinvoicenumber,"+stringField(getOldAllElectronicInvoiceNu())+" eiOldallelectronicinvoicenu " +
                "        ,"+timeField(getInvoiceTime())+" eiInvoicetime,"+timeField(getInvalidTime())+" eiInvalidtime,"+(getRushRedState())+" eiRushredstate " +
                "        ,"+stringField(getPdfUrl())+" eiPdfurl,"+stringField(getOfdUrl())+" eiOfdurl,"+stringField(getImageUrl())+" eiImageurl,"+numberField(getCalcExcludingTaxAmount())+" eiCalcexcludingtaxamount,"+numberField(getCalcTaxAmount())+" eiCalctaxamount,"+numberField(getCaleOrderAmount())+" eiCaleorderamount" +
                "        ,"+stringField(getSellerTaxNo())+" eiSellertaxno,"+stringField(getSellerName())+" eiSellername,"+stringField(getTaxNo())+" eiTaxno " +
                "        ,"+stringField(getBuyerName())+" eiBuyername,"+stringField(getRemark())+" eiRemark,"+stringField(getClerker())+" eiClerker,"+stringField(getPayee())+" eiPayee,"+stringField(getChecker())+" eiChecker" +
                "        ,"+stringField(getBField1())+" eiBfield1,"+stringField(getBField2())+" eiBfield2,"+stringField(getBField3())+" eiBfield3 " +
                "    from dual,(select io_id,io_code,io_biid,io_bicode from invoiceorder where io_id = "+ioid+") " +
                ") b on (nvl(a.ei_orderno,' ') = b.eiorderno) " +
                "when matched then update set ei_updatedate=sysdate " +
                "    ,ei_detno = eidetno,ei_sid = eisid,ei_createtime = eicreatetime,ei_invoicetype = eiinvoicetype,ei_invoiceline = eiinvoiceline,ei_specificfactor = eispecificfactor " +
                "    ,ei_invoicestate = eiinvoicestate,ei_exceptionmessage = eiexceptionmessage,ei_invoicecode = eiinvoicecode,ei_invoicenumber = eiinvoicenumber " +
                "    ,ei_allelectronicinvoicenumber = eiallelectronicinvoicenumber,ei_oldinvoicecode = eioldinvoicecode,ei_oldinvoicenumber = eioldinvoicenumber,ei_oldallelectronicinvoicenu = eioldallelectronicinvoicenu " +
                "    ,ei_invoicetime = eiinvoicetime,ei_invalidtime = eiinvalidtime,ei_rushredstate = eirushredstate " +
                "    ,ei_pdfurl = eipdfurl,ei_ofdurl = eiofdurl,ei_imageurl = eiimageurl,ei_calcexcludingtaxamount = eicalcexcludingtaxamount,ei_calctaxamount = eicalctaxamount,ei_caleorderamount = eicaleorderamount " +
                "    ,ei_sellertaxno = eisellertaxno,ei_sellername = eisellername,ei_taxno = eitaxno " +
                "    ,ei_buyername = eibuyername,ei_remark = eiremark,ei_clerker = eiclerker,ei_payee = eipayee,ei_checker = eichecker,ei_bfield1 = eibfield1,ei_bfield2 = eibfield2,ei_bfield3 = eibfield3 " +
                "when NOT MATCHED THEN insert (ei_id,ei_indate,ei_detno,ei_ioid,ei_iocode,ei_biid,ei_bicode " +
                "    ,ei_sid,ei_orderno,ei_createtime,ei_invoicetype,ei_invoiceline,ei_specificfactor,ei_invoicestate,ei_exceptionmessage,ei_invoicecode,ei_invoicenumber " +
                "    ,ei_allelectronicinvoicenumber,ei_oldinvoicecode,ei_oldinvoicenumber,ei_oldallelectronicinvoicenu,ei_invoicetime,ei_invalidtime,ei_rushredstate,ei_pdfurl,ei_ofdurl " +
                "    ,ei_imageurl,ei_calcexcludingtaxamount,ei_calctaxamount,ei_caleorderamount,ei_sellertaxno,ei_sellername,ei_taxno,ei_buyername,ei_remark,ei_clerker " +
                "    ,ei_payee,ei_checker,ei_bfield1,ei_bfield2,ei_bfield3) " +
                "values(elecinvoice_seq.nextval,sysdate,eidetno,io_id,io_code,io_biid,io_bicode " +
                "    ,eisid,eiorderno,eicreatetime,eiinvoicetype,eiinvoiceline,eispecificfactor,eiinvoicestate,eiexceptionmessage,eiinvoicecode,eiinvoicenumber " +
                "    ,eiallelectronicinvoicenumber,eioldinvoicecode,eioldinvoicenumber,eioldallelectronicinvoicenu,eiinvoicetime,eiinvalidtime,eirushredstate,eipdfurl,eiofdurl " +
                "    ,eiimageurl,eicalcexcludingtaxamount,eicalctaxamount,eicaleorderamount,eisellertaxno,eisellername,eitaxno,eibuyername,eiremark,eiclerker " +
                "    ,eipayee,eichecker,eibfield1,eibfield2,eibfield3)";
        return sql;
    }
    private String numberField(Number v){
        return (v == null ? "null" : v.toString());
    }
    private String stringField(String v){
        return (v == null ? "null" : "'"+v+"'");
    }
    private String timeField(String v){
        return (v == null ? "null" : "(to_date('1970-01-0100:00:00','yyyy-mm-dd hh24:mi:ss') + "+v+"/1000/24/60/60)");
    }

}