DEV Community

Juanan Ruiz

Posted on Aug 6, 2018 • Originally published at galatar.com on Jul 25, 2018

Generar código autonumérico con reinicio anual

#php #sql #spanish

Siempre aconsejo que las tablas de una base de datos relacional tengan un campo clave autonumérico de tipo entero, que sea totalmente independiente de cualquier otro campo que contenga la tabla y de cualquier otro campo que forme parte de la “lógica de negocio”. Por muy inmutable que te pueda parecer cualquier otro dato, como el DNI, la matrícula de un coche o el código de una factura, es preferible tener una clave adicional que identifique ese registro de manera independiente en la base de datos, que “no tenga que ver nada con nadie”.

Hay veces que además de ese campo necesitarás generar una clave única que vaya en parte codificada y en parte autogenerada. Un ejemplo típico suele ser el código de una factura. La mayoría de las empresas utilizan algún tipo de código en el que aparece el año y un numeral que se va incrementando a lo largo del año para volver a cero al año siguiente.

Imagina que tienes que generar una factura que sigue una numeración parecida a esta: “F-0001/2018”. Vas a ver como generar una consulta SQL que encuentre el código de factura más alto del año y te devuelva el siguiente. Sería estupendo que también devolviera el número correcto si es la primera factura del año o que le pudiéras pedir el siguiente número de factura de cualquier año concreto. No es mucho pedir ¿no?

Digamos que la tabla de facturas almacena el número de factura en un campo ‘codigo’ y también tiene un campo ‘fecha_emision’ para almacenar la fecha de emisión de la factura. Para simplificar la consulta vamos a generar de momento las facturas para el año actual.

SELECT CONCAT('F-',LPAD(SUBSTR(codigo,3,4)+1,4,'0'), '/',   
 YEAR(NOW())) AS codigo   
 FROM factura WHERE YEAR(fecha_emision) = YEAR(NOW())  
UNION  
SELECT CONCAT('F-0001', '/', YEAR(NOW())) AS codigo  
ORDER BY codigo DESC LIMIT 1;

La primera consulta, antes del UNION, busca el código más alto del año actual y te lo devuelve sumándole “1” y rellenando con ceros a la izquierda.

Si todavía no hay registros para el año en curso, entonces esta primera consulta no devuelve nada pero la segunda parte, después del UNION viene al rescate y genera la primera factura del año.

Si necesitas generar registros para años anteriores solo tienes que modificar esta consulta pasándole el año como parámetro en lugar de las expresiones YEAR(NOW())

Otra forma parecida de hacerlo sería con esta consulta que no utiliza UNION

SELECT CASE WHEN COUNT(*) > 0 THEN   
CONCAT('F-',LPAD(SUBSTR(codigo,3,4)+1,4,'0'), '/', YEAR(NOW()))  
ELSE   
CONCAT('F-0001', '/', YEAR(NOW()))  
END AS codigo   
FROM factura WHERE YEAR(fecha_emision) = YEAR(NOW()) 
ORDER BY codigo DESC LIMIT 1;

Desde tu código PHP

Desde PHP se podría ejecutar primero esta consulta y con el “código” resultante guardar el registro completo. O, mejor incluso, si hay que rellenar algo en un formulario, ejecutar esto en el script PHP que va a pintar el formulario y dejarlo ya impreso como un “input disabled” en el propio formulario. Luego se enviará con el resto de campos para grabar en la base de datos.

<?php  
global $dbh;  

$query = $dbh->prepare(  
    "SELECT CONCAT('F-',LPAD(SUBSTR(codigo,3,4)+1,4,'0'), '/',   
                YEAR(NOW())) AS codigo   
                FROM factura WHERE year(fecha_emision) = YEAR(NOW())  
              UNION  
                SELECT CONCAT('F-0001', '/', YEAR(NOW())) AS codigo  
              ORDER BY codigo DESC LIMIT 1;  
    ");  
// Establece la forma de devolver los resultados, en este caso devolverá un array asociativo  
$query->setFetchMode(PDO::FETCH_ASSOC);  
$query->execute();  

echo "<!DOCTYPE html><html lang='en'><head><meta charset='UTF-8'></head><body>";  
while ($row = $query->fetch()){  
    echo '<form method="post" action="index.php?p=factura_grabar">';  
    echo '<input type="text" name="codigo" value="' . $row["codigo"] . '">';  
    echo '<input type="date" name="fecha_emision">';  
    echo '<input type="submit" value="Grabar factura">';  
    echo '</form>';  
}  
echo "</body></html>";