لقد طُلب مني كتابة هذا المقال بمقالات مماثلة حول حبري: تقويم إنتاج باستخدام PostgreSQL و MS SQL . قررت استخدام نهج مختلط. من ناحية ، احتفظ فقط بالاستثناءات للتواريخ ، وقم بإنشاء تقويم على الطاير ، من ناحية أخرى ، يمكنك حفظ هذا التقويم في جدول دائم والبحث بسرعة حسب التاريخ أو السمات الأخرى.
للتطوير ، سنستخدم Firebird 3.0 ، وقد وسع بشكل كبير قدرات PSQL مقارنة بالإصدارات السابقة. سيتم تضمين جميع الإجراءات والوظائف للعمل مع التقويم في حزمة DATE_UTILS.
أولاً ، قم بإنشاء جدول لتخزين تواريخ العطلات القياسية.
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;سيساعدنا هذا الجدول على أتمتة عملية ملء التقويم حتى لا نضيف العطلات في كل مرة كعطلة نهاية أسبوع.
الآن قم بإنشاء جدول لتخزين الاستثناءات. سيتم تخزين كل من أيام الأسبوع ، التي أصبحت عطلات نهاية الأسبوع ، والعكس بالعكس ، عطلات نهاية الأسبوع التي تم تحديدها على أنها أيام عمل.
بالإضافة إلى ذلك ، يمكنك ترك ملاحظة اعتباطية لأي تاريخ.
CREATE TABLE CALENDAR_NOTES (
BYDATE DATE NOT NULL,
DAY_TYPE SMALLINT NOT NULL,
REMARK VARCHAR(255),
CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);يشير الحقل DAY_TYPE إلى نوع التاريخ: 0 - يوم العمل. 1 - يوم عطلة ، 2 - عطلة.
للعمل مع جدول الاستثناء ، قم بإنشاء إجراءين مخزنين ووضعهما داخل الحزمة 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
على عكس PostgreSQL ، ليس لدى Firebird وظيفة خاصة لتوليد سلسلة من القيم. يمكن أن يتم هذا الجيل باستخدام CTE العودية ، ولكن في هذه الحالة سوف نكون مقيدين بعمق العودية. سنقوم بذلك بشكل أسهل قليلاً ، ونكتب إجراءً مخصصًا انتقائيًا مخزّنًا لإنشاء تسلسل من التواريخ ، ووضعه داخل حزمة 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
يوفر الإجراء الحماية ضد التكرار ؛ إذا كان التاريخ الأدنى أكبر من الحد الأقصى ، فسيتم طرح استثناء E_MIN_DATE_EXCEEDS ، والذي يتم تعريفه على النحو التالي:
CREATE EXCEPTION E_MIN_DATE_EXCEEDS ' ';الآن دعنا ننتقل إلى إنشاء التقويم على الطاير. إذا كان التاريخ موجودًا في جدول الاستثناء ، فسيتم استنتاج نوع التاريخ والملاحظة من جدول الاستثناء. إذا لم يكن التاريخ في جدول الاستثناءات ، ولكنه موجود في الجدول مع تواريخ العطلات ، فإننا نعرض ملاحظة من جدول العطلات. يتم تحديد عطلات نهاية الأسبوع من خلال عدد أيام الأسبوع ، أما بقية التواريخ فهي أيام عمل. يتم تنفيذ الخوارزمية الموصوفة بواسطة الاستعلام التالي
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احفظ هذا الاستعلام في إجراء مخزن انتقائي وأضف مخرجات بعض الأعمدة الإضافية
--
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دعنا نضيف عدة وظائف لعرض أيام الأسبوع وأسماء الشهور ونوع التاريخ باللغة الروسية.
--
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الآن يمكننا عرض التقويم باستخدام الاستعلام التالي:
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>
إذا كنت بحاجة إلى وضع علامة على بعض التاريخ على أنه يوم عطلة أو يوم عمل ، فاستخدم الاستعلام التالي:
EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, ' ');
لإزالة التاريخ من قائمة الاستثناءات ، يجب عليك إكمال الاستعلام
EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');
الآن قم بإنشاء جدول لتخزين تقويم الإنتاج ، واكتب إجراءً لملئه.
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تم تصميم إجراء ملء الجدول لتخزين التقويم بطريقة أنه إذا كان التاريخ موجودًا فيه بالفعل ، فسيتم تحديث نوع التاريخ والملاحظات فقط إذا حدثت التغييرات في جدول الاستبعاد أو تمت إزالة التاريخ من قائمة الاستبعادات.
لكي تنعكس التغييرات في جدول الاستبعاد على الفور في جدول التقويم ، سنقوم بتغيير طفيف لإجراءات SET_DATE_NOTE و UNSET_DATE_NOTE. التغيير الأول تافه جدًا ، نضيف فقط طلبًا آخر إلى الإجراء لتحديث الملاحظة ونوع التاريخ في جدول التقويم.
--
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يعد حذف التاريخ أكثر تعقيدًا بعض الشيء ، لأنه يجب علينا إعادة الملاحظة التي كان عليها التاريخ قبل إضافته إلى قائمة الاستثناءات. للقيام بذلك ، نستخدم نفس المنطق لتحديد نوع التاريخ والملاحظات التي تم استخدامها بالفعل في إجراء 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
يمكنك عرض تقويم من جدول باستخدام الاستعلام التالي:
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>هذا كل شئ. تمكنا من إنشاء تقويم إنتاج سريع ، وإدارة استثناءات التاريخ ، وحفظ التقويم في جدول للبحث السريع عن التاريخ. يمكن العثور على البرنامج النصي لإنشاء الجداول وحزم التقويم هنا .