发票信息查询的sql组装进行优化

xinyb

2023-12-16 e2ab2b0e0b97f2a1c81b78ea0163d74e8eae519a

发票信息查询的sql组装进行优化

 src/com/yc/MaintenanceFee/service/impl/InvoiceServiceImpl.java

@@ -39,13 +39,14 @@
                    " case when a.DocStatus = 100 and isnull(a.ReturnStatus,'') = '' then 1 else 0 end as isShowReturnButton,\n" +
                    " a.CltCode,a.CltName,(case when isnull(a.invoiceMedium,'') = '' then '' else\n" +
                    " case when a.invoiceMedium = 'PaperInvoice' then '纸质发票' else '电子发票' end end) as invoiceMedium ,\n" +
                    " (select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode) as InvoiceTypeCode,a.billingNameType,a.buyerTaxNo,a.buyerName,\n" +
                    " (select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode and s.dictid=-15050411) as InvoiceTypeCode,a.billingNameType,a.buyerTaxNo,a.buyerName,\n" +
                    " a.buyerAddress,a.buyerTelephone,a.buyerBankName,a.buyerBankNumber,a.billingCompany,a.receivingName,\n" +
                    " a.ReceivingTelephone,a.receivingAddress,a.sumTotalMoney2,a.sumNetMoney,a.sumVatMoney,b.vatRate,a.memo,b.docItem,a.refCode as matCode,b.matName,\n" +
                    " b.UOM,b.digit,b.price,b.totalMoney,b.totalMoney2,b.otherMoney,b.itemMemo,a.AmountRecemoney,b.netprice \n" +
                    " from t150504H a \n" +
                    " join t150504D b on a.DocCode=b.docCode \n" +
                    " where a.CltCode=@cltCode and b.refformid=120311";
            //  参数号：-15050411，表示：维护发票类型 在9815功能号设置
            if (!StringUtils.isBlank(startDate)) {
                sql += " and a.DocDate>=" + GridUtils.prossSqlParm(startDate);
            }
@@ -79,7 +80,7 @@
                    " case when a.DocStatus = 100 and isnull(a.ReturnStatus,'') = '' then 1 else 0 end as isShowReturnButton,\n" +
                    "a.CltCode,a.CltName,(case when isnull(a.invoiceMedium,'') = '' then '' else\n" +
                    " case when a.invoiceMedium = 'PaperInvoice' then '纸质发票' else '电子发票' end end) as invoiceMedium," +
                    " (select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode) as InvoiceTypeCode,a.billingNameType,a.buyerTaxNo,a.buyerName,\n" +
                    " (select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode and s.dictid=-15050411) as InvoiceTypeCode,a.billingNameType,a.buyerTaxNo,a.buyerName,\n" +
                    " a.buyerAddress,a.buyerTelephone,a.buyerBankName,a.buyerBankNumber,a.billingCompany,a.receivingName,\n" +
                    " a.ReceivingTelephone,a.receivingAddress,a.sumTotalMoney2,a.sumNetMoney,a.sumVatMoney,b.vatRate,a.memo,b.docItem,b.ShouKuanDoccode as matCode," +
                    " (select EnterDate from t150101H where docCode = b.ShouKuanDoccode) as docTime,b.matName,\n" +
@@ -255,7 +256,7 @@
                    "  raiserror('获取不到当前系统的客户编号(cltCode)',16,1)\n" +
                    "  return \n" +
                    " end \n";
            sql += "select (select s.dictValue from _sysdict s where s.interValue=invoiceTypeCode) as invoiceTypeCode,billingNameType,buyerTaxNo,buyerName,\n" +
            sql += "select (select s.dictValue from _sysdict s where s.interValue=invoiceTypeCode and s.dictid=-15050411) as invoiceTypeCode,billingNameType,buyerTaxNo,buyerName,\n" +
                    " buyerAddress,buyerTelephone,buyerBankName,buyerBankNumber,tel2 as buyerPhone,email as buyerEmail \n" +
                    " from t110203 where cltCode=@cltCode";
            return super.jdbcTemplate.queryForMap(sql);
@@ -544,7 +545,7 @@
//                  " from t150504h a join t150504d b on a.doccode = b.doccode \n" +
//                  " where a.doccode = "+GridUtils.prossSqlParm(fpDocCode)+" \n" +
//                  " group by a.doccode,b.vatrate";
            String sql = " select a.doccode,(select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode) as InvoiceTypeCode,\n" +
            String sql = " select a.doccode,(select s.dictValue from _sysdict s where s.interValue=a.InvoiceTypeCode and s.dictid=-15050411) as InvoiceTypeCode,\n" +
                    " a.buyerName,a.buyerTelephone,a.buyerTaxNo,a.buyerAddress,\n" +
                    " a.buyerBankName,a.buyerBankNumber,b.vatrate,\n" +
                    " sum(isnull(b.netmoney,0)) as netmoney,\n" +