src/main/java/com/ruoyi/purchase/controller/AccountingReportController.java
@@ -5,8 +5,8 @@ import com.ruoyi.framework.aspectj.lang.annotation.Log; import com.ruoyi.framework.aspectj.lang.enums.BusinessType; import com.ruoyi.framework.web.domain.AjaxResult; import com.ruoyi.purchase.dto.InvoicePurchaseDto; import com.ruoyi.purchase.dto.InvoicePurchaseReportDto; import com.ruoyi.purchase.dto.VatDto; import com.ruoyi.purchase.pojo.InvoicePurchase; import com.ruoyi.purchase.service.IInvoicePurchaseService; import io.swagger.annotations.Api; @@ -15,6 +15,8 @@ import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @Api(tags = "采购报表") @@ -34,8 +36,8 @@ @Log(title = "采购报表-增值税比对", businessType = BusinessType.OTHER) @GetMapping("/listVat") public AjaxResult listVat(Page page, InvoicePurchase invoicePurchase) { IPage<InvoicePurchase> result = invoicePurchaseService.listVat(page, invoicePurchase); public AjaxResult listVat(Page page,String month) { IPage<VatDto> result = invoicePurchaseService.listVat(page, month); return AjaxResult.success(result); } } src/main/java/com/ruoyi/purchase/dto/TicketRegistrationDto.java
@@ -70,6 +70,5 @@ @TableField(exist = false) private String createdAtStart; @TableField(exist = false) private String createdAtEnd; } src/main/java/com/ruoyi/purchase/dto/VatDto.java
@@ -2,18 +2,21 @@ import lombok.Data; import java.math.BigDecimal; import java.time.LocalDate; @Data public class VatDto { //月份 private LocalDate month ; private String month ; //进项税 private String jTaxAmount; private BigDecimal jTaxAmount; //销项税 private String xTaxAmount; private BigDecimal xTaxAmount; private BigDecimal taxAmount; } src/main/java/com/ruoyi/purchase/mapper/InvoicePurchaseMapper.java
@@ -22,5 +22,7 @@ IPage<InvoicePurchaseReportDto> selectPurchaseReport(IPage page, @Param("c") InvoicePurchaseReportDto invoicePurchaseReportDto); List<VatDto> listVat(Page page, InvoicePurchase invoicePurchase); IPage<VatDto> listVat(Page page,@Param("month") String month); List<VatDto> listVat1(); } src/main/java/com/ruoyi/purchase/service/IInvoicePurchaseService.java
@@ -6,6 +6,7 @@ import com.baomidou.mybatisplus.extension.service.IService; import com.ruoyi.purchase.dto.InvoicePurchaseDto; import com.ruoyi.purchase.dto.InvoicePurchaseReportDto; import com.ruoyi.purchase.dto.VatDto; import com.ruoyi.purchase.pojo.InvoicePurchase; import java.io.IOException; @@ -30,5 +31,5 @@ IPage<InvoicePurchaseReportDto> listPurchaseReport(Page page, InvoicePurchaseReportDto invoicePurchaseReportDto); IPage<InvoicePurchase> listVat(Page page, InvoicePurchase invoicePurchase); IPage<VatDto> listVat(Page page,String month); } src/main/java/com/ruoyi/purchase/service/impl/InvoicePurchaseServiceImpl.java
@@ -12,6 +12,7 @@ import com.ruoyi.project.system.mapper.SysUserMapper; import com.ruoyi.purchase.dto.InvoicePurchaseDto; import com.ruoyi.purchase.dto.InvoicePurchaseReportDto; import com.ruoyi.purchase.dto.VatDto; import com.ruoyi.purchase.mapper.InvoicePurchaseMapper; import com.ruoyi.purchase.mapper.PurchaseLedgerMapper; import com.ruoyi.purchase.pojo.InvoicePurchase; @@ -169,8 +170,11 @@ } @Override public IPage<InvoicePurchase> listVat(Page page, InvoicePurchase invoicePurchase) { invoicePurchaseMapper.listVat(page, invoicePurchase); return null; public IPage<VatDto> listVat(Page page, String month) { IPage<VatDto> vatDtos = invoicePurchaseMapper.listVat(page,month); vatDtos.getRecords().forEach(vatDto -> { vatDto.setTaxAmount(vatDto.getJTaxAmount().subtract(vatDto.getXTaxAmount())); }); return vatDtos; } } src/main/java/com/ruoyi/purchase/service/impl/PaymentRegistrationServiceImpl.java
@@ -397,7 +397,7 @@ paymentHistoryRecordVo.setHappenTime(localDate); paymentHistoryRecordVo.setCurrentPaymentAmount(currentPaymentAmount); paymentHistoryRecordVo.setInvoiceAmount(invoiceAmount); amountTotal = amountTotal.add(currentDateTotal); amountTotal = paymentHistoryRecordVo.getInvoiceAmount().subtract(paymentHistoryRecordVo.getCurrentPaymentAmount()); paymentHistoryRecordVo.setPayableAmount(amountTotal); result.add(paymentHistoryRecordVo); } src/main/java/com/ruoyi/sales/pojo/SalesLedgerProduct.java
@@ -161,4 +161,10 @@ @ApiModelProperty(value = "临时未来票金额") @TableField(exist = false) private BigDecimal tempFutureTicketsAmount; @ApiModelProperty("登记人") private String register; @ApiModelProperty("登记日期") private String registerDate; } src/main/java/com/ruoyi/sales/service/impl/InvoiceLedgerServiceImpl.java
@@ -304,7 +304,7 @@ public IPage<InvoiceRegistrationProductDto> registrationProductPage(Page page, InvoiceRegistrationProductDto registrationProductDto) { IPage<InvoiceRegistrationProductDto> invoiceRegistrationProductDtoIPage = invoiceRegistrationProductMapper.invoiceRegistrationProductPage(page, registrationProductDto); if (registrationProductDto.getStatus()) { invoiceRegistrationProductDtoIPage.getRecords().removeIf(invoiceRegistrationProduct -> StringUtils.isEmpty(invoiceRegistrationProduct.getInvoiceNo())); invoiceRegistrationProductDtoIPage.getRecords().removeIf(invoiceRegistrationProduct -> !StringUtils.isEmpty(invoiceRegistrationProduct.getInvoiceNo())); } return invoiceRegistrationProductDtoIPage; } src/main/java/com/ruoyi/sales/service/impl/InvoiceRegistrationServiceImpl.java
@@ -75,6 +75,7 @@ invoiceRegistrationProduct.setInvoiceAmount(productDatum.getCurrentInvoiceAmount()); invoiceRegistrationProduct.setInvoiceNum(productDatum.getCurrentInvoiceNum()); invoiceRegistrationProduct.setSalesLedgerProductId(productDatum.getId().intValue()); invoiceRegistrationProductMapper.insert(invoiceRegistrationProduct); salesLedgerProductMapper.updateById(productDatum); // 新增一条开票台账数据 src/main/java/com/ruoyi/sales/service/impl/ReceiptPaymentServiceImpl.java
@@ -239,7 +239,7 @@ customerInteractionDto.setHappenTime(localDate); customerInteractionDto.setInvoiceAmount(invoiceAmount); customerInteractionDto.setReceiptAmount(currentReceiptAmount); amountTotal = amountTotal.add(currentDateTotal); amountTotal = customerInteractionDto.getInvoiceAmount().subtract(customerInteractionDto.getReceiptAmount()); customerInteractionDto.setUnReceiptAmount(amountTotal); result.add(customerInteractionDto); } src/main/java/com/ruoyi/sales/service/impl/SalesLedgerServiceImpl.java
@@ -91,6 +91,7 @@ return salesLedgerMapper.selectSalesLedgerList(salesLedgerDto); } @Override public SalesLedgerDto getSalesLedgerWithProducts(SalesLedgerDto salesLedgerDto) { // 1. 查询主表 SalesLedger salesLedger = salesLedgerMapper.selectById(salesLedgerDto.getId()); @@ -107,6 +108,8 @@ // 提供临时未开票数，未开票金额供前段计算 product.setTempnoInvoiceAmount(product.getNoInvoiceAmount()); product.setTempNoInvoiceNum(product.getNoInvoiceNum()); product.setRegister(SecurityUtils.getLoginUser().getUser().getNickName()); product.setRegisterDate(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))); } // 3.查询上传文件 @@ -505,10 +508,10 @@ // 2. 查询当天/公司已存在的序列号（与原逻辑一致） Long tenantId = SecurityUtils.getLoginUser().getTenantId(); if(null != tenantId){ if (null != tenantId) { //获取公司编号 SysDept sysDept = sysDeptMapper.selectDeptById(tenantId.longValue()); if(!ObjectUtils.isEmpty(sysDept)){ if (!ObjectUtils.isEmpty(sysDept)) { datePart = (StringUtils.isEmpty(sysDept.getDeptNick()) ? "" : sysDept.getDeptNick()) + datePart; } } src/main/resources/mapper/purchase/InvoicePurchaseMapper.xml
@@ -30,15 +30,114 @@ </where> </select> <select id="listVat" resultType="com.ruoyi.purchase.dto.VatDto"> SELECT DATE_FORMAT(il.invoice_date, '%Y-%m') AS month, ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount FROM invoice_ledger il LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id WHERE il.invoice_no IS NOT NULL AND invoice_type = '增专票' GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m') ORDER BY month; select * from (SELECT COALESCE(a1.month, a2.month) AS month, IFNULL(a1.tax_amount, 0) AS j_tax_amount, IFNULL(a2.x_tax_amount, 0) AS x_tax_amount FROM ( -- 第一个查询：来自 invoice_ledger 的税额 SELECT DATE_FORMAT(il.invoice_date, '%Y-%m') AS month, ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount FROM invoice_ledger il LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id WHERE il.invoice_no IS NOT NULL AND invoice_type = '增专票' GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m') ) a1 LEFT JOIN ( -- 第二个查询：来自 ticket_registration 的税额 SELECT DATE_FORMAT(a.issue_date, '%Y-%m') AS month, SUM(a.invoice_amount) AS x_tax_amount FROM ( SELECT DISTINCT pr.id, tr.issue_date, ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price, ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount FROM product_record pr LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id LEFT JOIN product_model pm ON pm.id = pr.product_model_id WHERE type = 2 AND tr.invoice_number IS NOT NULL ) a GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m') ) a2 ON a1.month = a2.month UNION ALL SELECT COALESCE(a1.month, a2.month) AS month, IFNULL(a1.tax_amount, 0) AS tax_amount, IFNULL(a2.x_tax_amount, 0) AS x_tax_amount FROM ( -- 第二个查询：来自 ticket_registration 的税额（反过来补全没有匹配到的） SELECT DATE_FORMAT(a.issue_date, '%Y-%m') AS month, SUM(a.invoice_amount) AS x_tax_amount FROM ( SELECT DISTINCT pr.id, tr.issue_date, ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price, ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount FROM product_record pr LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id LEFT JOIN product_model pm ON pm.id = pr.product_model_id WHERE type = 2 AND tr.invoice_number IS NOT NULL ) a GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m') ) a2 LEFT JOIN ( -- 第一个查询：来自 invoice_ledger 的税额 SELECT DATE_FORMAT(il.invoice_date, '%Y-%m') AS month, ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount FROM invoice_ledger il LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id WHERE il.invoice_no IS NOT NULL AND invoice_type = '增专票' GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m') ) a1 ON a1.month = a2.month WHERE a1.month IS NULL ORDER BY month )as a <where> <if test="month != null"> and a.month = date_format(#{month}) </if> </where> </select> <select id="listVat1" resultType="com.ruoyi.purchase.dto.VatDto"> # SELECT # DATE_FORMAT(il.invoice_date, '%Y-%m') AS month, # ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount # FROM invoice_ledger il # LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id # WHERE il.invoice_no IS NOT NULL # AND invoice_type = '增专票' # GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m') # ORDER BY month; # select DATE_FORMAT(a.issue_date, '%Y-%m'), sum(a.invoice_amount) as xTaxAmount # from (SELECT distinct pr.id, # tr.issue_date, # ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price, # ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), # 2) AS invoice_amount # FROM product_record pr # LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id # LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id # LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id # LEFT JOIN product_model pm ON pm.id = pr.product_model_id # WHERE type = 2 # and tr.invoice_number is not null) a # GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m') </select> </mapper> src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml
@@ -171,7 +171,7 @@ AND T1.payment_date >= date_format(#{params.paymentDateStart},'%Y-%m-%d') </if> <if test="params.paymentDateEnd != null and params.paymentDateEnd !='' "> AND T1.payment_date < date_format(#{params.paymentDateEnd},'%Y-%m-%d') AND T1.payment_date <= date_format(#{params.paymentDateEnd},'%Y-%m-%d') </if> </where> ORDER BY T1.payment_date,T1.create_time DESC src/main/resources/mapper/purchase/ProductRecordMapper.xml
@@ -32,10 +32,10 @@ and sl.supplier_name = #{c.supplierName} </if> <if test="c.createdAtStart != null and c.createdAtStart != ''"> and pr.created_at >= date_format(#{c.createdAtStart},'%Y-%m-%d') and pr.created_at >= date_format(#{c.createdAtStart},'%Y-%m-%d hh:mm:ss') </if> <if test="c.createdAtEnd != null and c.createdAtEnd != ''"> and pr.created_at < date_format(#{c.createdAtEnd},'%Y-%m-%d') and pr.created_at <= date_format(#{c.createdAtEnd},'%Y-%m-%d hh:mm:ss') </if> </select> </mapper> src/main/resources/mapper/sales/InvoiceRegistrationProductMapper.xml
@@ -111,7 +111,7 @@ AND T3.invoice_date >= date_format(#{invoiceRegistrationProductDto.invoiceDateStart}, '%Y-%m-%d') </if> <if test="invoiceRegistrationProductDto.invoiceDateEnd != null and invoiceRegistrationProductDto.invoiceDateEnd != ''"> AND T3.invoice_date < date_format(#{invoiceRegistrationProductDto.invoiceDateEnd}, '%Y-%m-%d') AND T3.invoice_date <= date_format(#{invoiceRegistrationProductDto.invoiceDateEnd}, '%Y-%m-%d') </if> </where> ORDER BY T1.create_time DESC src/main/resources/mapper/sales/ReceiptPaymentMapper.xml
@@ -190,57 +190,94 @@ AND T1.receipt_payment_date >= date_format(#{params.receiptPaymentDateStart},'%Y-%m-%d') </if> <if test="params.receiptPaymentDateEnd != null and params.receiptPaymentDateEnd != '' "> AND T1.receipt_payment_date < date_format(#{params.receiptPaymentDateEnd}, '%Y-%m-%d') AND T1.receipt_payment_date <= date_format(#{params.receiptPaymentDateEnd}, '%Y-%m-%d') </if> </where> ORDER BY T1.receipt_payment_date DESC </select> <!-- SELECT--> <!-- *--> <!-- FROM--> <!-- (--> <!-- SELECT--> <!-- receipt_payment_amount AS receiptAmount,--> <!-- receipt_payment_date AS happenTime,--> <!-- 0 AS type,--> <!-- 0 AS invoiceAmount--> <!-- FROM--> <!-- receipt_payment--> <!-- WHERE--> <!-- invoice_ledger_id IN (--> <!-- SELECT--> <!-- id--> <!-- FROM--> <!-- invoice_ledger--> <!-- WHERE--> <!-- invoice_registration_product_id IN (--> <!-- SELECT--> <!-- id--> <!-- FROM--> <!-- invoice_registration_product--> <!-- WHERE--> <!-- sales_ledger_id IN ( SELECT id FROM sales_ledger WHERE customer_id = #{customerId} ))) UNION--> <!-- SELECT--> <!-- 0 AS receiptAmount,--> <!-- invoice_date AS happenTime,--> <!-- 1 AS type,--> <!-- invoice_total AS invoiceAmount--> <!-- FROM--> <!-- invoice_ledger--> <!-- WHERE--> <!-- invoice_registration_product_id IN (--> <!-- SELECT--> <!-- id--> <!-- FROM--> <!-- invoice_registration_product--> <!-- WHERE--> <!-- sales_ledger_id IN ( SELECT id FROM sales_ledger WHERE customer_id = #{customerId} ))--> <!-- AND invoice_date IS NOT NULL--> <!-- ) T1--> <!-- ORDER BY--> <!-- T1.happenTime ASC--> <select id="customerInteractions" resultType="com.ruoyi.sales.dto.CustomerInteractionDto"> SELECT * rp.receipt_payment_amount AS receiptAmount, rp.receipt_payment_date AS happenTime, 0 AS type, 0 AS invoiceAmount FROM ( SELECT receipt_payment_amount AS receiptAmount, receipt_payment_date AS happenTime, 0 AS type, 0 AS invoiceAmount FROM receipt_payment WHERE invoice_ledger_id IN ( SELECT id FROM invoice_ledger WHERE invoice_registration_product_id IN ( SELECT id FROM invoice_registration_product WHERE sales_ledger_id IN ( SELECT id FROM sales_ledger WHERE customer_id = #{customerId} ))) UNION SELECT 0 AS receiptAmount, invoice_date AS happenTime, 1 AS type, invoice_total AS invoiceAmount FROM invoice_ledger WHERE invoice_registration_product_id IN ( SELECT id FROM invoice_registration_product WHERE sales_ledger_id IN ( SELECT id FROM sales_ledger WHERE customer_id = #{customerId} )) AND invoice_date IS NOT NULL ) T1 ORDER BY T1.happenTime ASC receipt_payment rp WHERE EXISTS ( SELECT 1 FROM invoice_ledger il JOIN invoice_registration_product irp ON il.invoice_registration_product_id = irp.id JOIN sales_ledger sl ON irp.sales_ledger_id = sl.id WHERE sl.customer_id =22 AND rp.invoice_ledger_id = il.id ) UNION ALL SELECT 0 AS receiptAmount, il.invoice_date AS happenTime, 1 AS type, il.invoice_total AS invoiceAmount FROM invoice_ledger il JOIN invoice_registration_product irp ON il.invoice_registration_product_id = irp.id JOIN sales_ledger sl ON irp.sales_ledger_id = sl.id WHERE sl.customer_id = 22 AND il.invoice_date IS NOT NULL ORDER BY happenTime ASC; </select> <select id="receiptPaymentHistoryListNoPage" resultType="com.ruoyi.sales.dto.ReceiptPaymentDto">
