发票号码应从每个金融年份的1开始 [英] Invoice no should starting with 1 for every finacial year

问题描述

我有一个应用程序，其发票号为.invoice文本框，其自动增量值初始为1。现在我想要每个财政年度开始新的发票号码。



我尝试过的事情：

I have one application having invoice number.invoice textbox having auto increment value initial as "1". Now I want invoice number should be started fresh for every financial year.

What I have tried:

private void FillInvoiceID()
    {
        string con = ConfigurationManager.AppSettings["FilePath"].ToString();
        OleDbConnection c1 = new OleDbConnection(con);
        if (c1.State == ConnectionState.Open)
        {
            c1.Close();
        }
        c1.Open();
        string retInvoiceIDQuery = "SELECT Max(INVOICENO) as INVOICENO FROM TAXINVOICE";
        OleDbCommand commd = new OleDbCommand(retInvoiceIDQuery, c1);
        OleDbDataAdapter datadp = new OleDbDataAdapter(commd);
        DataTable datset = new DataTable();
        datadp.Fill(datset);
        if (datset.Rows[0][0] != null && string.IsNullOrEmpty(datset.Rows[0][0].ToString()))
        {
            datset.Rows[0][0] = "0";
        }

        invoice = Convert.ToInt32(datset.Rows[0]["INVOICENO"].ToString()) + 1;
        txtInvoice.Text = invoice.ToString();
        c1.Close();
    }

推荐答案

这是一个使用标识列和触发器的解决方案，理论上应该可以在多个用户环境（与读取当前最大发票号的解决方案不同）。

我有发票表，其中至少包含：

Here is a solution that uses an identity column and triggers and in theory should work well in a multi-user environment (unlike solutions that read the current max invoice number).
I have an invoice table that contains at least:

create table invoice
(
	id int identity(1,1),
	invoicedate date,
	invoiceNumber varchar(20)
)

请注意，我有一个 id 列，SQL将自动递增我，但我还有一个 invoiceNumber 列，其中包含从每年1开始的人为发票编号。



我还需要一个表来跟踪每年的最大 id 。

Note that I have an id column that SQL is going to automatically increment for me but I also have an invoiceNumber column which will contain the contrived invoice number starting at 1 each year.

I also need a table to track the maximum id per year.

create table invoiceYears
(
	invoiceYear int NOT NULL,
	maxid int
)

然后我在发票表上创建一个TRIGGER，以便在每次将发票插入表格时计算人工发票编号。

I then create a TRIGGER on the invoice table to "calculate" the contrived invoiceNumber each time an invoice is inserted into the table.

CREEATE TRIGGER trig ON [dbo].invoice 
AFTER INSERT
AS
	declare @invid int;
	declare @invyear int;
	declare @invnum varchar(20);


	select @invid=i.id from inserted i;	
	select @invyear=datepart(YEAR, i.invoicedate) from inserted i;	

	if exists (SELECT TOP 1 invoiceYear FROM invoiceYears WHERE invoiceYear = @invyear)
	BEGIN
		UPDATE invoiceYears set maxid = @invid WHERE invoiceYear = @invyear
	END
	ELSE
	BEGIN
		INSERT INTO invoiceYears values (@invyear, @invid)
	END

	UPDATE inv SET invoiceNumber = 'ABC-' + CAST(@invyear AS VARCHAR(4)) + '-' + CAST(inv.id - ISNULL(IY.maxid, 0) AS varchar(20))
	FROM invoice inv
	LEFT OUTER JOIN invoiceYears IY ON IY.invoiceYear = @invyear - 1
	WHERE inv.id = @invid

GO

请注意，它会在此表中插入一个新行今年的变化。如果您的财政年度没有从每年的1月1日开始，您将需要用财务年度的计算替换 DATEPART 。



其他注意事项：

- 因为我使用了触发器，所以这不是在高容量场景中使用的最高性能方法。

- 如果你跳过一年它将无效 - 除非你手动更新 invoiceYears 表

- 我通常会离开将发票编号格式化为表示层 - 如果从触发器中删除它，那么它在插入时执行效果更好，读取时只需要额外的额外开销。

- 请参阅上面关于事务的Tomas的注释和锁 - 我没有把这些放到解决方案中（为了保持一个简单的例子）

Note that it will insert a new row into this table at the change of the year. If your financial year does not start on 1st January of each year you will need to replace the DATEPART with the calculation of the financial year.

Other things to note:
- Because I've used a trigger then this isn't the most performant method to use in a high volume scenario.
- It won't work if you "skip" a year - unless you manually update the invoiceYears table
- I would normally leave the formatting of the invoice number to the presentation layer - if this is removed from the trigger then it performs better on inserts with only a minor extra overhead on reads.
- See the comments from Tomas above regarding transactions and locks - I haven't put these into the solution (for the sake of keeping as a simple example)

这篇关于发票号码应从每个金融年份的1开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！