自动增加发票号码,每月重置为零

0kjbasz6  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(428)

我有这个问题,我不知道如何创建发票编号。我希望它像这样工作:
我从表invoices下载所有已付款的发票,那些付款状态为ok的发票。
我从invoice\u order中下载最高的数字,将其加上+1,然后创建下一个数字,这意味着那些payment=ok的发票数字
在发票/发票订单表中保存新的发票号
每次有新的付款发票时都是这样,所以在一个月内是连续的
你有什么办法使这项工作成功吗?

$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>';  
        }          

    }
fdbelqdn

fdbelqdn1#

这个问题有点难理解,但我想您希望发票能收到连续的发票 order_number 一旦支付,a)无论何时支付,或b)每月重置数字。
如果a(每个发票订单都是唯一的)

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

相关问题