Numeração sequencial com ano civil

[hardcore]

Viva pessoal!

Precisava de uma sugestão vossa. O que se passa é o seguinte. Tenho um campo numa base de dados do tipo varchar e quero lá meter uma numeração do tipo: 001/2008, em que os três primeiros dígitos do lado esquerdo são sequenciais e os do lado direito pertencem ao ano civil.

O que eu queria era que esse numero sequencial avançasse automaticamente sem mexer  no ano civil visto que este só muda assim que mudamos de ano.

por exemplo:

Ano 2008:

001/2008

002/2008

003/2008

Ano 2009:

001/2009

002/2009

003/2009

Pensei em ir buscar o ano civil à hora do sistema mas não faço a mínima ideia como fazer andar os outros números.

Alguma sugestão? Serão todas bem vindas 🙂

[jintonico]

Tenta isto:

1 - Transforma em string

2 - Parte a string pelo separador "/"

3 - converte a parte q te interessa para inteiro, ou seja "001" passa para 1

4 - incrementa isso

5 - transforma em string

6 - ve o comprimento da string (para o exemplo deve ter um comprimento de 1)

(reconstruir o valor original)

7 - se o comprimento for 1 adiciona-lhe dois 0 (ficas com 002)

8 - se comprimento for 2 adiciona-lhe um 0 (ou seja se tiveres 23 ficas com 023)

9 - junta este resultado ao ano

Penso que assim desenrasque

[DanielAmorim]

Porque não usas dois campos do tipo INT?

O campo do ano podias colocar qualquer coisa do tipo:

ano INT DEFAULT DATEPART(yyyy, GETDATE()) 

No outro campo guardavas o numero sequencial. Só uma coisa... se não tiver de ser mesmo sequencial podes indicar que o campo é IDENTITY, caso contrário não te esqueças de colocar a leitura e incremento do campo numa transacção, senão corres o risco de teres dois valor iguais 🙂

Esta solução retira-te o tratamento do campo em camadas superiores da aplicação e torna-te os acesso à Base de dados mais rápidos

[hardcore]

Tenta isto:

1 - Transforma em string

2 - Parte a string pelo separador "/"

3 - converte a parte q te interessa para inteiro, ou seja "001" passa para 1

4 - incrementa isso

5 - transforma em string

6 - ve o comprimento da string (para o exemplo deve ter um comprimento de 1)

(reconstruir o valor original)

7 - se o comprimento for 1 adiciona-lhe dois 0 (ficas com 002)

8 - se comprimento for 2 adiciona-lhe um 0 (ou seja se tiveres 23 ficas com 023)

9 - junta este resultado ao ano

Penso que assim desenrasque

Eu por acaso já tinha pensado dessa forma, mas como é que eu vou ver à base de dados qual é o ultimo? Se eu tiver os seguintes dados:

082/2008

002/2009

003/2009

Se colocar uma função máximo neste exemplo é sempre devolvido o 082/2008 e não o 003/2009 :\

Porque não usas dois campos do tipo INT?

O campo do ano podias colocar qualquer coisa do tipo:

ano INT DEFAULT DATEPART(yyyy, GETDATE()) 

No outro campo guardavas o numero sequencial. Só uma coisa... se não tiver de ser mesmo sequencial podes indicar que o campo é IDENTITY, caso contrário não te esqueças de colocar a leitura e incremento do campo numa transacção, senão corres o risco de teres dois valor iguais 🙂

Esta solução retira-te o tratamento do campo em camadas superiores da aplicação e torna-te os acesso à Base de dados mais rápidos

eu posso ter um campo sequencial como IDENTITY à parte... mas se tiver dois campos um um deles tem de ser sequencial até acabar o ano civil. A partir de 1 de Janeiro a contagem tem de voltar ao número 1

[DanielAmorim]

Tinha dito que Identity era uma hipótese e não é é... sorry

O segundo campo que refiro tem mesmo de ser um INT sequencial, para isso tens te fazer o seguinte numa transacção:

SE  Ver se já existe algum valor para o ano corrente

ENTÃO O valor é 1

SENÃO É o resultado do maior valor com 1

[Betovsky]

Isto não devia de estar na secção de Base de Dados?

Em relação ao problema em si. Aconselhava a teres 2 campos na base de dados, um para a sequência e outro pro ano. Depois terias um 3º campo que seria uma Computed Column (nas BDs que não suportam colunas virtuais podes sempre simular algo do género através de triggers) em que esta coluna iria conter a representação textual da concatenação dos teus 2 outros campos.

Assim mesmo quando quisesses ordenar seria muito simples, já que ordenavas primeiro pelo campo correspondente ao ano civil e depois pelo campo correspondente à numeração sequencial.

[jintonico]

Eu por acaso já tinha pensado dessa forma, mas como é que eu vou ver à base de dados qual é o ultimo? Se eu tiver os seguintes dados:

082/2008

002/2009

003/2009

Se colocar uma função máximo neste exemplo é sempre devolvido o 082/2008 e não o 003/2009 :\

eu posso ter um campo sequencial como IDENTITY à parte... mas se tiver dois campos um um deles tem de ser sequencial até acabar o ano civil. A partir de 1 de Janeiro a contagem tem de voltar ao número 1

Aproveitanto o que o Betovsky disse, podes criar dois campos, um para o ano e outro para o valor... fazes um select a base de dados para te dar a valor:

select TOP 1 * from tabelaXPTO order by ANO desc , NUM desc

Em principio n deves ter problemas independentemente o tipo de campo na base de dados, (int, nvarchar, varchar) desde q te mantenhas fiel aos dados (2008 e sempre maior que 2007 em qq tipo de dados, e na numeração a mm coisa)

[hardcore]

Sim, tens razão Betovsky. Apenas postei aqui porque estou a desenvolver em C# e tinha a ideia de postar mais tarde a resolução caso alguém precisasse. Se quiserem podem mover 🙂

Achei interessante a tua ideia, não tinha pensado num terceiro campo com a junção dos dois campos. Isso fez-me pensar noutra coisa... talvez terei de alterar um pouco a base de dados visto que tenho o campo idêntico em duas tabelas diferentes e o valor deveria ser continuo... (o erro foi meu :$). Vou criar uma tabela e ligar as outras duas a essa tabela nova.

Vou tentar fazer qualquer coisa e depois digo-vos alguma coisa.

Obrigado a todos

[hardcore]

Viva pessoal, hoje fiz um pequeno programinha para resolver a tal situação da numeração. Aqui vai o código.

Tabela Contrato (idContrato, numero, ano, combinacao)

idContrato - integer
numero - integer
ano - varchar(4)
combinacao - varchar(8)

 

 //Conecção à base de dados
    private static string GetConnectionString()
    {

        return ConfigurationManager.ConnectionStrings["myDataBaseConnectionString"].ConnectionString;

    }

    MySqlConnection myConnection = new MySqlConnection(GetConnectionString());
    MySqlDataAdapter myDataAdapter;
    DataSet myDataSet;
    MySqlCommand myCommand;

    private void Numeracao()
    {
        string Ano = TextBox2.Text;
string Numero = TextBox1.text;

        if(myConnection.State.ToString().Equals("Open"))
            myConnection.Close();

        myConnection.Open();

        myCommand = new MySqlCommand("VerificarTeste", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("_ano", MySqlDbType.VarChar).Value = Ano;

        myCommand.ExecuteNonQuery();

        myDataAdapter = new MySqlDataAdapter(myCommand);
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet);
        DataRow myDataRow;
        myDataRow = myDataSet.Tables[0].Rows[0];
        //string result = myDataRow[0].ToString();


        if (myDataRow[0].ToString() == null)
        {
            Numero = "1";
            myConnection.Close();
        }
        else
        {
            int result = Convert.ToInt32(myDataRow[0].ToString()) + 1;
            Numero = result.ToString();
            myConnection.Close();
        }  
    }

    private void Combinacao()
    {
        string combinacao;
        if (TextBox1.Text.Length == 1)
        {
            combinacao = "00" + TextBox1.Text + "/" + TextBox2.Text;
            TextBox3.Text = combinacao.ToString();
        }
        if (TextBox1.Text.Length == 2)
        {
            combinacao = "0" + TextBox1.Text + "/" + TextBox2.Text;
            TextBox3.Text = combinacao.ToString();
        }
        if (TextBox1.Text.Length == 3)
        {
            combinacao = TextBox1.Text + "/" + TextBox2.Text;
            TextBox3.Text = combinacao.ToString();
        }
    }

    private void Gravar()
    {
        string Numero = TextBox1.Text;
        string Ano = TextBox2.Text;
        string Combinacao = TextBox3.Text;
        myConnection.Open();
        myCommand = new MySqlCommand("GravarTeste", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("?_numero", MySqlDbType.Int32).Value = Numero;
        myCommand.Parameters.Add("?_ano", MySqlDbType.VarChar).Value = Ano;
        myCommand.Parameters.Add("?_combinacao", MySqlDbType.VarChar).Value = Combinacao;
        myCommand.ExecuteNonQuery();
        myConnection.Close();
    }

    private void ContarAno()
    {
        string Ano = DateTime.Today.Year.ToString();
string Numero = TextBox1.Text;
        myConnection.Open();

        myCommand = new MySqlCommand("VerificarTesteContarAno", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.Add("_ano", MySqlDbType.VarChar).Value = Ano;

        myCommand.ExecuteNonQuery();

        myDataAdapter = new MySqlDataAdapter(myCommand);
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet);
        DataRow myDataRow;
        myDataRow = myDataSet.Tables[0].Rows[0];
        string result = myDataRow[0].ToString();

        if (result.ToString().Equals("0"))
        {
            Numero = "1";
            myConnection.Close();
        }
        else
        {
            Numeracao();
            myConnection.Close();
        }
        myConnection.Close();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextBox2.Text = DateTime.Today.Year.ToString();
        ContarAno();
Combinacao();
        //ContarAno();
    }

    protected void ButtonOk_Click1(object sender, EventArgs e)
    {
        Gravar();
        ContarAno();
Combinacao();
    }

    protected void ButtonLimpar_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
    }

Espero que seja útil a alguém que precise.

Um abraço.

Cumprimentos.

[DanielAmorim]

Porque os campos ano e combinacao são varchar? Não podiam ser integer? ?

Pergunto isto porque da forma que tens isso não poderás ter um idContrato maior que 999.

O campo combinacao não será informação redundante? Se tens guardado o ano e idContrato é fácil apresentar a combinacao...

Pelo que vi do teu código estas a desenvolver páginas ASP.Net. O atendimento de pedidos ASP.Net são feitos em paralelo sempre que possível, então com esse código poderás ter problemas de sincronismo 🙂 Podem acontecer coisas estranhas como teres números de sequência repetidos... Porque não passar a lógica de incremento do número sequencial para um procedimento armazenado?

[hardcore]

Porque os campos ano e combinacao são varchar? Não podiam ser integer? ?

Pergunto isto porque da forma que tens isso não poderás ter um idContrato maior que 999.

sim, isso foi o exemplo. na versão final deixei os campos em integer

O campo combinacao não será informação redundante? Se tens guardado o ano e idContrato é fácil apresentar a combinacao...

Pelo que dizes, acho que tens razão. Não havia grande necessidade de guardar esse campo combinação... Mas o que acontece é que eu tenho que apresentar esse campo combinação em tabelas.

Pelo que vi do teu código estas a desenvolver páginas ASP.Net. O atendimento de pedidos ASP.Net são feitos em paralelo sempre que possível, então com esse código poderás ter problemas de sincronismo 🙂 Podem acontecer coisas estranhas como teres números de sequência repetidos... Porque não passar a lógica de incremento do número sequencial para um procedimento armazenado?

Como assim? Eu vejo sempre primeiro se existe o ano civil. Se não existir começa a a partir do número 1 e por aí a diante... Se o ano muda volta a começar a numeração sequencial em 1.

Queres dar uma ideia desse tal procedimento armazenado?

Cumprimentos.

[DanielAmorim]

Humm... a chave primária da tabela é composta, ou seja, é constituída por numero e ano? Se sim não irás ter valores repetidos, mas podes apanhar com erros ao tentar inserir uma chave repetida. Se a chave foi o idContrato poderás ter valores repetidos...

Por exemplo neste excerto de código...

         if (myDataRow[0].ToString() == null)
        {
            Numero = "1";
            myConnection.Close();
        }
        else
        {
            int result = Convert.ToInt32(myDataRow[0].ToString()) + 1;
            Numero = result.ToString();
            myConnection.Close();
        }  

Imagina que chegam dois pedidos para a página... logo duas thread's a correr para cada pedido. A primeira thread consulta a base de dados e vê que ainda não existe nenhum registo para o ano corrente e entra no if. Mal entra no if ocorre uma comutação de thread e começa a correr a thread do segundo pedido. Esta segunda thread também vai ver que ainda não nenhum registo para este ano (a primeira thread ainda não escreveu...) e irá assumir o valor. Aqui tens uma situação que as coisas não irão correr bem...

A ideia do Store Procedure é ser responsável pela adição de um novo Contrato. Este procedimento seria executado no âmbito de uma transacção com o nível de isolamento correcto (Repeatable Read salvo erro). Partindo do pressuposto que a tabela Contrato é assim:

CREATE TABLE Contrato
(
idContrato INT IDENTITY CONSTRAINT pk_Contrato PRIMARY KEY,
numero INT,
ano INT DEFAULT datepart(yyyy, getdate())
)

Ficaria qualquer coisa assim:

CREATE PROCEDURE InsertContrato
AS
DECLARE @NewNum INT

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRANSACTION

SELECT @NewNum = COUNT(numero) FROM Contrato WHERE ano = datepart(yyyy, getdate())

IF(@NewNum = 0)	SET @NewNum = 1

INSERT INTO Contrato (numero) VALUES (@NewNum)

IF( @@ROWCOUNT = 0)
	BEGIN
		ROLLBACK
		RETURN @@ERROR 
	END
ELSE
	BEGIN
		COMMIT
		RETURN @@ROWCOUNT 
	END

[hardcore]

Percebi o teu ponto de vista em relação aos dois acessos em simultâneo, para ser sincero não tinha pensado nisso  ? Ando ainda muito no básico e se há alguma coisa que este fórum tem de bom é fazer-nos pensar como programadores  😄

Quanto ao procedimento, ele vai fazer praticamente o mesmo que o if em C# mas a nivel de base de dados, segundo o que eu percebi. Isso irá evitar esse tal problema..

Vou aplicar a tua sugestão num projecto à parte para perceber melhor a coisa.

Obrigadão! Assim que tiver alguma dúvida posto  😛

[DanielAmorim]

Com o Store Procedure ganhas mais uma coisa... a lógica da inserção fica na base de dados. Em C# apenas terás de chamar o procedimento, fica limpinho 😛