Kalender produksi DIY di Firebird

Halo, nama saya Denis, saya akan menjadi pengembang sistem informasi, saya menulis artikel dan dokumentasi pada DBMS Firebird. Pada artikel ini, saya ingin berbicara tentang implementasi kalender produksi menggunakan Firebird.



Saya diminta untuk menulis artikel ini dengan artikel serupa di Habré: kalender produksi menggunakan PostgreSQL dan MS SQL . Saya memutuskan untuk menggunakan pendekatan campuran. Di satu sisi, Anda hanya dapat menyimpan pengecualian untuk tanggal dan menghasilkan kalender "on the fly", di sisi lain, kalender tersebut dapat disimpan ke tabel persisten dan cepat dicari berdasarkan tanggal atau atribut lainnya.



Untuk pengembangan kami akan menggunakan Firebird 3.0, itu telah memperluas kemampuan PSQL secara signifikan dibandingkan dengan versi sebelumnya. Semua prosedur dan fungsi untuk bekerja dengan kalender akan dienkapsulasi dalam paket DATE_UTILS.



Langkah pertama adalah membuat tabel untuk menyimpan tanggal liburan standar.

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;

Tabel seperti itu akan membantu kami mengotomatiskan proses mengisi kalender agar tidak menambah liburan setiap saat sebagai akhir pekan.



Sekarang mari kita buat tabel untuk menyimpan pengecualian. Ini akan menyimpan kedua hari kerja, yang telah menjadi akhir pekan, dan sebaliknya, akhir pekan yang ditetapkan sebagai hari kerja.



Selain itu, Anda dapat meninggalkan catatan sewenang-wenang untuk tanggal apa pun.

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

Bidang DAY_TYPE menunjukkan jenis tanggal: 0 - hari kerja. 1 hari libur, 2 hari libur.



Untuk bekerja dengan tabel pengecualian, kami akan membuat 2 prosedur tersimpan dan menempatkannya di dalam 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

Tidak seperti PostgreSQL, Firebird tidak memiliki fungsi khusus untuk menghasilkan serangkaian nilai. Generasi seperti itu dapat dilakukan dengan menggunakan CTE rekursif, tetapi dalam hal ini, kita akan dibatasi oleh kedalaman rekursi. Kami akan melakukannya sedikit lebih mudah, menulis prosedur tersimpan selektif khusus untuk menghasilkan urutan tanggal dan menempatkannya di dalam paket 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

Prosedur ini memberikan perlindungan terhadap perulangan, jika tanggal minimum lebih besar dari maksimum, maka pengecualian E_MIN_DATE_EXCEEDS akan dilemparkan, yang didefinisikan sebagai berikut:

CREATE EXCEPTION E_MIN_DATE_EXCEEDS '   ';

Sekarang mari kita beralih ke membuat kalender dengan cepat. Jika tanggal terkandung dalam tabel pengecualian, maka jenis tanggal dan catatan dari tabel pengecualian akan disimpulkan. Jika tanggal tersebut tidak ada dalam tabel pengecualian, tetapi tanggal tersebut ada dalam tabel dengan tanggal hari libur, maka kami menampilkan catatan dari tabel hari libur. Akhir pekan ditentukan oleh jumlah hari dalam seminggu, tanggal yang tersisa bekerja. Algoritma yang dijelaskan diimplementasikan oleh kueri berikut

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

Mari kita simpan kueri ini ke prosedur tersimpan selektif dan menambahkan output dari beberapa kolom tambahan

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

Mari kita tambahkan beberapa fungsi untuk menampilkan hari dalam seminggu, nama bulan dan jenis tanggal dalam bahasa Rusia.

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

Kami sekarang dapat menampilkan kalender menggunakan kueri berikut:

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>

Jika Anda perlu menandai beberapa tanggal sebagai hari libur atau hari kerja, gunakan kueri berikut:

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

Untuk menghapus tanggal dari daftar pengecualian, Anda harus menjalankan kueri

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

Sekarang mari kita buat tabel untuk menyimpan kalender produksi, dan menulis prosedur untuk mengisinya.

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

Prosedur untuk mengisi tabel untuk menyimpan kalender dirancang sedemikian rupa sehingga jika tanggal sudah ada di dalamnya, maka tanggal dan jenis catatan akan diperbarui hanya jika perubahan telah terjadi di tabel pengecualian atau tanggal telah dihapus dari daftar pengecualian.



Agar perubahan dalam tabel pengecualian segera tercermin dalam tabel kalender, kami akan sedikit mengubah prosedur SET_DATE_NOTE dan UNSET_DATE_NOTE. Perubahan pertama cukup sepele, kami hanya menambahkan permintaan lain untuk memperbarui catatan dan jenis tanggal di tabel KALENDER untuk prosedur.

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

Menghapus sebuah tanggal sedikit lebih rumit, karena kita harus mengembalikan komentar yang ada pada tanggal sebelum ditambahkan ke daftar pengecualian. Untuk melakukan ini, kami menggunakan logika yang sama untuk menentukan jenis tanggal dan catatan yang sudah digunakan dalam prosedur 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

Anda dapat menampilkan kalender dari tabel menggunakan kueri berikut:

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>

Itu saja. Kami dapat membuat kalender produksi dengan cepat, mengelola pengecualian tanggal, dan menyimpan kalender dalam tabel untuk pencarian tanggal cepat. Skrip untuk membuat tabel dan paket kalender dapat ditemukan di sini .