¿Qué datos guardar de una Factura?

16 comentarios

Este tema ya ha sido discutido en artículos anteriores pero siempre viene bien darle un breve repaso. Veo que aún hay mucha gente que tiene problemas conceptuales con el mismo.

El punto es muy sencillo: cualquier documento que pueda ser impreso y cuyos datos estaban correctos en el momento de la impresión debe poder ser vuelto a imprimir exactamente igual a la primera vez.

¿Por qué eso?

Porque en el momento de la impresión de un documento esos datos que se ven en él son los correctos (o al menos se supone que los son). Si más tarde por cualquier motivo se necesita reimprimir ese mismo documento entonces la reimpresión debe ser una copia exacta de lo que se imprimió la primera vez. Si eso no es así podrás tener problemas con la autoridad impositiva de tu país, porque a la gente del gobierno no le gusta que los documentos sean distintos.

Y tienen toda la razón del mundo, si a un cliente le diste una Factura por un total de 1.000 dólares y unos meses después al consultar esa Factura se ve que la venta fue de sólo 800 dólares hay un grave problema allí. Y en la mayoría de los países alguien se irá a la cárcel por eso o al menos pagará una fuerte multa, y todo eso podría haberse evitado si la Base de Datos hubiera estado cuidadosamente diseñada.

Entonces, lo que se imprime y sale de la Empresa no se debe normalizar.

En la gran mayoría de los casos, lo mejor y lo recomendable es que las tablas estén normalizadas. En este caso especial lo correcto es que no lo estén.

¿Por qué?

Porque si en la Factura el cliente tiene una dirección y un teléfono, después se muda, se actualizan su dirección y su teléfono en la tabla de CLIENTES, se reimprime la Factura y aparecerán en ella los nuevos datos, y eso está mal. Deberían aparecer los datos originales.

Eso hasta podría ser conversable con la gente del gobierno porque no hubo evasión impositiva ahí, pero ¿y si se cambió la cantidad de productos vendidos o los precios de venta?

Eso ya es otra cosa y un problema gravísimo. Porque eso sí puede provocar evasión impositiva, un delito muy grave en casi todos los países.

Entonces, en nuestra tabla de detalles de ventas tendríamos que guardar el Identificador del Producto vendido y también su código y también su nombre.

¿Y cómo evitamos que se modifiquen la cantidad vendida o el precio de venta o cualquier otro dato de detalle?

Con un trigger que se dispara cuando se quiere actualizar una fila, y envía una excepción.

IF UPDATING THEN
   EXCEPTION E_NO_SE_PUEDE_MODIFICAR_ESTA_FILA

¿Y cómo evitamos que se borre una fila?

Con un trigger que se dispara cuando se quiere borrar una fila y envía una excepción.

IF DELETING THEN
   EXCEPTION E_NO_SE_PUEDE_BORRAR_ESTA_FILA

¿Y si está todo mal y queremos borrar esa Factura?

Para este caso legítimo en nuestra tabla de cabecera tendremos una columna que nos indique que la Factura está anulada. Eso implica que la Factura jamás se borra de la tabla, simplemente se le pone una marca de que sus datos no deben ser utilizados en la mayoría de los informes.

Conclusión:

Los datos que se imprimen en documentos que salen de la Empresa deben ser siempre los mismos, no importa cuando ni cuantas veces se reimprima ese documento. Si eso no se hace así entonces alguna vez se podrán tener graves problemas con los clientes, con los proveedores o mucho peor, con el gobierno.

Para asegurar de que los datos sean siempre los mismos, las tablas donde se encuentran los datos que se imprimirán no deben estar normalizadas. En general, en la gran mayoría de los casos lo correcto es que las tablas sí estén normalizadas, pero en este caso particular (documentos que salen de la Empresa) lo correcto y lo recomendable es que no lo estén.

Para evitar que una fila se modifique podemos usar un trigger que impedirá las modificaciones. Para evitar que una fila sea borrada podemos usar un trigger que impedirá el borrado. Por supuesto que podremos usar un solo trigger para realizar ambas tareas.

Haciendo así nos aseguraremos de que siempre, sin importar cuanto tiempo haya pasado, nuestra Base de Datos mostrará información consistente.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Ventas mensuales a cada cliente usando tablas agregadas

Deja un comentario

En este artículo hay otro ejemplo del uso de las tablas agregadas. Ahora, las usaremos para ver en columnas las ventas realizadas durante cada mes del año.

NOTA IMPORTANTE:

Los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT han sido levemente modificados. Como seguramente sabes siempre el código fuente puede ser mejorado, y eso es justamente lo que sucedió con ellos. Las más nuevas versiones podrás descargar desde:

http://www.mediafire.com/view/ol58gyejul1t43a/CREAR_TABLA_PIVOT.txt

http://www.mediafire.com/view/1uebv8uerc5kwpe/ACTUALIZAR_TABLA_PIVOT.txt

Necesitarás de las nuevas versiones para que te funcionen los ejemplos que vienen a continuación.

Listado 1. La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   NOMBRE_MES,
   MVC_NOMCLI,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      'MES_' ||
      LPAD(EXTRACT(MONTH FROM MVC_FECHAX), 2, '0') || '_' ||
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
          1, 'ENE',
          2, 'FEB',
          3, 'MAR',
          4, 'ABR',
          5, 'MAY',
          6, 'JUN',
          7, 'JUL',
          8, 'AGO',
          9, 'SEP',
         10, 'OCT',
         11, 'NOV',
         12, 'DIC') AS NOMBRE_MES,
         C.CLI_NOMBRE AS MVC_NOMCLI,
         M.MVC_TOTALX
      FROM
         MOVIMCAB M
      JOIN
         CLIENTES C
            ON M.MVC_IDECLI = C.CLI_IDENTI;

AGREGADAS1

Captura 1. Si haces clic en la imagen la verás más grande

Ahora creamos la tabla agregada VENTAS1:

Listado 2. La tabla agregada VENTAS1

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

AGREGADAS2

Captura 2. Si haces clic en la imagen la verás más grande

Y a continuación le agregamos datos:

Listado 3. Insertando filas a la tabla VENTAS1

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_IDECLI INTEGER',
      'MVC_NOMCLI VARCHAR(40)',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGREGADAS3

Captura 3. Si haces clic en la imagen la verás más grande

Y listo, ya está. Ya tenemos nuestra tabla agregada con un Cliente por cada fila y las ventas mensuales en las siguientes columnas. Este tipo de tablas puede ser muy útil para la toma de decisiones.

Fíjate que lo único que necesitamos hacer es escribir una vista que contenga los datos que utilizaremos, los stored procedures CREAR_TABLA_PIVOT y ACTUALIZAR_TABLA_PIVOT se encargan de todo lo demás.

Si no queremos ver los Identificadores de los clientes entonces podríamos crear la tabla de esta manera:

Listado 4. La tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'NUMERIC(17, 4)')

La actualizaríamos así:

Listado 5. Insertando filas a la tabla VENTAS1 sin los identificadores de los clientes

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'VENTAS1',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

Y obtendríamos este resultado:

AGREGADAS4

Captura 4. Si haces clic en la imagen la verás más grande

Que es bastante similar al anterior pero no idéntico ¿por qué no? porque en el anterior las filas aparecían ordenadas por Identificador del Cliente y en el actual aparecen ordenadas por Nombre del Cliente. Es decir que las filas siempre aparecen ordenadas por el contenido de la primera columna.

Artículos relacionados:

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

El índice del blog Firebird21

El comando INSERT

Deja un comentario

El comando INSERT (insertar) se utiliza para agregarle nuevas filas a una tabla. Tiene dos sintaxis:

Sintaxis 1:

INSERT INTO tabla | vista (<lista de columnas>)
VALUES (<lista de valores>)
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

Se utiliza cuando se quiere agregar una fila a la tabla o vista.

Ejemplo:

INSERT INTO ALUMNOS
        (ALU_CODIGO, ALU_NOMBRE, ALU_APELLD)
VALUES  ('1253', 'MIRTHA', 'ROMERO');

Sintaxis 2:

INSERT INTO tabla | vista (<lista de columnas>)
SELECT <lista de valores> FROM ...
[RETURNING <lista de columnas a retornar>
[INTO <lista con el nombre de las variables>]]

 Ejemplo:

INSERT INTO GERENTES
         (GER_CODIGO, GER_NOMBRE)
   SELECT E.EMP_CODIGO, E.EMP_NOMBRE FROM EMPLEADOS E WHERE E.EMP_CARGOX = 'GERENTE'

Esta segunda sintaxis se utiliza cuando se quieren agregar muchas filas de una sola vez.

Si los valores que queremos agregarle a una tabla se encuentran en otra u otras tablas, esta segunda sintaxis es la adecuada porque es muy rápida.

Artículo relacionado:

El índice del blog Firebird21

Insertando valores por defecto

1 comentario

Con Firebird tenemos la posibilidad de insertar valores automáticamente en las columnas. Esto puede resultar muy útil porque nos ahorra tiempo al escribir el comando INSERT (o el comando: UPDATE OR INSERT) y también nos asegura que esas columnas siempre tengan valores, que no se guarde NULL en ellas.

INSERTAR1Captura 1. (Si haces clic en la imagen la verás más grande)

En la Captura 1 podemos ver la estructura de la tabla ALUMNOS. Ninguna columna tiene un valor por defecto entonces si escribimos:

INSERT INTO ALUMNOS
           (ALU_IDENTI, ALU_NOMBRE, ALU_APELLD)
    VALUES (0         , 'JUAN'    , 'PÉREZ')

Esto será lo que obtendremos:

INSERTAR2

Captura 2. (Si haces clic en la imagen la verás más grande)

Como puedes ver en la Captura 2 las columnas ALU_IDENTI, ALU_NOMBRE y ALU_APELLD tienen valores mientras que las demás columnas tienen NULL en ellas.

Modifiquemos ahora la tabla ALUMNOS para ponerle valores por defecto a algunas columnas y veamos que sucede.

INSERTAR3

Captura 3. (Si haces clic en la imagen la verás más grande)

Ahora tenemos cuatro columnas que tienen valores por defecto (son los que se encuentran en “Default Source”). Entonces cuando insertamos una nueva fila:

INSERT INTO ALUMNOS
           (ALU_IDENTI, ALU_NOMBRE, ALU_APELLD)
    VALUES (0         , 'MARÍA'   , 'BENÍTEZ' )

esto es lo que obtenemos:
INSERTAR4

Captura 4. (Si haces clic en la imagen la verás más grande)

 como puedes ver, ahora al insertar una fila también se insertan automáticamente sus valores por defecto.

CURRENT_USER es el usuario actual

CURRENT_TIMESTAMP es la fecha y hora del Servidor

Si no queremos que se inserten los valores por defecto entonces debemos asignarle un valor a las columnas respectivas, como vemos a continuación:

INSERT INTO ALUMNOS
           (ALU_IDENTI, ALU_NOMBRE, ALU_APELLD, ALU_CODDEP, ALU_CODLOC)
    VALUES (0         , 'ESTELA'  , 'MARTÍNEZ', '04'      , '03'      )

Como acá le asignamos valores a las columnas ALU_CODDEP y ALU_CODLOC entonces esos son los que se guardan, no los valores por defecto sino los que asignamos en el comando INSERT.

INSERTAR5

Captura 5. (Si haces clic en la imagen la verás más grande)

La columna ALU_CODDEP tiene ’04’ y la columna ALU_CODLOC tiene ’03’ porque esos valores fueron escritos en el comando INSERT y lo que se escribe en el INSERT siempre tiene preferencia. Los valores por defecto solamente se usan cuando no se especifica un valor para la columna.

Conclusión:

Si usamos valores por defecto tenemos estas ventajas:

  1. Nos aseguramos que la columna tenga un valor (o sea, que no se quede en NULL)
  2. Escribimos menos en el comando INSERT (porque el Firebird automáticamente le asigna un valor a la columna)

En muchos casos resulta muy conveniente registrar quien fue el usuario que insertó una fila y también la fecha y la hora en la cual lo hizo. Si usamos valores por defecto entonces no necesitamos recordar escribirlos, el Firebird lo hará por nosotros. Esta facilidad que nos provee el Firebird de inserción automática de valores en las columnas es muy útil, deberíamos aprovecharla.

Artículo relacionado:

El índice del blog Firebird21

Usando UPDATE OR INSERT

5 comentarios

Supongamos que queremos hacer lo siguiente:

  • Si existe una fila de una tabla, entonces cambiar el contenido de alguna columna
  • Si no existe esa fila, agregarla a la tabla

Esta es una tarea muy común en los programas llamados de ABM (agregar, borrar, modificar) o sea los programas que los usuarios utilizan para introducir datos a las tablas.

La forma antigua de realizar esa tarea sería esta:

SET TERM ^ ;

CREATE PROCEDURE GRABAR_BANCO(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
BEGIN

   IF (EXISTS(SELECT * FROM BANCOS WHERE BAN_IDENTI = :tnIdenti)) THEN
      UPDATE
         BANCOS
      SET
         BAN_NOMBRE = :tcNombre
      WHERE
         BAN_IDENTI = :tnIdenti;
   ELSE
      INSERT INTO BANCOS
             (BAN_IDENTI, BAN_NOMBRE)
      VALUES (:tnIdenti , :tcNombre );

END^

SET TERM ; ^

También podríamos escribirlo así, quizás sea más claro para algunos:

SET TERM ^ ;

CREATE PROCEDURE GRABAR_BANCO(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
BEGIN

   UPDATE
      BANCOS
   SET
      BAN_NOMBRE = :tcNombre
   WHERE
      BAN_IDENTI = :tnIdenti;

   IF (ROW_COUNT = 0) THEN
      INSERT INTO BANCOS
             (BAN_IDENTI, BAN_NOMBRE)
      VALUES (:tnIdenti , :tcNombre );

END^

SET TERM ; ^

En este caso, intentamos actualizar la tabla y si la variable ROW_COUNT es igual a cero significa que no lo conseguimos porque no había fila para actualizar, así que procedemos a insertar la fila.

En esos dos stored procedures se utilizó la forma antigua, la forma tradicional, la que puede utilizarse en cualquier motor SQL. Sin embargo, el Firebird nos provee de una mucha mejor alternativa: el comando UPDATE OR INSERT, el cual primero trata de actualizar una fila y si no lo consigue, la inserta.

SET TERM ^ ;

CREATE PROCEDURE GRABAR_BANCO(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
BEGIN

   UPDATE OR INSERT INTO BANCOS
          (BAN_IDENTI, BAN_NOMBRE)
   VALUES (:tnIdenti , :tcNombre );

END^

SET TERM ; ^

Como puedes ver, se ha escrito mucho menos y el código es más legible (bueno, al menos para mí es más legible). En este ejemplo la columna BAN_IDENTI es la Primary Key de la tabla BANCOS y el comando UPDATE OR INSERT intenta actualizar una fila que tenga esa Primary Key. Si no lo consigue (porque ninguna fila tiene esa Primary Key) entonces le inserta una fila a la tabla de BANCOS. Claro, fácil, sencillo, se escribe menos y se entiende mejor.

CSV2GDB

1 comentario

Esta es una contribución de Pedro Hernández, de HC Soft Programación, s.l.

Esta herramienta nos permite importar a nuestra Base de Datos cualquier archivo de texto con formato CSV. Las siglas CSV significan “comma separated values” o sea, “valores separados por comas”.

Por ejemplo, podríamos tener un archivo de texto con estas líneas:

Alicia,Genghis Khan Nº 1758,Constantinopla,Turquía
Silvia,Nabucodonosor Nº 5567,El Cairo,Egipto
Teresa,Napoleón Bonaparte Nº 2128,Berlín,Alemania
Zulma,Guillermo Tell Nº 3520,Berna,Suiza

Como ves, los valores están separados por comas y esta herramienta nos permite copiarlos dentro de una tabla de nuestra Base de Datos.

Puedes descargar el archivo .ZIP que contiene al ejecutable GSV2GDB.EXE desde este enlace:

http://www.hcsoft.net/hclabwin/archivos/2.%20Bases%20de%20datos/Utilidades/CSV2GDB.zip

Y puedes leer su manual en este enlace:

http://www.hcsoft.net/lab/index.php?zona=comohacer&hoja=csv2gdb

Muchas gracias a Pedro Hernández por contribuir con esta útil herramienta.

Insertar, modificar o borrar filas en una Base de Datos externa

10 comentarios

En general, lo recomendable es que todas tus tablas estén en una sola Base de Datos ya que así puedes relacionar muy fácilmente a esas tablas entre sí. Sin embargo a veces no puedes evitar tener dos o más bases de datos. En ese caso: ¿cómo harías para insertar, modificar o borrar filas en una tabla que se encuentra en la otra Base de Datos?

El comando EXECUTE STATEMENT viene rápidamente en tu ayuda.

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_1
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
   DECLARE VARIABLE lnIdenti  BIGINT;
   DECLARE VARIABLE lcNombre  VARCHAR(40);
BEGIN

   lnIdenti = 0;
   lcNombre = '''Esta es una prueba''';

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :lnIdenti || ',' || :lcNombre || ')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';
END;

Como puedes ver la variable lcNombre está rodeada por tres apóstrofos ¿por qué eso? porque si no se hace así ocurre un error ya que el contenido de esa variable debe estar rodeado de apóstrofos en el comando INSERT INTO. Cuando quieres que dentro de un string aparezca un apóstrofo debes escribir dos apóstrofos seguidos. Por ese motivo tuve que escribir tres apóstrofos.

Quizás también hayas notado que aunque la variable lnIdenti es numérica se la concatenó sin necesidad de convertirla previamente a tipo carácter, esa es una gran facilidad que nos da el Firebird.

En este ejemplo, los valores que insertamos en las columnas TAR_IDENTI y TAR_NOMBRE son constantes, sin embargo lo más común es que necesitemos insertar valores variables, como se ve en el siguiente ejemplo:

CREATE PROCEDURE INSERTAR_EN_BASEDATOS_EXTERNA_2(
   tnIdenti BIGINT,
   tcNombre VARCHAR(40))
AS
   DECLARE VARIABLE lcComando VARCHAR(200);
BEGIN

   lcComando = 'INSERT INTO TARJETAS(TAR_IDENTI, TAR_NOMBRE) VALUES(' ||
               :tnIdenti || ',''' || :tcNombre || ''')';

   EXECUTE STATEMENT
      lcComando
   ON EXTERNAL
      'E:\BASESDATOS\CONTABILIDAD.FDB'
   AS
      USER 'SYSDBA' PASSWORD 'masterkey';

END;

En este ejemplo los datos a insertar en la tabla externa fueron enviados como parámetros al stored procedure. Fíjate bien como el parámetro :tcNombre está rodeado por apóstrofos. Debes escribir esos apóstrofos para que funcione.

 Conclusión:

Para poder insertar, modificar o borrar filas en una tabla que se encuentra en otra Base de Datos debemos crear un string y luego ejecutarlo con el comando EXECUTE STATEMENT. Hay que tener en cuenta que las variables de tipo carácter deben estar rodeadas por apóstrofos.

Artículo relacionado:

https://firebird21.wordpress.com/2013/04/18/execute-statement/