Grab sequential ID based on last entered

Question

I got a problem that’s giving me trouble.

I have the following code:

$sql = $pdo->prepare ("SELECT cpf,idUser FROM `tblContacts` where cpf = '".$cpf."' limit 1");
$sql->execute();
$row = $sql->fetch();

if($row != null)
  {
    //  echo "SQL 01 ". $sql;
    $randomicId = $row->idUser;
  } 
  else
  {
    switch ($horaAtual) {
      case ($horaAtual > $hora1 && $horaAtual < $hora2): $periodo = 'manha'; break;
      case ($horaAtual > $hora2 && $horaAtual < $hora3): $periodo = 'tarde'; break;
      default: $periodo =  'manha';
    }

    $sql2 = "SELECT * FROM `tblUser` where grupo = 'vendas' and ativo = 1 and periodo = '".$periodo."' ORDER BY RAND() LIMIT 1";
    $stmt = $pdo->query($sql2); 
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if($row['idUser'] != null)
    {
      //echo "SQL 02 ". $sql2;
      $randomicId = $row['idUser'];
    }
  }

The code has 2 parts:

When a customer makes a registration, I take a user ID and assign it to this registration:

id   nome_cliente  ......  idUsuario

1    fulano                   2

If the so-and-so client makes another registration in the future, the system checks if he has any equal registration in the bank, if he has, he uses the same user ID as the user.

If you do not have any register in the bank, the system goes through a process of checking to get the ID of some user:

The system takes the current time:

• if it is before 15h, it will make a query in the bank and select users from the morning period.

• if it is after 15h, it will make a query in the bank and select users of the afternoon period.

Table users:

id   nome      periodo  ...
1    siclano    tarde

The system, after checking the period, picks up a random ID with the RAND() limit 1 .

So the code is working.

But, taking the random ID, the following problem is happening: there are even more registrations for 1 user than for another.

So, I wanted to, rather than catch random, assign a sequential ID.

Checks the time, inserts the ID in sequence (Obs: Ids vary).

IdUsuario : 2,34,45 

cadastro 1  - idUsuario : 2
cadastro 2  - idUsuario : 34
cadastro 3  - idUsuario : 45
cadastro 4  - idUsuario : 2
....

Someone can help make it that way?

Answer 1

To do this control you need to have a field where control was the last user used. I suggest doing a normalization in your bank by changing the field periodo passing the data to a table and storing only the ID of these records in the user table, and storing in this table which was the last user used, so you will have a queue for each turn. Follow an example.

Basic modeling:

Turns

CREATE TABLE `turnos`(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(45),
  lastfunc INT
);

INSERT INTO turnos (nome) VALUES ('manhã'), ('tarde');

 _________________________
| id | nome  | lastfunc   |
|____|_______|____________|
| 1  | manhã |            |
|____|_______|____________|
| 2  | tarde |            |
|____|_______|____________|
2 registros

The column lastfunc will be used to control the sequence of users/employees.

Employees

CREATE TABLE `funcionarios`(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(45),
  turno_id INT NOT NULL,
  FOREIGN KEY (turno_id) REFERENCES turnos(id)
);

INSERT INTO funcionarios (nome, turno_id)  VALUES
('João', 1), ('Maria', 1), ('José', 2), ('Joana', 2);

 __________________________
| id | nome     | turno_id |
|____|__________|__________|
| 1  | João     | 1        |  ---
|____|__________|__________|    |-- Turno da Manhã
| 2  | Maria    | 1        |  ---
|____|__________|__________|
| 3  | José     | 2        |  ---
|____|__________|__________|    |-- Turno da Tarde
| 4  | Joana    | 2        |  ---
|____|__________|__________|
4 registros

Customers

CREATE TABLE `clientes`(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(45),
  funcionario_id INT,
  FOREIGN KEY (funcionario_id) REFERENCES funcionarios(id)
);

 ____________________________
| id | nome | funcionario_id |
|____|______|________________|
0 registro

Code and Logic.

The logic is that each time we register a client, we look for an employee to associate it with their record, the rule of employee search is as follows:

• If registered customer search existing in the register
• If there is next to the queue search based on the time
• If there is no next, search for the first in line

Based on this rule, I built the following code: (Detail in the comments)

// Altere CPF e Variável $turno para testes
$novoCliente = Array(
   'cpf' => '66666666668',
   'nome' => 'Fulano Pereira Pinto',
   'funcionario_id' => null
);

$turno = 2; // 1 - Manhã | 2 - Tarde

// Define variável para armazenar funcionário
$funcId = null;


// Consulta se o cliente tem cadastro com base no CPF e pega o funcionário associado
$stmt = $con->prepare("SELECT funcionario_id FROM clientes WHERE cpf = :cpf"); 
// Executa a query
$stmt->execute( Array(':cpf' => $novoCliente['cpf']) ); 

// Verifica se teve resultado, se sim armazena ID do funcionário
if ($stmt->rowCount() > 0)
   $funcId = $stmt->fetch(PDO::FETCH_ASSOC)['funcionario_id'];


// Se não tem cadastro
if (is_null($funcId)){

   // Pega o próximo funcionario da sequencia com base no turno
   // A query busca o funcionario que tem um ID MAIOR do que o ID que estiver
   // na tabela de turno no campo `lastfunc`, ou seja o próximo
   $sql  = "SELECT fu.id as id
            FROM funcionarios as fu
               INNER JOIN turnos as tu ON tu.id = fu.turno_id
            WHERE 
               fu.id > tu.lastfunc AND
               fu.turno_id = :turno
            ORDER BY fu.id ASC LIMIT 1";
   $stmt = $con->prepare($sql);
   $stmt->execute( [':turno' => $turno] ); // Executa a query

   // Se teve resultado armazena o ID do funcionário
   if ($stmt->rowCount() > 0)
      $funcId = $stmt->fetch(PDO::FETCH_ASSOC)['id'];
}

// Se a regra anterior não teve resultado é porque
// chegou no último da fila e não tem próximo
// ou porque não tem nenhum id no campo `lastfunc` da tabela `turno`
// Então executa a regra seguinte
if (is_null($funcId)){

   // Seleciona o primeiro funcionario
   $sql  = "SELECT id FROM funcionarios ORDER BY id ASC LIMIT 1";
   $stmt = $con->prepare($sql); 
   $stmt->execute(); // Executa a query

   // Se teve resultado armazena o ID do funcionário
   if ($stmt->rowCount() > 0)
      $funcId = $stmt->fetch(PDO::FETCH_ASSOC)['id'];

  // Se não houver funcionar é porque não existe funcionário cadastrado
}

if (is_null($funcId)){
   // Se não pegou nenhum funcionário interrompe a execução do código
   exit('Erro: Nenhum funcionário pode ser associado ao cliente. Cadastro interrompido.');
} else {
   // Se encontrou algum funcionário atualiza os dados do cliente
   $novoCliente['funcionario_id'] = $funcId;
}

// Insere o funcionário no banco com os dados
$stmt = $con->prepare("INSERT INTO clientes (cpf, nome, funcionario_id) VALUES (:cpf, :nome, :funcionario)"); 
// Executa a query
$res = $stmt->execute( 
   Array(
      ':cpf' => $novoCliente['cpf'],
      ':nome' => $novoCliente['nome'],
      ':funcionario' => $novoCliente['funcionario_id']
   ) 
); 


if ($res){
   // Essa parte é muito IMPORTANTE
   // Tens que armazenar o ID do funcionário utilizado no turno dele,
   // para que a fila ande, pois é com base nesse campo que verificamos a 
   // posição da fila e então pegamos o próximo.
   $stmt = $con->prepare("UPDATE turnos SET lastfunc = :lfunc WHERE id = :turno"); 
   $res = $stmt->execute( 
      Array(
         ':lfunc' => $funcId,
         ':turno' => $turno
      ) 
   );
}


echo $res ? 'Sucesso' : 'Erro';

Upshot

Obs.: The first record came out with no employee id because I forgot to update the variable $novoCliente['funcionario_id'] before the INSERT customer’s.