Calendario de producción de bricolaje en Firebird

Hola, mi nombre es Denis, soy desarrollador de sistemas de información, escribo artículos y documentación sobre Firebird DBMS. En este artículo quiero hablar sobre la implementación del calendario de producción usando Firebird DBMS.



Artículos similares sobre un habr me han llevado a escribir este artículo: un calendario de producción usando PostgreSQL y MS SQL . Decidí usar un enfoque mixto. Por un lado, puede almacenar solo excepciones para fechas y generar un calendario "sobre la marcha", por otro lado, dicho calendario se puede guardar en una tabla permanente y buscar rápidamente por fecha u otros atributos.



Usaremos Firebird 3.0 para el desarrollo, ha ampliado significativamente las capacidades de PSQL en comparación con las versiones anteriores. Todos los procedimientos y funciones para trabajar con el calendario se encapsularán en el paquete DATE_UTILS.



El primer paso es crear una tabla para almacenar las fechas estándar de vacaciones.

CREATE TABLE HOLIDAYS (
    ID      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    AMONTH  SMALLINT NOT NULL,
    ADAY    SMALLINT NOT NULL,
    REMARK  VARCHAR(255) NOT NULL,
    CONSTRAINT PK_HOLIDAYS PRIMARY KEY (ID),
    CONSTRAINT UNQ_HOLIDAYS UNIQUE (AMONTH, ADAY
);

INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (1, 1, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (2, 1, 7, '');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (3, 2, 23, '  ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (4, 3, 8, '  ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (5, 5, 1, '   ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (6, 5, 9, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (7, 6, 12, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (8, 11, 4, '  ');

COMMIT;

Dicha tabla nos ayudará a automatizar el proceso de llenar el calendario para no agregar días festivos cada fin de semana.



Ahora cree una tabla para almacenar las excepciones. Almacenará los dos días laborables, que se han convertido en fines de semana, y viceversa, los fines de semana designados como días laborables.



Además, puede dejar una nota arbitraria para cualquier fecha.

CREATE TABLE CALENDAR_NOTES (
    BYDATE    DATE NOT NULL,
    DAY_TYPE  SMALLINT NOT NULL,
    REMARK    VARCHAR(255),
    CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);

El campo DAY_TYPE indica el tipo de fecha: 0 - día hábil. 1 - día libre, 2 - vacaciones.



Para trabajar con la tabla de excepciones, crearemos 2 procedimientos almacenados y los colocaremos dentro del paquete DATE_UTILS.

  
  --       
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
  END

  --    
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;
  END

Firebird, a diferencia de PostgreSQL, carece de una función especial para generar series de valores. Dicha generación se puede hacer usando un CTE recursivo, pero en este caso, estaremos limitados por la profundidad de recursión. Lo haremos un poco más fácil, escribiremos un procedimiento almacenado selectivo personalizado para generar una secuencia de fechas y colocarlo dentro del paquete DATE_UTILS.

  
  --   
  --   1 
  PROCEDURE GENERATE_SERIES (
      MIN_DATE DATE,
      MAX_DATE DATE)
  RETURNS (
      BYDATE DATE)
  AS
  BEGIN
    IF (MIN_DATE > MAX_DATE) THEN
      EXCEPTION E_MIN_DATE_EXCEEDS;
    BYDATE = MIN_DATE;
    WHILE (BYDATE <= MAX_DATE) DO
    BEGIN
      SUSPEND;
      BYDATE = BYDATE + 1;
    END
  END

El procedimiento proporciona protección contra bucles; si la fecha mínima es mayor que la máxima, se generará una excepción E_MIN_DATE_EXCEEDS, que se define de la siguiente manera:

CREATE EXCEPTION E_MIN_DATE_EXCEEDS '   ';

Ahora pasemos a generar el calendario sobre la marcha. Si la fecha está contenida en la tabla de excepciones, se mostrará el tipo de fecha y una nota de la tabla de excepciones. Si la fecha no está en la tabla de excepciones, pero está presente en la tabla con fechas de vacaciones, entonces mostramos una nota de la tabla de vacaciones. Los fines de semana están determinados por el número del día de la semana, el resto de las fechas son días hábiles. El algoritmo descrito se implementa mediante la siguiente solicitud

SELECT
    D.BYDATE,
    CASE
        WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
        WHEN HOLIDAYS.ID IS NOT NULL THEN 2
        WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
        ELSE 0
    END AS DATE_TYPE,
    COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
    LEFT JOIN HOLIDAYS 
      ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
         HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
    LEFT JOIN CALENDAR_NOTES NOTES 
      ON NOTES.BYDATE = D.BYDATE

Guarde esta consulta en un procedimiento almacenado selectivo y agregue el resultado de algunas columnas adicionales

--  
PROCEDURE GET_CALENDAR (
    MIN_DATE DATE,
    MAX_DATE DATE)
RETURNS (
    BYDATE     DATE,
    YEAR_OF    SMALLINT,
    MONTH_OF   SMALLINT,
    DAY_OF     SMALLINT,
    WEEKDAY_OF SMALLINT,
    DATE_TYPE  SMALLINT,
    REMARK     VARCHAR(255))
AS
BEGIN
    FOR
      SELECT
          D.BYDATE,
          EXTRACT(YEAR FROM d.BYDATE) AS YEAR_OF,
          EXTRACT(MONTH FROM d.BYDATE) AS MONTH_OF,
          EXTRACT(DAY FROM d.BYDATE) AS DAY_OF,
          EXTRACT(WEEKDAY FROM d.BYDATE) AS WEEKDAY_OF,
          CASE
            WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
      FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
          LEFT JOIN HOLIDAYS
            ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
               HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
          LEFT JOIN CALENDAR_NOTES NOTES
            ON NOTES.BYDATE = D.BYDATE
      INTO BYDATE,
           YEAR_OF,
           MONTH_OF,
           DAY_OF,
           WEEKDAY_OF,
           DATE_TYPE,
           REMARK
    DO
      SUSPEND;
END

Agregue algunas funciones para mostrar los días de la semana, los nombres de los meses y el tipo de fecha en ruso.

--     
  FUNCTION GET_WEEKDAY_NAME(AWEEKDAY SMALLINT) RETURNS CHAR(2)
  AS
  BEGIN
    RETURN CASE AWEEKDAY
      WHEN 1 THEN ''
      WHEN 2 THEN ''
      WHEN 3 THEN ''
      WHEN 4 THEN ''
      WHEN 5 THEN ''
      WHEN 6 THEN ''
      WHEN 0 THEN ''
    END;
  END

  --   
  FUNCTION GET_MONTH_NAME(AMONTH SMALLINT) RETURNS VARCHAR(10)
  AS
  BEGIN
    RETURN CASE AMONTH
      WHEN 1 THEN ''
      WHEN 2 THEN ''
      WHEN 3 THEN ''
      WHEN 4 THEN ''
      WHEN 5 THEN ''
      WHEN 6 THEN ''
      WHEN 7 THEN ''
      WHEN 8 THEN ''
      WHEN 9 THEN ''
      WHEN 10 THEN ''
      WHEN 11 THEN ''
      WHEN 12 THEN ''
    END;
  END

  --    
  FUNCTION GET_DAY_TYPE_NAME(ADAY_TYPE SMALLINT) RETURNS VARCHAR(11)
  AS
  BEGIN
    RETURN CASE ADAY_TYPE
      WHEN 0 THEN ''
      WHEN 1 THEN ''
      WHEN 2 THEN ''
    END;
  END

Ahora podemos mostrar el calendario usando la siguiente consulta:

SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM DATE_UTILS.GET_CALENDAR(DATE '01.05.2019', DATE '31.05.2019') D

BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019               1                
2019-05-02     2019               2                 
2019-05-03     2019               3                 
2019-05-04     2019               4                 
2019-05-05     2019               5                 
2019-05-06     2019               6                 <null>
2019-05-07     2019               7                 <null>
2019-05-08     2019               8                 <null>
2019-05-09     2019               9              
2019-05-10     2019              10                 
2019-05-11     2019              11                <null>
2019-05-12     2019              12                <null>
2019-05-13     2019              13                 <null>
2019-05-14     2019              14                 <null>
2019-05-15     2019              15                 <null>
2019-05-16     2019              16                 <null>
2019-05-17     2019              17                 <null>
2019-05-18     2019              18                <null>
2019-05-19     2019              19                <null>
2019-05-20     2019              20                 <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019              21                 <null>
2019-05-22     2019              22                 <null>
2019-05-23     2019              23                 <null>
2019-05-24     2019              24                 <null>
2019-05-25     2019              25                <null>
2019-05-26     2019              26                <null>
2019-05-27     2019              27                 <null>
2019-05-28     2019              28                 <null>
2019-05-29     2019              29                 <null>
2019-05-30     2019              30                 <null>
2019-05-31     2019              31                 <null>

Si necesita marcar una fecha como fin de semana o día de la semana, use la siguiente consulta:

EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, ' ');

Para eliminar la fecha de la lista de excepciones, debe completar la consulta

EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');

Ahora creemos una tabla para almacenar el calendario de producción y escribamos un procedimiento para llenarlo.

CREATE TABLE CALENDAR (
    BYDATE      DATE NOT NULL,
    YEAR_OF     SMALLINT NOT NULL,
    MONTH_OF    SMALLINT NOT NULL,
    DAY_OF      SMALLINT NOT NULL,
    WEEKDAY_OF  SMALLINT NOT NULL,
    DATE_TYPE   SMALLINT NOT NULL,
    REMARK      VARCHAR(255),
    CONSTRAINT PK_CALENDAR PRIMARY KEY (BYDATE)
);

  -- /  
  PROCEDURE FILL_CALENDAR (
      MIN_DATE DATE,
      MAX_DATE DATE)
  AS
  BEGIN
    MERGE INTO CALENDAR
    USING (
      SELECT
        BYDATE,
        YEAR_OF,
        MONTH_OF,
        DAY_OF,
        WEEKDAY_OF,
        DATE_TYPE,
        REMARK
      FROM DATE_UTILS.GET_CALENDAR(:MIN_DATE, :MAX_DATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN NOT MATCHED THEN
    INSERT (
      BYDATE,
      YEAR_OF,
      MONTH_OF,
      DAY_OF,
      WEEKDAY_OF,
      DATE_TYPE,
      REMARK
    )
    VALUES (
      S.BYDATE,
      S.YEAR_OF,
      S.MONTH_OF,
      S.DAY_OF,
      S.WEEKDAY_OF,
      S.DATE_TYPE,
      S.REMARK
    )
    WHEN MATCHED AND
      (CALENDAR.DATE_TYPE <> S.DATE_TYPE OR 
       CALENDAR.REMARK <> S.REMARK) THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END

El procedimiento para llenar la tabla para almacenar el calendario está diseñado de tal manera que si ya existe una fecha en él, la fecha y el tipo de nota se actualizarán solo si se han producido cambios en la tabla de exclusión o si la fecha se ha eliminado de la lista de exclusión.



Para que los cambios en la tabla de exclusión se reflejen inmediatamente en la tabla del calendario, cambiaremos ligeramente los procedimientos SET_DATE_NOTE y UNSET_DATE_NOTE. El primer cambio es bastante trivial, solo agregamos otra solicitud para actualizar la nota y el tipo de fecha en la tabla CALENDARIO al procedimiento.

  --       
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);

    --        
    UPDATE CALENDAR
    SET DATE_TYPE = :ADAY_TYPE,
        REMARK = :AREMARK
    WHERE BYDATE = :ADATE
      AND (DATE_TYPE <> :ADAY_TYPE OR REMARK <> :AREMARK);
  END

Eliminar la anotación de una fecha es un poco más complicado porque tenemos que devolver la anotación que tenía antes de que se excluyera. Para hacer esto, usamos la misma lógica para determinar el tipo de fecha y los comentarios que ya se usaron en el procedimiento GET_CALENDAR.

  --    
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;

    --        
    MERGE INTO CALENDAR
    USING (
      SELECT
          :ADATE AS BYDATE,
          CASE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM :ADATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          HOLIDAYS.REMARK AS REMARK
      FROM RDB$DATABASE
      LEFT JOIN HOLIDAYS ON
        HOLIDAYS.AMONTH = EXTRACT(MONTH FROM :ADATE) AND
        HOLIDAYS.ADAY = EXTRACT(DAY FROM :ADATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN MATCHED THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END

Puede mostrar un calendario desde una tabla utilizando la siguiente consulta:

SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM CALENDAR D
WHERE D.BYDATE BETWEEN DATE '01.05.2019' AND DATE '31.05.2019'

BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019               1                
2019-05-02     2019               2                 
2019-05-03     2019               3                 
2019-05-04     2019               4                 
2019-05-05     2019               5                 
2019-05-06     2019               6                 <null>
2019-05-07     2019               7                 <null>
2019-05-08     2019               8                 <null>
2019-05-09     2019               9              
2019-05-10     2019              10                 
2019-05-11     2019              11                <null>
2019-05-12     2019              12                <null>
2019-05-13     2019              13                 <null>
2019-05-14     2019              14                 <null>
2019-05-15     2019              15                 <null>
2019-05-16     2019              16                 <null>
2019-05-17     2019              17                 <null>
2019-05-18     2019              18                <null>
2019-05-19     2019              19                <null>
2019-05-20     2019              20                 <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019              21                 <null>
2019-05-22     2019              22                 <null>
2019-05-23     2019              23                 <null>
2019-05-24     2019              24                 <null>
2019-05-25     2019              25                <null>
2019-05-26     2019              26                <null>
2019-05-27     2019              27                 <null>
2019-05-28     2019              28                 <null>
2019-05-29     2019              29                 <null>
2019-05-30     2019              30                 <null>
2019-05-31     2019              31                 <null>

Eso es todo. Pudimos generar un calendario de producción sobre la marcha, administrar excepciones de fechas y guardar el calendario en una tabla para búsquedas rápidas de fechas. Puede encontrar el script para crear tablas y paquetes de calendario aquí .