DIY Produktionskalender in Firebird

Hallo, mein Name ist Denis, ich bin ein Entwickler von Informationssystemen, ich schreibe Artikel und Dokumentationen über das Firebird DBMS. In diesem Artikel möchte ich über die Implementierung eines Produktionskalenders mit Firebird sprechen.



Ähnliche Artikel auf einem Habr haben mich veranlasst, diesen Artikel zu schreiben: einen Produktionskalender mit PostgreSQL und MS SQL . Ich entschied mich für einen gemischten Ansatz. Einerseits können Sie nur Ausnahmen für Datumsangaben speichern und "on the fly" einen Kalender erstellen, andererseits kann ein solcher Kalender in einer permanenten Tabelle gespeichert und schnell nach Datum oder anderen Attributen durchsucht werden.



Für die Entwicklung werden wir Firebird 3.0 verwenden. Es hat die PSQL-Funktionen im Vergleich zu früheren Versionen erheblich erweitert. Alle Prozeduren und Funktionen für die Arbeit mit dem Kalender werden im Paket DATE_UTILS gekapselt.



Der erste Schritt besteht darin, eine Tabelle zum Speichern der Standardfeiertagsdaten zu erstellen.

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;

Eine solche Tabelle hilft uns, das Ausfüllen des Kalenders zu automatisieren, um nicht jedes Mal Feiertage als Wochenende hinzuzufügen.



Erstellen Sie nun eine Tabelle zum Speichern der Ausnahmen. Es werden beide Wochentage gespeichert, die zu Wochenenden geworden sind, und umgekehrt Wochenenden, die als Arbeitstage festgelegt sind.



Darüber hinaus können Sie für jedes Datum eine beliebige Notiz hinterlassen.

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

Das Feld DAY_TYPE gibt die Art des Datums an: 0 - Geschäftstag. 1 - freier Tag, 2 - Urlaub.



Um mit der Ausnahmetabelle zu arbeiten, erstellen wir zwei gespeicherte Prozeduren und platzieren sie im Paket 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

Im Gegensatz zu PostgreSQL fehlt Firebird eine spezielle Funktion zum Generieren von Werteserien. Eine solche Erzeugung kann unter Verwendung eines rekursiven CTE erfolgen, aber in diesem Fall werden wir durch die Tiefe der Rekursion begrenzt. Wir machen es etwas einfacher, schreiben eine benutzerdefinierte selektive gespeicherte Prozedur, um eine Folge von Datumsangaben zu generieren, und platzieren sie im DATE_UTILS-Paket.

  
  --   
  --   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

Die Prozedur bietet Schutz vor Schleifen. Wenn das Mindestdatum größer als das Maximum ist, wird die Ausnahme E_MIN_DATE_EXCEEDS ausgelöst, die wie folgt definiert ist:

CREATE EXCEPTION E_MIN_DATE_EXCEEDS '   ';

Lassen Sie uns nun mit dem Generieren des Kalenders im laufenden Betrieb fortfahren. Wenn das Datum in der Ausnahmetabelle enthalten ist, werden der Datumstyp und die Notiz aus der Ausnahmetabelle abgeleitet. Wenn das Datum nicht in der Ausnahmetabelle enthalten ist, es jedoch in der Tabelle mit den Feiertagsdaten enthalten ist, wird eine Notiz aus der Feiertagstabelle angezeigt. Wochenenden werden durch die Anzahl der Wochentage bestimmt, die restlichen Daten sind Arbeitstage. Der beschriebene Algorithmus wird durch die folgende Anforderung implementiert

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

Speichern Sie diese Abfrage in einer selektiven gespeicherten Prozedur und fügen Sie die Ausgabe einiger zusätzlicher Spalten hinzu

--  
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

Fügen wir verschiedene Funktionen zum Anzeigen von Wochentagen, Monatsnamen und Datumsangaben in russischer Sprache hinzu.

--     
  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

Wir können den Kalender jetzt mit der folgenden Abfrage anzeigen:

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>

Wenn Sie ein Datum als Wochenende oder Wochentag markieren müssen, verwenden Sie die folgende Abfrage:

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

Um ein Datum aus der Liste der Ausschlüsse zu entfernen, müssen Sie eine Abfrage ausführen

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

Erstellen wir nun eine Tabelle zum Speichern des Produktionskalenders und schreiben eine Prozedur zum Auffüllen.

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

Das Verfahren zum Füllen der Tabelle zum Speichern des Kalenders ist so konzipiert, dass, wenn bereits ein Datum darin vorhanden ist, das Datum und der Notiztyp nur aktualisiert werden, wenn Änderungen in der Ausschlusstabelle aufgetreten sind oder das Datum aus der Ausschlussliste entfernt wurde.



Damit die Änderungen in der Ausschlusstabelle sofort in der Kalendertabelle angezeigt werden, werden die Prozeduren SET_DATE_NOTE und UNSET_DATE_NOTE geringfügig geändert. Die erste Änderung ist ziemlich trivial. Wir fügen der Prozedur lediglich eine weitere Anforderung hinzu, um den Notiz- und Datumstyp in der Tabelle CALENDAR zu aktualisieren.

  --       
  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

Das Löschen eines Datums ist etwas komplizierter, da wir die Bemerkung zurückgeben müssen, die das Datum hatte, bevor es zur Liste der Ausnahmen hinzugefügt wurde. Zu diesem Zweck verwenden wir dieselbe Logik zum Bestimmen des Datumstyps und der Notizen, die bereits in der Prozedur GET_CALENDAR verwendet wurden.

  --    
  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

Mit der folgenden Abfrage können Sie einen Kalender aus einer Tabelle anzeigen:

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>

Das ist alles. Wir konnten im laufenden Betrieb einen Produktionskalender erstellen, Datumsausnahmen verwalten und den Kalender in einer Tabelle speichern, um schnell nach Daten suchen zu können. Das Skript zum Erstellen von Tabellen und Kalenderpaketen finden Sie hier .