service/src/main/java/com/ruoyi/cwgl/domain/vo/ReceivableBillAccountAnalysisVo.java
@@ -75,7 +75,7 @@ /** * 还款金额 */ @Excel(name = "还款金额") @Excel(name = "还款金额",scale = 2) private BigDecimal latestRepaymentAmount; /** @@ -88,13 +88,13 @@ /** * 开票金额 */ @Excel(name = "开票金额") @Excel(name = "开票金额",scale = 2) private BigDecimal totalInvoiceAmount; /** * 未开票金额 */ @Excel(name = "未开票金额") @Excel(name = "未开票金额",scale = 2) private BigDecimal unInvoicedAmount; /** @@ -106,7 +106,7 @@ /** * 尚欠金额 */ @Excel(name = "尚欠金额") @Excel(name = "尚欠金额",scale = 2) private BigDecimal pendingAmount; /** @@ -118,6 +118,6 @@ /** * 逾期利息 */ @Excel(name = "逾期利息") @Excel(name = "逾期利息",scale = 2) private BigDecimal overdueInterest; } service/src/main/java/com/ruoyi/cwgl/domain/vo/ReceivableBillAgingAnalysisVo.java
@@ -74,13 +74,13 @@ * 含暂估应收账款余额 * 已确认应收账款金额 */ @Excel(name = "含暂估应收账款余额") @Excel(name = "含暂估应收账款余额",scale = 2) private BigDecimal totalAmount; /** * 已确认应收账款金额 */ @Excel(name = "已确认应收账款金额") @Excel(name = "已确认应收账款金额",scale = 2) private BigDecimal confirmedAmount; public BigDecimal getConfirmedAmount() { @@ -98,13 +98,13 @@ /** * 已收金额 */ @Excel(name = "已收金额") @Excel(name = "已收金额",scale = 2) private BigDecimal receivedAmount; /** * 待收金额 */ @Excel(name = "待收金额") @Excel(name = "待收金额",scale = 2) private BigDecimal pendingAmount; @@ -138,50 +138,50 @@ /** * 30天内到期金额 */ @Excel(name = "30天内到期金额", sort = 22) @Excel(name = "30天内到期金额",scale = 2) private BigDecimal dueIn30Days; /** * 逾期1~30天金额 */ @Excel(name = "逾期1~30天金额", sort = 24) @Excel(name = "逾期1~30天金额",scale = 2) private BigDecimal overdue1To30Days; /** * 逾期31~60天金额 */ @Excel(name = "逾期31~60天金额", sort = 25) @Excel(name = "逾期31~60天金额" ,scale = 2) private BigDecimal overdue31To60Days; /** * 逾期61~90天金额 */ @Excel(name = "逾期61~90天金额", sort = 26) @Excel(name = "逾期61~90天金额",scale = 2) private BigDecimal overdue61To90Days; /** * 逾期91~180天金额 */ @Excel(name = "逾期91~180天金额", sort = 27) @Excel(name = "逾期91~180天金额",scale = 2) private BigDecimal overdue91To180Days; /** * 逾期181~365天金额 */ @Excel(name = "逾期181~365天金额", sort = 28) @Excel(name = "逾期181~365天金额",scale = 2) private BigDecimal overdue181To365Days; /** * 逾期1年以上金额 */ @Excel(name = "逾期1年以上金额", sort = 29) @Excel(name = "逾期1年以上金额",scale = 2) private BigDecimal overdueOver1Year; /** * 逾期天数 */ @Excel(name = "逾期天数", sort = 30) @Excel(name = "逾期天数") private Integer overdueDays; /** service/src/main/resources/mapper/cwgl/ReceivableBillManagementMapper.xml
@@ -388,9 +388,36 @@ <!-- 查询应收账单账龄分析列表 --> <select id="selectReceivableBillAgingAnalysisList" parameterType="com.ruoyi.cwgl.domain.ReceivableBillManagement" resultMap="ReceivableBillAgingAnalysisVoResult"> <include refid="selectReceivableBillManagementVo"/> SELECT thisTab.*, rib_latest.latest_invoice_time as latestInvoiceTime, COALESCE(rib_total.total_invoice_amount, 0) as totalInvoiceAmount, CASE WHEN thisTab.pending_amount > COALESCE(rib_total.total_invoice_amount, 0) THEN thisTab.pending_amount - COALESCE(rib_total.total_invoice_amount, 0) ELSE 0 END as unInvoicedAmount FROM receivable_bill_management thisTab -- 获取最新开票日期 LEFT JOIN ( SELECT head_id, MAX(invoice_time) as latest_invoice_time FROM receivable_invoice_business WHERE status = 1 GROUP BY head_id ) rib_latest ON thisTab.id = rib_latest.head_id -- 获取开票总金额 LEFT JOIN ( SELECT head_id, SUM(invoicing_amount) as total_invoice_amount FROM receivable_invoice_business WHERE status = 1 GROUP BY head_id ) rib_total ON thisTab.id = rib_total.head_id <where> <if test="systemNo != null and systemNo != ''"> and thisTab.system_no = #{systemNo}</if> <if test="customerName != null and customerName != ''"> and thisTab.customer_name like concat('%', #{customerName}, '%')</if> </where> ORDER BY customer_name, bill_due_date @@ -528,19 +555,30 @@ -- 获取最新还款信息 LEFT JOIN ( SELECT bill_id, receipt_date as latest_repayment_date, receipt_amount as latest_repayment_amount FROM receivable_bill_settlement_detail WHERE deleted = 0 AND (bill_id, receipt_date) IN ( SELECT bill_id, MAX(receipt_date) SELECT r1.bill_id, r1.receipt_date as latest_repayment_date, r1.receipt_amount as latest_repayment_amount FROM receivable_bill_settlement_detail r1 INNER JOIN ( SELECT bill_id, MAX(receipt_date) as max_receipt_date FROM receivable_bill_settlement_detail WHERE deleted = 0 GROUP BY bill_id ) r2 ON r1.bill_id = r2.bill_id AND r1.receipt_date = r2.max_receipt_date WHERE r1.deleted = 0 -- 如果同一天有多条记录，取金额最大的那条 AND r1.receipt_amount = ( SELECT MAX(receipt_amount) FROM receivable_bill_settlement_detail r3 WHERE r3.bill_id = r1.bill_id AND r3.receipt_date = r1.receipt_date AND r3.deleted = 0 ) ) rbsd_latest ON thisTab.id = rbsd_latest.bill_id <where> <if test="systemNo != null and systemNo != ''"> and thisTab.system_no = #{systemNo}</if> <if test="customerName != null and customerName != ''"> and thisTab.customer_name like concat('%', #{customerName}, '%')</if> </where> ORDER BY thisTab.customer_name, thisTab.bill_due_date </select>
