99 lines
4.5 KiB
PL/PgSQL
99 lines
4.5 KiB
PL/PgSQL
begin;
|
||
|
||
set search_path to public;
|
||
|
||
-- 2026-05-25 日历能力升级脚本
|
||
-- 说明：
|
||
-- 1. 创建并补齐业务日历表 business_calendar_day。
|
||
-- 2. 用于“同步当年日历”功能落库存储节假日、法定休息日、调休补班、工作日定义。
|
||
-- 3. 可重复执行。
|
||
|
||
create table if not exists business_calendar_day (
|
||
id bigint generated by default as identity primary key,
|
||
calendar_date date not null,
|
||
calendar_year integer not null,
|
||
day_of_week integer not null,
|
||
day_type_code integer not null default 0,
|
||
day_type varchar(32) not null default 'WORKDAY',
|
||
day_name varchar(64) not null,
|
||
holiday_name varchar(100),
|
||
holiday_target varchar(100),
|
||
holiday_wage integer,
|
||
is_weekend boolean not null default false,
|
||
is_rest_day boolean not null default false,
|
||
is_workday boolean not null default true,
|
||
is_holiday boolean not null default false,
|
||
is_legal_holiday boolean not null default false,
|
||
is_makeup_workday boolean not null default false,
|
||
source varchar(50) not null default 'timor',
|
||
synced_by_user_id bigint,
|
||
synced_at timestamptz not null default now(),
|
||
created_at timestamptz not null default now(),
|
||
updated_at timestamptz not null default now()
|
||
);
|
||
|
||
alter table if exists business_calendar_day
|
||
add column if not exists calendar_year integer not null default 1970,
|
||
add column if not exists day_of_week integer not null default 1,
|
||
add column if not exists day_type_code integer not null default 0,
|
||
add column if not exists day_type varchar(32) not null default 'WORKDAY',
|
||
add column if not exists day_name varchar(64) not null default '周一',
|
||
add column if not exists holiday_name varchar(100),
|
||
add column if not exists holiday_target varchar(100),
|
||
add column if not exists holiday_wage integer,
|
||
add column if not exists is_weekend boolean not null default false,
|
||
add column if not exists is_rest_day boolean not null default false,
|
||
add column if not exists is_workday boolean not null default true,
|
||
add column if not exists is_holiday boolean not null default false,
|
||
add column if not exists is_legal_holiday boolean not null default false,
|
||
add column if not exists is_makeup_workday boolean not null default false,
|
||
add column if not exists source varchar(50) not null default 'timor',
|
||
add column if not exists synced_by_user_id bigint,
|
||
add column if not exists synced_at timestamptz not null default now();
|
||
|
||
do $$
|
||
begin
|
||
if exists (
|
||
select 1
|
||
from information_schema.tables
|
||
where table_schema = current_schema()
|
||
and table_name = 'business_calendar_day'
|
||
) and not exists (
|
||
select 1
|
||
from pg_constraint
|
||
where conname = 'uk_business_calendar_day_date'
|
||
) then
|
||
alter table business_calendar_day
|
||
add constraint uk_business_calendar_day_date unique (calendar_date);
|
||
end if;
|
||
end
|
||
$$;
|
||
|
||
create index if not exists idx_business_calendar_day_year_date
|
||
on business_calendar_day (calendar_year asc, calendar_date asc);
|
||
|
||
create index if not exists idx_business_calendar_day_type
|
||
on business_calendar_day (day_type asc, calendar_date asc);
|
||
|
||
comment on table business_calendar_day is '业务日历定义表';
|
||
comment on column business_calendar_day.calendar_date is '日历日期';
|
||
comment on column business_calendar_day.calendar_year is '年份';
|
||
comment on column business_calendar_day.day_of_week is '星期，1-7 对应周一到周日';
|
||
comment on column business_calendar_day.day_type_code is '日历类型编码：0工作日、1周末休息、2节假日、3调休补班';
|
||
comment on column business_calendar_day.day_type is '日历类型';
|
||
comment on column business_calendar_day.day_name is '日历展示名称';
|
||
comment on column business_calendar_day.holiday_name is '节假日名称';
|
||
comment on column business_calendar_day.holiday_target is '调休关联节日';
|
||
comment on column business_calendar_day.holiday_wage is '节假日工资倍数';
|
||
comment on column business_calendar_day.is_weekend is '是否周末';
|
||
comment on column business_calendar_day.is_rest_day is '是否休息日';
|
||
comment on column business_calendar_day.is_workday is '是否工作日';
|
||
comment on column business_calendar_day.is_holiday is '是否节假日';
|
||
comment on column business_calendar_day.is_legal_holiday is '是否法定节假日';
|
||
comment on column business_calendar_day.is_makeup_workday is '是否法定调休补班';
|
||
comment on column business_calendar_day.source is '日历来源';
|
||
comment on column business_calendar_day.synced_by_user_id is '最近同步人ID';
|
||
comment on column business_calendar_day.synced_at is '最近同步时间';
|
||
|
||
commit;
|