Generate sequential number within one month

Question

How to generate sequential numbers consisting of year, month, 5-digit sequence. where the digits are zero when changing the month?

Example:

14 // ANO
11 // MÊS
00000 // SEQUÊNCIA QUE AO MUDAR O MÊS ZERA

Staying: 141100000..141100001..141100002

When changing the month: 141200000..141200001..141200002

These numbers are assigned to a service order and saved in DB, obviously they will be unique.

My job is like this:

function generateNumber(){
     return date('ym').'00000'; //141100000
}

Answer 1

A possible solution for your problem would be to create a sequence in the database and reset it with a database check at the beginning of each month.

That way your code would look like this:

function generateNumber(){
    // obtem o numero da sequencia do banco de dados
    $proximoNumeroSequencia = $banco->proximoNumeroSequencia('nome_sequencia');

    return date('ym') . $proximoNumeroSequencia; //141100000
}

In banks like Oracle and Postgresql it is easy to do this using the command CREATE SEQUENCE from the database to create the sequence, the command nextval to get the next sequence value and use the native Scheduler to reset the sequence at the beginning of each month.

Answer 2

I wrote an algorithm that generates the id increment if there is already a case in the database for the current month and year - otherwise it generates the first id.

public function geraId()
{
    $mysqli = new mysqli("localhost", "login", "senha", "db");
    $result = $mysqli->query("
        SELECT id
        FROM tabela
        ORDER BY id DESC
        LIMIT 1", MYSQLI_USE_RESULT));
    if (sizeof($result) == 1) {
        $lastId = $result[0]["id"];
        $lastIdYear = substr($lastId, 0, 2);
        $lastIdMonth = substr($lastId, 2, 2);
        if ($lastIdYear == date('Y') && $lastIdMonth == date('m')) {
            $id = substr($lastId, 4, 5);
            $id++;

            return $id;
        }
    }
    $id = date('Y-m') . '00001';

    return $id;
}

Answer 3

You can extract the number of the last service order and generate a new one, making a query that returns the largest number of that month and add one more, if the query does not return nothing means that it is the first OS, as this does not change can fix the code in another function.

<?php
function conectarBanco(){
    return new mysqli('localhost', 'usuario', 'senha', 'base');
}

function primeiraOrdemMes(){
    return date('ym'). '00001';
}

function novaOrdemServico(){
    $db = conectarBanco();
    $sql = 'SELECT max(numero_ordem_servico) as ultimo_id FROM ordem
            WHERE month(data_entrada) = month(now()) ';

    $result = $db->query($sql);

    //já existe uma ordem cadastrada no mês
    if($result === true){
        $ordem_servico = $result->fetch_assoc();
        return ++$ordem_servico['ultimo_id'];
    }else{
        return primeiraOrdemMes();
    }
}

$nova_ordem = novaOrdemServico();