php - 自动增加发票号码，每月重置为零

Question

我遇到了这个问题，我不知道如何创建发票编号。我希望它像这样工作:

1. 我从发票表中下载所有已付款的发票，即付款状态正常的发票。
2. 我从invoice_order下载最大的数字，添加+1并创建下一个数字，这意味着付款=OK的发票号码
3. 将新发票编号保存在发票/invoice_order 表中
4. 每次有新的已付款发票时都会这样，以便在给定月份内保持连续性

您对如何实现这项工作有什么想法吗？

    $year = date('Y'); 
    $month = date('m');
    $curent_date = date('Y-m-d');           

    $ask = $conn->createCommand("SELECT * FROM invoices WHERE payment = 'OK' "
            . "AND invoice_month=$month AND invoice_year=$year");
    $data = $ask->query();  

    while (($row = $data->read()) !== FALSE) { 
        if($row['invoice_suffix'] != 'KOR') {

                echo $row['ID'].'<br>';  
        }          



    } 

Answer 1

理解这个问题有点困难，但我认为您希望发票在支付后收到连续的 order_number a) 无论何时支付或 b)数字每月重置。

如果A(每个invoice_order都是唯一的)

UPDATE invoices t1,
       (SELECT MAX(invoice_order)+1 as x FROM invoices WHERE payment = 'OK') t2
SET t1.payment = 'OK', t1.invoice_order = t2.x
WHERE t1.id = INVOICE_TO_BE_UPDATED;

如果B(每个月都有一组新的重复订单号)

UPDATE invoices t1,
       (SELECT month, year, MAX(invoice_order)+1 as x FROM invoices WHERE payment = 'OK' GROUP BY month, year) t2
SET t1.payment = 'OK', t1.invoice_order = t2.x
WHERE t1.id = INVOICE_TO_BE_UPDATED AND t1.month = t2.month AND t1.year = t2.year;

在这里摆弄:https://www.db-fiddle.com/f/ccS723rK7vCjdJBdDihjj6/0