267 lines
8.1 KiB
SQL
267 lines
8.1 KiB
SQL
-- @block create table anwesenheit
|
||
CREATE TABLE "public"."anwesenheit" (
|
||
"counter_id" SERIAL PRIMARY KEY,
|
||
"timestamp" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
|
||
"card_uid" varchar(255) COLLATE "pg_catalog"."default",
|
||
"check_in_out" int2,
|
||
"geraet_id" int2
|
||
);
|
||
COMMENT ON COLUMN "public"."anwesenheit"."check_in_out" IS '1=Check In 2=Check Out 254=Automatic Check Out';
|
||
COMMENT ON COLUMN "public"."anwesenheit"."geraet_id" IS 'ID des Lesegerätes';
|
||
|
||
-- @block create table personaldaten
|
||
CREATE TABLE "public"."personal_daten" (
|
||
"personal_nummer" SERIAL PRIMARY KEY,
|
||
"akiv_beschaeftig" bool,
|
||
"vorname" varchar COLLATE "pg_catalog"."default",
|
||
"nachname" varchar COLLATE "pg_catalog"."default",
|
||
"geburtsdatum" date,
|
||
"plz" varchar COLLATE "pg_catalog"."default",
|
||
"adresse" varchar COLLATE "pg_catalog"."default",
|
||
"geschlecht" numeric,
|
||
"card_uid" varchar(255) COLLATE "pg_catalog"."default",
|
||
"hauptbeschaeftigung_ort" int2
|
||
);
|
||
COMMENT ON COLUMN "public"."personal_daten"."akiv_beschaeftig" IS 'derzeit aktiv beschaeftigt : 1';
|
||
COMMENT ON COLUMN "public"."personal_daten"."geschlecht" IS 'w:1 m:2 div:3 kA:null ';
|
||
COMMENT ON COLUMN "public"."personal_daten"."card_uid" IS 'RFID-Karten-UID';
|
||
COMMENT ON COLUMN "public"."personal_daten"."hauptbeschaeftigung_ort" IS 'Chemnitz:1 Sayda:2';
|
||
|
||
|
||
-- @block drop tables
|
||
DROP TABLE IF EXISTS "public"."anwesenheit";
|
||
DROP TABLE IF EXISTS "public"."personal_daten";
|
||
|
||
|
||
-- @block insert into personal_daten
|
||
INSERT INTO personal_daten (
|
||
personal_nummer,
|
||
akiv_beschaeftig,
|
||
vorname,
|
||
nachname,
|
||
geburtsdatum,
|
||
plz,
|
||
adresse,
|
||
geschlecht,
|
||
card_uid,
|
||
hauptbeschaeftigung_ort
|
||
)
|
||
VALUES (
|
||
123,
|
||
true,
|
||
'Max',
|
||
'Mustermann',
|
||
now(),
|
||
'00815',
|
||
'Musterstrasse',
|
||
1,
|
||
'test_card',
|
||
'1'
|
||
);
|
||
|
||
-- @block select
|
||
SELECT *
|
||
FROM personal_daten;
|
||
|
||
-- @block work and pause time
|
||
WITH ordered_bookings AS (
|
||
SELECT
|
||
timestamp,
|
||
check_in_out,
|
||
LAG(timestamp) OVER (PARTITION BY card_uid ORDER BY timestamp) AS prev_timestamp,
|
||
LAG(check_in_out) OVER (PARTITION BY card_uid ORDER BY timestamp) AS prev_check
|
||
FROM anwesenheit
|
||
WHERE card_uid = 'acde-edca' -- Replace with actual card_uid
|
||
AND timestamp::DATE = '2025-02-23' -- Replace with actual date
|
||
)
|
||
SELECT
|
||
-- Total work time: Duration between check-in (1,3) and check-out (2,4,254)
|
||
COALESCE(
|
||
SUM(
|
||
CASE
|
||
WHEN prev_check IN (1, 3) AND check_in_out IN (2, 4, 254)
|
||
THEN timestamp - prev_timestamp
|
||
ELSE INTERVAL '0'
|
||
END
|
||
), INTERVAL '0'
|
||
) AS total_work,
|
||
|
||
-- Total pause time: Duration between check-out (2,4,254) and next check-in (1,3)
|
||
COALESCE(
|
||
SUM(
|
||
CASE
|
||
WHEN prev_check IN (2, 4, 254) AND check_in_out IN (1, 3)
|
||
THEN timestamp - prev_timestamp
|
||
ELSE INTERVAL '0'
|
||
END
|
||
), INTERVAL '0'
|
||
) AS total_pause
|
||
|
||
FROM ordered_bookings;
|
||
|
||
-- @block work and pause time multi day
|
||
WITH ordered_bookings AS (
|
||
SELECT
|
||
timestamp::DATE AS work_date, -- Extract date for grouping
|
||
timestamp,
|
||
check_in_out,
|
||
LAG(timestamp) OVER (
|
||
PARTITION BY card_uid, timestamp::DATE -- Reset for each day
|
||
ORDER BY timestamp
|
||
) AS prev_timestamp,
|
||
LAG(check_in_out) OVER (
|
||
PARTITION BY card_uid, timestamp::DATE
|
||
ORDER BY timestamp
|
||
) AS prev_check
|
||
FROM anwesenheit
|
||
WHERE card_uid = $1 -- Replace with actual card_uid
|
||
AND timestamp::DATE >= $2 -- Set date range
|
||
AND timestamp::DATE < $3
|
||
)
|
||
SELECT
|
||
work_date,
|
||
|
||
-- Total work time per day
|
||
COALESCE(
|
||
EXTRACT(EPOCH FROM SUM(
|
||
CASE
|
||
WHEN prev_check IN (1, 3) AND check_in_out IN (2, 4, 254)
|
||
THEN timestamp - prev_timestamp
|
||
ELSE INTERVAL '0'
|
||
END
|
||
)), 0
|
||
) AS total_work,
|
||
|
||
-- Extract total pause time in seconds
|
||
COALESCE(
|
||
EXTRACT(EPOCH FROM SUM(
|
||
CASE
|
||
WHEN prev_check IN (2, 4, 254) AND check_in_out IN (1, 3)
|
||
THEN timestamp - prev_timestamp
|
||
ELSE INTERVAL '0'
|
||
END
|
||
)), 0
|
||
) AS total_pause
|
||
|
||
FROM ordered_bookings
|
||
GROUP BY work_date
|
||
ORDER BY work_date;
|
||
|
||
|
||
-- Generate weekdays for 2 weeks (Mon–Fri), starting 2 weeks ago
|
||
WITH days AS (
|
||
SELECT gs::date AS work_date
|
||
FROM generate_series(
|
||
date_trunc('week', CURRENT_DATE) - interval '14 days', -- start 2 weeks ago Monday
|
||
CURRENT_DATE, -- end TODAY (no future days)
|
||
interval '1 day'
|
||
) gs
|
||
WHERE EXTRACT(ISODOW FROM gs) <= 5 -- only Mon–Fri
|
||
),
|
||
sample_bookings AS (
|
||
SELECT
|
||
d.work_date,
|
||
'aaaa-aaaa'::varchar AS card_uid,
|
||
1 AS check_in_out, -- come
|
||
101 AS geraet_id,
|
||
(d.work_date + make_time(8, floor(random()*50)::int, 0))::timestamptz AS ts,
|
||
1 AS anwesenheit_typ
|
||
FROM days d
|
||
UNION ALL
|
||
SELECT
|
||
d.work_date,
|
||
'aaaa-aaaa'::varchar AS card_uid,
|
||
2 AS check_in_out, -- go
|
||
101 AS geraet_id,
|
||
(d.work_date + make_time(16, floor(random()*50)::int, 0))::timestamptz AS ts,
|
||
1 AS anwesenheit_typ
|
||
FROM days d
|
||
),
|
||
ins_anw AS (
|
||
-- insert only bookings up to now (prevents future times on today)
|
||
INSERT INTO anwesenheit ("timestamp", card_uid, check_in_out, geraet_id)
|
||
SELECT ts, card_uid, check_in_out, geraet_id
|
||
FROM sample_bookings
|
||
WHERE ts <= NOW()
|
||
RETURNING 1
|
||
)
|
||
-- now insert absences (uses the same days CTE)
|
||
INSERT INTO abwesenheit (card_uid, abwesenheit_typ, datum)
|
||
SELECT
|
||
'aaaa-aaaa',
|
||
(ARRAY[1, 2])[floor(random()*2 + 1)], -- example types
|
||
d.work_date::timestamptz
|
||
FROM days d
|
||
WHERE random() < 0.2 -- ~20% random absences
|
||
ORDER BY d.work_date;
|
||
|
||
|
||
WITH params AS (
|
||
SELECT
|
||
'aaaa-aaaa'::varchar AS card_uid,
|
||
101::int AS geraet_id,
|
||
14::int AS start_days_ago, -- how many days back to start
|
||
0::int AS end_days_ahead, -- how many days forward (0 = today)
|
||
0::float AS pause_probability,
|
||
0.0::float AS absence_probability
|
||
),
|
||
days AS (
|
||
SELECT gs::date AS work_date, p.card_uid, p.geraet_id, p.pause_probability, p.absence_probability
|
||
FROM params p,
|
||
generate_series(
|
||
date_trunc('week', CURRENT_DATE) - (p.start_days_ago || ' days')::interval,
|
||
CURRENT_DATE + (p.end_days_ahead || ' days')::interval,
|
||
interval '1 day'
|
||
) gs
|
||
WHERE EXTRACT(ISODOW FROM gs) <= 5 -- only Mon–Fri
|
||
),
|
||
base_bookings AS (
|
||
-- come
|
||
SELECT
|
||
d.work_date, d.card_uid, 1 AS check_in_out, d.geraet_id,
|
||
(d.work_date + make_time(8, floor(random()*40)::int, 0))::timestamptz AS ts
|
||
FROM days d
|
||
UNION ALL
|
||
-- go
|
||
SELECT
|
||
d.work_date, d.card_uid, 2 AS check_in_out, d.geraet_id,
|
||
(d.work_date + make_time(16, floor(random()*40)::int, 0))::timestamptz AS ts
|
||
FROM days d
|
||
),
|
||
pause_bookings AS (
|
||
-- pause come
|
||
SELECT
|
||
d.work_date, d.card_uid, 3 AS check_in_out, d.geraet_id,
|
||
(d.work_date + make_time(11, floor(random()*30)::int, 0))::timestamptz AS ts
|
||
FROM days d
|
||
WHERE random() < d.pause_probability
|
||
UNION ALL
|
||
-- pause go
|
||
SELECT
|
||
d.work_date, d.card_uid, 4 AS check_in_out, d.geraet_id,
|
||
(d.work_date + make_time(12, floor(random()*30)::int, 0))::timestamptz AS ts
|
||
FROM days d
|
||
WHERE random() < d.pause_probability
|
||
),
|
||
all_bookings AS (
|
||
SELECT * FROM base_bookings
|
||
UNION ALL
|
||
SELECT * FROM pause_bookings
|
||
),
|
||
ins_anw AS (
|
||
INSERT INTO anwesenheit ("timestamp", "card_uid", "check_in_out", "geraet_id", "anwesenheit_typ")
|
||
SELECT ts, card_uid, check_in_out, geraet_id, 1 as anwesenheit_typ
|
||
FROM all_bookings
|
||
WHERE ts <= NOW()
|
||
ORDER BY work_date, ts
|
||
RETURNING 1
|
||
)
|
||
INSERT INTO abwesenheit (card_uid, abwesenheit_typ, datum)
|
||
SELECT
|
||
d.card_uid,
|
||
(ARRAY[1, 2])[floor(random()*2 + 1)], -- example types
|
||
d.work_date::timestamptz
|
||
FROM days d
|
||
WHERE random() < d.absence_probability
|
||
ORDER BY d.work_date;
|